Обсуждение: Polymorphic delete help needed

> I am doing a project using Ruby On Rails with PostgreSQL as the
> database.  I have not seen the term polymorphic used with databases
> except with Rails so I will quickly describe it.
>
> Instead of holding just an id as a foreign key, the record holds a
> "type" field which is a string and an id.  The string is the name of
> the table to which the id applies.  (That is slightly simplified).

    Here is how I implemented something very similar (in PHP) :

    - "Node" class and several derived classes.
    - "nodes" table which contains the fields for the base class with node_id
as a PK and a field which indicates the class
    - "nodes_***" tables which contain the extra fields for the derived class
"***", having node_id as a primary key.

    As you see this is very similar to what you got.
    All the "nodes_***" tables have :
    node_id PRIMARY KEY REPERENCES nodes( id ) ON DELETE CASCADE

    So when I delete a Node, the derived class records are automatically
deleted in the auxiliary tables.
    Since there can be only one distinct node_id per node, you can put ON
DELETE CASCADE safely.

    Now, for your tree-thing, the fact that references are polymorphic isn't
important since they all refer to the same main "nodes" table.

    However as soon as you say "when a node no longer has any relations
pointing to it", then you get to choose between various flavors of garbage
collection and reference counting...

    Personally I like to move the deleted or orphaned rows to a "trash"
folder so they can be recovered if the user did delete the wrong node for
instance. Once in a while i "empty trash".

> The first problem that creates is it makes it hard to do a constraint
> on the name/id pair.  I thought about writing a function that would

    Is this ClassName / id only found in the "main" table or is every FK
implemented as a ClassName / id pair ?

On Jul 6, 2007, at 2:31 AM, PFC wrote:

>
>> I am doing a project using Ruby On Rails with PostgreSQL as the
>> database.  I have not seen the term polymorphic used with databases
>> except with Rails so I will quickly describe it.
>>
>> Instead of holding just an id as a foreign key, the record holds a
>> "type" field which is a string and an id.  The string is the name of
>> the table to which the id applies.  (That is slightly simplified).
>
>     Here is how I implemented something very similar (in PHP) :
>
>     - "Node" class and several derived classes.
>     - "nodes" table which contains the fields for the base class with
> node_id as a PK and a field which indicates the class
>     - "nodes_***" tables which contain the extra fields for the
> derived class "***", having node_id as a primary key.
>
>     As you see this is very similar to what you got.
>     All the "nodes_***" tables have :
>     node_id PRIMARY KEY REPERENCES nodes( id ) ON DELETE CASCADE
>
>     So when I delete a Node, the derived class records are
> automatically deleted in the auxiliary tables.
>     Since there can be only one distinct node_id per node, you can put
> ON DELETE CASCADE safely.
>
>     Now, for your tree-thing, the fact that references are polymorphic
> isn't important since they all refer to the same main "nodes" table.
>
>     However as soon as you say "when a node no longer has any
> relations pointing to it", then you get to choose between various
> flavors of garbage collection and reference counting...
>
>     Personally I like to move the deleted or orphaned rows to a
> "trash" folder so they can be recovered if the user did delete the
> wrong node for instance. Once in a while i "empty trash".

Your method is not exactly what I am doing because I do not have the
"nodes" table.  I have only the "nodes_***" tables.  But, I believe
your approach has many advantages.

Rails has an inheritance ability but they do it differently.  They
simply have a "nodes" table with all the "nodes_***" tables smashed
in to it.  I did not like that approach at all.

But doing what you are doing, I believe I can very nicely fit into
Rails and (obviously) PostgreSQL.  Plus, your suggestion of moving
entries to a "trash" bin seems very wise.

>
>> The first problem that creates is it makes it hard to do a constraint
>> on the name/id pair.  I thought about writing a function that would
>
>     Is this ClassName / id only found in the "main" table or is every
> FK implemented as a ClassName / id pair ?

The ClassName / id pair is found only in the relationships table.
There are two instances of it however: parent and child.

On Thu, Jul 05, 2007 at 09:56:12PM -0500, Perry Smith wrote:
> I am doing a project using Ruby On Rails with PostgreSQL as the
> database.  I have not seen the term polymorphic used with databases
> except with Rails so I will quickly describe it.

You have now :)

http://archives.postgresql.org/sfpug/2005-04/msg00022.php

> Instead of holding just an id as a foreign key, the record holds a
> "type" field which is a string and an id.  The string is the name of
> the table to which the id applies.  (That is slightly simplified).

This is brittle by nature.  The above link sketches out a way to make
it stable.  If you have questions, ask :)

> The first problem

of many ;)

[other stuff snipped]

