Composite datatypes, dynamic member fields

От:

Robert Staudinger <r.staudinger@seminarshop.com>

Дата:

10 мая 2002 г. в 08:43:03

Hello, 

I'm planning to build some kind of simple object oriented database and
came across PostgreSQL because of it's nearly unlimited ways of
extensibility.
The documentation is really good an I learned a lot about composite
datatypes, how to program them in c, create indices ... from the "Server
Programming" section of the "Developer Manual".
The problem in my case is that I don't know the names of the attributes
beforehand so I can't do a table bases mapping with one table for each
type of object ("class"). So I thought It would maybe be possible to
store the objects always in the same composed datatype - with "dynamic
member fields".
Ok this may be completely insane but ...

Maybe you could just tell me if it's worth to dig deeper.

Robert Staudinger

Re: Composite datatypes, dynamic member fields

От:

Robert Staudinger <robson@stereolyzer.net>

Дата:

13 мая 2002 г. в 06:24:28

> I use inheritance in such a case.
> (see INHERITS in CREATE TABLE)
> Table with common columns as a base class
> and different tables for classes with
> inherited base class.
> Multiple and multilevel inheritanse possible.
The problem in my case is that even a group of objects which belong 
together might not have even one common attribute.

One idea is to implement a . operator on a basic data type and return
the value 
for the corresponding field from the "operator function".
E.g.
"select * from mytable where mytype.mymember='x'"
could call something like
mytype_member_read( mytype, member_name )
but I'm not sure which datatype member_name would be in this case.

Unfortunately I don't have any idea how to handle updates on such fields
with a user defined member access operator yet.

Does anybody have any advice on that?

Robert Staudinger
PS: Please excuse if this explanation doesn't make perfect sense to you
- it's a little hard for me to say in English what I mean.

Re: Composite datatypes, dynamic member fields

От:

Robert Staudinger <robson@stereolyzer.net>

Дата:

13 мая 2002 г. в 11:38:21

On Mon, 2002-05-13 at 16:17, Tom Lane wrote:
> Robert Staudinger  writes:
> > One idea is to implement a . operator on a basic data type and return
> > the value 
> > for the corresponding field from the "operator function".
> > E.g.
> > "select * from mytable where mytype.mymember='x'"
> > could call something like
> > mytype_member_read( mytype, member_name )
> > but I'm not sure which datatype member_name would be in this case.
> 
> PG has always had the ability to define functions that could be
> notationally treated as fields.  A trivial example:
> 
> test72=# create table tours(depart date, return date);
> CREATE
> test72=# insert into tours values('2002-01-01', '2002-01-10');
> INSERT 525275 1
> test72=# insert into tours values('2001-12-15', '2002-01-05');
> INSERT 525276 1
> test72=# create function numdays(tours) returns int as '
> test72'# select $1.return - $1.depart' language sql;
> CREATE
> test72=# select *, tours.numdays from tours;
>    depart   |   return   | numdays
> ------------+------------+---------
>  2002-01-01 | 2002-01-10 |       9
>  2001-12-15 | 2002-01-05 |      21
> (2 rows)
> 
> The computed field doesn't quite have the same status as real fields
> --- notice that * doesn't know about it in the above example --- but
> it's a useful technique anyway.
> 
> 			regards, tom lane

Hmm I don't know if this solves my problem.
E.g.
I want to store a group of linked objects. Maybe x(ht)ml could be an
example:



one
two
three



these should be stored in a table
( column "object" is of a base type written in c and should hold
arbitrary string attributes)

id parentid object
------------------
1  0        table
2  1        tr
3  2        td1
4  2        td2
5  2        td3

to be able to query something like
"select * from mytable where object.border='1'"
i'd probably need a . operator on "object" if "object" is a base type
implemented in c which holds the attributes internally - just as
described in my previous post.

The problem is that i don't know how to handle
"update mytable set object.margin='2' where parentid='2'"

Please note that I'm just thinking about how OR mapping could be done by
taking advantage of postgresql's extensibility - this is not a real
world problem ;-)

