If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

---

The original of this article appears at The Esperanto Group. Some slides from “introducing Informix as a general database platform. In HTML format.” are there as well.

Informix Universal Server

Kerry Sainsbury (kerry@kcbbs.gen.nz), 8 March 1999

Ever since Informix “released” Universal Server at the end of 1997, I’ve been keen to get my hands on it. Now that a trial edition has been made available at www.intraware.com, I finally got the opportunity to try it out. This document describes some of the very cool features of “Universal Server” that distinguish it from the regular “Dynamic Server”, yet doesn’t shirk the important task of highlighting some of the things that really *suck* about the product.

This document also works as a tutorial, so if you want to download the product and fire-up dbaccess, feel free!

Row Types

Informix Universal Server (IUS) gives us the concept of ‘row types’. These are approximately the equivalent of ‘classes’ in an object-oriented environment, just without any methods:

create row type person_t ( name varchar(30), birthdate date );

‘row types’ support inheritance. Here’s the definition of a cartoon-character class, which inherits all the properties of the ‘person_t’ type, and adds a ‘popularity’ indicator in the form of an integer:

create row type character_t ( popularity int ) under person_t; 

These ‘row types’ are, like classes, just definitions however. In order to actually store some instances of the classes, we need to create a database table in which to put them. The following table, called ‘character’, stores rows of the ‘character_t’ class:

create table character of type character_t;

Inserting data into this table is done the same way as a regular SQL’92 table:

insert into character ( name, birthdate, popularity )
 values ("Mickey", "02/02/1946", 10 );
insert into character ( name, birthdate, popularity )
 values ("Donald", "03/03/1953", 9 );

Classes (ie: ‘row types’) can include other classes within them. That is, they support both inheritance (’is-a’) and composition (’has-a’) models. The follow example first creates an ‘address’ class, then an ‘employee’ class which includes an address, and finally an employee table to store the employee objects.

create row type address_t ( description varchar(30),
                            city varchar(30) );
create row type employee_t ( startdate date, salary int,
                             address address_t ) under person_t;

create table employee of type employee_t; 

Now when we want to insert a row into the ‘employee’ table, things start getting a little more interesting. The insertion of the ‘address’ portion of the ‘employee’ needs to be explicitly labelled with the ‘row(….)::address_t’ notation:

insert into employee ( name, birthdate, startdate, salary, address )
values ("Joe Bloggs", "7/9/1963", "7/10/1984", 90000,
row("101 Toontown Sq, Disneyland", "Anaheim CA")::address_t );

At this point we encounter the first limitation of IUS — I’m damned if I can figure out how one can insert a row with partial row type information for the address. I seem unable to ignore the ‘city’ portion of the address, for example. You have to specify the full ‘address’ information.

Let’s move on to some cool stuff now, and worry about the implications of the above limitation later.

Next up we create a new sub-class of ‘employee’, especially for managers. Then we create a ‘management’ table, in which to place our ‘management’ objects. The key thing to notice is that we will create the new table ’under’ the employee table - previously we had only created new ‘row types’ under other ‘row types’.

create row type management_t ( stresslevel integer )
  under employee_t;
create table management of type management_t
  under employee; 

Now let’s put a row into the ‘management’ table - that ‘row(…)::address_t’ notation’s still there, remember.

insert into management (stresslevel, name, birthdate,
startdate, salary, address )

values (99, "Walt Disney", "7/9/32", "7/10/45", 12500000,

  row("Rich Dude Hills", "Beverly Hills CA")::address_t ); 

And let’s do a simple SELECT, to see what we’ve got in management.

select * from management; 

Just the ‘Walt Disney’ row is returned, but what if we SELECT from ‘employee’:

select * from employee; 

Cool - Table inheritance! Not only do we see our ‘Joe Bloggs’ row, we also see the ‘Walt Disney’ row we inserted into the ‘management’ table. Very excellent Informix! And just to prove that we can extract just the ‘employee’ data if we need to, we can use the following syntax:

select * from only(employee); { Now just get employees }

One issue with these ‘row types’ is that you need to have a development tool that understands how to interpret these special data types. If you don’t, you’ll need to select the nested row type information columns individually, eg:

select name, birthdate, startdate, salary,
       address.description,
       address.city from employee; 

