Databases Reference
In-Depth Information
3.1
Commercial Relational Systems
Relational database systems, both open-source and proprietary, rely on the DDL
statements from SQL (CREATE, DROP, and ALTER) to perform schema evolution,
though the exact dialect may vary from system to system (
Turker 2000
). So, to add
an integer-valued column C to a table T, one uses the following syntax:
ALTER TABLE T ADD COLUMN C int;
Other changes are differently specified in commercial DBMS. For instance, renam-
ing a table in Oracle is performed using the following syntax:
ALTER TABLE foo RENAME TO bar;
SQL Server uses a stored procedure for that particular change:
sp rename 'foo', 'bar', 'TABLE';
Schema evolution primitives in the SQL language and in commercial DBMS are
atomic in nature. Unless there is a proprietary extension to the language, each state-
ment describes a simple change to a schema. For instance, individual tables may be
added or dropped, individual columns may be added or dropped from a table, and
individual constraints may be added or dropped. Additionally, individual properties
of a single object may be changed; so, one can rename a column, table, or constraint;
one can change individual properties of columns, such as their maximum length or
precision; and one can change the data type of a column under the condition that the
conversion of data from the old type to the new type can be done implicitly.
However, one cannot specify more complex, compound tasks such as horizontal
or vertical splitting or merging of tables in commercial DBMS. Such actions may be
accomplished as a sequence of atomic actions - a horizontal split, for instance, may
be represented as creating each of the destination tables, copying rows to their new
tables, and then dropping the old table. Using this piecemeal approach is always
possible; however, it loses the original intent that treats the partition action as a sin-
gle action with its own properties and semantics, including knowing that horizontal
merge is its inverse.
The approach that has been taken by and large by commercial vendors is to
include at most a few small features in the DBMS itself and then provide robust
tooling that operates above the database. One feature that is fairly common across
systems is
transactional DDL
; CREATE, ALTER, and DROP statements can be
bundled inside transactions and undone via a rollback. A consequence of this fea-
ture is that multiple versions of schemas at a time may be maintained for each table
and potentially for rows within the table for concurrent access. Even though multiple
versions of schema may exist internally within the engine, there is still only a single
version available to the application. PostgreSQL, SQL Server, and DB2 all support
this feature; in Oracle, DDL statements implicitly mark a transaction boundary and
run independently.
Commercial systems automatically perform update propagation for the simple
changes they support. Simple actions, such as column addition, deletion, or type
