Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
> Thanks very much! It really seems inheritance itself is a bit buggy. > Good news is that it has the same bug in probably all db backends :) Submitted a ticket to trac. - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
> > > > > Hi, can you please test the code below (if you have sqlite)? If you > > > > > use a different db, can you please change the connectionForURI to > use > > > > > yours and test then? The correct output should be 5 of course. > > > > > > > > I can't, but I don't see the point. What do you expect me to see > running > > > > the test? > > > > > > > > > Well, if you would have a different db than sqlite and for you the > > > test would pass it would mean that the problem is either with sqlite > > > or the python binding pysqlite, but not sqlobject. > > > > I thought Oleg tested that. Anyways, I've attached two output from the > > test (one for postgresql, and one for mysql), both failed as expected. > > I allways forget the attachments... :S Thanks very much! It really seems inheritance itself is a bit buggy. Good news is that it has the same bug in probably all db backends :) - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
On Fri, Jun 15, 2007 at 02:00:51PM -0300, Leandro Lucarella wrote: > I thought Oleg tested that. Sorry, I didn't. I have upgraded OS on my computer, and I am still not fully up. Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
Leandro Lucarella, el 15 de junio a las 14:00 me escribiste: > Daniel Nogradi, el 14 de junio a las 17:19 me escribiste: > > > > Hi, can you please test the code below (if you have sqlite)? If you > > > > use a different db, can you please change the connectionForURI to use > > > > yours and test then? The correct output should be 5 of course. > > > > > > I can't, but I don't see the point. What do you expect me to see running > > > the test? > > > > > > Well, if you would have a different db than sqlite and for you the > > test would pass it would mean that the problem is either with sqlite > > or the python binding pysqlite, but not sqlobject. > > I thought Oleg tested that. Anyways, I've attached two output from the > test (one for postgresql, and one for mysql), both failed as expected. I allways forget the attachments... :S -- LUCA - Leandro Lucarella - Usando Debian GNU/Linux Sid - GNU Generation E-Mail / JID: [EMAIL PROTECTED] GPG Fingerprint: D9E1 4545 0F4B 7928 E82C 375D 4B02 0FE0 B08B 4FB2 GPG Key: gpg --keyserver pks.lugmen.org.ar --recv-keys B08B4FB2 El techo de mi cuarto lleno de planetas Y mi mente es un planeta más Donde vivo yo y nadie podrá entrar Jamás 1/Query : CREATE TABLE named ( id SERIAL PRIMARY KEY, name TEXT, child_name VARCHAR(255) ) 1/QueryR : CREATE TABLE named ( id SERIAL PRIMARY KEY, name TEXT, child_name VARCHAR(255) ) 1/COMMIT : auto 1/Query : CREATE TABLE zoo ( id SERIAL PRIMARY KEY, child_name VARCHAR(255) ) 1/QueryR : CREATE TABLE zoo ( id SERIAL PRIMARY KEY, child_name VARCHAR(255) ) 1/COMMIT : auto 1/Query : CREATE TABLE cage ( id SERIAL PRIMARY KEY, zoo_id INT, child_name VARCHAR(255) ) 1/QueryR : CREATE TABLE cage ( id SERIAL PRIMARY KEY, zoo_id INT, child_name VARCHAR(255) ) 1/COMMIT : auto 1/Query : ALTER TABLE cage ADD CONSTRAINT zoo_id_exists FOREIGN KEY (zoo_id) REFERENCES zoo (id) 1/QueryR : ALTER TABLE cage ADD CONSTRAINT zoo_id_exists FOREIGN KEY (zoo_id) REFERENCES zoo (id) 1/COMMIT : auto 1/Query : CREATE TABLE animal ( id SERIAL PRIMARY KEY, cage_id INT, child_name VARCHAR(255) ) 1/QueryR : CREATE TABLE animal ( id SERIAL PRIMARY KEY, cage_id INT, child_name VARCHAR(255) ) 1/COMMIT : auto 1/Query : ALTER TABLE animal ADD CONSTRAINT cage_id_exists FOREIGN KEY (cage_id) REFERENCES cage (id) 1/QueryR : ALTER TABLE animal ADD CONSTRAINT cage_id_exists FOREIGN KEY (cage_id) REFERENCES cage (id) 1/COMMIT : auto 1/QueryIns: INSERT INTO named (id, name, child_name) VALUES (1, 'myzoo', 'zoo') 1/COMMIT : auto 1/QueryOne: SELECT name, child_name FROM named WHERE id = (1) 1/QueryR : SELECT name, child_name FROM named WHERE id = (1) 1/COMMIT : auto 1/QueryIns: INSERT INTO zoo (id, child_name) VALUES (1, NULL) 1/COMMIT : auto 1/QueryOne: SELECT child_name FROM zoo WHERE id = (1) 1/QueryR : SELECT child_name FROM zoo WHERE id = (1) 1/COMMIT : auto 1/QueryIns: INSERT INTO named (id, name, child_name) VALUES (2, 'firstcage', 'cage') 1/COMMIT : auto 1/QueryOne: SELECT name, child_name FROM named WHERE id = (2) 1/QueryR : SELECT name, child_name FROM named WHERE id = (2) 1/COMMIT : auto 1/QueryIns: INSERT INTO cage (id, zoo_id, child_name) VALUES (2, 1, NULL) 1/COMMIT : auto 1/QueryOne: SELECT zoo_id, child_name FROM cage WHERE id = (2) 1/QueryR : SELECT zoo_id, child_name FROM cage WHERE id = (2) 1/COMMIT : auto 1/QueryIns: INSERT INTO named (id, name, child_name) VALUES (3, 'secondcage', 'cage') 1/COMMIT : auto 1/QueryOne: SELECT name, child_name FROM named WHERE id = (3) 1/QueryR : SELECT name, child_name FROM named WHERE id = (3) 1/COMMIT : auto 1/QueryIns: INSERT INTO cage (id, zoo_id, child_name) VALUES (3, 1, NULL) 1/COMMIT : auto 1/QueryOne: SELECT zoo_id, child_name FROM cage WHERE id = (3) 1/QueryR : SELECT zoo_id, child_name FROM cage WHERE id = (3) 1/COMMIT : auto 1/QueryIns: INSERT INTO named (id, name, child_name) VALUES (4, 'tiger', 'animal') 1/COMMIT : auto 1/QueryOne: SELECT name, child_name FROM named WHERE id = (4) 1/QueryR : SELECT name, child_name FROM named WHERE id = (4) 1/COMMIT : auto 1/QueryIns: INSERT INTO animal (id, cage_id, child_name) VALUES (4, 2, NULL) 1/COMMIT : auto 1/QueryOne: SELECT cage_id, child_name FROM animal WHERE id = (4) 1/QueryR : SELECT cage_id, child_name FROM animal WHERE id = (4) 1/COMMIT : auto 1/QueryIns: INSERT INTO named (id, name, child_name) VALUES (5, 'lion', 'animal') 1/COMMIT : auto 1/QueryOne: SELECT name, child_name FROM named WHERE id = (5) 1/QueryR : SELECT name, child_name FROM named WHERE id = (5) 1/COMMIT : auto 1/QueryIns: INSERT INTO animal (id, cage_id, child_name) VA
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
Daniel Nogradi, el 14 de junio a las 17:19 me escribiste:
> > > Hi, can you please test the code below (if you have sqlite)? If you
> > > use a different db, can you please change the connectionForURI to use
> > > yours and test then? The correct output should be 5 of course.
> >
> > I can't, but I don't see the point. What do you expect me to see running
> > the test?
>
>
> Well, if you would have a different db than sqlite and for you the
> test would pass it would mean that the problem is either with sqlite
> or the python binding pysqlite, but not sqlobject.
I thought Oleg tested that. Anyways, I've attached two output from the
test (one for postgresql, and one for mysql), both failed as expected.
> > > If you don't use joins, how would you select the total number of
> > > animals in a zoo in this example? Select (almost) everything and
> > > filter through them in python?
> >
> > For counting:
> >
> > count = 0
> > for c in the_zoo.cages:
> > count += len(c.animals)
>
>
> That is of course okay, but it will issue lot of sql queries and the
> advantage of using joins is that there will only be a single query. So
I know they are suboptimal, as I said:
> > > > I use inheritance a lot, but I don't usually do JOINs (I just do
> > > > it the OO
> > > > way since I don't have very large sets of data) and it works very
> > > > well.
> > For selecting:
> >
> > my_list = [a for a in animal.select() if predicate(a)]
>
> Same here.
Yes this one was just dumb, at least if your predicate can be expressed as
a simple WHERE statement, where you can do:
my_list = list(animal.select(zoo=a_zoo))
=)
> I'll try to narrow down where the problem is (a) sqlite (b) pysqlite
> (c) sqlobject. If anyone else could give the test code a shot with a
> different db that would be really helpful.
I think it's an inheritance problem. The query is not well constructed.
Even this simple query is not well constructed:
animal.select( zoo.q.name=='myzoo').count()
-> 1/QueryOne: SELECT COUNT(*) FROM named WHERE (((named.name) = ('myzoo'))
AND ((named.child_name) = ('animal')))
I don't think inheritance was though for this kind of use (which I think
it's making inheritance go wild is the common ansestor for al classes).
--
LUCA - Leandro Lucarella - Usando Debian GNU/Linux Sid - GNU Generation
E-Mail / JID: [EMAIL PROTECTED]
GPG Fingerprint: D9E1 4545 0F4B 7928 E82C 375D 4B02 0FE0 B08B 4FB2
GPG Key: gpg --keyserver pks.lugmen.org.ar --recv-keys B08B4FB2
DETUVIERON BANDA DE PIRATAS DEL ASFALTO
SON TODOS URUGUAYOS Y ROBARON MILES DE LITROS DE CERVEZA
-- Crónica TV
-
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
___
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
> > > > > > > > sqlobject.dberrors.OperationalError: no such column: > animal.cage_id > > > > > > > > > > > > > >But there is "cage_id" columnm in the "animal" table > > > > > > > > > > > > Well, that's exactly the problem :) > > > > > > > > > >Are you sure this part of the problem is in SQLObject and not in > > > SQLite? > > > > > (There are probably other problems - inheritance was developed for > > > simple > > > > > use cases and hardly support joins and aggregates...) > > > > > > > > I only guess that the problem is with SQLObject since SQLite itself is > > > > pretty reliable. But I'm getting also more and more convinced that > > > > using inheritance is not a good idea, I ran into other similar > > > > troubles too. So probably it's best to stay away from them. > > > > > > I use inheritance a lot, but I don't usually do JOINs (I just do it the > OO > > > way since I don't have very large sets of data) and it works very well. > > > > > > Hi, can you please test the code below (if you have sqlite)? If you > > use a different db, can you please change the connectionForURI to use > > yours and test then? The correct output should be 5 of course. > > I can't, but I don't see the point. What do you expect me to see running > the test? Well, if you would have a different db than sqlite and for you the test would pass it would mean that the problem is either with sqlite or the python binding pysqlite, but not sqlobject. > > If you don't use joins, how would you select the total number of > > animals in a zoo in this example? Select (almost) everything and > > filter through them in python? > > For counting: > > count = 0 > for c in the_zoo.cages: > count += len(c.animals) That is of course okay, but it will issue lot of sql queries and the advantage of using joins is that there will only be a single query. So if performance is of concern using joins is advantageous but at the moment I (or anyone else using sqlite) can't use them together with inheritance. > > or: > > count = 0 > for a in animal.select(): > if a.cage.zoo == the_zoo: > count += 1 Here you potentially select a lot more items than necessary, so might also be a problem performance-wise. > For selecting: > > my_list = [a for a in animal.select() if predicate(a)] Same here. I'll try to narrow down where the problem is (a) sqlite (b) pysqlite (c) sqlobject. If anyone else could give the test code a shot with a different db that would be really helpful. Cheers, Daniel - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
Daniel Nogradi, el 12 de junio a las 14:41 me escribiste: > > > > > > > sqlobject.dberrors.OperationalError: no such column: > > > > > > > animal.cage_id > > > > > > > > > > > >But there is "cage_id" columnm in the "animal" table > > > > > > > > > > Well, that's exactly the problem :) > > > > > > > >Are you sure this part of the problem is in SQLObject and not in > > SQLite? > > > > (There are probably other problems - inheritance was developed for > > simple > > > > use cases and hardly support joins and aggregates...) > > > > > > I only guess that the problem is with SQLObject since SQLite itself is > > > pretty reliable. But I'm getting also more and more convinced that > > > using inheritance is not a good idea, I ran into other similar > > > troubles too. So probably it's best to stay away from them. > > > > I use inheritance a lot, but I don't usually do JOINs (I just do it the OO > > way since I don't have very large sets of data) and it works very well. > > > Hi, can you please test the code below (if you have sqlite)? If you > use a different db, can you please change the connectionForURI to use > yours and test then? The correct output should be 5 of course. I can't, but I don't see the point. What do you expect me to see running the test? > If you don't use joins, how would you select the total number of > animals in a zoo in this example? Select (almost) everything and > filter through them in python? For counting: count = 0 for c in the_zoo.cages: count += len(c.animals) or: count = 0 for a in animal.select(): if a.cage.zoo == the_zoo: count += 1 For selecting: my_list = [a for a in animal.select() if predicate(a)] -- LUCA - Leandro Lucarella - Usando Debian GNU/Linux Sid - GNU Generation E-Mail / JID: [EMAIL PROTECTED] GPG Fingerprint: D9E1 4545 0F4B 7928 E82C 375D 4B02 0FE0 B08B 4FB2 GPG Key: gpg --keyserver pks.lugmen.org.ar --recv-keys B08B4FB2 Do not get mad with others Because they know more than you It is not their fault - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
> > > > > > sqlobject.dberrors.OperationalError: no such column: animal.cage_id > > > > > > > > > >But there is "cage_id" columnm in the "animal" table > > > > > > > > Well, that's exactly the problem :) > > > > > >Are you sure this part of the problem is in SQLObject and not in > SQLite? > > > (There are probably other problems - inheritance was developed for > simple > > > use cases and hardly support joins and aggregates...) > > > > I only guess that the problem is with SQLObject since SQLite itself is > > pretty reliable. But I'm getting also more and more convinced that > > using inheritance is not a good idea, I ran into other similar > > troubles too. So probably it's best to stay away from them. > > I use inheritance a lot, but I don't usually do JOINs (I just do it the OO > way since I don't have very large sets of data) and it works very well. Hi, can you please test the code below (if you have sqlite)? If you use a different db, can you please change the connectionForURI to use yours and test then? The correct output should be 5 of course. If you don't use joins, how would you select the total number of animals in a zoo in this example? Select (almost) everything and filter through them in python? Thanks a lot, Daniel # from sqlobject import * from sqlobject.inheritance import InheritableSQLObject from sqlobject.sqlbuilder import LEFTJOINOn from sqlobject import connectionForURI sqlhub.processConnection = connectionForURI( 'sqlite:///:memory:', debug=True ) class named( InheritableSQLObject ): name = StringCol( ) class zoo( named ): # class zoo( SQLObject ): # name = StringCol( ) cages = MultipleJoin( 'cage' ) class cage( named ): # class cage( SQLObject ): # name = StringCol( ) animals = MultipleJoin( 'animal' ) zoo = ForeignKey( 'zoo' ) class animal( named ): # class animal( SQLObject ): # name = StringCol( ) cage = ForeignKey( 'cage' ) named.createTable( ) zoo.createTable( ) cage.createTable( ) animal.createTable( ) z = zoo( name='myzoo' ) c1 = cage( name='firstcage', zoo=z ) c2 = cage( name='secondcage', zoo=z ) a11 = animal( name='tiger', cage=c1 ) a12 = animal( name='lion', cage=c1 ) a21 = animal( name='croc', cage=c2 ) a22 = animal( name='hypo', cage=c2 ) a23 = animal( name='fish', cage=c2 ) print '-'*80 joins = [ ] joins.append( LEFTJOINOn( None, cage, animal.q.cageID==cage.q.id ) ) joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) ) print animal.select( zoo.q.name=='myzoo', join=joins ).count( ) - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
Daniel Nogradi, el 10 de junio a las 20:52 me escribiste: > > > > > sqlobject.dberrors.OperationalError: no such column: animal.cage_id > > > > > > > >But there is "cage_id" columnm in the "animal" table > > > > > > Well, that's exactly the problem :) > > > >Are you sure this part of the problem is in SQLObject and not in SQLite? > > (There are probably other problems - inheritance was developed for simple > > use cases and hardly support joins and aggregates...) > > I only guess that the problem is with SQLObject since SQLite itself is > pretty reliable. But I'm getting also more and more convinced that > using inheritance is not a good idea, I ran into other similar > troubles too. So probably it's best to stay away from them. I use inheritance a lot, but I don't usually do JOINs (I just do it the OO way since I don't have very large sets of data) and it works very well. -- LUCA - Leandro Lucarella - Usando Debian GNU/Linux Sid - GNU Generation E-Mail / JID: [EMAIL PROTECTED] GPG Fingerprint: D9E1 4545 0F4B 7928 E82C 375D 4B02 0FE0 B08B 4FB2 GPG Key: gpg --keyserver pks.lugmen.org.ar --recv-keys B08B4FB2 EXPOSICION INTERNACIONAL DE INODOROS -- Crónica TV - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
> > > > sqlobject.dberrors.OperationalError: no such column: animal.cage_id > > > > > >But there is "cage_id" columnm in the "animal" table > > > > Well, that's exactly the problem :) > >Are you sure this part of the problem is in SQLObject and not in SQLite? > (There are probably other problems - inheritance was developed for simple > use cases and hardly support joins and aggregates...) I only guess that the problem is with SQLObject since SQLite itself is pretty reliable. But I'm getting also more and more convinced that using inheritance is not a good idea, I ran into other similar troubles too. So probably it's best to stay away from them. Daniel - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
On Sun, Jun 10, 2007 at 05:46:32PM +0200, Daniel Nogradi wrote: > > > sqlobject.dberrors.OperationalError: no such column: animal.cage_id > > > >But there is "cage_id" columnm in the "animal" table > > Well, that's exactly the problem :) Are you sure this part of the problem is in SQLObject and not in SQLite? (There are probably other problems - inheritance was developed for simple use cases and hardly support joins and aggregates...) Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
> Here is a thin that puzzles me:
>
> On Sun, Jun 10, 2007 at 03:05:28PM +0200, Daniel Nogradi wrote:
> > sqlobject.dberrors.OperationalError: no such column: animal.cage_id
>
>But there is "cage_id" columnm in the "animal" table
Well, that's exactly the problem :)
> there were a lot
> of INSERTs and SELECTs for the table before the traceback. Add "debug=1" to
> the DB URI to see the SQL statements.
Here is the full output from the above code:
1/Query : CREATE TABLE named (
id INTEGER PRIMARY KEY,
name TEXT,
child_name VARCHAR(255)
)
1/QueryR : CREATE TABLE named (
id INTEGER PRIMARY KEY,
name TEXT,
child_name VARCHAR(255)
)
2/Query : CREATE TABLE zoo (
id INTEGER PRIMARY KEY,
child_name VARCHAR(255)
)
2/QueryR : CREATE TABLE zoo (
id INTEGER PRIMARY KEY,
child_name VARCHAR(255)
)
3/Query : CREATE TABLE cage (
id INTEGER PRIMARY KEY,
zoo_id INT CONSTRAINT zoo_id_exists REFERENCES zoo(id) ,
child_name VARCHAR(255)
)
3/QueryR : CREATE TABLE cage (
id INTEGER PRIMARY KEY,
zoo_id INT CONSTRAINT zoo_id_exists REFERENCES zoo(id) ,
child_name VARCHAR(255)
)
4/Query : CREATE TABLE animal (
id INTEGER PRIMARY KEY,
cage_id INT CONSTRAINT cage_id_exists REFERENCES cage(id) ,
child_name VARCHAR(255)
)
4/QueryR : CREATE TABLE animal (
id INTEGER PRIMARY KEY,
cage_id INT CONSTRAINT cage_id_exists REFERENCES cage(id) ,
child_name VARCHAR(255)
)
5/QueryIns: INSERT INTO named (name, child_name) VALUES ('myzoo', 'zoo')
5/QueryR : INSERT INTO named (name, child_name) VALUES ('myzoo', 'zoo')
6/QueryOne: SELECT name, child_name FROM named WHERE ((named.id) = (1))
6/QueryR : SELECT name, child_name FROM named WHERE ((named.id) = (1))
7/QueryIns: INSERT INTO zoo (id, child_name) VALUES (1, NULL)
7/QueryR : INSERT INTO zoo (id, child_name) VALUES (1, NULL)
8/QueryOne: SELECT child_name FROM zoo WHERE ((zoo.id) = (1))
8/QueryR : SELECT child_name FROM zoo WHERE ((zoo.id) = (1))
9/QueryIns: INSERT INTO named (name, child_name) VALUES ('firstcage', 'cage')
9/QueryR : INSERT INTO named (name, child_name) VALUES ('firstcage', 'cage')
10/QueryOne: SELECT name, child_name FROM named WHERE ((named.id) = (2))
10/QueryR : SELECT name, child_name FROM named WHERE ((named.id) = (2))
11/QueryIns: INSERT INTO cage (id, zoo_id, child_name) VALUES (2, 1, NULL)
11/QueryR : INSERT INTO cage (id, zoo_id, child_name) VALUES (2, 1, NULL)
12/QueryOne: SELECT zoo_id, child_name FROM cage WHERE ((cage.id) = (2))
12/QueryR : SELECT zoo_id, child_name FROM cage WHERE ((cage.id) = (2))
13/QueryIns: INSERT INTO named (name, child_name) VALUES ('secondcage', 'cage')
13/QueryR : INSERT INTO named (name, child_name) VALUES ('secondcage', 'cage')
14/QueryOne: SELECT name, child_name FROM named WHERE ((named.id) = (3))
14/QueryR : SELECT name, child_name FROM named WHERE ((named.id) = (3))
15/QueryIns: INSERT INTO cage (id, zoo_id, child_name) VALUES (3, 1, NULL)
15/QueryR : INSERT INTO cage (id, zoo_id, child_name) VALUES (3, 1, NULL)
16/QueryOne: SELECT zoo_id, child_name FROM cage WHERE ((cage.id) = (3))
16/QueryR : SELECT zoo_id, child_name FROM cage WHERE ((cage.id) = (3))
17/QueryIns: INSERT INTO named (name, child_name) VALUES ('tiger', 'animal')
17/QueryR : INSERT INTO named (name, child_name) VALUES ('tiger', 'animal')
18/QueryOne: SELECT name, child_name FROM named WHERE ((named.id) = (4))
18/QueryR : SELECT name, child_name FROM named WHERE ((named.id) = (4))
19/QueryIns: INSERT INTO animal (id, cage_id, child_name) VALUES (4, 2, NULL)
19/QueryR : INSERT INTO animal (id, cage_id, child_name) VALUES (4, 2, NULL)
20/QueryOne: SELECT cage_id, child_name FROM animal WHERE ((animal.id) = (4))
20/QueryR : SELECT cage_id, child_name FROM animal WHERE ((animal.id) = (4))
21/QueryIns: INSERT INTO named (name, child_name) VALUES ('lion', 'animal')
21/QueryR : INSERT INTO named (name, child_name) VALUES ('lion', 'animal')
22/QueryOne: SELECT name, child_name FROM named WHERE ((named.id) = (5))
22/QueryR : SELECT name, child_name FROM named WHERE ((named.id) = (5))
23/QueryIns: INSERT INTO animal (id, cage_id, child_name) VALUES (5, 2, NULL)
23/QueryR : INSERT INTO animal (id, cage_id, child_name) VALUES (5, 2, NULL)
24/QueryOne: SELECT cage_id, child_name FROM animal WHERE ((animal.id) = (5))
24/QueryR : SELECT cage_id, child_name FROM animal WHERE ((animal.id) = (5))
25/QueryIns: INSERT INTO named (name, child_name) VALUES ('croc', 'animal')
25/QueryR : INSERT INTO named (name, child_name) VALUES ('croc', 'animal')
26/QueryOne: SELECT name, child_name FROM named WHERE ((named.id) = (6))
26/QueryR : SELECT name, child_name FROM named WHERE ((named.id) = (6))
27/QueryIns: INSERT INTO animal (id, cage_id, child_name) VALUES (6, 3, NULL)
27/QueryR : INSERT INTO animal (id, cage_id, child_name) VALUES (6, 3, NULL)
28/QueryOne: SELECT cage_id, child_name FROM animal
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
Here is a thin that puzzles me: On Sun, Jun 10, 2007 at 03:05:28PM +0200, Daniel Nogradi wrote: > sqlobject.dberrors.OperationalError: no such column: animal.cage_id But there is "cage_id" columnm in the "animal" table, there were a lot of INSERTs and SELECTs for the table before the traceback. Add "debug=1" to the DB URI to see the SQL statements. Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
> > if the model is changed slightly to use inheritance:
> > then the exact same query stops working.
>
>In what way?
Here is a full traceback:
37/QueryOne: SELECT COUNT(*) FROM named LEFT JOIN cage ON
((animal.cage_id) = (cage.id)) LEFT JOIN zoo ON ((cage.zoo_id) =
(zoo.id)) WHERE (((named.name) = ('myzoo')) AND ((named.child_name) =
('animal')))
37/QueryR : SELECT COUNT(*) FROM named LEFT JOIN cage ON
((animal.cage_id) = (cage.id)) LEFT JOIN zoo ON ((cage.zoo_id) =
(zoo.id)) WHERE (((named.name) = ('myzoo')) AND ((named.child_name) =
('animal')))
Traceback (most recent call last):
File "dbtest.py", line 50, in
print animal.select( zoo.q.name=='myzoo', join=joins ).count( )
File
"/usr/local/lib/python2.5/site-packages/SQLObject-0.10dev_r2716-py2.5.egg/sqlobject/sresults.py",
line 222, in count
count = self.accumulate('COUNT(*)')
File
"/usr/local/lib/python2.5/site-packages/SQLObject-0.10dev_r2716-py2.5.egg/sqlobject/sresults.py",
line 207, in accumulate
return conn.accumulateSelect(self, *exprs)
File
"/usr/local/lib/python2.5/site-packages/SQLObject-0.10dev_r2716-py2.5.egg/sqlobject/dbconnection.py",
line 408, in accumulateSelect
val = self.queryOne(q)
File
"/usr/local/lib/python2.5/site-packages/SQLObject-0.10dev_r2716-py2.5.egg/sqlobject/dbconnection.py",
line 385, in queryOne
return self._runWithConnection(self._queryOne, s)
File
"/usr/local/lib/python2.5/site-packages/SQLObject-0.10dev_r2716-py2.5.egg/sqlobject/dbconnection.py",
line 255, in _runWithConnection
val = meth(conn, *args)
File
"/usr/local/lib/python2.5/site-packages/SQLObject-0.10dev_r2716-py2.5.egg/sqlobject/dbconnection.py",
line 378, in _queryOne
self._executeRetry(conn, c, s)
File
"/usr/local/lib/python2.5/site-packages/SQLObject-0.10dev_r2716-py2.5.egg/sqlobject/sqlite/sqliteconnection.py",
line 183, in _executeRetry
raise OperationalError(ErrorMessage(e))
sqlobject.dberrors.OperationalError: no such column: animal.cage_id
You can reproduce the problem with the following:
#
from sqlobject import *
from sqlobject.inheritance import InheritableSQLObject
from sqlobject.sqlbuilder import LEFTJOINOn
from sqlobject import connectionForURI
sqlhub.processConnection = connectionForURI( 'sqlite:///:memory:', debug=True )
class named( InheritableSQLObject ):
name = StringCol( )
class zoo( named ):
# class zoo( SQLObject ):
# name = StringCol( )
cages = MultipleJoin( 'cage' )
class cage( named ):
# class cage( SQLObject ):
# name = StringCol( )
animals = MultipleJoin( 'animal' )
zoo = ForeignKey( 'zoo' )
class animal( named ):
# class animal( SQLObject ):
# name = StringCol( )
cage = ForeignKey( 'cage' )
named.createTable( )
zoo.createTable( )
cage.createTable( )
animal.createTable( )
z = zoo( name='myzoo' )
c1 = cage( name='firstcage', zoo=z )
c2 = cage( name='secondcage', zoo=z )
a11 = animal( name='tiger', cage=c1 )
a12 = animal( name='lion', cage=c1 )
a21 = animal( name='croc', cage=c2 )
a22 = animal( name='hypo', cage=c2 )
a23 = animal( name='fish', cage=c2 )
print '-'*80
joins = [ ]
joins.append( LEFTJOINOn( None, cage, animal.q.cageID==cage.q.id ) )
joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) )
print animal.select( zoo.q.name=='myzoo', join=joins ).count( )
If you comment out all references to 'named' and subclass from
SQLObject instead, the query works as expected and prints 5, the
correct number.
Cheers,
Daniel
-
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
___
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
On Sat, Jun 09, 2007 at 06:37:04PM +0200, Daniel Nogradi wrote: > if the model is changed slightly to use inheritance: > then the exact same query stops working. In what way? Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
> Multiple LEFTJOINOn queries seem to work if all classes subclass > SQLObject, but not if they subclass a subclass of > InheritableSQLObject. This is what I mean: > > > class zoo( SQLObject ): > name = StringCol( ) > cages = MultipleJoin( 'cage' ) > > class cage( SQLObject ): > name = StringCol( ) > animals = MultipleJoin( 'animal' ) > zoo = ForeignKey( 'zoo' ) > > class animal( SQLObject ): > name = StringCol( ) > cage = ForeignKey( 'cage' ) > > > So there are zoos, each zoo has a number of cages and each cage has a > number of animals. The following query selects the total number of > animals in a given zoo: > > > joins = [ ] > joins.append( LEFTJOINOn( None, cage, animal.q.cageID==cage.q.id ) ) > joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) ) > > print animal.select( zoo.q.name=='myzoo', join=joins ).count( ) > > > This is all fine, but if the model is changed slightly to use inheritance: > > > class named( InheritableSQLObject ): > name = StringCol( ) > > class zoo( named ): > cages = MultipleJoin( 'cage' ) > > class cage( named ): > animals = MultipleJoin( 'animal' ) > zoo = ForeignKey( 'zoo' ) > > class animal( named ): > cage = ForeignKey( 'cage' ) > > > then the exact same query stops working. Why is that? If it is a > feature and not a bug, what should be the correct query for the > getting the total number of animals? > > Daniel > python 2.5, SQLObject-0.10dev_r2660, sqlite. Just checked and the behaviour is the same with the latest revision 2716. - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
[SQLObject] LEFTJOINOn and InheritableSQLObject weirdness
Multiple LEFTJOINOn queries seem to work if all classes subclass SQLObject, but not if they subclass a subclass of InheritableSQLObject. This is what I mean: class zoo( SQLObject ): name = StringCol( ) cages = MultipleJoin( 'cage' ) class cage( SQLObject ): name = StringCol( ) animals = MultipleJoin( 'animal' ) zoo = ForeignKey( 'zoo' ) class animal( SQLObject ): name = StringCol( ) cage = ForeignKey( 'cage' ) So there are zoos, each zoo has a number of cages and each cage has a number of animals. The following query selects the total number of animals in a given zoo: joins = [ ] joins.append( LEFTJOINOn( None, cage, animal.q.cageID==cage.q.id ) ) joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) ) print animal.select( zoo.q.name=='myzoo', join=joins ).count( ) This is all fine, but if the model is changed slightly to use inheritance: class named( InheritableSQLObject ): name = StringCol( ) class zoo( named ): cages = MultipleJoin( 'cage' ) class cage( named ): animals = MultipleJoin( 'animal' ) zoo = ForeignKey( 'zoo' ) class animal( named ): cage = ForeignKey( 'cage' ) then the exact same query stops working. Why is that? If it is a feature and not a bug, what should be the correct query for the getting the total number of animals? Daniel python 2.5, SQLObject-0.10dev_r2660, sqlite. - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
