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.
Global Tables (only exist once)
- TaxRates - Tax Bands and Rates
- PostCodes - Valid Postal Codes
- NatHols - National Holidays
Customer Tables (exist many times)
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 MySQL Database
The Database Switching Object
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
Global_Variable Handle ghoDbSwitch
// 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