Default identity w/ SQLAlchemy - missing relations
Robin Haswell
I'm returning to TG again for a private project, and I'm aiming at using
SQLAlchemy to get over the hideous performance I experience with SQLObject.
Anyway, this issue must have been raised before, but my problem is that
the relations defined with ActiveMapper for identity don't appear. Quick TB:
rob@library:/www/dg-admin$ tg-admin shell
Python 2.4.3 (#2, Oct 6 2006, 07:52:30)
[GCC 4.0.3 (Ubuntu 4.0.3-1ubuntu5)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(CustomShell)
>>> User.get(1).groups
[2006-10-23 14:40:52,553] [engine]: SELECT tg_user.user_id AS
tg_user_user_id, tg_user.created AS tg_user_created, tg_user.user_name
AS tg_user_user_name, tg_user.display_name AS tg_user_display_name,
tg_user.password AS tg_user_password, tg_user.email_address AS
tg_user_email_address
FROM tg_user
WHERE tg_user.user_id = %s ORDER BY tg_user.user_id
[2006-10-23 14:40:52,553] [engine]: [1]
Traceback (most recent call last):
File "<console>", line 1, in ?
AttributeError: 'User' object has no attribute 'groups'
>>>
And my identity code is the one provided by the template, but I'll paste
it below anyway. I've tried commenting out either the Group.users or
User.groups line, to no avail. Version info:
rob@library:/www/dg-admin$ tg-admin info
TurboGears Complete Version Information
TurboGears requires:
* TurboGears 1.0b1
* nose 0.9.0
* configobj 4.3.2
* RuleDispatch 0.5a0.dev-r2115
* setuptools 0.6c3
* FormEncode 0.6
* cElementTree 1.0.5-20051216
* PasteScript 0.9.8
* elementtree 1.2.6
* simplejson 1.4
* SQLObject 0.7.1
* CherryPy 2.2.1
* TurboKid 0.9.9
* TurboCheetah 0.9.5
* TurboJson 0.9.9
* PyProtocols 1.0a0
* Cheetah 2.0rc7
* PasteDeploy 0.9.6
* Paste 0.9.8.1
* FormEncode 0.6
* kid 0.9.3
* Cheetah 2.0rc7
* elementtree 1.2.6
Identity Providers
* sqlobject (TurboGears 1.0b1)
* sqlalchemy (TurboGears 1.0b1)
Any help would be appreciated.
Code below.
Cheers
-Rob
class Visit(ActiveMapper):
class mapping:
__table__ = "visit"
visit_key = column(String(40), primary_key=True)
created = column(DateTime, nullable=False, default=datetime.now)
expiry = column(DateTime)
def lookup_visit(cls, visit_key):
return Visit.get(visit_key)
lookup_visit = classmethod(lookup_visit)
# tables for SQLAlchemy identity
user_group = Table("user_group", metadata,
Column("user_id", Integer,
ForeignKey("tg_user.user_id"),
primary_key=True),
Column("group_id", Integer,
ForeignKey("tg_group.group_id"),
primary_key=True))
group_permission = Table("group_permission", metadata,
Column("group_id", Integer,
ForeignKey("tg_group.group_id"),
primary_key=True),
Column("permission_id", Integer,
ForeignKey("permission.permission_id"),
primary_key=True))
class VisitIdentity(ActiveMapper):
class mapping:
__table__ = "visit_identity"
visit_key = column(String(40), # foreign_key="visit.visit_key",
primary_key=True)
user_id = column(Integer, foreign_key="tg_user.user_id", index=True)
class Group(ActiveMapper):
"""
An ultra-simple group definition.
"""
class mapping:
__table__ = "tg_group"
group_id = column(Integer, primary_key=True)
group_name = column(Unicode(16), unique=True)
display_name = column(Unicode(255))
created = column(DateTime, default=datetime.now)
users = many_to_many("User", user_group, backref="groups")
permissions = many_to_many("Permission", group_permission,
backref="groups")
class User(ActiveMapper):
"""
Reasonably basic User definition. Probably would want additional
attributes.
"""
class mapping:
__table__ = "tg_user"
user_id = column(Integer, primary_key=True)
user_name = column(Unicode(16), unique=True)
email_address = column(Unicode(255), unique=True)
display_name = column(Unicode(255))
password = column(Unicode(40))
created = column(DateTime, default=datetime.now)
groups = many_to_many("Group", user_group, backref="users")
def permissions(self):
perms = set()
for g in self.groups:
perms = perms | set(g.permissions)
return perms
permissions = property(permissions)
class Permission(ActiveMapper):
class mapping:
__table__ = "permission"
permission_id = column(Integer, primary_key=True)
permission_name = column(Unicode(16), unique=True)
description = column(Unicode(255))
groups = many_to_many("Group", group_permission,
backref="permissions")
Lee McFadden
>
> I've tried commenting out either the Group.users or
> User.groups line, to no avail.
Commenting out one or the other of those lines (but not both) is the
fix for this little bug in the quickstart template. Did you restart
tg-admin shell after making the changes to your model.py?
This bug has been fixed in SVN and will be available with the next release.
Lee
--
Lee McFadden
blog: http://www.splee.co.uk
work: http://fireflisystems.com
Robin Haswell
> Commenting out one or the other of those lines (but not both) is the
> fix for this little bug in the quickstart template. Did you restart
> tg-admin shell after making the changes to your model.py?
Yes! I'm not that stupid :-P Observe:
rob@library:/www/dg-admin$ tg-admin shell
Python 2.4.3 (#2, Oct 6 2006, 07:52:30)
[GCC 4.0.3 (Ubuntu 4.0.3-1ubuntu5)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(CustomShell)
>>> User.relations
{'groups': <sqlalchemy.ext.activemapper.many_to_many object at 0xb7442aec>}
>>> Group.relations
{'permissions': <sqlalchemy.ext.activemapper.many_to_many object at
0xb744252c>}
>>> User.get(1).groups
[2006-10-23 15:51:50,663] [engine]: SELECT tg_user.user_id AS
tg_user_user_id, tg_user.created AS tg_user_created, tg_user.user_name
AS tg_user_user_name, tg_user.display_name AS tg_user_display_name,
tg_user.password AS tg_user_password, tg_user.email_address AS
tg_user_email_address
FROM tg_user
WHERE tg_user.user_id = %s ORDER BY tg_user.user_id
[2006-10-23 15:51:50,664] [engine]: [1]
Traceback (most recent call last):
File "<console>", line 1, in ?
AttributeError: 'User' object has no attribute 'groups'
>>> Group.get(1).users
[2006-10-23 15:51:52,196] [engine]: SELECT tg_group.group_name AS
tg_group_group_name, tg_group.group_id AS tg_group_group_id,
tg_group.display_name AS tg_group_display_name, tg_group.created AS
tg_group_created
FROM tg_group
WHERE tg_group.group_id = %s ORDER BY tg_group.group_id
[2006-10-23 15:51:52,196] [engine]: [1]
Traceback (most recent call last):
File "<console>", line 1, in ?
AttributeError: 'Group' object has no attribute 'users'
>>>
Notice how Group.relations doesn't contain the "users" relation, which
it would if I hadn't commented one of them out.
-Rob
Jose Soares
I would like to use the PaginateDataGrid with SQLAlchemy.
I see there's a demo project using SQLObject.
I would like to know if is there any demo using SQLAlchemy?
jo
Lee McFadden
>
Sorry Rob, I wasn't insulting your intelligence - sometimes the most
simple and obvious possibility is the correct one :)
I'm now getting the same thing, although I wasn't last night when
helping someone in IRC. Have you also upgraded to SQLAlchemy 0.3 or
installed SA since 0.3 was released?
Jorge Vargas
Alastair Houghton
>
> Hey guys
>
> I'm returning to TG again for a private project, and I'm aiming at
> using
> SQLAlchemy to get over the hideous performance I experience with
> SQLObject.
>
> Anyway, this issue must have been raised before, but my problem is
> that
> the relations defined with ActiveMapper for identity don't appear.
> Quick TB:
I think if you look in the bug tracking system for TurboGears that
you'll find
the answer to this question. I know I found it somewhere on the
TurboGears site.
The problem is the many_to_many() mappings, which need to be on *one*
side but
*not* the other. Delete the mappings from the Group class, for
instance, and
the problem will go away. The backref arguments will create the
attributes on
the class without a many_to_many() mapping anyway.
Kind regards,
Alastair.
Robin Haswell
> I'm now getting the same thing, although I wasn't last night when
> helping someone in IRC. Have you also upgraded to SQLAlchemy 0.3 or
> installed SA since 0.3 was released?
>
> Lee
I was using SA 0.28, which came with TG 1.01b. I have since performed
`sudo easy_install -U -Z SQLAlchemy` to get SA 0.30 and the problem is
still there. I also get a muthaload of debugging output! But no fix.
What's odd though is that my custom objects using one_to_many seem to
work fine. If Jonathan LaCour (sp?) has no clues I'll set up my own
many_to_many and see if it makes a difference.
One thought I had is that I'm using MySQL 5, and although SA seems to
identify its relational columns with a foreign_key attribute (little too
automagic for my liking), MySQL doesn't preserve relations without
manual intervention.
However, starting with a fresh SQLite database yields the same result,
so that last paragraph is now a bit redundant.
-Rob
Jorge Vargas
Robin Haswell
> And my identity code is the one provided by the template, but I'll paste
> it below anyway. I've tried commenting out either the Group.users or
> User.groups line, to no avail. Version info:
I have fixed this! You need to comment out ALL the duplicate relations,
not just the ones interfering with what you're doing. I removed the two
dups from Group as per the changeset Jorge mentioned and everything it
hunky dory. I'll update wiki pages now.
-Rob
jose
Hi Jorge,
I patched paginate.py and I tryed it, the pagination is Ok, but the
'sort' doesn't work.
If you want take a look at it, the demo is here:
http://www.allevatori.com/download/paginatedatagrid.tgz
jo
Jonathan LaCour
> What's odd though is that my custom objects using one_to_many seem to
> work fine. If Jonathan LaCour (sp?) has no clues I'll set up my own
> many_to_many and see if it makes a difference.
I see similar odd results. It looks like it works fine when I test
similar relationships built in classes outside of TurboGears, but for
some reason there is a problem with the specific ones in TurboGears.
I am not exactly sure why this is, but it looks like it was fixed by
not using ActiveMapper for now, which is fine. Its less readable, but
it certainly works :)
BTW, for those of you who are curious, there is some good stuff coming
with ActiveMapper soon. I have been looking for someone to take it to
the next level, since I want to dedicate my time to some other projects
and I think I may have found someone who wants to take it in a very
interesting direction that could benefit TurboGears. I'll let you all
know how it all pans out!
--
Jonathan LaCour
http://cleverdevil.org
Robin Haswell
> I see similar odd results. It looks like it works fine when I test
> similar relationships built in classes outside of TurboGears, but for
> some reason there is a problem with the specific ones in TurboGears.
Did you read my latest message?
http://groups-beta.google.com/group/turbogears/msg/43dc969aa3f29cff
Jorge Vargas
> -Rob
>
> >
>
Jonathan LaCour
> Did you read my latest message?
> http://groups-beta.google.com/group/turbogears/msg/43dc969aa3f29cff
Ah hah! I knew I must have missed something. Glad to see it was
something simple :)
ben.ha...@gmail.com
code is slow there is no turboboost switch. Its like my included
batteries are one shots. I need jumper cables over here.
Robin Haswell
Sorry mate, SQLObject is slow by design.
-Rob
Steve Holden
slower than hand-crafted SQL because of the additional generality they
provide. I'm sure Ian Bicking didn't start out with slowness asa design
goal :)
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
Robin Haswell
> Perhaps that phrase is a little misleading. Object mappers tend to be
> slower than hand-crafted SQL because of the additional generality they
> provide. I'm sure Ian Bicking didn't start out with slowness asa design
> goal :)
Perhaps. I don't think Ian Bicking envisaged people working with large
datasets in SQLObject - the technique of selecting a list of IDs then
selecting the data for each ID in a seperate query is horrific.
SQLAlchemy seems to perform much better, by selecting all the data it
needs at once then constructing the objects from that dataset, *vastly*
increasing the speed, to a level on a par with user-generated code.
-Rob
Stuart Clarke
*** This is of great interest to me. Can someone explain the reason for
SO being slow? I'm using it in what will hopefully become a very
high-traffic site, and although I'm memcached-ing and squid-ing the hell
out of everything, this is still of great concern.
Is it slow because it doesn't pre-compile queries, or is it because of
the actual query-text it uses? Can its performance be boosted by
judicious use of indices, etc?
And the final question: is SQLAlchemy any better in this regard? I'm
liking SO right now, because of things like paginate and Catwalk. So
I'm wondering, how hard is it to transition an existing site (with
database) from SO to SA?
Thanks everyone,
Stuart
Marco Mariani
> And the final question: is SQLAlchemy any better in this regard?
complexity the schema is covering.
Having a rule of a single-column surrogate primary key can force the
developer to use more joins or more queries than he would like, for
instance.
I've never needed to benchmark queries, but I have the feeling that most
times the performance problems comes from the schema design, I am not
talking about huge sets of data but the kind of heavily related tables
that are typical in an enterprise application.
Sqlalchemy poses no restrictions on the relations (I _do_ love
autoload), or the queries you can map an object to, and that sometimes
helps performance.
This is not an attempt to start a religion war on surrogate keys ;-)
Jorge Vargas
>
> On Thu, 2006-10-26 at 09:20 +0100, Robin Haswell wrote:
> > ben.ha...@gmail.com wrote:
> > > I think we need an optimizing sql compiler for tg. All my sqlobject
> > > code is slow there is no turboboost switch. Its like my included
> > > batteries are one shots. I need jumper cables over here.
> >
> > Sorry mate, SQLObject is slow by design.
>
> *** This is of great interest to me. Can someone explain the reason for
> SO being slow? I'm using it in what will hopefully become a very
> high-traffic site, and although I'm memcached-ing and squid-ing the hell
> out of everything, this is still of great concern.
>
the default ones populate lists which has to fetch each object and it
ends up being slow.
Now you can optimize this by using SO* instead of plain cols as
SORelatedJoin instead of RelatedJoin,etc.
> Is it slow because it doesn't pre-compile queries, or is it because of
> the actual query-text it uses? Can its performance be boosted by
> judicious use of indices, etc?
>
it does precompile, it also catches.
> And the final question: is SQLAlchemy any better in this regard? I'm
> liking SO right now, because of things like paginate and Catwalk. So
> I'm wondering, how hard is it to transition an existing site (with
> database) from SO to SA?
>
you can hop on top of activemapper and the changes are close to none.
doing the changes to full port to the regular mapper is not a big deal either.
> Thanks everyone,
>
> Stuart
>
>
> >
>
Stuart Clarke
> > > ben.ha...@gmail.com wrote:
> > > > I think we need an optimizing sql compiler for tg. All my sqlobject
> > > > code is slow there is no turboboost switch. Its like my included
> > > > batteries are one shots. I need jumper cables over here.
> > >
> > > Sorry mate, SQLObject is slow by design.
> >
> > *** This is of great interest to me. Can someone explain the reason for
> > SO being slow? I'm using it in what will hopefully become a very
> > high-traffic site, and although I'm memcached-ing and squid-ing the hell
> > out of everything, this is still of great concern.
> >
> There's still a performance problem with Many-to-Many relationships,
> the default ones populate lists which has to fetch each object and it
> ends up being slow.
>
> Now you can optimize this by using SO* instead of plain cols as
> SORelatedJoin instead of RelatedJoin,etc.
*** Thanks for the info. Is this documented anywhere? I'm specifically
interested to know:
1. How exactly the SO* mechanisms work, and improve things over the
vanilla equivalents.
2. What disadvantages they have over the vanilla equivalents (otherwise
we'd be using them all the time, right?).
3. I've seen mention of an SQLRelatedJoin, and an SOSQLRelatedJoin. How
do they fit in?
Thanks again,
Stuart
Jorge Vargas
they are exactly the same in interface it's just that the SQL versions
are faster because they use the q values instead of list joins.
I'm affaid to say that the only docs about it are at
http://www.sqlobject.org/sqlobject/joins.py.html?f=275&l=277#275
but again they are design to be interchangable
> Thanks again,
>
> Stuart
>
>
> >
>