The present application is directed towards systems and methods for selecting a database from a plurality of databases to forward a SQL query request based on a property of the SQL request. A device intermediary to a plurality of clients and databases may establish a plurality of connections to the plurality of databases. The device may receive, from a client of the plurality of clients, a request to execute a SQL query. The device may evaluate one or more properties of the request to execute the SQL query responsive to a policy. The device may select a database from the plurality of databases based on a result of evaluation of the one or more properties of the request to execute the SQL query. The device may forward the request to execute the SQL query to the selected database via a connection of the plurality of connections.
Legal claims defining the scope of protection. Each claim is shown in both the original legal language and a plain English translation.
1. A method for selecting by an intermediary device a database from a plurality of databases to forward a structured query language (SQL) query request based on a property of the SQL query request, the method comprising: (a) establishing, by a device intermediary to a plurality of clients and a plurality of databases, a plurality of server-side connections to the plurality of databases, the device establishing a pool of server-side connections to each database of the plurality of databases; (b) receiving, by the device from a client of the plurality of clients, a request to execute a structured query language (SQL) query included in the request; (c) parsing, by the device, the SQL query to identify one or more first properties of the SQL query that are exposed using policy expressions corresponding to a first policy for selecting a database to execute the SQL query; (d) evaluating, by the device responsive to the first policy, the one or more first properties of the SQL query to select a database to execute the SQL query, the one or more first properties of the SQL query identifying an action to be taken on a database; (e) selecting, by the device, a database from the plurality of databases based on a result of evaluation of the one or more first properties of the SQL query; (f) parsing, by the device, the SQL query to identify one or more second properties of the SQL query that are exposed using policy expressions corresponding to a second policy for selecting a server-side connection of the pool of server-side connections over which to forward the SQL query to the database; (g) evaluating, by the device responsive to the second policy, the one or more second properties of the SQL query to select a server-side connection of the pool of server-side connections over which to forward the SQL query to the database, the one or more second properties identifying connection characteristics of a database connection; (h) selecting, by the device, the server-side connection of the pool of server-side connections based on a result of evaluation of the one or more second properties of the SQL query; and (i) forwarding, by the device, the SQL query to the selected database via the server-side connection of the plurality of server-side connections.
An intermediary device selects a database to execute a SQL query based on properties of the query. The device establishes multiple connections to a pool of databases. When a client sends a SQL query, the device parses the query to identify properties defined by a first policy, which determines which database should handle the query based on the action the query will take. Based on the evaluated properties, a database is selected. Next, the device parses the query again using a second policy to select a server-side connection from a connection pool based on database connection characteristics. Finally, the device forwards the SQL query to the selected database through the chosen server-side connection.
2. The method of claim 1 , further comprising multiplexing, by the device, structured query language (SQL) requests from the plurality of clients via the pool of server-side connections to a database of the plurality of databases.
The intermediary device from the previous SQL query routing description also supports multiplexing SQL requests from multiple clients over the pool of established server-side connections to each database. This allows the device to efficiently manage database connections and optimize resource utilization by sharing connections among multiple clients.
3. The method of claim 1 , wherein step (e) further comprises specifying, by the second policy, the policy expressions corresponding to the second policy to evaluate the one or more second properties comprising the connection characteristics of a database connection for the request to execute the SQL query comprising one of the following: identification of an authenticated user, a name of the database, a character set sent by the client and client's capabilities.
As part of the process in the first SQL query routing description, the second policy uses expressions to evaluate connection characteristics for the request to execute the SQL query. The connection characteristics include identification of an authenticated user, the database name, the character set sent by the client, and the client's capabilities. These characteristics are used to select the optimal server-side connection.
4. The method of claim 1 , wherein step (c) further comprises specifying, by the first policy, the policy expressions corresponding to the first policy to evaluate the one or more first properties of the SQL query, the one or more first properties comprising command parameters of the SQL query comprising one or more of the following: a size of the request and a type of command.
As part of the process in the first SQL query routing description, the first policy uses expressions to evaluate properties of the SQL query. These properties are command parameters of the SQL query, such as the size of the request and the type of command (e.g., SELECT, INSERT, UPDATE). The evaluation of these command parameters helps the device determine the appropriate database for query execution.
5. The method of claim 1 , wherein step (c) further comprises specifying, by the first policy, the policy expressions corresponding to the first policy to evaluate the one or more first properties of the SQL query comprising a grammar part and a data part of the SQL query.
As part of the process in the first SQL query routing description, the first policy evaluates properties of the SQL query by examining both the grammar part and the data part of the query. This allows for a more granular analysis of the query to determine the appropriate database for executing the query.
6. The method of claim 1 , wherein step (e) further comprises selecting, by a load balancer of the device, the database from the plurality of databases based on least connection load balancing.
As part of the process in the first SQL query routing description, the database selection is performed by a load balancer within the device. The load balancer uses a "least connection" algorithm to select the database with the fewest active connections, thus distributing the workload evenly across the available databases.
7. The method of claim 1 , wherein step (e) further comprises determining, by the device responsive to the result of the evaluation, that the SQL query comprises a write query and selecting the database comprising a master database in a master and slave configuration.
As part of the process in the first SQL query routing description, the device determines if the SQL query is a "write" query (e.g., INSERT, UPDATE, DELETE). If it is, the device selects a "master" database in a master-slave database replication configuration. This ensures that all write operations are directed to the master database to maintain data consistency.
8. The method of claim 1 , wherein step (e) further comprises determining, by the device responsive to the result of the evaluation, that the SQL query comprises a read query and selecting the database comprising a slave database in a master and slave configuration.
As part of the process in the first SQL query routing description, the device determines if the SQL query is a "read" query (e.g., SELECT). If it is, the device selects a "slave" database in a master-slave database replication configuration. This distributes read operations across the slave databases, reducing the load on the master database and improving performance.
9. The method of claim 1 , wherein step (e) further comprises determining, by the device responsive to the result of the evaluation that the SQL query is for the database corresponding to one or more of a predetermined user, a predetermined database name and a predetermined table.
As part of the process in the first SQL query routing description, the device determines if the SQL query is targeted towards a specific database based on predefined criteria such as a predetermined user, a predetermined database name, or a predetermined table. If the query matches these criteria, the device routes the query to the corresponding database.
10. The method of claim 1 , wherein the first policy includes an object-oriented expression specifying at least one object class and one or more members of the object classes; and wherein selecting the database from the plurality of databases based on the result of evaluation of the one or more first properties of the SQL query includes: evaluating a payload of the SQL query; assigning a value to an object specified by the object-oriented expression based on a portion of the payload corresponding to the object; evaluating the object-oriented expression based on the assigned value; and taking an action specified by the policy based on the evaluation of the object-oriented expression based on the assigned value.
As part of the process in the first SQL query routing description, the first policy includes an object-oriented expression that specifies object classes and their members. Database selection involves evaluating the SQL query payload, assigning values to objects based on payload portions, and then evaluating the object-oriented expression. The action specified by the policy is then taken based on the evaluation, enabling complex, data-driven routing decisions.
11. A system for selecting by an intermediary device a database from a plurality of databases to forward a structured query language (SQL) query request based on a property of the SQL request, the system comprising: a device intermediary to a plurality of clients and a plurality of databases, wherein the device establishes a plurality of server-side connections to the plurality of databases, the device establishing a pool of server-side connections to each database of the plurality of databases; a database proxy of the device receiving from a client of the plurality of clients, a request to execute a structured query language (SQL) query included in the request; a policy engine of the database proxy parsing the SQL query to identify one or more first properties of the SQL query that are exposed using policy expressions corresponding to a first policy for selecting a database to execute the SQL query, evaluating, responsive to the first policy, the one or more first properties of the SQL query to select a database to execute the SQL query, the one or more first properties of the SQL query identifying an action to be taken on a database, parsing the SQL query to identify one or more second properties of the SQL query that are exposed using policy expressions corresponding to a second policy for selecting a server-side connection of the pool of server-side connections over which to forward the SQL query to the database, and evaluating, responsive to the second policy, one or more second properties of the SQL query to select a server-side connection of the pool of server-side connections over which to forward the SQL query to the selected database, the one or more second properties identifying connection characteristics of a database connection; and wherein the database proxy selects a database from the plurality of databases based on a result of evaluation of the one or more first properties of the SQL query, selects the server-side connection of the pool of server-side connections based on a result of evaluation of the one or more second properties of the SQL query and forwards the SQL query to the selected database via the server-side connection of the plurality of server-side connections.
A system that selects a database for a SQL query based on the query's properties. The system includes a device between clients and databases, establishing connections to a pool of databases. A database proxy receives SQL queries. A policy engine parses the query to identify properties defined by a first policy to determine which database should handle the query based on action the query will take. It then parses based on a second policy to select a server-side connection from a pool. The database proxy selects a database and a connection based on evaluated properties and forwards the SQL query to the selected database using the selected connection.
12. The system of claim 11 , wherein the database proxy multiplexes structured query language (SQL) requests from the plurality of clients via the pool of connections to a database of the plurality of databases.
The system from the previous SQL query routing description also includes a database proxy that multiplexes SQL requests from multiple clients over the pool of established server-side connections to each database. This allows the proxy to efficiently manage database connections and optimize resource utilization by sharing connections among multiple clients.
13. The system of claim 11 , wherein the second policy specifies the policy expressions corresponding to the second policy to evaluate the one or more second properties comprising the connection characteristics of a database connection for the request to execute the SQL query comprising one of the following: identification of an authenticated user, a name of the database, a character set sent by the client and client's capabilities.
In the system from the previous SQL query routing description, the second policy used by the policy engine specifies expressions to evaluate connection characteristics, including the authenticated user, database name, character set, and client capabilities. These characteristics are used to select the optimal server-side connection.
14. The system of claim 11 , wherein the first policy specifies the policy expressions corresponding to the first policy to evaluate the one or more first properties of the request to execute the SQL query, the one or more properties comprising command parameters of the request for the SQL query comprising one or more of the following: a size of the request and a type of command.
In the system from the previous SQL query routing description, the first policy used by the policy engine specifies expressions to evaluate the SQL query's properties. These properties are command parameters such as the size of the request and the type of command (e.g., SELECT, INSERT, UPDATE).
15. The system of claim 11 , wherein the first policy specifies the policy expressions corresponding to the first policy to evaluate the one or more first properties of the request to execute the SQL query comprising a grammar part and a data part of the SQL query.
In the system from the previous SQL query routing description, the first policy used by the policy engine evaluates properties of the SQL query by examining both the grammar part and the data part of the query. This enables a more detailed analysis to determine the appropriate database for executing the query.
16. The system of claim 11 , wherein a load balancer of the device selects the database from the plurality of databases based on least connection load balancing.
In the system from the previous SQL query routing description, a load balancer within the device selects the database. The load balancer uses a "least connection" algorithm to select the database with the fewest active connections, thus distributing the workload evenly.
17. The system of claim 11 , wherein the database proxy, responsive to the result of the evaluation of the one or more first properties of the SQL query by the policy engine, determines that the SQL query comprises a write query and selecting the database comprising a master database in a master and slave configuration.
In the system from the previous SQL query routing description, the database proxy determines if the SQL query is a "write" query. If so, the proxy selects a "master" database in a master-slave database configuration, ensuring that write operations are directed to the master database.
18. The system of claim 11 , wherein the database proxy, responsive to the result of the evaluation of the one or more first properties of the SQL query by the policy engine, determines that the SQL query comprises a read query and selecting the database comprising a slave database in a master and slave configuration.
In the system from the previous SQL query routing description, the database proxy determines if the SQL query is a "read" query. If so, the proxy selects a "slave" database in a master-slave database configuration, distributing read operations across the slave databases.
19. The system of claim 11 , wherein the database proxy, responsive to the result of the evaluation the one or more first properties of the SQL query by the policy engine, determines that the SQL query is for the database corresponding to one or more of a predetermined user, a predetermined database name and a predetermined table.
In the system from the previous SQL query routing description, the database proxy determines if the SQL query is targeted towards a specific database based on criteria such as a predetermined user, database name, or table. If it matches, the proxy routes the query to the corresponding database.
20. The system of claim 11 , wherein the first policy includes an object-oriented expression specifying at least one object class and one or more members of the object classes; and wherein selecting the database from the plurality of databases based on the result of evaluation of the one or more first properties of the SQL query includes: evaluating a payload of the SQL query; assigning a value to an object specified by the object-oriented expression based on a portion of the payload corresponding to the object; evaluating the object-oriented expression based on the assigned value; and taking an action specified by the policy based on the evaluation of the object-oriented expression based on the assigned value.
In the system from the previous SQL query routing description, the first policy includes an object-oriented expression that specifies object classes and members. Database selection involves evaluating the SQL query payload, assigning values to objects based on payload portions, and evaluating the object-oriented expression. An action is taken based on the evaluation, enabling data-driven routing.
Cooperative Patent Classification codes for this invention. Click any code to explore related patents in that topic.
December 15, 2011
March 7, 2017
Browse 5M+ US patents with plain-English claim translations and AI-generated analysis.