liquid_feedback_core
changeset 282:3ac4a5664f5c
Removed preliminary API tables for upcoming release (will be included later)
author | jbe |
---|---|
date | Sun Aug 19 18:16:47 2012 +0200 (2012-08-19) |
parents | 2fd3ac2f8323 |
children | a00b58b7a510 |
files | core.sql |
line diff
1.1 --- a/core.sql Wed Aug 15 11:08:13 2012 +0200 1.2 +++ b/core.sql Sun Aug 19 18:16:47 2012 +0200 1.3 @@ -194,101 +194,6 @@ 1.4 -- END OF DEPRECARED API TABLES -- 1.5 1.6 1.7 --- NEW PRELIMINARY API TABLES -- 1.8 - 1.9 -CREATE TYPE "api_access_level" AS ENUM ( 1.10 - 'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' ); 1.11 - 1.12 -COMMENT ON TYPE "api_access_level" IS 'PRELIMINARY, SUBJECT TO CHANGE! Access scope for API consumers.'; 1.13 - 1.14 - 1.15 -CREATE TABLE "registered_client" ( 1.16 - "id" SERIAL8 PRIMARY KEY, 1.17 - "name" TEXT NOT NULL, 1.18 - "url" TEXT, 1.19 - "client_identifier" TEXT NOT NULL UNIQUE, 1.20 - "client_secret" TEXT, 1.21 - "code_grant" BOOLEAN NOT NULL, 1.22 - "implicit_grant" BOOLEAN NOT NULL, 1.23 - "client_grant" BOOLEAN NOT NULL, 1.24 - "access_level" "api_access_level", 1.25 - "client_grant_access_level" "api_access_level", 1.26 - "single_token" BOOLEAN NOT NULL DEFAULT FALSE, 1.27 - "always_authorized" BOOLEAN NOT NULL DEFAULT FALSE, 1.28 - "auth_duration" INTERVAL, 1.29 - "login_duration" INTERVAL, 1.30 - "refresh_duration" INTERVAL, 1.31 - "access_duration" INTERVAL, 1.32 - CONSTRAINT "code_or_implicit_grant_requires_access_level" 1.33 - CHECK (("code_grant"=FALSE AND "implicit_grant"=FALSE) OR "access_level" NOTNULL), 1.34 - CONSTRAINT "client_grant_requires_client_grant_access_level" 1.35 - CHECK ("client_grant"=FALSE OR "client_grant_access_level" NOTNULL) ); 1.36 - 1.37 -COMMENT ON TABLE "registered_client" IS 'PRELIMINARY, SUBJECT TO CHANGE! OAuth2 client registered by administrator'; 1.38 - 1.39 -COMMENT ON COLUMN "registered_client"."name" IS 'Name of the registered client'; 1.40 -COMMENT ON COLUMN "registered_client"."url" IS 'Optional URL for web clients'; 1.41 -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'; 1.42 -COMMENT ON COLUMN "registered_client"."client_secret" IS 'Secret for client authentication'; 1.43 -COMMENT ON COLUMN "registered_client"."code_grant" IS 'Enable OAuth2 Authorization Code Grant'; 1.44 -COMMENT ON COLUMN "registered_client"."implicit_grant" IS 'Enable OAuth2 Implicit Grant'; 1.45 -COMMENT ON COLUMN "registered_client"."client_grant" IS 'Enable OAuth2 Client Credentials Grant'; 1.46 -COMMENT ON COLUMN "registered_client"."access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant'; 1.47 -COMMENT ON COLUMN "registered_client"."client_grant_access_level" IS 'Maximum access level for OAuth2 Client Credentials Grant'; 1.48 -COMMENT ON COLUMN "registered_client"."single_token" IS 'Allow only one valid refresh token'; 1.49 -COMMENT ON COLUMN "registered_client"."always_authorized" IS 'Members do not need to authorize the client'; 1.50 -COMMENT ON COLUMN "registered_client"."auth_duration" IS 'Duration of authorization by member'; 1.51 -COMMENT ON COLUMN "registered_client"."login_duration" IS 'Life time of refresh code chain'; 1.52 -COMMENT ON COLUMN "registered_client"."refresh_duration" IS 'Life time of a refresh code'; 1.53 -COMMENT ON COLUMN "registered_client"."access_duration" IS 'Life time of an access code'; 1.54 - 1.55 - 1.56 -CREATE TABLE "authorized_client" ( 1.57 - "id" SERIAL8 PRIMARY KEY, 1.58 - UNIQUE ("client_identifier", "member_id"), 1.59 - "client_identifier" TEXT NOT NULL, 1.60 - "member_id" INT4 NOT NULL REFERENCES "member" ("id") 1.61 - ON DELETE CASCADE ON UPDATE CASCADE, 1.62 - "access_level" "api_access_level" NOT NULL, 1.63 - "first_auth" TIMESTAMPTZ NOT NULL, 1.64 - "last_auth" TIMESTAMPTZ NOT NULL, 1.65 - UNIQUE ("client_identifier", "member_id") ); 1.66 - 1.67 -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'; 1.68 - 1.69 -COMMENT ON COLUMN "authorized_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint'; 1.70 -COMMENT ON COLUMN "authorized_client"."member_id" IS 'Member who authorized the client'; 1.71 -COMMENT ON COLUMN "authorized_client"."access_level" IS 'Authorized access level'; 1.72 -COMMENT ON COLUMN "authorized_client"."first_auth" IS 'Date/time of initial authorization'; 1.73 -COMMENT ON COLUMN "authorized_client"."last_auth" IS 'Date/time of last authorization refresh'; 1.74 - 1.75 - 1.76 -CREATE TABLE "authorized_client_token" ( 1.77 - "id" SERIAL8 PRIMARY KEY, 1.78 - "authorized_client_id" INT8 NOT NULL REFERENCES "authorized_client" 1.79 - ON DELETE CASCADE ON UPDATE CASCADE, 1.80 - "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.81 - "authorization_code" TEXT, 1.82 - "refreshed" TIMESTAMPTZ, 1.83 - "refresh_token" TEXT, 1.84 - "old_refresh_token" TEXT, 1.85 - CONSTRAINT "one_of_authorization_code_and_refresh_token_set" 1.86 - CHECK ("authorization_code" NOTNULL OR "refresh_token" NOTNULL), 1.87 - CONSTRAINT "refresh_token_if_and_only_if_refreshed" 1.88 - CHECK ("refreshed" NOTNULL = "refresh_token" NOTNULL), 1.89 - CONSTRAINT "old_refresh_token_requires_current_refresh_token" 1.90 - CHECK ("refresh_token" NOTNULL OR "old_refresh_token" ISNULL) ); 1.91 - 1.92 -COMMENT ON TABLE "authorized_client_token" IS 'PRELIMINARY, SUBJECT TO CHANGE! Issued OAuth2 authorization codes and refresh tokens'; 1.93 - 1.94 -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'; 1.95 -COMMENT ON COLUMN "authorized_client_token"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)'; 1.96 -COMMENT ON COLUMN "authorized_client_token"."refreshed" IS 'Date/time of last refresh'; 1.97 -COMMENT ON COLUMN "authorized_client_token"."refresh_token" IS 'OAuth2 refresh token'; 1.98 - 1.99 --- END OF NEW PRELIMINARY API TABLES -- 1.100 - 1.101 - 1.102 CREATE TABLE "member_history" ( 1.103 "id" SERIAL8 PRIMARY KEY, 1.104 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,