1 files changed, 71 insertions, 0 deletions
diff --git a/design/database.tex b/design/database.tex
index 0375202..2caa20e 100644
--- a/design/database.tex
+++ b/design/database.tex
@@ -22,4 +22,75 @@ Estimat af datastørrelser:
 Hvis en entry i gennemsnit består af 200 tegn er det
 2200 * 200 = 440000 bytes/dag.
 eller 153.16Mbytes/år
+
+// som root
+// # createuser -P -h localhost -U postgres
+// # createdb -U postgres -h localhost pracro
+
+
+CREATE DATABASE pracro
+  WITH OWNER = pracro
+       ENCODING = 'UNICODE'
+       TABLESPACE = pg_default;
+
+CREATE TABLE transactions
+(
+  "cpr" varchar(255),
+  "transaction" varchar(255),
+  "makro" varchar(255),
+  "version" varchar(255),
+  "timestamp" varchar(255),
+  "user" varchar(255)
+) 
+WITH OIDS;
+ALTER TABLE transactions OWNER TO pracro;
+
+CREATE TABLE fields
+(
+  "transaction" varchar(255),
+  "name" varchar(255),
+  "value" varchar(255)
+) 
+WITH OIDS;
+ALTER TABLE fields OWNER TO pracro;
+
+// Get all matching fields
+SELECT transactions.timestamp, transactions.transaction, fields.name, fields.value
+  FROM transactions, fields
+  WHERE transactions.cpr='2003791613'
+  AND transactions.transaction=fields.transaction
+  AND fields.name='fisk';
+
+transactionid skal bygges af
+unixtime-pid-commit_idx
+inkl. "-"erne.
+
 \end{verbatim}
+%// Get latest matching field
+%SELECT fields.value, MAX(transactions.timestamp)
+%  FROM fields, transactions
+%  WHERE transactions.cpr='2003791613'
+%  AND transactions.transaction=fields.transaction
+%  AND fields.name='fisk'
+%  GROUP BY fields.value;
+%
+%SELECT fields.name, fields.value, transactions.timestamp FROM transactions, fields
+%  WHERE transactions.transaction=(SELECT MAX(transactions.timestamp) as ts
+%    FROM fields, transactions
+%    WHERE transactions.cpr='2003791613'
+%    AND transactions.transaction=fields.transaction
+%    AND fields.name='fisk')
+%  AND fields.name='fisk';
+%
+%SELECT name, value, timestamp FROM transactions, fields,
+%(SELECT transactions.transaction, MAX(transactions.timestamp)
+%    FROM fields, transactions
+%    WHERE transactions.cpr='2003791613'
+%    AND transactions.transaction=fields.transaction
+%    AND fields.name='fisk'
+%    GROUP BY transactions.transaction) max
+%  WHERE transactions.timestamp=max.max
+%  AND transactions.transaction=max.transaction
+%  AND fields.name='fisk';
+%
+%AAAAAAAAAARGH