An important aspect of IOTK is its ability to force a standard way of writing code. I have learned that this promotes higher quality software, decreases debugging time, and makes it much easier for developers to pick up the work of others.
There are several ways I am trying to accomplish this, one of which is having committed to PHP as the language to use for all aspects of IOTK. API code, command line interface programs (CLI), even certain types of configuration files are written entirely in PHP. I want things to be consistent because I can move more quickly if I’m constantly working in a familiar, standard environment.
Until recently IOTK really consisted of 3 languages: the PHP API, the SQL used for DDL, and the PL/SQL used for DML. Each of these languages is very different requiring their own standards and knowledge of their ins and outs.
An essential part of IOTK is its Code Generator facility that is able to write a lot of the PHP and PL/SQL that is required to interact with a table. I will spend more time explaining the Code Generator another time. But because of it I found myself spending more and more time editing SQL.
So the goal in creating the Table Builder was to create a PHP interface that allows me to “describe” a table but to separate what I want from the code that produces it. That is precisely what the Table Builder does. I am now able to construct a table using code that looks like this:
co_Table::make('new_table')
->int('id', true)
->pk()
->int('parent_id')
->fk('parent_table', true)
->varchar('value', 50, true)
->date_col('date_created', true, 'sysdate')
->iot()
->seq()
->idx(array('parent_id', 'date_created'), 1);
The indentation is deliberate and I will explain each line:
- co_Table::make(‘new_table’) Makes a new table object to be used to define a table named “new_table”.
- ->int(‘id’, true) Defines a column of type integer with the name “id”. The “true” parameter indicates that the column should be set to not null.
- ->pk() co_Table keeps track of the active column – the last column that was defined. Certain co_Table functions can be called that specifically apply to the active column. pk() is one of those functions. It tells co_Table to define the primary key, in this case, as the “id” column. The indentation used for ->int() and ->pk() is used to make it visually clear that the primary key definition belongs to integer column that was just created.
- ->int(‘parent_id’) Defines a column of type integer with the name “parent_id”.
- ->fk(‘parent_table’, true) Is similar to pk() except that it defines a foreign key constraint. “parent_table” is the name of the parent table in which the foreign key can be found and true indicates that the “on delete cascade” clause of the constraint should be enabled.
- ->varchar(‘value’, 50, true) Defines a column of type varchar2 with the name “value”. It has a a maximum length of 50 characters and is defined as not null (the true).
- ->date_col(‘date_created’, true, ‘sysdate’) Defines a column of type date with the name “date_created.” The true indicates that the column is not nullable and “sysdate” is the default value to be assigned to the column.
- ->iot() Defines the entire table as an index organized table.
- ->seq() Signifies that a sequence should be created for this table. Note that co_Table will handle naming the sequence in the IOTK standard manner.
- ->idx(array(‘parent_id’, ‘date_created’), 1) Signifies that an index should be created using the columns “parent_id” and “date_created” and compressed by the first column (the 1).
Calling ->get_definition() on the resultant co_Table object will produce the exact SQL required to create the entities I have described above. You can literally cut and paste the output in SQL*Plus and it is always outputted in the IOTK standard way for DDL.
Here is the code I would normally type to create the entities described above and the output that co_Table would produce:
create table new_table(
id integer not null
constraint new_table_pk primary key,
parent_id integer default null
constraint new_table_parent_fk foreign key
references parent_table on delete cascade,
value varchar2(50) not null,
date_created date default sysdate not null)
organization index;
create sequence new_table_seq nocache;
create index new_table_prnt_id_dt_crtd_idx
on new_table (parent_id, date_created)
compress 1;
A lot less typing goes into the co_Table implementation. As mentioned previously, co_Table will handle naming entities to make sure they are Oracle compliant as well as standardized according to IOTK.
co_Table supports all of the features you would need to create tables in the IOTK framework including multi-column primary and foreign keys, check constraints, LOB columns, unique constraints, etc. In addition, it defines a number of functions for IOTK defined “data types” – ways to validate and represent data throughout the IOTK framework – like booleans, passwords, email addresses, and IOTK specific types like DataStore and Content IDs. Many of these data types include not just column definitions but constraint definitions as well for ensuring the data meets the integrity requirements set for them.
One missing piece is how you might use co_Table to actually load your SQL. It seems silly to have to create a PHP file than execute a script to create SQL that you would cut and paste into SQL*Plus. In fact, none of that happens when you’re using IOTK. One of the core facilities (and perhaps the most important part of the system) is the DB Builder. It automates the ordered tear down and rebuilding of all of the database objects defined by your application. All you have to do is create your SQL and put it in the right place and the DB Builder will handle finding and loading it or detecting that it has changed and reloading it. More on the DB Builder some time in the near future.
I have yet to deploy the Table Builder into a real product yet as it is currently the project I’m actively developing. I have high hopes for it and should have it completed in the next few days. In the mid-term I will investigate how to modify IOTK (and facilities like the Table Builder) to be able to produce either Oracle database code or, say, MySQL database code based on a configuration setting. This is a potentially powerful feature that is enabled by abstracting how the database entities are defined (described) and how they are actually written.
