sqlobject-discuss — All things SQLObject-related

[SQLObject] a simple question and small code changes

[Peter W. <pw-...@te...>]

Hi.
I've just started having a look at SQLObject and so far very impressed 
- seems nice and natural.

I'm using the download from the website, not cvs, and can't find a way 
to delete a row from the database, is there something that I'm 
overlooking or is this not implemented yet?

Also I've been using SQLite quite extensively lately and noticed a 
couple of places in DBConnection.py that can be added to/changed.

To find if a table exists (def tableExists) in SQLite use:
select tbl_name from sqlite_master where type='table' where tbl_name = 
'TABLE'

Also createIDColumn should be:
	return '%s INTEGER PRIMARY KEY' % soClass._idName
INTEGER is the only type specified that means anything to SQLite.

Hope that helps. Again looking good so far :-)

--
peter w.

Re: [SQLObject] possible documentation improvement

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

On Tue, 2003-04-01 at 03:47, Bud P.Bruegger wrote:
> [I'm just starting to play with SQLObject and thought taking note of
> some points may help improve the documentation.  I'm using version
> 0.2.1.  Maybe some of what I note is already done in CVS?]

I haven't changed the documentation much since 0.2.1, or fixed many bugs
(I dunno if that's good or bad :), mostly been adding features.  So lots
of things will still apply. 

> DateTime:  
> Would it help to add the library to the Requirements section?

That shouldn't have been a requirement, just something that gets used if
available.  I've updated the code in CVS to be more tollerant, thanks.

  Ian

[SQLObject] possible documentation improvement

[Bud P. B. <bu...@si...>]

[I'm just starting to play with SQLObject and thought taking note of
some points may help improve the documentation.  I'm using version
0.2.1.  Maybe some of what I note is already done in CVS?]

DateTime:  
Would it help to add the library to the Requirements section?

hope this is helpful

--b

Re: [SQLObject] PostgreSQL and Serial, foreign keys for self-reference

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

Quoting Ian Bicking <ia...@co...>:

> On Mon, 2003-03-31 at 10:43, Bud P.Bruegger wrote:

> > Is there a reason for using SEQUENCE instead of SERIAL?
> 
> AFAIK (but I'm not a Postgres expert), SERIAL is
> equivalent to creating
> a sequence in the same way SQLObject expects.  Maybe
> it'll even work.
> It's easier for SQLObject to use the sequence directly
> rather than let
> the ID be generated implicitly by the default, but
> obviously you don't
> have to use the default.

Yes, in Postgres the preferred way is to use SERIAL, and all
of our tables are generated that way. Sorry I haven't
mentioned this before Ian, and it makes some changes to your
PostgresConnection class.

When you use SERIAL in Postgres, it creates a sequence named
slightly differently than SQLObject expects now, so there
either needs to be a way to pass the sequence name, or just
change it to the SERIAL format: tablname_idname_seq (ie,
product_id_seq), slightly different from the current
"product_seq". So the postgresconnection could pull table
name and idfield name, and both request and generate the
appropriate sequence name. I'll create a patch sometime
today for this.

As far as SQLObject using this, no change is necessary
(beyond referring to the appropriate sequence mentioned
above). Calling NEXTVAL and inserting (instead of letting
the default made by SERIAL be used) works completely fine,
and works if you have both SQLObject and other DB tools
creating records.

- Luke

Re: [SQLObject] Dynamic features in CVS

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

On 03/31/2003 01:20:59 PM sqlobject-discuss-admin wrote:

>my understanding is that
>database introspection in Postgres is a lot harder than MySQL.  I have
>to figure out how to do tableExists in Postgres too...

Not at all. Just rummage through the PostgreSQL system catalogs named pg_*.
See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=catalogs.html

For example, all relations are listed in pg_class. So, select relName,
relkind from pg_class where relkind = 'r' gives you a list of all
relations, including the system relations.

...Edmund.

[SQLObject] Dynamic features in CVS

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

I added some stuff to CVS for adding and removing columns on the fly
(including doing an ALTER), as well as crude support for creating a
class from the schema directly.  It only works for MySQL, I'll have to
look up the Postgres ALTER (probably easy), and my understanding is that
database introspection in Postgres is a lot harder than MySQL.  I have
to figure out how to do tableExists in Postgres too...

Anyway, this is all part of a larger plan to integrate FunFormKit with
SQLObject, and create a generic through-the-web database interface,
including database building (for Access-like functionality).  Integrated
with a simple permission model, you could phrase all sorts of
applications this way -- surveys, registration, mailing lists, etc.

Still a bunch of steps left, though...

  Ian

Re: [SQLObject] PostgreSQL and Serial, foreign keys for self-reference

[Bud P. B. <bu...@si...>]

On 31 Mar 2003 11:12:16 -0600
Ian Bicking <ia...@co...> wrote:

> AFAIK (but I'm not a Postgres expert), SERIAL is equivalent to creating
> a sequence in the same way SQLObject expects.  Maybe it'll even work. 
> It's easier for SQLObject to use the sequence directly rather than let
> the ID be generated implicitly by the default, but obviously you don't
> have to use the default.

I thought of just trying it in the CREATE statement:

CREATE TABLE person(
  id INT PRIMARY KEY SERIAL,
  ....

and will let you know if it works the same as when creating a sequence
explicitly.    (Or did I misunderstand the doc and SQLObject actually
generates the CREATE statements?).

thanks

--b

Re: [SQLObject] PostgreSQL and Serial, foreign keys for self-reference

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

On Mon, 2003-03-31 at 10:43, Bud P.Bruegger wrote:
> 1.  Does SQLObject support self-references back to the same class,
> such as they occur in parent/child relationships (or hierarchies)?
> Can I use the foreignKey keyword argument for that?

I can't see any reason it would be a problem, though I guess I haven't
thought about it specifically.   

> 2. While I don't have much experience in Postgresql, I read up on the
> CREATE SEQUENCE statement.  It seems there is an easier way to do this
> as illustrated in the following example from
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL
> 
> CREATE TABLE tablename (colname SERIAL);
> 
> It seems that prior to version 7.3, SERIAL implied UNIQUE.  I checked
> in older documentation (version 6.5), and SERIAL seems to be supported
> even back then. 
> 
> Is there a reason for using SEQUENCE instead of SERIAL?

AFAIK (but I'm not a Postgres expert), SERIAL is equivalent to creating
a sequence in the same way SQLObject expects.  Maybe it'll even work. 
It's easier for SQLObject to use the sequence directly rather than let
the ID be generated implicitly by the default, but obviously you don't
have to use the default.

  Ian

[SQLObject] PostgreSQL and Serial, foreign keys for self-reference

[Bud P. B. <bu...@si...>]

I just started a project and would like to use SQLObject.  May I ask
two questions:

1.  Does SQLObject support self-references back to the same class,
such as they occur in parent/child relationships (or hierarchies)?
Can I use the foreignKey keyword argument for that?

2. While I don't have much experience in Postgresql, I read up on the
CREATE SEQUENCE statement.  It seems there is an easier way to do this
as illustrated in the following example from
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL

CREATE TABLE tablename (colname SERIAL);

It seems that prior to version 7.3, SERIAL implied UNIQUE.  I checked
in older documentation (version 6.5), and SERIAL seems to be supported
even back then. 

Is there a reason for using SEQUENCE instead of SERIAL?

many thanks for your help

--bud

/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

Re: [SQLObject] Caching, and Nonexistant objects

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

On Sat, 2003-03-29 at 16:15, Luke Opperman wrote:
> Second, very bad things happen if I instantiate an object
> with an id that doesn't exist in the database (ie,
> Product(43) ). It is instantiated fine, but when an
> attribute is accessed it hangs python entirely. The cause of
> the hang is unclear at this point, and concerns me, but at a
> minimum we need a way to check if an object exists! Our
> feeling is that instantiating a non-existant ID should
> throw an exception immediately, but alternatively (or as the
> implementation of throwing an exception), a function to make
> sure the id is valid in the database should be created.
> Probably should be in the Connection classes, since all SQL
> should ideally be over there..
> 
> Now that I look through the code again, _SO_autoInit()
> should be taking care of this, but that only happens when
> attrs are accessed, and that's where the fatal hang is
> happening anyways.

The fatal hang is bad... it should just be an exception.

What do you get from connection.queryOne("SELECT bla FROM wherever WHERE
id = 43") ?  Or does it hang?  The queryOne method should just return
None, which'll cause some random exception (should be defined), but I
don't know why it would hang.

  Ian

Re: [SQLObject] Caching, and Nonexistant objects

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

On Sat, 2003-03-29 at 16:15, Luke Opperman wrote:
> I still only encounter this bug when destroying an object,
> since destroy() makes no check for existance in the cache.
> So our oblivious fix (ignoring why things still aren't being
> cached) is to add:
> 
>         if self._SO_instanceCache.has_key(self.id):
>             del self._SO_instanceCache[self.id]
> 
> at the end of destroy().

That's something I forgot to update when I put in the new cache system. 
I believe it should be okay now.

  Ian

[SQLObject] Caching, and Nonexistant objects

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

Hello all -

Two short things:

First, caching is still screwed up for us. I'm using CVS
from a week or so ago, after all the createTable stuff was
added. (Due to my changes mentioned in another message about
defaultOrder sorting stuff, I haven't tried updating to
the latest, but I show no diffs that would seem to fix this...)

I still only encounter this bug when destroying an object,
since destroy() makes no check for existance in the cache.
So our oblivious fix (ignoring why things still aren't being
cached) is to add:

        if self._SO_instanceCache.has_key(self.id):
            del self._SO_instanceCache[self.id]

at the end of destroy().

Second, very bad things happen if I instantiate an object
with an id that doesn't exist in the database (ie,
Product(43) ). It is instantiated fine, but when an
attribute is accessed it hangs python entirely. The cause of
the hang is unclear at this point, and concerns me, but at a
minimum we need a way to check if an object exists! Our
feeling is that instantiating a non-existant ID should
throw an exception immediately, but alternatively (or as the
implementation of throwing an exception), a function to make
sure the id is valid in the database should be created.
Probably should be in the Connection classes, since all SQL
should ideally be over there..

Now that I look through the code again, _SO_autoInit()
should be taking care of this, but that only happens when
attrs are accessed, and that's where the fatal hang is
happening anyways.

Summary:

a new connection-level function, tentatively named
'isValidID'. _init should call this (?), and raise a new
exception before caching etc.

Thoughts?

- Luke

Re: [SQLObject] Using existing databases in read-only mode

[Frank B. <fb...@fo...>]

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> I'm out of the loop, still at PyCon, so I won't be able to follow up on 
> this, but the ID field is implicit (you can set the name with _idName). 
>  So you shouldn't have a Col('id').

I *knew* it was a stupid mistake on my side. Thanks a lot Ian, and
have fun on PyCon.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Using existing databases in read-only mode

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

I'm out of the loop, still at PyCon, so I won't be able to follow up on 
this, but the ID field is implicit (you can set the name with _idName). 
  So you shouldn't have a Col('id').

That error message is kind of funny, but maybe it's just the strange 
artifact from two ID's.  I'll have to make an assert about that.

On Thursday, March 27, 2003, at 12:40 PM, Frank Barknecht wrote:

> Hallo,
>
> I'm trying to clean up a project and use SQLObject as DB-wrapper. The
> tables are filled and I only want to use SO in read-only mode.
>
> Now I have a probably very stupid question: How do I instantiate a new
> SQLObject given an id.
>
> I'm trying something like this, which feels natural, but doesn't work:
>
> from SQLObject import *
>
> SQLProductFields = [Col('id'), Col('description'), Col('format'),
> 	Col('artist'), Col('category'), Col('title'), Col('price'),
> 	Col('label')]
>
> class Normal(SQLObject):
> 	_columns = SQLProductFields
> 	_connection = DBConnection.MySQLConnection(host='localhost', 
> db='fbar',
> 		user='foo', passwd='')
>
> entry=45
> n = Normal(entry)   # error here!
>
> Traceback (most recent call last):
>   File "SQLText.py", line 9, in ?
>     n = Normal(45)
>   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
> 419, in __new__
>     val._init(id, connection)
>   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
> 428, in _init
>     self.id = id
>   File "<string>", line 1, in <lambda>
>   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
> 456, in _SO_setValue
>     self._SO_writeLock.acquire()
> AttributeError: 'Normal' object has no attribute '_SO_writeLock'
>
> I'm probably doing something totally wrong, but I don't get, what...
> I'm using SO from half an hour ago.
> Ciao
> -- 
>  Frank Barknecht                               _ ______footils.org__
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by:
> The Definitive IT and Networking Event. Be There!
> NetWorld+Interop Las Vegas 2003 -- Register today!
> http://ads.sourceforge.net/cgi-bin/redirect.pl?keyn0001en
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
--
Ian Bicking  ia...@co...  http://colorstudy.com
4869 N. Talman Ave., Chicago, IL 60625  /  773-275-7241
"There is no flag large enough to cover the shame of
  killing innocent people" -- Howard Zinn

[SQLObject] Using existing databases in read-only mode

[Frank B. <fb...@fo...>]

Hallo,

I'm trying to clean up a project and use SQLObject as DB-wrapper. The
tables are filled and I only want to use SO in read-only mode.

Now I have a probably very stupid question: How do I instantiate a new
SQLObject given an id.

I'm trying something like this, which feels natural, but doesn't work:

from SQLObject import *

SQLProductFields = [Col('id'), Col('description'), Col('format'),
	Col('artist'), Col('category'), Col('title'), Col('price'),
	Col('label')]

class Normal(SQLObject):
	_columns = SQLProductFields
	_connection = DBConnection.MySQLConnection(host='localhost', db='fbar', 
		user='foo', passwd='')

entry=45
n = Normal(entry)   # error here!

Traceback (most recent call last):
  File "SQLText.py", line 9, in ?
    n = Normal(45)
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 419, in __new__
    val._init(id, connection)
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 428, in _init
    self.id = id
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 456, in _SO_setValue
    self._SO_writeLock.acquire()
AttributeError: 'Normal' object has no attribute '_SO_writeLock'

I'm probably doing something totally wrong, but I don't get, what...
I'm using SO from half an hour ago.
Ciao
-- 
 Frank Barknecht                               _ ______footils.org__

[SQLObject] default sort field

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

Hey again -

I've got a few suggestions, which I'm in the midst of implementing, and curious
whether people 1) see a need and 2) have alternate implementations for these.

Proposal:

Each SQLObject-derived class should be able to set a default ordering of the
results returned by .select()s, and (more importantly?) when accessed via a join.

Our need:

When retrieving objects for display, we often have one primary way that the list
should be viewed. This may be a natural field, such as alpha by name, or it may
be a field explicitly for client-defined order. This requires every request to
remember to order by this field, and makes control of the default order
de-centralized.

Proposed solution:

There's two possibilities: a column could say "I'm default order!", or it could
be a new class-level attribute like "_defaultOrderField = 'name'". We also need
to provide a mechanism to specify ascending or descending order.

We are implementing the first option, with two new class-level attributes:

  _defaultOrderField: must be a field defined in columns, or else it will be
reset to None. By default, None.

  _defaultOrderDirection: less likely to be used, either "DESC" or "ASC"
(case-insensitive). Default is 'ASC', as in SQL 'standard'.

This then requires modifications to .select, and to *Joins .performJoin methods
(as joins do not use .select). If _defaultOrderField is None, the SQL is
unchanged from current.

Thoughts?

- Luke

[SQLObject] Schema creation (in CVS)

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

I've implemented table creation in CVS.  The new class methods
createTable and dropTable do what you might expect.

However, to do this you have to give more information about your
columns.  The new Col module defines several subclasses for different
types of columns.  While constraints are used there, they aren't
actually checked yet.

The extra information about columns won't be required.  While it's
necessary for some features, I don't see any reason it would be required
for normal use.

  Ian

[SQLObject] ANN: SQLObject 0.2.1

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

SQLObject 0.2.1 has been released, fixing a caching bug, adding SQLite
support, and optimizing selects.

SQLObject is now a SourceForge project, with its own web page, mailing
list, and CVS repository.  Details at:

    http://sqlobject.org

What is SQLObject?
------------------

SQLObject is an object-relational mapper. It allows you to translate
RDBMS table rows into Python objects, and manipulate those objects to
transparently manipulate the database.



-- 
Ian Bicking  ia...@co...  http://colorstudy.com
4869 N. Talman Ave., Chicago, IL 60625  /  773-275-7241
"There is no flag large enough to cover the shame of 
 killing innocent people" -- Howard Zinn

Re: [SQLObject] alternateMethodName and creating SQL

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

On Thu, 2003-03-13 at 02:52, Luke Opperman wrote:
> > 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.
> 
> Agreed. There's no reason my code has to be in SQLObject (where it now resides)
> except for _table and _idName, yes. RelatedJoins still make life messy, because
> from a single object you can't tell what it's own column in the intersection
> table might be called, and so my first thought tells me you need an equivalent
> of needSet/classRegistry... I suppose those things (perhaps the entire list of
> generated tables, if i could see a point to it...) could be held in the
> DBConnection though.

I must admit I've only recently been learning Postgres, so I still
initially think about MySQL behavior, where relations are just
implicit.  So I'm not entirely sure of the correct behavior... if you
can give a small class definition and the appropriate class definition,
that'd help me.

> Hmm, now that I think about it some more, maybe I'm less opposed to simply
> switching imports. Right now, here create a local subclass of SQLObject called
> CoreObject which contains the _connection attribute. So switching Databases is
> in one spot for our entire library of objects. In the same way, at the
> CoreObject level we would import the proper (database-specific) set of Columns,
> and re-export (__all__) them from there. Then rather than each Object importing
> CoreObject from CoreObject, and * from PostgresColumns, they would simply
> import * from CoreObject, and hence get the database-specific connection and the
> database-specific columns. And if I needed to create a column type not
> pre-existing in SQLObject, I'd put it somewhere, and import it at CoreObject,
> and continue on in the same manner (when building specific objects one would
> never know or care where a column definition was really coming from).

I still don't like that.  Then in the docs I have to go through this
complex description of how you should set up your modules if you want to
change databases in the future.  The Right Way should also be the easy
way.

I'm strongly leaning towards putting database compatibility into the Col
subclasses.  It does mean that DBConnection won't be the sole location
for compatibility code, but I don't think that's too big a deal.

> > 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.
> 
> Is there some way that you use it where you change the _connection between
> instances / dynamically by re-assigning _connection during runtime. I can see
> that this is *possible* now, but I'm not seeing why you'd do such a thing
> (effectively, move databases/migrate data while leaving the objects in place in
> a live system...?) doesn't mean there's not a valid reason to, but elaborate for
> me. 

After you've started using the class changing _connection would do weird
stuff.  Though potentially you could pass the connection object into the
constructor (like you'd do with transactions)... that actually would
work right now, but strange things might start happening when you did
joins or otherwise mixed the objects.  Definitely bad stuff.

But what I want to be able to support is pre-instantiation dynamicism. 
For instance, an application might allow the user to select their
database in a configuration file or even something more interactive. 
Selecting the kind of database can go right alongside selecting the
connection parameters.

Also, by putting compatibility code into the Col subclasses, the
SQLObject user is shielded from that detail.  But if it's done with
imports then it's exposed.  More conceptual overhead.

> > 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.
> 
> Hmm. I'm not seeing how ValidatorConverters are too general yet. Where would the
> function you describe go? For us, using a list of validators is as easy as
> calling them (single function, just takes value) when a column is set (in
> SQLObject), and if a ValueError happens, it just bubbles up and away (SQLObject
> is not made more complicated except the need to run through the list).

Well, the whole converting is weird.  SQLBuilder does some conversion,
but it's very limited.  I'm not sure how conversion works into
everything else.  And ValidatorConverter already badly needs an
unconverter interface, among other additions, which will make it more
complicated.

The validation functions would be pretty much like ValidatorConverters,
and you'd use them like you're using them currently.  So you could do
something like Col(contraint=Range(10, 20)) where Range looked like:

class Range:
    def __init__(self, min, max):
        self.min, self.max = min, max
    def __call__(self, obj, col, value):
        if not value >= self.min and value < min:
            raise ValueError, "Not in range [%s, %s): %s" \
                % (self.min, self.max, value)

Other validators could be even simpler.  I suppose some columns, like
IntCol, would add their own validators in addition to the user-provided
validators.

> > 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.
> 
> Is planned from us, and should come about in the next few weeks. You're exactly
> right, there's very little code shared between them in our planned
> implementation, and I will admit that part of the reason we're using
> ValidatorConverters is to ease the transition. :) I'll send you details of this
> as it gets closer on my radar.

It would be easy to take these validators and use a wrapper to turn them
into ValidatorConverters.  Though there would still be issues of
converting values to and from strings, which this doesn't support.

  Ian

Re: [SQLObject] alternateMethodName and creating SQL

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

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

Agreed. There's no reason my code has to be in SQLObject (where it now resides)
except for _table and _idName, yes. RelatedJoins still make life messy, because
from a single object you can't tell what it's own column in the intersection
table might be called, and so my first thought tells me you need an equivalent
of needSet/classRegistry... I suppose those things (perhaps the entire list of
generated tables, if i could see a point to it...) could be held in the
DBConnection though.
 
> 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.

Yes, the NxM problem leads to having either separate imports (which you oppose
below, and I agree with you on the switching database reason, so screw that
idea), or explicit knowledge of what dbtypes or columntypes exist in the
opposite piece (DBConnection.createSql('String') lookup or
String.createSQL('postgresconnection') lookup or equivalent like your
postgresSqlCreate/mySqlCreate curry).

Hmm, now that I think about it some more, maybe I'm less opposed to simply
switching imports. Right now, here create a local subclass of SQLObject called
CoreObject which contains the _connection attribute. So switching Databases is
in one spot for our entire library of objects. In the same way, at the
CoreObject level we would import the proper (database-specific) set of Columns,
and re-export (__all__) them from there. Then rather than each Object importing
CoreObject from CoreObject, and * from PostgresColumns, they would simply
import * from CoreObject, and hence get the database-specific connection and the
database-specific columns. And if I needed to create a column type not
pre-existing in SQLObject, I'd put it somewhere, and import it at CoreObject,
and continue on in the same manner (when building specific objects one would
never know or care where a column definition was really coming from).

(Maybe you could take this one step further, so that it's "from
SQLObject.PostgresConnection import *", and that gives you the actual
PostgresConnection and the Columns. But that doesn't make it as easy for someone
to write their own extensions/additions to the list of available Columns. Hmm.)

Solves it from my end, in that there's still only one file I need to change
Database-specific stuff in (and I already had to change it there regardless of
DB-specific columns). But it's a slightly less pretty setup (requires a smart
local setup outside of SQLObject's direct control). But the current situation
can promote hard-to-change code too, if you were to define the DBConnection for
each Object in it's specific source file, you'd have to go through and modify
them all.


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

Is there some way that you use it where you change the _connection between
instances / dynamically by re-assigning _connection during runtime. I can see
that this is *possible* now, but I'm not seeing why you'd do such a thing
(effectively, move databases/migrate data while leaving the objects in place in
a live system...?) doesn't mean there's not a valid reason to, but elaborate for
me. 
 
> 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...

Understand and agree with your point, somewhat undecided. I'd say StringCol is
nicer, but that's just me.
 
> 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.

Hmm. I'm not seeing how ValidatorConverters are too general yet. Where would the
function you describe go? For us, using a list of validators is as easy as
calling them (single function, just takes value) when a column is set (in
SQLObject), and if a ValueError happens, it just bubbles up and away (SQLObject
is not made more complicated except the need to run through the list).
 
> 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.

Is planned from us, and should come about in the next few weeks. You're exactly
right, there's very little code shared between them in our planned
implementation, and I will admit that part of the reason we're using
ValidatorConverters is to ease the transition. :) I'll send you details of this
as it gets closer on my radar.

- Luke

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

Re: [SQLObject] alternateMethodName and creating SQL

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

Quoting Ian Bicking <ia...@co...>:

> It's a little fuzzy.  Some exception will be raised if nothing is
> returned.  Ambiguous results are silently ignored right now, which
> probably isn't good.  Ideally you should declare the column UNIQUE in
> your table schema.  If SQLObject ever generates table schemas (maybe not
> far off) then it would add UNIQUE based on this, but would otherwise let
> the database do the work of ensuring that.

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.

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')
      ]

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.

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.

