cpl@bhpmrl.oz.au (Paul Lloyd) (01/03/91)
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? Its aim is to extend the available data types beyond the standard ones
supported by SQL, and to allow the database developer to define operators
appropriate to that data type which can be used directly in an SQL query (this
is also extended to redefining standard SQL operators for that data type). It
is considerably more than SybaseUs data types which are more a way of
specifying domains (eg. telephone numbers, product code numbers, etc.), and are
therefore more data classes with associated edit masks.
It seems to me that this has distinct potential in technological areas, but
my imagination is limited, and technology is such a vast area. I guess I am
looking for some generally applicable ideas which I can sell to my clients who
look on databases as something for the accountants, or as something on which
to conduct subject and literature searches. Is the Object Management Extension
an idea whose time is yet to come? I havenUt come across any articles showing
how it can be used, yet.
Your help will be greatly appreciated.
--
/\/\ 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.netjfr@locus.com (Jon Rosen) (01/04/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? Its aim is to extend the available data types beyond the standard ones >supported by SQL, and to allow the database developer to define operators >appropriate to that data type which can be used directly in an SQL query (this >is also extended to redefining standard SQL operators for that data type). It >is considerably more than SybaseUs data types which are more a way of >specifying domains (eg. telephone numbers, product code numbers, etc.), and are >therefore more data classes with associated edit masks. > >It seems to me that this has distinct potential in technological areas, but >my imagination is limited, and technology is such a vast area. I guess I am >looking for some generally applicable ideas which I can sell to my clients who >look on databases as something for the accountants, or as something on which >to conduct subject and literature searches. Is the Object Management Extension >an idea whose time is yet to come? I havenUt come across any articles showing >how it can be used, yet. > >Your help will be greatly appreciated. >-- The object extensions to Ingres were based on the Postgres studies done at Berkley. Micheal Stonebraker, who was a founder of Ingres Corp (formerly RTI, now Ask Systems), was a principal in the research. I heard Stonebraker talk at the dbExpo last year in San Francisco and his discussion of object extensions to SQL intrigued me very much. He contents that SQL has won as the data access language of choice and that future object-orientation in data bases will be through SQL extensions or new ANSI standards for SQL which support the three major aspects of objects for data bases: user-defined data types, user-defined operators and user-defined accelerators. Inheritance comes with user-defined data types. The example he gave was excellent. A typical real-world application might involve building a data base as a backend for a 3d graphics product. You might want to store all of the different things in a building, for instance, such as tables, chairs, doors, windows, etc., so that you could use your graphics frontend product to "walk" through the building. At each stage of the viewing process, the product needs to find all objects within your viewport and create the 3d images from the data base. To simplify the request (and to show how inadequate current SQL is at this type of problem), he assumed that we would only deal with 2 dimensions and further, that we were only interested in boxes (not complex 3d objects like tables). A table to hold a set of rectangular boxes would look like this in normal SQL: Create Table BoxTable ( BoxId Integer, X1 Float, Y1 Float, X2 Float, Y2 Float) where (x1,y1) and (x2,y2) were the upper left and lower right coordinates of the box. To answer the query "Find all boxes that are within the viewport bounded by (0,0)-(1,1)" you need to give the following SQL: Select BoxId Where X1 > 0 And X1 < 1 And Y1 > 0 And Y1 < 1 Or X2 > 0 And X2 < 1 And Y2 > 0 And Y2 < 1 Or ... You can see where this is going... And this is a simple 2d box query... Imagine trying to do this with a 3d complex object... What we really need is a data representation for a box... Create Table BoxTable ( BoxId Integer, Box BoxType) Now, the internal representation of a BoxType object is defined somewhere else... It maybe the two vertices or maybe it is something else (origin, height and length, etc.) Now what we want to say is: Select BoxId Where Intersect(Box,BoxType(0,0,1,1)) = True or some similar syntax... BoxType(...) is used as a constructor to create a new temporary object of type box from a set of data values and Intersect is a Box-related operator which returns whether Box and BoxType intersect (True or False)... Of course, this might just be a comparison similar to the one we did manually, but at least the semantics have been hidden... better, we would have a different type of indexing scheme, such as an R-tree, which would be a box-related accelerator that would be used by the relational data base and would allow much faster comparisons and searches using BoxType objects. Anyway, this is a quicky intro on object extensions... I see a lot of commercial uses... For one, simple types of objects that are used a lot can be defined once and used in lots of tables without taking up programmer time to define (let alone keep in sync)... If I was building a commercial application, for instance, I might define an Address type which consisted of StreetAddr, City, State, Zip and use the Address type in all of my tables... This is more consistent and easier to reuse... Plus, if I decide to build some special functions or algorithms for searching, formatting, or ???, the addresses, I can create user-defined operators and let everyone use them.; Hope this long-winded discussion has been of SOME benefit. Jon Rosen
mhunt@pbs.org (01/04/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.