From 89624d5e05480c832a079008bbb9992f411be0dd Mon Sep 17 00:00:00 2001 From: Christian Heller Date: Fri, 19 Apr 2024 06:14:25 +0200 Subject: [PATCH] Refactor updates of relations tables. --- plomtask/db.py | 18 ++++++++++++++++-- plomtask/misc.py | 8 +++----- plomtask/processes.py | 23 +++++++---------------- plomtask/todos.py | 34 ++++++++-------------------------- 4 files changed, 34 insertions(+), 49 deletions(-) diff --git a/plomtask/db.py b/plomtask/db.py index abd8f61..bf633e2 100644 --- a/plomtask/db.py +++ b/plomtask/db.py @@ -67,6 +67,20 @@ class DatabaseConnection: """Close DB connection.""" self.conn.close() + def rewrite_relations(self, table_name: str, key: str, target: int, + rows: list[list[Any]]) -> None: + """Rewrite relations in table_name to target, with rows values.""" + self.exec(f'DELETE FROM {table_name} WHERE {key} = ?', (target,)) + for row in rows: + values = tuple([target] + row) + q_marks = self.__class__.q_marks_from_values(values) + self.exec(f'INSERT INTO {table_name} VALUES {q_marks}', values) + + @staticmethod + def q_marks_from_values(values: tuple[Any]) -> str: + """Return placeholder to insert values into SQL code.""" + return '(' + ','.join(['?'] * len(values)) + ')' + class BaseModel: """Template for most of the models we use/derive from the DB.""" @@ -114,11 +128,11 @@ class BaseModel: def save_core(self, db_conn: DatabaseConnection, update_with_lastrowid: bool = True) -> None: """Write bare-bones self (sans connected items), ensuring self.id_.""" - q_marks = ','.join(['?'] * (len(self.to_save) + 1)) values = tuple([self.id_] + [getattr(self, key) for key in self.to_save]) + q_marks = DatabaseConnection.q_marks_from_values(values) table_name = self.table_name - cursor = db_conn.exec(f'REPLACE INTO {table_name} VALUES ({q_marks})', + cursor = db_conn.exec(f'REPLACE INTO {table_name} VALUES {q_marks}', values) if update_with_lastrowid: self.id_ = cursor.lastrowid diff --git a/plomtask/misc.py b/plomtask/misc.py index dcaad17..efa8898 100644 --- a/plomtask/misc.py +++ b/plomtask/misc.py @@ -46,8 +46,6 @@ class VersionedAttribute: def save(self, db_conn: DatabaseConnection) -> None: """Save as self.history entries, but first wipe old ones.""" - db_conn.exec(f'DELETE FROM {self.table_name} WHERE parent = ?', - (self.parent.id_,)) - for timestamp, value in self.history.items(): - db_conn.exec(f'INSERT INTO {self.table_name} VALUES (?, ?, ?)', - (self.parent.id_, timestamp, value)) + db_conn.rewrite_relations(self.table_name, 'parent', self.parent.id_, + [[item[0], item[1]] + for item in self.history.items()]) diff --git a/plomtask/processes.py b/plomtask/processes.py index 9c5c824..a3682c5 100644 --- a/plomtask/processes.py +++ b/plomtask/processes.py @@ -183,25 +183,16 @@ class Process(BaseModel): def save(self, db_conn: DatabaseConnection) -> None: """Add (or re-write) self and connected items to DB.""" self.save_core(db_conn) + assert isinstance(self.id_, int) self.title.save(db_conn) self.description.save(db_conn) self.effort.save(db_conn) - db_conn.exec('DELETE FROM process_conditions WHERE process = ?', - (self.id_,)) - for condition in self.conditions: - db_conn.exec('INSERT INTO process_conditions VALUES (?,?)', - (self.id_, condition.id_)) - db_conn.exec('DELETE FROM process_fulfills WHERE process = ?', - (self.id_,)) - for condition in self.fulfills: - db_conn.exec('INSERT INTO process_fulfills VALUES (?,?)', - (self.id_, condition.id_)) - db_conn.exec('DELETE FROM process_undoes WHERE process = ?', - (self.id_,)) - for condition in self.undoes: - db_conn.exec('INSERT INTO process_undoes VALUES (?,?)', - (self.id_, condition.id_)) - assert isinstance(self.id_, int) + db_conn.rewrite_relations('process_conditions', 'process', self.id_, + [[c.id_] for c in self.conditions]) + db_conn.rewrite_relations('process_fulfills', 'process', self.id_, + [[c.id_] for c in self.fulfills]) + db_conn.rewrite_relations('process_undoes', 'process', self.id_, + [[c.id_] for c in self.undoes]) db_conn.exec('DELETE FROM process_steps WHERE owner = ?', (self.id_,)) for step in self.explicit_steps: diff --git a/plomtask/todos.py b/plomtask/todos.py index 840c298..348dbdd 100644 --- a/plomtask/todos.py +++ b/plomtask/todos.py @@ -181,29 +181,11 @@ class Todo(BaseModel): self.save_core(db_conn) assert isinstance(self.id_, int) db_conn.cached_todos[self.id_] = self - db_conn.exec('DELETE FROM todo_children WHERE parent = ?', - (self.id_,)) - for child in self.children: - db_conn.exec('INSERT INTO todo_children VALUES (?, ?)', - (self.id_, child.id_)) - db_conn.exec('DELETE FROM todo_fulfills WHERE todo = ?', (self.id_,)) - for condition in self.fulfills: - if condition.id_ is None: - raise NotFoundException('Fulfilled Condition of Todo ' - 'without ID (not saved?)') - db_conn.exec('INSERT INTO todo_fulfills VALUES (?, ?)', - (self.id_, condition.id_)) - db_conn.exec('DELETE FROM todo_undoes WHERE todo = ?', (self.id_,)) - for condition in self.undoes: - if condition.id_ is None: - raise NotFoundException('Undone Condition of Todo ' - 'without ID (not saved?)') - db_conn.exec('INSERT INTO todo_undoes VALUES (?, ?)', - (self.id_, condition.id_)) - db_conn.exec('DELETE FROM todo_conditions WHERE todo = ?', (self.id_,)) - for condition in self.conditions: - if condition.id_ is None: - raise NotFoundException('Condition of Todo ' - 'without ID (not saved?)') - db_conn.exec('INSERT INTO todo_conditions VALUES (?, ?)', - (self.id_, condition.id_)) + db_conn.rewrite_relations('todo_children', 'parent', self.id_, + [[c.id_] for c in self.children]) + db_conn.rewrite_relations('todo_conditions', 'todo', self.id_, + [[c.id_] for c in self.conditions]) + db_conn.rewrite_relations('todo_fulfills', 'todo', self.id_, + [[c.id_] for c in self.fulfills]) + db_conn.rewrite_relations('todo_undoes', 'todo', self.id_, + [[c.id_] for c in self.undoes]) -- 2.30.2