MediaFrenzy

April 24, 2007

SQLite3: Database of Champions

Filed under: Uncategorized — Allan @ 9:37 pm

I’ve spent a fair amount of time playing with SQLite in the past, and found that it is suprisingly nippy for a database that runs from a single file on your filesystem. I’ve used it succesfully both in it’s traditional form as an actual database, and in a somewhat non-traditional fashion as an in-memory database to make sure that the 1000’s of codes I was generating were unique.

I’ve written before about how Mail.app stores all of its mail in an SQLite database, and so I wasn’t really suprised to find that iGTD uses an SQLite database to store all of its data as well.

On my machine, the data files lives at /Users/allank/Library/Application Support/iGTD/iGTD.sql. The database schema looks like this:


CREATE TABLE ZCONTEXT ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZSPECIALINDEX INTEGER, ZNAME VARCHAR, ZISINBOX INTEGER, ZSYNCHRONIZABLE INTEGER );
CREATE TABLE ZPROJECT ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZNAME VARCHAR, ZDEFAULTTAGS VARCHAR, ZPATHNAME VARCHAR, ZACTIVE INTEGER, ZCONTENT VARCHAR, ZPARENTPROJECT INTEGER, ZDEFAULTCONTEXT INTEGER );
CREATE TABLE ZSMARTFOLDER ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZPREDICATEQUERY VARCHAR, ZTAGS VARCHAR, ZPREDICATETEXT VARCHAR, ZDUEDATEDAYSNUMBER INTEGER, ZINCLUDEOVERDUETASKS INTEGER, ZNAME VARCHAR, ZISALLPENDING INTEGER, ZISNEXTACTION INTEGER, ZTAGSQUERYMODE INTEGER );
CREATE TABLE ZTASK ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZNEWINPROJECT INTEGER, ZCONTACT VARCHAR, ZWAITINGFOR INTEGER, ZPRIORITY INTEGER, ZTAGS VARCHAR, ZRECURNUMBER INTEGER, ZNEXTACTION INTEGER, ZDUEDATE TIMESTAMP, ZURL VARCHAR, ZCOMPLETIONDATE TIMESTAMP, ZDONE INTEGER, ZNAME VARCHAR, ZCONTENT VARCHAR, ZNEWINCONTEXT INTEGER, ZRECURMODE INTEGER, ZMAYBE INTEGER, ZPARENTPROJECT INTEGER, ZPARENTCONTEXT INTEGER );
CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY, Z_UUID VARCHAR(255), Z_PLIST BLOB);
CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER INTEGER, Z_MAX INTEGER);
CREATE INDEX ZPROJECT_ZDEFAULTCONTEXT_INDEX ON ZPROJECT (ZDEFAULTCONTEXT);
CREATE INDEX ZPROJECT_ZPARENTPROJECT_INDEX ON ZPROJECT (ZPARENTPROJECT);
CREATE INDEX ZTASK_ZPARENTCONTEXT_INDEX ON ZTASK (ZPARENTCONTEXT);
CREATE INDEX ZTASK_ZPARENTPROJECT_INDEX ON ZTASK (ZPARENTPROJECT);

All of my contexts, projects and tasks are neatly stored for me to go poke about with. If I needed to, I could build a simple PHP web front-end for it. Not that I’d ever want to be anywhere without my machine, but still. What’s more promising is that I’ve always wanted to be able to log time to some of the tasks I have. Currently iGTD doesn’t have the functionality, but now that I can access the data this easily, I can build an external SQLite database for logging time, and use the ATTACH function to grab the current tasks out of iGTD. Sounds like a cool project to play with coding Dashboard Widgets. Watch this space!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: