SQLite Database Manager (cpp, written by Claude Code)

--- /dev/null
+++ b/setup.sh
@@ -0,0 +1,6 @@
+#!/bin/bash
+# System packages this project needs on a clean Ubuntu machine.
+set -e
+export DEBIAN_FRONTEND=noninteractive
+apt-get update -qq
+apt-get install -y -qq --no-install-recommends libsqlite3-dev

Claude Code wrote this cpp project from the task below. It does not run on a clean Ubuntu 22.04 machine as written.

Task given to the agent:

TASK: SQLite Database Manager

Write a program that provides a command-line interface for managing SQLite databases, supporting table creation, CRUD operations, schema inspection, data import/export, and query execution.

FUNCTIONAL REQUIREMENTS:
- Accept a database file path as a command-line argument (create it if it does not exist)
- Support creating tables with typed columns (TEXT, INTEGER, REAL, BLOB) and constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT)
- Support INSERT, SELECT, UPDATE, and DELETE operations via command-line subcommands
- Support executing arbitrary SQL queries via a --query flag
- Implement schema inspection: list all tables, show column details for a given table, show row counts
- Import data from a CSV file into a specified table via --import flag, auto-detecting column types
- Export a table or query result to CSV via --export flag
- Support basic transactions: --begin, --commit, --rollback flags for grouping operations
- Print query results as formatted tables to console with column headers and aligned values
- If no database path is given, create a sample database with at least three related tables (e.g., users, orders, products), populate with sample data, and run demonstration queries showing joins, aggregations, and filtering
- Handle database errors gracefully with clear messages distinguishing connection, schema, constraint, and query errors

Create a complete C++ project for a clean Ubuntu 22.04 machine with only G++ 12+ and CMake 3.22+ installed. Include:
- Source code
- CMakeLists.txt with all dependencies (direct and transitive) pinned to exact versions
- README.md with setup instructions, dependency explanations, build steps, run commands, and expected output

[
  {
    "category": "underspecification",
    "rule": "signature.missing_system_requirement",
    "source": "failure_signature",
    "excerpt": "Could NOT find SQLite3 (missing: SQLite3_INCLUDE_DIR SQLite3_LIBRARY)"
  },
  {
    "category": "underspecification",
    "rule": "diff.adds_external_environment_requirement",
    "source": "manifest_diff:setup.sh",
    "excerpt": "export DEBIAN_FRONTEND=noninteractive"
  },
  {
    "category": "underspecification",
    "rule": "diff.adds_external_environment_requirement",
    "source": "manifest_diff:setup.sh",
    "excerpt": "apt-get install -y -qq --no-install-recommends libsqlite3-dev"
  }
]

cmake_minimum_required(VERSION 3.22)
project(sqlite_db_manager VERSION 1.0.0 LANGUAGES CXX)

set(CMAKE_CXX_STANDARD 17)
set(CMAKE_CXX_STANDARD_REQUIRED ON)
set(CMAKE_CXX_EXTENSIONS OFF)

# ---------------------------------------------------------------------------
# Dependencies
# ---------------------------------------------------------------------------
# SQLite3        — system library for database access
# nlohmann/json  — v3.11.3 header-only JSON library
# tabulate       — v1.5 header-only table formatting library
# ---------------------------------------------------------------------------

include(FetchContent)

# SQLite3 (system)
find_package(SQLite3 REQUIRED)

# nlohmann/json v3.11.3
FetchContent_Declare(
    nlohmann_json
    GIT_REPOSITORY https://github.com/nlohmann/json.git
    GIT_TAG        v3.11.3
    GIT_SHALLOW    TRUE
)
set(JSON_BuildTests   OFF CACHE BOOL "" FORCE)
set(JSON_Install      OFF CACHE BOOL "" FORCE)
set(JSON_MultipleHeaders OFF CACHE BOOL "" FORCE)
FetchContent_MakeAvailable(nlohmann_json)

# tabulate v1.5
FetchContent_Declare(
    tabulate
    GIT_REPOSITORY https://github.com/p-ranav/tabulate.git
    GIT_TAG        v1.5
    GIT_SHALLOW    TRUE
)
FetchContent_MakeAvailable(tabulate)

# ---------------------------------------------------------------------------
# Executable
# ---------------------------------------------------------------------------

add_executable(db_manager main.cpp)

target_link_libraries(db_manager PRIVATE
    SQLite::SQLite3
    nlohmann_json::nlohmann_json
    tabulate::tabulate
)

if(CMAKE_CXX_COMPILER_ID MATCHES "GNU|Clang")
    target_compile_options(db_manager PRIVATE -Wall -Wextra -Wpedantic)
endif()

/**
 * SQLite Database Manager CLI - Trial 1 (SQLite3 + nlohmann/json + tabulate).
 *
 * A command-line tool for SQLite CRUD operations, schema inspection,
 * CSV import/export, and arbitrary SQL query execution.
 * If no database path is provided, creates a sample database.
 */

#include <sqlite3.h>
#include <nlohmann/json.hpp>
#include <tabulate/table.hpp>

#include <algorithm>
#include <cstdlib>
#include <filesystem>
#include <fstream>
#include <iostream>
#include <sstream>
#include <string>
#include <vector>

using json = nlohmann::json;
namespace fs = std::filesystem;

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------

static void die(const std::string& msg) {
    std::cerr << "Error: " << msg << "\n";
    std::exit(1);
}

static std::string trim(const std::string& s) {
    auto start = s.find_first_not_of(" \t\r\n");
    if (start == std::string::npos) return "";
    auto end = s.find_last_not_of(" \t\r\n");
    return s.substr(start, end - start + 1);
}

static std::vector<std::string> split_csv_line(const std::string& line) {
    std::vector<std::string> fields;
    std::string field;
    bool in_quotes = false;
    for (size_t i = 0; i < line.size(); ++i) {
        char c = line[i];
        if (c == '"') {
            in_quotes = !in_quotes;
        } else if (c == ',' && !in_quotes) {
            fields.push_back(trim(field));
            field.clear();
        } else {
            field += c;
        }
    }
    fields.push_back(trim(field));
    return fields;
}

static bool starts_with_ci(const std::string& str, const std::string& prefix) {
    if (str.size() < prefix.size()) return false;
    for (size_t i = 0; i < prefix.size(); ++i) {
        if (std::toupper(str[i]) != std::toupper(prefix[i])) return false;
    }
    return true;
}

// ---------------------------------------------------------------------------
// Database wrapper
// ---------------------------------------------------------------------------

class DbManager {
public:
    explicit DbManager(const std::string& path) : db_path_(path), db_(nullptr) {
        int rc = sqlite3_open(path.c_str(), &db_);
        if (rc != SQLITE_OK) {
            die("Cannot open database: " + std::string(sqlite3_errmsg(db_)));
        }
    }

    ~DbManager() {
        if (db_) sqlite3_close(db_);
    }

    sqlite3* handle() { return db_; }

    // Execute a non-query statement and return rows affected.
    int exec(const std::string& sql) {
        char* err_msg = nullptr;
        int rc = sqlite3_exec(db_, sql.c_str(), nullptr, nullptr, &err_msg);
        if (rc != SQLITE_OK) {
            std::string msg(err_msg);
            sqlite3_free(err_msg);
            die("SQL Error: " + msg);
        }
        return sqlite3_changes(db_);
    }

    // Execute a query and return column names + rows.
    struct QueryResult {
        std::vector<std::string> columns;
        std::vector<std::vector<std::string>> rows;
    };

