[Sqlalchemy-commits] [1393] sqlalchemy/branches/schema/lib/sqlalchemy: fixed HAVING order, fixed map
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1393] sqlalchemy/branches/schema/lib/sqlalchemy: fixed HAVING order, fixed mapper column types propigated in UPDATE/DELETE
|
From: <co...@sq...> - 2006-05-05 00:44:36
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head><style type="text/css"><!-- #msg dl { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; } #msg dt { float: left; width: 6em; font-weight: bold; } #msg dt:after { content:':';} #msg dl, #msg dt, #msg ul, #msg li { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; } #msg dl a { font-weight: bold} #msg dl a:link { color:#fc3; } #msg dl a:active { color:#ff0; } #msg dl a:visited { color:#cc6; } h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; } #msg pre { overflow: auto; background: #ffc; border: 1px #fc0 solid; padding: 6px; } #msg ul, pre { overflow: auto; } #patch { width: 100%; } #patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;} #patch .propset h4, #patch .binary h4 {margin:0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;} #patch .propset .diff, #patch .binary .diff {padding:10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;} #patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;} #patch .lines, .info {color:#888;background:#fff;} --></style> <title>[1393] sqlalchemy/branches/schema/lib/sqlalchemy: fixed HAVING order, fixed mapper column types propigated in UPDATE/DELETE</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1393</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-05-04 19:44:26 -0500 (Thu, 04 May 2006)</dd> </dl> <h3>Log Message</h3> <pre>fixed HAVING order, fixed mapper column types propigated in UPDATE/DELETE</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemybranchesschemadocbuildcontentadv_datamappingtxt">sqlalchemy/branches/schema/doc/build/content/adv_datamapping.txt</a></li> <li><a href="#sqlalchemybranchesschemalibsqlalchemyansisqlpy">sqlalchemy/branches/schema/lib/sqlalchemy/ansisql.py</a></li> <li><a href="#sqlalchemybranchesschemalibsqlalchemyormmapperpy">sqlalchemy/branches/schema/lib/sqlalchemy/orm/mapper.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemybranchesschemadocbuildcontentadv_datamappingtxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/adv_datamapping.txt (1392 => 1393)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/adv_datamapping.txt 2006-05-05 00:42:42 UTC (rev 1392) +++ sqlalchemy/branches/schema/doc/build/content/adv_datamapping.txt 2006-05-05 00:44:26 UTC (rev 1393) </span><span class="lines">@@ -38,8 +38,12 @@ </span><span class="cx"> Column('user_id', INT, ForeignKey("users")), </span><span class="cx"> Column('keyword_id', INT, ForeignKey("keywords")) </span><span class="cx"> ) </span><del>-### Overriding Properties {@name=overriding} </del><span class="cx"> </span><ins>+ +### More On Mapper Properties {@name=properties} + +#### Overriding Properties {@name=overriding} + </ins><span class="cx"> A common request is the ability to create custom class properties that override the behavior of setting/getting an attribute. Currently, the easiest way to do this in SQLAlchemy is how it would be done in any Python program; define your attribute with a different name, such as "_attribute", and use a property to get/set its value. The mapper just needs to be told of the special name: </span><span class="cx"> </span><span class="cx"> {python} </span><span class="lines">@@ -58,8 +62,28 @@ </span><span class="cx"> </span><span class="cx"> In a later release, SQLAlchemy will also allow _get_email and _set_email to be attached directly to the "email" property created by the mapper, and will also allow this association to occur via decorators. </span><span class="cx"> </span><del>-### More On Relations {@name=relations} </del><ins>+### Overriding Column Names {@name=colname} </ins><span class="cx"> </span><ins>+When mappers are constructed, by default the column names in the Table metadata are used as the names of attributes on the mapped class. This can be customzed within the properties by stating the key/column combinations explicitly: + + {python} + user_mapper = mapper(User, users_table, properties={ + 'id' : users_table.c.user_id, + 'name' : users_table.c.user_name, + }) + +In the situation when column names overlap in a mapper against multiple tables, columns may be referenced together with a list: + + {python} + # join users and addresses + usersaddresses = sql.join(users_table, addresses_table, users_table.c.user_id == addresses_table.c.user_id) + m = mapper(User, usersaddresses, + properties = { + 'id' : [users_table.c.user_id, addresses_table.c.user_id], + } + ) + + </ins><span class="cx"> #### Custom Join Conditions {@name=customjoin} </span><span class="cx"> </span><span class="cx"> When creating relations on a mapper, most examples so far have illustrated the mapper and relationship joining up based on the foreign keys of the tables they represent. in fact, this "automatic" inspection can be completely circumvented using the `primaryjoin` and `secondaryjoin` arguments to `relation`, as in this example which creates a User object which has a relationship to all of its Addresses which are in Boston: </span><span class="lines">@@ -70,10 +94,10 @@ </span><span class="cx"> class Address(object): </span><span class="cx"> pass </span><span class="cx"> </span><del>- mapper(Address, addresses) - mapper(User, users, properties={ </del><ins>+ mapper(Address, addresses_table) + mapper(User, users_table, properties={ </ins><span class="cx"> 'boston_addreses' : relation(Address, primaryjoin= </span><del>- and_(users.c.user_id==Address.c.user_id, </del><ins>+ and_(users_table.c.user_id==Address.c.user_id, </ins><span class="cx"> Addresses.c.city=='Boston')) </span><span class="cx"> }) </span><span class="cx"> </span><span class="lines">@@ -85,9 +109,9 @@ </span><span class="cx"> class Keyword(object): </span><span class="cx"> pass </span><span class="cx"> Keyword.mapper = mapper(Keyword, keywords) </span><del>- User.mapper = mapper(User, users, properties={ </del><ins>+ User.mapper = mapper(User, users_table, properties={ </ins><span class="cx"> 'keywords':relation(Keyword.mapper, </span><del>- primaryjoin=users.c.user_id==userkeywords.c.user_id, </del><ins>+ primaryjoin=users_table.c.user_id==userkeywords.c.user_id, </ins><span class="cx"> secondaryjoin=userkeywords.c.keyword_id==keywords.c.keyword_id </span><span class="cx"> ) </span><span class="cx"> }) </span><span class="lines">@@ -97,19 +121,19 @@ </span><span class="cx"> The previous example leads in to the idea of joining against the same table multiple times. Below is a User object that has lists of its Boston and New York addresses, both lazily loaded when they are first accessed: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- User.mapper = mapper(User, users, properties={ </del><ins>+ User.mapper = mapper(User, users_table, properties={ </ins><span class="cx"> 'boston_addreses' : relation(Address.mapper, primaryjoin= </span><del>- and_(users.c.user_id==Address.c.user_id, </del><ins>+ and_(users_table.c.user_id==Address.c.user_id, </ins><span class="cx"> Addresses.c.city=='Boston')), </span><span class="cx"> 'newyork_addresses' : relation(Address.mapper, primaryjoin= </span><del>- and_(users.c.user_id==Address.c.user_id, </del><ins>+ and_(users_table.c.user_id==Address.c.user_id, </ins><span class="cx"> Addresses.c.city=='New York')), </span><span class="cx"> }) </span><span class="cx"> </span><span class="cx"> A complication arises with the above pattern if you want the relations to be eager loaded. Since there will be two separate joins to the addresses table during an eager load, an alias needs to be used to separate them. You can create an alias of the addresses table to separate them, but then you are in effect creating a brand new mapper for each property, unrelated to the main Address mapper, which can create problems with commit operations. So an additional argument `use_alias` can be used with an eager relationship to specify the alias to be used just within the eager query: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- User.mapper = mapper(User, users, properties={ </del><ins>+ User.mapper = mapper(User, users_table, properties={ </ins><span class="cx"> 'boston_addreses' : relation(Address.mapper, primaryjoin= </span><span class="cx"> and_(User.c.user_id==Address.c.user_id, </span><span class="cx"> Addresses.c.city=='Boston'), lazy=False, use_alias=True), </span><span class="lines">@@ -159,35 +183,35 @@ </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> # disable all ordering </span><del>- mapper = mapper(User, users, order_by=None) </del><ins>+ mapper = mapper(User, users_table, order_by=None) </ins><span class="cx"> </span><span class="cx"> # order by a column </span><del>- mapper = mapper(User, users, order_by=users.c.user_id) </del><ins>+ mapper = mapper(User, users_table, order_by=users_tableusers_table.c.user_id) </ins><span class="cx"> </span><span class="cx"> # order by multiple items </span><del>- mapper = mapper(User, users, order_by=[users.c.user_id, desc(users.c.user_name)]) </del><ins>+ mapper = mapper(User, users_table, order_by=[users_table.c.user_id, desc(users_table.c.user_name)]) </ins><span class="cx"> </span><span class="cx"> "order_by" can also be specified to an individual `select` method, overriding all other per-engine/per-mapper orderings: </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> # order by a column </span><del>- l = mapper.select(users.c.user_name=='fred', order_by=users.c.user_id) </del><ins>+ l = mapper.select(users_table.c.user_name=='fred', order_by=users_table.c.user_id) </ins><span class="cx"> </span><span class="cx"> # order by multiple criterion </span><del>- l = mapper.select(users.c.user_name=='fred', order_by=[users.c.user_id, desc(users.c.user_name)]) </del><ins>+ l = mapper.select(users_table.c.user_name=='fred', order_by=[users_table.c.user_id, desc(users_table.c.user_name)]) </ins><span class="cx"> </span><span class="cx"> For relations, the "order_by" property can also be specified to all forms of relation: </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> # order address objects by address id </span><del>- mapper = mapper(User, users, properties = { - 'addresses' : relation(mapper(Address, addresses), order_by=addresses.c.address_id) </del><ins>+ mapper = mapper(User, users_table, properties = { + 'addresses' : relation(mapper(Address, addresses_table), order_by=addresses_table.c.address_id) </ins><span class="cx"> }) </span><span class="cx"> </span><span class="cx"> # eager load with ordering - the ORDER BY clauses of parent/child will be organized properly </span><del>- mapper = mapper(User, users, properties = { - 'addresses' : relation(mapper(Address, addresses), order_by=desc(addresses.c.email_address), eager=True) - }, order_by=users.c.user_id) </del><ins>+ mapper = mapper(User, users_table, properties = { + 'addresses' : relation(mapper(Address, addresses_table), order_by=desc(addresses_table.c.email_address), eager=True) + }, order_by=users_table.c.user_id) </ins><span class="cx"> </span><span class="cx"> ### Limiting Rows {@name=limits} </span><span class="cx"> </span><span class="lines">@@ -197,7 +221,7 @@ </span><span class="cx"> class User(object): </span><span class="cx"> pass </span><span class="cx"> </span><del>- mapper(User, users) </del><ins>+ mapper(User, users_table) </ins><span class="cx"> {sql}r = session.query(User).select(limit=20, offset=10) </span><span class="cx"> SELECT users.user_id AS users_user_id, </span><span class="cx"> users.user_name AS users_user_name, users.password AS users_password </span><span class="lines">@@ -212,8 +236,8 @@ </span><span class="cx"> pass </span><span class="cx"> class Address(object): </span><span class="cx"> pass </span><del>- mapper(User, users, properties={ - 'addresses' : relation(mapper(Address, addresses), lazy=False) </del><ins>+ mapper(User, users_table, properties={ + 'addresses' : relation(mapper(Address, addresses_table), lazy=False) </ins><span class="cx"> }) </span><span class="cx"> r = session.query(User).select(User.c.user_name.like('F%'), limit=20, offset=10) </span><span class="cx"> {opensql}SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, </span><span class="lines">@@ -230,27 +254,7 @@ </span><span class="cx"> </span><span class="cx"> The main WHERE clause as well as the limiting clauses are coerced into a subquery; this subquery represents the desired result of objects. A containing query, which handles the eager relationships, is joined against the subquery to produce the result. </span><span class="cx"> </span><del>-### Overriding Column Names {@name=colname} </del><span class="cx"> </span><del>-When mappers are constructed, by default the column names in the Table metadata are used as the names of attributes on the mapped class. This can be customzed within the properties by stating the key/column combinations explicitly: - - {python} - user_mapper = mapper(User, users, properties={ - 'id' : users.c.user_id, - 'name' : users.c.user_name, - }) - -In the situation when column names overlap in a mapper against multiple tables, columns may be referenced together with a list: - - {python} - # join users and addresses - usersaddresses = sql.join(users, addresses, users.c.user_id == addresses.c.user_id) - m = mapper(User, usersaddresses, - properties = { - 'id' : [users.c.user_id, addresses.c.user_id], - } - ) - </del><span class="cx"> ### Deferred Column Loading {@name=deferred} </span><span class="cx"> </span><span class="cx"> This feature allows particular columns of a table to not be loaded by default, instead being loaded later on when first referenced. It is essentailly "column-level lazy loading". This feature is useful when one wants to avoid loading a large text or binary field into memory when its not needed. Individual columns can be lazy loaded by themselves or placed into groups that lazy-load together. </span><span class="lines">@@ -314,8 +318,8 @@ </span><span class="cx"> pass </span><span class="cx"> </span><span class="cx"> # a 'lazy' relationship </span><del>- mapper(User, users, properties = { - 'addreses':relation(mapper(Address, addresses), lazy=True) </del><ins>+ mapper(User, users_table, properties = { + 'addreses':relation(mapper(Address, addresses_table), lazy=True) </ins><span class="cx"> }) </span><span class="cx"> </span><span class="cx"> # copy the mapper and convert 'addresses' to be eager </span><span class="lines">@@ -450,8 +454,8 @@ </span><span class="cx"> {python} </span><span class="cx"> AddressUser.mapper = mapper( </span><span class="cx"> AddressUser, </span><del>- addresses, inherits=User.mapper, - inherit_condition=users.c.user_id==addresses.c.user_id </del><ins>+ addresses_table, inherits=User.mapper, + inherit_condition=users_table.c.user_id==addresses_table.c.user_id </ins><span class="cx"> ) </span><span class="cx"> </span><span class="cx"> ### Mapping a Class against Multiple Tables {@name=joins} </span><span class="lines">@@ -464,7 +468,7 @@ </span><span class="cx"> pass </span><span class="cx"> </span><span class="cx"> # define a Join </span><del>- j = join(users, addresses) </del><ins>+ j = join(users_table, addresses_table) </ins><span class="cx"> </span><span class="cx"> # map to it - the identity of an AddressUser object will be </span><span class="cx"> # based on (user_id, address_id) since those are the primary keys involved </span><span class="lines">@@ -473,8 +477,8 @@ </span><span class="cx"> A second example: </span><span class="cx"> {python} </span><span class="cx"> # many-to-many join on an association table </span><del>- j = join(users, userkeywords, - users.c.user_id==userkeywords.c.user_id).join(keywords, </del><ins>+ j = join(users_table, userkeywords, + users_table.c.user_id==userkeywords.c.user_id).join(keywords, </ins><span class="cx"> userkeywords.c.keyword_id==keywords.c.keyword_id) </span><span class="cx"> </span><span class="cx"> # a class </span><span class="lines">@@ -554,7 +558,7 @@ </span><span class="cx"> </span><span class="cx"> {python} </span><span class="cx"> usermapper = mapper(User, users) </span><del>- mapper(Address, addresses, properties={ </del><ins>+ mapper(Address, addresses_table, properties={ </ins><span class="cx"> 'user':relation(User) </span><span class="cx"> }) </span><span class="cx"> </span><span class="lines">@@ -645,10 +649,10 @@ </span><span class="cx"> class User(object): </span><span class="cx"> pass </span><span class="cx"> </span><del>- User.mapper = mapper(User, users) </del><ins>+ User.mapper = mapper(User, users_table) </ins><span class="cx"> </span><span class="cx"> # select users </span><del>- c = users.select().execute() </del><ins>+ c = users_table.select().execute() </ins><span class="cx"> </span><span class="cx"> # get objects </span><span class="cx"> userlist = User.mapper.instances(c) </span><span class="lines">@@ -658,10 +662,10 @@ </span><span class="cx"> class Address(object): </span><span class="cx"> pass </span><span class="cx"> </span><del>- Address.mapper = mapper(Address, addresses) </del><ins>+ Address.mapper = mapper(Address, addresses_table) </ins><span class="cx"> </span><span class="cx"> # select users and addresses in one query </span><del>- s = select([users, addresses], users.c.user_id==addresses.c.user_id) </del><ins>+ s = select([users_table, addresses_table], users_table.c.user_id==addresses_table.c.user_id) </ins><span class="cx"> </span><span class="cx"> # execute it, and process the results with the User mapper, chained to the Address mapper </span><span class="cx"> r = User.mapper.instances(s.execute(), Address.mapper) </span><span class="lines">@@ -759,10 +763,10 @@ </span><span class="cx"> To use MapperExtension, make your own subclass of it and just send it off to a mapper: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- m = mapper(User, users, extension=MyExtension()) </del><ins>+ m = mapper(User, users_table, extension=MyExtension()) </ins><span class="cx"> </span><span class="cx"> Multiple extensions will be chained together and processed in order; they are specified as a list: </span><span class="cx"> </span><span class="cx"> {python} </span><del>- m = mapper(User, users, extension=[ext1, ext2, ext3]) </del><ins>+ m = mapper(User, users_table, extension=[ext1, ext2, ext3]) </ins><span class="cx"> </span></span></pre></div> <a id="sqlalchemybranchesschemalibsqlalchemyansisqlpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/lib/sqlalchemy/ansisql.py (1392 => 1393)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/lib/sqlalchemy/ansisql.py 2006-05-05 00:42:42 UTC (rev 1392) +++ sqlalchemy/branches/schema/lib/sqlalchemy/ansisql.py 2006-05-05 00:44:26 UTC (rev 1393) </span><span class="lines">@@ -372,15 +372,15 @@ </span><span class="cx"> if group_by: </span><span class="cx"> text += " GROUP BY " + group_by </span><span class="cx"> </span><ins>+ if select.having is not None: + t = self.get_str(select.having) + if t: + text += " \nHAVING " + t + </ins><span class="cx"> order_by = self.get_str(select.order_by_clause) </span><span class="cx"> if order_by: </span><span class="cx"> text += " ORDER BY " + order_by </span><span class="cx"> </span><del>- if select.having is not None: - t = self.get_str(select.having) - if t: - text += " \nHAVING " + t - </del><span class="cx"> text += self.visit_select_postclauses(select) </span><span class="cx"> </span><span class="cx"> if select.for_update: </span></span></pre></div> <a id="sqlalchemybranchesschemalibsqlalchemyormmapperpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/lib/sqlalchemy/orm/mapper.py (1392 => 1393)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/lib/sqlalchemy/orm/mapper.py 2006-05-05 00:42:42 UTC (rev 1392) +++ sqlalchemy/branches/schema/lib/sqlalchemy/orm/mapper.py 2006-05-05 00:44:26 UTC (rev 1393) </span><span class="lines">@@ -649,9 +649,9 @@ </span><span class="cx"> if len(update): </span><span class="cx"> clause = sql.and_() </span><span class="cx"> for col in self.pks_by_table[table]: </span><del>- clause.clauses.append(col == sql.bindparam(col._label)) </del><ins>+ clause.clauses.append(col == sql.bindparam(col._label, type=col.type)) </ins><span class="cx"> if self.version_id_col is not None: </span><del>- clause.clauses.append(self.version_id_col == sql.bindparam(self.version_id_col._label)) </del><ins>+ clause.clauses.append(self.version_id_col == sql.bindparam(self.version_id_col._label, type=col.type)) </ins><span class="cx"> statement = table.update(clause) </span><span class="cx"> rows = 0 </span><span class="cx"> supports_sane_rowcount = True </span><span class="lines">@@ -727,9 +727,9 @@ </span><span class="cx"> if len(delete): </span><span class="cx"> clause = sql.and_() </span><span class="cx"> for col in self.pks_by_table[table]: </span><del>- clause.clauses.append(col == sql.bindparam(col.key)) </del><ins>+ clause.clauses.append(col == sql.bindparam(col.key, type=col.type)) </ins><span class="cx"> if self.version_id_col is not None: </span><del>- clause.clauses.append(self.version_id_col == sql.bindparam(self.version_id_col.key)) </del><ins>+ clause.clauses.append(self.version_id_col == sql.bindparam(self.version_id_col.key, type=self.version_id_col.type)) </ins><span class="cx"> statement = table.delete(clause) </span><span class="cx"> print "DELETE IS", delete </span><span class="cx"> c = connection.execute(statement, delete) </span></span></pre> </div> </div> </body> </html> |
