1.1 --- a/core.sql	Tue Aug 07 12:25:02 2012 +0200
     1.2 +++ b/core.sql	Wed Aug 08 05:35:49 2012 +0200
     1.3 @@ -170,24 +170,43 @@
     1.4  COMMENT ON COLUMN "member"."statement"            IS 'Freely chosen text of the member for his/her profile';
     1.5  
     1.6  
     1.7 -CREATE TYPE "application_access_level" AS ENUM
     1.8 -  ('member', 'full', 'pseudonymous', 'anonymous');
     1.9 -
    1.10 -COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API';
    1.11 -
    1.12 -
    1.13 -CREATE TABLE "member_application" (
    1.14 +CREATE TABLE "member_api_client" (
    1.15          "id"                    SERIAL8         PRIMARY KEY,
    1.16          UNIQUE ("member_id", "name"),
    1.17 +        UNIQUE ("member_id", "client_identifier"),
    1.18          "member_id"             INT4            NOT NULL REFERENCES "member" ("id")
    1.19                                                  ON DELETE CASCADE ON UPDATE CASCADE,
    1.20          "name"                  TEXT            NOT NULL,
    1.21 -        "comment"               TEXT,
    1.22 -        "access_level" "application_access_level" NOT NULL,
    1.23 -        "key"                   TEXT            NOT NULL UNIQUE,
    1.24 -        "last_usage"            TIMESTAMPTZ );
    1.25 -
    1.26 -COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API';
    1.27 +        "client_identifier"     TEXT            NOT NULL,
    1.28 +        "redirection_endpoint"  TEXT            NOT NULL,
    1.29 +        "multi_access"          BOOLEAN         NOT NULL,
    1.30 +        "validity_period"       INTERVAL        NOT NULL,
    1.31 +        "last_usage"            TIMESTAMPTZ     NOT NULL );
    1.32 +
    1.33 +COMMENT ON TABLE "member_api_client" IS 'Registered OAuth2 client for a member';
    1.34 +
    1.35 +COMMENT ON COLUMN "member_api_client"."name"                 IS 'Name of the client as chosen by member';
    1.36 +COMMENT ON COLUMN "member_api_client"."client_identifier"    IS 'OAuth2 client id';
    1.37 +COMMENT ON COLUMN "member_api_client"."redirection_endpoint" IS 'OAuth2 redirection endpoint';
    1.38 +COMMENT ON COLUMN "member_api_client"."multi_access"         IS 'TRUE = Allow to issue more than one access token simultanously';
    1.39 +COMMENT ON COLUMN "member_api_client"."validity_period"      IS 'Life time of an OAuth2 access token';
    1.40 +
    1.41 +
    1.42 +CREATE TABLE "member_api_access" (
    1.43 +        "id"                    SERIAL8         PRIMARY KEY,
    1.44 +        "member_api_client_id"  INT8            NOT NULL REFERENCES "member_api_client" ("id")
    1.45 +                                                ON DELETE CASCADE ON UPDATE CASCADE,
    1.46 +        "expiry"                TIMESTAMPTZ     NOT NULL,
    1.47 +        "authorization_code"    TEXT,
    1.48 +        "access_token"          TEXT,
    1.49 +        CONSTRAINT "one_of_authorization_code_and_access_token_set"
    1.50 +          CHECK ("authorization_code" NOTNULL OR "access_token" NOTNULL) );
    1.51 +
    1.52 +COMMENT ON TABLE "member_api_access" IS 'Issued OAuth2 authorization codes and access tokens';
    1.53 +
    1.54 +COMMENT ON COLUMN "member_api_access"."expiry"             IS 'Expiry of access token, or expiry of authorization code when access token is NULL';
    1.55 +COMMENT ON COLUMN "member_api_access"."authorization_code" IS 'OAuth2 authorization code';
    1.56 +COMMENT ON COLUMN "member_api_access"."access_token"       IS 'OAuth2 access token';
    1.57  
    1.58  
    1.59  CREATE TABLE "member_history" (