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

UPDATED WED Aug 15/07

PLEASE NOTE: requires the following modules "active_record", "rbosa" and "hpricot" and my own Imageshack API to work.

USAGE:

You can include/require this as a module in your own scripts, or as I do, use it directly from irb in the Terminal. Documentation is provided within the script itself, but essentially I use it to get these things done:

With Aperture launched, select the pictures of your choice (one or multiples makes no diff), then entering the following inside irb results in:

1. Aperture.reveal is essentially "Reveal in Finder"

2. Aperture.paths returns the Unix paths to the locations of the pics.

3. Aperture.open opens the pictures with an external default app (usually Preview).

4. If you provide the script with Photoshop's path, then:

Aperture.edit will open the original file directly in Photoshop without creating a separate version inside Aperture.

5. Aperture.ids will give you an array of the unique ID asssigned by Aperture to each pic within the ZRKVERSION table. What this is for is up to you to find out.

6. NEW: Imageshack.us upload/backup

Aperture.upload will upload the pic(s) to Imageshack.us and return the direct link/URL of the pic.

Upon first launch, a new metadata field is created inside Aperture's metadata panel called "Imageshack URL" (if you don't see it, inside Aperture, Ctrl-D --> Others). the uploaded pic's URL will be stored inside this field automatically.

The API directly interrogates Aperture's SQLite DB. Who knows, could be a nice place to start from to building other stuff.

require 'rubygems'
require 'active_record'
require 'rbosa'
require 'hpricot'
require 'open3'
require 'beckett/imageshack'

# --- GLOBALS --- #

HOME = ENV['HOME']
PATH_TO_PHOTOSHOP = "/Applications/Adobe\\ Photoshop\\ CS3/Adobe\\ Photoshop\\ CS3.app/Contents/MacOS/Adobe\\ Photoshop\\ CS3"

Aperture = OSA.app('Aperture')

# --- CONNECT TO CURRENT LIBRARY --- #

