sqlobject-discuss — All things SQLObject-related

[SQLObject] SQLObject delete queries

[Dave P. <dpo...@te...>]

How could one proceed to delete multiple records out of a database with
a single query?  I've been trying various methods but I seem to always
end up with an "unhashable" error.

desired query: DELETE * FROM accounts WHERE record_expiration <= NOW();

attempted SQLObject code:

Accounts.delete( (Accounts.q.record_expiration <= func.NOW()) )

the only way I've been able to delete the records was true a rather more
db intensive method:

for account in Accounts.select( (Accounts.q.record_expiration <=
func.NOW()) ):
  Accounts.delete( account.id )

Is there any better way to delete multiple rows?

Thanks in advance,
Dave.

Re: [SQLObject] SQLObject does not recognize sequence functions if _idName is used

[Sidnei da S. <si...@pl...>]

On Thu, Jul 31, 2003 at 02:53:28PM +0200, Luc Stepniewski wrote:
| But why does it work if I don't define the _idName attribute? Isn't there 
| another problem with the use of that attribute which may not be accounted 
| when looking for the primary key?

Hummm... it may be because when you set the attribute it uses a
SQLConstant, but thats only a wild guess. I haven't actually digged
the code.

| I've looked at Converters.py. I don't understand the way the information is 
| identified. When I look at it, it seems the information is identified only by 
| its basic types. 
| I added an output of obj.__class__ in sqlRepr() in Converters.py to see the 
| class name. The problem is that I get 'SQLObject.SQLBuilder.SQLConstant'.
| What am I supposed to do this it?
| SQLObject don't seem to support Bool postgreSQL type, so I looked at what was 
| returned by sqlRepr() when trying to interpret Bool keyword: it returns the 
| same thing: SQLObject.SQLBuilder.SQLConstant

Thanks for  tracking it. I registered a converter for SQLConstant and
a few more friends and added tests.

[]'s
-- 
Sidnei da Silva <si...@pl...>
dreamcatching :: making your dreams come true
http://dreamcatcher.homeunix.org

A programming language is low level when its programs require attention
to the irrelevant.

Re: [SQLObject] SQLObject does not recognize sequence functions if _idName is used

[Luc S. <luc...@ad...>]

On Thursday 31 July 2003 14:02, Sidnei da Silva wrote:
> On Thu, Jul 31, 2003 at 11:45:50AM +0200, Luc Stepniewski wrote:
> | Hello,
> |
> | I tried (on PostgreSQL 7.3.3) to use the _fromDatabase=True feature to be
> | able to use my already created tables (manually created, not with
> | SQLObject). SQObject chokes on the parsing of the primary key (which is a
> | serial defined type):
> |
> | ValueError: Unknown SQL builtin type: <type 'instance'> for
> | nextval('public.societe_soc_id_seq'::text)
>
> Its a bug yes. The solution is to find out what class
> 'nextval('public.societe_soc_id_seq'::text)' is (humm... I should put
> that in the error message) and register a converter to it. There are
> lots of examples in SQLBuilder.py and Converters.py, but I couldnt
> catch all the possible classes that would slipt through the sqlRepr
> function. I just added SQLObjectField yesterday, and a few more, but I
> didn't added Field for example as I couldn't see where it could be used.

But why does it work if I don't define the _idName attribute? Isn't there 
another problem with the use of that attribute which may not be accounted 
when looking for the primary key?

I've looked at Converters.py. I don't understand the way the information is 
identified. When I look at it, it seems the information is identified only by 
its basic types. 
I added an output of obj.__class__ in sqlRepr() in Converters.py to see the 
class name. The problem is that I get 'SQLObject.SQLBuilder.SQLConstant'.
What am I supposed to do this it?
SQLObject don't seem to support Bool postgreSQL type, so I looked at what was 
returned by sqlRepr() when trying to interpret Bool keyword: it returns the 
same thing: SQLObject.SQLBuilder.SQLConstant

Thanks,
Luc
-- 
Luc Stepniewski <ls...@ad...> <http://lstep.free.fr/>
Adelux - Securite, Linux  Public key: <http://lstep.free.fr/pubkey.txt>
Key BC0E3C2A fingerprint = A4FA466C68D27E46B427  07D083ED6340BC0E3C2A

Re: [SQLObject] SQLObject does not recognize sequence functions if _idName is used

[Sidnei da S. <si...@pl...>]

On Thu, Jul 31, 2003 at 11:45:50AM +0200, Luc Stepniewski wrote:
| Hello,
| 
| I tried (on PostgreSQL 7.3.3) to use the _fromDatabase=True feature to be able 
| to use my already created tables (manually created, not with SQLObject). 
| SQObject chokes on the parsing of the primary key (which is a serial defined 
| type):
| 
| ValueError: Unknown SQL builtin type: <type 'instance'> for 
| nextval('public.societe_soc_id_seq'::text)

Its a bug yes. The solution is to find out what class
'nextval('public.societe_soc_id_seq'::text)' is (humm... I should put
that in the error message) and register a converter to it. There are
lots of examples in SQLBuilder.py and Converters.py, but I couldnt
catch all the possible classes that would slipt through the sqlRepr
function. I just added SQLObjectField yesterday, and a few more, but I
didn't added Field for example as I couldn't see where it could be used.

[]'s
-- 
Sidnei da Silva <si...@pl...>
dreamcatching :: making your dreams come true
http://dreamcatcher.homeunix.org

A year spent in artificial intelligence is enough to make one believe in God.

[SQLObject] SQLObject does not recognize sequence functions if _idName is used

[Luc S. <luc...@ad...>]

Hello,

I tried (on PostgreSQL 7.3.3) to use the _fromDatabase=True feature to be able 
to use my already created tables (manually created, not with SQLObject). 
SQObject chokes on the parsing of the primary key (which is a serial defined 
type):

ValueError: Unknown SQL builtin type: <type 'instance'> for 
nextval('public.societe_soc_id_seq'::text)

The content of my DB:
db => \d societe
                                       Table "public.societe"
   Colonne  |          Type          |                        Modifications
--------------+----------------------+-------------------------------------------------------------
 soc_id       | integer                | not null default
                                    nextval('public.societe_soc_id_seq'::text)
 soc_nom   | character varying(200) |
Index: societe_pkey primary key btree (soc_id)

The code to access it:
======================
class societe(SQLObject):
    _connection = conn
    _fromDatabase = True
    _idName = 'soc_id'

p = societe.new(socNom='Adelux', socMail='co...@ad...',
                        socWeb='http://www.adelux.fr')
======================

What's strange is that *if* I set a simple primary key as 'id' in my table 
(the default that is looked for in SQLObject I guess), it works.  It's as if 
the '_idName' attribute didn't work and wasn't taken into account...

Is this a bug?

Thanks,
Luc Stepniewski
-- 
Luc Stepniewski <ls...@ad...> <http://lstep.free.fr/>
Adelux - Securite, Linux  Public key: <http://lstep.free.fr/pubkey.txt>
Key BC0E3C2A fingerprint = A4FA466C68D27E46B427  07D083ED6340BC0E3C2A

[SQLObject] Information about the restrictions of foreign keys

[Luc S. <luc...@ad...>]

Hello,

I'm trying to understand how SQLObject works, but I'm having problem 
understanding the generated tables:

If I want to create two tables, with one that has a relation one-to-many to 
the other, I define an attribute with the ForeignKey() method, but when I do 
this, I notice that there are no *real* restriction on the usage of that 
generated column in the database (I'm using PostgreSQL). Here are more 
explanations:

Let's take two example tables/classes:

=======
class societe(SQLObject):
    _connection = conn
    _columns=[
        StringCol('socNom', length=20, alternateID=True,
                       notNull=1,unique=True),
        StringCol('socMail', length=50, unique=True),
        ]
    secteurRegies = MultipleJoin('secteurRegie')

class secteurRegie(SQLObject):
    _connection = conn
    _columns=[]
    societe = ForeignKey('societe')

societe.createTable()
secteurRegie.createTable()
=======
Here are the created tables in PostgreSQL:
Query   : CREATE TABLE societe (
    id SERIAL PRIMARY KEY,
    soc_nom VARCHAR(20) NOT NULL UNIQUE,
    soc_mail VARCHAR(50) UNIQUE,
)
Query   : CREATE TABLE secteur_regie (
    id SERIAL PRIMARY KEY,
    societe_id INT
)
=======
And nothing more ! I would have expected that SQLObject would define 
societe_id to be a foreign key to societe.id.
That means I can do INSERTs in secteur_regie with non-existent societe_id 
values. That bothers me a lot.
I guess this is not too important if I'm using *only* SQLObject, but I'm 
accessing/INSERTing data from other sources programs, and I want to be sure 
that I don't have any incoherent data (and, btw, use the 'ON DELETE CASCADE' 
to remove automatically deleted records).

So, to summarize, Is it possible to implement a code like this:

ALTER TABLE secteur_regie
        ADD FOREIGN KEY (societe_id)
        REFERENCES societe (id) ON UPDATE CASCADE ON DELETE CASCADE;


Or maybe I haven't  understood how to declare foreign keys?

