Synchronizing Schema Changes and Managing Referential Integrity Violations

Synchronizing Schema Changes

Being a true research and development company, Simbex violates one of the simplest rules-of-thumb in database synchronization: If you don't want trouble, don't change the schema. A schema change is hard enough with one database, but with two or 10 or 100 databases in active use, it can be an administrative nightmare. In a research environment, it's also a common occurrence: nothing stays the same for long.

To help reduce the administrative pain, central control of schema changes was built into the HIT System synchronization process. Using a new table called dbmlsync_sql_download in Listing 1 (with "dbmlsync" referring to the MobiLink client component dbmlsync.exe), schema changes can be synchronized out to remote databases along at the same time that changes to the data are synchronized.

CREATE TABLE dbmlsync_sql_download (
   sql_download_id UNSIGNED BIGINT NOT NULL DEFAULT autoincrement,
   db_id INTEGER NOT NULL,
   sql LONG VARCHAR NOT NULL,
   modification_date TIMESTAMP NOT NULL DEFAULT timestamp,
   PRIMARY KEY ( sql_download_id ) );

Rows in dbmlsync_sql_download are inserted into the central or "consolidated" database at Simbex and downloaded to the remote sideline controller databases. The columns are filled as follows:

sql_download_id is automatically incremented to uniquely identify each row, and it also specifies the processing order if two SQL scripts in this table apply to the same remote database.

db_id identifies which remote sideline controller database this SQL script applies to; this column is specific to Simbex, but most MobiLink synchronization setups have a similar "remote database identifier" column.

sql contains the SQL script to be downloaded.

modification_date is automatically set when the SQL script is inserted and updated when the script is changed, and it is used to determine which rows contain fresh SQL that must be downloaded.

A two-step process is used to create rows in dbmlsync_sql_download. First, a text file is coded containing the SQL script, and second, that text file is loaded into a new row in dbmlsync_sql_download.

Listing 2 is an example of a SQL script used to add a new column to a table on the remote database; in this example, the filename is alter_remote_version_v2m.sql.

ALTER TABLE DBA.sbx_activity
   ADD type INTEGER NOT NULL DEFAULT 0;
--)RUN

ALTER PUBLICATION DBA.PC MODIFY
   TABLE DBA.sbx_activity ( activity,
                            description,
                            creation_date,
                            modified_by,
                            created_by,
                            deleted,
                            type );
--)RUN
ALTER SYNCHRONIZATION SUBSCRIPTION TO DBA."PC"
   MODIFY OPTION ScriptVersion='PC_v2m';
--)RUN

The first ALTER TABLE command in Listing 2 adds the new "type" column to the end of each row the sbx_activity table, and initializes that column to 0 for all existing rows.

The next two commands are MobiLink overhead; they are necessary to keep the MobiLink synchronization setup to date. The ALTER PUBLICATION command tells MobiLink to include the new type column in the next synchronization. The ALTER SYNCHRONIZATION SUBSCRIPTION command tells MobiLink that a different set of scripts on the consolidated database, identified as version "PC_v2m", must now be used when synchronizing with this remote database.

The special --)RUN comment lines in Listing 2 serve to identify commands that are to be executed separately the remote database; this process will be handled by code shown later in this article.

Testing schema changes ahead of time is extremely important in a synchronizing environment; even the smallest mistake can bring the synchronization process to a halt when the software detects a mismatch or when the database software detects an integrity violation. Gathering separate commands into a single SQL script makes coding and testing a schema change easier, as opposed to storing each command as a separate entry.

Once the SQL script file is created, it is loaded into the consolidated database using the INSERT in Listing 3. In this example, the db_id of 2 identifies which sideline controller database the SQL is going to, and the SQL Anywhere function xp_read_file is called to convert the script file alter_remote_version_v2m.sql into a LONG VARCHAR string.

INSERT dbmlsync_sql_download ( db_id, sql )
   VALUES ( 2, xp_read_file ( 'alter_remote_version_v2m.sql' ) )

Listing 4 shows the download_cursor script that the MobiLink server runs on the consolidated database to determine which rows in dbmlsync_sql_download are to be downloaded to which remote database.

