BKS Active Record: Tutorial / Documentation

Add-Ons
1

Hey guys,

I just started with BKS Active Record which could be awesome software, but was really hard to get going. After having figured out the basics, I would like to share it with the community. Hopefully, it saves you guys some time and increases the Active Record usage in Xojo.

This is all the documentation I could find:

After working through above sources, this is my summary to get ActiveRecord working in your project and use it:

I) Including ActiveRecord in your project:

  1. Download BKS Active Record

  2. Copy the BKS folder from the downloaded project into the project

  3. Set the constants in BKS_ActiveRecordConfig accordingly (In my case: kIncludeMySQLCommunityServer = true)

  4. Add a database in the open event of the app (adding it from the IDE did not work for me):

[code]Dim db As New MySQLCommunityServer
db.host=“my_host”
db.port=3306
db.databaseName=“my_db”
db.userName=“my_user”
db.Password=“my_password”

If not db.Connect Then
MsgBox(“Can’t connect!”)
return
End If[/code]

  1. Register the database with Active Record: (right after the above code in the open event)
		BKS_ActiveRecord.Connect(db)

  1. Add Classes to the Project (In my case: Person with properties id(Integer), Name(String) and Age(Integer).
    Important 1: For the ActiveRecord magic to work, the super class for every class which should be saved to the database needs to be BKS_ActiveRecord.Base
    Important 2: To work with ActiveRecord, every class needs to have an id(integer) property (needs to be auto increment in the database, more about the database itself later)

  2. Add a table for each class that you registered with ActiveRecord with the exact same name as your class (case sensitive!). Apparently, you could also add “tbl” as a prefix to the table name. I did not do that. (Sequel Pro is a really good free open source tool for modifying MySQL databases on Mac OS. )

  3. Add each class property as a column in the database. (Strings will be VARCHAR, Integer will be INT). Don’t forget to add an “id” column with auto increment turned on.

That’s it. Now, you can Create, Read, Update, Delete. All you classes now have the following instance methods as they all inherit from BKS_ActiveRecord.Base:

  • load
  • save
  • delete

II) Basic usage of CRUD functions:

A) How to create a new record in the database:

[code] // Create a new instance of your class
Dim newPerson As New Person
newPerson.Name = “Jane”
newPerson.Age = 18

//calling the .save method to save it to the database
newPerson.Save[/code]

B) How to load and update and delete a record from the database:

dim p As New Person
//load Person from database
If  p.Load(1) Then  // 1 is the id of the requested Person
   MsgBox ("Name should be Jane and is ===> " + p.Name) // Output will be "Jane" since it was the first record generated
End If

//Modify the instance
p.Name = "Otto"
//write the updated instance to database
p.save

//Reload record set again to check if the name changed
If  p.Load(1) Then  // 1 is the id of the requested Person
   MsgBox ("Name should be Otto and is ===> " + p.Name) // Output should now be "Otto" 
End If

// Delete the instance from database
p.delete

III) Querying the database with Active Record:
A) Get all instances
To make it easier, I used the shared method “List” from the example file provided by BKS and added it to my Person class (I don’t really understand why it’s not a shared method in BKS_ActiveRecord.Base):

[code]Public Shared Function List(sCriteria as string = “”, sOrder as string = “”) as Person()
dim aro() as Person
dim oRecord as Person
dim ty as Introspection.TypeInfo = GetTypeInfo( Person )

for each oRecord in BKS_ActiveRecord.Query(ty, sCriteria, sOrder)
aro.Append(oRecord)
next

return aro
End Function[/code]

Now I can easily call the method to get an Array of dictionaries of all records in the database and put them in a listbox:

for each oPerson as Person in Person.List
	Window1.Listbox1.AddRow( oPerson.Name, Str(oPerson.Age) )
	Window1.Listbox1.RowTag( Window1.Listbox1.LastIndex ) = oPerson
next

