Achievo/Howto/Developers/Create database patches

From Achievo/ATK Wiki

When you create a new module or change an existing module it sometimes happens that you need to change the database structure. The achievo installer checks the install directory for every module that is enabled for install.inc or patch-<n>.inc files to execute. Both files have the $setup class available with lots of functions (see available functions) to change the database structure and the $db class for executing sql queries.

Contents 1 New module 2 Existing module 3 Available functions 4 FAQ

New module

For a new module you should create an install.inc in the install directory. This file most of the times contains at least 2 lines. One with an installNode call to install an atk node and one for setting the current patch level, example:

$setup->installNode('module.node');
$setup->setVersion(1);

Existing module

When you have an existing node with a database, you can add patches in the install directory. Patch files are called patch-<n>.inc where n is the latest number. When you add a patch you should always update the install files with the latest changes and update it to the latest patch version. Example of a patch file:

  $setup->addColumn("organization", "mail_address", "varchar(100)", true, "");
  $setup->addColumn("organization", "mail_zipcode", "varchar(20)", true, "");

Available functions

function addColumn($table, $col, $type, $nullable=true, $default="")

function alterColumn($table, $col, $newname, $type, $nullable=true, $default="")

function clearThemeCache()

function columnExists($table, $column,$type="")

function createView($name, $select, $withCheckOption = false)

function dropColumn($table, $col)

function dropNode($node)

function dropSequence($sequence)

function dropTable($table)

function dropView($name)

function executeSQL($sql)

function executeSQLFile($modname,$installfile)

function installNode($nodename)

function renameSequence($seq_name,$new_name)

function renameTable($old, $new)

function setVersion($version, $module="")

FAQ

Q. Why can't I use installNode when I added new fields to a node ?

A. Because the installNode isn't smart enough, it does detect which fields are missing, but there are cases that this goes wrong:

Let say we have these patches:

Patch-2: InstallNode('module.node'); Patch-3: alterColumn('node', 'field1', 'field2', 'int(11), true, 0);

For patch-2 we have added some new fields, and in patch-3 we change a fieldname. If your patch level is 1 and you run the installer, it will execute patch-2. This patch will add the new fields, but also the field we changed in patch-3 because this field is now in our node constructor. The biggest problem is that it creates field2, but drops field1!! So we lost all data of field1.

Q. In some modules I see an install.sql or patch-<n>.sql, can I also use them ?

A. Yes you can, but currently there isn't a option to create database specific patches. So when you create an .sql file it should be compatible with all databases (mysql, postgresql, etc)