sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] _fromDatabase for Postgres

[David M. C. <da...@da...>]

On Thu, Apr 17, 2003 at 06:29:59PM -0500, Ian Bicking wrote:

> columnsFromSchema is pretty much it.  I don't believe anyone else has
> indicated they're working on it.  I assume it involves parsing the
> appropriate pg_* tables, but I just didn't know what the appropriate
> tables are (sure are a lot of them).

Looks like guessClass will also need to be generalized a bit as Postgres has
type names like "character varying(30)".  Or maybe this should be a method
of the DBAPI subclass.  Also not sure what to do with booleans, but that's
another thread.

For the curious, here's a query (found using psql -E) that gets the same
info as the MySQL "SHOW COLUMNS FROM %s" query (field, type, notnull,
default):

SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod),
a.attnotnull, substring(d.adsrc for 128)
FROM pg_catalog.pg_attribute a, pg_catalog.pg_attrdef d
WHERE a.attrelid = '%s'::regclass AND d.adrelid=a.attrelid
AND d.adnum = a.attnum AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

Obvious, isn't it!  However, this returns a string representation of the
default value.

To get foreign key info I use:

SELECT pg_catalog.pg_get_constraintdef(oid) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '%s'::regclass AND r.contype = 'f'

which returns the equivalent SQL used to define the constraints, e.g.

FOREIGN KEY (recording_id) REFERENCES recording(id) ON UPDATE NO ACTION ON
DELETE CASCADE

This can be with the regular expression:

