ReStore Developers Manual

Developers Manual 1.20

4. Customising ReStore for Individual Databases

4.4 Maintenance and Administration

Similar to datatypes and functions, relational databases also differ in what table maintenance actions are permissible, and how they are accomplished. From ReStore's point of view, it is necessary that a table can be renamed, and that a column can be removed (dropped) from an existing table. In both cases, there are two possibilities:

the action can be accomplished with a single dedicated SQL command

the action requires a workaround, consisting of several different actions

The former is the preferred alternative, and this is the standard behaviour. However, ReStore also allows for configurable workarounds. Both possibilities are supported via the maintenanceActions instance variable of SSWSQLDialect. This holds a Dictionary mapping a Symbol naming the action to a collection of parameterised SQL statements.

Renaming a Table
The ability to rename a database table is required in order to support renaming of classes. The 'name' of this action in the maintenanceActions Dictionary is #renameTableNamed:to:. The name is parameterised like a regular message, as a guide to the parameters involved - the first parameter is the existing table name, the second is the new name (these parameters are supplied by the ReStore framework; you do not need to generate these yourself).

The dedicated SQL command for this action is ALTER TABLE <old name> RENAME TO <new name>, and is configured as follows:

maintenanceActions
        at: #renameTableNamed:to:
        put: #('ALTER TABLE %1 RENAME TO %2')

Remember that, in order to support workarounds requiring multiple SQL statements, the value in the maintenanceActions Dictionary is a collection of paramterised SQL statements - the parameters in each statement correspond to the implied 'parameters' of the name.

If your chosen database does not support ALTER TABLE...RENAME TO, then a common workaround is:

create a table with the new name, based on the existing table

delete the existing table

This behaviour can be configured as follows:

maintenanceActions
        at: #renameTableNamed:to:
        put:
            #( 'SELECT * INTO %2 FROM %1'
                'DROP TABLE %1')

You should consult your database documentation to discover the preferred way to rename a table, and use or amend one of the above configurations as required.

Removing a Column
As instance variables are removed from classes, the corresponding database column will need to be dropped from its table. As with renaming a table, there is a dedicated SQL command to handle this. The maintenanceAction name for this operation is #dropColumnNamed:fromTableNamed:leaving: - the implied parameters are the name of the column to be dropped, the table from which the column will be dropped, and finally a single String listing the other (remaining) columns in that table. This last parameter is provided as it is a requirement for the suggested workaround; however first let us consider the default configuration:

maintenanceActions
        at: #dropColumnNamed:fromTableNamed:leaving:
        put: #('ALTER TABLE %2 DROP COLUMN %1')

This uses the dedicated SQL command ALTER TABLE...DROP COLUMN. If this is not available, then a common workaround to remove a column is as follows:

create a table with a temporary name, containing the contents of the original table but without the column to be dropped

remove the original table

rename the temporary table to the name of the original table

...and the configuration for this workaround is:

maintenanceActions
        at: #dropColumnNamed:fromTableNamed:leaving:
        put:
            #( 'CREATE TABLE tmp AS SELECT %3 FROM %2'
                'DROP TABLE %2'
                'RENAME tmp TO %2')

Again, you should consult your chosen database's documentation to find out if the dedicated SQL is supported, and if not to discover the suggested workaround.

Note that, without a functioning configuration for both these maintenance actions, you will not be able to take full advantage of ReStore's ability to maintain your database schema.

Post-Login Script
Although not strictly speaking 'maintenance', the maintenanceActions Dictionary is also used to provide the ability to execute fixed set of SQL commands on login. This 'script' is stored at the name #postLoginScript, and is not paramterised.

There is no default configuration for this 'action' in the standard initialization, since by default it is not needed. However, it can prove useful if your database requires special notification after connection. For example, it is used by the MySQL configuration to set the auto-commit mode of the database, since the standard method of doing this (using an ODBC command) has no effect (as of MySQL 3.23.38).

4. Customising ReStore for Individual Databases

Home | Index | Next