| 1 | +--------------------------------------------------------------------+ |
| 2 | | CiviCRM version 4.7 | |
| 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 |