The Database Abstraction Layer (DAL)

Hello everyone,

This is the third and last article I promised in the answers to my friends who are curious to know about what I am doing right now. I am not going to repeat introductory text again and simply jumping on what the Database Abstraction Layer (DAL) after all and how it would work.

The framework is being implemented keeping scalability in mind as well as support for wide range of deployment options including clouds. Other goals are keep it simple, do it your self, keep the thirdparty dependencies minimal.

So the first obvious thing that comes in mind is the database support ranging from RDBMS to highly scalable NoSQL DBMS to OODBMS. We have our reasons to choose one or another DBMS, be it cost, performance or something else. We all know how difficult it is to change the options once we deploy the application. Yes there are ORM available that can solve this issue but most of them only supports RDBMS. I am not going to explain pros and cons of theses available ORM but will provide you some glimpse on how the proposed DAL would work and easy your way. It's up to you to evaluate it against existing solutions.

Let I first show you how the DAL API looks like:

from xyz import db

class User(db.Model):
    name = db.String(size=100, required=True)
    dob = db.Date(requied=True)
    email = db.String(size=100, required=True, unique=True)

    @db.validate('dob')
    def check_dob(self, value):         
        # if calculated age is less then 18 raise ValidationError
        pass

class Article(db.Model):
    title = db.String(size=100, required=True)
    publish_date = db.Date(default_now=True, required=True)
    text = db.Text(required=True)
    user = db.ManyToOne('User', required=True)

class Comment(db.Model):
    title = db.String(size=100, required=True)
    comment_date = db.Date(default_now=True, required=True)
    text = db.Text(required=True)
    article = db.ManyToOne('Article', required=True)
    user = db.ManyToOne('User', required=True)

    @db.validate('text')
    def check_comment(self, value):
        # check value for blog compliance
        pass

Sounds familiar, isn't it? The first import statement says `import from xyz`. Yes I still haven't decided on the name.

Next, you must define your schema as python classes derived from db.Model. The DAL is there to enforce the schema in the case of underlying backend DBMS doesn't support schema, like BigTable and some other schemaless DBMS while giving that task to the DBMS is it supports schema.

Database tables declared as subclasses of `Model` defines table properties as class members of type `Field` (db.String, db.Date, db.Text are all kind of db.Field). So if you want to publish an article with title, body and date, you would do it as shown above.

Some DBMS doesn't support constraints so validation is left as the responsibility of programmer. The DAL will take care of `required` and/or `unique` constraints as well as `referential integrity` if the DBMS doesn't support it.

Till now it just looks similar to `django` models but wait let I show you one more interesting feature of the proposed Model API, inheritance.

class A(db.Model):
    a = db.String()

class B(db.Model):
    b = db.String()

class C(A, B):
    c = db.String()

This is not supported. To keep the DAL simple, multiple inheritance (of Model) is not supported. This is allowed in `django` though. Let's see how inheritance works with DAL.

class A(db.Model):
    a = db.String()

    def do_something(self):
        pass

class B(A):
    b = db.String()

class C(B):
    c = db.String()

    def do_somethinf(self):
        super(C, self).do_something()
        # do something else

class D(A):
    d = db.String()

    def do_something(self):
        supper(D, self).do_something()
        # do something here

Here, B is derived from A, C from B and D from A but wait, this is not the case. The DAL is implemented in such a way that no matter which model you inherit from, it always inherits from the last defined derived class of the first defined parent. That is, the hierarchy would be:

A -> B -> C -> D instead of A -> B -> C and A -> D

Another interesting feature is, no matter which class you use to instantiate a model, you will have an object of the latest defined class in the hierarchy. Why this is so? Let I explain:

Suppose your application is using the above defined `User` model class like this:

user = User(**kwargs)
user.do_something()
user.save()

Where `kwargs` is a `dict` of form variables coming from an http post request.

Now if you think that `User` should have one more property `lang` but you don't want to change your running system by modifying the source code, you simply create a subclass of `User` and all the methods/members defined in that subclass will be available to the application.

class UserEx(User):
    lang = db.String(size=6, selection=[('en_EN', 'English'),
                                        ('fr_FR', 'French'),
                                        ('de_DE', 'German')])

    def do_something(self):
        super(UserEx, self).do_something()
        ...

So now if the html form has `lang` field, the above code will work without any change and still saving `lang` value. You can also change the behavior of the base class by overriding methods. This will allow you to implement highly modular applications with the proposed DAL API.

This is somewhat similar to `openerp` osv objects. If you familiar with `openerp` then you should be aware of how easy it is to extend existing data model by simply creating and osv class with `_inherits`, but as far as I know, the behavior of `openerp` orm is not fully object oriented as shown here.

Another problem of supporting almost all kind of database is query language support. RDBMS supports SQL while NoSQL DMBS might have their own query language. The DAL doesn't support SQL at all but sill it provides a way to filter the records. Let's see:

query = User.all().filter('name = :name and dob <= :dob',
                        name='some', dob='01-01-1992').order('-dob')
users = query.fetch(10)

for user in users:
    print user.name, user.dob

The above query is equivalant to:

SELECT * FROM "user"
WHERE "name" LIKE '%some%' AND "dob" <= '01-01-1992'
ORDER BY "dob" DESC LIMIT 10

The syntax is again similar to `google appengine` ORM with same restrictions. You can only perform search on single table, you can't use literal in query string but use named parameters which are bound to the provided parameters.

So this is how the DAL API would be, I will explain it with more information later. I am enabling anonymous comments for this post to see what you think about it. I will be happy to see your useful suggestions and ideas.

I will give more information on the DAL and the other components of the framework as it continues to reach to the first prototype stage. Stay tuned...

Regards