[SQLObject] Re: Impressions so far

[Edmund L. <el...@in...>]

Ian Bicking wrote:


>>6. Need to have some way of specifying unique constraints for single and 
>>  groups of columns for auto schema generation. --- See (7) below.
> 
> 
> At some point SQLObject shouldn't do every detail of schema creation. 
> Maybe there should be a hook for adding your own SQL that gets executed
> on createTable/dropTable, so you can use those methods without (or in
> addition to) the automatic table definition.

True, however, if you're going to support schema generation, it makes
sense to at least support the most common constraints: unique, not null,
etc. As it stands, SQLObject nearly supports the common cases. Adding
uniqueness support would allow SQLObject to support most common cases.


>>7. I don't use enums at all. For table driven applications, I use 
>>primary keys coupled with foreign key constraints. This allows you to 
>>change the allowed values of a column by varying the contents of another 
>>table. This is one argument against using synthetic primary keys. But 
>>using synthetic primary keys is still OK so long as unique columns can 
>>be specified, and the foreign key constraint can take the name of a column.

Speaking of enums, I notice that there's no explicit support for
booleans. No big deal unless you consider schema generation, where the
boolean column type should be generated.


>>8. There is no way to specify (at least not that I can see), outer 
>>joins. These are important. ---
> 
> You'll have to give an example of what you want to do.  I don't see the
> need.

Maybe it is just that I don't know how to do it in SQLObject? Here's 
what I'm trying to do...

Schema:

create table person (
     id       serial,
     username varchar(30),
--
constraint person_key
     primary key (id),
constraint person_uq
     unique (username)
);

create table car (
     id       serial,
     owner_id integer,
     make     varchar(30),
--
constraint car_key
     primary key (id)
constraint car_owner_fk
     foreign key (owner_id) references person (id)
);


What I want: a list of car makes and owners. In SQL:

select p.username, c.make
from person as p
     left join car as c on p.id = c.owner


If I don't use a left outer join, I will get a list of people who have
cars, and the people who don't have cars won't appear in it.


In SQLObject:

class Person(SQLObject):
     _columns = [
         StringCol("username", length=30, notNone=True,
             alternateID=True)]

class Car(SQLObject):
     _columns = [
         StringCol("make", length=30, notNone=True),
         IntCol("ownerId", foreignKey="Person")]


To get a list of all people: Person.select()
To get a list of all car makes: Car.select()

How do I achieve the same as a left outer join without writing a loop?



> notNull=True or notNone=True as a keyword argument to your column.

OK, it wasn't in the manual, or I'm going blind.


> Yeah... but I'm still unsold.  You can always do:
> 
> class Person(SQLObject):
> 
>     def _get_gender(self):
>         return self._SO_get_gender().name
>     def _set_gender(self, value):
>         self._SO_set_gender(Gender.byName(value))
> 
> It's not that bad to use properties and create your own attributes, if
> SQLObject's attributes don't suite you.  People should do that more -- I
> went out of my way to make it possible (and to document it)!  This is
> exactly the sort of thing it's there for.

Sorry, I didn't understand the implications of this when I read the 
documentation. I'll give it a shot.


Regarding the _connection class attribute. How do I use it? Do I just 
pass it a connection object?

This raise the bigger question of how to handle connections when 
multiple modules, each implementing SQLObjects are used. What is the 
best way to do this?

Should I just import the connection string from a global settings file 
and have each module open its own connection via __connection__? Seems 
wrong since I might want to change the backend DB, and don't want to 
dive into each module to edit the __connection__ arguments.

I have a global connection pool, so perhaps I should just have each 
SQLObject-based module import the pool and fetch a connection. Is this 
what the _connection attribute is for? Can we not use the DBConnection 
object?

Oh about orderBy... how do we specify whether it is ascending or descending?


...Edmund.