Skip to content

A native macOS / windows / Linux database IDE built with Swift and AppKit. Connect to PostgreSQL, MySQL, SQLite, and Redis from a single app with a fast, keyboard-driven interface.

C++
14.2 KB
Raw Blame
#pragma once
#include <string>
#include <vector>
#include <set>
#include <unordered_map>
#include <algorithm>
#include <cwctype>
#include "TableRow.h"
#include "DatabaseType.h"

namespace DBModels
{
    enum class EditType { Insert, Update, Delete };

    struct PendingEdit
    {
        EditType type;
        int rowIndex = -1;          // -1 for inserts (new rows)
        std::wstring column;        // for Update only
        std::wstring oldValue;      // for Update only
        std::wstring newValue;      // for Update only
        TableRow primaryKey;        // WHERE clause for update/delete
        TableRow insertValues;      // full row for inserts
    };

    // Tracks pending INSERT/UPDATE/DELETE changes before commit
    class ChangeTracker
    {
    public:
        void trackInsert(const TableRow& values, int rowIndex = -1)
        {
            PendingEdit edit;
            edit.type = EditType::Insert;
            edit.rowIndex = rowIndex;
            edit.insertValues = values;
            edits_.push_back(edit);
        }

        void trackUpdate(int rowIndex, const std::wstring& column,
                         const std::wstring& oldValue, const std::wstring& newValue,
                         const TableRow& primaryKey)
        {
            // If reverting to original, skip
            if (oldValue == newValue) return;

            // If this row is a pending INSERT, update the insert values directly
            // (no separate UPDATE needed — just modify the INSERT data)
            for (auto& e : edits_)
            {
                if (e.type == EditType::Insert && e.rowIndex == rowIndex)
                {
                    if (!isNullCell(newValue) && !newValue.empty())
                        e.insertValues[column] = newValue;
                    else
                        e.insertValues.erase(column); // NULL = omit from INSERT
                    return;
                }
            }

            // Merge: overwrite existing UPDATE for same row+column
            for (auto& e : edits_)
            {
                if (e.type == EditType::Update && e.rowIndex == rowIndex && e.column == column)
                {
                    if (newValue == e.oldValue)
                    {
                        // Reverted to original — remove edit
                        edits_.erase(std::remove_if(edits_.begin(), edits_.end(),
                            [&](const PendingEdit& x) { return &x == &e; }), edits_.end());
                    }
                    else
                    {
                        e.newValue = newValue;
                    }
                    return;
                }
            }

            PendingEdit edit;
            edit.type = EditType::Update;
            edit.rowIndex = rowIndex;
            edit.column = column;
            edit.oldValue = oldValue;
            edit.newValue = newValue;
            edit.primaryKey = primaryKey;
            edits_.push_back(edit);
        }

        void trackDelete(int rowIndex, const TableRow& primaryKey)
        {
            // Remove any pending updates for this row
            edits_.erase(std::remove_if(edits_.begin(), edits_.end(),
                [rowIndex](const PendingEdit& e) {
                    return e.type == EditType::Update && e.rowIndex == rowIndex;
                }), edits_.end());

            PendingEdit edit;
            edit.type = EditType::Delete;
            edit.rowIndex = rowIndex;
            edit.primaryKey = primaryKey;
            edits_.push_back(edit);
        }

        void discardAll()
        {
            edits_.clear();
        }

        bool hasChanges() const { return !edits_.empty(); }
        int changeCount() const { return static_cast<int>(edits_.size()); }

        const std::vector<PendingEdit>& pendingEdits() const { return edits_; }

        std::set<int> deletedRowIndices() const
        {
            std::set<int> indices;
            for (auto& e : edits_)
                if (e.type == EditType::Delete && e.rowIndex >= 0)
                    indices.insert(e.rowIndex);
            return indices;
        }

