# started from awe/astro/docs/man_db_typeevol.tex

\section{Database Type Evolution}
=================================

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Type Evolution begin
\subsection{Database Type Evolution}
\newcommand{\AWOPER}{{\tt AWOPER}}
\newcommand{\classname}{Demo}
\newcommand{\aclass}{{\tt "\classname"}}
\newcommand{\atype}{{\tt "\classname\$"}}
\newcommand{\atable}{{\tt "\classname"}}
\newcommand{\aview}{{\tt "\classname+"}}
Persistent classes and attributes are defined in \py.  The SQL definition in
Oracle is derived from the \py\ definition. This means that whenever a change
is made to a persistent class the corresponding SQL definition has to be
changed accordingly.  This requires manual intervention and details are given
in this section on what do.

\noindent
Always make a backup first. If you do not have set up {\tt RMAN} you can
shutdown the database and make an off-line backup.  Otherwise log in to the
Recovery Manager as follows
%
\begin{flushleft}
\tt
\$ORACLE\_HOME/bin/rman target sys@aw98
\end{flushleft}
%
From the {\tt RMAN} prompt type
%
\begin{flushleft}
\tt
RMAN> backup database plus archivelog;
\end{flushleft}

\subsection{Overview}
There are different categories of database type evolution. Some of these
require a simple SQL statement, but most require close attention.  In general
type evolution requires extreme care, especially when changes are made in a
database that is populated. Only when a backup is available it is possible to
retrieve types or attributes that have been removed.

All database type evolution operations fall in one of three categories:
{\em Adding}, {\em Removing}, {\em Changing}.  Each operation can be applied
to a persistent class or to a persistent attribute.  The simplest operation is
{\em Adding}, the most dangerous one is {\em Removing} and the most
complicated one is {\em Changing}.

\subsection{The SQL representation of persistent \py\ class}
\label{evol:mapping}
For the following detailed type evolution descriptions it is useful to keep in
mind that for each persistent class \aclass\ in \py\ an Oracle {\tt TYPE}
called \atype, an object {\tt TABLE} called \atable\ and a {\tt VIEW}
called \aview\ exist.

When adding or changing attributes it is necessary to know who their \py\
type translates into an SQL type.  The module
{\tt astro.database.oraclesupport} contains a dictionary called
{\tt typemap} for this purpose.

For list attributes an additional type in SQL is created which is a nested
table of the type of the list attribute. If \aclass\ has a list attribute which
is defined as \verb!p = persistent('', int, [])!, then {\tt "\classname\$p"}
is a {\tt TYPE} defined as {\tt TABLE OF SMALLINT}.

Link attributes in \py\ are represented in SQL by a {\tt REF} to the type
the attributes links to and link list attributes are represented by a type that
is defined as a {\tt TABLE OF REF {\it <type-being-referenced>}}.

\subsection{Finding information about the SQL types, tables and views}
There are several system views in the database that can be use to inspect
existing definitions of structures such as types, tables and views.  To find
the definition of a structure in {\tt SQL*Plus} the {\tt describe}
command can be used.\footnote{Use {\tt help describe} from the
{\tt SQL*Plus} prompt}

The {\tt USER\_OBJECT\_TABLES} view contains all the object tables in the
users schema. Likewise, {\tt USER\_VIEWS} contains all views and
{\tt USER\_TYPES} contains all types. The {\tt USER\_TYPE\_ATTRS} view
contains all attributes and their definition for all types.

\noindent
To get the names of all types that contain {\tt \classname}
\begin{flushleft}
\tt
SELECT TYPE\_NAME FROM USER\_TYPES WHERE TYPE\_NAME LIKE '\%\classname';
\end{flushleft}
%
The {\tt USER\_TYPES} views also has a column {\tt SUPERTYPE\_NAME} with
the name of the type from which the {\tt TYPE\_NAME} is derived.

\subsection{Adding a persistent class}
To add a persistent class import the class in \py\ as the \AWOPER\ database
user that owns the schema. To make the new class visible to other database
users the {\tt Toolbox/dbgrants.py} script needs to be run. The script
will run as \AWOPER\ and ask for its password.  Note that no manual SQL is
required.

