sqlbashsqlite

How can I increment a field if the record already exists?

I have a bash script to dump a csv file to sqlite3:

 cat <<EOF | sqlite3 "$1.db"
CREATE TABLE IF NOT EXISTS 'dump' (
pk INTEGER PRIMARY KEY,
first TEXT,
last TEXT,
seen INTEGER,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(first, last)
);
.mode csv
.separator ";"
.import '$1.tmp' 'dump'
EOF

Which works for basic dump but this dump file is constantly being updated and I need to track the number of times that person is seen. How can I increment the "seen" field if that person is already in the database?

Solution

  • You have a Bash script that imports a CSV into an SQLite database. The CSV is constantly updated, and you need to increment the seen field for existing records while inserting new ones.

    Solution: Efficient UPSERT with SQLite Transactions

    To efficiently handle this, we'll use SQLite's INSERT OR REPLACE syntax within a transaction.

    cat <<EOF | sqlite3 "$1.db"
BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS 'dump' (
    pk INTEGER PRIMARY KEY,
    first TEXT,
    last TEXT,
    seen INTEGER DEFAULT 1,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(first, last)
);

.mode csv
.separator ";"

-- Create a temporary table with necessary columns
CREATE TEMP TABLE tmp_import (
    first TEXT,
    last TEXT
);

-- Import the CSV into the temporary table
.import '$1.tmp' tmp_import;

-- Efficiently insert or update records
INSERT OR REPLACE INTO dump (first, last, seen)
SELECT first, last, seen + 1
FROM tmp_import;

DROP TABLE tmp_import;
COMMIT;
EOF

    Explanation: