sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] RFP: SQLObject-Web ?

[Oleg B. <ph...@ph...>]

On Mon, Feb 02, 2009 at 03:07:49PM -0500, Stef Telford wrote:
> However, what is the alias lock for ? In the

   It is a lock that protects shared alias counter in the class
sqlbuilder.Alias.

> scenario I plan on using SQLObject in, would an alias lock even be
> required/needed ?

   Yes.

> alias's will stay 'fixed' at webserver/framework
> launch .. no ?

   Alias counter is increased on every instantiation of Alias().

> Actually, I -had- hoped that SQLObject could have writelocks
> enabled/disabled, same for connection pooling (which you tell me it
> does, which is great), caching enabled/disabled (the version inside

   Only row caching needs to be disabled.

> SQLObject). The queryCaching I do currently by using python's 'global'
> in the servlet , I can't imagine that going into the main SQLObject
> (and it probably shouldn't). I could put that in a derived class from
> the framework Servlet itself.

   Ok.

> Let me jst say that I have had -bad- previous experience with forks of
> projects and libraries. Invariably they start to become more and more
> incompatible. I would prefer -not- to get into that situation if
> possible.

   But of course!

> Actually, is it possible to get (under subversion) a delta of patches
> from one version to another but cherry picking the changes to be made?

   With some work - that is, save a diff to a text file and edit it. It is
just a well-known patch format.

> Thank you for your time Oleg, I know my emails may seem to be strange,

   They are not! You have a need and a vision WRT SO.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] very newbie: how to start with SQLObject ?

[Stef M. <ste...@gm...>]

hello,

until now I used SQL directly,
but to create functional code, independent of the chosen database,
I'm looking for something different.

So I found SQLObject and SQLAlchemy,
and can't find out from the discussions which is better suited for my 
purposes,
so I decided to give both a try.

I started with SQLObject because in general it's decribed as "easier".
So I started here:

http://www.sqlobject.org/SQLObject.html

The first problem is that the first example already contains an error ( 
at least that's what I guess)

connection_string = 'sqlite:' + db_filename

I think there's a slash missing ?

Now the second problem is,
that I want to start with an existing database:

So this is my code:

from sqlobject import *
class Test_Create2 ( SQLObject ) :
  class sqlmeta :
    print 'piep'
    fromDatabase = True

if __name__ == '__main__':
    DataBase_Name = 'D:/Data_Python_25/support/test_vl.db'
    sqlhub.processConnection = connectionForURI ( 'sqlite:/' + 
DataBase_Name )
    Test_Create2.createTable ()

and this the error I get:
Traceback (most recent call last):
  File "D:\Data_Python_25\support\db_support.py", line 507, in <module>
    class Test_Create2 ( SQLObject ) :
  File 
"p:\python\lib\site-packages\SQLObject-0.10.4-py2.5.egg\sqlobject\declarative.py", 
line 92, in __new__
    cls.__classinit__(cls, new_attrs)
  File 
"p:\python\lib\site-packages\SQLObject-0.10.4-py2.5.egg\sqlobject\main.py", 
line 791, in __classinit__
    cls.sqlmeta.addColumnsFromDatabase()
  File 
"p:\python\lib\site-packages\SQLObject-0.10.4-py2.5.egg\sqlobject\main.py", 
line 446, in addColumnsFromDatabase
    conn = connection or soClass._connection
  File 
"p:\python\lib\site-packages\SQLObject-0.10.4-py2.5.egg\sqlobject\dbconnection.py", 
line 829, in __get__
    return self.getConnection()
  File 
"p:\python\lib\site-packages\SQLObject-0.10.4-py2.5.egg\sqlobject\dbconnection.py", 
line 842, in getConnection
    "No connection has been defined for this thread "
AttributeError: No connection has been defined for this thread or process

thanks,
Stef Mientki

Re: [SQLObject] RFP: SQLObject-Web ?

[Stef T. <st...@um...>]

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

Oleg Broytmann wrote:
> On Mon, Feb 02, 2009 at 12:38:45PM -0500, Stef Telford wrote:
>> Oleg Broytmann wrote:
>>> On Sun, Feb 01, 2009 at 06:25:07PM -0500, Stef Telford wrote:
>>> No offence taken. :)
>> good show. I always get worried about people taking offense over
>> e-mail. I find that tone is very hard to convey.
>
> I know. I did it many time in this mailing list - inadvertently
> offended people. Different levels of English knowledge, different
> vocabularies, different understanding...
>

Well, don't worry about your english, I am scottish - NO one
understands me usually when I talk ;)
I dare say, your english is better than (I am guessing) my russian
(??) so, not a worry/concern.

>>> Still I don't understand. What do you mean by saying "there is
>>> no contention"? If there are threads and there are shared
>>> variables - the variables must be protected, right? Have I
>>> missed something? There are global counters, e.g. in Alias -
>>> how would they fare if different threads would want to increase
>>> them?
>> Well, at the start of every request, a connection is created to
>> the database. Now, -any- rdbms worth it's salt basically creates
>> an isolation level so that I can see -my- changes but no one
>> else's changes
>
> Aha, I see. We are talking about different concepts. You are
> talking about database isolation. I am talking about python shared
> variables. Those locks in the code protect python variables, not
> database! Think about that very pool, connection._pool - nobody
> (i.e., no other thread) should read it even less should write to it
> until SQLObject finishes manipulating it. The same is true for
> alias lock.
>

