Dynamic Database Switching

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.

1. IFDEF ghoDbSwitch
2. ELSE

  Global_Variable Handle ghoDbSwitch

1. 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
     //Set psCurrentDatabase to sDB
  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