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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.