Ah, getting rid of the writelocks for connection pooling isn't a worry
(I was thinking of getting rid of that and letting the framework deal
with that instead). However, what is the alias lock for ? In the
scenario I plan on using SQLObject in, would an alias lock even be
required/needed ? alias's will stay 'fixed' at webserver/framework
launch .. no ?

>> I don't mind tidying up and putting in memcache support and
>> master/slave style connections for a web version, but I obviously
>> don't want to play 'catch up' with every new release that you
>> fellows do.. hrm.
>
> But what was you plan from the beginning? If you were going to have
>  a forked version of SQLObject - wouldn't fork and catching up the
> only option? You can add some code to the very SQLObject, but if
> you want to remove some generic code - what is the way?
>

Actually, I -had- hoped that SQLObject could have writelocks
enabled/disabled, same for connection pooling (which you tell me it
does, which is great), caching enabled/disabled (the version inside
SQLObject). The queryCaching I do currently by using python's 'global'
in the servlet , I can't imagine that going into the main SQLObject
(and it probably shouldn't). I could put that in a derived class from
the framework Servlet itself.

Let me jst say that I have had -bad- previous experience with forks of
projects and libraries. Invariably they start to become more and more
incompatible. I would prefer -not- to get into that situation if
possible.

Actually, is it possible to get (under subversion) a delta of patches
from one version to another but cherry picking the changes to be made
? That's the one thing under git that would make things .. 'bearable'
at least.

Thank you for your time Oleg, I know my emails may seem to be strange,
especially if english is not your first language. It is truly appreciated.

Regards
Stef

> Oleg.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJh1KUANG7uQ+9D9URAqsjAJ9Y9z3O7b1hMt1ZwrpuX/mKsEmcwQCfVg89
05Vbk+mOwUeRTDqI+HxDnXQ=
=1/yh
-----END PGP SIGNATURE-----

Re: [SQLObject] RFP: SQLObject-Web ?

[Oleg B. <ph...@ph...>]

On Mon, Feb 02, 2009 at 12:38:45PM -0500, Stef Telford wrote:
> Oleg Broytmann wrote:
> > On Sun, Feb 01, 2009 at 06:25:07PM -0500, Stef Telford wrote:
> > No offence taken. :)
> 
> good show. I always get worried about people taking offense over
> e-mail. I find that tone is very hard to convey.

   I know. I did it many time in this mailing list - inadvertently offended
people. Different levels of English knowledge, different vocabularies,
different understanding...

> > Still I don't understand. What do you mean by saying "there is no
> > contention"? If there are threads and there are shared variables -
> > the variables must be protected, right? Have I missed something?
> > There are global counters, e.g. in Alias - how would they fare if
> > different threads would want to increase them?
> 
> Well, at the start of every request, a connection is created to the
> database. Now, -any- rdbms worth it's salt basically creates an
> isolation level so that I can see -my- changes but no one else's
> changes

   Aha, I see. We are talking about different concepts. You are talking
about database isolation. I am talking about python shared variables. Those
locks in the code protect python variables, not database! Think about that
very pool, connection._pool - nobody (i.e., no other thread) should read it
even less should write to it until SQLObject finishes manipulating it. The
same is true for alias lock.

> I don't mind tidying up and putting in
> memcache support and master/slave style connections for a web version,
> but I obviously don't want to play 'catch up' with every new release
> that you fellows do.. hrm.

   But what was you plan from the beginning? If you were going to have
a forked version of SQLObject - wouldn't fork and catching up the only
option? You can add some code to the very SQLObject, but if you want to
remove some generic code - what is the way?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] RFP: SQLObject-Web ?

[Stef T. <st...@um...>]

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

Oleg Broytmann wrote:
> On Sun, Feb 01, 2009 at 06:25:07PM -0500, Stef Telford wrote:
>> Oleg Broytmann wrote:
>>> My company uses SQLObject solely for desktop applications.
>>>
>> Understandable, and this is pretty much how I was thinking
>> SQLObject was designed/targetted for. This makes perfect sense,
>> but in a web
>
> I don't think it is how and what it's designed for. Its original
> author, Ian Bicking, AFAIK, had been working in a web environment.
>
>> framework perhaps not as much. No offense meant ;)
>
> No offence taken. :)
>

good show. I always get worried about people taking offense over
e-mail. I find that tone is very hard to convey.

>>>> *) There is writeLocks - surely not required since most
>>>> frameworks run the entire thread under a transaction anyway.
>>> I don't see how a web transaction is related to
>>> multithread-safe locks. Whatever a web transaction is, there
>>> are a lot of different web frameworks, and who guarantees it is
>>> thread-safe to touch shared variables?
>>>
>> well, from what I have seen over the years, most web frameworks
>> that use an ORM do the following; start request handling in a
>> thread create connection to db do processing commit
>> connection/rollback capture end request
>>
>> I know that's -very- high level, but, there is never really any
>> contention in such an environment and, therefore, there is no
>> need for writeLocks. I am thinking of webware here, probably
>> Turbogears from what I can recall and .. you get the idea. so, I
>> am suggesting that the web edition get's -rid- of writeLocks :)
>
> Still I don't understand. What do you mean by saying "there is no
> contention"? If there are threads and there are shared variables -
> the variables must be protected, right? Have I missed something?
> There are global counters, e.g. in Alias - how would they fare if
> different threads would want to increase them?
>

