Welcome to the Database Programmer, the blog for anybody who
wants to learn the practical realities of working
with databases.
There is a new entry every Monday morning, and the
>complete table of contents is here. This week we are
talking about tools again, specifically upgrades.
Structure Change Tools Are Crucial
There are two big things that your toolkit must contain if you
are going to be a successful database programmer. The first,
which we talked about last week, is a "http://database-programmer.blogspot.com/2008/02/framework-and-database.html"
>framework that respects relational concepts. The second,
which we will talk about now, is a strong upgrade tool, something
that can modify your structures for you.
Iterative Development
Requirements change in one way or another in the life of a
project. Two philosophies exist to deal with this fact, which
I tend to think of as the figure-it-all-out-before-we-code-damnit!
school and the lets-make-sure-we-can-adapt-as-we-go school.
If you are a member of the second school, you know that
a table design that seemed correct yesterday will reveal
mistakes and weaknesses today, and will have to be changed.
In this situation the unredeemable code grinder will attempt
to code his way out of the situation, adding
edge-case handlers and special condition routines
to make up for the fact that the tables do not match the
requirements. After a few years of this most programmers except
a handful of those present from day 1 will be afraid to touch
the code, and inevitably the team will declare they have some
huge wonderful great new ideas that require them to start
from scratch.
The database programmer by contrast will first revise
the table design and update the structures before
even considering what changes to make to the code. While the
code grinder is always looking for the ultimate class structure
first, the database programmer wants first to make
sure that the tables match the requirements, always ensuring that
any special-purpose code that follows is built on a strong
foundation.
Therefore I state the iterative argument: the development process
itself will require structure changes. The choice of framework
will heavily influence your ability to perform this task.
The Operational Argument
Consider these four actions, are they more alike
to each other, or more different from each other?
- A programmer spends four hours creating
a new feature start to finish on his own
workstation.
- A testing server is upgraded, merging
3 days' work from 4 programmers.
- A live server is upgraded to the latest
published version of a package.
- A server has a package installed on it
for the first time.
From a manager's perspective these are all very
different operations, involving different kinds of
people, different approval processes and different
kinds of follow-up. A manager would say they are
different.
However, from a technical standpoint they are
all exactly the same operation. All four
operations require the execution of these two steps:
- Modify table structures.
- Deploy new code.
A programmer sitting at a workstation on a new feature will
frequently need to add a column to a table or make one or two
new tables. Then she adds supporting user interface code where
the framework default screens won't do the job. The operation is
change tables + make new code.
When a QA server is updated with the latest efforts of more than
one programmer, the operation is exactly the same: all table
structure changes are made and all new code is deployed. The
operation is change tables + use new code.
When a production server is upgraded it is the exact same
as the QA server, new table structures and new code.
When a package is installed for the first time and there is no
database, it must be created. This is no different than the
previous two examples. Instead of modifying tables it adds
tables. Instead of adding one or two it adds them all. Instead
of overwriting code it puts the code down for the first
time. The operation then is still the same: change tables +
use new code.
Approach For Trivial Cases
If your database structure is modest (less than 10 tables),
if you have one or perhaps
two programmers, and if you do not change the application much,
then you can probably forget about a tool. Most tools and
conventions are meant to coordinate more than one programmer
makes lots of changes. If you don't need that coordination
then you don't need the tool, unless it really gives you
something you like.
The Pros and Cons of Build Scripts
A build script is a program that does one or both of these
two tasks:
- Contains commands that modify table structures
- Contains commands that manipulate data, such as populating
rows in a newly created table or fixing some bug from a
prior release.
Build scripts are a natural first step when your team and your
customer count begins to grow. One of the biggest first wins
is that you can put them into source control. This alone can
be worth going to scripts even if you are just one programmer.
It is also fairly trivial to make a small library of commands
like "AddColumn" that will check to see if the column is already
there and only attempt to add it if necessary.
This makes the scripts safer to rerun, which is always
a good thing.
You can also have programmers create a build script for each
distinct feature they work on, also making it easier to identify
which structure changes are tied to which unit of work.
A few years ago I became programmer #5 on what had been a four
man team. This team used build scripts exactly as I have just
described them. When I left a little over three years later
we were about 20 programmers on two continents and over 50
customers. This growth revealed a few weaknesses in the
build script method.
- Separate build scripts are slow. If you keep separate
scripts for each unit of work, you risk that a major upgrade
will rewrite a large table more than once. When the table is
10 Gigs this is no laughing matter.
- Quality Control is Imposible. Scripts are usually the least
tested part of the system. The programmer runs a script
at most a
handful of times, and usually in unrealistically
ideal circumstances. Then an IT person runs the upgrade during
off hours (Friday night at 8:00pm) and the script crashes and
either you get a phone call and go back to work or the
customer is told the upgrade failed. Yuck.
- Dependencies Become Horrible. There will always be a need
to run the scripts in the order they were written. Even if clever
programming gives you a certain leeway here, you will keep running
into cases where a programmer must write his script on the
assumption that another script has already run. If you have even
3 features tying each other up like this then suddenly we have
3 or 4 programmers stuck in meetings trying to coordinate, and
we all know how fun it is trying to get programmers to
coordinate in a meeting!
I do not mean to say that you should not use scripts. All of the
hazards mentioned above can be acceptable if the scripts give
you enough productivity to operate profitably. But if you want
maximum upgrade performance and quality, you will sooner or later
have to go to meta-data and a differences engine.
End of the Line: Meta Data
The only way to solve all three of the problems mentioned above
is to have a single file of some form, whether it be YAML, XML
or even plaintext, which describes the database in its entirety.
This file is placed under source control and is delivered with
other files in the upgrade. We call this file a "meta-data"
file because it contains data about data.
When an upgrade runs, the build tool takes that file of yours
and creates a picture of what the database should be, and then
examines the current structure. Once this is done the tool can
work out which changes need to be made, and execute them. Tools
such as these work for development, upgrades, and new installs,
without needing a separate tool for each case.
A difference engine is the best performing because it does only
the changes required in each situation. It has the highest QA
because programmers are just supplying inputs to the builder
instead of writing new code themselves every time. Finally
it reduced dependencies by the simplest of all approaches: it
just builds what it knows about and is immune from any trap where
one feature requires another.
Conclusion: Pick a Tool for Your Situation
This week I have reviewed three basic approaches to handling
structure changes. The simplist situation may require no special
tool, while a larger but modest situation requires a bit more,
and the final stage requires an intelligent meta-data based
agent. Also I have shown that many operations in the
development cycle are in fact the same operation over and over:
table changes + new code. This means that a good tool for
handling these two operations can be useful for development,
QA, the new install, and the upgrade.
Next week we will return to Table Design Patterns with
"The Primary Key That Wasn't."
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
