SQlite Version (for history) - dossier - console collection manager DIR Log DIR Files DIR Refs DIR Tags DIR README DIR LICENSE --- DIR commit 67938e405b6efa96c2815577e70c7ec6d367fcab DIR parent 9720541044836a4a509a41f038fcd22f64ec1116 HTML Author: Solene Rapenne <solene@perso.pw> Date: Sun, 15 Jul 2018 01:42:51 +0200 SQlite Version (for history) Diffstat: A cdb_sqlite | 142 +++++++++++++++++++++++++++++++ 1 file changed, 142 insertions(+), 0 deletions(-) --- DIR diff --git a/cdb_sqlite b/cdb_sqlite @@ -0,0 +1,142 @@ +#!/bin/sh + +if [ ! -f "database.sqlite" ] +then + printf 'CREATE TABLE collection ( id text primary key );' | \ + sqlite3 database.sqlite || exit 1 +fi +#mkdir -p "${REPO}" || exit 1 + +# displays the values of an identifier +# $1 identifier +show() { + printf "SELECT * FROM collection WHERE id = '%s';" \ + "$1" | \ + sqlite3 database.sqlite -line + + if [ "$?" -eq 0 ] + then + exit 0 + else + printf '%s is not in the library.\n' "$1" + exit 1 + fi +} + +# export the data in csv format "data","data","data" +# we assume it'll works with the dataset +export_csv() { + sqlite3 database.sqlite -header -csv 'select * from collection;' + exit $? +} + +# delete identifier from attributes +# $1 identifier +delete() { + printf "DELETE from collection where id = '%s';" "$1" | \ + sqlite3 database.sqlite + + if [ "$?" -ne 0 ] + then + printf "%s is not in the library!\n" "$1" + exit 1 + else + exit 0 + fi +} + +# displays list of identifiers +show_list() { + printf "SELECT id from collection;" | \ + sqlite3 database.sqlite + exit 0 +} + +# displays attributes used +show_attributes() { + sqlite3 database.sqlite 'PRAGMA table_info(collection)' | \ + cut -d '|' -f 2 + exit 0 +} + +# add/modify a value +# $@ identifier / attr / value / attr / value / .... +# shift to have attr / value again and again +add_value() { + ID="$1" + shift + + while [ "$#" -gt 1 ] + do + ATTRIBUTE="$1" + VALUE="$2" + shift 2 + + # add a column if it doesn't exist + if [ -z "$(sqlite3 database.sqlite 'PRAGMA table_info(collection);' | grep "|${ATTRIBUTE}|")" ] + then + printf 'ALTER TABLE collection ADD COLUMN %s text;' "${ATTRIBUTE}" | \ + sqlite3 database.sqlite + fi + printf "INSERT INTO collection('id') VALUES ('%s');" "$ID" | \ + sqlite3 database.sqlite >/dev/null 2>&1 + + printf "UPDATE collection SET %s = '%s' WHERE id = '%s';" \ + "$ATTRIBUTE" "$VALUE" "$ID" | \ + sqlite3 database.sqlite + done + exit 0 +} + +# returns identifiers having attribute=value +# $1 attribute +# $2 value +search_value() { + printf "SELECT id FROM collection WHERE %s = '%s';" \ + "$1" "$2" | \ + sqlite3 database.sqlite + exit 0 +} + +# returns list of identifiers in a attribute +# $1 attribute +list() { + printf "SELECT id,%s as attribute FROM collection WHERE attribute <> '';" \ + "$1" | \ + sqlite3 database.sqlite + exit 0 +} + +# displays usage +usage() { + printf '%s\n' \ + "cdb help" \ + "cdb export" \ + "cdb show [identifier]" \ + "cdb search [attribute [value]]" \ + "cdb identifier attribute value ... attribute value ..." + exit 0 +} + +if [ "$1" = "export" ] ; then export_csv ; fi +if [ "$1" = "rm" ] && [ "$#" -eq 2 ] ; then delete "$2" ; fi +if [ "$1" = "help" ] ; then usage ; fi + +# dealing with identifiers +if [ "$1" = "show" ] +then + if [ "$#" -eq 1 ]; then show_list ; fi + if [ "$#" -eq 2 ]; then show "$2" ; fi +fi + +# dealing with attributes +if [ "$1" = "search" ]; +then + if [ "$#" -eq 1 ]; then show_attributes ; fi + if [ "$#" -eq 2 ]; then list "$2" ; fi + if [ "$#" -eq 3 ]; then search_value "$2" "$3" ; fi +fi + +if [ "$#" -ge 3 ]; then add_value "$@" ; fi + +usage