Replicating Complex Augmented Views via SQL Conversion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional replication systems are inefficient in replicating augmented views, particularly those with complex hierarchies, as they require the entirety of contributing database tables to be replicated separately and the views to be manually created in the target system.
Innovation Solution
A replication system that determines whether an augmented view can be loaded or replicated in a target system, creating SQL views and database tables accordingly, and converts SQL statements to use augmented view field names and aliases, while removing or replacing session context variables to create the views and underlying objects in the target system.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional replication systems replicate augmented views, then data can be mirrored between systems, but the entire database table must be replicated separately and the view must be manually created, resulting in high complexity and time consumption
Solution Approach 1:
The patent extracts only the necessary components (SQL statements, metadata annotations, associations) of the augmented view from the source system and transfers them to the target system, rather than replicating the entire database table. This allows the augmented view to be automatically recreated with its metadata and associations intact, significantly reducing replication complexity and time requirements.
Solution Approach 2:
The system performs preliminary analysis of the augmented view structure, SQL statements, and metadata annotations before replication. It automatically determines the replication plan, converts SQL statements to the target system's dialect, and prepares all necessary objects in advance, eliminating the need for manual creation and reducing overall replication time.
2Reliability
If the entire database table is replicated separately, then all data is available in the target system, but data redundancy increases and manual view creation is required
Solution Approach 1:
The patent extracts only the augmented view definition (SQL statements, metadata annotations, associations) rather than replicating the entire underlying table. The target system creates the augmented view logically over existing or replicated base tables, eliminating redundant data storage while maintaining data consistency through the replicated view structure and metadata.
Solution Approach 2:
The replicated augmented view structure serves multiple functions: it provides data querying capabilities, maintains metadata annotations for data governance, establishes associations with other views, and enables consistent data access across the target system. This multi-functional approach reduces the need for separate manual view creation and minimizes data redundancy.
3Adaptability or versatility
If augmented views with complex hierarchies are replicated, then comprehensive data structure is maintained, but manual creation and separate table replication are required
Solution Approach 1:
The patent extracts the complete augmented view hierarchy including all SQL statements, metadata annotations, and associations from the source system. The replication system automatically processes this extracted information, converting SQL dialects and reconstructing the hierarchical view structure in the target system, thereby maintaining complex data relationships without manual configuration.
Solution Approach 2:
The system performs preliminary parsing and analysis of the augmented view hierarchy structure before replication. It automatically generates the replication plan, converts SQL statements to the target system's dialect, and prepares all hierarchical objects in advance, eliminating manual configuration requirements while preserving complex view hierarchies.
Data Source
AI summary
Systems and methods include determination of an augmented view of a source system to be created in a target system, the augmented view including a structured query language view and annotations, and the augmented view associated with an object hierarchy, determination of a structured query language statement to create the augmented view in the source system, conversion of the structured query language statement to include augmented view names and augmented view field names, modification of one or more session context variables of the converted structured query language statement, creation of a target augmented view in the target system using the converted and modified structure query language statement, and creation of each object of the object hierarchy which is not an augmented view in the target system.