 |
Claims  |
|
|
We claim:
1. A method for operating a computing apparatus to translate into a linear
query a graphic language query expressed as one or more elements,
including example elements and implied operand predicates, appearing in
rows and columms of an example table including one or more source and
target tables and, optionally, in condition blocks, comprising the steps
of:
generating row names into a row name table having one entry for each row in
a source table;
for each data field within each row named in said row name table which
contains an example element definition or implied operand predicate,
generating and loading into a column data table an entry specifying the
row name, column name, and data; and
generating into a conditions table an entry containing a basic predicate
for each column data table entry containing an implied operand predicate
or an example element having an implied condition;
thereby establishing in said row name table, column data table, and
conditions table a data structure for synthesizing into a linear query a
graphic language query specified as a target point query, or a combined
print query, or an insert query, or an update query, or a delete query.
2. The method of claim 1, comprising the further steps of determining from
said column data table the row name and column name for example elements
referenced in the condition block, and responsive thereto generating into
said conditions table a condition entry for each condition specified in a
condition block.
3. The method of claim 1, comprising the further step of substituting
parameters passed in said graphic language query prior to building said
data structure.
4. The method of claim 1, comprising the further step, responsive to said
graphic language query being a combined print query expressed as a print
command appearing in a source table, of synthesizing a linear select
query.
5. The method of claim 4 wherein said linear select query is synthesized
according to the steps of:
identifying as simple P. rows those rows in a source table containing a
print command which is not linked to any other row, and as dependent P.
rows those rows in a source table which are linked to one or more other
rows;
generating for all simple P. rows a single select statement including in a
FROM clause the name of said source table, and in a WHERE clause the
ANDing of conditions that reference a simple P. row and, if there are more
than one simple P. rows, for each simple P. row in excess of one the ORing
of conditions that reference one simple P. row with those conditions that
reference another simple P. row;
generating for each dependent P. row a select statement including (1) in a
FROM clause the table name of the table containing the P. row and the
table name of the table containing a linked P. row for each such linked P.
row, each table name being associated with a correlation variable, and (2)
in a WHERE clause the ANDing of all conditions that reference the linked
rows in the FROM clause; and
generating as said linear select query the UNION of all generated select
statements.
6. The method of claim 1, comprising the further step, responsive to said
graphic language query being a target print query expressed as a print
command appearing in a target table, of synthesizing a linear select
query.
7. The method of claim 6 wherein said linear select query is synthesized
according to the steps of:
generating for each target row of a target table containing a print command
P. a select statement including (1) in a SELECT clause a list of constants
and expressions that appear in or are referenced by the data fields of the
target row for which the select statement is being generated; (2) in a
FROM clause the table name and associated correlation variable for each
table containing the target or a linked row; and (3) in a WHERE clause the
logical ANDing of any conditions that reference rows that are referenced
in the target row;
responsive to the target print query including a sort specification,
generating an ORDER BY clause; and
generating as said linear select query any generated ORDER BY clause and
the UNION of all generated select statements.
8. The method of claim 1, comprising the further step, responsive to said
graphic language query being an insert query expressed as an insert
command appearing in an example table, of synthesizing a linear insert
query.
9. The method of claim 8, wherein said linear insert query is synthesized
according to the steps of:
identifying as a simple I. row a row containing only an insert command and
constant values, and as a complex I. row a row containing an insert
command and one or more example elements linking the complex I. row to one
or more linked rows;
responsive to said insert command appearing in a simple I. row, generating
only an insert statement;
responsive to said insert command appearing in a complex I. row, generating
an insert statement and a single select statement, the select statement
including a SELECT clause, a FROM clause, and a WHERE clause; the SELECT
clause containing constants and expressions appearing in the data fields
of the complex I. row and references to columns in other rows linked to
the complex I. row; the FROM clause including the table names of all
tables containing the complex I. row and any linked row, each such table
name being associated with a correlation variable; and the WHERE clause
including the logical ANDing of all conditions that reference rows that
are referenced in the complex I. row;
generating said linear insert query to include said insert statement and
any generated select statement.
10. The method of claim 1, comprising the further step, responsive to said
graphic language query being a delete query expressed as a delete command
appearing in a source table, of synthesizing a linear delete query.
11. The method of claim 10, wherein said linear delete query is synthesized
according to the steps of:
identifying as a D. row each row in a source table containing a delete
command;
identifying as a linked row (1) each row referenced by the same condition,
and (2) each linked row linked to a same row;
identifying as a dependent row each linked row that is linked to a D. row;
generating a condition statement for each D. row, including (1) generating
in an outer query a delete statement including (a) in a DELETE FROM clause
the table name of the source table containing the D. row; (b) in a WHERE
clause the logical ANDing of conditions which reference only said each D.
row and, using an EXISTS comparison operator, any generated sub-queries;
and (2) generating a sub-query for each set of dependent rows linked to
said each D. row, a subquery including (a) in a SELECT* FROM clause the
logical ANDing of all table names of tables within the set of dependent
rows, each table name being associated with correlation variable; and (b)
in a WHERE clause the logical ANDing of conditions that reference the set
of dependent rows; and
generating as said linear delete query the logical ORing of the condition
statements generated for each D. row.
12. The method of claim 1, comprising the further step, responsive to said
graphic language query being an update query expressed as an update
command, of synthesizing a linear update query.
13. The method of claim 12, wherein said linear update query is synthesized
according to the steps of:
identifying as a simple update query an update query U. appearing in a U.
row that is not linked to another row, and as a dependent update query an
update query U. appearing in a row that is linked to one or more other
rows;
for a simple update query, generating (1) in a SET clause constant values
and expressions in the U. row referencing other columns in the U. row; and
(2) in a WHERE clause the logical ANDing of any conditions that reference
only the U. row;
for a dependent update query, generating in an outer query (1) in a SET
clause constant values and expressions in the U. row referencing other
columns in the U. row; (2) in a WHERE clause the logical ANDing of any
conditions that reference only the U. row and, using an EXISTS comparison
operator, any generated subqueries; and generating in a sub-query (1) in a
SELECT* FROM clause the table names of tables containing a linked row for
each such linked row, each table name being associated with a correlation
variable; and (2) in a WHERE clause the logical ANDing of all conditions
that reference the linked rows; and
generating said linear update query to include a SET clause and any
generated WHERE clause and sub-query.
14. A computing apparatus for translating into a linear query a graphic
language query expressed as one or more elements, including example
elements and implied operand predicates, appearing in rows and columns of
an example table including one or more source and target tables and,
optionally, in condition blocks, comprising:
means for generating row names into a row name table having one entry for
each row in a source table;
means, operable for each data field within each row named in said row name
table which contains an example element definition or implied operand
predicate, for generating and loading into a column data table an entry
specifying the row name, column name, and data;
means for generating into a conditions table an entry containing a basic
predicate for each column data table entry containing an implied operand
predicate or an example element having an implied condition;
thereby establishing in said row name table, column data table, and
conditions table a data structure for synthesizing into a linear query a
graphic language query specified as a target print query, or a combined
print query, or an insert query, or an update query, or a delete query. |
|
 |
