Write Selected Database Tables

The operation of writing to databases in AIMMS is tuned for performance.

In an application with several database table, it is not very efficient to write all database tables. Much more efficient is to skip writing to those tables where data has not changed.

To do so, you can use a combination of the function ReferencedIdentifiers(), the construct DatachangeMonitor, and runtime libraries.

Example of database tables

Here is an example with two database tables. The mechanism works the same for any number of database tables.

 1DeclarationSection Database_table_declarations {
 2   DatabaseTable db_ab {
 3      DataSource: "data\\abc.dsn";
 4      TableName: "TableAB";
 5      Mapping: {
 6            "NamesA" -->i_a,
 7            "NamesB" -->i_b,
 8            "Vals1"  -->p_AB1( i_a, i_b ),
 9            "vals2"  -->p_AB2( i_a, i_b )
10      }
11   }
12   DatabaseTable db_bc {
13      DataSource: "data\\abc.dsn";
14      TableName: "TableBC";
15      Mapping: {
16            "NamesB" -->i_b,
17            "NamesC" -->i_c,
18            "Vals3"  -->p_BC1( i_b, i_c )
19      }
20   }
21}

  • Writing to table db_ab saves the data of i_a, i_b, p_AB1, p_AB2. In other words, when the data of i_a, i_b, p_AB1, or p_AB2 is changed, we want to write to table db_ab.

  • Writing to table db_bc saves the data of i_b, i_c, p_BC1. Again, when the data of i_b, i_c, p_BC1 is changed, we want to write to table db_bc.

This was the original procedure to write the data:

1 Procedure pr_OriginalDatabaseWriteProcedure {
2   Body: {
3            write to table db_ab;
4            write to table db_bc;
5   }
6}

However, we want to change it to something like this (in pseudo code):

 1 Procedure pr_TargetDatabaseWriteProcedure {
 2     Body: {
 3         if a set or parameter referenced in db_ab is changed then
 4             write to table db_ab;
 5         endif ;
 6
 7         if a set or parameter referenced in db_bc is changed then
 8             write to table db_bc;
 9         endif ;
10     }
11 }

To avoid coding errors and maintenance issues from doing this manually, AIMMS has the following facilities:

  • The predeclared function ReferencedIdentifiers() (see sidebar) examines portions of AIMMS code and returns the identifiers referenced.

  • The construct DatachangeMonitor (see sidebar) checks a given set of AIMMS identifiers for changed values.

  • Runtime libraries, that is AIMMS code generated in the model that can be activated in the same session.

    Each database table is monitored separately, so you need a separate monitor for each table. We need runtime libraries because each monitor has a reference to a set, not the value of a set.

By automating the use of ReferencedIdentifiers and DatachangeMonitors we avoid maintenance problems.

Example of runtime library

Code writing runtime libraries are a bit abstract.

Before trying to understand a procedure that creates a runtime library, let’s take an example of code created by such a procedure:

 1 LibraryModule RuntimeLibraryDatachangeMonitorsForDatabaseTablesToBeWritten {
 2     Prefix: rldmfdttbw;
 3     DeclarationSection Datachange_monitor_names_and_sets {
 4         Set MonitorSet_db_ab {
 5             SubsetOf: AllIdentifiers;
 6             Definition: data { s_A, s_B, p_AB1, p_AB2 };
 7         }
 8         Set MonitorSet_db_bc {
 9             SubsetOf: AllIdentifiers;
10             Definition: data { s_B, s_C, p_BC1 };
11         }
12     }
13     Procedure pr_InitDatachangeMonitors {
14         Body: {
15             DataChangeMonitorCreate("DatachangeMonitor_db_ab",MonitorSet_db_ab,1);
16             DataChangeMonitorCreate("DatachangeMonitor_db_bc",MonitorSet_db_bc,1);
17         }
18     }
19     Procedure pr_WriteTablesWhenDataChanged {
20         Body: {
21             if DataChangeMonitorHasChanged("DatachangeMonitor_db_ab") then
22                       write to table db_ab;
23                       p01_dbWritten('db_ab') := 1;
24                       DataChangeMonitorReset( "DatachangeMonitor_db_ab", MonitorSet_db_ab );
25             endif;
26
27             if DataChangeMonitorHasChanged("DatachangeMonitor_db_bc") then
28                       write to table db_bc;
29                       p01_dbWritten('db_bc') := 1;
30                       DataChangeMonitorReset( "DatachangeMonitor_db_bc", MonitorSet_db_bc );
31             endif;
32         }
33     }
34 }

An explanation of the contents for the database table db_ab follows below. In addition, the library shows how the repetition is done for subsequent tables such as db_bc.

  • line 1: The name of the runtime library. Here, unique and descriptive of the purpose in Camel Case.

  • line 2: The prefix. Here, acronym of the runtime library name in lower case.

  • line 6: The sets and parameters referenced in the first database table, constructed using the function ReferencedIdentifiers.

  • line 4-7: A set declaration and definition for the identifiers referenced in the first table.

  • line 15: Create a datachange monitor for table db_ab using the set MonitorSet_db_ab.

  • line 21: Check if data is changed for table db_ab.

  • line 22: Perform the actual write action.

  • line 23: Mark the table as written.

  • line 24: Reset the data change monitor.

