ActiveRecord for Real Studio

We are Real Studio consultants.  It’s what we do and we do a LOT of projects.  If I had to put a percentage on the projects that are database driven I’d have to say that it’s above 95% for the past ten years.

Real Studio doesn’t have database binding like Visual Basic 6 but it’s not a real big deal.  If anything, the lack of binding makes the code more explicit (i.e. easier to read) and you don’t have to go hunting through control properties to find table and field names.  The Real Studio database classes are generic so it doesn’t matter, generally, what database you’re connecting to.  The drawback to the lack of binding and the generic classes is that it does lend itself to creating the same code over and over and over again.

Because of the nature of Real Studio many users tend to put their db code into the form (window) and tie it to controls.  This leads to spaghetti code with the database specific code all over the place and makes changes to your database harder.  Seth has done two presentations at ARBP conferences 2009, 2011 and introduced attendees to ActiveRecord that we’ve used for years now.

Active Record is a very simple, and limited Object Relational Model (ORM) system.  It allows us to create REALbasic classes that the IDE knows about.  It’s not exceptionally strong with the relational data, or large blobs, but it can be programmed to handle it.

In a new project we’re converting an existing Visual Basic 6 project with roughly 25 tables and several tables have over a hundred fields each.  Using conventional means it would mean having a database editor open so I can copy and paste field names all the time.  However, using ActiveRecord we created the classes (we have a utility to do this) and now the IDE knows the table and field names.  This makes coding very fast and they’re is no worrying about spelling errors and there’s no longer any issue of what the data type is because the class knows what it is.  This is nice since the compiler will pick up any many errors that may not usually find until runtime.

The client was ecstatic after the conversion since he figured that would have taken about 20 hours to convert the VB6 code into something useable in RB.  Instead, between our utility and ActiveRecord it took me less than 4 hours.  So now instead of spending all the time getting classes ready, we’re doing the real work of connecting up the UI to a set of data aware classes.

Another feature that was added was to flag the developer if a field is in the database that isn’t in the class.  How many times do you add a field to the database (or a coworker does) and you forget to hook it up.  This doesn’t happen using ActiveRecord.  You can have class properties that aren’t a field, but if you delete a field property that’s been used in the application the compiler will flag you on it and that’s very useful too.

ActiveRecord makes extensive use of Shared Methods so that all of the database code for that table is access from that class and that class only.  It has a number of methods built-in such as getting a list of rows (in array form) and finding a record by the primary key.  It’s easily extensible.

Like I said earlier, it’s not perfect.  It doesn’t handle relational data at all, but it can be modified to do so.  Large blobs can slow it down, but in the few times this has been a big deal we’ve implemented ‘lazy loading’ where we don’t load that particular field until we ask for it.

We have a single tutorial page up for it now at the main website.  We’ll eventually turn this into video tutorials and we’ll demonstrate it in more video’s.  It’s an MIT style license so feel free to use it.  If you have additions and suggestions, please don’t hesitate to contact us.

More information, and downloadable classes can be found at http://www.bkeeney.com/realbasic/activerecord

13 thoughts on “ActiveRecord for Real Studio

  1. Had a quick look, and a nice class. My DB projects are a bit more data-crunching, so are based on subclasses of a generic Database class (DBServices) that includes all the functions needed (and wrapping around the different type of database used). Still object oriented, just based on a different model of object. However, I can see where the two could potentially be combined to great effect, so may need to trade code with you 🙂

  2. Thanks. We’ve found it useful and use it so often that it just made sense to have others benefit from it.

    I think it as at Real World 2008 Geoff talked about having an ORM implementation in Real Studio. It’s such a useful thing I’m kind of disappointed it hasn’t made it into the product. I think the biggest factor is that SQLite doesn’t lend itself well to relational data (or at least didn’t at the time).

  3. Thanks Bob for sharing. I am a huge fan of Active Record. It just makes sense to have an ORM for most of DB-related stuff. Of course, there might be exceptions where you need additional flexibility… However, it should be easy to implement a “query” function to pass a custom sql query (ie with conditions).

    What’s _really_ missing though is the relational thing. I haven’t come across any project so far that doesn’t use tables that refer to others. Since it’s not that easy to do , may I ask you how you approached that issue? I might give it some thoughts and look at other ORMs. Thanks.

  4. @Alex
    I would like to add support for relational data but haven’t gotten around to it yet. Right now I manually add methods to my classes to navigate between related tables. If you take a look at the sample project you can see an example of this with the Person and Contact classes.

  5. Hi Seth!

    Thanks for your quick response. I will certainly have a look at it. But theoretically speaking, how would you approach that in a general manner? Would you add a subclass for the secondary table and then map it in the class of the primary one with a property (type of the subclass)? That could work for 1:n relations, but not for m:1 and n:m…

  6. Instead of having the secondary table as a property, I would probably use a generic intermediary class that models the relationship between the tables. So if the relationship was 1:n, the intermediary class might be called OneToMany.

  7. NOW that is an interesting thought. I am looking forward to seeing an implementation in the future 🙂 keep up the good work!

  8. I’m having several issues with ActiveRecord so far. I tried to implement it in a very simple project and failed.

    Does the ID property of an object need to be an integer? Do I have to map primary keys somewhere?

    Thanks and best,
    Alex

  9. Active record tables need to have an integer primary key. As long as the field is set to primary key ActiveRecord should be able to map it. What database are you using?

  10. Hi Seth,

    It’s Postgresql. And the primary key is a field of type text, so this might be the issue.

  11. @Alex
    Yeah, that would do it. Without an integer ID that automatically increments, ActiveRecord doesn’t have any way of knowing if the record needs to be inserted or updated.

    You can always add an integer primary key and then make your old key into a field with a unique constraint.

  12. @Seth

    By the way, wouldn’t it make sense for MySQL to add support for general encoding preference? So we just had to set it once and then I/O gets encoded accordingly?

Comments are closed.