在bash下初始化sqlite舍不得删

#!/bin/bash
set -euo pipefail  #

check_db() {
    if [[ -f "$DB_FILE" ]]; then
        echo "[INFO] database: $DB_FILE check ok"
    else
        echo "[ERROR] database: $DB_FILE check failed" >&2
        exit 1
    fi
}


table_port_traffic_usage() {
    local action="$1"
    check_db
    local table="port_traffic_usage"
    case "$action" in
        create)
            echo "[INFO] Creating table: $table"

            sqlite3 "$DB_FILE" <<EOF
CREATE TABLE IF NOT EXISTS $table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts INTEGER NOT NULL,
    port INTEGER NOT NULL,
    tx_count REAL,
    rx_count REAL
);
EOF
            sqlite3 "$DB_FILE" <<EOF
CREATE INDEX IF NOT EXISTS idx_ts_$table
ON $table (ts);
EOF

            echo "[OK] $table table created."
            ;;
        delete)
            echo "[INFO] Dropping table: $table"
            sqlite3 "$DB_FILE" "DROP TABLE IF EXISTS $table;"
            echo "[OK] $table table deleted."
            ;;
        *)
            echo "Usage: $0 {create|delete}"
            return 1
            ;;
    esac
}

table_port_limit_record() {
    local action="$1"
    check_db
    local table="port_limit_record"
    case "$action" in
        create)
            echo "[INFO] Creating table: $table"

            sqlite3 "$DB_FILE" <<EOF
CREATE TABLE IF NOT EXISTS $table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts INTEGER NOT NULL,
    port INTEGER NOT NULL,
    traffic_when_limit REAL NOT NULL CHECK (traffic_when_limit > 0)
);
EOF
            sqlite3 "$DB_FILE" <<EOF
CREATE INDEX IF NOT EXISTS idx_ts_$table
ON $table (ts);
EOF

            echo "[OK] $table table created."
            ;;
        delete)
            echo "[INFO] Dropping table: $table"
            sqlite3 "$DB_FILE" "DROP TABLE IF EXISTS $table;"
            echo "[OK] $table table deleted."
            ;;
        *)
            echo "Usage: $0 {create|delete}"
            return 1
            ;;
    esac
}

table_port_limit_station() {
    local action="$1"
    check_db
    local table="port_limit_station"
    case "$action" in
        create)
            echo "[INFO] Creating table: $table"

            sqlite3 "$DB_FILE" <<EOF
CREATE TABLE IF NOT EXISTS $table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts INTEGER NOT NULL,
    port INTEGER NOT NULL UNIQUE
);
EOF
            sqlite3 "$DB_FILE" <<EOF
CREATE INDEX IF NOT EXISTS idx_ts_$table
ON $table (ts);
EOF

            echo "[OK] $table table created."
            ;;
        delete)
            echo "[INFO] Dropping table: $table"
            sqlite3 "$DB_FILE" "DROP TABLE IF EXISTS $table;"
            echo "[OK] $table table deleted."
            ;;
        *)
            echo "Usage: $0 {create|delete}"
            return 1
            ;;
    esac
}




init_db() {

    sqlite3 "$DB_FILE" "VACUUM;"
    table_port_traffic_usage create
    table_port_limit_record create
    table_port_limit_station create
}

destroy_db() {
    table_port_traffic_usage delete
    table_port_limit_record delete
    table_port_limit_station delete
    rm -f "$DB_FILE"
}

handle_db(){
    local flag=$1
    case "$flag" in
        init)
            init_db
            echo "[OK] database initialized."
            ;;
        destroy)
            destroy_db
            echo "[OK] database destroyed."
            ;;
        *)
            echo "Usage: $0 {init|destroy}"
            exit 1
            ;;
    esac

}


if [[ "${BASH_SOURCE[0]}" == "${0}" ]]; then
    DB_FILE="./test.db"
    handle $1 
fi