|
 |
Claims |
|
|
|
Description |
|
|
BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates to a new computing apparatus and method for managing
a database. More specifically, it relates to an apparatus and method for
synthesizing from a graphic language query an equivalent linear language
query for accessing a relational data base.
2. Description of the Prior Art
An example of a linear query language is provided by the IBM Structured
Query Language (SQL), as is described in SQL/Data System Terminal User's
Guide, IBM Publication SH24-5016-0 and SQL/Data System Application
Programming, IBM Publication SH24-5018-0. Such a linear query language
provides for defining, accessing, and modifying multiple views of stored
tables in a relational data base, such as is described in M. W. Blasgen,
et al, "System R: An architectural overview", IBM System Journal, Vol. 20,
No. 1, 1981, pp. 41ff.
An example of a graphic query language is provided by the IBM
Query-by-Example (QBE), as is described in Query-by-Example Terminal
User's Guide, IBM Publication SH20-2078-0. Such a graphic query language
also provides for defining, accessing, and modifying stored tables in a
data base, and provides a particularly "user friendly" format for the
terminal operator, such as is described in M. M. Zloof, "Query-by-Example:
a data base language", IBM System Journal, Vol 16, No. 4, 1977, pp. 324ff.
The SQL and QBE query languages have each been designed to access data
bases, but heretofore a data base managed by one could not be accessed by
the other. Consequently, there exists a need to enable a terminal
operator, or user, to access a data base according to a graphic query
language even where the data base manager requires that queries be
expressed according to a linear query language.
SUMMARY OF THE INVENTION
Therefore, in accordance with the invention, an apparatus and method is
provided for converting a query expressed in a graphic query language into
an equivalent linear query.
Thus, a method is provided for operating a computing apparatus to translate
into a linear query a graphic language query expressed as one or more
elements, including example elements and implied operand predicates,
appearing in rows and columns of an example table including one or more
source and target tables and, optionally, in condition blocks. The method
comprises the steps of: generating row names into a row name table having
one entry for each row in a source table; for each data field within each
row named in said row name table which contains an example element
definition or implied operand predicate, generating and loading into a
column data table an entry specifying the row name, column name, and data;
marking as bound those column data table entries containing an example
element not having an implied condition; and generating into a conditions
table an entry containing a basic predicate for each column data table
entry containing an implied operand predicate or an example element having
an implied condition; thereby establishing in the row name table, column
data table, and conditions table a data structure for synthesizing into a
linear query a graphic language query specified as a target print query,
or a combined print query, or an insert query, or an update query, or a
delete query.
In accordance with another aspect of the invention, a method is provided
for determining from the column data table the row name and column name
for example elements referenced in the condition block, and responsive
thereto generating into the conditions table a condition entry for each
condition specified in a condition block.
In accordance with a further aspect of the invention, a method is provided
for synthesizing a graphic combined print query into a linear query
comprising the UNION of generated select statements.
In accordance with a further apsect of the invention, a method is provided
for synthesizing a graphic target print query into a linear query
comprising any generated ORDER BY clause and the UNION of generated select
statements.
In accordance with a further aspect of the invention, a method is provided
for synthesizing a graphic insert query into a linear insert statement and
any generated select statements.
In accordance with a further aspect of the invention, a method is provided
for synthesizing a graphic delete query into a linear delete query
comprising the logical ORing of condition statements.
In accordance with yet a further aspect of the invention, a method is
provided for synthesizing a graphic update query into a linear update
query including a SET clause and any generated WHERE clause and sub-query.
In accordance with yet a further aspect of the invention, a computing
apparatus is provided for translating into a linear query a graphic
language query expressed as one or more elements, including example
elements and implied operand predicates, appearing in rows and columns of
an example table including one or more source and target tables and,
optionally, in conditions blocks. The apparatus includes means for
generating row names into a row name table having one entry for each row
in a source table; means, responsive to each data field within each row
named in said row name table which contains an example element definition
or implied operand predicate, for generating and loading into a column
data table an entry specifying the row name, column name, and data; means
for marking as bound those column data table entries containing an example
element not having an implied condition; and means for generating into a
conditions table an entry containing a basic predicate for each column
data table entry containing an implied operand predicate or an example
element having an implied condition; thereby establishing in said row name
table, column data table, and conditions table a data structure for
synthesizing into a linear query a graphic language query specified as a
target print query, or a combined print query, or an insert query, or an
update query, or a delete query.
BRIEF DESCRIPTION OF THE DRAWINGS
FIG. 1 illustrates an example table of a graphic query which is a named, or
source, table.
FIG. 2 illustrates an example table of a graphic query which is an unnamed,
or target, table.
FIG. 3 illustrates a graphic query conditions box.
FIG. 4 is a diagram illustrating a relational data base, a typical graphic
query for accessing that data base, and the output responding to the
query.
FIG. 5 is a diagram illustrating the control blocks structure implemented
according to a preferred embodiment of the invention for synthesizing a
linear query from a graphic query.
FIG. 6 is a diagram illustrating the processing modules implemented
according to a preferred embodiment of the invention for synthesizing a
linear query from a graphic query.
FIGS. 7A and 7B illustrate the steps performed by the graphic form manager
module DSQGFTM 110, table input process module DSQGFTT 114, and source
process module DSQGFTS 116 of FIG. 3.
FIG. 8 illustrates the steps performed by the retrieval process module
DSQGFTP 120 of FIG. 3.
FIG. 9 illustrates the steps performed by the output, or target, table
process module DSQGFTX 126 of FIG. 3.
FIG. 10 illustrates the steps performed by the delete process module
DSQGFTD 118 of FIG. 3.
FIG. 11 illustrates the steps performed by the update process module
DSQGFTU 124 of FIG. 3.
FIG. 12 illustrates the steps performed by the insert process module
DSQGFTI 122 of FIG. 3.
DESCRIPTION OF THE PREFERRED EMBODIMENTS
(In Table 49 is set forth a listing of abbreviations used in the
description which follows.)
Referring to FIGS. 1 through 4, an introductory explanation will be given
of an example of a graphic query language--in this instance, the Query By
Example (QBE) language. QBE allows a user to express a query by creating
examples of the items being queried. The example tables 2 and 4 (also
referred to as skeletons), illustrated in FIGS. 1 and 2, and the condition
box 6 in FIG. 3, are used to create the example query Q. The items being
queried are in a 2-dimensional table form, as is illustrated in FIG. 4,
tables 40 and 50. Conditions 8 may appear in a QBE query that limit the
selection data 9 from tables 2, 4.
The terms, "example table", "example row", and "example column", are used
in referencing an example table. A table is a rectangular array of
elements. Example table refers collectively to all the items that appear
in an example table 2 or 4. A "skeleton" is another term for an example
table. Example row refers collectively to row 7 and data fields 9 that
appear in one row of an example table 2, 4; and example column refers
collectively to the column name 5 and data fields 9 that appear in one
column of an example table 2. (A source table 2 may have a column name
field 5, but a target table 4 may not.) Actual tables are made up of
elements, each of which appears in a particular row and column of a table.
A QBE data field 9 may contain an example element (EE) 18 (FIG. 4), that
is an example of the actual elements that appear in a column 42 of a table
40. In the embodiments herein described, an example element name begins
with an underscore (.sub.--). The elements in a column usually have the
same significance. For example, a column may contain the names of people
working for a particular business.
A results table 56 is a table created by a QBE query. Each row of a results
table may be viewed as containing elements from a single row of an
extended Cartesian product (ECP). An ECP is a matrix product formed from
one or more source tables 2. The concept of an ECP is used in this
description to aid in understanding, but it should be understood that an
actual ECP is not necessarily constructed in the preferred embodiments
hereinafter described. Since an ECP is a matrix product of all source
table 2 rows, the row dimension of the ECP is the product of the row
dimensions of the source tables, and the column dimension is the sum of
the column dimensions of the source tables.
A row is a duplicate row if each element of the row is a duplicate of each
element with the same ordinality in some other row of the table. Elements
of a table have the same ordinality as the column and row containing the
element. For example, the 3rd element in a row is in the 3rd column, and
the 12th element in a column is in the 12th row.
Not all rows of an ECP need be used in formulating a results table 56. That
is, a QBE query may contain selection criteria that selects a subset of
ECP rows to be used in the formulation of a results table 56. In addition,
not all columns need be selected in formulating a results table 56. Thus,
a QBE query need not name all columns from selected ECP rows. An element
in a results table 56 may be a copy of an element from a column of a
selected ECP row. It may be the evaluation of an arithmetic expression
containing copies of 1 or more elements from the selected ECP row. It may
even be a constant value not copied from a selected row. Generally, no
duplicate rows appear in a QBE results table. That is, only one copy of
duplicate rows produced by the QBE query are retained in the QBE results
table.
Referring to FIG. 4, an example of a QBE query is shown. Appearing on the
screen of the terminal user would be example tables 16, 26, and 36 and
result table 56. The relational data bases being accessed by the query,
and stored in the data storage area of a host computer, are illustrated as
tables 40 and 50. Table 16 includes a table name 10 of SALES, column names
DEPARTMENT 12, and ITEM 14, and example elements .sub.-- TOY 18 and
.sub.-- INK 19. Example table 26 includes a table name SUPPLY 20, column
names 22, 24 (ITEM and SUPPLIER), and data fields 27 and 29 contain
example elements .sub.-- INK and .sub.-- IBM. Tables 16 and 26 are source,
or named, tables. Table 36, on the other hand, having its name field 30
blank, is a target table, and illustrates the information to be displayed
in output table 56 in response to the query of tables 16, 26, and 36.
FIG. 4 illustrates a query where a target table 36 is required, for data
from two tables (SALES table 40 and SUPPLY table 50) are to be combined in
the output, or results, table 56. (Example element 19 illustrates an
example element with an implied condition, the condition implied being
that #$@0001.ITEM.dbd.#$@0002.ITEM, where #$@0001 is the row name assigned
to the row including data field 19, and #$@0002 is the row name of the row
including data field 27, as will be more fully described hereafter.)
The query of FIG. 4 is interpreted as follows. In data field 38 is
"P.AO(1)..sub.-- TOY". "P." is a QBE print command, "AO(1)." is a QBE sort
or ordering parameter, and ".sub.-- TOY" is an example element. .sub.--
TOY is in the DEPARTMENT column 12 in SALES table 10, thus directing the
query, as is illustrated by line 60, to access SALES table 40 and put into
the corresponding column 57 of output table 56 data from column 12 that
satisfies the query. Similarly, the .sub.-- IBM in data field 39
references example element .sub.-- IBM 29 in the SUPPLIER column 24 of
SUPPLY table 20, thus directing that data from SUPPLY table 50, SUPPLIER
column 54 which satisfies the query be printed out to the result table 56
in column 58. The example elements 19 and 27 provide the link between
tables 40 and 50.
Thus, as is illustrated by line 62, output table 56 will include in column
57 the department name from sales table 40, and, as is illustrated by line
66, in column 58 the supplier name from supply table 50 where, as is
illustrated by line 64, the item from column 44 is the same as the item
from column 52, with data ordered first (as is specified by "AO(1)." in
element 38) on department name, and second (as is specified by "AO(2)." in
field 39) on supplier name.
Referring now to Tables 1 through 14, the terms essential to an
understanding of the metalanguage statement of the procedure of the
invention will be given a formal syntactic definition together with
semantic rules. Specification progresses from the more elemental to the
most complex concepts in order to obviate the need for forward references.
The syntax notation used herein is defined in IBM Standards Manual 33-07
(Standard I-B 3-9001 008), with the addition of the additional syntax
notation of the "may be repeated" box, and the "with" box. A construct
bracketed by a "may be repeated" box may be repeated. When two constructs
are separated by a "with" box, both constructs may be present.
Table 1 gives the syntax definition of a character. A character is a digit
or a letter or a special character. A special character is any graphic
character which is not a digit or a letter.
Table 2 provides the syntax definition of a constant, which is a string
constant, or a numeric constant. A string constant is a string of one or
more characters, or an identifier. An identifier is a letter followed by
one or more letters or digits. A delimited identifier is one or more
characters enclosed within quotes (").
Table 3 provides the syntax definitions names. A table name is an
identifier or a delimited identifier, optionally preceded by a name
qualifier followed by a period (.). A column name is an identifier or a
delimited identifier. A name qualifier is an identifier or a delimited
identifier. An example element is an underscore (.sub.--), followed by one
or more letters or digits. A table name is the name of a predefined table.
Column names are names of predefined columns. An example element is a
variable name for the elements that appear in a particular column of a
table. A name qualifier is used to give uniqueness to tables with the same
table name.
Table 4 provides the syntax definition of tokens. A token is any one of the
following: identifier; example element; numeric constant; order spec; or
keyword occurrence. A keyword occurrence is a keyword which is not
contained in a character string constant. A keyword is any one of the
following sequences of non blank characters: NOT IN LIKE AND OR P. I. U.
D. A keyword may also be a character or character string signifying
"equal" or "not equal" or "greater than" or "less than" or "greater than
or equal to" or "less than or equal to". A spacer is a sequence of one or
more blank characters. A token may be preceded and/or followed by a
spacer.
Table 5 provides the syntax definition of expressions. An expression is an
arith-term, or an expression followed by an infix-op-1 followed by an
arith-term. And infix-op-1 is an addition (+) or a subtraction (-) sign.
An arith-term is an arith-factor, or an arith-term followed by an
infix-op-2 followed by an arith-factor. An infix-op-2 is a division (/) or
a multiplication (*) sign. An arith-factor is a primary, which may be
preceded by a prefix-op. A prefix-op is an unary plus (+) or an unary
minus (-) sign. A primary is a constant, or an example element reference,
or an expression enclosed within parentheses. An example element reference
(EER) is an underscore (.sub.--) followed by a letter or a digit, which
may be followed by one or more letters or digits. An example element
reference (EER) is a reference to an element value represented by the
example element name used in the reference.
Expressions in a QBE query apply to each row of an ECP formed from source
tables referenced by the query. QBE processes each row of an ECP formed
from source tables referenced by the query. As each row is processed,
expressions are evaluated by substituting for each example element
reference, a corresponding element value.
Table 6 sets forth the syntax of predicates. A predicate is a basic
predicate, or an in-predicate, or a like-predicate, or a null-predicate. A
basic predicate is an expression followed by a comp-op followed by an
expression. An in-predicate is an expression, followed by IN or NOT IN,
followed by one or more constants separated by commas. A like-predicate is
an example element, followed by LIKE or NOT LIKE, followed by a character
string constant. A null-predicate is an example element followed by a
null-comp-op followed by a null-symbol.
A basic predicate compares two values, each derived from the evaluation of
the expressions that appear in the basic predicate.
The like-predicate is used to ignore certain character positions in
comparing an element to a string constant. The characters .sub.13 and [,
for example, may be used in a string constant to indicate, respectively,
one or more character positions that are to be ignored.
The in-predicate is evaluated as true if the element referenced by the
example element is equal to one or more of the constant values expressed
to the right of the IN comp op (comparison operator).
Table 7 provides the syntactic definition of an implied operand predicate.
An implied operand predicate appearing in a data field 9 refers to the
column in which it appears in an example table. As each row of ECP is
processed, a basic predicate is formed from the element in the referenced
column followed by the implied predicate.
Table 8 gives the syntax of condition lists. A condition list is a
condition, which may be repeated. A condition must appear in a condition
box 6. One or more conditions boxes 6 may be used to contain a conditions
list. Conditions in a QBE query apply to each row of an ECP formed from
source tables referenced by the query. As each row is processed conditions
are evaluated by substituting for each example element, the corresponding
element value.
Table 9 gives the syntax of a source table list. A source-table-list is a
source table, which may be repeated. A source table is one or more tables,
each table including a table-name in a table name field 3, and a source
entry in at least one data field 9, and, optionally, a column-name in a
column name field 5 for the column containing a data field 9. A source
entry is an example-element-definition, or an implied-operand-predicate,
or empty.
The term "source row" is used to refer collectively to all of the source
entries that appear in the same source row. An example element reference
(EER) refers to an example element definition (EED) if both contain the
same example-element (EE) name. More generally, an item X refers to an
item Y if the item X contains an example element reference which refers to
an example element definition contained in the item Y.
Two rows R1 and R2 in a query Q are directly linked if one or more of the
following is true: R1 refers to R2; R2 refers to R1; there is a condition
in the query Q which refers to both R1 and R2. Two rows R1 and R2 in a
query Q are linked if one or more of the following is true: R1 and R2 are
directly linked; R1 is directly linked to some other row RX in the query Q
and RX is linked to R2.
In QBE, one and only one source entry may appear in a source row. Also, in
a source table a source entry in a column without a column name must be
empty. An example element which can be parsed as either an example element
definition or an implied operand predicate shall be parsed as an example
element definition. (This latter rule specifies the manner in which the
syntactic ambiguity between example element definition and implied operand
predicate must be resolved. The ambiguous case involves an implied operand
basic predicate with an omitted comp op and an expression which is an
example element reference. Such a case is treated as an example element
definition.) In this embodiment, the implementation of empty is an entry
consisting entirely of blank characters.
Table 10 provides the syntax of a combined print query. A
combined-print-query is a combined-print-table, which may be preceded by a
condition list, which may be preceded by a source-table-list. A
combined-print-table is an example table with a table-name, with at least
one column having a column-name and an optional source-entry, and which
example table may have one or more rows having a combined-row-attributes
in row field 7 and one or more combined-entries in data fields 9. A
print-command is designated by "P.". An order-spec is "AO." or "DO.". An
integer value in parentheses may optionally appear before the closing".
A set-spec is an all-spec or an unique "unq-spec". An all-spec is
represented by "ALL." The unq-spec is represented by "UNQ.", and requires
that single copies of duplicate rows be presented in the results table.
The term "combined row" is used to refer collectively to the combined row
attributes and the combined entries of a row in a combined print table. A
combined print table can be made up of source rows and combined rows. The
term combined print row is used to refer to a row of a combined print
table, whether it is a source row or a combined row. In a combined print
table, the number of source entries in each source row must be equal to
the number of column names. (A source entry may, however, be blank.) A
combined print row contains at least one print command. (This latter rule
resolves the syntactic ambiguity between source row and combined print
row.) If the combined row attributes of a combined print row do not
contain a print command, then each combined entry in that combined print
row that contains an order spec must also contain a print command. If a
print command is contained in the i-th combined entry of some combined
print row in a combined print table, then every combined print row of that
combined print table must either have a print command in its combined row
attributes or have a print command in its i-th combined entry. If a print
command is contained in the combined row attributes of some combined print
row in a combined print table, then every combined print row must either
have a print command in its combined row attributes or have a print
command in every combined entry. If a combined print table contains more
than one order spec, then each order spec must contain an integer. If an
integer is contained in an order spec, it may not be duplicated in another
order spec in the same combined-print-table. A combined column may not
contain more than one order-spec.
A QBE combined-print-query produces a results table that is then displayed
on the screen from which the query was submitted. The result contains
elements copied from the source table named in the combined print table. A
P. (print command) in a column data field 9 indicates that elements from
the like named column of the source table are to be copied into the
results. A P. in a row field 7 causes elements from all columns 5 named in
the query to appear in the results. Examples of elements to be displayed
may be used in a query. Thus, a "P..sub.-- SMITH" appearing in a data
filed 9 of a column having a column name 5 of NAME and in a table 2 having
table name 3 of EMP will display all the NAMEs, including but not limited
to SMITH, for example, from the EMP table. Examples of data to be
displayed are optional. That is, if .sub.-- SMITH were omitted from the
preceding example, the results would be the same.
AO. means sort in ascending order, and DO. means sort in descending order.
If an order-spec appears in a combined-column, results table rows are
sorted by the elements in that column. If more than one order-spec appears
in a combined-print-query, data is sorted first by the lowest integer
value in an order-spec, then by the each succeeding higher integer value
in the remaining order-specs.
Only defined tables may be named in a combined print query. That is, tables
that are being queried must already exist and be in the library of tables
available to the user. Columns that are referenced must be defined as part
of defined tables.
A qualified retrieval is one that contains conditions and/or implied
predicates. All of the conditions and all of the basic predicates formed
from implied operand predicates are logically ANDed to form a selection
criteria. The same rules for the valuation of conditions applies to the
evaluation of selection criteria. As each row of an ECP formed from a
combined print query is processed only elements from those rows that meet
the selection criteria appear in a combined print query results table.
A combined print query may be either a simple retrieval or a query
dependent retrieval.
A combined-print-query is a simple retrieval if no source-rows appear in
the query. Selection of elements from a row of a table is dependent only
upon values of elements in the same row. An ECP is formed for each
combined print row that is a copy of the table named in the combined print
table. A combined print query is a query dependent retrieval query if it
contains one or more source rows linked to a combined-print-row.
Conditions 8 in a combined print query may be expressed in one or more
condition boxes 6.
When two or more combined print rows appear in the same example table, each
combined print row is treated like a separate query. An ECP is formed for
each combined print row consisting of a copy of the table referenced by
the combined print row and a copy of a source table for each example row
linked to the combined print row. The results from each query are then
UNIONed to form one results table. If a combined print query contains sort
criteria, the results apply only to the final results table.
Table 11 provides the syntax of a target print query. A target-print-query
is a source-table-list with a target-print-table, with an optional
condition-list. A target-print-table is an example table 4 having in row
field 7 a target-row-attributes and in data field 9 a target-entry, the
target-row-attributes and target-entry may be repeated. A
target-row-attributes is a print-command followed by an optional set-spec.
A print command is designated by "P.". A target-entry is an expression,
preceded by an optional order-spec, which may be preceded by a
print-command.
The term "target print row" is used to refer collectively to all of the
target entries in a target row. If the target row attributes of a target
print row do not contain a print command, then each target entry contained
in that target print row must contain a print command. If a target print
table contains more than one order spec, then each order spec must contain
an integer value. If an integer value appears in one such order spec, the
same value may not appear in another order spec in the same target print
table.
The combined print query syntax of this example permits a results table
that contains only copies of elements from one source table. The target
print query syntax, on the other hand, allows creation of a results table
that contains constant values, expressions, and elements from more than
one source table. In general, conceptually, an ECP is formed consisting of
a copy of a source table for every example row linked to the target row.
A combined print query can be expressed in target print query syntax. When
multiple target print rows appear in a target print query, each print row
is treated like a separate query just as in a combined print query. An ECP
is formed for each query, and the results are UNIONed to form a final
results table. Sort criteria applies to the final results table only.
The maintenance queries are insert, delete, update, and create queries.
Table 12 shows the insert query syntax. An insert query is an insert-table,
optionally preceded by a condition-list, which may be preceded by a
source-table-list. An insert table is a source table 2 having a table-name
in table name field 3, a column-name in column field 5, an
insert-row-attributes in row field 7, and an insert-entry in data field 9.
A plurality of column-name and insert-entry fields may appear. An
insert-command is designated by "I.".
The term "insert row" is used to collectively refer to a row containing
insert row attributes and one or more insert entries. The number of insert
entries in the insert row of an insert table is equal to the number of
column names in that insert table. A given column name must not be
contained more than once in an insert table. Herein, the table name of the
insert table of an insert query must not be the table name of any source
table of that insert query. (This restriction is due to the SQL rule that
the SQL query spec in an SQL row insertion must not refer to the table
being augmented.)
An insert query causes one or more rows to be added to an existing table. A
simple insert query is one in which all insert values are constant values.
A query dependent insert query is one in which insert values contain
elements from one or more source rows. Columns defined for a table that
are not named in an insert row receive the null value for each new row
inserted in the table. If more than one source row appears in a query
dependent query, an ECP is formed that contains a copy of a source table
for each source row.
Table 13 provides the syntax of an update query. An update query is an
update-table, with an optional-condition list, with an optional
source-table-list. The update-command is designated by "U.".
The term "update row" is used to refer collectively to the source entries
or the column updated entry. In an update table, the number of update
entries in the update row must be equal to the number of column names. A
column update row must contain at least one column update item. (This rule
resolves the syntactic ambiquity between source row and column update
row.) A column-update-entry that contains an update-command may not refer
to a source-entry. (This restriction is due to the SQL rule that the SQL
query spec in a SQL row update may refer only to columns of the table
being updated.)
An update query modifies existing elements in selected rows of a table. An
update query is a simple query if the query does not contain source rows.
An update query is a query dependent update if the query contains one or
more source rows. The conditions for forming an ECP are the same as for
insert queries.
Table 14 provides the syntax of a delete query. A delete-query is a
delete-table, with an optional condition-list, with an optional
source-table-list. A delete table is an example table 2 having a
table-name in field 3, a column-name in one or more fields 5, and one or
more rows including at least one row with a delete-command in field 7 and
a delete-entry in one or more fields 9, and, optionally, a source-entry in
zero or more data fields 9. The delete-command is designated by "D.". A
delete-entry is a source-item.
The term "delete row" refers collectively to the delete command and delete
entries of a row in a delete table.
The number of delete entries in the delete row of a delete table must be
equal to the number of column names in that delete table. The table name
contained in the delete table of a delete query must not be contained in
any source table of that delete query. (This restriction is due to the SQL
rule that the SQL query spec in an SQL row deletion must not refer to the
table being augmented.)
A delete query deletes rows from a table. A delete query is a simple delete
query if no source rows appear in the query. A delete query is a query
dependent delete if one or more source rows appear in the query. If more
than one delete row appear in a query, each delete row is treated like a
separate delete query. An ECP is formed for each delete row consisting of
a copy of the table referenced by the delete row and a copy of the table
referenced by each source row linked to the delete row.
Referring now to FIG. 5, a description will be given of one possible
structure for use in implementing the translation procedure of the
invention. GFTSQL 70 is a buffer area in storage into which the SQL query
is generated, forming the output of the procedure of FIGS. 7-12. Examples
of generated SQL queries are set forth in the tables which fo | | |
