OAuth DB

MySQL JDJhJDA4JFN5TVNkcjlxQzBaeE55UG9QOVVVT3V0MllQS0k0NS9tWXQ4R3FqSGNBTkludktrL0k4eHNp h3. Useful Queries

see http://www.eliacom.com/mysql-gui-wp-errno-150.php see http://fromdual.com/innodb-version

mysql> SHOW DATABASES;
mysql> USE database_name;
mysql> SHOW TABLES;
mysql> SHOW CREATE TABLE table_name;
mysql> DESC table_name;
mysql> SHOW INNODB STATUS;
mysql> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY, PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR FROM information_schema.PLUGINS WHERE PLUGIN_NAME = ‘innodb’;

INSERT INTO eyefi_api.OAUTH_CLIENT (USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, NAME, URL, CREATED)
VALUES), MD5), ‘[“JSON array of URI”]’, ‘Unique App Name’, ‘http://disneyland.eye.fi’, NULL);

INSERT INTO eyefi_api.OAUTH_CLIENT (USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, NAME, URL, CREATED)
VALUES), MD5), ‘[“http://echo.eye.fi”]’, “Kim’s App 1000”, ‘http://disneyland.eye.fi’, NULL);

ALTER TABLE eyefi_api.OAUTH_APPAUTH ADD UNIQUE;

TOOLS
https://api2.eye.fi/oauth/echo.php URL Encoded: https%3A%2F%2Fapi2.eye.fi%2Foauth%2Fecho.php
https://api2.eye.fi/oauth/sig.php client_id client_secret redirect_uri and other query parameters to be used on the call

MINIMUM
https://api2.eye.fi/oauth/auth.php?client_id=_____&client_sig=_____&redirect_uri=_____&response_type=code
https://api2.eye.fi/oauth/access.php?client_id=_____&client_sig=_____&code=_____&grant_type=authorization_code&redirect_uri=_____

NORMAL
https://api2.eye.fi/oauth/auth.php?client_id=1f9151e7ffa6580e62f371ca316d677f&client_sig=_____&redirect_uri=_____&response_type=code&state=auth-token
https://api2.eye.fi/oauth/access.php?client_id=_____&client_sig=_____&code=_____&grant_type=authorization_code&redirect_uri=_____&state=access-token

EXAMPLE
https://api2.eye.fi/oauth/auth.php?client_id=1f9151e7ffa6580e62f371ca316d677f&client_sig=4856499fb14222198dc6744652061982&redirect_uri=http%3A%2F%2Fserver.eyefi.vm%2Foauth%2Fecho.php&response_type=code&state=auth-token

https://api2.eye.fi/oauth/access.php?client_id=1f9151e7ffa6580e62f371ca316d677f&client_sig=f366dce4941ac1885dafc3e9a6da5c77&code=JDJ5JDA4JHh0aE1yM0tNN0FqalNQaVd5LkZyQU9jSFRIR3YzRGdkRWNSQ1NLS0trRU0wUEQwV2FRZlIu&grant_type=authorization_code&redirect_uri=http%3A%2F%2Fserver.eyefi.vm%2Foauth%2Fecho.php&state=access-token

http://server.eyefi.vm/oauth/sig.php?client_id=1234&client_secret=affce40e&code=0abfca32ef&grant_type=authorization_code&redirect_uri=http%3A%2F%2Fserver.eyefi.vm%2Foauth%2Fecho.php&state=access-token

https://api2.eye.fi/oauth/auth.php client_id client_sig redirect_uri response_type=code

https://api2.eye.fi/oauth/access.php client_id client_sig code grant_type=authorization_code redirect_uri

INSERT INTO eyefi_api.OAUTH_CLIENT (USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, NAME, URL, CREATED) VALUES), MD5), ‘[“http://runeimp.com/myapp_uri”,“http://myapp_uri.runeimp.com/two-b.php”]’, ‘My App 1002’, ‘http://apps.eye.fi/my-app-1002’, NULL
);

INSERT INTO eyefi_api.OAUTH_APPAUTH (CLIENT_ID, USER_ID, CREATED) VALUES, NULL
);