\subsection{Removing a persistent class}
Check that no classes are derived from the class you are trying to remove.  If
classes are derived from the class or if attributes in other classes refer to
instances of the class you cannot use the following commands to remove the
database type. Instead you'll have to follow the procedure described in
\S\ref{evol:movesubclass}.

\noindent
To remove a persistent class \aclass\ use the following commands in the
specified order.

\begin{flushleft}
\tt
DROP VIEW \aview; \\
DROP TABLE \atable; \\
DROP TYPE \atype; \\
\end{flushleft}
%
After these elements have been dropped you have to check whether \atype\ has
list attributes which have to be removed. The types for such attributes have to
be dropped as well using the {\tt DROP TYPE} command.  The names of the
types of these attributes, e.g. for \atype, can be found with
\begin{flushleft}
\tt
SELECT TYPE\_NAME FROM USER\_TYPES WHERE TYPE\_NAME LIKE '\classname\$\%;'
\end{flushleft}

\subsection{Adding persistent attributes to a class}
\label{evol:addattribute}
To add persistent attributes to a class you need to know their name and their
type. If attributes {\tt x} and {\tt y} are added with
%
\begin{flushleft}
\tt
x = persistent('This is x', int, 3) \\
y = persistent('This is y', float, 4.2) \\
\end{flushleft}
%
then the following command will add these attributes to the type in the
database.
%
\begin{flushleft}
\tt
ALTER TYPE \atype\ ADD ATTRIBUTE ("x" SMALLINT, "y" DOUBLE PRECISION) CASCADE; \\
\end{flushleft}
%
Then the attributes of existing objects have to be given their default values.
%
\begin{flushleft}
\tt
UPDATE \atable\ SET "x"=3, "y"=4.2; \\
\end{flushleft}

\subsection{Removing presistent attributes from a class}
\label{evol:dropattribute}
Removing one or more attributes is perhaps the simplest, but not less
hazardous, operation of all. To remove {\tt x} and {\tt y} it is
sufficient to execute
\begin{flushleft}
\tt
ALTER TYPE \atype\ DROP ATTRIBUTE ("x", "y") CASCADE;
\end{flushleft}
%
{\em Be careful to also drop any list types that have been defined in the
given type! See also \S\ref{evol:mapping}}.

\subsection{Renaming a persistent attribute}
To rename a persistent attribute the procedures described in
\S\ref{evol:addattribute} and \S\ref{evol:dropattribute} are combined.  In the
next example the name of an attribute is changed from {\tt x} to {\tt z}

\begin{flushleft}
\tt
ALTER TYPE \atype\ ADD ATTRIBUTE "z" SMALLINT CASCADE; \\
UPDATE \atable SET "z"="x"; \\
COMMIT; \\
ALTER TYPE \atype\ DROP ATTRIBUTE "x" CASCADE; \\
\end{flushleft}

\subsection{Changing the type of a persistent attribute}
Changing the type of a persistent attribute is done in different ways for
different types. The basic procedure is however always the same.

\begin{itemize}
\item First add a dummy attribute with the eventual type for the attribute.
This is done, like for any other attribute, following the steps in
\S\ref{evol:addattribute}.

\item The next thing to do is to copy the value old attribute to the new
attribute while converting it to the new type. Depending on the types that are
involved, the conversion can be simple or complicated. The guideline is the
purpose of the typechange and the person requesting the type change will know
best what this purpose is.

\item After a succesful copy the old attribute can be removed according to the
outline given in \S\ref{evol:dropattribute}.

\item Before the dummy attribute can be removed, the attribute whose type is
changed needs to be added with its final type, as explained in
\S\ref{evol:addattribute}.

\item Now the dummy attribute has to be copied to the attribute for which the
type has changed. This can be done with a simple {\tt UPDATE} statement.

\item Finally the dummy attribute can be removed using the procedure shown in
\S\ref{evol:dropattribute}.
\end{itemize}

\subsection{Moving a persistent subclass to a different parent class}
\label{evol:movesubclass}
When a persistent subclass needs to be moved to a different place in the class
hierarchy a combination of many of the previously called techniques is needed.

%FIXME

\subsection{Error messages}
{\tt ORA-22337: the type of accessed object has been evolved} Stop the
current SQL session and start a new one.

% Type Evolution end