sqlobject-discuss

[SQLObject] Additionnal datas on joins

[G. <fra...@cl...>]

Hi all,

in some cases, there's additionnal datas in relation table. For example, th=
e rank if the relation is ordered, or a lock object for edition...=20

It's very common to see that.

So I'm thinking about how to implement this :

 - one (ugly but usuable) way to do that without modifying SQLObject is to =
describe the join like an entity with 2 foreign keys, and then specifying t=
he additionnal columns as usual.

 - a cleaner way would be to add the columns to the join declaration, and t=
hen attach these columns to the entities retrevied when performing the join.

so, SQLObject guys, what do you think about that ?

Fran=E7ois

ps : I'll upload soon my customized version in the CVS, with support for st=
ring primary key and ordered join. I'll post when ready ;)

Re: [SQLObject] Additionnal datas on joins

[Brad B. <br...@bb...>]

On Mon, Jul 28, 2003 at 10:31:01AM +0200, Fran?ois Girault wrote:
> Hi all,
> 
> in some cases, there's additionnal datas in relation table. For example, the rank if the relation is ordered, or a lock object for edition... 
> 
> It's very common to see that.

Yes, this is the use case for composite keys. :)

> So I'm thinking about how to implement this :
> 
>  - one (ugly but usuable) way to do that without modifying SQLObject is to describe the join like an entity with 2 foreign keys, and then specifying the additionnal columns as usual.

I don't know of a way in SQLObject to force two columns to be unique,
except to define semantics for composite keys.
 
>  - a cleaner way would be to add the columns to the join declaration, and then attach these columns to the entities retrevied when performing the join.
> 
> so, SQLObject guys, what do you think about that ?

The cleanest way is to figure out how we want to define composite
primary keys.

Like this?

class SiteMonitor(SQLObject):
    site = ForeignKey("Site")
    monitor = ForeignKey("Monitor")

    _primary_key = (site, monitor)

or this perhaps?

class SiteMonitor(SQLObject):
    site = ForeignKey("Site")
    monitor = ForeignKey("Monitor")

    PrimaryKey(site, monitor)

It would be nice if specifying the primary key looked somewhat like
specifying the foreign key (like the latter example), but in other
ways, the former example is more SQLObjectish in its use of _foo's for
meta information about the table.

I think either of these syntaxes would be useable. What do you guys
think?

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Additionnal datas on joins

[Brad B. <br...@bb...>]

On Mon, Jul 28, 2003 at 09:41:12AM -0400, Brad Bollenbach wrote:
> On Mon, Jul 28, 2003 at 10:31:01AM +0200, Fran?ois Girault wrote:
> > Hi all,
> > 
> > in some cases, there's additionnal datas in relation table. For example, the rank if the relation is ordered, or a lock object for edition... 
> > 
> > It's very common to see that.
> 
> Yes, this is the use case for composite keys. :)

Which -- by the way -- I need to be able to do *right* *now*. :) I'm
willing to help on getting the tests that need to pass written, and
writing the code that passes them once we, the users of SQLObject,
decide what is the most natural way to express composition.

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Additionnal datas on joins

[Bruno T. <bt...@as...>]

Hi

> Which -- by the way -- I need to be able to do *right* *now*. :) I'm

    +1 for me too.

> willing to help on getting the tests that need to pass written, and
> writing the code that passes them once we, the users of SQLObject,
> decide what is the most natural way to express composition.


what about:

    class Foo(SQLObject):
        foo =3D StringCol(dbName=3D'foo')
        bar =3D ForeignKey([IntCol(dbName=3D'bar_id'),
                            IntCol(dbName=3D'bar_idd')])

So this would make room for keys of type other than integer and allow us
to have a variable number of keys in the composite foreignKey.

[]'s

 Bruno Trevisan

 bt...@as...          |=3D| Async Open Source |=3D| D. Alexandrina, 253=
4
 http://www.async.com.br/ |=3D| +55 16 261-2331   |=3D| 13566-290
                          |=3D| +55 16 9781-8717  |=3D| S=E3o Carlos, SP, B=
rasil

Re: [SQLObject] Additionnal datas on joins

