sqlobject-discuss

[SQLObject] creating joins

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

I was wondering, when instantiating a Join object, shouldn't joinColumn
default to idName of the class instead of always making up the id column
with the Style?  I mean, if I define _idName for my class, then for
every single join I have to say joinColumn = idName.  Does this make
sense to anyone?

Why am I doing something so stupid in my database naming?  Because I
defined a database with the tables "user" and "group", found out pgsql
didn't like those names, so I renamed the tables "users" and "groups"
without chaging "user_id" and "group_id" references.  Am I lazy?  Yes,
but I think what I'm asking makes sense on some level anyhow.

Nick

Re: [SQLObject] creating joins

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

On Mon, 2003-04-28 at 14:49, Nick wrote:
> I was wondering, when instantiating a Join object, shouldn't joinColumn
> default to idName of the class instead of always making up the id column
> with the Style?  I mean, if I define _idName for my class, then for
> every single join I have to say joinColumn = idName.  Does this make
> sense to anyone?

Hmmm... I'm not following you.

Say you have a Person and an Address (one to many), Address has a
person_id column, but Person has an id column, and they go together. 
How does idName come into it? 

I mean, ideally when you define the foreign key column in Address it
would automatically create the join, but the fear of circular
dependencies has scared me off from doing that so far :)

  Ian

Re: [SQLObject] creating joins

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

On Tue, 2003-04-29 at 03:23, Ian Bicking wrote:
> On Mon, 2003-04-28 at 14:49, Nick wrote:
> > I was wondering, when instantiating a Join object, shouldn't joinColumn
> > default to idName of the class instead of always making up the id column
> > with the Style?
>
> Say you have a Person and an Address (one to many), Address has a
> person_id column, but Person has an id column, and they go together. 
> How does idName come into it? 

Most non-MySQL schema designers (in my experience, anyway) tend to name
columns that are the same data the same in every table they appear in. 
That means, if person_id is in Address, then the key in Person is
person_id as well.  This uncomplicates JOINS in a major way, as well as
maintains consistency in your data dictionary.  Therefore, idName for a
table is almost always the same name as the foreign key in another
table, except where you have 2 in the same table.

> I mean, ideally when you define the foreign key column in Address it
> would automatically create the join, but the fear of circular
> dependencies has scared me off from doing that so far :)

Actually, I did do it that way, and you're not going to get any circular
dependencies as long as you're using strings to resolve your class names
late.  Perhaps you could explain some of your fears to me, because I
still don't fully grok every aspect of the code yet :)  I was originally
using class references for my foreign key definitions and ran into the
circular dependecy probelms, but there were ways around that, too.  The
programmer *could* get into an infinite loop of they follow circular
references, but that's their problem :-P  That can happen now.

Nick

Re: [SQLObject] creating joins

[David M. C. <da...@da...>]

On Tue, Apr 29, 2003 at 11:36:13AM -0500, Nick wrote:

> Most non-MySQL schema designers (in my experience, anyway) tend to name
> columns that are the same data the same in every table they appear in. 
> That means, if person_id is in Address, then the key in Person is
> person_id as well.  This uncomplicates JOINS in a major way, 

It's called a natural join.  In postgres if you name your columns this way
you can do

select * from person join address

and it automatically does the join on the common column.

Dave Cook

Re: [SQLObject] creating joins

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

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

> Most non-MySQL schema designers (in my experience, anyway) tend to
> name
> columns that are the same data the same in every table they appear
> in.
> That means, if person_id is in Address, then the key in Person is
> person_id as well.  This uncomplicates JOINS in a major way, as
> well as
> maintains consistency in your data dictionary.  Therefore, idName
> for a
> table is almost always the same name as the foreign key in another
> table, except where you have 2 in the same table.

