Schema - Database Table Management

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::listAllTables

The 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)
2 == smalint(6)
4 == int(11)
8 == bigint(20)

TRUE|FALSE
Sqloo::DATATYPE_FLOAT int, any number sets field as double NA
Sqloo::DATATYPE_STRING

2^8 == varchar([int])
2^16 == text
2^24 == mediumtext
2^32 == longtext

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.