[Brad B. <br...@bb...>]

On Mon, Jul 28, 2003 at 12:25:56PM -0300, Bruno Trevisan wrote:
> Hi
> 
> > Which -- by the way -- I need to be able to do *right* *now*. :) I'm
> 
>     +1 for me too.
> 
> > willing to help on getting the tests that need to pass written, and
> > writing the code that passes them once we, the users of SQLObject,
> > decide what is the most natural way to express composition.
> 
> 
> what about:
> 
>     class Foo(SQLObject):
>         foo = StringCol(dbName='foo')
>         bar = ForeignKey([IntCol(dbName='bar_id'),
>                             IntCol(dbName='bar_idd')])
> 
> So this would make room for keys of type other than integer and allow us
> to have a variable number of keys in the composite foreignKey.

The problem is we're not talking about a column here; we're talking
about how to say "use these two (or three, or N) columns as the
primary key". It doesn't make sense to assign this to a single
attribute (attributes map one-to-one with SQL table columns, unless
you've added some normal Python properties to your class as well), but
rather to use meta-fu to let SQLObject know what we mean.

The semantics should be unified, of course, so that you'd use the same
semantics for single-column keys as for multi-column keys. I see no
harm in continuing to follow the rule as well that if no key is
defined, an implicit ID column is generated (as long as it's
documented, which it is).

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Additionnal datas on joins

[Ian B. <ia...@co...>]

On Mon, 2003-07-28 at 08:41, Brad Bollenbach wrote:
> The cleanest way is to figure out how we want to define composite
> primary keys.
> 
> Like this?
> 
> class SiteMonitor(SQLObject):
>     site = ForeignKey("Site")
>     monitor = ForeignKey("Monitor")
> 
>     _primary_key = (site, monitor)

I'm thinking:

    id = ('site', 'monitor')
or:
    id = (site, monitor)
both should be equivalent.

This goes along with using:
    id = StringCol()
when you have a non-integer primary key.

When using a composite keys, obj.id should return a tuple of the
respective columns -- ids should be hashable and immutable, which fits
tuples nicely.

  Ian

Re: [SQLObject] Additionnal datas on joins

[Ian B. <ia...@co...>]

On Mon, 2003-07-28 at 03:31, Fran=E7ois Girault wrote:
> Hi all,
>=20
> in some cases, there's additionnal datas in relation table. For
> example, the rank if the relation is ordered, or a lock object for
> edition...=20

I'm not entirely clear on what you want here.  I think you are
describing a whole set of features, and maybe you're thinking about how
to describe those.

I'm not actually very happy with the _columns (now defunct), _idName,
_joins, etc., style.  So alternative syntaxes are good.

If the feature needs a name (like columns and joins do), then attributes
is the way to go.  Neither ordering or locks need a name, though -- they
would be used implicitly in other areas.  __order__ and __lock__ are
possibilities -- at least they are obviously magic, but I'm also a
little reluctant to overuse the double-underscore variables.  Though I
don't really believe the idea that double underscore variables are
preserved for Python.  Python grows such variables very slowly.

I'm open to other ideas.  Nested classes, like:

class InvoiceItem(SQLObject):

    invoiceOrder =3D IntCol()

    class meta:
        orderBy =3D invoiceOrder
        lock =3D 'optimistic'

Certainly easily extensible, without a not-so-bad syntax.  It would lead
to the separate mapping object that some people sometime want...

But maybe that's not what you are asking about ;)  I'm not entirely
clear on the rest of this...

> It's very common to see that.
>=20
> So I'm thinking about how to implement this :
>=20
>  - one (ugly but usuable) way to do that without modifying SQLObject
> is to describe the join like an entity with 2 foreign keys, and then
> specifying the additionnal columns as usual.
>=20
>  - a cleaner way would be to add the columns to the join declaration,
> and then attach these columns to the entities retrevied when
> performing the join.

I don't think this would be threadsafe -- things shouldn't be attached
to SQLObject instances unless they really belong to the instance, not
the instance in a specific context.

But as I reread this, I'm really not clear about what you're trying to
do.  An example might clarify (though I probably won't be able to follow
up)

  Ian