-- Change oauth2db to the name you want for the database

CREATE DATABASE oauth2db USING CODESET UTF8 TERRITORY US;
CONNECT TO oauth2db;

CREATE BUFFERPOOL BUFF16K IMMEDIATE SIZE 2500 AUTOMATIC PAGESIZE 16K;
create tablespace TAB16K pagesize 16K bufferpool BUFF16K dropped table recovery on;
CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS16K PAGESIZE 16K BUFFERPOOL BUFF16K;

---- CREATE TABLES ----
CREATE TABLE OAuthDBSchema.OAUTH20CACHE 
(
  LOOKUPKEY VARCHAR(256) NOT NULL, 
  UNIQUEID VARCHAR(128) NOT NULL, 
  COMPONENTID VARCHAR(256) NOT NULL, 
  TYPE VARCHAR(64) NOT NULL, 
  SUBTYPE VARCHAR(64), 
  CREATEDAT BIGINT, 
  LIFETIME INT, 
  EXPIRES BIGINT, 
  TOKENSTRING VARCHAR(2048) NOT NULL, 
  CLIENTID VARCHAR(64) NOT NULL, 
  USERNAME VARCHAR(64) NOT NULL, 
  SCOPE VARCHAR(512) NOT NULL, 
  REDIRECTURI VARCHAR(2048), 
  STATEID VARCHAR(64) NOT NULL,
  EXTENDEDFIELDS CLOB NOT NULL DEFAULT '{}'
);

CREATE TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG 
(
  COMPONENTID VARCHAR(256) NOT NULL, 
  CLIENTID VARCHAR(256) NOT NULL, 
  CLIENTSECRET VARCHAR(256), 
  DISPLAYNAME VARCHAR(256) NOT NULL, 
  REDIRECTURI VARCHAR(2048), 
  ENABLED INT,
  CLIENTMETADATA CLOB NOT NULL DEFAULT '{}'
);

CREATE TABLE OAuthDBSchema.OAUTH20CONSENTCACHE (
  CLIENTID VARCHAR(256) NOT NULL,
  USERID VARCHAR(256),
  PROVIDERID VARCHAR(256) NOT NULL,
  SCOPE VARCHAR(1024) NOT NULL,
  EXPIRES BIGINT,
  EXTENDEDFIELDS CLOB NOT NULL DEFAULT '{}'
);

---- ADD CONSTRAINTS ----
ALTER TABLE OAuthDBSchema.OAUTH20CACHE 
  ADD CONSTRAINT PK_LOOKUPKEY PRIMARY KEY (LOOKUPKEY);

ALTER TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG 
  ADD CONSTRAINT PK_COMPIDCLIENTID PRIMARY KEY (COMPONENTID,CLIENTID);

---- CREATE INDEXES ----
CREATE INDEX OAUTH20CACHE_EXPIRES ON OAUTHDBSCHEMA.OAUTH20CACHE (EXPIRES ASC);

---- GRANT PRIVILEGES ----
---- UNCOMMENT THE FOLLOWING IF YOU USE AN ACCOUNT OTHER THAN ADMINISTRATOR FOR DB ACCESS ----

-- Change dbuser to the account you want to use to access your database 
-- GRANT ALL ON OAuthDBSchema.OAUTH20CACHE TO USER dbuser;
-- GRANT ALL ON OAuthDBSchema.OAUTH20CLIENTCONFIG TO USER dbuser;

---- END OF GRANT PRIVILIGES ----

DISCONNECT CURRENT;
