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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---