The app database uses the following tables:
Android App
Panel Content
IOS App
In the ios App DB, all columns (except the id column) are prefixed with the initial of the table, eg the sets table has columns scode, sname, scountry, ssort etc. This is intended to make it easier to write SQL joins without ambiguous column references.
Note that SQLLite only has string and integer column types, so a date is stored as a string etc.
sets
The sets table holds the list of datasets
| column | type | use | example |
|---|---|---|---|
| id | autoincrement int | row id | |
| scode | string | set code | 'UK' |
| sname | string | name of the set | 'Mainline UK' |
| supdated | string (date) | set updated date | 02/05/2017 |
| sdownload | string (date time) | time set was last downloaded/updated | 2017-12-29T08:10:15Z |
| scountry | string | Country code for this set | 'UK' |
| snotify | string (date time) | set when "An update is available" is shown | 2017-12-29T08:10:15Z |
| ssort | string | sorting order for this set (in SQL format) | |
| sdontmatch | int (boolean) | set to 1 (true) if this set is not used in matching | 0 |
locos
The locos table contain all the book records for SpotLog with an entry for each loco or unit in the app's data. Most of the data is pulled from the server during data download or update, but some fields (eg lseen/lphoto) are updated by the app.
| column | type | use | example |
|---|---|---|---|
| id | autoincrement int | row id | |
| lnumber | string | number of loco or unit | '158880' |
| lwebid | string | global UID (in SpotLog system) for this loco | 4323 |
| laltnumber | string | Alternative numbers (eg previously carried) | "158737" |
| lform | string | Formation of unit, ie car numbers | "52737,57737" |
| ldepot | string | Depot | "SA" |
| lstatus | string | Status | |
| lname | string | Name (if carried or assigned) | |
| llivery | string | Livery | "SWM" |
| lseen | int | True if seen | 0/1 |
| lphoto | int | True if photo'd | 0/1 |
| lhaul | int | True if marked for haulage | 0/1 |
| lmod | string (date time) | date time record modified, used in syncing | |
| lpool | string | Pool | "HYHQ" |
| lowner | string | Owner | |
| lbase | string | Base | |
| llocation | string | Location | "04/03/2017 08:40:16" |
| lsyncid | string | unique id for syncing records |
classes link
record
control
The control table holds app specific values such as the appid, synckey etc
| column | type | use | example |
|---|---|---|---|
| id | autoincrement int | row id | |
| ckey | string | key for item | 'appid' |
| cval | string | value for item | 'abc87-2kjdid-dkwee' |
Some entries used include:
| ckey | cval | ||
|---|---|---|---|
| appid | the unique id of this app installation | generated when app first starts up | |
| synckey | synckey (if set) | set on settings page | |
| lastsync | date/time of last sync | ||
| SQL |
(multiple entries) Recent SQL commands used in SQL page |
classattr
.