#!/bin/bash
#< Script to load my notes files into my personal MySQL notes database

AWK="/usr/bin/awk"
RM="/bin/rm"
ECHO="/bin/echo"
EGREP="/usr/bin/egrep"
FIND="/usr/bin/find"
MYSQL="/usr/local/mysql/bin/mysql"
SED="/usr/bin/sed"
XARGS="/usr/bin/xargs"

DB_NAME="notesdb"
DB_USER="notesuser"
DB_PASS="password"
NOTES_DIR="/Users/kevin/notes"
SQL_OUT="./tmp.sql"

function generate_file_list {
   FILE_LIST=$( ${FIND} ${NOTES_DIR} -type f -maxdepth 1 | ${XARGS} file | ${EGREP} 'script|text' | ${AWK} 'BEGIN {FS=":"} {print $1}' )
}

function generate_sql {
   {
      ${ECHO} "DROP TABLE notes_table;"
      ${ECHO} "CREATE TABLE notes_table ( id        int(100)        not null unique primary key auto_increment,"
      ${ECHO} "                           subject   varchar(128)    ,"
      ${ECHO} "                           text      text           );"
      for FILE in ${FILE_LIST}; do
         ${ECHO} "INSERT INTO notes_table VALUES( '', '${FILE##*/}', '"
         ${SED} 's!\\!\\\\!g' ${FILE} | ${SED} "s/\'/\\\'/g"
         ${ECHO} "');"
      done
   } > ${SQL_OUT}
}

function do_sql {
   ${MYSQL} -u ${DB_USER} --password=${DB_PASS} ${DB_NAME} < ${SQL_OUT}
}

generate_file_list
generate_sql
do_sql

${RM} -f ${SQL_OUT}

exit 0