% -*- 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