CRM/Upgrade/Incremental/sql/README.txt

   1 +--------------------------------------------------------------------+
   2 | Copyright CiviCRM LLC. All rights reserved.                        |
   3 |                                                                    |
   4 | This work is published under the GNU AGPLv3 license with some      |
   5 | permitted exceptions and without any warranty. For full license    |
   6 | and copyright information, see https://civicrm.org/licensing       |
   7 +--------------------------------------------------------------------+
   8
   9 This README file documents conventions used in Incremental Upgrade SQL statements.
  10
  11 ====================
  12 Specifying Domain ID
  13 ====================
  14 SQL statements in CRM/Upgrade/Incremental/sql templates which include domain_id should use {$domainID} which is defined in CRM_Upgrade_Form::processLocales()
  15 and is therefore available to all incremental .tpl files.
  16
  17 Correct usage:
  18 ----------------------------------------------------
  19 INSERT INTO `civicrm_report_instance`
  20     ( `domain_id`, `title`, `report_id`, `description`, `permission`, `form_values`)
  21 VALUES
  22     ( {$domainID}, 'Mailing Detail Report', 'mailing/detail', 'Provides reporting on Intended and Successful Deliveries, Unsubscribes and Opt-outs, Replies and Forwards.', '', '{literal}a:30:{s:6:"fields";a:6:{s:9:"sort_name";s:1:"1";s:12:"mailing_name";s:1:"1";s:11:"delivery_id";s:1:"1";s:14:"unsubscribe_id";s:1:"1";s:9:"optout_id";s:1:"1";s:5:"email";s:1:"1";}s:12:"sort_name_op";s:3:"has";s:15:"sort_name_value";s:0:"";s:6:"id_min";s:0:"";s:6:"id_max";s:0:"";s:5:"id_op";s:3:"lte";s:8:"id_value";s:0:"";s:13:"mailing_id_op";s:2:"in";s:16:"mailing_id_value";a:0:{}s:18:"delivery_status_op";s:2:"eq";s:21:"delivery_status_value";s:0:"";s:18:"is_unsubscribed_op";s:2:"eq";s:21:"is_unsubscribed_value";s:0:"";s:12:"is_optout_op";s:2:"eq";s:15:"is_optout_value";s:0:"";s:13:"is_replied_op";s:2:"eq";s:16:"is_replied_value";s:0:"";s:15:"is_forwarded_op";s:2:"eq";s:18:"is_forwarded_value";s:0:"";s:6:"gid_op";s:2:"in";s:9:"gid_value";a:0:{}s:9:"order_bys";a:1:{i:1;a:2:{s:6:"column";s:9:"sort_name";s:5:"order";s:3:"ASC";}}s:11:"description";s:21:"Mailing Detail Report";s:13:"email_subject";s:0:"";s:8:"email_to";s:0:"";s:8:"email_cc";s:0:"";s:10:"permission";s:1:"0";s:9:"parent_id";s:0:"";s:6:"groups";s:0:"";s:9:"domain_id";i:1;}{/literal}');
  23 ----------------------------------------------------
  24
  25 Previously, a SELECT INTO @domainID was used. This method is deprecated and should NOT be used:
  26
  27 ----------------------------------------------------
  28 -- get domain id
  29 SELECT  @domainID := min(id) FROM civicrm_domain;
  30 ----------------------------------------------------
  31
  32
  33 ==========================
  34 Translate or Localize Text
  35 ==========================
  36 Text which is visible to users needs to be translated or set to localizable (which encompasses translation). Localize is used for fields that support
  37 multiple language values in multi-language installs. Check the schema definition for a given field if you're not sure whether a string is localizable.
  38
  39 For example, to check if civicrm_option_value.label is localizable look at the Label field in xml/schema/Core/OptionValue.xml
  40 <field>
  41      <name>label</name>
  42      <title>Option Label</title>
  43      <type>varchar</type>
  44      <required>true</required>
  45      <length>255</length>
  46      <localizable>true</localizable>
  47      <comment>Option string as displayed to users - e.g. the label in an HTML OPTION tag.</comment>
  48      <add>1.5</add>
  49 </field>
  50
  51 Localizable is true so we need to do inserts using the {localize} tag around that column. Check the Option Value insert example in the next section.
  52
  53 If a field is NOT localizable, but we just need to make sure it can be translated - use the {ts} tag with sql escape parameter as shown below.
  54
  55 ----------------------------------------------------
  56 INSERT INTO civicrm_navigation
  57     ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
  58 VALUES
  59     ( {$domainID}, 'civicrm/admin&reset=1', '{ts escape="sql" skip="true"}Administration Console{/ts}', 'Administration Console', 'administer CiviCRM', '', @adminlastID, '1', NULL, 1 );
  60 ----------------------------------------------------
  61
  62 ===========================
  63 Inserting Option Value Rows
  64 ===========================
  65 When you insert an option value row during an upgrade, do NOT use hard-coded integers for the "value" and "weight" columns. Since in many cases additional
  66 option value rows can be defined by users, you can't determine the next available unique value by looking at a sample installation. Use SELECT max() into
  67 a variable and increment it.
  68
  69 Here's an example which localizes the Label and grabs next available integer for value and weight columns
  70 ------------------------------------------------------------------------------
  71 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
  72
  73 SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
  74 SELECT @max_val    := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_activity_type;
  75 SELECT @max_wt     := max(weight) from civicrm_option_value where option_group_id=@option_group_id_activity_type;
  76
  77 INSERT INTO civicrm_option_value
  78   (option_group_id,                {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, value,                           name,               weight,                        filter, component_id)
  79 VALUES
  80     (@option_group_id_activity_type, {localize}'{ts escape="sql"}Change Custom Data{/ts}'{/localize},{localize}''{/localize}, (SELECT @max_val := @max_val+1), 'Change Custom Data', (SELECT @max_wt := @max_wt+1), 0, @caseCompId);
  81
  82 ------------------------------------------------------------------------------
  83 More details: https://docs.civicrm.org/dev/en/latest/translation/database/#localised-fields-schema-changes