pre draft of the db layout

8 files changed, 583 insertions, 0 deletions

diff --git a/development/pim/dbpaper/dbpimpaper.tex b/development/pim/dbpaper/dbpimpaper.tex
new file mode 100644
index 0000000..f664a05
--- a/dev/null
+++ b/development/pim/dbpaper/dbpimpaper.tex
@@ -0,0 +1,57 @@
+\documentclass [12pt,a4paper]{article}

+\usepackage{german}

+\usepackage[latin1]{inputenc}

+\pagestyle{headings}

+\usepackage{epsfig}

+\usepackage{graphicx}

+

+\begin{document}

+

+\begin{titlepage}

+

+\begin{center}

+

+\Huge{Database for Opie}

+\Large{\\pre beta\\}

+\Large{September - 6 - 2002}

+\linebreak

+

+\normalsize{}

+\vfill

+

+\begin{figure}[h]

+\begin{minipage}{14cm}

+\centerline{\includegraphics[width=8 cm]{logo}}

+\end{minipage}

+\end{figure}

+

+\end{center}

+

+\vfill

+\centerline{

+\begin{tabular}[t]{|c|c|c|} \hline

+\textbf{Version} & \textbf{Author} & \textbf{Email} \\ \hline\hline

+0.0.3 & Maximilian Reiß & harlekin@handhelds.org \\ \hline

+0.0.4 & Stefan Eilers & eilers.stefan@epost.de \\ \hline

+0.0.5 & Holger Freyther & zecke@handhelds.org \\ \hline

+0.0.6 & Stefan Eilers & eilers.stefan@epost.de \\ \hline

+0.0.7 & Maximilian Reiß & harlekin@handhelds.org \\ \hline

+\end{tabular}

+}

+

+\end{titlepage}

+

+\tableofcontents{}

+

+\pagebreak

+

+\input{intro.tex}

+\input{rel.tex}

+\input{tables.tex}

+\input{interface.tex}

+\input{gui.tex}

+

+\pagebreak

+\listoffigures

+

+\end{document}

