Dynamic Database Switching

From DFWiki

Jump to: navigation, search

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.

Contents

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.

<source lang="vdf"> 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 - this is the only fully public message in the class:
  Procedure SwitchDB String sDB
     String sCurr
     
     Get_Database_Name to sCurr

     Move (Uppercase(Trim(sDB)))   to sDB
     Move (Uppercase(Trim(sCurr))) to sCurr
     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 </source>

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.

<source lang="vdf"> 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:

  1. IFDEF ghoDbSwitch
  2. ELSE
  Global_Variable Handle ghoDbSwitch
  Move 0 to ghoDbSwitch  // Initalise to zero
  1. 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 </source>

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:

<source lang="vdf"> 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 </source>

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:

<source lang="vdf">

  If ghoDbSwitch Send AddCustomerTable of ghoDbSwitch Customer.File_Number

</source>

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:

<source lang="vdf">

  1. IFDEF ghoDbSwitch
  2. ELSE
  Global_Variable Handle ghoDbSwitch
  Move 0 to ghoDbSwitch  // Initalise to zero
  1. ENDIF

Register_Procedure SwitchDB String sDatabase </source>

(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:

<source lang="vdf">

     If ghoDbSwitch Send SwitchDB sDatabase

</source>

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 maintained 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_SPACE_NAME XXXXX" 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.

Personal tools