43 lines
1.9 KiB
SQL
43 lines
1.9 KiB
SQL
-- Sub 2 — Post-migration verification. Run on local dev DB after upgrade.
|
|
-- Every SELECT should return 0 rows (anomalies). If any return > 0, migration has gaps.
|
|
|
|
-- 1. No part should have an empty part_number
|
|
SELECT id, name FROM fp_part_catalog
|
|
WHERE (part_number IS NULL OR part_number = '') AND active = TRUE;
|
|
|
|
-- 2. No part should have an empty revision
|
|
SELECT id, name, part_number FROM fp_part_catalog
|
|
WHERE (revision IS NULL OR revision = '') AND active = TRUE;
|
|
|
|
-- 3. Every description-template row with text in `description` should have
|
|
-- both internal_description and customer_facing_description populated
|
|
SELECT id, name FROM fp_sale_description_template
|
|
WHERE description IS NOT NULL
|
|
AND description <> ''
|
|
AND (
|
|
internal_description IS NULL OR internal_description = ''
|
|
OR customer_facing_description IS NULL OR customer_facing_description = ''
|
|
);
|
|
|
|
-- 4. Every SO line with a non-empty `name` should have x_fc_internal_description set
|
|
SELECT id, order_id FROM sale_order_line
|
|
WHERE (name IS NOT NULL AND name <> '')
|
|
AND (x_fc_internal_description IS NULL OR x_fc_internal_description = '');
|
|
|
|
-- 5. Every part should have certificate_requirement set (default 'inherit')
|
|
SELECT id, name FROM fp_part_catalog
|
|
WHERE certificate_requirement IS NULL;
|
|
|
|
-- 6. Count summary (informational)
|
|
SELECT 'parts_total' AS metric, COUNT(*) AS value FROM fp_part_catalog
|
|
UNION ALL
|
|
SELECT 'parts_inherit_cert', COUNT(*) FROM fp_part_catalog WHERE certificate_requirement = 'inherit'
|
|
UNION ALL
|
|
SELECT 'description_templates_total', COUNT(*) FROM fp_sale_description_template
|
|
UNION ALL
|
|
SELECT 'description_templates_with_both', COUNT(*) FROM fp_sale_description_template
|
|
WHERE internal_description IS NOT NULL AND customer_facing_description IS NOT NULL
|
|
UNION ALL
|
|
SELECT 'so_lines_with_internal_desc', COUNT(*) FROM sale_order_line
|
|
WHERE x_fc_internal_description IS NOT NULL AND x_fc_internal_description <> '';
|