zz note: object relational mapping

Let's look at the expression of a SQL object-relational-mapping pattern using the Python meta-object protocol. Examples of this include the Django database access, and Clearsilver odb.py. Using these abstractions looks something like this:

import odb

# define table
class AgentsTable(odb.Table):
  def _defineRows(self):
    self.d_addColumn("agent_id",kInteger,None,primarykey = 1,autoincrement = 1)
    self.d_addColumn("login",kVarString,200,notnull=1)
    self.d_addColumn("ticket_count",kIncInteger,None)

if __name__ == "__main__":
    # open database
    ndb = MySQLdb.connect(host = 'localhost',
                          user='username', 
                          passwd = 'password', 
                          db='testdb')
    db = Database(ndb)
    tbl = AgentsTable(db,"agents")

    # create row
    agent_row = tbl.newRow()
    agent_row.login = "foo"
    agent_row.save()

    # fetch row (must use primary key)
    try:
      get_row = tbl.fetchRow( ('agent_id', agent_row.agent_id) )
    except odb.eNoMatchingRows:
      print "this is bad, we should have found the row"

    # fetch rows (can return empty list)
    list_rows = tbl.fetchRows( ('login', "foo") )

The Python implementation of something like this rather simple, and relies on the python meta-object protocol methods __getitem__(self,key), and __setitem__(self,key,value) which allow an object to handle any field access with code.

---

The Static Typed Version - Code Generation

In this case, the database fields are not themselves dynamic. They are listed in the code as table definitions. Most static type systems can handle the type expression of this problem, and in fact, provide static type checking for field access and usage.

What we want to do in a static language is...

1. define a syntax for database schema
2. use that syntax/type to describe your actual database schema
3. at compile-time, use that data to generate code that allows typesafe access to your database.

NOTE: using macros for expressing the database-schema in (1/2) is not generally the preferred solution, because it complicates understanding the syntax and error results. Programmers generally prefer to express these things in normal program syntax and data-types, so it is easy to follow.

The main reason this pattern is often cumbersome in static typed languages, is that they don't make expressing these steps a fluent part of programming in the language. The most common way to handle the above pattern is to write a completely separate program to generate code and incorporate it into the build. While this has the same result, it (a) requires a ton more code; (b) is more complicated; (c) is potentially different each time someone use this kind of code-generation pattern; (d) creates additional platform portability issues.