db.migration.V0.1__init.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of formkiq-server Show documentation
Server-side integration for the FormKiQ ios application
There is a newer version: 0.6.1
DROP EXTENSION IF EXISTS lo;

CREATE EXTENSION lo;

---
--- users
---
CREATE TABLE public.users  ( 
	user_id 			varchar(255) PRIMARY KEY,
    client_id 			varchar(255) NOT NULL,
    email       		varchar(255) NOT NULL,
    password    		varchar(255) NULL,
    status      		varchar(10) NOT NULL,
    role	      		varchar(10) NOT NULL,
    inserted_date		timestamp NOT NULL,
    updated_date		timestamp NOT NULL,
    reset_token			varchar(255) NULL,
    reset_inserted_date	timestamp NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE UNIQUE INDEX email_idx ON public.users (email);

---
--- user_shares
---
CREATE TABLE public.user_shares  ( 
    user_share_id	varchar(255) PRIMARY KEY,
    user_id			varchar(255) NOT NULL,
    form_log_id		varchar(255) NOT NULL,
    share_type		varchar(10) NOT NULL,
    inserted_date	timestamp NOT NULL,
    updated_date	timestamp NOT NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE INDEX user_shares_form_log_id_idx ON public.user_shares (form_log_id);

CREATE INDEX user_shares_user_id_idx ON public.user_shares (user_id);

---
--- forms
---
CREATE TABLE public.form_logs  ( 
    form_log_id		varchar(255) PRIMARY KEY,
    form_id			varchar(255) NOT NULL,
    sha1_hash		varchar(255) NOT NULL,
	asset_id		varchar(255) NOT NULL,
    inserted_date	timestamp NOT NULL,
    updated_date	timestamp NOT NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE INDEX form_logs_formid_idx ON public.form_logs (form_id);
    
---
--- form_record_logs
---
CREATE TABLE public.form_record_logs  ( 
    form_record_log_id	varchar(255) PRIMARY KEY,
    form_record_id		varchar(255) NOT NULL,
    form_id				varchar(255) NOT NULL,
    sha1_hash			varchar(255) NOT NULL,
    asset_id			varchar(255) NOT NULL,
    inserted_date		timestamp NOT NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE INDEX form_record_logs_record_idx ON public.form_record_logs (form_record_id);

CREATE INDEX form_record_logs_idx ON public.form_record_logs (form_id, inserted_date);
    
CREATE TABLE public.assets  ( 
    asset_id		varchar(255) PRIMARY KEY,
    data 			lo NOT NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE TRIGGER assets_data_lo BEFORE UPDATE OR DELETE ON assets
    FOR EACH ROW EXECUTE PROCEDURE lo_manage(data);
    
---
--- oauth
---
create table public.oauth_access_token (
	token_id VARCHAR(255),
	token bytea,
	authentication_id VARCHAR(255),
	user_name VARCHAR(255),
	client_id VARCHAR(255),
	authentication bytea,
	refresh_token VARCHAR(255)
);

create table oauth_refresh_token (
	token_id VARCHAR(255),
	token bytea,
	authentication bytea
);

create table oauth_client_details (
	client_id VARCHAR(255) PRIMARY KEY,
	resource_ids VARCHAR(255),
	client_secret VARCHAR(255),
	scope VARCHAR(255),
	authorized_grant_types VARCHAR(255),
	web_server_redirect_uri VARCHAR(255),
	authorities VARCHAR(255),
	access_token_validity INTEGER,
	refresh_token_validity INTEGER,
	additional_information VARCHAR(4096),
	autoapprove VARCHAR(255)
);