DATABASE QUERY LANGUAGE TRANSFORMATION METHOD, TRANSFORMATION APPARATUS AND DATABASE QUERY SYSTEM

• US 20090094216A1
• Filed: 04/03/2008
• Published: 04/09/2009
• Est. Priority Date: 06/23/2006
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Liu, Tao, Hu, Gang, Hou, Xue Qiao, Qian, Qian
• Granted Patent: US 9,223,827 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

The present invention provides a method for transforming a concept-based query into SQL query statements, comprising the steps of: transforming inputted concept-based queries into logic rules; checking validity of the logic rules; optimizing the valid logic rules; and translating the logic rules into SQL query statements in accordance with the optimization result. The present invention further provides corresponding transformation engine, database query system and computer program product. The present invention facilitates people to use more descriptive query language to query data stored in relational database. Furthermore, the present invention can automatically generate the correct and efficient SQL query statements so that people'"'"'s efforts to deal with complex data model are saved significantly.

Claims

1. A method for transforming a concept-based query into SQL query statements, comprising the steps of:

transforming an inputted concept-based query into logic rules;

checking validity of said logic rules;

optimizing logic rules that are valid; and

translating optimized logic rules into SQL query statements.

2. The method according to claim 1, all the limitations of which are incorporated herein by reference, wherein said step of transforming an inputted concept-based query into logic rules further comprises the steps of:

performing constraint normalization on said inputted concept-based query;

performing conceptual model relational inference on the constraint-normalized query statement in accordance with conceptual model;

performing transformation from the concepts in the conceptual model to the predicates in the logic rules and from the properties in the conceptual model to the variables in the logic rules based on a predetermined mapping table to obtain a transformed query statement; and

performing normal-form normalization on the transformed query statement to obtain the logic rules.

3. The method according to claim 2, all the limitations of which are incorporated herein by reference, wherein said step of performing conceptual model relational inference on the constraint-normalized query statement further comprises:

performing subsumption inference on the constraint-normalized query statement; and

said step of performing normal-form normalization on the transformed query statement to obtain the logic rules comprises;

performing disjunctive normal-form normalization on the transformed query statement.

4. The method according to claim 1, all the limitations of which are incorporated herein by reference, wherein said step of checking validity of the logic rules further comprises the steps of:

syntax checking for checking whether or not the logic rules that have undergone normal-form normalization conform to the defined syntax of the logic rule descriptive language; and

semantic checking for checking whether or not the logic rules are logically valid.

5. The method according to claim 4, all the limitations of which are incorporated herein by reference, wherein said step of syntax checking further comprises:

safe rule checking of the logic rules, including existence checking of the predicates and the variables and consistency checking of the predicates and the variables; and

said step of semantic checking further comprises;

checking whether or not there are non-linear recursive predicates, checking whether or not there are indirect recursive predicates, and checking whether or not there is unstratified negation.

6. The method according to claim 1, all the limitations of which are incorporated herein by reference, wherein the step of optimizing further comprises the steps of:

building a dependency graph in accordance with dependency relations amongst the predicates in the logic rules, wherein every node corresponds to one predicate in the logic rules and dependency relations amongst the respective predicates are denoted by directed edges amongst nodes; and

optimizing the logic rules in accordance with said dependency graph.

7. The method according to claim 6, all the limitations of which are incorporated herein by reference, wherein said step of optimizing the logic rules in accordance with said dependency graph comprises the steps of:

generating an optimal processing order for predicates, in accordance with said dependency graph; and

pushing the constraints in higher level predicates in the dependency graph downwards to lower level predicates in the dependency graph.

8. The method according to claim 3, all the limitations of which are incorporated herein by reference, wherein said step of translating the logic rules into SQL query statements comprises the steps of:

translating every disjunction in the logic rules that have undergone disjunctive normal-form normalization into an SQL SELECT query statement; and

joining the respective separate SQL SELECT query statements by using the keyword UNION ALL,

wherein based on the predetermined mapping table, the predicates in the logic rules are transformed into table names of a relational database, and the variables in the logic rules are transformed into column names of the relational database.

9. The method according to claim 8, all the limitations of which are incorporated herein by reference, wherein said step of translating every disjunction in the logic rules that have undergone disjunctive normal-form normalization into an SQL SELECT query statement comprises the steps of:

constructing the SELECT block of the SQL query statement by using column names corresponding to the arguments in the head of the logic rule as attributes that follow the keyword SELECT;

constructing the FROM block of the SQL query statement by using table names corresponding to the predicates involved in the body of the logic rule as table names that follow the keyword FROM; and

constructing the WHERE block of the SQL query statement by analyzing relations amongst the variables of respective predicates in the logic rule and predicates expressing relation operations.

