Re: [SQLObject] alternateMethodName and creating SQL

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

On Thu, 2003-03-13 at 01:30, Luke Opperman wrote:
> Yes, generating schemas was the second part of my subject that I now realize I
> never wrote. :) I haven't quite decided how to handle varying databases, but
> that's not an issue for me at this moment.

Ultimately all the SQL should be moved out of SQLObject, and be into
DBConnection.  For instance, selects are now done in DBConnection, with
some help from SQLBuilder (though SQLBuilder currently is still database
agnostic).  SQLObject handles the select without using any SQL.  The
same should happen with the database creation.  SQLObject should pass
the database connection a couple parameters (_table and _idName should
be enough), along with the list of columns, and the database connection
has the responsibility from there.

But that's only part of it.  I'm blanking out on a good example of a
case where MySQL and Postgres differ on data type definitions, though
there's a ton of them.  Presumably a column type should know how to
represent itself in the create statement, but it has to do that with the
database connection in mind.  It's an NxM kind of problem (N being the
kinds of columns, M being the supported databases).  Maybe each column
should simply have a postgresCreate, mysqlCreate, etc. methods.  I think
that's probably as good as it will get.  But then that moves SQL
generation back into the SQLObject module and away from DBConnection.

> How we're generating SQL is by subclassing Col, so our object defs look like:
> 
> class OurObj(SQLObject):
>   _columns = [
>       String('name'),
>       Integer('toes'),
>       Date('birthday')
>       ]

I'd like to make import * reasonably clean, so I'd probably prefer names
like StringCol (and StringCol is still shorter than SQLObject.String);
but otherwise that seems like the right way to do it.  Of course you can
always do "import SQLObject as SO", and SO.String isn't any worse to
type than StringCol.  Hmm...

> and SQLObject has a classmethod "createSql", which calls out to each column's
> own createSql method, taking into consideration foreignKeys and alternateIds.
> RelatedJoins are still biting us, but mostly because I haven't looked at it enough.
> 
> I suppose one solution to the database-variance would be to have modules for
> each Column-subclass group (String,Int,Float,Date,whatever) so you'd
> 
> from PostgresTypes import *
> or
> from MySqlTypes import *
> 
> and those would be responsible for defining the createSql method.

I don't want to use imports for choosing a database.  Right now you can
change databases really easily and dynamically.  I don't see any reason
that has to change.

> We're also using the Column subclasses to enforce validation, which means our
> base Column has a validators=[] named parameter, and String's __init__ adds a
> default AsString() validator to the start of that list etc. We're using the same
> ValidatorConverters we have from working with FFK, btw.

ValidatorConverters seem too general to me, but validation functions
would certainly be useful.  It seems like all the interface you need for
SQLObject would be a function.  It could just raise ValueError when
necessary.  Maybe take three arguments -- the SQLObject instance that is
having its value set, the Col object for the column that's being set,
and of course the value that's being set.

Actual integration between SQLObject and FunFormKit should come about at
some time, but I don't think there's actually much code that needs to be
shared between them.  At some point I'd like to make some sort of
builder that takes a SQLObject class and builds a FormDefinition -- I'd
have to expand the introspection some, though.

  Ian