cpl@bhpmrl.oz.au (Paul Lloyd) (02/01/91)
Some time back (9 Jan, to be exact), I raised a query with the network
regarding how people are using Ingres' Object Management Extension. The
response was not great, but the information I received should be of general
interest. So here is my belated summary:
1. Ingres supplied from their marketing literature the following examples:
Industry Datatype Functions Application
-------- -------- --------- -----------
Defence latitude/ distance() simulations
longitude
Financial time series history() trading
360-day year accrual() bond tracking
Banking/ working days addition() scheduling
General
Manufacturing (X,Y) intersect() CAD/CAM
Research/ vectors, magnitude() modelling
Education matrices inverse
plus: ordered pairs of values, variable length arrays of numbers, & arbitrary
precision rational numbers stored as 2 bitnums (arbitrary precision
integers) & interpreted as the ratio of the 1st over the 2nd (never
overflows, underflows, or loses precision).
2. M. Hunt of the Public Broadcasting Service (PBS), near Washington DC
describes:
(a) A code for identifying each programme which is braodcast, comprising:
Root - a 6 char code identifying the programme series
Episode - a 6 digit nbr identifying the episode number
Subscript - a 2 char code identifying the programme version.
An example is: 'SESA 000203C ', identifying the closed captioned
version of the 203rd episode of 'Sesame Street'. This UDT was
previously stored as 3 columns in a table. The single UDT is easier to
maintain than 3 columns, & each component is a standard Ingres datatype
making for an easy low-level domain check. Performance improvements
seen since the back-end/server handles much of the data checking.
Reports that SQL predicates, LIKE/NOT LIKE cannot be used with UDTs at
present, and are limited with wild card usage. Has not had time to
develop functions to emulate the LIKE predicate. These limitations have
stopped development of another code involving the series title (details
not supplied, apart from a 256 char component).
(b) Storage of time of day as the number of seconds (integer datatype) since
PBS works on a 30-hour day. Since humans still need to work in a
hh:mm:ss format, UDFs were developed to handle the conversions between
the different datatypes. These functions can be explicitly quoted in an
SQL query.
3. D. Manatt of Lawrence Livermore Natl Lab quotes Stonebraker's Bond traders
example:
Dates & times with 12 30-day months totalling 364 days in a year (I hope
I have that right!!).
He goes on with the need to isolate the components of date and time
values, and being able to determine time intervals both forward &
backward. (I note that SQL-2 has various new operators for date & time
including an INTERVAL - cf. Programming & Design, Nov. 1990).
4. J. Rosen of Lotus Computing Corp. quotes Stonebraker's address @ dbExpo 90:
An application involving a database as a backend for a 3-D graphics
product. For a building database, you might want to store data on the
different objects within that building (furniture items, walls, windows,
etc.) so that the graphics frontend can allow you to "walk through" the
building & make modifications to any object as you go. Stonebraker
demonstrated the power of UDTs as compared with normal table column
storage of the data for 2-D objects (for simplification). Rosen gives
an example for finding the objects visible within a defined viewport
(the graphical frontend) using both data storage methods. I can provide
a copy of Rosen's article to any requestor.
Rosen sees quite a lot of potential use for UDTs and their associated
UDFs in the general commercial area. He gives an example of an Address
datatype which he considers would provide more consistency between
applications, and the provision of UDFs for formatting, searching, etc.
should make end-users more productive & ease the load on application
programmers (since both the UDTs & the UDFs are bound to the kernel,
they are available across all applications).
5. D. Hansen quotes an R&D Lab who were investigating a Graphical Information
System using Ingres' OME:
He/they were investigating the definition of latitude-longitude &
cartesian coordinate UDTs, and extending the query language to
understand concepts such as distance between objects.
6. T. Sweeney of Ingres quotes a paper presented by Ian Howells on a current
application used by the UK Atomic Energy Authority - entitled:
"Getting the Most out of User Defined Data Types"
The application required floating point data to be stored in 3-D arrays.
The article Sweeney supplied contained only the first few pages of the
paper which illustrate the economies in defining the schema using a pre-
defined array UDT. The full paper covers the following areas:
(i) storage & access of UDTs
(ii) use of functions on UDTs
(iii) SQL & UDTs
(iv) code development with UDTs
(v) code maintenance with UDTs
(vi) network traffic with & without UDTs
(vii) benchmarking with UDTs
(viii) using UDTs & DBMS facilities.
Please contact Tony Sweeney direct at sweeny%ingres.com@munnari.oz for
details on how to obtain a copy of the paper.
The above is a fairly limited range of uses, but it is fairly early days for
this innovation and extension to the relational data model. I would expect to
see quite a list developing as the OME becomes more familiar to developers.
A couple of points come out of the survey, though:
(i) a move from individual column storage for raw data where the
object is often seen by the user/developer as the composite
of these data elements,
(ii) a means of improving performance by letting the back-end
(DBMS) handle the complex object,
(iii) a means of improving consistency between applications and
application developer productivity where a datatype is
shared,
(iv) a means of improving end-user intelligibility of the data
he/she is using.
I wish to thank all those who have contributed their information and
experience.
Paul.
--
/\/\ Paul Lloyd, Superintendent Computer Systems
/ / /\ BHP Research - Melbourne Laboratories
/ / / \ 245-273 Wellington Rd Mulgrave Vic 3170 AUSTRALIA
/ / / /\ \ Phone : +61-3-560-7066 ext:7375
\ \/ / / / Fax : +61-3-561-6709
\ / / / ACSnet : cpl@bhpmrl.oz.au
\/\/\/ Internet: cpl%bhpmrl.oz.au@uunet.uu.net