Thanks for your help,
Luc Stepniewski
-- 
Luc Stepniewski <ls...@ad...> <http://lstep.free.fr/>
Adelux - Securite, Linux  Public key: <http://lstep.free.fr/pubkey.txt>
Key BC0E3C2A fingerprint = A4FA466C68D27E46B427  07D083ED6340BC0E3C2A

[SQLObject] Multiple relations between two tables

[Andy T. <an...@ha...>]

Hi,

How do I define two (or more) relationships between two tables in my 
schema? I have a currencies table and a currency_rates table. Before I 
try and add the relationships their definitions are;

"""
class Currencies(SQLObject):
	currencyCode=StringCol(length=6)
	currencyDesc=StringCol(length=255)
	exchanges=MultipleJoin('Exchanges', joinColumn='currency_id')
	countries=MultipleJoin('Countries', joinColumn='currency_id')

class CurrencyRates(SQLObject):
	rateDate=DateTimeCol()
	rate=Col()
"""

Now I get confused when I add the two relationships (currency from and 
currency to). I tried;

"""
class Currencies(SQLObject):
	currencyCode=StringCol(length=6)
	currencyDesc=StringCol(length=255)
	exchanges=MultipleJoin('Exchanges', joinColumn='currency_id')
	countries=MultipleJoin('Countries', joinColumn='currency_id')
	ratesFrom=MultipleJoin('CurrencyRates', joinColumn='currency_from_id')
	ratesTo=MultipleJoin('CurrencyRates', joinColumn='currency_to_id')

class CurrencyRates(SQLObject):
	rateDate=DateTimeCol()
	rate=Col()
	currencyFrom=ForeignKey('Currencies')
	currencyTo=ForeignKey('Currencies')
"""

But this causes an error on import;

"""
Traceback (most recent call last):
   File "classes.py", line 51, in ?
     class CurrencyRates(SQLObject):
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 233, in __new__
     newClass.addColumn(column)
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 503, in addColumn
     '_SO_class_%s' % column.foreignKey)
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 102, in addNeedSet
     getattr(obj, attr)(cls)
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 404, in __new__
     val._init(id, connection, selectResults)
   File "C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLObject.py", 
line 662, in _init
     selectResults = (connection or 
self._connection)._SO_selectOne(self, dbNames)
   File 
"C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\DBConnection.py", line 
267, in _SO_selectOne
     return self.queryOne("SELECT %s FROM %s WHERE %s = %s" %
   File 
"C:\PROGRA~2\PYTHON22\Lib\site-packages\SQLObject\SQLBuilder.py", line 
126, in sqlRepr
     raise ValueError, "Unknown SQL builtin type: %s for %s" % \
ValueError: Unknown SQL builtin type: <class 
'SQLObject.SQLObject.MetaSQLObject'> for <class '__main__.Currencies'>
"""

Any suggestions? Apologies if this question has already been answered.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

[SQLObject] New data types

[Javier R. <jav...@Ho...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,
	I was trying to make a BoolCol class just to learn how things work.
	I grabed the StringCol and SOStringCol classes, changed things and the
generated SQL was right. This was very easy to make!!
	Now I was wondering if there is a way I can define filters for the
data, both in it's way in and out of the DB, so that I can take whatever
the Python type is and change it to what the DB wants. In this case
change the int to a '1' or the like for SQLite and 'TRUE' for PostgreSQL
(don't know MySQL) and the inverse operation when read from the DB. Is
there a way to do this?

Thanks,
	Javier

PS: I'm developing an app from scratch, should I be using the SQLObject
from CVS? Has it's structure changed much from v0.4?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/JhJi8XQC840MeeoRAnrpAKCPxQAgTjONiYfl7ssQ3TN3qXHQhwCaA4ly
eMRgVkId6zZdbkSDhM6+5vY=
=Lf4C
-----END PGP SIGNATURE-----

Re: [SQLObject] Dot q Bug

[Sidnei da S. <si...@pl...>]

On Mon, Jul 28, 2003 at 05:08:26PM -0400, Brad Bollenbach wrote:
| I noticed a rather surprising bug whilst running from the latest CVS
| today:
|   File "/home/bradb/src/Python/cvs_modules/SQLObject/Converters.py", line 99, in sqlRepr
|     raise ValueError, "Unknown SQL builtin type: %s for %s" % \
| ValueError: Unknown SQL builtin type: <type 'instance'> for basic_http_monitor_config.path
| 
| Strange but true.
| 
| Anyone else noticed dot magic to be broken?

My fault. I seem to have introduced this with the converters
refactoring. Ill check in a fix asap.

[]'s
-- 
Sidnei da Silva <si...@pl...>
dreamcatching :: making your dreams come true
http://dreamcatcher.homeunix.org

//GO.SYSIN DD *, DOODAH, DOODAH

[SQLObject] Dot q Bug

[Brad B. <br...@bb...>]

I noticed a rather surprising bug whilst running from the latest CVS
today:

Python 2.3c1 (#2, Jul 22 2003, 14:59:44) 
[GCC 2.95.4 20011002 (Debian prerelease)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from xalertdbi import BasicHTTPMonitorConfig
>>> b = BasicHTTPMonitorConfig.select("path = '/'")
>>> print list(b)
[<BasicHTTPMonitorConfig 1 monitorID=1 keywords='hello,world' path='/' port=80 md5checksum=None>]
>>> b = BasicHTTPMonitorConfig.select(BasicHTTPMonitorConfig.q.path == '/')
>>> print list(b)
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/home/bradb/src/Python/cvs_modules/SQLObject/DBConnection.py", line 134, in iterSelect
    query = self.queryForSelect(select)
  File "/home/bradb/src/Python/cvs_modules/SQLObject/DBConnection.py", line 172, in queryForSelect
    return q + self.whereClauseForSelect(select)
  File "/home/bradb/src/Python/cvs_modules/SQLObject/DBConnection.py", line 176, in whereClauseForSelect
    q = str(select.clause)
  File "/home/bradb/src/Python/cvs_modules/SQLObject/SQLBuilder.py", line 157, in __str__
    return self.sqlRepr()
  File "/home/bradb/src/Python/cvs_modules/SQLObject/SQLBuilder.py", line 219, in sqlRepr
    return "(%s %s %s)" % (sqlRepr(self.expr1), self.op, sqlRepr(self.expr2))
  File "/home/bradb/src/Python/cvs_modules/SQLObject/Converters.py", line 99, in sqlRepr
    raise ValueError, "Unknown SQL builtin type: %s for %s" % \
ValueError: Unknown SQL builtin type: <type 'instance'> for basic_http_monitor_config.path

Strange but true.

Anyone else noticed dot magic to be broken?

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Additionnal datas on joins

[Brad B. <br...@bb...>]

On Mon, Jul 28, 2003 at 12:25:56PM -0300, Bruno Trevisan wrote:
> Hi
> 
> > Which -- by the way -- I need to be able to do *right* *now*. :) I'm
> 
>     +1 for me too.
> 
> > willing to help on getting the tests that need to pass written, and
> > writing the code that passes them once we, the users of SQLObject,
> > decide what is the most natural way to express composition.
> 
> 
> what about:
> 
>     class Foo(SQLObject):
>         foo = StringCol(dbName='foo')
>         bar = ForeignKey([IntCol(dbName='bar_id'),
>                             IntCol(dbName='bar_idd')])
> 
> So this would make room for keys of type other than integer and allow us
> to have a variable number of keys in the composite foreignKey.

The problem is we're not talking about a column here; we're talking
about how to say "use these two (or three, or N) columns as the
primary key". It doesn't make sense to assign this to a single
attribute (attributes map one-to-one with SQL table columns, unless
you've added some normal Python properties to your class as well), but
rather to use meta-fu to let SQLObject know what we mean.

The semantics should be unified, of course, so that you'd use the same
semantics for single-column keys as for multi-column keys. I see no
harm in continuing to follow the rule as well that if no key is
defined, an implicit ID column is generated (as long as it's
documented, which it is).

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Additionnal datas on joins

[Bruno T. <bt...@as...>]

Hi

> Which -- by the way -- I need to be able to do *right* *now*. :) I'm

    +1 for me too.

> willing to help on getting the tests that need to pass written, and
> writing the code that passes them once we, the users of SQLObject,
> decide what is the most natural way to express composition.


what about:

    class Foo(SQLObject):
        foo =3D StringCol(dbName=3D'foo')
        bar =3D ForeignKey([IntCol(dbName=3D'bar_id'),
                            IntCol(dbName=3D'bar_idd')])

So this would make room for keys of type other than integer and allow us
to have a variable number of keys in the composite foreignKey.

[]'s

 Bruno Trevisan

 bt...@as...          |=3D| Async Open Source |=3D| D. Alexandrina, 253=
4
 http://www.async.com.br/ |=3D| +55 16 261-2331   |=3D| 13566-290
                          |=3D| +55 16 9781-8717  |=3D| S=E3o Carlos, SP, B=
rasil

Re: [SQLObject] Additionnal datas on joins

[Brad B. <br...@bb...>]

On Mon, Jul 28, 2003 at 09:41:12AM -0400, Brad Bollenbach wrote:
> On Mon, Jul 28, 2003 at 10:31:01AM +0200, Fran?ois Girault wrote:
> > Hi all,
> > 
> > in some cases, there's additionnal datas in relation table. For example, the rank if the relation is ordered, or a lock object for edition... 
> > 
> > It's very common to see that.
> 
> Yes, this is the use case for composite keys. :)

Which -- by the way -- I need to be able to do *right* *now*. :) I'm
willing to help on getting the tests that need to pass written, and
writing the code that passes them once we, the users of SQLObject,
decide what is the most natural way to express composition.

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Additionnal datas on joins

[Brad B. <br...@bb...>]

On Mon, Jul 28, 2003 at 10:31:01AM +0200, Fran?ois Girault wrote:
> Hi all,
> 
> in some cases, there's additionnal datas in relation table. For example, the rank if the relation is ordered, or a lock object for edition... 
> 
> It's very common to see that.

Yes, this is the use case for composite keys. :)

> So I'm thinking about how to implement this :
> 
>  - one (ugly but usuable) way to do that without modifying SQLObject is to describe the join like an entity with 2 foreign keys, and then specifying the additionnal columns as usual.

I don't know of a way in SQLObject to force two columns to be unique,
except to define semantics for composite keys.
 
>  - a cleaner way would be to add the columns to the join declaration, and then attach these columns to the entities retrevied when performing the join.
> 
> so, SQLObject guys, what do you think about that ?

The cleanest way is to figure out how we want to define composite
primary keys.

Like this?

class SiteMonitor(SQLObject):
    site = ForeignKey("Site")
    monitor = ForeignKey("Monitor")

    _primary_key = (site, monitor)

or this perhaps?

class SiteMonitor(SQLObject):
    site = ForeignKey("Site")
    monitor = ForeignKey("Monitor")

    PrimaryKey(site, monitor)

It would be nice if specifying the primary key looked somewhat like
specifying the foreign key (like the latter example), but in other
ways, the former example is more SQLObjectish in its use of _foo's for
meta information about the table.

I think either of these syntaxes would be useable. What do you guys
think?

--
Brad Bollenbach
BBnet.ca

[SQLObject] Additionnal datas on joins

[G. <fra...@cl...>]

Hi all,

in some cases, there's additionnal datas in relation table. For example, th=
e rank if the relation is ordered, or a lock object for edition...=20

It's very common to see that.

So I'm thinking about how to implement this :

 - one (ugly but usuable) way to do that without modifying SQLObject is to =
describe the join like an entity with 2 foreign keys, and then specifying t=
he additionnal columns as usual.

 - a cleaner way would be to add the columns to the join declaration, and t=
hen attach these columns to the entities retrevied when performing the join.

so, SQLObject guys, what do you think about that ?

Fran=E7ois

ps : I'll upload soon my customized version in the CVS, with support for st=
ring primary key and ordered join. I'll post when ready ;)

Re: [SQLObject] Composed Primary key

[G. <fra...@cl...>]

On Thu, 24 Jul 2003 14:06:49 -0400
Brad Bollenbach wrote:

> (Sorry for the delayed response. I briefly skimmed this before, and
> now that I know I need this, I'd like to contribute)
> 
> I gather you've already implemented this. If so, how?
> 

I've managed to delay this feature in my project, because composite keys are known to slow down db perfs, especially on join operation. 

My project manager always wants this feature, but I'll implement it in some months...

[SQLObject] Forcing cache refresh of MultipleJoins

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

I have a one-to-many relationship of the form:

class A(SQLObject):
    ...
    things = MultipleJoin("B")

class B(SQLObject):
    ...
    aThing = ForeignKey("A")


If I check what things are in A via something like aObj.things, then 
delete one of the B objects that appears in the list directly via 
bObj.destroySelf(), executing the aObj.things again raises a
SQLObjectNotFound error for the just deleted B instance.

Executing aObj.things a second time works though. So there appears to be 
some kind of cache synchronization issue at play here. Is there some way 
to force an object to refresh it's cached values?

...Edmund.

[SQLObject] SQLObject newbie

[Javier R. <jav...@Ho...>]

	Hi, I started using SQLObject today and I like it a lot.
	There are a couple of questions I have for which I could find no
answers on the mailing list.
	First, why are transactions not supported in SQLite? I believe the DB
has that ability.
	Second, I could not get the code in the attached file (prueba.py)
working. Is what I'm trying to do there forbiden?
	Third, the following code gets a type error when run in the interactive
shell:

		import SQLObject
		SQLObject.__dict__

	Finally, thank you all for your patience and help.

Good luck,
	Javier

Re: [SQLObject] id, str(id) and duplicate objects in the cache

[Brad B. <br...@bb...>]

On Sun, Jul 20, 2003 at 04:21:47PM +0100, Matt Goodall wrote:
> I just found an interesting problem due to programmer (i.e. me) error
> but it leaves the SQLObject caches damaged so I wondered if SQLObject
> should cope better.
> 
> The problem is probably best demonstrated by (untested) example with a
> description of what happens inside SQLObject's caches:
> 
>   id = Obj.new(col1='a', col2='b')
>   # added to cache with key == int(1)
> 
>   o = Obj(id)
>   # found in cache with key == int(1)
> 
>   o = Obj(str(id)) # note the cast to a string
>   # reads from database, adds to cache with key == str(1)
>   # Eek, two objects now in cache!
> 
>   o.col1, o.col2 = o.col2, o.col1
>   # writes change to db, only object str(id) in cache matches
> 
>   o = Obj(str(id))
>   # found in cache with key == str(1)
> 
>   o = Obj(id)
>   # found in cache with key == int(1) but bad data
> 
> Lovely isn't it, and all because I stored the object's id in a hidden
> form field ;-). I won't tell you how long it took to work out what was
> happening!
> 
> My app is working again now but should SQLObject have let this happen?

