CouchQL development progressing

As I mentioned in a previous post I have been working of a library to ease the creation of map/reduce views in CouchDB.

The code is being hosted on google code and can be checked out and used now. The development is currently at a very early stage, but the fundamentals are sound.

Code such that given below will work. In this example it will return all the documents with a member ‘x’ whoes value is greater than one.

c = db.cursor()
c.execute("SELECT * FROM _ WHERE x > %s", (1, ))
for doc in c.fetchall():
     # process doc

The code is executed as a temporary view, but very high on my list is to use permanent views for much higher performance. This will be added before a first release, as will the ability to have multiple expressions anded together in the where clause.

Introducing CouchQL

CouchDB is a very exciting development in the world of databases and I’m greatly enjoying building a website which uses it. One problem is that most of the of views that I have created are extremely simple and could easily be represented using SQL. Although I wrote some code to help make life easier, creating a view such as that below is never going to be as simple as including SELECT * FROM table WHERE (status="open" OR status="accepted") AND latest AND key="xyz" directly in your code.

function (doc) {
    if((doc["status"] == "open" || doc["status"] == "accepted") && doc["latest"]) {
        emit(doc["key"], null);

The SQL above and the Javascript view function are directly equivalent, which is why I’ve started working on an extension to the Python CouchDB library, which I’ve decided to call CouchQL.

The basic strategy is going to be this. The library adds a method to the Database object, cursor which returns an object which is compatible with the standard Python database API. When executing a CouchQL query a hash is taken of the textual query and a call is made to the view couchql_hash. If the view is not found then the query is turned into Javascript, added the server and the call repeated.

One of the common mistakes with CouchDB is to treat as if it were a traditional RDBMS. CouchQL has the danger of confusing people even more by allowing users to query CouchDB as if it is an RDBMS. CouchQL is not SQL, even if it does pretend to be SQL-like. I’ve not yet decided on how much processing should be done in the library to make the query language more SQL-like. The query SELECT * FROM table WHERE x > 5 OR x < 3 cannot be directly represented as call to a CouchDB view. It can be represented as two separate calls to the same view with the results merged. Is this a good idea? I’m not sure.

Development work has only just started on this library, but I’m actively working on and hope to be able to announce something useful to the CouchDB mailing list soon.