ActiveRecord for Xojo

This document describes the BKeeney ActiveRecord library for Xojo. I wrote ActiveRecord because I got tired of manually creating mappings between my database tables and my object model. Download the project below for an unencrypted set of Xojo modules and classes.

What does active record mean?

Active record is a design pattern for accessing databases from object-oriented languages. It was described by Martin Fowler in his book Patterns of Enterprise Application Architecture, although he didn’t invent the idea. You can find more details and links to other resources on Wikipedia.

The basic idea is that access to each table is wrapped by a class. The class has a property for each field in the table and it has methods to load and save from the database.

Overview of BKeeney ActiveRecord

The main way that you interact with ActiveRecord in your code is through the Base class in the BKS_ActiveRecord project. Base implements the standard Save, Load, and Delete methods and you create subclasses of it for each of your tables (see the end of this document for a complete list of methods provided by Base).

For example, if you’re using REALSQLDatabase and create a table using the following statement:

CREATE TABLE tblPerson( 
 id INTEGER PRIMARY KEY, 
 FirstName TEXT, 
 LastName TEXT, 
 DateOfBirth TEXT )

You could create a class, say Person, and set it’s super class to BKS_ActiveRecord.Base. Then you would add properties for id, FirstName, LastName, and DateOfBirth.

Connecting the model to a database

Before you can start using the active record classes to interact with your database you have to let the class model know what database to use. To connect your model to a database, use the BKS_ActiveRecord.Connect method like this:

BKS_ActiveRecord.Connect(db)

If you want to use more than one database for different parts of the your class model you can send a class to the Connect method:

BKS_ActiveRecord.Connect(GetTypeInfo(clsBankAccount), dbBankAccounts)

Now clsBankAccount and all of its subclasses will use the dbBankAccounts database while the other active records classes will use db. The database can be any of databases support by ActiveRecord, currently: REALSQLDatabase, MySQLCommunityServer, and PostgreSQLDatabase.

Creating a new record

To create a new entry in your table you could use the following code:

Dim newPerson As New Person
newPerson.FirstName = "Jane"
newPerson.LastName = "Doe"
newPerson.DateOfBirth = New Date(1971,1,1)
newPerson.Save

Base automatically maps the properties to the correct fields in the database and since the object was created new it knows that it needs to insert a record into the database.

Loading an existing record

If you have the ID of an existing record you can load it with the Load method:

Dim existingPerson As New Person
Call existingPerson.Load(1)

How to add ActiveRecord to your project

• Download the project:

  BKeeney ActiveRecord Rbp (46.2 KiB)

  Updated 01 May 2013!
  
• Copy the BKS folder from the ActiveRecord project into your project.
• Add code to your project to open your database and call the BKS_ActiveRecord.Connect method to connect your model to the database.
• Create a subclass of BKS_ActiveRecord.Base for each of your database tables and add a property for each field.
• (Optional) Register each of the subclasses with active record using the BKS_ActiveRecord.Table method. If your table and class names match or you add tbl as a prefix to your tables, then active record can find the appropriate table automatically.

BKS_ActiveRecord.Base Class

Public Methods

• Constructor – Default constructor which takes no parameters.
• Clone – Creates a copy of the object. Properties that are saved in the database are copied to the new object.
• Save – Save changes to the object. Creates a new record or updates an existing record as required.
• Delete – Deletes the record associated with the object.
• Load – Load record with the given record ID. Returns true if the record was found and false if the record was not found.
• Validate – Raises the Validate event and returns true if no errors are found.
• Clone – Returns a copy of the object. Clone copies all the properties that are saved to the database except the primary key.
• Db – Returns a reference to the database to which this instance is connected.

Properties

• ID – Returns the primary key of the record.
• IsNew – Returns true if the object has never been saved in the database.
• IsModified – Returns true if the object has been modified since it was last saved. New objects return false until at least one property is changed.

Shared Methods

• List – Helper method for subclasses to work with lists.