Relational Metadata Management System for SQL Statement Generation

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing technologies for generating SQL DDL and DML statements are labor-intensive, error-prone, and expensive, with limited reusability and vendor lock-in, leading to inconsistent results and high costs due to manual effort and proprietary dependencies.

Innovation Solution

A relational metadata management system that stores metadata from source databases and generates SQL DDL and DML statements programmatically, enabling consistent and repeatable generation across multiple database systems, supporting multiple metadata repositories, and allowing for the creation of sophisticated VIEW-based architectures.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Ease of operation

If manual creation and editing of SQL scripts is used, then flexibility in creating custom queries is achieved, but labor intensity and error rate increase significantly

Engineering Contradiction:

Improveease of SQL statement creationVSAvoidproductivity in SQL statement creation

Core Design Contradiction:

Ease of operationVSProductivity

Solution Approach 1:

The system automatically copies metadata from source database tables and uses it to generate SQL DDL and DML statements, eliminating the need for manual script creation and reducing errors associated with copy/paste cycles

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The system performs self-service by automatically generating SQL statements from metadata without requiring manual intervention, thereby increasing productivity while maintaining flexibility through programmable templates

Inventive Principle:

Principle #25Self-service

2Extent of automation

If data modeling tools are used to generate DDL and DML, then some automation is achieved, but the tools are tightly coupled with a single physical source and require extensive manual work

Engineering Contradiction:

Improveautomation in DDL and DML generationVSAvoidcomplexity of metadata management system

Core Design Contradiction:

Extent of automationVSDevice complexity

Solution Approach 1:

The system segments metadata management by separating the metadata repository from the SQL generation process, allowing multiple source databases to be independently managed and queried without tight coupling

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The metadata repository serves multiple functions: storing metadata from various source databases, generating DDL statements, generating DML statements, and supporting multiple target databases, thereby reducing overall system complexity

Inventive Principle:

Principle #6Universality (Multi-functionality)

3Productivity

If ELT/ETL tools are used for data transformation, then physical data transformation is achieved, but VIEW generation and DDL/DML creation are limited

Engineering Contradiction:

Improveproductivity in data transformationVSAvoidversatility in SQL object generation

Core Design Contradiction:

ProductivityVSAdaptability or versatility

Solution Approach 1:

The system merges the capabilities of data transformation with VIEW generation and DDL/DML creation by using the same metadata repository and transformation logic to produce both transformed data and SQL statements

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The system performs preliminary action by generating DDL and DML statements from metadata before actual data transformation occurs, enabling proactive schema design and query preparation

Inventive Principle:

Principle #10Preliminary action

4Reliability

If presentation naming and business logic are hard coded in BI tools, then vendor lock-in occurs, but reusability across products is lost

Engineering Contradiction:

Improvereliability of business logicVSAvoidreusability of metadata

Core Design Contradiction:

ReliabilityVSAdaptability or versatility

Solution Approach 1:

The system extracts business logic and presentation naming from proprietary BI tool environments and stores it in a standardized metadata repository that can be accessed and reused across multiple products and vendors

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The metadata repository acts as an intermediary layer between source databases and various BI/AI/ML products, enabling reusability of business logic while maintaining reliability through standardized metadata formats

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS11977539B1Systems and methods for generation and management of SQL data definition and data manipulation statements via serially reusable metadata

Publication Date: 2024.05.07 SNOWBUILDER IO LLC

AI summary

This disclosure relates to improved techniques for generating SQL statements, including both DDL and DML statements. In certain embodiments, a relational metadata management system (RMMS) stores and maintains a metadata repository corresponding to one or more source databases. The RMMS can represent a SQL engine layer that is situated on top of a SQL engine and/or SQL database to facilitate higher levels of abstraction and reusability of the data included in the databases. The RMMS can include a SQL generator that utilizes the metadata stored in the metadata repository to generate the SQL statements for querying the one or more source databases.