Diffstat (limited to 'development/pim/dbpaper/tables.tex') (more/less context) (show whitespace changes)
-rw-r--r-- | development/pim/dbpaper/tables.tex | 303 |
1 files changed, 303 insertions, 0 deletions
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 | ||
6 | Todolist:\\ | ||
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} | ||
15 | CREATE 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 | ||
33 | Datebook: \\ | ||
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} | ||
43 | CREATE 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 | ||
54 | Recurrence:\\ | ||
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} | ||
63 | CREATE 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 | ||
77 | Addressbook: \\ | ||
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} | ||
87 | CREATE 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 | ||
98 | Postal:\\ | ||
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} | ||
107 | CREATE 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 | ||
122 | Personal 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} | ||
131 | CREATE 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 | ||
141 | TableID: \\ | ||
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} | ||
150 | CREATE 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 | ||
162 | CrossRef: \\ | ||
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} | ||
171 | CREATE 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 | ||
184 | Category: \\ | ||
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} | ||
193 | CREATE 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 | ||
204 | Files: \\ | ||
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} | ||
213 | CREATE 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 | ||
222 | Applications: \\ | ||
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} | ||
231 | CREATE 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} | ||
249 | CREATE TABLE LOCATION ( | ||
250 | UID int NOT NULL UNIQUE, | ||
251 | Name text, | ||
252 | PRIMARY KEY (UID) | ||
253 | ); | ||
254 | \end{verbatim} | ||
255 | |||
256 | |||
257 | \noindent | ||
258 | Alarm: \\ | ||
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} | ||
267 | CREATE 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 | ||
280 | Record Categories: \\ | ||
281 | no 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} | ||
290 | CREATE 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 | ||
300 | Each 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 | |||