sqlobject-discuss

[SQLObject] Composed Primary key

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

Hi all,

here's another feature I need for my project : I have several tables
whose primary key is composed of two or three columns.

You could tell me : add an id to your table and that's ok... but not for
my needs (damned)!
=20
So I'd like to implement support for that...

As I want to do it clean, I think I'm going to realize a "Key" or "Id" obje=
ct.

This object would contain a list of Column objects, which together are
the primary key

its SQLRepr would be (imo) simple to generate, just a 'col1=3Dx AND col2=3Dy
AND col3=3Dz' to replace in WHERE clauses for select, update and delete.

I will start this soon, so I'd like to have some suggestions from
SQLObject's dev&users before diving in

Best Regards,

Fran=E7ois Girault

Re: [SQLObject] Composed Primary key

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

On Thu, 2003-07-17 at 11:09, Fran=E7ois Girault wrote:
> Hi all,
>=20
> here's another feature I need for my project : I have several tables
> whose primary key is composed of two or three columns.

Well, if there's going to be composite keys, that might as well be
generalized to all the columns (i.e., any attribute could be a composite
of several columns).

The .id should remain largely the same, except it becomes a tuple.  The
individual columns should not be individually accessible from Python
(except like .id[0], .id[1], etc).

Implementation-wise, I'm not sure, that's a messy issue.

  Ian

Re: [SQLObject] Composed Primary key

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

On Thu, Jul 17, 2003 at 01:41:02PM -0500, Ian Bicking wrote:
> On Thu, 2003-07-17 at 11:09, Fran?ois Girault wrote:
> > Hi all,
> > 
> > here's another feature I need for my project : I have several tables
> > whose primary key is composed of two or three columns.
> 
> Well, if there's going to be composite keys, that might as well be
> generalized to all the columns (i.e., any attribute could be a composite
> of several columns).

What's the use case for this?

Composition seems fairly specific to primary key definitions; I don't
see it useful as a general purpose thing that belongs in SQLObject
column definition semantics. I would have thought it preferable to
define columns as normal, and then create another property to abstract
them.

e.g. (slightly contrived, and totally untested)

class Date(SQLObject):

    day = IntCol()
    month = IntCol()
    year = IntCol()

    def get_date(self):
        return "%d/%02d/%02d" % (self.year, self.month, self.day)

    def set_date(self, value):
        self.year, self.month, self.day = [int(x) for x in value.split("-")]
        
    date = property(get_date, set_date)

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Composed Primary key

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

On Thu, 2003-07-24 at 13:18, Brad Bollenbach wrote:
> > > here's another feature I need for my project : I have several tables
> > > whose primary key is composed of two or three columns.
> > 
> > Well, if there's going to be composite keys, that might as well be
> > generalized to all the columns (i.e., any attribute could be a composite
> > of several columns).
> 
> What's the use case for this?

Well, there's the possibility of things like a point type or something,
where there's more than one column that gets turned into one Python
object (immutable, of course).

But I waver.  Now I think I agree with you.  There's lots of things that
SQLObject *could* do, but I don't think it should do all those.  So long
as there are ways to code, manually, what you want to do, this is enough
for most of these cases.  Only when something is really common or really
hairy or just not possible with the current code should we add more
features.

Like you said, we can do this with properties, and it's not too hard. 
For the id it's a bit more difficult, and I can deal with improving
that.  In combination with the non-integer keys, composite keys might
not be too bad.  (Though I draw the line at mutable primary keys!)  And
you can't do them with properties.

I'd like to do some type coercion stuff, like I've mentioned but not
implemented for a long time.  Then I have to hold off on more features,
because SQLObject could become difficult to understand.  I think it's
much more readable to do:

class Position(SQLObject):
    x = FloatCol()
    y = FloatCol()
    def _get_pos(self):
        return (self.x, self.y)
    def _set_pos(self, value):
        self.set(x=value[0], y=value[1])

Than to do:

class Position(SQLObject):
    pos = CompositeCol(FloatCol('x'), FloatCol('y'))

Oh, sure, that's much more elegant looking, much more economical in
typing.  But it obscures what is made very explicit in the first
example.  And if you really wanted to you could always do:

