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