Dynamic Database Switching

For some kinds of system - typically those which provide the same service to a number of different customer organisations - there may be a need to maintain multiple versions of some of the database tables, held within the same database server instance.

A typical example might be where the same database was to hold data for a number of customers, about their customers, but some of the tables would ideally be global - shared by all the customers.

For example:

Global Tables (only exist once)

• TaxRates - Tax Bands and Rates
• PostCodes - Valid Postal Codes
• NatHols - National Holidays

Customer Tables (exist many times)

• Customers
• Transactions

Some applications - those used by the customers - will only access one database, perhaps defined by a user's login, while others - those used by administrators perhaps - will need to be able to switch between databases. However certain kinds of application, even as used by the customers, may require the capability to dynamically switch database: web applications (especially if using process pooling) are an example of this.

This article will look at a technique for handling this using MySQL and the Mertech database driver for that database.

Technique using MySQL Database

The Database Switching Object

Use MerTech.inc
 
Define C_InitialDB for "defaultDB"  // This will be used for the tables to be 
                                    // initially opened in and for global tables
 
// We define a global handle for the object which will handle the switching.
// We do this conditionally in case something else (see below) also defines it.
#IFDEF ghoDbSwitch
#ELSE
   Global_Variable Handle ghoDbSwitch
#ENDIF
 
// We use the special Mertech command "Set_Database_Name" to establish the default
// database as the one to initially open tables in:
Set_Database_Name to C_InitialDB
 
// Define the class we are going to make an object of.  This *could* be done
// directly in the object, but this way is "cleaner".
Class cDbSwitcher is a cObject
   
   Procedure Construct_Object
      Forward Send Construct_Object
      
      Property Integer[] paiCustomerTables  // This will store the file numbers of
                                            // the tables to be "switched".
   End_Procedure  // Construct_Object
   
   // Just encapsulates the Mertech GET_DATABASE_NAME command:
   Function psCurrentDatabase Returns String
      String  sDB
      
      Get_Database_Name to sDB
      Function_Return sDB
   End_Function  // psCurrentDatabase
 
   // Opens the "customer" tables
   Procedure OpenCustomerTables
      Integer i
      Integer[] aiTabs
      
      Get paiCustomerTables to aiTabs
      
      For i from 0 to (SizeOfArray(aiTabs) - 1)
         Open aiTabs[i]
      Loop
      
   End_Procedure  // OpenCustomerTables
   
   // Closes the customer tables:
   Procedure CloseCustomerTables
      Integer i
      Integer[] aiTabs
      
      Get paiCustomerTables to aiTabs
      
      For i from 0 to (SizeOfArray(aiTabs) - 1)
         Close aiTabs[i]
      Loop
      
   End_Procedure  // CloseCustomerTables
   
   // Switches database:
   Procedure SwitchDB String sDB
      String sCurr
      
      Get_Database_Name to sCurr  // Note: Mertech docs say not supported on MySQl,
                                  // but seems to work.
      Move (Trim(sDB)) to sDB
      If (sDB = sCurr) Procedure_Return  // Don't do more than we have to.
      
      Send CloseCustomerTables
      Set_Database_Name to sDB
      Send OpenCustomerTables
   End_Procedure
   
   // Add a table to the list of "customer" tables:
   Procedure AddCustomerTable Integer iTable
      Integer i
      Integer[] aiTabs
      
      Get paiCustomerTables to aiTabs
      
      For i from 0 to (SizeOfArray(aiTabs) - 1)
         If (iTable = aiTabs[i]) Function_Return  // Already registered
      Loop
      
      // Not in yet, so add it:
      Move (SizeOfArray(aiTabs)) to i
      Move iTable to aiTabs[i]
      Set paiCustomerTables to aiTabs
   End_Procedure  // AddCustomerTable
   
   Procedure End_Construct_Object
      Forward Send End_Construct_Object
      Move Self to ghoDbSwitch
   End_Procedure  // End_Construct_Object
   
End_Class  // cDbSwitcher
 
// Create an instance of the class:
Object oDbSwitcher is a cDbSwitcher
End_Object  // oDbSwitcher

This object should then be used in each program which will require this functionality, ideally near the top of the source file, before any tables get opened.

DataDictionary Sub-Class

We then need to define which tables will be "customer" tables - that is the ones which will exist as different versions in different databases. To do this it is most convenient to have a subclass of the Data Access DataDictionary class, eith just in the workspace, or in a library workspace if it is to be used in more than one project (placed in the AppSrc directory it either case). In this we will:

• Create a property in Construct_Object
• Augment End_Construct_Object to "register" the table if required.

Use DataDict.pkg

// We want to should ensure that the global handle is defined, so that this
// class will work even if the database switch object is not in use.
#IFDEF ghoDbSwitch
#ELSE
   Global_Variable Handle ghoDbSwitch
   Move 0 to ghoDbSwitch  // Initalise to zero
#ENDIF

// Register the procedure we are (maybe) going to call, so calss will work in
// all cases.
Register_Procedure AddCustomerTable Integer iTab

Class cMyDataDictionary is a DataDictionary
   
   Procedure Construct_Object
      Forward Send Construct_Object
      
      Property Boolean  pbCustomerTable Public False
   End_Procedure  // Construct_Object
   
   Procedure End_Construct_Object
      Forward Send End_Construct_Object

      // IF this is a customer table AND we have the switcher object:
      If (pbCustomerTable(Self) and (ghoDbSwitch <> 0)) ;
         Send AddCustomerTable of ghoDbSwitch (Main_File(Self))
   End_Procedure  // End_Construct_Object
   
End_Class  // cMyDataDictionary