It seems that an error is raised when a deleted node is flushed when
post_update flag is set (See attached test case). Working with rev 1855.
After the second flush, the expected sql output should be:
[engine]: UPDATE node SET prev_sibling_id=? WHERE node.id = ?
[engine]: [2, 4]
[engine]: UPDATE node SET next_sibling_id=? WHERE node.id = ?
[engine]: [4, 2]
[engine]: DELETE FROM node WHERE node.id = ?
[engine]: [3]
[engine]: COMMIT
Instead we get:
[engine]: UPDATE node SET prev_sibling_id=? WHERE node.id = ?
[engine]: [2, 4]
[engine]: DELETE FROM node WHERE node.id = ?
[engine]: [3]
[engine]: UPDATE node SET prev_sibling_id=? WHERE node.id = ?
[engine]: [2, 4]
[engine]: UPDATE node SET next_sibling_id=? WHERE node.id = ?
[engine]: [4, 2]
[engine]: UPDATE node SET parent_id=?, next_sibling_id=? WHERE node.id = ?
[engine]: [None, None, 3]
[engine]: ROLLBACK
With the traceback:
File "tree.py", line 214, in ?
session.flush()
File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 234,
in flush
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
207, in flush
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
377, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
639, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
650, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
615, in _execute_childtasks
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
648, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
611, in _execute_dependencies
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
508, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/dependency.py", line
197, in process_dependencies
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
261, in register_object
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
588, in append_postupdate
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 841,
in save_obj
sqlalchemy.exceptions.FlushError: ConcurrencyError - updated rowcount 0
does not match number of objects updated 1
Two things are wrong here.
1) Attempting to update a deleted node, causing the error.
2) Node id:4 is being updated twice, while is not an error as such -
should not be happening (symptom of something else?)
Nick
Michael Bayer wrote:
> I should note that the relationship you are actually trying to set up
> includes a circular relationship between two individual rows. by
> default this is impossible to accomplish with only INSERT statements
> and not violate foreign key constraints. to break this circular
> relation you have to also add a special flag "post_update=True" to
> one side of the circle, which will add an extra UPDATE statement to
> the commit.
>
> n_mapper = mapper(Node, node_table, properties={
> 'parent': relation(
> Node,
> lazy=True,
> primaryjoin=node_table.c.parent_id==node_table.c.id,
> foreignkey=node_table.c.id,
> uselist=False
> ),
> 'children': relation(
> Node,
> primaryjoin=node_table.c.id==node_table.c.parent_id,
> lazy=True,
> cascade="all"
> ),
> 'previous_sibling': relation(
> Node,
> primaryjoin=node_table.c.prev_sibling_id==node_table.c.id,
> foreignkey=node_table.c.id,
> lazy=True,
> uselist=False
> ),
> 'next_sibling': relation(
> Node,
> primaryjoin=node_table.c.next_sibling_id==node_table.c.id,
> foreignkey=node_table.c.id,
> lazy=True,
> uselist=False,
> post_update=True,
> )
> })
>
[ snip ]
import sqlalchemy.mods.threadlocal
from sqlalchemy import *
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = BoundMetaData(engine)
session = objectstore.session
node_table = Table('node', metadata,
Column('id', Integer, primary_key=True),
Column('path', String(50), nullable=False),
Column('parent_id', Integer, ForeignKey('node.id'), nullable=True),
Column('prev_sibling_id', Integer, ForeignKey('node.id'), nullable=True),
Column('next_sibling_id', Integer, ForeignKey('node.id'), nullable=True)
)
class Node(object):
def __init__(self, path=''):
self.path = path
def has_child_nodes(self):
return len(self.children) == 0
def __repr__(self):
return '<Node(path=%s)>' % self.path
def get_first_child(self):
if '_first_child' not in self.__dict__:
self._first_child = get_first_child(self)
return self._first_child
def set_first_child(self, child):
self._first_child = child
first_child = property(get_first_child, set_first_child)
def get_last_child(self):
if '_last_child' not in self.__dict__:
self._last_child = get_last_child(self)
return self._last_child
def set_last_child(self, child):
self._last_child = child
last_child = property(get_last_child, set_last_child)
n_mapper = mapper(Node, node_table, properties={
'parent': relation(
Node,
lazy=True,
primaryjoin=node_table.c.parent_id==node_table.c.id,
foreignkey=node_table.c.id,
uselist=False
),
'children': relation(
Node,
primaryjoin=node_table.c.id==node_table.c.parent_id,
lazy=True,
cascade="all"
),
'previous_sibling': relation(
Node,
primaryjoin=node_table.c.prev_sibling_id==node_table.c.id,
foreignkey=node_table.c.id,
lazy=True,
uselist=False
),
'next_sibling': relation(
Node,
primaryjoin=node_table.c.next_sibling_id==node_table.c.id,
foreignkey=node_table.c.id,
lazy=True,
uselist=False,
post_update=True
)
})
def get_last_child(node):
node = session.query(Node).select(sqlalchemy.and_(
node_table.c.parent_id==node.c.id,
node_table.c.next_sibling_id==None
))
if len(node) == 0:
return None
return node[0]
def get_first_child(node):
node = session.query(Node).select(sqlalchemy.and_(
node_table.c.parent_id==node.c.id,
node_table.c.prev_sibling_id==None
))
if len(node) == 0:
return None
return node[0]
def insert_before(parent, child, ref=None):
if ref is not None:
assert ref in parent.children, "Not a child"
if ref is None:
# append to end of list
parent.children.append(child)
child.parent = parent
if parent.first_child is None:
parent.first_child = child
if parent.last_child is not None:
parent.last_child.next_sibling = child
child.previous_sibling = parent.last_child
parent.last_child = child
else:
parent.last_child = parent.children[-1:][0]
else:
ref_index = parent.children.index(ref)
previous_child = None
next_child = None
if ref_index > 0:
previous_child = parent.children[ref_index - 1]
try:
next_child = parent.children[ref_index]
except IndexError, e:
next_child = None
parent.children.insert(ref_index, child)
child.parent = parent
child.previous_sibling = previous_child
child.next_sibling = next_child
if previous_child is not None:
previous_child.next_sibling = child
if next_child is not None:
next_child.previous_sibling = child
if parent.first_child == ref:
parent.first_child = child
def append_child(parent, child):
insert_before(parent, child)
def remove_child(parent, child):
assert child in parent.children, 'Not a child'
idx = parent.children.index(child)
previous_child = None
next_child = None
if idx > 0:
previous_child = parent.children[idx - 1]
try:
next_child = parent.children[idx + 1]
except IndexError, e:
next_child = None
parent.children.remove(child)
if previous_child is not None:
previous_child.next_sibling = next_child
if next_child is not None:
next_child.previous_sibling = previous_child
if parent.first_child == child:
parent.first_child = child.next_sibling
if parent.last_child == child:
parent.last_child = child.previous_sibling
child.parent = None
child.previous_sibling = None
child.next_sibling = None
# child is now an orphan - delete it
session.delete(child)
return child
if __name__ == '__main__':
session = objectstore.session
node_table.create()
root = Node('root')
about = Node('about')
cats = Node('cats')
stories = Node('stories')
bruce = Node('bruce')
append_child(root, about)
append_child(root, cats)
append_child(root, stories)
append_child(root, bruce)
session.flush()
remove_child(root, cats)
session.flush()
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