That's all I've got to write tonight.

- Luke

Re: [SQLObject] sqlite support without patch

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

On Mon, 2003-03-03 at 07:13, Frank Barknecht wrote:
> Do you have an idea, why I'm always getting "p is not p2" in people.py
> regardless of the backend I'm using? And is this important?

I've been busy with a paper, but this should be fixed now.  Uniqueness
like that is depended on for much of the caching, so it's important that
it does work (at least if you use caching).

  Ian

Re: [SQLObject] alternateMethodName and creating SQL

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

On Wed, 2003-03-12 at 15:22, Luke Opperman wrote:
> Of course, maybe part of the reason this hasn't been implemented yet is because
> the semantics are more difficult that the implementation: What does the user
> expect if there are really more than one returned by that query? What if there
> are none? If alternateID is set in the Column, is it SQLObject's responsibility
> to enforce uniqueness?

It's a little fuzzy.  Some exception will be raised if nothing is
returned.  Ambiguous results are silently ignored right now, which
probably isn't good.  Ideally you should declare the column UNIQUE in
your table schema.  If SQLObject ever generates table schemas (maybe not
far off) then it would add UNIQUE based on this, but would otherwise let
the database do the work of ensuring that.

It might be good to define the exception that's raised when nothing's
found, though... KeyError?  IndexError?  ValueError?

  Ian

Re: [SQLObject] alternateMethodName and creating SQL

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

On Wed, 2003-03-12 at 15:22, Luke Opperman wrote:
> Col.alternateMethodName: apparently not implemented yet? I assume the
> implementation will look something like this (in the metaclass, while evaluating
> each Column object: I added it around line 195 in SQLObject.py):

Yes, I had implemented this in a previous incarnation of SQLObject, but
hadn't finished porting it to this version.  It's now implemented in
CVS.  

  Ian