This document describes the BKeeney ActiveRecord library for Xojo. We wrote ActiveRecord because we got tired of manually creating mappings between database tables and the object model. Download the project below for an unencrypted set of Xojo modules and classes.
Why Use ActiveRecord in your Xojo Application?
Once you have the Data classes setup the Xojo IDE will (mostly) autocomplete the table and field names as you type them into the Xojo Code Editor. No more fat fingering of names that generate SQL errors at runtime. It also means that the compiler knows if you’re messing up the datatype.
Additionally, to you, as a developer, it means that every database operation is abstracted from you and you need to know very little database coding. The database is just one more object in Xojo. You no longer have to stress over Recordset versus DatabaseRecord, and ActiveRecord automatically uses Transactions (you can override this naturally).
ActiveRecord has the potential of saving you a ton of time. In one of our consulting projects we estimated that using ActiveRecord with a database that had 250 tables (most of them with 50 fields each) helped us complete the project 60% faster than using traditional Xojo database methods.
What does ActiveRecord mean?
ActiveRecord 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:
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:
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 Xojo project: [wpfilebase tag=file id=129 tpl=download-button /] Updated 16 December 2014
- 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.
- 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.
- 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.
- 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.
- List – Helper method for subclasses to work with lists.
Generating Your Data Classes
If you already have a database with your tables and fields already defined, it can be time-consuming to create the data classes that ActiveRecord uses. We have ARGen, a commercial utility designed specifically to generate the Xojo classes required. It can be found at https://www.bkeeney.com/allproducts/argen/