In Revolution a behavior script extends the functionality of the object that it is attached to. SQL Yoga allows you to set a behavior for a Database object so that you can enable additional features.

Revolution stores a behavior script in a button control so open the application stack again and add a new button to it. Name the button SQL Yoga Table Objects Behavior.

Next, set the table objects behavior property of the Database object to the long id of the SQL Yoga Table Objects Behavior button and save the Database object. The 'table objects behavior' property tells SQL Yoga which button contains the behavior script that will extend the functionality of the Database object.

Now that you have assigned the table objects behavior of the Database object you can begin to write the behavior script. Edit the script of the SQL Yoga Table Objects Behavior script.

Paste the following RevTalk code into the SQL Yoga Table Objects Behavior button script and compile. If there is a mouseUp handler in the script make sure and delete it. After you insert the code we will go through the relevant parts.

----------

Copy & Paste The Following Code

----------

/**

* You can trigger this message for the database object by

* calling tableobjects_reload. Anytime you want to change the defined

* objects for the Database object modify this handler and call tableobjects_reload.

* You should then call dbobject_save.

*/

on dbobject.createTables

## Create Table Objects in order to define

## relationships

tableobj_createObject "projects"

tableobj_createObject "todo_items"

tableobj_createObject "people"

## Now that table objects exist create relationships

_CreateRelationships

end dbobject.createTables

private command _CreateRelationships

----------

## Define relationship between projects and to-do items

tblrelation_createObject "projects to todo items"

tblrelation_set "projects to todo items", "type", "one-to-many"

tblrelation_set "projects to todo items", "left table", "projects"

tblrelation_set "projects to todo items", "left table key", "id"

tblrelation_set "projects to todo items", "right table", "todo_items"

tblrelation_set "projects to todo items", "right table key", "project_id"

tblrelation_set "projects to todo items", "order by", "todo_items.sequence"

----------

----------

## Define relationship between people and to-do items

tblrelation_createObject "people to todo items"

tblrelation_set "people to todo items", "type", "many-to-many"

tblrelation_set "people to todo items", "left table", "people"

tblrelation_set "people to todo items", "left table key", "id"

tblrelation_set "people to todo items", "cross-reference table", "people_todo"

tblrelation_set "people to todo items", "cross-reference table key for left table", "people_id"

tblrelation_set "people to todo items", "cross-reference table key for right table", "todo_id"

tblrelation_set "people to todo items", "right table", "todo_items"

tblrelation_set "people to todo items", "right table key", "id"

tblrelation_set "people to todo items", "order by", "todo_items.name"

----------

end _CreateRelationships

With SQL Yoga you define all of the objects that extend functionality of your Database object in a special message named dbobject.createTables. Within this message you create the Table, Relationship, Scope and SQL Query Templates that your application will use.

Since we are going to define relationships between various tables we need to create what are called Table objects. A Table object represents a table in your database and allows you to extend the basic properties of that table.

For example, you can define additional properties for a table using a Table object. Say you have a table with first_name and last_name fields. You could define a 'name' property for the table object that returned a concatenation of those two fields.

Or perhaps you have a table with a description field that can contain a lot of text. You could define a 'short description' property that returned a shortened version of the description.

Right now we are just going to extend the projects, todo_items and peoples tables by defining the relationships between them. To do this you create a Table object for each table by calling tableobj_createObject. This command will add an object to the Database object.

A Relationship object provides SQL Yoga the information it needs to dynamically generate SQL queries based off of the relationships. For example, the SQL Yoga command sqlrecord_link can automatically link records in two tables for you. sqlrecord_getRelated will automatically retrieve records from a table related to a SQL Record object's table. SQL Query objects use relationships to automatically generate JOIN queries if needed. It's all automated and it's all available to you by defining Relationship objects.

Defining a Relationship only requires that you tell SQL Yoga the type of relationship that exists between the two tables as well as which fields in each table are used to link the two tables together. As you can see in this code you can also specify which field is used to sort the related records that are returned (1).

Now that we have finished writing the dbobject.createTables.Default message we need to trigger it. Whenever you make an update to this handler (i.e. you add/remove a Table or other object) you can call tableobjects_reload. This deletes any existing objects in your Database object and calls the handler.

If you want to confirm that dbobject.createTables was called and did what it was supposed to then you can print out the array representation of the Database object in the message box. Do this by executing:

get dbobject_getArray()

put printkeys(it)

If you search through the printout you should see an entry for relationships along with the relationships you just created. For example, projects to todo items.