1.1 --- a/core.sql	Wed Aug 08 16:57:52 2012 +0200
     1.2 +++ b/core.sql	Wed Aug 08 18:48:13 2012 +0200
     1.3 @@ -176,7 +176,7 @@
     1.4  
     1.5  CREATE TABLE "api_client" (
     1.6          "id"                    SERIAL8         PRIMARY KEY,
     1.7 -        "name"                  TEXT            NOT NULL,
     1.8 +        "name"                  TEXT,
     1.9          UNIQUE ("member_id", "client_identifier"),
    1.10          "member_id"             INT4            REFERENCES "member" ("id")
    1.11                                                  ON DELETE CASCADE ON UPDATE CASCADE,
    1.12 @@ -185,9 +185,10 @@
    1.13          "member_authorization"  BOOLEAN         NOT NULL,
    1.14          "public_access_level"   "api_access_level",
    1.15          "access_level"          "api_access_level" NOT NULL,
    1.16 -        "parallel_access"       BOOLEAN         NOT NULL,
    1.17          "validity_period"       INTERVAL        NOT NULL,
    1.18          "last_usage"            TIMESTAMPTZ     NOT NULL,
    1.19 +        CONSTRAINT "system_clients_require_name"
    1.20 +          CHECK ("name" NOTNULL OR "member_id" ISNULL),
    1.21          CONSTRAINT "public_access_level_set_if_and_only_if_system_client"
    1.22            CHECK ("member_id" ISNULL = "public_access_level" NOTNULL) );
    1.23  CREATE UNIQUE INDEX "api_client_non_member_client_identifier_idx"
    1.24 @@ -196,13 +197,12 @@
    1.25  COMMENT ON TABLE "api_client" IS 'Registered OAuth2 client for a member';
    1.26  
    1.27  COMMENT ON COLUMN "api_client"."member_id"            IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator';
    1.28 -COMMENT ON COLUMN "api_client"."name"                 IS 'Name of the client as chosen by member or administrator';
    1.29 +COMMENT ON COLUMN "api_client"."name"                 IS 'Name of the client as chosen by member or administrator, NULL means unnamed';
    1.30  COMMENT ON COLUMN "api_client"."client_identifier"    IS 'OAuth2 client id, also used as redirection endpoint if "member_authorization" is set to TRUE';
    1.31  COMMENT ON COLUMN "api_client"."client_secret"        IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set';
    1.32  COMMENT ON COLUMN "api_client"."member_authorization" IS 'Allow OAuth2 Authorization Code Grant and Implicit Grant, in which case the "client_identifier" is used as the redirection endpoint';
    1.33  COMMENT ON COLUMN "api_client"."public_access_level"  IS 'Access level for OAuth2 Client Credentials Grant';
    1.34  COMMENT ON COLUMN "api_client"."access_level"         IS 'Access level for OAuth2 Authorization Code Grant and Implicit Grant';
    1.35 -COMMENT ON COLUMN "api_client"."parallel_access"      IS 'Multiple entries in "api_access" table allowed';
    1.36  COMMENT ON COLUMN "api_client"."validity_period"      IS 'Period after which an entry in the "api_access" table expires';
    1.37  
    1.38  
    1.39 @@ -212,6 +212,8 @@
    1.40                                                  ON DELETE CASCADE ON UPDATE CASCADE,
    1.41          "member_id"             INT4            REFERENCES "member" ("id")
    1.42                                                  ON DELETE CASCADE ON UPDATE CASCADE,
    1.43 +        "access_level"          "api_access_level" NOT NULL,
    1.44 +        "validity_period"       INTERVAL        NOT NULL,
    1.45          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.46          "authorization_code"    TEXT,
    1.47          "refreshed"             TIMESTAMPTZ,
    1.48 @@ -220,12 +222,15 @@
    1.49          CONSTRAINT "one_of_authorization_code_and_refresh_token_set"
    1.50            CHECK ("authorization_code" NOTNULL OR "refresh_token" NOTNULL),
    1.51          CONSTRAINT "refresh_token_if_and_only_if_refreshed"
    1.52 -          CHECK ("refreshed" NOTNULL = "refresh_token" NOTNULL) );
    1.53 +          CHECK ("refreshed" NOTNULL = "refresh_token" NOTNULL),
    1.54 +        CONSTRAINT "old_refresh_token_requires_current_refresh_token"
    1.55 +          CHECK ("refresh_token" NOTNULL OR "old_refresh_token" ISNULL) );
    1.56  
    1.57  COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and refresh tokens';
    1.58  
    1.59 +COMMENT ON COLUMN "api_client"."validity_period"      IS 'Period after which an entry in the "api_access" table expires';
    1.60  COMMENT ON COLUMN "api_access"."created"              IS 'Date/time when authorization code (or first refresh token when there is no authorization code) has been created';
    1.61 -COMMENT ON COLUMN "api_access"."authorization_code"   IS 'OAuth2 authorization code';
    1.62 +COMMENT ON COLUMN "api_access"."authorization_code"   IS 'OAuth2 authorization code (only valid for a very short time after it has been created)';
    1.63  COMMENT ON COLUMN "api_access"."refreshed"            IS 'Date/time of last refresh';
    1.64  COMMENT ON COLUMN "api_access"."refresh_token"        IS 'OAuth2 refresh token';
    1.65