Is it possible to write scripts that contain SQLite dot commands ( vis. .read file.sql; .separator ,; .import file.csv; )?
I'm building and repeatedly rebuilding an SQLite database and need to type in roughly twenty four dot command statements every time I rebuild the database. It would be really nice if I could put those commands in a script and have SQLite read them.
Is there a way to put dot commands ( not SQL statements ) into a script and have SQLite run them?
I'm on Mac OS X using bash.
I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately.
The gist of it? Put your commands in a text file and direct it to SQLite using the input file descriptor, or just script everything in a bash script.
sqlite3 database.db < commands.txt
#!/bin/bash -- sqlite3 -batch $1 <<"EOF" CREATE TABLE log_entry ( <snip> ); .separator "\t" .import logfile.log log_entry EOF And then on the command line:
import.sh database.db As an alternative, which I consider simpler, you can just use the .read command. From the command line of your OS (terminal in Mac OS X), type:
sqlite3 yourdatabase.db ".read script_full_of_dot_commands" It will start sqlite3, use the mentioned database, perform all of the commands in script_full_of_dot_commands, and leave.
For instance, let's assume you have the following files:
script_full_of_dot_commands: A file with both .commandsand SQL commands:
.read file.sql .separator "," .import file.csv t .headers on .separator "|" SELECT name, a FROM t WHERE a < 2 ; SELECT count(*) FROM t2; file.sql: A file with just SQL commands.
DROP TABLE IF EXISTS t; DROP TABLE IF EXISTS t2; CREATE TABLE t (a integer PRIMARY KEY, name varchar); CREATE TABLE t2 (b integer PRIMARY KEY); file.csv: a data file to fill a table
1,One 2,Two 3,Three 4,Four The result of performing
sqlite3 yourdatabase.db ".read script_full_of_dot_commands" is
name|a One|1 count(*) 0 Check also: Running a Sqlite3 Script from Command Line from StackOverflow.
One more scriptable way to provide SQLite dot commands:
sqlite3 db.sqlite < <(echo -e ".read file.sql \n.separator ',' \n.import file.csv") You can also mix in SQL commands. Just be sure that the echo -e "…" command prints things the way you would write them into the SQLite command-line client.
Advantages:
<<"EOF" … EOF HERE document syntax. It works, but I always find multi-line shell commands messy to work with when copy&pasting into a terminal.Explanations:
<(…) to provide a file input without needing a file on disk.echo -e "\n".After being frustrated with this for over an hour today, I found that individually quoting multiple arguments also works:
sqlite3 -echo -bail exifdata.db '.trace ./exifdata.log' '.read ./exifdata.sql'
As opposed to grouping the dot commands into a single set of quotes:
sqlite3 -echo -bail exifdata.db '.trace ./exifdata.log .read ./exifdata.sql'
Which just responded with the somewhat unhelpful error Usage: .trace FILENAME
In fact, I am delighted to report that this also works:
insertrow() { SQL="INSERT INTO exifdata (md5, path, bytes, dtcreated, exifhash, exifdata)" SQL="$SQL VALUES (:md5, :path, :bytes, :dtcreated, :exifhash, :exifdata)" sqlite3 $GLOBAL_OPTS "$CMD_OPTS" \ ".param init" \ ".param set :md5 $1" \ ".param set :path '$2'" \ ".param set :bytes $3" \ ".param set :dtcreated '$4'" \ ".param set :exifhash $5" \ ".param set :exifdata '$6'" \ "$SQL" \ ".param clear" } 