Updating tables is fairly straight-forward. The only ‘tricky’ part is updating the ‘nested row type’ columns, like address. The following example fixes the address’s city information in the employee table:

update employee
   set address.city = "Auckland"
 where address.city = "Oakland" 

In order to update the entire ‘address_t’ information, you’ll need to use the same ‘row(…::address_t) notation we used in the ‘insert’:

update employee
   set address = row(address.description,
                    'Auckland')::address_t
 where address.city = "Oakland" 

Tables don’t have to be created ‘of type x_t’. You can create ‘regular’ database tables which just use ‘row types’ as column definitions:

create table ad_agencies( name varchar(30),
address address_t, contact person_t ); 

insert into ad_agencies values
   ("Saachi and Saachi",
row("321 Expensive Location", "City")::address_t,
row("Mrs Shipley", "11/04/1942")::person_t ); 

Stuff that sucks

There is no ‘alter row type’ command, in fact, if you have a ‘typed table’ (like the ‘employee’ table which was created ‘…of type employee_t’), the only column adjustment ‘alter table’ can do is ‘drop type’.

‘drop type’ retains all the data in the table, but replaces the ‘row type’ structures with regular database columns. Our ‘employee’ table would change from (startdate date, salary int, address address_t) to (startdate date, salary int, description varchar(30), city varchar(30));

The reason you might want an ‘alter row type’ is if, for example, you decide it would be a good idea to alter ‘person_t’ to include the person’s shoe size. Not unreasonable, huh?

The bad news is that you can’t change the definition of ‘person_t’ without quite a bit of work. You’ll have to alter every table which features ‘person_t’ so that it doesn’t include ‘person_t’, insert a column to hold the new data, and recreate the row-type. For simple tables like ‘character’, this isn’t too difficult:

alter table character drop type;
alter table character add( shoesize integer before popularity ); 

…but then we get to employee and management (and their associated row types)

“alter table employee drop type” doesn’t work, presumably because the ‘management’ table is ‘under’ it. “alter table management drop type” doesn’t work, presumably because it’s ‘under’ employee.

In order to make this work, you would have to:

• Select the data from every table which uses the ‘person_t’ row type, and put it into a temporary tables.
• Alter the temporary tables, placing a new ’shoe_size’ column after the ‘birth_date’ column in each of the tables.
• Drop all the original tables.
• Drop all the person_t row type descendants
• Drop the ‘person_t’ row type.
• Recreate the original tables, using the structure of the new temporary tables.
• Create the new ‘person_t’ row type.
• Recreate all the person_t row type descendants.
• Alter the tables, addings ‘person_t’ row types (and descendants) as appropriate.
  

Just absurd! Even if somehow you managed to keep all the above items straight, I’m sure the referential integrity contraints would drive you nuts anyway!

I’m not sure it would work anyway. What happens if you have a non-typed table, like the ‘ad_agencies’ table, and you attempt to remove the ‘employee_t’ type - you’re going to end up with two ‘name’ columns (one for the name of the agency, and another for the name of the employee). Yuck!

Of course none of this matters if you create databases which never need to be changed :-)

DISTINCT DATA TYPES

IUS adds what it refers to as ‘distinct’ data types. They are an approximation of SQL92 ‘domains’, which act as aliases to built-in datatypes:

create distinct type phone_number_t as char(7);
create table informant(name char(10), phone_number phone_number_t);

This is fine, but doesn’t really go far enough. You can’t specify ‘not null’. You can’t specify a default value. Most importantly, there is no ‘alter distinct type’, so if you decide that phone numbers need to be longer, you’re simply out of luck.

