feat: Views, Triggers & Routines diff support — full DB object coverage across MySQL, PostgreSQL, SQLite#159
Closed
feat: Views, Triggers & Routines diff support — full DB object coverage across MySQL, PostgreSQL, SQLite#159
Conversation
…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.
|
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
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
Diff/model classes (CreateView,AlterView,DropView,CreateTrigger,AlterTrigger,DropTrigger,CreateRoutine,AlterRoutine,DropRoutine)SQLGen/DiffToSQL/generators — every class implements bothgetUp()andgetDown()DiffSorterupdated: DROP view/trigger/routine runs before table operations; CREATE/ALTER runs after data operationsDBSchema::diffViews(),diffTriggers(),diffRoutines()added to the comparison pipelineAdapter layer
getViews(),getTriggers(),getRoutines()added to all three adaptersMySQLAdapter::normalizeCreateStatement()stripsDEFINER,ALGORITHM,SQL SECURITY, and trailing semicolonsPostgresDialect::dropTrigger()emitsDROP TRIGGER ... ON table(PostgreSQL-specific syntax)SQLiteAdapter::getRoutines()returns[](SQLite has no stored procedures)Binary UUID support
BinaryValuewrapper for correct hex/binary round-tripping in data diffsInsertDataSQL,DeleteDataSQL,UpdateDataSQLnow emit explicit column-name listsTests
Unit tests — 522 tests, 886 assertions (all passing)
ProgrammableObjectsSQLTest— SQL generation for all 9 object types, UP and DOWNDiffSorterProgrammableTest— ordering with views/triggers/routines in the diff graphDiffSorterFKTest— FK topological sort regression coverageBinaryValueSQLTest— binary UUID round-tripE2E tests — full matrix
programmable_objects_8.txtbaseline recordedprogrammable_objects_pgsql_16.txtbaseline recordedprogrammable_objects_sqlite.txtbaseline recordedtestProgrammableObjectsDiffskipped — Dolt cannot execute multi-statement fixtures via PDONotable fixes during testing
parseSqlStatements()— rewrote with BEGIN/END depth tracking; naive;split broke inside trigger bodiesnormalizeCreateStatement()usesrtrim(trim(), ';')to remove trailing;that PDO injects into stored definitionsphpunit.v9.xml— missing./Unitdirectory added to Unit testsuite; unit tests were silently not running in container modeInfrastructure
docker-compose.yml— Dolt service and additional PostgreSQL version profiles.env.example— all DB port and credential variables documentedscripts/run-tests.sh—--dolt,--allflags;--recordmode support.github/workflows/tests.yml— Dolt CI job, full PostgreSQL matrix, SQLite jobDocumentation
docs/DB-ecosystem-compatibility.md— Object Support Matrix.github/copilot-instructions.md— diff object count (18→27), 4 new Key Gotchas