CALL ml_add_table_script ( 'PC_v2', 'dbmlsync_sql_download', 'download_cursor',
'SELECT sql_download_id,
      db_id,
      sql
   FROM dbmlsync_sql_download
WHERE modification_date > ? -- last_download
   AND db_id = CAST ( ? AS INTEGER ) -- ml_username' );

The WHERE clause contains two "?" placeholders which are filled in by MobiLink at run time. The first "?" is replaced by the date and time of the previous successful download, and that is used to choose rows for this download that have been inserted or updated since that point.

The second "?" is the "MobiLink user name" which serves to uniquely identify the remote database that is being synchronized. In the case of Simbex, the db_id values 1, 2, 3 are also used as MobiLink user names for simplicity.

Listing 5 shows the procedure that executes the SQL scripts when they reach the remote database. It is a user-written procedure, but because it has been given the special name sp_hook_dbmlsync_schema_upgrade the MobiLink client component dbmlsync.exe will automatically call this procedure as soon as all upload and download processing is complete.

MobiLink recognizes several different sp_hook procedure names like sp_hook_dbmlsync_schema_upgrade, and if you code them they will be called. This one is designed for processing schema changes like our example, and it offers several advantages over a solution you might create to run outside the MobiLink synchronization process:

1. The sp_hook_dbmlsync_schema_upgrade procedure is called automatically; you don't have to build any special logic on the remote database side to determine when a schema change needs to be applied.
2. It is OK to ALTER a table involved in synchronization in this procedure without first removing the table from the synchronization definition. If you do it outside this procedure, you have to take that extra step first.
3. The procedure is called after synchronization is complete so all changes made to the remote have already been uploaded. This is important because you can't ALTER a table involved in synchronization if any updates using the old schema are still waiting to be uploaded.

The procedure in Listing 5 is driven by a cursor fetch loop using the streamlined FOR statement supported by SQL Anywhere. This cursor retrieves all rows in the dbmlsync_sql_download table that have not yet been processed, and that fact is determined by a NOT EXISTS subquery on the dbmlsync_sql_download_ack table.

   CREATE PROCEDURE sp_hook_dbmlsync_schema_upgrade()
   BEGIN
   DECLARE @special_marker VARCHAR ( 100 );
   DECLARE @special_marker_length BIGINT;
   DECLARE @sql_part_pos BIGINT;
   DECLARE @special_marker_pos BIGINT;
   DECLARE @sql_part_length BIGINT;
   DECLARE @sql_part LONG VARCHAR;
   DECLARE @sqlstate VARCHAR ( 5 );
   DECLARE @errormsg VARCHAR ( 32767 );
   DECLARE @sql_part_number UNSIGNED INTEGER;
-- Process one or more rows of downloaded sql that have not been processed before, either in whole or in part.
   FOR f_fetch AS c_fetch NO SCROLL CURSOR FOR
      SELECT sql_download_id AS @sql_download_id,
          db_id     AS @db_id,
          sql       AS @downloaded_sql
      FROM dbmlsync_sql_download
      WHERE NOT EXISTS ( SELECT *
                  FROM dbmlsync_sql_download_ack
                  WHERE dbmlsync_sql_download_ack.sql_download_id
                      = dbmlsync_sql_download.sql_download_id )
      ORDER BY sql_download_id
      FOR READ ONLY
      DO
-- Process each part of @downloaded_sql that is delimited by the special comment marker.
   SET @special_marker = STRING ( '--', ')RUN' );
   SET @special_marker_length = LENGTH ( @special_marker );
   SET @sql_part_number = 1;
   SET @sql_part_pos = 1;
   SET @special_marker_pos = LOCATE ( @downloaded_sql, @special_marker, @sql_part_pos );
   
    WHILE @special_marker_pos > 0 LOOP
    -- 1. Extract the SQL part from the full string.
      SET @sql_part_length = @special_marker_pos - @sql_part_pos + @special_marker_length;
      SET @sql_part = SUBSTR ( @downloaded_sql, @sql_part_pos, @sql_part_length );
    -- 2. Execute the SQL part in a "try/catch" block
         BEGIN
            SET @sqlstate = '00000'; -- no error yet
            SET @errormsg = '';
            EXECUTE IMMEDIATE @sql_part;
            EXCEPTION
              WHEN OTHERS THEN

              -- Record the error but don't stop.
              SELECT SQLSTATE, ERRORMSG() INTO @sqlstate, @errormsg;
         END;
    -- 3. Record the result.
         INSERT dbmlsync_sql_download_ack VALUES (
            @sql_download_id, @sql_part_number, @db_id,
            @sql_part, @sqlstate, @errormsg, DEFAULT );
    -- 4. Look for the next SQL part.
            SET @sql_part_number = @sql_part_number + 1;
            SET @sql_part_pos = @special_marker_pos + @special_marker_length;
            SET @special_marker_pos = LOCATE ( @downloaded_sql, @special_marker, @sql_part_pos );
         END LOOP;
END FOR;
END; -- sp_hook_dbmlsync_schema_upgrade

Inside the cursor fetch FOR loop in Listing 5, a nested WHILE loop breaks the SQL script into the individual parts separated by the special RUN comment marker, and inside that WHILE loop each SQL part is processed step-by-step:

Step 1 extracts the SQL part from the larger string.

Step 2 uses EXECUTE IMMEDIATE to run the individual SQL part against the remote database. The EXCEPTION clause implements "try/catch" processing for the EXECUTE IMMEDIATE: any error is recorded in the two local variables @sqlstate and @sqlcode, but processing then continues with no further diagnostics or error handling.

Step 3 records everything about what happened with the EXECUTE IMMEDIATE in the dbmlsync_sql_download_ack table; rows in this table are uploaded to the consolidated database as "acknowledgements" of the various SQL commands that were received and processed. LOCATE searches forward in a string for the first occurrence of another string after a specified start position, returning zero when that substring is not found.

Listing 6 shows the dbmlsync_sql_download_ack table that is used in Step 3 described above.

CREATE TABLE dbmlsync_sql_download_ack (
   sql_download_id UNSIGNED BIGINT NOT NULL,
   sql_part_number UNSIGNED INT NOT NULL,
   db_id INTEGER NOT NULL,
   sql_part LONG VARCHAR NOT NULL,
   sql_part_sqlstate VARCHAR ( 5 ) NOT NULL,
   sql_part_errormsg VARCHAR ( 32767 ) NOT NULL DEFAULT '',
   creation_date TIMESTAMP NULL DEFAULT current timestamp,
   PRIMARY KEY ( sql_download_id, sql_part_number ) );

The dbmlsync_sql_download_ack table contains the sql_download_id and db_id values from the corresponding rows in dbmlsync_sql_download, plus these extra columns:

• sql_part_number is the ordinal number 1, 2, 3 of the individual RUN parts of the SQL script.
• sql_part contains the individual SQL parts that were executed.
• sql_part_sqlstate is the SQLSTATE returned by the EXECUTE IMMEDIATE, with '00000' indicating success.
• sql_part_errormsg is the corresponding error message, if any.
• creation_date provides an audit trail of when the SQL part was executed.

Listing 7 shows the upload_insert script that the MobiLink server runs on the consolidated database whenever a new row in dbmlsync_sql_download_ack is uploaded. This upload is a very important part of the schema change process because it allows changes to be tracked and checked centrally, with no DBA present at the remote location.

CALL ml_add_table_script ( 'PC_v2', 'dbmlsync_sql_download_ack', 'upload_insert',
'INSERT dbmlsync_sql_download_ack (
   sql_download_id,
   sql_part_number,
   db_id,
   sql_part,
   sql_part_sqlstate,
   sql_part_errormsg,
   creation_date )
VALUES ( ?, ?, ?, ?, ?, ?, ? )' );

Figure 4 shows what the dbmlsync_sql_download_ack table looks like on the consolidated database after the SQL script in Listing 8 has been processed on a remote database and the acknowledgement rows uploaded in return. In this case everything worked, no error messages.