Somewhat assumingly, there is a ‘drop type’ command, which is quite happy to execute, even if there are tables which use the type! Recreating the dropped type won’t let you recover your table either :-(

drop type phone_number_t restrict; 

select * from informant; // bang!!

OPAQUE DATA TYPES

…are custom data types, which the engine knows nothing about. You define:

• A data structure for how the data is stored on disk
• Support functions to determine how to convert between the disk format and the user format
• Secondary access methods that determine how the index on this data type is built, used, and manipulated
• User functions that use the data type
• A row in a system catalog table to register the opaque type in the database
• Methods to control use of operators, aggregates, and casting.

This is all defined via a C-based API. I did not investigate this facility - I couldn’t see myself ever needing it!

SETS

IUS has some simple support for ’sets’, eg:

create table item( item_number integer,
                   description varchar(40),
                   colours set (char(10) not null )); 

insert into item values ( 1234, 'gardening clogs',
                   "set{'red','blue','green'}" ); 

insert into item values ( 5678, 'pruning hosery',
                   "set{'yellow'}" );

What we’ve defined here the fact that the ‘item’ table contains a set called ‘colours’. We then inserted some values into the table. The big thing that’s missing, is that we don’t have the ability to specify what the valid values for the set are — we can insert ‘Tuesday’ into our set of colours, and nothing will complain! There should be (but isn’t) a syntax something like the following:

create set colours_t char(10) of
{'red', 'blue', 'green', 'yellow', 'magenta'}

which would then support:

create table item( item_number integer,
                   description varchar(40),
                   colours colours_t not null);

Enough of my fantasys however. The ’set’ support that does exist lets us do things like list the items which are available in red:

select * from item where 'red' in colours;

… and list the items which have more than one colour available:

select * from item where cardinality ( colours ) > 1;

There are some variations on sets - ‘multisets’ allow a value to be repeated in the set, and ‘lists’ are ordered sets, eg:

create table city_temps( city_name char(30),
                         avg_temps LIST(integer not null));

What’s naff about sets?

When you update a collection type, you must update the entire collection! You can’t update individual items in the collection. Doh!

I can’t see how I can access the elements of the various ’set’ types. There was no syntax I could find that would tell me what colours a given ‘item’ was available in.

I was expecting, at least, some syntax which would return a pseudo-join result set, like this:

item_number description      colours
1234        gardening clogs  red
1234        gardening clogs  blue
1234        gardening clogs  green
5678        pruning hosery   yellow

I was unable to find any syntax to access the ‘ordered’ information in a ‘list’ set. How can I iterate through the list? Find the first, or last element in the list? I figure I must be missing something!

STORED PROCEDURES

IUS stored procedures support method overloading. We’re finally back at something cool! Let’s create a procedure which calculates a 1% bonus for employees.

create procedure bonus( emp employee_t ) returning money;
   return emp.salary * 0.01;
end procedure;

If we run the procedure, we see that both regular employees (Joe Bloggs) and management (Walt Disney) get the same bonus:

select name, salary, bonus(employee) from employee 

Now let’s create another ‘bonus’ procedure, but this once takes a ‘management_t’ row type as an argument, instead of the ‘employee_t’ the previous one did. Notice that we do not have to drop the existing ‘bonus’ procedure:

create procedure bonus( emp management_t ) returning money;
   return emp.salary * 0.5;
end procedure;

Running the same SELECT statement now gives the regular employees a 1% bonus, while management get a 50% bonus:

select name, salary, bonus(employee) from employee

While this is very nice stuff, I have to wonder why the procedures have been created seperately from the row-type information. One of the big things about the OO paradigm is the way data and procedures are drawn together, so I would’ve expected the syntax to be more like:

select name, salary, employee.bonus() from employee

Perhaps it’s a SQL3-ism.

OBJECT IDs - ‘ref’ and ‘deref’

I read Michael Stonebraker’s “Object-Relational DBMSs - Tracking the next great wave’ book when creating this document. In his book he talks about the ability to include a reference to another object in a table, eg:

create row type department_t( name string,
manager ref(management_t) references management,
workers set(ref(employee_t) references employees ) ); 

The above example defines a department, which directly references a row in the management table, and a set of rows in the employee table.

This techique would move much of the need for joins, and be generally rather sexy. In fact Stonebraker says that in order for row types to be useful, an object-relational db must include these ‘ref’ and ‘deref’ functions.

So guess what’s not in IUS?

Summary

The release I downloaded (9.14.TC1 for Windows NT, 17 Apr 1998) has some very interesting technology underneath it, but I was disappointed at its lack of suitability for any ‘real-world’ database - meaning, one in which the schema may need to be altered!

Finally, I need to credit Mary Mudie’s “Informix-Universal Server” chapter in “Informix Unleashed”, which helped provide some of the examples I’ve used in this document.

Kerry Sainsbury

kerry@kcbbs.gen.nz