sqlobject-discuss

[SQLObject] Col naming in MultipleJoin

[deelan <de...@in...>]

hi there,

i'm starting to play around with sqlobject 0.3, i'm following the
html pages and trying out things described in the manual. i'm having 
troubles to correctly name a foreign key column using multiplejoin.

for example, using this two classes:

class PhoneNumber(SQLObject):
     """Phone number"""

     _table = "number"
     _idName = "NumberId"
     _columns = [
         Col("PersonID", dbName="PersonId", foreignKey="Person"),
         Col("phone", dbName="Phone"),
         Col("type", dbName="PhoneType")
         ]

class Person(SQLObject):
     """Person in address book"""

     _table = "person"
     _idName = "PersonId"
     _columns = [
         Col("firstName", dbName="FName"),
         Col("lastName", dbName="LName"),
         ]
     _joins = [MultipleJoin('PhoneNumber', joinColumn='PersonId')]

in phonenumber class, "PersonID" column must named "PersonID" with 
correct "ID" case both in the class and in the DB, it seems to ignore 
dbName="" value i specify in the col constructor. to maintain 
consistency with my db schema it would be better to name field as 
PersonId (see Person class).

infact if i change phonenumber "PersonID" with "PersonId" sqlobject 
throws out an error:"All foreign key columns must end with 'ID'"
perhaps i'm missing something obvious here.

linked to this issue adding a new phonenumber to
person "p" instance requies the oddly named "PersonID"

p = Person(1)

this fails:
pn = PhoneNumber.new(phone='666', type='home', PersonId=p.id)

this works:
pn = PhoneNumber.new(phone='666', type='home', PersonID=p.id)

please note, you access person's unique id with p.id (lowercase) but
you have to name Col named "PersonID" ;(

any help would be appreciated.

thanks.

PS:
BTW i've just found that this works:

p = Person(1)
pn = PhoneNumber.new(phone='666', type='home', Person=p)

Re: [SQLObject] Col naming in MultipleJoin

[Nick <ni...@dd...>]

On Sat, 2003-05-10 at 08:28, deelan wrote:
> this fails:
> pn = PhoneNumber.new(phone='666', type='home', PersonId=p.id)
> 
> this works:
> pn = PhoneNumber.new(phone='666', type='home', PersonID=p.id)

Correct; the parameters in SQLObject methods match to the first argument
to Col, the Pythonic column name, not dbName, the column name in the
database.

> p = Person(1)
> pn = PhoneNumber.new(phone='666', type='home', Person=p)

That would be preferred for readability.

Nick

Re: [SQLObject] Col naming in MultipleJoin

[deelan <de...@in...>]

Nick wrote:
>>this fails:
>>pn = PhoneNumber.new(phone='666', type='home', PersonId=p.id)
>>
>>this works:
>>pn = PhoneNumber.new(phone='666', type='home', PersonID=p.id)
> 
> 
> Correct; the parameters in SQLObject methods match to the first argument
> to Col, the Pythonic column name, not dbName, the column name in the
> database.

that's fine, but what i would like to know is why i can't name
the Col "PersonId"  and i have to call it "PersonID", this happens
both in the pythonic column name and in the DB column name.

am i missing something?

thanks for your support.

Re: [SQLObject] Col naming in MultipleJoin

[Nick <ni...@dd...>]

On Sat, 2003-05-10 at 13:03, deelan wrote:
> Nick wrote:
> >>this fails:
> >>pn = PhoneNumber.new(phone='666', type='home', PersonId=p.id)
> >>
> >>this works:
> >>pn = PhoneNumber.new(phone='666', type='home', PersonID=p.id)
> > 
> > 
> > Correct; the parameters in SQLObject methods match to the first argument
> > to Col, the Pythonic column name, not dbName, the column name in the
> > database.
> 
> that's fine, but what i would like to know is why i can't name
> the Col "PersonId"  and i have to call it "PersonID", this happens
> both in the pythonic column name and in the DB column name.

No, the dbName can be anything.  It doesn't matter what it's called in
the database as long as you use it in the dbName parameter.  The fact
that you have to call a column somethingid is hardcoded in SQLObject.  I
don't particularly like it, since it's clear in the Col definition that
this is a foreign key.  I don't particularly see the downside in having
to say pn.Person.id vs. pn.PersonID.  I think it's more clear if you're
really making an object oriented model.  So I altered my local code to
do this and maintain a patch against CVS.

Nick

Re: [SQLObject] Col naming in MultipleJoin

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

On Sat, 2003-05-10 at 08:28, deelan wrote:
> infact if i change phonenumber "PersonID" with "PersonId" sqlobject 
> throws out an error:"All foreign key columns must end with 'ID'"
> perhaps i'm missing something obvious here.

That was a fairly arbitrary requirement.  The naming requirement still
exists in CVS, but it's case-insensitive, which would fix your problem. 
So feel free to remove or modify that assert, since the behavior will be
fixed for 0.4.

> linked to this issue adding a new phonenumber to
> person "p" instance requies the oddly named "PersonID"
> 
> p = Person(1)
> 
> this fails:
> pn = PhoneNumber.new(phone='666', type='home', PersonId=p.id)
> 
> this works:
> pn = PhoneNumber.new(phone='666', type='home', PersonID=p.id)

Because you are defining an attribute you named PhoneNumber.PersonID
(which happens to equate to number.PersonId in the database).  So even
if you named it that against your will ;), it's just being consistent.

  Ian

