Table Behaviors

Table Behaviors

Each table in your database is represented by a Table object in SQL Yoga. You can add to the table object by creating a table behavior for a table.

• Add additional properties
• Add code that is run before or after create, update, and delete operations for records in the table.
• Add validation checks that are run before creating or updating a record in the database

Important: Table behaviors only work when you using sqlrecord_* API calls. sqlquery_* calls cannot access additional properties nor will they trigger callbacks.

Creating a table behavior stack

To define a table behavior you create a stack (script only stacks are appropriate here) using the following naming convention:

[TABLE_NAME] table behavior

For example, if you have a table named person then you would create a stack named person table behavior. If you are using Levure then you can simply create a file in the app/behaviors folder and add the following to the first line:

script "person table behavior"

SQL Yoga will look for a table behavior stack to send messages to and if you request a table object property that is not an existing column in the table.

Adding a New Property

To add a new property to a table object you add getters and setters to the table object behavior script.

A getter is a function defined using the following naming convention:

[TABLE_NAME]_get[PROPERTY_NAME_WITHOUT_SPACES]

A setter is a command defined as follows:

[TABLE_NAME]_set[PROPERTY_NAME_WITHOUT_SPACES]

For example, assume you have a table named person with first_name and last_name columns. When working with a SQL Record object you can get these properties using the following syntax:

sqlrecord_find "person", tID
sqlrecord_get("person", "first_name") && sqlrecord_get("person", "last_name")

Let's synthesize a new "full name" property for each person. We can do that by defining a function in the person table behavior stack script:

function person_getFullName @xRecordA
  local tName

  if xRecordA["first_name"] is not empty then
    put xRecordA["first_name"] into tName
  end if

  if tName is not empty and xRecordA["last_name"] is not empty then
    put space after tName
  end if
  put xRecordA["last_name"] after tName

  return tName
end person_getFullName

With the addition of this function the following code will now print out the full name:

sqlrecord_find "person", tID
sqlrecord_get("person", "full name")

You can also add a setter. While adding a setter for the full name might not make sense in this case, it would look something like this:

command person_setFullName @xRecordA, pValue
  ... do something with pValue
end person_setFullName

Adding Callbacks

Callbacks can be triggered before or after a record is created, updated, or deleted. A callback is a command using the following naming convention:

[TABLE_NAME]_before[Create|Update|Delete]
[TABLE_NAME]_after[Create|Update|Delete]

For example, assume you have a table named skill with a sequence column. Each time you create a new skill it should be sequenced at the end of the existing skills. Before creating the record you can determine the correct value for the sequence column using a "before create" callback. Here is an example:

command skill_beforeCreate @xRecordA, pConnectionA
  local tQueryA, tSequence

  put sqlquery_createObject("skills", pConnectionA) into tQueryA
  sqlquery_set tQueryA, "select clause", "max(sequence)"
  sqlquery_set tQueryA, "find", "first"
  sqlquery_retrieveAsData tQueryA, tSequence
  sqlrecord_set xRecordA, "sequence", tSequence + 1

  return empty
end skill_beforeCreate

With the callback defined, you can now create a new skill record and the proper sequence will automatically be inserted. When the following code is executed the skill_beforeCreate handler will be called from the sqlrecord_create handler. The record array (xRecordA) that is passed in will have the label property set to My New Skill. The sequence column will be populated and then the record will be inserted into the database.

local tSkillA

put sqlrecord_createObject("skill") into tSkillA
sqlrecord_set tSkillA, "label", "My New Skill"
sqlrecord_create tSkillA

Adding Validation Checks

Validation checks work in the same way that callbacks work. You define a command in the table behavior stack script using a special naming convention. The command returns empty if all validation checks pass or an error message if there is a validation error. The second parameter of the command is pEventType and is either create or update. This parameter allows you more control over when you validate certain columns.

Important: The validation check occurs before other callbacks.

The naming convention for validation checks is the following:

[TABLE_NAME]_validate

Here is an example validation callback:

command person_validate @xRecordA, pEventType
  local tError

  if sqlrecord_get(xRecordA, "first_name") is empty OR sqlrecord_get(xRecordA, "last_name") is empty then
    put "missing first name or last name" into tError
  end if

  return tError
end person_validate