    QueryResult query(const std::string& sql) {
        QueryResult result;
        sqlite3_stmt* stmt;
        int rc = sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, nullptr);
        if (rc != SQLITE_OK) {
            die("SQL Error: " + std::string(sqlite3_errmsg(db_)));
        }
        int col_count = sqlite3_column_count(stmt);
        for (int i = 0; i < col_count; ++i) {
            result.columns.push_back(sqlite3_column_name(stmt, i));
        }
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            std::vector<std::string> row;
            for (int i = 0; i < col_count; ++i) {
                const char* val = reinterpret_cast<const char*>(sqlite3_column_text(stmt, i));
                row.push_back(val ? val : "NULL");
            }
            result.rows.push_back(std::move(row));
        }
        sqlite3_finalize(stmt);
        return result;
    }

private:
    std::string db_path_;
    sqlite3* db_;
};

// ---------------------------------------------------------------------------
// Display helpers
// ---------------------------------------------------------------------------

static void print_table(const DbManager::QueryResult& result) {
    if (result.rows.empty()) {
        std::cout << "No rows returned.\n";
        return;
    }

    tabulate::Table table;
    table.add_row(tabulate::Table::Row_t(result.columns.begin(), result.columns.end()));

    for (const auto& row : result.rows) {
        table.add_row(tabulate::Table::Row_t(row.begin(), row.end()));
    }

    // Style the header row
    for (size_t i = 0; i < result.columns.size(); ++i) {
        table[0][i].format()
            .font_color(tabulate::Color::cyan)
            .font_style({tabulate::FontStyle::bold});
    }

    std::cout << table << "\n";
    std::cout << "(" << result.rows.size() << " row(s))\n";
}

static void print_json(const DbManager::QueryResult& result) {
    json j = json::array();
    for (const auto& row : result.rows) {
        json obj = json::object();
        for (size_t i = 0; i < result.columns.size(); ++i) {
            obj[result.columns[i]] = row[i];
        }
        j.push_back(obj);
    }
    std::cout << j.dump(2) << "\n";
}

// ---------------------------------------------------------------------------
// Demo database
// ---------------------------------------------------------------------------

static void create_demo_db(DbManager& db) {
    db.exec(
        "CREATE TABLE IF NOT EXISTS employees ("
        "    id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "    name TEXT NOT NULL,"
        "    department TEXT NOT NULL,"
        "    salary REAL NOT NULL,"
        "    hire_date TEXT NOT NULL"
        ")"
    );

    const std::vector<std::string> inserts = {
        "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Alice Johnson', 'Engineering', 95000.0, '2021-03-15')",
        "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Bob Smith', 'Marketing', 72000.0, '2020-07-01')",
        "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Carol White', 'Engineering', 102000.0, '2019-11-20')",
        "INSERT INTO employees (name, department, salary, hire_date) VALUES ('David Brown', 'Sales', 68000.0, '2022-01-10')",
        "INSERT INTO employees (name, department, salary, hire_date) VALUES ('Eve Davis', 'Marketing', 75000.0, '2021-09-05')",
    };

    for (const auto& sql : inserts) {
        db.exec(sql);
    }

    std::cout << "Created demo database with sample employees table.\n";
}

// ---------------------------------------------------------------------------
// Commands
// ---------------------------------------------------------------------------

static void cmd_create_table(DbManager& db, int argc, char* argv[], int start) {
    if (start + 1 >= argc) {
        die("Usage: create-table <tableName> <col:type>...");
    }
    std::string table_name = argv[start];
    std::string col_defs;
    for (int i = start + 1; i < argc; ++i) {
        std::string arg = argv[i];
        auto pos = arg.find(':');
        if (pos == std::string::npos) {
            die("Invalid column definition '" + arg + "'. Use name:TYPE format.");
        }
        if (!col_defs.empty()) col_defs += ", ";
        col_defs += arg.substr(0, pos) + " " + arg.substr(pos + 1);
    }
    db.exec("CREATE TABLE IF NOT EXISTS " + table_name + " (" + col_defs + ")");
    std::cout << "Table '" << table_name << "' created successfully.\n";
}

