Database Object Persistence

BlitzMax Forums/Brucey's Modules/Database Object Persistence

Brucey

(Posted 2009) [#1]

Currently I'm just messing around with some ideas, but I thought it would be fun to have the ability to take a BlitzMax Object, and almost-transparently fetch/save it to a database.

Using Reflection as a starting point, I came up with this :

Type Person Extends MDO {table="PERSON"}

	Field id:Int {generator="native" column="id"}
	Field forename:String {column="forename"}
	Field surname:String {column="surname"}

End Type

"column" tag is optional if field and column are the same name.
"generator" I just lifted from Hibernate, and doesn't mean anything yet.

Rather than processing everything "on-the-fly", we pre-process the "MDO" types, to generate some helper code. It's this code which will do all the hard work for us.

So, imagine our application. We connect to a database, open a session, and retrieve some data from it :

Local db:TDBConnection = LoadDatabase("SQLITE", "maxtest.db")
Local session:DBSession = DBSession.Create(db)

Local p:Person = Person(session.get(New Person, TInt(2)))

This will fetch and populate a Person object, for the id "2".

Here's the generated SQL :

SELECT id, forename, surname FROM PERSON WHERE id = ?

I've yet to work out a caching mechanism, which would allow it to reuse the prepared statement.
But for a first step, it's working well, and as you can see, requires far less code to be written than it would if you were to do it by hand.

Anyway, at some point, it might fit in well with the Database framework, as some extra functionality which further abstracts away the database and the model.

DavidDC

(Posted 2009) [#2]

Clever!

Brucey

(Posted 2009) [#3]

It'd be more clever were it finished, and it's only doing the "R" of CRUD.
It probably also wants something similar to htbaa's ORM stuff to allow you to create more custom queries.

Oh, and "p" is Null if no row is returned :-p

Htbaa

(Posted 2009) [#4]

This is a much nicer approach than my go at an ORM module. I did a easy SQL builder a while ago, which is on GitHub. It can generate easy SELECT/UPDATE/DELETE/INSERT queries. My ORM approach was buggy and got thrown into the bin.

RktMan

(Posted 2010) [#5]

i've been playing with a framework as well.

i didn't know you could attach metadata to the types ! that's awesome ... where is that documented ?

anyway ...

here is what i did ...

the highlights are :

*) i post an event to execute a query
*) the event passes a query string, a "type" to map the data to, a callback function to receive the data when the query is done, and the params.
*) the database code uses the column names in the query result to map to the fields of the passed in type, and creates instances and sets the field values from the result set.

now that i understand that you can do metadata on your types ... i may take yet another stab at making my stuff even more generic.

type tt_query
 field entity:ttypeid
 field query:string
 field callback(list:tlist)
 field params:tdbtype[]
end type
.
.
.
local query:tt_query = new tt_query
query.entity = ttypeid.forname("tt_apt")
query.query = "SELECT * FROM APT_HDR WHERE ID LIKE ?"
query.callback = callback_apt_list
local param:tdbstring = new tdbstring
param.setstring(gadgettext(_gg_tf_apt_id))
query.params = [param]
createevent(event_id_db , EVENT_DB_SOURCE_QUERY , 0 , 0 , 0 , 0 , query ).emit()

.
.
.

case EVENT_DB_SOURCE_QUERY

local query:tt_query = tt_query(_db_event_current.extra)
if not _db_results then
print "execute query"
_query = TDatabaseQuery.Create(_db_c)
_query.prepare(query.query)
for local param:tdbtype = eachin query.params
_query.addbindvalue(param)
next
_query.execute()
if _db_c.hasError() runtimeerror("error on query")
_db_results = createlist()
end if

if _query.nextrow() then
local record:tqueryrecord = _query.rowrecord()
local entity:object = query.entity.newobject()				
local count:int = record.count()
for local idx:int = 0 to count-1
debugstop
local columnfield:tqueryfield = record.getfield(idx)
local efield:tfield = query.entity.findfield(columnfield.name)
if efield
select efield.typeid()
case stringtypeid
efield.setstring(entity , record.getstring(idx) )
case floattypeid
efield.setfloat(entity, record.getfloat(idx))
end select
end if
next				
_db_results.addlast(entity)
else
' emit event and cleanup
print "query complete"
query.callback(_db_results)
_query = null
_db_results = null
_db_event_current = null
end if

Tony