SQLObject

On Mon, Feb 21, 2005 at 05:43:18PM -0300, Carlos Ribeiro wrote:
> In short, 
> 
> [1]    dbUser.byUsername(myname)
> 
> works, while:
> 
> [2]    dbUsuario.byUsername(myname)
> 
> do not, because queryOne is not able to make an automatic join for
> inherited tables.

   Can you make a short program that I could use as a test case?

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

On Mon, 21 Feb 2005 23:48:12 +0300, Oleg Broytmann <phd@...> wrote:
> On Mon, Feb 21, 2005 at 05:43:18PM -0300, Carlos Ribeiro wrote:
> > In short,
> >
> > [1]    dbUser.byUsername(myname)
> >
> > works, while:
> >
> > [2]    dbUsuario.byUsername(myname)
> >
> > do not, because queryOne is not able to make an automatic join for
> > inherited tables.
> 
>    Can you make a short program that I could use as a test case?

Sure.

==[alt_keys_inheritance.py]==
import sqlobject

DEFAULT_DB_CONNECTION = 'mysql://root:sidercom@...>'

from sqlobject import StringCol, connectionForURI
from sqlobject.inheritance import InheritableSQLObject

db = connectionForURI(DEFAULT_DB_CONNECTION)

class TestUser(InheritableSQLObject):
    _connection = db
    _inheritable = True
    username = StringCol(length=12, alternateID=True)
    name = StringCol(length=12)
    password = StringCol(length=12)

class TestInternalUser(TestUser):
    phone = StringCol(length=12)

TestUser.dropTable(ifExists=True)
TestInternalUser.dropTable(ifExists=True)

TestUser.createTable()
TestInternalUser.createTable()

# add a TestUser
TestUser(username='admin', name='Administrative account', password='')

# add a TestInternalUser
TestInternalUser(username='ceo', name='The Boss', password='', phone='555-5555')

# find the TestUser by username
print TestUser.byUsername('admin')

# find the TestInternalUser by username
print TestInternalUser.byUsername('ceo')
=======

Results:

cribeiro@... $ python  alt_keys_inheritance.py
Traceback (most recent call last):
  File "alt_keys_inheritance.py", line 8, in ?
    db = connectionForURI(dbConnectionURI)
NameError: name 'dbConnectionURI' is not defined
cribeiro@... $ python  alt_keys_inheritance.py
cribeiro@... $ python  alt_keys_inheritance.py
<TestUser 1L childName=None username='admin' name='Administrati' password=''>
Traceback (most recent call last):
  File "alt_keys_inheritance.py", line 36, in ?
    print TestInternalUser.byUsername('ceo')
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python2.4/site-packages/sqlobject/main.py", line
1155, in _SO_fetchAlternateID
    value)
  File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py",
line 422, in _SO_selectOneAlt
    return self.queryOne("SELECT %s FROM %s WHERE %s = %s" %
  File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py",
line 236, in queryOne
    return self._runWithConnection(self._queryOne, s)
  File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py",
line 127, in _runWithConnection
    val = meth(conn, *args)
  File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py",
line 229, in _queryOne
    self._executeRetry(conn, c, s)
  File "/usr/lib/python2.4/site-packages/sqlobject/mysql/mysqlconnection.py",
line 35, in _executeRetry
    return cursor.execute(query)
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 95,
in execute
    return self._execute(query, args)
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
114, in _execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line
33, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'username'
in 'where clause'")



-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...

Just to complement the test code that I've sent, some ideas & dirty hacks:

My current workaround is a hack, really: it's a hardcoded wrapper for
the byUsername method that calls the ancestor:

class TestInternalUser(TestUser):
    ...
    def byUsername(cls, username, parent=TestUser):
        result = parent.byUsername(username)
        if isinstance(result , cls):
            return result
        else:
            raise SQLObjectNotFound
    byUsername = classmethod(byUsername)
    ...

It works, but it's ugly and dependings on hardcoded stuff... but it
solved my problem for now.

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...

On Mon, Feb 21, 2005 at 06:23:57PM -0300, Carlos Ribeiro wrote:
> >    Can you make a short program that I could use as a test case?
> 
> Sure.

   Got it. I'll work on it tomorrow. It is 1am here (one hour past
midnight) now.

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

On Tue, 22 Feb 2005 01:04:18 +0300, Oleg Broytmann <phd@...> wrote:
> On Mon, Feb 21, 2005 at 06:23:57PM -0300, Carlos Ribeiro wrote:
> > >    Can you make a short program that I could use as a test case?
> >
> > Sure.
> 
>    Got it. I'll work on it tomorrow. It is 1am here (one hour past
> midnight) now.