\((.+)\) REFERENCES (.+)\(

Dave Cook

Re: [SQLObject] _fromDatabase for Postgres

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

On Wed, 2003-04-16 at 20:00, David M. Cook wrote:
> Are there any plans afoot to add this support for Postgres?  I know how to
> get the info out of the Postgres system tables, so it looks like the
> columnsFromSchema would not be hard to implement for PosgresConnection, but
> what else might be involved?  Well, I'm going to try dinking with it a bit
> anyway, so we'll see what happens.

columnsFromSchema is pretty much it.  I don't believe anyone else has
indicated they're working on it.  I assume it involves parsing the
appropriate pg_* tables, but I just didn't know what the appropriate
tables are (sure are a lot of them).

  Ian

Re: [SQLObject] problem: implementing BoolCol

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

On Thu, 2003-04-17 at 10:47, Bud P.Bruegger wrote:
> > class AsBool(Validator):
> >     def convertSQL(self, value):
> >         return ["'f'", "'t'"][not not value]
> >     def unconvertSQL(self, value):
> >         return value == 'f'
> 
> >From what I understand, the above may not be sufficient.  My current
> misunderstanding is that the conversion to and from sql depends on
> three parameters:
> 
> * the type/class of the object instance

The validator can take arguments in its __init__, which could be used to
specialize it more fully.

> * the desired SQL type  (there are several possible implementation
>   decisions for a single python type, it seems)

This gets folded in.  For instance, I would expect a BoolCol to add a
AsBool validator automatically.

> * the actual DBI module:  I looked a little further into psycopg and it seems
>   they do some interesting things including type casting that requires
>   registration of classes etc.  That seems very dependent on which
>   DBI module one uses (note that there are several for PostgreSQL).  

Yes... this would probably not work together with psycopg, but function
instead of psycopg in this case.  At least, so long as there is a
neutral way psycopg can handle Postgres objects.

> I believe ultimately it would be nice to have a single mechanism that
> is used for both "built-in" types as well as for user defined types.
> This is where type casting (probably a better term for what I called
> "higher level types") can come in.  Psycopg already seems to do most
> of this that could directly be used--but I have to look into this a
> little closer..  

Hmm... yes, typecasting is another way of looking at this.  I'm still
not firm on how the validator stuff is going to work... maybe thinking
in terms of typecasting would be better than conversion.  Of course the
types involved aren't actually Python types -- they are foreign types
that are not properly typed in Python, and changing those into Python
types (or vice versa).

> For the time being, it seems that a lot can be done by overwriting the
> _get_xxx and _set_xxx methods as you indicate in your other message.
> Just that user defined types would make it much more eligant and
> manageable...

Indeed.  The hooks are there specifically for this case, but it
certainly could be more elegant.

  Ian

Re: [SQLObject] request (info or feature): two hooks for implementing higher-level types

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

...
> > * afterLoad() that is called just after the object has been created from
...
> > * beforeStore() that is called just before storing data in the DBMS

> I think you would want to do:
> 
> def _get_point(self):
>     return Point(self._ptX, self._ptY)
> 
> def _set_point(self, value):
>     self._ptX, self._ptY = value.x(), value.y()
> 
> Isn't that sufficient?  
> 

It is basically what I was looking for, thanks.  But (see my other
message), it may be nicer to do it at a class/type level much rather
than at attribute level.  Assume, for example, that there are many
attributes of type Point and each needs to have its own _get and _set
method...  Also, (if I understand it correctly), _ptX and _ptY are not
really encapsulated???

My favorite long term solution would still be user definable
types/classes for attributes where a single attribute may optionally
correspond to multiple fields in the table.  (New example: address
that may break up in street, city, zip, country..).  

cheers
--b

Re: [SQLObject] problem: implementing BoolCol

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

On 16 Apr 2003 16:15:25 -0500
Ian Bicking <ia...@co...> wrote:

....
> 
> Yes, that is a problem.  In the meantime you can override the accessor
> directly.  Ultimately the Constraints system will handle this, as I'm
> going to expand it to also do some sort of conversion.  You'll have
> something like:
> 
> class AsBool(Validator):
>     def convertSQL(self, value):
>         return ["'f'", "'t'"][not not value]
>     def unconvertSQL(self, value):
>         return value == 'f'
> 
> (Assuming you also get 't'/'f' back, but maybe I'm wrong)
> 
>   Ian

From what I understand, the above may not be sufficient.  My current
misunderstanding is that the conversion to and from sql depends on
three parameters:

* the type/class of the object instance
* the desired SQL type  (there are several possible implementation
  decisions for a single python type, it seems)
* the actual DBI module:  I looked a little further into psycopg and it seems
  they do some interesting things including type casting that requires
  registration of classes etc.  That seems very dependent on which
  DBI module one uses (note that there are several for PostgreSQL).  

I believe ultimately it would be nice to have a single mechanism that
is used for both "built-in" types as well as for user defined types.
This is where type casting (probably a better term for what I called
"higher level types") can come in.  Psycopg already seems to do most
of this that could directly be used--but I have to look into this a
little closer..  

For the time being, it seems that a lot can be done by overwriting the
_get_xxx and _set_xxx methods as you indicate in your other message.
Just that user defined types would make it much more eligant and
manageable...

cheers

--bud

[SQLObject] Error with _SO_autoInitDone while deleting SQLQobject

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

Hallo,

this code snippet in my survey application
(webware-sandbox/Sandbox/fbar/survey) does not delete the Question,
but instead gives this error: 
'Question' object has no attribute '_SO_autoInitDone'


toDelete = 2
try:
	q = Question(toDelete)
	message = q.question
	if self._survey.deleteQ(q):
		self._msg ='''The question <b>%s</b> was deleted.<br>''' % message
	else:
		self._msg ='''Could not delete <b>%s</b>.<br>''' % message

except Exception, err:
	self._msg ='''Could not delete question!! Error: %s''' % err

Does anyone know, what this means adn how I could fix it?

All code is online at
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/webware-sandbox/Sandbox/fbar/survey/

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Remarks to first experience with SQLObject

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

On Sun, 2003-04-13 at 09:44, Michal Kaukic wrote:
> Hi, Ian and all,
> 
>    I am Mathematician, no CS man at all, but I like generality 
> and clarity. So, SQLObject captured my attention.
> Now I am trying to rethink our system for student exams agenda
> in terms of SQLObject (current implementation is in Python/Webware
> with psycopg PostgreSQL driver).
> 
>    First, my understanding of SQLObject internals is very limited. 
> Or, maybe, I am pushing SQLObject beyond its current limits...
> But my current work with SQLObject is like "debug thrice, write once" :-)
> Despite this, I like it. So here are my remarks:
> 
> 
> 1. From PostgreSQL 7.3 documentation:
>   
>   "Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer
>    automatic. If you wish a serial column to be UNIQUE or a PRIMARY KEY it
>    must now be specified, same as with any other data type."
>   
>    So, I think, it will be useful to add this information to SQLObject
>    documentation (Using SQLObject/Declaring the Class).
>    In DBConnection.py, it is already O.K.
> 
> 2. I refactored database tables several times to make them more
>    "SQLObject compliant". In one of attempts I choosed  column names
>    like "Passwd" and got db. column name:  "_passwd", which I not liked :-(
>    Users should not be trapped like that, if they are strictly using 
>    proposed naming conventions. But for me, this happens.  
>    
>    Maybe, splitWords from util.py can be written like that: 
> 
> def splitWords(s):
>     res = _translateRE.sub(lambda x: '_%s' % x.group(0).lower(), s)
>     if s[0].isupper():
>         return res[1:]
>     return res

That is a weird translation, and it shouldn't go that way, but Python
conventionally doesn't have attributes that start with caps, i.e. it
should be passwd instead of Passwd.

> 
> 3. Say, we have the following code fragment:
>    
> # ------------------------------ here begins ------------------
> from SQLObject import *
> from md5 import md5
> 
> # ...
> 
> class Student(SQLObject):
>     _idName='student_id'
>     _cacheValues=False
>     _columns=['name',
>               'surname',
>               'group',
>               Col('passwd',default=''),
>               Col('contact',default='')]
> 
>     def _set_passwd(self,pwstr):
> 	# This fails, self has (as yet) no attribute "id",
>         # which I can understand, but don't like
>         #
>         #if not pwstr:
>         #    print self.__dict__
>         #    pstr=str(self.__dict__['id'])
>         cryptpwd=md5(pstr).hexdigest()
>         print cryptpwd                       # I see, it is O.K
>         self._SO_set_passwd(cryptpwd)
> 
> #   If not defined, "Unreadable attribute" error occurs...
>     def _get_passwd(self):
>         self._SO_get_passwd()

What exactly is the problem you're encountering with this code?

> # ---------------------------- here ends ----------------------
> 
> 
> This code used to work for me as generator of all kinds of troubles...
> 
> ----------------------------------------
> A. Trouble with creating Student object:
> 
>    SQL statement for creating table is:
> 
>    CREATE TABLE "student" (
>         "student_id" SERIAL PRIMARY KEY,
>         "name"    varchar(20)   NOT NULL,
>         "surname" varchar(20) NOT NULL,
>         "group"   varchar(7) NOT NULL),
>         "passwd"  varchar(32) DEFAULT '' NOT NULL,
>         "contact" varchar(128) DEFAULT '');   
> 
>    This will implicitly create the sequence "student_student_id"
>    for primary key. 
> 
>    But DBConnection.py, line 439 (checked yesterday) states:
> 
>          c.execute('SELECT nextval(\'%s_id_seq\')' % table)
>      
>    Should be the idName not involved in that?
>    Maybe:
> 
>          c.execute('SELECT nextval(\'%s_%s_seq\')' % (table,idName)) ?  	   
> 
>    In my case, student_id_seq was expected, but student_student_id_seq
>    was the reality.

Oops, I actually changed the (internal) API just to fix that, but then
forgot to actually finished it up.  But yes, fixed in CVS now.

   
> 
> ---------------------------------
> B. Troubles with passwd attribute.
> 
>    Let us create the student:
>    
>    >>> S=Student.new(name='Milan',surname='Frco',group='5Z023',passwd='frc')
> 
>    But what we see is:
>    >>> S
>    <Student 15007 name='Milan' ... group='5Z023' passwd=None contact=''> 
> 
> The resulting query seems pretty O.K.:
>     
>     INSERT INTO student (passwd, meno, priezv, skupina, contact, student_id) 
>     VALUES ('f964379c80b1ab8b890cf40876fbd0aa', 'Milan', 'Frco', 
>     '5Z023', '', 15007).
> 
> 
>    But, it seems like S.passwd has nothing in common with attribute
>    generated by  "_set_passwd" method.
>    Without "_get_passwd" defined, we get "Unreadable attribute" error. 
> 
>    If we execute:
>    >>> S.passwd="caramba" 
>    
>    (Query is O.K., in database it IS updated.)
> 
>    But, we still have passwd=None in S (also retrieving as S.passwd). 
>    How can it be, I wonder...  

A bug in how the properties were created.  Should be fixed in CVS.


>    But, also if I retrieve (in another Python commandline session)
>    the Student object with student_id=15007,
> 
>    >>> P=Student(15007)
> 
>    I still get "None" for password, although corresponding query
>    (SELECT passwd FROM student WHERE student_id = 15007) in psql
>    gives the right, MD5-encrypted password.
> 
>    I had a goodwill to follow the first example in docs 
>    (Customizing the Objects/Overriding Column Attributes),
>    but somewhere, something, goes wrong.
> 
>    (Also, maybe, in second example form docs,
>     there should be the call:
> 	 "self._SO_set_phoneNumber(value)"
>     instead of
>          "self._SO_set_phoneNumber(self, value)".)

Oh, yep, typo.

>    I will be very grateful, if someone can explain this behaviour and
>    suggest some way of creating "Student" with MD5-encrypted passwd
>    (even better - with initial value as the MD5-encrypted string
>     representation of "student_id").

For the student_id, I think you want to do:

def new(cls, **kw):
    obj = SQLObject.new(cls, **kw)
    obj.passwd = obj.id
    return obj
new = classmethod(new)

> ------------------------------------
> C. What we find, trying "destroy"...
> 
>    Now, we want to delete the abovementioned object S from database.
>    I suppose (looking into source :-), the method "destroy" can do this:
> 
>    >>> S.destroy()
>    >>> S
>    AssertionError: Student with id 15007 has become obsolete
> 
>    So far, so good.
>    >>> del S
>    >>> S = Student(15007)
>    >>> S
>     .... (most of Traceback removed)
> 
>     File "/usr/local/lib/python2.2/site-packages/SQLObject/SQLObject.py", 
>     line 652, in _SO_getValue
>     return results[0]
>     TypeError: unsubscriptable object
> 
>    This is the behaviour (in PostgreSQL/psycopg) if we retrieve any object
>    with nonexisting "id". Maybe, it would be better to make assertion like:
> 
> ---------------------
> (file SQLObject.py, lines 651,...):
> 
>         self._SO_writeLock.release()
>         assert results != None, "%s with id %s is not in the database" \
>                        % (self.__class__.__name__, self.id)
>         return results[0]
> ---------------------
> 
>    Now, accessing S gives:
> 
>       "AssertionError: Student with id 15007 is not in the database." 
> 
>    which is more to my liking.      

Yes, that's a much better error message.

> 
> That's all for now. Sorry for long posting... 

Took a while to work through it, but all good bugs.  Thanks.

  Ian

Re: [SQLObject] Cache Lock Problems

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

Okay, I see the problem:


    def testPurge1(self):
        x = CacheSet()
        y = Something()
        obj = x.get(1, y.__class__)
        self.assertEqual(obj, None)
        x.put(1, y.__class__, y)
        j = x.get(1, y.__class__)
        self.assertEqual(j, y)
        x.purge(1, y.__class__)
        j = x.get(1, y.__class__)
        print "Got %r" % j

So when you do that last .get, you've locked the cache and you don't do
a put to unlock it.  So that's an error in how you're using
CacheSet/CacheFactory.

I've changed it slightly so that you can use try:finally: to protect
against this, which SQLObject instances now do.  I suspect some error
was happening during instantiation of some object, and that's what was
causing the bug.

  Ian

[SQLObject] _fromDatabase for Postgres

[David M. C. <da...@da...>]

Are there any plans afoot to add this support for Postgres?  I know how to
get the info out of the Postgres system tables, so it looks like the
columnsFromSchema would not be hard to implement for PosgresConnection, but
what else might be involved?  Well, I'm going to try dinking with it a bit
anyway, so we'll see what happens.

Dave Cook

Re: [SQLObject] Expiring the cache

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

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

> I was thinking of something like that, only calling it .refresh().  What
> bothered me was if you do this at, say, the beginning of a request you
> may be confused when some other request does the same thing, and the
> object gets refreshed in the middle of your request.
> 
> I'm thinking there needs to be some consolidation of the different ways
> instances act with respect to threads.  There's a few use cases I can
> see:
[...]
> * You want a live instance of the object, attached directly to the
> database.  No caching should occur.  There's no reason not to share this
> instance between threads, as far as I can see.

Maybe the last one might be what I currently need. Caching is okay
(for performance reasons probably needed), but I need changes to be
visible on all Pages.

But then maybe the problem _I_ have has nothing to do with the
caching, I'm not sure.

I just checked in the application I'm working on into the
webware-sandbox under Sandbox/fbar/survey, so if you'd like to take a
look?

It's a survey we want to do with the visitors of our website.
Edit-pages will let our editors add and change the questions and their
answers. Now I can change the questions without problems, changes are
immediatly visible. But not so the answers, which get pulled from the
DB per joins over the Questions. That is, a Question has multiple
answers:

Question "q"
  Since when do you know us?
     Answers "q.answers"
        [ ] 1996
	[ ] 1997
	...
	[ ] 20022
	[ ] 2003

The "Answers" tree is build via "for a in q.answers: ..."

Now if I change the answers in a web form, to correct the "20022"
mistake, this is seen immediatly in the database, but even with strong
browser reloads (Ctl-Shift-Reload,... ;) it is not seen in the Pages.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] request (info or feature): two hooks for implementing higher-level types

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

On Sat, 2003-04-12 at 02:54, Bud P.Bruegger wrote:
> Ian,
> 
> I thought more about my "higher-level" types and believe what I really
> need are two hooks:
> 
> * afterLoad() that is called just after the object has been created from
>   data from the DBMS
> 
> * beforeStore() that is called just before storing data in the DBMS
> 
> By default, these don't do anything and need to be overloaded if one
> wants to use them.
> 
> The following example shows how to apply this to my problem:
> 
> assume _columns contains
>   ...
>   FloatCol('_ptX'),
>   FloatCol('_ptY'),
>   ...

I think you would want to do:

def _get_point(self):
    return Point(self._ptX, self._ptY)

def _set_point(self, value):
    self._ptX, self._ptY = value.x(), value.y()

Isn't that sufficient?  

  Ian

Re: [SQLObject] TypeError: default __new__ takes no parameters

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

Yes, this is a bug that must not have been caught by the interpreter
until 2.3.  Fix now in CVS.

On Sun, 2003-04-13 at 02:25, Matt Mohebbi wrote:
> Hello,
> 
> I have just checked out SQLObject from CVS and installed it on my
> debian system (python2.3). I get the following when I attempt to run
> the test program.
> 
> >>> p = Person.new(firstName="John", lastName="Doe", username="johnd")
> Traceback (most recent call last):
>   File "people.py", line 161, in ?
>     runTest(test1)
>   File "people.py", line 159, in runTest
>     exec line[4:]
>   File "<string>", line 1, in ?
>   File "/usr/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 665, in new
>     inst = cls(None)
>   File "/usr/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 292, in __new__
>     inst = object.__new__(cls, id)
> TypeError: default __new__ takes no parameters
> 
> Any idea what could be causing this?
> 
> Thanks,
> Matt
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: Etnus, makers of TotalView, The debugger 
> for complex code. Debugging C/C++ programs can leave you feeling lost and 
> disoriented. TotalView can help you find your way. Available on major UNIX 
> and Linux platforms. Try it free. www.etnus.com
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] problem: implementing BoolCol

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

