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

section{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 pydefinition. 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 pyclass} label{evol:mapping} For the following detailed type evolution descriptions it is useful to keep in mind that for each persistent class aclassin pyan Oracle {tt TYPE} called atype, an object {tt TABLE} called atableand a {tt VIEW} called aviewexist.

When adding or changing attributes it is necessary to know who their pytype 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 aclasshas 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 pyare 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 pyas the AWOPERdatabase 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 AWOPERand 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 Sref{evol:movesubclass}.

noindent To remove a persistent class aclassuse 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 atypehas 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 atypeADD 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 atableSET “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 atypeDROP 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 Sref{evol:mapping}}.

subsection{Renaming a persistent attribute} To rename a persistent attribute the procedures described in Sref{evol:addattribute} and Sref{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 atypeADD ATTRIBUTE “z” SMALLINT CASCADE; \ UPDATE atable SET “z”=”x”; \ COMMIT; \ ALTER TYPE atypeDROP 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 Sref{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 Sref{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 Sref{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 Sref{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