Here is a Library I wrote a few months ago after I read through Adam’s tutorial to get a feel for sqlite3.
THIS HAS NOT BEEN THOROUGHLY TESTED!!
PLEASE LOOK THIS OVER THOROUGHLY BEFORE USE
That said, hopefully this will give you a head start and something
you can modify to your needs.
Cheers,
Craig
--#################################################################################
--# #
--# SqliteClass.applescript #
--# #
--# author: Craig Williams #
--# created: 2008-07-17 #
--# #
--#################################################################################
--# #
--# This program is free software: you can redistribute it and/or modify #
--# it under the terms of the GNU General Public License as published by #
--# the Free Software Foundation, either version 3 of the License, or #
--# (at your option) any later version. #
--# #
--# 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, see <http://www.gnu.org/licenses/>. #
--# #
--#################################################################################
(*
folder is created in ~/Library/Application Support/database_name
@property = folder_name
@property = database_name
@function = create_db(table_name, column_names_array)
@function = sql_insert(table_name, the_values)
@function = sql_update(table_name, the_fields, the_values, search_field, search_value)
@function = sql_addColumn(table_name, col_name)
@function = sql_select(column_names_array, table_name, search_field, search_value)
@function = sql_select_all(column_names_array, table_name)
@function = sql_select_all_where(table_name, search_field, search_value)
@function = sql_delete(table_name, search_field, search_value)
@function = sql_delete_every_row(table_name)
@function = sql_delete_table(table_name)
*)
property folder_name : "NameOfDBFolder"
property database_name : "NameOfDB"
-- I create comma sep quoted string to enter into sqlite db
on returnCommaSepQuotedString(the_array)
set return_string to ""
if length of the_array > 1 then
repeat with i from 1 to count of the_array
set this_item to item i of the_array
set return_string to return_string & "'" & this_item & "', "
end repeat
return text 1 thru -3 of return_string as string
else
--return "'" & (item 1 of the_array) & "'"
return item 1 of the_array
end if
end returnCommaSepQuotedString
-- RETURN FILE PATH, HEAD, TAIL
on createFolderReturnFilePathHeadTail()
set support_folder to (path to application support from user domain)
tell application "Finder"
set folder_path to (support_folder & folder_name) as string
if not (exists folder folder_path) then
make folder at support_folder with properties {name:folder_name}
end if
end tell
set file_path to support_folder & folder_name & ":" & database_name & ".db" as string
set file_path to quoted form of POSIX path of file_path
set loc to space & file_path & space
set head to "sqlite3" & loc & quote
set tail to quote
return {file_path, head, tail}
end createFolderReturnFilePathHeadTail
-- CREATE DB
on create_db(table_name, column_names_array)
set column_names_string to my returnCommaSepQuotedString(column_names_array)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set sql_statement to "create table if not exists " & table_name & "(" & column_names_string & "); "
--display dialog head & sql_statement & tail as string
--return
do shell script head & sql_statement & tail
end create_db
-- INSERT INTO DB
on sql_insert(table_name, the_values)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set the_values to my returnCommaSepQuotedString(the_values)
set sql_statement to "insert into " & table_name & " values(" & the_values & "); "
do shell script head & sql_statement & tail
end sql_insert
-- UPDATE DB
on sql_update(table_name, the_fields, the_values, search_field, search_value)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
repeat with i from 1 to count of the_fields
set this_item to item i of the_fields
set sql_statement to ("UPDATE " & table_name & " set " & this_item & " = '" & item i of the_values & "' WHERE " & search_field & " = '" & search_value & "'; " as string)
log sql_statement
do shell script head & sql_statement & quote
end repeat
end sql_update
-- ADD COLUMN
on sql_addColumn(table_name, col_name)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set sql_statement to ("ALTER table " & table_name & " add " & col_name & "; " as string)
do shell script head & sql_statement & quote
end sql_addColumn
-- SELECT
on sql_select(column_names_array, table_name, search_field, search_value)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set column_names_string to my returnCommaSepQuotedString(column_names_array)
set sql_statement to ("SELECT " & column_names_string & " FROM " & table_name & " WHERE " & search_field & " = " & search_value & "; " as string)
set sql_execute to (do shell script head & sql_statement & quote)
set sel_list to my tidStuff(return, sql_execute)
--set sel_list to my tidStuff(return, sql_execute)
--log sel_list
return my sel_list
end sql_select
-- SELECT ALL
on sql_select_all(table_name)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set sql_statement to ("SELECT * FROM " & table_name & " ; " as string)
set sql_execute to (do shell script head & sql_statement & quote)
return my tidStuff(return, sql_execute)
end sql_select_all
-- SELECT ALL WHERE
on sql_select_all_where(table_name, search_field, search_value)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set sql_statement to ("SELECT * FROM " & table_name & " WHERE " & search_field & " = " & search_value & " ; " as string)
set sql_execute to (do shell script head & sql_statement & quote)
return my tidStuff(return, sql_execute)
end sql_select_all_where
-- DELETE ONE ROW
on sql_delete(table_name, search_field, search_value)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set sql_statement to ("DELETE FROM " & table_name & " WHERE " & search_field & " = " & search_value & " ; " as string)
log sql_statement
do shell script head & sql_statement & quote
end sql_delete
-- DELETE EVERY ROW
on sql_delete_every_row(table_name)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set sql_statement to ("DELETE * FROM " & table_name & "; " as string)
do shell script head & sql_statement & quote
end sql_delete_every_row
-- DELETE TABLE
on sql_delete_table(table_name)
set {file_path, head, tail} to my createFolderReturnFilePathHeadTail()
set sql_statement to ("DELETE " & table_name & "; " as string)
do shell script head & sql_statement & quote
end sql_delete_table
-- TURN STRING INTO LIST
on tidStuff(paramHere, textHere)
set OLDtid to AppleScript's text item delimiters
set AppleScript's text item delimiters to paramHere
set theItems to text items of textHere
set AppleScript's text item delimiters to OLDtid
return theItems
end tidStuff
but im getting a error “SQL error: no such table: mods”.