johnslattery
9/1/2016 - 2:39 PM
A template for processing data with SQLite using SQL, client directives, and shell variables. Process the template with envsubst and pass it
A template for processing data with SQLite using SQL, client directives, and shell variables. Process the template with envsubst and pass it to the client.
-----------------
-- environment --
-----------------
.bail on
.load /usr/local/lib/sqlite3-pcre/pcre.so
PRAGMA foreign_keys = ON;
---------------
-- resources --
---------------
ATTACH DATABASE "$another_db_file" AS db;
DROP VIEW IF EXISTS temporaryview;
CREATE TEMP VIEW temporaryview AS
SELECT
t.field
FROM db.table t;
DROP TABLE IF EXISTS temporarytable;
CREATE TEMP TABLE temporarytable (
field TEXT);
------------------
-- process data --
------------------
.mode list
.headers ON
.output "$out_file"
SELECT
o.LastName,
o.FirstName,
o.MI,
strftime('%m/%d/%Y', o.DoB) DoB
FROM table o;
.output stdout
.headers OFFf () {
sqlite3 -batch "$db_file" < <(
export another_db_file out_file
envsubst '$another_db_file:$out_file' \
< 'sqlite-process-template.sql') || return
}