10. The method according to claim 8, all the limitations of which are incorporated herein by reference, wherein the recursive logic rule is translated into an SQL query statement using a common table expression, wherein table name corresponding to the recursive predicate in the recursive logic rule is used as table name in said common table expression.

11. A transformation apparatus for transforming a concept-based query into SQL query statements, comprising:

a transformer for transforming an inputted concept-based query into logic rules;

a checker for checking validity of the logic rules outputted from said transformer;

an optimizer for optimizing the logic rules that have been checked to be valid by said checker; and

a translator for translating optimized logic rules into SQL query statements.

12. The transformation apparatus according to claim 11, all the limitations of which are incorporated herein by reference, further comprising a mapping table memory for providing said transformer with concept-to-predicate and property-to-variable mapping information, and

said transformer further comprising:

means for performing constraint normalization on the inputted concept-based query;

means for performing conceptual model relational inference on the constraint-normalized query statement in accordance with conceptual model;

means for performing transformation from the concepts in the conceptual model to the predicates in the logic rules and from the properties in the conceptual model to the variables in the logic rules based on a predetermined mapping table to obtain a transformed query statement; and

means for performing normal-form normalization on the transformed query statement to obtain logic rules.

13. The transformation apparatus according to claim 12, all the limitations of which are incorporated herein by reference, wherein

said means for performing conceptual model relational inference on the constraint-normalized query statement comprises:

means for performing subsumption inference on the constraint-normalized query statement; and

said means for performing normal-form normalization on the transformed query statement to obtain logic rules comprises;

means for performing disjunctive normal-form normalization on the transformed query statement.

14. The transformation apparatus according to claim 12, all the limitations of which are incorporated herein by reference, wherein said checker comprises:

a syntax checker for checking whether or not the logic rules that have undergone normal-form normalization conform to the defined syntax of the defined logic rule descriptive language; and

a semantic checker for checking whether or not the logic rule are logically valid.

15. The transformation apparatus according to claim 14, all the limitations of which are incorporated herein by reference, wherein

said syntax checker further performs safe rule checking on the logic rules, including existence checking of the predicates and the variables and consistency checking of the predicates and the variables;

and

said semantic checker further checks whether or not there are non-linear recursive predicates, whether nor not there are indirect recursive predicates, and whether or not there is unstratified negation.

16. The transformation apparatus according to claim 11, all the limitations of which are incorporated herein by reference, wherein said optimizer further comprises:

means for building a dependency graph in accordance with dependency relations amongst the predicates in the logic rules, wherein every node corresponds to one predicate in the logic rules and the dependency relations amongst the respective predicates are denoted by directed edges among nodes; and

means for optimizing the logic rules in accordance with said dependency graph.

17. The transformation apparatus according to claim 16, all the limitations of which are incorporated herein by reference, wherein said means for optimizing the logic rules in accordance with said dependency graph comprises:

means for generating an optimal processing order for predicates, in accordance with the dependency graph; and

means for pushing the constraints in higher level predicates of the dependency graph downwards to lower level predicates of the dependency graph.

18. The transformation apparatus according to claim 13, all the limitations of which are incorporated herein by reference, wherein said mapping table memory is further used to provide said translator with predicate-to-relation and variable-to-entity mapping information;

and

said translator comprises;

means for translating every disjunction in the logic rules that have undergone disjunctive normal-form normalization into an SQL SELECT query statement; and

keyword UNION ALL,

wherein based on the predetermined mapping table, the predicates in the logic rules are transformed into table names of a relational database, and the variables in the logic rules are transformed into column names of the relational database.

19. The transformation apparatus according to claim 18, all the limitations of which are incorporated herein by reference, said means for translating every disjunction in the logic rules that have undergone disjunctive normal-form normalization into a SQL SELECT query statement performing the following operation:

constructing the SELECT block of the SQL query statement by using column names corresponding to the arguments in the head of the logic rule as attributes that follow the keyword SELECT;

constructing the FROM block of the SQL query statement by using table names corresponding to the predicates involved in the body of the logic rule as table names that follow the keyword FROM; and

constructing the WHERE block of the SQL query statement by analyzing relations amongst the variables of respective predicates in the logic rule and predicates expressing relation operations.

20. The transformation apparatus according to claim 18, all the limitations of which are incorporated herein by reference, wherein said translator translates the recursive logic rule into an SQL query statement using a common table expression, wherein table name corresponding to the recursive predicate in the recursive logic rule is used as table name in said common table expression.

21. A computer program product containing computer program codes for implementing a method further comprising:

transforming an inputted concept-based query into logic rules;

checking validity of said logic rules;

optimizing logic rules that are valid; and

translating optimized logic rules into SQL query statements.