Log-Based DDL Generation for Database Schema Evolution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods are limited in automatically generating Data Definition Language (DDL) statements based on multiple database log entries, particularly failing to capture schema changes over time and enabling UNDO operations at a later time.
Innovation Solution
A method to identify, aggregate, and sort database log records associated with specific DDL commands, allowing for the generation of DDL commands that may be stored as 'DDL Objects' for later use, including UNDO operations by sequencing log entries out of strict time order when necessary.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If log entries are processed in strict time sequence, then the processing order is simple and straightforward, but UNDO operations cannot be accurately generated because the temporal order of schema changes must be reversed to properly unwind database operations
Solution Approach 1:
The patent applies inversion by reversing the temporal order of log entries when generating UNDO operations. Instead of processing logs in chronological order (oldest first), the system processes them in reverse chronological order (newest first) to properly unwind schema changes. This allows UNDO operations to accurately reverse the sequence of database modifications by applying changes in the opposite order they were originally executed.
2Productivity
If only a single log entry or snapshot is used to generate DDL statements, then the generation process is simple and fast, but changes made to the database schema over time cannot be captured for later UNDO operations
Solution Approach 1:
The patent applies preliminary action by capturing and storing all log entries related to schema changes as they occur, rather than waiting until a snapshot is needed. The system proactively identifies, aggregates, and preserves the complete history of DDL operations in log files, ensuring that all schema evolution information is available for future UNDO or MIGRATE operations without requiring re-processing of the entire database history.
3Loss of information
If multiple log entries are aggregated to capture schema changes over time, then complete schema evolution history is captured, but the complexity of identifying, sorting, and processing the log entries increases significantly
Solution Approach 1:
The patent applies segmentation by dividing the complex task of DDL generation into distinct modular components: (1) log entry identification and filtering to isolate schema change records, (2) aggregation to group related log entries by schema object, (3) sorting to establish proper temporal or reverse-temporal order, and (4) DDL statement generation. This modular approach manages complexity by handling each aspect separately rather than processing all log entries uniformly.
Solution Approach 2:
The patent introduces an intermediary data structure (DDL command object) that serves as a mediator between raw log entries and final DDL statements. This intermediary structure aggregates and organizes multiple log entries associated with a single DDL command, allowing the system to process logs in a structured manner and generate accurate UNDO or MIGRATE operations without directly manipulating the raw log data.
Data Source
AI summary
Systems, methods, and computer readable media for automatically generating Data Definition Language (DDL) commands from database log information is described. In general, techniques are disclosed for analyzing database log entries to identify those associated with targeted DDL commands and associating those entries with a DDL command object. The DDL command object may be used (immediately or at some later time) to generate a DDL commands corresponding to the (possibly many) aggregated log records associated with the command object. The use of multiple database log entries as described herein enables the generation of DDL commands that capture database activity occurring over a period of time (full time context auditing) and can, therefore, naturally account for database schema changes.