The FrogPad

Roastmaster Support => Support => Topic started by: Kor Kiley on February 26, 2014, 11:22:36 PM

Title: Working with the database
Post by: Kor Kiley on February 26, 2014, 11:22:36 PM
Hi Danny,

I exported my database to dropbox and am exploring it on my PC using a DBM called SQLstudio. I've also done a brief test executing a basic query with Python which was pretty straightforward.

I couldn't seem to quite figure out the timestamp data type you use for date/time data. Is this a unix epoch time? I tried using the date time function with the unixepoch switch and this got me closer then other things I tried but this year becomes 1980 something and the time is off as well.

I'm also finding the number of tables that I need to link to, even to print out a list of beans in my inventory, a bit daunting. If you have any tips for handling this I would appreciate it. I'm sure you must have a bunch of views defined to handle the associations.

Thanks Danny
Title: Re: Working with the database
Post by: Kor Kiley on February 27, 2014, 12:12:52 PM
An explanation of the schema would probably be helpful as well : ) but I don't want to take much of your time. I can always muddle through.
Title: Re: Working with the database
Post by: Danny Hall on February 27, 2014, 04:19:51 PM
Hi Kor

Apple's Core Data uses a reference date of 1 January 2001, GMT - 31 years ahead of Unix Epoch time. Times are stored in the database as a decimal value of the elapsed seconds since that reference date. You'll just need to translate that +31 years from UET.

There is only 1 many-to-many relationship in the database: between ZCUSTOMROASTERSETTING and ZROAST - handled by the Z_13ROASTS join table.

Beyond that, I've modeled several join tables to facilitate the weight or volume of a few key one-to-many relationships. They are:

Due to its portability, SQLite lacks much of the functionality of other SQL engines, so... your code will be cleaner, but using raw SQLite to select date, market name and duration of every roast that was started on Feb 27th would be

SELECT datetime(r.ZDATE, 'unixepoch', '31 YEARS', 'localtime') as 'Date',
b.ZMARKETNAME as 'Market Name',
(cast(cast(r.ZDURATION/60.0 as int) as string) || ':' || cast(ZDURATION%60 as string)) as 'Duration'
from ((ZROAST r join ZROASTEDITEM i on i.ZROAST = r.Z_PK) join ZBEAN b on i.ZBEAN = b.Z_PK)
where ZDATE >= (strftime('%s','2014-02-27 00:00:00 -05:00') - strftime('%s','2001-01-01 00:00:00'))
AND ZDATE <= (strftime('%s','2014-02-27 23:59:59 -05:00') - strftime('%s','2001-01-01 00:00:00'));

Note the locale offsets - I'm in -5

Using SQLite in Apple's Core Data, I work in predicates, which are akin to SQL, but much cleaner than this. You'd be well off defining a method to take a date and return the seconds offset from 1/1/1. Doing that, the code becomes much cleaner.

Hope this helps - let me know.
Title: Re: Working with the database
Post by: Kor Kiley on February 27, 2014, 06:50:56 PM
Thanks Danny. That's a big help! I'll forge on.