The app database uses the following tables:


Android App
Panel Content
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.


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


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


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    

(multiple entries)

Recent SQL commands used in SQL page