#!/bin/bash -
# ------------------------------------------------------------------------------
# sqlite.sh
# =========
#
# Scope Native
# Copyright (C) 2024 by RaySoft, Zurich, Switzerland
# License GNU General Public License (GPL) 2.0
# https://www.gnu.org/licenses/gpl2.txt
#
# ------------------------------------------------------------------------------
set -o 'noglob' -o 'nounset' -o 'pipefail' # -o 'xtrace' -o 'errexit'
# ------------------------------------------------------------------------------
DB_FILE="${HOME}/tmp/test.sqlite"
# ------------------------------------------------------------------------------
SQLITE=('/usr/bin/sqlite3')
# ------------------------------------------------------------------------------
"${SQLITE[@]}" "${DB_FILE}" <<EOS
CREATE TABLE IF NOT EXISTS user (
usr_id INTEGER PRIMARY KEY,
usr_name TEXT,
usr_prename TEXT
);
INSERT INTO user (usr_name, usr_prename)
VALUES ('Smith', 'John'),
('Smith', 'Jane');
.exit
EOS
"${SQLITE[@]}" "${DB_FILE}" <<EOS
CREATE TABLE IF NOT EXISTS host (
hst_id INTEGER PRIMARY KEY,
hst_name TEXT,
hst_ip TEXT
);
INSERT INTO host (hst_name, hst_ip)
VALUES ('Venus', '192.168.1.100'),
('Mars', '192.168.1.200');
.exit
EOS
"${SQLITE[@]}" "${DB_FILE}" <<EOS
CREATE TABLE IF NOT EXISTS user_host (
usr_hst_id INTEGER PRIMARY KEY,
usr_id INTEGER,
hst_id INTEGER
);
INSERT INTO user_host (usr_id, hst_id)
VALUES (1, 2),
(2, 1);
.exit
EOS
"${SQLITE[@]}" "${DB_FILE}" <<EOS
UPDATE host
SET hst_ip = '192.168.1.201'
WHERE hst_id = 2;
.exit
EOS
"${SQLITE[@]}" -header "${DB_FILE}" <<EOS
SELECT usr_name, usr_prename, hst_name, hst_ip
FROM user, user_host, host
WHERE user.usr_id = user_host.usr_id
AND user_host.hst_id = host.hst_id;
.exit
EOS
# ------------------------------------------------------------------------------
exit 0