> that creates is it makes it hard to do a constraint
> on the name/id pair.

Let PostgreSQL work *for* you instead of picking a fight with it and
then piling on heaps of unnecessary code.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

O>>     Here is how I implemented something very similar (in PHP) :
>>
>>     - "Node" class and several derived classes.
>>     - "nodes" table which contains the fields for the base class with
>> node_id as a PK and a field which indicates the class
>>     - "nodes_***" tables which contain the extra fields for the derived
>> class "***", having node_id as a primary key.
>>
>>     As you see this is very similar to what you got.
>>     All the "nodes_***" tables have :
>>     node_id PRIMARY KEY REPERENCES nodes( id ) ON DELETE CASCADE


> Your method is not exactly what I am doing because I do not have the
> "nodes" table.  I have only the "nodes_***" tables.  But, I believe your
> approach has many advantages.

    OK, I see.
    The advantage of my approach is that :

    - all common fields that are shared by all derived classes are stored in
the "base" nodes table
    - the PK on this table uniquely identifies any instance, whatever its
object class
    - the tables holding the derived classes's extra members (ie columns)
merely refer to it via a FK which is clean

    When you want to get a Node instance by its id, the ORM grabs the row in
the main table, which contains the type information, then instantiates the
right object class, grabs the extra row in the extra tables (which shares
the same id), and gives this data to the instantiated object which then
uses it to populate its member variables.

    Therefore, there can be no duplicates, and you only need to look into one
table to know if an object with a specific id exists or not, or to delete
it.

    Say Nodes can be, for instance, Photos, or Albums, or Articles ; say an
Album contains Photos and Articles and you want to display its contents :

    - select from relations r JOIN nodes n ON n.id=r.child_id WHERE
r.parent_id = (id of album)
    => gets the ids of children along with extra info stored in the relations
table (like sort order, etc)

    - scan the result set and group it by object class
    - for each object class :
        - grab the rows into the extra tables for the ids of objects of this
class using SELECT WHERE id IN (...)
        - instantiate and populate

    So if you have an Album with 5 Articles and 10 Photos, you do 3 queries -
    - get 15 rows from Nodes JOIN relations
    - get 5 rows from nodes_articles
    - get 10 rows from nodes_photos

    It's pretty efficient. I believe you can tweak Rails' ORM into doing that.

    FYI it's a PHP ORM that I wrote over the course of various projects.
Unfortunately PHP is very bad at generic/meta programming, so the
implementation is quite convoluted, and I did not opensource it because
the documentation would be quite an effort to write ;)

> Rails has an inheritance ability but they do it differently.  They
> simply have a "nodes" table with all the "nodes_***" tables smashed in
> to it.  I did not like that approach at all.

    Hm, another reason why I don't really like the Rails approach... do they
at least use unique IDs ? Please say yes ;)

> But doing what you are doing, I believe I can very nicely fit into Rails
> and (obviously) PostgreSQL.  Plus, your suggestion of moving entries to
> a "trash" bin seems very wise.

>>> The first problem that creates is it makes it hard to do a constraint
>>> on the name/id pair.  I thought about writing a function that would
>>
>>     Is this ClassName / id only found in the "main" table or is every FK
>> implemented as a ClassName / id pair ?
>
> The ClassName / id pair is found only in the relationships table.  There
> are two instances of it however: parent and child.

    I believe duplicating the ClassName and storing it everywhere to be bad.
With my approach you only need to use the id in reference since two
instances of the same base class cannot have the same id regardless of
their class, and you only need the id to instantiate a row, whatever its
class.

    If you want to specify that a class instance can only have children of
specific classes (or their subclasses), for example a FriendList can only
contain instances of People, or whatever, you can use a constraint trigger
which will check the class of the referenced row against a table
describing the allowed parent/child combinations.

On Jul 6, 2007, at 8:23 AM, PFC wrote:

>     The advantage of my approach is that :

[ snip -- all very good stuff ]

I think I can make something like this into Rails fairly easy.

>     Hm, another reason why I don't really like the Rails approach...
> do they at least use unique IDs ? Please say yes ;)

