author | harlekin <harlekin> | 2002-09-16 19:45:19 (UTC) |
---|---|---|
committer | harlekin <harlekin> | 2002-09-16 19:45:19 (UTC) |
commit | 154ef04f6d74044a750ec89c14f3521b0602e795 (patch) (side-by-side diff) | |
tree | 7db23061f91861a275beb06772e6864682a660ff /development/pim/dbpaper/tables.tex | |
parent | 3a8345528bc39a57b5d5bb50f4a69e32eae6d223 (diff) | |
download | opie-154ef04f6d74044a750ec89c14f3521b0602e795.zip opie-154ef04f6d74044a750ec89c14f3521b0602e795.tar.gz opie-154ef04f6d74044a750ec89c14f3521b0602e795.tar.bz2 |
pre draft of the db layout
Diffstat (limited to 'development/pim/dbpaper/tables.tex') (more/less context) (ignore 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 @@ +\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 + |