# HG changeset patch
# User jbe
# Date 1344427814 -7200
# Node ID d1d5dca15491243d7a2912f95801fbe8c8ab0d34
# Parent  e3c9f737a09622c7fa2d4274fffbaf83b5adce6b
API system clients and redundancy in API access table

diff -r e3c9f737a096 -r d1d5dca15491 core.sql
--- a/core.sql	Wed Aug 08 05:35:49 2012 +0200
+++ b/core.sql	Wed Aug 08 14:10:14 2012 +0200
@@ -170,6 +170,31 @@
 COMMENT ON COLUMN "member"."statement"            IS 'Freely chosen text of the member for his/her profile';
 
 
+CREATE TYPE "api_access_level" AS ENUM (
+  'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' );
+
+
+CREATE TABLE "system_api_client" (
+        "id"                    INT4            PRIMARY KEY,
+        "name"                  TEXT            NOT NULL UNIQUE,
+        "client_identifier"     TEXT            NOT NULL UNIQUE,
+        "client_secret"         TEXT,
+        "redirection_endpoint"  TEXT,
+        "access_level"          "api_access_level" NOT NULL,
+        "multi_access"          BOOLEAN         NOT NULL,
+        "validity_period"       INTERVAL        NOT NULL,
+        "last_usage"            TIMESTAMPTZ     NOT NULL );
+
+COMMENT ON TABLE "system_api_client" IS 'Registered OAuth2 clients by the system administrator';
+
+COMMENT ON COLUMN "system_api_client"."name"                 IS 'Name of the client as chosen by member';
+COMMENT ON COLUMN "system_api_client"."client_identifier"    IS 'OAuth2 client id';
+COMMENT ON COLUMN "system_api_client"."client_secret"        IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set';
+COMMENT ON COLUMN "system_api_client"."redirection_endpoint" IS 'OAuth2 redirection endpoint, enables OAuth2 Authorization Code Grant and Implicit Grant when set';
+COMMENT ON COLUMN "system_api_client"."multi_access"         IS 'TRUE = Allow to issue more than one access token simultanously';
+COMMENT ON COLUMN "system_api_client"."validity_period"      IS 'Life time of an OAuth2 access token';
+
+
 CREATE TABLE "member_api_client" (
         "id"                    SERIAL8         PRIMARY KEY,
         UNIQUE ("member_id", "name"),
@@ -179,6 +204,7 @@
         "name"                  TEXT            NOT NULL,
         "client_identifier"     TEXT            NOT NULL,
         "redirection_endpoint"  TEXT            NOT NULL,
+        "access_level"          "api_access_level" NOT NULL,
         "multi_access"          BOOLEAN         NOT NULL,
         "validity_period"       INTERVAL        NOT NULL,
         "last_usage"            TIMESTAMPTZ     NOT NULL );
@@ -192,21 +218,28 @@
 COMMENT ON COLUMN "member_api_client"."validity_period"      IS 'Life time of an OAuth2 access token';
 
 
-CREATE TABLE "member_api_access" (
+CREATE TABLE "api_access" (
         "id"                    SERIAL8         PRIMARY KEY,
-        "member_api_client_id"  INT8            NOT NULL REFERENCES "member_api_client" ("id")
+        "client_identifier"     TEXT            NOT NULL,
+        "redirection_endpoint"  TEXT,
+        "access_level"          "api_access_level" NOT NULL,
+        "member_id"             INT4            REFERENCES "member" ("id")
                                                 ON DELETE CASCADE ON UPDATE CASCADE,
         "expiry"                TIMESTAMPTZ     NOT NULL,
         "authorization_code"    TEXT,
         "access_token"          TEXT,
+        CONSTRAINT "authorization_code_requires_redirection_endpoint"
+          CHECK ("authorization_code" ISNULL OR "redirection_endpoint" NOTNULL),
         CONSTRAINT "one_of_authorization_code_and_access_token_set"
           CHECK ("authorization_code" NOTNULL OR "access_token" NOTNULL) );
 
-COMMENT ON TABLE "member_api_access" IS 'Issued OAuth2 authorization codes and access tokens';
-
-COMMENT ON COLUMN "member_api_access"."expiry"             IS 'Expiry of access token, or expiry of authorization code when access token is NULL';
-COMMENT ON COLUMN "member_api_access"."authorization_code" IS 'OAuth2 authorization code';
-COMMENT ON COLUMN "member_api_access"."access_token"       IS 'OAuth2 access token';
+COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and access tokens';
+
+COMMENT ON COLUMN "api_access"."client_identifier"    IS 'OAuth2 client id';
+COMMENT ON COLUMN "api_access"."redirection_endpoint" IS 'OAuth2 redirection endpoint';
+COMMENT ON COLUMN "api_access"."expiry"               IS 'Expiry of access token, or expiry of authorization code when access token is NULL';
+COMMENT ON COLUMN "api_access"."authorization_code"   IS 'OAuth2 authorization code';
+COMMENT ON COLUMN "api_access"."access_token"         IS 'OAuth2 access token';
 
 
 CREATE TABLE "member_history" (