Molding and Casting

So, how does the idb tool work? First, idb contacts the tag database, which resides in the .idb.db file in the user's home directory. If idb does not find a database there, it creates a new one. To do this, idb uses the SQL commands in createDBSQL() starting in line 23 in Listing 1.

01 package main
02
03 import (
04   "database/sql"
05   _ "github.com/mattn/go-sqlite3"
06   "os"
07   "path"
08 )
09
10 const DbName = ".idb.db"
11
12 func dbInit() (*sql.DB, error) {
13   homedir, err := os.UserHomeDir()
14   if err != nil {
15     return nil, err
16   }
17   dbPath := path.Join(homedir, DbName)
18   db, err := sql.Open("sqlite3", dbPath)
19   _, err = db.Exec(createDBSQL())
20   return db, err
21 }
22
23 func createDBSQL() string {
24   return `
25 CREATE TABLE IF NOT EXISTS tag (
26   id INTEGER PRIMARY KEY,
27   name TEXT UNIQUE
28 );
29 CREATE TABLE IF NOT EXISTS tagmap (
30   tag_id INTEGER,
31   file_id INTEGER,
32   UNIQUE(tag_id, file_id)
33 );
34 CREATE TABLE IF NOT EXISTS file (
35   id INTEGER PRIMARY KEY,
36   name TEXT UNIQUE
37 );`
38 }

Some special SQLite features in the three table definitions will facilitate the insertion work later on. The tag and file tables, which assign numeric IDs to tag and file paths, define an integer with the primary key attribute as their first id column. This prompts SQLite to constantly increment the IDs of new entries by one – ideal for referencing them by their IDs later on in a subtle and unique way.

The UNIQUE specifier in the string column to the right for tag or file names determines that the table does not allow duplicate name entries. As a practical side effect, the tool can later use INSERT OR IGNORE to create entries in one fell swoop if they do not yet exist and leave existing ones there without any complaints.

Name to Number

The name2id() function in Listing 2 does just that: It expects a name, either a file name or a tag name, and inserts it into the file or tag lookup tables, depending on what the table parameter is set to. If the name does not yet exist, the INSERT command appends the new entry at the bottom of the table in line 10, and SQLite automatically generates a previously unused index integer number id for it.

01 package main
02
03 import (
04   "database/sql"
05   "fmt"
06   _ "github.com/mattn/go-sqlite3"
07 )
08
09 func name2id(db *sql.DB, table string, name string) (int, error) {
10   query := fmt.Sprintf("INSERT OR IGNORE INTO %s(name) VALUES(?)", table)
11   stmt, err := db.Prepare(query)
12   panicOnErr(err)
13
14   _, err = stmt.Exec(name)
15   panicOnErr(err)
16
17   id := -1
18
19   query = fmt.Sprintf("SELECT id FROM %s WHERE name = ?", table)
20   row := db.QueryRow(query, name)
21   _ = row.Scan(&id)
22
23   return id, nil
24 }
25
26 func tagMap(db *sql.DB, tagId, fileId int) {
27   query := "INSERT OR IGNORE INTO tagmap(tag_id, file_id) VALUES(?, ?)"
28   stmt, err := db.Prepare(query)
29   panicOnErr(err)
30   _, err = stmt.Exec(tagId, fileId)
31   panicOnErr(err)
32   return
33 }
34
35 func tagSearch(db *sql.DB, tagId int) ([]string, error) {
36   result := []string{}
37   query := `
38     SELECT file.name FROM file, tagmap
39     WHERE tagmap.tag_id = ?
40     AND file.id = tagmap.file_id;`
41   rows, err := db.Query(query, tagId)
42   if err != nil {
43     return result, err
44   }
45
46   for rows.Next() {
47     path := ""
48     err = rows.Scan(&path)
49     if err != nil {
50       return result, err
51     }
52     result = append(result, path)
53   }
54
55   return result, nil
56 }
57
58 func tagList(db *sql.DB) {
59   query := `SELECT name FROM tag`
60   rows, err := db.Query(query)
61   panicOnErr(err)
62
63   for rows.Next() {
64     tag := ""
65     err = rows.Scan(&tag)
66     panicOnErr(err)
67     fmt.Printf("%s\n", tag)
68   }
69
70    return
71 }