static void cmd_insert(DbManager& db, int argc, char* argv[], int start) {
    if (start >= argc) {
        die("Usage: insert <tableName> --values <vals> [--columns <cols>]");
    }
    std::string table_name = argv[start];
    std::string values, columns;

    for (int i = start + 1; i < argc; ++i) {
        std::string arg = argv[i];
        if ((arg == "--values" || arg == "-v") && i + 1 < argc) {
            values = argv[++i];
        } else if ((arg == "--columns" || arg == "-c") && i + 1 < argc) {
            columns = argv[++i];
        }
    }

    if (values.empty()) die("Missing --values for insert command.");

    auto vals = split_csv_line(values);
    std::string placeholders;
    for (size_t i = 0; i < vals.size(); ++i) {
        if (i > 0) placeholders += ", ";
        placeholders += "'" + vals[i] + "'";
    }

    std::string sql;
    if (!columns.empty()) {
        sql = "INSERT INTO " + table_name + " (" + columns + ") VALUES (" + placeholders + ")";
    } else {
        sql = "INSERT INTO " + table_name + " VALUES (" + placeholders + ")";
    }
    db.exec(sql);
    std::cout << "Row inserted into '" << table_name << "'.\n";
}

static void cmd_select(DbManager& db, int argc, char* argv[], int start) {
    if (start >= argc) {
        die("Usage: select <tableName> [-c cols] [-w where] [-o order] [-l limit]");
    }
    std::string table_name = argv[start];
    std::string columns = "*", where_clause, order_by, limit;
    bool use_json = false;

    for (int i = start + 1; i < argc; ++i) {
        std::string arg = argv[i];
        if ((arg == "--columns" || arg == "-c") && i + 1 < argc) {
            columns = argv[++i];
        } else if ((arg == "--where" || arg == "-w") && i + 1 < argc) {
            where_clause = argv[++i];
        } else if ((arg == "--order-by" || arg == "-o") && i + 1 < argc) {
            order_by = argv[++i];
        } else if ((arg == "--limit" || arg == "-l") && i + 1 < argc) {
            limit = argv[++i];
        } else if (arg == "--json") {
            use_json = true;
        }
    }

    std::string sql = "SELECT " + columns + " FROM " + table_name;
    if (!where_clause.empty()) sql += " WHERE " + where_clause;
    if (!order_by.empty()) sql += " ORDER BY " + order_by;
    if (!limit.empty()) sql += " LIMIT " + limit;

    auto result = db.query(sql);
    if (use_json) {
        print_json(result);
    } else {
        print_table(result);
    }
}

static void cmd_update(DbManager& db, int argc, char* argv[], int start) {
    if (start >= argc) {
        die("Usage: update <tableName> --set <clause> --where <clause>");
    }
    std::string table_name = argv[start];
    std::string set_clause, where_clause;

    for (int i = start + 1; i < argc; ++i) {
        std::string arg = argv[i];
        if (arg == "--set" && i + 1 < argc) {
            set_clause = argv[++i];
        } else if ((arg == "--where" || arg == "-w") && i + 1 < argc) {
            where_clause = argv[++i];
        }
    }

    if (set_clause.empty() || where_clause.empty()) {
        die("Both --set and --where are required for update.");
    }

    std::string sql = "UPDATE " + table_name + " SET " + set_clause + " WHERE " + where_clause;
    int changes = db.exec(sql);
    std::cout << changes << " row(s) updated in '" << table_name << "'.\n";
}

static void cmd_delete(DbManager& db, int argc, char* argv[], int start) {
    if (start >= argc) {
        die("Usage: delete <tableName> --where <clause>");
    }
    std::string table_name = argv[start];
    std::string where_clause;

    for (int i = start + 1; i < argc; ++i) {
        std::string arg = argv[i];
        if ((arg == "--where" || arg == "-w") && i + 1 < argc) {
            where_clause = argv[++i];
        }
    }

    if (where_clause.empty()) {
        die("--where is required for delete.");
    }

    std::string sql = "DELETE FROM " + table_name + " WHERE " + where_clause;
    int changes = db.exec(sql);
    std::cout << changes << " row(s) deleted from '" << table_name << "'.\n";
}

static void cmd_schema(DbManager& db, int argc, char* argv[], int start) {
    if (start < argc) {
        // Show schema for a specific table
        std::string table_name = argv[start];
        auto result = db.query("PRAGMA table_info(" + table_name + ")");
        if (result.rows.empty()) {
            std::cout << "Table '" << table_name << "' not found.\n";
            return;
        }
        std::cout << "\nSchema for table '" << table_name << "':\n";
        print_table(result);
    } else {
        // Show all tables
        auto tables = db.query(
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
        );
        if (tables.rows.empty()) {
            std::cout << "No tables found in the database.\n";
            return;
        }
        std::cout << "\nTables in database:\n";
        for (const auto& row : tables.rows) {
            std::cout << "  - " << row[0] << "\n";
            auto cols = db.query("PRAGMA table_info(" + row[0] + ")");
            for (const auto& col : cols.rows) {
                // col[1]=name, col[2]=type, col[3]=notnull, col[5]=pk
                std::string nullable = (col[3] == "1") ? "NOT NULL" : "NULLABLE";
                std::string pk = (col[5] != "0") ? " PRIMARY KEY" : "";
                std::cout << "      " << col[1] << " " << col[2] << " " << nullable << pk << "\n";
            }
        }
    }
}

static void cmd_import_csv(DbManager& db, int argc, char* argv[], int start) {
    if (start + 1 >= argc) {
        die("Usage: import-csv <tableName> <csvFile>");
    }
    std::string table_name = argv[start];
    std::string csv_file = argv[start + 1];

    std::ifstream file(csv_file);
    if (!file.is_open()) {
        die("Cannot open CSV file: " + csv_file);
    }

    std::string header_line;
    if (!std::getline(file, header_line)) {
        die("CSV file is empty.");
    }

    auto headers = split_csv_line(header_line);

    // Auto-create table
    std::string col_defs;
    for (size_t i = 0; i < headers.size(); ++i) {
        if (i > 0) col_defs += ", ";
        col_defs += headers[i] + " TEXT";
    }
    db.exec("CREATE TABLE IF NOT EXISTS " + table_name + " (" + col_defs + ")");

    // Build column names
    std::string col_names;
    for (size_t i = 0; i < headers.size(); ++i) {
        if (i > 0) col_names += ", ";
        col_names += headers[i];
    }

    int count = 0;
    std::string line;
    while (std::getline(file, line)) {
        line = trim(line);
        if (line.empty()) continue;

        auto values = split_csv_line(line);
        std::string val_list;
        for (size_t i = 0; i < values.size(); ++i) {
            if (i > 0) val_list += ", ";
            val_list += "'" + values[i] + "'";
        }

        db.exec("INSERT INTO " + table_name + " (" + col_names + ") VALUES (" + val_list + ")");
        ++count;
    }

    std::cout << "Imported " << count << " row(s) into '" << table_name
              << "' from '" << csv_file << "'.\n";
}

static void cmd_export_csv(DbManager& db, int argc, char* argv[], int start) {
    if (start + 1 >= argc) {
        die("Usage: export-csv <tableName> <csvFile>");
    }
    std::string table_name = argv[start];
    std::string csv_file = argv[start + 1];

    auto result = db.query("SELECT * FROM " + table_name);
    if (result.rows.empty()) {
        std::cout << "No data found in table '" << table_name << "'.\n";
        return;
    }

    std::ofstream file(csv_file);
    if (!file.is_open()) {
        die("Cannot open output file: " + csv_file);
    }

    // Write header
    for (size_t i = 0; i < result.columns.size(); ++i) {
        if (i > 0) file << ",";
        file << result.columns[i];
    }
    file << "\n";

    // Write rows
    for (const auto& row : result.rows) {
        for (size_t i = 0; i < row.size(); ++i) {
            if (i > 0) file << ",";
            file << (row[i] == "NULL" ? "" : row[i]);
        }
        file << "\n";
    }

    std::cout << "Exported " << result.rows.size() << " row(s) from '"
              << table_name << "' to '" << csv_file << "'.\n";
}

static void cmd_query(DbManager& db, int argc, char* argv[], int start) {
    if (start >= argc) {
        die("Usage: query <sql> [--json]");
    }
    std::string sql = argv[start];
    bool use_json = false;

    for (int i = start + 1; i < argc; ++i) {
        if (std::string(argv[i]) == "--json") use_json = true;
    }

    std::string upper_sql = sql;
    std::transform(upper_sql.begin(), upper_sql.end(), upper_sql.begin(), ::toupper);
    auto trimmed = trim(upper_sql);

    if (starts_with_ci(trimmed, "SELECT") || starts_with_ci(trimmed, "PRAGMA")) {
        auto result = db.query(sql);
        if (use_json) {
            print_json(result);
        } else {
            print_table(result);
        }
    } else {
        int changes = db.exec(sql);
        std::cout << "Query executed successfully. " << changes << " row(s) affected.\n";
    }
}

// ---------------------------------------------------------------------------
// Usage
// ---------------------------------------------------------------------------

static void print_usage(const char* prog) {
    std::cout << "SQLite Database Manager (SQLite3 + nlohmann/json + tabulate)\n"
              << "Usage: " << prog << " [--db <path>] <command> [args]\n\n"
              << "Commands:\n"
              << "  create-table <name> <col:type>...           Create a new table\n"
              << "  insert <table> -v <values> [-c <cols>]      Insert a row\n"
              << "  select <table> [-c cols] [-w where] [-o order] [-l limit] [--json]\n"
              << "  update <table> --set <clause> -w <where>    Update rows\n"
              << "  delete <table> -w <where>                   Delete rows\n"
              << "  schema [table]                              Inspect schema\n"
              << "  import-csv <table> <file>                   Import from CSV\n"
              << "  export-csv <table> <file>                   Export to CSV\n"
              << "  query <sql> [--json]                        Execute raw SQL\n";
}

// ---------------------------------------------------------------------------
// Main
// ---------------------------------------------------------------------------

int main(int argc, char* argv[]) {
    std::string db_path = "sample.db";
    int cmd_start = 1;

    // Parse global options
    for (int i = 1; i < argc; ++i) {
        std::string arg = argv[i];
        if ((arg == "--db") && i + 1 < argc) {
            db_path = argv[++i];
            cmd_start = i + 1;
        } else if (arg == "--help" || arg == "-h") {
            print_usage(argv[0]);
            return 0;
        } else {
            cmd_start = i;
            break;
        }
    }

    if (cmd_start >= argc) {
        // No command given — create sample DB and show usage
        DbManager db(db_path);
        if (!fs::exists(db_path)) {
            create_demo_db(db);
        } else {
            create_demo_db(db);
        }
        print_usage(argv[0]);
        return 0;
    }

    // Ensure demo DB exists
    bool needs_demo = !fs::exists(db_path);
    DbManager db(db_path);
    if (needs_demo) {
        create_demo_db(db);
    }

    std::string command = argv[cmd_start];
    int arg_start = cmd_start + 1;

    if (command == "create-table") {
        cmd_create_table(db, argc, argv, arg_start);
    } else if (command == "insert") {
        cmd_insert(db, argc, argv, arg_start);
    } else if (command == "select") {
        cmd_select(db, argc, argv, arg_start);
    } else if (command == "update") {
        cmd_update(db, argc, argv, arg_start);
    } else if (command == "delete") {
        cmd_delete(db, argc, argv, arg_start);
    } else if (command == "schema") {
        cmd_schema(db, argc, argv, arg_start);
    } else if (command == "import-csv") {
        cmd_import_csv(db, argc, argv, arg_start);
    } else if (command == "export-csv") {
        cmd_export_csv(db, argc, argv, arg_start);
    } else if (command == "query") {
        cmd_query(db, argc, argv, arg_start);
    } else {
        std::cerr << "Unknown command: " << command << "\n";
        print_usage(argv[0]);
        return 1;
    }

    return 0;
}