METHOD AND SYSTEM FOR OPERATING ON DATABASE QUERIES

• US 20140372466A1
• Filed: 06/14/2013
• Published: 12/18/2014
• Est. Priority Date: 06/14/2013
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Wong, Joseph
• Granted Patent: US 9,384,236 B2
• Time in Patent Office: Days
• US Class Current: 707/760
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/2452 Query translation

Abstract

The present disclosure involves systems, software, and computer implemented methods for operating on database queries. One example method includes identifying a parameterized input query in a first database query language. The parameterized input query includes one or more parameter placeholders. A random value is generated, for each parameter placeholder. A concrete instance of the parameterized input query in the first database query language is constructed. The concrete instance of the parameterized input query replaces each parameter placeholder with the corresponding generated random value. A translated output query in a second database query language is generated, from the concrete instance of the parameterized input query. The translated output query includes the generated random values. Each of the generated random values are replaced within the translated output query in the second database query language with a placeholder value associated with the second database query language.

Claims

1. A method of operating on database queries, comprising:

identifying a parameterized input query in a first database query language, wherein the parameterized input query includes one or more parameter placeholders, each parameter placeholder defining a parameter name and a declared parameter type;

generating, for each parameter placeholder in the parameterized input query in the first database query language, a random value of the declared parameter type;

constructing a concrete instance of the parameterized input query in the first database query language, where the concrete instance of the parameterized input query replaces each parameter placeholder with the corresponding generated random value of the declared parameter type;

generating, from the concrete instance of the parameterized input query, a translated output query in a second database query language, the translated output query including the generated random value of the declared parameter type; and

replacing the generated random values within the translated output query in the second database query language with a placeholder value associated with the second database query language.

2. The method of claim 1, wherein the first database query language is a Domain Specific Language (DSL) and the second database query language is a Structured Query Language (SQL).

3. The method of claim 2, wherein the DSL is an extension of an ActiveRecord query language from a Ruby on Rails framework.

4. The method of claim 1, wherein the second database query language is a Structured Query Language (SQL) and wherein generating the random value of the declared parameter type for each parameter placeholder in the parameterized input query in the first database query language includes:

generating a first random value for a first parameter placeholder in the parameterized input query;

generating a SQL representation of the generated first random value;

confirming the SQL representation of the generated first random value is not a substring of the translated output query, not a substring of any of the other generated SQL representations of random values associated with the other parameter placeholders, and does not contain as a substring any of the other generated SQL representations of random values associated with the other parameter placeholders;

storing an association between the parameter placeholder and the generated first random value; and

storing an association between the parameter placeholder and the SQL representation of the generated first random value.

5. The method of claim 4, further comprising generating an error condition in response to attempting to generate a random value for a parameter more than a threshold number of times.

6. The method of claim 1 wherein replacing the generated random values within the translated output query in the second database query language with a placeholder value associated with the second database query language comprises using a regular expression to identify the generated random values within the translated output query.

7. The method of claim 1 wherein the parameter type is a non-list parameter type and the placeholder value associated with the second database query language is a single-character placeholder value.

8. The method of claim 1 wherein the parameter type is a list parameter type and the placeholder value associated with the second database query language is a multiple-character placeholder value.

9. The method of claim 1, further comprising saving a query record in a cache, wherein the query record relates the parameterized input query to the translated output query.

10. The method of claim 1, wherein the one or more parameter placeholders includes a first parameter that is a list-valued parameter, the method further comprising replacing a parameter placeholder that corresponds to the list-valued parameter in the translated output query with a sequence of single-character placeholder values.

14. The system of claim 10, wherein the second database query language is a Structured Query Language (SQL) and wherein generating the random value of the declared parameter type for each parameter placeholder in the parameterized input query in the first database query language includes:

generating a first random value for a first parameter placeholder in the parameterized input query;

generating a SQL representation of the generated first random value;

confirming the SQL representation of the generated first random value is not a substring of the translated output query, not a substring of any of the other generated SQL representations of random values associated with the other parameter placeholders, and does not contain as a substring any of the other generated SQL representations of random values associated with the other parameter placeholders;

storing an association between the parameter placeholder and the generated first random value; and

storing an association between the parameter placeholder and the SQL representation of the generated first random value.

15. The system of claim 4, the operations further comprising generating an error condition in response to attempting to generate a random value for a parameter more than a threshold number of times.

11. A system comprising:

one or more computers associated with an enterprise portal; and

a computer-readable medium coupled to the one or more computers having instructions stored thereon which, when executed by the one or more computers, cause the one or more computers to perform operations comprising;

identifying a parameterized input query in a first database query language, wherein the parameterized input query includes one or more parameter placeholders, each parameter placeholder defining a parameter name and a declared parameter type;

generating, for each parameter placeholder in the parameterized input query in the first database query language, a random value of the declared parameter type;

constructing a concrete instance of the parameterized input query in the first database query language, where the concrete instance of the parameterized input query replaces each parameter placeholder with the corresponding generated random value of the declared parameter type;

generating, from the concrete instance of the parameterized input query, a translated output query in a second database query language, the translated output query including the generated random value of the declared parameter type; and

replacing the generated random values within the translated output query in the second database query language with a placeholder value associated with the second database query language.

12. The system of claim 11, wherein the first database query language is a Domain Specific Language (DSL) and the second database query language is a Structured Query Language (SQL).

13. The system of claim 12, wherein the DSL is an extension of an ActiveRecord query language from a Ruby on Rails framework.

16. A computer program product encoded on a non-transitory storage medium, the product comprising non-transitory, computer readable instructions for causing one or more processors to perform operations comprising:

identifying a parameterized input query in a first database query language, wherein the parameterized input query includes one or more parameter placeholders, each parameter placeholder defining a parameter name and a declared parameter type;

generating, for each parameter placeholder in the parameterized input query in the first database query language, a random value of the declared parameter type;

constructing a concrete instance of the parameterized input query in the first database query language, where the concrete instance of the parameterized input query replaces each parameter placeholder with the corresponding generated random value of the declared parameter type;

generating, from the concrete instance of the parameterized input query, a translated output query in a second database query language, the translated output query including the generated random value of the declared parameter type; and

replacing the generated random values within the translated output query in the second database query language with a placeholder value associated with the second database query language.

17. The product of claim 16, wherein the first database query language is a Domain Specific Language (DSL) and the second database query language is a Structured Query Language (SQL).

18. The product of claim 17, wherein the DSL is an extension of an ActiveRecord query language from a Ruby on Rails framework.

19. The product of claim 16, wherein the second database query language is a Structured Query Language (SQL) and wherein generating the random value of the declared parameter type for each parameter placeholder in the parameterized input query in the first database query language includes:

generating a first random value for a first parameter placeholder in the parameterized input query;

generating a SQL representation of the generated first random value;

confirming the SQL representation of the generated first random value is not a substring of the translated output query, not a substring of any of the other generated SQL representations of random values associated with the other parameter placeholders, and does not contain as a substring any of the other generated SQL representations of random values associated with the other parameter placeholders;

storing an association between the parameter placeholder and the generated first random value; and

storing an association between the parameter placeholder and the SQL representation of the generated first random value.

20. The product of claim 16, the operations further comprising generating an error condition in response to attempting to generate a random value for a parameter more than a threshold number of times.