sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Transactions

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

On Thu, 2003-07-10 at 17:32, Matt Goodall wrote:
> > I added this fix to CVS, but my transaction test I added isn't working
> > like I would think it should.  Could you look at it?
> 
> I just updated from CVS and the problem is still occurring. I also can't
> find the transaction test you mention.

I think SF CVS is slow today.  Look for this line in
Transaction.__init__(DBConnection.py:333):
        self.cache = CacheSet(cache=dbConnection.doCache)

Once you get it you'll know you have a more current CVS.

  Ian

Re: [SQLObject] Transactions

[Matt G. <ma...@po...>]

On Thu, 2003-07-10 at 20:28, Ian Bicking wrote:
> On Thu, 2003-07-10 at 10:06, Matt Goodall wrote:
> > Is the transaction support working in 0.4 and CVS? I just tried the 
> > following:
> > 
> >     conn = PostgresConnection(...)
> >     trans = conn.transaction()
> >     print Person(1, trans)
> > 
> > and got "AttributeError: 'Transaction' object has no attribute 'cache'"
> > 
> > A quick look at the code implies that the Person ctor was expecting a 
> > connection, not a transaction.
> 
> No, I just extended the DBConnection interface to include a cache, and
> forgot to extend Transaction too.

Ah, that would explain it ;-).

> I added this fix to CVS, but my transaction test I added isn't working
> like I would think it should.  Could you look at it?

I just updated from CVS and the problem is still occurring. I also can't
find the transaction test you mention.

> (Maybe I have to turn off autocommit or something...)

Almost certainly, PostgreSQL and SQLite both have autocommit on by
default.

- Matt

-- 
Matt Goodall, Pollenation Internet Ltd
w: http://www.pollenation.net
e: ma...@po...

Re: [SQLObject] Best way to order columns

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

On Thu, 2003-07-10 at 14:37, Mark Melvin wrote:
> Yes, of course.  Perhaps I am using things in a bad way.  In order to 
> generate column labels for a list control I am iterating through the 
> SQLObject._columns attribute, pulling out .kw['name'] for each one.  It is 
> a list in a particular order, which I assume is used to generate the 
> select() statement because the order appears to match.  It does not appear 
> to match the order in which the columns are created in my particular class. 
>  I guess I should look at the source to see how this _columns list is 
> created and go from there.

It is probably sorted by the order of the hashes of the strings used to
name the objects, i.e., effectively unsorted.  When you do:

class Something(SQLObject):
    fname = Col()
    lname = Col()

SQLObject (MetaSQLObject to be particular) looks through the classes
*dictionary*, which is of course unordered.  If you want to order them
more naturally, you'll have to add another attribute that contains that
ordering (like order=['fname', 'lname']), or do it alphabetically or
something.

I suppose a slightly hackish solution would be to make Col objects
remember what order they were instantiated in.  I'm not sure if that's
evil or convenient.

> Cool.  I have re-architected my LEFT JOINed approach to be a single table 
> and I am finding SQLObject pretty sweet.  Simplifying my first case helped 
> me a lot to get started, and it should be easier to extend to more 
> complicated cases.  I found the documentation - while good, is missing a 
> lot of the little things.  I found myself doing a dir() on object a lot to 
> figure out what was in there.

A lot of stuff that is not documented is an unstable interface.  You're
welcome to use it of course (I'm a firm opponent of double-underscore
private variables), but be warned it may change.  If there's something
in particular that you think is important, bring it up and perhaps we
can make a stable interface for it.

It sounds like you are doing some introspection, which is something in
particular that I have put off until I have a better feel for how it
should work.

  Ian

Re: [SQLObject] Cached Objects

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

On Thu, 2003-07-10 at 10:49, Matt Goodall wrote:
> I hope this has not been discussed in the archives but I cannot get to 
> them at the moment ...
> 
> Here's my understanding of SQLObject caching - The caching takes place 
> at object and attribute level. Objects are cached by class+id and the 
> caches (there's actually one for each class) belong to the connection. 
> Attributes are cached per object by wrapping the SQL getter in another 
> getter that checks whether the attribute exists first. All fairly 
> straightforward ... for a metaclass ;-). Both of these forms of caching 
> can be turned off.

Right, I think ;)  Or more simply:

Instances can cache their column values (controlled via _cacheValues). 
The values are cached indefinitely (though a .sync() method is probably
called for).

Connections cache the instances, always.  The degree to which they cache
can be controlled (with cache=False to the connection constructor).  But
if there is already an instance in memory, then that instance is
returned instead of creating a new one.  cache=True just means that the
object will be kept around a little while even after it might have been
garbage collected.

