Giving up trying to use Oracle for object oriented data management

From: Michael <rbtree1_at_yahoo.com>
Date: Mon, 18 Jun 2001 00:12:49 -0400
Message-ID: <tiqvu6crbvuk9f@news.supernews.com>

Having used Oracle since the the late days of V6. I have found myself at odds with Oracle's direction and price and the core product saddled with more features than I use or need. The memory use has grown dramatically from 8.0.x 8.1.7 for like configurations. It appears more and more that Oracle today is a company driven by marketeers than engineers - built to meet the latest IT yuppy buzzword or that which is in vogue than to strive for technical excellence or cutting edge academically based technology inovation.

While MySql has offered me with a viable alternative to pricey Oracle for most applications, I was absolutely blown away by PostgresSQL. It is by far the best answer I found for a possible object oriented alternative to Oracle. It makes data relationship management much easier than using multiple detail tables.

http://www.postgresql.org (Main site)
http://www.postgresql.org/idocs/index.php?postgres.html(docs)

Other than the rich object oriented / user extensible type support and object inheritance postgresSqL has a number of other

features:

PHP and C/C++ /ODBC support. (do you really want to waste your time with PRO*C?)
"Free" (GPLed) (Do your really want to waste money for additional Oracle licenses for tier-2 and tier-3 projects?)
received the 2000 Linux Journal Editor's Choice Award for Best Database!
received the 'LinuxWorld Editors Choice Award' for The Best Database Management System!
use of regular expressions in query.
Sql constructs for access to object oriented data is not convoluted.
Many Datatypes including ...

Numeric Types
Monetary Type
Character Types

 Date/Time Types
 Date/Time Input
 Date/Time Output

Time Zones
Internals
Boolean Type
Geometric Types
Point
Line Segment
Box
Path
Polygon
Circle
Network Address Data Types
inet
cidr
macaddr
Bit String Types

8. Network addres functions

Operator Description Usage
< Less than inet '192.168.1.5' < inet '192.168.1.6' <= Less than or equal inet '192.168.1.5' <= inet '192.168.1.5' = Equals inet '192.168.1.5' = inet '192.168.1.5' >= Greater or equal inet '192.168.1.5' >= inet '192.168.1.5'
> Greater inet '192.168.1.5' > inet '192.168.1.4'

<>  Not equal inet '192.168.1.5' <> inet '192.168.1.4'
<<  is contained within inet '192.168.1.5' << inet '192.168.1/24'
<<=  is contained within or equals inet '192.168.1/24' <<= inet

'192.168.1/24'
>> contains inet'192.168.1/24' >> inet '192.168.1.5' >>= contains or equals inet '192.168.1/24' >>= inet '192.168.1/24'

All of the operators for inet can be applied to cidr values as well. The operators <<, <<=, >>, >>= test for subnet inclusion: they

consider only the network parts of the two addresses, ignoring any host part, and determine whether one network part is

identical to or a subnet of the other.

broadcast(inet) inet broadcast address for network broadcast('192.168.1.5/24') 192.168.1.255/24 host(inet) text extract IP address as text host('192.168.1.5/24') 192.168.1.5
masklen(inet) integer extract netmask length masklen('192.168.1.5/24') 24 netmask(inet) inet construct netmask for network netmask('192.168.1.5/24') 255.255.255.0
network(inet) cidr extract network part of address network('192.168.1.5/24') 192.168.1.0/24
text(inet) text extract IP address and masklen as text text(inet '192.168.1.5') 192.168.1.5/32
abbrev(inet) text extract abbreviated display as text abbrev(cidr '10.1.0.0/16') 10.1/16

All of the functions for inet can be applied to cidr values as well. The host(), text(), and abbrev() functions are

Interview ..
http://lwn.net/2001/features/Momjian/ (Linux Weekly News interviewed Bruce Momjian)

Books:

PostgreSQL: Introduction and Concepts
by Bruce Momjian
http://www.amazon.com/exec/obidos/ASIN/0201703319/o/qid=992831565/sr=2-1/ref =aps_sr_b_1_1/002-8804306-9429613

PostgreSQL
by Jeff Perkins
http://www.amazon.com/exec/obidos/ASIN/0761524444/o/qid=992831565/sr=2-2/ref =aps_sr_b_1_2/002-8804306-9429613

Postgresql Programmer's Guide
by Thomas Lockhart (Editor)
http://www.amazon.com/exec/obidos/ASIN/0595149170/o/qid=992831565/sr=2-3/ref =aps_sr_b_1_3/002-8804306-9429613

http://www.postgresql.org (Main site)

Postgres allows columns of a table to be defined as variable-length multi-dimensional arrays. Arrays of any built-in type or user-defined type can be created. To illustrate their use, we create this table:

CREATE TABLE sal_emp (

    name text,
    pay_by_quarter integer[],
    schedule text[][]
);

The above query will create a table named sal_emp with a text string (name), a one-dimensional array of type integer (pay_by_quarter), which shall represent the employee's salary by quarter, and a two-dimensional array of text (schedule), which represents the employee's weekly schedule.

Now we do some INSERTs; note that when appending to an array, we enclose the values within braces and separate them by commas. If you know C, this is not unlike the syntax for initializing structures.

INSERT INTO sal_emp

    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {}}');
INSERT INTO sal_emp

    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"talk", "consult"}, {"meeting"}}');

Now, we can run some queries on sal_emp. First, we show how to access a single element of an array at a time. This query retrieves the names of the employees whose pay changed in the second quarter:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name

Carol
(1 row)
Postgres uses the "one-based" numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

This query retrieves the third quarter pay of all employees:

SELECT pay_by_quarter[3] FROM sal_emp;

pay_by_quarter

          10000
          25000

(2 rows)

To search for a value in an array, you must check each value of the array. This can be done by hand (if you know the size of the array):

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

However, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. Although it is not part of the primary PostgreSQL distribution, in the contributions directory, there is an extension to PostgreSQL that defines new functions and operators for iterating over array values. Using this, the above query could be: SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;

To search the entire array (not just specified columns), you could use: SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000; Received on Sun Jun 17 2001 - 23:12:49 CDT