Eric Day

libdrizzle + SQLite hack

I’ve been wanting to try something with libdrizzle since I added the server protocol interface. After an hour or two of hacking, I came up with:

lap> sqlite3 farm
SQLite version 3.5.9
sqlite> CREATE TABLE animals (
   ...>   name  VARCHAR(255) NOT NULL,
   ...>   sound VARCHAR(255));
sqlite> INSERT INTO animals VALUES ("Cow", "Moo");
sqlite> SELECT * FROM animals;
Cow|Moo
sqlite>

Next, using a new program I hacked together in the libdrizzle examples directory:

./sqlite_server -m -v farm 54321

Now, in another terminal:

lap> mysql -h 127.0.0.1 -u root -P 54321
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: libdrizzle+SQLite

mysql> INSERT INTO animals VALUES ("Sheep", "Baa");
Query OK, 0 rows affected (0.14 sec)

mysql> SELECT * FROM animals;
+-------+-------+
| name  | sound |
+-------+-------+
| Cow   | Moo   |
| Sheep | Baa   |
+-------+-------+
2 rows in set (0.00 sec)

mysql> SELECT @@version_comment;
+------------------------------------+
| version                            |
+------------------------------------+
| SQLite Server using libdrizzle 0.1 |
+------------------------------------+
1 row in set (0.00 sec)

mysql>

There, now you make any SQLite database look like a Drizzle or MySQL server. The sqlite_server program is about 400 lines and only uses libsqlite3 and libdrizzle. For the curious, this is *not* included in the 0.1 release of libdrizzle, you’ll need to grab the latest source from Launchpad (bzr branch lp:libdrizzle). This was done quickly just for demonstration purposes, but if someone actually wants to make a project out of this I’d be happy to assist!

Now, what else can we slap a Drizzle/MySQL server interface on…? :)

7 Responses to "libdrizzle + SQLite hack"

1. Arjen Lentz says:

   February 12, 2009 at 9:25 pm

   “because I can” is the only possible excuse for this, Eric.
   Now let it go.
2. Stewart Smith says:

   February 12, 2009 at 9:27 pm

   awesome.
3. José Borges Ferreira says:

   February 12, 2009 at 9:37 pm

   How about perl bindings ?
   In no time you will have a lot of examples. At least the common usage for proxies. ( HA, Load Balance, DHT/Sharding ?!, etc)
4. William Newton says:

   February 13, 2009 at 2:04 pm

   There is one obvious answer to this: POSTGRESQL! It would be hilarious, but also somewhat useful.

   It sounds silly, but back, way back in the day in an alternative universe I was at a company and we were trying to decide what database our simple small web application should use POSTGRESQL or Mysql. There were various arguments on each side of the technical merit of the two. But the deciding vote between our two camps was that the mysql command line interface was better.
5. Eric Day says:

   February 13, 2009 at 5:31 pm

   Arjen: Don’t worry, I don’t have any further plans. ;)

   Jose: The libdrizzle server interface is just C right now, but the Python and Perl bindings are just around the corner (see the drizzle-interface project in Launchpad). PHP has full client support, but I’ve not added the server stuff yet (then you could write PHP scripts to run from PHP-CLI…ick). Patrick Galbraith is also working on the Perl DBD driver using the new libdrizzle as well.

   William: Hehe, that has crossed my mind. I’ve also thought about doing it the other way as well, adding PostreSQL protocol support into libdrizzle (so then it could speak all three). Either way, this could make for interesting sharding setups, using a libdrizzle based proxy server to route to one of Drizzle, MySQL, or PostgreSQL depending on the query and strengths of a particular DMBS.
6. Oddments » Blog Archive » Goodbye Concentric, Hello Drizzle says:

   February 27, 2009 at 1:22 am

   [...] I also plan to blog a bit more about both projects and hopefully throw together some more hacks. [...]
7. lee says:

   August 17, 2010 at 3:52 pm

   Is this such a bad idea after all?

   What would be required to make it useful for both MySQL & SQLite developers? Perhaps with some irony Oracle has gone & tacked SQLite onto BerkeleyDB w/ a drop-in replacement lib for API calls. In the interim of MySQL dropping BDB support, I think BDB has gained row-level (range-offset) locking of its tables.

   Seems to me this’d be an interesting project, the upshot being that terabytes (maybe petabytes…?) of very fast hash can be accessed via SQL. There are a handful of SQLite Server projects but I haven’t eval’d those yet.

   What would libdrizzle bring to the table? What would it take to turn this into a project, say a libdrizzle-sqlite-bdb data stack?

   Maybe put it on a ceph-btrfs-drbd stack & you’ve got a monster. Or worse yet, a Kerrighed or OpenSSI cluster =’s a linux equiv. of a sysplex… ;-)

   ?

Leave a Reply