After using Prepare to prepare the statement in line 11, Exec() in line 14 plants the name into the SQL command, using a technique that provides protection against SQL injection attacks, and then executes the query. If the file or tag already exists in the database, nothing happens thanks to OR IGNORE.

The subsequent name lookup using SELECT in line 19 looks for the same entry again and returns the associated id after line 21 has retrieved the only matching row. The name2id() function is nothing more than a convenient, persistent way to assign names to numeric IDs and return the latter on request.

Using the index numbers for the tag and file, tagMap() can glue a tag to a file starting in line 26, calling INSERT to append a new table row with the two numbers. If the file already has the tag, OR IGNORE will keep things tidy, as previously shown.

When Three Become One

To find all files with a certain tag, the SELECT statement in the tagSearch() function needs to join all three tables starting in line 35. The tag I am looking for in tag has an index number that tagmap assigns to the index number of a file, whose name in turn is indexed in the file table.

Because the tag I am looking for is already present as an index number, because of the previous call to name2id(), it is up to the query to define a condition that welds together the two tables, tagmap and file. The file_id of the tagmap table needs to match the id from the file table. Line 46 gobbles up the names of the matching files that come pouring in and appends them to the result variable in line 52. This is a slice of strings that the function returns to the caller when done.

Finally, the last function in Listing 2, tagList, lists all tag names found in the tag table after SQLite extracts them with the SELECT command starting in line 59.

To wrap it all up, the main program in Listing 3 processes flags such as --tag (set or query a tag) or --tags (list all defined tags) that the user includes in the call at the command line. It opens the database in line 23, where dbInit() creates the SQLite file if it does not already exist. If there are no files following a --tag=... option, line 40 uses tagSearch() to fetch all matching file paths from the database, and the for loop starting in line 42 prints them out. If --xlink is also set, line 44 creates a symlink to each matching photo file in the current directory respectively. This way it's easy to create, view, and process collections of photos with identical tags in a temporary directory.

01 package main
02
03 import (
04   "flag"
05   "fmt"
06   "os"
07   "path"
08   "path/filepath"
09 )
10
11 func main() {
12   flag.Usage = func() {
13     fmt.Printf("Usage: %s --tag=tagname photo ...\n", path.Base(os.Args[0]))
14     os.Exit(1)
15   }
16
17   tags := flag.Bool("tags", false, "list all tags")
18   xlink := flag.Bool("xlink", false, "create links in current dir")
19
20   tag := flag.String("tag", "", "tag to assign/search")
21   flag.Parse()
22
23   db, err := dbInit()
24   panicOnErr(err)
25   defer db.Close()
26
27   if *tags {
28     tagList(db)
29     return
30   }
31
32   if tag == nil {
33     flag.Usage()
34   }
35
36   tagId, err := name2id(db, "tag", *tag)
37   panicOnErr(err)
38
39   if flag.NArg() == 0 {
40     matches, err := tagSearch(db, tagId)
41     panicOnErr(err)
42     for _, match := range matches {
43       if *xlink {
44         err := os.Symlink(match, filepath.Base(match))
45         panicOnErr(err)
46       }
47       fmt.Println(match)
48     }
49   } else {
50     for _, file := range flag.Args() {
51       ppath, err := filepath.Abs(file)
52       panicOnErr(err)
53       fileId, err := name2id(db, "file", ppath)
54       panicOnErr(err)
55
56       fmt.Printf("Tagging %s with %s\n", ppath, *tag)
57
58       tagMap(db, tagId, fileId)
59       panicOnErr(err)
60     }
61   }
62 }
63
64 func panicOnErr(err error) {
65   if err != nil {
66     panic(err)
67   }
68 }

If the user has passed one or more files to the tag specification, the else branch tags the files starting in line 49. To do this, line 53 first uses name2id to insert the name of the image file into the index (if it is not already there); it then calls tagMap() with the index number retrieved in line 58, which establishes the relation in the tagmap table.

For error handling, the listings use panicOnErr() (from line 64) in the usual way. This makes short work of errors and aborts the program. In production software, errors are usually handled by returning to higher levels instead.

You can compile the whole thing, including all referenced GitHub packages, with the commands in Listing 4. The result is the binary idb, which offers you all of the functions discussed in this column.

$ go mod init idb
$ go mod tidy
$ go build idb.go db.go dbinit.go