        // Generate SQL statements for all pending changes.
        // `columnTypes` lets generators emit dialect-correct literals
        // for typed columns (e.g. boolean → unquoted TRUE/FALSE instead
        // of the text '0'/'1' that round-trips through the edit UI).
        // Safe to pass an empty map; callers who have schema info should
        // populate it from the table's ColumnInfo list.
        std::vector<std::wstring> generateSQL(
            const std::wstring& table,
            const std::wstring& schema,
            DatabaseType dbType,
            const std::unordered_map<std::wstring, std::wstring>& columnTypes = {}) const
        {
            std::vector<std::wstring> statements;

            for (auto& edit : edits_)
            {
                switch (edit.type)
                {
                case EditType::Insert:
                    statements.push_back(generateInsert(table, schema, edit, dbType, columnTypes));
                    break;
                case EditType::Update:
                    // ClickHouse uses ALTER TABLE ... UPDATE instead of standard UPDATE.
                    if (dbType == DatabaseType::ClickHouse)
                        statements.push_back(generateClickHouseUpdate(table, schema, edit, dbType, columnTypes));
                    else
                        statements.push_back(generateUpdate(table, schema, edit, dbType, columnTypes));
                    break;
                case EditType::Delete:
                    // ClickHouse uses ALTER TABLE ... DELETE instead of standard DELETE FROM.
                    if (dbType == DatabaseType::ClickHouse)
                        statements.push_back(generateClickHouseDelete(table, schema, edit, dbType));
                    else
                        statements.push_back(generateDelete(table, schema, edit, dbType));
                    break;
                }
            }
            return statements;
        }

    private:
        std::vector<PendingEdit> edits_;

        static std::wstring quoteId(const std::wstring& name, DatabaseType dbType)
        {
            if (dbType == DatabaseType::MySQL ||
                dbType == DatabaseType::ClickHouse)
                return L"`" + name + L"`";
            return L"\"" + name + L"\"";  // PostgreSQL, SQLite, MSSQL
        }

        static std::wstring quoteLit(const std::wstring& value)
        {
            if (isNullCell(value)) return L"NULL";
            std::wstring escaped;
            escaped += L'\'';
            for (wchar_t c : value)
            {
                if (c == L'\'') escaped += L"''";
                else escaped += c;
            }
            escaped += L'\'';
            return escaped;
        }

        // Boolean column detection. Covers the dialect-specific spellings
        // driver metadata actually hands back (PG: "boolean"/"bool";
        // MSSQL: "bit"; MySQL: "tinyint(1)"/"bool"/"boolean"; SQLite:
        // "boolean"). tinyint without (1) is a regular integer, skip.
        static bool isBooleanType(const std::wstring& dataType)
        {
            std::wstring t = dataType;
            std::transform(t.begin(), t.end(), t.begin(),
                [](wchar_t c) { return std::towlower(c); });
            // Strip size/precision so "boolean(1)" / "bit(1)" still match.
            auto paren = t.find(L'(');
            std::wstring head = (paren == std::wstring::npos) ? t : t.substr(0, paren);
            if (head == L"bool" || head == L"boolean" || head == L"bit") return true;
            // tinyint(1) is MySQL's conventional boolean carrier; plain
            // tinyint is a 1-byte int and must keep integer formatting.
            if (head == L"tinyint" && t.find(L"(1)") != std::wstring::npos) return true;
            return false;
        }

        // Map the text the grid round-trips ("0"/"1"/"t"/"f"/"true"/"false"/
        // "TRUE"/"FALSE"/"") to a dialect-appropriate unquoted boolean
        // literal. MSSQL bit wants 0/1 (no TRUE keyword); everyone else
        // accepts TRUE/FALSE.
        static std::wstring booleanLit(const std::wstring& value, DatabaseType dbType)
        {
            if (isNullCell(value)) return L"NULL";
            std::wstring v = value;
            std::transform(v.begin(), v.end(), v.begin(),
                [](wchar_t c) { return std::towlower(c); });
            const bool truthy =
                (v == L"1" || v == L"t" || v == L"true" || v == L"yes" || v == L"y");
            if (dbType == DatabaseType::MSSQLServer)
                return truthy ? L"1" : L"0";
            return truthy ? L"TRUE" : L"FALSE";
        }

        // Type-aware literal: route boolean columns through booleanLit,
        // everything else keeps the existing text-quoted behaviour.
        static std::wstring quoteLitTyped(
            const std::wstring& column,
            const std::wstring& value,
            DatabaseType dbType,
            const std::unordered_map<std::wstring, std::wstring>& columnTypes)
        {
            auto it = columnTypes.find(column);
            if (it != columnTypes.end() && isBooleanType(it->second))
                return booleanLit(value, dbType);
            return quoteLit(value);
        }

        static std::wstring qualifiedTable(const std::wstring& table,
                                           const std::wstring& schema,
                                           DatabaseType dbType)
        {
            if (dbType == DatabaseType::SQLite || schema.empty())
                return quoteId(table, dbType);
            return quoteId(schema, dbType) + L"." + quoteId(table, dbType);
        }

