Database Schema

With the package created, first thing to do is develop the database schema. Apex strongly emphasizes writing SQL database schemas in SQL, hence the design of its migrations. If you are not familiar with SQL, it's quite easy to learn and the majority of details you need to know to write SQL schemas can be found on the How to Write Proper, Well Structured mySQL Database Schemas page of this site.

Please note, Apex does also offer full interopability with both, Doctrine and Eloquent if you prefer to use them for your models and migrations.

install.sql

Full details can be found on the Package Installation Migration page of the documentation, but every package comes with an initial migration that is executed upon installation. The contents of the install.sql file will be executed against the database to create any necessary database tables.

Open the file at /etc/Disaster/install.sql and enter the following contents:

CREATE TABLE disaster_locations (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    country VARCHAR(2) NOT NULL,
    city VARCHAR(150) NOT NULL,
    notes TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE disaster_volunteers (
    uuid VARCHAR(30) NOT NULL PRIMARY KEY,
    location_id INT NOT NULL DEFAULT 0,
    profession VARCHAR(255) NOT NULL DEFAULT '',
    FOREIGN KEY (uuid) REFERENCES armor_users (uuid) ON DELETE CASCADE
);

CREATE TABLE disaster_projects (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    location_id INT NOT NULL,
    status VARCHAR(20) NOT NULL, 
    title VARCHAR(255) NOT NULL,
    description LONGTEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES disaster_locations (id) ON DELETE CASCADE
);

CREATE TABLE disaster_assignees (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    is_active BOOLEAN NOT NULL,
    project_id INT NOT NULL,
    uuid VARCHAR(30) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES disaster_projects (id) ON DELETE CASCADE
);

remove.sql

Open the file at /etc/Disaster/remove.sql, and replace it with the following contents:

DROP TABLE IF EXISTS disaster_volunteers;
DROP TABLE IF EXISTS disaster_assignees;
DROP TABLE IF EXISTS disaster_projects;
DROP TABLE IF EXISTS disaster_locations;

If and when the package is removed, this SQL code wil be automatically executed against the database to remove any necessary database tables, et al.

Execute Installation Migration

Within the terminal, run the following migration install CLI command:

apex migration install disaster

This will execute all code within the /etc/Disaster/install.sql file against the database, creating all necessary database tables.