Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!usc!zaphod.mps.ohio-state.edu!rpi!uupsi!pbs.org!mhunt
From: mhunt@pbs.org
Newsgroups: comp.databases
Subject: Re: Ingres Object Management Extension
Message-ID: <1991Jan4.073618.11220@pbs.org>
Date: 4 Jan 91 12:36:18 GMT
References: <1991Jan3.021150.12498@bhpmrl.oz.au>
Organization: PBS:Public Broadcasting Service, Alexandria, VA
Lines: 91

In article <1991Jan3.021150.12498@bhpmrl.oz.au>, cpl@bhpmrl.oz.au (Paul Lloyd) writes:
> A query to Ingres users, and any others who may have wishful thoughts along
> these lines:
> 
> Could you please tell me what you are using IngresU Object Management 
> Extension for?  

I work for Public Broadcasting Service (PBS) near Washington, D.C. We have 
been using Object Management for a little more than a year.  We have 
developed two user-defined datatypes (UDTs) and several user-defined functions
(UDFs). One of the UDTs that we use is the NOLA_CODE. It is used to identify 
each program record for all of the programs that we air and consists of the 
following components:

	Root  	  - This portion of the UDT identifies the program series 
		    (i.e. 'SESA' is used to identify the series "Sesame 
                    Street").  This component can be up to six characters 
                    long in the range of 'A     ' through 'ZZZZZZ'.

	Episode   - This portion of the UDT is the episode number. This 
		    component can be up to six digits in length in the 
		    range of '000000' through '999999'.

	Subscript - This portion of the UDT is used to identify the program
		    version (i.e. 'C' is used to denote that the program is 
		    Closed Captioned for the hearing impaired, or 'R' is used 
		    to denote programs that have been repackaged, etc...). 
		    This component can be up to two characters in the range 
                    of '  ' through 'ZZ'.

An example of a real NOLA_CODE would be 'SESA  000203C', which can used to 
identify the record describing the closed captioned version of the 203rd 
episode of Sesame Street. Prior to developing the UDT, we used three separate 
columns in the database tables.  As you may know, three keys columns are much 
more difficult to maintain than one.  All validation for this datatype occurs
in a manner similar to standard datatypes (i.e. INTEGER, DATE, ect...), 
because the software for the NOLA_CODE is linked in with the Ingres kernel. 
Anything that can be done in the Ingres backend/server will result in 
increased performance over any frontend/client processes. 

There are a few limitations that we have been able to workaround with this
UDT, however they have prevented us from implementing our other UDT --
PBS_TITLE.  Currently, the LIKE and NOT LIKE SQL predicates can not be used
with a UDT, and you are limited to what you can do with wildcards.  Due to
the static ranges of NOLA_CODEs, we have been able to workaround this problem
by building low key and high key values that can be used with the standard 
operators (i.e. '<', '>', '!=', etc...) in WHERE qualifications.  However, 
the PBS_TITLE UDT can contain up to 256 characters and it's not practical 
to implement it until the LIKE and NOT LIKE predicates are supported.  I 
haven't had the time yet to investigate the addition of operators that 
could be used to emulate LIKE and NOT LIKE. I am hoping that Ingres will 
support them in their next release of the Object Management Extension. 

Before I came to PBS, I was employed with a systems integration firm that
developed Ingres applications.  We were seriously considering the 
development of UDTs for latitudes and longitudes used throughout one of 
the applications.  When I left they were storing them as FLOAT in the database
and called frontend procedures to convert them to '00:00:00' and '000:00:00' 
formats, respectively. I could definately see the usefulness of having UDTs for
these types of attributes.  However, I haven't kept in touch with team 
members of that particular project and I don't know if they went through with 
the development of these datatypes.  

The Object Management Extension also allows you to add UDFs that can be used
in SQL queries like any other standard SQL function.  Of course, these 
functions are not ANSI SQL, however your client may not be overly concerned 
with this issue. We have developed several UDFs and they have proven to be 
very useful and have played a key role in justifying the aquistion of the 
Object Management Extension.  

Since PBS operates on a 30 hour rather than a 24 hour clock, we store all 
of our time related fields in seconds as INTEGER in the database, although we 
need to display them using the 'hh:mm:ss' format.  We do this primarily 
because it is easier to do computations to determine if there are 
scheduling conflicts in the database.  I wrote a UDF to convert the seconds
value to hh:mm:ss (STOT) and another that converts hh:mm:ss to seconds (TTOS). 
Following is an example of how it works in ESQL:

 	Suppose that the user has specified the following values in simple 
	fields on a form:
	
	program_start_time = 09:00:00
	program_end_time   = 09:28:45
	program_black_time = 00:01:15 (Time slice between programs)

	EXEC SQL SELECT 
	    STOT((TTOS(:program_end_time) + TTOS(:program_black_time)) 
                - TTOS(:program_start_time))
		    INTO :event_duration;
		
	event_duration would result in 00:30:00.