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 $locales = CRM_Core_I18n
::getMultilingual();
28 $tables = CRM_Core_I18n_SchemaStructure
::tables();
30 foreach ($locales as $locale) {
31 foreach ($tables as $table) {
32 CRM_Core_DAO
::executeQuery("DROP VIEW IF EXISTS {$table}_{$locale}");
38 * Switch database from single-lang to multi (by adding
39 * the first language and dropping the original columns).
41 * @param string $locale
42 * the first locale to create (migrate to).
44 public static function makeMultilingual($locale) {
45 $isUpdateDone = FALSE;
46 $domain = new CRM_Core_BAO_Domain();
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;
54 foreach ($domains as $domain) {
55 // skip if the domain is already multi-lang.
56 if ($domain->locales
) {
61 $isUpdateDone = self
::alterTablesToSupportMultilingual($locale);
64 // update civicrm_domain.locales
65 $domain->locales
= $locale;
68 // CRM-21627 Updates the $dbLocale
69 CRM_Core_BAO_ConfigSetting
::applyLocale(Civi
::settings($domain->id
), $domain->locales
);
74 * Switch database from multi-lang back to single (by dropping
75 * additional columns and views and retaining only the selected locale).
77 * @param string $retain
78 * the locale to retain.
80 public static function makeSinglelingual($retain) {
81 $domain = new CRM_Core_DAO_Domain();
82 $domain->id
= CRM_Core_Config
::domainID();
84 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
86 // break early if the db is already single-lang
91 // lets drop all triggers first
92 $logging = new CRM_Logging_Schema();
93 $logging->dropTriggers();
95 // turn subsequent tables singlelingual
96 $tables = CRM_Core_I18n_SchemaStructure
::tables();
97 foreach ($tables as $table) {
98 self
::makeSinglelingualTable($retain, $table);
101 // update civicrm_domain.locales
102 $domain->locales
= 'NULL';
105 //CRM-6963 -fair assumption.
109 // now lets rebuild all triggers
110 CRM_Core_DAO
::triggerRebuild();
114 * Switch a given table from multi-lang to single (by retaining only the selected locale).
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.
123 * @param array $triggers
125 public static function makeSinglelingualTable(
128 $class = 'CRM_Core_I18n_SchemaStructure',
131 $domain = new CRM_Core_DAO_Domain();
132 $domain->id
= CRM_Core_Config
::domainID();
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();
210 $domain->id
= CRM_Core_Config
::domainID();
212 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
214 // break early if the locale is already supported
215 if (in_array($locale, $locales)) {
219 $dao = new CRM_Core_DAO();
221 // build the required SQL queries
222 $columns = CRM_Core_I18n_SchemaStructure
::columns();
223 $indices = CRM_Core_I18n_SchemaStructure
::indices();
225 foreach ($columns as $table => $hash) {
227 foreach ($hash as $column => $type) {
228 // CRM-7854: skip existing columns
229 if (CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($table, "{$column}_{$locale}", FALSE)) {
232 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
233 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
237 $queries[] = self
::createViewQuery($locale, $table, $dao);
240 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
243 // execute the queries without i18n rewriting
244 foreach ($queries as $query) {
245 $dao->query($query, FALSE);
248 // update civicrm_domain.locales
249 $locales[] = $locale;
250 $domain->locales
= implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $locales);
253 // invoke the meta trigger creation call
254 CRM_Core_DAO
::triggerRebuild();
258 * Rebuild multilingual indices, views and triggers (useful for upgrades)
260 * @param array $locales
261 * locales to be rebuilt.
262 * @param string $version
263 * version of schema structure to use.
264 * @param bool $isUpgradeMode
265 * Are we upgrading our database
267 public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) {
269 $latest = self
::getLatestSchema($version);
270 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
271 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
274 $class = 'CRM_Core_I18n_SchemaStructure';
276 $indices =& $class::indices();
277 $tables =& $class::tables();
279 $dao = new CRM_Core_DAO();
281 // get all of the already existing indices
283 foreach (array_keys($indices) as $table) {
284 $existing[$table] = [];
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
;
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])) {
306 foreach ($locales as $locale) {
307 foreach ($tables as $table) {
308 $queries[] = self
::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode);
313 $last = array_pop($locales);
315 foreach ($queries as $query) {
316 $dao->query($query, FALSE);
319 // invoke the meta trigger creation call
320 CRM_Core_DAO
::triggerRebuild();
324 * Rewrite SQL query to use views to access tables with localized columns.
326 * @param string $query
327 * the query for rewrite.
330 * the rewritten query
332 public static function rewriteQuery($query) {
334 $tables = self
::schemaStructureTables();
335 foreach ($tables as $table) {
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
339 $query = preg_replace("/([^'\"])({$table})(\z|[^a-z_'\"])/", "\\1\\2{$dbLocale}\\3", $query);
341 // uncomment the below to rewrite the civicrm_value_* queries
342 // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
347 * @param null $version
352 public static function schemaStructureTables($version = NULL, $force = FALSE) {
353 static $_tables = NULL;
354 if ($_tables === NULL ||
$force) {
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}";
361 $tables =& $class::tables();
364 $tables = CRM_Core_I18n_SchemaStructure
::tables();
376 public static function getLatestSchema($version) {
377 // remove any .upgrade sub-str from version. Makes it easy to do version_compare & give right result
378 $version = str_ireplace(".upgrade", "", $version);
380 // fetch all the SchemaStructure versions we ship and sort by version
382 foreach (scandir(dirname(__FILE__
)) as $file) {
384 if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
385 $schemas[] = str_replace('_', '.', $matches[1]);
388 usort($schemas, 'version_compare');
390 // find the latest schema structure older than (or equal to) $version
392 $latest = array_pop($schemas);
393 } while (version_compare($latest, $version, '>'));
395 return str_replace('.', '_', $latest);
399 * CREATE INDEX queries for a given locale and table.
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.
409 * array of CREATE INDEX queries
411 private static function createIndexQueries($locale, $table, $class = 'CRM_Core_I18n_SchemaStructure') {
412 $indices =& $class::indices();
413 $columns =& $class::columns();
414 if (!isset($indices[$table])) {
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}";
427 $cols = implode(', ', $index['field']);
428 $name = $index['name'];
430 $name .= '_' . $locale;
432 // CRM-7854: skip existing indices
433 if (CRM_Core_DAO
::checkConstraintExists($table, $name)) {
436 $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
442 * CREATE VIEW query for a given locale and table.
444 * @param string $locale
445 * locale of the view.
446 * @param string $table
448 * @param CRM_Core_DAO $dao
449 * A DAO object to run DESCRIBE queries.
450 * @param string $class
451 * schema structure class to use.
452 * @param bool $isUpgradeMode
453 * Are we in upgrade mode therefore only build based off table not class
455 * The generated CREATE VIEW query
457 private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) {
458 $columns =& $class::columns();
461 $dao->query("DESCRIBE {$table}", FALSE);
462 while ($dao->fetch()) {
463 // view non-internationalized columns directly
464 if (!array_key_exists($dao->Field
, $columns[$table]) &&
465 !preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Field
)
467 $cols[] = '`' . $dao->Field
. '`';
469 $tableCols[] = $dao->Field
;
471 // view internationalized columns through an alias
472 foreach ($columns[$table] as $column => $_) {
473 if (!$isUpgradeMode) {
474 $cols[] = "`{$column}_{$locale}` `{$column}`";
476 elseif (in_array("{$column}_{$locale}", $tableCols)) {
477 $cols[] = "`{$column}_{$locale}` `{$column}`";
480 return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
485 * @param null $tableName
487 public static function triggerInfo(&$info, $tableName = NULL) {
488 // get the current supported locales
489 $locales = CRM_Core_I18n
::getMultilingual();
494 $locale = array_pop($locales);
497 if (count($locales) == 0) {
501 $currentVer = CRM_Core_BAO_Domain
::version(TRUE);
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}";
510 $class = 'CRM_Core_I18n_SchemaStructure';
513 $columns =& $class::columns();
515 foreach ($columns as $table => $hash) {
524 foreach ($hash as $column => $_) {
525 $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL AND NEW.{$column}_{$locale} != '' THEN";
526 foreach ($locales as $old) {
527 $trigger[] = "IF NEW.{$column}_{$old} IS NULL OR NEW.{$column}_{$old} = '' THEN SET NEW.{$column}_{$old} = NEW.{$column}_{$locale}; END IF;";
529 foreach ($locales as $old) {
530 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL AND NEW.{$column}_{$old} != '' THEN";
531 foreach (array_merge($locales, [
537 $trigger[] = "IF NEW.{$column}_{$loc} IS NULL OR NEW.{$column}_{$loc} = '' THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
540 $trigger[] = 'END IF;';
543 $sql = implode(' ', $trigger);
547 'event' => ['UPDATE'],
552 // take care of the ON INSERT triggers
553 foreach ($columns as $table => $hash) {
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};";
560 foreach ($locales as $old) {
561 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
562 foreach (array_merge($locales, [
568 $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
571 $trigger[] = 'END IF;';
574 $sql = implode(' ', $trigger);
578 'event' => ['INSERT'],
585 * Alter tables to the structure to support multilingual.
587 * This alters the db structure to use language specific field names for
588 * localised fields and adds the relevant views.
590 * @param string $locale
594 protected static function alterTablesToSupportMultilingual($locale): bool {
595 $dao = new CRM_Core_DAO();
597 // build the column-adding SQL queries
598 $columns = CRM_Core_I18n_SchemaStructure
::columns();
599 $indices = CRM_Core_I18n_SchemaStructure
::indices();
601 foreach ($columns as $table => $hash) {
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}";
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}";
620 $queries[] = self
::createViewQuery($locale, $table, $dao);
623 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
626 // execute the queries without i18n rewriting
627 foreach ($queries as $query) {
628 $dao->query($query, FALSE);