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 $domain = new CRM_Core_DAO_Domain();
50 // break early if the db is already multi-lang
51 if ($domain->locales
) {
55 $dao = new CRM_Core_DAO();
57 // build the column-adding SQL queries
58 $columns = CRM_Core_I18n_SchemaStructure
::columns();
59 $indices = CRM_Core_I18n_SchemaStructure
::indices();
61 foreach ($columns as $table => $hash) {
63 if (isset($indices[$table])) {
64 foreach ($indices[$table] as $index) {
65 if (CRM_Core_BAO_SchemaHandler
::checkIfIndexExists($table, $index['name'])) {
66 $queries[] = "DROP INDEX {$index['name']} ON {$table}";
71 foreach ($hash as $column => $type) {
72 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
73 if (CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($table, $column)) {
74 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}";
75 $queries[] = "ALTER TABLE {$table} DROP {$column}";
80 $queries[] = self
::createViewQuery($locale, $table, $dao);
83 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
86 // execute the queries without i18n rewriting
87 foreach ($queries as $query) {
88 $dao->query($query, FALSE);
91 // update civicrm_domain.locales
92 $domain->locales
= $locale;
95 // CRM-21627 Updates the $dbLocale
96 CRM_Core_BAO_ConfigSetting
::applyLocale(Civi
::settings($domain->id
), $domain->locales
);
100 * Switch database from multi-lang back to single (by dropping
101 * additional columns and views and retaining only the selected locale).
103 * @param string $retain
104 * the locale to retain.
106 public static function makeSinglelingual($retain) {
107 $domain = new CRM_Core_DAO_Domain();
109 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
111 // break early if the db is already single-lang
116 // lets drop all triggers first
117 $logging = new CRM_Logging_Schema();
118 $logging->dropTriggers();
120 // turn subsequent tables singlelingual
121 $tables = CRM_Core_I18n_SchemaStructure
::tables();
122 foreach ($tables as $table) {
123 self
::makeSinglelingualTable($retain, $table);
126 // update civicrm_domain.locales
127 $domain->locales
= 'NULL';
130 //CRM-6963 -fair assumption.
134 // now lets rebuild all triggers
135 CRM_Core_DAO
::triggerRebuild();
139 * Switch a given table from multi-lang to single (by retaining only the selected locale).
141 * @param string $retain
142 * the locale to retain.
143 * @param string $table
144 * the table containing the column.
145 * @param string $class
146 * schema structure class to use to recreate indices.
148 * @param array $triggers
150 public static function makeSinglelingualTable(
153 $class = 'CRM_Core_I18n_SchemaStructure',
156 $domain = new CRM_Core_DAO_Domain();
158 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
160 // break early if the db is already single-lang
165 $columns =& $class::columns();
166 $indices =& $class::indices();
170 if (isset($indices[$table])) {
171 foreach ($indices[$table] as $index) {
172 foreach ($locales as $loc) {
173 $queries[] = "DROP INDEX {$index['name']}_{$loc} ON {$table}";
178 $dao = new CRM_Core_DAO();
180 foreach ($columns[$table] as $column => $type) {
181 $queries[] = "ALTER TABLE {$table} CHANGE `{$column}_{$retain}` `{$column}` {$type}";
182 foreach ($locales as $loc) {
183 if (strcmp($loc, $retain) !== 0) {
184 $dropQueries[] = "ALTER TABLE {$table} DROP {$column}_{$loc}";
190 foreach ($locales as $loc) {
191 $queries[] = "DROP VIEW IF EXISTS {$table}_{$loc}";
194 // add original indices
195 $queries = array_merge($queries, self
::createIndexQueries(NULL, $table));
197 // execute the queries without i18n rewriting
198 $dao = new CRM_Core_DAO();
199 foreach ($queries as $query) {
200 $dao->query($query, FALSE);
203 foreach ($dropQueries as $query) {
204 $dao->query($query, FALSE);
207 if (!empty($triggers)) {
208 if (CRM_Core_Config
::isUpgradeMode()) {
209 foreach ($triggers as $triggerInfo) {
210 $when = $triggerInfo['when'];
211 $event = $triggerInfo['event'];
212 $triggerName = "{$table}_{$when}_{$event}";
213 CRM_Core_DAO
::executeQuery("DROP TRIGGER IF EXISTS {$triggerName}");
217 // invoke the meta trigger creation call
218 CRM_Core_DAO
::triggerRebuild($table);
223 * Add a new locale to a multi-lang db, setting
224 * its values to the current default locale.
226 * @param string $locale
227 * the new locale to add.
228 * @param string $source
229 * the locale to copy from.
231 public static function addLocale($locale, $source) {
232 // get the current supported locales
233 $domain = new CRM_Core_DAO_Domain();
235 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
237 // break early if the locale is already supported
238 if (in_array($locale, $locales)) {
242 $dao = new CRM_Core_DAO();
244 // build the required SQL queries
245 $columns = CRM_Core_I18n_SchemaStructure
::columns();
246 $indices = CRM_Core_I18n_SchemaStructure
::indices();
248 foreach ($columns as $table => $hash) {
250 foreach ($hash as $column => $type) {
251 // CRM-7854: skip existing columns
252 if (CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($table, "{$column}_{$locale}", FALSE)) {
255 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
256 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
260 $queries[] = self
::createViewQuery($locale, $table, $dao);
263 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
266 // execute the queries without i18n rewriting
267 foreach ($queries as $query) {
268 $dao->query($query, FALSE);
271 // update civicrm_domain.locales
272 $locales[] = $locale;
273 $domain->locales
= implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $locales);
276 // invoke the meta trigger creation call
277 CRM_Core_DAO
::triggerRebuild();
281 * Rebuild multilingual indices, views and triggers (useful for upgrades)
283 * @param array $locales
284 * locales to be rebuilt.
285 * @param string $version
286 * version of schema structure to use.
287 * @param bool $isUpgradeMode
288 * Are we upgrading our database
290 public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) {
292 $latest = self
::getLatestSchema($version);
293 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
294 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
297 $class = 'CRM_Core_I18n_SchemaStructure';
299 $indices =& $class::indices();
300 $tables =& $class::tables();
302 $dao = new CRM_Core_DAO();
304 // get all of the already existing indices
306 foreach (array_keys($indices) as $table) {
307 $existing[$table] = [];
308 $dao->query("SHOW INDEX FROM $table", FALSE);
309 while ($dao->fetch()) {
310 if (preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Key_name
)) {
311 $existing[$table][] = $dao->Key_name
;
316 // from all of the CREATE INDEX queries fetch the ones creating missing indices
317 foreach ($locales as $locale) {
318 foreach (array_keys($indices) as $table) {
319 $allQueries = self
::createIndexQueries($locale, $table, $class);
320 foreach ($allQueries as $name => $query) {
321 if (!in_array("{$name}_{$locale}", $existing[$table])) {
329 foreach ($locales as $locale) {
330 foreach ($tables as $table) {
331 $queries[] = self
::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode);
336 $last = array_pop($locales);
338 foreach ($queries as $query) {
339 $dao->query($query, FALSE);
342 // invoke the meta trigger creation call
343 CRM_Core_DAO
::triggerRebuild();
347 * Rewrite SQL query to use views to access tables with localized columns.
349 * @param string $query
350 * the query for rewrite.
353 * the rewritten query
355 public static function rewriteQuery($query) {
357 $tables = self
::schemaStructureTables();
358 foreach ($tables as $table) {
360 // should match the civicrm table name such as: civicrm_event
361 // but must not match the table name if it's a substring of another table: civicrm_events_in_cart
362 $query = preg_replace("/([^'\"])({$table})(\z|[^a-z_'\"])/", "\\1\\2{$dbLocale}\\3", $query);
364 // uncomment the below to rewrite the civicrm_value_* queries
365 // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
370 * @param null $version
375 public static function schemaStructureTables($version = NULL, $force = FALSE) {
376 static $_tables = NULL;
377 if ($_tables === NULL ||
$force) {
379 $latest = self
::getLatestSchema($version);
380 // FIXME: Doing require_once is a must here because a call like CRM_Core_I18n_SchemaStructure_4_1_0 makes
381 // class loader look for file like - CRM/Core/I18n/SchemaStructure/4/1/0.php which is not what we want to be loaded
382 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
383 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
384 $tables =& $class::tables();
387 $tables = CRM_Core_I18n_SchemaStructure
::tables();
399 public static function getLatestSchema($version) {
400 // remove any .upgrade sub-str from version. Makes it easy to do version_compare & give right result
401 $version = str_ireplace(".upgrade", "", $version);
403 // fetch all the SchemaStructure versions we ship and sort by version
405 foreach (scandir(dirname(__FILE__
)) as $file) {
407 if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
408 $schemas[] = str_replace('_', '.', $matches[1]);
411 usort($schemas, 'version_compare');
413 // find the latest schema structure older than (or equal to) $version
415 $latest = array_pop($schemas);
416 } while (version_compare($latest, $version, '>'));
418 return str_replace('.', '_', $latest);
422 * CREATE INDEX queries for a given locale and table.
424 * @param string $locale
425 * locale for which the queries should be created (null to create original indices).
426 * @param string $table
427 * table for which the queries should be created.
428 * @param string $class
429 * schema structure class to use.
432 * array of CREATE INDEX queries
434 private static function createIndexQueries($locale, $table, $class = 'CRM_Core_I18n_SchemaStructure') {
435 $indices =& $class::indices();
436 $columns =& $class::columns();
437 if (!isset($indices[$table])) {
442 foreach ($indices[$table] as $index) {
443 $unique = isset($index['unique']) && $index['unique'] ?
'UNIQUE' : '';
444 foreach ($index['field'] as $i => $col) {
445 // if a given column is localizable, extend its name with the locale
446 if ($locale and isset($columns[$table][$col])) {
447 $index['field'][$i] = "{$col}_{$locale}";
450 $cols = implode(', ', $index['field']);
451 $name = $index['name'];
453 $name .= '_' . $locale;
455 // CRM-7854: skip existing indices
456 if (CRM_Core_DAO
::checkConstraintExists($table, $name)) {
459 $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
465 * CREATE VIEW query for a given locale and table.
467 * @param string $locale
468 * locale of the view.
469 * @param string $table
471 * @param CRM_Core_DAO $dao
472 * A DAO object to run DESCRIBE queries.
473 * @param string $class
474 * schema structure class to use.
475 * @param bool $isUpgradeMode
476 * Are we in upgrade mode therefore only build based off table not class
478 * array of CREATE INDEX queries
480 private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) {
481 $columns =& $class::columns();
484 $dao->query("DESCRIBE {$table}", FALSE);
485 while ($dao->fetch()) {
486 // view non-internationalized columns directly
487 if (!in_array($dao->Field
, array_keys($columns[$table])) and
488 !preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Field
)
490 $cols[] = '`' . $dao->Field
. '`';
492 $tableCols[] = $dao->Field
;
494 // view intrernationalized columns through an alias
495 foreach ($columns[$table] as $column => $_) {
496 if (!$isUpgradeMode) {
497 $cols[] = "`{$column}_{$locale}` `{$column}`";
499 elseif (in_array("{$column}_{$locale}", $tableCols)) {
500 $cols[] = "`{$column}_{$locale}` `{$column}`";
503 return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
508 * @param null $tableName
510 public static function triggerInfo(&$info, $tableName = NULL) {
511 // get the current supported locales
512 $domain = new CRM_Core_DAO_Domain();
514 if (empty($domain->locales
)) {
518 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
519 $locale = array_pop($locales);
522 if (count($locales) == 0) {
526 $currentVer = CRM_Core_BAO_Domain
::version(TRUE);
528 if ($currentVer && CRM_Core_Config
::isUpgradeMode()) {
529 // take exact version so that proper schema structure file in invoked
530 $latest = self
::getLatestSchema($currentVer);
531 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
532 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
535 $class = 'CRM_Core_I18n_SchemaStructure';
538 $columns =& $class::columns();
540 foreach ($columns as $table => $hash) {
549 foreach ($hash as $column => $_) {
550 $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
551 foreach ($locales as $old) {
552 $trigger[] = "IF NEW.{$column}_{$old} IS NULL THEN SET NEW.{$column}_{$old} = NEW.{$column}_{$locale}; END IF;";
554 foreach ($locales as $old) {
555 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
556 foreach (array_merge($locales, [
562 $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
565 $trigger[] = 'END IF;';
568 $sql = implode(' ', $trigger);
572 'event' => ['UPDATE'],
577 // take care of the ON INSERT triggers
578 foreach ($columns as $table => $hash) {
580 foreach ($hash as $column => $_) {
581 $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
582 foreach ($locales as $old) {
583 $trigger[] = "SET NEW.{$column}_{$old} = NEW.{$column}_{$locale};";
585 foreach ($locales as $old) {
586 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
587 foreach (array_merge($locales, [
593 $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
596 $trigger[] = 'END IF;';
599 $sql = implode(' ', $trigger);
603 'event' => ['INSERT'],