- fixed autoflush with count(), aggregates

- doc formatting bonanza
- delete() section to orm tutorial

6 files changed, 141 insertions, 18 deletions

diff --git a/doc/build/content/datamapping.txt b/doc/build/content/ormtutorial.txt
index 37afa0fd5..5295c01db 100644
--- a/doc/build/content/datamapping.txt
+++ b/doc/build/content/ormtutorial.txt
@@ -312,8 +312,8 @@ and `one()`, applies a limit of *two*, and if not exactly one row returned (no m
 All `Query` methods that don't return a result instead return a new `Query` object, with modifications applied.  Therefore you can call many query methods successively to build up the criterion you want:
 
     {python}
-    >>> query = session.query(User).filter(User.id<2).filter_by(name='ed')
-    {sql}>>> query.filter(User.fullname=='Ed Jones').all()
+    {sql}>>> session.query(User).filter(User.id<2).filter_by(name='ed').\
+    ...     filter(User.fullname=='Ed Jones').all()
     SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
     FROM users 
     WHERE users.id < ? AND users.name = ? AND users.fullname = ? ORDER BY users.oid
@@ -561,8 +561,8 @@ Which brings us to the next big topic.  What if we want to create joins that *do
 One way to join two tables together is just to compose a SQL expression.   Below we make one up using the `id` and `user_id` attributes on our mapped classes:
 
     {python}
-    >>> query = session.query(User).filter(User.id==Address.user_id)
-    {sql}>>> query.filter(Address.email_address=='jack@google.com').all()
+    {sql}>>> session.query(User).filter(User.id==Address.user_id).\
+    ...         filter(Address.email_address=='jack@google.com').all()
     SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
     FROM users, addresses 
     WHERE users.id = addresses.user_id AND addresses.email_address = ? ORDER BY users.oid
@@ -572,8 +572,8 @@ One way to join two tables together is just to compose a SQL expression.   Below
 Or we can make a real JOIN construct; below we use the `join()` function available on `Table` to create a `Join` object, then tell the `Query` to use it as our FROM clause:
 
     {python}
-    >>> query = session.query(User).select_from(users_table.join(addresses_table))
-    {sql}>>> query.filter(Address.email_address=='jack@google.com').all()
+    {sql}>>> session.query(User).select_from(users_table.join(addresses_table)).\
+    ...         filter(Address.email_address=='jack@google.com').all()
     SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
     FROM users JOIN addresses ON users.id = addresses.user_id 
     WHERE addresses.email_address = ? ORDER BY users.oid
@@ -618,9 +618,9 @@ In all cases, we can get the `User` and the matching `Address` objects back at t
 Another common scenario is the need to join on the same table more than once.  For example, if we want to find a `User` who has two distinct email addresses, both `jack@google.com` as well as `j25@yahoo.com`, we need to join to the `Addresses` table twice.  SQLAlchemy does provide `Alias` objects which can accomplish this; but far easier is just to tell `join()` to alias for you:
 
     {python}
-    >>> query = session.query(User).join('addresses', aliased=True)
-    >>> query = query.filter(Address.email_address=='jack@google.com')
-    >>> query = query.join('addresses', aliased=True).filter(Address.email_address=='j25@yahoo.com')
+    >>> query = session.query(User).join('addresses', aliased=True).\
+    ...     filter(Address.email_address=='jack@google.com').\
+    ...     join('addresses', aliased=True).filter(Address.email_address=='j25@yahoo.com')
     {sql}>>> query.all()
     SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
     FROM users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id JOIN addresses AS addresses_2 ON users.id = addresses_2.user_id 
@@ -628,4 +628,119 @@ Another common scenario is the need to join on the same table more than once.  F
     ['jack@google.com', 'j25@yahoo.com']
     {stop}[<User(u'jack',u'Jack Bean', u'gjffdd')>]
 
-The key thing which occured above is that our SQL criterion were **alisaed** as appropriate corresponding to the alias generated in the most recent `join()` call.  This is an example of where SQLAlchemy's relational construct engine makes a complicated task simple.
\ No newline at end of file
+The key thing which occured above is that our SQL criterion were **aliased** as appropriate corresponding to the alias generated in the most recent `join()` call.  
+
+## Deleting
+
+Lets try to delete `jack` and see how that goes.  We'll mark as deleted in the session, then we'll issue a `count` query to see that no rows remain:
+
+    {python}
+    >>> session.delete(jack)
+    {sql}>>> session.query(User).filter_by(name='jack').count()
+    UPDATE addresses SET user_id=? WHERE addresses.id = ?
+    [None, 1]
+    UPDATE addresses SET user_id=? WHERE addresses.id = ?
+    [None, 2]
+    DELETE FROM users WHERE users.id = ?
+    [5]
+    SELECT count(users.id) 
+    FROM users 
+    WHERE users.name = ?
+    ['jack']
+    {stop}0
+    
+So far, so good.  How about Jack's `Address` objects ?
+
+    {python}
+    {sql}>>> session.query(Address).filter(
+    ...     Address.email_address.in_('jack@google.com', 'j25@yahoo.com')
+    ...  ).count()
+    SELECT count(addresses.id) 
+    FROM addresses 
+    WHERE addresses.email_address IN (?, ?)
+    ['jack@google.com', 'j25@yahoo.com']
+    {stop}2
+    
+Uh oh, they're still there !  Anaylzing the flush SQL, we can see that the `user_id` column of each addresss was set to NULL, but the rows weren't deleted.  SQLAlchemy doesn't assume that deletes cascade, you have to tell it so.  
+
+So lets rollback our work, and start fresh with new mappers that express the relationship the way we want:
+
+    {python}
+    {sql}>>> session.rollback()  # roll back the transaction
+    ROLLBACK
+    
+    >>> session.clear() # clear the session
+    >>> clear_mappers() # clear mappers
+    
+We need to tell the `addresses` relation on `User` that we'd like session.delete() operations to cascade down to the child `Address` objects.  Further, we also want `Address` objects which get detached from their parent `User`, whether or not the parent is deleted, to be deleted.  For these behaviors we use two **cascade options** `delete` and `delete-orphan`, using the string-based `cascade` option to the `relation()` function:
+
+    {python}
+    >>> mapper(User, users_table, properties={    # doctest: +ELLIPSIS
+    ...     'addresses':relation(Address, backref='user', cascade="all, delete, delete-orphan")
+    ... })
+    <sqlalchemy.orm.mapper.Mapper object at 0x...>
+    
+    >>> mapper(Address, addresses_table) # doctest: +ELLIPSIS
+    <sqlalchemy.orm.mapper.Mapper object at 0x...>
+
+Now when we load Jack, removing an address from his `addresses` collection will result in that `Address` being deleted:
+
+    {python}
+    # load Jack by primary key
+    {sql}>>> jack = session.query(User).get(jack.id)    #doctest: +NORMALIZE_WHITESPACE
+    BEGIN
+    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
+    FROM users 
+    WHERE users.id = ? ORDER BY users.oid 
+    LIMIT 1 OFFSET 0
+    [5]
+    {stop}
+    
+    # remove one Address (lazy load fires off)
+    {sql}>>> del jack.addresses[1]  
+    SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
+    FROM addresses 
+    WHERE ? = addresses.user_id ORDER BY addresses.oid
+    [5]
+    {stop}
+    
+    # only one address remains
+    {sql}>>> session.query(Address).filter(
+    ...     Address.email_address.in_('jack@google.com', 'j25@yahoo.com')
+    ... ).count()
+    DELETE FROM addresses WHERE addresses.id = ?
+    [2]
+    SELECT count(addresses.id) 
+    FROM addresses 
+    WHERE addresses.email_address IN (?, ?)
+    ['jack@google.com', 'j25@yahoo.com']
+    {stop}1
+    
+Deleting Jack will delete both Jack and his remaining `Address`:
+
+    {python}
+    >>> session.delete(jack)
+    {sql}>>> session.commit()
+    DELETE FROM addresses WHERE addresses.id = ?
+    [1]
+    DELETE FROM users WHERE users.id = ?
+    [5]
+    COMMIT
+
+    {sql}>>> session.query(User).filter_by(name='jack').count()
+    BEGIN
+    SELECT count(users.id) 
+    FROM users 
+    WHERE users.name = ?
+    ['jack']
+    {stop}0
+    
+    {sql}>>> session.query(Address).filter(
+    ...    Address.email_address.in_('jack@google.com', 'j25@yahoo.com')
+    ... ).count()
+    SELECT count(addresses.id) 
+    FROM addresses 
+    WHERE addresses.email_address IN (?, ?)
+    ['jack@google.com', 'j25@yahoo.com']
+    {stop}0
+    
\ No newline at end of file
diff --git a/doc/build/genhtml.py b/doc/build/genhtml.py
index 312617fe9..f21891a21 100644
--- a/doc/build/genhtml.py
+++ b/doc/build/genhtml.py
@@ -15,7 +15,7 @@ files = [
     'index',
     'documentation',
     'intro',
-    'datamapping',
+    'ormtutorial',
     'adv_datamapping',
     'unitofwork',
     'sqlconstruction',
diff --git a/doc/build/read_markdown.py b/doc/build/read_markdown.py
index 842d4205f..9c45afd1d 100644
--- a/doc/build/read_markdown.py
+++ b/doc/build/read_markdown.py
@@ -143,15 +143,15 @@ def replace_pre_with_mako(tree):
         # syntax highlighter which uses the tokenize module
         text = re.sub(r'>>> ', r'">>>" ', text)
 
-        sqlre = re.compile(r'{sql}(.*?\n)((?:PRAGMA|BEGIN|SELECT|INSERT|DELETE|UPDATE|CREATE|DROP|PRAGMA|DESCRIBE).*?)\n\s*((?:{stop})|\n|$)', re.S)
+        sqlre = re.compile(r'{sql}(.*?)\n((?:PRAGMA|BEGIN|SELECT|INSERT|DELETE|ROLLBACK|COMMIT|UPDATE|CREATE|DROP|PRAGMA|DESCRIBE).*?)\n\s*((?:{stop})|\n|$)', re.S)
         if sqlre.search(text) is not None:
             use_sliders = False
         else:
             use_sliders = True
         
-        text = sqlre.sub(r"""${formatting.poplink()}\1\n<%call expr="formatting.codepopper()">\2</%call>\n\n""", text)
+        text = sqlre.sub(r"""${formatting.poplink()}\1<%call expr="formatting.codepopper()">\2</%call>""", text)
 
-        sqlre2 = re.compile(r'{opensql}(.*?\n)((?:PRAGMA|BEGIN|SELECT|INSERT|DELETE|UPDATE|CREATE|DROP).*?)\n\s*((?:{stop})|\n|$)', re.S)
+        sqlre2 = re.compile(r'{opensql}(.*?\n)((?:PRAGMA|BEGIN|SELECT|INSERT|DELETE|UPDATE|ROLLBACK|COMMIT|CREATE|DROP).*?)\n\s*((?:{stop})|\n|$)', re.S)
         text = sqlre2.sub(r"<%call expr='formatting.poppedcode()' >\1\n\2</%call>\n\n", text)
 
         tag = et.Element("MAKO:formatting.code", extension='extension', paged='paged', toc='toc')
@@ -177,6 +177,7 @@ def replace_pre_with_mako(tree):
             title = m.group(2)
             text = m.group(3)
             text = re.sub(r'{(python|code|diagram).*?}(\n\s*)?', '', text)
+            text = re.sub(r'\\', r'${r"\\\\" + "\\n"}', text)
             splice_code_tag(parents[precode], text, code=code, title=title)
         elif precode.text.lstrip().startswith('>>> '):
             splice_code_tag(parents[precode], precode.text)
diff --git a/doc/build/templates/formatting.html b/doc/build/templates/formatting.html
index 1c4013a9f..ec84ce9ae 100644
--- a/doc/build/templates/formatting.html
+++ b/doc/build/templates/formatting.html
@@ -73,11 +73,13 @@
 
                 if whitespace is not None or re.search(r"\w", line) is not None:
                     g += (line + "\n")
-
-
-            return g.rstrip()
+                else:
+                    g += "\n"
+            
+            return g #.rstrip()
         
         p = re.compile(r'<pre>(.*?)</pre>', re.S)
+        
         def hlight(match):
             return "<pre>" + highlight.highlight(fix_indent(match.group(1)), html_escape = html_escape, syntaxtype = syntaxtype) + "</pre>"
             
diff --git a/doc/build/testdocs.py b/doc/build/testdocs.py
index 04a825ee8..1b2936d8b 100644
--- a/doc/build/testdocs.py
+++ b/doc/build/testdocs.py
@@ -62,7 +62,7 @@ def replace_file(s, newfile):
         raise ValueError("Couldn't find suitable create_engine call to replace '%s' in it" % oldfile)

     return s

 

-filename = 'content/datamapping.txt'

+filename = 'content/ormtutorial.txt'

 s = open(filename).read()

 #s = replace_file(s, ':memory:')

 s = re.sub(r'{(?:stop|sql)}', '', s)

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 6d7e55af3..bf16cb699 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -419,6 +419,9 @@ class Query(object):
 
         ops = {'distinct':self._distinct, 'order_by':self._order_by or None, 'from_obj':self._from_obj}
 
+        if self._autoflush and not self._populate_existing:
+            self.session._autoflush()
+
         if self._order_by is not False:
             s1 = sql.select([col], self._criterion, **ops).alias('u')
             return self.session.execute(sql.select([func(s1.corresponding_column(col))]), mapper=self.mapper).scalar()
@@ -791,6 +794,8 @@ class Query(object):
         else:
             primary_key = self.primary_key_columns
             s = sql.select([sql.func.count(list(primary_key)[0])], whereclause, from_obj=from_obj, **context.select_args())
+        if self._autoflush and not self._populate_existing:
+            self.session._autoflush()
         return self.session.scalar(s, params=self._params, mapper=self.mapper)
         
     def compile(self):