Migrations and seed data

Numbered scripts

The install/ folder holds scripts named 0001.php, 0002.php, and up. Each one runs SQL through Database::Execute. The number is the version the script brings the module to. Early scripts create tables; later scripts alter or replace them.

Registering the folder

The module's index.php ends with the call that applies its scripts. The signature is:

Database::Install(string $moduleName, string $path, int $latestVersion);

Database::Install looks up the version the module has reached, runs every script above that number up to $latestVersion, and records the new version. A script never runs twice. When you add 0002.php, raise the version to match:

//  in Shop/index.php
Database::Install('Shop', __DIR__.'/install', 2);

Create a table

The first script creates the module's table. This is Shop/install/0001.php:

CREATE TABLE shop_product(
    `ShopProductID` BIGINT NOT NULL AUTO_INCREMENT,
    `ShopProductName` VARCHAR(250) NOT NULL,
    `ShopProductPrice` INT NOT NULL DEFAULT 0,
    `ShopProductStock` INT NOT NULL DEFAULT 0,
    `ShopProductAdded` BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (ShopProductID)
);

Wrapped in a script it reads:

<?php
Database::Execute("
    CREATE TABLE shop_product(
        `ShopProductID` BIGINT NOT NULL AUTO_INCREMENT,
        `ShopProductName` VARCHAR(250) NOT NULL,
        `ShopProductPrice` INT NOT NULL DEFAULT 0,
        `ShopProductStock` INT NOT NULL DEFAULT 0,
        `ShopProductAdded` BIGINT NOT NULL DEFAULT 0,
        PRIMARY KEY (ShopProductID)
    );
");

Change a table later

A later script alters or replaces a table. The Member module does this in 0006.php: it drops the old member_feature table and creates a new one with an immutable design. A replacement script drops then recreates:

<?php

// Replace shop_product with an added category column.
Database::Execute("
    ALTER TABLE shop_product
    ADD COLUMN `ShopProductCategoryID` BIGINT NOT NULL DEFAULT 0
");

Because each script runs once, a site that already ran 0001.php gets only the new change when it reaches 0002.php, and a fresh site runs both in order.

The rules a script follows

Install scripts stay simple on purpose, so they run cleanly on a live system:

• A script does only basic database operations: a schema change, or populating data. Nothing more.
• A script must not require any other file and must not depend on application classes. It talks to Database::Execute and nothing else, so it runs the same way regardless of what code is loaded.
• Scripts are incremental. Each numbered file carries the module forward by one step from the state the last one left.
• Scripts are immutable once a site has launched. A script that has run on a live database is never edited. Every later change to schema or data is a new numbered file.

Immutability is what lets migrations run against a live system without wiping data. A site records the version it has reached; the framework applies only the newer files, so existing rows are left untouched. If you edited an old script instead, a live site would never re-run it and would silently miss the change.

A note while developing

The immutable rule holds strictly once a site is live. While building a module before launch, if you do edit a script that others have already run, they cannot get the change by re-running: they must wipe their database and re-run every script from scratch. With more than one developer that is disruptive, so the safe habit even during development is to add a new numbered file rather than change an existing one.

Seed production data

Data the site always needs ships as an INSERT in its own numbered script, so it lands once alongside the schema:

<?php
Database::Execute("
    INSERT INTO shop_category (ShopCategoryName)
    VALUES ('Uncategorised')
");

Example data

Demonstration data belongs in its own install folder that a site chooses to run. Point a second Database::Install call at that folder, under its own module name, so example rows are opt-in and never seeded into a real site:

//  optional: only where a site wants the demo catalogue
Database::Install('ShopExample', __DIR__.'/example', 1);

Next: Storage.