Automated Query Parameter File Generation for SQL Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional systems require extensive manual effort and time to create query language parameter files for new data sources, as each data source necessitates a new parameter file to address SQL particularities, making it inefficient to support diverse data sources.
Innovation Solution
The PRM tool generates query language parameter files automatically by aggregating configuration parameters and values from multiple data sources, using a master parameter data structure in XML format, and tests queries to determine supported SQL features, thereby creating a new parameter file that can be used by the connection server to generate suitable SQL queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a manual process is used to create query language parameter files for each new data source, then the parameter file can be customized to address SQL particularities, but the time and effort required increases significantly
Solution Approach 1:
The system copies and adapts parameter files from existing data sources to create parameter files for new data sources. Instead of manually creating a parameter file from scratch, the system uses a generated parameter file as a template and automatically adapts it to the new data source's SQL particularities, significantly reducing the time and effort required while maintaining accuracy.
Solution Approach 2:
The manual mechanical process of analyzing documentation and testing data sources to create parameter files is replaced by an automated system. The system automatically generates parameter files by querying the data source and extracting SQL particularities, eliminating the need for manual analysis and testing while producing accurate results.
2Device complexity
If a generic query engine is used for all data sources, then system simplicity is maintained, but configuration information must be manually created for each new data source
Solution Approach 1:
The system enables self-service by allowing new data sources to be added automatically without requiring manual configuration. When a new data source is connected, the system automatically queries it to extract SQL particularities and generates the necessary parameter files, making the system self-configuring and eliminating the need for manual intervention in the configuration process.
Solution Approach 2:
The system dynamically changes parameters based on the specific data source being accessed. Instead of using a fixed configuration, the system queries the data source to determine its SQL particularities and automatically adjusts the parameter files accordingly, allowing the same generic query engine to adapt to different data sources without manual reconfiguration.
3Manufacturing precision
If manual analysis and testing is performed for each new data source, then the parameter file accurately reflects the data source's SQL support, but the process requires many workdays
Solution Approach 1:
The manual mechanical process of analyzing documentation and testing data sources is replaced by an automated system that queries the data source directly. The system executes queries to extract SQL particularities automatically, eliminating the need for manual analysis while maintaining high accuracy in reflecting the data source's actual capabilities.
Solution Approach 2:
The system uses feedback from actual data source queries to generate accurate parameter files. By querying the data source and analyzing its responses, the system automatically determines which SQL features are supported, ensuring high accuracy without requiring manual testing. The feedback loop allows the system to adapt to the specific data source's capabilities dynamically.
Data Source
AI summary
Some aspects include determination of a data structure including a plurality of query language configuration parameters and at least two values associated with each of the plurality of query language configuration parameters, and generation, based on the data structure, of a first structured language query associated with a first value associated with one of the plurality of query language configuration parameters, and a second structured language query associated with a second value associated with the one of the plurality of query language configuration parameters. Also included are determination of a first expected query result associated with the first structured language query and a second expected query result associated with the second structured language query, query of a data source with the first structured language query and the second structured language query, reception of a first query result associated with the first structured language query and a second query result associated with the second structured language query, determination that the first query result matches the first expected query result and that the second query result does not match the second expected query result, and addition, to a query language configuration parameter file associated with the data source, of the one of the plurality of query language configuration parameters in association with the first value.