WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Introduction and Scope
As a database developer, you may be fed up with never-ending customer changes. Actually, this is not only relevant to database developers but also for all other developers. But, because database developers have to meet customer requirements, they may need to change the databases. If these changes are done manually, you must list all the changes to be done and do it perfectly. It is not a sound method, is it? So, we have to find an elegant way of doing it.
What Are the Changes?
What are the changes that you may need to make for the databases? They can be creating a new table, dropping an existing table, altering an existing table, deleting the existing data of a table, or inserting some data. All the changes need not be done only for one database. There can be more than one database, depending on the customer's needs. For example, I have a done a project that contains configuration data in one database and day-to-day transactions are saved in some other database.
How Can We Do This?
If we can write an EXE so that it reads some other source that users can change, and then do the changes, it will be handy. We just need to change the source file rather than the VB application. A text file would do the job for us. But, an XML file would be the more suitable one. This gives you another advantage over the VB application. That is that the XML file size will be just over 2-3 Kb, which you can send through an e-mail.
First, let's create the XML file.
- <Change Ver="1.20"> - <DBChanges> - <Database Name="Config"> <Command>Drop Table Schdules</Command> <Command>CREATE TABLE Devices ([ID] int ,[Tire] int ,[Outputs] int , [Inputs] int ,[TrendSamplesMax] int , [Arrays] int)</Command> </Database> - <Database Name="Transacation"> <Command>alter table TrendViewCache add ControllerTrend Bit</Command> </Database> </DBChanges> - <CopyData> - <Database Name="Config"> <Table>Devices</Table> </Database> </CopyData> </Change>
Listing 1 is the listing of the XML file. We may call it SCRIPT.XML.
There are are several things that I have not discussed earlier in the XML file. The first of them is the Version. This is for reference. The Version would be enough to identify the changes that have been done to the database. As I discussed earlier, commands are separated for the databases. Here, I have used two databases: Config and Transaction. As you can see, there is another XML tag, called CopyData. Sometimes, you need to store some information that users may need to enter. For an example, if you want to store a country list in a table (of course, you can get the country list from the Windows Registry) that you haven't sent to customers earlier. Still, you can use the INSERT command and do it. But, a better way is to send the template data in a MSAccess table and copy those data with the aid of a VB application.
For this VB application, I have used Microsoft XML, v 3.0 and the Microsoft Activex Data Objects (ADO) 2.7 library.
Private Sub cmdContinue_Click() Dim xmldoc As New MSXML2.DOMDocument30 Dim XMLNode1 As IXMLDOMNode Dim XMLNode2 As IXMLDOMNode Dim XMLNode3 As IXMLDOMNode Dim XMLNode4 As IXMLDOMNode If xmldoc.Load(App.Path & "\Script.XML") Then lblVersion = "Version: " & xmldoc.childNodes(0).Attributes(0).nodeValue For Each XMLNode1 In xmldoc.childNodes For Each XMLNode2 In XMLNode1.childNodes For Each XMLNode3 In XMLNode2.childNodes For Each XMLNode4 In XMLNode3.childNodes Select Case UCase(XMLNode2.baseName) Case "DBCHANGE" call_DbChange UCase(XMLNode3.Attributes.Item(0).nodeValue), XMLNode4.Text Case "COPYDATA" call_CopyData UCase(XMLNode3.Attributes.Item(0).nodeValue), XMLNode4.Text End Select Next Next Next Next End If End Sub
This function will load the XML and read its content. From XMLNode2.baseName, it is identified whether it has to change the database or it has to copy the data. Depending on that, it calls the two functions.
Private Sub call_DbChange(strDbName As String, strCommand As String) Dim rstConn As New ADODB.Connection Select Case strDbName Case "CONFIG" rstConn.ConnectionString = TARGET_CONFIG_CONNCETION_STRING Case "TRANSACTION" rstConn.ConnectionString = TARGET_TRANS_CONNCETION_STRING End Select rstConn.Open rstConn.Execute strCommand rstConn.Close End Sub
Listing 3 shows how it changes the database, which is self explanatory. TARGET_CONFIG_CONNCETION_STRING and TARGET_TRANS_CONNCETION_STRING are connection strings that you can build, depending on your database.
Private Sub call_CopyData(strDbName As String, strTable As String) Dim rstSourceRst As New ADODB.Recordset Dim rstTargetRst As New ADODB.Recordset Dim i As Integer Dim f As Field Select Case strDbName Case "CONFIG" rstSourceRst.Open strTable, SOURCE_TRANS_CONNCETION_STRING, adOpenForwardOnly, adLockReadOnly rstTargetRst.Open strTable, TARGET_TRANS_CONNCETION_STRING, adOpenDynamic, adLockOptimistic Case "TRANSACTION" rstSourceRst.Open strTable, SOURCE_TRANS_CONNCETION_STRING, adOpenForwardOnly, adLockReadOnly rstTargetRst.Open strTable, TARGET_TRANS_CONNCETION_STRING, adOpenDynamic, adLockOptimistic End Select Do While rstSourceRst.EOF = False rstTargetRst.AddNew For i = 0 To rstSourceRst.Fields.Count - 1 rstTargetRst.Fields(i) = rstSourceRst.Fields(i) Next rstTargetRst.Update rstSourceRst.MoveNext Loop rstSourceRst.Close rstTargetRst.Close End Sub
Listing 4 shows how to copy the template data to the customer's table.
This can be improved to keep the updated version in the customer's computer—say, in the Windows Registry—so that when you are running the next upgrade, you can check the existing version. It would be better if you can keep the date there, as well.
My idea introduces a simple way to provide database upgrades using the power of XML. You may have greater ideas than this. Feel free to share your experience and creative ideas with us. Let's make database upgrading a normal work process from what looks like a mess!