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 NameDescription

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="" />