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