diff options
| -rw-r--r-- | initialize.sql | 54 | ||||
| -rw-r--r-- | main.go | 49 |
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; */ @@ -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 { |