Developers/Library/Setup tasks/Schema update

From Aimeos documentation

< Developers‎ | Library/Setup tasks
Revision as of 07:44, 21 October 2019 by Aimeos (talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

<languages/>


<translate> Updating an existing schema or adding a new table to an existing data domain (product, catalog, attribute, etc.) is very simple and described in this article. If you need to migrate data, want to add a new data domain or rename a table, you need to create your own setup task.

Schema file

For already existing data domains, a schema file exists in the ./lib/mshoplib/setup/default/schema/ directory of the Aimeos core. It consists of the list of tables including their definitions as Doctring DBAL schema. The attribute.php file contains these lines for example:

return array(
  'table' => array(
    'mshop_attribute_type' => function ( \Doctrine\DBAL\Schema\Schema $schema ) {
 
        $table = $schema->createTable( 'mshop_attribute_type' );
 
        $table->addColumn( 'id', 'integer', array( 'autoincrement' => true ) );
        $table->addColumn( 'siteid', 'integer', array() );
        $table->addColumn( 'domain', 'string', array( 'length' => 32 ) );
        $table->addColumn( 'code', 'string', array( 'length' => 32 ) );
        $table->addColumn( 'label', 'string', array( 'length' => 255 ) );
        $table->addColumn( 'status', 'smallint', array() );
        $table->addColumn( 'mtime', 'datetime', array() );
        $table->addColumn( 'ctime', 'datetime', array() );
        $table->addColumn( 'editor', 'string', array( 'length' => 255 ) );
 
        $table->setPrimaryKey( array( 'id' ), 'pk_msattty_id' );
        $table->addUniqueIndex( array( 'siteid', 'domain', 'code' ), 'unq_msattty_sid_dom_code' );
        $table->addIndex( array( 'siteid', 'status' ), 'idx_msattty_sid_status' );
        $table->addIndex( array( 'siteid', 'label' ), 'idx_msattty_sid_label' );
        $table->addIndex( array( 'siteid', 'code' ), 'idx_msattty_sid_code' );
 
        return $schema;
    },
 
    // ...
  ),
},

The files from the core contains an anonymous function that creates the table, adds the columns and indexes. An important detail is to return the updated schema. This object will then be passed to the anonymous function for the same table in your extension.

Please remember that identifiers (table/column/index names) must not be longer then 30 characters!

Add new tables

In your own extension, you have to create a PHP file with the same name as the file for the existing data domain, i.e. attribute.php in the ./lib/custom/setup/default/schema/ directory. This file can contain one or more anonymous functions for new tables of the same data domain. They are created exactly like in the Aimeos core, e.g.

return array(
  'table' => array(
    'mshop_attribute_mytable' => function ( \Doctrine\DBAL\Schema\Schema $schema ) {
 
        $table = $schema->createTable( 'mshop_attribute_mytable' );
 
        $table->addColumn( 'id', 'integer', array( 'autoincrement' => true ) );
        $table->addColumn( 'siteid', 'integer', array() );
        $table->addColumn( 'myvalue', 'string', array( 'length' => 255, 'notnull' => false ) );
        $table->addColumn( 'mtime', 'datetime', array() );
        $table->addColumn( 'ctime', 'datetime', array() );
        $table->addColumn( 'editor', 'string', array( 'length' => 255 ) );
 
        $table->setPrimaryKey( array( 'id' ), 'pk_msattmy_id' );
        $table->addUniqueIndex( array( 'siteid', 'myvalue' ), 'unq_msattmy_sid_myval' );
 
        return $schema;
    },
  ),
);

The array key and the table name must be the same. Also, don't forget to return the schema object!

Tables for new domains

To create tables for a new data domain (not the existing ones like "attribute", "product", "service", etc.), then you need to create a setup task too. This setup task must extend from the existing MShopCreateTables (or MAdminCreateTables) setup task:

namespace Aimeos\MW\Setup\Task;
 
class TablesCreateMydomain extends TablesCreateMShop
{
	public function getPreDependencies()
	{
		return ['MShopCreateTables', 'MAdminCreateTables'];
	}
 
	public function getPostDependencies()
	{
		return [];
	}
 
	public function clean()
	{
		$this->msg( 'Cleaning base tables', 0 ); $this->status( '' );
		$ds = DIRECTORY_SEPARATOR;
 
		$files = [
			'db-<mydomain>' => 'default' . $ds . 'schema' . $ds . '<mydomain>.php'
		];
		$this->setupSchema( $files, true );
	}
 
	public function migrate()
	{
		$this->msg( 'Creating base tables', 0 ); $this->status( '' );
		$ds = DIRECTORY_SEPARATOR;
 
		$files = [
			'db-<mydomain>' => 'default' . $ds . 'schema' . $ds . '<mydomain>.php'
		];
		$this->setupSchema( $files );
	}
}

In the clean() and migrate() methods, you have to call the setupSchema() method from the parent class with the file that contains your schema definition. For clean(), the second parameter must be true so DBAL will remove old tables, columns and indexes. The $files array contains the name of the database connection as key (db is used if db-<mydomain> isn't explicitely configured) and the relative path to the schema file.

Please replace <mydomain> and rename the class by the real name of your new domain.

For more information about setup tasks, please read the rest of the documentation: Developers/Library/Setup_tasks

Modify existing tables

It's possible to modify schema definitions of core tables in extensions. This enables your extension to add additional columns or indexes to core tables or change column options like their length or type. Your changes will be applied to the tables while the setup tasks are running as long as your extension is installed.

You have to create a PHP file with the same name as the file for the existing data domain, i.e. attribute.php in the ./lib/custom/setup/default/schema/ directory - just like for adding a new table.

The important difference to adding new tables is that the schema object already contains a table definition. You can retrieve the definition by using the getTable() method of the schema object. Afterwards, you can use the methods of the table schema class to modify the table according to your needs, e.g.

return array(
  'table' => array(
    'mshop_attribute_type' => function ( \Doctrine\DBAL\Schema\Schema $schema ) {
 
        $table = $schema->getTable( 'mshop_attribute_type' );
 
        $table->addColumn(  'value', 'string', array( 'length' => 255 ) );
        $table->changeColumn(  'value', array( 'length' => 64 ) );
        $table->dropColumn(  'value' );
 
        $table->addIndex( array( 'value' ), 'idx_msattty_val' );
        $table->dropIndex( 'unq_msattmy_sid_myval' );
        $table->renameIndex( 'unq_msattmy_sid_myval', 'unq_msattmy_sid_myval_new' );
 
        return $schema;
    },
  ),
);

The array key and the table name must be the same. Also, don't forget to return the schema object!

Platform specific

Sometimes you might need to use column or index options that are only available for specific database servers, e.g. a MySQL fulltext index. While you can create those indexes without problems in your own setup task, DBAL would try to change your column or index during every setup run.

The solution is to list the identifiers of those platform specific indexes in the exclude section of the same file, e.g. in the attribute.php file located in the ./lib/custom/setup/default/schema/ directory:

return array(
  'exclude' => array(
    'idx_msindte_value',
  ),
 
  'table' => array(
    // ...
  )
);

This will leave the index with the name idx_msindte_value untouched by DBAL if its part of a table in the same data domain. </translate>