However, transactions are implemented by using multiple connections, so
the cache is per-transaction.  When a transaction finishes, its cached
objects just disappear or something (umm... not well defined).

> In a typical Apache 1.x style web application where there are multiple 
> processes, the connection's caching has to be disabled (in general) to 
> avoid the problems inherent with multiple, independent caches. There 
> will, however, be some SQLObjects that are good candidates for caching 
> even in this configuration.

All instances should be cacheable regardless, no?  Creating an instance
(not via .new()) doesn't even imply any SQL statements, so long as
_cacheValues=False.

> It seems to me that it would be useful to be able to turn off object 
> level caching on a class-by-class basis. That is, the connection is 
> created with caching enabled but the SQLObjects are never actually cached.
> 
> I haven't thought this through well enough but it may also be useful to 
> cache objects for the lifetime of a transaction. When loading an object 
> via a transaction it would first look to see if the connection had the 
> object in its cache and if so return that otherwise it would load the 
> object from the database and store it in the transation's caches. The 
> transaction's caches would be flushed, probably on commit or rollback. 
> This may not be worth the effort but in a multi-process application it 
> may be the only form of caching that is viable.

Transactions were supposed to have caches too, I just forgot.

> As a side issue, I could personally never imagine turing off the 
> attribute caching (_cacheValues=False) as the number of SQL queries is 
> quite frightening. Do people really use SQLObject like this and if so, 
> why and how do you cope with the load on the database?

Yes, could be rather, um, verbose.  Perhaps more manual control, via a
sync method, would work better.  Though I suppose with a sync method,
the sharing of instances is a problem, since you expect your object to
be stable except at points that you sync it, but if another thread has
the object as well it could be sync'ed part way through.  In a
multi-process environment this wouldn't matter so much.

The use of a Transaction, even for a
non-transaction-supporting-database, is probably the best thing here,
since you somehow have to indicate which thread you are in, and the
transaction does that (implicitly), and it's actually just a thin
wrapper on the normal connection.

Caching columns in a multi-process environment probably calls for
locking of some sort, perhaps the optimistic locking which has been
brought up here before.  Otherwise concurrency is a little fuzzy.

  Ian

Re: [SQLObject] Best way to order columns

[Mark M. <ma...@di...>]

On 10 Jul 2003 14:18:07 -0500, Ian Bicking <ia...@co...> wrote:

> On Wed, 2003-07-09 at 13:01, Mark Melvin wrote:
>> I have two questions:
>>
>> 1) What is the best way force a particular order to the columns returned 
>> by a select()?
>>
>> By this I do not mean sorting records - I mean the order of the columns.
>>
>> So for a given select(), SQLObject returns the columns in the order they 
>> appear in the database.  Is there a way to specify a new default in 
>> which they are returned, or do I have to reorder them myself after I do 
>> a select()?
>
> Columns aren't ordered as they are returned -- full objects with
> attributes for the columns are returned.
>

Yes, of course.  Perhaps I am using things in a bad way.  In order to 
generate column labels for a list control I am iterating through the 
SQLObject._columns attribute, pulling out .kw['name'] for each one.  It is 
a list in a particular order, which I assume is used to generate the 
select() statement because the order appears to match.  It does not appear 
to match the order in which the columns are created in my particular class. 
 I guess I should look at the source to see how this _columns list is 
created and go from there.


