pica/database/migrations/m0.sql

--creating system table
CREATE TABLE IF NOT EXISTS system (
    `key` VARCHAR(32) PRIMARY KEY,
    `value` TEXT
);

--creating roles table
CREATE TABLE IF NOT EXISTS roles (
    `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(256) UNIQUE NOT NULL,
    `color` INTEGER UNSIGNED DEFAULT 0
);

--creating accounts table
CREATE TABLE IF NOT EXISTS accounts (
    `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `login` VARCHAR(256) UNIQUE NOT NULL,
    `nick` VARCHAR(256),
    `type` INTEGER UNSIGNED NOT NULL,
    `password` VARCHAR(128),
    `created` TIMESTAMP
);

--creating role bindings table
CREATE TABLE IF NOT EXISTS roleBindings (
    `account` INTEGER UNSIGNED NOT NULL,
    `role` INTEGER UNSIGNED NOT NULL,

    PRIMARY KEY (account, role),
    FOREIGN KEY (account) REFERENCES accounts(id),
    FOREIGN KEY (role) REFERENCES roles(id)
);

--creating sessions table
CREATE TABLE IF NOT EXISTS sessions (
    `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `owner` INTEGER UNSIGNED NOT NULL,
    `started` TIMESTAMP,
    `latest` TIMESTAMP,
    `access` CHAR(32) NOT NULL UNIQUE,
    `renew` CHAR(32),
    `persist` BOOLEAN NOT NULL,
    `device` TEXT,

    FOREIGN KEY (owner) REFERENCES accounts(id)
);

--creating currencies table
CREATE TABLE IF NOT EXISTS currencies (
    `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(16) NOT NULL UNIQUE,
    `title` VARCHAR(256),
    `manual` BOOLEAN NOT NULL,
    `created` TIMESTAMP,
    `type` INTEGER UNSIGNED NOT NULL,
    `value` DECIMAL (20, 5) NOT NULL,
    `source` TEXT,
    `description` TEXT,
    `icon` VARCHAR(256),

    INDEX manual_idx (manual)
);

--creating assets table
CREATE TABLE IF NOT EXISTS assets (
    `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `owner` INTEGER UNSIGNED NOT NULL,
    `currency` INTEGER UNSIGNED NOT NULL,
    `title` VARCHAR(256),
    `icon` VARCHAR(256),
    `color` INTEGER UNSIGNED DEFAULT 0,
    `balance` DECIMAL (20, 5) DEFAULT 0,
    `type` INTEGER UNSIGNED NOT NULL,
    `archived` BOOLEAN DEFAULT FALSE,
    `created` TIMESTAMP,

    INDEX owner_idx (owner),
    INDEX archived_idx (archived),

    FOREIGN KEY (owner) REFERENCES accounts(id),
    FOREIGN KEY (currency) REFERENCES currencies(id)
);

--creating parties table
CREATE TABLE IF NOT EXISTS parties (
    `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(256) NOT NULL UNIQUE
);

--creating transactions table
CREATE TABLE IF NOT EXISTS transactions (
    `id` INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `initiator` INTEGER UNSIGNED NOT NULL,
    `type` INTEGER UNSIGNED NOT NULL,
    `asset` INTEGER UNSIGNED NOT NULL,
    `parent` INTEGER UNSIGNED,
    `value` DECIMAL (20, 5) NOT NULL,
    `state` INTEGER UNSIGNED DEFAULT 0,
    `modified` TIMESTAMP,
    `performed` TIMESTAMP,
    `party` INTEGER UNSIGNED,
    `notes` TEXT,

    INDEX initiator_idx (initiator),
    INDEX parent_idx (parent),
    INDEX asset_idx (asset),
    INDEX performed_idx (performed),
    INDEX modified_idx (modified),
    INDEX party_idx (party),

    FOREIGN KEY (initiator) REFERENCES accounts(id),
    FOREIGN KEY (asset) REFERENCES assets(id),
    FOREIGN KEY (parent) REFERENCES transactions(id),
    FOREIGN KEY (party) REFERENCES parties(id)
);

--creating trigger before insert accounts
CREATE TRIGGER before_insert_accounts
BEFORE INSERT ON accounts
FOR EACH ROW
BEGIN
    SET NEW.created = UTC_TIMESTAMP();
END;

--creating trigger before  insert sessions
CREATE TRIGGER before_insert_sessions
BEFORE INSERT ON sessions
FOR EACH ROW
BEGIN
    SET NEW.started = UTC_TIMESTAMP();
    SET NEW.latest = UTC_TIMESTAMP();
END;

--creating trigger before insert currencies
CREATE TRIGGER before_insert_currencies
BEFORE INSERT ON currencies
FOR EACH ROW
BEGIN
    IF NEW.created IS NULL THEN
        SET NEW.created = UTC_TIMESTAMP();
    END IF;
END;

--creating trigger before insert assets
CREATE TRIGGER before_insert_assets
BEFORE INSERT ON assets
FOR EACH ROW
BEGIN
    IF NEW.created IS NULL THEN
        SET NEW.created = UTC_TIMESTAMP();
    END IF;
END;

--creating trigger before insert transactions
CREATE TRIGGER before_insert_transactions
BEFORE INSERT ON transactions
FOR EACH ROW
BEGIN
    SET NEW.modified = UTC_TIMESTAMP();
    IF NEW.performed IS NULL THEN
        SET NEW.performed = UTC_TIMESTAMP();
    END IF;
END;

--creating trigger before update transactions
CREATE TRIGGER before_update_transactions
BEFORE UPDATE ON transactions
FOR EACH ROW
BEGIN
    SET NEW.modified = UTC_TIMESTAMP();
END;

--creating default roles
INSERT IGNORE INTO
roles  (`name`)
VALUES ('root'),
       ('default');

--inserting initial version
INSERT IGNORE INTO
system (`key`, `value`)
VALUES ('version', '0');

--recording initial time
INSERT IGNORE INTO
system (`key`, `value`)
VALUES ('created', UTC_TIMESTAMP());

--creating default currencies
INSERT IGNORE INTO
currencies (`code`, `title`,               `manual`, `description`,  `type`, `value`, `icon`)
VALUES     ('USD',  'United States Dollar', TRUE,    'Base currency', 1,      1,      'currency-usd');