sqlite_fk.py 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342
  1. """Selected ON DELETE rules of the foreign keys, applied by hand on SQLite.
  2. The SQLite database runs with ``PRAGMA foreign_keys`` off: turning it on breaks
  3. the migrations' table rebuilds (measured: every test errors with "no such table:
  4. main.print_archives"). So a delete there applies none of the models' ON DELETE
  5. CASCADE / SET NULL rules. Rows pointing at the deleted row keep its id, and
  6. SQLite hands the highest freed id to the next row created, which then inherits
  7. them: a queue item prints another archive, a project shows another project's
  8. files.
  9. Only the references in APPLIED are handled: those where a dangling id does harm
  10. and the model's rule is what the app wants. Not every model rule is (deleting a
  11. printer would make a printer-scoped notification provider global, and delete
  12. finished queue history), so a reference is added here deliberately, never by
  13. default.
  14. Hooks apply them at every ORM delete, so no delete path needs to remember:
  15. - ``after_delete`` on every mapped class collects the ids of ``db.delete(obj)``,
  16. and ``after_flush`` applies the rules once per flush, one statement per rule;
  17. - ``do_orm_execute`` applies them before ``db.execute(delete(Model)...)``.
  18. Rule statements go straight to the connection and don't trigger the hooks again.
  19. ``sweep_orphans`` repairs what earlier deletes left behind; init_db runs it at
  20. every start. Nothing here does anything on PostgreSQL, which enforces the rules
  21. itself.
  22. """
  23. import logging
  24. from functools import cache
  25. from typing import Any
  26. from sqlalchemy import Column, Connection, Delete, ForeignKey, Table, delete, event, exists, select, text, update
  27. from sqlalchemy.orm import Mapper, ORMExecuteState, Session
  28. from backend.app.core.database import Base
  29. logger = logging.getLogger(__name__)
  30. # (child table, child column) whose ON DELETE rule is applied on SQLite.
  31. APPLIED: frozenset[tuple[str, str]] = frozenset(
  32. {
  33. # print_archives
  34. ("active_print_spoolman", "archive_id"),
  35. ("archive_aliases", "archive_id"),
  36. ("budget_reservations", "print_archive_id"),
  37. ("library_folders", "archive_id"),
  38. ("pending_uploads", "archived_id"),
  39. ("pipeline_runs", "source_archive_id"),
  40. ("print_batches", "archive_id"),
  41. ("print_log_entries", "archive_id"),
  42. ("print_queue", "archive_id"),
  43. ("project_bom_items", "archive_id"),
  44. ("spool_usage_history", "archive_id"),
  45. # spool
  46. ("spool_usage_history", "spool_id"),
  47. ("telegram_pending_verdicts", "archive_id"),
  48. ("wallet_transactions", "print_archive_id"),
  49. # library_files. print_queue.library_file_id is CASCADE in the model, but
  50. # library deletes run release_queue_references first, which keeps the rows
  51. # (finished plates count towards a batch); the rule then finds nothing.
  52. ("library_file_tags", "file_id"),
  53. ("pipeline_runs", "sliced_library_file_id"),
  54. ("pipeline_runs", "source_library_file_id"),
  55. ("print_archives", "library_file_id"),
  56. ("print_batches", "library_file_id"),
  57. ("print_queue", "library_file_id"),
  58. ("print_queue_variants", "library_file_id"),
  59. # print_queue
  60. ("pipeline_jobs", "queue_entry_id"),
  61. ("print_log_entries", "queue_item_id"),
  62. ("print_queue_variants", "queue_item_id"),
  63. ("wallet_transactions", "print_queue_id"),
  64. # print_batches
  65. ("print_batch_plates", "batch_id"),
  66. ("print_queue", "batch_id"),
  67. # projects
  68. ("library_files", "project_id"),
  69. ("library_folders", "project_id"),
  70. ("pending_uploads", "project_id"),
  71. ("print_archives", "project_id"),
  72. ("print_batches", "project_id"),
  73. ("print_queue", "project_id"),
  74. ("project_bom_items", "project_id"),
  75. # printers: what the next printer given the id would otherwise take
  76. # over. Only these: providers, plugs and virtual printers go with the
  77. # printer in its delete route, and the rest is decided separately. The
  78. # print being tracked would be restored for a new printer that is already
  79. # printing, charging its filament to the deleted printer's spools.
  80. ("active_print_sessions", "printer_id"),
  81. ("active_print_spoolman", "printer_id"),
  82. ("pipeline_jobs", "assigned_printer_id"),
  83. ("slicer_pipelines", "target_printer_id"),
  84. # A deleted printer's spools would stay "assigned" to it (SA-1).
  85. ("spool_assignment", "printer_id"),
  86. # A printer given a deleted one's id would never be told about the
  87. # faults that printer was notified about (#3486).
  88. ("hms_notified_faults", "printer_id"),
  89. # A profile default's K profile would select a calibration slot on
  90. # the next printer given the id (#1326).
  91. ("filament_profile_default_k_profile", "printer_id"),
  92. # The maintenance log outlives its printer; the next printer given the
  93. # id would take over its history.
  94. ("maintenance_log_entries", "printer_id"),
  95. # filament_profile_default: the next default given the id would take
  96. # over its presets and K profiles (#1326).
  97. ("filament_profile_default_k_profile", "profile_default_id"),
  98. ("filament_profile_default_preset", "profile_default_id"),
  99. # maintenance_types: the log keeps a deleted custom task's entries; the
  100. # next task given the id would take them over.
  101. ("maintenance_log_entries", "maintenance_type_id"),
  102. # maintenance_history goes with its printer task; the next reset given
  103. # the id, maybe on another printer, would have its history deleted
  104. # with this entry.
  105. ("maintenance_log_entries", "history_id"),
  106. # external_links: the next link given the id would show the count an
  107. # app reported for the deleted one.
  108. ("external_link_badges", "link_id"),
  109. # smart_plugs: a printer delete now deletes its plugs; their energy
  110. # history would pass to the next plug given the id.
  111. ("smart_plug_energy_snapshots", "plug_id"),
  112. # users: everything a user owns goes with them or loses its owner, or a
  113. # new user given the freed id inherits it (wallet, cost centres, private
  114. # folders). Without an owner, folders and private cost centres are left
  115. # to admins, not opened to everyone.
  116. ("announcement_reads", "user_id"),
  117. ("api_keys", "user_id"),
  118. ("connected_app_grants", "user_id"),
  119. ("connected_apps", "created_by_id"),
  120. ("cost_center_members", "user_id"),
  121. ("cost_centers", "owner_user_id"),
  122. ("file_variant_groups", "created_by_id"),
  123. ("library_files", "created_by_id"),
  124. ("library_folders", "created_by_id"),
  125. ("long_lived_tokens", "user_id"),
  126. ("maintenance_log_entries", "performed_by_id"),
  127. ("pipeline_runs", "created_by"),
  128. ("print_archives", "created_by_id"),
  129. ("print_batches", "created_by_id"),
  130. ("print_log_entries", "created_by_id"),
  131. ("print_queue", "created_by_id"),
  132. ("scheduled_dryings", "created_by_id"),
  133. ("slicer_pipelines", "created_by"),
  134. # A new user given the id would have their cloud sign-in used for the
  135. # slot's preset (#3519).
  136. ("spool_assignment", "assigned_by_user_id"),
  137. ("sponsor_toast_state", "user_id"),
  138. ("user_email_preferences", "user_id"),
  139. ("user_groups", "user_id"),
  140. ("user_oidc_links", "user_id"),
  141. ("user_otp_codes", "user_id"),
  142. ("user_totp", "user_id"),
  143. ("user_wallets", "user_id"),
  144. ("wallet_transactions", "created_by_user_id"),
  145. ("wallet_transactions", "user_id"),
  146. }
  147. )
  148. _PENDING = "bambuddy_sqlite_fk_pending"
  149. _TABLES = "bambuddy_sqlite_fk_tables"
  150. # Keeps any one statement's IN list small.
  151. _CHUNK = 500
  152. _MAX_DEPTH = 32
  153. def _applies(connection: Connection) -> bool:
  154. """Only SQLite: PostgreSQL enforces the rules itself."""
  155. return connection.dialect.name == "sqlite"
  156. def _existing_tables(connection: Connection) -> set[str]:
  157. """Tables the database has (cached on the connection); a model without one has no rows to fix."""
  158. tables = connection.info.get(_TABLES)
  159. if tables is None:
  160. result = connection.execute(text("SELECT name FROM sqlite_master WHERE type = 'table'"))
  161. tables = connection.info[_TABLES] = {row[0] for row in result}
  162. return tables
  163. def _single_primary_key(table: Table) -> Column | None:
  164. columns = list(table.primary_key.columns)
  165. return columns[0] if len(columns) == 1 else None
  166. @cache
  167. def _applied_foreign_keys() -> tuple[ForeignKey, ...]:
  168. """The model foreign keys of APPLIED references to a single-column primary key."""
  169. from backend.app.core.database import load_all_models
  170. found = []
  171. for table in load_all_models().sorted_tables:
  172. for fk in table.foreign_keys:
  173. if (
  174. (table.name, fk.parent.name) in APPLIED
  175. and fk.ondelete in ("CASCADE", "SET NULL")
  176. and isinstance(fk.column.table, Table)
  177. and fk.column is _single_primary_key(fk.column.table)
  178. ):
  179. found.append(fk)
  180. return tuple(found)
  181. @cache
  182. def _rules(parent_table: str) -> tuple[tuple[Table, Column, str], ...]:
  183. """(child table, child column, ON DELETE action) of the APPLIED references to *parent_table*."""
  184. return tuple(
  185. (fk.parent.table, fk.parent, str(fk.ondelete))
  186. for fk in _applied_foreign_keys()
  187. if fk.column.table.name == parent_table and isinstance(fk.parent.table, Table)
  188. )
  189. def _apply(connection: Connection, parent_table: str, ids: Any, depth: int = 0) -> None:
  190. """Apply the rules for rows of *parent_table* whose primary key is in *ids* (a list or a select)."""
  191. rules = _rules(parent_table)
  192. if not rules or depth > _MAX_DEPTH:
  193. return
  194. if isinstance(ids, list):
  195. for start in range(0, len(ids), _CHUNK):
  196. _apply_chunk(connection, rules, ids[start : start + _CHUNK], depth)
  197. else:
  198. _apply_chunk(connection, rules, ids, depth)
  199. def _apply_chunk(connection: Connection, rules: tuple, ids: Any, depth: int) -> None:
  200. tables = _existing_tables(connection)
  201. for child, column, action in rules:
  202. if child.name not in tables:
  203. continue
  204. if action == "SET NULL":
  205. connection.execute(update(child).where(column.in_(ids)).values({column.name: None}))
  206. continue
  207. child_key = _single_primary_key(child)
  208. if child_key is not None:
  209. # By depth, not by table: a table that refers to itself recurses too.
  210. _apply(connection, child.name, select(child_key).where(column.in_(ids)), depth + 1)
  211. connection.execute(delete(child).where(column.in_(ids)))
  212. @event.listens_for(Base, "after_delete", propagate=True)
  213. def _collect_orm_delete(mapper: Mapper, connection: Connection, target: Any) -> None:
  214. table = mapper.local_table
  215. session = Session.object_session(target)
  216. if session is None or not isinstance(table, Table) or not _rules(table.name) or not _applies(connection):
  217. return
  218. pending = session.info.setdefault(_PENDING, {})
  219. pending.setdefault(table.name, []).append(mapper.primary_key_from_instance(target)[0])
  220. @event.listens_for(Session, "after_flush")
  221. def _apply_orm_deletes(session: Session, flush_context: Any) -> None:
  222. pending = session.info.pop(_PENDING, None)
  223. if not pending:
  224. return
  225. connection = session.connection()
  226. # The parent rows are already gone; with foreign keys off that doesn't stop
  227. # the rules from finding their dependents.
  228. for table, ids in pending.items():
  229. _apply(connection, table, ids)
  230. @event.listens_for(Session, "after_soft_rollback")
  231. def _forget_pending(session: Session, previous_transaction: Any) -> None:
  232. # A flush that failed after collecting must not leave ids for the next one.
  233. session.info.pop(_PENDING, None)
  234. @event.listens_for(Session, "do_orm_execute")
  235. def _before_bulk_delete(state: ORMExecuteState) -> None:
  236. statement = state.statement
  237. if not state.is_delete or not isinstance(statement, Delete):
  238. return
  239. table = statement.table
  240. if not isinstance(table, Table) or not _rules(table.name):
  241. return
  242. key = _single_primary_key(table)
  243. if key is None:
  244. return
  245. connection = state.session.connection()
  246. if not _applies(connection):
  247. return
  248. # The session would flush pending changes only after this hook; flush now so
  249. # the rules see them (a queue item just moved off the archive must not go).
  250. if state.session.autoflush:
  251. state.session.flush()
  252. where = statement.whereclause
  253. _apply(connection, table.name, select(key).where(where) if where is not None else select(key))
  254. def _still_not_null(connection: Connection, column: Column) -> bool:
  255. """Whether the column is NOT NULL in the database, whatever the model says."""
  256. rows = connection.execute(text(f'PRAGMA table_info("{column.table.name}")'))
  257. return any(row[1] == column.name and row[3] for row in rows)
  258. async def sweep_orphans(conn: Any) -> None:
  259. """Repair APPLIED references to rows that no longer exist (SQLite only).
  260. A SET NULL reference to a missing row is cleared and a CASCADE row is
  261. deleted, which is what the rule would have done at the delete. Repeated
  262. until a pass changes nothing, since a deleted row can leave its own
  263. dependents behind.
  264. """
  265. def run(sync: Any) -> None:
  266. connection = sync.connection() if isinstance(sync, Session) else sync
  267. if not _applies(connection):
  268. return
  269. tables = _existing_tables(connection)
  270. rules = [
  271. fk for fk in _applied_foreign_keys() if fk.parent.table.name in tables and fk.column.table.name in tables
  272. ]
  273. refused = {fk for fk in rules if fk.ondelete == "SET NULL" and _still_not_null(connection, fk.parent)}
  274. for fk in refused:
  275. # A migration that would have dropped it failed (and said so); clearing
  276. # the column would fail too, and take the start of the app with it.
  277. logger.warning("Not clearing %s.%s: the column is still NOT NULL", fk.parent.table.name, fk.parent.name)
  278. rules = [fk for fk in rules if fk not in refused]
  279. for _ in range(10):
  280. changed = 0
  281. for fk in rules:
  282. child, column, referenced = fk.parent.table, fk.parent, fk.column
  283. missing = column.is_not(None) & ~exists(
  284. select(1).select_from(referenced.table).where(referenced == column)
  285. )
  286. if fk.ondelete == "SET NULL":
  287. result = connection.execute(update(child).where(missing).values({column.name: None}))
  288. else:
  289. result = connection.execute(delete(child).where(missing))
  290. if result.rowcount:
  291. changed += result.rowcount
  292. logger.info(
  293. "%s %d %s row(s) whose %s no longer exists",
  294. "Cleared" if fk.ondelete == "SET NULL" else "Deleted",
  295. result.rowcount,
  296. child.name,
  297. referenced.table.name,
  298. )
  299. if not changed:
  300. return
  301. await conn.run_sync(run)