Automating Schema Management
Part 3 - Automated schema management
What Can Automation Provide?
Automated support for upgrading database schemas can do the following:
- Create/drop/alter objects to force schemas in the working database to
agree with those in the reference database.
This is often called schema synchronization.
- Preserve existing data values in existing objects
(such as tables and sequences).
- Preserve privileges on existing objects when they are re-built.
- Preserve physical attributes of existing objects when
they are re-built.
Automated tools will often require assistance (or at least
sensible defaults) in the following areas:
- Physical attributes on new objects (such as new clusters,
tables and indexes).
- Privileges on new objects (such as new tables, views,
sequences and procedures).
- Data population of new objects (such as tables and sequences).
Automated tools will probably be "confused" (and need
extra directions) under the following circumstances:
- Renames of schema objects (e.g. tables and sequences).
- Renames of table columns.
Why You Need To Be In Control
Why is it important to distinguish objects that have been renamed?
Take an example where a table is renamed from "EMP" to "HR_EMPLOYEE".
Why? Because we have finally graduated from the Oracle® school
of SCOTT/TIGER examples!
In this scenario, if our working database (DB) has not
yet been updated, then a comparison tool that is not CASE
driven will report that table "EMP"
is present in the working DB and should not be there.
Also, it will report that the table "HR_EMPLOYEE" is absent from the
working DB and that it should be present.
By default, an automated upgrade would then assume it
needed to drop the "EMP" table from our working DB
and would assume it needed to create the new
"HR_EMPLOYEE" table in our working DB.
It would not make the connection that the two tables are
actually the same table.
What the automated tool should do, is to rename
the table "EMP" to "HR_EMPLOYEE".
Additionally, it may need to make some other alterations
to this table, possibly even rebuild it.
The bottom line is this: the data in the old EMP table
should be preserved! That is, even if the table is
re-built, the data needs to be copied into the new
table structure.
If we were to blindly use our automated tool then any
data in the "EMP" table would be lost.
Alas, those 14 famous rows would be gone forever!
CASE driven tools may possess the "knowledge" of what has
been renamed but suffer some other problems.
One particular problem is that they must assume that the working
database is comprised of specific [i.e. known] schema versions.
When the target database does not precisely match the expected schema versions,
the upgrade fails.
This problem can be circumvented by reverse engineering the target database
using the CASE tool and then comparing this with the reference model.
However, this means that the case tool will no longer have "knowledge"
of what was renamed.
What's that old saying about a free lunch?
Tools that are not CASE driven will require some input
from the user as to what objects have been renamed.
Typically, this input will follow from a review of the schema
comparison output and recognition that (for example) the
"EMP" table has not been dropped from the model
but has in fact been renamed.
If you are using any form of CASE tool then this info is probably available
in the form of audit reports.
In the above example, if a directive is passed to the compare
tool informing it that the "HR_EMPLOYEE" table is just a rename of
the "EMP" table then the tool would simply upgrade the table (assuming of
course that it has that capability).
In this event, existing row data, physical attributes, privileges etc.
are preserved.
Renames of sequences and table columns can be handled in a similar fashion.
The Silver Bullet
If one believes the marketing blurbs, they [the marketing
people, and they are extremely good at convincing us aren't they?]
would have us
believe that tools exist which allow us to point, click and hey presto, your
database has been upgraded to the latest version [auto-magically]! Well I don't
believe in Santa Clause and lately I stopped believing in magic [although I see
it is making a big comeback on TV and the movies]. Anyhow, if schema upgrades could be fully
automated then we would be able to convert a SAP application database into an
Oracle® Applications database by simply comparing the two and clicking
the DO-MAGIC button! Think of the number of consultants that would put out of
business! The point is this, schema comparison and upgrade tools can
update the database structure but you are going to have to upgrade the data all
by your lonesome! This leads us on to the next topic.
Meta-Data Stored As Data
And what about meta data? Many applications store rules
and application structure as meta data within the application's tables.
Conceptually, this is no different to defining tables, views etc to store the
application's data. The problem is, most [if not all] tools cannot handle
application meta data stored within the application's own tables.
The reason is simply that there is no defined set of rules for how application
meta-data is handled.
This implies that you will just have to be very disciplined and carefully
version your schemas and the associated meta-data.
The topic of meta-data opens the proverbial Pandora's Box.
Applications usually do not let end-users change database
structures but often, quite happily allow them to
"fiddle" the meta-data that defines logical structures.
This is a topic well beyond the scope of this document.
Summary
Schema management tools can provide some of the answers. They will not do so without your help.
Futhermore, they provide little or no assistance for those parts of your application which
store meta-data as data.
Copyright © Impact Systems Pty. Ltd. 2008
|