Updating from DB: 1.4.5 > Current - Breaks ITFlow

opensourcefollower Any easy way to update the DB?

Fixed - Ran - itflow_updates_1.4.5_to_1.5.9.sql and it worked. Thanks.

-- Update from 1.4.5 to 1.4.6

CREATE TABLE `custom_links` (

`custom_link_id` INT(11) NOT NULL AUTO_INCREMENT,

`custom_link_name` VARCHAR(200) NOT NULL,

`custom_link_description` TEXT DEFAULT NULL,

`custom_link_uri` VARCHAR(500) NOT NULL,

`custom_link_icon` VARCHAR(200) DEFAULT NULL,

`custom_link_created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

`custom_link_updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP NULL,

`custom_link_archived_at` DATETIME NULL,

PRIMARY KEY (`custom_link_id`)

);

UPDATE `settings` SET `config_current_database_version` = '1.4.6';

-- Update from 1.4.6 to 1.4.7

ALTER TABLE `documents` ADD `document_client_visible` INT(11) NOT NULL DEFAULT '1' AFTER `document_parent`;

UPDATE `settings` SET `config_current_database_version` = '1.4.7';

-- Update from 1.4.7 to 1.4.8

ALTER TABLE `settings` ADD `config_invoice_paid_notification_email` VARCHAR(200) DEFAULT NULL AFTER `config_invoice_late_fee_percent`;

UPDATE `settings` SET `config_current_database_version` = '1.4.8';

-- Update from 1.4.8 to 1.4.9

ALTER TABLE `settings` DROP `config_stripe_client_pays_fees`;

UPDATE `settings` SET `config_current_database_version` = '1.4.9';

-- Update from 1.4.9 to 1.5.0

ALTER TABLE `user_roles` ADD `user_role_is_admin` INT(11) NOT NULL DEFAULT '0' AFTER `user_role_description`;

UPDATE `user_roles` SET `user_role_is_admin` = '1' WHERE `user_role_id` = 3;

CREATE TABLE `modules` (

`module_id` INT(11) NOT NULL AUTO_INCREMENT,

`module_name` VARCHAR(200) NOT NULL,

`module_description` VARCHAR(200) NULL,

PRIMARY KEY (`module_id`)

);

INSERT INTO modules SET module_name = 'module_client', module_description = 'General client & contact management';

INSERT INTO modules SET module_name = 'module_support', module_description = 'Access to ticketing, assets and documentation';

INSERT INTO modules SET module_name = 'module_credential', module_description = 'Access to client credentials - usernames, passwords and 2FA codes';

INSERT INTO modules SET module_name = 'module_sales', module_description = 'Access to quotes, invoices and products';

INSERT INTO modules SET module_name = 'module_financial', module_description = 'Access to payments, accounts, expenses and budgets';

INSERT INTO modules SET module_name = 'module_reporting', module_description = 'Access to all reports';

CREATE TABLE `user_role_permissions` (

`user_role_id` INT(11) NOT NULL,

`module_id` INT(11) NOT NULL,

`user_role_permission_level` INT(11) NOT NULL

);

INSERT INTO user_role_permissions SET user_role_id = 1, module_id = 1, user_role_permission_level = 1;

INSERT INTO user_role_permissions SET user_role_id = 1, module_id = 2, user_role_permission_level = 1;

INSERT INTO user_role_permissions SET user_role_id = 1, module_id = 4, user_role_permission_level = 1;

INSERT INTO user_role_permissions SET user_role_id = 1, module_id = 5, user_role_permission_level = 2;

INSERT INTO user_role_permissions SET user_role_id = 1, module_id = 6, user_role_permission_level = 1;

INSERT INTO user_role_permissions SET user_role_id = 2, module_id = 1, user_role_permission_level = 2;

INSERT INTO user_role_permissions SET user_role_id = 2, module_id = 2, user_role_permission_level = 2;

INSERT INTO user_role_permissions SET user_role_id = 2, module_id = 3, user_role_permission_level = 2;

INSERT INTO user_role_permissions SET user_role_id = 2, module_id = 4, user_role_permission_level = 2;

