Feature Request - Database integrity tests

[fooman]

This is a continuation of learning from M1.

With Magento 1's sql installation routine it is possible to create a disconnect between a fresh installation and an upgrade (since the upgrade code can be different to the install code - not sure how this is handled in M2). These differences can create subtly different databases. For example the following is a very common example when comparing a 1.3.2.4 installation upgraded to 1.9.1.0 vs. a direct install of 1.9.1.0:

`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Id',

vs.

`store_id` smallint(5) unsigned NOT NULL COMMENT 'Store Id',

Or

`title` varchar(255) DEFAULT NULL COMMENT 'Title',

vs.

`title` varchar(255) NOT NULL COMMENT 'Title',

Or actual value content

INSERT INTO `eav_entity_type` VALUES 
(1,'customer','customer/customer','customer/attribute','customer/entity','','',1,'default',1,'eav/entity_increment_numeric',0,8,'0','customer/eav_attribute','customer/attribute_collection'),
(2,'customer_address','customer/address','customer/attribute','customer/address_entity','','',1,'default',2,'',0,8,'0','customer/eav_attribute','customer/address_attribute_collection'),
(3,'catalog_category','catalog/category','catalog/resource_eav_attribute','catalog/category','','',1,'default',3,'',0,8,'0','catalog/eav_attribute','catalog/category_attribute_collection'),
(4,'catalog_product','catalog/product','catalog/resource_eav_attribute','catalog/product','','',1,'default',4,'',0,8,'0','catalog/eav_attribute','catalog/product_attribute_collection'),
(5,'quote','sales/quote','','sales/quote','','',1,'default',5,'',0,8,'0','',''),
(6,'quote_item','sales/quote_item','','sales/quote_item','','',1,'default',6,'',0,8,'0','',''),
(7,'quote_address','sales/quote_address','','sales/quote_address','','',1,'default',7,'',0,8,'0','',''),
(8,'quote_address_item','sales/quote_address_item','','sales/quote_entity','','',1,'default',8,'',0,8,'0','',''),
(9,'quote_address_rate','sales/quote_address_rate','','sales/quote_entity','','',1,'default',9,'',0,8,'0','',''),
(10,'quote_payment','sales/quote_payment','','sales/quote_entity','','',1,'default',10,'',0,8,'0','',''),
(11,'order','sales/order','','sales/order','','',1,'default',11,'eav/entity_increment_numeric',1,8,'0','',''),
(12,'order_address','sales/order_address','','sales/order_entity','','',1,'default',12,'',0,8,'0','',''),
(13,'order_item','sales/order_item','','sales/order_entity','','',1,'default',13,'',0,8,'0','',''),
(14,'order_payment','sales/order_payment','','sales/order_entity','','',1,'default',14,'',0,8,'0','',''),
(15,'order_status_history','sales/order_status_history','','sales/order_entity','','',1,'default',15,'',0,8,'0','',''),
(16,'invoice','sales/order_invoice','','sales/order_entity','','',1,'default',16,'eav/entity_increment_numeric',1,8,'0','',''),
(17,'invoice_item','sales/order_invoice_item','','sales/order_entity','','',1,'default',17,'',0,8,'0','',''),
(18,'invoice_comment','sales/order_invoice_comment','','sales/order_entity','','',1,'default',18,'',0,8,'0','',''),
(19,'shipment','sales/order_shipment','','sales/order_entity','','',1,'default',19,'eav/entity_increment_numeric',1,8,'0','',''),
(20,'shipment_item','sales/order_shipment_item','','sales/order_entity','','',1,'default',20,'',0,8,'0','',''),
(21,'shipment_comment','sales/order_shipment_comment','','sales/order_entity','','',1,'default',21,'',0,8,'0','',''),
(22,'shipment_track','sales/order_shipment_track','','sales/order_entity','','',1,'default',22,'',0,8,'0','',''),
(23,'creditmemo','sales/order_creditmemo','','sales/order_entity','','',1,'default',23,'eav/entity_increment_numeric',1,8,'0','',''),
(24,'creditmemo_item','sales/order_creditmemo_item','','sales/order_entity','','',1,'default',24,'',0,8,'0','',''),
(25,'creditmemo_comment','sales/order_creditmemo_comment','','sales/order_entity','','',1,'default',25,'',0,8,'0','','');

vs.

INSERT INTO `eav_entity_type` VALUES 
(1,'customer','customer/customer','customer/attribute','customer/entity',NULL,NULL,1,'default',1,'eav/entity_increment_numeric',0,8,'0','customer/eav_attribute','customer/attribute_collection'),
(2,'customer_address','customer/address','customer/attribute','customer/address_entity',NULL,NULL,1,'default',2,NULL,0,8,'0','customer/eav_attribute','customer/address_attribute_collection'),
(3,'catalog_category','catalog/category','catalog/resource_eav_attribute','catalog/category',NULL,NULL,1,'default',3,NULL,0,8,'0','catalog/eav_attribute','catalog/category_attribute_collection'),
(4,'catalog_product','catalog/product','catalog/resource_eav_attribute','catalog/product',NULL,NULL,1,'default',4,NULL,0,8,'0','catalog/eav_attribute','catalog/product_attribute_collection'),
(5,'order','sales/order',NULL,'sales/order',NULL,NULL,1,'default',0,'eav/entity_increment_numeric',1,8,'0',NULL,NULL),
(6,'invoice','sales/order_invoice',NULL,'sales/invoice',NULL,NULL,1,'default',0,'eav/entity_increment_numeric',1,8,'0',NULL,NULL),
(7,'creditmemo','sales/order_creditmemo',NULL,'sales/creditmemo',NULL,NULL,1,'default',0,'eav/entity_increment_numeric',1,8,'0',NULL,NULL),
(8,'shipment','sales/order_shipment',NULL,'sales/shipment',NULL,NULL,1,'default',0,'eav/entity_increment_numeric',1,8,'0',NULL,NULL);

A few more interesting tables eav_attribute or eav_form_fieldset.

While I am not aware of any current bugs caused by the above it is only a matter of time until it will create hard to track issues (ie someone relying on a default value which is different depending on upgrade path/history).

For Magento2 I would like ebay to adopt something like the following test:

1.) Install Version N-1
2.) Run Integration tests or something else that creates meaningful data (product, customer, order, run indexer)
3.) Upgrade to Version N
4.) Take database snapshot
5.) Install Version N into a fresh database
6.) Run the same tests as 2.)
7.) Take another database snapshot
8.) Compare 7.) and 4.) - my expectation here is that these are functionally the same (ie excluding any date/time fields)

[choukalos]

Assigning Eugene to consider in context of the Magento 2 upgrade tool. Talking with @TexanHogman sounds like this should really be considered a bug for each module that's doing this and assigned to those teams to ensure consistency in the schema upgrade scripts.

[fooman]

Thanks Chuck - if you wanted to solve it on an architectural level I suppose one could try to do away with different scripts for upgrade vs install as this is a duplication in itself.

[fooman]

Bug #4785 / #5426 could have been caught with this.

[antonkril]

We have implemented the test and run it to compare DB schemas.

Closing this issue. Please reopen if you have additional questions