DoctrineMigrations\Version20190902140506::mySQLUp()

Complexity

Conditions	3
Paths	6

Size

Total Lines	154
Code Lines	129

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
    public function mySQLUp(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf('mysql' !== $this->connection->getDatabasePlatform()->getName(), 'Migration can only be executed safely on \'mysql\'.');

        try {
            //Check if we can use this migration method:
            $version = (int) $this->connection->fetchColumn("SELECT keyValue AS version FROM `internal` WHERE `keyName` = 'dbVersion'");
            $this->abortIf(26 !== $version, 'This database migration can only be used if the database version is 26! Install Part-DB 0.5.6 and update database there!');
        } catch (DBALException $dBALException) {
            //when the table was not found, then you can not use this migration
            $this->skipIf(true, 'Empty database detected. Skip migration.');
        }

        //Deactive SQL Modes (especially NO_ZERO_DATE, which prevents updating)
        $this->addSql("SET sql_mode = ''");

        //Rename attachment tables (fix typos)
        $this->addSql('RENAME TABLE `attachement_types` TO `attachment_types`;');
        $this->addSql('RENAME TABLE `attachements` TO `attachments`;');

        $this->addSql('CREATE TABLE currencies (id INT AUTO_INCREMENT NOT NULL, parent_id INT DEFAULT NULL, iso_code VARCHAR(255) NOT NULL, exchange_rate NUMERIC(11, 5) DEFAULT NULL, comment LONGTEXT NOT NULL, not_selectable TINYINT(1) NOT NULL, name VARCHAR(255) NOT NULL, last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, INDEX IDX_37C44693727ACA70 (parent_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE `measurement_units` (id INT AUTO_INCREMENT NOT NULL, parent_id INT DEFAULT NULL, unit VARCHAR(255) DEFAULT NULL, is_integer TINYINT(1) NOT NULL, use_si_prefix TINYINT(1) NOT NULL, comment LONGTEXT NOT NULL, not_selectable TINYINT(1) NOT NULL, name VARCHAR(255) NOT NULL, last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, INDEX IDX_F5AF83CF727ACA70 (parent_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE part_lots (id INT AUTO_INCREMENT NOT NULL, id_store_location INT DEFAULT NULL, id_part INT NOT NULL, description LONGTEXT NOT NULL, comment LONGTEXT NOT NULL, expiration_date DATETIME DEFAULT NULL, instock_unknown TINYINT(1) NOT NULL, amount DOUBLE PRECISION NOT NULL, needs_refill TINYINT(1) NOT NULL, last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, INDEX IDX_EBC8F9435D8F4B37 (id_store_location), INDEX IDX_EBC8F943C22F6CC4 (id_part), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');

        /* Migrate the part locations for parts with known instock */
        $this->addSql(
            'INSERT IGNORE INTO part_lots (id_part, id_store_location, amount, instock_unknown, last_modified, datetime_added) '.
            'SELECT parts.id, parts.id_storelocation,  parts.instock, 0, NOW(), NOW() FROM parts '.
            'WHERE parts.instock >= 0'
        );

        //Migrate part locations for parts with unknown instock
        $this->addSql(
            'INSERT IGNORE INTO part_lots (id_part, id_store_location, amount, instock_unknown, last_modified, datetime_added) '.
            'SELECT parts.id, parts.id_storelocation, 0, 1, NOW(), NOW() FROM parts '.
            'WHERE parts.instock = -2'
        );

        $this->addSql('ALTER TABLE currencies ADD CONSTRAINT FK_37C44693727ACA70 FOREIGN KEY (parent_id) REFERENCES currencies (id)');
        $this->addSql('ALTER TABLE `measurement_units` ADD CONSTRAINT FK_F5AF83CF727ACA70 FOREIGN KEY (parent_id) REFERENCES `measurement_units` (id)');
        $this->addSql('ALTER TABLE part_lots ADD CONSTRAINT FK_EBC8F9435D8F4B37 FOREIGN KEY (id_store_location) REFERENCES `storelocations` (id)');
        $this->addSql('ALTER TABLE part_lots ADD CONSTRAINT FK_EBC8F943C22F6CC4 FOREIGN KEY (id_part) REFERENCES `parts` (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE parts DROP INDEX parts_order_orderdetails_id_k, ADD UNIQUE INDEX UNIQ_6940A7FE81081E9B (order_orderdetails_id)');
        $this->addSql('ALTER TABLE parts DROP FOREIGN KEY parts_id_storelocation_fk');
        $this->addSql('DROP INDEX favorite ON parts');
        $this->addSql('DROP INDEX parts_id_storelocation_k ON parts');
        $this->addSql('ALTER TABLE parts DROP FOREIGN KEY parts_id_footprint_fk');
        $this->addSql('ALTER TABLE parts DROP FOREIGN KEY parts_id_manufacturer_fk');
        $this->addSql('ALTER TABLE parts CHANGE mininstock minamount DOUBLE PRECISION NOT NULL, ADD id_part_unit INT DEFAULT NULL, ADD manufacturer_product_number VARCHAR(255) NOT NULL, ADD manufacturing_status VARCHAR(255) DEFAULT NULL, ADD needs_review TINYINT(1) NOT NULL, ADD tags LONGTEXT NOT NULL, ADD mass DOUBLE PRECISION DEFAULT NULL, DROP instock, CHANGE id_category id_category INT NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE order_quantity order_quantity INT NOT NULL, CHANGE manual_order manual_order TINYINT(1) NOT NULL, CHANGE manufacturer_product_url manufacturer_product_url VARCHAR(255) NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CHANGE favorite favorite TINYINT(1) NOT NULL, DROP id_storelocation');
        $this->addSql('ALTER TABLE parts ADD CONSTRAINT FK_6940A7FE5697F554 FOREIGN KEY (id_category) REFERENCES `categories` (id)');
        $this->addSql('ALTER TABLE parts ADD CONSTRAINT FK_6940A7FEEBBCC786 FOREIGN KEY (id_master_picture_attachement) REFERENCES `attachments` (id)');
        $this->addSql('ALTER TABLE parts ADD CONSTRAINT FK_6940A7FE2626CEF9 FOREIGN KEY (id_part_unit) REFERENCES `measurement_units` (id)');
        $this->addSql('CREATE INDEX IDX_6940A7FEEBBCC786 ON parts (id_master_picture_attachement)');
        $this->addSql('CREATE INDEX IDX_6940A7FE2626CEF9 ON parts (id_part_unit)');
        $this->addSql('CREATE INDEX IDX_6940A7FE5697F554 ON parts (id_category)');
        $this->addSql('DROP INDEX parts_id_category_k ON parts');
        $this->addSql('DROP INDEX parts_id_footprint_k ON parts');
        $this->addSql('CREATE INDEX IDX_6940A7FE7E371A10 ON parts (id_footprint)');
        $this->addSql('DROP INDEX parts_id_manufacturer_k ON parts');
        $this->addSql('CREATE INDEX IDX_6940A7FE1ECB93AE ON parts (id_manufacturer)');
        $this->addSql('ALTER TABLE parts ADD CONSTRAINT parts_id_footprint_fk FOREIGN KEY (id_footprint) REFERENCES footprints (id)');
        $this->addSql('ALTER TABLE parts ADD CONSTRAINT parts_id_manufacturer_fk FOREIGN KEY (id_manufacturer) REFERENCES manufacturers (id)');
        $this->addSql('ALTER TABLE attachment_types DROP FOREIGN KEY attachement_types_parent_id_fk');
        $this->addSql('ALTER TABLE attachment_types ADD filetype_filter LONGTEXT NOT NULL, ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('DROP INDEX attachement_types_parent_id_k ON attachment_types');
        $this->addSql('CREATE INDEX IDX_EFAED719727ACA70 ON attachment_types (parent_id)');
        $this->addSql('ALTER TABLE attachment_types ADD CONSTRAINT attachement_types_parent_id_fk FOREIGN KEY (parent_id) REFERENCES attachment_types (id)');
        $this->addSql('ALTER TABLE categories DROP FOREIGN KEY categories_parent_id_fk');
        $this->addSql('ALTER TABLE categories ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE disable_footprints disable_footprints TINYINT(1) NOT NULL, CHANGE disable_manufacturers disable_manufacturers TINYINT(1) NOT NULL, CHANGE disable_autodatasheets disable_autodatasheets TINYINT(1) NOT NULL, CHANGE disable_properties disable_properties TINYINT(1) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('DROP INDEX categories_parent_id_k ON categories');
        $this->addSql('CREATE INDEX IDX_3AF34668727ACA70 ON categories (parent_id)');
        $this->addSql('ALTER TABLE categories ADD CONSTRAINT categories_parent_id_fk FOREIGN KEY (parent_id) REFERENCES categories (id)');
        $this->addSql('ALTER TABLE devices DROP FOREIGN KEY devices_parent_id_fk');
        $this->addSql('ALTER TABLE devices ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE order_quantity order_quantity INT NOT NULL, CHANGE order_only_missing_parts order_only_missing_parts TINYINT(1) NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CHANGE comment comment LONGTEXT NOT NULL');
        $this->addSql('DROP INDEX devices_parent_id_k ON devices');
        $this->addSql('CREATE INDEX IDX_11074E9A727ACA70 ON devices (parent_id)');
        $this->addSql('ALTER TABLE devices ADD CONSTRAINT devices_parent_id_fk FOREIGN KEY (parent_id) REFERENCES devices (id)');
        $this->addSql('ALTER TABLE footprints DROP FOREIGN KEY footprints_parent_id_fk');
        $this->addSql('ALTER TABLE footprints ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('DROP INDEX footprints_parent_id_k ON footprints');
        $this->addSql('CREATE INDEX IDX_A34D68A2727ACA70 ON footprints (parent_id)');
        $this->addSql('ALTER TABLE footprints ADD CONSTRAINT footprints_parent_id_fk FOREIGN KEY (parent_id) REFERENCES footprints (id)');
        $this->addSql('ALTER TABLE manufacturers DROP FOREIGN KEY manufacturers_parent_id_fk');
        $this->addSql('ALTER TABLE manufacturers ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE address address VARCHAR(255) NOT NULL, CHANGE phone_number phone_number VARCHAR(255) NOT NULL, CHANGE fax_number fax_number VARCHAR(255) NOT NULL, CHANGE email_address email_address VARCHAR(255) NOT NULL, CHANGE website website VARCHAR(255) NOT NULL, CHANGE auto_product_url auto_product_url VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('DROP INDEX manufacturers_parent_id_k ON manufacturers');
        $this->addSql('CREATE INDEX IDX_94565B12727ACA70 ON manufacturers (parent_id)');
        $this->addSql('ALTER TABLE manufacturers ADD CONSTRAINT manufacturers_parent_id_fk FOREIGN KEY (parent_id) REFERENCES manufacturers (id)');
        $this->addSql('ALTER TABLE storelocations DROP FOREIGN KEY storelocations_parent_id_fk');
        $this->addSql('ALTER TABLE storelocations ADD storage_type_id INT DEFAULT NULL, ADD only_single_part TINYINT(1) NOT NULL, ADD limit_to_existing_parts TINYINT(1) NOT NULL, ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE is_full is_full TINYINT(1) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('ALTER TABLE storelocations ADD CONSTRAINT FK_7517020B270BFF1 FOREIGN KEY (storage_type_id) REFERENCES `measurement_units` (id)');
        $this->addSql('CREATE INDEX IDX_7517020B270BFF1 ON storelocations (storage_type_id)');
        $this->addSql('DROP INDEX storelocations_parent_id_k ON storelocations');
        $this->addSql('CREATE INDEX IDX_7517020727ACA70 ON storelocations (parent_id)');
        $this->addSql('ALTER TABLE storelocations ADD CONSTRAINT storelocations_parent_id_fk FOREIGN KEY (parent_id) REFERENCES storelocations (id)');
        $this->addSql('ALTER TABLE suppliers DROP FOREIGN KEY suppliers_parent_id_fk');
        $this->addSql('ALTER TABLE suppliers ADD default_currency_id INT DEFAULT NULL, ADD shipping_costs NUMERIC(11, 5) DEFAULT NULL, ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE address address VARCHAR(255) NOT NULL, CHANGE phone_number phone_number VARCHAR(255) NOT NULL, CHANGE fax_number fax_number VARCHAR(255) NOT NULL, CHANGE email_address email_address VARCHAR(255) NOT NULL, CHANGE website website VARCHAR(255) NOT NULL, CHANGE auto_product_url auto_product_url VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('ALTER TABLE suppliers ADD CONSTRAINT FK_AC28B95CECD792C0 FOREIGN KEY (default_currency_id) REFERENCES currencies (id)');
        $this->addSql('CREATE INDEX IDX_AC28B95CECD792C0 ON suppliers (default_currency_id)');
        $this->addSql('DROP INDEX suppliers_parent_id_k ON suppliers');
        $this->addSql('CREATE INDEX IDX_AC28B95C727ACA70 ON suppliers (parent_id)');
        $this->addSql('ALTER TABLE suppliers ADD CONSTRAINT suppliers_parent_id_fk FOREIGN KEY (parent_id) REFERENCES suppliers (id)');
        $this->addSql('DROP INDEX attachements_class_name_k ON attachments');
        $this->addSql('ALTER TABLE attachments DROP FOREIGN KEY attachements_type_id_fk');
        $this->addSql('ALTER TABLE attachments ADD datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CHANGE type_id type_id INT DEFAULT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE filename filename VARCHAR(255) NOT NULL, CHANGE show_in_table show_in_table TINYINT(1) NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('ALTER TABLE attachments ADD CONSTRAINT FK_47C4FAD61F1F2A24 FOREIGN KEY (element_id) REFERENCES `parts` (id) ON DELETE CASCADE');
        $this->addSql('DROP INDEX attachements_type_id_fk ON attachments');
        $this->addSql('CREATE INDEX IDX_47C4FAD6C54C8C93 ON attachments (type_id)');
        $this->addSql('CREATE INDEX IDX_47C4FAD61F1F2A24 ON attachments (element_id)');
        $this->addSql('DROP INDEX attachements_element_id_k ON attachments');
        $this->addSql('ALTER TABLE attachments ADD CONSTRAINT attachements_type_id_fk FOREIGN KEY (type_id) REFERENCES attachment_types (id)');
        $this->addSql('ALTER TABLE users CHANGE name name VARCHAR(180) NOT NULL, CHANGE first_name first_name VARCHAR(255) DEFAULT NULL, CHANGE last_name last_name VARCHAR(255) DEFAULT NULL, CHANGE department department VARCHAR(255) DEFAULT NULL, CHANGE email email VARCHAR(255) DEFAULT NULL, CHANGE need_pw_change need_pw_change TINYINT(1) NOT NULL, CHANGE config_language config_language VARCHAR(255) DEFAULT NULL, CHANGE config_timezone config_timezone VARCHAR(255) DEFAULT NULL, CHANGE config_theme config_theme VARCHAR(255) DEFAULT NULL, CHANGE config_currency config_currency VARCHAR(255) NOT NULL, CHANGE perms_labels perms_labels INT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('ALTER TABLE users ADD CONSTRAINT FK_1483A5E9FE54D947 FOREIGN KEY (group_id) REFERENCES `groups` (id)');
        $this->addSql('CREATE INDEX IDX_1483A5E9FE54D947 ON users (group_id)');
        $this->addSql('DROP INDEX name ON users');
        $this->addSql('CREATE UNIQUE INDEX UNIQ_1483A5E95E237E06 ON users (name)');
        $this->addSql('DROP INDEX device_parts_combination_uk ON device_parts');
        $this->addSql('ALTER TABLE device_parts CHANGE id_part id_part INT DEFAULT NULL, CHANGE id_device id_device INT DEFAULT NULL, CHANGE quantity quantity INT NOT NULL');
        $this->addSql('ALTER TABLE device_parts ADD CONSTRAINT FK_AFC547992F180363 FOREIGN KEY (id_device) REFERENCES `devices` (id)');
        $this->addSql('ALTER TABLE device_parts ADD CONSTRAINT FK_AFC54799C22F6CC4 FOREIGN KEY (id_part) REFERENCES `parts` (id)');
        $this->addSql('CREATE INDEX IDX_AFC547992F180363 ON device_parts (id_device)');
        $this->addSql('DROP INDEX device_parts_id_device_k ON device_parts');
        $this->addSql('CREATE INDEX IDX_AFC54799C22F6CC4 ON device_parts (id_part)');
        $this->addSql('DROP INDEX device_parts_id_part_k ON device_parts');
        $this->addSql('ALTER TABLE orderdetails ADD last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CHANGE id_supplier id_supplier INT DEFAULT NULL, CHANGE supplierpartnr supplierpartnr VARCHAR(255) NOT NULL, CHANGE obsolete obsolete TINYINT(1) NOT NULL, CHANGE supplier_product_url supplier_product_url VARCHAR(255) NOT NULL');
        $this->addSql('ALTER TABLE orderdetails ADD CONSTRAINT FK_489AFCDC4CE34BEC FOREIGN KEY (part_id) REFERENCES `parts` (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE orderdetails ADD CONSTRAINT FK_489AFCDCCBF180EB FOREIGN KEY (id_supplier) REFERENCES `suppliers` (id)');
        $this->addSql('CREATE INDEX IDX_489AFCDC4CE34BEC ON orderdetails (part_id)');
        $this->addSql('DROP INDEX orderdetails_part_id_k ON orderdetails');
        $this->addSql('CREATE INDEX IDX_489AFCDCCBF180EB ON orderdetails (id_supplier)');
        $this->addSql('DROP INDEX orderdetails_id_supplier_k ON orderdetails');
        $this->addSql('DROP INDEX pricedetails_combination_uk ON pricedetails');
        $this->addSql('ALTER TABLE pricedetails ADD id_currency INT DEFAULT NULL, ADD datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CHANGE price price NUMERIC(11, 5) NOT NULL, CHANGE price_related_quantity price_related_quantity DOUBLE PRECISION NOT NULL, CHANGE min_discount_quantity min_discount_quantity DOUBLE PRECISION NOT NULL, CHANGE manual_input manual_input TINYINT(1) NOT NULL');
        $this->addSql('ALTER TABLE pricedetails ADD CONSTRAINT FK_C68C44594A01DDC7 FOREIGN KEY (orderdetails_id) REFERENCES `orderdetails` (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE pricedetails ADD CONSTRAINT FK_C68C4459398D64AA FOREIGN KEY (id_currency) REFERENCES currencies (id)');
        $this->addSql('CREATE INDEX IDX_C68C4459398D64AA ON pricedetails (id_currency)');
        $this->addSql('CREATE INDEX IDX_C68C44594A01DDC7 ON pricedetails (orderdetails_id)');
        $this->addSql('DROP INDEX pricedetails_orderdetails_id_k ON pricedetails');
        $this->addSql('DROP INDEX name ON groups');
        $this->addSql('ALTER TABLE groups ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE perms_labels perms_labels INT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL');
        $this->addSql('ALTER TABLE groups ADD CONSTRAINT FK_F06D3970727ACA70 FOREIGN KEY (parent_id) REFERENCES `groups` (id)');
        $this->addSql('CREATE INDEX IDX_F06D3970727ACA70 ON groups (parent_id)');

        //Fill empty timestamps with current date
        $tables = ['attachments', 'attachment_types', 'categories', 'devices', 'footprints', 'manufacturers',
            'orderdetails', 'pricedetails', 'storelocations', 'suppliers', ];

        foreach ($tables as $table) {
            $this->addSql("UPDATE ${table} SET datetime_added = NOW() WHERE datetime_added = '0000-00-00 00:00:00'");
            $this->addSql("UPDATE ${table} SET last_modified = datetime_added WHERE last_modified = '0000-00-00 00:00:00'");
        }

        //Set the dbVersion to a high value, to prevent the old Part-DB versions to upgrade DB!
        $this->addSql("UPDATE `internal` SET `keyValue` = '99' WHERE `internal`.`keyName` = 'dbVersion'");
    }