Well, at the start of every request, a connection is created to the
database. Now, -any- rdbms worth it's salt basically creates an
isolation level so that I can see -my- changes but no one else's
changes, and it also 'locks' any items that are updated - thus
avoiding update contention. The -database- should handle this in other
words, rather than trying to put the problem onto the application to
deal with this, let's let the database handle it. I assume that this
is what the writeLocks are trying to do (stop different threads from
updating the same item at the sametime). I understand that RDBMS rules
out mySQL (not flamebait, merely saying that ACID isn't really "there"
on mySQL to my liking).

Perhaps I haven't dug far enough into sqlobject's code yet. but if all
the objects are instantiated in one thread, the thread runs at the
start of the request through until the end of the request before going
back to the threading pool, then why is threading.lock() even needed ?
how can another process 'jump' or assume thread control (assuming the
framework doesn't call yield, which it doesn't ;) ? perhaps I
misunderstand python's threading model.

The globals table (as far as I can tell) contain a list of tables and
expressions for sqlbuilder.. when would these change -without- me
having to restart the webserver ? if I change the schema of an object
in the database, I would expect to bounce the webservers, right ?

>>>> *) cache built in at a fundamental level - most web people
>>>> would probably feel safer with memcache, currently I don't
>>>> see anyway to disable this
>>> Probably by writing a Cache class that doesn't cache.
>>>
>> yes.. I could do that, but then the 'web' edition would have all
>> these references to 'cache' laying around that would be confusing
>> for later people/programmers. I would prefer to keep code clean
>> and simple if at all possible.
>
> Another approach for SQLObject would be to grow an API to disable
> caching, similar to how one can disable connection pooling. Setting
>  connection.cache to None, or a flag in sqlmeta. But the patch to
> implement this would be rather big and have to be thoroughly
> tested.
>
>>>> *) no query caching
>>> What is a query in terms of an ORM?
>> In -my- usage pattern, it makes a lot of sense. Sometimes a page
>> calls .count() more than 20 times for the same query/object. Poor
>> design ? perhaps, however I think that the 'path of least
>> surprise' is that SQLObject would 'cache' that query and return
>> the cached value since we are inside the same request/response
>> cycle.
>
> Even inside one transaction the result of any .count() could
> change. But if one is satisfied with caching results of such
> queries... well, it is up to her/him, I don't mind having an API
> for such caching.
>

The count should could change inside a transaction, but the web
'workflow' doesn't really mix page rendering and saving in one
transaction. If a save requests come in, I would expect a new
connection to be forked, all the work done in 'updating' in that one,
and a 'read' connection which only EVER deals with 'reads'. I don't
think this use case is really much different from how most web
frameworks work. A mixed read+update style in a single connection,
personally speaking, would seem like -making- work for myself ;)

>>>> So, is it insanity to propose that perhaps there is a
>>>> sqlobject-web version?
>>> You are welcome to support one.
>>>
>> Well, there's the rub.. I could fork a new version, and then
>> spend most of my time playing catch-up with the 'official'
>> releases of SQLObject .. but I don't think that really helps
>> anyone. Is there any way of making a git branch and then before a
>> release even flinging across an email/changeset ? I don't know
>> your dev process's involved :D
>
> Currently the developers are using Subversion. Worse then, the
> development process is a bit stalled as I don't have spare time and
> other developers are not active too. :(
>

ah, sorry to hear that. Hrm. alright. this does raise a certain amount
of .. concern. I think this end up being the -real- problem (no
offense meant again :). I don't mind tidying up and putting in
memcache support and master/slave style connections for a web version,
but I obviously don't want to play 'catch up' with every new release
that you fellows do.. hrm.

Regards
Stef

> Oleg.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJhy+gANG7uQ+9D9URAiAGAJ0atjrZ8hHjxQRW/oLES6ju57CewQCbB94l
sREC4Wlep8lOhF5WlNCUAMk=
=msNB
-----END PGP SIGNATURE-----

Re: [SQLObject] Tricky select with computed value

[Iwan V. <iw...@re...>]

On Mon, 2009-02-02 at 17:12 +0300, Oleg Broytmann wrote:
> On Mon, Feb 02, 2009 at 03:22:05PM +0200, Iwan Vosloo wrote:
> > Article.select("(age(article.published_date)) < (article.running_time *
> > '1 day'::interval)")
> 
>    Try
> 
> from sqlobject.sqlbuilder import func, SQLConstant
> Article.select(func.age(Article.q.publishedDate) < Article.q.runningTime * SQLConst("'1 day'::interval"))


Great, thanks Oleg, that works.

-i

Re: [SQLObject] Tricky select with computed value

[Oleg B. <ph...@ph...>]

On Mon, Feb 02, 2009 at 03:22:05PM +0200, Iwan Vosloo wrote:
> Article.select("(age(article.published_date)) < (article.running_time *
> '1 day'::interval)")

   Try

from sqlobject.sqlbuilder import func, SQLConstant
Article.select(func.age(Article.q.publishedDate) < Article.q.runningTime * SQLConst("'1 day'::interval"))

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Tricky select with computed value

[Iwan V. <iw...@re...>]

Hi there,

We have a class similar to this definition:

class Article(SQLObject):
  publishedDate = DateTimeCol()
  runningTime = IntCol()   # This is the number of days the article 
                           #  runs after being published


To now query for Articles that are still within their running time, you
can do something like this (assuming Postgresql):

Article.select("(age(article.published_date)) < (article.running_time *
'1 day'::interval)")

Not very pretty, and in our real app, we cannot do this anyways for
unrelated reasons. 

Is there a way to express this in Python instead?

Or how else could one do it? (I wondered if you can add a computed
attribute on the class, and somehow have SQLBuilder understand that...
but I imagine that's not possible.)

In the above, age is actually a postgresql function, so that part you
can write using the "q magic" in python.  But the multiplication (and
the typecast) on the other end is problematic to write in Python.

-i

Re: [SQLObject] RFP: SQLObject-Web ?

[Oleg B. <ph...@ph...>]

On Sun, Feb 01, 2009 at 06:25:07PM -0500, Stef Telford wrote:
> Oleg Broytmann wrote:
> > My company uses SQLObject solely for desktop applications.
> >
> Understandable, and this is pretty much how I was thinking SQLObject
> was designed/targetted for. This makes perfect sense, but in a web

   I don't think it is how and what it's designed for. Its original author,
Ian Bicking, AFAIK, had been working in a web environment.

> framework perhaps not as much. No offense meant ;)

   No offence taken. :)

