Systems and Methods for Database Proxy Request Switching

• US 20120166483A1
• Filed: 12/15/2011
• Published: 06/28/2012
• Est. Priority Date: 12/28/2010
• Status: Active Grant

• Current Assignee: Citrix Systems, Inc. (Cloud Software Group), Foley & Lardner LLP
• Original Assignee: Citrix Systems, Inc. (Cloud Software Group)
• Inventors: Choudhary, Akshat, Ramachandra, Pratap
• Granted Patent: US 9,589,029 B2
• Time in Patent Office: Days
• US Class Current: 707/770
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: G06F 16/275 Synchronous replication

Abstract

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.

Claims

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 request, the method comprising:

(a) establishing, by a device intermediary to a plurality of clients and a plurality of databases, a plurality of connections to 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;

(c) evaluating, by the device responsive to a policy, one or more properties of the request to execute the SQL query;

(d) selecting, by the device, 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; and

(e) forwarding, by the device, the request to execute the SQL query to the selected database via a connection of the plurality of connections.

2. The method of claim 1, wherein step (a) further comprises establishing, by the device, a pool of connections to each database of the plurality of databases.

3. The method of claim 2, further comprising multiplexing, by the device, structured query language (SQL) requests from the plurality of clients via the pool of connections to a database of the plurality of databases.

4. The method of claim 1, wherein step (c) further comprises specifying, by the policy, an expression to evaluate the one or more properties comprising a characteristic 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.

5. The method of claim 1, wherein step (c) further comprises specifying, by the policy, an expression to evaluate the one or more 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.

6. The method of claim 1, wherein step (c) further comprises specifying, by the policy, an expression to evaluate the one or more properties of the request to execute the SQL query comprising a grammar part and a data part of the SQL query.

7. The method of claim 1, wherein step (d) further comprises selecting, by a load balancer of the device, the database from the plurality of databases based on least connection load balancing.

8. The method of claim 1, wherein step (d) further comprises determining, by the device responsive to the result of the evaluation, that the request to execute the SQL query comprises a write query and selecting the database comprising a master database in a master and slave configuration.

9. The method of claim 1, wherein step (d) further comprises determining, by the device responsive to the result of the evaluation, that the request for the SQL query comprises a read query and selecting the database comprising a slave database in a master and slave configuration.

10. The method of claim 1, wherein step (d) 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.

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 connections to 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;

a policy engine of the database proxy evaluating responsive to a policy one or more properties of the request to execute the SQL query; and

wherein the database proxy selects 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 and forwards the request to execute the SQL query to the selected database via a connection of the plurality of connections.

12. The system of claim 11, wherein the device establishes a pool of connections to each database of the plurality of databases.

13. The system of claim 12, 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.

14. The system of claim 11, wherein the policy specifies an expression to evaluate the one or more properties comprising a characteristic 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.

15. The system of claim 11, wherein the policy specifies an expression to evaluate the one or more 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.

16. The system of claim 11, wherein the policy specifies an expression to evaluate the one or more properties of the request to execute the SQL query comprising a grammar part and a data part of the SQL query.

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

18. The system of claim 11, wherein the database proxy, responsive to the result of the evaluation by the policy engine, determines that the request to execute the SQL query comprises a write query and selecting the database comprising a master database in a master and slave configuration.

19. The system of claim 11, wherein the database proxy, responsive to the result of the evaluation by the policy engine, determines that the request for the SQL query comprises a read query and selecting the database comprising a slave database in a master and slave configuration.

20. The system of claim 11, wherein the database proxy, responsive to the result of the evaluation 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.