summaryrefslogtreecommitdiff
path: root/development/pim
authorharlekin <harlekin>2002-09-16 19:45:19 (UTC)
committer harlekin <harlekin>2002-09-16 19:45:19 (UTC)
commit154ef04f6d74044a750ec89c14f3521b0602e795 (patch) (unidiff)
tree7db23061f91861a275beb06772e6864682a660ff /development/pim
parent3a8345528bc39a57b5d5bb50f4a69e32eae6d223 (diff)
downloadopie-154ef04f6d74044a750ec89c14f3521b0602e795.zip
opie-154ef04f6d74044a750ec89c14f3521b0602e795.tar.gz
opie-154ef04f6d74044a750ec89c14f3521b0602e795.tar.bz2
pre draft of the db layout
Diffstat (limited to 'development/pim') (more/less context) (ignore whitespace changes)
-rw-r--r--development/pim/dbpaper/dbpimpaper.tex57
-rw-r--r--development/pim/dbpaper/gui.tex15
-rw-r--r--development/pim/dbpaper/interface.tex2
-rw-r--r--development/pim/dbpaper/intro.tex112
-rw-r--r--development/pim/dbpaper/logo.pngbin0 -> 51465 bytes
-rw-r--r--development/pim/dbpaper/re.tex13
-rw-r--r--development/pim/dbpaper/rel.tex81
-rw-r--r--development/pim/dbpaper/tables.tex303
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 @@
1\documentclass [12pt,a4paper]{article}
2\usepackage{german}
3\usepackage[latin1]{inputenc}
4\pagestyle{headings}
5\usepackage{epsfig}
6\usepackage{graphicx}
7
8\begin{document}
9
10\begin{titlepage}
11
12\begin{center}
13
14\Huge{Database for Opie}
15\Large{\\pre beta\\}
16\Large{September - 6 - 2002}
17\linebreak
18
19\normalsize{}
20\vfill
21
22\begin{figure}[h]
23\begin{minipage}{14cm}
24\centerline{\includegraphics[width=8 cm]{logo}}
25\end{minipage}
26\end{figure}
27
28\end{center}
29
30\vfill
31\centerline{
32\begin{tabular}[t]{|c|c|c|} \hline
33\textbf{Version} & \textbf{Author} & \textbf{Email} \\ \hline\hline
340.0.3 & Maximilian Reiß & harlekin@handhelds.org \\ \hline
350.0.4 & Stefan Eilers & eilers.stefan@epost.de \\ \hline
360.0.5 & Holger Freyther & zecke@handhelds.org \\ \hline
370.0.6 & Stefan Eilers & eilers.stefan@epost.de \\ \hline
380.0.7 & Maximilian Reiß & harlekin@handhelds.org \\ \hline
39\end{tabular}
40}
41
42\end{titlepage}
43
44\tableofcontents{}
45
46\pagebreak
47
48\input{intro.tex}
49\input{rel.tex}
50\input{tables.tex}
51\input{interface.tex}
52\input{gui.tex}
53
54\pagebreak
55\listoffigures
56
57\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 @@
1\pagebreak
2\section{Sample gui}
3
4\begin{figure}[h]
5
6\begin{minipage}{14cm}
7%% Fehlt mir leider...
8Fehlt mir...
9%%\centerline{\includegraphics[width=14 cm]{addgui}}
10
11\caption{Extended ER Diagram}
12\end{minipage}
13\end{figure}
14
15\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 @@
1\pagebreak
2\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 @@
1\pagebreak
2\section{Introduction}
3
4\subsection{This document}
5
6Note: This paper is just a request for comment! Don't expect to find anything in the real implementation !
7\\
8This draft paper describes the database layout of the Opie PIM applications.
9Design goal is a fast and extendable layout on a sql database to support cross referencing.
10
11
12\subsection{Copyright}
13
14Copyright notice
15\copyright 2002, Maximilian Rei{\ss} \& Stefan Eilers \& Holger Freyther
16
17\pagebreak
18\section{Relations}
19\subsection{About Cross Referencing and Horizontal Data Storage}
20Before you read on, it may be helpful to get some information about the solution of ``horizontal'' data storage into the tables and cross referencing.
21Some of the tables are just using 4 attributes:
22\begin{enumerate}
23\item UID: This countains an unique ID for the complete entry.
24\item ID: This defines a number to seperate the rows of an entry.
25\item Type: Describes the type of this entry.
26\item Value: And the data which was described by ``Type'' is stored here.
27\end{enumerate}
28
29These 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:
30The ``Type'' has just to be converted into an ``attribute'' in a XML-tag. Thats all, and that will work for all future Types !
31
32Let's see how such an entry may look like for the table ``ADDRESSBOOK'':
33
34\begin{tabular}[ht]{|l|l|l|l|}
35\hline
36\underline{UID} & \underline{ID} & Type & Value\\
37\hline
38 1231& 00& Title& Herr\\
39\hline
40 1231& 01& FirstName& Stefan\\
41\hline
42 1231& 02& LastName& Eilers\\
43\hline
44\end{tabular}
45\\
46\\
47We need some additional information about this entry, which is stored into the table ``PERSONAL\_DATA\_CONTACT'':
48\\
49\\
50\begin{tabular}[ht]{|l|l|l|l|}
51\hline
52\underline{UID} & \underline{ID} & Type & Value\\
53\hline
54 2345& 00& Email& eilers@sra.uni-hannover.de, eilers.stefan@epost.de\\
55\hline
56 2345& 01& DefaultEmail& eilers@sra.uni-hannover.de\\
57\hline
58 2345& 02& HomeWebPage& www.sra.uni-hannover.de/$\sim$eilers/\\
59\hline
60\end{tabular}
61\\
62\\
63The 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''!}:
64\\
65\\
66\begin{tabular}[ht]{|l|l|l|l|l|l|}
67\hline
68\underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\
69\hline
70 01& 1231& & 03& 2345&\\
71\hline
72\end{tabular}
73\\
74\\
75Type was left empty, due to the fact that we wanted to link the complete entry and not some rows of the entry.
76\\
77In 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 ?
78The 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''!}\\
79\\
80\begin{tabular}[ht]{|l|l|l|l|}
81\hline
82\underline{UID} & \underline{ID} & Type & Value\\
83\hline
84 2344& 11& Children& Amaly, Karl, Clarissa\\
85\hline
86\end{tabular}\\
87\\
88and reference it again with the table ``ADDRESSBOOK'':\\
89\\
90\begin{tabular}[ht]{|l|l|l|l|l|l|}
91\hline
92\underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\
93\hline
94 01& 1231& & 05& 2344&\\
95\hline
96\end{tabular}
97\\
98\\
99But what happens if you want to store additional information about the children, as there mobile phone numbers, etc?
100
101In 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:\\
102\\
103\begin{tabular}[ht]{|l|l|l|l|l|l|}
104\hline
105\underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\
106\hline
107 05& 2344& Children& 01& 9213& \\
108\hline
109 05& 2344& Children& 01& 7654& \\
110\hline
111\end{tabular}
112\\
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
Binary files differ
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 @@
1\pagebreak
2\section{ER Diagrams}
3
4\begin{figure}[h]
5
6\begin{minipage}{14cm}
7\centerline{\includegraphics[width=14 cm]{ER-komplett}}
8
9\caption{Extended ER Diagram}
10\end{minipage}
11\end{figure}
12
13\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 @@
1
2\pagebreak
3
4\subsection{Database Relations}
5TODOLIST \{ UID, DueDate, StartDate, EndDate, Completed, Description, Summary, Priority, Progress, Parent, Status \}\\
6DueDate can be null \\
7StartDate and EndDate are for timetracking reasons.
8\\
9\\
10\noindent
11DATEBOOK \{ UID, ID, Item, Value \}\\
12ID autoincrement\\
13Type element \{ Description, Location, Type, start, end, note, created \}\\
14\\
15\noindent
16RECURRANCE \{ UID, TID, RType, RWeekDays, RPosition, RFreq, RHasEndDate, REndDate \} \\
17The TID (TableID) is needed here, since both, TODOLIST and DATEBOOK use it, and in future maybe more.\\
18\\
19\noindent
20ADDRESSBOOK \{UID, ID, Item, Value \}\\
21ID autoincrement\\
22For contact to a person:\\
23Type element \{Title, FirstName, MiddleName, LastName, Suffix, Note \}\\
24For contact to a company:\\
25Type element \{ CompanyName, Department, Note \}\\
26\\
27\noindent
28POSTAL \{ UID, TID, Type, Street, City, State, Zip, Country \} \\
29where Type can be Home, Business as predefined or any other as custom fields (not syncable) \\
30\\
31\noindent
32PERSONAL\_DATA \{ UID, ID, Item, Value \}\\
33ID autoincrement \\
34Item element \{ Company, JobTitle, Department, Office, Profession, Assistant
35 \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, \\
36 DefaultEmail, Email, HomeWebpage, Homephone, HomeFax, HomeMobil \}\\
37\\
38\noindent
39TABLEID \{ TID, Name, DefaultRep, Version \} \\
40TID Autoincrement \\
41The first 3 TIDs are set: 0 == datebook, 1 == addressbook, 2 == todolist.\\
42The rest can are assigned in order of registration of the service/app.
43DefaultRep is the default representation. That are fields from the table of the app itself which should be shown when crosslinked. \\
44\\
45\noindent
46CROSSREF \{ TID1, UID1, Item1, TID2, UID2, Item2 \}\\
47Item \emph{x} defines the field-type in the table which should be addressed. It may be empty if no special field should be addressed. \\
48Maybe add a field ``discription'' like ``verwaltet'', ``schaut an''
49
50\noindent
51Thus, 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.\\
52
53\noindent
54Using ``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!
55
56\noindent
57Still 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...\\
58\\
59\noindent
60ALARM \{ TID, UID, Sound, Day, Month, Year, Time \}\\
61TID is needed since more then one app can have alarms and UID is not necessarly unique over different apps. \\
62A exclusive table for all alarms\\
63\\
64\noindent
65CATEGORY \{ UID, Parent, Name, ApplicationName, ForeGroundColor, BackGroundColor \}\\
66UID due to making syncing sane\\
67Parent is used for subcategories \\
68\\
69\noindent
70RECORD\_CATEGORIES \{ TID, UID, CategoryUID \}\\
71\\
72\noindent
73FILES \{ UID, Type, Url \}\\
74\\
75\noindent
76APPLICATION \{ UID, TYPE, URL \}\\
77\\
78\noindent
79LOCATION \{ UID, Name\}\\
80
81
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 @@
1\pagebreak
2
3\section{Tables}
4
5\noindent
6Todolist:\\
7\begin{tabular}[ht]{|l|l|l|l|l|l|l|l|l|l|l|l|l|}
8\hline
9\underline{UID} & DueDate & StartDate & FinishDate & Completed & Description & Summary & Priority & Progress & Parent & Status \\
10\hline
11 & & & & & & & & & & & &\\
12\hline
13\end{tabular}
14\begin{verbatim}
15CREATE TABLE TODOLIST (
16 UID int NOT NULL UNIQUE,
17 DueDate text, -- sqlite does not know timestamps
18 StartDate text,
19 FinishDate text,
20 Completed bool,
21 Description text,
22 Summary text,
23 Priority int,
24 Progress int,
25 Parent int,
26 Status int
27 PRIMARY KEY(UID)
28);
29\end{verbatim}
30
31
32\noindent
33Datebook: \\
34\begin{tabular}[ht]{|l|l|l|l|}
35\hline
36\underline{UID} & \underline{ID} & Item & Value\\
37\hline
38 & & & \\
39\hline
40\end{tabular}
41
42\begin{verbatim}
43CREATE TABLE DATEBOOK (
44 UID int NOT NULL,
45 ID int AUTOINCREMENT,
46 Item text,
47 Value text,
48 PRIMARY KEY(UID,ID)
49};
50\end{verbatim}
51
52
53\noindent
54Recurrence:\\
55\begin{tabular}[ht]{|l|l|l|l|l|l|l|l|}
56\hline
57\underline{UID} & \underline{TID} & RType & RWeekDays & RPosition & RFreq & RHasEndDate &REndDate \\
58\hline
59 & & & & & & &\\
60\hline
61\end{tabular}
62\begin{verbatim}
63CREATE TABLE RECURRENCE (
64 UID int NOT NULL UNIQUE,
65 TID int NOT NULL
66 RType int,
67 RWeekDays int,
68 RPosition int,
69 RFreq int,
70 RHasEndDate bool,
71 REndDate DATETIME,
72 PRIMARY KEY (UID)
73);
74\end{verbatim}
75
76\noindent
77Addressbook: \\
78\begin{tabular}[ht]{|l|l|l|l|}
79\hline
80\underline{UID} & \underline{ID} & Item & Value\\
81\hline
82 & & & \\
83\hline
84\end{tabular}
85
86\begin{verbatim}
87CREATE TABLE ADDRESSBOOK (
88 UID int NOT NULL,
89 ID int AUTOINCREMENT,
90 Item text,
91 Value text,
92 PRIMARY KEY(UID,ID)
93};
94\end{verbatim}
95
96
97\noindent
98Postal:\\
99\begin{tabular}[ht]{|l|l|l|l|l|l|l|l|}
100\hline
101\underline{UID} & \underline{TID} & \underline{Type} & Street & City & State & Zip & Country \\
102\hline
103 & & & & & & &\\
104\hline
105\end{tabular}
106\begin{verbatim}
107CREATE TABLE POSTAL (
108 UID int NOT NULL ,
109 TID int NOT NULL,
110 Type int NOT NULL UNIQUE, -- maybe text, then more are possible
111 Street text,
112 City text,
113 State text,
114 Zip text, -- who knows if theres no country with letters in the zip code
115 Country,
116 PRIMARY KEY (UID, Type)
117);
118\end{verbatim}
119
120
121\noindent
122Personal data: \\
123\begin{tabular}[ht]{|l|l|l|l|}
124\hline
125\underline{UID} & \underline{ID} & Ident & Value \\
126\hline
127 & & & \\
128\hline
129\end{tabular}
130\begin{verbatim}
131CREATE TABLE PERSONAL_DATA_CONTACT (
132 UID int NOT NULL UNIQUE,
133 ID int AUTOINCREMENT,
134 Ident text,
135 Value text,
136 PRIMARY KEY(UID,ID)
137);
138\end{verbatim}
139
140\noindent
141TableID: \\
142\begin{tabular}[ht]{|l|l|l|l|}
143\hline
144\underline{UID} & Name & DefaultRep & Version\\
145\hline
146 & & &\\
147\hline
148\end{tabular}
149\begin{verbatim}
150CREATE TABLE TABLEID (
151 UID int NOT NULL UNIQUE,
152 Name text,
153 DefaultRep text, -- list of which which columns from apps table are supposed to be queried for default,
154 Version int NOT NULL
155 PRIMARY KEY (UID)
156);
157\end{verbatim}
158
159
160
161\noindent
162CrossRef: \\
163\begin{tabular}[ht]{|l|l|l|l|l|l|}
164\hline
165\underline{TID1} & \underline{UID1} & \underline{Item1} & \underline{TID2} & \underline{UID2} & \underline{Item2} \\
166\hline
167 & & & & &\\
168\hline
169\end{tabular}
170\begin{verbatim}
171CREATE TABLE CROSSREF (
172 TID1 int NOT NULL,
173 UID1 int NOT NULL,
174 Item1 int NOT NULL,
175 TID2 int MOT NULL,
176 UID2 int NOT NULL,
177 Item2 int NOT NULL,
178 PRIMARY KEY()
179);
180\end{verbatim}
181
182
183\noindent
184Category: \\
185\begin{tabular}[ht]{|l|l|l|l|l|l|}
186\hline
187\underline{UID} & Parent & Name & ApplicationName & ForeGroundColor & BackGroundColor\\
188\hline
189 & & & & &\\
190\hline
191\end{tabular}
192\begin{verbatim}
193CREATE TABLE CATEGORY (
194 UID int NOT NULL UNIQUE,
195 Name text,
196 ApplicationName text,
197 ForeGroundColor text,<-- maybe hex?
198 BackGroundColor text,
199 PRIMARY KEY(UID)
200);
201\end{verbatim}
202
203\noindent
204Files: \\
205\begin{tabular}[ht]{|l|l|l|l|}
206\hline
207\underline{UID} & MimeType & URL \\
208\hline
209 & &\\
210\hline
211\end{tabular}
212\begin{verbatim}
213CREATE TABLE FILES (
214 UID int NOT NULL UNIQUE,
215 MimeType text, -- mimetype
216 URL text,
217 PRIMARY KEY (UID)
218);
219\end{verbatim}
220
221\noindent
222Applications: \\
223\begin{tabular}[ht]{|l|l|l|}
224\hline
225\underline{UID} & MimeType & URL \\
226\hline
227 & & \\
228\hline
229\end{tabular}
230\begin{verbatim}
231CREATE TABLE APPLICATION (
232 UID int NOT NULL UNIQUE,
233 MimeType text, -- mimetype
234 URL text,
235 PRIMARY KEY (UID)
236);
237\end{verbatim}
238
239\noindent
240 Location: \\
241\begin{tabular}[ht]{|l|l|}
242\hline
243\underline{UID} & Name \\
244\hline
245 & \\
246\hline
247\end{tabular}
248\begin{verbatim}
249CREATE TABLE LOCATION (
250 UID int NOT NULL UNIQUE,
251 Name text,
252 PRIMARY KEY (UID)
253);
254\end{verbatim}
255
256
257\noindent
258Alarm: \\
259\begin{tabular}[ht]{|l|l|l|l|l|l|l|l|}
260\hline
261\underline{TID} & \underline{UID} & Sound & Day & Month & Year & Time \\
262\hline
263 & & & & & & \\
264\hline
265\end{tabular}
266\begin{verbatim}
267CREATE TABLE ALARM(
268 TID int NOT NULL,
269 UID int NOT NULL,
270 Sound text,
271 Day int MOT NULL,
272 Month int NOT NULL,
273 Year int NOT NULL,
274 Time text
275 PRIMARY KEY()
276);
277\end{verbatim}
278
279\noindent
280Record Categories: \\
281no sure if we need it, breaks the crossref table idea \\
282\begin{tabular}[ht]{|l|l|l|}
283\hline
284\underline{TID} & \underline{UID} & CategoryUID\\
285\hline
286 & &\\
287\hline
288\end{tabular}
289\begin{verbatim}
290CREATE TABLE RECORD_CATEGORIES(
291 TID int NOT NULL,
292 UID int NOT NULL,
293 CategoryId int NOT NULL
294 PRIMARY KEY()
295);
296\end{verbatim}
297\pagebreak
298
299\noindent
300Each app that wants to participate in cross referencing needs : \\
301- to implement a certain interface
302- need to suppy a default representation which is set as it is registered into the TID table
303