Schema Updates

Schema Planning and Validation

Before applying schema modifications, it is recommended to run a schema planning phase to preview changes and validate their correctness.

Schema Planning Steps:

1. Run Schema Plan Command:

   • Generate a schema plan to preview the changes before applying them.

   • Review the generated plan to ensure all modifications are correct.

   • Validate the logs in the xdr-s-* file.

Command:

poetry run xdrcli plan-schemas \
  --xdr_package_file_path /path/to/xdr_package.yaml \
  --xdr_root_log_path /path/to/logs \
  --target production \
  --target_file_path /path/to/xdr_targets.yaml

Options:

• --schema_filter_list: String value to only plan changes for schemas containing the specified string.

• --organisation_lookup_key: String value to only plan changes for customers containing the specified string.

Schema Modification Process

Updating Order By/Primary Key

Objective:

• Modify the ORDER BY and PRIMARY KEY columns of an existing table schema.

• Add new indexes or delete existing indexes on a table.

Steps:

1. Run Apply Schema Command:

   • Use the schema update command to create a replacement table with updated primary key and ORDER BY columns.

   • The command will handle the creation of a new table with the updated schema, replacement of the old table, and validation of data migration.

   • The command will also handle the addition of new indexes, removal of old indexes, and validation of changes.

Command:

poetry run xdrcli apply-schemas \
  --xdr_package_file_path /path/to/xdr_package.yaml \
  --schema_update_flag YES \
  --xdr_root_log_path /path/to/logs \
  --target production \
  --target_file_path /path/to/xdr_targets.yaml

Options:

• --schema_filter_list: String value to only update schemas that contain the specified string.

• --organisation_lookup_key: String value to only update schemas for customers containing the specified string.

Implementation Details

The schema modification process involves a series of steps to ensure data integrity and minimal downtime. The SchemaModifier class handles the entire schema evolution process, including reading schema files, creating replacement tables, and validating data migration. It utilizes the xdrcli commands for schema planning and updates, providing detailed logging for monitoring the schema modification process.

Key Functions:

• create_replacement_table(database_name, table_name, expected_primary_key, expected_order_by_key, ttl_value): Creates a replacement table with the updated schema, including the new primary key and ORDER BY columns.

• exchange_tables(database_name, table_name): Swaps the old table with the newly created replacement table.

• check_table_records(database_name, table_name): Checks the record counts for the specified table to ensure data migration is correct.

• add_indexes(database_name, table_name, current_indexes, expected_indexes): Adds new indexes to the replacement table based on the expected schema.

• remove_indexes(database_name, table_name, current_indexes, expected_indexes): Removes old indexes from the replacement table that are no longer needed.

• insert_records_into_new_table(database_name, table_name): Inserts records from the old table into the replacement table.

• validate_data_migration(database_name, table_name): Validates that data has been correctly migrated and is consistent with the original table.

• drop_replacement_table(database_name, table_name): Drops the replacement table after the migration is complete to clean up temporary resources.

• run_plan_schemas(): Generates and reviews the schema plan to preview changes before applying them.

• log_schema_changes(): Provides detailed logging of schema changes and the status of each step in the modification process.

Error Handling and Logging

• The SchemaModifier class and xdrcli commands include detailed logging to capture the status of each step in the schema modification process.

• Error handling ensures that issues encountered during schema updates are logged and addressed promptly.

• Logs are generated in the specified xdr_root_log_path directory, allowing for easy monitoring and troubleshooting.

Best Practices:

• Backup Data: Always back up your data before performing schema modifications.

• Review Plans: Thoroughly review the schema plan to understand the changes that will be applied.

• Monitor Logs: Monitor the logs during the update process to catch any issues early.

• Validate Integrity: Validate data integrity after the migration to ensure all records are consistent and no data is lost.

• Test Changes: If possible, test schema changes in a staging environment before applying them to production.