Right now, if you delete a row that is used in a multi-relationship, a dangling reference is left on the row that references the now deleted row.
Deleting this reference is really hard. You can’t do it from the data tables; even if you try to modify the table, the row that is dead still shows in the list, and can’t be removed:
Modifying the cell shows this dialog:
Of course the deleted row doesn’t appear in that list (since it’s… deleted), but you ALSO can’t remove that reference. Even changing other references preserves the reference to the dead row.
The only way to fix this is to clear the cell altogether and rebuild the list of “good” rows (usually from memory…).
Can we either automatically remove dead links when you open up the multi-link selection box in the UI, or, provide a button to remove references to dead rows?
As it stands right now we do awful things like running a script like this in the server REPL:
from anvil.tables import app_tables
for row in app_tables.table_1.search():
good_rows = []
for link in row["multi_link"]:
try:
link["name"]
good_rows.append(link)
except Exception:
print("removing dangling reference")
row[""multi_link"] = good_rows
Clone link:
2 Likes
To solve this issue programatically I created an AbstractService that is always called to delete rows of any type, checks for every relation that row has in other datatables of that app and either removes the child row, detaches itself from the row or from the list in the row or aborts the operation depending on the type of CascadeAction I pass while calling the delete method.
However, this obviously doesn’t work while changing manually using the IDE, but I avoid using it exactly because of those types of reasons.
2 Likes
That sound super handy! Is it relatively efficient? Feel like sharing it here?
Sure.
from anvil.tables import app_tables
from anvil import tables
class ReferentialActionType():
NO_ACTION = 'NO_ACTION'
CASCADE = 'CASCADE'
DETACH = 'DETACH'
@tables.in_transaction
def delete(row, on_link_single_action=ReferentialActionType.CASCADE, on_link_multiple_action=ReferentialActionType.DETACH):
_on_delete(row, on_link_single_action, on_link_multiple_action)
def _on_delete(row, on_link_single_action=ReferentialActionType.CASCADE, on_link_multiple_action=ReferentialActionType.DETACH):
"""Removes the row, also performing actions on its children"""
if on_link_single_action != ReferentialActionType.NO_ACTION or on_link_multiple_action != ReferentialActionType.NO_ACTION:
relations = _get_relationships_by_table_id(
_get_row_table_id(row),
on_link_single_action != ReferentialActionType.NO_ACTION,
on_link_multiple_action != ReferentialActionType.NO_ACTION
)
children = []
for tab, rels in relations.items():
if len(rels) > 1:
query = q.any_of(**{
rel[0]:(row if rel[1] == 'link_single' else [row]) for rel in rels
})
children = app_tables[t].search(query)
else:
# Just one relation to the table
col_name = rels[0][0]
col_type = rels[0][1]
children = app_tables[tab].search(**{col_name: row if col_type == 'link_single' else [row]})
for child in children:
for rel in rels:
col_name, col_type = rel
curr_val = child[col_name]
if col_type == 'link_single':
if curr_val != row:
continue
# If it matches...
if on_link_single_action == ReferentialActionType.CASCADE:
_on_delete(child, on_link_single_action, on_link_multiple_action)
elif on_link_single_action == ReferentialActionType.DETACH:
child[col_name] = None
else:
if row not in curr_val:
continue
# If contains...
# remove from the list in both cases
curr_val.remove(row)
row[col_name] = curr_val
if on_link_multiple_action == ReferentialActionType.CASCADE and len(curr_val) == 0:
_on_delete(child, on_link_single_action, on_link_multiple_action)
row.delete()
def _get_row_table_id(row):
return eval(row.get_id())[0]
def _get_relationships_by_table_id(table_id:int, get_link_single=True, get_link_multiple=True):
relations = {}
column_types = []
if get_link_single:
column_types.append('link_single')
if get_link_multiple:
column_types.append('link_multiple')
if len(column_types) > 0:
for t in list(app_tables):
for c in app_tables[t].list_columns():
if c['type'] in column_types and c['table_id'] == table_id:
other_r = relations.get(t, [])
other_r.append((c['name'], c['type']))
relations[t] = other_r
return relations
def get_related_rows(row):
from anvil.tables import query as q
table_id = _get_row_table_id(row)
relations = _get_relationships_by_table_id(table_id)
results = {}
for t, rs in relations.items():
query = {}
if len(rs) > 1:
query = q.any_of(**{
r[0]:(row if r[1] == 'link_single' else [row]) for r in rs
})
results[t] = app_tables[t].search(query)
else:
name_of = rs[0][0]
type_of = rs[0][1]
query[name_of] = row if type_of == 'link_single' else [row]
results[t] = app_tables[t].search(**query)
return results
This is the delete part of the AbstractService. In my apps, every class has it’s own Service functions that, in the end, mostly call the ones in AbstractService.
When you call the delete function, you can pass NO_ACTION, CASCADE or DETACH to inform which type of action to do with orphans. You can also inform different actions for rows that use the one delete in single link or list of links. My default approach is to remove the orphans when it’s a single row link and to just remove from the list when it’s a multiple rows link. In this implementation the CASCADE option for list will only delete the child if there’s no other links in the row.
It does use recursive calls… But this shouldn’t be too much and you could potentially redo it as interative procedures.
5 Likes