Create the runtime library

 1Procedure pr_CreateAndInitializeRuntimeLibraryForTableWriteManagement {
 2   Body: {
 3      ! Initialize writing the library.
 4      sp_runtimePrefix := "rldmfdttbw" ;
 5      if ep_runtimeLib then
 6            me::Delete( ep_runtimeLib );
 7      endif ;
 8         ep_runtimeLib := me::CreateLibrary( "RuntimeLibraryDatachangeMonitorsForDatabaseTablesToBeWritten", sp_runtimePrefix);
 9      ep_runtimeDecl := me::Create("Datachange monitor names and sets",'declaration', ep_runtimeLib);
10
11      ! The set of database tables that are to be managed via data change monitors
12      s_SelectedDatabaseTables := AllDatabaseTables * Database_table_declarations ;
13
14      ! initialize the text for the runtime procedures
15      sp_bodyInitProc := "" ;
16      sp_bodyWriteProc := "" ;
17
18      for i_db do ! for each database table to be handled
19
20            ! Initialization per table.
21            sp_bodyLineWrite := "" ;
22            sp_bodyLineInit := "" ;
23
24            ! Determine the collection of identifiers referenced in the database table.
25            s_singleTon := i_db ;
26            s_RefdIds := ReferencedIdentifiers(
27                  searchIdentSet :  s_singleTon,
28                  searchAttrSet  :  AllAttributeNames,
29                  recursive      :  1)
30                     * ( AllVariables + AllParameters + AllSets );
31            s_ReferencedIdentifiersByDatabaseTables(i_db) := s_RefdIds ;
32
33            ! Create the data change monitor code
34            sp_set := "data " + s_RefdIds ;
35             ep_datachangeMonitorSet(i_db):= me::Create(FormatString("MonitorSet_%e", i_db), 'set', ep_runtimeDecl);
36             me::SetAttribute(ep_datachangeMonitorSet(i_db),'subset of', "AllIdentifiers");
37             me::SetAttribute(ep_datachangeMonitorSet(i_db),'definition', sp_set);
38            sp_datachangeMonitorName( i_db ) := formatString( "DatachangeMonitor_%e", i_db );
39
40            ! To initialize a data change monitor, we'll only have to call DataChangeMonitorCreate.
41            sp_bodyLineInit  := formatString("DataChangeMonitorCreate(\"DatachangeMonitor_%e\",MonitorSet_%e,1);\n",i_db,i_db);
42            sp_bodyInitProc += sp_bodyLineInit ;
43
44            ! To write to a database table, but only when data is changed, we need to
45            ! 1) check DataChangeMonitorHasChanged,
46            ! 2) actually write,
47            ! 3) register the writing, and
48            ! 4) Reset the data change monitor.
49            sp_bodyLineWrite := formatString("if DataChangeMonitorHasChanged(\"DatachangeMonitor_%e\") then\n",i_db);
50            sp_bodyLineWrite += formatString("          write to table %e;\n",i_db); ;
51            sp_bodyLineWrite += formatString("          p01_dbWritten(\'%e\') := 1;\n",i_db); ;
52            sp_bodyLineWrite += formatString("          DataChangeMonitorReset( \"DatachangeMonitor_%e\", MonitorSet_%e );\n",i_db,i_db) ;
53            sp_bodyLineWrite += formatString("endif;\n\n") ;
54            sp_bodyWriteProc += sp_bodyLineWrite ;
55
56      endfor ;
57
58         ep_InitProc := me::Create( "pr_InitDatachangeMonitors", 'procedure', ep_runtimeLib);
59      me::SetAttribute( ep_InitProc, 'body', sp_bodyInitProc);
60
61      ep_WriteProc := me::Create( "pr_WriteTablesWhenDataChanged", 'procedure', ep_runtimeLib);
62      me::SetAttribute( ep_WriteProc, 'body', sp_bodyWriteProc);
63
64      me::Compile(ep_runtimeLib);
65      apply(ep_InitProc);
66   }
67   Set s_Singleton {
68      SubsetOf: AllIdentifiers;
69   }
70   Set s_RefdIds {
71      SubsetOf: AllIdentifiers;
72   }
73}

Notes:

  • sp_bodyLineInit, sp_bodyInitProc we collect the text for the datachange monitor initialization procedure.

  • sp_bodyLineWrite, sp_bodyWriteProc we collect the text for the write procedure, as illustrated in the previous section.

Call to write the database tables

1Procedure pr_SaveModifiedDatabaseTables {
2   Body: {
3      p01_dbWritten(i_db) := 0 ;
4      apply( ep_writeProc);
5   }
6   Comment: "Write the database tables by calling a runtime created procedure.""}

Essentially just an apply statement of the procedure we created above.

Example project

Download the attached project for an example.