The internal codename for the platform is Virtual Office or VO. The VO platform wraps the Sqloo library in the following three classes and initialized by Common::getSqloo. You can go look at them if you want to figure out how it is done, but that is not the scope of this document.
Common::masterPool
Common::loadTable
Common::listAllTablesThe table strucuture is defined in a list of PHP files located in the /schema directory. Each file represents a table or table relationship in the case of a N:M table.
We'll use a trimmed down version the vo_user table to show how to work with these individual tables to further extend the VO platform.
Create New Table
Start of by adding a php file to the /schema directory. The file name must be the same name as the table name. So for the table vo_user, the file name will be /schema/vo_user.php.
The following code starts a new table called vo_user.
$table = $sqloo->newTable( "vo_user" );Add Column Fields
Creating table fields is done by creating a multi-dimensional array as seen in the following example.
$table->column = array(
"customer_id" => array( // Randomly generated string that can be used as an id index for external or public facing calls like the API
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_STRING, "size" => 36 ),
Sqloo::COLUMN_ALLOW_NULL => TRUE,
Sqloo::COLUMN_DEFAULT_VALUE => NULL
),
"email" => array(
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_STRING, "size" => 128 )
),
"display_name" => array(
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_STRING, "size" => 128 ),
Sqloo::COLUMN_DEFAULT_VALUE => ""
),
"password" => array(
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_STRING, "size" => 40 )
),
"about" => array(
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_STRING, "size" => 1024*1024 ),
Sqloo::COLUMN_DEFAULT_VALUE => ""
),
"status" => array( // True == enabled, False == disabled or deleted
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_BOOLEAN ),
Sqloo::COLUMN_DEFAULT_VALUE => TRUE
),
"facebook_id" => array(
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_INTEGER ),
Sqloo::COLUMN_DEFAULT_VALUE => 0
),
"modified" => array(
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_TIME )
),
"added" => array(
Sqloo::COLUMN_DATA_TYPE => array( "type" => Sqloo::DATATYPE_TIME )
)
);Field Attribute Reference
Sqloo::COLUMN_DATA_TYPE
| type | size | unsigned |
| Sqloo::DATATYPE_BOOLEAN |
NA |
NA |
| Sqloo::DATATYPE_INTEGER |
1 == tinyint(1) |
TRUE|FALSE |
| Sqloo::DATATYPE_FLOAT | int, any number sets field as double | NA |
| Sqloo::DATATYPE_STRING |
2^8 == varchar([int]) |
NA |
| Sqloo::DATATYPE_FILE | ||
| Sqloo::DATATYPE_TIME | NA | NA |
| Sqloo::DATATYPE_OVERRIDE | set custom field type |
Sqloo::COLUMN_ALLOW_NULL
TRUE | FALSE
Define whether the field can be NULL or not.
Sqloo::COLUMN_DEFAULT_VALUE
Preset the field on row creation if a value is not passed.
Parent Attribute Reference
Create parent table relationships by creating a multi-diemensional array as seen in the following example. The key is the name of the field. The value array are the rules for the relationshiop, inlcuding INNODB inheritance (MySQL).
$table->parent = array(
"parent" => array(
Sqloo::PARENT_TABLE_NAME => "vo_user",
Sqloo::PARENT_ALLOW_NULL => TRUE,
Sqloo::PARENT_DEFAULT_VALUE => NULL,
Sqloo::PARENT_ON_DELETE => Sqloo::ACTION_SET_NULL,
Sqloo::PARENT_ON_UPDATE => Sqloo::ACTION_CASCADE
),
// To accommodate multi-site setups
"site" => array(
Sqloo::PARENT_TABLE_NAME => "cms_page",
Sqloo::PARENT_ALLOW_NULL => TRUE,
Sqloo::PARENT_DEFAULT_VALUE => NULL,
Sqloo::PARENT_ON_DELETE => Sqloo::ACTION_SET_NULL,
Sqloo::PARENT_ON_UPDATE => Sqloo::ACTION_CASCADE
)
);Sqloo::PARENT_TABLE_NAME
Name of the table you are joining. Sqloo handles the id relationships in joins all in the background.
Sqloo::PARENT_ALLOW_NULL
Whether the relationship allows null or is required.
Sqloo::PARENT_DEFAULT_VALUE
A default value. Usually not set as the relationship ID is not known.
Sqloo::PARENT_ON_DELETE
INNODB allows for relationship cleanup. This tells how to handle that. For example, sat you create a user table and a relate a user to a row in the address table. If this user relationship row in the address table is set to Sqloo::ACTION_CASCADE, then when the related user table row is deleted, so will the any address row connected to that user. Makes it nice when you don't want to have orphan rows.
Use wisely though. There may be cases where you don't want to cascade the action. A commerce order may be related to a user. If the user is deleted the order row would go away if cascade is used. This could through accounting off. In this case you may just want to use Sqloo::ACTION_SET_NULL. The user field in the order table would set the user id to NULL instead of deleting the row.
Possible values
- Sqloo::ACTION_RESTRICT
- Sqloo::ACTION_CASCADE
- Sqloo::ACTION_SET_NULL
- Sqloo::ACTION_NO_ACTION
Sqloo::PARENT_ON_UPDATE
Possible values
- Sqloo::ACTION_RESTRICT
- Sqloo::ACTION_CASCADE
- Sqloo::ACTION_SET_NULL
- Sqloo::ACTION_NO_ACTION
Index Attribute Reference
Create indexes by creating a multi-dimensional array as seen in the following example.
$table->index = array(
array(
Sqloo::INDEX_COLUMN_ARRAY => array( "email" ),
Sqloo::INDEX_UNIQUE => TRUE
),
array(
Sqloo::INDEX_COLUMN_ARRAY => array( "customer_id" )
),
array(
Sqloo::INDEX_COLUMN_ARRAY => array( "status" )
),
array(
Sqloo::INDEX_COLUMN_ARRAY => array( "account_type" )
),
array(
Sqloo::INDEX_COLUMN_ARRAY => array( "full_name" )
),
array(
Sqloo::INDEX_COLUMN_ARRAY => array( "added" )
),
array(
Sqloo::INDEX_COLUMN_ARRAY => array( "modified" )
)
);Sqloo::INDEX_COLUMN_ARRAY
Set this with an array of one or more fields to create an index to improve table search, sort, and join efficiency.
Sqloo::INDEX_UNIQUE
TRUE | FALSE
Define the index as unique. An error is thrown if an attemp to add a value is the same as a value already in the table field. Handy for an email account field if you want to make them unique.