> >> *) There is writeLocks - surely not required since most
> >> frameworks run the entire thread under a transaction anyway.
> >
> > I don't see how a web transaction is related to multithread-safe
> > locks. Whatever a web transaction is, there are a lot of different
> > web frameworks, and who guarantees it is thread-safe to touch
> > shared variables?
> >
> well, from what I have seen over the years, most web frameworks that
> use an ORM do the following;
>     start request handling in a thread
>         create connection to db
>         do processing
>         commit connection/rollback capture
>     end request
> 
> I know that's -very- high level, but, there is never really any
> contention in such an environment and, therefore, there is no need for
> writeLocks. I am thinking of webware here, probably Turbogears from
> what I can recall and .. you get the idea. so, I am suggesting that
> the web edition get's -rid- of writeLocks :)

   Still I don't understand. What do you mean by saying "there is no
contention"? If there are threads and there are shared variables - the
variables must be protected, right? Have I missed something?
   There are global counters, e.g. in Alias - how would they fare if
different threads would want to increase them?

> >> *) cache built in at a fundamental level - most web people would
> >> probably feel safer with memcache, currently I don't see anyway
> >> to disable this
> >
> > Probably by writing a Cache class that doesn't cache.
> >
> yes.. I could do that, but then the 'web' edition would have all these
> references to 'cache' laying around that would be confusing for later
> people/programmers. I would prefer to keep code clean and simple if at
> all possible.

   Another approach for SQLObject would be to grow an API to disable
caching, similar to how one can disable connection pooling. Setting
connection.cache to None, or a flag in sqlmeta. But the patch to implement
this would be rather big and have to be thoroughly tested.

> >> *) no query caching
> >
> > What is a query in terms of an ORM?
> 
> In -my- usage pattern, it makes a lot of sense. Sometimes a page calls
> .count() more than 20 times for the same query/object. Poor design ?
> perhaps, however I think that the 'path of least surprise' is that
> SQLObject would 'cache' that query and return the cached value since
> we are inside the same request/response cycle.

   Even inside one transaction the result of any .count() could change.
But if one is satisfied with caching results of such queries... well, it is
up to her/him, I don't mind having an API for such caching.

> >> So, is it insanity to propose that perhaps there is a
> >> sqlobject-web version?
> >
> > You are welcome to support one.
> >
> Well, there's the rub.. I could fork a new version, and then spend
> most of my time playing catch-up with the 'official' releases of
> SQLObject .. but I don't think that really helps anyone. Is there any
> way of making a git branch and then before a release even flinging
> across an email/changeset ? I don't know your dev process's involved :D

   Currently the developers are using Subversion. Worse then, the
development process is a bit stalled as I don't have spare time and other
developers are not active too. :(

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] RFP: SQLObject-Web ?

[David T. <no...@op...>]

On Sun, 2009-02-01 at 18:25 -0500, Stef Telford wrote:
> Hello Oleg,
> 
> Oleg Broytmann wrote:
> > Hello!
> >
> > My company uses SQLObject solely for desktop applications.
> >
> Understandable, and this is pretty much how I was thinking SQLObject
> was designed/targetted for. This makes perfect sense, but in a web
> framework perhaps not as much. No offense meant ;)
> > On Sun, Feb 01, 2009 at 03:49:46PM -0500, Stef Telford wrote:
> >> *) There is writeLocks - surely not required since most
> >> frameworks run the entire thread under a transaction anyway.
> >
> > I don't see how a web transaction is related to multithread-safe
> > locks. Whatever a web transaction is, there are a lot of different
> > web frameworks, and who guarantees it is thread-safe to touch
> > shared variables?
> >
> well, from what I have seen over the years, most web frameworks that
> use an ORM do the following;
>     start request handling in a thread
>         create connection to db
>         do processing
>         commit connection/rollback capture
>     end request
> 
> I know that's -very- high level, but, there is never really any
> contention in such an environment and, therefore, there is no need for
> writeLocks. I am thinking of webware here, probably Turbogears from
> what I can recall and .. you get the idea. so, I am suggesting that
> the web edition get's -rid- of writeLocks :)

