359 lines
9.2 KiB
Bash
Executable File
359 lines
9.2 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
set -ueo pipefail
|
|
[ "${DEBUG:-0}" = "1" ] && set -x
|
|
: <<'DOCS'
|
|
=head1 NAME
|
|
|
|
tags is a tool for keeping file tags in a SQLite database.
|
|
|
|
=head1 SYNOPSIS
|
|
|
|
tag [OPTIONS] <init|import|add|list|bytag|listtags>
|
|
|
|
=head1 OPTIONS
|
|
|
|
=over 4
|
|
|
|
=item B<init>
|
|
|
|
Create an empty database.
|
|
|
|
=item B<import> I<filename>
|
|
|
|
Import filename, can be single value or multiple values.
|
|
|
|
=item B<autoimport> I<regexp> I<path>
|
|
|
|
Import all files in I<path>. Can be filtered using custom I<regexp>, or one of the regular expression presets (currently only "video").
|
|
|
|
=item B<add> I<type> I<value>
|
|
|
|
Adds a new item of the specified I<type>: C<tag>, C<file>, C<hash>.
|
|
|
|
=item B<list> I<type> I<[--bytag|--byfile]>
|
|
|
|
List C<tags>, C<files>, C<hashes> (these are all table names, internally).
|
|
|
|
=item B<tag> I<[-i|--interactive]> I<--file|--id> I<tag>
|
|
|
|
Tags I<filename> with one or more I<tag>.
|
|
|
|
=back
|
|
|
|
=head1 TODO
|
|
|
|
* adjust sqlite_insert_multiple to allow defining columns per insert, currently it's hardcoded to 2
|
|
* merge sqlite_insert_single and sqlite_insert_multiple probably as result of the above
|
|
* listtags doesn't work with full path
|
|
* listtags doesn't work after tagging a file
|
|
|
|
=head1 LICENSE AND COPYRIGHT
|
|
|
|
=cut
|
|
DOCS
|
|
|
|
SCRIPT_DIR=$(dirname "$(readlink "$0")")
|
|
SCRIPT_NAME=$(basename "$0")
|
|
|
|
|
|
DB_FILE="${PWD}/tags.db"
|
|
[ "${1:-}" = "--db" ] && DB_FILE=$(readlink -f "${2/#~/$HOME}") && shift 2
|
|
|
|
|
|
DB_SCHEMA="${SCRIPT_DIR}/database.sql"
|
|
declare -A FILTERS
|
|
FILTERS[video]="avi|flv|mkv|mov|mp4|mpg|ogv|webm|wmv"
|
|
|
|
|
|
fail() {
|
|
echo "$1" >&2;
|
|
exit 1
|
|
}
|
|
|
|
log() {
|
|
echo "$1"
|
|
}
|
|
|
|
init() {
|
|
# DB_NAME
|
|
[ -f "$1" ] && fail "Database file \"$1\" already exists. Aborting."
|
|
sqlite3 "$1" < "$DB_SCHEMA"
|
|
log "Empty database file \"$1\" created."
|
|
}
|
|
|
|
enumerate_positional_parameters() {
|
|
log "Positional parameters in $FUNCNAME:"
|
|
CNT=0
|
|
for VALUE in "$@"; do
|
|
let CNT++ || true
|
|
echo "${CNT}: $VALUE"
|
|
done
|
|
}
|
|
|
|
listtags() {
|
|
# $FILENAME $LIMIT=ALL
|
|
[ -z "$1" ] && fail "No filename supplied."
|
|
FILENAME="$1"
|
|
shift
|
|
|
|
# ! file_exists_in_db "$FILENAME" && fail "File '$FILENAME' does not exist in database."
|
|
|
|
LIMIT="${1:-0}"
|
|
ADDITIONAL_QUERY=""
|
|
[ $LIMIT -gt 0 ] && ADDITIONAL_QUERY="LIMIT $LIMIT"
|
|
|
|
sqlite_query \
|
|
"SELECT filename, label from files \
|
|
INNER JOIN tags_ties ON tags_ties.fid = files.id \
|
|
INNER JOIN tags ON tags.id = tags_ties.tid \
|
|
WHERE filename LIKE \"%$FILENAME%\"\
|
|
$ADDITIONAL_QUERY"
|
|
}
|
|
|
|
add() {
|
|
# $TYPE(tag|path|hash) $VALUE1..$VALUEN
|
|
[ "$1" = "tag" ] && shift && add_tag "$@" && exit 0
|
|
[ "$1" = "path" ] && shift && add_path "$@" && exit 0
|
|
[ "$1" = "hash" ] && shift && add_hash "$@" && exit 0
|
|
[ "$1" = "file" ] && fail "Use \"$SCRIPT_NAME add path\" instead."
|
|
pod2usage "$0"
|
|
exit 1
|
|
}
|
|
|
|
sqlite_query() {
|
|
# $QUERY
|
|
dbfile_exists || fail "Database file '$DB_FILE' does not exist. Use '$SCRIPT_NAME init' to create default one, or specify filename with '--db'."
|
|
sqlite3 "$DB_FILE" "$1"
|
|
}
|
|
|
|
sqlite_lastrows() {
|
|
# $TABLE $LIMIT
|
|
sqlite_query "SELECT * FROM \"$1\" ORDER BY id DESC LIMIT ${2}"
|
|
}
|
|
|
|
sqlite_insert_single() {
|
|
# $TABLE $COLUMN $VALUES
|
|
# $VALUES can be comma-delimited
|
|
[ -z "$1" ] && fail "No table specified."
|
|
TABLE="$1"
|
|
[ -z "$2" ] && fail "No column(s) supplied."
|
|
COLUMN="$2"
|
|
[ -z "$3" ] && fail "No column values supplied."
|
|
shift 2
|
|
VALUES="(\"$1\")"
|
|
if [[ ! -z "${2:-}" ]]; then
|
|
while true; do
|
|
shift
|
|
[ -z "${1:-}" ] && break
|
|
VALUES+=",(\"$1\")"
|
|
done
|
|
fi
|
|
QUERY="PRAGMA foreign_keys=ON;"
|
|
QUERY+="INSERT INTO $TABLE ($COLUMN) VALUES ${VALUES} RETURNING *;"
|
|
sqlite_query "$QUERY"
|
|
}
|
|
|
|
sqlite_insert_multi() {
|
|
# $TABLE $COLUMN $VALUE1 $VALUE2
|
|
[ -z "${1:-}" ] && fail "No table specified."
|
|
TABLE="$1"
|
|
[ -z "${2:-}" ] && { pod2usage "$0"; exit 1 ; }
|
|
COLUMN="$2"
|
|
[[ -z "${3:-}" || -z "${4:-}" ]] && { pod2usage "$0"; exit 1 ; }
|
|
shift 2
|
|
VALUES="($1,$2)"
|
|
if [[ ! -z "${3:-}" && ! -z "${4:-}" ]]; then
|
|
while true; do
|
|
shift 2
|
|
[[ -z "${1:-}" && -z "${2:-}" ]] && break
|
|
VALUES+=",($1,$2)"
|
|
done
|
|
fi
|
|
QUERY="PRAGMA foreign_keys=ON;"
|
|
QUERY+="INSERT INTO $TABLE ($COLUMN) VALUES ${VALUES} RETURNING *;"
|
|
sqlite_query "$QUERY"
|
|
}
|
|
|
|
add_tag() {
|
|
# $TAG1...$TAGN
|
|
TABLE="tags"
|
|
COLUMN="label"
|
|
sqlite_insert_single "$TABLE" "$COLUMN" "$@"
|
|
}
|
|
|
|
add_path() {
|
|
# $PATH1..$PATHN
|
|
TABLE="files"
|
|
COLUMN="filename,path"
|
|
FILES=()
|
|
for FILE in "$@"; do
|
|
[ ! -f "$FILE" ] && fail "File '$FILE' does not exist in the specified path."
|
|
local FILENAME=$(basename "$FILE")
|
|
local FILEPATH=$(readlink -f "$(dirname "$FILE")")
|
|
file_exists_in_db "$FILE" && fail "File '$FILE' already exists in database."
|
|
FILES+=("\"$FILENAME\"")
|
|
FILES+=("\"$FILEPATH\"")
|
|
done
|
|
sqlite_insert_multi "$TABLE" "$COLUMN" "${FILES[@]}"
|
|
}
|
|
|
|
add_hash() {
|
|
# $FILE $HASH
|
|
local TABLE="hashes"
|
|
local COLUMN="fid,md5"
|
|
local FILENAME="${1:-}"
|
|
[ -z "$FILENAME" ] && fail "No file specified."
|
|
local FID=$(id_by_filename "$FILENAME")
|
|
[[ "$FID" -eq 0 ]] && fail "File \"$FILENAME\" does not exist in database."
|
|
sqlite_insert_multi "$TABLE" "$COLUMN" $FID "'$2'"
|
|
}
|
|
|
|
add_path_auto() {
|
|
# $REGEX $FOLDER
|
|
find "${2}" -type f -regextype posix-extended -iregex "$1" -exec "$0" --db "$DB_FILE" import "{}" +
|
|
}
|
|
|
|
tag_exists_in_db() {
|
|
# $TAGLABEL
|
|
TAG=${1:-}
|
|
RESULT=$(sqlite_query "SELECT id FROM tags WHERE label = \"$TAG\"")
|
|
if [[ -z "$RESULT" ]]; then
|
|
return 1
|
|
else
|
|
return 0
|
|
fi
|
|
}
|
|
|
|
file_exists_in_db() {
|
|
# $FILENAME
|
|
local RESULT=$(id_by_filename "$1")
|
|
if [[ "$RESULT" -eq 0 ]]; then
|
|
return 1
|
|
else
|
|
return 0
|
|
fi
|
|
}
|
|
|
|
id_by_filename() {
|
|
# FILENAME
|
|
local FILENAME=$(basename "${1:-}")
|
|
local FILEPATH=$(readlink -f "$(dirname "${1:-}")")
|
|
local RESULT=0
|
|
RESULT=$(sqlite_query "SELECT id FROM files WHERE filename = \"$FILENAME\" AND path = \"$FILEPATH\"")
|
|
echo $RESULT
|
|
}
|
|
|
|
dbfile_exists() {
|
|
if [[ ! -f "$DB_FILE" ]]; then
|
|
return 1
|
|
else
|
|
return 0
|
|
fi
|
|
}
|
|
|
|
main() {
|
|
|
|
[ -z "${1:-}" ] && { pod2usage "$0"; exit 1; }
|
|
|
|
if [[ "$1" = "init" ]]; then
|
|
init "${2:-$DB_FILE}"
|
|
exit 0
|
|
fi
|
|
|
|
[ ! -f "$DB_FILE" ] && fail "Database file \"$DB_FILE\" does not exist."
|
|
|
|
if [[ "$1" = "import" ]]; then
|
|
shift
|
|
add "path" "$@"
|
|
fi
|
|
|
|
if [[ "$1" = "autoimport" ]]; then
|
|
shift
|
|
if [[ "${1:-}" =~ "${!FILTERS[@]}" ]]; then
|
|
add_path_auto ".*(${FILTERS[$1]})" "$2"
|
|
else
|
|
add_path_auto "$1" "$2"
|
|
fi
|
|
fi
|
|
|
|
if [[ "$1" = "add" ]]; then
|
|
shift
|
|
add "$@"
|
|
fi
|
|
|
|
if [[ "$1" = "list" ]]; then
|
|
[ -z "${2:-}" ] && { pod2usage "$0"; exit 1 ; }
|
|
TABLE_NAME="$2"
|
|
sqlite_query "SELECT * FROM \"${TABLE_NAME}\""
|
|
exit 0
|
|
fi
|
|
|
|
if [[ "$1" = "bytag" ]]; then
|
|
[ -z "$2" ] && fail "No tag supplied."
|
|
TAG_NAME="$2"
|
|
sqlite_query "SELECT filename FROM files WHERE id = (SELECT id FROM tags_ties WHERE id = (SELECT id FROM tags WHERE label = \"${TAG_NAME}\"))"
|
|
exit 0
|
|
fi
|
|
|
|
if [[ "$1" = "tag" ]]; then
|
|
shift
|
|
if [[ "${1:-}" = "-i" || "${1:-}" = "--interactive" ]]; then
|
|
"$0" --db "$DB_FILE" tagfid \
|
|
$(sqlite_query "SELECT id,filename,path FROM files" | fzf | xargs -I{} echo '{}' | awk -F'|' '{print $1}') \
|
|
$(sqlite_query "SELECT label FROM tags" | fzf --multi | xargs -I{} echo '{}' | awk -F'|' '{print $1}')
|
|
exit 0
|
|
fi
|
|
[ -z "${1:-}" ] && fail "No filename supplied."
|
|
FILENAME="$1"
|
|
! file_exists_in_db "$FILENAME" && fail "File '$FILENAME' does not exist in database."
|
|
shift
|
|
[ -z "${1:-}" ] && fail "No tag supplied."
|
|
COUNTER=0
|
|
while true; do
|
|
LABEL="$1"
|
|
! tag_exists_in_db "$LABEL" && fail "Tag '$TAG' does not exist in database."
|
|
shift
|
|
local FID=$(id_by_filename "$FILENAME")
|
|
sqlite_query "INSERT INTO tags_ties (fid, tid) VALUES ($FID,(SELECT id FROM tags WHERE label = \"$LABEL\"))"
|
|
COUNTER=$((COUNTER++))
|
|
[ -z "${1:-}" ] && break
|
|
done
|
|
listtags "$FILENAME" "$COUNTER"
|
|
exit 0
|
|
fi
|
|
|
|
if [[ "$1" = "tagfid" ]]; then
|
|
shift
|
|
[ -z "${1:-}" ] && fail "No file ID supplied."
|
|
local FID="$1"
|
|
shift
|
|
[ -z "${1:-}" ] && fail "No tag supplied."
|
|
COUNTER=0
|
|
while true; do
|
|
LABEL="$1"
|
|
! tag_exists_in_db "$LABEL" && fail "Tag '$TAG' does not exist in database."
|
|
shift
|
|
sqlite_query "INSERT INTO tags_ties (fid, tid) VALUES ($FID,(SELECT id FROM tags WHERE label = \"$LABEL\"))"
|
|
COUNTER=$((COUNTER++))
|
|
[ -z "${1:-}" ] && break
|
|
done
|
|
exit 0
|
|
fi
|
|
|
|
if [[ "$1" = "listtags" ]]; then
|
|
[ -z "${2:-}" ] && { pod2usage "$0"; exit 1 ; }
|
|
listtags "$2"
|
|
exit 0
|
|
fi
|
|
|
|
if [[ "$1" = "listbyfile" ]]; then
|
|
sqlite_query "SELECT id,filename FROM files" |\
|
|
fzf --delimiter="|" --preview "sqlite3 $DB_FILE 'SELECT label FROM tags WHERE id IN (SELECT tid FROM tags_ties WHERE fid ={2})'"
|
|
fi
|
|
}
|
|
|
|
main "$@"
|
|
|
|
|
|
|