Skip to content

feat: Views, Triggers & Routines diff support — full DB object coverage across MySQL, PostgreSQL, SQLite#160

Merged
jasdeepkhalsa merged 7 commits intomasterfrom
feature/polish-for-next-release
Mar 27, 2026
Merged

feat: Views, Triggers & Routines diff support — full DB object coverage across MySQL, PostgreSQL, SQLite#160
jasdeepkhalsa merged 7 commits intomasterfrom
feature/polish-for-next-release

Conversation

@jasdeepkhalsa
Copy link
Copy Markdown
Member

Summary

Extends DBDiff to diff all common programmable database objects — Views, Triggers, and Stored Procedures/Functions — across every supported engine. Pairs with the existing tables/indexes/FKs coverage to deliver complete schema diffing.


What's new

Programmable object diff support

Object type MySQL PostgreSQL SQLite
Views ✅ CREATE / ALTER / DROP
Triggers ✅ CREATE / ALTER / DROP
Procedures ✅ CREATE / ALTER / DROP — (unsupported)
Functions ✅ CREATE / ALTER / DROP — (unsupported)
  • 9 new Diff/ model classes (CreateView, AlterView, DropView, CreateTrigger, AlterTrigger, DropTrigger, CreateRoutine, AlterRoutine, DropRoutine)
  • 9 new SQLGen/DiffToSQL/ generators — every class implements both getUp() and getDown()
  • DiffSorter updated: DROP view/trigger/routine runs before table operations; CREATE/ALTER runs after data operations
  • DBSchema::diffViews(), diffTriggers(), diffRoutines() added to the comparison pipeline

Adapter layer

  • getViews(), getTriggers(), getRoutines() added to all three adapters
  • MySQLAdapter::normalizeCreateStatement() strips DEFINER, ALGORITHM, SQL SECURITY, and trailing semicolons
  • PostgresDialect::dropTrigger() emits DROP TRIGGER ... ON table (PostgreSQL-specific syntax)
  • SQLiteAdapter::getRoutines() returns [] (SQLite has no stored procedures)

Binary UUID support

  • New BinaryValue wrapper for correct hex/binary round-tripping in data diffs
  • InsertDataSQL, DeleteDataSQL, UpdateDataSQL now emit explicit column-name lists

Tests

Unit tests — 522 tests, 886 assertions (all passing)

  • ProgrammableObjectsSQLTest — SQL generation for all 9 object types, UP and DOWN
  • DiffSorterProgrammableTest — ordering with views/triggers/routines in the diff graph
  • DiffSorterFKTest — FK topological sort regression coverage
  • BinaryValueSQLTest — binary UUID round-trip

E2E tests — full matrix

Engine Result Notes
MySQL 8.4 13/13 ✅ programmable_objects_8.txt baseline recorded
PostgreSQL 16 13/13 ✅ programmable_objects_pgsql_16.txt baseline recorded
SQLite 13/13 ✅ (1 pre-existing skip) programmable_objects_sqlite.txt baseline recorded
Dolt 13/13 ✅ (7 skipped) testProgrammableObjectsDiff skipped — Dolt cannot execute multi-statement fixtures via PDO

Notable fixes during testing

  • SQLite parseSqlStatements() — rewrote with BEGIN/END depth tracking; naive ; split broke inside trigger bodies
  • MySQL PDO double-semicolonnormalizeCreateStatement() uses rtrim(trim(), ';') to remove trailing ; that PDO injects into stored definitions
  • phpunit.v9.xml — missing ./Unit directory added to Unit testsuite; unit tests were silently not running in container mode

Infrastructure

  • docker-compose.yml — Dolt service and additional PostgreSQL version profiles
  • .env.example — all DB port and credential variables documented
  • scripts/run-tests.sh--dolt, --all flags; --record mode support
  • .github/workflows/tests.yml — Dolt CI job, full PostgreSQL matrix, SQLite job

Documentation

  • README — updated feature list; new "Views", "Triggers", "Routines" subsections in "How Does the Diff Work?"; Docker/Podman install section (explicit Podman commands, rootless networking note, install hints)
  • DOCKER.md — Podman setup and rootless networking guidance
  • docs/DB-ecosystem-compatibility.md — Object Support Matrix
  • .github/copilot-instructions.md — diff object count (18→27), 4 new Key Gotchas

