liquid_feedback_core

changeset 266:d1d5dca15491

API system clients and redundancy in API access table
author jbe
date Wed Aug 08 14:10:14 2012 +0200 (2012-08-08)
parents e3c9f737a096
children 5bf2c973ec3c
files core.sql
line diff
     1.1 --- a/core.sql	Wed Aug 08 05:35:49 2012 +0200
     1.2 +++ b/core.sql	Wed Aug 08 14:10:14 2012 +0200
     1.3 @@ -170,6 +170,31 @@
     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 "api_access_level" AS ENUM (
     1.8 +  'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' );
     1.9 +
    1.10 +
    1.11 +CREATE TABLE "system_api_client" (
    1.12 +        "id"                    INT4            PRIMARY KEY,
    1.13 +        "name"                  TEXT            NOT NULL UNIQUE,
    1.14 +        "client_identifier"     TEXT            NOT NULL UNIQUE,
    1.15 +        "client_secret"         TEXT,
    1.16 +        "redirection_endpoint"  TEXT,
    1.17 +        "access_level"          "api_access_level" NOT NULL,
    1.18 +        "multi_access"          BOOLEAN         NOT NULL,
    1.19 +        "validity_period"       INTERVAL        NOT NULL,
    1.20 +        "last_usage"            TIMESTAMPTZ     NOT NULL );
    1.21 +
    1.22 +COMMENT ON TABLE "system_api_client" IS 'Registered OAuth2 clients by the system administrator';
    1.23 +
    1.24 +COMMENT ON COLUMN "system_api_client"."name"                 IS 'Name of the client as chosen by member';
    1.25 +COMMENT ON COLUMN "system_api_client"."client_identifier"    IS 'OAuth2 client id';
    1.26 +COMMENT ON COLUMN "system_api_client"."client_secret"        IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set';
    1.27 +COMMENT ON COLUMN "system_api_client"."redirection_endpoint" IS 'OAuth2 redirection endpoint, enables OAuth2 Authorization Code Grant and Implicit Grant when set';
    1.28 +COMMENT ON COLUMN "system_api_client"."multi_access"         IS 'TRUE = Allow to issue more than one access token simultanously';
    1.29 +COMMENT ON COLUMN "system_api_client"."validity_period"      IS 'Life time of an OAuth2 access token';
    1.30 +
    1.31 +
    1.32  CREATE TABLE "member_api_client" (
    1.33          "id"                    SERIAL8         PRIMARY KEY,
    1.34          UNIQUE ("member_id", "name"),
    1.35 @@ -179,6 +204,7 @@
    1.36          "name"                  TEXT            NOT NULL,
    1.37          "client_identifier"     TEXT            NOT NULL,
    1.38          "redirection_endpoint"  TEXT            NOT NULL,
    1.39 +        "access_level"          "api_access_level" NOT NULL,
    1.40          "multi_access"          BOOLEAN         NOT NULL,
    1.41          "validity_period"       INTERVAL        NOT NULL,
    1.42          "last_usage"            TIMESTAMPTZ     NOT NULL );
    1.43 @@ -192,21 +218,28 @@
    1.44  COMMENT ON COLUMN "member_api_client"."validity_period"      IS 'Life time of an OAuth2 access token';
    1.45  
    1.46  
    1.47 -CREATE TABLE "member_api_access" (
    1.48 +CREATE TABLE "api_access" (
    1.49          "id"                    SERIAL8         PRIMARY KEY,
    1.50 -        "member_api_client_id"  INT8            NOT NULL REFERENCES "member_api_client" ("id")
    1.51 +        "client_identifier"     TEXT            NOT NULL,
    1.52 +        "redirection_endpoint"  TEXT,
    1.53 +        "access_level"          "api_access_level" NOT NULL,
    1.54 +        "member_id"             INT4            REFERENCES "member" ("id")
    1.55                                                  ON DELETE CASCADE ON UPDATE CASCADE,
    1.56          "expiry"                TIMESTAMPTZ     NOT NULL,
    1.57          "authorization_code"    TEXT,
    1.58          "access_token"          TEXT,
    1.59 +        CONSTRAINT "authorization_code_requires_redirection_endpoint"
    1.60 +          CHECK ("authorization_code" ISNULL OR "redirection_endpoint" NOTNULL),
    1.61          CONSTRAINT "one_of_authorization_code_and_access_token_set"
    1.62            CHECK ("authorization_code" NOTNULL OR "access_token" NOTNULL) );
    1.63  
    1.64 -COMMENT ON TABLE "member_api_access" IS 'Issued OAuth2 authorization codes and access tokens';
    1.65 -
    1.66 -COMMENT ON COLUMN "member_api_access"."expiry"             IS 'Expiry of access token, or expiry of authorization code when access token is NULL';
    1.67 -COMMENT ON COLUMN "member_api_access"."authorization_code" IS 'OAuth2 authorization code';
    1.68 -COMMENT ON COLUMN "member_api_access"."access_token"       IS 'OAuth2 access token';
    1.69 +COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and access tokens';
    1.70 +
    1.71 +COMMENT ON COLUMN "api_access"."client_identifier"    IS 'OAuth2 client id';
    1.72 +COMMENT ON COLUMN "api_access"."redirection_endpoint" IS 'OAuth2 redirection endpoint';
    1.73 +COMMENT ON COLUMN "api_access"."expiry"               IS 'Expiry of access token, or expiry of authorization code when access token is NULL';
    1.74 +COMMENT ON COLUMN "api_access"."authorization_code"   IS 'OAuth2 authorization code';
    1.75 +COMMENT ON COLUMN "api_access"."access_token"         IS 'OAuth2 access token';
    1.76  
    1.77  
    1.78  CREATE TABLE "member_history" (

Impressum / About Us