Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

Translating Queries in Relational Databases Using Schema Mapping

IP.com Disclosure Number: IPCOM000010707D
Original Publication Date: 2003-Jan-10
Included in the Prior Art Database: 2003-Jan-10
Document File: 4 page(s) / 83K

Publishing Venue

IBM

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 45% of the total text.

Page 1 of 4

Translating Queries in Relational Databases Using Schema Mapping

  An algorithm is disclosed in which relational database queries (SQL) in one database schema can be translated to queries in another schema, using the mapping between the two schemas.

Database query translation has many important applications, especially in database migration. Almost every enterprise application runs a database system to manage various data available. Over years of operation, business processes need to be improved and business logic calls for changes. With the inevitable demands for database upgrading, database schema will be changed and the numerous queries in legacy code need to be translated into the new schema. Besides, there are many cases in which databases are migrated from one vendor to another.

In our algorithm:

1) We represent the schema mapping as table/column mapping in the form of XML file so that ordinary users can understand, create, and modify the mapping; and
2) We use abstract syntax tree as the internal representation of the queries, instead of hypergraph or Datalog that are commonly used in many SQL processors.

This algorithm is a practical, convenient and efficient way to translate SQL queries. It is simple for users to create the schema mapping information. Based on this mapping information, the algorithm applies the heuristics rules to translate SQL queries. To guarantee the correctness of the translation, a complementary validation tool is used.

This algorithm has two advantages over existing methods.

First, though query transformation has been widely studied, there are no practical methods, tools, or prototypes available today. Most researches have been focusing on the correctness of the transformation. Henceforth, the schema mapping is required to be represented as view definitions which are difficult to obtain. On the other hand, our approach focuses on features such as efficiency and usability.

Second, there are also extensive works on SQL implementations, especially query optimization and rewriting using view definitions. To some extent, query rewriting using views can be regarded as a kind of query transformation. However, that kind of work often cannot be reused because of two reasons. (1) Query optimization focus on producing optimized code. (2) Query optimization is performed on the internal representation of the queries. The optimized code is also in the form of internal representation. It won't produce the SQL queries we need.

The overall structure of the query transformation is described in the following diagram:

1

Page 2 of 4

Parse SQL query

Traverse the tree, transform the node

Abstract syntax tree

Transformed Abstract syntax tree

Recover the query

SQL query in old schema

Schema Mapping

SQL query in new schema

Query translation is performed in four steps.

1) Firstly, the query in the source schema is parsed as an abstract syntax tree. Every syntactic element such as where condition in the query is represented as a...