Experimental MongoDB PHP driver emulator for SQL backend (MoSQL)

[jm]

Hi all,

I know this is kind of stupid idea to begin with, but there was a
sudden need to make our PHP/Mongo app work with SQL databases, and
this was the best I could come up with, so here goes....

MoSQL aims to be a drop-in replacement for MongoDB drivers for cases
where a Mongo server or the client driver is not available. As the
storage backend, it can use almost any SQL database supported by PDO,
but generally we are thinking about SQLite or MySQL.

The idea is that instead of new Mongo("mongodb://localhost:27017/"),
you can use new MoSQL("sqlite://".urlencode("C:\\MYDBFOLDER\\")) and
your app "just works (tm)"

Why you might ask... The thing is, MongoDB is great, but sometimes
using it is impractical or impossible because various reasons:
• Limits on 32bit platforms restrict database size. This might be a
problem on some VPS providers, desktops and mobile devices.. (MoSQL
might have trouble with big databases too, but yeah)
• High memory and disk space usage might be a problem on desktop/
offline installations, netbooks, other mobile platforms. (if only
there was an alternative low performance/low footprint/32bit safe
storage engine for MongoDB.... one can wish)
• Cheap webhotels often only have MySQL option.
• Stubborn IT departments / politics that insist on using MySQL or
similar.
• Running a database server might be overkill for some applications
(vs. embedded SQLITE for example).

Obviously any RDBMS is sub-optimal for schema-free data, so this is
approach will not perform as well as real Mongo. But for some
applications it just might be good enough. We use it mainly with
SQLite for single-user local offline versions of our normally
multiuser-oriented Mongo apps.

• Do you have a MongoDB PHP application you want to use, but MongoDB
is not available or practical?
• Is your app is more read intensive thań write intensive?
• Does your app deal with low to moderate amount of data and/or only
few concurrent users?
• Does your app use mostly simple queries, no mapreduce, javascript
etc?

If you answered yes to all questions, MoSQL might be just what you
need.

MoSQL also includes CPMoSQL, a driver that simultaneously connects
both to MongoDB (using the standard driver) and SQL backend (using
MoSQL). It does all operations on both drivers, compares results and
gives a warning (through Firebug) if the results differ. This can be
used to test the application and verify correct behaviour.

You can check the test script (test.php) to get a better picture of
what's properly implemented. Basic finds, findone, update, save,
cursor, limit, skip operations at least. Currently MoSQL supports only
simple equality checks, and $ne, $gt, $gte, $lt, $lte, $in, $nin
operators in queries. More test cases are needed to make sure they act
just as the standard driver with various datatypes etc.. Adding
support for the rest of the query types should be relatively easy.

This driver is getting close to "good enough" for our purposes (it
works with our app, that is), and honestly I probably won't develop it
further very actively. That's why I'm posting this in such an early
alpha state of development. But if someone is willing to maintain the
code and make it a proper open source project, I would be more than
happy. There is a lot of room for improvement, someone more proficient
with SQL might find a lot better ways to handle the schemaless storage
etc..

Some drawbacks:
• Implements only a subset of the API (hopefully will get more
complete in the future)
• Is obviously slower (worst cases might be order of magnitude
slower), and might not work with larger datasets. You should simply
test whether it is fast enough for your app.
• I don't guarantee any correct behaviour or safety so use at your own
risk (however you should be able to find most bugs/differences by
testing with CPMoSQL).
• Underlying SQL schema might change in future versions.
• I know well documented code, proper exceptions and stubs for
unimplemented features would be nice, but... yeah.
Some pros:
• Obviously, you no longer need to worry about availability of MongoDB
server or even the PHP driver.
• Small databases need very little disk space, at least SQLite.
• Very simple installation in most cases. When using SQLite, you don't
need a db server running at all and PDO is standard in PHP. Just
include MoSQL.php and replace new Mongo($uri); with new MoSQL($uri);
if you are lucky, everything just works (tm)
• Should work with any database supported by PDO. Only SQLite is
tested, for now. See PDO documentation for connection URI formats and
needed drivers.
• The code is relatively simple and straightforward. I hope to keep it
that way too, and possibly add features via class extends.

So there, the code is at: http://dl.dropbox.com/u/14489569/mosql/mosql.zip

For documentation and samples, you should just refer to test.php,
where most of the functionality is used.

Any questions, suggestions, test scripts, feature requests are
welcome! Most welcome are any offers on taking this code and making it
a proper open source community project, if there is enough interest in
this kind of thing!

Cheers, JM