Thanks :-) Also, I fear my little hack isn't working as I hoped, but
the idea is still valid. I'll let you know if I can make it work...

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...

On Tue, 22 Feb 2005 01:04:18 +0300, Oleg Broytmann <phd@...> wrote:
> On Mon, Feb 21, 2005 at 06:23:57PM -0300, Carlos Ribeiro wrote:
> > >    Can you make a short program that I could use as a test case?
> >
> > Sure.
> 
>    Got it. I'll work on it tomorrow. It is 1am here (one hour past
> midnight) now.
> 
> Oleg.

More info: the problem also affects the selectBy() method. However,
the by<alt-key-name> case seems to be much easier to handle: it takes
only one field, and it returns only one result. A generic solution to
solve the selectBy case seems to be much harder.

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...

On Mon, Feb 21, 2005 at 07:24:05PM -0300, Carlos Ribeiro wrote:
> More info: the problem also affects the selectBy() method.

   It should not, because cls.selectBy() uses cls.SelectResults class,
which in the case of InheritableSQLObject is InheritableSelectResults,
that *does* the correct join.

   The problem with alternateID is in _SO_fetchAlternateID() - it must
be overriden in InheritableSQLObject and must construct the same join.

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

On Tue, 22 Feb 2005 01:34:14 +0300, Oleg Broytmann <phd@...> wrote:
> On Mon, Feb 21, 2005 at 07:24:05PM -0300, Carlos Ribeiro wrote:
> > More info: the problem also affects the selectBy() method.
> 
>    It should not, because cls.selectBy() uses cls.SelectResults class,
> which in the case of InheritableSQLObject is InheritableSelectResults,
> that *does* the correct join.

Ok, I may have been mistaken. My first attempy was done using
selectBy, and it failed. I tried the 'byAltkey' approach after in an
attempt at making things simpler. I'll try to reproduce it, it's quite
possible that I have done some mistake.

>    The problem with alternateID is in _SO_fetchAlternateID() - it must
> be overriden in InheritableSQLObject and must construct the same join.

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...

On Tue, 22 Feb 2005 01:34:14 +0300, Oleg Broytmann <phd@...> wrote:
> On Mon, Feb 21, 2005 at 07:24:05PM -0300, Carlos Ribeiro wrote:
> > More info: the problem also affects the selectBy() method.
> 
>    It should not, because cls.selectBy() uses cls.SelectResults class,
> which in the case of InheritableSQLObject is InheritableSelectResults,
> that *does* the correct join.
> 
>    The problem with alternateID is in _SO_fetchAlternateID() - it must
> be overriden in InheritableSQLObject and must construct the same join.

I've patched the script, and here's the test results:

cribeiro@... $ python alt_keys_inheritance.py
<TestUser 1L childName=None username='admin' name='Administrati' password=''>
Passed test 1
Failed test 2!!!
Failed test 3!!!   <-- selectBy

Check the code below, it's nearly the same as before. I am running
revision 632 from SVN (updated this morning).

====
cribeiro@... $ cat alt_keys_inheritance.py
import sqlobject

DEFAULT_DB_CONNECTION = 'mysql://root:sidercom@...>'

from sqlobject import StringCol, connectionForURI
from sqlobject.inheritance import InheritableSQLObject

db = connectionForURI(DEFAULT_DB_CONNECTION)

class TestUser(InheritableSQLObject):
    _connection = db
    _inheritable = True
    username = StringCol(length=12, alternateID=True)
    name = StringCol(length=12)
    password = StringCol(length=12)

class TestInternalUser(TestUser):
    phone = StringCol(length=12)

TestUser.dropTable(ifExists=True)
TestInternalUser.dropTable(ifExists=True)

TestUser.createTable()
TestInternalUser.createTable()

# add a TestUser
TestUser(username='admin', name='Administrative account', password='')

# add a TestInternalUser
TestInternalUser(username='ceo', name='The Boss', password='', phone='555-5555')

# find the TestUser by username
try:
    print TestUser.byUsername('admin')
    print "Passed test 1"
except:
    print "Failed test 1!!!"

# find the TestInternalUser by username
try:
    print TestInternalUser.byUsername('ceo')
    print "Passed test 2"
except:
    print "Failed test 2!!!"

# try to use the selectBy
try:
    print TestInternalUser.selectBy(username='ceo')
    print "Passed test 3"
except:
    print "Failed test 3!!!"


