A basic example of using BEGIN-INSERT-COMMIT to write data (gathered by a shell script) to an SQLite database file.

# basic form of BEGIN-INSERT-COMMIT to write data to existing db
/usr/bin/sqlite3 db  < <( 
cat <<-'EOF'
BEGIN;
insert statement 1;
insert statement 2;
insert statement 3;
...
COMMIT;
.q
EOF
)


man sqlite3

/usr/bin/sqlite3
sqlite> .help
sqlite> .q


# file info database function
# usage: finfodb [optional: -t] [directory] [optional: filename]

function finfodb() {

   declare -a ar
   declare -i i timestamp
   declare checksum db filename searchdir

   declare dbdir="${HOME}/Library/FileInfoDB" 
   declare dbname="finfodb"
   declare sleep_before_write="0.5"
   declare -i block_insert_num=1000

   dbdir="${dbdir%/}"    # cut off trailing slash if necessary

   if [[ $# -eq 0 ]]; then 

      printf "%s\n%s\n" 'No arguments given!' 'Usage: finfodb [optional: -t] [directory] [optional: filename]'
      return 1

   elif [[ "${1}" == '-t' ]]; then

      if [[ ! -d "${2}" ]]; then printf "%s\n" "No directory: ${2}"; return 1; fi
      timestamp=1
      searchdir="${2}"
      filename="${3:-*}"

   else

      if [[ ! -d "${1}" ]]; then printf "%s\n" "No directory: ${1}"; return 1; fi
      timestamp=0
      searchdir="${1}"
      filename="${2:-*}"

   fi


   # store the SQLite databases in $dbdir
   /bin/mkdir -p "${dbdir}"

   if [[ $timestamp -eq 1 ]]; then

      # create name of SQLite database with time stamp
      db="${dbdir}/${dbname}--$(/bin/date -u +%Y-%m-%d--%H.%M.%S--%Z)"
      #db="${dbdir}/${dbname}--$(/bin/date +%Y-%m-%d--%H.%M.%S--%Z)"

   else

      db="${dbdir}/${dbname}"

   fi


   # create the SQLite database if necessary

   if [[ ! -f "${db}" ]]; then

/usr/bin/sqlite3 "${db}"  < <( 
/bin/cat <<-'EOF'
     PRAGMA encoding = "UTF-8";
     PRAGMA auto_vacuum = 1;
     create TABLE filecheck (num INTEGER PRIMARY KEY, filepath TEXT, checksum TEXT, timeEnter DATE);
     CREATE TRIGGER insert_filecheck_timeEnter AFTER INSERT ON filecheck
     BEGIN
      UPDATE filecheck SET timeEnter = DATETIME('NOW') WHERE rowid = new.rowid;
     END;
EOF
)

   fi

   i=0
   ar[${i}]='BEGIN;'$'\n'

   while read -d $'\0' filepath; do 

      # write $block_insert_num insert statements to ${db}
      # empty $ar and reset $i to 0
      if [[ $i -ge $block_insert_num ]]; then
      #if [[ "${#ar[@]}" -ge $block_insert_num ]]; then
         let i++
         ar[${i}]='COMMIT;'$'\n'
         let i++
         ar[${i}]='.q'$'\n'
         /bin/sleep $sleep_before_write
         # write insert statements stored in $ar to $db (see below)
         # delete a possible single leading space in every line (i.e. array item) using "${ar[@]/# /}"
         /usr/bin/sqlite3 "${db}" < <( printf "%s" "${ar[@]/# /}" )
         #/usr/bin/sqlite3 "${db}" < <( for ((i=0; i < "${#ar[@]}"; i++)); do printf "%s" "${ar[$i]#"${ar[$i]%%[! ]*}"}"; done )
         #/usr/bin/sqlite3 "${db}" <<< "$(echo "${ar[@]}" | /usr/bin/sed 's/^ *//')"
         unset -v ar i
         declare -a ar
         declare -i i=0
         ar[${i}]='BEGIN;'$'\n'
         echo
      fi

      let i++

      filepath_cleaned="${filepath//[[:cntrl:]]/}"   # remove control characters such as \n, \r, ...

      # cf. http://codesnippets.joyent.com/posts/show/1395
      if [[ ${#filepath_cleaned} -lt 85 ]]; then
         printf "\r\e[0K\e[1;32m%s\e[0m  %s" "${i}" "${filepath_cleaned}"
      else
         printf "\r\e[0K\e[1;32m%s\e[0m  %s" "${i}" "${filepath_cleaned:0:40}.....${filepath_cleaned: -40}"
      fi

      checksum="$(/sbin/md5 -q "${filepath}" 2>/dev/null)"

      # sha512
      # sudo port install openssl
      # cf. http://trac.macports.org/wiki/InstallingMacPorts
      #checksum="$(/opt/local/bin/openssl dgst -sha512 2>/dev/null < "${filepath}")"

      # escape single quotes for SQLite; cf. http://sqlite.org/lang_expr.html
      filepath="${filepath//\'/''}" 

      # escape newline characters \n
      #filepath="${filepath//$'\n'/\\n}" 
    
      # remove control characters such as \n, \r, ...  
      #filepath="${filepath//[[:cntrl:]]/}"   

      # create insert statements
      ar[${i}]="insert into filecheck (filepath,checksum) values ('${filepath}', '${checksum}');"$'\n'

   done < <(/usr/bin/find -x "${searchdir}" \( -type f -or -type l \) -name "${filename}" -print0 2>/dev/null)

   echo

   let i++
   ar[${i}]='COMMIT;'$'\n'
   let i++
   ar[${i}]='.q'$'\n'

   /bin/sleep $sleep_before_write


   # write insert statements stored in $ar to $db
   # cf. Removing whitspace from variables in bash?, http://www.jlaforums.com/viewtopic.php?t=1427403
   #/usr/bin/sqlite3 "${db}" <<< "$(for ((i=0; i < "${#ar[@]}"; i++)); do printf "%s" "${ar[$i]#"${ar[$i]%%[! ]*}"}"; done)"
   #/usr/bin/sqlite3 "${db}" < <( for ((i=0; i < "${#ar[@]}"; i++)); do printf "%s" "${ar[$i]#"${ar[$i]%%[! ]*}"}"; done )

   # delete a possible single leading space in every line (i.e. array item) using "${ar[@]/# /}"
   /usr/bin/sqlite3 "${db}" < <( printf "%s" "${ar[@]/# /}" )

   # alternatives with sed to trim leading whitespace
   #/usr/bin/sqlite3 "${db}" <<< "$(echo "${ar[@]}" | /usr/bin/sed 's/^ *//')"
   #/usr/bin/sqlite3 "${db}"  < <(echo "${ar[@]}" | /usr/bin/sed 's/^ *//' )


   /usr/sbin/chown -R $(/usr/bin/logname):$(/usr/bin/logname) "${dbdir}"
   /bin/chmod -R 0700 "${dbdir}"

   return 0
}



finfodb
finfodb 1234
finfodb -t
finfodb /usr/bin
finfodb /usr/bin "*z*"
finfodb -t /usr/bin
finfodb -t /usr/bin "*z*"

finfodb ~/Library
finfodb /Library

open ~/Library/FileInfoDB

sqlite3 ~/Library/FileInfoDB/finfodb  ".dump"
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from sqlite_master"
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck";
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE checksum LIKE ''";
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE filepath LIKE '%sync%'";
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE filepath LIKE '%/_sync'";
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE filepath LIKE '%/ds%'";


sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE checksum LIKE ''";

sqlite3 ~/Library/FileInfoDB/finfodb  "delete from filecheck WHERE checksum LIKE ''";

sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE checksum LIKE ''";

sqlite3 ~/Library/FileInfoDB/finfodb "vacuum"

sqlite3 ~/Library/FileInfoDB/finfodb "pragma integrity_check"

Further information:

- man sqlite3
- SQLite Tutorial: Common Commands and Triggers
- Build an application using simple shell scripts and SQLite
- A Quick Guide to SQLite and Ruby
- SQLite: Available Documentation
- sqlite3: A command-line access program for SQLite databases
- Datatypes In SQLite Version 3
- The SQLite Query Optimizer Overview
- SQL As Understood By SQLite
- SQL As Understood By SQLite: BEGIN TRANSACTION
- SQL As Understood By SQLite: expression (LIKE operator)
- Atomic Commit In SQLite
- SQLite: PRAGMA command
- SQLite FAQ
- SQLite The Hammer
- SQLite ODBC Driver
- Actual ODBC Driver for Open Source Databases
- SQLite Download Page: Precompiled Binaries For Mac OS X