SQL interceptor for use with third party data analytics packages

• US 10,380,139 B2
• Filed: 11/15/2016
• Issued: 08/13/2019
• Est. Priority Date: 11/15/2016
• Status: Active Grant

• Current Assignee: Spirent Communications Incorporated
• Original Assignee: Spirent Communications Incorporated
• Inventors: Adamut, Marius
• Primary Examiner(s): Pham, Khanh B
• Application Number:
  US15/352,550
• Publication Number:
  US 20180137184A1
• Time in Patent Office: 1,001 Days
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: H04L 67/01 Protocols
• CPC Class Codes: H04L 67/02 based on web technology, e.g. hypertext transfer protocol [HTTP]

Abstract

An SQL interceptor inserted as a proxy between a database client and the corresponding database server intercepts a constrained application-generated SQL query and composes a new data request. Parameter values in the SQL query determine whether the new data request is sent to a database server or a web service provider. A reserved table name specified in the SQL query triggers a rewrite of the data request. Parameter values in the query are used to select among a plurality of executable modules to use for rewriting the data request. Special data encoding and formats need to be used based on the source of data that will receive and respond to the rewritten data request. For example, communication between a database client and server may use a vendor-specific, non-standard binary encoding, and XML and JSON response data must be reformatted as an SQL response for processing by the database client.

Claims

1. A method for enhancing data collection by a data analytics application comprising:

at a proxy between the data analytics application and a database server, receiving from a database client embedded in or linked to the data analytics application a data request comprising a binary-encoded application-generated SQL query;

decoding the binary-encoded application-generated SQL query;

identifying, by matching a database table name in the decoded application-generated SQL query to a list of reserved table names, whether the application-generated SQL query is a request to;

trigger generating a new SQL query to request data from the database server;

or

forward the application-generated SQL query without modification to the database server;

responsive to detecting that the database table name matching an entry in the list of reserved table names indicates a request for a new SQL database query;

generating the requested new SQL query, using a first executable module selected for execution among a plurality of executable modules based on a first parameter value in the decoded application-generated SQL query;

determining, by the first executable module, the database table determined based on a second parameter value in the decoded application-generated SQL query;

binary encoding, at the proxy, the new SQL query in a format accepted by the database server, and sending the binary-encoded new SQL query to the database server; and

receiving, at the proxy, SQL response data from the database server in a format accepted by the embedded or linked database client; and

sending from the proxy to the embedded or linked database client the SQL response data in a binary encoded format accepted by the embedded database client.

2. The method of claim 1, further comprising, while processing a second query:

wherein the identifying further includes, identifying from the matching of the database table name to the list of reserved table names that the application-generated SQL query is a request to generate a web service call to a web service provider;

responsive to detecting that the database table name matching an entry in the list of reserved table names in the decoded application-generated SQL query indicates a request to generate a web service call to a web service provider;

executing a second module to generate the web service call, the second executable module selected for execution based on a third parameter value in the decoded application-generated SQL query;

determining, by the executing module, a web service interface that is provided by the web service provider, the web service interface determined based on a fourth parameter value in the decoded application-generated SQL query;

sending, by the proxy, the web service call to the web service provider;

receiving, by the proxy, web service response data from the web service provider; and

re-structuring the web service response data as SQL response data.

3. The method of claim 2, wherein the second executable module is a function programmed into the proxy.

4. The method of claim 2, further comprising:

identifying, from the matching of the database table name to the list of reserved table names that the application-generated SQL query is a request to generate a JSON/REST web service call;

composing the web service request as a JSON/REST web service request, wherein the web service is a RESTful web service;

receiving JSON response data from the RESTful web service; and

transforming the JSON response data into SQL response data.

5. The method of claim 2, further comprising:

identifying, from the matching of the database table name to the list of reserved table names that the application-generated SQL query is a request to generate a XML/SOAP web service call;

composing the web service request as an XML/SOAP web service request;

receiving XML response data from the XML/SOAP web service; and

transforming the XML response data into SQL response data.

6. The method of claim 1, wherein the first executable module is a script, and the name of the script is the value of the second parameter value in the decoded application-generated SQL query.

7. A non-transitory computer-readable storage medium storing instructions for enhancing data collection by a data analytics application, the instructions which when executed by a processor, causes the processor to:

receive from a database client embedded in or linked to the data analytics application a data request comprising a binary-encoded application-generated SQL query;

decode the binary-encoded application-generated SQL query;

identify, by matching a database table name in the decoded application-generated SQL query to a list of reserved table names, whether the application-generated SQL query is a request to;

trigger generating a new SQL query to request data from the database server;

or

forward the application-generated SQL query without modification to a database server;

responsive to detecting that the database table name matching an entry in the list of reserved table names indicates a request for a new SQL database query;

generate the requested new SQL query, using a first executable module selected for execution among a plurality of executable modules based on a first parameter value in the decoded application-generated SQL query;

determine, by the executing module, the database table determined based on a second parameter value in the decoded application-generated SQL query;

binary encode the new SQL query in a format accepted by the database server, and sending the binary-encoded new SQL query to the database server; and

receive SQL response data from the database server in a format accepted by the embedded or linked database client; and

send to the embedded or linked database client the SQL response data in a format accepted by the embedded database client.

8. The non-transitory computer-readable storage medium of claim 7, further including instructions that cause the processor, by matching the database table name to the list of reserved names, to identify that the application-generated SQL query is a request to generate a web service call to a web service provider;

and further comprising;

responsive to detecting that the database table name matching an entry in the list of reserved table names indicates a request to generate a web service call to a web service provider;

execute a second module to generate the web service call, the second executable module selected for execution based on a third parameter value in the decoded application-generated SQL query;

determine, by the executing module, a web service interface that is provided by the web service provider, the web service interface determined based on a fourth parameter value in the decoded application-generated SQL query;

send the web service call to the web service provider;

receive web service response data from the web service provider; and

re-structure the web service response data as SQL response data.

9. A system for enhancing data collection by a data analytics application comprising:

a processor running the data analytics application that is linked to or has embedded within the application a database client for sending queries to a database server;

a processor running a proxy that is linked to or has embedded within the application a database client for sending queries to the database server;

a database server operable to respond to SQL queries;

the data analytics application operable to provide a user interface that allows a user to specify a constrained SQL query and invoke the embedded or linked database client to send the SQL query to the database server;

the embedded or linked database client operable to binary-encode the constrained SQL query and send the binary-encoded query to the database server;

the proxy operable to;

intercept the binary-encoded query that was sent to the database server, the proxy operable to decode the query and extract the constrained SQL query;

determine, by matching a database table name specified in the constrained SQL query to a list of reserved table names, to create a new SQL query instead of forwarding the constrained SQL query;

generate the new SQL query using a first executable module selected for execution among a plurality of executable modules based on a first parameter value in the decoded application-generated SQL query;

determine, by the executing module, the database table determined based on a second parameter value in the constrained SQL query; and

binary encoding, at the proxy, the new SQL query in a format accepted by the database server, and sending the binary-encoded new SQL query to the database server;

the database server operable to receive the binary-encoded new SQL query, process the query, and send SQL response data in a format accepted by the embedded or linked database client;

the proxy further operable to;

intercept the SQL response data from the database server, the SQL response data in a format accepted by the embedded or linked database client; and

send to the embedded or linked database client the SQL response data in format accepted by the embedded database client;

the database client embedded into or linked by the data analytics application operable to receive the SQL response data from the proxy and providing the data to the application; and

the application operable to create a visual representation of the SQL response data.

10. The system of claim 9, further comprising:

a channel for communication with a web service provider;

wherein the proxy is further operable to;

identify, based on the database table name in the decoded application-generated SQL query, identify that the application-generated SQL query is a request to generate a web service call to the web service provider;

execute a second module to generate the web service call, the second executable module selected for execution based on a third parameter value in the decoded application-generated SQL query;

determine, by the executing module, a web service interface that is provided by the web service provider, the web service interface determined based on a fourth parameter value in the decoded application-generated SQL query;

send the web service call to the web service provider;

receive web service response data from the web service provider; and

re-structure the web service response data as SQL response data.