#!/bin/sh
#!/usr/xpg4/bin/sh
# better to use xpg4-sh in Solaris
# http://www.gentei.org/~yuuji/software/#visq3
# $HGid: visq3,v 692:1690642d8967 2015-05-30 15:10 +0900 yuuji $

VI=${EDITOR:-vi}
SQ3=${SQLITE3:-sqlite3}
myname=`basename $0`
[ -x /usr/xpg4/bin/sh ] && xxxxx=.XXXXX

listtable() {
  $SQ3 $1 "select name from sqlite_master where type='table';" \
      | while read tb; do
	  sql=`$SQ3 $1 "select sql from sqlite_master where name=\"$tb\";"`
	  sql=`echo $sql`
	  recs="`$SQ3 $1 \"select count(*) from '$tb';\"`"
	  printf "%5d recs: %s\n" "$recs" "${sql#CREATE TABLE }"
	done
}

while true; do
  case $1 in
    -c*)	opt="$opt${opt:+ }-csv" ;;
    -s?)	opt="$opt${opt:+ }-separator ${1#-s}" ;;
    -s)		opt="$opt${opt:+ }-separator $2"; shift ;;
    *)		break ;;
  esac
  shift
done

if [ x"$1" = x"" ]; then
  cat<<_EOF_
Usage: $myname [Options] Sqlite3db Table
Options:
	-c	Add '-csv' option to $SQ3(CSV mode)
	-s SEP	Set field separator to 'SEP'

ABOLUTELY NO WARRANTY for the result of using this software.
Use with backup of database file.
_EOF_
  exit 0
elif [ x"$2" = x"" ]; then
  listtable $1
  exit 0
fi

tmpdir=`mktemp -d -t visq3$xxxxx` || exit 1
finalize() {
  rm -rf $tmpdir
  exit $rc
}
trap finalize INT TERM HUP
# Start to edit
db=$1
bak=${db}~
tbl=$2
dsvfile=$tmpdir/visq3-$$.txt
dsvbak=$tmpdir/visq3-$$.bak
echo ".output $dsvfile
select * from $tbl;" | $SQ3 $opt $db
cp $dsvfile $dsvbak
test -t 1 || VI=cat
count0="`$SQ3 $db \"select count(*) from '$tbl';\"`"
if ! $VI $dsvfile; then
  echo Abort. 1>&2
  rc=1
elif cmp $dsvfile $dsvbak >/dev/null 2>&1; then
  rc=0
  test -t 1 && echo "No difference." 1>&2
else
  cp -p "$db" "$bak"
  dsvlines=`wc -l < $dsvfile`
  echo "delete from $tbl;
.import $dsvfile $tbl" | $SQ3 $opt $db
  rc=$?
  count1="`$SQ3 $db \"select count(*) from '$tbl';\"`"
  if [ $dsvlines -ne $count1 ]; then
    echo "Number of record differs($dsvlines vs. $count1)." 1>&2
    echo "Seemed to fail .import" 1>&2
    echo "Cancel updating of $db" 1>&2
    mv $bak $db
  else
    echo "$count0 -> $count1" 1>&2
    cmp $db $bak >/dev/null 2>&1 && rm "$bak"
  fi
fi

finalize