Database to objects and back

General
1

Hi all,

I been off working in the M$ land with Entity Framework and I have an app I need to do in Xojo that will require a database but is not like your typical app where I need to show things in listboxes. I need to basically save and get data from the database and in the app have it represented in classes I create sort of like in Entity Framework. I’m wondering how people are doing this with Xojo?

I understand DatabaseRecord, RecordSet etc and while I can certainly code up routines myself to move data from those Xojo objects to my own classes I’m wondering if there is a cleaner way?

Example:

I have a user table in the SQL database. Assume it has ID, username, password, phone, etc.

In my app I have a UserClass and it would have properties or holders of some kind to basically match the data in the database. So I’d have ID, username, password, phone etc.

I execute a query to find a user and I get back a RecordSet with one record in it. All that I get and feel good about. But what is the best way to move the data from field types in RecordSet into my UserClass and if edits are made send them back using a DatabaseRecord?

I was trying to keep all database knowledge in a database class I have. This not only knows how to connect but has some methods to take a UserClass and put it to the database by copying the fields from the class into a DatabaseRecord. My problem is that no matter how I do it I cannot seem to keep the knowledge separate and discrete. IE if I have a method in my database class to do this it has to know a lot about UserClass. If I make UserClass responsible for translating to/from DatabaseRecord and RecordSet then it too needs to know too much about the database to fill in the fields.

I have considered that perhaps all of my classes that will hold database related stuff might have properties for a DatabaseRecord and a RecordSet so a specific class would know how to populate and extract from these properties within itself and I could allow these to be used in the actual database queries.

Another alternative I’ve considered is simply having a Dictionary in each of my classes that gives me a link between my property that holds the data and the database field name.

Anyway, lots of ideas on how to do this, not sure which one is the best. With my current scheme of keeping everything in my database class I find I’m writing a lot of code to move things between my app classes and the database. I basically for each object need a get/put routine as well a find and query routines that know how to work with that particular class. I am hoping there is an easier way.

Thanks in advance for any ideas!

2

bob keeney software - Argen and activerecord classes …
link text

3

I have created an app that will generate a class for a database table that is in SQLite. This app will create a class that can be dropped in to your project and holds all the get/set methods directly.

It has two options, one to create code for a standard Xojo app and one that will create code for a Xojo app using MBS SQL Plugin.

Download a copy here:
https://dl.dropboxusercontent.com/u/10747116/InternetUpdates/SourceCodeGenerator/SourceCodeGenerator.zip

4

I do this a lot with serialization

Here’s a basic load method:

Sub load(d as Dictionary) me.DBLoadDictionary = d Dim myProperties() as Introspection.PropertyInfo = Introspection.GetType(me).getproperties For each prop as Introspection.PropertyInfo in myProperties if not prop.CanWrite then Continue dim propName as string= prop.Name dim a() as Introspection.AttributeInfo = prop.GetAttributes For each attr as Introspection.AttributeInfo in a if attr.Name = "DBColumn" then propName = attr.Value.StringValue end Next if propName = "" then Continue If d.HasKey(propName) then if prop.PropertyType.Name = "string" and d.value(propName) = nil then prop.Value(me) = chr(0) else prop.Value(me) = d.Value(propName) end end Next End Sub

I overload the method so I can quickly load an object

Sub load(id as integer = 0) if id > 0 then dim rs as RecordSet = db.SQLSelect("SELECT * FROM `"+DBTable.sqlT+"` WHERE `Id`='"+str(id)+"' LIMIT 1") load(rs.toDictionary) end End Sub

I then for example have a subclass of DBClass called DBContact and I load it in like this:

dim myContact as new DBContact(contactId)
5

In Xojo at least, I believe the class representing the table (or an abstract entity composed of data from multiple tables) should be responsible for SQL operations related to said data. I don’t believe the class knows too much in this case. So I would have your UserClass and have all the SQL code there. If you code to the Database object and not one of its subclasses then UserClass can be portable across all SQL databases supported by Xojo. Nothing outside of UserClass should know anything about its underlying representation in SQL.

My database classes inherit from a parent class that implements common/useful functionality related to SQL, and I have an app that will take a SQL table and generate a subclass with properties and load/store methods that map between the instance properties and a RecordSet. Several Xojo developers have done something similar and have made their work public. I’ve been hesitant to make mine public, at least until I can get WCC 2.0 out of the gate.

I’m not convinced there’s a great deal of benefit from having yet another level of abstraction/indirection between a class representing an abstract entity (i.e. a user) and the database. There just seems to be more maintenance work and glue code. Again, this applies to the Xojo framework and the way Xojo compiles (i.e. Database will always be available to the linker). Other frameworks have another layer and can do interesting things as a result. But they also tend to help you manage the grunt work so there’s less glue code. And they were designed with those ‘interesting things’ in mind. (This shouldn’t be interpreted as a knock against Xojo. For all the extra functionality that can sometimes be found in .NET and Cocoa, I still find I can prototype and develop apps as fast or faster in Xojo.)

Introspection is another way to handle this, as Brock has illustrated. My only caution is that Introspection is definitely slower then directly coded mappings. It’s not going to matter for 10 or 100 or even 1,000 records. It can matter when you get into hundreds of thousands of records. (Sometimes there’s a legitimate need to load that many at once, though you generally will know this early on.) Otherwise Introspection can be a great way to avoid tedious code.

6

[quote=203697:@Simon Berridge]I have created an app that will generate a class for a database table that is in SQLite. This app will create a class that can be dropped in to your project and holds all the get/set methods directly.

It has two options, one to create code for a standard Xojo app and one that will create code for a Xojo app using MBS SQL Plugin.

Download a copy here:
https://dl.dropboxusercontent.com/u/10747116/InternetUpdates/SourceCodeGenerator/SourceCodeGenerator.zip[/quote]
seems nice but I cannot open an sqlite file with it
what is the file suffix you’re expecting for the sqlite database ?
thanks.

7

[quote=203719:@jean-yves pochez]seems nice but I cannot open an sqlite file with it
what is the file suffix you’re expecting for the sqlite database ?
thanks.[/quote]
Oops!

I think it is presently hard-coded for a ‘.db’ extension.

I will check this out and fix it but, in the meantime, copy your database file and give it a ‘db’ extension and it will work.

8

works fine with “.db” at the end
thanks.

9

[quote=203727:@jean-yves pochez]works fine with “.db” at the end
thanks.[/quote]
I have now uploaded a version that allows you to change the SQLite extension. Just open SourceCodeGenerator and it will auto-update to the latest version - thanks to Kaju Updater!

10

Thanks for the wonderful replies everyone. Still evaluating best methods. Thanks again!