Systems and methods for database proxy request switching
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.
1. A method comprising:
(a) receiving, by a device intermediary to a client and a plurality of databases, a request of the client, the request comprising a structured query language (SQL) query;
(b) identifying, by the device, one or more properties of the SQL query;
(c) evaluating, by the device, the one or more properties of the SQL query using a policy for selecting a database from the plurality of databases for which to forward the SQL query;
(d) selecting, by the device, the database from the plurality of databases using at least a result of evaluation of the one or more properties using the policy; and
(e) forwarding, by the device, responsive to selecting the database from the plurality of databases, the request to the selected database.
2. The method of claim 1 , further comprising establishing, by the device, one or more connections between the device and each of the plurality of databases.
3. The method of claim 2 , wherein (d) further comprises selecting, by the device, a connection from the one or more connections to forward the request to the selected database.
4. The method of claim 1 , wherein (c) further comprises specifying, by the policy, an expression to evaluate the one or more properties of the SQL query, wherein the one or more properties comprising a grammar part and a data part of the SQL query.
5. The method of claim 1 , wherein (c) further comprises evaluating the one or more properties 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.
6. The method of claim 1 , wherein (c) further comprises evaluating the one or more properties comprising one of the following: a size of the request and a type of command.
7. The method of claim 1 , wherein (c) further comprises determining, by the device responsive to the result of the evaluation, that the SQL query comprises a write query and wherein (d) further comprises selecting the database comprising a master database from the plurality of databases deployed in a master and slave configuration.
8. The method of claim 1 , wherein (c) further comprises determining, by the device responsive to the result of the evaluation, that the SQL query comprises a read query and wherein (d) further comprises selecting the database comprising a slave database from the plurality of databases deployed in a master and slave configuration.
9. The method of claim 1 , wherein (d) further comprises selecting, by the device, the database from the plurality of databases using load balancing.
10. The method of claim 1 , wherein (e) further comprises forwarding, by the device, the request using multiplexing via one or more connections pooled between the device and the selected database.
11. A system comprising:
a device intermediary to a client and a plurality of databases, wherein the device is configured to
receive a request of the client, the request comprising a structured query language (SQL) query;
identify one or more properties of the SQL query of the request;
evaluate the one or more properties of the SQL query using a policy for selecting a database from the plurality of databases for which to forward the SQL query;
select the database from the plurality of databases using at least a result of evaluation of the one or more properties using the policy; and
forward the request to the selected database.
12. The system of claim 11 , wherein the device is further configured to establish one or more connections between the device and each of the plurality of databases.
13. The system of claim 12 , wherein the device is further configured to select a connection from the one or more connections to forward the request to the selected database.
14. The system of claim 11 , wherein the policy is further configured to specify an expression to evaluate the one or more properties of the SQL query, wherein the one or more properties comprising a grammar part and a data part of the SQL query.
15. The system of claim 11 , wherein the device is further configured to evaluate the one or more properties 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.
16. The system of claim 11 , wherein the device is further configured to evaluate the one or more properties comprising one of the following: a size of the request and a type of command.
17. The system of claim 11 , wherein the plurality of databases are deployed in a master and slave configuration and wherein the device is further configured to determine, responsive to the result of the evaluation, that the SQL query comprises a write query and to select as master database as the selected database.
18. The system of claim 11 , wherein the plurality of databases are deployed in a master and slave configuration and wherein the device is further configured to determine, responsive to the result of the evaluation, that the SQL query comprises a read query and select a slave database as the selected database.
19. The system of claim 11 , wherein the device is further configured to select the database from the plurality of databases using load balancing.
20. The system of claim 11 , wherein the device is further configured to forward the request using multiplexing via one or more connections pooled between the device and the selected database.