use an actual schema for storing tag data

4 files changed, 78 insertions, 77 deletions

diff --git a/example/internal_tables.md b/example/internal_tables.md
new file mode 100644
index 0000000..a43b806
--- /dev/null
+++ b/example/internal_tables.md
@@ -0,0 +1,14 @@
+Underwriter uses SQLite to store tag information. The database structure
+is based off of the ["Toxi"][1] schema.
+
+[1]: http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/
+
+The three tables used are
+
+* `cards`, containing the name and creation date
+* `tags`, containing an ID of the tag and the name
+* `tagmap`, where each row matches a card with a tag
+
+The `tagmap` is many-to-many. For instance, if `X` is a member of
+`cards`, and `Y`, `Z` are members of tags, then `(X,Y)` and
+`(X,Z)` can be members of `tagmap`.
diff --git a/example/tag_idempotent.md b/example/tag_idempotent.md
index 07c695c..f8fa64f 100644
--- a/example/tag_idempotent.md
+++ b/example/tag_idempotent.md
@@ -1,5 +1,5 @@
 It is fine to add the same tag multiple times to a card: `underwriter`
 will merge multiple tag mentions.
 
-    underwriter tag tag_idempotent.md usage example command command
-    underwriter tag tag_idempotent.md usage usage example example
+    underwriter tag tag_idempotent.md usage example example
+    underwriter tag tag_idempotent.md usage feature
diff --git a/example/underwriter.db b/example/underwriter.db
index f04ccb7..5d1c961 100644
--- a/example/underwriter.db
+++ b/example/underwriter.db
diff --git a/underwriter b/underwriter
index c3115ee..dcc2012 100755
--- a/underwriter
+++ b/underwriter
@@ -6,104 +6,91 @@ import sys
 from datetime import datetime, timezone
 import base64
 
-def b64(s):
-	return base64.b64encode(s.encode()).decode()
 def current_time():
 	return int(datetime.now(timezone.utc).timestamp())
 
-# TODO: triggers for tags
-
 class Cursor(sqlite3.Cursor):
-	def new_tag(self, tag):
-		self.execute(f"""
-		CREATE TABLE IF NOT EXISTS "{b64(tag)}"
-		(fn TEXT PRIMARY KEY NOT NULL)
-		""")
-		self.execute(
-		"INSERT OR IGNORE INTO tags VALUES (?, '')",
-		(tag,))
-
-	def add_card_to_tag(self, name, tag):
-		self.execute(f"""
-		INSERT OR IGNORE INTO "{b64(tag)}" VALUES (?)""", (name,))
-
-	def remove_card_from_tag(self, name, tag):
-		self.execute(f"""
-		DELETE FROM "{b64(tag)}" WHERE name = ?""", (name,))
-
-def append_tab(listing, t):
-	return t if listing is None else listing + "\t" + t
+	def new_tags(self, tags):
+		tags = [(x,) for x in tags]
+		self.executemany("""INSERT INTO tags (name) VALUES (?)
+			ON CONFLICT DO NOTHING
+		""", tags
+		)
+	def delete_tag(self, tag):
+		self.execute("DELETE FROM tags WHERE name = ?", (tag,))
+
+	def new_card(self, name):
+		self.execute("""INSERT INTO cards
+			(name,created) VALUES (?,?)
+			ON CONFLICT DO NOTHING
+			""", (name, current_time())
+		)
+	def delete_card(self, name):
+		self.execute("DELETE FROM cards WHERE name = ?", (name,))
+
+	def add_tags_to_cards(self, pairs):
+		self.executemany("""INSERT INTO tagmap VALUES
+			  ((SELECT id FROM cards WHERE name = ?),
+			   (SELECT id FROM tags WHERE name = ?))
+			""", pairs
+		)
+
+	def remove_tags_from_cards(self, pairs):
+		self.executemany("""DELETE FROM tagmap WHERE
+			card = (SELECT id FROM cards WHERE name = ?)
+			AND tag = (SELECT id FROM tags WHERE name = ?)
+			""", pairs
+		)
 
 class Context:
 	@staticmethod
 	def initdb(dbname):
 		con = sqlite3.connect(dbname)
 		con.executescript("""
-CREATE TABLE cards (name TEXT PRIMARY KEY NOT NULL,
-                    tags TEXT NOT NULL DEFAULT(""),
-                    created INTEGER NOT NULL)
-WITHOUT ROWID;
-CREATE TABLE tags (name TEXT PRIMARY KEY NOT NULL,
-                   included TEXT NOT NULL DEFAULT(""))
-WITHOUT ROWID;
+CREATE TABLE cards (id INTEGER PRIMARY KEY,
+                    name TEXT UNIQUE NOT NULL,
+                    created INTEGER NOT NULL
+);
+CREATE TABLE tags (id INTEGER PRIMARY KEY,
+                   name TEXT UNIQUE NOT NULL
+);
+CREATE TABLE tagmap(card INTEGER NOT NULL,
+                    tag INTEGER NOT NULL,
+                    FOREIGN KEY(tag) REFERENCES tags
+                      ON UPDATE RESTRICT
+                      ON DELETE CASCADE,
+                    FOREIGN KEY (card) REFERENCES cards
+                     ON UPDATE RESTRICT
+                     ON DELETE CASCADE
+);
+
+-- XXX: Find a more efficient way to ensure uniqueness?
+CREATE TRIGGER tagmap_unique AFTER INSERT ON tagmap
+	WHEN (SELECT COUNT(*) FROM tagmap WHERE
+	 NEW.tag = tag AND NEW.card = card LIMIT 2) > 1
+	BEGIN DELETE FROM tagmap WHERE NEW.rowid = rowid;
+END;
 """)
 		return con
 
