author | eilers <eilers> | 2002-10-09 14:36:57 (UTC) |
---|---|---|
committer | eilers <eilers> | 2002-10-09 14:36:57 (UTC) |
commit | 273d5329e29c43b287876202de2f0c500a312b7b (patch) (unidiff) | |
tree | 04dfc8d29dd57dd2bea76beb98f154825ba178b3 | |
parent | f9f2d227b3cfbc2187b4f7f535fc59f9735798d0 (diff) | |
download | opie-273d5329e29c43b287876202de2f0c500a312b7b.zip opie-273d5329e29c43b287876202de2f0c500a312b7b.tar.gz opie-273d5329e29c43b287876202de2f0c500a312b7b.tar.bz2 |
Changes regarding the tips of Robert Wittams
-rw-r--r-- | development/pim/dbpaper/dbpimpaper.tex | 1 | ||||
-rw-r--r-- | development/pim/dbpaper/intro.tex | 63 | ||||
-rw-r--r-- | development/pim/dbpaper/rel.tex | 22 | ||||
-rw-r--r-- | development/pim/dbpaper/tables.tex | 9 |
4 files changed, 43 insertions, 52 deletions
diff --git a/development/pim/dbpaper/dbpimpaper.tex b/development/pim/dbpaper/dbpimpaper.tex index f664a05..fe060de 100644 --- a/development/pim/dbpaper/dbpimpaper.tex +++ b/development/pim/dbpaper/dbpimpaper.tex | |||
@@ -33,12 +33,13 @@ | |||
33 | \textbf{Version} & \textbf{Author} & \textbf{Email} \\ \hline\hline | 33 | \textbf{Version} & \textbf{Author} & \textbf{Email} \\ \hline\hline |
34 | 0.0.3 & Maximilian Reiß & harlekin@handhelds.org \\ \hline | 34 | 0.0.3 & Maximilian Reiß & harlekin@handhelds.org \\ \hline |
35 | 0.0.4 & Stefan Eilers & eilers.stefan@epost.de \\ \hline | 35 | 0.0.4 & Stefan Eilers & eilers.stefan@epost.de \\ \hline |
36 | 0.0.5 & Holger Freyther & zecke@handhelds.org \\ \hline | 36 | 0.0.5 & Holger Freyther & zecke@handhelds.org \\ \hline |
37 | 0.0.6 & Stefan Eilers & eilers.stefan@epost.de \\ \hline | 37 | 0.0.6 & Stefan Eilers & eilers.stefan@epost.de \\ \hline |
38 | 0.0.7 & Maximilian Reiß & harlekin@handhelds.org \\ \hline | 38 | 0.0.7 & Maximilian Reiß & harlekin@handhelds.org \\ \hline |
39 | 0.0.8 & Stefan Eilers & eilers.stefan@epost.de \\ \hline | ||
39 | \end{tabular} | 40 | \end{tabular} |
40 | } | 41 | } |
41 | 42 | ||
42 | \end{titlepage} | 43 | \end{titlepage} |
43 | 44 | ||
44 | \tableofcontents{} | 45 | \tableofcontents{} |
diff --git a/development/pim/dbpaper/intro.tex b/development/pim/dbpaper/intro.tex index 0a26b90..c456333 100644 --- a/development/pim/dbpaper/intro.tex +++ b/development/pim/dbpaper/intro.tex | |||
@@ -15,23 +15,24 @@ Copyright notice | |||
15 | \copyright 2002, Maximilian Rei{\ss} \& Stefan Eilers \& Holger Freyther | 15 | \copyright 2002, Maximilian Rei{\ss} \& Stefan Eilers \& Holger Freyther |
16 | 16 | ||
17 | \pagebreak | 17 | \pagebreak |
18 | \section{Relations} | 18 | \section{Relations} |
19 | \subsection{About Cross Referencing and Horizontal Data Storage} | 19 | \subsection{About Cross Referencing and Horizontal Data Storage} |
20 | 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. | 20 | 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. |
21 | Some of the tables are just using 4 attributes: | 21 | Some of the tables are just using 5 attributes: |
22 | \begin{enumerate} | 22 | \begin{enumerate} |
23 | \item UID: This countains an unique ID for the complete entry. | 23 | \item UID: This countains an unique ID for the complete entry. An entry may consist of multiple |
24 | \item ID: This defines a number to seperate the rows of an entry. | 24 | rows, seperated by ID. |
25 | \item ID: This defines a number to seperate the rows of an entry. UID + ID must be unique for every entry. | ||
25 | \item Type: Describes the type of this entry. | 26 | \item Type: Describes the type of this entry. |
26 | \item Value: And the data which was described by ``Type'' is stored here. | 27 | \item Priority: For instance: Your default email-address will get the priority 1 and the other 2.. . 0 means ``not defined''. |
28 | \item Value: The data which was described by ``Type'' is stored here. | ||
27 | \end{enumerate} | 29 | \end{enumerate} |
28 | 30 | ||
29 | 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: | 31 | 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. |
30 | The ``Type'' has just to be converted into an ``attribute'' in a XML-tag. Thats all, and that will work for all future Types ! | 32 | \\ |
31 | |||
32 | Let's see how such an entry may look like for the table ``ADDRESSBOOK'': | 33 | Let's see how such an entry may look like for the table ``ADDRESSBOOK'': |
33 | 34 | ||
34 | \begin{tabular}[ht]{|l|l|l|l|} | 35 | \begin{tabular}[ht]{|l|l|l|l|} |
35 | \hline | 36 | \hline |
36 | \underline{UID} & \underline{ID} & Type & Value\\ | 37 | \underline{UID} & \underline{ID} & Type & Value\\ |
37 | \hline | 38 | \hline |
@@ -41,72 +42,66 @@ Let's see how such an entry may look like for the table ``ADDRESSBOOK'': | |||
41 | \hline | 42 | \hline |
42 | 1231& 02& LastName& Eilers\\ | 43 | 1231& 02& LastName& Eilers\\ |
43 | \hline | 44 | \hline |
44 | \end{tabular} | 45 | \end{tabular} |
45 | \\ | 46 | \\ |
46 | \\ | 47 | \\ |
47 | We need some additional information about this entry, which is stored into the table ``PERSONAL\_DATA\_CONTACT'': | 48 | We need some additional information about this entry, which is stored into the table ``PERSONAL\_DATA'': |
48 | \\ | 49 | \\ |
49 | \\ | 50 | \\ |
50 | \begin{tabular}[ht]{|l|l|l|l|} | 51 | \begin{tabular}[ht]{|l|l|l|l|l|} |
51 | \hline | 52 | \hline |
52 | \underline{UID} & \underline{ID} & Type & Value\\ | 53 | \underline{UID} & \underline{ID} & Type & Priority & Value\\ |
53 | \hline | 54 | \hline |
54 | 2345& 00& Email& eilers@sra.uni-hannover.de, eilers.stefan@epost.de\\ | 55 | 2345& 00& Email& 1& eilers.stefan@epost.de\\ |
55 | \hline | 56 | \hline |
56 | 2345& 01& DefaultEmail& eilers@sra.uni-hannover.de\\ | 57 | 2345& 01& Email& 2& eilers@sra.uni-hannover.de\\ |
57 | \hline | 58 | \hline |
58 | 2345& 02& HomeWebPage& www.sra.uni-hannover.de/$\sim$eilers/\\ | 59 | 2345& 02& WebPage& 1& www.sra.uni-hannover.de/$\sim$eilers/\\ |
59 | \hline | 60 | \hline |
60 | \end{tabular} | 61 | \end{tabular} |
61 | \\ | 62 | \\ |
62 | \\ | 63 | \\ |
63 | 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''!}: | 64 | 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'' was registered in the table ``TABLEID'' as table ``03''and ADDRESSBOOK with ``01''!}: |
64 | \\ | 65 | \\ |
65 | \\ | 66 | \\ |
66 | \begin{tabular}[ht]{|l|l|l|l|l|l|} | 67 | \begin{tabular}[ht]{|l|l|l|l|l|l|} |
67 | \hline | 68 | \hline |
68 | \underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\ | 69 | \underline{TID1} & \underline{UID1} & \underline{ID1} & \underline{TID2} & \underline{UID2} & \underline{ID2} \\ |
69 | \hline | 70 | \hline |
70 | 01& 1231& & 03& 2345&\\ | 71 | 01& 1231& (NULL) & 03& 2345& (NULL) \\ |
71 | \hline | 72 | \hline |
72 | \end{tabular} | 73 | \end{tabular} |
73 | \\ | 74 | \\ |
74 | \\ | 75 | \\ |
75 | Type was left empty, due to the fact that we wanted to link the complete entry and not some rows of the entry. | 76 | ID was left empty, due to the fact that we wanted to link the complete entry and not some rows of the entry. |
76 | \\ | 77 | \\ |
77 | 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 ? | 78 | 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 ? |
78 | 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''!}\\ | 79 | The easiest way is to store the children into the table ``PERSONAL\_DATA'':\footnote{We remember that the table ``PERSONAL\_DATA'' was registered in the table ``TABLEID'' as table ``03''!}, too.\\ |
79 | \\ | 80 | \\ |
80 | \begin{tabular}[ht]{|l|l|l|l|} | 81 | \begin{tabular}[ht]{|l|l|l|l|l|} |
81 | \hline | 82 | \hline |
82 | \underline{UID} & \underline{ID} & Type & Value\\ | 83 | \underline{UID} & \underline{ID} & Type & Priority & Value\\ |
83 | \hline | 84 | \hline |
84 | 2344& 11& Children& Amaly, Karl, Clarissa\\ | 85 | 2345& 11& Child & 0 & Amaly \\ |
85 | \hline | 86 | \hline |
86 | \end{tabular}\\ | 87 | 2345& 12& Child & 0 & Karl \\ |
87 | \\ | ||
88 | and reference it again with the table ``ADDRESSBOOK'':\\ | ||
89 | \\ | ||
90 | \begin{tabular}[ht]{|l|l|l|l|l|l|} | ||
91 | \hline | 88 | \hline |
92 | \underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\ | 89 | 2345& 13& Child & 0 & Clarissa\\ |
93 | \hline | 90 | \hline |
94 | 01& 1231& & 05& 2344&\\ | 91 | \end{tabular}\\ |
95 | \hline | ||
96 | \end{tabular} | ||
97 | \\ | ||
98 | \\ | 92 | \\ |
93 | |||
99 | But what happens if you want to store additional information about the children, as there mobile phone numbers, etc? | 94 | But what happens if you want to store additional information about the children, as there mobile phone numbers, etc? |
100 | 95 | ||
101 | 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:\\ | 96 | 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:\\ |
102 | \\ | 97 | \\ |
103 | \begin{tabular}[ht]{|l|l|l|l|l|l|} | 98 | \begin{tabular}[ht]{|l|l|l|l|l|l|} |
104 | \hline | 99 | \hline |
105 | \underline{TID1} & \underline{UID1} & \underline{Type1} & \underline{TID2} & \underline{UID2} & \underline{Type2} \\ | 100 | \underline{TID1} & \underline{UID1} & \underline{ID1} & \underline{TID2} & \underline{UID2} & \underline{ID2} \\ |
106 | \hline | 101 | \hline |
107 | 05& 2344& Children& 01& 9213& \\ | 102 | 03& 2345& 11& 01& 9213& (NULL) \\ |
108 | \hline | 103 | \hline |
109 | 05& 2344& Children& 01& 7654& \\ | 104 | 03& 2345& 12& 01& 7654& (NULL) \\ |
110 | \hline | 105 | \hline |
111 | \end{tabular} | 106 | \end{tabular} |
112 | \\ | 107 | \\ |
diff --git a/development/pim/dbpaper/rel.tex b/development/pim/dbpaper/rel.tex index b7d0990..967155d 100644 --- a/development/pim/dbpaper/rel.tex +++ b/development/pim/dbpaper/rel.tex | |||
@@ -26,36 +26,30 @@ Type element \{ CompanyName, Department, Note \}\\ | |||
26 | \\ | 26 | \\ |
27 | \noindent | 27 | \noindent |
28 | POSTAL \{ UID, TID, Type, Street, City, State, Zip, Country \} \\ | 28 | POSTAL \{ UID, TID, Type, Street, City, State, Zip, Country \} \\ |
29 | where Type can be Home, Business as predefined or any other as custom fields (not syncable) \\ | 29 | where Type can be Home, Business as predefined or any other as custom fields (not syncable) \\ |
30 | \\ | 30 | \\ |
31 | \noindent | 31 | \noindent |
32 | PERSONAL\_DATA \{ UID, ID, Item, Value \}\\ | 32 | PERSONAL\_DATA \{ UID, ID, Type, Priority, Value \}\\ |
33 | ID autoincrement \\ | 33 | ID autoincrement \\ |
34 | Item element \{ Company, JobTitle, Department, Office, Profession, Assistant | 34 | Type element \{ Personal, Company, University, Birthday, Aniversary, Gender, Child, Jobtitle, Department, |
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, \\ | 35 | Office, Profession, Assistant, Nickname, Spouse, Manager, Note, Email, WebPage, Phone, Fax \}\\ |
36 | DefaultEmail, Email, HomeWebpage, Homephone, HomeFax, HomeMobil \}\\ | 36 | Priority: Highest = 1, Undefined = 0\\ |
37 | \\ | 37 | \\ |
38 | \noindent | 38 | \noindent |
39 | TABLEID \{ TID, Name, DefaultRep, Version \} \\ | 39 | TABLEID \{ TID, Name, DefaultRep, Version \} \\ |
40 | TID Autoincrement \\ | 40 | TID Autoincrement \\ |
41 | The first 3 TIDs are set: 0 == datebook, 1 == addressbook, 2 == todolist.\\ | 41 | The first 3 TIDs are set: 0 == datebook, 1 == addressbook, 2 == todolist.\\ |
42 | The rest can are assigned in order of registration of the service/app. | 42 | The rest can are assigned in order of registration of the service/app. |
43 | DefaultRep is the default representation. That are fields from the table of the app itself which should be shown when crosslinked. \\ | 43 | DefaultRep is the default representation. That are fields from the table of the app itself which should be shown when crosslinked\footnote{This should be described much more detailed to be clear! (se)}. \\ |
44 | \\ | 44 | \\ |
45 | \noindent | 45 | \noindent |
46 | CROSSREF \{ TID1, UID1, Item1, TID2, UID2, Item2 \}\\ | 46 | CROSSREF \{ TID1, UID1, ID1, TID2, UID2, ID2 \}\\ |
47 | 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. \\ | 47 | ID\emph{x} defines the ``line'' in the table (grouped by UID) which should be linked. It may be empty if no special field should be addressed. \\ |
48 | Maybe add a field ``discription'' like ``verwaltet'', ``schaut an'' | 48 | Maybe add a field ``discription'' like ``verwaltet'', ``schaut an'' |
49 | 49 | \\ | |
50 | \noindent | ||
51 | 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.\\ | ||
52 | |||
53 | \noindent | ||
54 | 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! | ||
55 | |||
56 | \noindent | 50 | \noindent |
57 | 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...\\ | 51 | 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...\\ |
58 | \\ | 52 | \\ |
59 | \noindent | 53 | \noindent |
60 | ALARM \{ TID, UID, Sound, Day, Month, Year, Time \}\\ | 54 | ALARM \{ TID, UID, Sound, Day, Month, Year, Time \}\\ |
61 | TID is needed since more then one app can have alarms and UID is not necessarly unique over different apps. \\ | 55 | TID is needed since more then one app can have alarms and UID is not necessarly unique over different apps. \\ |
diff --git a/development/pim/dbpaper/tables.tex b/development/pim/dbpaper/tables.tex index ac28a47..64ae02d 100644 --- a/development/pim/dbpaper/tables.tex +++ b/development/pim/dbpaper/tables.tex | |||
@@ -125,16 +125,17 @@ Personal data: \\ | |||
125 | \underline{UID} & \underline{ID} & Ident & Value \\ | 125 | \underline{UID} & \underline{ID} & Ident & Value \\ |
126 | \hline | 126 | \hline |
127 | & & & \\ | 127 | & & & \\ |
128 | \hline | 128 | \hline |
129 | \end{tabular} | 129 | \end{tabular} |
130 | \begin{verbatim} | 130 | \begin{verbatim} |
131 | CREATE TABLE PERSONAL_DATA_CONTACT ( | 131 | CREATE TABLE PERSONAL_DATA ( |
132 | UID int NOT NULL UNIQUE, | 132 | UID int NOT NULL UNIQUE, |
133 | ID int AUTOINCREMENT, | 133 | ID int AUTOINCREMENT, |
134 | Ident text, | 134 | Ident text, |
135 | Priority int, | ||
135 | Value text, | 136 | Value text, |
136 | PRIMARY KEY(UID,ID) | 137 | PRIMARY KEY(UID,ID) |
137 | ); | 138 | ); |
138 | \end{verbatim} | 139 | \end{verbatim} |
139 | 140 | ||
140 | \noindent | 141 | \noindent |
@@ -168,16 +169,16 @@ CrossRef: \\ | |||
168 | \hline | 169 | \hline |
169 | \end{tabular} | 170 | \end{tabular} |
170 | \begin{verbatim} | 171 | \begin{verbatim} |
171 | CREATE TABLE CROSSREF ( | 172 | CREATE TABLE CROSSREF ( |
172 | TID1 int NOT NULL, | 173 | TID1 int NOT NULL, |
173 | UID1 int NOT NULL, | 174 | UID1 int NOT NULL, |
174 | Item1 int NOT NULL, | 175 | ID1 int NOT NULL, |
175 | TID2 int MOT NULL, | 176 | TID2 int NOT NULL, |
176 | UID2 int NOT NULL, | 177 | UID2 int NOT NULL, |
177 | Item2 int NOT NULL, | 178 | ID2 int NOT NULL, |
178 | PRIMARY KEY() | 179 | PRIMARY KEY() |
179 | ); | 180 | ); |
180 | \end{verbatim} | 181 | \end{verbatim} |
181 | 182 | ||
182 | 183 | ||
183 | \noindent | 184 | \noindent |