Systems and methods for programmatic generation of database statements
View Patent ↗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.
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.