SELECT ID, CLIENT_ID, USER_ID, CREATED, MODIFIED FROM eyefi_api.OAUTH_APPAUTH;
SELECT USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, NAME, URL CREATED FROM eyefi_api.OAUTH_CLIENT;
SELECT USER_ID, CLIENT_ID, CLIENT_SECRET, NAME, CREATED FROM eyefi_api.OAUTH_CLIENT;
SELECT ID, CLIENT_ID, USER_ID, VALUE, TYPE, CREATED, EXPIRES_IN, VALID+0 AS VALID FROM eyefi_api.OAUTH_TOKEN;

UPDATE eyefi_api.oauth_appauth SET field = b’1’ WHERE client_id = ‘e7db7c767927e525d5d716c597b3f6c3’;

SELECT ID, CLIENT_ID, USER_ID, VALUE, TYPE, CREATED, EXPIRES_IN, VALID+0 AS VALID FROM eyefi_api.OAUTH_TOKEN;

mysql> SELECT API_KEY, SECRET, OPTIONS, DOMAINS, OWNER, COMMENTS FROM eyefi_api.API_KEYS WHERE API_KEY = ‘1f9151e7ffa6580e62f371ca316d677f’;
mysql> SELECT API_KEY, SECRET, OPTIONS, DOMAINS, OWNER, COMMENTS FROM eyefi_api.API_KEYS WHERE API_KEY = ‘4cc6b62ace52384f28d7f8c017a0cd5e’;
mysql> SELECT API_KEY, SECRET, OPTIONS, DOMAINS, OWNER, COMMENTS FROM eyefi_api.API_KEYS WHERE API_KEY = ‘cae35175b4271f96a987ad9dfe905d2b’;
mysql> INSERT INTO eyefi_api.API_KEYS(API_KEY, SECRET, OPTIONS, DOMAINS, OWNER, COMMENTS) VALUES (‘4cc6b62ace52384f28d7f8c017a0cd5e’, ‘526238584eecdaa51f80ae5a123c2f94’, 32, ‘internal,public,bulk’, ‘635’, ‘kim@eye.fi (OAuth)’);
mysql> UPDATE eyefi_api.API_KEYS SET OWNER = ‘635’ WHERE API_KEY = ‘4cc6b62ace52384f28d7f8c017a0cd5e’;

mysql> UPDATE eyefi_api.OAUTH_CLIENT SET CLIENT_ID = ‘cae35175b4271f96a987ad9dfe905d2b’, CLIENT_SECRET = ‘674d7a59d1bb2bebab97a63c5cf51242’ WHERE CLIENT_ID = ‘4cc6b62ace52384f28d7f8c017a0cd5e’;
mysql> SELECT ID, USER_ID, CLIENT_ID, CLIENT_SECRET, NAME, CREATED, MODIFIED FROM eyefi_api.OAUTH_CLIENT;

mysql> INSERT INTO eyefi_api.API_KEYS(API_KEY, SECRET, OPTIONS, DOMAINS, OWNER, COMMENTS) VALUES (‘1f9151e7ffa6580e62f371ca316d677f’, ‘affce40e603ba64b3e59c7f3e4af2593’, 32, ‘internal,public,bulk’, ‘732330’, ‘runeimp@eye.fi (OAuth)’);

——————-+———————+———+——-+—————————-+———- | Field | Type | Null | Key | Default | Extra |
——————-+———————+———+——-+—————————-+———- | API_KEY | varchar(32) | NO | PRI | NULL | | | SECRET | varchar(32) | NO | | NULL | | | OPTIONS | int(11) | NO | | 0 | | | ENABLE | tinyint(4) | NO | | 1 | | | DOMAINS | varchar(50) | NO | | NULL | | | OWNER | varchar(50) | NO | | NULL | | | COMMENTS | varchar(100) | NO | | NULL | | | USAGE_COUNT | int(8) | NO | | 0 | | | CREATED | timestamp | NO | | CURRENT_TIMESTAMP | |
——————-+———————+———+——-+—————————-+———-

DB Schemas

MySQL Descriptions:

mysql> DESC OAUTH_APPAUTH;
+-----------+---------------------+------+-----+---------------------+-----------------------------+
| Field     | Type                | Null | Key | Default             | Extra                       |
+-----------+---------------------+------+-----+---------------------+-----------------------------+
| ID        | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment              |
| CLIENT_ID | varchar(32)         | NO   |     | NULL                |                             |
| USER_ID   | int(11) unsigned    | YES  | MUL | NULL                |                             |
| FIELD     | bit(1)              | YES  |     | b'1'                |                             |
| CREATED   | timestamp           | NO   |     | 0000-00-00 00:00:00 |                             |
| MODIFIED  | timestamp           | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+-----------+---------------------+------+-----+---------------------+-----------------------------+

