First-class Polymorphic joins?

El 13/08/15 a las 21:23, Guyren Howe escribió:

I also think migrations ought to be a first-class feature…

What do you mean with "migrations ought to be a first-class feature"?

There have been, and there still are efforts for making upgrading as
smooth and simple as possible, but I'm not really sure where you want to
get with this.

Cheers,

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/13/2015 05:23 PM, Guyren Howe wrote:

It surprises me that no SQL database to my knowledge has polymorphic joins as a first-class feature.

A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to.

I am pretty sure it already does that:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

REFERENCES reftable [ ( refcolumn ) ]

So you could have a "tags" table, that can attach tags to any of a
variety of other tables. Rails handles this by including the table name
as a string.

This seems a reasonable thing to want to do, and it seems that the database could handle it by combining the fields from the target tables in the result.

I also think migrations ought to be a first-class feature…

So is this the push the ORM into the database day?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/13/2015 05:59 PM, Guyren Howe wrote:

Ccing list

On Aug 13, 2015, at 17:49 , Adrian Klaver <adrian.klaver@aklaver.com> wrote:

A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to.

I am pretty sure it already does that:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

REFERENCES reftable [ ( refcolumn ) ]

I apologize for not being clearer.

The point is that the fk in different rows can reference different tables. I might want to be able to attach a tag to a person or a blog post, say. And then I want to find all the persons and blog posts with a particular tag, in a single query.

Could you just not turn that around?:

tag
tag_id
tag_desc

person
person_id
tag_fk references tag

blog
blog_id
tag_fk references tag

The simplest implementation is to have a table reference as a first-class value I can store in a field.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/13/2015 05:37 PM, Martín Marqués wrote:

El 13/08/15 a las 21:23, Guyren Howe escribió:

I also think migrations ought to be a first-class feature…

What do you mean with "migrations ought to be a first-class feature"?

There have been, and there still are efforts for making upgrading as
smooth and simple as possible, but I'm not really sure where you want to
get with this.

I think Guyren is talking about something like Django or Rails
migrations, Alembic, Sqitch, etc. A way to do changes to database
objects either whole or as incremental changes. Basically a schema
versioning method.

Cheers,

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

El 13/08/15 a las 23:17, Adrian Klaver escribió:

On 08/13/2015 05:37 PM, Martín Marqués wrote:

El 13/08/15 a las 21:23, Guyren Howe escribió:

I also think migrations ought to be a first-class feature…

What do you mean with "migrations ought to be a first-class feature"?

There have been, and there still are efforts for making upgrading as
smooth and simple as possible, but I'm not really sure where you want to
get with this.

I think Guyren is talking about something like Django or Rails
migrations, Alembic, Sqitch, etc. A way to do changes to database
objects either whole or as incremental changes. Basically a schema
versioning method.

OK, I misunderstood the statement.

In that case, he should take a look at sqitch. Very, very nice IMO.

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,
You can do this today using inheritance.
define a table "tagable" with person & blog as child tables.

than you could run queries like:
select * from tags JOIN tagable on (tag_id = tag_fk);
tag_id | tag_desc | id | tag_fk | data
--------+--------------+----+--------+------------------------
3 | blog tag 1 | 1 | 3 | blog tagged by tag 1
2 | person tag 2 | 2 | 2 | person tagged by tag 2

simple :)

Regards,
- Jony

On Fri, Aug 14, 2015 at 4:04 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote: