17 files changed, 156 insertions, 0 deletions
diff --git a/OAuth/schema/OAuth.sql b/OAuth/schema/OAuth.sql
new file mode 100644
index 00000000..01e326da
--- /dev/null
+++ b/OAuth/schema/OAuth.sql
@@ -0,0 +1,114 @@
+-- (c) Aaron Schulz, 2013
+
+-- Replace /*_*/ with the proper prefix
+
+-- These tables should belong in one central DB per wiki-farm
+
+-- Client consumers (proposed as well as and accepted)
+CREATE TABLE IF NOT EXISTS /*_*/oauth_registered_consumer (
+    -- Immutable fields below:
+    -- Consumer ID (1:1 with oarc_consumer_key)
+    oarc_id integer unsigned NOT NULL PRIMARY KEY auto_increment,
+    -- OAuth consumer key and secret (or RSA key)
+    oarc_consumer_key varbinary(32) NOT NULL,
+    -- Name of the application
+    oarc_name varchar(128) binary NOT NULL,
+    -- (Central) user id of the user who proposed the application
+    oarc_user_id integer unsigned NOT NULL,
+    -- Version of the application
+    oarc_version varbinary(32) NOT NULL,
+    -- Callback URL
+    oarc_callback_url blob NOT NULL,
+    -- Is the consumer allowed to specify a callback URL? (See MWOAuthServer::checkCallback().)
+    oarc_callback_is_prefix tinyblob NULL DEFAULT NULL,
+    -- Application description
+    oarc_description blob NOT NULL,
+    -- Contact email address
+    oarc_email varchar(255) binary NOT NULL,
+    -- Confirmation of contact email address
+    oarc_email_authenticated varbinary(14) NULL,
+    -- Did the owner accept the developer agreement?
+    oarc_developer_agreement tinyint NOT NULL DEFAULT 0,
+    -- Is this consumer owner-only
+    oarc_owner_only tinyint NOT NULL DEFAULT 0,
+    -- What wiki this is allowed on (a single wiki or '*' for all)
+    oarc_wiki varbinary(32) NOT NULL,
+    -- Grants needed for client consumers
+    oarc_grants blob NOT NULL,
+    -- Timestamp of consumer proposal
+    oarc_registration varbinary(14) NOT NULL,
+
+    -- Mutable fields below:
+    oarc_secret_key varbinary(32) NULL,
+    oarc_rsa_key blob NULL,
+    -- JSON blob of allowed IP ranges
+    oarc_restrictions blob NOT NULL,
+    -- Stage in registration pipeline:
+    -- (0=proposed, 1=approved, 2=rejected, 3=expired, 4=disabled)
+    oarc_stage tinyint unsigned NOT NULL DEFAULT 0,
+    -- Timestamp of the last stage change
+    oarc_stage_timestamp varbinary(14) NOT NULL,
+    -- Whether this consumer is suppressed (hidden)
+    oarc_deleted tinyint unsigned NOT NULL DEFAULT 0,
+    -- Version of OAuth protocol this consumer uses
+    oarc_oauth_version TINYINT NOT NULL DEFAULT 1,
+    -- Allowed OAuth 2.0 grant types
+    oarc_oauth2_allowed_grants BLOB NULL,
+    -- OAuth2 flag indicating if consumer can be trusted with keeping secrets
+    oarc_oauth2_is_confidential TINYINT NOT NULL DEFAULT 1
+) /*$wgDBTableOptions*/;
+
+CREATE UNIQUE INDEX /*i*/oarc_consumer_key
+    ON /*_*/oauth_registered_consumer (oarc_consumer_key);
+CREATE UNIQUE INDEX /*i*/oarc_name_version_user
+    ON /*_*/oauth_registered_consumer (oarc_name,oarc_user_id,oarc_version);
+CREATE INDEX /*i*/oarc_user_id ON /*_*/oauth_registered_consumer (oarc_user_id);
+CREATE INDEX /*i*/oarc_stage_timestamp
+    ON /*_*/oauth_registered_consumer (oarc_stage,oarc_stage_timestamp);
+
+-- Grant approvals by users for consumers
+CREATE TABLE IF NOT EXISTS /*_*/oauth_accepted_consumer (
+    oaac_id integer unsigned NOT NULL PRIMARY KEY auto_increment,
+    -- The name of a wiki or "*"
+    oaac_wiki varchar(255) binary NOT NULL,
+    -- Key to the user who approved the consumer (on the central wiki)
+    oaac_user_id integer unsigned NOT NULL,
+    -- Key to the consumer
+    oaac_consumer_id integer unsigned NOT NULL,
+    -- Tokens for the consumer to act on behave of the user
+    oaac_access_token varbinary(32) NOT NULL,
+    oaac_access_secret varbinary(32) NOT NULL,
+    -- JSON blob of actually accepted grants
+    oaac_grants blob NOT NULL,
+    -- Timestamp of grant approval by the user
+    oaac_accepted varbinary(14) NOT NULL,
+    -- Version of OAuth protocol this consumer uses
+    oaac_oauth_version TINYINT NOT NULL DEFAULT 1
+) /*$wgDBTableOptions*/;
+
+CREATE UNIQUE INDEX /*i*/oaac_access_token
+    ON /*_*/oauth_accepted_consumer (oaac_access_token);
+CREATE UNIQUE INDEX /*i*/oaac_user_consumer_wiki
+    ON /*_*/oauth_accepted_consumer (oaac_user_id,oaac_consumer_id,oaac_wiki);
+CREATE INDEX /*i*/oaac_consumer_user
+    ON /*_*/oauth_accepted_consumer (oaac_consumer_id,oaac_user_id);
+CREATE INDEX /*i*/oaac_user_id ON /*_*/oauth_accepted_consumer (oaac_user_id,oaac_id);
+
+-- Access tokens used on OAuth2 requests
+CREATE TABLE IF NOT EXISTS /*_*/oauth2_access_tokens (
+    oaat_id integer unsigned NOT NULL PRIMARY KEY auto_increment,
+    -- Access token identifier
+    oaat_identifier varchar(255) NOT NULL,
+    -- Expiration timestamp
+    oaat_expires varbinary(14) NOT NULL,
+    -- Identifier of the acceptance that allows this access token to be created
+    oaat_acceptance_id integer unsigned NOT NULL,
+    -- Indicates if the access token has been revoked
+    oaat_revoked tinyint NOT NULL DEFAULT 0
+) /*$wgDBTableOptions*/;
+
+CREATE UNIQUE INDEX /*i*/oaat_identifier
+    ON /*_*/oauth2_access_tokens (oaat_identifier);
+
+CREATE INDEX /*i*/oaat_acceptance_id
+    ON /*_*/oauth2_access_tokens (oaat_acceptance_id);
diff --git a/OAuth/schema/index_on_oaat_acceptance_id.sql b/OAuth/schema/index_on_oaat_acceptance_id.sql
new file mode 100644
index 00000000..8caa11bf
--- /dev/null
+++ b/OAuth/schema/index_on_oaat_acceptance_id.sql
@@ -0,0 +1,2 @@
+CREATE INDEX /*i*/oaat_acceptance_id
+    ON /*_*/oauth2_access_tokens (oaat_acceptance_id);
diff --git a/OAuth/schema/mysql/callback_is_prefix.sql b/OAuth/schema/mysql/callback_is_prefix.sql
new file mode 100644
index 00000000..75881861
--- /dev/null
+++ b/OAuth/schema/mysql/callback_is_prefix.sql
@@ -0,0 +1 @@
+ALTER TABLE /*_*/oauth_registered_consumer ADD COLUMN `oarc_callback_is_prefix` tinyblob NULL DEFAULT NULL AFTER `oarc_callback_url`;
diff --git a/OAuth/schema/mysql/developer_agreement.sql b/OAuth/schema/mysql/developer_agreement.sql
new file mode 100644
index 00000000..742af2c3
--- /dev/null
+++ b/OAuth/schema/mysql/developer_agreement.sql
@@ -0,0 +1 @@
+ALTER TABLE /*_*/oauth_registered_consumer ADD COLUMN `oarc_developer_agreement` tinyint NOT NULL DEFAULT 0 AFTER `oarc_email_authenticated`;
diff --git a/OAuth/schema/mysql/oauth2_allowed_grants.sql b/OAuth/schema/mysql/oauth2_allowed_grants.sql
new file mode 100644
index 00000000..993cd15f
--- /dev/null
+++ b/OAuth/schema/mysql/oauth2_allowed_grants.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/oauth_registered_consumer
+	ADD oarc_oauth2_allowed_grants BLOB NULL;
diff --git a/OAuth/schema/mysql/oauth2_is_confidential.sql b/OAuth/schema/mysql/oauth2_is_confidential.sql
new file mode 100644
index 00000000..1864eed2
--- /dev/null
+++ b/OAuth/schema/mysql/oauth2_is_confidential.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/oauth_registered_consumer
+	ADD oarc_oauth2_is_confidential TINYINT NOT NULL DEFAULT 1;
diff --git a/OAuth/schema/mysql/oauth_version_accepted.sql b/OAuth/schema/mysql/oauth_version_accepted.sql
new file mode 100644
index 00000000..440d645c
--- /dev/null
+++ b/OAuth/schema/mysql/oauth_version_accepted.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/oauth_accepted_consumer
+	ADD oaac_oauth_version TINYINT NOT NULL DEFAULT 1;
diff --git a/OAuth/schema/mysql/oauth_version_registered.sql b/OAuth/schema/mysql/oauth_version_registered.sql
new file mode 100644
index 00000000..c9104c61
--- /dev/null
+++ b/OAuth/schema/mysql/oauth_version_registered.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/oauth_registered_consumer
+    ADD oarc_oauth_version TINYINT NOT NULL DEFAULT 1;
diff --git a/OAuth/schema/mysql/owner_only.sql b/OAuth/schema/mysql/owner_only.sql
new file mode 100644
index 00000000..f231f419
--- /dev/null
+++ b/OAuth/schema/mysql/owner_only.sql
@@ -0,0 +1 @@
+ALTER TABLE /*_*/oauth_registered_consumer ADD COLUMN `oarc_owner_only` tinyint NOT NULL DEFAULT 0 AFTER `oarc_developer_agreement`;
diff --git a/OAuth/schema/oauth2_access_tokens.sql b/OAuth/schema/oauth2_access_tokens.sql
new file mode 100644
index 00000000..73fdf86d
--- /dev/null
+++ b/OAuth/schema/oauth2_access_tokens.sql
@@ -0,0 +1,18 @@
+-- Access tokens used on OAuth2 requests
+CREATE TABLE IF NOT EXISTS /*_*/oauth2_access_tokens (
+    oaat_id integer unsigned NOT NULL PRIMARY KEY auto_increment,
+    -- Access token
+    oaat_identifier varchar(255) NOT NULL,
+    -- Expiration timestamp
+    oaat_expires varbinary(14) NOT NULL,
+	-- Identifier of the acceptance that allows this access token to be created
+	oaat_acceptance_id integer unsigned NOT NULL,
+    -- Indicates if the access token has been revoked
+    oaat_revoked tinyint NOT NULL DEFAULT 0
+) /*$wgDBTableOptions*/;
+
+CREATE UNIQUE INDEX /*i*/oaat_identifier
+    ON /*_*/oauth2_access_tokens (oaat_identifier);
+
+CREATE INDEX /*i*/oaat_acceptance_id
+	ON /*_*/oauth2_access_tokens (oaat_acceptance_id);
diff --git a/OAuth/schema/sqlite/callback_is_prefix.sql b/OAuth/schema/sqlite/callback_is_prefix.sql
new file mode 100644
index 00000000..13d84457
--- /dev/null
+++ b/OAuth/schema/sqlite/callback_is_prefix.sql
@@ -0,0 +1 @@
+ALTER TABLE /*_*/oauth_registered_consumer ADD COLUMN `oarc_callback_is_prefix` tinyblob NULL DEFAULT NULL;
diff --git a/OAuth/schema/sqlite/developer_agreement.sql b/OAuth/schema/sqlite/developer_agreement.sql
new file mode 100644
index 00000000..000bedec
--- /dev/null
+++ b/OAuth/schema/sqlite/developer_agreement.sql
@@ -0,0 +1 @@
+ALTER TABLE /*_*/oauth_registered_consumer ADD COLUMN `oarc_developer_agreement` tinyint NOT NULL DEFAULT 0;
diff --git a/OAuth/schema/sqlite/oauth2_allowed_grants.sql b/OAuth/schema/sqlite/oauth2_allowed_grants.sql
new file mode 100644
index 00000000..993cd15f
--- /dev/null
+++ b/OAuth/schema/sqlite/oauth2_allowed_grants.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/oauth_registered_consumer
+	ADD oarc_oauth2_allowed_grants BLOB NULL;
diff --git a/OAuth/schema/sqlite/oauth2_is_confidential.sql b/OAuth/schema/sqlite/oauth2_is_confidential.sql
new file mode 100644
index 00000000..1864eed2
--- /dev/null
+++ b/OAuth/schema/sqlite/oauth2_is_confidential.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/oauth_registered_consumer
+	ADD oarc_oauth2_is_confidential TINYINT NOT NULL DEFAULT 1;
diff --git a/OAuth/schema/sqlite/oauth_version_accepted.sql b/OAuth/schema/sqlite/oauth_version_accepted.sql
new file mode 100644
index 00000000..440d645c
--- /dev/null
+++ b/OAuth/schema/sqlite/oauth_version_accepted.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/oauth_accepted_consumer
+	ADD oaac_oauth_version TINYINT NOT NULL DEFAULT 1;
diff --git a/OAuth/schema/sqlite/oauth_version_registered.sql b/OAuth/schema/sqlite/oauth_version_registered.sql
new file mode 100644
index 00000000..c9104c61
--- /dev/null
+++ b/OAuth/schema/sqlite/oauth_version_registered.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/oauth_registered_consumer
+    ADD oarc_oauth_version TINYINT NOT NULL DEFAULT 1;
diff --git a/OAuth/schema/sqlite/owner_only.sql b/OAuth/schema/sqlite/owner_only.sql
new file mode 100644
index 00000000..c0ddbfa0
--- /dev/null
+++ b/OAuth/schema/sqlite/owner_only.sql
@@ -0,0 +1 @@
+ALTER TABLE /*_*/oauth_registered_consumer ADD COLUMN `oarc_owner_only` tinyint NOT NULL DEFAULT 0;