Lesson 4: Performing a schema change on the consolidated database

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

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

Perform a schema change on the consolidated database

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;

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

Discuss this page in DocCommentXchange.