Lesson 4: Performing a schema change on the consolidated database

In this lesson, you add a new column to the customer table to store the customer's cell phone number.

Prérequis

This lesson assumes you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Changing a schema using the ScriptVersion extended option.

This lesson assumes you have completed all preceding lessons. See Lesson 1: Creating and configuring the consolidated database.

Task

Using the instance of Interactive SQL that is connected to the consolidated database, execute the following SQL statement to insert a row in the customer table.

ALTER TABLE customer ADD cell_phone VARCHAR(12) DEFAULT NULL;

Create a new script version called my_ver2 to handle synchronizations from remote databases with the new schema. Remote databases with the old schema continue to use the old script version, my_ver1.

Execute the following SQL statements on the consolidated database:

CALL ml_add_column('my_ver2', 'customer', 'id', null );
CALL ml_add_column('my_ver2', 'customer', 'name', null );
CALL ml_add_column('my_ver2', 'customer', 'phone', null );
CALL ml_add_column('my_ver2', 'customer', 'cell_phone', null );

CALL ml_add_table_script( 'my_ver2', 'customer', 'upload_insert',
      'INSERT INTO customer ( id, name, phone, cell_phone ) ' 
      || 'VALUES ({ml r.id}, {ml r.name}, {ml r.phone}, {ml r.cell_phone})' );

CALL ml_add_table_script( 'my_ver2', 'customer', 'download_cursor',
       'SELECT id, name, phone, cell_phone from customer' );

CALL ml_add_table_script( 'my_ver2', 'customer', 'download_delete_cursor', '--{ml_ignore}' );

COMMIT;

Résultat

Changes are made to the consolidated database and a new script version is created to handle the schema change.

Suivant

Proceed to Lesson 5: Performing a schema change on the remote database.

Discuter à propos de cette page dans DocCommentXchange.