pb update 1.4.7 to 1.5.0

Trying to manually update the sql using the commands from the php as shown below errors on the JSON_SET because the JSON functions were added to MySQL 5.7 and MariaDB 10.2.3. In order to upgrade to phpIPAM 1.5.0 will require setting up a MySQL 5.7+ or a MariaDB >10.2.3 server but the builtin package on CentOS 7 is MariaDB 5.5.68.
# php functions/upgrade_queries.php 1.5.0

/* VERSION 1.5.26 */
ALTER TABLE `customers` CHANGE `postcode` `postcode` VARCHAR(32)  NULL  DEFAULT NULL;
-- Database version bump
UPDATE `settings` set `dbversion` = '26';

/* VERSION 1.5.27 */
UPDATE `ipaddresses` SET `is_gateway` = DEFAULT  WHERE `is_gateway` IS NULL;
UPDATE `ipaddresses` SET `excludePing` = DEFAULT WHERE `excludePing` IS NULL;
UPDATE `ipaddresses` SET `PTRignore` = DEFAULT   WHERE `PTRignore` IS NULL;
ALTER TABLE `ipaddresses` CHANGE `is_gateway` `is_gateway` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `ipaddresses` CHANGE `excludePing` `excludePing` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `ipaddresses` CHANGE `PTRignore` `PTRignore` BOOL NOT NULL DEFAULT '0';
UPDATE `subnets` SET `allowRequests` = DEFAULT  WHERE `allowRequests` IS NULL;
UPDATE `subnets` SET `showName` = DEFAULT       WHERE `showName` IS NULL;
UPDATE `subnets` SET `pingSubnet` = DEFAULT     WHERE `pingSubnet` IS NULL;
UPDATE `subnets` SET `discoverSubnet` = DEFAULT WHERE `discoverSubnet` IS NULL;
UPDATE `subnets` SET `resolveDNS` = DEFAULT     WHERE `resolveDNS` IS NULL;
UPDATE `subnets` SET `DNSrecursive` = DEFAULT   WHERE `DNSrecursive` IS NULL;
UPDATE `subnets` SET `DNSrecords` = DEFAULT     WHERE `DNSrecords` IS NULL;
UPDATE `subnets` SET `isFull` = DEFAULT         WHERE `isFull` IS NULL;
UPDATE `subnets` SET `isFolder` = DEFAULT       WHERE `isFolder` IS NULL;
ALTER TABLE `subnets` CHANGE `allowRequests` `allowRequests` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `subnets` CHANGE `showName` `showName` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `subnets` CHANGE `pingSubnet` `pingSubnet` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `subnets` CHANGE `discoverSubnet` `discoverSubnet` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `subnets` CHANGE `resolveDNS` `resolveDNS` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `subnets` CHANGE `DNSrecursive` `DNSrecursive` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `subnets` CHANGE `DNSrecords` `DNSrecords` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `subnets` CHANGE `isFull` `isFull` BOOL NOT NULL DEFAULT '0';
ALTER TABLE `subnets` CHANGE `isFolder` `isFolder` BOOL NOT NULL DEFAULT '0';
-- Database version bump
UPDATE `settings` set `dbversion` = '27';

/* VERSION 1.5.28 */
ALTER TABLE `subnets` ADD `isPool` BOOL NOT NULL DEFAULT '0';
-- Database version bump
UPDATE `settings` set `dbversion` = '28';

/* VERSION 1.5.29 */
ALTER TABLE `sections` ADD `showSubnet` BOOL NOT NULL DEFAULT '1';
-- Database version bump
UPDATE `settings` set `dbversion` = '29';

/* VERSION 1.5.30 */
-- Add Italian translation
INSERT INTO `lang` (`l_name`, `l_code`) VALUES ('Italian', 'it_IT.UTF-8');
-- Database version bump
UPDATE `settings` set `dbversion` = '30';

