Generating
Tables and Views in BRIM®
Tables and views are
generated by the BRIM® Relational Object Generator.
I. Relational Object
Generator
The Relational Object
Generator reads the UML repository and generates several SQL scripts that can
be used to create a combination of database objects, implementing a “relational
database” version of the class diagrams that are stored in the repository. The
data stored in the objects created from the generated scripts can be
manipulated using the standard SQL data manipulation language (DML) commands
that are supported by the Oracle RDBMS. The generated objects will
transparently invoke the UML engine to perform all necessary validations,
including the complex business rules that are supported by the UML engine and are
not available as declarative constraints through the RDBMS.
The scripts contain
SQL data definition language (DDL) commands to create several types of objects:
·
Relational
tables
·
Relational
views
·
Generic
structure views
·
Association
tables
·
Association
views
·
Association
generic structure views
·
Abstract class
views
·
Abstract class
generic structure views
·
Constraints
and indexes
This document will
describe the following:
·
Objects that
are implemented by the generator
·
Use of the
front-end interface
·
Execution of
the generated scripts
·
Use of the
generated objects by developers and end-users.
II.1. Generated Object Descriptions
This section
describes the objects generated in the BRIM®
environment.
The Generator
implements two distinct types of relational tables. These tables are generated
for concrete classes and for association classes. Both types of tables are
named using the convention T_ followed by the code for the concrete or
association class. For example, the relational table for the class NewCorp is named T_NewCorp.
The relational tables
for concrete classes are all structured according to the following rules:
·
First Column
The
first column in the table is the object ID. This column is named by appending
_OID to the class code. Applying this rule to NewCorp,
the first column in T_NewCorp is named NewCorp_OID.
·
Business
Events
If
the class is a business event, the table will include a column that stores the
current state of the object. This column is named state_id
and will follow the object ID column.
·
Object ID
columns
Following
the object ID are columns that contain the object IDs for any concrete
generalizations of the class. These columns are also named by appending _OID to
the class code of the generalization class.
·
Attribute
columns
Following
the object ID of the class and the object IDs of its concrete (if any)
generalizations are columns for each attribute of the class. This includes all
attributes that are inherited from either concrete or abstract generalizations
of the class. The columns are named by the attribute code (for example: Corp)
unless the attribute is multi-lingual.
If the class has
history, the attributes will include start and end date columns. These columns
are named by appending SDate and EDate
to the class code (GlAcctSDate, GlAcctEDate).
Manipulation:
uml_ddl.modifyassociationcardinality(associd,side,newcard,validationmode);
Manipulation:
Manipulation:
Information
Classes with History
Class Associations
After all of the
attributes have been included, columns are generated based on associations for
the class. This includes all associations that are inherited from either
concrete or abstract generalizations of the class. If the class is the detail
end of a master-detail association, the association is implemented in the
relational table as a foreign key relationship.
The Object ID of the
master class in the association is included as a column. The name of this
column can be specified using the FK keyword on the association. If the FK
keyword exists, the foreign key name specified by the keyword is used as the
column name. If the keyword does not exist, the column is named by appending
_OID to the code for the class that is the master end of the association.
Recursive
Association
If the association is
recursive (that is, the master and detail ends of the association are the class
that the relational table is based on), the column representing the association
is named by appending _RFK to the class code. If the FK
keyword exists, the foreign key name specified by the keyword is used as the
column name. If the keyword does not exist, the column is named by appending
_OID to the code for the class that is the master end of the association.
Abstract Classes
If the master class
in a master-detail association is abstract, a foreign key relationship can be
said to exist between the detail class and all of the first level concrete
classes that are specializations of the abstract master class (and therefore
inherit its associations). If the association is with an abstract class, there
will be multiple foreign key columns in the relational table.
These columns will be
named by appending either _OID or _RFK (for a recursive master detail
relationship in the concrete specialization of an abstract class) to the class
code of the concrete class. The FK keyword is ignored in cases involving
abstract classes.
Relational Table for Association Class
The relational table
for an association class is generated in a similar fashion to the table for
other concrete classes. The relational table is named using the same convention
(T_) and contains the column for the class object ID.
The relational table
also contains columns for the object IDs of the objects on both sides of the
linked association. If either class (or both classes) is abstract, the object
IDs of the concrete specializations of the class, are used.
Even though the
relational table may contain multiple columns for each side of the association,
the UML engine will only allow one class from each side to be populated. These
columns are named by appending either _OID or _RFK to the class code, depending
on whether or not the association is recursive.
If the association is recursive and
master-detail, the column for the class on the master side will be named using
_RFK and the column for the class on the detail side will be named using _OID.
If the recursive relationship is not master-detail, the column for the class at
the head of the association will be named using _RFK and the column for the
class at the end of the association will be named using _OID.
History
If the linked
association has history, two columns (SDate and EDate) will be added to the relational table, representing
the start and end dates of the association, respectively. After the columns for
the object IDs of the classes at the ends of the linked association, and the
start and end date columns (if the association has history), the relational
table for the association class will contain columns for the attributes of the
class and any foreign key relationships that represent master-detail
associations, just as in the relational tables for concrete classes as
described above.
If the class is
defined as being “out of the engine” (with the ClassOutOfEngine
keyword), the relational table will only contain the object ID of the its first concrete generalization, not all generalizations.
In addition, inherited attributes and associations that are not physically
owned by the class will not be included in the relational table.
The generated
relational views are the SQL-based mechanism for interacting with the UML
engine. A companion relational view is generated for every relational table.
The relational views support the SQL DML commands SELECT, INSERT, UPDATE and DELETE.
Relational views are named using the class code. The relational view that
corresponds to the class NewCorp is simply named NewCorp.
Relational views
contain all of the columns of their companion relational table. The companion
relational table for the class (and therefore the relational view) can be
identified by the prefix T_ followed by the class code.
The relational view NewCorp contains all of the columns in the companion
relational table T_NewCorp with the following
exceptions. There can be an additional column in the relational view that is
not present in the companion table, if the class that the view is based on has
history.
A column is included
in the relational view that contains the value returned by the display function
for the class. The column is named by appending the suffix _DSP to the class
code. In the relational view NewCorp, the column
would be named NewCorp_DSP.
If no display
function is defined for the class in the UML repository, the column will
contain an appropriate message instead of the display value.
Display function
columns are also added to the view preceding each class association column that
represents a master-detail relationship. These columns are named by appending
_DSP (or _DSR, in the case of a recursive relationship) to the foreign key
column name. This name can either be a class code or a foreign key name that is
specified with the FK keyword.
An additional display
function column is added to the view for business event classes. This column,
named state_dsp returns the display value for the
object state stored in state_id.
Relational views for
classes with history contain an active flag column with the name Activ_YN. This column will contain the literal value Y if
the record represents an active object, or N if the
record represents an inactive object.
If the class contains
attributes that are multi-lingual, the relational view will contain a single
column for the attribute. This column, named based on the attribute code, will
contain the value of the attribute in the currently active language for the
system.
The relational view
is implemented as a simple SELECT on the companion relational table with the
form: SELECT column list FROM T_class code. The Activ_YN field, if present, is implemented as a DECODE on
the class code EDate column. The single field for a
multi-lingual attribute, if present, is implemented as a DECODE on the
currently active language and returns the value from the appropriate column in
the companion relational table.
To support the other
DML statements, the generator creates INSTEAD OF triggers on the relational
view. The generator creates an INSTEAD OF INSERT, INSTEAD OF UPDATE and INSTEAD
OF DELETE trigger. The appropriate trigger is executed whenever a SQL statement
would perform an Insert, Update or Delete on the relational view. These
triggers convert the SQL based DML to a series of calls to the UML engine,
creating a bundle of operations to be performed by the engine. If the UML
engine successfully processes the bundle, the results of the DML operation will
be reflected in the system’s generic structures as well as the appropriate
relational tables. If the UML engine is unable to validate the bundle, an
appropriate error message will be returned. There are also some restrictions on
the type of operations supported by the relational views. The views cannot be
used to create inactive objects (by setting EDate or Activ_YN on insert). For business event classes, the state
cannot be set using the relational views. The views also do not support the
modification of the object id or of any display values. If the relational view
corresponds to an association class, it cannot be modified. The association
class object must be deleted and recreated.
The only supported
mechanisms for modifying data in the system are the UML engine and the
relational views. Any direct manipulation of either the generic database
structures or the relational tables will create inconsistent data in the system
and any subsequent queries or DML against the relational views or calls to the
UML engine may not produce the correct results.
If the class
is defined as being “out of the engine” (with the ClassOutOfEngine
keyword), the relational view not include generalization object IDs or
inherited attributes and associations that are not included in the relational
table. For
these classes, no INSTEAD OF triggers will be generated. All DML
operations must be performed directly on the relational table, as the engine
will not support any operations on the class.
The generic structure
views are generated to facilitate certain internal operations such as
rebuilding a corrupted database. They are not designed for general use. Each
relational table will have a companion generic structure view generated. The
generic structure view is named by replacing the T_ prefix of the relational
table with V_. The column list of the generic structure view is identical to
its companion relational table.
The generic structure
views only support SELECT operations. They are based on the underlying generic
structure (UML_OBJECT) and use various functions provided by the UML engine to
retrieve object IDs, attribute values and associated object IDs. Because of
their reliance on function calls, the performance of the generic structure
views will be significantly slower than the relational view that is associated
with the same relational table.
If the class
is defined as being “out of the engine” (with the ClassOutOfEngine
keyword), the generic structure view will be identical to the relational view
for the class.
Association tables
are generated to provide a relational structure that supports many-to many
relationships between classes. Association tables are only generated as
specified by the TABLE keyword on an association. If the association involves
an abstract class, the TABLE keyword will specify which of the concrete
specializations of the abstract class an association table should be generated
for. The name for an association table is constructed by concatenating the
prefix T_ with the table name specified in the keyword.
An association table
contains either two or four columns, depending on whether or not the
association has history. For associations without history, the two columns are
named by appending _OID to the class codes for the classes at the ends of the
association.
If the association is
recursive, the column for the object ID at the head of the association will be
named using _RFK instead of _OID. The column for the class at the end of the
association appears before the column for the class at the head of the
association. If the association has history, two additional columns are added
to the association table. These columns are <Class Code>SDate and <Class Code>EDate,
representing the start date and end date of the association, respectively.
The association views
are analogous to the relational views except that their companion entities are
the association tables. Each association table has a companion association view
that is named using the value in the TABLE keyword. The name of the companion
association table for an association view can be constructed by adding the
prefix T_ to the name of the association view. The association views support
the SQL DML statements SELECT, INSERT, UPDATE and DELETE.
The association view
contains all of the same columns as the companion association table. In the
same manner as the relational views, the association views are implemented as a
simple SELECT on the companion association table. If the association has
history, the association view will contain an additional column, Activ_YN. This column is an active flag and is implemented
as a DECODE on the End_DT column.
The INSERT, UPDATE
and DELETE statements are implemented using INSTEAD OF triggers on the
association view. The triggers support these statements by creating a bundle of
commands to be executed by the UML engine. Insertions and deletions are
implemented by issuing CREATE_ASSOC and DELETE_ASSOC commands to the UML
engine.
Since the UML engine does not explicitly
support the updating of associations, updates are supported using a two-step
process. The existing association is deleted and then a new association is
created using the updated values. As with the relational views, there are some
restrictions on the type of operations supported by the association views.
These restrictions will be fully discussed in the section of this document
describing the use of the association views.
II.6.
Association Generic Structure Views
The Association
generic structure views are also generated to facilitate certain internal
operations. They are not designed for general use. Each association table will
have a companion Generic Structure View generated. The Association Generic
Structure View is named by replacing the T_ prefix of the association table
with V_. The column list of the association generic structure view is identical
to its companion association table.
The association generic
structure views only support SELECT operations. They are based on the
SUPER_ASSOC generic structure. Unlike the generic structure views for
relational tables, the association generic structure views do not rely on
various function calls. The association generic structure views are implemented
as simple SELECTs because the SUPER_ASSOC table
contains all of the necessary data to return the desired results.
The abstract class
views are generated as query-only structures corresponding to the abstract
classes that are defined in the repository. These views are based on the
relational tables that are generated for the concrete specializations of the
abstract class. The abstract class views are implemented as a UNION ALL query
that combines the results of simple SELECTs against
the relevant relational tables. The abstract class view is named using the
class code of the abstract class. The abstract class view for the class GlChart is named GlChart.
The column list of
the abstract class views only contains information that is visible in the
abstract class and inherited by the concrete specialization classes. The first
column is the object ID of the abstract object. Since abstract classes are not
really instantiated in the database, this column contains the class code_OID column from each of the UNIONed
relational tables. To eliminate confusion, the column is named by appending OID
to the name of the abstract class. This means that the first column in the
abstract class view GlChart is named GlChart_OID and contains the data from T_GlFnclChart.GlFnclChart_OID
and T_GlMgntChart.GlMgntChart_OID.
The second column is
named ClassCd and contains the class code for the
concrete specialization class that the record belongs to. Following ClassCd,
a column is included in that contains the value returned by the display
function for the appropriate concrete specialization class. The column is named
by appending the suffix _DSP to the class code of the abstract class. In the
abstract class view GlChart, the column would be
named GlChart_DSP and would contain the value
returned by the display function for either GlFnclChart
or GlMgntChart, depending on the class of the record.
The display column is
followed by: columns for any concrete generalizations of the abstract class,
columns for the attributes of the abstract class, and columns for master-detail
associations that are implemented as foreign key relationships. All of these
columns follow the same naming conventions that apply to the relational tables
that the abstract class view is based on.
As with relational views, the abstract class views also contain columns
for the display functions of class associations that are implemented as foreign
key relationships. These columns are named by appending _DSP to the master
class name, or _DSR in the case of a recursive relationship.
If the abstract class
is an association class, the abstract class view will be based on a UNION ALL
query of the concrete specializations of the association class. The abstract
class view will be generated as described above, with the following additional
columns. After the display column, the abstract class view will contain columns
for the object IDs of the objects on both sides of the linked association. If
either class (or both classes) is abstract, the object IDs of
the concrete specializations of the class are used, just as in the
relational tables that the abstract class view is based on.
If the linked
association has history, the <class>SDate and
<class>EDate columns will be added to the
abstract class view as well.
II.8.
Abstract class generic structure views
The abstract class
generic structure views are based on the underlying generic structure
(UML_OBJECT) for the concrete specializations of the abstract class. The abstract
class generic structure views are named by adding the prefix V_ to the class
code of the abstract class. The abstract class generic structure view for the
class GlChart is named V_GlChart.
The column list of the abstract class generic structure view is identical to
its companion abstract class view with the exception of multi-lingual
attributes. The abstract class generic structure view will contain multiple
columns for each multi-lingual attribute, using the same naming convention as
the relational tables.
The abstract class
generic structure views use various functions provided by the UML engine to
retrieve object IDs, attribute values and associated object IDs. The views
query the appropriate generic structure as indicated above, adding an in-list
of the class codes for the concrete generalization classes as selection
criteria in the WHERE clause of the view. Because of their reliance on function
calls, the performance of the abstract class generic structure views will be
significantly slower than the abstract class view that is associated with the
same class.
The Relational Object
Generator generates a script to create two types of constraints and one type of
index for all of the tables (Relational and Association) that are included in
the scripts produced by the Generator.
A primary key
constraint is generated for both types of tables, and named by appending _PK to
the name of the table. For a relational table, the primary key will be the
single _OID column that is the object ID for the class implemented by the
table. For an association table, the primary key will contain the _OID (or _OID
and _RFK for recursive associations) columns for both sides of the association.
Foreign key
constraints are generated for the columns that represent master-detail
relationships in the relational tables. The foreign key constraints are
generated in order to facilitate the automatic implementation of master-detail
relationships in application development tools such as Oracle Forms. The
constraints are disabled, as the UML engine cannot guarantee that it will write
information to the database in a specified sequence that would satisfy the
foreign key constraints. The generated foreign key constraints are named by
appending _FK and a sequential number, starting at 1 for each class, to the
name of the relational table. The first foreign key constraint on a table would
have the suffix _FK1, the second _FK2, and so on.
For association
tables, a foreign key constraint is generated for each of the _OID or _RFK
columns that appear in the primary key. The naming convention for the
constraints is the same as for the relational tables. These foreign key
constraints are disabled as well.
An index is generated
that corresponds to each foreign key constraint that is generated. These
indexes will improve the performance of queries that are run against the
various types of views that are generated against the Relational and
association tables. The index has the same name as the foreign key that it is
based on.