Migration steps with same name are erroneously skipped

[asg017]

Discovered when I had two separate migrations with different migration_set names but the same step name.

The first migration:

from sqlite_migrate import Migrations

m1 = Migrations("m1")

@m1()
def m001_init(db):
    pass

The second:

from sqlite_migrate import Migrations
m2 = Migrations("m2")

@m2()
def m001_init(db):
    pass

The m1.m001_init() step runs successfully, but m2.m001_init() is skipped. This is because they share the same step name m001_init, but they should have a different namespace anyway.

Caused by this, I think:

sqlite-migrate/sqlite_migrate/__init__.py

Line 51 in 82f3459

if migration.name not in already_applied

[simonw]

Ouch, there's another problem here:

sqlite-migrate/sqlite_migrate/__init__.py

Lines 84 to 97 in 82f3459

	def ensure_migrations_table(self, db: "Database"):
	"""
	Create _sqlite_migrations table if it doesn't already exist
	"""
	table = _table(db, self.migrations_table)
	if not table.exists():
	table.create(
	{
	"migration_set": str,
	"name": str,
	"applied_at": str,
	},
	pk="name",
	)

The primary key on _sqlite_migrations is on name - but clearly it should be. compound primary key on (migration_set, name) instead - otherwise you can't have two migration sets with duplicate names.

[simonw]

Frustrating that we don't have migrations for the _sqlite_migrations table itself! I'm going to have to write code that knows how to upgrade that table by introspecting if it has the correct primary key.

[simonw]

I manually tested it against a file that had previously been migrated.

Before it had the old single primary key:

$ sqlite-utils schema creatures.db 
CREATE TABLE [_sqlite_migrations] (
   [migration_set] TEXT,
   [name] TEXT PRIMARY KEY,
   [applied_at] TEXT
);
CREATE TABLE [creatures] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT,
   [species] TEXT
, [weight] FLOAT);
$ sqlite-utils migrate creatures.db

Now it has the new primary key scheme:

$ sqlite-utils schema creatures.db 
CREATE TABLE "_sqlite_migrations" (
   [migration_set] TEXT,
   [name] TEXT,
   [applied_at] TEXT,
   PRIMARY KEY ([migration_set], [name])
);
CREATE TABLE "creatures" (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT,
   [species] TEXT,
   [weight] FLOAT,
   [age] INTEGER,
   [shoe_size] INTEGER
);

Running it a second time makes no changes:

$ sqlite-utils migrate creatures.db
$ sqlite-utils schema creatures.db 
CREATE TABLE "_sqlite_migrations" (
   [migration_set] TEXT,
   [name] TEXT,
   [applied_at] TEXT,
   PRIMARY KEY ([migration_set], [name])
);
CREATE TABLE "creatures" (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT,
   [species] TEXT,
   [weight] FLOAT,
   [age] INTEGER,
   [shoe_size] INTEGER
);