How to use Custom Tables in Chado

This lesson describes how to programmatically create and manage Custom Tables in Chado.

Warning

You should avoid making any changes to existing Chado tables as it could make upgrades to future releases of Chado more difficult and could break functionality in Tripal that expects Chado tabes to be a certain way. Instead, use custom tables!

Creating a Custom Table

To create a new custom table, you must first define the table schema which will include the table columns, constraints, default values, and indexes. This design must then be written using the the Drupal Schema API, which is a PHP associative array with key/value pairs that specify the components of the table. The following provides an example table schema array for a custom library_stock table that is intended to link records in the stock table of Chado with records in the library table of Chado:

[
    'table' => 'library_stock',
    'fields' => [
        'library_stock_id' => [
            'type' => 'serial',
            'not null' => TRUE,
        ],
        'library_id' => [
            'type' => 'int',
            'not null' => TRUE,
        ],
        'stock_id' => [
            'type' => 'int',
            'not null' => TRUE,
        ]
    ],
    'primary key' => [
        'library_stock_id'
    ],
    'unique keys' => [
        'library_stock_c1' => [
        'library_id',
        'stock_id'
        ]
    ],
    'indexes' => [
        'name' => ['library_id', 'stock_id'],
    ],
    'foreign keys' => [
        'library' => [
            'table' => 'library',
            'columns' => [
                'library_id' => 'library_id'
            ],
        ],
        'stock' => [
            'table' => 'stock',
            'columns' => [
                'stock_id' => 'stock_id'
            ]
        ]
    ]
]

Note that in the array structure above, the columns, primary keys, foreign keys, unique keys, and indexes for the table are indicated.

The table can be created by calling the create() function of the Tripal Custom Table Service. To create the library_stock table defined in the array above we would use the following:

// Get an instance of the Custom Table service.
/** @var \Drupal\tripal_chado\Services\ChadoCustomTableManager $ct_service **/
$ct_service = \Drupal::service('tripal_chado.custom_tables');

// Use the service to create the table object by providing it a name.
/** @var \Drupal\tripal_chado\ChadoCustomTables\ChadoCustomTable $custom_table **/
$custom_table = $ct_service->create('library_stock');

The code above will create an instance of a ChadoCustomTable object but it does not yet create the table in Chado. To do that you must set the table schema in the following way:

// Provide the Schema API array defining the table structure.
$success = $custom_table->setTableSchema($schema);

In the code above, the $schema variable contains the Schema API array defined above. Calling setTableSchema() will automatically create the table in the Chado schema and return TRUE on success. If there are any errors in the structure of the $schema array or any problems creating the table, messages will be logged to Drupal, the attempt will fail and the function will return FALSE.

Locking a Custom Table

Tripal provides to the site developers an interface by which they can add custom tables. Site developers can see custom tables in the interface which allows them to delete them, rename them or alter them. If you are adding a custom table for use by your extension module and you do not want the site developers to alter it in any way, you can lock the table. Non-custom Chado tables are not available for alteration and custom tables that are necessary for the functioning of a module should not be either.

After creation of your custom table, you can lock the table from the site developers by calling the setLocked() function on the ChadoCustomTable object and passing TRUE as the only argument.

$custom_table->setLocked(TRUE);

The Table ID

Every custom table in Tripal is given a unique ID. You can retreive this ID using the getTableId() function of the ChadoCustomTable object:

$table_id = $custom_table->getTableId();

Later, you can find the ID of any custom table using its name by calling the findByName() function of the Tripal Custom Table Service:

// Get an instance of the Custom Table service.
/** @var \Drupal\tripal_chado\Services\ChadoCustomTableManager $ct_service **/
$ct_service = \Drupal::service('tripal_chado.custom_tables');

$table_id = $ct_service->findByName('library_stock');

Finding Custom Tables

After custom tables are created, you will most likely want to work with them in other parts of your module. You will need to have a ChadoCustomTable object anytime you want to work with a custom table. There are multiple ways that you can find a table and get a ChadoCustomTable object for it: by ID, by name or by iterating through all custom tables.

Load by ID

If you know the ID of the table you can get a ChadoCustomTable object by calling the loadById() function the Custom Table Service:

// Get an instance of the Custom Table service.
/** @var \Drupal\tripal_chado\Services\ChadoCustomTableManager $ct_service **/
$ct_service = \Drupal::service('tripal_chado.custom_tables');

$custom_table = $ct_service->loadById($table_id);

In the code above, the $table_id argument would store the known ID of the table. The $custom_table variable is now a ChadoCustomTable object that can be used to work with the table.

Load by Name

Custom table names should be unique. So, if you only know the table name, you can get a ChadoCustomTable object using the loadbyName() function of the Chado Custom Table Service.

// Get an instance of the Custom Table service.
/** @var \Drupal\tripal_chado\Services\ChadoCustomTableManager $ct_service **/
$ct_service = \Drupal::service('tripal_chado.custom_tables');

$custom_table = $ct_service->loadbyName('library_stock');

Getting a List of Custom Tables

If you need to get a list of existing custom tables, you can retrieve the names and IDs by calling the getTables() function of the Tripal Custom Tables Service;

// Get an instance of the Custom Table service.
/** @var \Drupal\tripal_chado\Services\ChadoCustomTableManager $ct_service **/
$ct_service = \Drupal::service('tripal_chado.custom_tables');

$custom_tables = $ct_service->getTables();

In the code above, the $custom_tables variable will be an associative array where the keys are the table IDs and the values the table names for all custom tables.

Deleting a Custom Table

You can delete a custom table by calling the delete() function on the ChadoCustomTable object. You must know the table ID or the table name to do so. Here is example code using the table name:

// Get an instance of the Custom Table service.
/** @var \Drupal\tripal_chado\Services\ChadoCustomTableManager $ct_service **/
$ct_service = \Drupal::service('tripal_chado.custom_tables');

$custom_table = $ct_service->loadbyName('library_stock');
$custom_table->delete();

Changing a Custom Table

Suppose you have created a custom table for your module and released the module for others to use. Later you recognize you need to make changes to the custom table for improved functionality. To make changes to the table occur seamlessly for everyone who uses your module, you should create an update hook function in your module’s .install file. Within the update hook function you should perform the following:

  • Create a new version of the table.

  • Copy the data from the old table.

  • Delete the old table.

  • Update your module to use the new table.

Then, when your module is upgraded on a Drupal site to the next version, the table changes will happen automatically.

Using the Custom Table

After the custom table has been created you can use it the same as any other table in Chado. You can find examples for interacting with Chado tables in the Tripal DBX: Generic cross database support for Drupal.