From 7d25d598940652b2ae1ae4acad47588d5ec81bec Mon Sep 17 00:00:00 2001 From: Olaf Buddenhagen Date: Fri, 10 Apr 2015 00:16:54 +0200 Subject: [PATCH] Upgrade: Fix `value` for added `option_value` entries in various places In several places, the highest existing option value for a group was being determined using max(value), which breaks as soon as there are values above 9, because `value` is defined as a varchar rather than integer for lulz. In most instances, this is taken care of by using max(round(value)) instead -- but not everwhere. Fix the wrong ones. (BTW, convert(value, decimal) would be more obvious than round() IMHO...) Note that while this patch is against master, it should be backported to all maintained branches. PS. Database normalisation is only for wimps who can't handle the anomalies, right?... --- CRM/Upgrade/Incremental/sql/3.4.alpha1.mysql.tpl | 2 +- CRM/Upgrade/Incremental/sql/4.3.alpha1.mysql.tpl | 2 +- CRM/Upgrade/Incremental/sql/4.4.5.mysql.tpl | 4 ++-- CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl | 2 +- CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl | 2 +- 5 files changed, 6 insertions(+), 6 deletions(-) diff --git a/CRM/Upgrade/Incremental/sql/3.4.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/3.4.alpha1.mysql.tpl index 4ac3f735b5..0338f30dd5 100644 --- a/CRM/Upgrade/Incremental/sql/3.4.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/3.4.alpha1.mysql.tpl @@ -75,7 +75,7 @@ ON cml.membership_id=cm.id SET cml.membership_type_id=cm.membership_type_id; -- CRM-7445 add client to case SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type'; SELECT @weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; -SELECT @value := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; +SELECT @value := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act; SELECT @caseCompId := max(id) FROM civicrm_component where name = 'CiviCase'; INSERT INTO civicrm_option_value (option_group_id, {localize field='label'}label{/localize}, value, name, weight, {localize field='description'}description{/localize}, is_active, component_id) VALUES diff --git a/CRM/Upgrade/Incremental/sql/4.3.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.3.alpha1.mysql.tpl index d12b119bb0..5fe6f42fc2 100644 --- a/CRM/Upgrade/Incremental/sql/4.3.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.3.alpha1.mysql.tpl @@ -601,7 +601,7 @@ SELECT 'civicrm_payment_processor', id, @option_value_rel_id_as, @financial_acco -- CRM-9923 and CRM-11037 SELECT @option_group_id_batch_status := max(id) from civicrm_option_group where name = 'batch_status'; -SELECT @weight := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status; +SELECT @weight := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status; INSERT INTO `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`) diff --git a/CRM/Upgrade/Incremental/sql/4.4.5.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.4.5.mysql.tpl index a81e2a8a5b..99441185a8 100644 --- a/CRM/Upgrade/Incremental/sql/4.4.5.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.4.5.mysql.tpl @@ -1,7 +1,7 @@ {* file to handle db changes in 4.4.5 during upgrade *} -- CRM-14191 SELECT @option_group_id_batch_status := max(id) from civicrm_option_group where name = 'batch_status'; -SELECT @weight := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status; +SELECT @weight := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_status; UPDATE civicrm_option_value SET value = (Select @weight := @weight +1), @@ -9,7 +9,7 @@ weight = @weight WHERE option_group_id = @option_group_id_batch_status AND name IN ('Data Entry', 'Reopened', 'Exported') AND value = 0 ORDER BY id; SELECT @option_group_id_batch_modes := max(id) from civicrm_option_group where name = 'batch_mode'; -SELECT @weights := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_modes; +SELECT @weights := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_batch_modes; UPDATE civicrm_option_value SET value = (Select @weights := @weights +1), diff --git a/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl index 0fe807a9bb..14339e7ff2 100644 --- a/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl @@ -164,7 +164,7 @@ ALTER TABLE civicrm_pledge DROP honor_type_id; -- CRM-13964 and CRM-13965 SELECT @option_group_id_cs := max(id) from civicrm_option_group where name = 'contribution_status'; SELECT @option_val_id_cs_wt := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; -SELECT @option_val_id_cs_val := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; +SELECT @option_val_id_cs_val := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs; INSERT INTO `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) diff --git a/CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl index 864a61afb9..b5b305e1c5 100755 --- a/CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.6.alpha1.mysql.tpl @@ -105,7 +105,7 @@ CREATE TABLE IF NOT EXISTS `civicrm_recurring_entity` ( -- add batch type for pledge payments SELECT @option_group_id := id FROM civicrm_option_group WHERE name = 'batch_type'; -SELECT @max_option_value:= max(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id; +SELECT @max_option_value:= max(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id; INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}`label`{/localize}, value, name,weight) VALUES (@option_group_id, {localize}'{ts escape="sql"}Pledge Payment{/ts}'{/localize}, @max_option_value+1, 'Pledge Payment','3'); -- 2.25.1