[SQLObject] Re: Impressions so far

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

On Sat, 2003-05-31 at 14:40, Edmund Lian wrote:
> 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.

That would be fine.  If you can enumerate all those, and suggest how
they'd be specified in Python, I'm happy to add them. 

> >>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.

Yes, like Luke said, it is a problem with 't' and 'f'.  You'd have to
do:

    someBool = Col(sqlType='BOOLEAN')

    def _set_someBool(self, value):
        if value:
            self._SO_set_someBool(self, 't')
        else:
            self._SO_set_someBool(self, 'f')

I should remember to put this recipe in the docs, since a fix won't come
until the version after the upcoming version.

> 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?

Well, you would do a loop, but at least it's a pretty loop, thanks to
list comprehension:

[p.username, (p.car and p.car.make) for p in Person.select()]

You do more SQL queries in this version, but in most cases I doubt that
will be a performance problem.

> > notNull=True or notNone=True as a keyword argument to your column.
> 
> OK, it wasn't in the manual, or I'm going blind.

Mmm... yeah, I don't think I've documented any of the schema generation
yet.  I've been redoing the documentation just in the last week or so,
updating for the next release.

> 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.

Mostly I'd use a single module where you define the connection,
exporting a single "connection" variable, and then all the other classes
would use that.

I'd thought about naming connections before, so you'd say:

    _connection = "main"

And somewhere else:

PostgresConnection(name="main", ...)

And then it'd be registered.  Maybe that'd be useful to put back in.

> 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?

Luke gave a good solution to this.  It should be generalized, though.

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

Nowhere now.  I should add an option to SelectResults and a function to
SQLBuilder... I'll do that before I make the next release, since it
should just be a small change.

  Ian