On Wed, Aug 27, 2008 at 07:48:20AM -0700, Alex Mathieu wrote:
>
> F*ck... I just realized that I was using MyISAM table engine...
> here's the deal then... I cannot use InnoDB for this projet so.... I
> think I will be writing some recursive code that can determine if an
> object has childs dependencies and will delete the proper objects....
>
> thanks again =)
>
I posted this on the ML a while ago - one of the SA devs suggested a use case
for it would be your current situation so here it is again. Let me know if you
use it and have any problems.
def delete_cascade(orm_obj):
"""Perform a cascading delete on any ORM object and its children."""
# Since we take an ORM _object_, we need to discover its table:
obj_table = class_mapper(type(orm_obj)).mapped_table
def get_child_tables(parent_table, children=[]):
"""Recursively find all child tables."""
new_children = []
# Use SQLAlchemy's table_iterator reversed to give us the tables in the
# correct order to ensure that we can delete without breaking any constraints
# (i.e. we will not delete a parent before its child:
for table in obj_table.metadata.table_iterator(reverse=True):
for fk in table.foreign_keys:
if fk.references(parent_table) and \
(table, fk, parent_table) not in children:
new_children.append((table, fk, parent_table))
break
# If no new children are found we have reached the top of the recursion so we
# fall back down the stack:
if not new_children:
return []
else:
for child in new_children:
# Here is the recursive call:
children.extend(get_child_tables(child[0]))
children.extend(new_children)
return children
_children = get_child_tables(obj_table)
children = []
# This loop filters out any tables who have more than one foreign key where one
# of the foreign keys references the root node so we have no duplicates. The
# result is a list of tables that reference either the root node or their
# parent:
for child in _children:
if child[0] not in [x[0] for x in children]:
children.append(child)
elif child[1].references(obj_table):
for i, _child in enumerate(children):
if _child[0] == child[0]:
children[i] = child
break
# This is a rare-case optimisation that sees if any of the tables reference the
# root node indirectly by having a foreign key whose counterpart is a direct
# reference to the root node:
for child in children:
table, fk, parent_table = child
if not fk.references(obj_table):
parent_fk = fk.column.foreign_key
while parent_fk is not None:
if parent_fk.references(obj_table):
obj_column = (
parent_fk.column.key
)
break
parent_fk = parent_fk.column.foreign_key
# Finally build a select for grandchildren or later to establish which records
# need to be removed by seeing which of their parent's records are ancestors of
# the root node:
if parent_fk is None:
sel = select([fk.parent])
parent_fk = fk.column.foreign_key
while parent_fk is not None:
sel.append_whereclause(
parent_fk.parent==parent_fk.column
)
tmp = parent_fk.column.foreign_key
if tmp is not None:
parent_fk = tmp
else:
break
obj_column = (
parent_fk.column.key
)
sel.append_whereclause(
parent_fk.column==getattr(orm_obj, obj_column)
)
in_column = fk.column.key
yield delete(
fk.parent.table,
fk.parent.in_(sel)
)
continue
# Otherwise simply yield a delete statement to delete the first-generation
# child of the root node:
else:
obj_column = fk.column.key
yield delete(
table,
fk.parent==getattr(orm_obj, obj_column)
)
# Build the delete statement for the root node itself by introspectively
# discovering the primary keys of the root node's table and deleting a
# single record from this table (i.e. the root node):
pk = [getattr(orm_obj, x) for x in obj_table.primary_key.keys()]
pk_cols = [x for x in obj_table.c if x.primary_key]
cond = pk[0] == pk_cols[0]
for x, y in zip(pk[1:], pk_cols[1:]):
if x and y:
cond &= x == y
yield delete(
obj_table,
cond
)
--
--------------------------
Bob Farrell
pH, an Experian Company
www.phgroup.com
Office Line: 020 7598 0310
Fax: 020 7598 0311
--------------------------
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
