Defining Database-Relations and Referential Integrity in the SmartFramework
The SmartFramework allows developers to define relations between database tables and referential integrity based on those relations. The referential integrity is then automatically validated by the SmartDataAccess class.
Relations are also the foundation for the features of the Consultingwerk.SmartFramework.IRelationService service.
In order to use these features the Business Entities and Data Access classes that should leverage the referential integrity validation need to inherit directly or indirectly form the SmartBusinessEntity and SmartDataAccess classes.
Importing Database Tables into the SmartDB
The referential integrity is based on relations defined in the SmartDB’s extended meta-schema. In order to define relations the database tables need to be imported into the extended meta-schema first using the “Database Table Maintenance” program of the SmartFramework Maintenance.
Using the "Import Tables" button in the Ribbon
You can import the missing tables of any connected database:
After confirming the dialog the tables of the selected database will appear in the grid view of the “SmartTable Maintenance” form.
Defining Relations between Database Tables
Using the Relation Maintenance screen you can now define relations between database tables.
The following table describes the fields of the relation definition:
Field Name | Description |
---|---|
Default Parent Relation: | Logical flag indicating if this relation is the default parent relation (e.g. GetParentDescriptionOf method in the Consultingwerk.SmartFramework.IRelationService service) |
Description: | A description for the relation |
Parent Table: | The name of the parent table (lookup on the imported database tables) |
Parent Table Cardinality: | Cardinality of the parent table |
Parent Role Description: | Label of the parent record relation from the child record (e.g. “Order is header of OrderLine”) |
Child Table: | The name of the child table (lookup on the imported database tables) |
Child Table Cardinality: | Cardinality of the child table |
Child Role Description: | Label of the child record relation from the parent record (e.g. “Orderline belongs to Order) |
Relation Field Pair(s): | The comma delimited list of field pairs that manifest the relation (equality matching fields) |
On Parent Delete: | Referential Integrity Rule for deleting the Parent Record |
Inactive: | Allows to disable the relation without deleting it completely. When the relation is disabled it will not be used for referential integrity validation or other related functions |
Note
After modifying the relation definition it's required to restart all AppServer agents/sessions or self-service clients as the relations are cached at runtime.
On Parent Delete rules
Do nothing | No action is performed when the parent is deleted |
Cascade | Automatically delete child records of a parent record that is deleted |
Nullify | When the parent is deleted, assign the childs relation fields to ? |
Restrict | When the parent is deleted and at least one child record exists, reject the deletion of the parent |
The Parent Delete actions are cascaded from Parent to child to grant child …
Assuming the following relations:
Parent Table | Child Table | On Parent Delete |
Customer | Order | Cascade |
Order | OrderLine | Restrict |
When a customer is deleted all Orders will be deleted. But when one of the Orders has a related OrderLine record the whole transaction is rejected.
Distributing Relation Definitions
The extended meta-schema is stored in the following database tables:
- SmartTable
- SmartRelation
Records can be dumped and loaded using the Data Administration tool.
However the recommended method for distributing the records is to use the Business Entities
- Consultingwerk.SmartFramework.System.TableBusinessEntity
- Consultingwerk.SmartFramework.System.RelationBusinessEntity
and the GenericDataExporter or GenericDataImported as demonstrated in the
- Consultingwerk/SmartFramework/Tools/Export/release-data.p
- Consultingwerk/SmartFramework/Tools/Import/import-data.p
Procedures. The GenericDataExporter and GenericDataImporter is capable of updating existing records as well (where the Progress Data Administration tool would fail to update existing records).
The release-data.p and import-data.p procedures can be used as they are from ANT scripts, following the fragment shown here:
Macro Definition to dump data
<!-- Dump SmartDb Data --> <macrodef name="dumpBusinessEntityData"> <attribute name="BusinessEntity" /> <attribute name="Tables" /> <attribute name="QueryString" /> <attribute name="FileName" /> <sequential> <PCTRun procedure="Consultingwerk/SmartFramework/Tools/Export/release-data.p" graphicalMode="false" dlcHome="${progress.DLC}" cpinternal="iso8859-1" cpstream="iso8859-1" inputchars="16000" iniFile="ini/progress.ini" token="4000"> <propath> <pathelement path="." /> <pathelement path="OERA" /> <pathelement path="src" /> </propath> <DBConnection dbName="SmartDB" dbDir="../DB" singleUser="true"> <PCTAlias name="dictdb" /> </DBConnection> <Parameter name="BusinessEntity" value="@{BusinessEntity}" /> <Parameter name="Tables" value="@{Tables}" /> <Parameter name="QueryString" value="@{QueryString}" /> <Parameter name="FileName" value="@{FileName}" /> </PCTRun> </sequential> </macrodef>
ANT tasks to Dump the Tables and Relations
<dumpBusinessEntityData BusinessEntity="Consultingwerk.SmartFramework.System.TableBusinessEntity" Tables="*" QueryString="FOR EACH eSmartTable" FileName="Consultingwerk/SmartFramework/Data/smarttable.xml" /> <dumpBusinessEntityData BusinessEntity="Consultingwerk.SmartFramework.System.RelationBusinessEntity" Tables="*" QueryString="FOR EACH eSmartRelation" FileName="Consultingwerk/SmartFramework/Data/smartrelation.xml" />
Macro definition to import data
(from the SmartDB upgrade and migration script)
<!-- Import SmartDb Data --> <macrodef name="importBusinessEntityData"> <attribute name="BusinessEntity" /> <attribute name="FileName" /> <attribute name="SkipFields" /> <sequential> <PCTRun procedure="Consultingwerk/SmartFramework/Tools/Import/import-data.p" graphicalMode="false" dlcHome="${dlc}" cpinternal="iso8859-1" cpstream="iso8859-1" inputchars="16000" token="4000" stackSize="200" msgBufferSize="${Mm}" > <propath> <pathelement path="${installroot}" /> <pathelement path="${installroot}/src" /> </propath> <DBConnection dbName="${smartdb}" hostName="${smartdbhost}" dbPort="${smartdbport}" /> <Parameter name="BusinessEntity" value="@{BusinessEntity}" /> <Parameter name="FileName" value="@{FileName}" /> <Parameter name="SkipFields" value="@{SkipFields}" /> </PCTRun> <echo/> </sequential> </macrodef>
ANT tasks to Import the Tables and Relations
<importBusinessEntityData BusinessEntity="Consultingwerk.SmartFramework.System.TableBusinessEntity" FileName="Consultingwerk/SmartFramework/Data/smarttable.xml" SkipFields="" /> <importBusinessEntityData BusinessEntity="Consultingwerk.SmartFramework.System.RelationBusinessEntity" FileName="Consultingwerk/SmartFramework/Data/smartrelation.xml" SkipFields="" />