sqitch add suddently uses same template for all engines while add.all = true

[Cielquan]

Context

I recently started using sqitch for my DB migration management. I use MySQL for production and SQLite for quick testing. I use sqlfluff for linting and autoformatting my SQL scripts.

Because sqlfluff complains about BEGIN in MySQL Scripts, which is used in the sqitch default template for MySQL, I added custom templates to use START TRANSACTION in MySQL Scripts.

What I want

I want sqitch to create scripts for both SQLite and MySQL from there respective templates when I run e.g. sqitch add test -n "test".

What I get

When I run sqitch add test -n "test" I correctly get 6 test.ddl Scripts like shown in the file tree below. But the content of those files is the problem. At random sqitch chooses to either use MySQL or SQLite templates for both engines. The examples below are from the case MySQL templates were used.

I already did 2 migrations with sqitch before today and I added the templates before the first migration. So the setup worked 2 times already. So I am really confused why it suddenly stopped working.

I also tried checking out the commit where I added the templates and running sqitch add test -n "test", but I get the same bogus result.

Version

I installed sqitch around 7 weeks ago (according to git) and never updated it, but v1.4.0 is the most recent so it should not matter.

$ sqitch --version
sqitch (App::Sqitch) v1.4.0

OS

ubuntu 22.04.1

sqitch.conf

[core]
	top_dir = migrations
[engine "mysql"]
	target = db:mysql:
	registry = migration_data
	top_dir = migrations/mysql
	plan_file = migrations/sqitch.plan
	extension = ddl
[engine "sqlite"]
	target = db:sqlite:
	registry = migration_data
	top_dir = migrations/sqlite
	plan_file = migrations/sqitch.plan
	extension = ddl
[target "project"]
	uri = db:mysql:project
[target "project-sqlite"]
	uri = db:sqlite:project.db
[deploy]
	verify = true
[rebase]
	verify = true
[add]
	all = true
	template_directory = migrations/templates
[tag]
	all = true
[rework]
	all = true
[bundle]
	all = true

Truncated file tree in my repo:

sqitch.conf
migrations
├── sqitch.plan
├── mysql
│   ├── deploy
│   │   └── test.ddl
│   ├── revert
│   │   └── test.ddl
│   └── verify
│       └── test.ddl
├── sqlite
│   ├── deploy
│   │   └── test.ddl
│   ├── revert
│   │   └── test.ddl
│   └── verify
│       └── test.ddl
└── templates
    ├── deploy
    │   ├── mysql.tmpl
    │   └── sqlite.tmpl
    ├── revert
    │   ├── mysql.tmpl
    │   └── sqlite.tmpl
    └── verify
        ├── mysql.tmpl
        └── sqlite.tmpl

Templates

migrations/templates/deploy/mysql.tmpl

-- Deploy [% project %]:[% change %] to [% engine %]
[% FOREACH item IN requires -%]
-- requires: [% item %]
[% END -%]
[% FOREACH item IN conflicts -%]
-- conflicts: [% item %]
[% END -%]

start transaction;

-- XXX Add DDLs here.

commit;

migrations/templates/deploy/sqlite.tmpl

-- Deploy [% project %]:[% change %] to [% engine %]
[% FOREACH item IN requires -%]
-- requires: [% item %]
[% END -%]
[% FOREACH item IN conflicts -%]
-- conflicts: [% item %]
[% END -%]

begin;

-- XXX Add DDLs here.

commit;

migrations/templates/revert/mysql.tmpl

-- Revert [% project %]:[% change %] from [% engine %]

start transaction;

-- XXX Add DDLs here.

commit;

migrations/templates/revert/sqlite.tmpl

-- Revert [% project %]:[% change %] from [% engine %]

begin;

-- XXX Add DDLs here.

commit;

migrations/templates/verify/mysql.tmpl

-- Verify [% project %]:[% change %] on [% engine %]

start transaction;

-- XXX Add verifications here.

rollback;

migrations/templates/verify/sqlite.tmpl

-- Verify [% project %]:[% change %] on [% engine %]

begin;

-- XXX Add verifications here.

rollback;

Migration files

migrations/mysql/deploy/test.ddl

-- Deploy swlp:test to mysql

start transaction;

-- XXX Add DDLs here.

commit;

migrations/mysql/revert/test.ddl

-- Revert swlp:test from mysql

start transaction;

-- XXX Add DDLs here.

commit;

migrations/mysql/verify/test.ddl

-- Verify swlp:test on mysql

start transaction;

-- XXX Add verifications here.

rollback;

migrations/sqlite/deploy/test.ddl

-- Deploy swlp:test to sqlite

start transaction;

-- XXX Add DDLs here.

commit;

migrations/sqlite/revert/test.ddl

-- Revert swlp:test from sqlite

start transaction;

-- XXX Add DDLs here.

commit;

migrations/sqlite/verify/test.ddl

-- Verify swlp:test on sqlite

start transaction;

-- XXX Add verifications here.

rollback;

[theory]

Yay, someone is using templates sufficiently to surface this bug! Confirmed it is a bug, mostly because it was never updated to support adding change scripts to multiple engines at once, which was implemented all the way back in 0c7237c.