Versioned Database Query Management for Continuous Delivery
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional applications using database queries face challenges in upgrading database schemas and queries, leading to cumbersome recompilation and redeployment processes, suboptimal execution plans, and increased vulnerability to security attacks due to embedded queries.
Innovation Solution
A system that treats each version of a database query as immutable, allowing decoupling of applications from query definitions, enabling continuous integration and delivery, optimizing queries before deployment, and eliminating the need for client-side libraries, thereby simplifying upgrades and enhancing security.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If database queries are embedded in application code, then the application can execute queries directly, but upgrading queries requires recompilation and redeployment of the entire application
Solution Approach 1:
The patent separates database queries from application code by introducing a standalone query management system. Queries are stored as independent, versioned entities that can be updated separately from the application. The application contains only query identifiers and parameters, while the actual query definitions reside in a separate database or repository, enabling independent query upgrades without application recompilation.
Solution Approach 2:
The patent extracts query definitions from the application codebase and places them in an external, independently manageable location. This extraction allows queries to be developed, tested, and deployed separately from the application, eliminating the coupling that currently forces full application redeployment for query changes.
2Speed
If database queries are optimized by creating indexes, then query execution performance improves, but index creation takes time and delays query availability
Solution Approach 1:
The patent implements query optimization and index creation as preliminary actions that occur during query definition development and testing, before the query is deployed to production. Optimization work is completed in advance on development or staging environments, allowing production queries to be ready immediately without waiting for index creation during deployment.
Solution Approach 2:
The patent uses versioning and copying mechanisms to maintain multiple copies of queries in different environments (development, testing, production). Optimized versions with indexes are created and validated in lower environments, then copied to production without requiring real-time index creation, ensuring immediate availability of optimized queries.
3Adaptability or versatility
If client applications link with database libraries (JDBC, ODBC), then the application can interface with database systems, but the application becomes more complex and requires rebuilding during library upgrades
Solution Approach 1:
The patent introduces a standardized query interface as an intermediary layer between the application and database-specific libraries. The application communicates with the database through this abstract interface using query identifiers and parameters, while the actual database interaction is handled by the query management system. This intermediary eliminates the need for applications to directly link with or understand database-specific libraries.
Solution Approach 2:
The patent creates a universal query execution mechanism that works across different database systems without requiring application-specific configurations. The standardized interface and versioned query definitions provide multi-functionality, allowing the same application structure to work with various database systems and library versions through the abstracted query management layer.
4Adaptability or versatility
If applications are rebuilt and reinstalled during database upgrades, then the application can use newer database features, but the application experiences downtime during the upgrade process
Solution Approach 1:
The patent performs all necessary query modifications, optimizations, and validations in advance in development and testing environments before deploying to production. Query definitions are prepared, tested, and versioned beforehand, allowing production deployments to simply reference the pre-validated query versions without requiring application rebuilding or causing downtime.
Solution Approach 2:
The patent implements dynamic query versioning that allows the application to automatically reference updated query versions without requiring application restart or redeployment. The system dynamically resolves query identifiers to the latest valid versions, enabling continuous application availability while database queries are upgraded in the background.
Data Source
AI summary
A system stores associates a database query with a name and stores versions of the database query. The system allows applications to execute a specific version of the database query by specifying the name of the database query and a version identifier. For example, the database query may be exposed as an endpoint of a REST API and invoked using calls to the REST API. This allows the applications to be designed without including the query definition within the code of the application. Continuous delivery of database query is performed by creating a new version of the database query that are tested using applications in a test environment. Once the new version of a database query meets test criteria, the new version of the database query is executed by applications running in production environment by sending API requests that identify the query name and the new version.