redo schema

The new schema can now store blobs and file strings (which can be
URLs or other names). These are different tables.

In the future the cards table can have two columns, where one and
only one must be NULL, with a CHECK to confirm that. Then coalesce()
can be used to retrieve data from the table.

2 files changed, 55 insertions, 4 deletions

diff --git a/README.md b/README.md
index f2befb2..8a6256b 100644
--- a/README.md
+++ b/README.md
@@ -7,8 +7,7 @@ TODO
 ----
 
 * Transclusion parsing
-* Adding titles to cards
-* Storing cards as BLOBs in SQL database
 * caching queries (globs, tag literals, etc.)
 * Rewrite it (in another language?) if speed is an issue
-
+* Add untag_all command
+* optimize some sql statements
diff --git a/underwriter b/underwriter
index 534eb32..44cf458 100755
--- a/underwriter
+++ b/underwriter
@@ -7,6 +7,7 @@ from datetime import datetime, timezone
 import base64
 from natargs import Arguments
 import queryparse
+import io
 
 def current_time():
 	return int(datetime.now(timezone.utc).timestamp())
@@ -30,6 +31,17 @@ class Cursor(sqlite3.Cursor):
 	def delete_card(self, name):
 		self.execute("DELETE FROM cards WHERE name = ?", (name,))
 
+	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))
+	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))
+
 	def add_tags_to_cards(self, pairs):
 		self.executemany("""INSERT INTO tagmap VALUES
 			  ((SELECT id FROM cards WHERE name = ?),
@@ -49,6 +61,25 @@ initscript = \
                     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
+);
+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
 );
@@ -112,6 +143,19 @@ class Context:
 		for (i,) in self.db.execute(exec, lits).fetchall():
 			print(i)
 
+	def set_filename(self, l):
+		cur = self.make_cursor()
+		cur.associate_file(l[0], l[1])
+		self.db.commit()
+	def set_blob(self, l):
+		cur = self.make_cursor()
+		if l[1] == "-":
+			buf = "".join(sys.stdin.readlines())
+		else:
+			buf = "".join(io.open(l[1]).readlines())
+		cur.associate_file(l[0], buf)
+		self.db.commit()
+
 	def __init__(self, name):
 		if not os.path.isfile(name):
 			self.db = Context.initdb(name)
@@ -125,6 +169,9 @@ tag file [tags...]: add a file to the database if it not already in
     it, and add 0 or more tags to it
 untag file [tags...]: remove tags from a file
 
+set [filename | blob] name file: associate a filename (or the contents
+    of the file) with a name.
+
 list [tags | cards]: list all tags or cards in the database
 """
 
@@ -145,7 +192,12 @@ if __name__ == "__main__":
 	argparse.addarg(["tag"], lambda x:x > 0, ctx.card_add_tags)
 	argparse.addarg(["untag"], lambda x:x > 1, ctx.card_remove_tags)
 	argparse.addarg(["list", "tags"], lambda x: x == 0, ctx.list_tags)
-	argparse.addarg(["list", "cards"], lambda x: x == 0, ctx.list_cards)
+	argparse.addarg(["list", "cards"], lambda x: x == 0,
+		ctx.list_cards)
 	argparse.addarg(["search"], lambda x: x > 0, ctx.query)
 
+	argparse.addarg(["set", "filename"], lambda x: x == 2,
+		ctx.set_filename)
+	argparse.addarg(["set", "blob"], lambda x: x == 2, ctx.set_blob)
+
 	argparse.execarg(args)