crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/CRM/Upgrade/Incremental/sql/README.txt

   1 +--------------------------------------------------------------------+
   2 | CiviCRM version 4.6                                                |
   3 +--------------------------------------------------------------------+
   4 | Copyright CiviCRM LLC (c) 2004-2015                                |
   5 +--------------------------------------------------------------------+
   6 | This file is a part of CiviCRM.                                    |
   7 |                                                                    |
   8 | CiviCRM is free software; you can copy, modify, and distribute it  |
   9 | under the terms of the GNU Affero General Public License           |
  10 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  11 |                                                                    |
  12 | CiviCRM is distributed in the hope that it will be useful, but     |
  13 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  14 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  15 | See the GNU Affero General Public License for more details.        |
  16 |                                                                    |
  17 | You should have received a copy of the GNU Affero General Public   |
  18 | License and the CiviCRM Licensing Exception along                  |
  19 | with this program; if not, contact CiviCRM LLC                     |
  20 | at info[AT]civicrm[DOT]org. If you have questions about the        |
  21 | GNU Affero General Public License or the licensing of CiviCRM,     |
  22 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  23 +--------------------------------------------------------------------+
  24
  25 This README file documents conventions used in Incremental Upgrade SQL statements.
  26
  27 ====================
  28 Specifying Domain ID
  29 ====================
  30 SQL statements in CRM/Upgrade/Incremental/sql templates which include domain_id should use {$domainID} which is defined in CRM_Upgrade_Form::processLocales()
  31 and is therefore available to all incremental .tpl files.
  32
  33 Correct usage:
  34 ----------------------------------------------------
  35 INSERT INTO `civicrm_report_instance`
  36     ( `domain_id`, `title`, `report_id`, `description`, `permission`, `form_values`)
  37 VALUES
  38     ( {$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}');
  39 ----------------------------------------------------
  40
  41 Previously, a SELECT INTO @domainID was used. This method is deprecated and should NOT be used:
  42
  43 ----------------------------------------------------
  44 -- get domain id
  45 SELECT  @domainID := min(id) FROM civicrm_domain;
  46 ----------------------------------------------------
  47
  48
  49 ==========================
  50 Translate or Localize Text
  51 ==========================
  52 Text which is visible to users needs to be translated or set to localizable (which encompasses translation). Localize is used for fields that support
  53 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.
  54
  55 For example, to check if civicrm_option_value.label is localizable look at the Label field in xml/schema/Core/OptionValue.xml
  56 <field>
  57      <name>label</name>
  58      <title>Option Label</title>
  59      <type>varchar</type>
  60      <required>true</required>
  61      <length>255</length>
  62      <localizable>true</localizable>
  63      <comment>Option string as displayed to users - e.g. the label in an HTML OPTION tag.</comment>
  64      <add>1.5</add>
  65 </field>
  66
  67 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.
  68
  69 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.
  70
  71 ----------------------------------------------------
  72 INSERT INTO civicrm_navigation
  73     ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
  74 VALUES
  75     ( {$domainID}, 'civicrm/admin&reset=1', '{ts escape="sql" skip="true"}Administration Console{/ts}', 'Administration Console', 'administer CiviCRM', '', @adminlastID, '1', NULL, 1 );
  76 ----------------------------------------------------
  77
  78 ===========================
  79 Inserting Option Value Rows
  80 ===========================
  81 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
  82 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
  83 a variable and increment it.
  84
  85 Here's an example which localizes the Label and grabs next available integer for value and weight columns
  86 ------------------------------------------------------------------------------
  87 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
  88
  89 SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
  90 SELECT @max_val    := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_activity_type;
  91 SELECT @max_wt     := max(weight) from civicrm_option_value where option_group_id=@option_group_id_activity_type;
  92
  93 INSERT INTO civicrm_option_value
  94   (option_group_id,                {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, value,                           name,               weight,                        filter, component_id)
  95 VALUES
  96     (@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);
  97
  98 ------------------------------------------------------------------------------
  99 More details on the wiki: http://wiki.civicrm.org/confluence/display/CRMDOC/Internationalisation+for+Developers#InternationalisationforDevelopers-Localisedfieldsschemachanges