No. :)
 
> The obvious thing to do is to force the id to int() inside the Obj
> constructor but that would break any attempt to allow alphanumeric ids.
> So, perhaps a better solution is to always use a string as the cache
> keys?

Hmmm...I think there is a need here for a "primarykeygeddon" (to
borrow a Zope 3ism). The way in which SQLObject lets you define PK's
is inevitably overlapping with keys to use for caching.

Ideally, like Python dictionaries, any immutable type should be able
to be used as a key for a table, IMHO. So, there are use cases for all
of the following (and many more, but just to list a few different
examples to be mindful of...) to be primary keys:

* dates
* ints
* strings
* a tuple of columns (each of which may be different types)

It seems that the right thing to do here would be to use the same type
in the cache key as that being used for the PK of the SQLObject. It
may be useful to force the correct type to be passed into the
constructor when retrieving objects as well (I see little benefit in
implicit type conversion for this).

So, perhaps we should address composites at the same time as deciding
which keys to use in the cache.

So:

What do you think about forcing the correct type to be passed into the
constructor? (I think it's reasonable; 1 and "1" are two different
keys in a dictionary)

How do you want to be able to define composite keys in SQLObjects?

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Microsoft SQL Server support ?

[Brad B. <br...@bb...>]

On Fri, Jul 25, 2003 at 03:47:13PM -0400, Brad Bollenbach wrote:
> On Sat, Jul 19, 2003 at 01:01:22AM +0200, Frank Barknecht wrote:
> > Hallo,
> > Jim Vickroy hat gesagt: // Jim Vickroy wrote:
> > 
> > > Is there any plan to support Microsoft SQL Server?  If so, when?  Any
> > > suggestions on how I might start to add this support?
> > 
> > Last time I looked, you just had to adapt DBMConnection to make
> > SQLObject work with another backend. It's easy, just give it a go ;)
> 
> Surely you mean DBConnection? :)

Erm, to clarify.

DBConnection.py is the file to look in; DBAPI is the class you want to
inherit from to add support for a new database. There are four good
examples in DBConnection.py.

HTH,

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Microsoft SQL Server support ?

[Brad B. <br...@bb...>]

On Sat, Jul 19, 2003 at 01:01:22AM +0200, Frank Barknecht wrote:
> Hallo,
> Jim Vickroy hat gesagt: // Jim Vickroy wrote:
> 
> > Is there any plan to support Microsoft SQL Server?  If so, when?  Any
> > suggestions on how I might start to add this support?
> 
> Last time I looked, you just had to adapt DBMConnection to make
> SQLObject work with another backend. It's easy, just give it a go ;)

Surely you mean DBConnection? :)

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Using SQLObjects as Abstract Classes

[Brad B. <br...@bb...>]

On Fri, Jul 25, 2003 at 01:19:02PM -0500, Ian Bicking wrote:
> On Fri, 2003-07-25 at 12:27, Brad Bollenbach wrote:
> > > If you have that class layout, you are supposing that there will be
> > > other subclasses of Monitor.  However, each of these subclasses must be
> > > a separate table, or great confusion will ensue.
> > 
> > There will only be a single monitor table. Monitors store a common set
> > of properties. The only thing that's different is how they behave when
> > you say "do your monitor check".
> 
> Then make that into a multiple-class object, Checker.  Like:
> 
> class Checker(object):
>     def __init__(self, monitor):
>         self.monitor = monitor
>     def check(self):
>         assert 0, "Subclass responsibility"
> class HTTPChecker(Checker):
>     def check(self): ...
> class MD5HTTPChecker(HTTPChecker):
>     def check(self): ...
> 
> checkers = {'http': HTTPChecker, 'md5': MD5HTTPChecker}
> 
> class Monitor(SQLObject):
>     monitorType = StringCol(length=10)
>     def _get_checker(self):
>         return checkers[self.monitorType](self)
> 
> 
> Voila!  Composition beats inheritance!