This is also how some of my Pylons-based applications work, fwiw.  I
would definitely use this.

> >> *) no query caching
> >
> > What is a query in terms of an ORM? If 'document' is an SQLObject,
> > attribute access like 'document.title' is implemented using a SQL
> > query. How does query caching help? In any case - if query caching
> > helps, SQLObject will benefit if somebody commits a code, right?
> >
> True.. true. I have rudimentary queryCaching but, as I said, it only
> - -really- makes in the servlet/web response scenario above. I noticed
> that SQLObject issued a lot of similiar sql calls, again, mostly due
> to the object heavy pages we have/use. In -your- usage of SQLObject,
> the query cache would probably be a lot more pain than it is worth. We
> would have to hook into setattr on sqlojbect, get the class name and
> id, then walk over the queryCache checking if that update invalidates
> any of the cached queries.. major pain and probably not worth it.
> 
> In -my- usage pattern, it makes a lot of sense. Sometimes a page calls
> .count() more than 20 times for the same query/object. Poor design ?
> perhaps, however I think that the 'path of least surprise' is that
> SQLObject would 'cache' that query and return the cached value since
> we are inside the same request/response cycle.
> 
> Again, this is totally understandable given how -you- use SQLObject ..
> I am merely wandering if it may make sense to make a more .. well ..
> 'web-centric' version. I could be totally wrong here, of course, hence
> why it's a RFP ;)

I'm less interested in this one, because I usually just use a
per-request cache when I need one.  

Re: [SQLObject] RFP: SQLObject-Web ?

[Stef T. <st...@um...>]

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


Hello Oleg,

Oleg Broytmann wrote:
> Hello!
>
> My company uses SQLObject solely for desktop applications.
>
Understandable, and this is pretty much how I was thinking SQLObject
was designed/targetted for. This makes perfect sense, but in a web
framework perhaps not as much. No offense meant ;)
> On Sun, Feb 01, 2009 at 03:49:46PM -0500, Stef Telford wrote:
>> *) There is writeLocks - surely not required since most
>> frameworks run the entire thread under a transaction anyway.
>
> I don't see how a web transaction is related to multithread-safe
> locks. Whatever a web transaction is, there are a lot of different
> web frameworks, and who guarantees it is thread-safe to touch
> shared variables?
>
well, from what I have seen over the years, most web frameworks that
use an ORM do the following;
    start request handling in a thread
        create connection to db
        do processing
        commit connection/rollback capture
    end request

I know that's -very- high level, but, there is never really any
contention in such an environment and, therefore, there is no need for
writeLocks. I am thinking of webware here, probably Turbogears from
what I can recall and .. you get the idea. so, I am suggesting that
the web edition get's -rid- of writeLocks :)
>> *) cache built in at a fundamental level - most web people would
>> probably feel safer with memcache, currently I don't see anyway
>> to disable this
>
> Probably by writing a Cache class that doesn't cache.
>
yes.. I could do that, but then the 'web' edition would have all these
references to 'cache' laying around that would be confusing for later
people/programmers. I would prefer to keep code clean and simple if at
all possible.
>> *) no query caching
>
> What is a query in terms of an ORM? If 'document' is an SQLObject,
> attribute access like 'document.title' is implemented using a SQL
> query. How does query caching help? In any case - if query caching
> helps, SQLObject will benefit if somebody commits a code, right?
>
True.. true. I have rudimentary queryCaching but, as I said, it only
- -really- makes in the servlet/web response scenario above. I noticed
that SQLObject issued a lot of similiar sql calls, again, mostly due
to the object heavy pages we have/use. In -your- usage of SQLObject,
the query cache would probably be a lot more pain than it is worth. We
would have to hook into setattr on sqlojbect, get the class name and
id, then walk over the queryCache checking if that update invalidates
any of the cached queries.. major pain and probably not worth it.

In -my- usage pattern, it makes a lot of sense. Sometimes a page calls
.count() more than 20 times for the same query/object. Poor design ?
perhaps, however I think that the 'path of least surprise' is that
SQLObject would 'cache' that query and return the cached value since
we are inside the same request/response cycle.

Again, this is totally understandable given how -you- use SQLObject ..
I am merely wandering if it may make sense to make a more .. well ..
'web-centric' version. I could be totally wrong here, of course, hence
why it's a RFP ;)
>> *) connection pooling - I would have thought (again perhaps
>> naively) that in a web environment, this should exist outside the
>> ORM.
>
> It is easy to disable: connection._pool = None.
>
interesting. Noted (and thank you) - I guess I am still stuck in
request->response thinking. The connection seems to be screaming
'Singleton' or 'Borg' to me.. but.. perhaps that's jst me :)
>> So, is it insanity to propose that perhaps there is a
>> sqlobject-web version?
>
> You are welcome to support one.
>
Well, there's the rub.. I could fork a new version, and then spend
most of my time playing catch-up with the 'official' releases of
SQLObject .. but I don't think that really helps anyone. Is there any
way of making a git branch and then before a release even flinging
across an email/changeset ? I don't know your dev process's involved :D

Thanks for the prompt reply btw :D
Regards
Stef
> Oleg.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJhi9TANG7uQ+9D9URAhYJAJ42WZQe/gICkU+imbDvxFIiTjc4ywCfbLh1
n9+b02Wj0s1wJeTc2jX8ctA=
=+tq3
-----END PGP SIGNATURE-----

