Scripting SQLite with dot commands

Question

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.

Answer 1

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.

First Method:

sqlite3 database.db < commands.txt

Second Method:

#!/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

Answer 2

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.

Answer 3

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:

• No need for an extra file.
• No need for the <<"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:

• This uses Bash process substitution <(…) to provide a file input without needing a file on disk.
• The separator between dot commands is a newline right before the command, which we generate here with echo -e "\n".

Answer 4

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"
}