db.migration.V0.2__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

-- update schema_version set script='v0.1__init.sql', version='0.1'

---
--- user clients
---
CREATE TABLE public.user_clients  ( 
	id 			varchar(255) PRIMARY KEY,
	user_id 	varchar(255) NOT NULL,
    client_id 	varchar(255) NOT NULL,
    inserted_date		timestamp NOT NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE INDEX user_clients_user_id_idx ON public.user_clients (user_id);

insert into user_clients(id, user_id, client_id, inserted_date) select user_id, user_id, client_id, inserted_date from users;

ALTER TABLE users DROP COLUMN client_id;

drop table form_logs;

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

CREATE INDEX form_logs_idx ON public.form_logs (client_id);

---
--- forms
---
CREATE TABLE public.client_forms  ( 
    client_form_id	varchar(255) PRIMARY KEY,
    client_id		varchar(255) NOT NULL,
    parent_uuid		varchar(255) NULL,
    form_id			varchar(255) NOT NULL,
    sha1_hash		varchar(255) NOT NULL,
	asset_id		varchar(255) NOT NULL,
	data			JSONB NOT NULL,
    inserted_date	timestamp NOT NULL,
    updated_date	timestamp NOT NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE INDEX forms_formid_idx ON public.client_forms (form_id);

drop table user_shares;

drop table form_record_logs;

---
--- workflows
---
CREATE TABLE public.client_workflows  ( 
    client_workflow_id	varchar(255) PRIMARY KEY,
    client_id			varchar(255) NOT NULL,
    parent_uuid			varchar(255) NULL,
    uuid				varchar(255) NOT NULL,
    sha1_hash			varchar(255) NOT NULL,
    asset_id			varchar(255) NOT NULL,
	data				JSONB NOT NULL,
    inserted_date		timestamp NOT NULL,
    updated_date		timestamp NOT NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE INDEX workflows_workflowid_idx ON public.client_workflows (uuid);

---
--- workflow logs
---
CREATE TABLE public.workflow_logs  ( 
    workflow_log_id		SERIAL PRIMARY KEY,
    client_id			varchar(255) NOT NULL,
    uuid				varchar(255) NOT NULL,
    inserted_date		timestamp NOT NULL
)
WITHOUT OIDS 
TABLESPACE pg_default;

CREATE INDEX workflow_logs_idx ON public.workflow_logs (client_id);