ORM module for bah.database

I'm currently working on a ORM module for bah.database.

Currently it allows me to insert, update, delete, find (by primary key) and search for records. Which in turn are returned as an object or a TList of objects.

An example of how the module should be used (Please don't mind the naming of the types, it started as a little test, it's not a module yet):

Type TMDObjectTypes Extends TMissionDatabaseTable
	Method Init()
		Self.table = "object_types"
	End Method
End Type

'Search all TMDObjects which level_id = 1
'sort by creation_time
'return a maximum of 10 records
'starting at position 4
Local rs:TList = New TMDObjects.Search(["level_id = 1"], ["creation_time DESC"], 4, 10)
For Local record:TMDObjects = EachIn rs
	Print record.GetColumnString("id") + " - " + record.GetColumnString("name")
Next

'Find a single object by its primary key
Local r1:TMDObjects = TMDObjects(New TMDObjects.Find(8))
Print r1.GetColumnString("id") + " - " + r1.GetColumnString("name")
'Delete record from database
r1.Remove()
'Or do it like this
New TMDObjects.Remove(8)

'Lets insert a new record
Local record:TMDObjects = New TMDObjects
record.SetColumn("name", TDBString.Set("John Doe"))
record.SetColumn("age", TDBInt.Set(Rand(0, 90)))
'Save our record
record.Save()

Besides a GetColumnString() method there's also a GetColumn() method which returns the TDBType. The Save() method currently checks if the primary key has been set. If it is then it'll update the record, if not, it'll insert the record.

At the moment I have a TMissionDatabase singleton which return the databasehandle (TDatabaseConnection). But this will be extracted later on.

TMissionDatabaseTable is an abstract Type. You need to create a Init() method. In here you set the table name. By default the primaryKey is set to "id" but in the Init() method you can change this.

The Search() method:

Method Search:TList(where:String[] = Null, order:String[] = Null, limitStart:Int = 0, limitTotal:Int = -1)

You can provide an array of strings in the first parameter. This will form the WHERE part and chains it with AND. So if you do this:

record.Search(["age > 30","age < 40"]

Then the WHERE part will be: "Where age > 30 AND age < 40".

This currently limits its usage. I'm looking for something like Zend_Db_Select from the Zend Framework, written in PHP (http://framework.zend.com). It's a query builder that's really easy to use. Ideally the Search() method would only accept a query builder object. This could then be used for the Remove() method as well. As it currently only allows you to delete a fetched record, or when a primaryKey has been given it can delete the object without fetching it first.

A solution similar to Zend_Db_Select would make this module powerful, and fast as well. So if someone knows any C++ library that can do this I really would like to know. Otherwise I might just make me my own (in BlitzMax).

I'm also thinking about adding relationships. So you could do something like this:

Local r1:TMDObjects = TMDObjects(New TMDObjects.Find(8))
Local related:TList = r1.findDependentRowset("TMDOrders", "Orders")

Where related:TList is a list with TMDOrders objects. The 2nd parameter would be a rule, as with Zend_Db_Table. (Yes, it inspired me :-)).

So... Is anyone else interested in this?

Very interesting :-)

I wrote a query builder thing in Java, which lets you create custom queries - a bit like that zend db select, except there was no SQL involved - all done through methods.

Should be easy enough to build similar functionality in Blitz...

the_query = query(). ..
    from("products", ["product_id", "product_name", "price"]). ..
    where("product_name = ?", prod). ..
    where("price > ?", min_price)

Well that's the kind of API I was thinking about :-).

I think I'm going to work on a query builder first. I won't be guaranteeing support for all databases but I'm going to test with SQLite and MySQL. But if there's an existing C++ library which can easily be used with BlitzMax, please tell :-). I already found something called SQLpp but it seems to be a bit outdated.

Anyway, ideas are more than welcome. I'm going to try to keep this module small and compact with just enough functionality. Ah well, we'll see :-).

Little update, I'm almost off to bed now :-).

I started a TQueryBuilder type which will allow for the easy query building. I'm planning on adding INSERT, SELECT, UPDATE and DELETE support first. Joining might come later on, as well as grouping and all that kind of stuff.

I'm a little worried though about supporting the multiple databases. When INSERTing with MySQL you don't have to specify an auto_increment primary key. But with SQLite it seems I have to. So I need to ponder about how to get that done :-).

So far, a little example:

Local query1:TQueryBuilder = New TQueryBuilder
Local query2:TQueryBuilder = New TQueryBuilder
Local query3:TQueryBuilder = New TQueryBuilder

query1.From("objects").Where("level_id = ?", TDBInt.Set(1)).OrWhere("pos_x > ?", TDBInt.Set(0)).Order("creation_time DESC").Limit(0, 10)
query2.SetType(TQueryBuilder.TYPE_UPDATE).From("objects").Set("pos_x", TDBInt.Set(200)).Where("level_id = ?", TDBInt.Set(1)).Limit(0, 1)
query3.SetType(TQueryBuilder.TYPE_INSERT).From("objects").Set("pos_x", TDBInt.Set(300)).Set("pos_y", TDBInt.Set(400))

Print query1.ToString()
Print query2.ToString()
Print query3.ToString()

Which generates these queries:

SELECT * FROM objects WHERE level_id = ? OR pos_x > ? ORDER BY creation_time DESC LIMIT 0

UPDATE objects SET pos_x = ? WHERE level_id = ? LIMIT 0

INSERT INTO objects (pos_x,pos_y)VALUES(?,?)

Generating the query and binding the values to the query is done with a special method:

query3.bindValues(sth:TDatabaseQuery)

So you just pass a TDatabaseQuery to it.

And now I really need to get some sleep :-)