CRM/Core/I18n/Schema.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2018
  32  */
  33 class CRM_Core_I18n_Schema {
  34
  35   /**
  36    * Drop all views (for use by CRM_Core_DAO::dropAllTables() mostly).
  37    */
  38   public static function dropAllViews() {
  39     $domain = new CRM_Core_DAO_Domain();
  40     $domain->find(TRUE);
  41     if (!$domain->locales) {
  42       return;
  43     }
  44
  45     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
  46     $tables = CRM_Core_I18n_SchemaStructure::tables();
  47
  48     foreach ($locales as $locale) {
  49       foreach ($tables as $table) {
  50         CRM_Core_DAO::executeQuery("DROP VIEW IF EXISTS {$table}_{$locale}");
  51       }
  52     }
  53   }
  54
  55   /**
  56    * Switch database from single-lang to multi (by adding
  57    * the first language and dropping the original columns).
  58    *
  59    * @param string $locale
  60    *   the first locale to create (migrate to).
  61    */
  62   public static function makeMultilingual($locale) {
  63     $domain = new CRM_Core_DAO_Domain();
  64     $domain->find(TRUE);
  65
  66     // break early if the db is already multi-lang
  67     if ($domain->locales) {
  68       return;
  69     }
  70
  71     $dao = new CRM_Core_DAO();
  72
  73     // build the column-adding SQL queries
  74     $columns = CRM_Core_I18n_SchemaStructure::columns();
  75     $indices = CRM_Core_I18n_SchemaStructure::indices();
  76     $queries = array();
  77     foreach ($columns as $table => $hash) {
  78       // drop old indices
  79       if (isset($indices[$table])) {
  80         foreach ($indices[$table] as $index) {
  81           if (CRM_Core_BAO_SchemaHandler::checkIfIndexExists($table, $index['name'])) {
  82             $queries[] = "DROP INDEX {$index['name']} ON {$table}";
  83           }
  84         }
  85       }
  86       // deal with columns
  87       foreach ($hash as $column => $type) {
  88         $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
  89         if (CRM_Core_BAO_SchemaHandler::checkIfFieldExists($table, $column)) {
  90           $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}";
  91           $queries[] = "ALTER TABLE {$table} DROP {$column}";
  92         }
  93       }
  94
  95       // add view
  96       $queries[] = self::createViewQuery($locale, $table, $dao);
  97
  98       // add new indices
  99       $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
 100     }
 101
 102     // execute the queries without i18n rewriting
 103     foreach ($queries as $query) {
 104       $dao->query($query, FALSE);
 105     }
 106
 107     // update civicrm_domain.locales
 108     $domain->locales = $locale;
 109     $domain->save();
 110
 111     // CRM-21627 Updates the $dbLocale
 112     CRM_Core_BAO_ConfigSetting::applyLocale(Civi::settings($domain->id), $domain->locales);
 113   }
 114
 115   /**
 116    * Switch database from multi-lang back to single (by dropping
 117    * additional columns and views and retaining only the selected locale).
 118    *
 119    * @param string $retain
 120    *   the locale to retain.
 121    */
 122   public static function makeSinglelingual($retain) {
 123     $domain = new CRM_Core_DAO_Domain();
 124     $domain->find(TRUE);
 125     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 126
 127     // break early if the db is already single-lang
 128     if (!$locales) {
 129       return;
 130     }
 131
 132     // lets drop all triggers first
 133     $logging = new CRM_Logging_Schema();
 134     $logging->dropTriggers();
 135
 136     // turn subsequent tables singlelingual
 137     $tables = CRM_Core_I18n_SchemaStructure::tables();
 138     foreach ($tables as $table) {
 139       self::makeSinglelingualTable($retain, $table);
 140     }
 141
 142     // update civicrm_domain.locales
 143     $domain->locales = 'NULL';
 144     $domain->save();
 145
 146     //CRM-6963 -fair assumption.
 147     global $dbLocale;
 148     $dbLocale = '';
 149
 150     // now lets rebuild all triggers
 151     CRM_Core_DAO::triggerRebuild();
 152   }
 153
 154   /**
 155    * Switch a given table from multi-lang to single (by retaining only the selected locale).
 156    *
 157    * @param string $retain
 158    *   the locale to retain.
 159    * @param string $table
 160    *   the table containing the column.
 161    * @param string $class
 162    *   schema structure class to use to recreate indices.
 163    *
 164    * @param array $triggers
 165    */
 166   public static function makeSinglelingualTable(
 167     $retain,
 168     $table,
 169     $class = 'CRM_Core_I18n_SchemaStructure',
 170     $triggers = array()
 171   ) {
 172     $domain = new CRM_Core_DAO_Domain();
 173     $domain->find(TRUE);
 174     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 175
 176     // break early if the db is already single-lang
 177     if (!$locales) {
 178       return;
 179     }
 180
 181     $columns =& $class::columns();
 182     $indices =& $class::indices();
 183     $queries = array();
 184     $dropQueries = array();
 185     // drop indices
 186     if (isset($indices[$table])) {
 187       foreach ($indices[$table] as $index) {
 188         foreach ($locales as $loc) {
 189           $queries[] = "DROP INDEX {$index['name']}_{$loc} ON {$table}";
 190         }
 191       }
 192     }
 193
 194     $dao = new CRM_Core_DAO();
 195     // deal with columns
 196     foreach ($columns[$table] as $column => $type) {
 197       $queries[] = "ALTER TABLE {$table} CHANGE `{$column}_{$retain}` `{$column}` {$type}";
 198       foreach ($locales as $loc) {
 199         if (strcmp($loc, $retain) !== 0) {
 200           $dropQueries[] = "ALTER TABLE {$table} DROP {$column}_{$loc}";
 201         }
 202       }
 203     }
 204
 205     // drop views
 206     foreach ($locales as $loc) {
 207       $queries[] = "DROP VIEW IF EXISTS {$table}_{$loc}";
 208     }
 209
 210     // add original indices
 211     $queries = array_merge($queries, self::createIndexQueries(NULL, $table));
 212
 213     // execute the queries without i18n rewriting
 214     $dao = new CRM_Core_DAO();
 215     foreach ($queries as $query) {
 216       $dao->query($query, FALSE);
 217     }
 218
 219     foreach ($dropQueries as $query) {
 220       $dao->query($query, FALSE);
 221     }
 222
 223     if (!empty($triggers)) {
 224       if (CRM_Core_Config::isUpgradeMode()) {
 225         foreach ($triggers as $triggerInfo) {
 226           $when = $triggerInfo['when'];
 227           $event = $triggerInfo['event'];
 228           $triggerName = "{$table}_{$when}_{$event}";
 229           CRM_Core_DAO::executeQuery("DROP TRIGGER IF EXISTS {$triggerName}");
 230         }
 231       }
 232
 233       // invoke the meta trigger creation call
 234       CRM_Core_DAO::triggerRebuild($table);
 235     }
 236   }
 237
 238   /**
 239    * Add a new locale to a multi-lang db, setting
 240    * its values to the current default locale.
 241    *
 242    * @param string $locale
 243    *   the new locale to add.
 244    * @param string $source
 245    *   the locale to copy from.
 246    */
 247   public static function addLocale($locale, $source) {
 248     // get the current supported locales
 249     $domain = new CRM_Core_DAO_Domain();
 250     $domain->find(TRUE);
 251     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 252
 253     // break early if the locale is already supported
 254     if (in_array($locale, $locales)) {
 255       return;
 256     }
 257
 258     $dao = new CRM_Core_DAO();
 259
 260     // build the required SQL queries
 261     $columns = CRM_Core_I18n_SchemaStructure::columns();
 262     $indices = CRM_Core_I18n_SchemaStructure::indices();
 263     $queries = array();
 264     foreach ($columns as $table => $hash) {
 265       // add new columns
 266       foreach ($hash as $column => $type) {
 267         // CRM-7854: skip existing columns
 268         if (CRM_Core_DAO::checkFieldExists($table, "{$column}_{$locale}", FALSE)) {
 269           continue;
 270         }
 271         $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
 272         $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
 273       }
 274
 275       // add view
 276       $queries[] = self::createViewQuery($locale, $table, $dao);
 277
 278       // add new indices
 279       $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
 280     }
 281
 282     // execute the queries without i18n rewriting
 283     foreach ($queries as $query) {
 284       $dao->query($query, FALSE);
 285     }
 286
 287     // update civicrm_domain.locales
 288     $locales[] = $locale;
 289     $domain->locales = implode(CRM_Core_DAO::VALUE_SEPARATOR, $locales);
 290     $domain->save();
 291
 292     // invoke the meta trigger creation call
 293     CRM_Core_DAO::triggerRebuild();
 294   }
 295
 296   /**
 297    * Rebuild multilingual indices, views and triggers (useful for upgrades)
 298    *
 299    * @param array $locales
 300    *   locales to be rebuilt.
 301    * @param string $version
 302    *   version of schema structure to use.
 303    * @param bool $isUpgradeMode
 304    *   Are we upgrading our database
 305    */
 306   public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) {
 307     if ($version) {
 308       $latest = self::getLatestSchema($version);
 309       require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
 310       $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
 311     }
 312     else {
 313       $class = 'CRM_Core_I18n_SchemaStructure';
 314     }
 315     $indices =& $class::indices();
 316     $tables =& $class::tables();
 317     $queries = array();
 318     $dao = new CRM_Core_DAO();
 319
 320     // get all of the already existing indices
 321     $existing = array();
 322     foreach (array_keys($indices) as $table) {
 323       $existing[$table] = array();
 324       $dao->query("SHOW INDEX FROM $table", FALSE);
 325       while ($dao->fetch()) {
 326         if (preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Key_name)) {
 327           $existing[$table][] = $dao->Key_name;
 328         }
 329       }
 330     }
 331
 332     // from all of the CREATE INDEX queries fetch the ones creating missing indices
 333     foreach ($locales as $locale) {
 334       foreach (array_keys($indices) as $table) {
 335         $allQueries = self::createIndexQueries($locale, $table, $class);
 336         foreach ($allQueries as $name => $query) {
 337           if (!in_array("{$name}_{$locale}", $existing[$table])) {
 338             $queries[] = $query;
 339           }
 340         }
 341       }
 342     }
 343
 344     // rebuild views
 345     foreach ($locales as $locale) {
 346       foreach ($tables as $table) {
 347         $queries[] = self::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode);
 348       }
 349     }
 350
 351     // rebuild triggers
 352     $last = array_pop($locales);
 353
 354     foreach ($queries as $query) {
 355       $dao->query($query, FALSE);
 356     }
 357
 358     // invoke the meta trigger creation call
 359     CRM_Core_DAO::triggerRebuild();
 360   }
 361
 362   /**
 363    * Rewrite SQL query to use views to access tables with localized columns.
 364    *
 365    * @param string $query
 366    *   the query for rewrite.
 367    *
 368    * @return string
 369    *   the rewritten query
 370    */
 371   public static function rewriteQuery($query) {
 372     global $dbLocale;
 373     $tables = self::schemaStructureTables();
 374     foreach ($tables as $table) {
 375       // CRM-19093
 376       // should match the civicrm table name such as: civicrm_event
 377       // but must not match the table name if it's a substring of another table: civicrm_events_in_cart
 378       $query = preg_replace("/([^'\"])({$table})(\z|[^a-z_'\"])/", "\\1\\2{$dbLocale}\\3", $query);
 379     }
 380     // uncomment the below to rewrite the civicrm_value_* queries
 381     // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
 382     return $query;
 383   }
 384
 385   /**
 386    * @param null $version
 387    * @param bool $force
 388    *
 389    * @return array
 390    */
 391   public static function schemaStructureTables($version = NULL, $force = FALSE) {
 392     static $_tables = NULL;
 393     if ($_tables === NULL || $force) {
 394       if ($version) {
 395         $latest = self::getLatestSchema($version);
 396         // FIXME: Doing require_once is a must here because a call like CRM_Core_I18n_SchemaStructure_4_1_0 makes
 397         // class loader look for file like - CRM/Core/I18n/SchemaStructure/4/1/0.php which is not what we want to be loaded
 398         require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
 399         $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
 400         $tables =& $class::tables();
 401       }
 402       else {
 403         $tables = CRM_Core_I18n_SchemaStructure::tables();
 404       }
 405       $_tables = $tables;
 406     }
 407     return $_tables;
 408   }
 409
 410   /**
 411    * @param $version
 412    *
 413    * @return mixed
 414    */
 415   public static function getLatestSchema($version) {
 416     // remove any .upgrade sub-str from version. Makes it easy to do version_compare & give right result
 417     $version = str_ireplace(".upgrade", "", $version);
 418
 419     // fetch all the SchemaStructure versions we ship and sort by version
 420     $schemas = array();
 421     foreach (scandir(dirname(__FILE__)) as $file) {
 422       $matches = array();
 423       if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
 424         $schemas[] = str_replace('_', '.', $matches[1]);
 425       }
 426     }
 427     usort($schemas, 'version_compare');
 428
 429     // find the latest schema structure older than (or equal to) $version
 430     do {
 431       $latest = array_pop($schemas);
 432     } while (version_compare($latest, $version, '>'));
 433
 434     return str_replace('.', '_', $latest);
 435   }
 436
 437   /**
 438    * CREATE INDEX queries for a given locale and table.
 439    *
 440    * @param string $locale
 441    *   locale for which the queries should be created (null to create original indices).
 442    * @param string $table
 443    *   table for which the queries should be created.
 444    * @param string $class
 445    *   schema structure class to use.
 446    *
 447    * @return array
 448    *   array of CREATE INDEX queries
 449    */
 450   private static function createIndexQueries($locale, $table, $class = 'CRM_Core_I18n_SchemaStructure') {
 451     $indices =& $class::indices();
 452     $columns =& $class::columns();
 453     if (!isset($indices[$table])) {
 454       return array();
 455     }
 456
 457     $queries = array();
 458     foreach ($indices[$table] as $index) {
 459       $unique = isset($index['unique']) && $index['unique'] ? 'UNIQUE' : '';
 460       foreach ($index['field'] as $i => $col) {
 461         // if a given column is localizable, extend its name with the locale
 462         if ($locale and isset($columns[$table][$col])) {
 463           $index['field'][$i] = "{$col}_{$locale}";
 464         }
 465       }
 466       $cols = implode(', ', $index['field']);
 467       $name = $index['name'];
 468       if ($locale) {
 469         $name .= '_' . $locale;
 470       }
 471       // CRM-7854: skip existing indices
 472       if (CRM_Core_DAO::checkConstraintExists($table, $name)) {
 473         continue;
 474       }
 475       $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
 476     }
 477     return $queries;
 478   }
 479
 480   /**
 481    * CREATE VIEW query for a given locale and table.
 482    *
 483    * @param string $locale
 484    *   locale of the view.
 485    * @param string $table
 486    *   table of the view.
 487    * @param CRM_Core_DAO $dao
 488    *   A DAO object to run DESCRIBE queries.
 489    * @param string $class
 490    *   schema structure class to use.
 491    * @param bool $isUpgradeMode
 492    *   Are we in upgrade mode therefore only build based off table not class
 493    * @return array
 494    *   array of CREATE INDEX queries
 495    */
 496   private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) {
 497     $columns =& $class::columns();
 498     $cols = array();
 499     $tableCols = array();
 500     $dao->query("DESCRIBE {$table}", FALSE);
 501     while ($dao->fetch()) {
 502       // view non-internationalized columns directly
 503       if (!in_array($dao->Field, array_keys($columns[$table])) and
 504         !preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Field)
 505       ) {
 506         $cols[] = $dao->Field;
 507       }
 508       $tableCols[] = $dao->Field;
 509     }
 510     // view intrernationalized columns through an alias
 511     foreach ($columns[$table] as $column => $_) {
 512       if (!$isUpgradeMode) {
 513         $cols[] = "{$column}_{$locale} {$column}";
 514       }
 515       elseif (in_array("{$column}_{$locale}", $tableCols)) {
 516         $cols[] = "{$column}_{$locale} {$column}";
 517       }
 518     }
 519     return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
 520   }
 521
 522   /**
 523    * @param $info
 524    * @param null $tableName
 525    */
 526   public static function triggerInfo(&$info, $tableName = NULL) {
 527     // get the current supported locales
 528     $domain = new CRM_Core_DAO_Domain();
 529     $domain->find(TRUE);
 530     if (empty($domain->locales)) {
 531       return;
 532     }
 533
 534     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 535     $locale = array_pop($locales);
 536
 537     // CRM-10027
 538     if (count($locales) == 0) {
 539       return;
 540     }
 541
 542     $currentVer = CRM_Core_BAO_Domain::version(TRUE);
 543
 544     if ($currentVer && CRM_Core_Config::isUpgradeMode()) {
 545       // take exact version so that proper schema structure file in invoked
 546       $latest = self::getLatestSchema($currentVer);
 547       require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
 548       $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
 549     }
 550     else {
 551       $class = 'CRM_Core_I18n_SchemaStructure';
 552     }
 553
 554     $columns =& $class::columns();
 555
 556     foreach ($columns as $table => $hash) {
 557       if ($tableName &&
 558         $tableName != $table
 559       ) {
 560         continue;
 561       }
 562
 563       $trigger = array();
 564
 565       foreach ($hash as $column => $_) {
 566         $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
 567         foreach ($locales as $old) {
 568           $trigger[] = "IF NEW.{$column}_{$old} IS NULL THEN SET NEW.{$column}_{$old} = NEW.{$column}_{$locale}; END IF;";
 569         }
 570         foreach ($locales as $old) {
 571           $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
 572           foreach (array_merge($locales, array(
 573             $locale,
 574           )) as $loc) {
 575             if ($loc == $old) {
 576               continue;
 577             }
 578             $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
 579           }
 580         }
 581         $trigger[] = 'END IF;';
 582       }
 583
 584       $sql = implode(' ', $trigger);
 585       $info[] = array(
 586         'table' => array($table),
 587         'when' => 'BEFORE',
 588         'event' => array('UPDATE'),
 589         'sql' => $sql,
 590       );
 591     }
 592
 593     // take care of the ON INSERT triggers
 594     foreach ($columns as $table => $hash) {
 595       $trigger = array();
 596       foreach ($hash as $column => $_) {
 597         $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
 598         foreach ($locales as $old) {
 599           $trigger[] = "SET NEW.{$column}_{$old} = NEW.{$column}_{$locale};";
 600         }
 601         foreach ($locales as $old) {
 602           $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
 603           foreach (array_merge($locales, array(
 604             $locale,
 605           )) as $loc) {
 606             if ($loc == $old) {
 607               continue;
 608             }
 609             $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
 610           }
 611         }
 612         $trigger[] = 'END IF;';
 613       }
 614
 615       $sql = implode(' ', $trigger);
 616       $info[] = array(
 617         'table' => array($table),
 618         'when' => 'BEFORE',
 619         'event' => array('INSERT'),
 620         'sql' => $sql,
 621       );
 622     }
 623   }
 624
 625 }