*.df
) for the purposes of
inspection and analysis; combines this data with programmatically and
manually generated conversion hints in order to create an analogous
relational database schema and a skeleton mapping of data-aware Java
objects to the resulting schema. Author(s) |
Eric Faulhaber Greg Shah Rod Gaither |
Date |
October 21, 2005 |
Access Control |
CONFIDENTIAL |
uast
package. However, the assimilation of hints with this data to produce
a functional relational schema and ORM is not yet implemented.schema
package in its current implementation:SchemaParserTokenTypes
represent the ANTLR-generated classes which are responsible for lexing,
parsing, and understanding the Progress schema dump file.ProgessEntity
subclasses are used to represent the Progress schema in a more
accessible form for further inspection and analysis.SchemaInspector
interface and its various implementations are used to inspect and
analyze the schema for the purpose of conversion.uast
package summary for a useful primer on this technology.SchemaLexer
, which generates on
output a stream of tokens specific to the dump file format.SchemaParser
. This
class subjects the stream of tokens to a set of grammar rules which are
used to generate an Abstract Syntax Tree (AST). This is a tree of
token-based nodes which abstracts away the dump file syntax and
produces
a normalized, hierarchical representation of the dump file contents.SchemaTreeWalker
. This
object walks the tree of nodes, and as it encounters tokens which
represent logical schema entities (e.g., database, table, field, etc.),
it constructs Java objects which represent those entities in their
proper context with respect to one another (e.g., database contains
tables, table contains fields, etc.).SchemaInspector
interface.NamespaceInspector
);UPDATE DATABASE "?"Each phrase provides the name of the entity it describes, as well as the properties, if any, which have been defined for it. Entities are always defined after the entity which logically contains them. For example, table fields and indexes are always defined after the table which contains them.
ADD SEQUENCE "next-cust-num"
INITIAL 0
INCREMENT 1
CYCLE-ON-LIMIT no
MIN-VAL 0
ADD TABLE "customer"
AREA "Schema Area"
DESCRIPTION "Customer Data"
VALMSG "SERVICE APP 1.0"
DUMP-NAME "customer"
ADD FIELD "acct-num" OF "customer" AS integer
FORMAT "9"
INITIAL "0"
LABEL "Account Number"
POSITION 2
SQL-WIDTH 4
ORDER 10
ADD FIELD "first-name" OF "customer" AS character
FORMAT "X(12)"
INITIAL ""
LABEL "First Name"
POSITION 10
SQL-WIDTH 130
ORDER 20
ADD FIELD "last-name" OF "customer" AS character
FORMAT "X(12)"
INITIAL ""
LABEL "Last Name"
POSITION 10
SQL-WIDTH 130
ORDER 30
ADD FIELD "since" OF "customer" AS date
FORMAT "99/99/9999"
INITIAL ?
LABEL "Customer Since"
POSITION 5
SQL-WIDTH 4
ORDER 40
ADD INDEX "idx-acct" ON "customer"
AREA "Schema Area"
UNIQUE
PRIMARY
INDEX-FIELD "cust-num" ASCENDING
SchemaHintEngine
(TBD). This engine makes hints accessible to the classes which require
them in order to produce their respective
output files.NamespaceInspector
and
requires no hints, since it is based completely on the Progress schema
dump file.
<?xml version="1.0"?>This information is used by the
<!--Progress schema namespace dictionary-->
<!--Generated Tue Nov 30 10:51:29 EST 2004-->
<schema source="junk.df">
<database name="custsvc">
<table name="customer">
<field name="acct-num" type="integer" />
<field name="first-name" type="character" />
<field name="last-name" type="character" />
<field name="since" type="date" />
</table>
</database>
</schema>
NamespaceLoader
to load entries into the SchemaDictionary
,
in order to enable symbol
resolution of schema entity names during Progress source code
parsing.HibernateMappingInspector
(TBD), based upon the Progress schema, with a strong reliance upon
hints
about field type conversions and table relations. Alternately, they may
be produced by a set of classes which uses the P2R mapping document as
an input (TBD).org.hibernate.tools.hbm2ddl.SchemaExport
tool provided with the Hibernate toolset. This tool produces data
definition language ("DDL"), which is applied against the target
relational database to create the schema. The DDL optionally can be
saved off separately for reference and debugging purposes, batch use or
database recovery.<?xml version="1.0"?>Each database, table, and field entity within the
<!-- Progress/Relational schema cross-referenced mapping -->
<schema>
<!--Progress schema-->
<progress source="custsvc.df">
<database name="custsvc" ref="CUSTSVC">
<sequence name="next-cust-num" ref="nextCustNum">
<property name="INITIAL" value="0" />
<property name="INCREMENT" value="0" />
<property name="CYCLE-ON-LIMIT" value="no" />
<property name="MIN-VAL" value="0" />
</sequence>
<table name="customers" ref="CUSTOMERS">
<property name="AREA" value="Schema Area" />
<property name="DESCRIPTION" value="Customer service database" />
<property name="VALMSG" value="SERVICE APP 1.0" />
<property name="DUMP-NAME" value="customer" />
<field name="acct-num" ref="ACCT_NUM" type="integer">
<property name="FORMAT" value="999999" />
<property name="INITIAL" value="0" />
<property name="LABEL" value="Account Number" />
<property name="POSITION" value="2" />
<property name="SQL-WIDTH" value="4" />
<property name="ORDER" value="10" />
</field>
<field name="first-name" ref="FIRST_NAME" type="character">
<property name="FORMAT" value="X(12)" />
<property name="INITIAL" value="" />
<property name="LABEL" value="First Name" />
<property name="POSITION" value="10" />
<property name="SQL-WIDTH" value="130" />
<property name="ORDER" value="20" />
</field>
<field name="last-name" ref="LAST_NAME" type="character">
<property name="FORMAT" value="X(12)" />
<property name="INITIAL" value="" />
<property name="LABEL" value="Last Name" />
<property name="POSITION" value="10" />
<property name="SQL-WIDTH" value="130" />
<property name="ORDER" value="30" />
</field>
<field name="since" ref="SINCE" type="date">
<property name="FORMAT" value="99/99/9999" />
<property name="INITIAL" value="?" />
<property name="LABEL" value="Customer Since" />
<property name="POSITION" value="5" />
<property name="SQL-WIDTH" value="4" />
<property name="ORDER" value="40" />
</field>
<primary-key>
<field name="acct-num" />
</primary key>
</table>
<table name="invoices" ref="INVOICES">
<property name="AREA" value="Schema Area" />
<property name="DESCRIPTION" value="Invoices for customer orders" />
<property name="VALMSG" value="SERVICE APP 1.0" />
<property name="DUMP-NAME" value="invoices" />
<field name="invoice-num" ref="INVOICE_NUM" type="integer">
<property name="FORMAT" value="999999" />
<property name="INITIAL" value="0" />
<property name="LABEL" value="Invoice Number" />
<property name="POSITION" value="2" />
<property name="SQL-WIDTH" value="4" />
<property name="ORDER" value="10" />
</field>
<field name="cust-num" ref="CUST_NUM" type="integer">
<property name="FORMAT" value="999999" />
<property name="INITIAL" value="0" />
<property name="LABEL" value="Customer Account Number" />
<property name="POSITION" value="2" />
<property name="SQL-WIDTH" value="4" />
<property name="ORDER" value="20" />
</field>
<primary-key>
<field name="invoice-num" />
</primary key>
<foreign-key table="customers">
<field local="cust-num" foreign="acct-num" />
</foreign-key>
</table>
</database>
</progress>
<!--Relational schema-->
<relational>
<database name="CUSTSVC" ref="custsvc">
<table name="CUSTOMERS" ref="customers">
<column name="ACCT_NUM" ref="acct-num" type="integer">
<property name="format" value="000000" />
</column>
<column name="FIRST_NAME" ref="first-name" type="string" />
<column name="LAST_NAME" ref="last-name" type="string" />
<column name="SINCE" ref="since" type="date">
<property name="format" value="MM/dd/yyyy" />
</column>
<primary-key>
<column name="ACCT_NUM" />
</primary key>
</table>
<table name="INVOICES" ref="invoices">
<column name="INVOICE_NUM" ref="invoice-num" type="integer" />
<column name="cust-num" ref="CUST_NUM" type="integer" />
<primary-key>
<column name="INVOICE_NUM" />
</primary key>
<foreign-key table="CUSTOMERS">
<column local="CUST_NUM" foreign="ACCT_NUM" />
</foreign-key>
</table>
</database>
</relational>
</schema>
<progress>
element has an analog within the <relational> element. The ref
attribute in each of these tags refers to the name of that entity's
analog. Progress properties which do not change in the conversion are
not represented on the relational side, as these values can be looked
up
as necessary. Properties that do change (e.g., format clauses) or that
are necessarily different on the relational side (e.g., data type) are
represented by an appropriate tag or attribute.SummaryInspector
. Its
primary purpose is to give a more compact overview of the Progress
schema than is easily gleaned from reading the dump file. This report
is
useful for early analysis of a Progress schema.<database_name>.<table
_name
>.<field
_name
>
(a fully qualified field reference)<database
_name
>.<table
_name
>
(a fully qualified table reference)<table
_name>.
<field_name>
(a partially qualified field reference)<database
_name
>
(a
unique reference to a database)<table
_name
>
(an
unqualified reference to a table)<field
_name
>
(an
unqualified reference to a field)uast
package summary.SchemaDictionary
is responsible for loading schema namespace information, for managing
multiple scopes, and for providing an API to resolve schema names. A
default lookup scope is created upon construction of this object. It
relies upon the SchemaConfig
to determine which, if any, databases it should load by default into
this outermost scope. Calling code may create additional scopes "on
top" of this scope and load arbitrary database names, table names, and
field names into these scopes to augment the default lookup behavior,
as blocks are traversed during source code parsing. In a namespace lookup, more recently added
scopes will always be searched before older scopes. Thus, the
default scope added by the SchemaDictionary
constructor
will always be searched last.NameNode
object. Each NameNode
contains a Namespace
object representing that entity's private namespace for its child
entities. For instance, a database NameNode
has a private
namespace of table nodes
; a table NameNode
has a private namespace of field nodes;
a field NameNode
has an empty namespace.SchemaDictionary
. These
scopes are implemented as a stack; calling code can push a new logical
scope onto the top of the stack with SchemaDictionary
's addScope
method as it enters a new scope during parsing. Later, it should pop
the logical scope off the top of the stack with the removeScope
method, as it exits the corresponding scope in Progress source code.<database_name>.<table
_name
>.<field
_name
>
,
for instance (using names from the diagram above), DB_2.Table
_
1.F2
,
we first search for DB-2
in the Global Database
Namespace. Upon finding the name DB_2
, we look next for
the name Table_1
in the DB_2 Private Table Namespace.
Upon finding the name Table_1
, we look next for the name F2
in the DB_2
.Table_1 Private
Field Namespace. Upon finding the name F2
, we return the NameNode
found. If the DB_2
qualifier component of the name were
missing, the search would begin at the Global Table Namespace level. If
both the DB_2
and the Table_1
qualifier
components of the name were missing, the search would begin (and end)
at the Global Field Namespace level. This portion of the algorithm is
implemented in the SchemaDictionary.Scope.findNode
method.Namespace
's
find
method. Namespace
contains a java.util.List
of NameNode
objects which is sorted lazily when a search
is requested. The binary search implemented by
java.util.Collections
is used. For database name searches, this
binary search uses a comparator which requires an
exact match of all characters in the name component being resolved
(since database symbol names may not be abbreviated). For table and
field name searches (for which abbreviated symbol names are allowed),
both exact matches and abbreviated name matches are permitted. The
abbreviated name binary search is
performed using a comparator
which is more lenient: it permits comparisons between a full name
and a matching abbreviation to test as equivalent. Only one type of
search -- exact match or abbreviated match -- is performed at a time by
Namespace
's find
method.
for the current Namespace
.
If a single match is found, it has
succeeded. However, ambiguous matches must also be considered. To
handle this, a successful match is followed up by a test of the NameNode
s
which immediately surround the matching node in the namespace's NameNode
pool. If more than one match is found, the resolution is ambiguous, and
an AmbiguousSchemaNameException
is thrown. This exception contains a list of all the matching NameNode
objects.Progress
Property |
Context |
Purpose |
P2J
Use |
|||||||||||||||||||||||||||||||||
NAME |
database |
Logical name of the database. |
Always appears as "?" in the DF
file, so this value is provided in the P2J configuration. |
|||||||||||||||||||||||||||||||||
NAME | sequence |
Logical name of the sequence. |
TBD |
|||||||||||||||||||||||||||||||||
INITIAL |
sequence |
Initial value of the sequence. |
TBD |
|||||||||||||||||||||||||||||||||
INCREMENT | sequence |
Amount by which sequence is
incremented to generate a new, unique value. |
TBD |
|||||||||||||||||||||||||||||||||
CYCLE-ON-LIMIT | sequence |
Whether sequence wraps when it
reaches its limit. |
TBD |
|||||||||||||||||||||||||||||||||
MIN-VAL | sequence |
Minimum sequence value. |
TBD |
|||||||||||||||||||||||||||||||||
NAME |
table |
Name of the table. |
Translated to replace invalid
characters. Becomes basis for relational table name and Java data
model class name. Value may be overridden by hints. |
|||||||||||||||||||||||||||||||||
AREA |
table |
Progress housekeeping - table's
storage area. |
Discarded. |
|||||||||||||||||||||||||||||||||
DESCRIPTION |
table |
Table description. This
value does not appear to be used at runtime, but is rather for the
convenience of developers and administrators. |
Comment in the hibernate
configuration files where table is defined. |
|||||||||||||||||||||||||||||||||
VALEXP |
table |
Validation expression to be applied against record deletions for this table. This expression must ultimately evaluate to true or false, but it can contain any valid Progress code, including preprocessor file includes. | Converted into valid Java
code and triggered on record deletes, possibly via a stored procedure. Implementation TBD. Note: There are no instances of table-level validation expressions in any of the schema dumps we have processed. |
|||||||||||||||||||||||||||||||||
VALMSG | table |
Message to display to user if
validation expression fails. |
Converted to a Java string
resource and displayed to the user upon a failure of the converted
(Java) validation expression. Implementation TBD. |
|||||||||||||||||||||||||||||||||
DUMP-NAME | table |
Progress housekeeping - name of
file to which table definition is dumped on export. |
Discarded. It is assumed
third party tools will be used to manage the relational database after
conversion. |
|||||||||||||||||||||||||||||||||
FROZEN | table |
Progress housekeeping -
indicates table definition is read-only in data dictionary. |
Discarded. | |||||||||||||||||||||||||||||||||
HIDDEN | table |
Indicates whether table is
hidden. The implications of this are currently unknown. |
TBD |
|||||||||||||||||||||||||||||||||
NAME |
field |
Name of the field. |
Translated to replace invalid
characters. Becomes basis for relational column name, Java data
model instance variable name, and names of accessor/mutator methods in
data model class. Value may be overridden by hints. If no field LABEL property is provided, the unmodified Progress field name is stored as this column's label (see below). |
|||||||||||||||||||||||||||||||||
OF | field |
Name of table for which this
field is defined; a housekeeping vestige of the DF file format. |
Used as a reality check during
DF file parsing only. Discarded thereafter, since a column's
containing table is implicit. |
|||||||||||||||||||||||||||||||||
AS | field |
Data type of field. |
Converted to the appropriate
P2J data wrapper type (see com.goldencode.p2j.util )
or Hibernate type, from which the appropriate SQL type and Java data
type
are inferred. The mapping is as follows:
|
|||||||||||||||||||||||||||||||||
FORMAT | field |
Default format string used to
display field's value in UI. |
Translated to a compatible Java
format string and used when updating the UI. Implementation TBD. |
|||||||||||||||||||||||||||||||||
LABEL | field |
Label which appears by default
when this field appears in UI and in printed reports. |
Stored verbatim and used as the
field's default label in the UI and in printed reports.
Implementation TBD. This information is used to help generate XML comments in the Hibernate mapping documents and JavaDoc comments in the Java Data Model Object class definitions. If no label is provided, the Progress field's name (unmodified), must be used as its label, to maintain a compatible look and feel. |
|||||||||||||||||||||||||||||||||
POSITION | field |
Unknown. Possibly an
indicator of the order in which fields are displayed in the table
editor of the data dictionary. |
TBD |
|||||||||||||||||||||||||||||||||
SQL-WIDTH | field |
Indicates width for visual
representation of a field; highly related for format
phrase. For fields with extent > 0, this value must first be
divided by the extent size. |
Stored and used in calculating
the length in bytes of a character or binary column, but only if larger than the length
calculated using a statistical analysis of existing data for a given
column. |
|||||||||||||||||||||||||||||||||
ORDER | field |
Default order in which the UI
widget which displays this field's value is drawn in relation to other
fields' widgets, when multiple fields from a table are displayed.
Also the order in which table fields are exported to Progress export
files. The order assigned to a given field is compared with that
assigned to other fields in the table to determine the current field's
relative position (in ascending order). |
Stored and used to reorder
properties within a class in the P2O schema document. The order
is critically important when importing data from Progress export files,
as the data must be read from these files in this order. |
|||||||||||||||||||||||||||||||||
EXTENT | field |
If greater than 0, indicates the
field holds an array of values of the field's data type, rather than a
single value of that type. The value of the extent is the size of
the array. |
Array values are stored in a
secondary table. The secondary table contains a foreign key
column related to the primary key of the table containing the array
column. An index column in the secondary table maintains the
appropriate indexing of the array values. If multiple columns in
the same table use the same extent value, they are all be stored in the
same, secondary table, each in a separate column. Otherwise, a
separate, secondary table exists for each occurrence of a new extent
value within the same Progress table. The number of rows in a secondary table equals the number of rows in the primary table times the size of the extent which the secondary table manages. For example, a table containing 1000 rows which manages four fields, each with an extent of 12 will be related to a secondary table containing (1000 * 4 * 12), or 48,000 rows. The data model object which maps to a table containing array columns provides access methods to make this backing implementation transparent. See also Converted Table Structure. |
|||||||||||||||||||||||||||||||||
DECIMALS | field |
|||||||||||||||||||||||||||||||||||
COLUMN-LABEL | field |
Text which overrides LABEL when
the field is displayed in columns. |
Stored verbatim and used as the field's default column label in the UI and in printed reports. Implementation TBD. | |||||||||||||||||||||||||||||||||
INITIAL | field |
Initial value of the field. |
Translated into a value
appropriate for use in Java. Used to initialize the instance
variable which corresponds with this field upon construction of the
appropriate Java data model class. If no initial value is
defined, use the default for the field's data type. |
|||||||||||||||||||||||||||||||||
HELP | field |
Help text to display to user
when the UI widget which represents this control gains input focus. |
TBD. |
|||||||||||||||||||||||||||||||||
MANDATORY | field |
Indicates whether the field may
contain the unknown value. If set, the unknown value is
disallowed. |
Maps to NOT NULL or the
appropriate equivalent. |
|||||||||||||||||||||||||||||||||
CASE-SENS |
field |
Indicates the field is case
sensitive (it is not, by default). This refers to the field's
data, not its name. |
TBD. |
|||||||||||||||||||||||||||||||||
VALEXP | field |
Validation expression to be applied against record-level inserts and updates when the current field's value is saved from the record buffer to the database. This expression must ultimately evaluate to true or false, but it can contain any valid Progress code, including preprocessor file includes. | TBD. |
|||||||||||||||||||||||||||||||||
VALMSG |
field |
Message to display to user if
field's validation expression fails. |
TBD. |
|||||||||||||||||||||||||||||||||
VIEW-AS |
field |
Overrides the default widget
type used to display the current field in the UI. |
TBD. |
|||||||||||||||||||||||||||||||||
NAME |
index |
Logical name of the index. |
Translated into an
SQL-compatible name and used as the name of an analogous index defined
on the relational table associated with the Progress index' table. |
|||||||||||||||||||||||||||||||||
ON |
index |
Name of the table for which the
index is defined. |
Used as a reality check during DF file parsing only. Discarded thereafter, since an index' containing table is implicit. | |||||||||||||||||||||||||||||||||
UNIQUE |
index |
Indicates that the field or
fields comprising the index must be unique for each row. In the
case of multiple field indexes, the combination of all fields in the
index must be unique. |
Translated to a unique
constraint which is applied to the table for which the index is defined. |
|||||||||||||||||||||||||||||||||
PRIMARY |
index |
Indicates that the index is used
as the default for query purposes. |
TBD. |
|||||||||||||||||||||||||||||||||
WORD |
index |
Indicates that the index is a
lexical index on the individual words in the values of a character
field. |
Not used in initial
project; implementation will be deferred. |
|||||||||||||||||||||||||||||||||
DIRECTION | index field |
Indicates sort direction for the
index: ascending or descending. |
TBD. |
|||||||||||||||||||||||||||||||||
ABBREVIATED |
index field |
TBD. |
TBD. |
character
becomes the Hibernate string
type, integer
remains integer
, etc.).character
which in practice contains
very
lengthy data must be mapped to type text
(which
ultimately
is mapped by Hibernate to a database vendor-specific type such as LONGVARCHAR
).
In the event of refactoring a single field to multiple columns, or
multiple columns to a single field, type is represented in the hints
document by a non-standard identifier. This causes the conversion logic
to look up a class to handle the mapping in a custom way (TBD).time
type in Progress, but
there
is a Hibernate time
data type. For consistent use of time
information across tables, it may make sense to refactor all
multi-field
time representations from a Progress schema into a single relational
database column defined as Hibernate's time
type. A
hypothetical set of four Progress integer
fields
representing hours, minutes, seconds, and milliseconds would in this
case be mapped into a single column of type time
. This
would result in a special type descriptor (perhaps an XML attribute of type="specialtime1"
)
being defined in a hint and backed by a custom converter class.<converted
table name>__<extent size>
.vendor
which
contains several simple fields (name
and address
),
as well as several fields (order
, invoice
,
and widget
), which have extents greater than 0.Table vendor |
character name |
character address |
integer[5] order_number |
character[5] invoice |
character[3] widget |
Table vendor (primary) |
Table vendor__3 (secondary) |
Table vendor__5 (secondary) |
||
integer primary_key |
integer parent__id (references
vendor.primary_key) |
integer parent__id (references
vendor.primary_key) |
||
varchar name |
varchar widget |
integer order_number |
||
varchar address |
integer list__index |
varchar invoice |
||
integer list__index |
vendor
, where the multiplier is the extent size
represented by that secondary table. So, for instance, if table vendor
has 50 rows, table vendor__3
has 150 (50 * 3) rows, and
table vendor__5
has 250 (50 * 5) rows. Each
secondary table has a foreign key parent__id
which
references primary_key
in table vendor
.
A secondary table's primary key is a composite of parent__id
and list__index
, where the value of the latter represents
a record's zero-based index of its position in the original Progress
extent field's array. In other words, the data at each index in
the original extent field is distributed among <extent size> records in the
new, secondary table, as the following example indicates. The
order of the data values in the original, Progress extent field is
strictly maintained.name | address |
order_number |
invoice |
widget |
Vendor ABC |
123 Main Street, Anytown, USA,
etc... |
5 22 86 87 190 |
65-ABGD-001 78-FFFE-005 12-UIOP-002 56-B8TY-099 72-MNQW-62 |
W8 W3 W6 |
|
|
|
com.goldencode.p2j.util
)
to represent each simple field;List
s of these wrappers to represent an extent field
with an extent greater than 0 (in the case there is only one such
field); andvendor
table
in the previous section,
illustrates the core of the DMO class definition (sans JavaDoc
comments). This example assumes the initial value is specified as
the unknown value (?
) in the Progress schema for each of
the table's fields.
import java.util.*; |
vendor
maps to a DMO class named Vendor
.
The vendor's name
and address
properties
are accessible via simple getter and setter methods. The
properties associated with extent fields (widget
, orderNumber
,
and invoice
) are accessible via specialized getter and
setter methods which accept an index
parameter. The
index
parameter is zero-based; therefore it must
fall within the rangeindex
< extent sizeArrayIndexOutOfBoundsException
will be thrown for index
values outside of this range.widget
)
in the original Progress table. This maps to a simple List
of character
objects in the DMO. However, there are
two fields of extent 5 (order_number
and invoice
)
in the Progress table. These are grouped together into a static
inner class (Composite5
) of the top level class Vendor
.
The mapping of fields with a common extent as properties of an inner
class allows Hibernate to manage these properties as a single,
secondary table (vendor__5
-- see previous section) mapped to a
single class (inner class Vendor$Composite5
). The
naming convention for such inner classes is always:<converted table name>$Composite<extent
size>
Referenced
Table (A) |
Referencing
Table (B) |
|
column
1 |
column
1' |
|
column
2 |
column
2' |
|
column
3 |
column
3' |
|
additional columns... |
additional columns... |
Referenced
Table (A) |
Referencing
Table (B) |
|
surrogate
primary key |
surrogate
primary key |
|
column
1 |
foreign
key to table A |
|
column
2 |
additional columns... |
|
column
3 |
||
additional columns... |
use-index
directive,
is indexed. This assists the performance of the SQL order
by
phrase when sorting results.use-index
or is the primary index, the dropped index is flagged to delegate
references (including default references to the primary index) to the
surviving index.order by
clauses
(primary indexes and those referenced in source code by a use-index
statement) - all fields are considered for sorting;of
language keyword within a record phrase, as
in: ...tableA [outer-join] of tableB...
tableA
and tableB
,
the following information can be gleaned from the outcome of this
test. Assuming condition 1 is met...tableA
meets condition 2, but tableB
does not, a one-to-many relation between tableA
and tableB
is implied. Furthermore, a
primary key to foreign key relation between tableA
and tableB
is implied. The field
(or composite of fields) defined for the index which meets condition 2
is essentially the equivalent of a primary key. The matching
field (or
composite of fields) in the other table is essentially the equivalent
of a foreign key.tableA
and tableB
contain a
unique index which meets condition 2, and both indexes define the
identical set of index fields, the relation between the tables is
one-to-one. The set of fields in the common, unique index form
the primary/foreign key relation. Essentially, this set acts as
both a primary and foreign key in both tables.?
) are not
considered equal to one another. That is, for a unique index
containing a field fieldA
, which is not declared mandatory, two records containing
identical data in all other fields and the unknown value in fieldA
are each considered unique, and are both permitted in the table.NULL
in SQL is the equivalent for Progress' unknown value. Some
relational databases (e.g., PostgreSQL), consider two NULL
values inequal, which lends itself to a clean mapping between the
unknown value and NULL
. Other databases allow only
a single NULL
value for a column with a unique constraint.Sort Case |
Result |
explicit sort using BY + incompatible index selection | The sort will be completely
defined by the BY criteria. The index has no affect. |
explicit sort using BY + compatible single index selection cases (implicit or explicit) for secondary sort | Index driven sort. The BY
clause is only a subset so the index itself specifies the complete sort. |
explicit single index choice using USE-INDEX (with no explicit sort) | Index driven sort. |
implicit single index choice (with no explicit sort) | Index driven sort. |
"Abbreviate" is an index option that lets you conveniently search for a partial match based on the first few characters of a field (like usingBEGINS) in the FIND ... USING statement. This option is only available on indexes that have a character field as their last index component.Single Index Selection Rules
Operator |
Match
Type |
EQ, = |
Equality |
LT, < | Range |
GT, > | Range |
LE, <= | Range |
GE, >= | Range |
BEGINS | Begins (this plays a dual roal
in both rules 4 and 5 below) |
CONTAINS | Word |
Operand Reference Summary |
Right Operand | |||||||||
no
reference |
direct
reference |
nested
reference |
||||||||
Left
Operand |
no
reference |
|
|
|
||||||
direct
reference |
|
|
|
|||||||
nested
reference |
|
|
|