Re: [SQLObject] Col naming in MultipleJoin

[Luke O. <lu...@me...>]

Quoting Nick <ni...@dd...>:
>  The fact that you have to call a column somethingid is hardcoded
> in SQLObject.  I don't particularly like it, since it's clear in
> the Col definition that this is a foreign key.  I don't particularly
> see the downside in having to say pn.Person.id vs. pn.PersonID.
> I think it's more clear if you're really making an object oriented
> model.

Hmm, would you expand on this slightly? In our usage, yes, we almost
always use pn.Person.id, and .

The problem is that _columns/_joins defines the structure of the
database, and the pythonic access mechanisms appear by magic *for
some columns, and for all joins*. (somewhere around here I wrote up a
one-page summary of magic that happens, maybe it'd be useful in the
docs? I think it's all there already, this is just a summary of
'unexpected' things, to have people know they don't need to do
personID=myPeep.id, for instance.)

My question is: what would you prefer the initialization for
foreignKey's be? I'll throw out my idea, which is based on the fact
that in general, we specify the *python* attribute name as the column
name, and the dbName is optional:

  ForeignCol('person', foreignKey='Person')

would assume (based on Style object?) the dbname is person_id, and the
direct ID python access stays as "personID" (since I think there are
cases where there's justification for retaining immediate access to
the actual key values).

  ForeignCol('person', foreignKey='Person, dbName='person',
directAttr='personId')

Not sure on the nam 'directAttr', but this would be the mechanism for
overriding the direct python access name.

This doesn't have to require a change to existing code, as all other
columns could still take the foreignKey argument; this column-type is
just a shortcut that may make more sense for some people. Depending
on what people say, this could become KeyCol, or not (for
backwards-compatibility issues).

Thoughts?

- Luke

Re: [SQLObject] Col naming in MultipleJoin

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

On Sat, 2003-05-10 at 13:40, Luke Opperman wrote:
> My question is: what would you prefer the initialization for
> foreignKey's be? I'll throw out my idea, which is based on the fact
> that in general, we specify the *python* attribute name as the column
> name, and the dbName is optional:
> 
>   ForeignCol('person', foreignKey='Person')
> 
> would assume (based on Style object?) the dbname is person_id, and the
> direct ID python access stays as "personID" (since I think there are
> cases where there's justification for retaining immediate access to
> the actual key values).

I intended to do something like this.  Or maybe just:

KeyCol('Person')

