do an "lxd sql" import of missing snapshots after an incremental zfs sync

update_container_snapshot_lxd_sql_records.bash

#!/bin/bash -e

contname="$1"

lxdpool='lxd2' # using a separate lxd storage pooll for imported containers

message() {
    echo "$*" >&2
}

debug() {
    # echo "[dbg] $*" >&2

    # prepends every output line with '[dbg] '
    echo "$*" | while IFS= read -r L; do
        # echo "[dbg] $L" >&2
        message "[dbg] $L"
    done
}



lxd_sql() {
    debug "$@"
    lxd sql global "$@"
}


# takes a query with a single "select <field_name>"
lxd_get_last_value() {
    # // in debug mode:
    # lxd sql global "$@" | grep -v '^[+]' | tail -n +2 | tr -d '| ' | tail -1

    # // production mode: )
    lxd_sql "$@" | grep -v '^[+]' | tail -n +2 | tr -d '| ' | tail -1
}


if [ "x$contname" = 'x' ]; then

    echo "
stop the container, do a zfs send|recv, restart it 
and then pass the container name here as a 1st argument ) 
" >&2 ;

    exit 2

else

    contid=$( lxd_get_last_value "select id from instances where name='$contname'" )
    volid=$( lxd_get_last_value "select id from storage_volumes where name='$contname'" )

    debug "$contname -> id:$contid; vol-id: $volid"

    # get unfiled snapshots
    debug "assuming the 'import' lxd pool '$lxdpool' .."
    comm -13 <( lxd sql global "select name from instances_snapshots where instance_id=$contid" | grep -v '^[+]' | tail -n +2 | tr -d '| ' | sort ) \
             <( zfs list -o name -r -t all  pool1/$lxdpool/containers/$contname | grep '@snapshot-' | sed 's/[^@]*@snapshot-//' | sort ) |
    while read lxsnapname; do
        # these are the names w/o 'snapshot-' prefix

        message "--- $contname/$lxsnapname ---"

        # a simple stub ...
        dateadded=$(date +%s)

        lxd_sql "
INSERT INTO instances_snapshots( instance_id, name, creation_date, stateful, description, expiry_date) 
VALUES(${contid}, '${lxsnapname}', ${dateadded}, 0, '', -62135596800)
"
        # -- VALUES(${contid}, ${lxsnapname}, strftime('%s'), 0, '', -62135596800)


        snapid=$( lxd_get_last_value "select id from instances_snapshots where instance_id=${contid} and name='${lxsnapname}'" )
        debug "$contname/$lxsnapname -> id:$contid; snap-id: $snapid"

        last_snap_id=$( lxd_get_last_value "select id from storage_volumes_snapshots order by id asc" )
        free_snap_id=$(( ${last_snap_id-1} + 1 ))

        lxd_sql "
INSERT INTO storage_volumes_snapshots( id, storage_volume_id, name, description, expiry_date) 
VALUES(${free_snap_id}, ${volid}, '${lxsnapname}', '', -62135596800)
"

        # # test mode
        # snapid=0
        if [ "x${snapid}" = 'x' ]; then
            debug "$contname/$lxsnapname -> id:$contid has no snap id ((("
        else
            lxd_sql "
INSERT INTO instances_snapshots_config( instance_snapshot_id, key, value)
SELECT ${snapid}, key, value from instances_config WHERE instance_id=${contid};
"
        fi

    done

fi