#!/bin/bash
set -e

DB="${1:-packages.sqlite3}"

rm -f "$DB"

sqlite3 "$DB" <<'SQL'
CREATE TABLE files (
    path    TEXT NOT NULL,
    package TEXT NOT NULL
);

CREATE INDEX idx_files_path ON files(path);
CREATE INDEX idx_files_package ON files(package);
SQL

{
    echo 'BEGIN;'

    dpkg-query -W -f='${binary:Package}\n' |
    while IFS= read -r package; do
        echo "Processing $package..." >&2

        dpkg -L "$package" |
        while IFS= read -r path; do
            # Экранируем одинарные кавычки для SQL.
            path=${path//\'/\'\'}
            package_sql=${package//\'/\'\'}

            printf "INSERT INTO files(path, package) VALUES ('%s', '%s');\n" \
                "$path" "$package_sql"
        done
    done

    echo 'COMMIT;'
} | sqlite3 "$DB"

echo "Database: $DB" >&2
