Diffstat (limited to 'development/pim/dbpaper/tables.tex') (more/less context) (ignore whitespace changes)
-rw-r--r-- | development/pim/dbpaper/tables.tex | 23 |
1 files changed, 12 insertions, 11 deletions
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 | |||
@@ -17,272 +17,273 @@ CREATE TABLE TODOLIST ( | |||
17 | DueDate text, -- sqlite does not know timestamps | 17 | DueDate text, -- sqlite does not know timestamps |
18 | StartDate text, | 18 | StartDate text, |
19 | FinishDate text, | 19 | FinishDate text, |
20 | Completed bool, | 20 | Completed bool, |
21 | Description text, | 21 | Description text, |
22 | Summary text, | 22 | Summary text, |
23 | Priority int, | 23 | Priority int, |
24 | Progress int, | 24 | Progress int, |
25 | Parent int, | 25 | Parent int, |
26 | Status int | 26 | Status int |
27 | PRIMARY KEY(UID) | 27 | PRIMARY KEY(UID) |
28 | ); | 28 | ); |
29 | \end{verbatim} | 29 | \end{verbatim} |
30 | 30 | ||
31 | 31 | ||
32 | \noindent | 32 | \noindent |
33 | Datebook: \\ | 33 | Datebook: \\ |
34 | \begin{tabular}[ht]{|l|l|l|l|} | 34 | \begin{tabular}[ht]{|l|l|l|l|} |
35 | \hline | 35 | \hline |
36 | \underline{UID} & \underline{ID} & Item & Value\\ | 36 | \underline{UID} & \underline{ID} & Item & Value\\ |
37 | \hline | 37 | \hline |
38 | & & & \\ | 38 | & & & \\ |
39 | \hline | 39 | \hline |
40 | \end{tabular} | 40 | \end{tabular} |
41 | 41 | ||
42 | \begin{verbatim} | 42 | \begin{verbatim} |
43 | CREATE TABLE DATEBOOK ( | 43 | CREATE TABLE DATEBOOK ( |
44 | UID int NOT NULL, | 44 | UID int NOT NULL, |
45 | ID int AUTOINCREMENT, | 45 | ID int AUTOINCREMENT, |
46 | Item text, | 46 | Item text, |
47 | Value text, | 47 | Value text, |
48 | PRIMARY KEY(UID,ID) | 48 | PRIMARY KEY(UID,ID) |
49 | }; | 49 | }; |
50 | \end{verbatim} | 50 | \end{verbatim} |
51 | 51 | ||
52 | 52 | ||
53 | \noindent | 53 | \noindent |
54 | Recurrence:\\ | 54 | Recurrence:\\ |
55 | \begin{tabular}[ht]{|l|l|l|l|l|l|l|l|} | 55 | \begin{tabular}[ht]{|l|l|l|l|l|l|l|l|} |
56 | \hline | 56 | \hline |
57 | \underline{UID} & \underline{TID} & RType & RWeekDays & RPosition & RFreq & RHasEndDate &REndDate \\ | 57 | \underline{UID} & \underline{TID} & RType & RWeekDays & RPosition & RFreq & RHasEndDate &REndDate \\ |
58 | \hline | 58 | \hline |
59 | & & & & & & &\\ | 59 | & & & & & & &\\ |
60 | \hline | 60 | \hline |
61 | \end{tabular} | 61 | \end{tabular} |
62 | \begin{verbatim} | 62 | \begin{verbatim} |
63 | CREATE TABLE RECURRENCE ( | 63 | CREATE TABLE RECURRENCE ( |
64 | UID int NOT NULL UNIQUE, | 64 | UID int NOT NULL UNIQUE, |
65 | TID int NOT NULL | 65 | TID int NOT NULL |
66 | RType int, | 66 | RType int, |
67 | RWeekDays int, | 67 | RWeekDays int, |
68 | RPosition int, | 68 | RPosition int, |
69 | RFreq int, | 69 | RFreq int, |
70 | RHasEndDate bool, | 70 | RHasEndDate bool, |
71 | REndDate DATETIME, | 71 | REndDate DATETIME, |
72 | PRIMARY KEY (UID) | 72 | PRIMARY KEY (UID) |
73 | ); | 73 | ); |
74 | \end{verbatim} | 74 | \end{verbatim} |
75 | 75 | ||
76 | \noindent | 76 | \noindent |
77 | Addressbook: \\ | 77 | Addressbook: \\ |
78 | \begin{tabular}[ht]{|l|l|l|l|} | 78 | \begin{tabular}[ht]{|l|l|l|l|} |
79 | \hline | 79 | \hline |
80 | \underline{UID} & \underline{ID} & Item & Value\\ | 80 | \underline{UID} & \underline{ID} & Item & Value\\ |
81 | \hline | 81 | \hline |
82 | & & & \\ | 82 | & & & \\ |
83 | \hline | 83 | \hline |
84 | \end{tabular} | 84 | \end{tabular} |
85 | 85 | ||
86 | \begin{verbatim} | 86 | \begin{verbatim} |
87 | CREATE TABLE ADDRESSBOOK ( | 87 | CREATE TABLE ADDRESSBOOK ( |
88 | UID int NOT NULL, | 88 | UID int NOT NULL, |
89 | ID int AUTOINCREMENT, | 89 | ID int AUTOINCREMENT, |
90 | Item text, | 90 | Item text, |
91 | Value text, | 91 | Value text, |
92 | PRIMARY KEY(UID,ID) | 92 | PRIMARY KEY(UID,ID) |
93 | }; | 93 | }; |
94 | \end{verbatim} | 94 | \end{verbatim} |
95 | 95 | ||
96 | 96 | ||
97 | \noindent | 97 | \noindent |
98 | Postal:\\ | 98 | Postal:\\ |
99 | \begin{tabular}[ht]{|l|l|l|l|l|l|l|l|} | 99 | \begin{tabular}[ht]{|l|l|l|l|l|l|l|l|} |
100 | \hline | 100 | \hline |
101 | \underline{UID} & \underline{TID} & \underline{Type} & Street & City & State & Zip & Country \\ | 101 | \underline{UID} & \underline{TID} & \underline{Type} & Street & City & State & Zip & Country \\ |
102 | \hline | 102 | \hline |
103 | & & & & & & &\\ | 103 | & & & & & & &\\ |
104 | \hline | 104 | \hline |
105 | \end{tabular} | 105 | \end{tabular} |
106 | \begin{verbatim} | 106 | \begin{verbatim} |
107 | CREATE TABLE POSTAL ( | 107 | CREATE TABLE POSTAL ( |
108 | UID int NOT NULL , | 108 | UID int NOT NULL , |
109 | TID int NOT NULL, | 109 | TID int NOT NULL, |
110 | Type int NOT NULL UNIQUE, -- maybe text, then more are possible | 110 | Type int NOT NULL UNIQUE, -- maybe text, then more are possible |
111 | Street text, | 111 | Street text, |
112 | City text, | 112 | City text, |
113 | State text, | 113 | State text, |
114 | Zip text, -- who knows if theres no country with letters in the zip code | 114 | Zip text, -- who knows if theres no country with letters in the zip code |
115 | Country, | 115 | Country, |
116 | PRIMARY KEY (UID, Type) | 116 | PRIMARY KEY (UID, Type) |
117 | ); | 117 | ); |
118 | \end{verbatim} | 118 | \end{verbatim} |
119 | 119 | ||
120 | 120 | ||
121 | \noindent | 121 | \noindent |
122 | Personal data: \\ | 122 | Personal data: \\ |
123 | \begin{tabular}[ht]{|l|l|l|l|} | 123 | \begin{tabular}[ht]{|l|l|l|l|} |
124 | \hline | 124 | \hline |
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 | Value text, | 135 | Priority int, |
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 |
141 | TableID: \\ | 142 | TableID: \\ |
142 | \begin{tabular}[ht]{|l|l|l|l|} | 143 | \begin{tabular}[ht]{|l|l|l|l|} |
143 | \hline | 144 | \hline |
144 | \underline{UID} & Name & DefaultRep & Version\\ | 145 | \underline{UID} & Name & DefaultRep & Version\\ |
145 | \hline | 146 | \hline |
146 | & & &\\ | 147 | & & &\\ |
147 | \hline | 148 | \hline |
148 | \end{tabular} | 149 | \end{tabular} |
149 | \begin{verbatim} | 150 | \begin{verbatim} |
150 | CREATE TABLE TABLEID ( | 151 | CREATE TABLE TABLEID ( |
151 | UID int NOT NULL UNIQUE, | 152 | UID int NOT NULL UNIQUE, |
152 | Name text, | 153 | Name text, |
153 | DefaultRep text, -- list of which which columns from apps table are supposed to be queried for default, | 154 | DefaultRep text, -- list of which which columns from apps table are supposed to be queried for default, |
154 | Version int NOT NULL | 155 | Version int NOT NULL |
155 | PRIMARY KEY (UID) | 156 | PRIMARY KEY (UID) |
156 | ); | 157 | ); |
157 | \end{verbatim} | 158 | \end{verbatim} |
158 | 159 | ||
159 | 160 | ||
160 | 161 | ||
161 | \noindent | 162 | \noindent |
162 | CrossRef: \\ | 163 | CrossRef: \\ |
163 | \begin{tabular}[ht]{|l|l|l|l|l|l|} | 164 | \begin{tabular}[ht]{|l|l|l|l|l|l|} |
164 | \hline | 165 | \hline |
165 | \underline{TID1} & \underline{UID1} & \underline{Item1} & \underline{TID2} & \underline{UID2} & \underline{Item2} \\ | 166 | \underline{TID1} & \underline{UID1} & \underline{Item1} & \underline{TID2} & \underline{UID2} & \underline{Item2} \\ |
166 | \hline | 167 | \hline |
167 | & & & & &\\ | 168 | & & & & &\\ |
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 |
184 | Category: \\ | 185 | Category: \\ |
185 | \begin{tabular}[ht]{|l|l|l|l|l|l|} | 186 | \begin{tabular}[ht]{|l|l|l|l|l|l|} |
186 | \hline | 187 | \hline |
187 | \underline{UID} & Parent & Name & ApplicationName & ForeGroundColor & BackGroundColor\\ | 188 | \underline{UID} & Parent & Name & ApplicationName & ForeGroundColor & BackGroundColor\\ |
188 | \hline | 189 | \hline |
189 | & & & & &\\ | 190 | & & & & &\\ |
190 | \hline | 191 | \hline |
191 | \end{tabular} | 192 | \end{tabular} |
192 | \begin{verbatim} | 193 | \begin{verbatim} |
193 | CREATE TABLE CATEGORY ( | 194 | CREATE TABLE CATEGORY ( |
194 | UID int NOT NULL UNIQUE, | 195 | UID int NOT NULL UNIQUE, |
195 | Name text, | 196 | Name text, |
196 | ApplicationName text, | 197 | ApplicationName text, |
197 | ForeGroundColor text,<-- maybe hex? | 198 | ForeGroundColor text, <-- maybe hex? |
198 | BackGroundColor text, | 199 | BackGroundColor text, |
199 | PRIMARY KEY(UID) | 200 | PRIMARY KEY(UID) |
200 | ); | 201 | ); |
201 | \end{verbatim} | 202 | \end{verbatim} |
202 | 203 | ||
203 | \noindent | 204 | \noindent |
204 | Files: \\ | 205 | Files: \\ |
205 | \begin{tabular}[ht]{|l|l|l|l|} | 206 | \begin{tabular}[ht]{|l|l|l|l|} |
206 | \hline | 207 | \hline |
207 | \underline{UID} & MimeType & URL \\ | 208 | \underline{UID} & MimeType & URL \\ |
208 | \hline | 209 | \hline |
209 | & &\\ | 210 | & &\\ |
210 | \hline | 211 | \hline |
211 | \end{tabular} | 212 | \end{tabular} |
212 | \begin{verbatim} | 213 | \begin{verbatim} |
213 | CREATE TABLE FILES ( | 214 | CREATE TABLE FILES ( |
214 | UID int NOT NULL UNIQUE, | 215 | UID int NOT NULL UNIQUE, |
215 | MimeType text, -- mimetype | 216 | MimeType text, -- mimetype |
216 | URL text, | 217 | URL text, |
217 | PRIMARY KEY (UID) | 218 | PRIMARY KEY (UID) |
218 | ); | 219 | ); |
219 | \end{verbatim} | 220 | \end{verbatim} |
220 | 221 | ||
221 | \noindent | 222 | \noindent |
222 | Applications: \\ | 223 | Applications: \\ |
223 | \begin{tabular}[ht]{|l|l|l|} | 224 | \begin{tabular}[ht]{|l|l|l|} |
224 | \hline | 225 | \hline |
225 | \underline{UID} & MimeType & URL \\ | 226 | \underline{UID} & MimeType & URL \\ |
226 | \hline | 227 | \hline |
227 | & & \\ | 228 | & & \\ |
228 | \hline | 229 | \hline |
229 | \end{tabular} | 230 | \end{tabular} |
230 | \begin{verbatim} | 231 | \begin{verbatim} |
231 | CREATE TABLE APPLICATION ( | 232 | CREATE TABLE APPLICATION ( |
232 | UID int NOT NULL UNIQUE, | 233 | UID int NOT NULL UNIQUE, |
233 | MimeType text, -- mimetype | 234 | MimeType text, -- mimetype |
234 | URL text, | 235 | URL text, |
235 | PRIMARY KEY (UID) | 236 | PRIMARY KEY (UID) |
236 | ); | 237 | ); |
237 | \end{verbatim} | 238 | \end{verbatim} |
238 | 239 | ||
239 | \noindent | 240 | \noindent |
240 | Location: \\ | 241 | Location: \\ |
241 | \begin{tabular}[ht]{|l|l|} | 242 | \begin{tabular}[ht]{|l|l|} |
242 | \hline | 243 | \hline |
243 | \underline{UID} & Name \\ | 244 | \underline{UID} & Name \\ |
244 | \hline | 245 | \hline |
245 | & \\ | 246 | & \\ |
246 | \hline | 247 | \hline |
247 | \end{tabular} | 248 | \end{tabular} |
248 | \begin{verbatim} | 249 | \begin{verbatim} |
249 | CREATE TABLE LOCATION ( | 250 | CREATE TABLE LOCATION ( |
250 | UID int NOT NULL UNIQUE, | 251 | UID int NOT NULL UNIQUE, |
251 | Name text, | 252 | Name text, |
252 | PRIMARY KEY (UID) | 253 | PRIMARY KEY (UID) |
253 | ); | 254 | ); |
254 | \end{verbatim} | 255 | \end{verbatim} |
255 | 256 | ||
256 | 257 | ||
257 | \noindent | 258 | \noindent |
258 | Alarm: \\ | 259 | Alarm: \\ |
259 | \begin{tabular}[ht]{|l|l|l|l|l|l|l|l|} | 260 | \begin{tabular}[ht]{|l|l|l|l|l|l|l|l|} |
260 | \hline | 261 | \hline |
261 | \underline{TID} & \underline{UID} & Sound & Day & Month & Year & Time \\ | 262 | \underline{TID} & \underline{UID} & Sound & Day & Month & Year & Time \\ |
262 | \hline | 263 | \hline |
263 | & & & & & & \\ | 264 | & & & & & & \\ |
264 | \hline | 265 | \hline |
265 | \end{tabular} | 266 | \end{tabular} |
266 | \begin{verbatim} | 267 | \begin{verbatim} |
267 | CREATE TABLE ALARM( | 268 | CREATE TABLE ALARM( |
268 | TID int NOT NULL, | 269 | TID int NOT NULL, |
269 | UID int NOT NULL, | 270 | UID int NOT NULL, |
270 | Sound text, | 271 | Sound text, |
271 | Day int MOT NULL, | 272 | Day int MOT NULL, |
272 | Month int NOT NULL, | 273 | Month int NOT NULL, |
273 | Year int NOT NULL, | 274 | Year int NOT NULL, |
274 | Time text | 275 | Time text |
275 | PRIMARY KEY() | 276 | PRIMARY KEY() |
276 | ); | 277 | ); |
277 | \end{verbatim} | 278 | \end{verbatim} |
278 | 279 | ||
279 | \noindent | 280 | \noindent |
280 | Record Categories: \\ | 281 | Record Categories: \\ |
281 | no sure if we need it, breaks the crossref table idea \\ | 282 | no sure if we need it, breaks the crossref table idea \\ |
282 | \begin{tabular}[ht]{|l|l|l|} | 283 | \begin{tabular}[ht]{|l|l|l|} |
283 | \hline | 284 | \hline |
284 | \underline{TID} & \underline{UID} & CategoryUID\\ | 285 | \underline{TID} & \underline{UID} & CategoryUID\\ |
285 | \hline | 286 | \hline |
286 | & &\\ | 287 | & &\\ |
287 | \hline | 288 | \hline |
288 | \end{tabular} | 289 | \end{tabular} |