On Mon, 2003-04-14 at 10:53, Bud P.Bruegger wrote:
> I tried to implement BoolCol and have the following problem:
> 
> The function sqlRepr in SQLBuilder.py should cast boolean values to
> either 't' or 'f'  (or some alternative reps).  Problem is that (in
> Python 2.2 that I use), there is no Boolean type and sqlRepr has only
> the python type of the (value) object and knows nothing about the
> related ColType (SQL type).  So I can't figure out how to recognize
> that I deal with a boolean in sqlRepr...  
> 
> Would it be worth while to refactor sqlRepr to get a second argument
> that specifies the desired SQL type?

Yes, that is a problem.  In the meantime you can override the accessor
directly.  Ultimately the Constraints system will handle this, as I'm
going to expand it to also do some sort of conversion.  You'll have
something like:

class AsBool(Validator):
    def convertSQL(self, value):
        return ["'f'", "'t'"][not not value]
    def unconvertSQL(self, value):
        return value == 'f'

(Assuming you also get 't'/'f' back, but maybe I'm wrong)

  Ian

Re: [SQLObject] Cache Lock Problems

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

On Mon, 2003-04-14 at 17:08, Luke Opperman wrote:
> Quoting Ian Bicking <ia...@co...>:
> 
> > On Mon, 2003-04-14 at 14:52, Luke Opperman wrote:
> > ...
> > Hmmm... purge, delete, etc, aren't well thought out yet.  I.e.,
> > they don't ensure consistency.
> 
> Hmm. What consistency should happen here? The problem we identified
> was _not_ a thread/race-condition, but a single-thread issue that can
> be identified completely separate from SQLObject. I've attached a
> test script I used, that does the insert/purge/retrieve steps I
> mentioned last time, directly on a CacheSet.

Yes, the whole destroy/purge thing is broken.  That's why I didn't
document it :)  I'll have to take some time to read it over more closely
and probably reimplement it.

> > Actually there shouldn't be a locks there -- if None is returned
> > from get(), the caller is responsible for creating an object and
> > putting it into the cache with put(). Otherwise you can't be sure
> > that two objects won't be created.
> 
> I'm confused. Are you saying it is the caller's responsibility to lock
> a get/fail/put cycle? I can understand that, in which case an
> alternate fix to Cache.py is to remove all locking. Doesn't change
> the fact that locking is very broken in the currently released file,
> singlethreaded or not. :)

No, there's no lock required if the object is already in the cache. 
There's only a lock required if the cache lookup fails.  That's why the
caller doesn't do the locking, the Cache instance does. 

> Or is the intention that Cache is expected to handle the locking, but
> by the caller executing get and put in a specific order (this is more
> like the code appears to be...). Not sure I agree with this plan, and
> I'd definitely prefer it was more explicit
> (CacheSetInst.createLock.acquire()/release() by the caller?).

Yes, that's it.  But like I said, the caller doesn't know if a lock is
required.

  Ian

Re: [SQLObject] Expiring the cache

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

On Tue, 2003-04-15 at 12:43, Frank Barknecht wrote:
> Hallo,
> 
> I'm haveing some problems with changed SQLObjects in Webware. Too
> often the database itself is changed when I change an object, but the
> cache is not expired. In Webware, being a threaded system, I then get
> invalid data in other threads. 
> 
> As I only change objects in one place, I would love to be able to let
> the cache expire by hand there, with something like
> "mySqlobject.cache.expire()"

I was thinking of something like that, only calling it .refresh().  What
bothered me was if you do this at, say, the beginning of a request you
may be confused when some other request does the same thing, and the
object gets refreshed in the middle of your request.

I'm thinking there needs to be some consolidation of the different ways
instances act with respect to threads.  There's a few use cases I can
see:

* You want a fresh instance at the beginning of your "transaction" (not
necessarily a database transaction).  You don't want that instance
changing underneath you.
* You want a live copy of the object, but only live for the process
you're in.  So all threads share a copy, but multiple processes could go
out of sync.  Works well with Webware when its the only user of a
database, but I'm not sure outside of that.
* You want a instance that's associated with a database transaction,
with whatever semantics that implies.  Obviously you may have multiple
copies of the object, since multiple transactions could be occurring.
* You want a live instance of the object, attached directly to the
database.  No caching should occur.  There's no reason not to share this
instance between threads, as far as I can see.

More cases?  Is there a way all these cases can be expressed more
succinctly (like a couple options that can be combined in different
ways)?

  Ian

[SQLObject] Expiring the cache

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

Hallo,

I'm haveing some problems with changed SQLObjects in Webware. Too
often the database itself is changed when I change an object, but the
cache is not expired. In Webware, being a threaded system, I then get
invalid data in other threads. 

As I only change objects in one place, I would love to be able to let
the cache expire by hand there, with something like
"mySqlobject.cache.expire()"

Is this possible - or even a good idea?

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Cache Lock Problems

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

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

> On Mon, 2003-04-14 at 14:52, Luke Opperman wrote:
> ...
> Hmmm... purge, delete, etc, aren't well thought out yet.  I.e.,
> they don't ensure consistency.