Yes.  Rails makes it really hard NOT to have id's -- but you can if
you need t hook into existing databases.  But, two things: 1) Please
don't infer what Rails can and can not do from me.  I'm new to
Rails.  2) The single inheritance is one approach.  The polymophic
way (that I described first) is an unrelated approach.
.
>> But doing what you are doing, I believe I can very nicely fit into
>> Rails and (obviously) PostgreSQL.  Plus, your suggestion of moving
>> entries to a "trash" bin seems very wise.
>
>>>> The first problem that creates is it makes it hard to do a
>>>> constraint
>>>> on the name/id pair.  I thought about writing a function that would
>>>
>>>     Is this ClassName / id only found in the "main" table or is
>>> every FK implemented as a ClassName / id pair ?
>>
>> The ClassName / id pair is found only in the relationships table.
>> There are two instances of it however: parent and child.
>
>     I believe duplicating the ClassName and storing it everywhere to
> be bad. With my approach you only need to use the id in reference
> since two instances of the same base class cannot have the same id
> regardless of their class, and you only need the id to instantiate
> a row, whatever its class.

This is really big to me.  I did not mention it but I have a
constraint currently implemented in Rails that two things that have a
Relationship, must have a relationship that comes from another
table.  So A can relate to B only if the link_types table has an
entry from A to B.  So, I have duplicate information sprinkled all
over.  That is partly my fault but it is also, as you point out,
partly the way that Rails puts the type in the Relationship rather
than down in a common table.  The common table approach makes so much
sense.

I need to go back and re-read how Rails does single inheritance.
Maybe I got that all confused.  Because, really, that is what all
this is flowing back to create.

>
>     If you want to specify that a class instance can only have
> children of specific classes (or their subclasses), for example a
> FriendList can only contain instances of People, or whatever, you
> can use a constraint trigger which will check the class of the
> referenced row against a table describing the allowed parent/child
> combinations.

I kinda have this but it is implemented up in rails, not down in the
db where it would be more efficient.

I replied to David as well.  You both have similar ideas.  I will
work on this over the weekend and I hope I can give an update when
I'm done.

Thank you very much.  You have helped me a lot.
Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems

On Fri, Jul 06, 2007 at 08:39:50AM -0500, Perry Smith wrote:
>
> On Jul 6, 2007, at 8:01 AM, David Fetter wrote:
>
> >On Thu, Jul 05, 2007 at 09:56:12PM -0500, Perry Smith wrote:
> >>I am doing a project using Ruby On Rails with PostgreSQL as the
> >>database.  I have not seen the term polymorphic used with
> >>databases except with Rails so I will quickly describe it.
> >
> >You have now :)
> >
> >http://archives.postgresql.org/sfpug/2005-04/msg00022.php
> >
> >>Instead of holding just an id as a foreign key, the record holds a
> >>"type" field which is a string and an id.  The string is the name
> >>of the table to which the id applies.  (That is slightly
> >>simplified).
> >
> >This is brittle by nature.  The above link sketches out a way to
> >make it stable.  If you have questions, ask :)
> >
> >>The first problem
> >
> >of many ;)
> >
> >[other stuff snipped]
> >
> >>that creates is it makes it hard to do a constraint on the name/id
> >>pair.
> >
> >Let PostgreSQL work *for* you instead of picking a fight with it
> >and then piling on heaps of unnecessary code.
>
> I really want to follow this particular edict.  Rails makes it easy
> to put the checking up in Rails but, I assume that if the interface
> between PostgreSQL and one of its languages like "SQL"  or Python,
> etc is an inch thick, then the interface between Rails and
> PostgreSQL  would be 12 inches thick.

At some point, you're going to realize that Rails is the problem, not
the solution.  It's written by people who do not understand what a
shared data store is and reflects problems inherent in its native
database platform: MySQL 3.23.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

On 7/6/07, David Fetter <david@fetter.org> wrote:
> At some point, you're going to realize that Rails is the problem, not
> the solution.  It's written by people who do not understand what a
> shared data store is and reflects problems inherent in its native
> database platform: MySQL 3.23.

This is rampant FUD; generalizations such as these help nobody. There
are plenty of developers, myself included, who use Rails in a
relational way, with relational integrity, transactions and normalized
schemas, none of which are particularly hampered by Rails' MySQLisms.

On the other hand, it is true that ActiveRecord lacks some tools out
of the box -- for example, model-level foreign key constraints are
accessible through a plugin you need to install separately. It is also
true that some of ActiveRecord's mechanisms are less than clean,
polymorphic associations in particular.

Keep in mind that ActiveRecord is an object-relational mapper. There
is the well-known impedance mismatch between object-oriented
programming and the relational model, one that is not trivially
overcome (the view-based example cited earlier in this thread is a
paticularly egregious example of a non-trivial solution).

Polymorphic associations is a practical solution that compromises
relational purity for performance and ease of implementation, and it
could be argued that it's a "good enough" solution for many
developers, who are ultimately interested in getting things done, even
if they "do not understand what a shared data store is".

ActiveRecord's polymorphism can be explained on a paper napkin; I
haven't encountered a purely relational solution in this thread that
fits this description.

Alexander.