mysql> DESC OAUTH_CLIENT;
+---------------+---------------------+------+-----+---------------------+-----------------------------+
| Field         | Type                | Null | Key | Default             | Extra                       |
+---------------+---------------------+------+-----+---------------------+-----------------------------+
| ID            | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment              |
| USER_ID       | int(11) unsigned    | YES  | MUL | NULL                |                             |
| CLIENT_ID     | varchar(32)         | NO   |     | NULL                |                             |
| CLIENT_SECRET | varchar(32)         | NO   |     | NULL                |                             |
| REDIRECT_URI  | text                | YES  |     | NULL                |                             |
| NAME          | varchar(128)        | YES  | UNI | NULL                |                             |
| URL           | varchar(128)        | YES  |     | NULL                |                             |
| CREATED       | timestamp           | NO   |     | 0000-00-00 00:00:00 |                             |
| MODIFIED      | timestamp           | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+---------------+---------------------+------+-----+---------------------+-----------------------------+

mysql> DESC OAUTH_TOKEN;
+------------+---------------------------------+------+-----+-------------------+----------------+
| Field      | Type                            | Null | Key | Default           | Extra          |
+------------+---------------------------------+------+-----+-------------------+----------------+
| ID         | bigint(20) unsigned             | NO   | PRI | NULL              | auto_increment |
| CLIENT_ID  | varchar(32)                     | YES  | MUL | NULL              |                |
| USER_ID    | int(11) unsigned                | YES  | MUL | NULL              |                |
| VALUE      | varchar(128)                    | YES  |     | NULL              |                |
| TYPE       | enum('code','access','refresh') | YES  |     | code              |                |
| CREATED    | timestamp                       | NO   |     | CURRENT_TIMESTAMP |                |
| EXPIRES_IN | timestamp                       | YES  |     | NULL              |                |
| VALID      | bit(1)                          | YES  |     | b'1'              |                |
+------------+---------------------------------+------+-----+-------------------+----------------+

mysql> DESC API_KEYS;
+-------------+--------------+------+-----+-------------------+-------+
| Field       | Type         | Null | Key | Default           | Extra |
+-------------+--------------+------+-----+-------------------+-------+
| API_KEY     | varchar(32)  | NO   | PRI | NULL              |       |
| SECRET      | varchar(32)  | NO   |     | NULL              |       |
| OPTIONS     | int(11)      | NO   |     | 0                 |       |
| ENABLE      | tinyint(4)   | NO   |     | 1                 |       |
| DOMAINS     | varchar(50)  | NO   |     | NULL              |       |
| OWNER       | varchar(50)  | NO   |     | NULL              |       |
| COMMENTS    | varchar(100) | NO   |     | NULL              |       |
| USAGE_COUNT | int(8)       | NO   |     | 0                 |       |
| CREATED     | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
+-------------+--------------+------+-----+-------------------+-------+

SQL:

