Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
16 | */ |
17 | class CRM_Core_I18n_Schema { | |
18 | ||
19 | /** | |
20 | * Drop all views (for use by CRM_Core_DAO::dropAllTables() mostly). | |
6a488035 | 21 | */ |
00be9182 | 22 | public static function dropAllViews() { |
394d18d3 CW |
23 | $locales = CRM_Core_I18n::getMultilingual(); |
24 | if (!$locales) { | |
6a488035 TO |
25 | return; |
26 | } | |
27 | ||
6a488035 TO |
28 | $tables = CRM_Core_I18n_SchemaStructure::tables(); |
29 | ||
30 | foreach ($locales as $locale) { | |
31 | foreach ($tables as $table) { | |
32 | CRM_Core_DAO::executeQuery("DROP VIEW IF EXISTS {$table}_{$locale}"); | |
33 | } | |
34 | } | |
35 | } | |
36 | ||
37 | /** | |
38 | * Switch database from single-lang to multi (by adding | |
39 | * the first language and dropping the original columns). | |
40 | * | |
5a4f6742 CW |
41 | * @param string $locale |
42 | * the first locale to create (migrate to). | |
6a488035 | 43 | */ |
00be9182 | 44 | public static function makeMultilingual($locale) { |
6d78a98a | 45 | $isUpdateDone = FALSE; |
46 | $domain = new CRM_Core_BAO_Domain(); | |
47 | $domain->find(); | |
48 | $domains = []; | |
49 | while ($domain->fetch()) { | |
50 | // We need to build an array to iterate through here as something later down clears | |
51 | // the cache on the fetch results & causes only the first to be retrieved. | |
52 | $domains[] = clone $domain; | |
53 | } | |
54 | foreach ($domains as $domain) { | |
55 | // skip if the domain is already multi-lang. | |
56 | if ($domain->locales) { | |
57 | continue; | |
6a488035 TO |
58 | } |
59 | ||
6d78a98a | 60 | if (!$isUpdateDone) { |
61 | $isUpdateDone = self::alterTablesToSupportMultilingual($locale); | |
62 | } | |
6a488035 | 63 | |
6d78a98a | 64 | // update civicrm_domain.locales |
65 | $domain->locales = $locale; | |
66 | $domain->save(); | |
6a488035 | 67 | |
6d78a98a | 68 | // CRM-21627 Updates the $dbLocale |
69 | CRM_Core_BAO_ConfigSetting::applyLocale(Civi::settings($domain->id), $domain->locales); | |
6a488035 | 70 | } |
6a488035 TO |
71 | } |
72 | ||
73 | /** | |
74 | * Switch database from multi-lang back to single (by dropping | |
75 | * additional columns and views and retaining only the selected locale). | |
76 | * | |
5a4f6742 CW |
77 | * @param string $retain |
78 | * the locale to retain. | |
6a488035 | 79 | */ |
00be9182 | 80 | public static function makeSinglelingual($retain) { |
3bdca100 | 81 | $domain = new CRM_Core_DAO_Domain(); |
394d18d3 | 82 | $domain->id = CRM_Core_Config::domainID(); |
6a488035 TO |
83 | $domain->find(TRUE); |
84 | $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales); | |
85 | ||
86 | // break early if the db is already single-lang | |
87 | if (!$locales) { | |
88 | return; | |
89 | } | |
90 | ||
91 | // lets drop all triggers first | |
3bdca100 | 92 | $logging = new CRM_Logging_Schema(); |
481a74f4 | 93 | $logging->dropTriggers(); |
6a488035 TO |
94 | |
95 | // turn subsequent tables singlelingual | |
96 | $tables = CRM_Core_I18n_SchemaStructure::tables(); | |
97 | foreach ($tables as $table) { | |
98 | self::makeSinglelingualTable($retain, $table); | |
99 | } | |
100 | ||
101 | // update civicrm_domain.locales | |
102 | $domain->locales = 'NULL'; | |
103 | $domain->save(); | |
104 | ||
105 | //CRM-6963 -fair assumption. | |
106 | global $dbLocale; | |
107 | $dbLocale = ''; | |
108 | ||
109 | // now lets rebuild all triggers | |
481a74f4 | 110 | CRM_Core_DAO::triggerRebuild(); |
6a488035 TO |
111 | } |
112 | ||
113 | /** | |
114 | * Switch a given table from multi-lang to single (by retaining only the selected locale). | |
115 | * | |
5a4f6742 CW |
116 | * @param string $retain |
117 | * the locale to retain. | |
118 | * @param string $table | |
119 | * the table containing the column. | |
120 | * @param string $class | |
121 | * schema structure class to use to recreate indices. | |
6a488035 | 122 | * |
da6b46f4 | 123 | * @param array $triggers |
6a488035 | 124 | */ |
3bdca100 | 125 | public static function makeSinglelingualTable( |
6a488035 TO |
126 | $retain, |
127 | $table, | |
128 | $class = 'CRM_Core_I18n_SchemaStructure', | |
be2fb01f | 129 | $triggers = [] |
6a488035 | 130 | ) { |
3bdca100 | 131 | $domain = new CRM_Core_DAO_Domain(); |
394d18d3 | 132 | $domain->id = CRM_Core_Config::domainID(); |
6a488035 TO |
133 | $domain->find(TRUE); |
134 | $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales); | |
135 | ||
136 | // break early if the db is already single-lang | |
137 | if (!$locales) { | |
138 | return; | |
139 | } | |
140 | ||
0e6e8724 DL |
141 | $columns =& $class::columns(); |
142 | $indices =& $class::indices(); | |
be2fb01f CW |
143 | $queries = []; |
144 | $dropQueries = []; | |
6a488035 TO |
145 | // drop indices |
146 | if (isset($indices[$table])) { | |
147 | foreach ($indices[$table] as $index) { | |
148 | foreach ($locales as $loc) { | |
149 | $queries[] = "DROP INDEX {$index['name']}_{$loc} ON {$table}"; | |
150 | } | |
151 | } | |
152 | } | |
153 | ||
0e2b9f40 | 154 | $dao = new CRM_Core_DAO(); |
6a488035 TO |
155 | // deal with columns |
156 | foreach ($columns[$table] as $column => $type) { | |
0e2b9f40 | 157 | $queries[] = "ALTER TABLE {$table} CHANGE `{$column}_{$retain}` `{$column}` {$type}"; |
6a488035 | 158 | foreach ($locales as $loc) { |
40ee1db2 | 159 | if (strcmp($loc, $retain) !== 0) { |
0e2b9f40 MW |
160 | $dropQueries[] = "ALTER TABLE {$table} DROP {$column}_{$loc}"; |
161 | } | |
6a488035 TO |
162 | } |
163 | } | |
164 | ||
165 | // drop views | |
166 | foreach ($locales as $loc) { | |
0e2b9f40 | 167 | $queries[] = "DROP VIEW IF EXISTS {$table}_{$loc}"; |
6a488035 TO |
168 | } |
169 | ||
170 | // add original indices | |
171 | $queries = array_merge($queries, self::createIndexQueries(NULL, $table)); | |
172 | ||
173 | // execute the queries without i18n rewriting | |
3bdca100 | 174 | $dao = new CRM_Core_DAO(); |
6a488035 TO |
175 | foreach ($queries as $query) { |
176 | $dao->query($query, FALSE); | |
177 | } | |
178 | ||
179 | foreach ($dropQueries as $query) { | |
180 | $dao->query($query, FALSE); | |
181 | } | |
182 | ||
481a74f4 | 183 | if (!empty($triggers)) { |
6a488035 | 184 | if (CRM_Core_Config::isUpgradeMode()) { |
353ffa53 TO |
185 | foreach ($triggers as $triggerInfo) { |
186 | $when = $triggerInfo['when']; | |
187 | $event = $triggerInfo['event']; | |
188 | $triggerName = "{$table}_{$when}_{$event}"; | |
189 | CRM_Core_DAO::executeQuery("DROP TRIGGER IF EXISTS {$triggerName}"); | |
190 | } | |
6a488035 | 191 | } |
6a488035 | 192 | |
353ffa53 TO |
193 | // invoke the meta trigger creation call |
194 | CRM_Core_DAO::triggerRebuild($table); | |
195 | } | |
6a488035 TO |
196 | } |
197 | ||
198 | /** | |
199 | * Add a new locale to a multi-lang db, setting | |
200 | * its values to the current default locale. | |
201 | * | |
5a4f6742 CW |
202 | * @param string $locale |
203 | * the new locale to add. | |
204 | * @param string $source | |
205 | * the locale to copy from. | |
6a488035 | 206 | */ |
00be9182 | 207 | public static function addLocale($locale, $source) { |
6a488035 TO |
208 | // get the current supported locales |
209 | $domain = new CRM_Core_DAO_Domain(); | |
394d18d3 | 210 | $domain->id = CRM_Core_Config::domainID(); |
6a488035 TO |
211 | $domain->find(TRUE); |
212 | $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales); | |
213 | ||
214 | // break early if the locale is already supported | |
215 | if (in_array($locale, $locales)) { | |
216 | return; | |
217 | } | |
218 | ||
219 | $dao = new CRM_Core_DAO(); | |
220 | ||
221 | // build the required SQL queries | |
222 | $columns = CRM_Core_I18n_SchemaStructure::columns(); | |
223 | $indices = CRM_Core_I18n_SchemaStructure::indices(); | |
be2fb01f | 224 | $queries = []; |
6a488035 TO |
225 | foreach ($columns as $table => $hash) { |
226 | // add new columns | |
227 | foreach ($hash as $column => $type) { | |
228 | // CRM-7854: skip existing columns | |
eed7e803 | 229 | if (CRM_Core_BAO_SchemaHandler::checkIfFieldExists($table, "{$column}_{$locale}", FALSE)) { |
6a488035 TO |
230 | continue; |
231 | } | |
232 | $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}"; | |
233 | $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}"; | |
234 | } | |
235 | ||
236 | // add view | |
237 | $queries[] = self::createViewQuery($locale, $table, $dao); | |
238 | ||
239 | // add new indices | |
240 | $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table))); | |
241 | } | |
242 | ||
243 | // execute the queries without i18n rewriting | |
244 | foreach ($queries as $query) { | |
245 | $dao->query($query, FALSE); | |
246 | } | |
247 | ||
248 | // update civicrm_domain.locales | |
249 | $locales[] = $locale; | |
250 | $domain->locales = implode(CRM_Core_DAO::VALUE_SEPARATOR, $locales); | |
251 | $domain->save(); | |
252 | ||
253 | // invoke the meta trigger creation call | |
254 | CRM_Core_DAO::triggerRebuild(); | |
255 | } | |
256 | ||
257 | /** | |
258 | * Rebuild multilingual indices, views and triggers (useful for upgrades) | |
259 | * | |
5a4f6742 CW |
260 | * @param array $locales |
261 | * locales to be rebuilt. | |
262 | * @param string $version | |
263 | * version of schema structure to use. | |
41ace555 SL |
264 | * @param bool $isUpgradeMode |
265 | * Are we upgrading our database | |
6a488035 | 266 | */ |
41ace555 | 267 | public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) { |
6a488035 TO |
268 | if ($version) { |
269 | $latest = self::getLatestSchema($version); | |
270 | require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php"; | |
271 | $class = "CRM_Core_I18n_SchemaStructure_{$latest}"; | |
272 | } | |
273 | else { | |
274 | $class = 'CRM_Core_I18n_SchemaStructure'; | |
275 | } | |
0e6e8724 | 276 | $indices =& $class::indices(); |
353ffa53 | 277 | $tables =& $class::tables(); |
be2fb01f | 278 | $queries = []; |
3bdca100 | 279 | $dao = new CRM_Core_DAO(); |
6a488035 TO |
280 | |
281 | // get all of the already existing indices | |
be2fb01f | 282 | $existing = []; |
6a488035 | 283 | foreach (array_keys($indices) as $table) { |
be2fb01f | 284 | $existing[$table] = []; |
6a488035 TO |
285 | $dao->query("SHOW INDEX FROM $table", FALSE); |
286 | while ($dao->fetch()) { | |
287 | if (preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Key_name)) { | |
288 | $existing[$table][] = $dao->Key_name; | |
289 | } | |
290 | } | |
291 | } | |
292 | ||
293 | // from all of the CREATE INDEX queries fetch the ones creating missing indices | |
294 | foreach ($locales as $locale) { | |
295 | foreach (array_keys($indices) as $table) { | |
296 | $allQueries = self::createIndexQueries($locale, $table, $class); | |
297 | foreach ($allQueries as $name => $query) { | |
298 | if (!in_array("{$name}_{$locale}", $existing[$table])) { | |
299 | $queries[] = $query; | |
300 | } | |
301 | } | |
302 | } | |
303 | } | |
304 | ||
305 | // rebuild views | |
306 | foreach ($locales as $locale) { | |
307 | foreach ($tables as $table) { | |
41ace555 | 308 | $queries[] = self::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode); |
6a488035 TO |
309 | } |
310 | } | |
311 | ||
312 | // rebuild triggers | |
313 | $last = array_pop($locales); | |
314 | ||
315 | foreach ($queries as $query) { | |
316 | $dao->query($query, FALSE); | |
317 | } | |
318 | ||
319 | // invoke the meta trigger creation call | |
320 | CRM_Core_DAO::triggerRebuild(); | |
321 | } | |
322 | ||
323 | /** | |
324 | * Rewrite SQL query to use views to access tables with localized columns. | |
325 | * | |
5a4f6742 CW |
326 | * @param string $query |
327 | * the query for rewrite. | |
6a488035 | 328 | * |
a6c01b45 CW |
329 | * @return string |
330 | * the rewritten query | |
6a488035 | 331 | */ |
00be9182 | 332 | public static function rewriteQuery($query) { |
6a488035 TO |
333 | global $dbLocale; |
334 | $tables = self::schemaStructureTables(); | |
335 | foreach ($tables as $table) { | |
7f7d306e SV |
336 | // CRM-19093 |
337 | // should match the civicrm table name such as: civicrm_event | |
338 | // but must not match the table name if it's a substring of another table: civicrm_events_in_cart | |
bedb217f | 339 | $query = preg_replace("/([^'\"])({$table})(\z|[^a-z_'\"])/", "\\1\\2{$dbLocale}\\3", $query); |
6a488035 TO |
340 | } |
341 | // uncomment the below to rewrite the civicrm_value_* queries | |
342 | // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query); | |
343 | return $query; | |
344 | } | |
345 | ||
a0ee3941 EM |
346 | /** |
347 | * @param null $version | |
348 | * @param bool $force | |
349 | * | |
350 | * @return array | |
351 | */ | |
00be9182 | 352 | public static function schemaStructureTables($version = NULL, $force = FALSE) { |
6a488035 TO |
353 | static $_tables = NULL; |
354 | if ($_tables === NULL || $force) { | |
355 | if ($version) { | |
356 | $latest = self::getLatestSchema($version); | |
357 | // FIXME: Doing require_once is a must here because a call like CRM_Core_I18n_SchemaStructure_4_1_0 makes | |
358 | // class loader look for file like - CRM/Core/I18n/SchemaStructure/4/1/0.php which is not what we want to be loaded | |
359 | require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php"; | |
360 | $class = "CRM_Core_I18n_SchemaStructure_{$latest}"; | |
353ffa53 | 361 | $tables =& $class::tables(); |
6a488035 TO |
362 | } |
363 | else { | |
364 | $tables = CRM_Core_I18n_SchemaStructure::tables(); | |
365 | } | |
366 | $_tables = $tables; | |
367 | } | |
368 | return $_tables; | |
369 | } | |
370 | ||
a0ee3941 EM |
371 | /** |
372 | * @param $version | |
373 | * | |
374 | * @return mixed | |
375 | */ | |
00be9182 | 376 | public static function getLatestSchema($version) { |
6a488035 TO |
377 | // remove any .upgrade sub-str from version. Makes it easy to do version_compare & give right result |
378 | $version = str_ireplace(".upgrade", "", $version); | |
379 | ||
380 | // fetch all the SchemaStructure versions we ship and sort by version | |
be2fb01f | 381 | $schemas = []; |
6a488035 | 382 | foreach (scandir(dirname(__FILE__)) as $file) { |
be2fb01f | 383 | $matches = []; |
6a488035 TO |
384 | if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) { |
385 | $schemas[] = str_replace('_', '.', $matches[1]); | |
386 | } | |
387 | } | |
388 | usort($schemas, 'version_compare'); | |
389 | ||
390 | // find the latest schema structure older than (or equal to) $version | |
391 | do { | |
392 | $latest = array_pop($schemas); | |
393 | } while (version_compare($latest, $version, '>')); | |
394 | ||
395 | return str_replace('.', '_', $latest); | |
396 | } | |
397 | ||
398 | /** | |
fe482240 | 399 | * CREATE INDEX queries for a given locale and table. |
6a488035 | 400 | * |
5a4f6742 CW |
401 | * @param string $locale |
402 | * locale for which the queries should be created (null to create original indices). | |
403 | * @param string $table | |
404 | * table for which the queries should be created. | |
405 | * @param string $class | |
406 | * schema structure class to use. | |
6a488035 | 407 | * |
a6c01b45 CW |
408 | * @return array |
409 | * array of CREATE INDEX queries | |
6a488035 TO |
410 | */ |
411 | private static function createIndexQueries($locale, $table, $class = 'CRM_Core_I18n_SchemaStructure') { | |
0e6e8724 DL |
412 | $indices =& $class::indices(); |
413 | $columns =& $class::columns(); | |
6a488035 | 414 | if (!isset($indices[$table])) { |
be2fb01f | 415 | return []; |
6a488035 TO |
416 | } |
417 | ||
be2fb01f | 418 | $queries = []; |
6a488035 TO |
419 | foreach ($indices[$table] as $index) { |
420 | $unique = isset($index['unique']) && $index['unique'] ? 'UNIQUE' : ''; | |
421 | foreach ($index['field'] as $i => $col) { | |
422 | // if a given column is localizable, extend its name with the locale | |
423 | if ($locale and isset($columns[$table][$col])) { | |
424 | $index['field'][$i] = "{$col}_{$locale}"; | |
425 | } | |
426 | } | |
427 | $cols = implode(', ', $index['field']); | |
428 | $name = $index['name']; | |
429 | if ($locale) { | |
430 | $name .= '_' . $locale; | |
431 | } | |
432 | // CRM-7854: skip existing indices | |
433 | if (CRM_Core_DAO::checkConstraintExists($table, $name)) { | |
434 | continue; | |
435 | } | |
436 | $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})"; | |
437 | } | |
438 | return $queries; | |
439 | } | |
440 | ||
441 | /** | |
fe482240 | 442 | * CREATE VIEW query for a given locale and table. |
6a488035 | 443 | * |
5a4f6742 CW |
444 | * @param string $locale |
445 | * locale of the view. | |
446 | * @param string $table | |
447 | * table of the view. | |
6a0b768e TO |
448 | * @param CRM_Core_DAO $dao |
449 | * A DAO object to run DESCRIBE queries. | |
5a4f6742 CW |
450 | * @param string $class |
451 | * schema structure class to use. | |
41ace555 SL |
452 | * @param bool $isUpgradeMode |
453 | * Are we in upgrade mode therefore only build based off table not class | |
a6c01b45 CW |
454 | * @return array |
455 | * array of CREATE INDEX queries | |
6a488035 | 456 | */ |
41ace555 | 457 | private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) { |
0e6e8724 | 458 | $columns =& $class::columns(); |
be2fb01f CW |
459 | $cols = []; |
460 | $tableCols = []; | |
6a488035 TO |
461 | $dao->query("DESCRIBE {$table}", FALSE); |
462 | while ($dao->fetch()) { | |
463 | // view non-internationalized columns directly | |
c3f7ab62 | 464 | if (!array_key_exists($dao->Field, $columns[$table]) && |
6a488035 TO |
465 | !preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Field) |
466 | ) { | |
8928896f | 467 | $cols[] = '`' . $dao->Field . '`'; |
6a488035 | 468 | } |
41ace555 | 469 | $tableCols[] = $dao->Field; |
6a488035 | 470 | } |
c3f7ab62 | 471 | // view internationalized columns through an alias |
6a488035 | 472 | foreach ($columns[$table] as $column => $_) { |
41ace555 | 473 | if (!$isUpgradeMode) { |
8928896f | 474 | $cols[] = "`{$column}_{$locale}` `{$column}`"; |
41ace555 SL |
475 | } |
476 | elseif (in_array("{$column}_{$locale}", $tableCols)) { | |
8928896f | 477 | $cols[] = "`{$column}_{$locale}` `{$column}`"; |
41ace555 | 478 | } |
6a488035 TO |
479 | } |
480 | return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}"; | |
481 | } | |
482 | ||
a0ee3941 EM |
483 | /** |
484 | * @param $info | |
485 | * @param null $tableName | |
486 | */ | |
00be9182 | 487 | public static function triggerInfo(&$info, $tableName = NULL) { |
6a488035 | 488 | // get the current supported locales |
394d18d3 CW |
489 | $locales = CRM_Core_I18n::getMultilingual(); |
490 | if (!$locales) { | |
6a488035 TO |
491 | return; |
492 | } | |
493 | ||
6a488035 TO |
494 | $locale = array_pop($locales); |
495 | ||
496 | // CRM-10027 | |
497 | if (count($locales) == 0) { | |
498 | return; | |
499 | } | |
500 | ||
501 | $currentVer = CRM_Core_BAO_Domain::version(TRUE); | |
502 | ||
503 | if ($currentVer && CRM_Core_Config::isUpgradeMode()) { | |
504 | // take exact version so that proper schema structure file in invoked | |
505 | $latest = self::getLatestSchema($currentVer); | |
506 | require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php"; | |
507 | $class = "CRM_Core_I18n_SchemaStructure_{$latest}"; | |
508 | } | |
509 | else { | |
510 | $class = 'CRM_Core_I18n_SchemaStructure'; | |
511 | } | |
512 | ||
0e6e8724 | 513 | $columns =& $class::columns(); |
6a488035 TO |
514 | |
515 | foreach ($columns as $table => $hash) { | |
516 | if ($tableName && | |
517 | $tableName != $table | |
518 | ) { | |
519 | continue; | |
520 | } | |
521 | ||
be2fb01f | 522 | $trigger = []; |
6a488035 TO |
523 | |
524 | foreach ($hash as $column => $_) { | |
525 | $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN"; | |
526 | foreach ($locales as $old) { | |
527 | $trigger[] = "IF NEW.{$column}_{$old} IS NULL THEN SET NEW.{$column}_{$old} = NEW.{$column}_{$locale}; END IF;"; | |
528 | } | |
529 | foreach ($locales as $old) { | |
530 | $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN"; | |
be2fb01f | 531 | foreach (array_merge($locales, [ |
3bdca100 | 532 | $locale, |
be2fb01f | 533 | ]) as $loc) { |
6a488035 TO |
534 | if ($loc == $old) { |
535 | continue; | |
536 | } | |
537 | $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;"; | |
538 | } | |
539 | } | |
540 | $trigger[] = 'END IF;'; | |
541 | } | |
542 | ||
543 | $sql = implode(' ', $trigger); | |
be2fb01f CW |
544 | $info[] = [ |
545 | 'table' => [$table], | |
6a488035 | 546 | 'when' => 'BEFORE', |
be2fb01f | 547 | 'event' => ['UPDATE'], |
6a488035 | 548 | 'sql' => $sql, |
be2fb01f | 549 | ]; |
6a488035 TO |
550 | } |
551 | ||
552 | // take care of the ON INSERT triggers | |
553 | foreach ($columns as $table => $hash) { | |
be2fb01f | 554 | $trigger = []; |
6a488035 TO |
555 | foreach ($hash as $column => $_) { |
556 | $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN"; | |
557 | foreach ($locales as $old) { | |
558 | $trigger[] = "SET NEW.{$column}_{$old} = NEW.{$column}_{$locale};"; | |
559 | } | |
560 | foreach ($locales as $old) { | |
561 | $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN"; | |
be2fb01f | 562 | foreach (array_merge($locales, [ |
3bdca100 | 563 | $locale, |
be2fb01f | 564 | ]) as $loc) { |
6a488035 TO |
565 | if ($loc == $old) { |
566 | continue; | |
567 | } | |
568 | $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};"; | |
569 | } | |
570 | } | |
571 | $trigger[] = 'END IF;'; | |
572 | } | |
573 | ||
574 | $sql = implode(' ', $trigger); | |
be2fb01f CW |
575 | $info[] = [ |
576 | 'table' => [$table], | |
6a488035 | 577 | 'when' => 'BEFORE', |
be2fb01f | 578 | 'event' => ['INSERT'], |
6a488035 | 579 | 'sql' => $sql, |
be2fb01f | 580 | ]; |
6a488035 TO |
581 | } |
582 | } | |
96025800 | 583 | |
6d78a98a | 584 | /** |
585 | * Alter tables to the structure to support multilingual. | |
586 | * | |
587 | * This alters the db structure to use language specific field names for | |
588 | * localised fields and adds the relevant views. | |
589 | * | |
590 | * @param string $locale | |
591 | * | |
592 | * @return bool | |
593 | */ | |
594 | protected static function alterTablesToSupportMultilingual($locale): bool { | |
595 | $dao = new CRM_Core_DAO(); | |
596 | ||
597 | // build the column-adding SQL queries | |
598 | $columns = CRM_Core_I18n_SchemaStructure::columns(); | |
599 | $indices = CRM_Core_I18n_SchemaStructure::indices(); | |
600 | $queries = []; | |
601 | foreach ($columns as $table => $hash) { | |
602 | // drop old indices | |
603 | if (isset($indices[$table])) { | |
604 | foreach ($indices[$table] as $index) { | |
605 | if (CRM_Core_BAO_SchemaHandler::checkIfIndexExists($table, $index['name'])) { | |
606 | $queries[] = "DROP INDEX {$index['name']} ON {$table}"; | |
607 | } | |
608 | } | |
609 | } | |
610 | // deal with columns | |
611 | foreach ($hash as $column => $type) { | |
612 | $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}"; | |
613 | if (CRM_Core_BAO_SchemaHandler::checkIfFieldExists($table, $column)) { | |
614 | $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}"; | |
615 | $queries[] = "ALTER TABLE {$table} DROP {$column}"; | |
616 | } | |
617 | } | |
618 | ||
619 | // add view | |
620 | $queries[] = self::createViewQuery($locale, $table, $dao); | |
621 | ||
622 | // add new indices | |
623 | $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table))); | |
624 | } | |
625 | ||
626 | // execute the queries without i18n rewriting | |
627 | foreach ($queries as $query) { | |
628 | $dao->query($query, FALSE); | |
629 | } | |
630 | return TRUE; | |
631 | } | |
632 | ||
6a488035 | 633 | } |