def test_many_to_many_one(self):
class Node(Base):
pass
mapper(
Node,
nodes,
properties={
'children':
relationship(
Node,
secondary=node_to_nodes,
primaryjoin=nodes.c.id == node_to_nodes.c.left_node_id,
secondaryjoin=nodes.c.id == node_to_nodes.c.right_node_id,
backref='parents'),
'favorite':
relationship(Node, remote_side=nodes.c.id)
})
sess = create_session()
n1 = Node(data='n1')
n2 = Node(data='n2')
n3 = Node(data='n3')
n4 = Node(data='n4')
n5 = Node(data='n5')
n4.favorite = n3
n1.favorite = n5
n5.favorite = n2
n1.children = [n2, n3, n4]
n2.children = [n3, n5]
n3.children = [n5, n4]
sess.add_all([n1, n2, n3, n4, n5])
# can't really assert the SQL on this easily
# since there's too many ways to insert the rows.
# so check the end result
sess.flush()
eq_(
sess.query(node_to_nodes.c.left_node_id,
node_to_nodes.c.right_node_id).\
order_by(node_to_nodes.c.left_node_id,
node_to_nodes.c.right_node_id).\
all(),
sorted([
(n1.id, n2.id), (n1.id, n3.id), (n1.id, n4.id),
(n2.id, n3.id), (n2.id, n5.id),
(n3.id, n5.id), (n3.id, n4.id)
])
)
sess.delete(n1)
self.assert_sql_execution(
testing.db,
sess.flush,
# this is n1.parents firing off, as it should, since
# passive_deletes is False for n1.parents
CompiledSQL(
"SELECT nodes.id AS nodes_id, nodes.data AS nodes_data, "
"nodes.favorite_node_id AS nodes_favorite_node_id FROM "
"nodes, node_to_nodes WHERE :param_1 = "
"node_to_nodes.right_node_id AND nodes.id = "
"node_to_nodes.left_node_id",
lambda ctx: {u'param_1': n1.id},
),
CompiledSQL(
"DELETE FROM node_to_nodes WHERE "
"node_to_nodes.left_node_id = :left_node_id AND "
"node_to_nodes.right_node_id = :right_node_id",
lambda ctx: [{
'right_node_id': n2.id,
'left_node_id': n1.id
}, {
'right_node_id': n3.id,
'left_node_id': n1.id
}, {
'right_node_id': n4.id,
'left_node_id': n1.id
}]),
CompiledSQL("DELETE FROM nodes WHERE nodes.id = :id",
lambda ctx: {'id': n1.id}),
)
for n in [n2, n3, n4, n5]:
sess.delete(n)
# load these collections
# outside of the flush() below
n4.children
n5.children
self.assert_sql_execution(
testing.db,
sess.flush,
CompiledSQL(
"DELETE FROM node_to_nodes WHERE node_to_nodes.left_node_id "
"= :left_node_id AND node_to_nodes.right_node_id = "
":right_node_id", lambda ctx: [{
'right_node_id': n5.id,
'left_node_id': n3.id
}, {
'right_node_id': n4.id,
'left_node_id': n3.id
}, {
'right_node_id': n3.id,
'left_node_id': n2.id
}, {
'right_node_id': n5.id,
'left_node_id': n2.id
}]),
CompiledSQL("DELETE FROM nodes WHERE nodes.id = :id",
lambda ctx: [{
'id': n4.id
}, {
'id': n5.id
}]),
CompiledSQL("DELETE FROM nodes WHERE nodes.id = :id",
lambda ctx: [{
'id': n2.id
}, {
'id': n3.id
}]),
)
def test_many_to_many_one(self):
class Node(Base):
pass
mapper(Node, nodes, properties={
'children':relationship(Node, secondary=node_to_nodes,
primaryjoin=nodes.c.id==node_to_nodes.c.left_node_id,
secondaryjoin=nodes.c.id==node_to_nodes.c.right_node_id,
backref='parents'
),
'favorite':relationship(Node, remote_side=nodes.c.id)
})
sess = create_session()
n1 = Node(data='n1')
n2 = Node(data='n2')
n3 = Node(data='n3')
n4 = Node(data='n4')
n5 = Node(data='n5')
n4.favorite = n3
n1.favorite = n5
n5.favorite = n2
n1.children = [n2, n3, n4]
n2.children = [n3, n5]
n3.children = [n5, n4]
sess.add_all([n1, n2, n3, n4, n5])
# can't really assert the SQL on this easily
# since there's too many ways to insert the rows.
# so check the end result
sess.flush()
eq_(
sess.query(node_to_nodes.c.left_node_id,
node_to_nodes.c.right_node_id).\
order_by(node_to_nodes.c.left_node_id,
node_to_nodes.c.right_node_id).\
all(),
sorted([
(n1.id, n2.id), (n1.id, n3.id), (n1.id, n4.id),
(n2.id, n3.id), (n2.id, n5.id),
(n3.id, n5.id), (n3.id, n4.id)
])
)
sess.delete(n1)
self.assert_sql_execution(
testing.db,
sess.flush,
# this is n1.parents firing off, as it should, since
# passive_deletes is False for n1.parents
CompiledSQL(
"SELECT nodes.id AS nodes_id, nodes.data AS nodes_data, "
"nodes.favorite_node_id AS nodes_favorite_node_id FROM "
"nodes, node_to_nodes WHERE :param_1 = "
"node_to_nodes.right_node_id AND nodes.id = "
"node_to_nodes.left_node_id" ,
lambda ctx:{u'param_1': n1.id},
),
CompiledSQL(
"DELETE FROM node_to_nodes WHERE "
"node_to_nodes.left_node_id = :left_node_id AND "
"node_to_nodes.right_node_id = :right_node_id",
lambda ctx:[
{'right_node_id': n2.id, 'left_node_id': n1.id},
{'right_node_id': n3.id, 'left_node_id': n1.id},
{'right_node_id': n4.id, 'left_node_id': n1.id}
]
),
CompiledSQL(
"DELETE FROM nodes WHERE nodes.id = :id",
lambda ctx:{'id': n1.id}
),
)
for n in [n2, n3, n4, n5]:
sess.delete(n)
# load these collections
# outside of the flush() below
n4.children
n5.children
self.assert_sql_execution(
testing.db,
sess.flush,
CompiledSQL(
"DELETE FROM node_to_nodes WHERE node_to_nodes.left_node_id "
"= :left_node_id AND node_to_nodes.right_node_id = "
":right_node_id",
lambda ctx:[
{'right_node_id': n5.id, 'left_node_id': n3.id},
{'right_node_id': n4.id, 'left_node_id': n3.id},
{'right_node_id': n3.id, 'left_node_id': n2.id},
{'right_node_id': n5.id, 'left_node_id': n2.id}
]
),
CompiledSQL(
"DELETE FROM nodes WHERE nodes.id = :id",
lambda ctx:[{'id': n4.id}, {'id': n5.id}]
),
CompiledSQL(
"DELETE FROM nodes WHERE nodes.id = :id",
lambda ctx:[{'id': n2.id}, {'id': n3.id}]
),
)
