Information Security, Privacy & Compliance

sqlite

All things related to IT security, privacy and compliance.

Don’t forget to check out #InfoSecAndCompliance on Jaiku.

Some python code for extracting OS X Garmin Training Center data

Turns out that Garmin Training Center schema wasn’t all that difficult to figure out. I still don’t know how they have the timestamps encoded - doing a conversion results in a date that is ~30 years off and, if post-February, ~30 years and 1 day offf. Also, I don’t know how the longitude/latitude is encoded either. Any pointers would be most appreciated.

If you’ve got python and it’s sqlite library installed, then this script will be a half-decent example of how to pull data from “.gtc” database for each entry in the stored history. If not, have no fear, I hope to release a small app to let you extract the data on your own and I’m still thinking about that widget.

Pretty source after the jump.

Open in new window
Technorati Tags:

Posted in

| »

Garmin Training Center for OS X Data File Format

My interest in learning the secrets of the Garmin Training Center for OS X user data file was renewed upon learning that the author of TrailRunner added the ability to import GTC data directly into that app. After a cursory search for clues on the format of the “Training Center.gtc” data file, I managed to find the needed bit of knowledge after digging through one of the forums over at MotionBased. One of the posters - saeedi - suggested it was a sqlite database. After making a copy of the file, I opened it up with sqlite3, tried a “.schema” command and was greeted with the following:

CREATE TABLE ZCDABSTRACTSTEP ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZORDER INTEGER, ZBELONGSTOPLAN INTEGER, ZISCHILDOF INTEGER, ZITERATIONS INTEGER, ZNAME VARCHAR, ZISRESTING INTEGER, ZCOMPLETIONCRITERIA INTEGER, Z5_COMPLETIONCRITERIA INTEGER, ZPERFORMANCEZONE INTEGER, Z12_PERFORMANCEZONE INTEGER );

CREATE TABLE ZCDACTIVITYPROFILE ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZMAXHEARTRATE INTEGER, ZNAME VARCHAR, ZGEARWEIGHT FLOAT, ZACTIVITYTYPE INTEGER, ZDEVICE INTEGER );
CREATE TABLE ZCDCOMPLETIONCRITERIA ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZBELONGSTOLEAFSTEP INTEGER, ZTARGETCALORIES INTEGER, ZTARGETDISTANCE INTEGER, ZLIMIT INTEGER, ZLIMITTYPE INTEGER, ZTARGETTIME INTEGER );

CREATE TABLE ZCDDEVICE ( Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, ZID INTEGER, ZMAXSCHEDULEDPLANS INTEGER, ZMAXCOURSETRACKPOINTS INTEGER, ZMAXCOURSEPOINTS INTEGER, ZMAXSCHEDULEDPLANDATES INTEGER, ZPRODUCTNAME VARCHAR, ZMAXCOURSELAPS INTEGER, ZMAXUNSCHEDULEDPLANS INTEGER, ZNAME VARCHAR, ZMAXCOURSES INTEGER, ZPRODUCTID INTEGER, ZSOFTWAREVERSION INTEGER, ZOWNER INTEGER );
…(trimmed)…

It certainly looks like it’s a sqlite database. Just to verify, I tried a few SQL commands:

sqlite> select zproductname from zcddevice ;
ZPRODUCTNAME
--------------------
EDGE305
Forerunner305

sqlite>.output gtc-tree.html
sqlite>select * from zcdtreeitem

While not the prettiest schema, it’s workable. Once I get more time to dissect it a bit, I’ll post some code to extract runs/routes and maybe even take a shot at a widget.

Technorati Tags:

Posted in

| »