Re: [SQLObject] RFP: SQLObject-Web ?

[Oleg B. <ph...@ph...>]

Hello!

   My company uses SQLObject solely for desktop applications.

On Sun, Feb 01, 2009 at 03:49:46PM -0500, Stef Telford wrote:
>     *) There is writeLocks - surely not required since most frameworks
> run the entire thread under a transaction anyway.

   I don't see how a web transaction is related to multithread-safe locks.
Whatever a web transaction is, there are a lot of different web frameworks,
and who guarantees it is thread-safe to touch shared variables?

>     *) cache built in at a fundamental level - most web people would
> probably feel safer with memcache, currently I don't see anyway to
> disable this

   Probably by writing a Cache class that doesn't cache.

>     *) no query caching

   What is a query in terms of an ORM? If 'document' is an SQLObject,
attribute access like 'document.title' is implemented using a SQL query.
How does query caching help?
   In any case - if query caching helps, SQLObject will benefit if somebody
commits a code, right?

>     *) connection pooling - I would have thought (again perhaps
> naively) that in a web environment, this should exist outside the ORM.

   It is easy to disable: connection._pool = None.

>     So, is it insanity to propose that perhaps there is a
> sqlobject-web version?

   You are welcome to support one.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] RFP: SQLObject-Web ?

[Stef T. <st...@um...>]

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

Hello Everyone,
    Okay.. I have a question. I think (perhaps wrongly but) that most
people are using SQLObject in a web fashion these days. To my limited
sight, the code seems structured to exist in a more non-web fashion.
What do I mean by this ?

    *) There is writeLocks - surely not required since most frameworks
run the entire thread under a transaction anyway. Surely these are
therefore, at best, depreciated in a web environment and, at worst,
obsolete. It also makes pickle hard (obviously) cause I have to remove
them from the object before storing into memcache and then assign a
new one after a 'get' (of course, I have to do this with sqlmeta
- -anyway-, but at least I can see the reason d'etre with that. very
handy and cool)

    *) cache built in at a fundamental level - most web people would
probably feel safer with memcache, currently I don't see anyway to
disable this other than by hacking the get method inside SQLObject to
not put items into it's cache (at least, that's the way I got memcache
to be used and the inbuilt cache never used). Yes, I -can- subclass
the object, but surely the base object should be 'light' and then a
new SQLObject with caching derives from that. This would mean anyone
could subclass from the base object and the FK/Join goodness without
being forced to use SQLObjects caching.

    *) no query caching - seems strange to have object caching but no
query caching, when query caching is generally where 'web' starts to
notice major slowness. I have created a simple queryCache using a
global inside the servlet/framework request to do this, and it seems
to work pretty fine.

    *) connection pooling - I would have thought (again perhaps
naively) that in a web environment, this should exist outside the ORM.
In essence, something like pgpool-II or sequoia. There are whole
applications designed to do this, and even at it's best, on a page
load I notice that there are about 20 connections (did I mention that
the pages in my website/environment can get pretty Object-Heavy ? :).

    So, is it insanity to propose that perhaps there is a
sqlobject-web version ? missing writelocks (acid handled by the
database), no caching built in (memcache), no connection pooling
(again, other parts of the framework can handle this and argueably
better/cleaner).

    This email should NOT be taken as flamebait or anger or anything
other than a progressive/open email to start a discussion. If anyone
takes any offense, then my deepest apologies, as none are meant in anyway.
    Regards
    Stef
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJhgrqANG7uQ+9D9URAppvAJwKZFTB9QVp0BnTPIlJphj2PUweLgCgizgO
lt4o6qgrjgPBvmLICavJsk4=
=xYqf
-----END PGP SIGNATURE-----

Re: [SQLObject] Rudimentary question

[Oleg B. <ph...@ph...>]

On Sat, Jan 31, 2009 at 03:45:38PM -0500, Michael LeSauvage wrote:
> Keep in mind whilst reading this that I have essentially no SQL background.

   You are welcome!

>    - When I use a MultipleJoin/Foreign Key relationship between two tables,
>    are any columns or tables created to support this relationship?  I suspect
>    that this is simply a nice way of hiding a select query.

   Yes, a new field, int FOREIGN KEY, is created in the table where the
ForeignKey column is declared. MultipleJoin column is not a real column,
but a mark for SQLObject to know what ForeignKey points to it.

>    - When I use a RelatedJoin relationship between two tables, what happens
>    at the SQL level?  I think an intermediate table is created.  What's in this
>    table?

   Yes, there is a hidden table with two columns - two FOREIGN KEYs pointing
to both tables. If you want to create the table yourself to control what is
in the table, see
http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Rudimentary question

[Michael L. <the...@gm...>]

Keep in mind whilst reading this that I have essentially no SQL background.
While I am interested in learning about it, I have been searching
documentation and tutorials about SQLObject to no success.

My questions are:

   - When I use a MultipleJoin/Foreign Key relationship between two tables,
   are any columns or tables created to support this relationship?  I suspect
   that this is simply a nice way of hiding a select query.
   - When I use a RelatedJoin relationship between two tables, what happens
   at the SQL level?  I think an intermediate table is created.  What's in this
   table?

