# HG changeset patch # User jbe # Date 1345393007 -7200 # Node ID 3ac4a5664f5c8cdafb43552d6474888ea68cda76 # Parent 2fd3ac2f8323dd488ecbdd7e4204df219dd8d312 Removed preliminary API tables for upcoming release (will be included later) diff -r 2fd3ac2f8323 -r 3ac4a5664f5c core.sql --- a/core.sql Wed Aug 15 11:08:13 2012 +0200 +++ b/core.sql Sun Aug 19 18:16:47 2012 +0200 @@ -194,101 +194,6 @@ -- END OF DEPRECARED API TABLES -- --- NEW PRELIMINARY API TABLES -- - -CREATE TYPE "api_access_level" AS ENUM ( - 'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' ); - -COMMENT ON TYPE "api_access_level" IS 'PRELIMINARY, SUBJECT TO CHANGE! Access scope for API consumers.'; - - -CREATE TABLE "registered_client" ( - "id" SERIAL8 PRIMARY KEY, - "name" TEXT NOT NULL, - "url" TEXT, - "client_identifier" TEXT NOT NULL UNIQUE, - "client_secret" TEXT, - "code_grant" BOOLEAN NOT NULL, - "implicit_grant" BOOLEAN NOT NULL, - "client_grant" BOOLEAN NOT NULL, - "access_level" "api_access_level", - "client_grant_access_level" "api_access_level", - "single_token" BOOLEAN NOT NULL DEFAULT FALSE, - "always_authorized" BOOLEAN NOT NULL DEFAULT FALSE, - "auth_duration" INTERVAL, - "login_duration" INTERVAL, - "refresh_duration" INTERVAL, - "access_duration" INTERVAL, - CONSTRAINT "code_or_implicit_grant_requires_access_level" - CHECK (("code_grant"=FALSE AND "implicit_grant"=FALSE) OR "access_level" NOTNULL), - CONSTRAINT "client_grant_requires_client_grant_access_level" - CHECK ("client_grant"=FALSE OR "client_grant_access_level" NOTNULL) ); - -COMMENT ON TABLE "registered_client" IS 'PRELIMINARY, SUBJECT TO CHANGE! OAuth2 client registered by administrator'; - -COMMENT ON COLUMN "registered_client"."name" IS 'Name of the registered client'; -COMMENT ON COLUMN "registered_client"."url" IS 'Optional URL for web clients'; -COMMENT ON COLUMN "registered_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint if "code_grant" or "implicit_grant" is set to TRUE'; -COMMENT ON COLUMN "registered_client"."client_secret" IS 'Secret for client authentication'; -COMMENT ON COLUMN "registered_client"."code_grant" IS 'Enable OAuth2 Authorization Code Grant'; -COMMENT ON COLUMN "registered_client"."implicit_grant" IS 'Enable OAuth2 Implicit Grant'; -COMMENT ON COLUMN "registered_client"."client_grant" IS 'Enable OAuth2 Client Credentials Grant'; -COMMENT ON COLUMN "registered_client"."access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant'; -COMMENT ON COLUMN "registered_client"."client_grant_access_level" IS 'Maximum access level for OAuth2 Client Credentials Grant'; -COMMENT ON COLUMN "registered_client"."single_token" IS 'Allow only one valid refresh token'; -COMMENT ON COLUMN "registered_client"."always_authorized" IS 'Members do not need to authorize the client'; -COMMENT ON COLUMN "registered_client"."auth_duration" IS 'Duration of authorization by member'; -COMMENT ON COLUMN "registered_client"."login_duration" IS 'Life time of refresh code chain'; -COMMENT ON COLUMN "registered_client"."refresh_duration" IS 'Life time of a refresh code'; -COMMENT ON COLUMN "registered_client"."access_duration" IS 'Life time of an access code'; - - -CREATE TABLE "authorized_client" ( - "id" SERIAL8 PRIMARY KEY, - UNIQUE ("client_identifier", "member_id"), - "client_identifier" TEXT NOT NULL, - "member_id" INT4 NOT NULL REFERENCES "member" ("id") - ON DELETE CASCADE ON UPDATE CASCADE, - "access_level" "api_access_level" NOT NULL, - "first_auth" TIMESTAMPTZ NOT NULL, - "last_auth" TIMESTAMPTZ NOT NULL, - UNIQUE ("client_identifier", "member_id") ); - -COMMENT ON TABLE "authorized_client" IS 'PRELIMINARY, SUBJECT TO CHANGE! OAuth2 client authorized by member, or automatically authorized for a member if "registered_client"."always_authorized" is set'; - -COMMENT ON COLUMN "authorized_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint'; -COMMENT ON COLUMN "authorized_client"."member_id" IS 'Member who authorized the client'; -COMMENT ON COLUMN "authorized_client"."access_level" IS 'Authorized access level'; -COMMENT ON COLUMN "authorized_client"."first_auth" IS 'Date/time of initial authorization'; -COMMENT ON COLUMN "authorized_client"."last_auth" IS 'Date/time of last authorization refresh'; - - -CREATE TABLE "authorized_client_token" ( - "id" SERIAL8 PRIMARY KEY, - "authorized_client_id" INT8 NOT NULL REFERENCES "authorized_client" - ON DELETE CASCADE ON UPDATE CASCADE, - "created" TIMESTAMPTZ NOT NULL DEFAULT now(), - "authorization_code" TEXT, - "refreshed" TIMESTAMPTZ, - "refresh_token" TEXT, - "old_refresh_token" TEXT, - CONSTRAINT "one_of_authorization_code_and_refresh_token_set" - CHECK ("authorization_code" NOTNULL OR "refresh_token" NOTNULL), - CONSTRAINT "refresh_token_if_and_only_if_refreshed" - CHECK ("refreshed" NOTNULL = "refresh_token" NOTNULL), - CONSTRAINT "old_refresh_token_requires_current_refresh_token" - CHECK ("refresh_token" NOTNULL OR "old_refresh_token" ISNULL) ); - -COMMENT ON TABLE "authorized_client_token" IS 'PRELIMINARY, SUBJECT TO CHANGE! Issued OAuth2 authorization codes and refresh tokens'; - -COMMENT ON COLUMN "authorized_client_token"."created" IS 'Date/time when authorization code (or first refresh token when there is no authorization code) has been created'; -COMMENT ON COLUMN "authorized_client_token"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)'; -COMMENT ON COLUMN "authorized_client_token"."refreshed" IS 'Date/time of last refresh'; -COMMENT ON COLUMN "authorized_client_token"."refresh_token" IS 'OAuth2 refresh token'; - --- END OF NEW PRELIMINARY API TABLES -- - - CREATE TABLE "member_history" ( "id" SERIAL8 PRIMARY KEY, "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,