UPDATE `settings` SET `config_current_database_version` = '1.5.0';

-- Update from 1.5.0 to 1.5.1

DROP TABLE `account_types`;

ALTER TABLE `accounts` ADD `account_description` VARCHAR(250) DEFAULT NULL AFTER `account_name`;

ALTER TABLE `user_roles` MODIFY `user_role_is_admin` TINYINT(1) NOT NULL DEFAULT '0';

ALTER TABLE `shared_items` ADD `item_recipient` VARCHAR(250) DEFAULT NULL AFTER `item_note`;

UPDATE `settings` SET `config_current_database_version` = '1.5.1';

-- Update from 1.5.1 to 1.5.2

ALTER TABLE `custom_links` ADD `custom_link_location` INT(11) NOT NULL DEFAULT 1 AFTER `custom_link_icon`;

ALTER TABLE `custom_links` ADD `custom_link_new_tab` TINYINT(1) NOT NULL DEFAULT 0 AFTER `custom_link_uri`;

ALTER TABLE `custom_links` ADD `custom_link_order` INT(11) NOT NULL DEFAULT 0 AFTER `custom_link_location`;

UPDATE `settings` SET `config_current_database_version` = '1.5.2';

-- Update from 1.5.2 to 1.5.3

ALTER TABLE `settings` ADD `config_invoice_paid_notification_email` VARCHAR(200) DEFAULT NULL AFTER `config_invoice_late_fee_percent`;

UPDATE `settings` SET `config_current_database_version` = '1.5.3';

-- Update from 1.5.3 to 1.5.4

ALTER TABLE `users` ADD `user_type` TINYINT(1) NOT NULL DEFAULT 1 AFTER `user_password`;

UPDATE `settings` SET `config_current_database_version` = '1.5.4';

-- Update from 1.5.4 to 1.5.5

ALTER TABLE `user_roles` ADD `user_role_type` TINYINT(1) NOT NULL DEFAULT 1 AFTER `user_role_description`;

UPDATE `settings` SET `config_current_database_version` = '1.5.5';

-- Update from 1.5.5 to 1.5.6

ALTER TABLE `contacts` ADD `contact_user_id` INT(11) NOT NULL DEFAULT 0 AFTER `contact_vendor_id`;

UPDATE `settings` SET `config_current_database_version` = '1.5.6';

-- Update from 1.5.6 to 1.5.7

ALTER TABLE `users` ADD `user_auth_method` VARCHAR(200) NOT NULL DEFAULT 'local' AFTER `user_password`;

UPDATE `settings` SET `config_current_database_version` = '1.5.7';

-- Update from 1.5.7 to 1.5.8

-- Migrate contacts with logins to the users table

INSERT INTO users (user_name, user_email, user_password, user_auth_method, user_type)

SELECT contact_name, contact_email, contact_password_hash, contact_auth_method, 2

FROM contacts WHERE contact_archived_at IS NULL AND (contact_auth_method = 'local' OR contact_auth_method = 'azure');

UPDATE contacts c JOIN users u ON c.contact_email = u.user_email SET c.contact_user_id = u.user_id;

ALTER TABLE `contacts` DROP `contact_auth_method`, DROP `contact_password_hash`, DROP `contact_password_reset_token`, DROP `contact_token_expire`;

ALTER TABLE `users` ADD `user_password_reset_token` VARCHAR(200) NULL DEFAULT NULL AFTER `user_token`;

ALTER TABLE `users` ADD `user_password_reset_token_expire` DATETIME NULL DEFAULT NULL AFTER `user_password_reset_token`;

UPDATE `settings` SET `config_current_database_version` = '1.5.8';

-- Update from 1.5.8 to 1.5.9

ALTER TABLE `tasks` ADD `task_completion_estimate` INT(11) NOT NULL DEFAULT 0 AFTER `task_order`;

ALTER TABLE `task_templates` ADD `task_template_completion_estimate` INT(11) NOT NULL DEFAULT 0 AFTER `task_template_order`;

UPDATE `settings` SET `config_current_database_version` = '1.5.9';