Introduction and Scope
As a database developer , you may have being fed
up with the never ending customer changes. Actually this is not only relevant to
database developers but for all of the other developers. But for database developers
has to
meet the customer requirements they may need to change the databases. But if
these changes are done by manually you must list out all the changes to be done
and do it perfectly. It is not sound methodical isn't it? So we have to find out
an elegant way of doing it.
What are the changes?
What are the changes that you may need to do for the
databases? They can be creating a new table, dropping an existing the table,
altering an existing table, deleting existing data of a table, inserting some data.
This all the changes need not to be done to only for one database. Their can be
more than one database depending on the customer's needs. For example I have a
done a project which contains configuration data in one database and day to day
transactions are saved in some other database.
How can we do this?
We can simply write a VB application so that it can change
the databases. These changes may not be done at once. There can be changes for
two three times. If we use a simple VB application every time we need to change
application so that it does the necessary changes to the relevant database.
Problem will become more worse if you are having several customers, and they are
asking several changes. Then you will have to keep more than one EXE for this
simple and straight forward task. We, our self have made our life uneasy.
Haven't we?
If we can write an exe so that it reads some other source
which users can change and do the changes it will be handy. We just need to
change the source file rather than the VB application. Text file would do the job
for us. But XML file be the most suitable one. This gives you another advantage
over the VB application. That is XML file size will be just over 2-3 KB which you can
send it through an e-mail.
XML File
Let's first create the XML file.
Figure 1
Figure 1 is the listing of the XML file. we may call it
SCRIPT.XML.
There are are several things in the XML file which I have not
discussed earlier. First of them is the Version. This is for the reference.
Version would be enough to identify the changes that has done to the
database. As I discussed earlier commands are separated for the databases.
Here I have used two databases which are Config and Transaction. As you can see
there is a another XML tag called CopyData. Sometimes you need to store some
information which may to be entered by users. For an example if you want to
store country list in a table (of course you can get the country list from the
windows registry) which you haven't send it to customers earlier. Still you can
use INSERT command and do it. But better way is sending the template data in a MSAccess table and copy those data with the aid of
a VB application.
VB Application
For this VB application I have used Microsoft XML ,v 3.0 and
Microsoft Activex Data Objects (ADO) 2.7 library.
Figure 2
Above function will load the XML and read it's content. From the
XMLNode2.baseName it is identify whether it has to change the database or it has
to copy the data. Depending on that it calls the two functions.
Figure 3
Figure 3 shows how it changes the database which is self
introductionery. TARGET_CONFIG_CONNCETION_STRING, TARGET_TRANS_CONNCETION_STRING
are connection strings which you can build depending on your database.
Figure 4
Figure 4 shows how to copy the template data to the customers
table.
Improvements
This can be improved to keep the updated version in the
customers computer say in windows registry so that when you are running the next
upgrade you can check the existing version. Better if you can keep the
date as well there.
Conclusion
My idea is to introduces a simple way to the database
upgrades with using the power of XML. You may have great ideas than this. Fell
free to share your experience and creative ideas with us and Let's make database
upgrade an normal work from what it looks like a mess!
