April 13, 2009

Flip Video FlipShare software uses SQLite for its database engine

The Flip is a compact video camera from Pure Digital Technologies.

The FlipShare software for the Flip Video stores its information in a database. Using the file command reveals that the database format is SQLite, version 3.

$ cd ~/Movies/FlipShare\ Data/
$ file flipshare.db
flipshare.db: SQLite database (Version 3)

We can then display all the tables in the database using the sqlite3 command-line tool:

$ sqlite3 flipshare.db .tables
Contact                  ContactSetDetails        MediaElementSourceGraph
ContactInfo              ContactType              UserFolderMediaElements
ContactProvider          MediaElement             UserFolders
ContactProviderType      MediaElementHistory      Versions
ContactSet               MediaElementSource

The UserFolder table is rather simple:

$ sqlite3 flipshare.db '.schema UserFolders'
CREATE TABLE UserFolders (id integer primary key, folderName varchar(256) not null, parentId integer);

We can perform a schema dump of the UserFolder to see the contents:

$ sqlite3 flipshare.db '.dump UserFolders'
BEGIN TRANSACTION;
CREATE TABLE UserFolders (id integer primary key, folderName varchar(256) not null, parentId integer);
INSERT INTO "UserFolders" VALUES(1,'January 2009',8);
INSERT INTO "UserFolders" VALUES(2,'February 2009',8);
INSERT INTO "UserFolders" VALUES(3,'March 2009',8);
INSERT INTO "UserFolders" VALUES(4,'December 2008',8);
INSERT INTO "UserFolders" VALUES(5,'June 2008',8);
INSERT INTO "UserFolders" VALUES(6,'July 2008',8);
INSERT INTO "UserFolders" VALUES(7,'August 2008',8);
INSERT INTO "UserFolders" VALUES(8,'September 2008',8);
INSERT INTO "UserFolders" VALUES(9,'October 2008',8);
INSERT INTO "UserFolders" VALUES(10,'May 2008',8);
COMMIT;

The SQL dump of the UserFolders table matches the folders that we see within the FlipShare software:

The MediaElementSource table keeps track of the path to the video files:

$ sqlite3 flipshare.db '.schema MediaElementSource' | sed 's/[ ][ ]*/ /g'
CREATE TABLE MediaElementSource (id integer primary key, uri varchar(256) not null, mediaType int not null, dataAccessible int);

The MediaElement table stores metadata about the videos, such as the creation date and thumbnail:

$ sqlite3 flipshare.db '.schema MediaElement' | sed 's/[ ][ ]*/ /g'
CREATE TABLE MediaElement (id integer primary key, mediaType int not null, mediaOrigin int not null, mediaSourceId integer, name varchar(256) not null, CreationDate varchar(256), PreviewImagePath varchar(256), SizeInBytes integer not null, ParentFolder integer, StartTime double, EndTime double, category_id integer not null, album_id integer not null, hash_code varchar(32) not null, camcorder_serial varchar(48), width integer, height integer, duration double default 0);