Databases Reference
In-Depth Information
create and consume data about a person's name, and that data are visible to the other
version as well.
While editions solve a significant process problem, it does not solve data seman-
tics problems. For instance, there is no guarantee that the forward and reverse
triggers are in any way inverses of one another and are both essentially opaque
program codes to the database system.
SQL Server
ships with a tool called SQL Server Management Studio (SSMS) that
serves as the GUI front end for a database. The tool can present a database diagram
for a given database; the developer can then make changes directly to that diagram,
such as adding foreign keys or dropping columns, and the changes are then propa-
gated to the database when the diagram is saved. SSMS also has a
generate change
script
feature. While editing a table in a designer, SSMS will track the changes that
the developer has made on the table. SSMS packages those changes into a script
either on demand or whenever the designer is saved or closed.
SQL Server also includes a feature called
Data-Tier Applications
(Microsoft
SQL Server 2008 R2 Data-Tier Applications 2010). At the core of the feature is a
distributable file called a DAC pack. A DAC pack is essentially a deployable image
of a single version of an application database schema. A typical use case is that a
developer packages an application with the schema of a database within such a DAC
pack. Initially, when a customer installs the DAC pack an empty database is created
with the respective table definitions, views, indexes, etc., from the schema. When
the developer creates a new version of the application with an evolved version of the
database schema, it is again bundled in a DAC (the old schema version is not con-
sidered). When the customer installs the new DAC, the existing database is detected
and evolved (upgraded) to the new schema version. The current SQL server version
does not do any sophisticated schema-matching heuristics, but also does not make
any guesses. If a table has the same name in both the before and after versions, and
has columns that are named the same, the upgrade will attempt to transfer the data
from the old to the new, failing with a rollback if there are errors like incompat-
ible data types. The resulting evolution process is effectively able to add or drop
any objects - tables, columns, indexes, constraints, etc. - but unable to perform any
action that requires user intent to capture semantics, such as object renaming (which
to an automated process appears like a drop followed by an add). What the approach
thus supports are common evolution scenarios of schema element adds and drops for
which instance data can be migrated without either developer or user intervention.
IBM DB2
includes a tool called Optim Data Studio Administrator, which is a
workbench tool for displaying, creating, and editing database objects with a live
connection to a database (
IBM 2009a
). The interface has a largely hierarchical lay-
out, with databases at the top of the hierarchy moving down to tables and displayed
column properties. One can use the tool to manually edit schema objects and commit
them to the database. Data Studio Administrator can batch changes together into a
script that can subsequently be deployed independently. The script can be statically
checked to determine whether the operation can be performed without error. For
instance, when changing a column's data type, the default operation is to unload the
data from the column's table, make the change to the type, and then reload the data.