Hmm. What consistency should happen here? The problem we identified
was _not_ a thread/race-condition, but a single-thread issue that can
be identified completely separate from SQLObject. I've attached a
test script I used, that does the insert/purge/retrieve steps I
mentioned last time, directly on a CacheSet.


> Actually there shouldn't be a locks there -- if None is returned
> from get(), the caller is responsible for creating an object and
> putting it into the cache with put(). Otherwise you can't be sure
> that two objects won't be created.

I'm confused. Are you saying it is the caller's responsibility to lock
a get/fail/put cycle? I can understand that, in which case an
alternate fix to Cache.py is to remove all locking. Doesn't change
the fact that locking is very broken in the currently released file,
singlethreaded or not. :)

Or is the intention that Cache is expected to handle the locking, but
by the caller executing get and put in a specific order (this is more
like the code appears to be...). Not sure I agree with this plan, and
I'd definitely prefer it was more explicit
(CacheSetInst.createLock.acquire()/release() by the caller?).

Ok, i've blabbered enough. It appears the second one is what you were
trying to implement, and I see your point about ensuring two copies
aren't created if you don't have a lock in some way around the whole
get/fail/put cycle... from my look it seems SQLObject.__new__ is
doing the right thing then, so I'm feeling less comfy...

> I have a feeling if there's a problem, it's in
> SQLObject.__new__, where maybe it isn't careful enough about this
> sequence of calls.  Anyway, I'll look at this more closely later.
> Taxes due tomorrow :(

Enjoy,

- Luke

Re: [SQLObject] Cache Lock Problems

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

On Mon, 2003-04-14 at 14:52, Luke Opperman wrote:
> Symptoms: We've been having SQLObject completely hang the
> python interpreter on a regular basis, after some digging
> it turned out to be during Cache lookups. More
> specifically, if you stored an object, then destroyed it
> (purge in Cache), then tried to do a lookup for that id in
> the cache again (expecting failure), it would hang. Having
> found the problem, it's likely this behavior could happen
> at other points, but that's the easy way to duplicate. :)

Hmmm... purge, delete, etc, aren't well thought out yet.  I.e., they
don't ensure consistency.

> Fix: Cache.py is missing a lot of lock statements. I've
> attached a fixed version with "## LDO" comments, they are
> all in get() or put().

Actually there shouldn't be a locks there -- if None is returned from
get(), the caller is responsible for creating an object and putting it
into the cache with put().  Otherwise you can't be sure that two objects
won't be created.  I have a feeling if there's a problem, it's in
SQLObject.__new__, where maybe it isn't careful enough about this
sequence of calls.  Anyway, I'll look at this more closely later.  Taxes
due tomorrow :(

  Ian

[SQLObject] Cache Lock Problems

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

(Using SQLObject 0.3-CVS)
Hello all -

Wow. Had a big long email written out, went back to debug a
little more after lunch, and found the bug(s). Still would
like a way to _completely_ turn off caching, including
weakref\'ing. But it'd only be useful for us on days like
today, when we're suffering from a bug there and would
prefer to have all but one of us just avoid it. :)

Symptoms: We've been having SQLObject completely hang the
python interpreter on a regular basis, after some digging
it turned out to be during Cache lookups. More
specifically, if you stored an object, then destroyed it
(purge in Cache), then tried to do a lookup for that id in
the cache again (expecting failure), it would hang. Having
found the problem, it's likely this behavior could happen
at other points, but that's the easy way to duplicate. :)

Fix: Cache.py is missing a lot of lock statements. I've
attached a fixed version with "## LDO" comments, they are
all in get() or put().

- Luke

Now works: Re: [SQLObject] partial fix for DateTimeCol and Postgres

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

Actually, my fix worked and I simply failed to assign DateTime objects
as values as I should have.  So the following seems to work just fine:

dtval=DateTime.DateTime(1997,12,5)
p = Person.new(enumTest='aaa', username="johnd", dtTest=dtval)
dt=DateTime.now()
p.dtTest=dt

--b

[SQLObject] problem: implementing BoolCol

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

Ian and all,

I tried to implement BoolCol and have the following problem:

The function sqlRepr in SQLBuilder.py should cast boolean values to
either 't' or 'f'  (or some alternative reps).  Problem is that (in
Python 2.2 that I use), there is no Boolean type and sqlRepr has only
the python type of the (value) object and knows nothing about the
related ColType (SQL type).  So I can't figure out how to recognize
that I deal with a boolean in sqlRepr...  

Would it be worth while to refactor sqlRepr to get a second argument
that specifies the desired SQL type?

cheers

--b




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

[SQLObject] partial fix for DateTimeCol and Postgres

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

Ian and all,

here is a partial fix for DateTimeCol and PostgreSQL.  The problem is
that DATETIME is not defined, but TIMESTAMP should be used.  

fix:

I changed the DateTime class in Col.py to the following:

---------------------------------------------
class DateTimeCol(Col):

  def _mysqlType(self):
    return 'DATETIME'
		       
  def _postgresType(self):
    return 'TIMESTAMP'
---------------------------------------------

After this, the following seems to work ok:

* creates tables with DateTimeCols correctly (I believe)

* new seems to work correctly and insert a timestamp value

* the DateTime attribute can be accessed and is of type DateTime


But I found at least one remaining problem:

* after assigning a different value to the DateTime attribute, it's
  type changes to string!!!

For details, I attach the test routine I used and its output.

Will try to get a BooleanCol working...

--b

Re : [SQLObject] Remarks to first experience ...

[Michal K. <mi...@fr...>]

...


>#   If not defined, "Unreadable attribute" error occurs...
>    def _get_passwd(self):
>        self._SO_get_passwd()

At least one mystery solved. Forfot to return something in
above method...

                         Mike
                                            

------------------------------------------------------------------------
Michal Kaukic                                  (mi...@fr...)         
Dept. of Math. Methods, Fac. of Manag. & Informatics
University of Zilina,   Slovak Republic

[SQLObject] Remarks to first experience with SQLObject

[Michal K. <mi...@fr...>]

Hi, Ian and all,

   I am Mathematician, no CS man at all, but I like generality 
and clarity. So, SQLObject captured my attention.
Now I am trying to rethink our system for student exams agenda
in terms of SQLObject (current implementation is in Python/Webware
with psycopg PostgreSQL driver).

   First, my understanding of SQLObject internals is very limited. 
Or, maybe, I am pushing SQLObject beyond its current limits...
But my current work with SQLObject is like "debug thrice, write once" :-)
Despite this, I like it. So here are my remarks:


