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,