class TupleComposite(object):
    def __init__(self, *cols):
        self.cols = cols
    def __get__(self, obj, objtype):
        return tuple([getattr(obj, attr) for attr in self.cols])
    def __set__(self, obj, value):
        obj.set(**dict(zip(self.cols, value)))

class Position(SQLObject):
    x = FloatCol()
    y = FloatCol()
    pos = TupleComposite('x', 'y')

But then it's up to you to explain what TupleComposite is, and
DictComposite, and StructComposite, and whatever else you end up
creating -- instead of SQLObject coming to be what is perceived to be a
monolithic and complex system that is inaccessible to newcomers (which
happens to a lot of projects).

Anyway, which is a long way of saying I now agree, just composite ids
are called for.  They might look like:

class Whatever(SQLObject):
    domain = StringCol()
    subdomain = StringCol()
    id = ('domain', 'subdomain')

  Ian

Re: [SQLObject] Composed Primary key

[Magnus <ma...@th...>]

At 14:18 2003-07-24 -0400, Brad Bollenbach wrote:
> > Well, if there's going to be composite keys, that might as well be
> > generalized to all the columns (i.e., any attribute could be a composite
> > of several columns).
>
>What's the use case for this?

For instance money objects that consist of an amount and a currency.
You could make them full objects with an identifier and do:

order_line
|id |order_id|spec          |money_id|
+---+--------+--------------+--------+
|  1|     123|Dog collar    |       1|
|  2|     123|Dog food      |       2|

money
|id |amt    |currency|
+---+-------+--------+
|  1|12.50  |EUR     |
|  2|0.34   |USD     |

But is seems simpler to just do:

order_line
|id |order_id|spec          |price_amt|price_currency|
+---+--------+--------------+---------+--------------+
|  1|     123|Dog collar    |12.50    |EUR           |
|  2|     123|Dog food      |0.34     |USD           |


In this case you could say that the price is a value from a
design point of view, but an object from a programming point
of view. Martin Fowler calls such objects "Value Objects" in
the book "Patterns of Enterprise Application Architechture".



--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The Agile Programming Language 

Re: [SQLObject] Composed Primary key

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

On Tuesday, August 5, 2003, at 06:59 PM, Magnus Lyck=E5 wrote:

> At 14:18 2003-07-24 -0400, Brad Bollenbach wrote:
>> > Well, if there's going to be composite keys, that might as well be
>> > generalized to all the columns (i.e., any attribute could be a=20
>> composite
>> > of several columns).
>>
>> What's the use case for this?
>
> For instance money objects that consist of an amount and a currency.
> You could make them full objects with an identifier and do:
>
> order_line
> |id |order_id|spec          |money_id|
> +---+--------+--------------+--------+
> |  1|     123|Dog collar    |       1|
> |  2|     123|Dog food      |       2|
>
> money
> |id |amt    |currency|
> +---+-------+--------+
> |  1|12.50  |EUR     |
> |  2|0.34   |USD     |

This is bad database design. amt and currency belong in the order_line=20=

table.

> But is seems simpler to just do:
>
> order_line
> |id |order_id|spec          |price_amt|price_currency|
> +---+--------+--------------+---------+--------------+
> |  1|     123|Dog collar    |12.50    |EUR           |
> |  2|     123|Dog food      |0.34     |USD           |
>
>
> In this case you could say that the price is a value from a
> design point of view, but an object from a programming point
> of view. Martin Fowler calls such objects "Value Objects" in
> the book "Patterns of Enterprise Application Architechture".

I don't follow your point. At what point do Python properties stop=20
being useable here, and composite columns (for something other than the=20=

PK) become necessary?

--
Brad Bollenbach
BBnet.ca=

Re: [SQLObject] Composed Primary key

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

Hi There

>
> > At 14:18 2003-07-24 -0400, Brad Bollenbach wrote:
> >> > Well, if there's going to be composite keys, that might as well be
> >> > generalized to all the columns (i.e., any attribute could be a
> >> composite
> >> > of several columns).
> >>
> >> What's the use case for this?