CREATE TABLE eyefi_api.API_KEYS (
	`API_KEY` varchar(32) NOT NULL, -- md5(uniqid())
	`SECRET` varchar(32) NOT NULL, -- md5(uniqid())
	`OPTIONS` int(11) NOT NULL DEFAULT '0',
	`ENABLE` tinyint(4) NOT NULL DEFAULT '1',
	`DOMAINS` varchar(50) NOT NULL, -- admin | bulk | internal | public
	`OWNER` varchar(50) NOT NULL,
	`COMMENTS` varchar(100) NOT NULL,
	`USAGE_COUNT` int(8) NOT NULL DEFAULT '0',
	`CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`API_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE eyefi_api.OAUTH_CLIENT (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	user_id INT(11) UNSIGNED,
	client_id varchar(32) NOT NULL, -- MD5 Hash
	client_secret varchar(32) NOT NULL, -- MD5 Hash
	redirect_uri TEXT NULL, -- JSON array of valid URIs
	name VARCHAR(128) UNIQUE, -- The English name for the app
	url VARCHAR(128), -- URL the app calls home
	created TIMESTAMP NOT NULL DEFAULT 0,
	modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	INDEX (user_id, client_id, client_secret),
	UNIQUE INDEX (name),
	FOREIGN KEY (user_id) REFERENCES USERS(USER_ID) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

CREATE TABLE eyefi_api.OAUTH_CLIENT (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	user_id INT(11) UNSIGNED,
	client_id varchar(32) NOT NULL,
	client_secret varchar(32) NOT NULL,
	redirect_uri TEXT NULL, -- JSON array of valid URIs
	name VARCHAR(128) UNIQUE, -- The English name for the app
	url VARCHAR(128), -- URL the app calls home
	created TIMESTAMP NOT NULL DEFAULT 0,
	modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	INDEX (user_id, client_id, client_secret),
	UNIQUE INDEX (name),
	FOREIGN KEY (user_id) REFERENCES USERS(USER_ID) ON DELETE CASCADE,
	FOREIGN KEY (client_id) REFERENCES API_KEYS(API_KEY) ON DELETE CASCADE,
	FOREIGN KEY (client_secret) REFERENCES API_KEYS(SECRET) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

SHOW CREATE TABLE eyefi_api.OAUTH_CLIENT;

SELECT ID, USER_ID, CLIENT_ID, CREATED, MODIFIED FROM eyefi_api.OAUTH_CLIENT;
SELECT USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, NAME, URL CREATED FROM eyefi_api.OAUTH_CLIENT;

INSERT INTO eyefi_api.OAUTH_CLIENT (USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, NAME, URL, CREATED) VALUES(
	732330, MD5(UUID_SHORT()), MD5(UUID_SHORT()),
	'["http://myapp_uri.runeimp.com/zero.php","http://runeimp.com/myapp_uri"]',
	'My App 1000', 'http://apps.eye.fi/my-app-1000', NULL);

UPDATE eyefi_api.oauth_client SET user_id = 732330, created = TIMESTAMP('2012-07-03 21:01:52') WHERE client_id = 'e7db7c767927e525d5d716c597b3f6c3';
UPDATE eyefi_api.oauth_client SET user_id = 732330 WHERE client_id = 'cfe5ca6c25ab708ecee7d89ec0fd62f8';

mysql> SELECT user_id, client_id, client_secret, name, created FROM eyefi_api.oauth_client;
+---------+----------------------------------+----------------------------------+-------------+---------------------+
| user_id | client_id                        | client_secret                    | name        | created             |
+---------+----------------------------------+----------------------------------+-------------+---------------------+
|  732330 | e7db7c767927e525d5d716c597b3f6c3 | 95b5a61f663db598c702141e64481898 | My App 1000 | 2012-07-03 21:01:52 |
|  732330 | cfe5ca6c25ab708ecee7d89ec0fd62f8 | ae70ae0d443c6c98a2b7a9fb719a9ac4 | My App 1001 | 2012-07-03 21:04:23 |
|  732330 | 7b47e658e4de3096b9453fdc4f80a3b5 | 11f3d4dc7e757c16832cc6a12457234b | My App 1002 | 2012-07-03 22:21:42 |
+---------+----------------------------------+----------------------------------+-------------+---------------------+

SELECT ID, CLIENT_ID, USER_ID, VALUE, TYPE, CREATED, EXPIRES_IN FROM eyefi_api.OAUTH_TOKEN;

INSERT INTO eyefi_api.OAUTH_TOKEN (CLIENT_ID, USER_ID, VALUE, TYPE, CREATED, EXPIRES_IN) VALUES('1f9151e7ffa6580e62f371ca316d677f', 732330, 'JDJ5JDA4JGxEQVJ4Nm15N2xqbVZ5UGdYQ2t6TnVWNTlvdnFNVVVwRDZnTGpNaVo5V09wNkJCNm9xUU55', 'code', NULL, 1341564600) ON DUPLICATE KEY UPDATE VALUE = 'asdf', EXPIRES_IN = 1341564600;
INSERT INTO eyefi_api.OAUTH_TOKEN (CLIENT_ID, USER_ID, VALUE, TYPE, CREATED, EXPIRES_IN) VALUES('1f9151e7ffa6580e62f371ca316d677f', 732330, 'JDJ5JDA4JGxEQVJ4Nm15N2xqbVZ5UGdYQ2t6TnVWNTlvdnFNVVVwRDZnTGpNaVo5V09wNkJCNm9xUU55', 'code', NULL, 1341564600) ON DUPLICATE KEY UPDATE VALUE = 'test', EXPIRES_IN = 1369;

UPDATE eyefi_api.OAUTH_TOKEN SET EXPIRES_IN = 1369007 WHERE CLIENT_ID = '1f9151e7ffa6580e62f371ca316d677f';
UPDATE eyefi_api.OAUTH_TOKEN SET EXPIRES_IN = FROM_UNIXTIME(1369007) WHERE CLIENT_ID = '1f9151e7ffa6580e62f371ca316d677f';
UPDATE eyefi_api.OAUTH_TOKEN SET EXPIRES_IN = '1980-12-07' WHERE CLIENT_ID = '1f9151e7ffa6580e62f371ca316d677f';

eyefi_api.OAUTH_APPAUTH
eyefi_api.OAUTH_CLIENT
eyefi_api.OAUTH_TOKEN

DROP TABLE eyefi_api.OAUTH_APPAUTH;
DROP TABLE eyefi_api.OAUTH_CLIENT;
DROP TABLE eyefi_api.OAUTH_TOKEN;
DROP TABLE eyefi_api.API_KEYS;
DROP TABLE eyefi_api.API_SESSIONS;
DROP TABLE eyefi_api.USERS;

CREATE TABLE eyefi_api.API_KEYS (
	`API_KEY` varchar(32) NOT NULL,
	`SECRET` varchar(32) NOT NULL,
	`OPTIONS` int(11) NOT NULL DEFAULT '0',
	`ENABLE` tinyint(4) NOT NULL DEFAULT '1',
	`DOMAINS` varchar(50) NOT NULL,
	`USER_ID` int(11) NOT NULL,
	`COMMENTS` varchar(100) NOT NULL,
	`USAGE_COUNT` int(8) NOT NULL DEFAULT '0',
	`CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`API_KEY`),
	INDEX (`SECRET`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE eyefi_api.API_SESSIONS (
	`SESSION_ID` varchar(100) NOT NULL,
	`SESSION_EXPIRES` int(11) unsigned NOT NULL DEFAULT '0',
	`SESSION_DATA` text,
	PRIMARY KEY (`SESSION_ID`),
	KEY `SESSION_EXPIRES` (`SESSION_EXPIRES`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE eyefi_api.USERS (
	`USER_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`LOGIN` varchar(255) NOT NULL,
	`PASSWORD` varchar(255) NOT NULL,
	`ADMIN` tinyint(4) NOT NULL DEFAULT '0',
	`USER_FIRST_NAME` varchar(255) NOT NULL,
	`USER_LAST_NAME` varchar(255) NOT NULL,
	`SECRET` varchar(50) NOT NULL COMMENT 'Used for verifying login email address.',
	`TOKEN` varchar(255) NOT NULL COMMENT 'Used for password reset.',
	`STABLE_RANDOM` varchar(40) NOT NULL DEFAULT 'cafebabecafebabecafebabecafebabecafebabe' COMMENT 'PERMANENTLY STABLE RANDOM DATA ASSOCIATED WITH A USER THROUGH ITS LIFETIME',
	`CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`HAS_MESSAGES` int(11) NOT NULL DEFAULT '0',
	`LOCALE` varchar(5) NOT NULL DEFAULT 'en_US',
	`COUNTRY_CODE` char(2) NOT NULL,
	`OPT_MAILING_LIST` tinyint(4) NOT NULL DEFAULT '1',
	`CREATOR_API_KEY` varchar(32) DEFAULT NULL,
	PRIMARY KEY (`USER_ID`),
	UNIQUE KEY `LOGIN` (`LOGIN`),
	KEY `TOKEN` (`TOKEN`),
	KEY `LOCALE` (`LOCALE`),
	KEY `SECRET` (`SECRET`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO eyefi_api.API_KEYS(USER_ID, API_KEY, SECRET, OPTIONS, DOMAINS, COMMENTS)
	VALUES (732330, '1f9151e7ffa6580e62f371ca316d677f', 'affce40e603ba64b3e59c7f3e4af2593',
	32, 'bulk,internal,public', 'runeimp@gmail.com (OAuth)');

INSERT INTO eyefi_api.API_KEYS(USER_ID, API_KEY, SECRET, OPTIONS, DOMAINS, COMMENTS)
	VALUES (732330, 'f04adbc8dded4b5f0d2b5e4681769410', '7daaaeff1db2066c3967c8d5eb3aa87d',
	32, 'bulk,internal,public', 'runeimp@gmail.com (OAuth)');

INSERT INTO eyefi_api.API_KEYS(USER_ID, API_KEY, SECRET, OPTIONS, DOMAINS, COMMENTS)
	VALUES (635, 'cae35175b4271f96a987ad9dfe905d2b', '674d7a59d1bb2bebab97a63c5cf51242',
	32, 'bulk,internal,public', 'kim@eye.fi (OAuth)');

SELECT * FROM eyefi_api.API_KEYS;

INSERT INTO eyefi_api.USERS(USER_ID, LOGIN, PASSWORD, ADMIN, USER_FIRST_NAME, USER_LAST_NAME, SECRET, TOKEN, COUNTRY_CODE)
	VALUES (635, 'kim@eye.fi', 'password', 1, 'Kim', 'Gustafson', '', '', 'US');
INSERT INTO eyefi_api.USERS(USER_ID, LOGIN, PASSWORD, ADMIN, USER_FIRST_NAME, USER_LAST_NAME, SECRET, TOKEN, COUNTRY_CODE)
	VALUES (732330, 'runeimp@gmail.com', 'EyeFiLock69', 1, 'Mark', 'Gardner', '', '', 'US');

CREATE TABLE eyefi_api.OAUTH_APPAUTH (
	ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	USER_ID INT(11) UNSIGNED,
	CLIENT_ID VARCHAR(32) NOT NULL,
	CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (ID),
	UNIQUE KEY (USER_ID, CLIENT_ID),
	INDEX (USER_ID, CLIENT_ID),
	FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE,
	FOREIGN KEY (CLIENT_ID) REFERENCES API_KEYS(API_KEY) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT * FROM eyefi_api.OAUTH_APPAUTH;

-- Should probably be eyefi_api.API_CLIENT

DROP TABLE eyefi_api.OAUTH_CLIENT;

CREATE TABLE eyefi_api.OAUTH_CLIENT (
	ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	USER_ID INT(11) UNSIGNED,
	CLIENT_ID varchar(32) NOT NULL, -- MD5 Hash
	CLIENT_SECRET varchar(32) NOT NULL, -- MD5 Hash
	REDIRECT_URI TEXT NULL, -- JSON array of valid URIs
	DEV_NAME VARCHAR(128), -- The English name for the app developer
	CLIENT_NAME VARCHAR(128) UNIQUE, -- The English name for the app
	CLIENT_TYPE ENUM('public', 'confidential') NOT NULL DEFAULT 'public', -- confidential (web app), public (user-agent-based or native app)
	HOME VARCHAR(128), -- URL the app or dev website
	LOGO VARCHAR(128), -- URL for the app or dev logo
	CREATED TIMESTAMP NOT NULL DEFAULT 0,
	MODIFIED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (ID),
	INDEX (USER_ID, CLIENT_ID, CLIENT_SECRET),
	UNIQUE INDEX (CLIENT_NAME),
	FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE,
	FOREIGN KEY (CLIENT_ID) REFERENCES API_KEYS(API_KEY) ON DELETE CASCADE,
	FOREIGN KEY (CLIENT_SECRET) REFERENCES API_KEYS(SECRET) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO eyefi_api.OAUTH_CLIENT(USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, DEV_NAME, CLIENT_NAME, CLIENT_TYPE, HOME, LOGO, CREATED)
	VALUES (732330, '1f9151e7ffa6580e62f371ca316d677f', 'affce40e603ba64b3e59c7f3e4af2593',
	'["http://myapp_uri.runeimp.com/zero.php","http://runeimp.com/myapp_uri"]', 'RuneImp', 'RuneApp 1000', 1,
	'http://apps.eye.fi/my-app-1000', 'http://apps.eye.fi/my-app-1000/logo.png', NULL);

INSERT INTO eyefi_api.OAUTH_CLIENT(USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, DEV_NAME, CLIENT_NAME, CLIENT_TYPE, HOME, LOGO, CREATED)
	VALUES (732330, 'f04adbc8dded4b5f0d2b5e4681769410', '7daaaeff1db2066c3967c8d5eb3aa87d',
	'["https://dev-staging.eye.fi/tools/explorer/oauth.php"]', 'RuneImp', 'Eye-Fi API 2.0 Explorer', 'confidential',
	'https://dev-staging.eye.fi/tools/explorer/', 'https://dev-staging.eye.fi/assets/img/favicon_114.png', NULL);

INSERT INTO eyefi_api.OAUTH_CLIENT(USER_ID, CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, DEV_NAME, CLIENT_NAME, CLIENT_TYPE, HOME, LOGO, CREATED)
	VALUES (635, 'cae35175b4271f96a987ad9dfe905d2b', '674d7a59d1bb2bebab97a63c5cf51242',
	'["http://echo.eye.fi"]', 'Kim G', "Kim's App 1000", 2,
	'http://disneyland.eye.fi', 'http://disneyland.eye.fi/logo.jpg', NULL);

SELECT USER_ID, CLIENT_ID, CLIENT_SECRET, CLIENT_NAME, DEV_NAME, HOME, LOGO, CREATED FROM eyefi_api.OAUTH_CLIENT;
SELECT ID, USER_ID, CLIENT_ID, CLIENT_SECRET, DEV_NAME, CLIENT_NAME, CLIENT_TYPE, CREATED FROM eyefi_api.OAUTH_CLIENT;

CREATE TABLE eyefi_api.OAUTH_TOKEN (
	ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	CLIENT_ID VARCHAR(32),
	USER_ID INT(11) UNSIGNED,
	VALUE VARCHAR(128), -- Blowfish Hash
	TYPE ENUM('code', 'access', 'refresh') DEFAULT 'code', -- Enumeration of token types
	REDIRECT_URI VARCHAR(32) NOT NULL, -- A valid URI
	CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	EXPIRES_IN TIMESTAMP NOT NULL DEFAULT 0, -- CURRENT_TIMESTAMP+@value WHERE @value equals 600 for code (10 minutes), 86400 for access (24 hours), and 15724800 for refresh (half a year).
	PRIMARY KEY (ID),
	INDEX (CLIENT_ID),
	UNIQUE INDEX (CLIENT_ID, USER_ID, TYPE),
	FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE,
	FOREIGN KEY (CLIENT_ID) REFERENCES API_KEYS(API_KEY) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE eyefi_api.OAUTH_TOKEN DROP VALID;

SELECT * FROM eyefi_api.OAUTH_TOKEN;
SELECT ID, CLIENT_ID, USER_ID, VALUE, TYPE, CREATED, EXPIRES_IN FROM eyefi_api.OAUTH_TOKEN;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| eyefi_api          |
| eyefi_sdmfg        |
| eyefi_util         |
| mysql              |
| theschwartz        |
+--------------------+

mysql> USE eyefi_api;
mysql> SHOW TABLES;
+-----------------------------+
| Tables_in_eyefi_api         |
+-----------------------------+
| ACTIVITY_FEED_SETTINGS      |
| API_KEYS                    |
| API_SESSIONS                |
| CARD_FIRMWARES              |
| CARD_MANUFACTURING          |
| CARD_SESSIONS               |
| CARD_TO_PROFILE             |
| CARD_TO_SUBSCRIBERS         |
| CLEANUP_POLICY              |
| DATA_STORE                  |
| DEVICES                     |
| DEVICESCAPE_ACCOUNTING      |
| DEVICE_TO_CLEANUPPOLICY     |
| ES_SUBSCRIPTION_EVENT_TYPES |
| ES_SUBSCRIPTION_EXCEPTIONS  |
| ES_SUBSCRIPTION_FEED        |
| GEO_CORRECTION              |
| GEO_RESPONSE                |
| GEO_RESPONSE_CACHE          |
| LICENSES                    |
| MAC_RANGE_TO_SERVICE        |
| MESSAGES                    |
| OAUTH_ACCESS_TOKENS         |
| OAUTH_AUTH_CODE             |
| PRODUCT_TYPES               |
| SERVICES                    |
| SERVICES_CLICK_THROUGH      |
| SERVICE_PROFILES            |
| SHEEPNOTRY_RMA_TRACKING     |
| SUPPORT_CAMERA_URLS         |
| SUSPENDED_DEVICES           |
| SYNC_TRANSACTIONS           |
| TEST_GROUPS                 |
| TEST_GROUPS_GENID           |
| TEST_GROUP_USERS            |
| UPLOAD_SESSIONS             |
| USERS                       |
| USER_MESSAGE_STATUS         |
| USER_SUBSCRIBERS            |
| XMPP_ACCESS_TOKENS          |
+-----------------------------+

mysql> DESC OAUTH_ACCESS_TOKENS;
+---------------+--------------+------+-----+-------------------+----------------+
| Field         | Type         | Null | Key | Default           | Extra          |
+---------------+--------------+------+-----+-------------------+----------------+
| ID            | int(11)      | NO   | PRI | NULL              | auto_increment |
| ACCESS_TOKEN  | varchar(40)  | NO   |     | NULL              |                |
| REFRESH_TOKEN | varchar(40)  | NO   |     | NULL              |                |
| CLIENT_ID     | varchar(32)  | NO   |     | NULL              |                |
| SCOPE         | varchar(255) | NO   |     | NULL              |                |
| AT_EXPIRES_IN | int(11)      | NO   |     | NULL              |                |
| RT_EXPIRES_IN | int(11)      | NO   |     | NULL              |                |
| ENABLED       | tinyint(1)   | YES  |     | 1                 |                |
| CREATED       | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+---------------+--------------+------+-----+-------------------+----------------+

mysql> DESC OAUTH_AUTH_CODE;
+--------------+--------------+------+-----+-------------------+----------------+
| Field        | Type         | Null | Key | Default           | Extra          |
+--------------+--------------+------+-----+-------------------+----------------+
| ID           | int(11)      | NO   | PRI | NULL              | auto_increment |
| CODE         | varchar(40)  | NO   | UNI | NULL              |                |
| SCOPE        | varchar(255) | NO   |     | NULL              |                |
| CLIENT_ID    | varchar(32)  | NO   |     | NULL              |                |
| EXPIRES_IN   | int(11)      | NO   |     | NULL              |                |
| CALLBACK_URL | varchar(255) | YES  |     | NULL              |                |
| STATE        | varchar(255) | YES  |     | NULL              |                |
| CREATED      | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+--------------+--------------+------+-----+-------------------+----------------+

mysql> DESC USERS;
+------------------+------------------+------+-----+------------------------------------------+----------------+
| Field            | Type             | Null | Key | Default                                  | Extra          |
+------------------+------------------+------+-----+------------------------------------------+----------------+
| USER_ID          | int(11) unsigned | NO   | PRI | NULL                                     | auto_increment |
| LOGIN            | varchar(255)     | NO   | UNI | NULL                                     |                |
| PASSWORD         | varchar(255)     | NO   |     | NULL                                     |                |
| ADMIN            | tinyint(4)       | NO   |     | 0                                        |                |
| USER_FIRST_NAME  | varchar(255)     | NO   |     | NULL                                     |                |
| USER_LAST_NAME   | varchar(255)     | NO   |     | NULL                                     |                |
| SECRET           | varchar(50)      | NO   | MUL | NULL                                     |                |
| TOKEN            | varchar(255)     | NO   | MUL | NULL                                     |                |
| STABLE_RANDOM    | varchar(40)      | NO   |     | cafebabecafebabecafebabecafebabecafebabe |                |
| CREATED          | timestamp        | NO   |     | CURRENT_TIMESTAMP                        |                |
| HAS_MESSAGES     | int(11)          | NO   |     | 0                                        |                |
| LOCALE           | varchar(5)       | NO   | MUL | en_US                                    |                |
| COUNTRY_CODE     | char(2)          | NO   |     | NULL                                     |                |
| OPT_MAILING_LIST | tinyint(4)       | NO   |     | 1                                        |                |
| CREATOR_API_KEY  | varchar(32)      | YES  |     | NULL                                     |                |
+------------------+------------------+------+-----+------------------------------------------+----------------+

mysql> DESC API_SESSIONS;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| SESSION_ID      | varchar(100)     | NO   | PRI | NULL    |       |
| SESSION_EXPIRES | int(11) unsigned | NO   | MUL | 0       |       |
| SESSION_DATA    | text             | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+

• [..]
• API_Notes.textile
• AWS_Notes.textile
• Brightcove_Notes.textile
• CloudFoundry_Notes.textile
• eFax_Services_2009-02-20_v0001.txt
• Facebook_API_Samples.php
• Facebook_Notes.textile
• Flickr_API_Notes.textile
• Google_Libraries_API.textile
• LinkedIn_API.textile
• Matrix_URI_Notes.textile
• OAuth_DB.textile
• OAuth_Notes.textile
• PGP-GPG_Notes.textile
• RuneAuth.textile
• Twitter_API_Notes.textile
• XMPP_Notes.textile
• YouTube_API.textile
• YouTube_API_old.textile
• YQL-Yahoo_Query_Language.textile