Commit | Line | Data |
---|---|---|
6a488035 | 1 | +--------------------------------------------------------------------+ |
6b7eb9df | 2 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 3 | | | |
bc77d7c0 | 4 | | This work is published under the GNU AGPLv3 license with some | |
6b7eb9df TO |
5 | | permitted exceptions and without any warranty. For full license | |
6 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 TO |
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 | ---------------------------------------------------- | |
10824d34 | 28 | -- get domain id |
6a488035 TO |
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 ) | |
10824d34 | 58 | VALUES |
6a488035 TO |
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 | |
3d8c6ffe | 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); |
6a488035 TO |
81 | |
82 | ------------------------------------------------------------------------------ | |
d05f3550 | 83 | More details: https://docs.civicrm.org/dev/en/latest/translation/database/#localised-fields-schema-changes |