Cleanup database access a bit and add a proper database schema

1 files changed, 60 insertions, 0 deletions

diff --git a/server/pracro.psql b/server/pracro.psql
new file mode 100644
index 0000000..34283d0
--- /dev/null
+++ b/server/pracro.psql
@@ -0,0 +1,60 @@
+-- vim: syn=sql
+-- As root:
+-- #createuser -P -h localhost -U postgres
+-- #createdb -U postgres -h localhost pracro
+
+-- DROP DATABASE IF EXISTS pracro;
+-- CREATE DATABASE pracro WITH OWNER = pracro ENCODING = 'UNICODE' TABLESPACE = pg_default;
+
+-- DROP SEQUENCE IF EXISTS transeq;
+-- CREATE SEQUENCE transeq;
+
+DROP TABLE IF EXISTS transactions;
+CREATE TABLE transactions
+(
+  -- "uid"       bigint PRIMARY KEY,
+  "cpr"       varchar(11),
+  "macro"     text,
+  "version"   text,
+  "timestamp" bigint,
+  "user"      text
+) WITH OIDS;
+ALTER TABLE transactions OWNER TO pracro;
+CREATE INDEX x_transactions_cpr_timestamp ON transactions("cpr", "timestamp");
+
+
+DROP TABLE IF EXISTS fieldnames;
+CREATE TABLE fieldnames
+(
+  "name"        text PRIMARY KEY,
+  "description" text,
+  "timestamp"   bigint
+) WITH OIDS;
+ALTER TABLE fieldnames OWNER TO pracro;
+
+
+DROP TABLE IF EXISTS fields;
+CREATE TABLE fields
+(
+  "transaction" bigint,
+  "name"        text,
+  "value"       text
+) WITH OIDS;
+ALTER TABLE fields OWNER TO pracro;
+-- ALTER TABLE fields ADD CONSTRAINT cx_fields_transactions FOREIGN KEY ("transaction") REFERENCES transactions("uid");
+-- ALTER TABLE fields ADD CONSTRAINT cx_fields_name FOREIGN KEY ("name") REFERENCES fieldnames("name");
+
+
+DROP TABLE IF EXISTS journal;
+CREATE TABLE journal
+(
+  "cpr"       varchar(11),
+  "macro"     text,
+  "version"   text,
+  "timestamp" bigint,
+  "user"      text,
+  "journal"   text
+) WITH OIDS;
+ALTER TABLE journal OWNER TO pracro;
+CREATE INDEX x_journal_cpr_timestamp ON journal("cpr", "timestamp");
+