GoGear title management in Sqllite

06 Jan 2011 While writing this post I noticed that I am just wrong with my assumptions on my GoGear device. Read on to learn why. I'll publish it anyway as the information should still be valid for older versions of GoGear.

I've been looking for a way to enable the bookmarks feature for audio books for my Philips GoGear SA1922. Unfortunately according to this image, which is only displayed on the german site, it's not supported for this version though with the latest firmware there's a special menu option for audio books.

While experimenting I learned a few things along the way that are quite interesting. GoGear uses Sqlite for managing all meta information of the audio files stored. I use golb to transfer music from my Linux machine which does all the magic of extracting ID3 tags and inserting all data in the database. Normally you would use golb -f _system/media/audio/MyDb in the root folder of the mounted storage device to scan all files on the device and write it to the database _system/media/audio/MyDb.

If you want to see or manipulate the data you can use the sqlite client: In the same folder call sqlite _system/media/audio/MyDb. This will open a client console similar to mysql:

flo@hank:/media/PHILIPS$ sqlite _system/media/audio/MyDb
SQLite version 2.8.17
Enter ".help" for instructions
sqlite>

To see the schema information you can issue the .schema command, which display information on all the tables and its indexes:

sqlite> .schema
CREATE TABLE albumTable( iAlbumId INTEGER PRIMARY KEY, cAlbumTitle VARCHAR(100) );
CREATE TABLE artistTable( iArtistId INTEGER PRIMARY KEY, cArtistName VARCHAR(100) );
CREATE TABLE dirTable( iDirId INTEGER PRIMARY KEY, cDirName VARCHAR(260),iParentDirId INTEGER );
CREATE TABLE genreTable( iGenreId INTEGER PRIMARY KEY, cGenreName VARCHAR(50) );
CREATE TABLE playlistTable( iPlaylistId INTEGER PRIMARY KEY,cPlaylistName VARCHAR(100), cFileName VARCHAR(260),iDirId INTEGER );
CREATE TABLE playsongTable( iPlaysongId INTEGER PRIMARY KEY,iPlaylistId INTEGER, iOrderNr INTEGER,iSongId INTEGER );
CREATE TABLE songTable ( iSongId INTEGER PRIMARY KEY,cSongTitle VARCHAR(100),iArtistId INTEGER,iAlbumId INTEGER,iTrackNr INT8,iTrackLength INT16,iNrPlayed INT16,cFileName VARCHAR(260),iDirId INTEGER,iYear INT8,iGenreId INTEGER,iBitRate INTEGER,iSampleRate INTEGER,iFileSize INTEGER,iMediaType INTEGER );
CREATE INDEX album_cAlbumTitle ON albumTable (cAlbumTitle);
CREATE INDEX artist_cArtistName ON artistTable (cArtistName);
CREATE INDEX dir_cDirName ON dirTable (cDirName);
CREATE INDEX dir_iParentDirId ON dirTable (iParentDirId);
CREATE INDEX genre_cGenreName ON genreTable (cGenreName);
CREATE INDEX playlist_cPlaylistName ON playlistTable (cPlaylistName);
CREATE INDEX playsong_iOrderNr ON playsongTable (iOrderNr);
CREATE INDEX playsong_iPlaylistId ON playsongTable (iPlaylistId);
CREATE INDEX playsong_iSongId ON playsongTable (iSongId);
CREATE INDEX song_cFileName ON songTable (cFileName);
CREATE INDEX song_cSongTitle ON songTable (cSongTitle);
CREATE INDEX song_iAlbumId ON songTable (iAlbumId);
CREATE INDEX song_iArtistId ON songTable (iArtistId);
CREATE INDEX song_iDirId ON songTable (iDirId);
CREATE INDEX song_iGenre ON songTable (iGenreId);
CREATE INDEX song_iTrackNr ON songTable (iTrackNr);

To see some of the song information you can query the songTable:

sqlite> select * from songTable limit 3;
1|CRE041 Sprachen|1|1|0|4476|0|chaosradio_express_041.mp3|28|2007|1|128|44100|71709702|1
2|Java Posse #331 - Roundup '10 - Modules|2|2|331|3783|0|JavaPosse331.mp3|28|2010|2|96|44100|45460832|1
3|CRE080 Geschichte der Typographie|1|1|0|7947|0|chaosradio_express_080.mp3|28|2008|3|128|44100|127247455|1

You can use standard sql to update the information:

sqlite> update songTable set cSongTitle = "Sprachen - Chaos Radio Express 41" where iSongId = 1;

At this point I was about to hit the publish button for the post. Luckily I tried if the update happened at all. Turned on my device: Still the old title. Rebootet the device, deleted and recreated all indexes, inspected the Golb sourcecode, found nothing. After a while it struck me: I don't need golb for my GoGear version. This device seems to extract all information from the id3 tags directly, very likely during startup. Classic fail!