Thanks for sharing
Robert Staudinger

Re: Composite datatypes, dynamic member fields

От:

"."@babolo.ru

Дата:

10 мая 2002 г. в 16:17:13

Robert Staudinger writes:
> I'm planning to build some kind of simple object oriented database and
> came across PostgreSQL because of it's nearly unlimited ways of
> extensibility.
> The documentation is really good an I learned a lot about composite
> datatypes, how to program them in c, create indices ... from the "Server
> Programming" section of the "Developer Manual".
> The problem in my case is that I don't know the names of the attributes
> beforehand so I can't do a table bases mapping with one table for each
> type of object ("class"). So I thought It would maybe be possible to
> store the objects always in the same composed datatype - with "dynamic
> member fields".
I use inheritance in such a case.
(see INHERITS in CREATE TABLE)
Table with common columns as a base class
and different tables for classes with
inherited base class.
Multiple and multilevel inheritanse possible.

> Ok this may be completely insane but ...
> 
> Maybe you could just tell me if it's worth to dig deeper.
> 
> Robert Staudinger
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


-- 
@BABOLO      http://links.ru/

Re: Composite datatypes, dynamic member fields

От:

Robert Staudinger <robert.staudinger@fh-sbg.ac.at>

Дата:

13 мая 2002 г. в 09:13:23


> I use inheritance in such a case.
> (see INHERITS in CREATE TABLE)
> Table with common columns as a base class
> and different tables for classes with
> inherited base class.
> Multiple and multilevel inheritanse possible.
The problem in my case is that even a group of objects which belong 
together might not have even one common attributes.

One idea is to implement a . operator on a basic data type and return the value 
for the corresponding field from the "operator function".
E.g.
"select * from TABLE where mytype.mymember='x'"
could call something like
mytype_member_access( mytype, member_name )

Unfortunately I don't have any idea how to handle updates on fields yet.

Does anybody have any advice on that?

Robert Staudinger
PS: Please excuse if this explanation doesn't make much sense to you - 
it's a little hard for me to say in English what I
mean.

Re: Composite datatypes, dynamic member fields

От:

"Ross J. Reedstrom" <reedstrm@rice.edu>

Дата:

13 мая 2002 г. в 16:36:41

On Mon, May 13, 2002 at 10:17:55AM -0400, Tom Lane wrote:
> 
> The computed field doesn't quite have the same status as real fields
> --- notice that * doesn't know about it in the above example --- but
> it's a useful technique anyway.

That particular shortcoming can be overcome by wrapping the computed
fields in a view:

create view tour_lengths AS select *, tours.numdays from tours;

test=# create view tours_plus as  select *, tours.numdays from tours;
CREATE

test=# select * from tours_plus;  depart   |   return   | numdays 
------------+------------+---------2002-01-01 | 2002-01-10 |       92001-12-15 | 2002-01-05 |      21
(2 rows)


Ross

Re: Composite datatypes, dynamic member fields

От:

"."@babolo.ru

Дата:

13 мая 2002 г. в 19:46:24