So I think a have a situation where some could need this:
(note, this is existent database, system under development has to
integrate to it, so bad database layout is not a way out here. Badly
designed databases are out there and we must work with some of them) :(


products_table
+---+-----------------------+
|id | description    | family_id | subfamily_id |
+---+----------------+-----------+--------------+
|  1| Foobar gum     |        10 |             5|
|  1| Foobar lolipops|        10 |             6|
|  1| Foobar socks   |        20 |             5|

family_table
+---+-----------------------+
|id | description           |
+---+-----------------------+
| 10| Candies               |
| 20| Underwear             |

subfamily_table
+---+-----------+---------------+
|id | family_id | description   |
+---+-----------+---------------+
|  5|         10| chewing gum   |
|  5|         20| socks         |
|  6|         10| lolipops      |

I need to retrieve subfamily description for my products. It is possible
to manually retrieve it, but the idea is just state to SQLObject that id
and family_id are primary keys for the subfamily table and that the
subfamily are linked to the products through those keys. Having
SQLObject retrieving the subfamily object when I retrieve a product
using those definitions would be great.


[]'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] Composed Primary key

[Magnus <ma...@th...>]

At 19:13 2003-08-05 -0400, Brad Bollenbach wrote:

>On Tuesday, August 5, 2003, at 06:59 PM, Magnus Lyck=E5 wrote:
>
>>At 14:18 2003-07-24 -0400, Brad Bollenbach wrote:
>>> > Well, if there's going to be composite keys, that might as well be
>>> > generalized to all the columns (i.e., any attribute could be a=
 composite
>>> > of several columns).
>>>
>>>What's the use case for this?
>>
>>For instance money objects that consist of an amount and a currency.
>>You could make them full objects with an identifier and do:
>>
>>order_line
>>|id |order_id|spec          |money_id|
>>+---+--------+--------------+--------+
>>|  1|     123|Dog collar    |       1|
>>|  2|     123|Dog food      |       2|
>>
>>money
>>|id |amt    |currency|
>>+---+-------+--------+
>>|  1|12.50  |EUR     |
>>|  2|0.34   |USD     |
>
>This is bad database design. amt and currency belong in the order_line=
 table.

I think so too! That's why the propsed feature is good.

>>But is seems simpler to just do:
>>
>>order_line
>>|id |order_id|spec          |price_amt|price_currency|
>>+---+--------+--------------+---------+--------------+
>>|  1|     123|Dog collar    |12.50    |EUR           |
>>|  2|     123|Dog food      |0.34     |USD           |
>>
>>
>>In this case you could say that the price is a value from a
>>design point of view, but an object from a programming point
>>of view. Martin Fowler calls such objects "Value Objects" in
>>the book "Patterns of Enterprise Application Architechture".
>
>I don't follow your point. At what point do Python properties stop being=20
>useable here, and composite columns (for something other than the PK)=20
>become necessary?

If I have a Money class, my Python class OrderLine would have
an attribute "price", which was an instance of the Money class.
It should *not* have a priceAmount attribute and a separate
priceCurrency attribute. The internal composition of the price
is not the concern of the OrderLine class!

Thus, we get a mismatch between Python classes and RDBMS tables.

 From an OO Design point of view, you separate your data into
two categories: Objects and values. An object is something that
has an identity on its own, like a person, a window, or an
order line. A value is some data which just describes some
property of another object, but doesn't have its own identity,
such as a length, a name, or a monetary amount.

In Python, it's very common that design objects are instances
of a class, and design values have another type than instance,
such as long, float or string, but that's not always the case.

So, while the idea that a class in Python corresponds to a
table in SQL fits the bill in most cases, this is not always
the case. There can be mismatches in both directions.

If you have simple non-scalar objects like dates with time-zones,
money objects with a currency, measurements with a unit and a
given precision etc, you would probably want to "inline" these
values into your main table, rather than to assign some kind
of made up identity to these values and store them in a separate
table. You still implement these "value objects" as instances of
their own class in Python though.

The other way around, you can have a list of integers as a simple
attribute in a Python class, and then you want to go the other
way around, and split your Python class into more than one table.

It would be nice if SQLObject handles both these deviations from
the typical 1 class =3D=3D 1 table situation in a convenient way.

E.g. Python classes that have attributes like below...

class Money
     amt =3D int()
     currency =3D str()

class OrderLine
     id =3D oid()
     order_id =3D oid from Order()
     spec =3D str()
     price =3D Money()

...should be stored in *one* (not two) tables as we seem to
agree on above, with as little tweaking as possible.

On the other hand, if you have something like:

class Curve
    color =3D str()
    style =3D str()
    coords =3D list of complex

E.g.

c =3D Curve()
c.color =3D 'RED'
c.style =3D 'DASHED'
c.coords =3D [(1+2j), (3+5j), (3+2j)]

Then you obviously need two tables to comply to the first
normal form. One with an id, the color and the style, and
a separate table with the coordinate list.


--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The Agile Programming Language=20

Re: [SQLObject] Composed Primary key

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

On Tuesday, August 5, 2003, at 05:59 PM, Magnus Lyck=E5 wrote:
> order_line
> |id |order_id|spec          |price_amt|price_currency|
> +---+--------+--------------+---------+--------------+
> |  1|     123|Dog collar    |12.50    |EUR           |
> |  2|     123|Dog food      |0.34     |USD           |
>
>
> In this case you could say that the price is a value from a
> design point of view, but an object from a programming point
> of view. Martin Fowler calls such objects "Value Objects" in
> the book "Patterns of Enterprise Application Architechture".

Indeed, any value object (i.e., immutable) will work fine with=20
SQLObject, using a certain idiom:

class OrderLine(SQLObject):
     priceAmt =3D CurrencyCol()
     priceCurrency =3D String(length=3D3)

     def _get_price(self):
         return Price(self.priceAmt, self.priceCurrency)
     def _set_price(self, value):
         self.set(priceAmt=3Dvalue.amount, priceCurrency=3Dvalue.currency)=


class Price(object):
     def __init__(self, amount, currency):
         self.amount =3D amount
         self.currency =3D currency
     # and probably more methods too


It is important that Price be immutable (or at least treated that way),=20=

as the OrderLine instance won't detect changes to a Price instance, so=20=

they won't be saved in the database.

In fact, Price instances could be mutable if the instances were also=20
given a reference to the containing OrderLine, and notified it of=20
changes.

The whole idiom could be encapsulated in something like a=20
compositeproperty (analogous to property), but that would be only=20
slightly more compact while being less clear, IMHO.

I think this idiom is sufficient, so I don't plan any other support. =20
Except for the primary key, which is somewhat unique in that SQLObject=20=

uses that column internally.

   Ian

Re: [SQLObject] Composed Primary key

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

On Thu, Jul 17, 2003 at 06:09:28PM +0200, Fran?ois Girault wrote:
> Hi all,
> 
> here's another feature I need for my project : I have several tables
> whose primary key is composed of two or three columns.

[snip]
 
> As I want to do it clean, I think I'm going to realize a "Key" or "Id" object.
> 
> This object would contain a list of Column objects, which together are
> the primary key

[snip] 
 
> I will start this soon, so I'd like to have some suggestions from
> SQLObject's dev&users before diving in

(Sorry for the delayed response. I briefly skimmed this before, and
now that I know I need this, I'd like to contribute)

I gather you've already implemented this. If so, how?

There are various semantics that could be used.

SQL style:

class MonitoredDevice(SQLObject):
    """I'm a device being monitored."""

    _cacheValues = False

    monitor = ForeignKey('Monitor')
    device = ForeignKey('Device')
    one_month_rate = CurrencyCol(notNone = True)
    expiration_date = DateTimeCol(notNone = True)
    ...

    primary_key(monitor, device) # or PrimaryKey(monitor, device)


SQLObject style:

class MonitoredDevice(SQLObject):
    """I'm a device being monitored."""

    _cacheValues = False

    monitor = ForeignKey('Monitor')
    device = ForeignKey('Device')
    one_month_rate = CurrencyCol(notNone = True)
    expiration_date = DateTimeCol(notNone = True)
    ...

    _primary_key = (monitor, device)

or maybe something else. In any case, these composite keys would
supplant the autogenerated id column.

Any other semantic ideas that I've missed?

Is supplanting the autogenerated id column even possible in SQLObject
without rewriting everything?

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Composed Primary key

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

On Thu, 24 Jul 2003 14:06:49 -0400
Brad Bollenbach wrote:

> (Sorry for the delayed response. I briefly skimmed this before, and
> now that I know I need this, I'd like to contribute)
> 
> I gather you've already implemented this. If so, how?
> 

I've managed to delay this feature in my project, because composite keys are known to slow down db perfs, especially on join operation. 

My project manager always wants this feature, but I'll implement it in some months...