-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...

Hi!

I fought similar problems:

I define 2 classes, User and Admin (extending User). The classes could be:

class GUser(SQLObject): # generic user
  _inheritable=True
  login=StringCol(length=64, alternateID=True)
  password=StringCol(length=64)

class Admin(GUser):
  name=StringCol()

Suppose I instanciate some rows:

>>> u=GUser(login='test1', password='test1')
>>> v=Admin(login='test2', password='test2', name='Test 2')
>>> x=Admin(login='test3', password='test3', name='Test 3')

If I try this:

>>> GUser.byLogin('test1')
<GUser 1L childName=None password='test1' login='test1'>
>>> GUser.byLogin('test2')
<Admin 2L childName=None name='Test 2'>
>>> Admin.byLogin('test2')
# except...

This is the way it works now and I enjoy this way, Admin could have de
byLogin method, but I like to be able to select a Admin throught a
GUser.byLogin...

I use the svn checkout, I don't know what revision I using now, but I
try to change to the newer, I have some new problems:

# yesterday:
>>> a=Admin.get(2) # (or a=GUser.get(2))
>>> a.login
'test2'

# today:
>>> a=GUser.get(2)
>>> a.login
'test2'
>>> a=Admin.get(2)
>>> a.login
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python2.3/site-packages/sqlobject/main.py", line 847,
in _SO_loadValue
    result = getattr(self, attrName)
AttributeError: 'Admin' object has no attribute '_SO_val_login'

# yesterday
>>> for x in Admin.select(GUser.q.login=='test2'): print x
...
<Admin 2 ...>

# today
>>> for x in Admin.select(GUser.q.login=='test2'): print x
<Admin 2 childName=None name='Test 2'>
<Admin 3 childName=None name='Test 3'>
>>> for x in Admin.select(Admin.q.login=='test2'): print x
<Admin 2 childName=None name='Test 2'>
<Admin 3 childName=None name='Test 3'>

I don't worry to much with the yesterday syntax, but it will be more
readable to make Admin.select(Admin.q.login=='test2')...

I don't inherits from InheritableSQLObject, I use the standard
SQLObject class and it worked, still works...

Thanks for the attention, sorry any mistake... 

On Mon, 21 Feb 2005 21:08:50 -0300, Carlos Ribeiro <carribeiro@...> wrote:
> On Tue, 22 Feb 2005 01:34:14 +0300, Oleg Broytmann <phd@...> wrote:
> > On Mon, Feb 21, 2005 at 07:24:05PM -0300, Carlos Ribeiro wrote:
> > > More info: the problem also affects the selectBy() method.
> >
> >    It should not, because cls.selectBy() uses cls.SelectResults class,
> > which in the case of InheritableSQLObject is InheritableSelectResults,
> > that *does* the correct join.
> >
> >    The problem with alternateID is in _SO_fetchAlternateID() - it must
> > be overriden in InheritableSQLObject and must construct the same join.
> 
> I've patched the script, and here's the test results:
> 
> cribeiro@... $ python alt_keys_inheritance.py
> <TestUser 1L childName=None username='admin' name='Administrati' password=''>
> Passed test 1
> Failed test 2!!!
> Failed test 3!!!   <-- selectBy
> 
> Check the code below, it's nearly the same as before. I am running
> revision 632 from SVN (updated this morning).
> 
> ====
> cribeiro@... $ cat alt_keys_inheritance.py
> import sqlobject
> 
> DEFAULT_DB_CONNECTION = 'mysql://root:sidercom@...>'
> 
> from sqlobject import StringCol, connectionForURI
> from sqlobject.inheritance import InheritableSQLObject
> 
> db = connectionForURI(DEFAULT_DB_CONNECTION)
> 
> class TestUser(InheritableSQLObject):
>     _connection = db
>     _inheritable = True
>     username = StringCol(length=12, alternateID=True)
>     name = StringCol(length=12)
>     password = StringCol(length=12)
> 
> class TestInternalUser(TestUser):
>     phone = StringCol(length=12)
> 
> TestUser.dropTable(ifExists=True)
> TestInternalUser.dropTable(ifExists=True)
> 
> TestUser.createTable()
> TestInternalUser.createTable()
> 
> # add a TestUser
> TestUser(username='admin', name='Administrative account', password='')
> 
> # add a TestInternalUser
> TestInternalUser(username='ceo', name='The Boss', password='', phone='555-5555')
> 
> # find the TestUser by username
> try:
>     print TestUser.byUsername('admin')
>     print "Passed test 1"
> except:
>     print "Failed test 1!!!"
> 
> # find the TestInternalUser by username
> try:
>     print TestInternalUser.byUsername('ceo')
>     print "Passed test 2"
> except:
>     print "Failed test 2!!!"
> 
> # try to use the selectBy
> try:
>     print TestInternalUser.selectBy(username='ceo')
>     print "Passed test 3"
> except:
>     print "Failed test 3!!!"
> 
> 
> --
> Carlos Ribeiro
> Consultoria em Projetos
> blog: http://rascunhosrotos.blogspot.com
> blog: http://pythonnotes.blogspot.com
> mail: carribeiro@...
> mail: carribeiro@...
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 


