-- 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");