From 3ad20fdd0c8d5c20f0c02e3d1ad2e1e6b0f2a078 Mon Sep 17 00:00:00 2001
From: bertho <bertho>
Date: Wed, 4 Feb 2009 14:11:15 +0000
Subject: Cleanup database access a bit and add a proper database schema

---
 server/pracro.psql | 60 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 60 insertions(+)
 create mode 100644 server/pracro.psql

(limited to 'server/pracro.psql')

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");
+
-- 
cgit v1.2.3