Re: [SQLObject] Re: SQLObject

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

On Thu, 2003-04-10 at 08:31, Bud P.Bruegger wrote:
> > > 4. The whole business of using "id" as a default name for a primary key is
> > > somewhat contrived, and increases the difficulty of mapping SQLObject onto
> > > existing database schemas.
> 
> For what it's worth, here are some thoughts on the topic.  
> 
> I personally believe having id's is a great thing.  Using primary keys
> with any sort of business meaning will get you into trouble sooner or
> later.  So if SQLObject didn't add it, I would...  (BTW, this argument
> is also relevant for composite keys--they usually have business
> meaning...).  The only problem may occur if one uses SQLObject with
> pre-existing databases that don't have a comparable field...
> 
> Correct me if I'm wrong, but it seems that SQLObject needs an id
> column of type integer.  Could that be relaxed to be another type (a
> string).

Right now I'm sure I have made the assumption it is an integer in
several places (though probably all such assumptions have been moved to
DBConnection now, which makes it easier).  It wouldn't be hard to relax
that.

> In this case, one possible solution would be to use an option
> "primaryKey=True" for Col.  If a primary key is defined in _columns,
> then it is used, else, id is implied as is currently the case.  

I dunno... the object ID is special, and SQLObject treats it as
special.  It is assumed to be immutable, for instance, as any good ID
should be.  It's not a column like the others are, and I'm comfortable
with it being a special case.

> Another thought in this context is that PostgreSQL already inserts an
> object id column for every table.  But maybe it's not a good idea to
> use since other dbms don't have equivalents...
> 
> One thing on my wishlist would be to (optionally) create a globally
> unique id for the object.  That could be controlled by an additional
> option "globallyUnique=True" that would go together with PrimaryKey,
> Unique, or alternateID (i.e., everything that implies Unique).  

You mean add OIDs onto the databases that don't have it.  MiddleKit does
this, and it's one thing that annoyed me about it, though I can
understand the motivation.  It's an interesting idea, but I'm not sure
how to make it work within the bounds of SQL, and in a way that's both
useful and transparent.

> > > 7. I don't see any support for tables with composite primary keys. Is this
> > > an omission you plan to rectify?
> > 
> > I don't know.  I like single, simple primary keys.  They work well.  If
> > the table is really so minimal that it can't stand to have an extra
> > field for the id, then I suspect a SQLObject class is too heavy for it
> > (like with the join tables).  Of course, when adapting to a pre-existing
> > schema you might not have as many options.  Maybe it wouldn't be that
> > hard to fudge it (maybe make the ID into a tuple), but SQLObject really
> > expects for each object to have a single ID.
> 
> For the above considerations, I think a single object id is usually a
> good idea.  In one project I started out with composite keys and as
> the data structure evolved added an autoincrement key to keep it
> simpler and more changeable.  
> 
> However, it may be nice to have composite field indices on tables and
> have an equivalent to alternateID that creates a method with multiple
> arguments.  

If it's just the method, that's trivial enough to create on your own
that it doesn't seem like a big deal.  For instance, if a and b are your
composite fields, the method would look like:

def selectAB(self, a, b): return self.selectBy(a=a, b=b)[0]

  Ian