Database Migrations

WMS Sync Hub uses a versioned, numbered migration pattern. Both the plugin and the API contain the same migration list (currently M001 – M005); each is idempotent and safe to re-run.

Where they live

• Plugin: WmsSyncHub/Classes/Migrations.cs
• API: WmsSyncHubApi/AutoCount/Migrations.cs

Both runners read and write the current version into one field:

SELECT SchemaVersion FROM Z_WmsSyncConfig

…and apply every migration whose number is greater than the stored version. A LATEST_SCHEMA_VERSION constant at the top of each file caps the list:

public const int LATEST_SCHEMA_VERSION = 5;

Running model

Two runners can race — first one wins, the second's migration is a no-op because its check-if-exists guards short-circuit.

Idempotency rules (required, not optional)

Every migration MUST be safe to run twice. The two patterns we use:

-- For tables
CREATE TABLE IF NOT EXISTS Z_WmsFoo (...)

-- For columns
IF NOT EXISTS (SELECT 1 FROM sys.columns
               WHERE Name = 'NewCol' AND Object_ID = Object_ID('Z_WmsFoo'))
    ALTER TABLE Z_WmsFoo ADD NewCol NVARCHAR(50) NULL;

The C# helpers CreateTableIfNotExists and AddColumnIfMissing wrap both patterns.

The sacred rule: shared tables

Z_PickingList, Z_PickingListDetail, Z_PickingNote, Z_PickingNoteDetail,
Z_ScannedHistory

These belong to the sibling "Transfer SO to IV" plugin. In our migrations we may:

• CREATE TABLE IF NOT EXISTS — so the plugin works standalone in a dev DB where the sibling is not installed.
• CRUD data inside them (flip DtlStatus, read Rate, etc).

We may not:

• Add, rename, or drop columns.
• Add indexes.
• Add triggers.

Any WMS-only column has to live on a separate Z_Wms* table joined back on the natural key. The canonical example is Z_WmsPickingNoteStatus — we wanted WmsStage and WmsOrderNumber on the picking note, but those live here instead.

Worked example — adding M006

Suppose you want a new column Z_WmsPickingNoteStatus.LastError.

1. Add the migration method

// WmsSyncHub/Classes/Migrations.cs
private static void M006_AddLastErrorColumn(SqlConnection conn)
{
    AddColumnIfMissing(conn,
        "Z_WmsPickingNoteStatus",
        "LastError",
        "NVARCHAR(500) NULL");
}

2. Register it in the runner

private static readonly Action<SqlConnection>[] _migrations =
{
    M001_CreateBaseTables,
    M002_AddSyncLogRawPayload,
    M003_AddItemSyncConfig,
    M004_AddPickingNoteItemStatus,
    M005_AddAsnTables,
    M006_AddLastErrorColumn,     // <-- new
};

3. Bump the constant

public const int LATEST_SCHEMA_VERSION = 6;

4. Repeat in WmsSyncHubApi/AutoCount/Migrations.cs

Both sides must be in sync. If the API starts first and migrates to version 6, the plugin starting later must know about M006 too — otherwise any startup assertion comparing LATEST_SCHEMA_VERSION to the live value will fail.

5. Test

Inner loop: run dev.bat against a dev DB already at v5. Check:

• On first launch, Z_WmsSyncConfig.SchemaVersion flips to 6.
• Z_WmsPickingNoteStatus has the new column.
• On second launch, nothing happens (idempotent).

Caution

:::caution Never drop or rename an existing column Dropping Z_WmsPickingNoteStatus.WmsStage in M007 breaks every older plugin build that joins against it. If you really need to remove a column, add a new one, dual-write for a release, then drop after every client is on the new version. :::

:::caution Migrations are not transactional across M-numbers Each M00N method runs in its own transaction. If M006 succeeds and M007 fails, your DB is left at version 6 — not rolled back to 5. Write each migration so it can be safely picked up from anywhere. :::