1. From PostgreSQL 7.3 documentation:
  
  "Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer
   automatic. If you wish a serial column to be UNIQUE or a PRIMARY KEY it
   must now be specified, same as with any other data type."
  
   So, I think, it will be useful to add this information to SQLObject
   documentation (Using SQLObject/Declaring the Class).
   In DBConnection.py, it is already O.K.

2. I refactored database tables several times to make them more
   "SQLObject compliant". In one of attempts I choosed  column names
   like "Passwd" and got db. column name:  "_passwd", which I not liked :-(
   Users should not be trapped like that, if they are strictly using 
   proposed naming conventions. But for me, this happens.  
   
   Maybe, splitWords from util.py can be written like that: 

def splitWords(s):
    res = _translateRE.sub(lambda x: '_%s' % x.group(0).lower(), s)
    if s[0].isupper():
        return res[1:]
    return res


3. Say, we have the following code fragment:
   
# ------------------------------ here begins ------------------
from SQLObject import *
from md5 import md5

# ...

class Student(SQLObject):
    _idName='student_id'
    _cacheValues=False
    _columns=['name',
              'surname',
              'group',
              Col('passwd',default=''),
              Col('contact',default='')]

    def _set_passwd(self,pwstr):
	# This fails, self has (as yet) no attribute "id",
        # which I can understand, but don't like
        #
        #if not pwstr:
        #    print self.__dict__
        #    pstr=str(self.__dict__['id'])
        cryptpwd=md5(pstr).hexdigest()
        print cryptpwd                       # I see, it is O.K
        self._SO_set_passwd(cryptpwd)

#   If not defined, "Unreadable attribute" error occurs...
    def _get_passwd(self):
        self._SO_get_passwd()

# ---------------------------- here ends ----------------------


This code used to work for me as generator of all kinds of troubles...

----------------------------------------
A. Trouble with creating Student object:

   SQL statement for creating table is:

   CREATE TABLE "student" (
        "student_id" SERIAL PRIMARY KEY,
        "name"    varchar(20)   NOT NULL,
        "surname" varchar(20) NOT NULL,
        "group"   varchar(7) NOT NULL),
        "passwd"  varchar(32) DEFAULT '' NOT NULL,
        "contact" varchar(128) DEFAULT '');   

   This will implicitly create the sequence "student_student_id"
   for primary key. 

   But DBConnection.py, line 439 (checked yesterday) states:

         c.execute('SELECT nextval(\'%s_id_seq\')' % table)
     
   Should be the idName not involved in that?
   Maybe:

         c.execute('SELECT nextval(\'%s_%s_seq\')' % (table,idName)) ?  	   

   In my case, student_id_seq was expected, but student_student_id_seq
   was the reality.
   

---------------------------------
B. Troubles with passwd attribute.

   Let us create the student:
   
   >>> S=Student.new(name='Milan',surname='Frco',group='5Z023',passwd='frc')

   But what we see is:
   >>> S
   <Student 15007 name='Milan' ... group='5Z023' passwd=None contact=''> 

