1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
% -*- coding: utf-8 -*-
\section{Database layout}
\begin{figure}
\begin{center}
\includegraphics[width=120mm]{database.eps}\\
\end{center}
\label{database}
\caption{Databasens indhold og et foreslået tabel design.}
\end{figure}
\begin{verbatim}
Database designformål:
- Hurtig skrivning af en transaktion.
- Hurtig genfinding af en tidligere skrevet transaktion.
- Robusthed overfor ændringer i makroers layout.
- Gode muligheder for at lave statistik udtræk.
Estimat af datastørrelser:
200 patienter pr. dag.
10 fields pr. patient.
----
2000 fields + 200 headere = 2200 entries i databasen pr. dag.
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
|
