[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