The resulting query seems pretty O.K.:
    
    INSERT INTO student (passwd, meno, priezv, skupina, contact, student_id) 
    VALUES ('f964379c80b1ab8b890cf40876fbd0aa', 'Milan', 'Frco', 
    '5Z023', '', 15007).


   But, it seems like S.passwd has nothing in common with attribute
   generated by  "_set_passwd" method.
   Without "_get_passwd" defined, we get "Unreadable attribute" error. 

   If we execute:
   >>> S.passwd="caramba" 
   
   (Query is O.K., in database it IS updated.)

   But, we still have passwd=None in S (also retrieving as S.passwd). 
   How can it be, I wonder...  
   
   But, also if I retrieve (in another Python commandline session)
   the Student object with student_id=15007,

   >>> P=Student(15007)

   I still get "None" for password, although corresponding query
   (SELECT passwd FROM student WHERE student_id = 15007) in psql
   gives the right, MD5-encrypted password.

   I had a goodwill to follow the first example in docs 
   (Customizing the Objects/Overriding Column Attributes),
   but somewhere, something, goes wrong.

   (Also, maybe, in second example form docs,
    there should be the call:
	 "self._SO_set_phoneNumber(value)"
    instead of
         "self._SO_set_phoneNumber(self, value)".)


   I will be very grateful, if someone can explain this behaviour and
   suggest some way of creating "Student" with MD5-encrypted passwd
   (even better - with initial value as the MD5-encrypted string
    representation of "student_id").
 
------------------------------------
C. What we find, trying "destroy"...

   Now, we want to delete the abovementioned object S from database.
   I suppose (looking into source :-), the method "destroy" can do this:

   >>> S.destroy()
   >>> S
   AssertionError: Student with id 15007 has become obsolete

   So far, so good.
   >>> del S
   >>> S = Student(15007)
   >>> S
    .... (most of Traceback removed)

    File "/usr/local/lib/python2.2/site-packages/SQLObject/SQLObject.py", 
    line 652, in _SO_getValue
    return results[0]
    TypeError: unsubscriptable object

   This is the behaviour (in PostgreSQL/psycopg) if we retrieve any object
   with nonexisting "id". Maybe, it would be better to make assertion like:

---------------------
(file SQLObject.py, lines 651,...):

        self._SO_writeLock.release()
        assert results != None, "%s with id %s is not in the database" \
                       % (self.__class__.__name__, self.id)
        return results[0]
---------------------

   Now, accessing S gives:

      "AssertionError: Student with id 15007 is not in the database." 

   which is more to my liking.      


That's all for now. Sorry for long posting... 


                                   Mike
                         

[SQLObject] Plugging in a new templating engine

[Peter W. <pwi...@th...>]

Hi,
I've had a few discussions with some of the HTML folks I work with a 
some of them were a little wary of them dealing with Python code in the 
templates so I've been looking at the possibility of hooking other 
template engines into Draco that don't need Python code - these guys 
are used to various template kits sitting on top of Perl CGI's. Not 
having done a lot of Python before I had a quick look around and found 
what seems to be a nice simple template tool in SimpleTAL 
(http://www.owlfish.com/software/simpleTAL/).

I've been able to plug it into Draco with a minimum of fuss, just a new 
import statement and a few lines changed to dracohandler.py, I've made 
no attempt at this stage to support multiple types of templates and 
switch between them but doing that would be quite simple I'd think. 
Also the really simple change I did knocks out support for tag 
rewriters but could be put back in easily I'm sure.

Does this sound like something that could make sense in Draco  - with a 
config option of some sort to choose the template engine to use.

from simpletal import simpleTAL, simpleTALES
and at line 471 (needs to have support for using normal Draco templates 
as well)
             if template:
                 #environment = DracoEnvironment()
                 context = simpleTALES.Context()
                 if handler:
                     #environment.update(handler)
                     for key,value in handler.items():
                         context.addGlobal(key, value)
                 response.addHeader('Cache-Control', 'no-cache')
                 #rewriters = rewritemanager.rewriters()
                 #parser = Parser(document, opener, environment, 
rewriters)
                 #output = parser.parse()
                 response.setBuffering(1)
                 #response.write(output)
                 templateFile = opener.open(document)
                 template = simpleTAL.compileHTMLTemplate(templateFile)
                 templateFile.close()
                 template.expand(context, response)

This takes a template that looks like:
<html>
<body>
<h1 tal:content="title">Title</h1>
<h2 tal:condition="username">Welcome back <b 
tal:replace="username">Username here</b></h2>
<p>
Links for today are:
<ul>
   <li tal:repeat="news hotItems"><a href="http://www.sample.org/" 
tal:attributes="href news/link" tal:content="news/title">News 
item</a></li>
</ul>
</p>
</body>
</html>

and when the __handler__.py has something like:

	def test(self, path, args):
		self["title"] = "Daily links"
		self["username"] = "peter w"
		self["hotItems"] =  [
						{"link": "http://www.yahoo.com", "title": "Yahoo!"},
						{"link": "http://www.google.com", "title": "Google"},
						{"link": "http://www.apple.com", "title": "Apple"}
						]

it generates:
<html>
<body>
<h1>Daily links</h1>
<h2>Welcome back peter w</h2>
<p>
Links for today are:
<ul>
   <li><a href="http://www.yahoo.com">Yahoo!</a></li><li><a 
href="http://www.google.com">Google</a></li><li><a 
href="http://www.apple.com">Apple</a></li>
</ul>
</p>
</body>
</html>

Interesting?

regards,
--
peter w.