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 in different databases)

• 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.

Technique using a MySQL Database

This article will look at a technique for handling this using MySQL and the Mertech database driver for that database. The techniques for other database servers may be added later.

The Database Switching Object

The first thing that we need is a global object (global, at least partly because database connections and DataFlex file buffers are also global) to handle the actual database switching. This is probably best created in a package containing both the class definition and an object instance of that class - thus only the first "Use" statement in any program will actually include it and cause the object to be created.

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 - this is the only fully public message in the class:
   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 (semi-private):
   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, either 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 in 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  // The Data Access DD class

// We want to 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 class will work in
// all cases:
Register_Procedure AddCustomerTable Integer iTab

// Our sub-class of the standard DataDictionary class, on which we will base all
// our table data disctionaries:
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, call
      // the procedure passing it the file number:
      If (pbCustomerTable(Self) and ghoDbSwitch) ;
         Send AddCustomerTable of ghoDbSwitch (Main_File(Self))
   End_Procedure  // End_Construct_Object
   
End_Class  // cMyDataDictionary

In the Visual DataFlex Studio you should then set this sub-class to be the super-class for all of your data dictionaries: Tools -> Configure Workspace -> Class Preferences tab -> DataDictionary and put cMyDatadictionary (or whatever you are calling yours) and cMyDataDictionary.pkg in the two columns.

Data Dictionaries

In the data dictionary class file for each of your tables, you should ensure that these are based on your data dictionary sub-class, then, if the table to be marked as a "customer" table, set that property in the Construct_Object procedure:

Use  cMyDataDictionary.pkg          // DataDictionary Class Definition

Open Customer

Class Customer_DataDictionary  is a cMyDataDictionary
 
    Procedure Construct_Object
       Forward Send Construct_Object
       
       Set pbCustomerTable to True
    End_Procedure  // Construct_Object

End_Class  // Customer_DataDictionary

Without Data Dictionaries

If you need to have the database switching capability for tables you are opening directly, instead of using data dictionaries for them, you can just use the same call that was placed in the data dictionary sub-class, but using the table's File_Number rather than the Main_File property:

   If ghoDbSwitch Send AddCustomerTable of ghoDbSwitch Customer.File_Number

Swiching Database

Finally, having set up all the required infrastructure, actually invoking the database change becomes very simple. Each code module which uses it should implement the conditional declaration and initialisation of the global handle and register the SwitchDB procedure to ensure it will not cause compile-time or run-time errors if the Database Switcher is not present:

#IFDEF ghoDbSwitch
#ELSE
   Global_Variable Handle ghoDbSwitch
   Move 0 to ghoDbSwitch  // Initalise to zero
#ENDIF

Register_Procedure SwitchDB String sDatabase

(Possibly this too might be placed in a package file of its own, including registering the AddCustomerTable procedure as well, since effectively it has already been used three times.)

Then switching database becomes a simple matter of invoking that procedure:

      If ghoDbSwitch Send SwitchDB sDatabase

This might be invoked in the login module of some applications, where the database to use is determined by the user's identity, or in the Session Manager module of an Ajax Web Application where the database to use is mainteined in the Session record, or simply in the change of a visual control (such as a Combo Form) in an application in which the user can choose between many databases to work on.

In the .INT files

The Mertech Set_Database_Name command, on which the underlying functionality is based, will only be effective if the database is not set in the .INT file - so you should remove the line DATABASE_AREA_NAME=XXXX from the appropriate .INT files (and indeed probably from all the .INT files). Then the .TD files should be deleted so that they will be freshly regenerated on next access from the .INT files.