3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Core_I18n_Schema
{
20 * Drop all views (for use by CRM_Core_DAO::dropAllTables() mostly).
22 public static function dropAllViews() {
23 $domain = new CRM_Core_DAO_Domain();
25 if (!$domain->locales
) {
29 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
30 $tables = CRM_Core_I18n_SchemaStructure
::tables();
32 foreach ($locales as $locale) {
33 foreach ($tables as $table) {
34 CRM_Core_DAO
::executeQuery("DROP VIEW IF EXISTS {$table}_{$locale}");
40 * Switch database from single-lang to multi (by adding
41 * the first language and dropping the original columns).
43 * @param string $locale
44 * the first locale to create (migrate to).
46 public static function makeMultilingual($locale) {
47 $isUpdateDone = FALSE;
48 $domain = new CRM_Core_BAO_Domain();
51 while ($domain->fetch()) {
52 // We need to build an array to iterate through here as something later down clears
53 // the cache on the fetch results & causes only the first to be retrieved.
54 $domains[] = clone $domain;
56 foreach ($domains as $domain) {
57 // skip if the domain is already multi-lang.
58 if ($domain->locales
) {
63 $isUpdateDone = self
::alterTablesToSupportMultilingual($locale);
66 // update civicrm_domain.locales
67 $domain->locales
= $locale;
70 // CRM-21627 Updates the $dbLocale
71 CRM_Core_BAO_ConfigSetting
::applyLocale(Civi
::settings($domain->id
), $domain->locales
);
76 * Switch database from multi-lang back to single (by dropping
77 * additional columns and views and retaining only the selected locale).
79 * @param string $retain
80 * the locale to retain.
82 public static function makeSinglelingual($retain) {
83 $domain = new CRM_Core_DAO_Domain();
85 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
87 // break early if the db is already single-lang
92 // lets drop all triggers first
93 $logging = new CRM_Logging_Schema();
94 $logging->dropTriggers();
96 // turn subsequent tables singlelingual
97 $tables = CRM_Core_I18n_SchemaStructure
::tables();
98 foreach ($tables as $table) {
99 self
::makeSinglelingualTable($retain, $table);
102 // update civicrm_domain.locales
103 $domain->locales
= 'NULL';
106 //CRM-6963 -fair assumption.
110 // now lets rebuild all triggers
111 CRM_Core_DAO
::triggerRebuild();
115 * Switch a given table from multi-lang to single (by retaining only the selected locale).
117 * @param string $retain
118 * the locale to retain.
119 * @param string $table
120 * the table containing the column.
121 * @param string $class
122 * schema structure class to use to recreate indices.
124 * @param array $triggers
126 public static function makeSinglelingualTable(
129 $class = 'CRM_Core_I18n_SchemaStructure',
132 $domain = new CRM_Core_DAO_Domain();
134 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
136 // break early if the db is already single-lang
141 $columns =& $class::columns();
142 $indices =& $class::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}";
154 $dao = new CRM_Core_DAO();
156 foreach ($columns[$table] as $column => $type) {
157 $queries[] = "ALTER TABLE {$table} CHANGE `{$column}_{$retain}` `{$column}` {$type}";
158 foreach ($locales as $loc) {
159 if (strcmp($loc, $retain) !== 0) {
160 $dropQueries[] = "ALTER TABLE {$table} DROP {$column}_{$loc}";
166 foreach ($locales as $loc) {
167 $queries[] = "DROP VIEW IF EXISTS {$table}_{$loc}";
170 // add original indices
171 $queries = array_merge($queries, self
::createIndexQueries(NULL, $table));
173 // execute the queries without i18n rewriting
174 $dao = new CRM_Core_DAO();
175 foreach ($queries as $query) {
176 $dao->query($query, FALSE);
179 foreach ($dropQueries as $query) {
180 $dao->query($query, FALSE);
183 if (!empty($triggers)) {
184 if (CRM_Core_Config
::isUpgradeMode()) {
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}");
193 // invoke the meta trigger creation call
194 CRM_Core_DAO
::triggerRebuild($table);
199 * Add a new locale to a multi-lang db, setting
200 * its values to the current default locale.
202 * @param string $locale
203 * the new locale to add.
204 * @param string $source
205 * the locale to copy from.
207 public static function addLocale($locale, $source) {
208 // get the current supported locales
209 $domain = new CRM_Core_DAO_Domain();
211 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
213 // break early if the locale is already supported
214 if (in_array($locale, $locales)) {
218 $dao = new CRM_Core_DAO();
220 // build the required SQL queries
221 $columns = CRM_Core_I18n_SchemaStructure
::columns();
222 $indices = CRM_Core_I18n_SchemaStructure
::indices();
224 foreach ($columns as $table => $hash) {
226 foreach ($hash as $column => $type) {
227 // CRM-7854: skip existing columns
228 if (CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($table, "{$column}_{$locale}", FALSE)) {
231 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
232 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
236 $queries[] = self
::createViewQuery($locale, $table, $dao);
239 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
242 // execute the queries without i18n rewriting
243 foreach ($queries as $query) {
244 $dao->query($query, FALSE);
247 // update civicrm_domain.locales
248 $locales[] = $locale;
249 $domain->locales
= implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $locales);
252 // invoke the meta trigger creation call
253 CRM_Core_DAO
::triggerRebuild();
257 * Rebuild multilingual indices, views and triggers (useful for upgrades)
259 * @param array $locales
260 * locales to be rebuilt.
261 * @param string $version
262 * version of schema structure to use.
263 * @param bool $isUpgradeMode
264 * Are we upgrading our database
266 public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) {
268 $latest = self
::getLatestSchema($version);
269 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
270 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
273 $class = 'CRM_Core_I18n_SchemaStructure';
275 $indices =& $class::indices();
276 $tables =& $class::tables();
278 $dao = new CRM_Core_DAO();
280 // get all of the already existing indices
282 foreach (array_keys($indices) as $table) {
283 $existing[$table] = [];
284 $dao->query("SHOW INDEX FROM $table", FALSE);
285 while ($dao->fetch()) {
286 if (preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Key_name
)) {
287 $existing[$table][] = $dao->Key_name
;
292 // from all of the CREATE INDEX queries fetch the ones creating missing indices
293 foreach ($locales as $locale) {
294 foreach (array_keys($indices) as $table) {
295 $allQueries = self
::createIndexQueries($locale, $table, $class);
296 foreach ($allQueries as $name => $query) {
297 if (!in_array("{$name}_{$locale}", $existing[$table])) {
305 foreach ($locales as $locale) {
306 foreach ($tables as $table) {
307 $queries[] = self
::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode);
312 $last = array_pop($locales);
314 foreach ($queries as $query) {
315 $dao->query($query, FALSE);
318 // invoke the meta trigger creation call
319 CRM_Core_DAO
::triggerRebuild();
323 * Rewrite SQL query to use views to access tables with localized columns.
325 * @param string $query
326 * the query for rewrite.
329 * the rewritten query
331 public static function rewriteQuery($query) {
333 $tables = self
::schemaStructureTables();
334 foreach ($tables as $table) {
336 // should match the civicrm table name such as: civicrm_event
337 // but must not match the table name if it's a substring of another table: civicrm_events_in_cart
338 $query = preg_replace("/([^'\"])({$table})(\z|[^a-z_'\"])/", "\\1\\2{$dbLocale}\\3", $query);
340 // uncomment the below to rewrite the civicrm_value_* queries
341 // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
346 * @param null $version
351 public static function schemaStructureTables($version = NULL, $force = FALSE) {
352 static $_tables = NULL;
353 if ($_tables === NULL ||
$force) {
355 $latest = self
::getLatestSchema($version);
356 // FIXME: Doing require_once is a must here because a call like CRM_Core_I18n_SchemaStructure_4_1_0 makes
357 // class loader look for file like - CRM/Core/I18n/SchemaStructure/4/1/0.php which is not what we want to be loaded
358 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
359 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
360 $tables =& $class::tables();
363 $tables = CRM_Core_I18n_SchemaStructure
::tables();
375 public static function getLatestSchema($version) {
376 // remove any .upgrade sub-str from version. Makes it easy to do version_compare & give right result
377 $version = str_ireplace(".upgrade", "", $version);
379 // fetch all the SchemaStructure versions we ship and sort by version
381 foreach (scandir(dirname(__FILE__
)) as $file) {
383 if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
384 $schemas[] = str_replace('_', '.', $matches[1]);
387 usort($schemas, 'version_compare');
389 // find the latest schema structure older than (or equal to) $version
391 $latest = array_pop($schemas);
392 } while (version_compare($latest, $version, '>'));
394 return str_replace('.', '_', $latest);
398 * CREATE INDEX queries for a given locale and table.
400 * @param string $locale
401 * locale for which the queries should be created (null to create original indices).
402 * @param string $table
403 * table for which the queries should be created.
404 * @param string $class
405 * schema structure class to use.
408 * array of CREATE INDEX queries
410 private static function createIndexQueries($locale, $table, $class = 'CRM_Core_I18n_SchemaStructure') {
411 $indices =& $class::indices();
412 $columns =& $class::columns();
413 if (!isset($indices[$table])) {
418 foreach ($indices[$table] as $index) {
419 $unique = isset($index['unique']) && $index['unique'] ?
'UNIQUE' : '';
420 foreach ($index['field'] as $i => $col) {
421 // if a given column is localizable, extend its name with the locale
422 if ($locale and isset($columns[$table][$col])) {
423 $index['field'][$i] = "{$col}_{$locale}";
426 $cols = implode(', ', $index['field']);
427 $name = $index['name'];
429 $name .= '_' . $locale;
431 // CRM-7854: skip existing indices
432 if (CRM_Core_DAO
::checkConstraintExists($table, $name)) {
435 $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
441 * CREATE VIEW query for a given locale and table.
443 * @param string $locale
444 * locale of the view.
445 * @param string $table
447 * @param CRM_Core_DAO $dao
448 * A DAO object to run DESCRIBE queries.
449 * @param string $class
450 * schema structure class to use.
451 * @param bool $isUpgradeMode
452 * Are we in upgrade mode therefore only build based off table not class
454 * array of CREATE INDEX queries
456 private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) {
457 $columns =& $class::columns();
460 $dao->query("DESCRIBE {$table}", FALSE);
461 while ($dao->fetch()) {
462 // view non-internationalized columns directly
463 if (!in_array($dao->Field
, array_keys($columns[$table])) and
464 !preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Field
)
466 $cols[] = '`' . $dao->Field
. '`';
468 $tableCols[] = $dao->Field
;
470 // view intrernationalized columns through an alias
471 foreach ($columns[$table] as $column => $_) {
472 if (!$isUpgradeMode) {
473 $cols[] = "`{$column}_{$locale}` `{$column}`";
475 elseif (in_array("{$column}_{$locale}", $tableCols)) {
476 $cols[] = "`{$column}_{$locale}` `{$column}`";
479 return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
484 * @param null $tableName
486 public static function triggerInfo(&$info, $tableName = NULL) {
487 // get the current supported locales
488 $domain = new CRM_Core_DAO_Domain();
490 if (empty($domain->locales
)) {
494 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
495 $locale = array_pop($locales);
498 if (count($locales) == 0) {
502 $currentVer = CRM_Core_BAO_Domain
::version(TRUE);
504 if ($currentVer && CRM_Core_Config
::isUpgradeMode()) {
505 // take exact version so that proper schema structure file in invoked
506 $latest = self
::getLatestSchema($currentVer);
507 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
508 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
511 $class = 'CRM_Core_I18n_SchemaStructure';
514 $columns =& $class::columns();
516 foreach ($columns as $table => $hash) {
525 foreach ($hash as $column => $_) {
526 $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
527 foreach ($locales as $old) {
528 $trigger[] = "IF NEW.{$column}_{$old} IS NULL THEN SET NEW.{$column}_{$old} = NEW.{$column}_{$locale}; END IF;";
530 foreach ($locales as $old) {
531 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
532 foreach (array_merge($locales, [
538 $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
541 $trigger[] = 'END IF;';
544 $sql = implode(' ', $trigger);
548 'event' => ['UPDATE'],
553 // take care of the ON INSERT triggers
554 foreach ($columns as $table => $hash) {
556 foreach ($hash as $column => $_) {
557 $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
558 foreach ($locales as $old) {
559 $trigger[] = "SET NEW.{$column}_{$old} = NEW.{$column}_{$locale};";
561 foreach ($locales as $old) {
562 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
563 foreach (array_merge($locales, [
569 $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
572 $trigger[] = 'END IF;';
575 $sql = implode(' ', $trigger);
579 'event' => ['INSERT'],
586 * Alter tables to the structure to support multilingual.
588 * This alters the db structure to use language specific field names for
589 * localised fields and adds the relevant views.
591 * @param string $locale
595 protected static function alterTablesToSupportMultilingual($locale): bool {
596 $dao = new CRM_Core_DAO();
598 // build the column-adding SQL queries
599 $columns = CRM_Core_I18n_SchemaStructure
::columns();
600 $indices = CRM_Core_I18n_SchemaStructure
::indices();
602 foreach ($columns as $table => $hash) {
604 if (isset($indices[$table])) {
605 foreach ($indices[$table] as $index) {
606 if (CRM_Core_BAO_SchemaHandler
::checkIfIndexExists($table, $index['name'])) {
607 $queries[] = "DROP INDEX {$index['name']} ON {$table}";
612 foreach ($hash as $column => $type) {
613 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
614 if (CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($table, $column)) {
615 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}";
616 $queries[] = "ALTER TABLE {$table} DROP {$column}";
621 $queries[] = self
::createViewQuery($locale, $table, $dao);
624 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
627 // execute the queries without i18n rewriting
628 foreach ($queries as $query) {
629 $dao->query($query, FALSE);