Synch sqls with upgrade sqls

[apmuthu]

Some SQL statements in the default.sql and demo.sql files listed as version 4.14.1 are duplicated in the upgrade to version 4.15 as listed in upgrade4.14.1-4.14.2.sql. These need to be synched with:

--- mysql/country_sql/default.sql	Sat Mar 31 06:09:13 2018
+++ mysql/country_sql/default.sql	Sat Mar 31 14:54:16 2018
@@ -2920,12 +2920,10 @@
   `expirationdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `quantity` double NOT NULL DEFAULT '0',
   `qualitytext` text NOT NULL,
-  `createdate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`stockid`,`serialno`,`loccode`),
   KEY `StockID` (`stockid`),
   KEY `LocCode` (`loccode`),
   KEY `serialno` (`serialno`),
-  KEY `createdate` (`createdate`),
   CONSTRAINT `stockserialitems_ibfk_1` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`),
   CONSTRAINT `stockserialitems_ibfk_2` FOREIGN KEY (`loccode`) REFERENCES `locations` (`loccode`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--- mysql/country_sql/demo.sql	Sat Mar 31 06:09:13 2018
+++ mysql/country_sql/demo.sql	Sat Mar 31 14:54:28 2018
@@ -2922,12 +2922,10 @@
   `expirationdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `quantity` double NOT NULL DEFAULT '0',
   `qualitytext` text NOT NULL,
-  `createdate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`stockid`,`serialno`,`loccode`),
   KEY `StockID` (`stockid`),
   KEY `LocCode` (`loccode`),
   KEY `serialno` (`serialno`),
-  KEY `createdate` (`createdate`),
   CONSTRAINT `stockserialitems_ibfk_1` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`),
   CONSTRAINT `stockserialitems_ibfk_2` FOREIGN KEY (`loccode`) REFERENCES `locations` (`loccode`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--- mysql/upgrade4.14.1-4.14.2.sql	Sat Mar 31 06:09:13 2018
+++ mysql/upgrade4.14.1-4.14.2.sql	Sat Mar 31 15:02:59 2018
@@ -11,7 +11,7 @@
 UPDATE `pctabs` SET authorizerexpenses=authorizer;
 ALTER TABLE `pcashdetails` ADD COLUMN `tag` INT(11) NOT NULL DEFAULT 0 AFTER `tabcode`;
 INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('PcAuthorizeCash.php', '6', 'Authorisation of assigned cash');
-INSERT INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('Z_RemovePurchaseBackOrders.php', '1', 'Removes all purchase order back orders');
+INSERT IGNORE INTO `scripts` (`script`, `pagesecurity`, `description`) VALUES ('Z_RemovePurchaseBackOrders.php', '1', 'Removes all purchase order back orders');
 CREATE TABLE `pcashdetailtaxes` (
 	`counterindex` INT(20) NOT NULL AUTO_INCREMENT,
 	`pccashdetail` INT(20) NOT NULL DEFAULT 0,
@@ -31,7 +31,7 @@
 ALTER TABLE pcashdetails MODIFY receipt text COMMENT 'Column redundant. Replaced by receipt file upload. Nov 2017.';
 INSERT INTO `scripts` (`script` ,`pagesecurity` ,`description`) VALUES ('BankAccountBalances.php',  '1',  'Shows bank accounts authorised for with balances');
 
-ALTER TABLE `stockserialitems` ADD `createdate` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX ( `createdate` );
+ALTER TABLE `stockserialitems` ADD `createdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX ( `createdate` );
 UPDATE stockserialitems SET createdate = NULL;
 
 UPDATE stockserialitems as stockserialitems SET createdate = 
@@ -74,6 +74,7 @@
 	key (`shipdate`),
 	key (`status`),
 	key (`closed`),
+	key (`loccode`),
 	CONSTRAINT FOREIGN KEY(`loccode`) REFERENCES `locations`(`loccode`),
 	constraint foreign key (`orderno`) REFERENCES salesorders(`orderno`)
 ) Engine=InnoDB DEFAULT CHARSET=utf8;
@@ -89,6 +90,7 @@
 	`shipqty` double not null default '0',
 	PRIMARY KEY (`detailno`),
 	key (`prid`),
+	key (`stockid`),
 	constraint foreign key (`stockid`) REFERENCES stockmaster(`stockid`),
 	constraint foreign key (`prid`) REFERENCES pickreq(`prid`)
 ) Engine=InnoDB DEFAULT CHARSET=utf8; 

Such anomalies cause a freeze during installation and the above rectifies it.

weberp_sql_diffs.zip

[apmuthu]

The new version of the sqls for WebERP is attached herewith. This synchs the order of the tables and insert statements for ease of comparison between the default.sql and the demo.sql.

WebERP_sql_fixes.zip

[timschofield]

Thanks, I have applied these fixes,

Tim

[apmuthu]

Further fixes here now make for easy and error-free installation.
weberp_sql_fixes_2.zip

[timschofield]

Applied

Tim