diff --git a/development/pim/dbpaper/gui.tex b/development/pim/dbpaper/gui.tex
new file mode 100644
index 0000000..c62d136
--- a/dev/null
+++ b/development/pim/dbpaper/gui.tex
@@ -0,0 +1,15 @@
+\pagebreak
+\section{Sample gui}
+
+\begin{figure}[h]
+
+\begin{minipage}{14cm}
+%% Fehlt mir leider...
+Fehlt mir...
+%%\centerline{\includegraphics[width=14 cm]{addgui}}
+
+\caption{Extended ER Diagram}
+\end{minipage}
+\end{figure}
+
+\noindent
diff --git a/development/pim/dbpaper/interface.tex b/development/pim/dbpaper/interface.tex
new file mode 100644
index 0000000..a35b43b
--- a/dev/null
+++ b/development/pim/dbpaper/interface.tex
@@ -0,0 +1,2 @@
+\pagebreak
+\section{Interface}
\ No newline at end of file
diff --git a/development/pim/dbpaper/intro.tex b/development/pim/dbpaper/intro.tex
new file mode 100644
index 0000000..0a26b90
--- a/dev/null
+++ b/development/pim/dbpaper/intro.tex
@@ -0,0 +1,112 @@
+\pagebreak
+\section{Introduction}
+
+\subsection{This document}
+
+Note: This paper is just a request for comment! Don't expect to find anything in the real implementation !
+\\
+This draft paper describes the database layout of the Opie PIM applications.
+Design goal is a fast and extendable layout on a sql database to support cross referencing.
+
+
+\subsection{Copyright}
+
+Copyright notice
+\copyright 2002, Maximilian Rei{\ss} \& Stefan Eilers \& Holger Freyther
+
+\pagebreak
+\section{Relations}
+\subsection{About Cross Referencing and Horizontal Data Storage}
+Before you read on, it may be helpful to get some information about the solution of ``horizontal'' data storage into the tables and cross referencing.
+Some of the tables are just using 4 attributes:
+\begin{enumerate}
+\item UID: This countains an unique ID for the complete entry.
+\item ID: This defines a number to seperate the rows of an entry.
+\item Type: Describes the type of this entry.
+\item Value: And the data which was described by ``Type'' is stored here.
+\end{enumerate}
+
+These kinds of tables are very flexible in what they may contain, due to the fact that the ``Type'' may be without any constraint. Therefore it is perfectly for storing personal information, which may change in the future, or should be increased. The other advantage is the fact that these tables are automatically convertable into XML:
+The ``Type'' has just to be converted into an ``attribute'' in a XML-tag. Thats all, and that will work for all future Types !
+
+Let's see how such an entry may look like for the table ``ADDRESSBOOK'':
+
+\begin{tabular}[ht]{|l|l|l|l|}
+\hline
+\underline{UID} & \underline{ID} & Type & Value\\
+\hline
+ 1231& 00& Title& Herr\\
+\hline
+ 1231& 01& FirstName& Stefan\\
+\hline
+ 1231& 02& LastName& Eilers\\
+\hline
+\end{tabular}
+\\
+\\
+We need some additional information about this entry, which is stored into the table ``PERSONAL\_DATA\_CONTACT'':
+\\
+\\
+\begin{tabular}[ht]{|l|l|l|l|}
+\hline
+\underline{UID} & \underline{ID} & Type & Value\\
+\hline
+ 2345& 00& Email& eilers@sra.uni-hannover.de, eilers.stefan@epost.de\\
+\hline
+ 2345& 01& DefaultEmail& eilers@sra.uni-hannover.de\\
+\hline
+ 2345& 02& HomeWebPage& www.sra.uni-hannover.de/$\sim$eilers/\\
+\hline
+\end{tabular}
+\\
+\\
+The last question is: How to get these information together? This is the job of the cross-referencing-table\footnote{We expecting that the table ``PERSONAL\_DATA\_CONTACT'' was registered in the table ``TABLEID'' as table ``03''!}:
+\\
+\\
+\begin{tabular}[ht]{|l|l|l|l|l|l|}
+\hline
+\underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\
+\hline
+ 01& 1231& & 03& 2345&\\
+\hline
+\end{tabular}
+\\
+\\
+Type was left empty, due to the fact that we wanted to link the complete entry and not some rows of the entry.
+\\
+In some cases, it may be useful to reference in a much more smaller granularity than complete entries. What should we do, if we want to store the children of a person ? 
+The easiest way is to store the children into the table ``PERSONAL\_DATA'':\footnote{We expecting that the table ``PERSONAL\_DATA'' was registered in the table ``TABLEID'' as table ``05''!}\\
+\\
+\begin{tabular}[ht]{|l|l|l|l|}
+\hline
+\underline{UID} & \underline{ID} & Type & Value\\
+\hline
+ 2344&  11& Children& Amaly, Karl, Clarissa\\
+\hline
+\end{tabular}\\
+\\
+and reference it again with the table ``ADDRESSBOOK'':\\
+\\
+\begin{tabular}[ht]{|l|l|l|l|l|l|}
+\hline
+\underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\
+\hline
+ 01& 1231& & 05& 2344&\\
+\hline
+\end{tabular}
+\\
+\\
+But what happens if you want to store additional information about the children, as there mobile phone numbers, etc?
+
+In this case we need an entry in addressbook and a reference to this entry. Lets expect ``Amaly'' was stored in the table ``ADDRESSBOOK'' with UID 9213 and Karl with UID 7654. A cross-reference will look like this:\\
+\\
+\begin{tabular}[ht]{|l|l|l|l|l|l|}
+\hline
+\underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\
+\hline
+ 05& 2344& Children& 01& 9213& \\
+\hline
+ 05& 2344& Children& 01& 7654& \\
+\hline
+\end{tabular}
+\\
diff --git a/development/pim/dbpaper/logo.png b/development/pim/dbpaper/logo.png
new file mode 100644
index 0000000..7b8a711
--- a/dev/null
+++ b/development/pim/dbpaper/logo.png
diff --git a/development/pim/dbpaper/re.tex b/development/pim/dbpaper/re.tex
new file mode 100644
index 0000000..3e44cca
--- a/dev/null
+++ b/development/pim/dbpaper/re.tex
@@ -0,0 +1,13 @@
+\pagebreak
+\section{ER Diagrams}
+
+\begin{figure}[h]
+
+\begin{minipage}{14cm}
+\centerline{\includegraphics[width=14 cm]{ER-komplett}}
+
+\caption{Extended ER Diagram}
+\end{minipage}
+\end{figure}
+
+\noindent
diff --git a/development/pim/dbpaper/rel.tex b/development/pim/dbpaper/rel.tex
new file mode 100644
index 0000000..b7d0990
--- a/dev/null
+++ b/development/pim/dbpaper/rel.tex
@@ -0,0 +1,81 @@
+
+\pagebreak
+
+\subsection{Database Relations}
+TODOLIST \{ UID, DueDate, StartDate, EndDate, Completed, Description, Summary, Priority, Progress, Parent, Status \}\\
+DueDate can be null \\
+StartDate and EndDate are for timetracking reasons.
+\\
+\\
+\noindent
+DATEBOOK \{ UID, ID, Item, Value \}\\
+ID autoincrement\\
+Type element \{ Description, Location, Type, start, end, note, created \}\\
+\\
+\noindent
+RECURRANCE \{ UID, TID, RType, RWeekDays, RPosition, RFreq, RHasEndDate, REndDate \} \\
+The TID (TableID) is needed here, since both, TODOLIST and DATEBOOK use it, and in future maybe more.\\
+\\
+\noindent
+ADDRESSBOOK \{UID, ID, Item, Value \}\\
+ID autoincrement\\
+For contact to a person:\\
+Type element \{Title, FirstName, MiddleName, LastName, Suffix, Note \}\\  
+For contact to a company:\\
+Type element \{ CompanyName, Department, Note \}\\
+\\
+\noindent
+POSTAL \{ UID, TID, Type, Street, City, State, Zip, Country \} \\
+where Type can be Home, Business as predefined or any other as custom fields (not syncable) \\
+\\
+\noindent
+PERSONAL\_DATA \{ UID, ID, Item, Value \}\\
+ID autoincrement \\
+Item element \{ Company, JobTitle, Department, Office, Profession, Assistant
+ \footnote{What is Assistent and Manager ??  (se), and what about multiple assistents? Maybe by same UID but different ID and same Item - ugly(max)}, Manager, Spouse, Children, Gender, Birthday, Aniversary, Nickname, Note, \\
+ DefaultEmail, Email, HomeWebpage, Homephone, HomeFax, HomeMobil \}\\  
+\\ 
+\noindent
+TABLEID \{ TID, Name, DefaultRep, Version \}  \\
+TID Autoincrement \\
+The first 3 TIDs are set: 0 == datebook, 1 == addressbook, 2 == todolist.\\
+The rest can are assigned in order of registration of the service/app.
+DefaultRep is the default representation. That are fields from the table of the app itself which should be shown when crosslinked. \\
+\\
+\noindent
+CROSSREF \{ TID1, UID1, Item1, TID2, UID2, Item2 \}\\
+Item \emph{x} defines the field-type in the table which should be addressed. It may be empty if no special field should be addressed. \\
+Maybe add a field ``discription'' like ``verwaltet'', ``schaut an''
+
+\noindent
+Thus, it is possible to crossreference i.e. a Child (Item1 = ``Child'') in the Table PERSONAL\_DATA (TID1 = 0) with an entry in the Table ADDRESSBOOK (TID= 1, Item2 = ````) which stores the data of that child.\\
+
+\noindent
+Using ``Item'' to select which element should be referenced (instead using the ID which may only exist in our table) should guarantee that this reference may survive conversions between different formats. I.e.: For converting our Database into a XML-File, the Item should be converted directly into a attribute-name which is placed into a tag. Therefore the cross reference will stay correct!
+
+\noindent
+Still unclear is, how to synchronize our database (as XML-File, directly or via C++ Interface access) with outlook or other systems, without loosing this cross reference. We could not expect that this fine granularity or referencing may be supported by other systems...\\
+\\
+\noindent
+ALARM \{ TID, UID, Sound, Day, Month, Year, Time \}\\
+TID is needed since more then one app can have alarms and UID is not necessarly unique over different apps. \\
+A exclusive table for all alarms\\
+\\
+\noindent
+CATEGORY \{ UID, Parent, Name, ApplicationName, ForeGroundColor, BackGroundColor \}\\
+UID due to making syncing sane\\
+Parent is used for subcategories \\
+\\
+\noindent
+RECORD\_CATEGORIES \{ TID, UID, CategoryUID \}\\
+\\
+\noindent
+FILES \{ UID, Type, Url \}\\ 
+\\
+\noindent
+APPLICATION \{ UID, TYPE, URL \}\\ 
+\\
+\noindent
+LOCATION \{ UID, Name\}\\
+
+
diff --git a/development/pim/dbpaper/tables.tex b/development/pim/dbpaper/tables.tex
new file mode 100644
index 0000000..ac28a47
--- a/dev/null
+++ b/development/pim/dbpaper/tables.tex
@@ -0,0 +1,303 @@
+\pagebreak
+
+\section{Tables}
+
+\noindent
+Todolist:\\
+\begin{tabular}[ht]{|l|l|l|l|l|l|l|l|l|l|l|l|l|}
+\hline
+\underline{UID} & DueDate & StartDate & FinishDate & Completed & Description & Summary & Priority & Progress & Parent & Status \\
+\hline
+ & & & & & & & & & & & &\\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE TODOLIST (
+  UID         int NOT NULL UNIQUE,
+  DueDate     text,  -- sqlite does not know timestamps
+  StartDate   text,
+  FinishDate  text,
+  Completed   bool,
+  Description text,
+  Summary     text,
+  Priority    int,
+  Progress    int,
+  Parent      int,
+  Status      int
+  PRIMARY KEY(UID)
+);      
+\end{verbatim}
+
+
+\noindent
+Datebook: \\
+\begin{tabular}[ht]{|l|l|l|l|}
+\hline
+\underline{UID} & \underline{ID} & Item & Value\\
+\hline
+ & & & \\
+\hline
+\end{tabular}
+
+\begin{verbatim}
+CREATE TABLE DATEBOOK (
+  UID     int NOT NULL,
+  ID      int AUTOINCREMENT,
+  Item    text,
+  Value   text,
+  PRIMARY KEY(UID,ID)
+};
+\end{verbatim} 
+
+
+\noindent
+Recurrence:\\
+\begin{tabular}[ht]{|l|l|l|l|l|l|l|l|}
+\hline
+\underline{UID} & \underline{TID} & RType & RWeekDays & RPosition & RFreq & RHasEndDate &REndDate  \\
+\hline
+ & & & & & & &\\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE RECURRENCE (
+  UID          int NOT NULL UNIQUE,
+          TID   int NOT NULL
+  RType        int,
+  RWeekDays    int,
+  RPosition    int,
+  RFreq        int,
+  RHasEndDate bool,
+  REndDate     DATETIME,
+  PRIMARY KEY (UID)
+);
+\end{verbatim}
+
+\noindent
+Addressbook: \\
+\begin{tabular}[ht]{|l|l|l|l|}
+\hline
+\underline{UID} & \underline{ID} & Item & Value\\
+\hline
+ & & & \\
+\hline
+\end{tabular}
+
+\begin{verbatim}
+CREATE TABLE ADDRESSBOOK (
+  UID     int NOT NULL,
+  ID      int AUTOINCREMENT,
+  Item    text,
+  Value   text,
+  PRIMARY KEY(UID,ID)
+};
+\end{verbatim} 
+ 
+
+\noindent
+Postal:\\
+\begin{tabular}[ht]{|l|l|l|l|l|l|l|l|}
+\hline
+\underline{UID} & \underline{TID} & \underline{Type} & Street & City & State & Zip & Country  \\
+\hline
+ & & & & & & &\\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE POSTAL (
+  UID     int NOT NULL ,
+  TID      int NOT NULL,
+  Type    int NOT NULL UNIQUE,  -- maybe text, then more are possible
+  Street  text,
+  City    text,
+  State   text,
+  Zip     text,  -- who knows if theres no country with letters in the zip code
+  Country,
+  PRIMARY KEY (UID, Type)
+);
+\end{verbatim}
+ 
+
+\noindent
+Personal data: \\
+\begin{tabular}[ht]{|l|l|l|l|}
+\hline
+\underline{UID} & \underline{ID} & Ident & Value \\
+\hline
+ & & & \\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE PERSONAL_DATA_CONTACT (
+  UID    int NOT NULL UNIQUE,
+  ID     int AUTOINCREMENT,
+  Ident  text,
+  Value  text,
+  PRIMARY KEY(UID,ID)
+);      
+\end{verbatim}
+
+\noindent
+TableID: \\
+\begin{tabular}[ht]{|l|l|l|l|}
+\hline
+\underline{UID} & Name & DefaultRep & Version\\
+\hline
+ & & &\\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE TABLEID (
+  UID         int NOT NULL UNIQUE,
+  Name        text,
+  DefaultRep  text, -- list of which which columns from apps table are supposed to be queried for default,
+  Version     int NOT NULL 
+  PRIMARY KEY (UID)
+);
+\end{verbatim}
+
+
+
+\noindent
+CrossRef: \\
+\begin{tabular}[ht]{|l|l|l|l|l|l|}
+\hline
+\underline{TID1} & \underline{UID1} & \underline{Item1} & \underline{TID2} & \underline{UID2} & \underline{Item2} \\
+\hline
+ & & & & &\\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE CROSSREF (
+  TID1       int NOT NULL,
+  UID1       int NOT NULL,
+  Item1      int NOT NULL,
+  TID2       int MOT NULL,
+  UID2       int NOT NULL,
+  Item2      int NOT NULL,
+  PRIMARY KEY() 
+);
+\end{verbatim}
+
+
+\noindent
+Category: \\
+\begin{tabular}[ht]{|l|l|l|l|l|l|}
+\hline
+\underline{UID} & Parent & Name  & ApplicationName & ForeGroundColor & BackGroundColor\\
+\hline
+ & & & & &\\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE CATEGORY (
+  UID   int NOT NULL UNIQUE,
+  Name  text,
+  ApplicationName text,
+          ForeGroundColor text,<-- maybe hex?
+  BackGroundColor text,
+  PRIMARY KEY(UID)
+);
+\end{verbatim}
+
+\noindent
+Files: \\
+\begin{tabular}[ht]{|l|l|l|l|}
+\hline
+\underline{UID}  & MimeType & URL \\
+\hline
+ & &\\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE FILES (
+  UID       int NOT NULL UNIQUE,
+  MimeType      text,   -- mimetype
+  URL       text, 
+  PRIMARY KEY (UID)
+);
+\end{verbatim}
+
+\noindent
+Applications: \\
+\begin{tabular}[ht]{|l|l|l|}
+\hline
+\underline{UID} & MimeType & URL \\
+\hline
+ & & \\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE APPLICATION (
+  UID       int NOT NULL UNIQUE,
+  MimeType      text,   -- mimetype
+  URL       text, 
+  PRIMARY KEY (UID)
+);
+\end{verbatim}
+
+\noindent
+        Location: \\    
+\begin{tabular}[ht]{|l|l|}
+\hline
+\underline{UID} & Name  \\
+\hline
+ &  \\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE LOCATION (
+  UID   int NOT NULL UNIQUE,
+  Name  text,
+  PRIMARY KEY (UID)
+);
+\end{verbatim} 
+
+
+\noindent
+Alarm: \\
+\begin{tabular}[ht]{|l|l|l|l|l|l|l|l|}
+\hline
+\underline{TID} & \underline{UID} & Sound & Day & Month & Year & Time \\
+\hline
+ & & & & & & \\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE ALARM(
+  TID       int NOT NULL,
+  UID       int NOT NULL,
+  Sound     text,
+  Day       int MOT NULL,
+  Month     int NOT NULL,
+  Year      int NOT NULL,
+  Time      text
+  PRIMARY KEY() 
+);
+\end{verbatim}
+
+\noindent
+Record Categories: \\
+no sure if we need it, breaks the crossref table idea \\
+\begin{tabular}[ht]{|l|l|l|}
+\hline
+\underline{TID} & \underline{UID} & CategoryUID\\
+\hline
+ & &\\
+\hline
+\end{tabular}
+\begin{verbatim}
+CREATE TABLE RECORD_CATEGORIES(
+  TID          int NOT NULL,
+  UID          int NOT NULL,
+  CategoryId   int NOT NULL
+  PRIMARY KEY() 
+);
+\end{verbatim}
+\pagebreak
+
+\noindent
+Each app that wants to participate in cross referencing needs : \\
+- to implement a certain interface
+- need to suppy a default representation which is set as it is registered into the TID table
+