Indeed, this looks like the best way to do it (whilst avoiding any
ugly hacks).

Thanks. :)

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Using SQLObjects as Abstract Classes

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

On Fri, 2003-07-25 at 12:27, Brad Bollenbach wrote:
> > If you have that class layout, you are supposing that there will be
> > other subclasses of Monitor.  However, each of these subclasses must be
> > a separate table, or great confusion will ensue.
> 
> There will only be a single monitor table. Monitors store a common set
> of properties. The only thing that's different is how they behave when
> you say "do your monitor check".

Then make that into a multiple-class object, Checker.  Like:

class Checker(object):
    def __init__(self, monitor):
        self.monitor = monitor
    def check(self):
        assert 0, "Subclass responsibility"
class HTTPChecker(Checker):
    def check(self): ...
class MD5HTTPChecker(HTTPChecker):
    def check(self): ...

checkers = {'http': HTTPChecker, 'md5': MD5HTTPChecker}

class Monitor(SQLObject):
    monitorType = StringCol(length=10)
    def _get_checker(self):
        return checkers[self.monitorType](self)


Voila!  Composition beats inheritance!

> > Python inheritance simply doesn't map to RDBMS tables.  There are things
> > that can be called inheritance among RDBMS tables, but they aren't the
> > same thing.  So SQLObject just takes a one-class-one-table approach. 
> > You can still use inheritance, and a class can be abstract so long as
> > you don't use it, but it's still one-class-one-table.  It's improper to
> > use _table = 'monitor' in each class, because that breaks that rule.  If
> > you have one table you shouldn't have more than one class that you use
> > with that table.
> 
> Hrmph.
> 
> I can see where you're coming from, but perhaps some thought could be
> spent on figuring out how to make that "one class" (per table) be an
> abstract class (so, I kind of really only do have class per table,
> because StatusMonitor, KeywordMonitor, etc are just there to specify
> differences in behaviour, not in attributes. That "one class" is my
> abstract base.)

It just wouldn't work.  What are you going to do, fetch a generic
Monitor instance, check its type, then refetch a specific instance?  Or
do a direct query to find the type, then instantiate the object with the
correct class?  It just doesn't make sense.  It also is going to cause
all sorts of weird structural problems when you do need more tables. 
With the above technique you can add tables fairly easily.  Say you want
to create a md5_checker table (to store the MD5 hash).

class MD5Checker(HTTPChecker, SQLObject):
    def setMonitor(self, monitor):
        HTTPChecker.__init__(self, monitor)
    md5 = StringCol(length=128)

function md5CheckerGetter(self, monitor):
    inst = MD5Checker.selectBy(monitor=monitor)[0]
    inst.setMonitor(monitor)
    return inst

checkers['md5'] = md5CheckerGetter


You don't have to create tables for every checker, just the one that
needs it.  You don't have to fiddle with inheritance of the Monitor
class.  

Someon, who I can't remember, and who's link I've lost, gave a link to a
good article on inheritance and how to map them.  The stuff I've written
above follows one of them (one table for each class, including abstract
classes), while standard inheritance in SQLObject is one table per
concrete class.  Implementing the former directly in SQLObject is, IMHO,
a lot of work for nothing, since multiple classes is superior in Python
just like multiple tables are superior in the database (IMNSHO).  It may
not seem easier to implement (though I don't think it's any harder), but
I think it will be much easier to maintain, and result in more readable
code.

  Ian

Re: [SQLObject] Using SQLObjects as Abstract Classes

[Brad B. <br...@bb...>]

On Fri, Jul 25, 2003 at 11:08:36AM -0500, Ian Bicking wrote:
> On Fri, 2003-07-25 at 08:22, Brad Bollenbach wrote:
> > On Thu, Jul 24, 2003 at 08:57:19PM -0500, Ian Bicking wrote:
> > > On Thu, 2003-07-24 at 14:42, Brad Bollenbach wrote:
> > > > Hi all,
> > > > 
> > > > How can I use an SQLObject-derived class as an abstract base class?
> > > 
> > > Hmm... I'm not exactly clear what an abstract base class is.  Is there a
> > > particular problem you are trying to solve?
> > 
> > An abstract base class is a class that's not intended to be directly
> > instantiated, instead defining methods that are intended to be
> > overridden in derived classes.
> 
> Oh, right right, I don't know why the terminology was confusing me.
> 
> You'll have to do something like:
> 
> class MonitorBase:
>     ...
> 
> class Monitor(SQLObject, MonitorBase):
>     ...

