[SQLObject] ticket/294
I was wondering what the forseeable future of http://sqlobject.gcu.info/trac/ticket/294 is. Are there plans to fix this bug or is the general approach is that inheriable sqlobjects should only be used in simple cases? It would be great to know because I need to design my model accordingly. Cheers, Daniel - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] The proper way to delete an instance of an SQLObject that is linked as a RelatedJoin
Yes, you need to delete everything manually that is referenced by anything that is to be deleted. No, you don't. .destroySelf() in SQLObject 0.7.2 was extended to automatically remove rows from the intermediate table. Does it work? Maybe I'm misunderstanding you but if a foreignkey points to an object that is deleted, the object that refers to the deleted object will not be: from sqlobject import * sqlhub.processConnection = connectionForURI( 'sqlite:/:memory:' ) class city( SQLObject ): buildings = MultipleJoin( 'building' ) class building( SQLObject ): city = ForeignKey( 'city' ) city.createTable( ) building.createTable( ) c = city( ) b1 = building( city=c ) b2 = building( city=c ) c.destroySelf( ) print building.select( ).count( ) print b1.city This will print 2 and then an SQLObjectNotFound exception will be raised so you need to manually delete all buildings that refer to a deleted city. I guess you had RelatedJoin in mind. - 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] The proper way to delete an instance of an SQLObject that is linked as a RelatedJoin
I have two simple SQLObject classes, WebTag and WebTopic that are related using a RelatedJoin: class WebTag(SQLObject): Name = StringCol( alternateID=True) Topics = RelatedJoin('WebTopic') class WebTopic(SQLObject): Name = StringCol( unique=True ) Format = StringCol( default=rst ) Title = StringCol( ) Subtitle = StringCol( default=None) Description = StringCol(default=None) Keywords = StringCol( default=None ) Author = StringCol( default=None ) DateTimePosted = DateTimeCol() DateTimeLastModified = DateTimeCol() UUID = StringCol() Content = StringCol( default=None ) Tags = RelatedJoin('WebTag') What is the appropriate way to delete an instance of a WebTopic or a WebTag so that RelatedJoin won't be broken? Right now I have a simple: WebTopic.delete(id_to_delete) or a WebTag.delete(id_to_delete) But I just started getting this error, so I must have missed something in the documentation: for a in WebTag.selectBy(Name='python'): ... print a.Name ... print a.Topics ... python Traceback (most recent call last): File stdin, line 3, in module File string, line 1, in lambda File /usr/lib/python2.5/site-packages/sqlobject/joins.py, line 207, in performJoin return self._applyOrderBy([self.otherClass.get(id, conn) for (id,) in ids if id is not None], self.otherClass) File /usr/lib/python2.5/site-packages/sqlobject/main.py, line 913, in get val._init(id, connection, selectResults) File /usr/lib/python2.5/site-packages/sqlobject/main.py, line 958, in _init raise SQLObjectNotFound, The object %s by the ID %s does not exist % (self.__class__.__name__, self.id) sqlobject.main.SQLObjectNotFound: The object WebTopic by the ID 12 does not exist Any help and suggestions would be appreciated. The db can be wiped and started over, because this is a little test system. Should I have dropped all references to the missing WebTopic instance from all WebTag instances? Yes, you need to delete everything manually that is referenced by anything that is to be deleted. A question to the experts: I usually do this by overloading destroySelf so that it loops over all references, is that a good idea? - 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
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
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
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
[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
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
Re: [SQLObject] Vacation
Hello everyone. Hello and goodbye! I am leaving the town for a vacation. Please do not stop asking and answering questions. Help each other. Discuss bugs and patches in the list, submit them to the Trac or SF tracker. I'll be back in the beginning of June. Oleg. Have a good time! - 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] subquery with IN operator
Hi list, I would like to build an SQL query with sqlbuilder that contains several IN operators but I don't seem to succeed. The model is quite simple, there are several cities, each city contains a number of buildings, each building contains a number of floors and each floor contains a number of rooms. So it's pretty straightforward: class city(SQLObject): name = StringCol( ) buildings = MultipleJoin( 'building' ) class building(SQLObject): name = StringCol( ) complex = ForeignKey( 'city' ) floors = MultipleJoin( 'floor' ) class floor(SQLObject): name = StringCol( ) building = ForeignKey( 'building' ) rooms = MultipleJoin( 'room' ) class room(SQLObject): name = StringCol( ) floor = ForeignKey( 'floor' ) Now I would like to have a single SQL query for selecting all distinct room names in a given city. The problem is that with the above model both building.q.complex and building.q.floors throw an AttributeError so I can't use these in a room.select( ) statement together with IN and my impression so far has been that in such a select statement one should use these magic 'q' variables. Any ideas? - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.phpp=sourceforgeCID=DEVDEV ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] order by several columns and inheritance
BTW, is SQLObject being developed still? Does it make sense to submit bugs, patches, etc? Something related is whether SQLObject2 will actually be released any time soon or is that abandonware already? On 1/20/07, Daniel Nogradi [EMAIL PROTECTED] wrote: And it seems that the problem is that the query doesn't have FROM t, parent only FROM parent (among other things) while in the first case (only one orderBy column) the query was generated correctly. Any ideas? Seems like a bug in inheritance. Okay, I submitted it to the bug tracker. - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.phpp=sourceforgeCID=DEVDEV ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
[SQLObject] order by several columns and inheritance
As far as I can tell it is possible to pass a list to the orderBy keyword in a select to produce results that are ordered with respect to several columns. However when I try to do the same on inherited classes it fails (ordering by a single column works): --- class parent( InheritableSQLObject ): pass class t( parent ): c = IntCol( ) d = IntCol( ) --- Having a single orderBy column works, for example: --- for i in t.select( orderBy=t.q.d ): print i SELECT parent.id, parent.child_name FROM t, parent WHERE (((parent.child_name) = ('t')) AND ((t.id) = (parent.id))) ORDER BY t.d SELECT t.id, t.c, t.d, t.child_name FROM t WHERE ((t.id) = (1)) but multiple orderBy columns throw an exception in the first query: for i in t.select( orderBy=( t.q.c, t.q.d ) ): print i SELECT parent.id, parent.child_name FROM parent WHERE ((parent.child_name) = ('t')) ORDER BY t.c, t.d sqlobject.dberrors.OperationalError: no such column: t.c - And it seems that the problem is that the query doesn't have FROM t, parent only FROM parent (among other things) while in the first case (only one orderBy column) the query was generated correctly. Any ideas? - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.phpp=sourceforgeCID=DEVDEV ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] order by several columns and inheritance
And it seems that the problem is that the query doesn't have FROM t, parent only FROM parent (among other things) while in the first case (only one orderBy column) the query was generated correctly. Any ideas? Seems like a bug in inheritance. Okay, I submitted it to the bug tracker. - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.phpp=sourceforgeCID=DEVDEV ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] When are connections opened and closed?
I'm sure it's a noobish question, but when are connections opened and closed exactly? Exactly: they are opened and closed in dbconnection.py, in class DBAPI. Thanks very much again. So basically I don't have to worry about opening and closing the connection at all, right? It is opened automatically at the first request and closed automatically when the process ends? A more important concern I had is that whether I need to worry about a possible overhead of automatic opening and closing between requests, i.e. can I be sure that there is no unnecessary opening/closing going on behind the scenes? - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.phpp=sourceforgeCID=DEVDEV ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss