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
# cxxopts       — v3.1.1 lightweight CLI argument parser
# nlohmann/json — v3.11.3 header-only JSON library
# ---------------------------------------------------------------------------

include(FetchContent)

# SQLite3 (system)
find_package(SQLite3 REQUIRED)

# cxxopts v3.1.1
FetchContent_Declare(
    cxxopts
    GIT_REPOSITORY https://github.com/jarro2783/cxxopts.git
    GIT_TAG        v3.1.1
    GIT_SHALLOW    TRUE
)
set(CXXOPTS_BUILD_EXAMPLES OFF CACHE BOOL "" FORCE)
set(CXXOPTS_BUILD_TESTS    OFF CACHE BOOL "" FORCE)
FetchContent_MakeAvailable(cxxopts)

# 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)

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

add_executable(db_manager main.cpp)

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

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

/**
 * SQLite Database Manager CLI - Trial 3 (SQLite3 + cxxopts + nlohmann/json).
 *
 * 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 <cxxopts.hpp>
#include <nlohmann/json.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 (char c : line) {
        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_(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_);
    }

    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_);
    }

    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:
    sqlite3* db_;
};

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

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

    // Calculate column widths
    std::vector<size_t> widths(result.columns.size());
    for (size_t i = 0; i < result.columns.size(); ++i) {
        widths[i] = result.columns[i].size();
    }
    for (const auto& row : result.rows) {
        for (size_t i = 0; i < row.size() && i < widths.size(); ++i) {
            widths[i] = std::max(widths[i], row[i].size());
        }
    }

    // Build separator
    std::string sep = "+";
    for (size_t w : widths) {
        sep += std::string(w + 2, '-') + "+";
    }

    // Print header
    std::cout << sep << "\n";
    std::cout << "|";
    for (size_t i = 0; i < result.columns.size(); ++i) {
        std::cout << " ";
        std::cout << result.columns[i];
        std::cout << std::string(widths[i] - result.columns[i].size(), ' ');
        std::cout << " |";
    }
    std::cout << "\n" << sep << "\n";

    // Print rows
    for (const auto& row : result.rows) {
        std::cout << "|";
        for (size_t i = 0; i < row.size() && i < widths.size(); ++i) {
            std::cout << " ";
            std::cout << row[i];
            std::cout << std::string(widths[i] - row[i].size(), ' ');
            std::cout << " |";
        }
        std::cout << "\n";
    }
    std::cout << sep << "\n";
    std::cout << "(" << result.rows.size() << " row(s))\n";
}

static void print_json_output(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";
}

// ---------------------------------------------------------------------------
// Command implementations
// ---------------------------------------------------------------------------

static void cmd_create_table(DbManager& db, const std::vector<std::string>& positional) {
    if (positional.size() < 3) {
        die("Usage: create-table <tableName> <col:type>...");
    }
    std::string table_name = positional[1];
    std::string col_defs;
    for (size_t i = 2; i < positional.size(); ++i) {
        const auto& arg = positional[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, const std::vector<std::string>& positional,
                       const std::string& values, const std::string& columns) {
    if (positional.size() < 2) {
        die("Usage: insert <tableName> --values <vals> [--columns <cols>]");
    }
    std::string table_name = positional[1];

    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, const std::vector<std::string>& positional,
                       const std::string& columns, const std::string& where_clause,
                       const std::string& order_by, const std::string& limit_str,
                       bool use_json) {
    if (positional.size() < 2) {
        die("Usage: select <tableName> [-c cols] [-w where] [-o order] [-l limit]");
    }
    std::string table_name = positional[1];

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

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

static void cmd_update(DbManager& db, const std::vector<std::string>& positional,
                       const std::string& set_clause, const std::string& where_clause) {
    if (positional.size() < 2) {
        die("Usage: update <tableName> --set <clause> --where <clause>");
    }
    std::string table_name = positional[1];

    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, const std::vector<std::string>& positional,
                       const std::string& where_clause) {
    if (positional.size() < 2) {
        die("Usage: delete <tableName> --where <clause>");
    }
    std::string table_name = positional[1];

    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, const std::vector<std::string>& positional) {
    if (positional.size() >= 2) {
        std::string table_name = positional[1];
        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 {
        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) {
                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, const std::vector<std::string>& positional) {
    if (positional.size() < 3) {
        die("Usage: import-csv <tableName> <csvFile>");
    }
    std::string table_name = positional[1];
    std::string csv_file = positional[2];

    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 + ")");

    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, const std::vector<std::string>& positional) {
    if (positional.size() < 3) {
        die("Usage: export-csv <tableName> <csvFile>");
    }
    std::string table_name = positional[1];
    std::string csv_file = positional[2];

    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);
    }

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

    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, const std::vector<std::string>& positional, bool use_json) {
    if (positional.size() < 2) {
        die("Usage: query <sql> [--json]");
    }
    std::string sql = positional[1];
    auto trimmed = trim(sql);

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

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

int main(int argc, char* argv[]) {
    cxxopts::Options options("db_manager", "SQLite Database Manager (SQLite3 + cxxopts + nlohmann/json)");

    options.add_options()
        ("db", "Path to SQLite database file", cxxopts::value<std::string>()->default_value("sample.db"))
        ("c,columns", "Columns to select or column names for insert", cxxopts::value<std::string>()->default_value(""))
        ("v,values", "Comma-separated values for insert", cxxopts::value<std::string>()->default_value(""))
        ("w,where", "WHERE clause", cxxopts::value<std::string>()->default_value(""))
        ("o,order-by", "ORDER BY clause", cxxopts::value<std::string>()->default_value(""))
        ("l,limit", "Row limit", cxxopts::value<std::string>()->default_value(""))
        ("set", "SET clause for update", cxxopts::value<std::string>()->default_value(""))
        ("json", "Output results as JSON")
        ("h,help", "Print usage")
        ("positional", "Positional arguments", cxxopts::value<std::vector<std::string>>())
    ;

    options.parse_positional({"positional"});
    options.positional_help("<command> [args...]");

    cxxopts::ParseResult result;
    try {
        result = options.parse(argc, argv);
    } catch (const cxxopts::exceptions::exception& e) {
        std::cerr << "Error parsing options: " << e.what() << "\n";
        std::cout << options.help() << "\n";
        return 1;
    }

    if (result.count("help")) {
        std::cout << options.help() << "\n";
        std::cout << "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";
        return 0;
    }

    std::string db_path = result["db"].as<std::string>();
    bool use_json = result.count("json") > 0;

    std::vector<std::string> positional;
    if (result.count("positional")) {
        positional = result["positional"].as<std::vector<std::string>>();
    }

    if (positional.empty()) {
        // No command — create sample DB and show help
        DbManager db(db_path);
        create_demo_db(db);
        std::cout << options.help() << "\n";
        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 = positional[0];
    std::string columns = result["columns"].as<std::string>();
    std::string values = result["values"].as<std::string>();
    std::string where_clause = result["where"].as<std::string>();
    std::string order_by = result["order-by"].as<std::string>();
    std::string limit_str = result["limit"].as<std::string>();
    std::string set_clause = result["set"].as<std::string>();

    if (command == "create-table") {
        cmd_create_table(db, positional);
    } else if (command == "insert") {
        cmd_insert(db, positional, values, columns);
    } else if (command == "select") {
        cmd_select(db, positional, columns, where_clause, order_by, limit_str, use_json);
    } else if (command == "update") {
        cmd_update(db, positional, set_clause, where_clause);
    } else if (command == "delete") {
        cmd_delete(db, positional, where_clause);
    } else if (command == "schema") {
        cmd_schema(db, positional);
    } else if (command == "import-csv") {
        cmd_import_csv(db, positional);
    } else if (command == "export-csv") {
        cmd_export_csv(db, positional);
    } else if (command == "query") {
        cmd_query(db, positional, use_json);
    } else {
        std::cerr << "Unknown command: " << command << "\n";
        std::cout << options.help() << "\n";
        return 1;
    }

    return 0;
}