Create/update a SQLite3 database file with all files in a given path.

filestosql.zsh

#!/bin/zsh
#
# Create/update a SQLite3 database file with all files in a given path.
#
# Usage:
#
#    filestosql SQLITE_DATABASE [PATH]
#

DB_FILE=${1}
SEARCH_ROOT=${2-${PWD}}

FORMAT_ITEMS=(
    "%i" # Inode
    "%l" # Number of hard links
    "%a" # Last access date
    "%c" # Last change date
    "%m" # Last modification date
    "%B" # Birth date date
    "%z" # Size of file in bytes
    "%T" # File type
    "%p" # File type
    "%N" # Name
)

# -----------------------------------------------------------------------
#

CSV_FILE=$(mktemp)

FORMAT=${(j:%t:)FORMAT_ITEMS}

echo Generating file list...

find $SEARCH_ROOT -exec stat -f $FORMAT {} \; > $CSV_FILE

sqlite3 -batch $DB_FILE << EOF
.mode tabs

.print Creating table...
CREATE TABLE IF NOT EXISTS files (
    inode INT,
    hardlink_count INT,
    last_access_ts INT,
    last_change_ts INT,
    last_modification_ts INT,
    birth_ts INT,
    size INT,
    type STRING,
    permissions STRING,
    name STRING
);

.print Emptying table...
DELETE FROM files;

.print Importing file...
.import $CSV_FILE files
.quit
EOF

Caveat emptor: this can’t handle filenames containing newlines