CRM/Core/I18n/Schema.php

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