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     $domain = new CRM_Core_DAO_Domain();
  48     $domain->find(TRUE);
  49
  50     // break early if the db is already multi-lang
  51     if ($domain->locales) {
  52       return;
  53     }
  54
  55     $dao = new CRM_Core_DAO();
  56
  57     // build the column-adding SQL queries
  58     $columns = CRM_Core_I18n_SchemaStructure::columns();
  59     $indices = CRM_Core_I18n_SchemaStructure::indices();
  60     $queries = [];
  61     foreach ($columns as $table => $hash) {
  62       // drop old indices
  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}";
  67           }
  68         }
  69       }
  70       // deal with columns
  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}";
  76         }
  77       }
  78
  79       // add view
  80       $queries[] = self::createViewQuery($locale, $table, $dao);
  81
  82       // add new indices
  83       $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
  84     }
  85
  86     // execute the queries without i18n rewriting
  87     foreach ($queries as $query) {
  88       $dao->query($query, FALSE);
  89     }
  90
  91     // update civicrm_domain.locales
  92     $domain->locales = $locale;
  93     $domain->save();
  94
  95     // CRM-21627 Updates the $dbLocale
  96     CRM_Core_BAO_ConfigSetting::applyLocale(Civi::settings($domain->id), $domain->locales);
  97   }
  98
  99   /**
 100    * Switch database from multi-lang back to single (by dropping
 101    * additional columns and views and retaining only the selected locale).
 102    *
 103    * @param string $retain
 104    *   the locale to retain.
 105    */
 106   public static function makeSinglelingual($retain) {
 107     $domain = new CRM_Core_DAO_Domain();
 108     $domain->find(TRUE);
 109     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 110
 111     // break early if the db is already single-lang
 112     if (!$locales) {
 113       return;
 114     }
 115
 116     // lets drop all triggers first
 117     $logging = new CRM_Logging_Schema();
 118     $logging->dropTriggers();
 119
 120     // turn subsequent tables singlelingual
 121     $tables = CRM_Core_I18n_SchemaStructure::tables();
 122     foreach ($tables as $table) {
 123       self::makeSinglelingualTable($retain, $table);
 124     }
 125
 126     // update civicrm_domain.locales
 127     $domain->locales = 'NULL';
 128     $domain->save();
 129
 130     //CRM-6963 -fair assumption.
 131     global $dbLocale;
 132     $dbLocale = '';
 133
 134     // now lets rebuild all triggers
 135     CRM_Core_DAO::triggerRebuild();
 136   }
 137
 138   /**
 139    * Switch a given table from multi-lang to single (by retaining only the selected locale).
 140    *
 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.
 147    *
 148    * @param array $triggers
 149    */
 150   public static function makeSinglelingualTable(
 151     $retain,
 152     $table,
 153     $class = 'CRM_Core_I18n_SchemaStructure',
 154     $triggers = []
 155   ) {
 156     $domain = new CRM_Core_DAO_Domain();
 157     $domain->find(TRUE);
 158     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 159
 160     // break early if the db is already single-lang
 161     if (!$locales) {
 162       return;
 163     }
 164
 165     $columns =& $class::columns();
 166     $indices =& $class::indices();
 167     $queries = [];
 168     $dropQueries = [];
 169     // drop 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}";
 174         }
 175       }
 176     }
 177
 178     $dao = new CRM_Core_DAO();
 179     // deal with columns
 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}";
 185         }
 186       }
 187     }
 188
 189     // drop views
 190     foreach ($locales as $loc) {
 191       $queries[] = "DROP VIEW IF EXISTS {$table}_{$loc}";
 192     }
 193
 194     // add original indices
 195     $queries = array_merge($queries, self::createIndexQueries(NULL, $table));
 196
 197     // execute the queries without i18n rewriting
 198     $dao = new CRM_Core_DAO();
 199     foreach ($queries as $query) {
 200       $dao->query($query, FALSE);
 201     }
 202
 203     foreach ($dropQueries as $query) {
 204       $dao->query($query, FALSE);
 205     }
 206
 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}");
 214         }
 215       }
 216
 217       // invoke the meta trigger creation call
 218       CRM_Core_DAO::triggerRebuild($table);
 219     }
 220   }
 221
 222   /**
 223    * Add a new locale to a multi-lang db, setting
 224    * its values to the current default locale.
 225    *
 226    * @param string $locale
 227    *   the new locale to add.
 228    * @param string $source
 229    *   the locale to copy from.
 230    */
 231   public static function addLocale($locale, $source) {
 232     // get the current supported locales
 233     $domain = new CRM_Core_DAO_Domain();
 234     $domain->find(TRUE);
 235     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 236
 237     // break early if the locale is already supported
 238     if (in_array($locale, $locales)) {
 239       return;
 240     }
 241
 242     $dao = new CRM_Core_DAO();
 243
 244     // build the required SQL queries
 245     $columns = CRM_Core_I18n_SchemaStructure::columns();
 246     $indices = CRM_Core_I18n_SchemaStructure::indices();
 247     $queries = [];
 248     foreach ($columns as $table => $hash) {
 249       // add new columns
 250       foreach ($hash as $column => $type) {
 251         // CRM-7854: skip existing columns
 252         if (CRM_Core_BAO_SchemaHandler::checkIfFieldExists($table, "{$column}_{$locale}", FALSE)) {
 253           continue;
 254         }
 255         $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
 256         $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
 257       }
 258
 259       // add view
 260       $queries[] = self::createViewQuery($locale, $table, $dao);
 261
 262       // add new indices
 263       $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
 264     }
 265
 266     // execute the queries without i18n rewriting
 267     foreach ($queries as $query) {
 268       $dao->query($query, FALSE);
 269     }
 270
 271     // update civicrm_domain.locales
 272     $locales[] = $locale;
 273     $domain->locales = implode(CRM_Core_DAO::VALUE_SEPARATOR, $locales);
 274     $domain->save();
 275
 276     // invoke the meta trigger creation call
 277     CRM_Core_DAO::triggerRebuild();
 278   }
 279
 280   /**
 281    * Rebuild multilingual indices, views and triggers (useful for upgrades)
 282    *
 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
 289    */
 290   public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) {
 291     if ($version) {
 292       $latest = self::getLatestSchema($version);
 293       require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
 294       $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
 295     }
 296     else {
 297       $class = 'CRM_Core_I18n_SchemaStructure';
 298     }
 299     $indices =& $class::indices();
 300     $tables =& $class::tables();
 301     $queries = [];
 302     $dao = new CRM_Core_DAO();
 303
 304     // get all of the already existing indices
 305     $existing = [];
 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;
 312         }
 313       }
 314     }
 315
 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])) {
 322             $queries[] = $query;
 323           }
 324         }
 325       }
 326     }
 327
 328     // rebuild views
 329     foreach ($locales as $locale) {
 330       foreach ($tables as $table) {
 331         $queries[] = self::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode);
 332       }
 333     }
 334
 335     // rebuild triggers
 336     $last = array_pop($locales);
 337
 338     foreach ($queries as $query) {
 339       $dao->query($query, FALSE);
 340     }
 341
 342     // invoke the meta trigger creation call
 343     CRM_Core_DAO::triggerRebuild();
 344   }
 345
 346   /**
 347    * Rewrite SQL query to use views to access tables with localized columns.
 348    *
 349    * @param string $query
 350    *   the query for rewrite.
 351    *
 352    * @return string
 353    *   the rewritten query
 354    */
 355   public static function rewriteQuery($query) {
 356     global $dbLocale;
 357     $tables = self::schemaStructureTables();
 358     foreach ($tables as $table) {
 359       // CRM-19093
 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);
 363     }
 364     // uncomment the below to rewrite the civicrm_value_* queries
 365     // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
 366     return $query;
 367   }
 368
 369   /**
 370    * @param null $version
 371    * @param bool $force
 372    *
 373    * @return array
 374    */
 375   public static function schemaStructureTables($version = NULL, $force = FALSE) {
 376     static $_tables = NULL;
 377     if ($_tables === NULL || $force) {
 378       if ($version) {
 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();
 385       }
 386       else {
 387         $tables = CRM_Core_I18n_SchemaStructure::tables();
 388       }
 389       $_tables = $tables;
 390     }
 391     return $_tables;
 392   }
 393
 394   /**
 395    * @param $version
 396    *
 397    * @return mixed
 398    */
 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);
 402
 403     // fetch all the SchemaStructure versions we ship and sort by version
 404     $schemas = [];
 405     foreach (scandir(dirname(__FILE__)) as $file) {
 406       $matches = [];
 407       if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
 408         $schemas[] = str_replace('_', '.', $matches[1]);
 409       }
 410     }
 411     usort($schemas, 'version_compare');
 412
 413     // find the latest schema structure older than (or equal to) $version
 414     do {
 415       $latest = array_pop($schemas);
 416     } while (version_compare($latest, $version, '>'));
 417
 418     return str_replace('.', '_', $latest);
 419   }
 420
 421   /**
 422    * CREATE INDEX queries for a given locale and table.
 423    *
 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.
 430    *
 431    * @return array
 432    *   array of CREATE INDEX queries
 433    */
 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])) {
 438       return [];
 439     }
 440
 441     $queries = [];
 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}";
 448         }
 449       }
 450       $cols = implode(', ', $index['field']);
 451       $name = $index['name'];
 452       if ($locale) {
 453         $name .= '_' . $locale;
 454       }
 455       // CRM-7854: skip existing indices
 456       if (CRM_Core_DAO::checkConstraintExists($table, $name)) {
 457         continue;
 458       }
 459       $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
 460     }
 461     return $queries;
 462   }
 463
 464   /**
 465    * CREATE VIEW query for a given locale and table.
 466    *
 467    * @param string $locale
 468    *   locale of the view.
 469    * @param string $table
 470    *   table of the view.
 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
 477    * @return array
 478    *   array of CREATE INDEX queries
 479    */
 480   private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) {
 481     $columns =& $class::columns();
 482     $cols = [];
 483     $tableCols = [];
 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)
 489       ) {
 490         $cols[] = $dao->Field;
 491       }
 492       $tableCols[] = $dao->Field;
 493     }
 494     // view intrernationalized columns through an alias
 495     foreach ($columns[$table] as $column => $_) {
 496       if (!$isUpgradeMode) {
 497         $cols[] = "{$column}_{$locale} {$column}";
 498       }
 499       elseif (in_array("{$column}_{$locale}", $tableCols)) {
 500         $cols[] = "{$column}_{$locale} {$column}";
 501       }
 502     }
 503     return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
 504   }
 505
 506   /**
 507    * @param $info
 508    * @param null $tableName
 509    */
 510   public static function triggerInfo(&$info, $tableName = NULL) {
 511     // get the current supported locales
 512     $domain = new CRM_Core_DAO_Domain();
 513     $domain->find(TRUE);
 514     if (empty($domain->locales)) {
 515       return;
 516     }
 517
 518     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 519     $locale = array_pop($locales);
 520
 521     // CRM-10027
 522     if (count($locales) == 0) {
 523       return;
 524     }
 525
 526     $currentVer = CRM_Core_BAO_Domain::version(TRUE);
 527
 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}";
 533     }
 534     else {
 535       $class = 'CRM_Core_I18n_SchemaStructure';
 536     }
 537
 538     $columns =& $class::columns();
 539
 540     foreach ($columns as $table => $hash) {
 541       if ($tableName &&
 542         $tableName != $table
 543       ) {
 544         continue;
 545       }
 546
 547       $trigger = [];
 548
 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;";
 553         }
 554         foreach ($locales as $old) {
 555           $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
 556           foreach (array_merge($locales, [
 557             $locale,
 558           ]) as $loc) {
 559             if ($loc == $old) {
 560               continue;
 561             }
 562             $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
 563           }
 564         }
 565         $trigger[] = 'END IF;';
 566       }
 567
 568       $sql = implode(' ', $trigger);
 569       $info[] = [
 570         'table' => [$table],
 571         'when' => 'BEFORE',
 572         'event' => ['UPDATE'],
 573         'sql' => $sql,
 574       ];
 575     }
 576
 577     // take care of the ON INSERT triggers
 578     foreach ($columns as $table => $hash) {
 579       $trigger = [];
 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};";
 584         }
 585         foreach ($locales as $old) {
 586           $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
 587           foreach (array_merge($locales, [
 588             $locale,
 589           ]) as $loc) {
 590             if ($loc == $old) {
 591               continue;
 592             }
 593             $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
 594           }
 595         }
 596         $trigger[] = 'END IF;';
 597       }
 598
 599       $sql = implode(' ', $trigger);
 600       $info[] = [
 601         'table' => [$table],
 602         'when' => 'BEFORE',
 603         'event' => ['INSERT'],
 604         'sql' => $sql,
 605       ];
 606     }
 607   }
 608
 609 }