-	def name_exists(self, name):
-		cur = self.db.execute("SELECT name FROM cards WHERE (name = ?)",
-		(name,))
-		return cur.fetchone() == (name)
-
 	def make_cursor(self):
 		return self.db.cursor(Cursor)
 
-	def get_card(self, name):
-		vals = self.db.execute(
-		"SELECT tags,created FROM cards WHERE name = ?",
-		(name,)).fetchone()
-		return (None, None) if vals is None else vals
-
 	def card_add_tags(self, name, tags):
-		oldtags,created = self.get_card(name)
-		if created is None:
-			created = current_time()
-
-		oldtags = set([] if oldtags is None else oldtags.split("\t"))
 		cur = self.make_cursor()
-
-		# XXX: skip tags common to oldtags?
-		for t in tags:
-			cur.new_tag(t)
-			cur.add_card_to_tag(name, t)
-
-		cur.execute(
-		"INSERT OR REPLACE INTO cards VALUES (?, ?, ?)",
-		(name, "\t".join(list((oldtags | tags))), created))
+		cur.new_card(name)
+		cur.new_tags(tags)
+		cur.add_tags_to_cards([(name,t) for t in tags])
 		self.db.commit()
 
 	def card_remove_tags(self, name, tags):
-		oldtags,created = self.get_card(name)
-		if tags is None:
-			return None
-
-		oldtags = set([] if oldtags is None else oldtags.split("\t"))
-		oldtags -= tags
-
-		self.db.execute(
-		"INSERT OR REPLACE INTO cards VALUES (?,?,?)",
-		(name, "\t".join(list(oldtags)), created))
+		cur = self.make_cursor()
+		cur.remove_tags_from_cards([(name,t) for t in tags])
 		self.db.commit()
 
 	def remove_card(self, name):
-		tags,_ = self.get_card(name)
-		if tags is None:
-			return None
-
 		cur = self.make_cursor()
-
-		cur.execute("DELETE FROM cards WHERE name = ?", (name,))
-		for t in tags.split("\t"):
-			cur.remove_card_from_tag(name, t)
+		cur.remove_card(name)
 		self.db.commit()
 
 	def __init__(self, name):
@@ -140,12 +127,12 @@ if __name__ == "__main__":
 		usage()
 		sys.exit(0)
 	if args[0] == "tag":
-		ctx.card_add_tags(args[1], set(args[2:]))
+		ctx.card_add_tags(args[1], args[2:])
 	elif args[0] == "remove":
 		for i in args[1:]:
 			ctx.remove(i)
 	elif args[0] == "untag":
-		ctx.card_remove_tags(args[1], set(args[2:]))
+		ctx.card_remove_tags(args[1], args[2:])
 	else:
 		usage()
 		sys.exit(1)