CRM/Core/I18n/Schema.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Core_I18n_Schema {
  18
  19   /**
  20    * Drop all views (for use by CRM_Core_DAO::dropAllTables() mostly).
  21    */
  22   public static function dropAllViews() {
  23     $locales = CRM_Core_I18n::getMultilingual();
  24     if (!$locales) {
  25       return;
  26     }
  27
  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    *
  41    * @param string $locale
  42    *   the first locale to create (migrate to).
  43    */
  44   public static function makeMultilingual($locale) {
  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;
  58       }
  59
  60       if (!$isUpdateDone) {
  61         $isUpdateDone = self::alterTablesToSupportMultilingual($locale);
  62       }
  63
  64       // update civicrm_domain.locales
  65       $domain->locales = $locale;
  66       $domain->save();
  67
  68       // CRM-21627 Updates the $dbLocale
  69       CRM_Core_BAO_ConfigSetting::applyLocale(Civi::settings($domain->id), $domain->locales);
  70     }
  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    *
  77    * @param string $retain
  78    *   the locale to retain.
  79    */
  80   public static function makeSinglelingual($retain) {
  81     $domain = new CRM_Core_DAO_Domain();
  82     $domain->id = CRM_Core_Config::domainID();
  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
  92     $logging = new CRM_Logging_Schema();
  93     $logging->dropTriggers();
  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
 110     CRM_Core_DAO::triggerRebuild();
 111   }
 112
 113   /**
 114    * Switch a given table from multi-lang to single (by retaining only the selected locale).
 115    *
 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.
 122    *
 123    * @param array $triggers
 124    */
 125   public static function makeSinglelingualTable(
 126     $retain,
 127     $table,
 128     $class = 'CRM_Core_I18n_SchemaStructure',
 129     $triggers = []
 130   ) {
 131     $domain = new CRM_Core_DAO_Domain();
 132     $domain->id = CRM_Core_Config::domainID();
 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
 141     $columns =& $class::columns();
 142     $indices =& $class::indices();
 143     $queries = [];
 144     $dropQueries = [];
 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
 154     $dao = new CRM_Core_DAO();
 155     // deal with columns
 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}";
 161         }
 162       }
 163     }
 164
 165     // drop views
 166     foreach ($locales as $loc) {
 167       $queries[] = "DROP VIEW IF EXISTS {$table}_{$loc}";
 168     }
 169
 170     // add original indices
 171     $queries = array_merge($queries, self::createIndexQueries(NULL, $table));
 172
 173     // execute the queries without i18n rewriting
 174     $dao = new CRM_Core_DAO();
 175     foreach ($queries as $query) {
 176       $dao->query($query, FALSE);
 177     }
 178
 179     foreach ($dropQueries as $query) {
 180       $dao->query($query, FALSE);
 181     }
 182
 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}");
 190         }
 191       }
 192
 193       // invoke the meta trigger creation call
 194       CRM_Core_DAO::triggerRebuild($table);
 195     }
 196   }
 197
 198   /**
 199    * Add a new locale to a multi-lang db, setting
 200    * its values to the current default locale.
 201    *
 202    * @param string $locale
 203    *   the new locale to add.
 204    * @param string $source
 205    *   the locale to copy from.
 206    */
 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();
 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();
 224     $queries = [];
 225     foreach ($columns as $table => $hash) {
 226       // add new columns
 227       foreach ($hash as $column => $type) {
 228         // CRM-7854: skip existing columns
 229         if (CRM_Core_BAO_SchemaHandler::checkIfFieldExists($table, "{$column}_{$locale}", FALSE)) {
 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    *
 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
 266    */
 267   public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) {
 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     }
 276     $indices =& $class::indices();
 277     $tables =& $class::tables();
 278     $queries = [];
 279     $dao = new CRM_Core_DAO();
 280
 281     // get all of the already existing indices
 282     $existing = [];
 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;
 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) {
 308         $queries[] = self::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode);
 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    *
 326    * @param string $query
 327    *   the query for rewrite.
 328    *
 329    * @return string
 330    *   the rewritten query
 331    */
 332   public static function rewriteQuery($query) {
 333     global $dbLocale;
 334     $tables = self::schemaStructureTables();
 335     foreach ($tables as $table) {
 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
 339       $query = preg_replace("/([^'\"])({$table})(\z|[^a-z_'\"])/", "\\1\\2{$dbLocale}\\3", $query);
 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
 346   /**
 347    * @param null $version
 348    * @param bool $force
 349    *
 350    * @return array
 351    */
 352   public static function schemaStructureTables($version = NULL, $force = FALSE) {
 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}";
 361         $tables =& $class::tables();
 362       }
 363       else {
 364         $tables = CRM_Core_I18n_SchemaStructure::tables();
 365       }
 366       $_tables = $tables;
 367     }
 368     return $_tables;
 369   }
 370
 371   /**
 372    * @param $version
 373    *
 374    * @return mixed
 375    */
 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);
 379
 380     // fetch all the SchemaStructure versions we ship and sort by version
 381     $schemas = [];
 382     foreach (scandir(dirname(__FILE__)) as $file) {
 383       $matches = [];
 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   /**
 399    * CREATE INDEX queries for a given locale and table.
 400    *
 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.
 407    *
 408    * @return array
 409    *   array of CREATE INDEX queries
 410    */
 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])) {
 415       return [];
 416     }
 417
 418     $queries = [];
 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   /**
 442    * CREATE VIEW query for a given locale and table.
 443    *
 444    * @param string $locale
 445    *   locale of the view.
 446    * @param string $table
 447    *   table of the view.
 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
 454    * @return array
 455    *   array of CREATE INDEX queries
 456    */
 457   private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) {
 458     $columns =& $class::columns();
 459     $cols = [];
 460     $tableCols = [];
 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)
 466       ) {
 467         $cols[] = '`' . $dao->Field . '`';
 468       }
 469       $tableCols[] = $dao->Field;
 470     }
 471     // view internationalized columns through an alias
 472     foreach ($columns[$table] as $column => $_) {
 473       if (!$isUpgradeMode) {
 474         $cols[] = "`{$column}_{$locale}` `{$column}`";
 475       }
 476       elseif (in_array("{$column}_{$locale}", $tableCols)) {
 477         $cols[] = "`{$column}_{$locale}` `{$column}`";
 478       }
 479     }
 480     return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
 481   }
 482
 483   /**
 484    * @param $info
 485    * @param null $tableName
 486    */
 487   public static function triggerInfo(&$info, $tableName = NULL) {
 488     // get the current supported locales
 489     $locales = CRM_Core_I18n::getMultilingual();
 490     if (!$locales) {
 491       return;
 492     }
 493
 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
 513     $columns =& $class::columns();
 514
 515     foreach ($columns as $table => $hash) {
 516       if ($tableName &&
 517         $tableName != $table
 518       ) {
 519         continue;
 520       }
 521
 522       $trigger = [];
 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";
 531           foreach (array_merge($locales, [
 532             $locale,
 533           ]) as $loc) {
 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);
 544       $info[] = [
 545         'table' => [$table],
 546         'when' => 'BEFORE',
 547         'event' => ['UPDATE'],
 548         'sql' => $sql,
 549       ];
 550     }
 551
 552     // take care of the ON INSERT triggers
 553     foreach ($columns as $table => $hash) {
 554       $trigger = [];
 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";
 562           foreach (array_merge($locales, [
 563             $locale,
 564           ]) as $loc) {
 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);
 575       $info[] = [
 576         'table' => [$table],
 577         'when' => 'BEFORE',
 578         'event' => ['INSERT'],
 579         'sql' => $sql,
 580       ];
 581     }
 582   }
 583
 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
 633 }