Aaron Schulz has uploaded a new change for review. https://gerrit.wikimedia.org/r/69958
Change subject: Proposed SQL schema. ...................................................................... Proposed SQL schema. Change-Id: I93d30c7dc1c36dd572797d3b723346b02bb6e7fb --- M .gitignore M backend/schema/mysql/OAuth.sql 2 files changed, 79 insertions(+), 0 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/mediawiki/extensions/OAuth refs/changes/58/69958/1 diff --git a/.gitignore b/.gitignore index 98b092a..963b2ad 100644 --- a/.gitignore +++ b/.gitignore @@ -2,3 +2,4 @@ *~ *.kate-swp .*.swp +/nbproject/private/ \ No newline at end of file diff --git a/backend/schema/mysql/OAuth.sql b/backend/schema/mysql/OAuth.sql index 193d1a6..1635159 100644 --- a/backend/schema/mysql/OAuth.sql +++ b/backend/schema/mysql/OAuth.sql @@ -1,3 +1,81 @@ -- (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 auto_increment PRIMARY KEY, + -- OAuth consumer key and secret (or RSA key) + oarc_consumer_key varbinary(32) NOT NULL, + oarc_secret_key varbinary(32) NULL, + oarc_rsa_key blob NULL, + -- Callback URL + oarc_callback_url blob NOT NULL, + -- Name of the application + oarc_name varchar(255) NOT NULL, + -- Key to the user who proposed the application + oarc_user_id integer unsigned NOT NULL, + -- Version of the application + oarc_version DECIMAL(9,3) NOT NULL, + -- Application description + oarc_description blob NOT NULL, + -- Confirmed contact email address + oarc_email varchar(255) NOT NULL, + -- What wiki this is allowed on (a single wiki or '*' for all) + oarc_wiki varbinary(32) NOT NULL, + -- JSON blob of allowed IP ranges + oarc_origin_restrictions blob NOT NULL, + -- Timestamp of consumer proposal + oarc_registration varbinary(14) NOT NULL, + + -- Mutable fields below: + -- Stage in registration pipeline (0=new, 1=held, 2=approved, 3=rejected, 4=expired, 5=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 +); + +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 /*i*/ oauth_registered_consumer (oarc_stage,oarc_stage_timestamp); + +-- Grants needed for client consumers (immutable) +CREATE TABLE IF NOT EXISTS /*_*/oauth_required_grant ( + -- Key to the consumer + oarg_consumer_id integer unsigned NOT NULL, + -- Name of the grant defined by configuration + oarg_grant varchar(32) +); + +CREATE UNIQUE INDEX /*i*/oarg_consumer_grant + ON /*_*/oauth_required_grant (oarg_consumer_id,oarg_grant); +CREATE INDEX /*i*/oarg_grant ON /*_*/oauth_required_grant (oarg_grant); + +-- Grant approvals by users for consumers +CREATE TABLE IF NOT EXISTS /*_*/oauth_accepted_consumer ( + -- The name of a wiki or "*" + oaac_wiki varchar(255) 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, + -- Timestamp of grant approval by the user + oaac_accepted varbinary(14) NOT NULL +); + +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); -- To view, visit https://gerrit.wikimedia.org/r/69958 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I93d30c7dc1c36dd572797d3b723346b02bb6e7fb Gerrit-PatchSet: 1 Gerrit-Project: mediawiki/extensions/OAuth Gerrit-Branch: master Gerrit-Owner: Aaron Schulz <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