B) Querying the database to get specific records:
The above “List” method conveniently takes two arguments, the search term “sCriteria” and the order “sOrder”. In order to get only people by the name of John, you would add these arguments, e.g.

Dim myPersons() As Person
myPersons = Person.List("Name=""Otto""")

The argument sCriteria takes an SQL expression which would be

Name = "Otto"

In order to escape the quotes (") in Xojo you use an additional quote (") as escape:

"Name=""Otto"""

After having written that, it’s really not hard or complicated, but it took many hours figuring that out. However, there is so much more to Active Record like the validations AfterCreate(), AfterDelete(), AfterSave(), AfterUpdate() which I don’t have a clue how to access.

Maybe someone who uses ActiveRecord himself or maybe even the makers @BKS could elaborate where to go from there.

Thanks!
Ben

2

Thanks, Ben. Nice write up. Just to add to it ARGen, the utility that creates ActiveRecord and is our official ActiveRecord distribution is available at http://www.bkeeney.com/allproducts/argen/. It has a free mode that will get you the ActiveRecord classes but only convert two tables at a time. You can use this forever so converting your 100 table database is slow, but possible, with the free version.

The paid version lets you create basic UI based on your parameters. Want a list for that table? ARGen creates it. Want an edit window? ARGen creates it. We’ve used this on a couple of recent apps and has saved us many hours of setup work.

The Events (Before Create/Update/Delete and After Create/Update/Delete) aren’t used a lot. But they do make certain things very easy to implement. A great example is a LastModifiedDate field. In the Before Update event you can do a dtmLastModified = new Date. That’s it. Now, whoever, you go to save a record that gets updated. You can do similar for Created Date with Before Create event.

We’ve used the After events for an AudiTrail. So rather than having to add that code everywhere you save you simply use the event and send the entire object to your AuditTrail code.

Transactions are built-in to the product. So once you hit save anything done in the events is automatically included in that transaction. You can also create an overall transaction by calling BKS_ActiveRecord.BeginTransaction and ending it by calling the equivalent CommitTransaction or RollbackTransaction. All database errors throw an exception so you need to wrap code in a try-catch if you’re using your transactions this way.

For what it’s worth, we use table prefixes to tell us what type of table it is. T_ is a regular table. TR_ is a reference table. TX_ is a many-to-many table. Just a way to tell us something about the table before we do anything else.

What all would you like for us to do? We use it in practically every project.

3

Oh, and forgot that as part of our training video series at http://xojo.bkeeney.com/XojoTraining we’ve got a complete start-to-finish web application using nothing but ActiveRecord. That’s a subscriber series but it’s there if anyone is looking for more AR stuff.

4

I use ActiveRecord for all my applications that use a database/sqlite file. Even if the sqlite file is just cache or something.
I use the BeforeSave event to update my security hash on the rows in the database.
I have used both the BeforeUpdate and AfterUpdate events to make modifications of the objects automatically. Like the automagical logging of what the app is doing in an event log (sorta like what @Bob Keeney said above).

Using ActiveRecord, you can add all the business logic to the object (keeping it close to the data access method). This makes it also better/easier when you go to update the business logic, it is easy to update all the instances of the business logic code without having to sort through out the whole code base to make the updates.

There is many good things about ActiveRecord, being open sourced means you can add/update/modify as you see fit. And as @Bob Keeney said that his company uses it in all their applications, so they are always updating code base making it a better product.

5

Does active record and specifically ARGen change anything in how one goes about using prepared statments? Does it avoid them? Change them?

6

It uses them behind the scenes during the save so no worries there.

Depending on usage in creating the SQL you should probably use them there. For example, if you’re just doing a FindByID list method you don’t have to use PreparedStatements, but if you’re doing a FindByUserName where you pass in the string a user created you should use a PreparedStatement.

ARGen will create some examples of you. I’m pretty sure it will do some examples of this in freebie mode.

7

Thanks, I’ll look more in-depth in the morning.