/* VERSION 1.5.31 */
ALTER TABLE `users` CHANGE `module_permissions` `module_permissions` varchar(255) COLLATE utf8_bin DEFAULT '{"vlan":"1","l2dom":"1","vrf":"1","pdns":"1","circuits":"1","racks":"1","nat":"1","pstn":"1","customers":"1","locations":"1","devices":"1"}';
-- Clone users l2dom permissions from existing vlan permission level. MySQL5.7+
UPDATE users SET module_permissions = JSON_SET(module_permissions,'$.l2dom', JSON_EXTRACT(module_permissions,'$.vlan')); -- IGNORE_ON_FAILURE
-- Database version bump
UPDATE `settings` set `dbversion` = '31';

/* VERSION 1.5.32 */
ALTER TABLE `settings` CHANGE `scanPingType` `scanPingType` ENUM('none','ping','pear','fping') NOT NULL DEFAULT 'ping';
ALTER TABLE `settings` CHANGE `prettyLinks` `prettyLinks` ENUM('Yes','No') NOT NULL DEFAULT 'No';
ALTER TABLE `settings` CHANGE `log` `log` ENUM('Database','syslog', 'both') NOT NULL DEFAULT 'Database';
ALTER TABLE `settings` CHANGE `2fa_provider` `2fa_provider` ENUM('none','Google_Authenticator') NULL DEFAULT 'none';
-- Database version bump
UPDATE `settings` set `dbversion` = '32';

/* VERSION 1.5.33 */
UPDATE `usersAuthMethod` SET `type` = 'local' WHERE id = 1;
UPDATE `usersAuthMethod` SET `type` = 'http'  WHERE id = 2;
ALTER TABLE `usersAuthMethod` CHANGE `type` `type` ENUM('local','http','AD','LDAP','NetIQ','Radius','SAML2') NOT NULL DEFAULT 'local';
ALTER TABLE `usersAuthMethod` CHANGE `params` `params` text DEFAULT NULL;
ALTER TABLE `usersAuthMethod` CHANGE `protected` `protected` ENUM('Yes','No') NOT NULL DEFAULT 'Yes';
-- Database version bump
UPDATE `settings` set `dbversion` = '33';

/* VERSION 1.5.34 */
ALTER TABLE `customers` CHANGE `contact_mail` `contact_mail` varchar(254) DEFAULT NULL;
ALTER TABLE `settings` CHANGE `siteAdminMail` `siteAdminMail` varchar(254) DEFAULT NULL;
ALTER TABLE `settingsMail` CHANGE `mtype` `mtype` ENUM('localhost','smtp') NOT NULL DEFAULT 'localhost';
ALTER TABLE `settingsMail` CHANGE `msecure` `msecure` ENUM('none','ssl','tls')  NOT NULL  DEFAULT 'none';
ALTER TABLE `settingsMail` CHANGE `mauth` `mauth` ENUM('yes','no') NOT NULL DEFAULT 'no';
ALTER TABLE `settingsMail` CHANGE `muser` `muser` varchar(254) DEFAULT NULL;
ALTER TABLE `settingsMail` CHANGE `mpass` `mpass` varchar(128) DEFAULT NULL;
ALTER TABLE `settingsMail` CHANGE `mAdminName` `mAdminName` varchar(128) DEFAULT NULL;
ALTER TABLE `settingsMail` CHANGE `mAdminMail` `mAdminMail` varchar(254) DEFAULT NULL;
UPDATE `users` SET `mailNotify`='No' WHERE `mailNotify` IS NULL;
UPDATE `users` SET `mailChangelog`='No' WHERE `mailChangelog` IS NULL;
UPDATE `users` SET `menuType`='Dynamic' WHERE `menuType` IS NULL;
ALTER TABLE `users` CHANGE `disabled` `disabled` ENUM('Yes','No')  NOT NULL  DEFAULT 'No';
ALTER TABLE `users` CHANGE `mailNotify` `mailNotify` ENUM('Yes','No')  NOT NULL  DEFAULT 'No';
ALTER TABLE `users` CHANGE `mailChangelog` `mailChangelog` ENUM('Yes','No')  NOT NULL  DEFAULT 'No';
ALTER TABLE `users` CHANGE `passChange` `passChange` ENUM('Yes','No')  NOT NULL  DEFAULT 'No';
ALTER TABLE `users` CHANGE `compressOverride` `compressOverride` ENUM('default','Uncompress') NOT NULL DEFAULT 'default';
ALTER TABLE `users` CHANGE `menuType` `menuType` ENUM('Static','Dynamic')  NOT NULL  DEFAULT 'Dynamic';
ALTER TABLE `users` CHANGE `email` `email` varchar(254) CHARACTER SET utf8 DEFAULT NULL;
-- Database version bump
UPDATE `settings` set `dbversion` = '34';

