From 0cd0b92d9e261dbe75fa45001aeca74592c053f8 Mon Sep 17 00:00:00 2001 From: Christian Heller <c.heller@plomlompom.de> Date: Fri, 15 Mar 2024 19:02:39 +0100 Subject: [PATCH] Add foreign key restraints, expand and fix tests, add deletion and forking. --- new_todo/html/template.html | 7 +++- new_todo/init.sql | 14 +++++--- new_todo/todo.py | 72 ++++++++++++++++++++++++++++++++----- 3 files changed, 80 insertions(+), 13 deletions(-) diff --git a/new_todo/html/template.html b/new_todo/html/template.html index 35f69de..e60d0c5 100644 --- a/new_todo/html/template.html +++ b/new_todo/html/template.html @@ -2,6 +2,9 @@ {% block content %} <h3>edit template</h3> +{% if tmpl.forked_from %} +forked from: <a href="/template?id={{tmpl.id_}}">{{tmpl.forked_from.title.newest|e}}</a> at {{tmpl.forked_at}}<br /> +{% endif %} <form action="/template{% if tmpl.id_ %}?id={{tmpl.id_}}{% endif %}" method="POST"> current title: <input name="title" value="{{tmpl.title.newest|e}}" /><br /> {% for datetime, title in tmpl.title.history.items() %} @@ -15,7 +18,9 @@ current description: <textarea name="description">{{tmpl.description.newest|e}}< {% for datetime, description in tmpl.description.history.items() %} {{datetime}}: {{description}}<br /> {% endfor %} -<input type="submit" value="OK" /> +<input type="submit" name="update" value="update" /> +<input type="submit" name="fork" value="fork" /> +<input type="submit" name="delete" value="delete" /> </form> {% endblock %} diff --git a/new_todo/init.sql b/new_todo/init.sql index 1be1422..1a91146 100644 --- a/new_todo/init.sql +++ b/new_todo/init.sql @@ -5,23 +5,29 @@ CREATE TABLE days ( CREATE TABLE templates ( id INTEGER PRIMARY KEY, forked_from INTEGER, - forked_at TEXT + forked_at TEXT, + FOREIGN KEY (forked_from) REFERENCES templates(id) ); CREATE TABLE versioned_default_efforts ( template INTEGER NOT NULL, datetime TEXT NOT NULL, default_effort REAL NOT NULL, - PRIMARY KEY (template, datetime) + PRIMARY KEY (template, datetime), + FOREIGN KEY (template) REFERENCES templates(id) ); CREATE TABLE versioned_descriptions ( template INTEGER NOT NULL, datetime TEXT NOT NULL, description TEXT NOT NULL, - PRIMARY KEY (template, datetime) + PRIMARY KEY (template, datetime), + FOREIGN KEY (template) REFERENCES templates(id) + ); CREATE TABLE versioned_titles ( template INTEGER NOT NULL, datetime TEXT NOT NULL, title TEXT NOT NULL, - PRIMARY KEY (template, datetime) + PRIMARY KEY (template, datetime), + FOREIGN KEY (template) REFERENCES templates(id) + ); diff --git a/new_todo/todo.py b/new_todo/todo.py index b7ee52b..7a5ed2d 100755 --- a/new_todo/todo.py +++ b/new_todo/todo.py @@ -80,6 +80,7 @@ class TodoDBConnection: def __init__(self, db_file): self.file = db_file self.conn = sql_connect(self.file.path) + self.conn.execute('PRAGMA foreign_keys = ON') def commit(self): self.file.backup() @@ -117,6 +118,9 @@ class VersionedAttribute: db_conn.exec(f'REPLACE INTO {self.table_name} VALUES (?, ?, ?)', (self.parent.id_, date, value)) + def delete(self, db_conn): + db_conn.exec(f'DELETE FROM {self.table_name} WHERE template = ?', (self.parent.id_,)) + @property def _newest_datetime(self): return sorted(self.history.keys())[-1] @@ -290,8 +294,19 @@ class TodoTemplate: self.default_effort.save(db_conn) self.description.save(db_conn) + def delete(self, db_conn): + for row in db_conn.exec('SELECT * FROM templates WHERE forked_from = ?', (self.id_,)): + raise HandledException('cannot delete forking reference') + self.title.delete(db_conn) + self.default_effort.delete(db_conn) + self.description.delete(db_conn) + db_conn.exec('DELETE FROM templates WHERE id = ?', (self.id_,)) + def fork(self, db_conn): - return self.__class__(db_conn, id_=None, forked_from=self.id_, forked_at=Day.todays_date(with_time=True)) + forked = self.__class__(db_conn, id_=None, forked_from=self.id_, + forked_at=Day.todays_date(with_time=True)) + forked.save(db_conn) + return forked @@ -299,9 +314,9 @@ class TodoTemplate: class ParamsParser: - def __init__(self, url_query, site_cookie): + def __init__(self, url_query, site_cookie_dict): self.params = parse_qs(url_query, keep_blank_values=True) - self.cookie = site_cookie + self.cookie = site_cookie_dict def get(self, key, default): return self.params.get(key, [default])[0] @@ -392,10 +407,15 @@ class TodoHandler(BaseHTTPRequestHandler): def do_POST_template(self): id_ = self.params.get('id', None) tmpl = TodoTemplate.by_id(self.db_conn, id_, make_if_none=True) - tmpl.title.set(self.postvars['title'][0]) - tmpl.default_effort.set(float(self.postvars['default_effort'][0])) - tmpl.description.set(self.postvars['description'][0]) - tmpl.save(self.db_conn) + if 'update' in self.postvars.keys(): + tmpl.title.set(self.postvars['title'][0]) + tmpl.default_effort.set(float(self.postvars['default_effort'][0])) + tmpl.description.set(self.postvars['description'][0]) + tmpl.save(self.db_conn) + elif 'fork' in self.postvars.keys(): + tmpl.fork(self.db_conn) + elif 'delete' in self.postvars.keys(): + tmpl.delete(self.db_conn) self.redir_url = 'templates' # GET routes @@ -565,6 +585,23 @@ class TestWithDB(TestCase): tmpl_2.save(self.db_conn) self.assertEqual({tmpl_1.id_, tmpl_2.id_}, set(t.id_ for t in TodoTemplate.all(self.db_conn))) + def test_TodoTemplate_delete(self): + tmpl = TodoTemplate(self.db_conn) + tmpl.title.set('foo') + tmpl.save(self.db_conn) + self.assertIsInstance(TodoTemplate.by_id(self.db_conn, tmpl.id_), TodoTemplate) + tmpl.delete(self.db_conn) + self.assertEqual(None, TodoTemplate.by_id(self.db_conn, tmpl.id_)) + tmpl = TodoTemplate(self.db_conn) + tmpl.save(self.db_conn) + fork = tmpl.fork(self.db_conn) + with self.assertRaises(HandledException): + tmpl.delete(self.db_conn) + self.assertIsInstance(TodoTemplate.by_id(self.db_conn, tmpl.id_), TodoTemplate) + fork.delete(self.db_conn) + tmpl.delete(self.db_conn) + self.assertEqual(None, TodoTemplate.by_id(self.db_conn, tmpl.id_)) + def test_versioned_attributes(self): def test(name, default, values): def wait_till_next_timestamp(timestamp): @@ -580,8 +617,8 @@ class TestWithDB(TestCase): self.assertEqual(tmpl_attr.newest, default) self.assertEqual({}, tmpl_attr.history) # check we get new value when set - timestamp_1 = Day.todays_date(with_time=True) tmpl_attr.set(values[0]) + timestamp_1 = Day.todays_date(with_time=True) self.assertEqual(tmpl_attr.newest, values[0]) # check history remains unchanged if setting same value as .newest timestamp_2 = wait_till_next_timestamp(timestamp_1) @@ -652,3 +689,22 @@ class TestSansDB(TestCase): day3 = Day('2024-01-03') days = [day3, day1, day2] self.assertEqual(sorted(days), [day1, day2, day3]) + + def test_ParamsParser(self): + from urllib.parse import urlencode + q = urlencode({'is_foo': 'foo', 'is_whitespace': '', 'is_None': None}) + c = {'is_bar': 'bar'} + p = ParamsParser(q, c) + # check basic retrieval and default behavior + self.assertEqual(p.get('is_foo', None), 'foo') + self.assertEqual(p.get('missing', None), None) + self.assertEqual(p.get('missing', 'default'), 'default') + # check handling of empty and None values + self.assertEqual(p.get('missing', ''), '') + self.assertEqual(p.get('is_whitespace', None), '') + self.assertEqual(p.get('is_None', None), 'None') # TODO: unwanted behavior, or urlencode fault? + # check retrieval and setting of cookied values + self.assertEqual(p.get_cookied('missing', None), None) + self.assertEqual(c['missing'], None) + self.assertEqual(p.get_cookied('missing', 'default'), None) + self.assertEqual(p.get_cookied('is_bar', None), 'bar') -- 2.30.2