Database Type Evolution¶
Database Type Evolution¶
Persistent classes and attributes are defined in Python. The SQL definition in Oracle is derived from the Python 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.
Always make a backup first. If you do not have set up RMAN you can shutdown the database and make an off-line backup. Otherwise log in to the Recovery Manager as follows:
$ORACLE_HOME/bin/rman target sys@aw98
From the RMAN prompt type:
RMAN> backup database plus archivelog;
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: Adding, Removing, Changing. Each operation can be applied to a persistent class or to a persistent attribute. The simplest operation is Adding, the most dangerous one is Removing and the most complicated one is Changing.
The SQL representation of persistent Python class¶
For the following detailed type evolution descriptions it is useful to keep in mind that for each persistent class “Demo” in Python an Oracle TYPE called “Demo$”, an object TABLE called “Demo” and a VIEW called “Demo+” exist.
When adding or changing attributes it is necessary to know who their Pythontype translates into an SQL type. The module astro.database.oraclesupport contains a dictionary called 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 “Demo” has a list
attribute which is defined as p = persistent('', int, []), then
“Demo$p” is a TYPE defined as TABLE OF SMALLINT.
Link attributes in Python are represented in SQL by a REF to the type the attributes links to and link list attributes are represented by a type that is defined as a TABLE OF REF <type-being-referenced>.
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 SQL*Plus the describe command can be used. [1]
The USER_OBJECT_TABLES view contains all the object tables in the users schema. Likewise, USER_VIEWS contains all views and USER_TYPES contains all types. The USER_TYPE_ATTRS view contains all attributes and their definition for all types.
To get the names of all types that contain Demo:
SELECT TYPE_NAME FROM USER_TYPES WHERE TYPE_NAME LIKE '%Demo';
The USER_TYPES views also has a column SUPERTYPE_NAME with the name of the type from which the TYPE_NAME is derived.
Adding a persistent class¶
To add a persistent class import the class in Python as the AWOPER database user that owns the schema. To make the new class visible to other database users the 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.
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 Moving a persistent subclass to a different parent class.
To remove a persistent class “Demo” use the following commands in the specified order:
DROP VIEW "Demo+";
DROP TABLE "Demo";
DROP TYPE "Demo$";
After these elements have been dropped you have to check whether “Demo$” has list attributes which have to be removed. The types for such attributes have to be dropped as well using the DROP TYPE command. The names of the types of these attributes, e.g. for “Demo$”, can be found with:
SELECT TYPE_NAME FROM USER_TYPES WHERE TYPE_NAME LIKE 'Demo$%;'
Adding persistent attributes to a class¶
To add persistent attributes to a class you need to know their name and their type. If attributes x and y are added with:
x = persistent('This is x', int, 3)
y = persistent('This is y', float, 4.2)
then the following command will add these attributes to the type in the database:
ALTER TYPE "Demo$" ADD ATTRIBUTE ("x" SMALLINT, "y" DOUBLE PRECISION)
CASCADE;
Then the attributes of existing objects have to be given their default values:
UPDATE "Demo" SET "x"=3, "y"=4.2;
Removing presistent attributes from a class¶
Removing one or more attributes is perhaps the simplest, but not less hazardous, operation of all. To remove x and y it is sufficient to execute
ALTER TYPE “Demo$” DROP ATTRIBUTE (“x”, “y”) CASCADE;
Be careful to also drop any list types that have been defined in the given type! See also The SQL representation of persistent Python class .
Renaming a persistent attribute¶
To rename a persistent attribute the procedures described in Adding persistent attributes to a class and Removing presistent attributes from a class are combined. In the next example the name of an attribute is changed from x to z:
ALTER TYPE "Demo$" ADD ATTRIBUTE "z" SMALLINT CASCADE;
UPDATE "Demo"SET "z"="x";
COMMIT;
ALTER TYPE "Demo$" DROP ATTRIBUTE "x" CASCADE;
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.
- First add a dummy attribute with the eventual type for the attribute. This is done, like for any other attribute, following the steps in Adding persistent attributes to a class.
- 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.
- After a succesful copy the old attribute can be removed according to the outline given in Removing presistent attributes from a class.
- Before the dummy attribute can be removed, the attribute whose type is changed needs to be added with its final type, as explained in Adding persistent attributes to a class.
- Now the dummy attribute has to be copied to the attribute for which the type has changed. This can be done with a simple UPDATE statement.
- Finally the dummy attribute can be removed using the procedure shown in Removing presistent attributes from a class.
Moving a persistent subclass to a different parent class¶
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.
