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

Runs the SQL code located in a file you pass as a parameter against the DSN you specify as a second parameter.

What does it need?:
-----------------------------------
* isql (if you're reading this, you should have this)
* A pager (less/more/most/etc, you should have this)
* bash (you can try it in your shell, but no guarantees)
* tr (you should have this)
* sed (you should have this)

The code:
-----------------------------------

#!/bin/bash
# {{{ sql $1 $2 - Run a SQL script
function sql
{
      tr '\n' ' ' < "$1" | tr '\t' ' ' | isql "$2" Username Password | sed '1,8d' | sed 's/^.*SQL> //g' | $PAGER
}
# }}}

Parameters:
-----------------------------------

$1 = The file that contains SQL
$2 = The DSN you want to run the SQL against

What does it do?:
-----------------------------------
It chops off the extraneous "thing" that is spit out whenever you start iSQL, it looks like this:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

and then it pipes everything into your specified pager ($PAGER) so you can at least scroll around in the results instead of seeing an ASCII art wrapped mess of your results.

Example use:
-----------------------------------

username@host $> sql ~/sqlscripts/getallusers.sql DSNName

Notes:
-----------------------------------
For my purposes I use the same credentials for all my databases (so I don't forget them) which is why they are hardcoded in the function and not passed into the function.

By the way, if anyone has any time on there hands, could you please pick up the slack on the development of this program. It's about the only CLI tool (that I know of) that can work with TSQL on a MSSQL 2000 server. Or, if someone knows of something better, let me know!

Thanks:
-----------------------------------
Thanks everyone, and I hope others find this somewhat useful (although I know it's a far cry from perfect),

Chrelad

When I started using PostgreSQL, it seemed odd that when I created a table called Users with column names like userId, userPassword, etc. When I would select these items from the table, the column and table names would all be in lowercase. It's quite simple really, I wasn't using quotes :) Below is an example of how to maintain your case in PostgreSQL:

This will all come out lowercase

create table Users (
  userId integer unique not null,
  userFirstName varchar(50) not null,
  userLastName varchar(50) not null
);

Whereas this will come out as you expect it to

create table "Users" (
  "userId" integer unique not null,
  "userFirstName" varchar(50) not null,
  "userLastName" varchar(50) not null
);

-- This SQL code allows you to join a table to another table by finding columns that contain another column's information
-- (instead of where the columns are equal)

SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.col LIKE '%' + Table2.col + '%'

// produces some handy ALTER statements
// I think there's a way to this w/ mysqladmin but I couldn't find it.

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=MyISAM;') FROM information_schema.tables WHERE engine = 'InnoDB';

// Deletes the top 1 row from the table

set rowcount 1
DELETE FROM xxx WHERE xxx
set rowcount 0

// Reindex and rebuild statistics for each table in the database

EXEC sp_MSforeachtable "dbcc dbreindex('?')"
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Thanks to CodeProject for this code

Run this in the Query Analyzer (make sure the right database is selected first).

sp_change_users_login 'auto_fix', 'UserName'

This SQL query will reset an identity seed to the specified value.

Thanks to this site for the answer:

http://www.thescripts.com/forum/thread657059.html

I know this works for MSSQL 2000 at least :)

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Thanks to this website for the information:

http://www.databasejournal.com/features/mssql/article.php/1442021

-- The syntax for pulling a certain part of a DATETIME is:
--
-- CONVERT(date_type[(length)],expression[,style])
--
-- The available styles are as follows:
--
-- NULL  Jun 24 2001 9:48PM
-- 1     06/24/01
-- 101   06/24/2001
-- 2     01.06.24
-- 104   24.06.2001
-- 108   21:48:00
-- 112   20010624
-- 121   2001-06-24 21:48:00.000

-- Some example usage:
SELECT CONVERT(DATETIME,GETDATE(),112) as date
-- Will output in YYYYMMDD format
SELECT CONVERT(DATETIME,client.birthday,101) as birthday
-- Will output in MM/DD/YYYY format