Database Voodoo

A new directory is added to the database with dirInsert() as of line 85. If the SQLite database does not yet exist (Listing 1 checks for the existence of the database file), line 76 builds a new one and creates a fresh dirs table in it with the SQL create command. The table's two columns, dir and date, are of the text type. The fact that the directory path is a text string is not surprising, but SQLite also saves dates as text and compares them in string mode, which works because the timestamps are in the format YYYY-MM-DD HH::MM::SS, so later times also alphanumerically follow earlier ones.

We do not want SQLite to generate a new table row for existing paths, but simply refresh the existing entry's timestamp. This could be solved in SQL by a separate select query, followed by case logic, but in the SQLite dialect this is done more elegantly with the special REPLACE function (line 86).

This works in a similar way to SQL's standard UPDATE function, except it creates missing entries – but only if a unique index is defined for the corresponding table column. This explains why line 80 adds an index to the dir column after the table definition, causing replace in line 86 to create new entries and refresh old ones as needed.

The dirList() function retrieves existing database entries. The select statement in line 52 sorts them in descending order according to insertion date (i.e., entries created shortly beforehand appear at the top of the selection list). The LIMIT 10 statement fetches a maximum of 10, but since the terminal list display scrolls down on request, you could just as easily leave this out.

The for loop starting in line 59 uses rows.Next() and rows.Scan() to fetch the search query's next hits; the append statement in line 63 appends them to the items array slice. If the database is still unpopulated, line 67 inserts the user's home directory; otherwise, the displayed selection list would be empty and leave the user confused.

If two or more hits are found, line 69 steals the first one and removes it from the list, because this is the entry of the last directory visited (i.e., the current directory to which the user will probably not want to jump). The dbPath() function starting in line 95 specifies the path to the SQLite file in which the data is stored, this is hard-coded in Listing 1 as ~/.cdbm.db in the user's home directory.

Quite Wordy

It's worth noting that a Go program, which actually doesn't contain so much logic, needs a large number of lines. Go's relentlessly required, explicit error handling of each return value is partly to blame. An exception handler would be more compact for such a simple utility. The panicOnErr() function starting in line 103, which checks an error value passed to it and immediately aborts the program by calling panic(), helps to save lines. It is rumored that the next Go version will accommodate program authors with more compact mechanisms.

More Convenience

For hobbyists, the fun only starts here. For instance, the script could be extended to include a search function that only offers paths that match a search term entered at the command line. For example, the user could enter c usr and only see paths containing the string usr. And since all the user data are stored in an SQLite database whose schema can be easily extended, it makes sense to assign a counter to each stored path, which cdbm increments by one each time a directory is visited.

This could shift frequently visited paths higher up in the selection list based on an algorithm, because users would not want to scroll a long way to access frequently used paths. And, who knows, maybe it's worth adding a dash of artificial intelligence? A self-learning directory butler would be attractive to my younger coworkers.

The Author

Mike Schilli works as a software engineer in the San Francisco Bay area, California. Each month in his column, which has been running since 1997, he researches practical applications of various programming languages. If you email him at mailto:mschilli@perlmeister.com he will gladly answer any questions.