Robert Staudinger writes:
> On Mon, 2002-05-13 at 16:17, Tom Lane wrote:
> > Robert Staudinger  writes:
> > > One idea is to implement a . operator on a basic data type and return
> > > the value 
> > > for the corresponding field from the "operator function".
> > > E.g.
> > > "select * from mytable where mytype.mymember='x'"
> > > could call something like
> > > mytype_member_read( mytype, member_name )
> > > but I'm not sure which datatype member_name would be in this case.
> > 
> > PG has always had the ability to define functions that could be
> > notationally treated as fields.  A trivial example:
> > 
> > test72=# create table tours(depart date, return date);
> > CREATE
> > test72=# insert into tours values('2002-01-01', '2002-01-10');
> > INSERT 525275 1
> > test72=# insert into tours values('2001-12-15', '2002-01-05');
> > INSERT 525276 1
> > test72=# create function numdays(tours) returns int as '
> > test72'# select $1.return - $1.depart' language sql;
> > CREATE
> > test72=# select *, tours.numdays from tours;
> >    depart   |   return   | numdays
> > ------------+------------+---------
> >  2002-01-01 | 2002-01-10 |       9
> >  2001-12-15 | 2002-01-05 |      21
> > (2 rows)
> > 
> > The computed field doesn't quite have the same status as real fields
> > --- notice that * doesn't know about it in the above example --- but
> > it's a useful technique anyway.
> > 
> > 			regards, tom lane
> 
> Hmm I don't know if this solves my problem.
> E.g.
> I want to store a group of linked objects. Maybe x(ht)ml could be an
> example:
> 
> 
> 
> 
> 
> 
> 
> one two three
> 
> these should be stored in a table
> ( column "object" is of a base type written in c and should hold
> arbitrary string attributes)
> 
> id parentid object
> ------------------
> 1  0        table
> 2  1        tr
> 3  2        td1
> 4  2        td2
> 5  2        td3
> 
> to be able to query something like
> "select * from mytable where object.border='1'"
> i'd probably need a . operator on "object" if "object" is a base type
> implemented in c which holds the attributes internally - just as
> described in my previous post.
> 
> The problem is that i don't know how to handle
> "update mytable set object.margin='2' where parentid='2'"
> 
> Please note that I'm just thinking about how OR mapping could be done by
> taking advantage of postgresql's extensibility - this is not a real
> world problem ;-)
OK
If list of attributes must not be closed,
then in your example use table of attributes

CREATE TABLE attributes    ( id      int? oid?    , attname name? text?    , value   text?    , PRIMARY KEY(id,attname)    )
;
CREATE VIEW attributes_vAS SELECT * FROM attributes
;
CREATE RULE attributes_r ON INSERT TO attributes_v   DO INSTEAD    ( DELETE FROM attributes       WHERE (attributes.id, attributes.attname) = (new.id, new.attname)    ;      INSERT INTO attributes VALUES(new.id, new.attname, new.value)    )
;

so your examples will

SELECT mytable.* FROM mytable NATURAL JOIN attributes WHERE border='1';

and

INSERT INTO attributes_v SELECT id, 'margin', '2' FROM mytable WHERE parentid='2';

VIEW attributes_v used because I was in break twice in 7.1.3 -> 7.2 and
7.2 -> 7.2.1 transition and do not want remember of
order of apply of rules in different versions,
so I do it explicit.
May be it can be written shorter,
but danderous (IMHO).
As usual a lot of conviniense and optimisation
can be made dependant of task.
My examples are cutted from my big work and oversimplifyed,
so usual causes.

Sorry for bad English.

-- 
@BABOLO      http://links.ru/

Re: Composite datatypes, dynamic member fields

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

13 мая 2002 г. в 11:12:21

Robert Staudinger  writes:
> One idea is to implement a . operator on a basic data type and return
> the value 
> for the corresponding field from the "operator function".
> E.g.
> "select * from mytable where mytype.mymember='x'"
> could call something like
> mytype_member_read( mytype, member_name )
> but I'm not sure which datatype member_name would be in this case.

PG has always had the ability to define functions that could be
notationally treated as fields.  A trivial example:

test72=# create table tours(depart date, return date);
CREATE
test72=# insert into tours values('2002-01-01', '2002-01-10');
INSERT 525275 1
test72=# insert into tours values('2001-12-15', '2002-01-05');
INSERT 525276 1
test72=# create function numdays(tours) returns int as '
test72'# select $1.return - $1.depart' language sql;
CREATE
test72=# select *, tours.numdays from tours;  depart   |   return   | numdays
------------+------------+---------2002-01-01 | 2002-01-10 |       92001-12-15 | 2002-01-05 |      21
(2 rows)

The computed field doesn't quite have the same status as real fields
--- notice that * doesn't know about it in the above example --- but
it's a useful technique anyway.
		regards, tom lane