Table Objects

Table objects

SQL Yoga is able to automate a number of tasks because it imports information about the database tables and table columns. SQL Query and SQL Record objects can be used out of the box without doing anything more than setting up a connection to the database.

Some of SQL Yoga's features require some configuration on your part, however:

• Adding custom properties to database tables.
• Setting search conditions using Scope objects
• Defining validation routines that are run before data is inserted into a table.
• Defining callbacks when records are created, deleted, retrieved or updated from a table using the SQL Record APIs.
• Automatically linking and unlinking records in two related tables.
• Turning SQL Record results into hierarchal arrays based on relationships between tables.
• Retrieving the related records from a table that is related to a SQL Record object's table.
• Creating table aliases that include search conditions.

Table objects unlock these features.

Creating Table Objects

While Table objects can be created via the tableobj_createObject API, it is simpler to create them in the table objects.yml file.

If your table objects.yml file defines relationship or scope objects then a corresponding table object will be created automatically. For scope objects the table that the scope is attached to will have an object created automatically. For relationships table objects will be created for tables assigned to the left table, right table, and cross-reference table keys.

In most cases you probably won't have to explicity define your table objects. If you do then you add a tables entry to the table objects.yml file.

Here is some example YAML for creating a table object that redefines the primary key that is assigned to the table in the schema:

tables:
  - name: people
    primary key: last_name

Creating Table Aliases

A more common case than redefining the primary key for a table is that of creating a table alias. A table alias allows you to create a virtual table that points to an existing table but only returns a subset of data from within the table.

For example, the following YAML creates two table aliases for a document table. One targes documents of type pdf and the other documents of type docx. Table aliases can be used anywhere a table can be used such as when defining relationships and scopes.

tables:
  - name: pdf
    type: alias
    alias for table: document
    conditions: type = 'pdf'
  - name: docx
    type: alias
    alias for table: document
    conditions: type = 'docx'

Table Behaviors

Additional table object features are unlocked using table behaviors.