path: root/development/pim/dbpaper/tables.tex

\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 (
  UID       int NOT NULL UNIQUE,
  ID        int AUTOINCREMENT,
  Ident     text,
  Priority  int,
  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,
  ID1        int NOT NULL,
  TID2       int NOT NULL,
  UID2       int NOT NULL,
  ID2        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