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     $domain = new CRM_Core_DAO_Domain();
  24     $domain->find(TRUE);
  25     if (!$domain->locales) {
  26       return;
  27     }
  28
  29     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
  30     $tables = CRM_Core_I18n_SchemaStructure::tables();
  31
  32     foreach ($locales as $locale) {
  33       foreach ($tables as $table) {
  34         CRM_Core_DAO::executeQuery("DROP VIEW IF EXISTS {$table}_{$locale}");
  35       }
  36     }
  37   }
  38
  39   /**
  40    * Switch database from single-lang to multi (by adding
  41    * the first language and dropping the original columns).
  42    *
  43    * @param string $locale
  44    *   the first locale to create (migrate to).
  45    */
  46   public static function makeMultilingual($locale) {
  47     $isUpdateDone = FALSE;
  48     $domain = new CRM_Core_BAO_Domain();
  49     $domain->find();
  50     $domains = [];
  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;
  55     }
  56     foreach ($domains as $domain) {
  57       // skip if the domain is already multi-lang.
  58       if ($domain->locales) {
  59         continue;
  60       }
  61
  62       if (!$isUpdateDone) {
  63         $isUpdateDone = self::alterTablesToSupportMultilingual($locale);
  64       }
  65
  66       // update civicrm_domain.locales
  67       $domain->locales = $locale;
  68       $domain->save();
  69
  70       // CRM-21627 Updates the $dbLocale
  71       CRM_Core_BAO_ConfigSetting::applyLocale(Civi::settings($domain->id), $domain->locales);
  72     }
  73   }
  74
  75   /**
  76    * Switch database from multi-lang back to single (by dropping
  77    * additional columns and views and retaining only the selected locale).
  78    *
  79    * @param string $retain
  80    *   the locale to retain.
  81    */
  82   public static function makeSinglelingual($retain) {
  83     $domain = new CRM_Core_DAO_Domain();
  84     $domain->find(TRUE);
  85     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
  86
  87     // break early if the db is already single-lang
  88     if (!$locales) {
  89       return;
  90     }
  91
  92     // lets drop all triggers first
  93     $logging = new CRM_Logging_Schema();
  94     $logging->dropTriggers();
  95
  96     // turn subsequent tables singlelingual
  97     $tables = CRM_Core_I18n_SchemaStructure::tables();
  98     foreach ($tables as $table) {
  99       self::makeSinglelingualTable($retain, $table);
 100     }
 101
 102     // update civicrm_domain.locales
 103     $domain->locales = 'NULL';
 104     $domain->save();
 105
 106     //CRM-6963 -fair assumption.
 107     global $dbLocale;
 108     $dbLocale = '';
 109
 110     // now lets rebuild all triggers
 111     CRM_Core_DAO::triggerRebuild();
 112   }
 113
 114   /**
 115    * Switch a given table from multi-lang to single (by retaining only the selected locale).
 116    *
 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.
 123    *
 124    * @param array $triggers
 125    */
 126   public static function makeSinglelingualTable(
 127     $retain,
 128     $table,
 129     $class = 'CRM_Core_I18n_SchemaStructure',
 130     $triggers = []
 131   ) {
 132     $domain = new CRM_Core_DAO_Domain();
 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->find(TRUE);
 211     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 212
 213     // break early if the locale is already supported
 214     if (in_array($locale, $locales)) {
 215       return;
 216     }
 217
 218     $dao = new CRM_Core_DAO();
 219
 220     // build the required SQL queries
 221     $columns = CRM_Core_I18n_SchemaStructure::columns();
 222     $indices = CRM_Core_I18n_SchemaStructure::indices();
 223     $queries = [];
 224     foreach ($columns as $table => $hash) {
 225       // add new columns
 226       foreach ($hash as $column => $type) {
 227         // CRM-7854: skip existing columns
 228         if (CRM_Core_BAO_SchemaHandler::checkIfFieldExists($table, "{$column}_{$locale}", FALSE)) {
 229           continue;
 230         }
 231         $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
 232         $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
 233       }
 234
 235       // add view
 236       $queries[] = self::createViewQuery($locale, $table, $dao);
 237
 238       // add new indices
 239       $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
 240     }
 241
 242     // execute the queries without i18n rewriting
 243     foreach ($queries as $query) {
 244       $dao->query($query, FALSE);
 245     }
 246
 247     // update civicrm_domain.locales
 248     $locales[] = $locale;
 249     $domain->locales = implode(CRM_Core_DAO::VALUE_SEPARATOR, $locales);
 250     $domain->save();
 251
 252     // invoke the meta trigger creation call
 253     CRM_Core_DAO::triggerRebuild();
 254   }
 255
 256   /**
 257    * Rebuild multilingual indices, views and triggers (useful for upgrades)
 258    *
 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
 265    */
 266   public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) {
 267     if ($version) {
 268       $latest = self::getLatestSchema($version);
 269       require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
 270       $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
 271     }
 272     else {
 273       $class = 'CRM_Core_I18n_SchemaStructure';
 274     }
 275     $indices =& $class::indices();
 276     $tables =& $class::tables();
 277     $queries = [];
 278     $dao = new CRM_Core_DAO();
 279
 280     // get all of the already existing indices
 281     $existing = [];
 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;
 288         }
 289       }
 290     }
 291
 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])) {
 298             $queries[] = $query;
 299           }
 300         }
 301       }
 302     }
 303
 304     // rebuild views
 305     foreach ($locales as $locale) {
 306       foreach ($tables as $table) {
 307         $queries[] = self::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode);
 308       }
 309     }
 310
 311     // rebuild triggers
 312     $last = array_pop($locales);
 313
 314     foreach ($queries as $query) {
 315       $dao->query($query, FALSE);
 316     }
 317
 318     // invoke the meta trigger creation call
 319     CRM_Core_DAO::triggerRebuild();
 320   }
 321
 322   /**
 323    * Rewrite SQL query to use views to access tables with localized columns.
 324    *
 325    * @param string $query
 326    *   the query for rewrite.
 327    *
 328    * @return string
 329    *   the rewritten query
 330    */
 331   public static function rewriteQuery($query) {
 332     global $dbLocale;
 333     $tables = self::schemaStructureTables();
 334     foreach ($tables as $table) {
 335       // CRM-19093
 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);
 339     }
 340     // uncomment the below to rewrite the civicrm_value_* queries
 341     // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
 342     return $query;
 343   }
 344
 345   /**
 346    * @param null $version
 347    * @param bool $force
 348    *
 349    * @return array
 350    */
 351   public static function schemaStructureTables($version = NULL, $force = FALSE) {
 352     static $_tables = NULL;
 353     if ($_tables === NULL || $force) {
 354       if ($version) {
 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();
 361       }
 362       else {
 363         $tables = CRM_Core_I18n_SchemaStructure::tables();
 364       }
 365       $_tables = $tables;
 366     }
 367     return $_tables;
 368   }
 369
 370   /**
 371    * @param $version
 372    *
 373    * @return mixed
 374    */
 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);
 378
 379     // fetch all the SchemaStructure versions we ship and sort by version
 380     $schemas = [];
 381     foreach (scandir(dirname(__FILE__)) as $file) {
 382       $matches = [];
 383       if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
 384         $schemas[] = str_replace('_', '.', $matches[1]);
 385       }
 386     }
 387     usort($schemas, 'version_compare');
 388
 389     // find the latest schema structure older than (or equal to) $version
 390     do {
 391       $latest = array_pop($schemas);
 392     } while (version_compare($latest, $version, '>'));
 393
 394     return str_replace('.', '_', $latest);
 395   }
 396
 397   /**
 398    * CREATE INDEX queries for a given locale and table.
 399    *
 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.
 406    *
 407    * @return array
 408    *   array of CREATE INDEX queries
 409    */
 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])) {
 414       return [];
 415     }
 416
 417     $queries = [];
 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}";
 424         }
 425       }
 426       $cols = implode(', ', $index['field']);
 427       $name = $index['name'];
 428       if ($locale) {
 429         $name .= '_' . $locale;
 430       }
 431       // CRM-7854: skip existing indices
 432       if (CRM_Core_DAO::checkConstraintExists($table, $name)) {
 433         continue;
 434       }
 435       $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
 436     }
 437     return $queries;
 438   }
 439
 440   /**
 441    * CREATE VIEW query for a given locale and table.
 442    *
 443    * @param string $locale
 444    *   locale of the view.
 445    * @param string $table
 446    *   table of the view.
 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
 453    * @return array
 454    *   array of CREATE INDEX queries
 455    */
 456   private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) {
 457     $columns =& $class::columns();
 458     $cols = [];
 459     $tableCols = [];
 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)
 465       ) {
 466         $cols[] = '`' . $dao->Field . '`';
 467       }
 468       $tableCols[] = $dao->Field;
 469     }
 470     // view intrernationalized columns through an alias
 471     foreach ($columns[$table] as $column => $_) {
 472       if (!$isUpgradeMode) {
 473         $cols[] = "`{$column}_{$locale}` `{$column}`";
 474       }
 475       elseif (in_array("{$column}_{$locale}", $tableCols)) {
 476         $cols[] = "`{$column}_{$locale}` `{$column}`";
 477       }
 478     }
 479     return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
 480   }
 481
 482   /**
 483    * @param $info
 484    * @param null $tableName
 485    */
 486   public static function triggerInfo(&$info, $tableName = NULL) {
 487     // get the current supported locales
 488     $domain = new CRM_Core_DAO_Domain();
 489     $domain->find(TRUE);
 490     if (empty($domain->locales)) {
 491       return;
 492     }
 493
 494     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 495     $locale = array_pop($locales);
 496
 497     // CRM-10027
 498     if (count($locales) == 0) {
 499       return;
 500     }
 501
 502     $currentVer = CRM_Core_BAO_Domain::version(TRUE);
 503
 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}";
 509     }
 510     else {
 511       $class = 'CRM_Core_I18n_SchemaStructure';
 512     }
 513
 514     $columns =& $class::columns();
 515
 516     foreach ($columns as $table => $hash) {
 517       if ($tableName &&
 518         $tableName != $table
 519       ) {
 520         continue;
 521       }
 522
 523       $trigger = [];
 524
 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;";
 529         }
 530         foreach ($locales as $old) {
 531           $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
 532           foreach (array_merge($locales, [
 533             $locale,
 534           ]) as $loc) {
 535             if ($loc == $old) {
 536               continue;
 537             }
 538             $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
 539           }
 540         }
 541         $trigger[] = 'END IF;';
 542       }
 543
 544       $sql = implode(' ', $trigger);
 545       $info[] = [
 546         'table' => [$table],
 547         'when' => 'BEFORE',
 548         'event' => ['UPDATE'],
 549         'sql' => $sql,
 550       ];
 551     }
 552
 553     // take care of the ON INSERT triggers
 554     foreach ($columns as $table => $hash) {
 555       $trigger = [];
 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};";
 560         }
 561         foreach ($locales as $old) {
 562           $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
 563           foreach (array_merge($locales, [
 564             $locale,
 565           ]) as $loc) {
 566             if ($loc == $old) {
 567               continue;
 568             }
 569             $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
 570           }
 571         }
 572         $trigger[] = 'END IF;';
 573       }
 574
 575       $sql = implode(' ', $trigger);
 576       $info[] = [
 577         'table' => [$table],
 578         'when' => 'BEFORE',
 579         'event' => ['INSERT'],
 580         'sql' => $sql,
 581       ];
 582     }
 583   }
 584
 585   /**
 586    * Alter tables to the structure to support multilingual.
 587    *
 588    * This alters the db structure to use language specific field names for
 589    * localised fields and adds the relevant views.
 590    *
 591    * @param string $locale
 592    *
 593    * @return bool
 594    */
 595   protected static function alterTablesToSupportMultilingual($locale): bool {
 596     $dao = new CRM_Core_DAO();
 597
 598     // build the column-adding SQL queries
 599     $columns = CRM_Core_I18n_SchemaStructure::columns();
 600     $indices = CRM_Core_I18n_SchemaStructure::indices();
 601     $queries = [];
 602     foreach ($columns as $table => $hash) {
 603       // drop old indices
 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}";
 608           }
 609         }
 610       }
 611       // deal with columns
 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}";
 617         }
 618       }
 619
 620       // add view
 621       $queries[] = self::createViewQuery($locale, $table, $dao);
 622
 623       // add new indices
 624       $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
 625     }
 626
 627     // execute the queries without i18n rewriting
 628     foreach ($queries as $query) {
 629       $dao->query($query, FALSE);
 630     }
 631     return TRUE;
 632   }
 633
 634 }