        static std::wstring buildWhere(const TableRow& pk, DatabaseType dbType)
        {
            std::wstring where;
            bool first = true;
            for (auto& [col, val] : pk)
            {
                if (!first) where += L" AND ";
                where += quoteId(col, dbType);
                if (isNullCell(val))
                    where += L" IS NULL";
                else
                    where += L" = " + quoteLit(val);
                first = false;
            }
            return where;
        }

        static std::wstring generateInsert(
            const std::wstring& table,
            const std::wstring& schema,
            const PendingEdit& edit,
            DatabaseType dbType,
            const std::unordered_map<std::wstring, std::wstring>& columnTypes = {})
        {
            std::wstring sql = L"INSERT INTO " + qualifiedTable(table, schema, dbType) + L" (";
            std::wstring vals;
            bool first = true;
            for (auto& [col, val] : edit.insertValues)
            {
                // Blank cell (user left it empty in the new-row UI) →
                // omit the column so AUTO_INCREMENT / SERIAL / column
                // DEFAULT fire. Otherwise we emit '' which bombs on INT
                // columns and fights auto-increment. Explicit SQL NULL
                // still travels via the null sentinel (quoteLit → NULL).
                if (!isNullCell(val) && val.empty()) continue;
                if (!first) { sql += L", "; vals += L", "; }
                sql += quoteId(col, dbType);
                vals += quoteLitTyped(col, val, dbType, columnTypes);
                first = false;
            }
            // All columns blank — use each dialect's all-defaults insert
            // form so we don't produce invalid "INSERT INTO t () VALUES()".
            // MySQL accepts "() VALUES ()"; PostgreSQL/SQLite/MSSQL want
            // "DEFAULT VALUES".
            if (first)
            {
                if (dbType == DatabaseType::MySQL)
                    return L"INSERT INTO " + qualifiedTable(table, schema, dbType) +
                           L" () VALUES ()";
                return L"INSERT INTO " + qualifiedTable(table, schema, dbType) +
                       L" DEFAULT VALUES";
            }
            sql += L") VALUES (" + vals + L")";
            return sql;
        }

        static std::wstring generateUpdate(
            const std::wstring& table,
            const std::wstring& schema,
            const PendingEdit& edit,
            DatabaseType dbType,
            const std::unordered_map<std::wstring, std::wstring>& columnTypes = {})
        {
            std::wstring sql = L"UPDATE " + qualifiedTable(table, schema, dbType) + L" SET ";
            sql += quoteId(edit.column, dbType) + L" = "
                + quoteLitTyped(edit.column, edit.newValue, dbType, columnTypes);
            sql += L" WHERE " + buildWhere(edit.primaryKey, dbType);
            return sql;
        }

        static std::wstring generateDelete(const std::wstring& table,
                                           const std::wstring& schema,
                                           const PendingEdit& edit,
                                           DatabaseType dbType)
        {
            std::wstring sql = L"DELETE FROM " + qualifiedTable(table, schema, dbType);
            sql += L" WHERE " + buildWhere(edit.primaryKey, dbType);
            return sql;
        }

        // ClickHouse mutation: ALTER TABLE db.t UPDATE col=val WHERE ...
        static std::wstring generateClickHouseUpdate(
            const std::wstring& table,
            const std::wstring& schema,
            const PendingEdit& edit,
            DatabaseType dbType,
            const std::unordered_map<std::wstring, std::wstring>& columnTypes = {})
        {
            std::wstring sql = L"ALTER TABLE " + qualifiedTable(table, schema, dbType) + L" UPDATE ";
            sql += quoteId(edit.column, dbType) + L" = "
                + quoteLitTyped(edit.column, edit.newValue, dbType, columnTypes);
            sql += L" WHERE " + buildWhere(edit.primaryKey, dbType);
            return sql;
        }

        // ClickHouse mutation: ALTER TABLE db.t DELETE WHERE ...
        static std::wstring generateClickHouseDelete(
            const std::wstring& table,
            const std::wstring& schema,
            const PendingEdit& edit,
            DatabaseType dbType)
        {
            std::wstring sql = L"ALTER TABLE " + qualifiedTable(table, schema, dbType) + L" DELETE";
            sql += L" WHERE " + buildWhere(edit.primaryKey, dbType);
            return sql;
        }
    };
}