>> 2) In terms of sorting records, it appears that when I specify a join, I 
>> can use the orderBy keyword and pass it a list and it will happily sort 
>> the items based on the order of the items in the list (at least it 
>> appears to work that way).  However the _defaultOrder class variable 
>> will not take a list.  Is there any way to specify more than one 
>> parameter to the SQL that is generated for the "ORDER BY" clause as the 
>> default for select()?
>
> There was supposed to be :(  I guess I forgot the last part of
> implementing it.  Bad me for not making the right test.  CVS has this
> fix.  I think I'll put out a 0.4.1 before too long as well.
>

Cool.  I have re-architected my LEFT JOINed approach to be a single table 
and I am finding SQLObject pretty sweet.  Simplifying my first case helped 
me a lot to get started, and it should be easier to extend to more 
complicated cases.  I found the documentation - while good, is missing a 
lot of the little things.  I found myself doing a dir() on object a lot to 
figure out what was in there.

At any rate - thanks for a great tool.  It is making this project a lot 
easier.

-- 
Thanks,
Mark.

Re: [SQLObject] DBMConnection not saving any data

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

Yes indeed there is a problem.  It seems the dbm modules were not being
closed/synced when Python is exited, you have to explicitly close the
file.

I've made a fix to CVS (DBConnection.py) for this.

On Tue, 2003-07-08 at 15:20, Mark Melvin wrote:
> OK, I don't know if this is a dumb question or not - but I am trying to 
> develop using the DBM connection for now, but when I try to create the 
> tables, it creates the '.db' files but there is no data saved (i.e. they 
> are 0 bytes in size).  I assume I am doing something wrong.  I'm also 
> assuming this works on Win2K, with SQLObject 0.4, and that the DBM 
> filesystem is actually supposed to be a persistent format?
> 
> My code is as follows (note - I changed all my StringCols to Cols with 
> sqlType set just to make sure that wasn't it):
> 
> from SQLObject import *
> 
> __connection__ = DBMConnection('db/', debug=1)
> 
> class Customer(SQLObject):
>     Contacts    = MultipleJoin('Contact', orderBy=['LastName', 
> 'FirstName'])
>     CustomerName= Col(sqlType='VARCHAR(30)', default="")
>     Street      = Col(sqlType='VARCHAR(35)', default="")
>     Suite       = Col(sqlType='VARCHAR(35)', default="")
>     City        = Col(sqlType='VARCHAR(20)', default="")
>     Province    = Col(sqlType='VARCHAR(15)', default="")
>     PostalCode  = Col(sqlType='VARCHAR(7)', default="")
>     PhoneNumber1= Col(sqlType='VARCHAR(20)', default="")
>     FaxNumber   = Col(sqlType='VARCHAR(20)', default="")
> 
> class Contact(SQLObject):
>     Customer    = ForeignKey('Customer')
>     LastName    = Col(sqlType='VARCHAR(15)', default="")
>     FirstName   = Col(sqlType='VARCHAR(15)', default="")
>     Title       = Col(sqlType='VARCHAR(25)', default="")
>     PhoneNumber1= Col(sqlType='VARCHAR(20)', default="")
>     PhoneNumber2= Col(sqlType='VARCHAR(20)', default="")
>     Notes       = Col(sqlType='TEXT', default="")
> 
> if __name__ == '__main__':
>     Customer.createTable()
>     Contact.createTable()
>     Customer.new(CustomerName='Blow, Joe', City='Waterloo')
>     c = Customer.new(CustomerName='Wankus Inc.', City='Waterloo')
>     Contact.new(FirstName='Doctor', LastName='Evil', Title='The Bug 
> Cahuna', Customer=c)
>     print list(Customer.select())
>     print list(Contact.select())
> 
> 
> I see the data printed fine, and the select statements work fine as well.  
> It isn't saved to the file however.  Do I have to close the connection or 
> flush things somehow?

Re: [SQLObject] Transactions

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

On Thu, 2003-07-10 at 10:06, Matt Goodall wrote:
> Is the transaction support working in 0.4 and CVS? I just tried the 
> following:
> 
>     conn = PostgresConnection(...)
>     trans = conn.transaction()
>     print Person(1, trans)
> 
> and got "AttributeError: 'Transaction' object has no attribute 'cache'"
> 
> A quick look at the code implies that the Person ctor was expecting a 
> connection, not a transaction.

No, I just extended the DBConnection interface to include a cache, and
forgot to extend Transaction too.

I added this fix to CVS, but my transaction test I added isn't working
like I would think it should.  Could you look at it?  (Maybe I have to
turn off autocommit or something...)

  Ian

Re: [SQLObject] Best way to order columns

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

On Wed, 2003-07-09 at 13:01, Mark Melvin wrote:
> I have two questions:
> 
> 1) What is the best way force a particular order to the columns returned by 
> a select()?
>
> By this I do not mean sorting records - I mean the order of the columns.  
> So for a given select(), SQLObject returns the columns in the order they 
> appear in the database.  Is there a way to specify a new default in which 
> they are returned, or do I have to reorder them myself after I do a 
> select()?

Columns aren't ordered as they are returned -- full objects with
attributes for the columns are returned.

> 2) In terms of sorting records, it appears that when I specify a join, I 
> can use the orderBy keyword and pass it a list and it will happily sort the 
> items based on the order of the items in the list (at least it appears to 
> work that way).  However the _defaultOrder class variable will not take a 
> list.  Is there any way to specify more than one parameter to the SQL that 
> is generated for the "ORDER BY" clause as the default for select()?

There was supposed to be :(  I guess I forgot the last part of
implementing it.  Bad me for not making the right test.  CVS has this
fix.  I think I'll put out a 0.4.1 before too long as well.

  Ian

[SQLObject] Cached Objects

[Matt G. <ma...@po...>]

I hope this has not been discussed in the archives but I cannot get to 
them at the moment ...

Here's my understanding of SQLObject caching - The caching takes place 
at object and attribute level. Objects are cached by class+id and the 
caches (there's actually one for each class) belong to the connection. 
Attributes are cached per object by wrapping the SQL getter in another 
getter that checks whether the attribute exists first. All fairly 
straightforward ... for a metaclass ;-). Both of these forms of caching 
can be turned off.

In a typical Apache 1.x style web application where there are multiple 
processes, the connection's caching has to be disabled (in general) to 
avoid the problems inherent with multiple, independent caches. There 
will, however, be some SQLObjects that are good candidates for caching 
even in this configuration.

It seems to me that it would be useful to be able to turn off object 
level caching on a class-by-class basis. That is, the connection is 
created with caching enabled but the SQLObjects are never actually cached.

I haven't thought this through well enough but it may also be useful to 
cache objects for the lifetime of a transaction. When loading an object 
via a transaction it would first look to see if the connection had the 
object in its cache and if so return that otherwise it would load the 
object from the database and store it in the transation's caches. The 
transaction's caches would be flushed, probably on commit or rollback. 
This may not be worth the effort but in a multi-process application it 
may be the only form of caching that is viable.

As a side issue, I could personally never imagine turing off the 
attribute caching (_cacheValues=False) as the number of SQL queries is 
quite frightening. Do people really use SQLObject like this and if so, 
why and how do you cope with the load on the database?

Thanks, Matt

-- 
Matt Goodall, Pollenation Internet Ltd
w: http://www.pollenationinternet.com
e: ma...@po...

[SQLObject] Documentation Error

[Matt G. <ma...@po...>]

In the SQLObject (i.e. not SQLBuilder) documentation the Transations 
section says to set _cacheValue to False. It should say _cacheValues, 
note the extra 's' on the end.

Cheers, Matt

-- 
Matt Goodall, Pollenation Internet Ltd
w: http://www.pollenationinternet.com
e: ma...@po...

[SQLObject] Transactions

[Matt G. <ma...@po...>]

Is the transaction support working in 0.4 and CVS? I just tried the 
following:

    conn = PostgresConnection(...)
    trans = conn.transaction()
    print Person(1, trans)

and got "AttributeError: 'Transaction' object has no attribute 'cache'"

A quick look at the code implies that the Person ctor was expecting a 
connection, not a transaction.

Cheers, Matt

-- 
Matt Goodall, Pollenation Internet Ltd
w: http://www.pollenationinternet.com
e: ma...@po...

[SQLObject] Best way to order columns

[Mark M. <ma...@di...>]

I have two questions:

1) What is the best way force a particular order to the columns returned by 
a select()?

By this I do not mean sorting records - I mean the order of the columns.  
So for a given select(), SQLObject returns the columns in the order they 
appear in the database.  Is there a way to specify a new default in which 
they are returned, or do I have to reorder them myself after I do a 
select()?

2) In terms of sorting records, it appears that when I specify a join, I 
can use the orderBy keyword and pass it a list and it will happily sort the 
items based on the order of the items in the list (at least it appears to 
work that way).  However the _defaultOrder class variable will not take a 
list.  Is there any way to specify more than one parameter to the SQL that 
is generated for the "ORDER BY" clause as the default for select()?

-- 
Thanks,
Mark.

Re: [SQLObject] dropTable and Sequences

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

Hello again everyone! Just an addendum here, Postgres 7.3+ will automatically
drop sequences created using SERIAL.

- Luke

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

> On Thu, 2003-07-03 at 14:06, Brad Bollenbach wrote:
> > Hi all,
> >
> > dropTable() does not drop the associated sequence.
> >
> > I would like to submit a patch for this (with unit tests, of course :),
> > but should dropTable drop sequences as well, or should there be a
> > dropSequence() method which must be called explicitly?
> >
> > Given that createTable implicitly creates sequences, this might suggest
> > that dropTable should implicitly drop them.
>
> Well, createTable and dropTable already have
> dropJoinTables/createJoinTables keyword arguments.  So one could add
> dropSequences/createSequences to those (default True).  Or maybe the
> whole thing should be turned into dropDependent/createDependent
>
> Of course, right now only Postgres uses sequences, and for the most part
> that's implicit as well -- SERIAL creates a sequence, but we don't see
> it directly.  But, I would also presume that other databases that use
> sequences will be added eventually.
>
>   Ian
>
>
>
>
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
> http://aspnet.click-url.com/go/psa00100006ave/direct;at.asp_061203_01/01
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>


--
The Pursuit of Counterfactual Histories

[SQLObject] DBMConnection not saving any data

[Mark M. <ma...@di...>]

OK, I don't know if this is a dumb question or not - but I am trying to 
develop using the DBM connection for now, but when I try to create the 
tables, it creates the '.db' files but there is no data saved (i.e. they 
are 0 bytes in size).  I assume I am doing something wrong.  I'm also 
assuming this works on Win2K, with SQLObject 0.4, and that the DBM 
filesystem is actually supposed to be a persistent format?

My code is as follows (note - I changed all my StringCols to Cols with 
sqlType set just to make sure that wasn't it):

from SQLObject import *

__connection__ = DBMConnection('db/', debug=1)

class Customer(SQLObject):
    Contacts    = MultipleJoin('Contact', orderBy=['LastName', 
'FirstName'])
    CustomerName= Col(sqlType='VARCHAR(30)', default="")
    Street      = Col(sqlType='VARCHAR(35)', default="")
    Suite       = Col(sqlType='VARCHAR(35)', default="")
    City        = Col(sqlType='VARCHAR(20)', default="")
    Province    = Col(sqlType='VARCHAR(15)', default="")
    PostalCode  = Col(sqlType='VARCHAR(7)', default="")
    PhoneNumber1= Col(sqlType='VARCHAR(20)', default="")
    FaxNumber   = Col(sqlType='VARCHAR(20)', default="")

class Contact(SQLObject):
    Customer    = ForeignKey('Customer')
    LastName    = Col(sqlType='VARCHAR(15)', default="")
    FirstName   = Col(sqlType='VARCHAR(15)', default="")
    Title       = Col(sqlType='VARCHAR(25)', default="")
    PhoneNumber1= Col(sqlType='VARCHAR(20)', default="")
    PhoneNumber2= Col(sqlType='VARCHAR(20)', default="")
    Notes       = Col(sqlType='TEXT', default="")

if __name__ == '__main__':
    Customer.createTable()
    Contact.createTable()
    Customer.new(CustomerName='Blow, Joe', City='Waterloo')
    c = Customer.new(CustomerName='Wankus Inc.', City='Waterloo')
    Contact.new(FirstName='Doctor', LastName='Evil', Title='The Bug 
Cahuna', Customer=c)
    print list(Customer.select())
    print list(Contact.select())


I see the data printed fine, and the select statements work fine as well.  
It isn't saved to the file however.  Do I have to close the connection or 
flush things somehow?

-- 
Thanks,
Mark.

[SQLObject] DTO : a simple implementation by proxy

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

Hi all,

I've made this very little piece of code to implement DTO pattern. I know using __getattr__ is not so speed as using properties, but I'm not yet a great meta-programmer ;-)

Usage :
# loading SQLObject
pers = Person(1)
# creating DTO
persDTO = SQLObjectProxy(pers)
# no UPDATE request is done while setting attributes
persDTO.firstname = 'Dark'
persDTO.lastname = 'Vador'
# UPDATE request is done here
persDTO.save()

Maybe I'll add a getDTO method on SQLObject, or pass an argument to constructor

Here's my code snippet :

##############################################################################
class SQLObjectProxy:
    """

    SQLObjectProxy implements a simple proxy for SQLObject to save network
    traffic by explicit saving, because SQLObject does a request each time an
    attribute is set.
    By calling the save() method, all changes made to the object are saved in
    the database
    """
    
    ##########################################################################
    def __init__( self, subject ):
        """
        SQLObjectProxy contructor.
        @param subject: the SQLObject to wrap
        """
        self.__subject = subject
    
    ##########################################################################
    def __getattr__( self, name ):
        """

        Here is the heart of proxy : delegating attribute reading to the subject
        """
        return getattr( self.__subject, name)

    ##########################################################################
    def save(self):
        """
        
        The save method flushes all changes made to the object.
        """
        kw = {}
        for colname in self._SO_columnDict.keys():
            kw[colname] = getattr(self, colname)
        self.set(**kw)

Re: [SQLObject] How do I do a LEFT JOIN

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

On Mon, 2003-07-07 at 17:22, Mark Melvin wrote:
> I am by no means a SQL master, but do you think it will even come close?  I 
> mean, if you compare the following SQL statement:
> 
>                 "SELECT customer.company, contacts.last_name, 
> contacts.first_name, " \
>                 "customer.phone, customer.street, customer.city, 
> customer.province, " \
>                 "customer.pcode, customer.custid " \
>                 "FROM customer LEFT JOIN contacts ON 
> customer.custid=contacts.custid " \
>                 "ORDER BY customer.company, contacts.last_name, 
> contacts.first_name")

It's not a question about whether it will be the same speed -- it's a
question about whether it will be fast enough for what you are doing. 
If the database isn't a bottleneck, then performance is fine, even if it
is somewhat slower than it might have been.  And there's several parts
of the query as well, not all of which are faster with the left join. 
For instance, you have to reassemble the results into a set of customers
with contacts, since you'll get duplicate customer information back.

If you really wanted to do it quickly, you might be able to do something
like:

customers = Customer.select()
contacts = {}
for contact in Contact.select():
    contacts.setdefault(contact.customerID, []).append(contact)

Then it's two queries instead of one, and you can't use the accessor for
your customers (you have to use contacts[customer.id]), but performance
should be similar.  You can even select portions of the database if you
want, with something like:

query = Customer.q.firstName == 'John'
customers = Customer.select(query)
for contact in Contact.select(AND(Contact.q.customerID = Customer.q.id, query)):
    ...

Hmmm... this technique just occurred to me.  I'll copy it to the mailing
list for other's reference.  I think I need to start a FAQ too.

  Ian

Re: [SQLObject] How do I do a LEFT JOIN

[Mark M. <ma...@di...>]

On 07 Jul 2003 15:59:57 -0500, Ian Bicking <ia...@co...> wrote:

> On Mon, 2003-07-07 at 15:27, Mark Melvin wrote:
>> Hi all,
>>
>> I am super-excited about this package, but I can't seem to figure out 
>> how do execute a simple LEFT JOIN SQL query.  Am I missing somehting 
>> basic here?  I have my two tables set up, but I can't issue the SQL 
>> directly with the .select() method because it throws that pesky WHERE in 
>> at the end.  Here is what I am getting:
>
> I'm glad you are excited about it.  You can't really do a left join, but
> you can get the same thing iteratively, like:
>
> for customer in Customer.select():
> contacts = customer.Contacts

Thanks for the reply.  This seems to connect and do a single SQL query for 
each item in customer.Contacts.  I assume this will be incredibly slow for 
large record sets?  Hmmm...seeing as most of my data is going to be fetched 
this way (the out of the ordinary case being a customer with no Contacts) 
this may not be doable for me.  Darn.  I spent all day looking at 
implementing this...it is so much cleaner than coding SQL queries into my 
GUI.

-- 
Mark.

Re: [SQLObject] How do I do a LEFT JOIN

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

On Mon, 2003-07-07 at 15:27, Mark Melvin wrote:
> Hi all,
> 
> I am super-excited about this package, but I can't seem to figure out how 
> do execute a simple LEFT JOIN SQL query.  Am I missing somehting basic 
> here?  I have my two tables set up, but I can't issue the SQL directly with 
> the .select() method because it throws that pesky WHERE in at the end.  
> Here is what I am getting:

I'm glad you are excited about it.  You can't really do a left join, but
you can get the same thing iteratively, like:

for customer in Customer.select():
    contacts = customer.Contacts

You can't fetch the customers and the contacts together -- SQLObject
only knows how to select instances of one class at a time (this is made
up for by the fact it's easy to get related instances, as in
"customer.Contacts").

  Ian

[SQLObject] How do I do a LEFT JOIN?

[Mark M. <ma...@di...>]

Hi all,

I am super-excited about this package, but I can't seem to figure out how 
do execute a simple LEFT JOIN SQL query.  Am I missing somehting basic 
here?  I have my two tables set up, but I can't issue the SQL directly with 
the .select() method because it throws that pesky WHERE in at the end.  
Here is what I am getting:

class Customer(SQLObject):
    _table = "customer"
    _idName = "custid"
    Company     = StringCol(length=30, dbName='company', default="")
    Street      = StringCol(length=35, dbName='street', default="")
    City        = StringCol(length=20, dbName='city', default="")
    Province    = StringCol(length=15, dbName='province', default="")
    PostalCode  = StringCol(length=7, dbName='pcode', default="")
    Contacts    = MultipleJoin('Contact', joinColumn='custid', 
orderBy=['LastName', '-Title', 'FirstName'])

class Contact(SQLObject):
    _table = "contacts"
    _idName = "contactid"
    LastName    = StringCol(length=15, dbName='last_name', default="")
    FirstName   = StringCol(length=15, dbName='first_name', default="")
    Title       = StringCol(length=25, dbName='title', default="")
    Customer    = ForeignKey('Customer', dbName='custid', default=0)

if __name__ == '__main__':
    all = Customer.select("""JOIN ON Contacts USING (custid)""")

Gives me:

Select:  SELECT customer.custid, customer.province, customer.city, 
customer.stre
et, customer.pcode, customer.company FROM customer WHERE JOIN ON Contacts 
USING
(custid)

Can anyone point me in the right direction?  I need the LEFT JOIN behaviour 
here, not a simple equi-join, as there may be Customers defined, but no 
Contacts.

-- 
Thanks a lot,
Mark.

[SQLObject] How do I do a LEFT JOIN

[Mark M. <ma...@di...>]

Hi all,

I am super-excited about this package, but I can't seem to figure out how 
do execute a simple LEFT JOIN SQL query.  Am I missing somehting basic 
here?  I have my two tables set up, but I can't issue the SQL directly with 
the .select() method because it throws that pesky WHERE in at the end.  
Here is what I am getting:

class Customer(SQLObject):
    _table = "customer"
    _idName = "custid"
    Company     = StringCol(length=30, dbName='company', default="")
    Street      = StringCol(length=35, dbName='street', default="")
    City        = StringCol(length=20, dbName='city', default="")
    Province    = StringCol(length=15, dbName='province', default="")
    PostalCode  = StringCol(length=7, dbName='pcode', default="")
    Contacts    = MultipleJoin('Contact', joinColumn='custid', 
orderBy=['LastName', '-Title', 'FirstName'])

class Contact(SQLObject):
    _table = "contacts"
    _idName = "contactid"
    LastName    = StringCol(length=15, dbName='last_name', default="")
    FirstName   = StringCol(length=15, dbName='first_name', default="")
    Title       = StringCol(length=25, dbName='title', default="")
    Customer    = ForeignKey('Customer', dbName='custid', default=0)

if __name__ == '__main__':
    all = Customer.select("""JOIN ON Contacts USING (custid)""")

Gives me:

Select:  SELECT customer.custid, customer.province, customer.city, 
customer.stre
et, customer.pcode, customer.company FROM customer WHERE JOIN ON Contacts 
USING
(custid)

Can anyone point me in the right direction?  I need the LEFT JOIN behaviour 
here, not a simple equi-join, as there may be Customers defined, but no 
Contacts.

-- 
Thanks a lot,
Mark.

[SQLObject] Re: ANN: SQLObject 0.4

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

Hi there

Installing SLQObject 0.4 I found an error:

byte-compiling
/home/bt/local/lib/python2.1/site-packages/SQLObject/DBConnection.py to
DBConnection.pyc
  File
"/home/bt/local/lib/python2.1/site-packages/SQLObject/DBConnection.py",
line 142
    yield select.sourceClass(result[0])
               ^
SyntaxError: invalid syntax

The following patch seems to fix the problem, but Im not sure if its the
correct fix.

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


diff -ur SQLObject-0.4/SQLObject/DBConnection.py SQLObject-0.4.patched/SQLO=
bject/DBConnection.py
--- SQLObject-0.4/SQLObject/DBConnection.py=09Mon Jun 30 19:43:02 2003
+++ SQLObject-0.4.patched/SQLObject/DBConnection.py=09Mon Jul  7 09:28:10 2=
003
@@ -139,10 +139,10 @@
                 self.releaseConnection(conn)
                 break
             if select.ops.get('lazyColumns', 0):
-                yield select.sourceClass(result[0])
+                yield [select.sourceClass(result[0])]
             else:
                 obj =3D select.sourceClass(result[0], selectResults=3Dresu=
lt[1:])
-                yield obj
+                yield [obj]

     def countSelect(self, select):
         q =3D "SELECT COUNT(*) FROM %s WHERE %s" % \

[SQLObject] Solution to Re: Hook for .new() method?

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

I asked:

 >What's the best way to hook some code into the .new()?

Ian Bicking wrote:

 > Just with new, like:
 >
 > def new(cls, **kw):
 >     obj = super(cls).new(**kw)
 >     # ... do stuff ...
 >     return obj
 > new = classmethod(new)

I wrote again:

 >Hmmm... This doesn't seem to work. When I do this, I get
 >an AttributeError:
 >
 >     obj = super(cls).new(**kw)
 > AttributeError: 'super' object has no attribute 'new'
 >
 >I know that there are some bugs in the super method of Python 2.2. Is
 >this one of them? If so, how can I work around it?


To close on this... there is indeed a bug involving super() and 
classmethods that is fixed in Python 2.2.1. That was one problem. The 
other problem is that the code should have been:

Class MyClass(SQLObject):
     def new(cls, **kw):
         obj = super(MyClass, cls).new(**kw)
         # ... do stuff ...
         return obj
     new = classmethod(new)

i.e., the super call must name MyClass (duh on my part, typo on Ian's, 
I'm sure).

...Edmund.

[SQLObject] ANN: SQLObject 0.4

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

SQLObject 0.4: http://sqlobject.org

Changes
=======

* New (cleaner) column definition style, including for foreign keys
* Alternate naming conventions supported
* Subclassing supported

What Is SQLObject?
==================

SQLObject is an object-relational mapper, translating RDBMS tables into
classes, rows into instances of those classes, allowing you to
manipulate those objects to transparently manipulate the database. 
SQLObject currently supports Postgres, MySQL, and SQLite.

Links
=====

Download:
http://prdownloads.sourceforge.net/sqlobject/SQLObject-0.4.tar.gz?download
Documentation:
http://sqlobject.org/docs/SQLObject.html
News:
http://sqlobject.org/docs/News.html

-- 
Ian Bicking   ia...@co...  http://colorstudy.com
PGP:  gpg --keyserver pgp.mit.edu --recv-keys 0x9B9E28B7

Re: [SQLObject] SQLObject and Zope 3

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

On Fri, Jul 04, 2003 at 01:49:21PM -0500, Ian Bicking wrote:
| On Fri, 2003-07-04 at 11:17, Sidnei da Silva wrote:
| Cool... I was wondering how well it would work with Zope 3, and I'm glad
| it's not too hard.  It's just too bad that Zope 2 is out of the question
| :(

Indeed :(


| > However, Im doing something not that clean which is 'registering' a
| > connection with DBConnection._connections by assigning directly to the
| > module variable. I would like to see a method to do that (eg:
| > DBConnection.registerConnection(name)), so that if it changes in the
| > future, any code depending on it doesnt break :)
| 
| I'm not clear what you mean.  Can you give an example?

Im using the 'feature' of having the SQLObject class attribute
'_connection' being a string and having it look the connection on the
_connections registry on class creation.

    from SQLObject.DBConnection import _connections

    def registerConnection(self, connectionName):
        cs = zapi.getService(self, SQLDatabaseConnections)
        conn = cs.getConnection(connectionName)
        _connections[connectionName] = getAdapter(conn, ISQLConnection)

Another approach I tried is:

    def getFactory(self):
        klass = getService(self, 'ClassService').getClass(self.className)
        if not klass._connection:
            cs = getService(self, SQLDatabaseConnections)
            conn = cs.getConnection(self.connectionName)
            klass._connection = getAdapter(conn, ISQLConnection)
            klass.createTable(ifNotExists=True)
        return klass

But I feel dirty in both cases, for modifying a private variable.

[]'s
-- 
Sidnei da Silva (dreamcatcher) <si...@x3...>
X3ng Web Technology <http://www.x3ng.com.br>
GNU/Linux user 257852
Debian GNU/Linux 3.0 (Sid) 2.4.20-powerpc ppc

Real Users find the one combination of bizarre input values that shuts
down the system for days.
-----------------------------------------------------------------------
Verified for virus by mail.redesul.com.br
Scanner: clamscan / ClamAV - Version 0.54 - Updated 01/07/2003

Re: [SQLObject] dropTable and Sequences

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

On Thu, 2003-07-03 at 14:06, Brad Bollenbach wrote:
> Hi all,
> 
> dropTable() does not drop the associated sequence.
> 
> I would like to submit a patch for this (with unit tests, of course :),
> but should dropTable drop sequences as well, or should there be a
> dropSequence() method which must be called explicitly?
> 
> Given that createTable implicitly creates sequences, this might suggest
> that dropTable should implicitly drop them.

Well, createTable and dropTable already have
dropJoinTables/createJoinTables keyword arguments.  So one could add
dropSequences/createSequences to those (default True).  Or maybe the
whole thing should be turned into dropDependent/createDependent

Of course, right now only Postgres uses sequences, and for the most part
that's implicit as well -- SERIAL creates a sequence, but we don't see
it directly.  But, I would also presume that other databases that use
sequences will be added eventually.

  Ian