Re: [SQLObject] Remarks to first experience with SQLObject

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