U.S. patent application number 13/397710 was filed with the patent office on 2012-10-04 for system and method for processing an sql query made against a relational database.
Invention is credited to Sudarshan Srinivasa Murthy.
Application Number | 20120254178 13/397710 |
Document ID | / |
Family ID | 46928643 |
Filed Date | 2012-10-04 |
United States Patent
Application |
20120254178 |
Kind Code |
A1 |
Murthy; Sudarshan
Srinivasa |
October 4, 2012 |
SYSTEM AND METHOD FOR PROCESSING AN SQL QUERY MADE AGAINST A
RELATIONAL DATABASE
Abstract
A system and method for processing an SQL query made against a
relational database is disclosed. In one example embodiment, the
method includes receiving the SQL query made against the relational
database. Further, the received SQL query is parsed to obtain each
operator and associated one or more operands and sequence of
execution of the operators. Furthermore, a closure-friendly
operator is dynamically generated for each operator and the
associated one or more operands in the received SQL query. In
addition, the dynamically generated closure-friendly operators are
executed based on the obtained sequence of execution of the
operators.
Inventors: |
Murthy; Sudarshan Srinivasa;
(Bangalore, IN) |
Family ID: |
46928643 |
Appl. No.: |
13/397710 |
Filed: |
February 16, 2012 |
Related U.S. Patent Documents
|
|
|
|
|
|
Application
Number |
Filing Date |
Patent Number |
|
|
61468581 |
Mar 29, 2011 |
|
|
|
Current U.S.
Class: |
707/737 ;
707/755; 707/E17.044; 707/E17.089 |
Current CPC
Class: |
G06F 16/24537
20190101 |
Class at
Publication: |
707/737 ;
707/755; 707/E17.044; 707/E17.089 |
International
Class: |
G06F 17/30 20060101
G06F017/30 |
Claims
1. A computer-implemented method for processing an SQL query made
against a relational database, comprising: parsing the SQL query to
obtain each operator and associated one or more operands and
sequence of execution of the operators; dynamically generating a
closure-friendly operator for each operator and the associated one
or more operands in the SQL query; and executing the dynamically
generated closure-friendly operators based on the obtained sequence
of execution of the operators.
2. The computer-implemented method of claim 1, further comprising:
receiving the SQL query made against the relational database.
3. The computer-implemented method of claim 1, wherein dynamically
generating the closure-friendly operator for each operator and the
associated one or more operands in the SQL query comprises:
dynamically generating the closure-friendly operator for each
operator and the associated one or more operands using optimization
techniques.
4. The computer-implemented method of claim 3, wherein the
optimization techniques are selected from the group consisting of
just-in-time compilation, code caching, code libraries, lazy
evaluation and the like.
5. The computer-implemented method of claim 1, further comprising:
grouping each dynamically generated closure-friendly operator into
possibly overlapping patterns; and classifying impact of each
pattern on a queried relational database and a database schema.
6. The computer-implemented method of claim 5, further comprising:
analyzing the SQL query using partitions of the closure-friendly
operators; and tracing/debugging the SQL query based on the
analysis.
7. A computer-implemented method for processing an SQL query made
against a relational database, comprising: receiving the SQL query
made against the relational database; parsing the received SQL
query to obtain each operator and associated one or more operands
and sequence of execution of the operators; dynamically generating
a closure-friendly operator for each operator and the associated
one or more operands in the received SQL query; grouping each
dynamically generated closure-friendly operator into possibly
overlapping patterns; and classifying impact of each pattern on a
queried relational database and a database schema.
8. The computer-implemented method of claim 7, further comprising:
analyzing the received SQL query using partitions of the
closure-friendly operators; and tracing/debugging the received SQL
query based on the analysis.
9. The computer-implemented method of claim 7, wherein dynamically
generating the closure-friendly operator for each operator and the
associated one or more operands in the received SQL query
comprises: dynamically generating the closure-friendly operator for
each operator and the associated one or more operands using
optimization techniques.
10. The computer-implemented method of claim 9, wherein the
optimization techniques are selected from the group consisting of
just-in-time compilation, code caching, code libraries, lazy
evaluation and the like.
11. A system for processing an SQL query made against a relational
database, comprising: one or more clients; a network; a relational
database; and a computer coupled to the one or more clients and the
relational database via the network, where in the computer
comprises: a processor; memory operatively coupled to the
processor; and a database management system including a
closure-friendly SQL query processor residing in the memory,
wherein the closure-friendly SQL query processor receives the SQL
query made against the relational database from the one or more
clients via the network, wherein the closure-friendly SQL query
processor parses the received SQL query to obtain each operator and
associated one or more operands and sequence of execution of the
operators, wherein the closure-friendly SQL query processor
dynamically generates a closure-friendly operator for each operator
and the associated one or more operands in the received SQL query,
wherein the closure-friendly SQL query processor executes the
dynamically generated closure-friendly operators based on the
obtained sequence of execution of the operators.
12. The system of claim 11, wherein the closure-friendly SQL query
processor dynamically generates the closure-friendly operator for
each operator and the associated one or more operands using
optimization techniques.
13. The system of claim 12, wherein the optimization techniques are
selected from the group consisting of just-in-time compilation,
code caching, code libraries, lazy evaluation and the like.
14. The system of claim 11, wherein the closure-friendly SQL query
processor groups each dynamically generated closure-friendly
operator into possibly overlapping patterns, and wherein the
closure-friendly SQL query processor classifies impact of each
pattern on a queried relational database and a database schema.
15. The system of claim 14, wherein the closure-friendly SQL query
processor. analyzing the received SQL query using partitions of the
closure-friendly operators; and tracing/debugging the received SQL
query based on the analysis.
16. A non-transitory computer-readable storage medium for
processing an SQL query made against a relational database, having
instructions that, when executed by a computing device cause the
computing device to: receiving the SQL query made against the
relational database; parsing the received SQL query to obtain each
operator and associated one or more operands and sequence of
execution of the operators; dynamically generating a
closure-friendly operator for each operator and the associated one
or more operands in the received SQL query; and executing the
dynamically generated closure-friendly operators based on the
obtained sequence of execution of the operators.
17. The non-transitory computer-readable storage medium of claim
16, wherein dynamically generating the closure-friendly operator
for each operator and the associated one or more operands in the
received SQL query comprises: dynamically generating the
closure-friendly operator for each operator and the associated one
or more operands using optimization techniques.
18. The non-transitory computer-readable storage medium of claim
17, wherein the optimization techniques are selected from the group
consisting of just-in-time compilation, code caching, code
libraries, lazy evaluation and the like.
19. The non-transitory computer-readable storage medium of claim
16, further comprising: grouping each dynamically generated
closure-friendly operator into possibly overlapping patterns; and
classifying impact of each pattern on a queried relational database
and a database schema.
20. The non-transitory computer-readable storage medium of claim
19, further comprising: analyzing the received SQL query using
partitions of the closure-friendly operators; and tracing/debugging
the received SQL query based on the analysis.
Description
TECHNICAL FIELD
[0001] The present invention relates generally to database
management, and more particularly to an SQL (structured query
language) query made against a relational database.
BACKGROUND
[0002] Generally, an SQL (structured query language) query
processor in a relational database management system (RDBMS)
processes an SQL query by using a composition of universal
parameterized functions to implement different clauses of the SQL
query, which are essentially operators such as, FROM (F), WHERE
(W), GROUP BY (G) and SELECT (L). Further, the universal
parameterized functions take input parameters based on their
implementation. For example, the universal parameterized function
corresponding to the F operator takes an array of relations as
input parameter and the universal parameterized function
corresponding to the W operator takes a relation and a filter
condition as input parameters.
[0003] Existing SQL query processors model the F, W, G and L
operators such that a single universal parameterized function can
support any set of input parameters used with its corresponding SQL
clauses. For example, the same W operator can be used with any
filter condition such as, a>5, a<4 and so on, over any
relational database. Thus, the universal parameterized functions
are context independent. They are implementation friendly and
promote code reuse, but they are not congruent with formal closure,
a fundamental property of SQL queries or of relational data under
SQL queries. As a result, using universal parameterized functions
does not aid formal verification of SQL queries. The incongruence
of the universal parameterized functions with formal closure
property results in inefficient traceability of the SQL queries.
Also, the above described SQL query process does not provide type
safety over the input parameters and output.
SUMMARY
[0004] A system and method for processing an SQL query made against
a relational database is disclosed. In accordance with one aspect
of the present invention, the SQL query made against the relational
database is received. Further, the received SQL query is parsed to
obtain each operator and associated one or more operands and
sequence of execution of the operators. Furthermore, a
closure-friendly operator is dynamically generated for each
operator and the associated one or more operands in the received
SQL query. In addition, the dynamically generated closure-friendly
operators are executed based on the obtained sequence of execution
of the operators.
[0005] Also in this aspect of the present invention, each
dynamically generated closure-friendly operator is grouped into
possibly overlapping patterns. Further, the impact of each pattern
on a queried relational database and a database schema is
classified. Furthermore, the received SQL query is analyzed using
partitions of the closure-friendly operators. In addition, the
received SQL query is traced/debugged based on the analysis.
[0006] According to another aspect of the present subject matter, a
non-transitory computer-readable storage medium for processing the
SQL query made against the relational database, having instructions
that, when executed by a computing device causes the computing
device to perform the method described above.
[0007] According to yet another aspect of the present invention, a
system for processing the SQL query made against the relational
database includes one or more clients, a network and a relational
database. Further, a computer is coupled to the one or more clients
and the relational database via the network. Furthermore, the
computer includes a processor and memory. The memory is operatively
coupled to the processor. In addition, the computer includes a
database management system including a closure-friendly SQL query
processor residing in the memory.
[0008] In one embodiment, the closure-friendly SQL query processor
receives the SQL query made against the relational database from
one or more clients via the network. Further, the closure-friendly
SQL query processor parses the received SQL query to obtain each
operator and the associated one or more operands and the sequence
of execution of the operators. Furthermore, the closure-friendly
SQL query processor dynamically generates the closure-friendly
operator for each operator and the associated one or more operands
in the received SQL query. Also, the closure-friendly SQL query
processor executes the dynamically generated closure-friendly
operators based on the obtained sequence of execution of the
operators.
[0009] The methods and systems disclosed herein may be implemented
in any means or combination of means for achieving various aspects,
and other features will be apparent from the accompanying drawings
and from the detailed description that follows.
BRIEF DESCRIPTION OF THE DRAWINGS
[0010] Various embodiments are described herein with reference to
the drawings, wherein:
[0011] FIG. 1 illustrates a process flow of a method for processing
an SQL query made against a relational database, according to one
embodiment;
[0012] FIG. 2 is an exemplary pseudo-code illustrating dynamically
generated closure-friendly operators for an SQL query, according to
one embodiment;
[0013] FIG. 3A is a state diagram illustrating an SQL query
evaluated over a database D, according to one embodiment;
[0014] FIG. 3B illustrates a table including operator patterns over
the database D, such as the one shown in FIG. 3A, according to one
embodiment;
[0015] FIG. 4A is another state diagram illustrating an SQL query
evaluated over a database schema S, according to one
embodiment;
[0016] FIG. 4B illustrates a table including operator patterns over
the database schema S, such as the one shown in FIG. 4A, according
to one embodiment;
[0017] FIG. 5 is a generalized state diagram illustrating an SQL
query evaluated over a set of database schemas, according to one
embodiment;
[0018] FIG. 6 is a table illustrating operator patterns over the
database D and its database schema S, according to one
embodiment;
[0019] FIG. 7 is a table illustrating extended operator patterns
over a database D.sup.U, a database D.sup.+ and a database schema
S.sup.U, according to one embodiment; and
[0020] FIG. 8 illustrates a block diagram of a networked computer
system incorporating a database management system for processing an
SQL query made against the relational database, such as those shown
in FIG. 1, according to one embodiment;
[0021] The drawings described herein are for illustration purposes
only and are not intended to limit the scope of the present
invention in any way.
DETAILED DESCRIPTION
[0022] A system and method for processing an SQL query made against
a relational database is disclosed. In the following detailed
description of the embodiments of the invention, reference is made
to the accompanying drawings that form a part hereof, and in which
are shown by way of illustration specific embodiments in which the
invention may be practiced. These embodiments are described in
sufficient detail to enable those skilled in the art to practice
the invention, and it is to be understood that other embodiments
may be utilized and that changes may be made without departing from
the scope of the present invention. The following detailed
description is, therefore, not to be taken in a limiting sense, and
the scope of the present invention is defined by the appended
claims.
[0023] The terms "operator" and "function" are used interchangeably
throughout the document.
[0024] FIG. 1 illustrates a flow diagram 100 of an exemplary
computer-implemented method for processing an SQL query made
against a relational database, according to one embodiment. At
block 102, the SQL query made against the relational database is
received. At block 104, the received SQL query is parsed to obtain
each operator and associated one or more operands and sequence of
execution of the operators. Exemplary operators include FROM (F),
WHERE (W), GROUP BY (G), SELECT (L) and the like. Exemplary
operands include parameters such as filter criteria, tables and the
like. Generally, F, W, G and L operators are executed in the
aforesaid sequence. In this embodiment, each of the F, W, G and L
operator is treated as a potentially distinct operator. Also in
this embodiment, each SQL query is treated as a potentially
distinct operator.
[0025] At block 106, a closure-friendly operator is dynamically
generated for each operator and the associated one or more operands
in the received SQL query. This is explained in more detail with
reference to FIG. 2. Exemplary pseudo-codes for possible
dynamically generated closure-friendly operators for queries Q1
"SELECT A FROM R WHERE A>5" and Q2 "SELECT A, B FROM R1, R2
WHERE A>B" are included in Appendix "A" and Appendix "B",
respectively. In this embodiment, the dynamically generated
closure-friendly operators aid closure of the relational database
under SQL queries. This is explained in more detail with reference
to FIGS. 3A, 4A and 5.
[0026] In one embodiment, at block 108, after the closure-friendly
operator is dynamically generated for each operator and the
associated one or more operands, the dynamically generated
closure-friendly operators are executed based on the obtained
sequence of execution of the operators. For example, an SQL query
"SELECT*FROM R WHERE a>5" is executed by dynamically generating
closure-friendly operators for the L, F and W operators with the
associated operands "*", "R" and "a>5", respectively. Initially,
the dynamically generated closure-friendly operator corresponding
to the F operator computes a cross product of the relation R over
the input and generates a table. Further, the dynamically generated
closure-friendly operator corresponding to the W operator applies
the operand "a>5", which is a filter criterion, to the table
generated by the F operator and generates an intermediate table.
Furthermore, the dynamically generated closure-friendly operator
corresponding to the L operator generates a table which is the
result of the SQL query.
[0027] In addition in this embodiment, the closure-friendly
operator is dynamically generated for each operator and the
associated one or more operands possibly using optimization
techniques. Exemplary optimization techniques include just-in-time
(JIT) compilation, code caching, code libraries, lazy evaluation
and the like. The optimization techniques can also include
traditional optimization techniques, such as join order, push
selects down and the like. Further, the optimization techniques can
be implemented using functional techniques, procedural techniques,
object oriented techniques and so on. For example, the JIT
compilation technique is used to dynamically generate native codes
for the closure-friendly operators. Further, the dynamically
generated closure-friendly operators are cached using the code
caching technique. Furthermore, the cached closure-friendly
operators may be reused when executing the received SQL query which
may benefit from the cached closure-friendly operators. In one
embodiment, generics can be employed to ease the process of
dynamically generating the closure-friendly operators and to obtain
type safety.
[0028] Further as shown in FIG. 1, at block 114, the received SQL
query is analyzed using partitions of the closure-friendly
operators. At block 116, the received SQL query is traced/debugged
based on the analysis.
[0029] In another embodiment, at block 110, after the
closure-friendly operators are dynamically generated for each
operator and the associated one or more operands, each dynamically
generated closure-friendly operator is grouped into possibly
overlapping patterns. At block 112, the impact of each pattern on
the queried relational database and the database schema is
classified. This is explained in more detail with reference to
FIGS. 6 and 7. At block 114, the received SQL query is analyzed
using partitions of the closure-friendly operators. At block 116,
the received SQL query is traced/debugged based on the
analysis.
[0030] Now, referring to FIG. 2, an exemplary pseudo-code
illustrates dynamically generated closure-friendly operators for an
SQL query, according to one embodiment. Particularly, FIG. 2
illustrates relevant portions of the dynamically generated
closure-friendly operators for an SQL query Q3 "SELECT A FROM R
WHERE A>5". As shown in FIG. 2, Q3 includes clauses "SELECT A",
"FROM R" and "WHERE A>5". The arrow 202, shown in FIG. 2, points
to a portion of the pseudo-code dynamically generated for the
operand "R" associated with the FROM operator in Q3. Further, the
arrow 204, shown in FIG. 2, points to a portion of the pseudo-code
dynamically generated for the operand "A>5" associated with the
WHERE operator in Q3.
[0031] Now, referring to FIG. 3A, a state diagram 300A illustrates
an SQL query evaluated over a database D, according to one
embodiment. As shown, two additional databases, a database D.sup.+
and a database D.sup.U, are defined over the database D. The
database D.sup.+ includes every possible instance of the database
D. Further, the database D.sup.+ also includes a table without any
rows. The database D.sup.U is a universal database which includes
all possible databases that can be obtained in a typical SQL
query.
[0032] Further as shown in FIG. 3A, the state diagram 300A includes
three possible states. The possible states include a state D, a
state (D.sup.+-D) and a state (D.sup.U-D.sup.+). The state D
includes the database D. The state (D.sup.+-D) includes the
database D.sup.+ without any data contained in the database D. The
state (D.sup.U-D.sup.+) includes the database D.sup.U without any
data contained in the database D. Furthermore, the state diagram
300A is illustrated using unified modeling language (UML) syntax.
As shown in FIG. 3A, 320 refers to a start state. In addition as
shown in FIG. 3A, .epsilon. 322 represents a state transition from
the start state 320 to the state D. However, while describing state
transitions in the state diagram 300A, the state D is considered as
the start state. Also, FIG. 3A illustrates state transitions
corresponding to F, W, G, L, UNION (U), MINUS (M), INTERSECT (I)
and DIVIDE (D) operators in the SQL query.
[0033] Referring now to FIG. 3B, a table 300B shows the state
transitions corresponding to FWGL operators. Particularly, table
300B shows operator patterns over the database D. In table 300B,
`X` indicates an applicability of the operator pattern to a
specific input-output combination. Also in table 300B, `-`
indicates an inapplicability of the operator pattern to a specific
input-output combination. In addition, the state transitions for U,
M, and I operators are same as those for the W operator and the
state transitions for the D operator are same as those for the L
operator. The state transitions illustrated in the state diagram
300A are obtained using the table 300B.
[0034] Referring back to FIG. 3A, the operators causing the state
transitions are labeled with one or two suffix characters to denote
a source state and a destination state. The operators labeled with
one suffix character indicate a transition from a state to itself.
The operators labeled with two suffix characters indicate
transition between distinct states.
[0035] Further as shown in FIG. 3A, a transition (FWGLUMID).sub.D
302 represents the FWGLUMID operators that operate on data from the
state D and produces a data in the state D. Further, a transition
(FWGLUMID).sub.D+ 304 represents the FWGLUMID operators that
operate on data from the state D and produces a data in the state
(D.sup.+-D). In addition, a transition (FGLD).sub.DU 306 represents
the FGLD operators that operate on data from the state D and
produces a data in the state (D.sup.U-D.sup.+).
[0036] Furthermore as shown in FIG. 3A, a transition
(FWGLUMID).sub.+ 308 represents the FWGLUMID operators that
operates on data from the state (D.sup.+-D) and produces a data in
the state (D.sup.+-D). In addition, a transition (FWGLUMID).sub.+D
310 represents the FWGLUMID operators that operate on data from the
state (D.sup.+-D) and produces a data in the state D. Also, a
transition (FGLD).sub.+U 312 represents the FGLD operators that
operate on data from the state (D.sup.+-D) and produces a data in
the state (D.sup.U-D.sup.+).
[0037] In addition as shown in FIG. 3A, a transition
(FWGLUMID).sub.U 314 represents the FWGLUMID operators that operate
on data from the state (D.sup.U-D.sup.+) and produces a data in the
state (D.sup.U-D.sup.+). Further, a transition (FGLD).sub.UD 316
represents the FGLD operators that operate on data from the state
(D.sup.U-D.sup.+) and produces a data in the state D. Furthermore,
a transition (FGLD).sub.U+ 318 represents the FGLD operators that
operate on data from the state (D.sup.U-D.sup.+) and produces a
data in the state (D.sup.+-D).
[0038] Also, the state diagram 300A is used to trace progression of
the SQL query without having to the SQL query. For example, an SQL
query Q4 "SELECT*FROM R" starts executing at the state D. As shown
in FIG. 3A, F and L operators operating on data from the state D,
produces a data in the state D. Thus, the state transitions for the
SQL query Q4 are D, D and D. As a result, the SQL query Q4 starts
executing from the state D and ends at the state D. However, it is
not always possible to trace the data transitions for an SQL query
without executing the SQL query. However, database schema
transitions of the SQL query can be traced without having to
execute the SQL query. This is explained in more detail with
reference to FIGS. 4A and 4B.
[0039] Now, referring to FIG. 4A, which illustrates another state
diagram 400A of an SQL query evaluated over a database schema S,
according to one embodiment. As shown, an additional database
schema is defined over the database schema S. The additional
database schema is a universal database schema S.sup.U. S.sup.U
includes all possible database schemas that can be obtained in a
typical SQL query. Further as shown, the state diagram 400A
includes two possible states, a state S and a state (S.sup.U-S).
The state S includes the database schema S and the state
(S.sup.U-S) includes the universal database schema S.sup.U without
any schema contained in database schema S. Furthermore, as in state
diagram 300A shown in FIG. 3A, the state diagram 400A is also
illustrated using the UML syntax. As shown in FIG. 4A, 410 refers
to a start state. In addition as shown in FIG. 4A, .epsilon. 412
represents a state transition from the start state 410 to the state
S. However, while describing state transitions in the state diagram
400A, the state S is considered as the start state. Also, FIG. 4A
illustrates the state transitions corresponding to F, W, G, L, U,
M, I and D operators in the SQL query.
[0040] Referring now to FIG. 4B, a table 400B shows the state
transitions corresponding to FWGL operators. Particularly, table
400B shows operator patterns over the database schema S. In table
400B, `X` indicates the applicability of the operator pattern to
the specific input-output combination. Also in table 400B, `-`
indicates inapplicability of the operator pattern to the specific
input-output combination. In addition, the state transitions for U,
M, and I operators are same as those for W operator and the state
transitions for the D operator are same as those for L operator.
The state transitions illustrated in the state diagram 400A are
obtained using the table 400B.
[0041] Referring back to FIG. 4A, the operators causing the state
transitions are labeled with one or two suffix characters to denote
a source state and a destination state. The operators labeled with
one suffix character indicate a transition from a state to itself.
The operators labeled with two suffix characters indicate
transition between distinct states.
[0042] Further as shown in FIG. 4A, a transition (FWGLUMID).sub.S
402 represents the FWGLUMID operators that operate on a database
schema from the state S and produces a database schema in the state
S. Furthermore, a transition (FGLD).sub.SU 404 represents the FGLD
operators that operate on a database schema from the state S and
produces a database schema in the state (S.sup.U-S).
[0043] In addition as shown in FIG. 4A, a transition
(FWGLUMID).sub.U 406 represents the FWGLUMID operators that operate
on a database schema from the state (S.sup.U-S) and produces a
database schema in the state (S.sup.U-S). Also, a transition
(FGLD).sub.US 408 represents the FGLD operators that operate on a
database schema from the state (S.sup.U-S) and produces a database
schema in the state S.
[0044] Moreover, the state diagram 400A is used to trace
progression of the SQL query. For example, the SQL query Q4
"SELECT*FROM R" starts executing at the state S. As shown in FIG.
4A, F and L operators operating on a database schema from the state
S, produces a database schema in the state S. Thus, the state
transitions for the SQL query Q4 are S, S and S. As a result, the
SQL query Q4 starts executing from the state S and ends at the
state S. In addition, the database schema transitions are used to
obtain the possible data transitions for the SQL query. An
exemplary procedure for obtaining possible data transitions for an
SQL query from the database schema transitions is included in
Appendix "C".
[0045] Now, referring to FIG. 5, a generalized state diagram 500
illustrates an SQL query evaluated over a set of database schemas,
according to one embodiment. As shown, the state diagram 500 is
used to analyze, debug and trace SQL queries beyond closure.
Further, the state diagram 500 illustrates state transitions over a
set of database schemas S.sub.0, S.sub.1, S.sub.2 and
(S.sup.U-US.sub.i), where, i=0 . . . 2. The database schema
(S.sup.U-US.sub.i) includes the universal database schema without
any database schema contained in any of the database schemas
S.sub.0, S.sub.1 and S.sub.2. Furthermore, the state diagram 500
includes 4 possible states, a state S.sub.0, a state S.sub.1, a
state S.sub.2 and a state (S.sup.U-US.sub.i). In addition, the
state diagram 500 partitions the FWGLUMID operators based on their
ability to map results among the database schemas S.sub.0, S.sub.1,
S.sub.2 and (S.sup.U-US.sub.i).
[0046] Similar to state diagrams 300A and 400A in FIGS. 3A and 4A,
the state diagram 500 is also based on UML syntax. As shown in FIG.
5, 502 refers to a start state. In addition as shown in FIG. 5,
.epsilon. 504 represents a state transition from the start state
502 to the state S.sub.0. However, while describing the state
transitions in the state diagram 500, the state S.sub.0 is
considered as the start state. Also in the state diagram 500, the
operators causing the state transitions are labeled with one or two
suffix characters to denote a source state and a destination state.
The operators labeled with one suffix character indicate a
transition from a state to itself. The operators labeled with two
suffix characters indicate transition between distinct states
[0047] As shown in FIG. 5, a transition (FWGLUMID).sub.0 506
represents the FWGLUMID operators that operate on a database schema
from the state S.sub.0 and produces a database schema in the state
S.sub.0. Further, a transition (FGLD).sub.01 508 represents the
FGLD operators that operate on a database schema from the state
S.sub.0 and produces a database schema in the state S.sub.1.
Furthermore, a transition (FGLD).sub.02 510 represents the FGLD
operators that operate on a database schema from the state S.sub.0
and produces a database schema in the state S.sub.2. In addition, a
transition (FGLD).sub.0U 512 represents the FGLD operators that
operate on a database schema from the state S.sub.0 and produce a
database schema in the state (S.sup.U-US.sub.i).
[0048] Further as shown in FIG. 5, a transition (FWGLUMID).sub.1
514 represents the FWGLUMID operators that operate on a database
schema from the state S.sub.1 and produces a database schema in the
state S.sub.1. Furthermore, a transition (FGLD).sub.10 516
represents the FGLD operators that operate on a database schema
from the state S.sub.1 and produces a database schema in the state
S.sub.0. In addition, a transition (FGLD).sub.12 518 represents the
FGLD operators that operate on a database schema from the state
S.sub.1 and produces a database schema in the state S.sub.2. Also,
a transition (FGLD).sub.1U 520 represents the FGLD operators that
operate on a database schema from the state S.sub.1 and produce a
database schema in the state (S.sup.U-US.sub.i).
[0049] Furthermore as shown in FIG. 5, a transition
(FWGLUMID).sub.2 522 represents the FWGLUMID operators that operate
on a database schema from the state S.sub.2 and produces a database
schema in the state S.sub.2. In addition, a transition
(FGLD).sub.20 524 represents the FGLD operators that operate on a
database schema from the state S.sub.2 and produces a database
schema in the state S.sub.0. Also, a transition (FGLD).sub.21 526
represents the FGLD operators that operate on a database schema
from the state S.sub.2 and produces a database schema in the state
S.sub.1. Moreover, a transition (FGLD).sub.2U 528 represents the
FGLD operators that operate on a database schema from the state
S.sub.2 and produce a database schema in the state
(S.sup.U-US.sub.i).
[0050] In addition as shown in FIG. 5, a transition
(FWGLUMID).sub.U 530 represents the FWGLUMID operators that operate
on a database schema from the state (S.sup.U-US.sub.i) and produces
a database schema in the state (S.sup.U-US.sub.i). Further, a
transition (FGLD).sub.U0 532 represents the FGLD operators that
operate on a database schema from the state (S.sup.U-US.sub.i) and
produces a database schema in the state S.sub.0. Furthermore, a
transition (FGLD).sub.U1 534 represents the FGLD operators that
operate on a database schema from the state (S.sup.U-US.sub.i) and
produces a database schema in the state S.sub.1. Also, a transition
(FGLD).sub.U2 536 represents the FGLD operators that operate on a
database schema from the state (S.sup.U-US.sub.i) and produces a
database schema in the state S.sub.2.
[0051] Referring now to FIG. 6, a table 600 illustrates operator
patterns over the database D and the database schema S, according
to one embodiment. In table 600, the database D.sup.+ excludes the
database D, the database D.sup.U excludes the database D.sup.+ and
the database schema S.sup.U excludes the database schema S. As
shown in table 600, column 602 includes the operators used in an
SQL query and column 604 includes different operator patterns for
each operator in column 602. Further, column 606 shows whether the
database D and the database schema S is closed under each operator
pattern in column 604. Also, column 608 shows remarks for each
operator pattern in column 604. For example, the first row in table
600 illustrates that the database D is closed under the FROM
operator when the input contains only one relation.
[0052] Referring now to FIG. 7, a table 700 illustrates extended
operator patterns over the database D.sup.U, the database D.sup.+
and the database schema S.sup.U, according to one embodiment. As
shown In table 700, the database D.sup.+ excludes the database D,
the database D.sup.U excludes the database D.sup.+ and the database
schema S.sup.U excludes the database schema S. Further as shown in
table 700, column 702 includes the operators used in an SQL query
and column 704 includes different operator patterns for each
operator in column 702. Furthermore as shown in table 700, column
706 shows remarks for each operator pattern in column 704. For
example, the first row in table 700 illustrates that the FROM
operator operated on data from database D.sup.+ produces data in
database D when the input has more than one relation.
[0053] Now, referring to FIG. 8, a block diagram of a networked
computer system 800 incorporating a database management system
(DBMS) 812 is illustrated, according to one embodiment. As shown in
FIG. 8, the networked computer system 800 includes one or more
clients 802A-N, a network 804, a computer 806, a display 818 and a
mass storage 820. Further as shown in FIG. 8, the mass storage 820
includes a relational database 822. In this embodiment, the
computer 806 is coupled to the clients 802A-N and the relational
database 822 via the network 804. Furthermore as shown in FIG. 8,
the computer 806 includes a processor 808 operatively coupled to
memory 810. In addition as shown in FIG. 8, the memory 810 includes
the DBMS 812 and an operating system 816. The DBMS 812 further
includes a closure-friendly SQL query processor 814 residing in the
memory 810.
[0054] In operation, the closure-friendly SQL query processor 814
receives an SQL query made against the relational database 822 from
one or more clients 802A-N via the network 804. Further, the
closure-friendly SQL query processor 814 parses the received SQL
query to obtain each operator and associated one or more operands
and sequence of execution of the operators. Furthermore, the
closure-friendly SQL query processor 814 dynamically generates the
closure-friendly operator for each operator and associated operands
in the received SQL query. This is explained in detail with
reference to FIG. 2.
[0055] In this embodiment, the closure-friendly SQL query processor
814 dynamically generates the closure-friendly operator for each
operator and the associated one or more operands possibly using
optimization techniques. Exemplary optimization techniques include
just-in-time compilation, code caching, code libraries, lazy
evaluation and the like. This is explained in more detail with
reference to FIG. 1. In addition, the closure-friendly SQL query
processor 814 executes the dynamically generated closure-friendly
operators based on the obtained sequence of execution of the
operators.
[0056] Further in this embodiment, each dynamically generated
closure-friendly operator is grouped into possibly overlapping
patterns by the closure-friendly SQL query processor 814.
Furthermore, the impact of each pattern on the queried relational
database and the database schema is classified by the
closure-friendly SQL query processor 814. This is explained in
detail with reference to FIGS. 6 and 7. Also, the received SQL
query is analyzed by the closure-friendly SQL query processor 814
using partitions of the closure-friendly operators. Moreover, the
received SQL query is traced/debugged, by the closure-friendly SQL
query processor 814, based on the analysis.
[0057] In various embodiments, the methods and systems described in
FIGS. 1 through 8 proposes a closure-friendly SQL query processor
which is closer to formalism and easily verifiable. The
closure-friendly operators generated by the closure-friendly SQL
query processor allow the relational databases to be closed under a
SQL query. Also, the closure-friendly operators aid analysis,
debugging and tracing of the SQL query. In addition, the
closure-friendly SQL query processor restores the ability to
perform mathematical analysis in the implementation.
[0058] Although the present embodiments have been described with
reference to specific example embodiments, it will be evident that
various modifications and changes may be made to these embodiments
without departing from the broader scope of the various
embodiments. Furthermore, the various devices, modules, analyzers,
generators, and the like described herein may be enabled and
operated using hardware circuitry, for example, complementary metal
oxide semiconductor based logic circuitry, firmware, software
and/or any combination of hardware, firmware, and/or software
embodied in a machine readable medium. For example, the various
electrical structures and methods may be embodied using
transistors, logic gates, and electrical circuits, such as an
application specific integrated circuit.
TABLE-US-00001 APPENDIX A //Functions specific to Query Q1 Relation
Q1_F (Relation[ ] relations) //this function can be optimized away
return relations[0] Relation Q1_W (Relation R) Relation result
.rarw. Empty instance of Schema (R) for each Row r in R if r[A]
> 5 Add r to result return result Relation Q1_L (Relation R)
Relation result .rarw. Empty //create result schema Add schema of
R["A"] to Schema(result) //add rows to result relation for each Row
r in R Row r' .rarw. Empty r'["A"] .rarw. r["A"] Add r' to result
return result
TABLE-US-00002 APPENDIX B //Functions specific to Q2 Relation Q2_F
(Relation[ ] relations) //as written, this function is not
query-specific //but it can be made more specific by exploiting
//the schema and bindings of the input relations return
relations[0] * relations[1] Relation Q2_W (Relation R) Relation
result .rarw. Empty instance of Schema(R) for each Row r in R if
r[A] > r[B] Add r to result return result Relation Q2_L
(Relation R) Relation result .rarw. Empty //create result schema
Add schema of R["A"] to Schema(result) Add schema of R["B"] to
Schema(result) //add rows to result relation for each Row r in R
Row r' .rarw. Empty r'["A"] .rarw. r["A"] r'["B"] .rarw. r["B"] Add
r' to result return result
TABLE-US-00003 APPENDIX C SchemaStateAndDataStates (Database D,
Operator .theta.) S .rarw. Schema(D) s' .rarw. schema of the result
of .theta. over D if s' S, schema state is S, possible data states
are D and D+ else schema state is S.sup.U, data state is
D.sup.U.
* * * * *