SQLite3 + Dropbox == Awesome

Dropbox is one of the coolest apps I have ever used and everyone knows how to use it. I can tell my clients to Dropbox me files and they don’t roll their eyes. Finally something useful out there. No, this isn’t an advert for Dropbox I will explain my excitement in a minute.

I wrote a PHP/MySQL contact manager for a client a little while ago, it was the successor to an Excel VBA app that succeeded a Psion Series 3 from 1993. So the client was very happy with the system everything working perfectly. But then he wants to be able to access his database of contacts from other computers as well (work / home / etc…). Initially I was going to build an addon that allowed him to export a file and import it on the other end. But I didn’t really like that idea because it relies too much on the client to get it right, which is not a good thing at all. (Think late night phone calls and weekends filled with a moaning client)

It took a while to realise that perhaps Dropbox could do exactly what I wanted, the thing that held me up was MySQL. Enter SQLite3. I have know about SQLite3 for a while now but I have never used it much. I knew it was a portable database that lived in a single file but have never had a use for it. Until now.

PHP also makes it really simple to convert from a MySQL database to a SQLite3 database, thought I did spend a lot of time fiddling with export tools. I ended up porting the table structure and then writing a simple MySQL query that pushed all the data into the SQLite3 database. It took me around two hours to convert the contact manager from MySQL to SQLite3 which is not bad. All the queries work without needing to change, basically you just change any mysql_* function to sqlite_* and hey presto you’re done :) .

And for the cherry on top I use Dropbox to store the SQLite3 database and then reference it from the PHP application running in XAMPP on his local machines. Then whenever he updates his database on one computer it updates them all. Also with Dropbox he can work offline as well and then sync when he connects.

Of course I know this is not the elixir to everything but for something simple like this where there is one guy using an app on several computers it is perfect.

I think maybe for my next project I will start working on building a Dropbox clone to use in my own office, maybe with a few extra features more useful to developers. Haha, no… but then again…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>