/* VERSION 1.5.35 */
ALTER TABLE `requests` ADD `mac` varchar(20) DEFAULT NULL;
-- Database version bump
UPDATE `settings` set `dbversion` = '35';

/* VERSION 1.5.36 */
ALTER TABLE `settings` ADD `enableVaults` TINYINT(1)  NOT NULL  DEFAULT '1';
CREATE TABLE `vaults` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `type` enum('passwords','certificates') NOT NULL DEFAULT 'passwords',
  `description` text,
  `test` char(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `vaultItems` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `vaultId` int(11) unsigned NOT NULL,
  `type` enum('password','certificate') NOT NULL DEFAULT 'password',
  `type_certificate` enum('public','pkcs12','certificate','website') NOT NULL DEFAULT 'public',
  `values` text,
  PRIMARY KEY (`id`),
  KEY `vaultId` (`vaultId`),
  CONSTRAINT `vaultItems_ibfk_1` FOREIGN KEY (`vaultId`) REFERENCES `vaults` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Database version bump
UPDATE `settings` set `dbversion` = '36';

/* VERSION 1.5.37 */
ALTER TABLE `customers` CHANGE `lat`  `lat`  varchar(31) DEFAULT NULL;
ALTER TABLE `customers` CHANGE `long` `long` varchar(31) DEFAULT NULL;
ALTER TABLE `locations` CHANGE `lat`  `lat`  varchar(31) DEFAULT NULL;
ALTER TABLE `locations` CHANGE `long` `long` varchar(31) DEFAULT NULL;
CREATE TABLE `nominatim` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `nominatim` (`id`, `url`) VALUES (1, 'https://nominatim.openstreetmap.org/search');
CREATE TABLE `nominatim_cache` (
  `sha256` binary(32) NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  `query` text NOT NULL,
  `lat_lng` text NOT NULL,
  PRIMARY KEY (`sha256`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Database version bump
UPDATE `settings` set `dbversion` = '37';

/* VERSION 1.5.38 */
ALTER TABLE `logs` CHANGE `command` `command` text DEFAULT NULL;
ALTER TABLE `logs` CHANGE `details` `details` text DEFAULT NULL;
ALTER TABLE `changelog` CHANGE `caction` `caction` ENUM('add','edit','delete','truncate','resize','perm_change') NOT NULL DEFAULT 'edit';
ALTER TABLE `changelog` CHANGE `cresult` `cresult` ENUM('error','success') NOT NULL DEFAULT 'success';
ALTER TABLE `changelog` CHANGE `cdiff` `cdiff` text DEFAULT NULL;
ALTER TABLE `users` CHANGE `module_permissions` `module_permissions` varchar(255) DEFAULT '{"vlan":"1","l2dom":"1","vrf":"1","pdns":"1","circuits":"1","racks":"1","nat":"1","pstn":"1","customers":"1","locations":"1","devices":"1","routing":"1","vaults":"1"}';
ALTER TABLE `firewallZoneSubnet` ADD PRIMARY KEY (`zoneId`,`subnetId`);
ALTER TABLE `circuitsLogicalMapping` ADD PRIMARY KEY (`logicalCircuit_id`, `circuit_id`);
-- Database version bump
UPDATE `settings` set `dbversion` = '38';

/* VERSION 1.5.39 */
INSERT INTO `widgets` (`wtitle`, `wdescription`, `wfile`, `wparams`, `whref`, `wsize`, `wadminonly`, `wactive`) VALUES ('MAC lookup', 'Shows MAC address vendor', 'mac-lookup', NULL, 'yes', '6', 'no', 'yes');
-- Database version bump
UPDATE `settings` set `dbversion` = '39';
phpipam / phpipam

pb update 1.4.7 to 1.5.0 #3579
