New database layout.

1 files changed, 66 insertions, 50 deletions

diff --git a/server/pracro.psql b/server/pracro.psql
index 8211397..5a34e9d 100644
--- a/server/pracro.psql
+++ b/server/pracro.psql
@@ -2,16 +2,47 @@
 -- PostgreSQL database dump
 --
 
+SET statement_timeout = 0;
 SET client_encoding = 'SQL_ASCII';
-SET standard_conforming_strings = off;
+SET standard_conforming_strings = on;
 SET check_function_bodies = false;
 SET client_min_messages = warning;
-SET escape_string_warning = off;
 
 SET search_path = public, pg_catalog;
 
+--
+-- Name: session_status; Type: TYPE; Schema: public; Owner: pracro
+--
+
+CREATE TYPE session_status AS ENUM (
+    'active',
+    'idle',
+    'committed'
+);
+
+
+ALTER TYPE public.session_status OWNER TO pracro;
+
 SET default_tablespace = '';
 
+SET default_with_oids = false;
+
+--
+-- Name: commits; Type: TABLE; Schema: public; Owner: pracro; Tablespace: 
+--
+
+CREATE TABLE commits (
+    patientid text,
+    template text,
+    version text,
+    "timestamp" bigint,
+    uid bigint NOT NULL,
+    status session_status DEFAULT 'committed'::session_status
+);
+
+
+ALTER TABLE public.commits OWNER TO pracro;
+
 SET default_with_oids = true;
 
 --
@@ -21,7 +52,9 @@ SET default_with_oids = true;
 CREATE TABLE fieldnames (
     name text,
     description text,
-    "timestamp" bigint
+    "timestamp" bigint,
+    "extract" boolean DEFAULT false,
+    caption text
 );
 
 
@@ -41,34 +74,37 @@ CREATE TABLE fields (
 ALTER TABLE public.fields OWNER TO pracro;
 
 --
--- Name: commits; Type: TABLE; Schema: public; Owner: pracro; Tablespace: 
+-- Name: sessionseq; Type: SEQUENCE; Schema: public; Owner: pracro
 --
 
-CREATE TABLE commits (
-    patientid text,
-    sessionid text,
-    template text,
-    version text,
-    "timestamp" bigint,
-    uid bigint
-);
+CREATE SEQUENCE sessionseq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
 
 
-ALTER TABLE public.commits OWNER TO pracro;
+ALTER TABLE public.sessionseq OWNER TO pracro;
+
+--
+-- Name: sessionseq; Type: SEQUENCE SET; Schema: public; Owner: pracro
+--
+
+SELECT pg_catalog.setval('sessionseq', 433, true);
+
 
 --
 -- Name: transactions; Type: TABLE; Schema: public; Owner: pracro; Tablespace: 
 --
 
 CREATE TABLE transactions (
---    patientid text,
---    template text,
-    cid bigint,
     macro text,
     version text,
     "timestamp" bigint,
     "user" text,
-    uid bigint
+    uid bigint NOT NULL,
+    cid bigint
 );
 
 
@@ -89,48 +125,26 @@ CREATE SEQUENCE trseq
 ALTER TABLE public.trseq OWNER TO pracro;
 
 --
--- Name: sessionseq; Type: SEQUENCE; Schema: public; Owner: pracro
---
-
-CREATE SEQUENCE sessionseq
-    START WITH 1
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-
-ALTER TABLE public.sessionseq OWNER TO pracro;
-
---
 -- Name: trseq; Type: SEQUENCE SET; Schema: public; Owner: pracro
 --
 
-SELECT pg_catalog.setval('trseq', 1, false);
-
-
---
--- Data for Name: fieldnames; Type: TABLE DATA; Schema: public; Owner: pracro
---
-
-COPY fieldnames (name, description, "timestamp") FROM stdin;
-\.
+SELECT pg_catalog.setval('trseq', 2490, true);
 
 
 --
--- Data for Name: fields; Type: TABLE DATA; Schema: public; Owner: pracro
+-- Name: commits_pkey; Type: CONSTRAINT; Schema: public; Owner: pracro; Tablespace: 
 --
 
-COPY fields (transaction, name, value) FROM stdin;
-\.
+ALTER TABLE ONLY commits
+    ADD CONSTRAINT commits_pkey PRIMARY KEY (uid);
 
 
 --
--- Data for Name: transactions; Type: TABLE DATA; Schema: public; Owner: pracro
+-- Name: transactions_pkey; Type: CONSTRAINT; Schema: public; Owner: pracro; Tablespace: 
 --
 
-COPY transactions (patientid, macro, version, "timestamp", "user", uid) FROM stdin;
-\.
+ALTER TABLE ONLY transactions
+    ADD CONSTRAINT transactions_pkey PRIMARY KEY (uid);
 
 
 --
@@ -148,17 +162,19 @@ CREATE INDEX x_fields_name_tid ON fields USING btree (name, transaction);
 
 
 --
--- Name: x_transaction_patientid_macro_ts; Type: INDEX; Schema: public; Owner: pracro; Tablespace: 
+-- Name: cid; Type: FK CONSTRAINT; Schema: public; Owner: pracro
 --
 
-CREATE INDEX x_transaction_patientid_macro_ts ON transactions USING btree (patientid, macro, "timestamp");
+ALTER TABLE ONLY transactions
+    ADD CONSTRAINT cid FOREIGN KEY (cid) REFERENCES commits(uid) MATCH FULL ON DELETE CASCADE;
 
 
 --
--- Name: x_transaction_patientid_ts; Type: INDEX; Schema: public; Owner: pracro; Tablespace: 
+-- Name: transaction; Type: FK CONSTRAINT; Schema: public; Owner: pracro
 --
 
-CREATE INDEX x_transaction_patientid_ts ON transactions USING btree (patientid, "timestamp");
+ALTER TABLE ONLY fields
+    ADD CONSTRAINT transaction FOREIGN KEY (transaction) REFERENCES transactions(uid) MATCH FULL ON DELETE CASCADE;
 
 
 --