Drunken Database

Clientside Database

Internal / External storage

The database file is kept at the default location, which is on the phones main memory. Ideally it should be kept on an sd-card, and handle cards being removed / inserted.

SQL Code

online relates to wether or not the row has been processed for synchronisation with the server. It might not be uploaded if the user has desired otherwise, but it has been processed for uploading. The name ought to be refactored.

Table: Trip

A Trip is the main entity which wraps around one or more locations and mood readings(in one term Reading). An end date could be added to this table, but it might aswell be deducted from the list of readings. So could the startdate, but a Trip might start before the first reading (when the user starts the program, and puts the cell phone in his pocket), and only one Trip ought to be active at a time.

create table Trip(id INTEGER PRIMARY KEY AUTOINCREMENT, startDateTime LONG NOT NULL, active BOOLEAN NOT NULL, foreignId LONG, online BOOLEAN, name VARCHAR);

Table: Event

An Event is the abstraction of all type of events. The table is designed to contain subtypes.

An event has an Id because in theory two events can occur at the same time, which alters the dateTime useless as a unique value. A reading is a mood and location reading. In early ER-models the location and the mood was two different entities, and the mood had a datetime field. The argument was that one might stay put at the same location for a period of time, so there would be no need for the duplication. Since the precision of the GPS is not yet known, we don't know if this argument will hold.

The reason for grouping a mood and a location together is that we don't know the precision of the gps and whenever we are interested in a mood, we will also be interested in the location of the reading, so we would almost always join the two entites together anyways. By grouping them into one table we obtain a more practical result, even though we do not stay completely true to Boyce Codd.

create table Event(id INTEGER PRIMARY KEY AUTOINCREMENT, trip INTEGER NOT NULL, dateTime LONG NOT NULL, longitude DOUBLE, latitude DOUBLE, altitude LONG, mood SMALLINT, sender VARCHAR, receiver VARCHAR, message VARCHAR, online BOOLEAN);

Index: TripReading?

The index was added for practical reasons. The most common lookup on the client side will be to see a previous trip, either plotted on a map, or looking at statistical data. For this reason a select statement will most always include a WHERE clause to exclude irrelevant trips. By placing the index on DESC trips(the most resent ones will be listed first) and ASC readings, the most common lookup is supported.

The sqlite3 database has no choice between different index types, or modes like clustered / unclustered.

Create index TripEvent on Event(trip DESC, id ASC);

Assumptions not supported by SQLite

We have made several assumptions to the dataset which is not supported by SQLite, since it doesn't enforce constraints or foreignkeys.
1. A trip is unique in regards to it's starting date. No two trips from the same client have started at the same time.
2. No Reading can occur unless a trip is active (in the process of being build by the user).
3. A reading can occur without a mood, this can happen if another event than the user triggers the reading, for instance a timed reading, or an incomming sms / picture event / video event.