As I said in the
previous post, I wanted to add SQLite support to HippoHX. So far, so good: the AS3 client API is already on SVN and working. haXe and AS2 client APIs are still to come, and so does the mandatory demo. Doing demos is similar to documenting, a pain in the butt. I never find a
nice example and when I think of one, most of the times I don't go for it because fancy = long time.
However, still wanted to do something nice with the SQLite demo and luckily for me I remembered that since Firefox 3 bookmarks are stored using a local SQLite database. Reading your bookmarks from a Flash app sounds like a nice demo, right?
¬¬First, you have to find where the data is stored. A quick Google search comes up with the name of the file: places.sqlite. The first problem I found was that that file is stored in your profile folder, but the name of your profile folder is random for security reasons. So I either had to:
1) Find a cross-platform way of finding the folder for the current user.
2) Search for the file in the user folder.
#2 triggered an old idea: adding search capabilities to HippoHX's API. Back in September there was
some discussion about this in haXe's mailing list but I wasn't completely happy with Michael Oster's solution, so I've implemented my own search method based on regular expressions (File.search() is on SVN now).
Now that I can find where the file is, it's time to display its content. First problem: you cannot open the file if Firefox is opened at the same time. Sort of makes sense because at the end of the day places.sqlite is only a file, so whoever is using it needs to lock it down to guarantee consistency. Ok, so if we get an error while accessing it, let's just make a copy and access the copy instead.
Ok, we are getting somewhere. Next, what's the structure of the database? You might think that because it's an Open Source project you are going to find the schema of the database in a quick Google search right?
Wrong. I still haven't been able to find it. Ok, let's just move on and finish the damn tutorial, shall we? Tell you what, let's just open the db through the command line and dump it to a text file. Here I come: fire up the console in my Linux box:
[code lang="bash"]sqlite places.sqlite
sqlite> SELECT * FROM moz_bookmarks;
sqlite> SQL error: database is locked[/code]
Shit, forgot FF is opened. Let's close FF, start again and:
[code lang="bash"]Unable to open database "places.sqlite": file is encrypted or is not a database[/code]
Say
what now? If FF is opened I cannot access it, if FF is closed, the file is encrypted? Oh boy. Googled again until I found
this:
The windows version seems to work
Let's boot up virtual XP, install SQLite, find the file and finally I can access it on the command line! For some odd reason I still don't know, the file is encrypted on Linux but not on Windows....
Aaaaaaaaaaaaaanyway, want to get some meaningful info out of your bookmarks? Try this:
[code lang="sql"]SELECT moz_bookmarks.title,moz_places.url FROM moz_bookmarks LEFT JOIN moz_places WHERE moz_bookmarks.fk = moz_places.id AND moz_bookmarks.title != "null" AND moz_places.url LIKE "%http%";[/code]
So, what have we learnt today?
Stick to the crappy tutorials!