This still doesn't solve the problem though. I would have to do it
more like:

class StatusMonitor(SQLObject, MonitorBase)

class KeywordMonitor(SQLObject, MonitorBase)

etc

(which still doesn't quite work, because I'm getting separate tables
for each, which I don't want)

Eeek.

> > The problem I'm trying to solve is a web monitoring system:
> > 
> > MonitorBase
> >      | | |
> >      | | -> StatusMonitor
> >      |  --> KeywordMonitor
> >       ----> MD5ChecksumMonitor

[snip] 

> If you have that class layout, you are supposing that there will be
> other subclasses of Monitor.  However, each of these subclasses must be
> a separate table, or great confusion will ensue.

There will only be a single monitor table. Monitors store a common set
of properties. The only thing that's different is how they behave when
you say "do your monitor check".

> Python inheritance simply doesn't map to RDBMS tables.  There are things
> that can be called inheritance among RDBMS tables, but they aren't the
> same thing.  So SQLObject just takes a one-class-one-table approach. 
> You can still use inheritance, and a class can be abstract so long as
> you don't use it, but it's still one-class-one-table.  It's improper to
> use _table = 'monitor' in each class, because that breaks that rule.  If
> you have one table you shouldn't have more than one class that you use
> with that table.

Hrmph.

I can see where you're coming from, but perhaps some thought could be
spent on figuring out how to make that "one class" (per table) be an
abstract class (so, I kind of really only do have class per table,
because StatusMonitor, KeywordMonitor, etc are just there to specify
differences in behaviour, not in attributes. That "one class" is my
abstract base.)

FWIW, the architecture of this app may change so that each monitor has
its own table (/maybe/), but in any case, I can see many uses for
abstract base SQLObjects.

What do you guys think about using abstracts as SQLObjects? Can there
be a solution?

--
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Using SQLObjects as Abstract Classes

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

On Fri, 2003-07-25 at 08:22, Brad Bollenbach wrote:
> On Thu, Jul 24, 2003 at 08:57:19PM -0500, Ian Bicking wrote:
> > On Thu, 2003-07-24 at 14:42, Brad Bollenbach wrote:
> > > Hi all,
> > > 
> > > How can I use an SQLObject-derived class as an abstract base class?
> > 
> > Hmm... I'm not exactly clear what an abstract base class is.  Is there a
> > particular problem you are trying to solve?
> 
> An abstract base class is a class that's not intended to be directly
> instantiated, instead defining methods that are intended to be
> overridden in derived classes.

Oh, right right, I don't know why the terminology was confusing me.

You'll have to do something like:

class MonitorBase:
    ...

class Monitor(SQLObject, MonitorBase):
    ...

> The problem I'm trying to solve is a web monitoring system:
> 
> MonitorBase
>      | | |
>      | | -> StatusMonitor
>      |  --> KeywordMonitor
>       ----> MD5ChecksumMonitor
> 
> So, I want to be able to do something like (untested code):
> 
> class MonitorBase(SQLObject):
>     """I'm the base class from which all monitors shall
>     be derived.
>     """
> 
>     created_date = DateTimeCol(notNone = True)
>     last_run_date = DateTimeCol(notNone = True)
>     active = IntCol()
>     ...
> 
>     def doMonitorCheck(self):
>         """Run the check for this monitor."""
>         raise NotImplementedError("doMonitorCheck is an abstract method.")
> 
> 
> class StatusMonitor(MonitorBase):
>     """I monitor websites for the 200 OK status."""
> 
>     def doMonitorCheck(self):
>         ...logic to check for 200 OK...
> 
> Except this breaks, because any property access on StatusMonitor tries
> to look for a table called status_monitor. Unless I specify _table =
> 'monitor' in every derived class, which seems a bit clunky.

If you have that class layout, you are supposing that there will be
other subclasses of Monitor.  However, each of these subclasses must be
a separate table, or great confusion will ensue.

Python inheritance simply doesn't map to RDBMS tables.  There are things
that can be called inheritance among RDBMS tables, but they aren't the
same thing.  So SQLObject just takes a one-class-one-table approach. 
You can still use inheritance, and a class can be abstract so long as
you don't use it, but it's still one-class-one-table.  It's improper to
use _table = 'monitor' in each class, because that breaks that rule.  If
you have one table you shouldn't have more than one class that you use
with that table.

  Ian