All help is much appreciated.  If you have a reference on the web to point
me at, I'm quite happy to read; I just haven't been able to find the answers
myself.


Mike

Re: [SQLObject] How to add a foreign key at runtime

[Davide A. <dav...@gm...>]

On Jan 27, Oleg Broytmann <ph...@ph...> wrote:

> > What we need is a way to execute a SQL statement like (for MySQL):
> > ALTER TABLE my_info ADD FOREIGN KEY (kind_id) REFERENCES info_type;
> 
> The question was 'how to add', not 'how to change'. SQLObject
> cannot change column types, but you can just execute the query:
> 
> connection.query('ALTER TABLE my_info ADD FOREIGN KEY (kind_id) REFERENCES info_type')

I see; anyway, I've used a horrible workaround to build the needed
query using SQLObject.
I'll describe it here, in case someone else has a similar need (disclaimer:
it's well beyond utterly insanety ;-)

This is the metacode, to handle a list of tables where some columns must
be "converted" to foreign keys:

for origTable in tablesThatNeedFK:
    # Name of the "fake" table we temporary need.
    fakeTableName = 'myfaketable%s' % origTable.sqlmeta.table
    attrs = {}
    for col in columnsOforigTable:
        if thisIsNOTaColToMakeFK:
            # XXX: maybe it can work even _copying_ the column - not tested.
            attrs[colName] = createASQLObjectColWithTheSameParameters()
            continue
        fk = ForeignKey(foreignTableName, name=colName, default=None)
        attrs[colName] = fk
    # Create a new sqlobject.SQLObject subclass, identical to the original
    # one, but with a different name and with some columns replaced with FK.
    newcls = type(fakeTableName, (SQLObject,), attrs)

    # Connect it.
    newcls.setConnection(origTable._connection)

    for fkCol in columnsThatMustBeMadeFK:
        # Get the SQL that _WOULD BE_ run, if we had to create
        # this "fake" table, using the createReferenceConstraint method.
        fkQuery = newcls._connection.createReferenceConstraint(newcls,
                            newcls.sqlmeta.columns[fkCol])
        # Remove "myfaketable" to get references to _real_ tables.
        fkQuery = fkQuery.replace('myfaketable', '')
        # Execute the query.
        origTable._connection.query(fkQuery)

    # Disconnect.  Maybe it's possible/wise to remove the "fake" class
    # from the registry.  Another solution is to store the built "fake"
    # classes in a global proxy somewhere, so that they can be retrieved
    # from there, if the precedure is run again.
    newcls._connection.close()


As said, pure craziness... but it works. :-)


-- 
Davide Alberani <dav...@gm...> [PGP KeyID: 0x465BFD47]
http://erlug.linux.it/~da/

Re: [SQLObject] Call for (a new) developers

[Oleg B. <ph...@ph...>]

On Tue, Jan 27, 2009 at 09:45:54AM -0800, Daniel Fetchinson wrote:
> I can't take on maintenance of SO unfortunately, but I'll try porting
> it and will happily submit any resulting patches.

   You are welcome!

> Also, are there major features that need to go into SO still? I mean
> wouldn't a bugfix, minor fix, maintenance mode be enough?

   Even this is better than nothing!

   About new features: I will translate my TODO and post here for
a discussion.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] porting to python 3

[Daniel F. <fet...@go...>]

>> >> Martin van Loewis ported postgresql to python 3
>> >
>> >    Which one? There are 3 or 4 DB API drivers for Pg; SO uses psycopg.
>>
>> Yes, that one, see here:
>>
>> http://mail.python.org/pipermail/python-porting/2008-December/000004.html
>>
>> So I think it would be absolutely useful to think about porting SO,
>> there are already 2 db API drivers available.
>
>    IWBN to find a better developer. I never was a good developer, I was
> a maintainer at best. And now I have even less time.
>    Anybody wants to displace me? I am still an SO user, and I am still
> going to be a developer, just not a major one.

I can't take on maintenance of SO unfortunately, but I'll try porting
it and will happily submit any resulting patches.

Also, are there major features that need to go into SO still? I mean
wouldn't a bugfix, minor fix, maintenance mode be enough? It's a
mature product already I suppose. It's better to dedicate time/effort
to bugfixes and maintenance than implementing new features which are
buggy due to lack of time of the developers.

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Re: [SQLObject] porting to python 3

[Oleg B. <ph...@ph...>]

On Tue, Jan 27, 2009 at 09:18:51AM -0800, Daniel Fetchinson wrote:
> >> Martin van Loewis ported postgresql to python 3
> >
> >    Which one? There are 3 or 4 DB API drivers for Pg; SO uses psycopg.
> 
> Yes, that one, see here:
> 
> http://mail.python.org/pipermail/python-porting/2008-December/000004.html
> 
> So I think it would be absolutely useful to think about porting SO,
> there are already 2 db API drivers available.

   IWBN to find a better developer. I never was a good developer, I was
a maintainer at best. And now I have even less time.
   Anybody wants to displace me? I am still an SO user, and I am still
going to be a developer, just not a major one.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] porting to python 3

[Daniel F. <fet...@go...>]

>> Martin van Loewis ported postgresql to python 3
>
>    Which one? There are 3 or 4 DB API drivers for Pg; SO uses psycopg.

Yes, that one, see here:

http://mail.python.org/pipermail/python-porting/2008-December/000004.html

