Gallery2:Alter Table Tutorial

This tutorial explains how Map and Entity tables can be altered in Gallery 2.

Before following this tutorial, you should have a basic understanding how to create your own tables in Gallery 2 and what Map and Entity tables are.

Related documentation: Development Documentation for Gallery 2

For New Installations - Update the Table Definition

This section covers how to update the table definition (CREATE TABLE SQL).

Map Tables

Open modules/[your module]/classes/Maps.xml (not Maps.inc)
Change the definition of your map table (e.g. by adding a new column to the column list)
Increment the "minor" table version by 1. If it was 0 before, change it to 1. If it was 3 before, change it to 4. etc.

Entity Tables

Open modules/[your module]/classes/GallerySomeEntity.class (e.g. GalleryItem.class or GalleryFileSystemEntity.class)
Change the definition of your entity, e.g. by adding new members. Don't forget to add the necessary XML in the /** */ comment tags!
Increment the "minor" table version by 1. If it was 0 before, change it to 1. If it was 3 before, change it to 4. etc.

For Upgrades - Add An Alter Table Definition

If you want to deploy your module, you'll want that users of a previous version of your module can easily upgrade to the new version of your module and part thereof is altering the existing table structure to the new definition. This is done by ALTER TABLE statements.

In Gallery 2, you don't have to write your own ALTER TABLE SQL for all the supported database systems. Instead, you need to write a XML description of the ALTER TABLE statement. Based on the XML description, Gallery will then generate the necessary SQL for all supported database systems for you.

1. Create a new file at modules/[your module]/classes/GalleryStorage/xml-src/A_[TableName]_[old-major-version].[old-minor-version].xml (A_ means ALTER)

Example: GalleryFooMap was at version (major.minor) 1.3 and you changed the definition and its new version is 1.4. The filename should be A_GalleryFooMap_1.3.xml

2. In that file, describe the changes between the new table version and the old version.

Example: In this example, we are adding a new column (column name 'renderer' of type STRING MEDIUM (128 chars)) to the GalleryItem table. The old table version was 1.1 and the new version is 1.2.

 <?xml version="1.0" encoding="utf-8"?>

 <!DOCTYPE change
  SYSTEM "../../../../../lib/tools/dtd/DatabaseChangeDefinition2.0.dtd">
 <change>
  <table-name>Item</table-name>
  <schema-from>
    <schema-major>1</schema-major>
    <schema-minor>1</schema-minor>
  </schema-from>
  <schema-to>
    <schema-major>1</schema-major>
    <schema-minor>2</schema-minor>
  </schema-to>
  <add>
    <column>
      <column-name>renderer</column-name>
      <column-type>STRING</column-type>
      <column-size>MEDIUM</column-size>
    </column>
  </add>
 </change>

Note: Take a look at modules/*/classes/GalleryStorage/xml-src/A_*.xml to learn by example.

You can't do everything possible with our XML abstraction of ALTER TABLE SQL and some changes need to be done in multiple steps (e.g. 2 or 3 A_..xml files).

Generate SQL

After having changed the Map / Entity definition and having created an A_[table-name]_[major].[minor].xml file for upgrades, you're now ready to generate the new SQL.

In the command line, you enter:

  cd modules/[your module]/classes/
  make

You may need to use gmake instead of make

Verify that the SQL has been generated correctly by looking at modules/[your module]/classes/GalleryStorage/schema.tpl. The CREATE TABLE statement should now reflect the new table structure and there should be a new ALTER TABLE statement too.

For Upgrades - Execute The Alter Table Code

New installations of your module should work fine at this point. The table is created with the new table structure at module installation time.

But for module upgrades from module versions with older table structures, you need to add a tiny bit of module upgrade code to instruct it when to execute the ALTER TABLE code.

1. Open modules/[your module]/module.inc

2. Add a upgrade() function if there is none already. E.g. copy code from modules/comment/module.inc to get started.

3. In the module constructor, increment the version.

Example: If it was $this->setVersion('1.1.5'); before, change it to $this->setVersion('1.2.0');

4. In the upgrade function, add a case for the previous version.

Example: If the version was 1.1.5 before and the new version is 1.2.0, add

 case '1.1.5':

5. In your upgrade case, add a configureStore() call to trigger the ALTER TABLE statement.

Example: In this example we're executing the alter table statement for GalleryFooMap from table schema version 1.1 to 1.2.

	case '1.1.5':
            global $gallery;
	    $storage =& $gallery->getStorage();
	    $ret = $storage->configureStore($this->getId(), array('GalleryFooMap:1.1'));
	    if ($ret) {
		return $ret;
	    }

	case 'end of upgrade path':

That's it!

You may want to populate / copy data in your upgrade code as well. That's up to you.

Personal tools