Merge the hierarchy into a single table

2 files changed, 50 insertions, 53 deletions

diff --git a/initialize.sql b/initialize.sql
index f42e55b..9698c69 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -12,46 +12,36 @@ CREATE INDEX IF NOT EXISTS image__dhash ON image(dhash);
 
 --
 
-CREATE TABLE IF NOT EXISTS directory(
-	id       INTEGER NOT NULL,                  -- unique ID
-	name     TEXT NOT NULL,                     -- basename
-	parent   INTEGER REFERENCES directory(id),  -- root if NULL
+CREATE TABLE IF NOT EXISTS node(
+	id       INTEGER NOT NULL,             -- unique ID
+	parent   INTEGER REFERENCES node(id),  -- root if NULL
+	name     TEXT NOT NULL,                -- path component
+	mtime    INTEGER,                      -- files: Unix time in seconds
+	sha1     TEXT REFERENCES image(sha1),  -- files: content hash
 	PRIMARY KEY (id)
 ) STRICT;
 
-CREATE UNIQUE INDEX IF NOT EXISTS directory__parent_name
-ON directory(IFNULL(parent, 0), name);
+CREATE INDEX IF NOT EXISTS node__sha1 ON node(sha1);
+CREATE UNIQUE INDEX IF NOT EXISTS node__parent_name
+ON node(IFNULL(parent, 0), name);
 
--- FIXME: I want a nullable parent, but that can't be a primary key.
---  - Perhaps have an INTEGER for the PK of entry, and use a UNIQUE INDEX.
---  - Alternatively, create a directory record for the root.
-CREATE TABLE IF NOT EXISTS entry(
-	parent   INTEGER NOT NULL REFERENCES directory(id),
-	name     TEXT NOT NULL,     -- last FS path component
-	mtime    INTEGER NOT NULL,  -- Unix time of last modification in seconds
-	sha1     TEXT NOT NULL REFERENCES image(sha1),
-	PRIMARY KEY (parent, name)
-) STRICT;
-
-CREATE INDEX IF NOT EXISTS entry__sha1 ON entry(sha1);
-
-/*
-Automatic garbage collection, not sure if it actually makes any sense:
-
-CREATE TRIGGER IF NOT EXISTS entry__parent__gc
-AFTER DELETE ON entry FOR EACH ROW
+CREATE TRIGGER IF NOT EXISTS node__sha1__check
+BEFORE UPDATE OF sha1 ON node
+WHEN OLD.sha1 IS NULL AND NEW.sha1 IS NOT NULL
+AND EXISTS(SELECT id FROM node WHERE parent = OLD.id)
 BEGIN
-	DELETE FROM directory WHERE id = OLD.parent
-	AND id NOT IN (SELECT DISTINCT parent FROM entry)
-	AND id NOT IN (SELECT DISTINCT parent FROM directory);
+	SELECT RAISE(ABORT, 'trying to turn a non-empty directory into a file');
 END;
 
-CREATE TRIGGER IF NOT EXISTS directory__parent__gc
-AFTER DELETE ON directory FOR EACH ROW
+/*
+Automatic garbage collection, not sure if it actually makes any sense.
+It's also not recursive, but it should be:
+
+CREATE TRIGGER IF NOT EXISTS node__parent__gc
+AFTER DELETE ON node FOR EACH ROW
 BEGIN
-	DELETE FROM directory WHERE id = OLD.parent
-	AND id NOT IN (SELECT DISTINCT parent FROM entry)
-	AND id NOT IN (SELECT DISTINCT parent FROM directory);
+	DELETE FROM node WHERE id = OLD.parent
+	AND id NOT IN (SELECT DISTINCT parent FROM node);
 END;
 */
 
diff --git a/main.go b/main.go
index 5fdcd22..7edf574 100644
--- a/main.go
+++ b/main.go
@@ -109,9 +109,13 @@ func (pb *progressBar) Stop() {
 }
 
 func (pb *progressBar) update() {
+	var fraction int
+	if pb.target != 0 {
+		fraction = int(float32(pb.current) / float32(pb.target) * 100)
+	}
+
 	target := fmt.Sprintf("%d", pb.target)
-	fmt.Printf("\r%*d/%s (%2d%%)", len(target), pb.current, target,
-		int(float32(pb.current)/float32(pb.target)*100))
+	fmt.Printf("\r%*d/%s (%2d%%)", len(target), pb.current, target, fraction)
 }
 
 func (pb *progressBar) Step() {
@@ -197,8 +201,8 @@ func handleThumbs(w http.ResponseWriter, r *http.Request) {
 
 func getSubdirectories(tx *sql.Tx, parent int64) (names []string, err error) {
 	// TODO: This is like dbCollectStrings(), just needs an argument.
-	rows, err := tx.Query(
-		`SELECT name FROM directory WHERE IFNULL(parent, 0) = ?`, parent)
+	rows, err := tx.Query(`SELECT name FROM node
+		WHERE IFNULL(parent, 0) = ? AND sha1 IS NULL`, parent)
 	if err != nil {
 		return nil, err
 	}
@@ -225,9 +229,10 @@ type webEntry struct {
 
 func getSubentries(tx *sql.Tx, parent int64) (entries []webEntry, err error) {
 	rows, err := tx.Query(`
-		SELECT i.sha1, e.name, e.mtime, IFNULL(i.thumbw, 0), IFNULL(i.thumbh, 0)
-		FROM entry AS e
-		JOIN image AS i ON e.sha1 = i.sha1 WHERE e.parent = ?`, parent)
+		SELECT i.sha1, n.name, n.mtime, IFNULL(i.thumbw, 0), IFNULL(i.thumbh, 0)
+		FROM node AS n
+		JOIN image AS i ON n.sha1 = i.sha1
+		WHERE n.parent = ?`, parent)
 	if err != nil {
 		return nil, err
 	}
@@ -297,10 +302,10 @@ func getImageDimensions(sha1 string) (w int64, h int64, err error) {
 
 func getImagePaths(sha1 string) (paths []string, err error) {
 	rows, err := db.Query(`WITH RECURSIVE paths(parent, path) AS (
-		SELECT parent, name AS path FROM entry WHERE sha1 = ?
+		SELECT parent, name AS path FROM node WHERE sha1 = ?
 		UNION ALL
-		SELECT d.parent, d.name || '/' || p.path
-		FROM directory AS d JOIN paths AS p ON d.id = p.parent
+		SELECT n.parent, n.name || '/' || p.path
+		FROM node AS n JOIN paths AS p ON n.id = p.parent
 	) SELECT path FROM paths WHERE parent IS NULL`, sha1)
 	if err != nil {
 		return nil, err
@@ -524,9 +529,9 @@ func cmdRun(args []string) error {
 func idForPath(tx *sql.Tx, path []string, create bool) (int64, error) {
 	var parent sql.NullInt64
 	for _, name := range path {
-		if err := tx.QueryRow(
-			`SELECT id FROM directory WHERE name = ? AND parent IS ?`,
-			name, parent).Scan(&parent); err == nil {
+		if err := tx.QueryRow(`SELECT id FROM node
+			WHERE parent IS ? AND name = ? AND sha1 IS NULL`,
+			parent, name).Scan(&parent); err == nil {
 			continue
 		} else if !errors.Is(err, sql.ErrNoRows) {
 			return 0, err
@@ -534,9 +539,11 @@ func idForPath(tx *sql.Tx, path []string, create bool) (int64, error) {
 			return 0, err
 		}
 
+		// This fails when trying to override a leaf node.
+		// That needs special handling.
 		if result, err := tx.Exec(
-			`INSERT INTO directory(name, parent) VALUES (?, ?)`,
-			name, parent); err != nil {
+			`INSERT INTO node(parent, name) VALUES (?, ?)`,
+			parent, name); err != nil {
 			return 0, err
 		} else if id, err := result.LastInsertId(); err != nil {
 			return 0, err
@@ -689,10 +696,10 @@ func (i *importer) Import(path string) error {
 	}
 
 	// FIXME: This disallows any entries directly in the root.
-	_, err = tx.Exec(`INSERT INTO entry(parent, name, mtime, sha1)
-		VALUES (?, ?, ?, ?) ON CONFLICT DO UPDATE SET mtime = ?, sha1 = ?`,
-		dbParent, dbBasename, s.ModTime().Unix(), hexSHA1,
-		s.ModTime().Unix(), hexSHA1)
+	_, err = tx.Exec(`INSERT INTO node(parent, name, mtime, sha1)
+		VALUES (?, ?, ?, ?) ON CONFLICT DO
+		UPDATE SET mtime = excluded.mtime, sha1 = excluded.sha1`,
+		dbParent, dbBasename, s.ModTime().Unix(), hexSHA1)
 	if err != nil {
 		return err
 	}
@@ -700,7 +707,7 @@ func (i *importer) Import(path string) error {
 	return tx.Commit()
 }
 
-// cmdImport adds files to the "entry" table.
+// cmdImport adds files to the "node" table.
 // TODO: Consider making this copy rather than symlink images.
 func cmdImport(args []string) error {
 	if len(args) < 1 {
@@ -845,7 +852,7 @@ func cmdTag(args []string) error {
 			return nil
 		}
 		if _, err := stmt.Exec(sha1, spaceID, tag, weight, weight); err != nil {
-			return err
+			return fmt.Errorf("%s: %s", sha1, err)
 		}
 	}
 	if err := scanner.Err(); err != nil {