SQL to API Query Translation System
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional database management systems face challenges in securely accessing and manipulating cloud-stored data, particularly in multi-tenanted environments, where REST APIs lack the flexibility of SQL for complex queries and data joining, leading to inefficiencies and increased development needs for end-user data manipulation.
Innovation Solution
A system comprising a parser to generate API calls from SQL queries, an API call executor to execute these calls, and a query engine to process results, which includes an endpoint update unit for dynamic data management and supports complex queries across multiple API endpoints, allowing for secure and flexible data retrieval.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If REST APIs are used to access cloud-stored data in multi-tenanted environments, then security is improved by preventing unauthorized access to other tenants' data, but flexibility for complex queries and data joining is lost
Solution Approach 1:
The patent introduces an intermediary layer (the system with parser, API call executor, and query engine) that sits between the user and the REST API endpoints. This intermediary translates SQL queries into multiple coordinated API calls, executes them, and combines the results. This mediator enables complex data manipulation capabilities while maintaining the security boundaries of the REST API architecture, as the system controls what data is retrieved and how it is combined.
Solution Approach 2:
The patent segments a complex SQL query into multiple simpler API calls. The parser breaks down the query into components, the API call executor divides the data retrieval into separate endpoint calls, and the query engine reassembles the results. This segmentation allows the system to handle complex queries through multiple simple, secure API calls rather than requiring a single complex endpoint.
2Adaptability or versatility
If traditional ODBC API is used to access database, then flexibility for complex SQL queries is maintained, but security concerns arise in multi-tenanted cloud environments where tenants could access other tenants' data
Solution Approach 1:
The system acts as a secure intermediary that translates flexible SQL queries into secure REST API calls. The parser interprets the SQL query intent, the API call executor translates it into multiple secure endpoint calls with appropriate tenant ID parameters, and the query engine combines results. This mediator maintains SQL flexibility while enforcing REST API security models.
Solution Approach 2:
The system changes the parameter structure from direct database access to API-based access with tenant ID parameters. Instead of allowing direct SQL execution against shared databases, the system transforms queries into parameterized API calls that include tenant identification, thereby maintaining query flexibility while enforcing security through parameter control.
3Reliability
If REST endpoints expose narrowly-specified data types separately, then security and data encapsulation are improved, but ease of operation for end users wanting to query and join data is worsened
Solution Approach 1:
The system serves as an intermediary that translates user-friendly SQL queries into multiple coordinated API calls to the narrowly-specified endpoints. The parser understands the user's data joining and filtering requirements, the API call executor calls the appropriate endpoints, and the query engine combines the results according to the original query intent, making the encapsulated endpoints easy to use.
Solution Approach 2:
The system provides universal access to multiple narrowly-specified REST endpoints through a single unified interface. The parser, API call executor, and query engine work together to handle various types of queries (joins, filters, aggregations) across different endpoints, making the system as versatile as direct database access while maintaining the benefits of encapsulated endpoints.
Data Source
AI summary
A system for retrieving data from one or more server computers, comprising: a parser operable to generate a plurality of application programming interface, API, calls to one or more API endpoints of the one or more server computers from a query; an API call executor operable to execute the API calls and receive the results; and a query engine operable to execute the query on the received results.