Both person and personID then being implied.  I'm not sure whether I
should just replace KeyCol, or create a ForeignKey or ForeignCol class. 
I created a ForeignKey class in CVS, at least for now.

  Ian

Re: [SQLObject] Col naming in MultipleJoin

[Nick <ni...@dd...>]

see attached patch.  it basically puts the ID on for you instead of you
having to do it in Col() so it doesn't break anything.  There's no
reason to change the functionality other than I don't like forced naming
conventions in this way :)

Nick

On Sat, 2003-05-10 at 13:40, Luke Opperman wrote:
> Quoting Nick <ni...@dd...>:
> >  The fact that you have to call a column somethingid is hardcoded
> > in SQLObject.  I don't particularly like it, since it's clear in
> > the Col definition that this is a foreign key.  I don't particularly
> > see the downside in having to say pn.Person.id vs. pn.PersonID.
> > I think it's more clear if you're really making an object oriented
> > model.
> 
> Hmm, would you expand on this slightly? In our usage, yes, we almost
> always use pn.Person.id, and .
> 
> The problem is that _columns/_joins defines the structure of the
> database, and the pythonic access mechanisms appear by magic *for
> some columns, and for all joins*. (somewhere around here I wrote up a
> one-page summary of magic that happens, maybe it'd be useful in the
> docs? I think it's all there already, this is just a summary of
> 'unexpected' things, to have people know they don't need to do
> personID=myPeep.id, for instance.)
> 
> My question is: what would you prefer the initialization for
> foreignKey's be? I'll throw out my idea, which is based on the fact
> that in general, we specify the *python* attribute name as the column
> name, and the dbName is optional:
> 
>   ForeignCol('person', foreignKey='Person')
> 
> would assume (based on Style object?) the dbname is person_id, and the
> direct ID python access stays as "personID" (since I think there are
> cases where there's justification for retaining immediate access to
> the actual key values).
> 
>   ForeignCol('person', foreignKey='Person, dbName='person',
> directAttr='personId')
> 
> Not sure on the nam 'directAttr', but this would be the mechanism for
> overriding the direct python access name.
> 
> This doesn't have to require a change to existing code, as all other
> columns could still take the foreignKey argument; this column-type is
> just a shortcut that may make more sense for some people. Depending
> on what people say, this could become KeyCol, or not (for
> backwards-compatibility issues).
> 
> Thoughts?
> 
> - Luke
> 
> 
> 
> -------------------------------------------------------
> Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
> The only event dedicated to issues related to Linux enterprise solutions
> www.enterpriselinuxforum.com
> 
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] Col naming in MultipleJoin

[Luke O. <lu...@me...>]

Quoting Nick <ni...@dd...>:

> see attached patch.  it basically puts the ID on for you instead of
> you
> having to do it in Col() so it doesn't break anything.  There's no
> reason to change the functionality other than I don't like forced
> naming
> conventions in this way :)

Yep, that's the condensed version of what I was suggesting: turn it
around, so that the column names you define are all the *python*
accessors, and the fact that it's a key is tacked on elsewhere (or
explicit, as dbName sometimes must be). (This makes the assumption
that I think we both share, that the 'correct' pythonic accessor is
directly to the object, and the secondary one is to the ID key
value.)

(although I personally wouldn't hardcode it into Col() either way,
rather make it a subclass of Col; your patch is minimally
code-invasive, but very disruptive of existing code. But I realize
it's mostly for private consumption only. :)

I suppose if I were writing ForeignCol as described in my last post,
I'd go one step further, and remove the duplicate verbage (which
exists whether it is ForeignCol or KeyCol) of saying 'foreignKey=':

 ForeignKey('mother', to='Person')

in the shortest form.

possible braindead implementation, requiring no changes to existing
Col implementation, ignoring directAttr idea:

class ForeignCol(Col):
  def __init__(self, name, **kw):
    to = popKey(kw, 'to')
    if to:
      kw['foreignKey'] = to
    newName = name + 'ID'
    Col.__init__(self, newName, **kw)