I'll have to say this is a style issue, not so much a MySQL issue. :)
A quick poll amongst developers here (all Postgres or MSSQL people)
shows 50/50 between naming id columns "table_id" or just "id". I'm
definitely an "id" person. :) (We've got one guy who routinely names
every column in a table "tablename_colname". This makes as much sense
to me as "tablename_id", so yeah..

This is not SQLObject specific, but I guess I've never understood the
"tablename_id" argument for making joins cleaner. in SQL:

".... ON person.id = other.person_id...." vs
".... ON person.person_id = other.person_id"

always seemed to be duplicating the person metadata to me, and makes
me forget which is primary. whereas there's no doubt in my mind
whether something is a foreignKey or primaryKey if it's just "id".

Everyone here names foreignkeys "table_id" except in rare cases (two
that come to mind: foreignKey to own table (usually called
"parent_table_id" by myself) and multiple foreignKeys to another
table, called arbitrary things). So yeah. Not sure what my point is,
except I'll assume the new Style object ought to cover this, and
that's that.

> I was originally
> using class references for my foreign key definitions and ran into
> the circular dependecy probelms, but there were ways around that,
> too. The programmer *could* get into an infinite loop of they follow
> circular references, but that's their problem :-P
> That can happen now.

The problem (that i've experienced) is circular *import* dependencies.
I would agree, strings seem to solve this. With caveats mentioned in
another mail about joined/FK'ed classes possibly needing to be
imported by a third-party class that doesn't want to have to know
that Person has a Phone object (and hence where to import Phone
from), just that Person.phone returns a Phone object.

Automatically creating the joins might conceivably solve this,
although I'm pretty sure I can come up with with the same import
circular dependencies whether it is at module import time (solved by
strings) or object instantiation time (although classRegistry can
probably be used to save this: you'll never import that FK'ed class
if it's already in the classRegistry...) Hmm.

- Luke

Re: [SQLObject] creating joins

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

On Tue, 2003-04-29 at 12:05, Luke Opperman wrote:
> Automatically creating the joins might conceivably solve this,
> although I'm pretty sure I can come up with with the same import
> circular dependencies whether it is at module import time (solved by
> strings) or object instantiation time (although classRegistry can
> probably be used to save this: you'll never import that FK'ed class
> if it's already in the classRegistry...) Hmm.

I think this should be okay, so long as *somebody* imports all the
classes.  You can't use one class until the other class has been setup
(because of the fixup step).

The the module that uses one class doesn't need to import all of them --
so long as they all get imported when the application gets started up
(at least, that's how it should work).

  Ian

Re: [SQLObject] creating joins

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

On Tue, 2003-04-29 at 12:05, Luke Opperman wrote:
> This is not SQLObject specific, but I guess I've never understood the
> "tablename_id" argument for making joins cleaner. in SQL:
> 
> ".... ON person.id = other.person_id...." vs
> ".... ON person.person_id = other.person_id"

try USING (person_id) instead.

> So yeah. Not sure what my point is,
> except I'll assume the new Style object ought to cover this, and
> that's that.

I looked at that, and I think it's a step in the right direction, but it
needs to be fleshed out a little more I think.

> The problem (that i've experienced) is circular *import* dependencies.
> I would agree, strings seem to solve this. With caveats mentioned in
> another mail about joined/FK'ed classes possibly needing to be
> imported by a third-party class that doesn't want to have to know
> that Person has a Phone object (and hence where to import Phone
> from), just that Person.phone returns a Phone object.

As long as you make your classes part of a module, I don't see this
being a problem, since the class names are all stored up when you define
the class, which will happen whether you import 1, 2, or all the classes
in a module.

Nick

Re: [SQLObject] creating joins

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

> As long as you make your classes part of a module, I don't see this
> being a problem, since the class names are all stored up when you
> define
> the class, which will happen whether you import 1, 2, or all the
> classes
> in a module.

 I'm not quite following: Here's the full example that with
SQLObject's current classRegistry/string-defined FKs will lead to
third-part classes needing to import things they don't directly know
about:

------ Person.py
class Person(SQLObject):
    _columns = [StringCol(\'name\')]
    _joins = [MultipleJoin(\'Cat\')]

------ Cat.py
class Cat(SQLObject):
    _columns = [StringCol('name'), KeyCol('person_id',
foreignKey='Person')]

------ MyApp.py

from Person import Person
peep = Person(1)
for cat in peep.cats: # error because Cat has not been imported.
    print cat

------ END

I really dislike having to import Cat in MyApp.py (which solves the
problem), since it's not explicitly used, and there's no reason MyApp
should even know where to import it from. Of course, neither does
SQLObject, so I don't have a solution.

- Luke



--
 i find your contempt for naked feet curious.

Re: [SQLObject] creating joins

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

On Tue, 2003-04-29 at 14:01, Luke Opperman wrote:
> ------ Person.py
> class Person(SQLObject):
>     _columns = [StringCol(\'name\')]
>     _joins = [MultipleJoin(\'Cat\')]
> 
> ------ Cat.py
> class Cat(SQLObject):
>     _columns = [StringCol('name'), KeyCol('person_id',
> foreignKey='Person')]

Stick these in a package, MyDB with the lines in __init__:
from Person import Person
from Cat import Cat

> from Person import Person
to:
from MyDB import Person
> peep = Person(1)
> for cat in peep.cats: # error because Cat has not been imported.
>     print cat

Problem solved, because Cat has been imported through the package.

Nick

Re: [SQLObject] creating joins

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

> from MyDB import Person
> > peep = Person(1)
> > for cat in peep.cats: # error because Cat has not been imported.
> >     print cat
>
> Problem solved, because Cat has been imported through the package.
>
> Nick

Ugh. Certainly this works.. I'm initially shying away from doing full
imports like this for performance reasons, but I suppose it's a
one-time cost per interpreter. Also, I could create functional
partitions of some of these large databases where this concerns me
(from MyDB.ProductStuff import ....). Hmm, ok.

(The other part that bothers me is maintaining the list of DB Objects
in both the directory and in the __init__, but I suppose I can solve
that with an "import everything in this dir" script I have lying
around, in __init__.py instead of explicit imports.)

- Luke