EBloodBank\Controllers\Install::createTheSchemasTables()

Complexity

Conditions	2
Paths	2

Size

Total Lines	133
Code Lines	107

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
    protected function createTheSchemasTables()
    {
        $stmts = [];
        $connection = $this->getContainer()->get('db_connection');

        /*** Users Table **************************************************/

        $stmts[] = <<<'SQL'
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(255) NOT NULL,
  `user_email` VARCHAR(100) NOT NULL,
  `user_pass` VARCHAR(128) NOT NULL,
  `user_role` VARCHAR(45) NOT NULL,
  `user_created_at` DATETIME NOT NULL,
  `user_status` VARCHAR(45) NOT NULL,
  `user_meta` JSON NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB
SQL;

        $stmts[] = <<<'SQL'
CREATE UNIQUE INDEX `user_email_UNIQUE` ON `user` (`user_email` ASC)
SQL;

        /*** Cities Table *************************************************/

        $stmts[] = <<<'SQL'
CREATE TABLE IF NOT EXISTS `city` (
  `city_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `city_name` VARCHAR(255) NOT NULL,
  `city_created_at` DATETIME NOT NULL,
  `city_created_by` INT UNSIGNED NULL,
  PRIMARY KEY (`city_id`),
  CONSTRAINT `city_created_by`
    FOREIGN KEY (`city_created_by`)
    REFERENCES `user` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
SQL;

        $stmts[] = <<<'SQL'
CREATE INDEX `city_created_by_idx` ON `city` (`city_created_by` ASC)
SQL;

        $stmts[] = <<<'SQL'
CREATE UNIQUE INDEX `city_name_UNIQUE` ON `city` (`city_name` ASC)
SQL;

        /*** Districts Table **********************************************/

        $stmts[] = <<<'SQL'
CREATE TABLE IF NOT EXISTS `district` (
  `district_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `district_name` VARCHAR(255) NOT NULL,
  `district_city_id` INT UNSIGNED NOT NULL,
  `district_created_at` DATETIME NOT NULL,
  `district_created_by` INT UNSIGNED NULL,
  PRIMARY KEY (`district_id`),
  CONSTRAINT `district_city_id`
    FOREIGN KEY (`district_city_id`)
    REFERENCES `city` (`city_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `district_created_by`
    FOREIGN KEY (`district_created_by`)
    REFERENCES `user` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
SQL;

        $stmts[] = <<<'SQL'
CREATE INDEX `district_city_id_idx` ON `district` (`district_city_id` ASC)
SQL;

        $stmts[] = <<<'SQL'
CREATE INDEX `district_created_by_idx` ON `district` (`district_created_by` ASC)
SQL;

        $stmts[] = <<<'SQL'
CREATE UNIQUE INDEX `district_name_UNIQUE` ON `district` (`district_name` ASC, `district_city_id` ASC)
SQL;

        /*** Donors Table *************************************************/

        $stmts[] = <<<'SQL'
CREATE TABLE IF NOT EXISTS `donor` (
  `donor_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `donor_name` VARCHAR(255) NOT NULL,
  `donor_gender` VARCHAR(45) NOT NULL,
  `donor_birthdate` DATE NOT NULL,
  `donor_blood_group` VARCHAR(45) NOT NULL,
  `donor_district_id` INT UNSIGNED NOT NULL,
  `donor_created_at` DATETIME NOT NULL,
  `donor_created_by` INT UNSIGNED NULL,
  `donor_status` VARCHAR(45) NOT NULL,
  `donor_meta` JSON NULL,
  PRIMARY KEY (`donor_id`),
  CONSTRAINT `donor_district_id`
    FOREIGN KEY (`donor_district_id`)
    REFERENCES `district` (`district_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `donor_created_by`
    FOREIGN KEY (`donor_created_by`)
    REFERENCES `user` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
SQL;

        $stmts[] = <<<'SQL'
CREATE INDEX `donor_district_id_idx` ON `donor` (`donor_district_id` ASC)
SQL;

        $stmts[] = <<<'SQL'
CREATE INDEX `donor_created_by_idx` ON `donor` (`donor_created_by` ASC)
SQL;

        /*** Variables Table **********************************************/

        $stmts[] = <<<'SQL'
CREATE TABLE IF NOT EXISTS `variable` (
  `variable_name` VARCHAR(45) NOT NULL,
  `variable_value` LONGTEXT NULL,
  PRIMARY KEY (`variable_name`))
ENGINE = InnoDB
SQL;

        foreach ($stmts as $stmt) {
            $connection->exec($stmt);
        }