modify schema (again)

This bundles the data and filename into the cards table, with a
CHECK enforcing that both cannot be set. They are unset when they
are NULL. Cards can have both data and filename be NULL, meaning
that there is nothing associated with the title. These cards can
still be tagged like normal.

When inserting a new blob (or filename), the program checks if there
is currently a filename (or blob, when you insert a filename) associated
with the card. The program will then prompt you if you want to overwrite
the blob or filename.

3 files changed, 38 insertions, 27 deletions

diff --git a/README.md b/README.md
index 8a6256b..9ae8102 100644
--- a/README.md
+++ b/README.md
@@ -11,3 +11,5 @@ TODO
 * Rewrite it (in another language?) if speed is an issue
 * Add untag_all command
 * optimize some sql statements
+* Redirects?
+* Globs for untagging
diff --git a/example/underwriter.db b/example/underwriter.db
deleted file mode 100644
index 2e270cc..0000000
--- a/example/underwriter.db
+++ /dev/null
diff --git a/underwriter b/underwriter
index 44cf458..968d51d 100755
--- a/underwriter
+++ b/underwriter
@@ -33,14 +33,13 @@ class Cursor(sqlite3.Cursor):
 
 	def associate_file(self, name, file):
 		self.new_card(name)
-		self.execute("""INSERT OR REPLACE INTO files VALUES
-			((SELECT id FROM cards WHERE name = ?), ?)""",
-			(name, file))
+		self.execute("UPDATE cards SET file = ? WHERE name = ?",
+			(file, name))
+
 	def associate_blob(self, name, blob):
 		self.new_card(name)
-		self.execute("""INSERT OR REPLACE INTO blobs VALUES
-			((SELECT id FROM cards WHERE name = ?), ?)""",
-			(name, blob))
+		self.execute("UPDATE cards SET dat = ? WHERE name = ?",
+			(blob, name))
 
 	def add_tags_to_cards(self, pairs):
 		self.executemany("""INSERT INTO tagmap VALUES
@@ -59,26 +58,12 @@ class Cursor(sqlite3.Cursor):
 initscript = \
 """CREATE TABLE cards (id INTEGER PRIMARY KEY,
                     name TEXT UNIQUE NOT NULL,
-                    created INTEGER NOT NULL
-);
-CREATE TABLE files (id INTEGER UNIQUE NOT NULL,
-                    filename TEXT NOT NULL,
-                    FOREIGN KEY (id) REFERENCES cards
-                    ON UPDATE RESTRICT
-                    ON DELETE CASCADE
-);
-CREATE TABLE blobs (id INTEGER UNIQUE NOT NULL,
-                    content BLOB NOT NULL,
-                    FOREIGN KEY (id) REFERENCES cards
-                    ON UPDATE RESTRICT
-                    ON DELETE CASCADE
+                    dat BLOB, file TEXT,
+                    created INTEGER NOT NULL,
+                    CHECK (CASE WHEN dat NOT NULL THEN file IS NULL
+                          WHEN file NOT NULL THEN dat IS NULL
+                    END)
 );
-CREATE TRIGGER files_disjoint_blobs BEFORE INSERT ON files
-	BEGIN SELECT RAISE(ABORT, "file already in blobs")
-	FROM blobs WHERE NEW.id = blobs.id; END;
-CREATE TRIGGER blobs_disjoint_files BEFORE INSERT ON blobs
-	BEGIN SELECT RAISE(ABORT, "blob already in files")
-	FROM files WHERE NEW.id = files.id; END;
 
 CREATE TABLE tags (id INTEGER PRIMARY KEY,
                    name TEXT UNIQUE NOT NULL
@@ -107,6 +92,14 @@ class Context:
 		con.executescript(initscript)
 		return con
 
+	def get_data(self, s):
+		l = self.db.execute("""SELECT
+			dat NOT NULL, file NOT NULL FROM cards
+			WHERE name = ?""", (s,)).fetchall()
+		if len(l) == 0:
+			l = [(0,0)]
+		return {"blob" : l[0][0], "filename" : l[0][1]}
+
 	def make_cursor(self):
 		return self.db.cursor(Cursor)
 
@@ -143,17 +136,33 @@ class Context:
 		for (i,) in self.db.execute(exec, lits).fetchall():
 			print(i)
 
+	def _check_dup(self, n, typ, f):
+		od = self.get_data(n)
+		if od is not None and od[typ]:
+			r = input(
+f""""{n}" already has a {typ} associated with it. Overwrite? [y/N]: """)
+			if len(r) == 0 or r.lower()[0] != "y":
+				return False
+			f(n, None)
+		return True
+
 	def set_filename(self, l):
 		cur = self.make_cursor()
+		if not self._check_dup(l[0], "blob", cur.associate_blob):
+			print("Aborting!")
+			return None
 		cur.associate_file(l[0], l[1])
 		self.db.commit()
 	def set_blob(self, l):
 		cur = self.make_cursor()
+		if not self._check_dup(l[0], "filename", cur.associate_file):
+			print("Aborting!")
+			return None
 		if l[1] == "-":
 			buf = "".join(sys.stdin.readlines())
 		else:
 			buf = "".join(io.open(l[1]).readlines())
-		cur.associate_file(l[0], buf)
+		cur.associate_blob(l[0], buf)
 		self.db.commit()
 
 	def __init__(self, name):
@@ -182,7 +191,7 @@ if __name__ == "__main__":
 	argparse = Arguments(usagestr)
 
 	if len(args) == 0:
-		argparse.usage()
+		argparse.usage(1)
 
 	if args[0] == "--database" or args[0] == "-d":
 		db = args[1]