So I think it would be absolutely useful to think about porting SO,
there are already 2 db API drivers available.

Cheers,
Daniel

-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Re: [SQLObject] How to add a foreign key at runtime

[Oleg B. <ph...@ph...>]

On Tue, Jan 27, 2009 at 02:12:15PM +0100, Davide Alberani wrote:
> What we need is a way to execute a SQL statement like (for MySQL):
>   ALTER TABLE my_info ADD FOREIGN KEY (kind_id) REFERENCES info_type;

   The question was 'how to add', not 'how to change'. SQLObject cannot
change column types, but you can just execute the query:

connection.query('ALTER TABLE my_info ADD FOREIGN KEY (kind_id) REFERENCES
info_type')

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] How to add a foreign key at runtime

[Davide A. <dav...@gm...>]

On Mon, Jan 12, 2009 at 6:15 PM, Oleg Broytmann <ph...@ph...> wrote:

> > In short, I need something like that:
> > "ALTER TABLE foo ADD FOREIGN KEY (dest_id) REFERENCES dest_type"
> >
> > Is this possible with SQLObject?
>
>   Foo.sqlmeta.addColumn(
>      ForeignKey("DestType", name='dest', default=None),
>   changeSchema=True)
>

Hi,
this is not exactly what we were trying to do, since we already
have a column that must be "made" foreign key.

A simple example:
=============================================================
from sqlobject import *

URI = 'mysql://USER:PWD@localhost/DB'

class InfoType(SQLObject):
    kind = UnicodeCol(notNone=True)

class MyInfo(SQLObject):
    # For a series of reasons we don't want to specify 'kind' as ForeignKey,
    # at creation time.
    kindID = IntCol(notNone=True)
    info = UnicodeCol(notNone=True)

# Connect.
conn = connectionForURI(URI, debug=True)
InfoType.setConnection(conn)
MyInfo.setConnection(conn)

# Drop/create tables.
InfoType.dropTable(ifExists=True)
MyInfo.dropTable(ifExists=True)
InfoType.createTable(ifNotExists=True)
MyInfo.createTable(ifNotExists=True)

# The foreign key, pointing to InfoType.id.
fk = ForeignKey('InfoType', name='kind', default=None)
MyInfo.sqlmeta.addColumn(fk, changeSchema=True)
=============================================================

The above code raises an AssertionError:
  AssertionError: The class __main__.MyInfo already has a column 'kindID'
(<IntCol 895364c kindID>), you cannot add the column <ForeignKey 89b588c
kind>

What we need is a way to execute a SQL statement like (for MySQL):
  ALTER TABLE my_info ADD FOREIGN KEY (kind_id) REFERENCES info_type;

Notice that the my_info table already exists and is full of data, so
we can't drop/recreate it.

Is there any way to do this, using SQLObject?


Thanks!
-- 
Davide Alberani <dav...@gm...>  [PGP KeyID: 0x465BFD47]
http://erlug.linux.it/~da/

Re: [SQLObject] porting to python 3

[Oleg B. <ph...@ph...>]

On Tue, Jan 27, 2009 at 01:28:18AM -0800, Daniel Fetchinson wrote:
> Martin van Loewis ported postgresql to python 3

   Which one? There are 3 or 4 DB API drivers for Pg; SO uses psycopg.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] porting to python 3

[Daniel F. <fet...@go...>]

>> I've started to play with 2to3 and sqlobject 0.10.4. I haven't really
>> converted anything yet, just noted that the following 2 issues need to
>> be changed before a conversion can be successful (both are trivial):
>>
>> RefactoringTool: ### In file col.py ###
>> RefactoringTool: Line 357: could not convert: raise '@@: figure out
>> the exception for a delete'
>> RefactoringTool: Python 3 does not support string exceptions
>> RefactoringTool: Line 978: could not convert: raise "Enum type is not
>> supported"
>> RefactoringTool: Python 3 does not support string exceptions
>>
>> I'd say a simple Exception( '@@: figure out the exception for a delete' )
>> and
>> Exception( 'Enum type is not supported' ) fix will be good enough.
>>
>> I'll report on any further progress I make on porting sqlobject to python
>> 3.
>
>    Thank you!. That's easy to fix. Harder is to get Python DB API drivers
> - what DB API drivers are compiled for Py3k? sqlite? Even harder would be
> to convert SO to unicode-only.

Martin van Loewis ported postgresql to python 3 and of course the
python 3 distribution comes with sqlite. That's already two, actually
the two that I ever would want to use :)

So I'll go ahead and play with 2to3 more. I haven't thought about the
unicode-only issue though.

Cheers,
Daniel

-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Re: [SQLObject] default empty dict for picklecol

[Daniel F. <fet...@go...>]

>> Just letting you know that I upgraded to FormEncode 1.2.1 and
>> everything works fine with sqlobject 0.10.4 in a pretty large
>> turbogears app (which doesn't mean that everything will be fine for
>> everyone).
>
>    Thank you!
>
>> By the way, are there plans for a 1.0 release? What are your
>> requirements for this?
>
>    There are many plans. I have a rather big TODO for 1.0, 1.1 and even
> 1.2; it's in Russian so I cannot just post it here. But I am very busy at
> the job, and cannot devote much time to SQLObject. :(

Please post the link to the Russian TODO list, google will be able to
translate it for everyone not speaking Russian.

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown