Introduction

Innomatic provides database handling capabilities with the \Innomatic\Dataaccess\DataAccess* classes, with a driver for each supported database type (e.g. MySQL, PostgreSQL).

After Innomatic bootstrap you can access root and current tenant database with the following methods:

$container = \Innomatic\Core\InnomaticContainer::instance('\Innomatic\Core\InnomaticContainer');
$rootDataAccess   = $container->getDataAccess();
$tenantDataAccess = $container->getCurrentTenant()->getDataAccess();

Queries are executed in plain SQL with the execute() method, e.g.:

$queryResult = $tenantDataAccess->execute(‘SELECT * FROM my_table’);

Query results

Select queries returns a \Innomatic\Dataaccess\DataAccessResult object with the query results.

Results can be iterated with the moveNext() method, while result columns can be fetched with the getFields() method: 

$query = $tenantDataAccess->execute(‘SELECT title FROM my_table’);

while (!$query->eof) {
    $title = $query->getFields(‘title’);
    echo “Title: $title\n”;
    $query->moveNext();
}

Sequences

The DataAccess abstraction layer requires to use sequences for handling auto increments.

A sequence may get different underlying implementation for each database driver but it is usually stored inside a table.

To get the next sequence number for table ‘mytable’, key ‘id’:

$id = $dataAccess->getNextSequenceValue(‘mytable_id_seq’);
$dataAccess->execute(“INSERT INTO mytable (id, title) VALUES ($id, ‘xyz’)”);

XML tables

To create root and tenant tables, you should use the roottable and domaintable components in application.xml, e.g.:

<domaintable name=”root_basic_table” file=”root_basic_table.xml" /> 
<roottable name=”tenant_basic_table” file=”tenant_basic_table.xml" />

Root tables are created during application installation, while tenant tables are created only when the application is enabled to a tenant, and only for that tenant.

A table in Innomatic is defined in a XML format inside a file in core/db/<table_name.xml>.

Innomatic supports table changes during application update if a table definition has been modified (e.g. new or deleted fields, keys, etc.). At this stage Innomatic doesn’t automatically apply all types of changes (e.g. defaults, length, etc.).

Example:

<?xml version='1.0'?>
<database>
 
  <table name=“projects">
    <field name="id"    type="integer"  notnull="1"/>
    <field name="name”  type="text"    notnull="1"/>
    <field name="description”  type="text"/>
    <field name="customerid”  type="integer"/>
    <field name="status"    type="integer"/>
    <field name="priority”  type="integer"/>
       
    <key field="id" type="unique"/>
  </table>
 
  <sequence name=“projects_id_seq"/>
 
</database>

Supported fields

Field types:

• text
• string
• boolean
• integer
• decimal
• float
• date
• datetime
• timestamp
• unixtimestamp
• time

Attributes:

• default
• notnull
• length

Keys:

• unique
• index
• primary

Next: Step 7: Localization