class LibraryConnect

  def initialize
    findLibrary
    connect unless $PATH_TO_DB.nil?
  end

  protected

  def findLibrary
  
  aperture_plist = HOME + '/Library/Preferences/com.apple.Aperture.plist'
  plistdump = HOME + '/Library/Caches/' + 'plistdump.tmp'
  plutil_error = Open3.popen3("plutil -convert xml1 -o #{plistdump} #{aperture_plist}") { |stdin,stdout,stderr| stderr.read }

  if plutil_error == ""
      doc = Hpricot(File.open(plistdump)) ; %x( rm #{plistdump} )
      relative_lib_path = (doc/:key).select { |key| key.inner_text == 'LibraryPath' }[0].next_sibling.inner_text
      $PATH_TO_LIB = relative_lib_path =~ /^~/ ? relative_lib_path.gsub(/^~/, HOME) : relative_lib_path
      $PATH_TO_DB = "#{$PATH_TO_LIB}/Aperture.aplib/Library.apdb"
      puts "Current library --> " + $PATH_TO_LIB
  else
      $PATH_TO_DB = nil
      puts "Cannot find library. Goodbye."
  end

  end

  def connect
    ActiveRecord::Base.establish_connection(
      :adapter => "sqlite3",
      :dbfile  => $PATH_TO_DB
    )
  end

end

# --- LIBRARY CLASSES-SQL TABLES MAPPINGS --- #

class Pictures < ActiveRecord::Base         # Contains the majority of non-metadata info on a picture
  set_table_name "ZRKVERSION"
  set_primary_key "ZUUID"
end

class Projects < ActiveRecord::Base         # Every pic only belongs to one project
  set_table_name "ZRKFOLDER"
  set_primary_key "ZUUID"
end

class Masters < ActiveRecord::Base          # Info on master images
  set_table_name "ZRKMASTER"
  set_primary_key "Z_PK"
end

class Files < ActiveRecord::Base            # Info on the actual image file 
  set_table_name "ZRKFILE"
  set_primary_key "Z_PK"
end

class Metadata < ActiveRecord::Base         # Metadata information tagged to every picture in library
  set_table_name "ZRKSEARCHABLEPROPERTY"
  set_primary_key "Z_PK"
end

class Albums < ActiveRecord::Base           # All albums within library
  set_table_name "ZRKPERSISTENTALBUM"
  set_primary_key "Z_PK"
end

class AlbumVersions < ActiveRecord::Base    # Foreign key that links a picture's Z_PK (from ZRKVERSION) to an
  set_table_name "Z_11VERSIONS"             # album's Z_PK in ZRKPERSISTENTALBUM
end

class Properties < ActiveRecord::Base       # Metadata categories
  set_table_name "ZRKPROPERTYIDENTIFIER"
  set_primary_key "Z_PK"
end

class Picture                               # Custom class that contains all relevant info on a picture

attr_reader :id, :project_id, :master_id, :file_id, :version_id, :width, :height, :filesize, :name, :moddate

def initialize(pic_id)

  # Query ZRKVERSION
  
  @id = pic_id
  pic = Pictures.find(pic_id)
  @project_id = pic.ZPROJECTUUID
  @master_id = pic.ZMASTER
  @file_id = pic.ZFILE
  @version_id = pic.Z_PK

  # Query ZRKFILE

  file = Files.find(@file_id)
  @width = pic.ZPIXELWIDTH
  @height = pic.ZPIXELHEIGHT
  @filesize = file.ZFILESIZE
  @name = file.ZNAME
  @moddate = realtime(file.ZFILEMODIFICATIONDATE_before_type_cast)

end

def filepath
    path = $PATH_TO_LIB + '/' + self.projectPath + '/' + self.importGroup + '/' + self.folderName + '/' + self.filename
end

def nixpath
    self.filepath.gsub(/ /,'\ ')
end

def open
  `open #{self.nixpath}`
end

def edit
  if PATH_TO_PHOTOSHOP != ""
    `open -a #{$PATH_TO_PHOTOSHOP} #{self.nixpath}`
  else
    `open #{self.nixpath}`
  end
end

def reveal
    puts nixpath
    `open #{File.dirname(self.nixpath)}`
end

def shack_uri
  pic_meta = Metadata.find( :first, :conditions => { :ZVERSION => @version_id, :ZPROPERTYIDENTIFIER => $SHACKURLPROP } )
  unless pic_meta == nil
    pic_meta.ZPROPERTYSPECIFICSTRING
  else
    return 'Does not exist.'
  end
end

def storeURI(url)
records = Metadata.find( :all, :conditions => { :ZVERSION => @version_id, :ZPROPERTYIDENTIFIER => $SHACKURLPROP } )
if  records.empty? == true
    new_uri = Metadata.create(
        :Z_ENT => 13,
        :Z_OPT => 1,
        :ZPROPERTYSPECIFICNUMBER =>  nil,
        :ZPROPERTYSPECIFICSTRING => url,
        :ZVALUETYPE => 3,
        :ZVERSION => @version_id,
        :ZPROPERTYIDENTIFIER => $SHACKURLPROP )
  new_uri.Z_PK
else
  records.each { |entry| Metadata.destroy(entry.Z_PK) }
  self.storeURI(url)
end

end

def realtime(sqtime)
  t = Time.at(sqtime.to_i + 978307200)      # => # Derives from: Time.utc(2001,"jan",1,0,0,0).to_i
  Time.utc(t.year,t.month,t.day,t.hour,t.min)
end

protected

def projectPath
    Projects.find(@project_id).ZLIBRARYRELATIVEPATH
end

def importGroup
   Masters.find(@master_id).ZIMPORTGROUP
end

def folderName
   Masters.find(@master_id).ZNAME
end

def filename
  Files.find(@file_id).ZNAME
end

end

class Album

def initialize(zpk)
  @album = Albums.find(zpk)
end

def add_count(n=0)
  @album.update_attributes(   :ZDATELASTSAVEDINDATABASE => (Time.now.to_f + 978307200.0),
                              :ZMETADATACHANGEDATE => (Time.now.to_f + 978307200.0),
                              :ZVERSIONCOUNT => @album.ZVERSIONCOUNT + n,
                              :ZCREATEDATE => @album.ZCREATEDATE_before_type_cast.to_f )
end

end

# --- INTERACTING WITH APERTURE FROM IRB --- #
# All operations are performed recursively on image(s) within the array returned by Aperture

class OSA::Aperture::Application

def new_prop(fieldName)

Properties.create(  :Z_ENT => 12,
                    :Z_OPT => 1,
                    :ZPROPERTYKEY => fieldName,
                    :ZPROPERTYTYPE => 7)
                    
return Properties.find_by_ZPROPERTYKEY(fieldName).Z_PK

end

def ids     # Returns the IDs used to query the ZRKVERSION table
  self.selection.collect { |pic| pic.id2 }
end

def paths   # Returns the filepaths of selected images
  self.ids.collect { |id| Picture.new(id).filepath }
end

def reveal  # 'Reveal in Finder' selected images
  self.ids.each { |id| Picture.new(id).reveal }
end

def open    # Opens selected images in Preview
  self.ids.each { |id| Picture.new(id).open }
end

def edit    # Opens the original file in Photoshop CS3 (if path provided or default)
  self.ids.each { |id| Picture.new(id).edit }
end

def upload(reveal=false)  # Uploads the image to Imageshack and returns the direct link to the pic, if called with true, then opens it in browser after upload

  self.ids.each do |id|
    pic = Picture.new(id)
    pic_mirror = ShackMirror.new(pic.filepath)
    pic.storeURI(pic_mirror.url)
    `open #{pic_mirror.url}` if reveal
    puts pic_mirror.url
  end

end

def print(name)
  self.ids.each { |id| puts Picture.new(id).send(name) }
end

end

# --- INITIALIZE --- #

LibraryConnect.new

$SHACKURLPROP = Properties.find_by_ZPROPERTYKEY("Imageshack URL") == nil ? Aperture.new_prop("Imageshack URL") : Properties.find_by_ZPROPERTYKEY("Imageshack URL").Z_PK

var $sqlite = {
	storageService: [],
	mDBConn: [],
	
	_initService : function(file){
		var db = Components.classes["@mozilla.org/file/directory_service;1"].getService(Components.interfaces.nsIProperties).get("ProfD", Components.interfaces.nsIFile);
		db.append(file);
		this.storageService[file] = Components.classes["@mozilla.org/storage/service;1"].getService(Components.interfaces.mozIStorageService);
		this.mDBConn[file] = (this.storageService[file]).openDatabase(db);
			
	},
	
	select : function(file,sql,param){
		if (this.storageService[file]== undefined){
                    this._initService(file);
		}
		var ourTransaction = false;
		if ((this.mDBConn[file]).transactionInProgress){
			ourTransaction = true;
			(this.mDBConn[file]).beginTransactionAs((this.mDBConn[file]).TRANSACTION_DEFERRED);
		}
		var statement = (this.mDBConn[file]).createStatement(sql);
                if (param){
			for (var m=2, arg=null; arg=arguments[m]; m++) {
				statement.bindUTF8StringParameter(m-2, arg);
			}
		}
		try{
			var dataset = [];
			while (statement.executeStep()){
				var row = [];
				for(var i=0,k=statement.columnCount; i<k; i++){
					row[statement.getColumnName(i)] = statement.getUTF8String(i);
				}
				dataset.push(row);
			}
			// return dataset;	
		}
		finally {
			statement.reset();
		}
		if (ourTransaction){
			(this.mDBConn[file]).commitTransaction();
		}
        return dataset;	
	},
	
	
	cmd : function(file,sql,param){
		if (this.storageService[file] == undefined){
	                    this._initService(file);
		}
		var ourTransaction = false;
		if ((this.mDBConn[file]).transactionInProgress){
			ourTransaction = true;
			(this.mDBConn[file]).beginTransactionAs((this.mDBConn[file]).TRANSACTION_DEFERRED);
		}
		var statement = (this.mDBConn[file]).createStatement(sql);
		if (param){
			for (var m=2, arg=null; arg=arguments[m]; m++) {
				statement.bindUTF8StringParameter(m-2, arg);
			}
		}
		try{
			statement.execute();
		}
		finally {
			statement.reset();
		}
		if (ourTransaction){
			(this.mDBConn[file]).commitTransaction();
		}
	}	

}

Usage:

<script language="javascript">
//some variables :
//assuming db file is in user's profile directory:
var myDBFile = 'mydb.sqlite';

// some example SQL queries:
var myCreateDBQuery = 'CREATE TABLE IF NOT EXISTS mybooks_tbl (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT);';

var myInsertQuery = 'INSERT INTO mybooks_tbl(title) VALUES("book title1");';
var myInsertQueryParameterized = 'INSERT INTO mybooks_tbl(title) VALUES(?1);';

var mySelectQuery = 'SELECT id,title FROM mybooks_tbl';
var mySelectQueryParameterized = 'SELECT id,title FROM mybooks_tbl WHERE id = ?1 AND title = ?2';



// For anything other than SELECT statement, use $sqlite.cmd() :
 
// creating a DB:
function test_createDB(){
	$sqlite.cmd(myDBFile,myCreateDBQuery);
}

// simple add record:
function test_addRecord(){
	$sqlite.cmd(myDBFile,myInsertQuery);
}

// parameterized add record, add parameters as much as you want:	
function test_addRecordParameterized(){
	// for example, adding 3 records:
	for(var i = 1 ; i < 4; i++){
		$sqlite.cmd(myDBFile,myInsertQueryParameterized,'book title'+i+'');
	}
}

// for SELECT, use $sqlite.select() :

// simple select:
function test_Select(){
	var myArray1 = $sqlite.select(myDBFile,mySelectQuery);
	// Now you can loop through the array:
	for(var j=0;j<myArray1.length;j++){
		// change this as you wish:
		alert(myArray1[j]['title']);
	}
}

// select with bound parameters, add parameters as much as you want:
function test_SelectParameterized(){
	var myArray1 = $sqlite.select(myDBFile,mySelectQueryParameterized,'1','book title1');
	// Now you can loop through the array:
	for(var j=0;j<myArray1.length;j++){
		// change this as you wish:
		alert(myArray1[j]['title']);
	}
}
</script>
<a href="#" onclick="test_createDB();">Create DB</a> | 
<a href="#" onclick="test_addRecord()">Simple Add Record</a> | 
<a href="#" onclick="test_addRecordParameterized()">Parameterized Add Record</a> | 
<a href="#" onclick="test_Select()">Simple Select</a> | 
<a href="#" onclick="test_SelectParameterized()">Parameterized Select</a> |