-- 
Michel Thadeu Sabchuk
Curitiba - PR

Hello!

On Mon, Feb 21, 2005 at 09:08:50PM -0300, Carlos Ribeiro wrote:
> Failed test 2!!!
> Failed test 3!!!   <-- selectBy

   I think I've fixed both problems - with byID() and selectBy()
(actually inherited byID() uses inherited selectBy() :). Commited at
revision 635. Added more tests. New tarball at the usual place:
   http://phd.pp.ru/Software/Python/misc/SQLObject/

   Please test and report if the patches fix your problems.

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

Hello!

   I've commited a patch (rev. 638) that simplifies and makes faster my
previous solution. I've changed the signature for the method
_SO_fetchAlternateID.

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

On Tue, 22 Feb 2005 15:45:26 +0300, Oleg Broytmann <phd@...> wrote:
> Hello!
> 
> On Mon, Feb 21, 2005 at 09:08:50PM -0300, Carlos Ribeiro wrote:
> > Failed test 2!!!
> > Failed test 3!!!   <-- selectBy
> 
>    I think I've fixed both problems - with byID() and selectBy()
> (actually inherited byID() uses inherited selectBy() :). Commited at
> revision 635. Added more tests. New tarball at the usual place:
>    http://phd.pp.ru/Software/Python/misc/SQLObject/
> 
>    Please test and report if the patches fix your problems.

Hi Oleg. Sorry for the late reply. I may be jumping the gun here a
little bit, but I still have some problems. I'm running the latest SVN
version (revision 670). Namely, the inherited class doesn't expose the
'by<AltKey>' method as I expected it would. As I'm on dial up now,
it's difficult to make a good test case & send right now. I'll see
what I can do and keep you posted, ok?

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...

On Tue, Mar 08, 2005 at 11:00:14AM -0300, Carlos Ribeiro wrote:
> Namely, the inherited class doesn't expose the
> 'by<AltKey>' method as I expected it would.

   I'll look into it...

> it's difficult to make a good test case & send right now. I'll see
> what I can do and keep you posted, ok?

   Sure.

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

On Tue, 8 Mar 2005 17:10:42 +0300, Oleg Broytmann <phd@...> wrote:
> On Tue, Mar 08, 2005 at 11:00:14AM -0300, Carlos Ribeiro wrote:
> > Namely, the inherited class doesn't expose the
> > 'by<AltKey>' method as I expected it would.
> 
>    I'll look into it...

Oops. It seems that I had a rather silly bug on my inherited
classes... no wonder it wasn't working. Sorry, I did really jump the
gun here.

> > it's difficult to make a good test case & send right now. I'll see
> > what I can do and keep you posted, ok?
> 
>    Sure.
> 
> Oleg.

Thanks for the attention, and sorry for the disturbance...

-- 
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...

On Tue, Mar 08, 2005 at 11:37:28AM -0300, Carlos Ribeiro wrote:
> Oops. It seems that I had a rather silly bug on my inherited
> classes... no wonder it wasn't working.

   Is it working now?

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

Carlos Ribeiro wrote:
> On Tue, 8 Mar 2005 17:10:42 +0300, Oleg Broytmann <phd@...> wrote:
> 
>>On Tue, Mar 08, 2005 at 11:00:14AM -0300, Carlos Ribeiro wrote:
>>
>>>Namely, the inherited class doesn't expose the
>>>'by<AltKey>' method as I expected it would.
>>
>>   I'll look into it...
> 
> 
> Oops. It seems that I had a rather silly bug on my inherited
> classes... no wonder it wasn't working. Sorry, I did really jump the
> gun here.

I can't say I understand what's going on here, but it's always good if 
SQLObject can catch bugs -- if an extra assert or a better error message 
would have helped you, it would be great to get that into the code.

-- 
Ian Bicking  /  ianb@...  /  http://blog.ianbicking.org