Database Schema Evolution via Temporary Column Renaming

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Database table schema evolution causes naming collisions during data archiving, as existing tools do not handle column renames and additions effectively, leading to potential data misarchiving or failure in subsequent runs.

Innovation Solution

Detect renamed columns, generate temporary renames to avoid naming collisions, and sort the necessary ALTER statements to ensure the target archive tables match the source tables, allowing for seamless schema evolution without requiring users to track changes or run specifications after each alteration.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If column renaming is allowed in source tables during schema evolution, then adaptability of the archiving tool improves, but naming collisions occur in target archive tables causing data misarchiving or failure

Engineering Contradiction:

Improveschema evolution capabilityVSAvoiddata archiving accuracy

Core Design Contradiction:

Adaptability or versatilityVSReliability

Solution Approach 1:

The system performs preliminary detection of schema changes including column renames before executing the archiving operation. By detecting renames in advance and generating appropriate ALTER TABLE statements with temporary renames, the system prepares the target archive table schema to match the source table schema evolution, preventing naming collisions during data archiving.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system introduces an intermediary processing layer that generates and applies ALTER TABLE statements between the source table schema changes and the archiving operation. This intermediary mechanism uses temporary column names as mediators to resolve naming conflicts, allowing the archiving tool to handle schema evolution reliably without direct exposure to naming collisions.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Reliability

If users manually track and manage table schema changes, then data integrity can be maintained, but operational complexity and time consumption increase

Engineering Contradiction:

Improvedata integrityVSAvoidtime for version tracking

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system implements self-service automation where the archiving tool automatically detects schema changes including column renames, generates the necessary ALTER TABLE statements, and applies them to the target archive tables. This eliminates the need for users to manually track version changes or determine the correct sequence of operations, maintaining data integrity while saving significant time and effort.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system establishes a feedback mechanism that continuously monitors source table schema changes and automatically responds by generating and applying corresponding ALTER TABLE statements to target archive tables. This closed-loop feedback system ensures data integrity is maintained through automatic adaptation to schema evolution without requiring manual user intervention or time-consuming version tracking.

Inventive Principle:

Principle #23Feedback

3Adaptability or versatility

If multiple column renames are applied serially to target archive tables, then schema changes can be implemented, but naming collisions occur when column names are reused

Engineering Contradiction:

Improveschema change implementationVSAvoidnaming collision management

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The system performs preliminary detection and analysis of all schema changes including multiple column renames before executing them on target archive tables. By detecting renames in advance and generating a comprehensive set of ALTER TABLE statements with temporary renames, the system resolves potential naming collisions before they occur, enabling smooth implementation of multiple schema changes without complexity.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system introduces temporary column names as intermediary elements to resolve naming collisions during serial column renames. These temporary names act as mediators that allow multiple renames to be applied safely by preventing direct conflicts between old and new column names, thereby simplifying the management of complex schema evolution scenarios.

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS7725439B2Handling column renaming as part of schema evolution in a data archiving tool

Publication Date: 2010.05.25 X CORP

AI summary

Methods and apparatus, including computer program products, implementing and using techniques for managing database table schema evolution. It is detected whether any columns in a source table have been renamed since a previous archiving process. A set of table changes is generated for the renamed columns. It is detected whether any possible naming collisions exist in the set of table changes. If a possible naming collision is detected, a temporary rename is created for the renamed column. The set of table changes, including any created temporary renames, is sorted.