Virtualizing schema relations over a single database relation
According to one embodiment of the present invention, a system maps one or more virtual relations to a table of a relational database management system. The system generates a structured query language (SQL) statement for the table from a SQL statement for a virtual relation by applying the mapping to one or more elements of the SQL statement for the virtual relation. Embodiments of the present invention further include a method and computer program product for mapping virtual relations to a table in substantially the same manners described above.
1. A computer-implemented method comprising:
receiving a first structured query language (SQL) statement including a virtual relation;
mapping the virtual relation to a base table of a relational database management system by:
referencing a row of a first table of metadata corresponding to the virtual relation and having first information regarding the virtual relation,
referencing at least one row of a second table of metadata, each of the at least one row of the second table of metadata including second information regarding a respective field of at least one field associated with the virtual relation, and
referencing at least one row of the base table, each of the at least one row of the base table including an instance of a field value corresponding to the respective field of the at least one field associated with the virtual relation;
generating a second SQL statement for the base table from the first SQL statement including the virtual relation by applying the mapping to one or more elements of the first SQL statement for the virtual relation; and
applying the second SQL statement against real relations of the base table.
2. The computer-implemented method of claim 1 , further comprising:
mapping to the base table each of a plurality of virtual relations specified via one or more data definition language (DDL) statements.
3. The computer-implemented method of claim 1 , wherein the first SQL statement for the virtual relation is used to generate the second SQL statement for the base table and includes data manipulation language (DML) statements.
4. The computer-implemented method of claim 1 , wherein the generating of the second SQL statement for the base table includes transforming one or more predicates of the first SQL statement for the virtual relation into a SQL string to select identifiers of rows of the base table containing values belonging to one or more instances of the virtual relation that satisfy the one or more predicates of the first SQL statement for the virtual relation.
5. The computer-implemented method of claim 1 , wherein the first SQL statement for the virtual relation is a SELECT statement, and the method further comprises:
receiving results of applying the generated second SQL statement against the base table; and
transforming the received results into a result set for the first SQL statement for the virtual relation.
6. The computer-implemented method of claim 1 , wherein the first SQL statement for the virtual relation is received from a client application and includes a selected one of a DML operation and a DDL operation, and transactional and ACID properties of the selected operation are respected by passing a selected one of a COMMIT operation and a ROLLBACK operation of the client application through to a database management system that manages the base table.
7. The computer-implemented method of claim 1 , further comprising passing a SQL statement lacking an association with any virtual relation through from a client application to a database management system to provide the client application access to the base table and the virtual relation.
8. The computer-implemented method of claim 1 , wherein the first SQL statement for the virtual relation comprises a SELECT statement, and the generated second SQL statement for the base table includes a predicate filter based on a projection specification in the SELECT statement to reduce data retrieval from the base table.
9. The computer-implemented method of claim 1 , wherein the base table includes a plurality of container columns storing distinct data types, and each of the at least one field is mapped to a corresponding one of the plurality of container columns storing a data type for that field.
10. The computer-implemented method of claim 1 , further comprising:
identifying whether the first SQL statement includes a data definition language verb; and
modifying, when the first SQL statement is identified as including the data definition language verb, at least one of the first table and the second table without modifying the base table.