#!/bin/bash
# NAME:
# backcd-sql
# DESCRIPTION:
# an attempt to semi-automate insertion of contents of backup CDs to 
# a postgresql database
# AUTHOR:
# copyright (C) 2004 Terry Vessels
# LICENSE:
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License, version 2,
#    as published by the Free Software Foundation.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
# END OF LICENSE.
#########################################################################

# to make a list of the contents of a single backup cd
# edit these if needed
cddev=/dev/cdrom
mnt_dir=/cdrom
lst_dir=~/backup-cds/singles
# db=name of database
db=backup_cds
# tb=name of table
# table format:
# title (text) - title/label of CD
# directory (text) - path to file on CD
# filename (text) - filename on CD
# filesize (bigint) - size in bytes of file
# filedate (date) - YYYY-MM-DD of file
tb=cds
# dbuser=name of postgres admin
dbuser=postgres
# because some slobbering damned idiot BROKE ls --full-time
# after ls version 4.1
lver=$(ls --version | head -1 | awk '{print $3}')
lmaj=${lver%%\.*}
lmin=$(echo ${lver} | awk 'BEGIN{FS="."}{print $2}')
# end ls stuff
if [ -z $1 ]
then
	echo "Usage: $0 cd_name"
	exit
else
	cd_name="$1"
fi
mkdir -p ${lst_dir}
if [ -n "$(mount | grep ${mnt_dir})" ]
then
	umount ${mnt_dir}
fi
read -p "Put CD ${cd_name} in and press Enter" U_IN
mount ${mnt_dir} && \
cd ${mnt_dir}
if [ "$(pwd)" != "${mnt_dir}" ]
then
	echo trouble mounting ${cddev} at ${mnt_dir}
	exit
else
	cddate=$(ls --color=none -ld --time-style=+%Y-%m-%d ${mnt_dir}|awk '{print $6}')
	echo ${cddate}
	read -p "use ${cddate}-${cd_name} for ${cd_name} ? [y]/n" U_IN
	if [ "${U_IN}" != "n" ]
	then
		cd_name=${cddate}-${cd_name}
	fi
#	read -p "${cd_name} OK?" U_IN
fi
if [ -f ${lst_dir}/${cd_name} ]
then
#	read -p "remove the old ${lst_dir}/${cd_name} ?[y]/n" U_IN
	rm ${lst_dir}/${cd_name}
fi
if [ -f ${lst_dir}/${cd_name}.sql ]
then
#	read -p "remove the old ${lst_dir}/${cd_name}.sql ?[y]/n" U_IN
	rm ${lst_dir}/${cd_name}.sql
fi
if [ ${lmaj} == 4 ] && [ ${lmin} == 1 ]
then
	ls --color=none -1lR --full-time >> ${lst_dir}/${cd_name}
else
	ls --color=none -1lR --time-style=+%a\ %b\ %d\ %T\ %Y >> ${lst_dir}/${cd_name}
fi
ls -l ${lst_dir}/${cd_name}
cd ..
#read -p "umount the CD? [y]/n" U_IN
#if [ "${U_IN}" == "n" ]
#then
#	exit
#fi
sync
umount ${mnt_dir}
awk -v title=${cd_name} 'BEGIN \
{
	dname=""
	fname=""
}
{
	first=substr($0, 1, 1)
	if (first==".") {
		dname=substr($0, 1, (length($0)-1))
	#} else {
	#	dname=""
	}
	if (first == "-" && NF>=11) {
		fname=substr($0, index($0,$11))
		year=$10
		day=$8
		mon=$7
		size=$5
	} else {
		fname=""
	}
	if (length(dname) != 0 && length(fname) != 0) {
		print title"	"dname"/	"fname"	"size"	"year" "mon" "day
	}
}' ${lst_dir}/${cd_name} > ${lst_dir}/${cd_name}.sql
psql -d ${db} -U ${dbuser} -c "
	copy ${tb}
		from '${lst_dir}/${cd_name}.sql'
		using delimiters '	';
"