Static SQL Query Pre-generation via API Static Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Dynamic SQL queries in API-based query systems require runtime execution to generate SQL statements, leading to inefficiencies and performance issues due to unknown table and column names, and necessitate post-deployment tuning, which can be impractical and resource-intensive.
Innovation Solution
Pre-generating static SQL queries by performing static analysis on API-defined queries using object-relational mapping (ORM) metadata before application execution, determining columns, operators, and SQL segments to create pre-compiled, optimized SQL queries that can be executed directly.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If dynamic SQL queries are used in API-based query systems, then the system can handle runtime flexibility and unknown table/column names, but performance overhead increases and post-deployment tuning is required
Solution Approach 1:
The patent performs static analysis on API-defined queries before application execution to pre-generate SQL queries. This preliminary action captures query information during compilation, allowing the system to maintain runtime flexibility through API-based queries while eliminating performance overhead by having SQL queries ready in advance, thus resolving the contradiction between adaptability and productivity
2Adaptability or versatility
If dynamic SQL queries are generated at runtime, then the system can adapt to unknown table and column names, but database security is reduced and performance tuning becomes impractical
Solution Approach 1:
The patent performs static analysis during compilation to pre-generate SQL queries with known table and column names before application execution. This preliminary action maintains adaptability to unknown schemas while improving database security by having pre-validated queries, thus resolving the contradiction between adaptability and reliability
3Productivity
If static SQL queries are used, then database security is improved and performance is optimized, but the system loses runtime flexibility and requires known table/column names prior to compilation
Solution Approach 1:
The patent introduces static analysis as an intermediary process between API-defined queries and SQL generation. This intermediary performs compilation-time analysis to extract query information, enabling the system to maintain runtime flexibility through API-based queries while achieving performance optimization through pre-generated SQL, thus resolving the contradiction between productivity and adaptability
Data Source
AI summary
A method is provided for receiving source code that includes an application programming interface (API) defined query written to communicate with an API. The API is configured to provide access to an object relational model (ORM) for converting, during execution of the source code, the API defined query into a dynamic structured query language (SQL) query comprising SQL statements. The method also includes generating a static SQL query that includes SQL statements. The static SQL query is generated in response to the ORM and to the API, by a computer, prior to the source code being executed.