Systems and methods for programmatic generation of database statements

• US 8,972,433 B2
• Filed: 04/17/2009
• Issued: 03/03/2015
• Est. Priority Date: 04/18/2008
• Status: Active Grant

• Current Assignee: Travelport LP (Travelport Worldwide Ltd.)
• Original Assignee: Travelport Operations, Inc. (Travelport Worldwide Ltd.)
• Inventors: McLean, Scott, Folsom, Keith, Clough, Gabriel
• Primary Examiner(s): KUDDUS, DANIEL A
• Application Number:
  US12/738,350
• Publication Number:
  US 20110113054A1
• Time in Patent Office: 2,146 Days
• US Class Current: 707/760
• CPC Class Codes: G06F 16/8358 Query translation

Abstract

Computer-based systems and methods for programmatically generating code, such as Structure Query Language (SQL) code to perform an operation on a database. A code generation engine, referred to herein as an Alchemy engine, receives a request in a first language, such as an XML-based language, and translates the request into a second language, such as an SQL-based language. The translation process utilizes information stored in a metadata database.

Claims

1. A computer system comprising:

a computer database comprising a plurality of data tables;

a computer device, comprising a processor and memory, that executes an application that requires data from the database; and

a code generation engine, in communication with the computer device and the database, wherein the code generation engine comprises a processor circuit, a memory circuit, and a metadata database comprising computer database metadata, wherein the code generation engine is programmed to;

receive a data request from the application, wherein the data request is in a first language, wherein the data request indicates a data rule and a plurality of attributes corresponding to the data rule; and

translate the data request, using one or more translation formulas stored in the metadata database, to a plurality of data queries of the data tables of the computer database, wherein the plurality of data queries are in a second language that is different from the first language, wherein a first data query of the plurality of data queries returns a first data element stored at a first data table of the plurality of data tables as a first attribute of the plurality of attributes, and wherein a second data query of the plurality of data queries returns a result of an application of the one or more translation formulas to a second data element stored at a second table of the plurality of data tables as a second attribute of the plurality of attributes,

wherein the first data query comprises a first SQL statement and the second data query comprises a second SQL statement, and wherein the code generation engine is also programmed to execute the first and second SQL statements, and, in response to the data request, return a result set to the application, wherein the result set comprises the first attribute and the second attribute.

2. The computer system of claim 1, wherein the first language is an XML-based language.

3. The computer system of claim 1, wherein the second language is a SQL-based language.

4. The computer system of claim 1, wherein the data rule comprises an element selected from the group consisting of an attribute, a function, and a limit.

5. A computer-implemented method for retrieving data from a database, the method comprising:

storing data in a computer database comprising a plurality of data tables;

receiving from an application, executed by a computer system comprising a processor and a memory, a data request in a first language, wherein the data request indicates a data rule and a plurality of attributes corresponding to the data rule; and

translating with a code generation engine, comprising a processor circuit and a memory circuit, using one or more translation formulas stored in a metadata database, the data request to a plurality of data queries of the data tables of the computer database, wherein the plurality of data queries are in a second language that is different from the first language, wherein a first data query of the plurality of data queries returns a first data element stored at a first data table of the plurality of data tables as a first attribute of the plurality of attributes, and wherein a second data query of the plurality of data queries returns a result of an application of the one or more translation formulas to a second data element stored at a second table of the plurality of data tables as a second attribute of the plurality of attributes, wherein the first data query comprises a first SQL statement and the second data query comprises a second SQL statement, and wherein the code generation engine is also programmed to execute the first and second SQL statements, and, in response to the data request, return a result set to the application, wherein the result set comprises the first attribute and the second attribute, and wherein the code generation engine comprises a processor circuit, a memory circuit, and a metadata database comprising computer database metadata.

6. The method of claim 5, wherein the first language is an XML-based language.

7. The method of claim 5, wherein the second language is SQL-based language.

8. The method of claim 5, wherein the data rule comprises an element selected from the group consisting of an attribute, a function, and a limit.

9. The method of claim 5, further comprising storing a result set of the first and second data queries in a non-transitory computer readable medium.

10. A non-transitory computer readable medium having instructions stored thereon which when executed by a processor cause the processor to:

store data in a computer database comprising a plurality of data tables;

receive from an application, executed by a computer system comprising a processor and a memory, a data request in a first language, wherein the data request indicates a data rule and a plurality of attributes corresponding to the data rule;

translate the data request, using one or more translation formulas stored in a metadata database, to a plurality of data queries of the data tables of the computer database, wherein the plurality of data queries are in a second language that is different from the first language, wherein a first data query of the plurality of data queries returns a first data element stored at a first data table of the plurality of data tables as a first attribute of the plurality of attributes, and wherein a second data query of the plurality of data queries returns a result of an application of the one or more translation formulas to a second data element stored at a second table of the plurality of data tables as a second attribute of the plurality of attributes, wherein the first data query comprises a first SQL statement and the second data query comprises a second SQL statement;

execute the first and second SQL statements; and

in response to the data request, return a result set to the application, wherein the result set comprises the first attribute and the second attribute.

11. The computer readable medium of claim 10, wherein the data request is XML-based and the one or more data queries are SQL statement.

12. The computer readable medium of claim 10, wherein the first language is XML-based and the second language is SQL-based.

13. The computer readable medium of claim 10, further comprising instructions to store query data into a memory.