summaryrefslogtreecommitdiff
path: root/design/database.tex
diff options
context:
space:
mode:
Diffstat (limited to 'design/database.tex')
-rw-r--r--design/database.tex71
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