…ge across MySQL, PostgreSQL, SQLite

Adds diff, SQL generation, and E2E test coverage for all common programmable database objects:
Views, Triggers, and Stored Procedures/Functions.

New capabilities:
- Views: CREATE VIEW, ALTER VIEW, DROP VIEW — MySQL, PostgreSQL, SQLite
- Triggers: CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER — MySQL, PostgreSQL, SQLite
- Routines: CREATE/ALTER/DROP PROCEDURE/FUNCTION — MySQL, PostgreSQL (SQLite has none)
- All six diff models implement getUp() + getDown() SQL generation
- DiffSorter updated: DROP view/trigger/routine before table ops; CREATE/ALTER after data ops

Adapter layer:
- Added getViews(), getTriggers(), getRoutines() to all three adapters
- MySQLAdapter normalizeCreateStatement() strips DEFINER, ALGORITHM, SQL SECURITY, trailing semicolons
- PostgresAdapter DROP TRIGGER emits ON table clause (PG-specific syntax)
- SQLiteAdapter getRoutines() returns [] (SQLite has no stored procedures)

Binary UUID support:
- BinaryValue wrapper for correct hex/binary round-tripping in data diffs
- InsertDataSQL, DeleteDataSQL, UpdateDataSQL emit explicit column-name lists

Test coverage:
- 4 new unit test suites: ProgrammableObjectsSQLTest, DiffSorterProgrammableTest, DiffSorterFKTest, BinaryValueSQLTest (522 tests, 886 assertions)
- New E2E scenario testProgrammableObjectsDiff — baselines recorded for MySQL 8, PG 16, SQLite
- Dolt: testProgrammableObjectsDiff skipped (multi-statement fixture loading not supported)
- Dolt E2E baselines: schema_only, single_table, tables_ignore, fields_ignore added
- SQLiteTest parseSqlStatements() rewritten with BEGIN/END depth tracking to handle triggers
- phpunit.v9.xml: fixed missing Unit testsuite directory

Infrastructure and docs:
- docker-compose.yml: added Dolt service and postgres variants
- .env.example: added all DB port and credential variables
- scripts/run-tests.sh: --dolt, --all flags; --record mode support
- .github/workflows/tests.yml: Dolt job, PG matrix, SQLite job added
- README: updated feature list, How Diff Works section, Docker/Podman install docs
- DOCKER.md: Podman setup, rootless networking, cross-version guide
- docs/DB-ecosystem-compatibility.md: Object Support Matrix added
Only PG 16 baseline was recorded. The output is identical across all
supported PostgreSQL major versions — copy the PG 16 baseline to
14, 15, 17, and 18 so the CI matrix passes.
PG 14/15 table-qualify column references in view definitions
(e.g. products.id instead of id). The PG 16 baseline was incorrectly
copied — replace with version-specific baselines that match the
actual output from these older PostgreSQL versions.
- Remove die() from Logger::error() — callers control flow via exceptions
- DBDiff::run() re-throws exceptions instead of calling die()
- ArrayDiffTest resets ParamsFactory singleton in tearDown() to prevent
  stale params leaking into subsequent test suites
- ArrayDiff: add explicit property declarations (PHP 8.2+ compat),
  break after key match in tag() to prevent null-access warning
- DBManager: guard against null $params->input with clear DBException
- phpunit.xml: enable failOnNotice and failOnWarning (PHPUnit 10+)
- Fix DsnParser CI verification script key paths
INSERT/DELETE SQL now emits explicit column-name lists per the
InsertDataSQL/DeleteDataSQL generators. Update all MySQL expected
output files to match:

- End2End baselines (migration_expected_5, _8, _9): add column lists
  to INSERT INTO asas/cc/zz statements
- Comprehensive _9.txt baselines: sync with already-correct _8 versions
- Create missing programmable_objects_9.txt for MySQL 9.x CI jobs
@sonarqubecloud
Copy link
Copy Markdown

@jasdeepkhalsa jasdeepkhalsa merged commit b204171 into master Mar 27, 2026
65 checks passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant