CRM/Core/I18n/Schema.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  *
  34  */
  35 class CRM_Core_I18n_Schema {
  36
  37   /**
  38    * Drop all views (for use by CRM_Core_DAO::dropAllTables() mostly).
  39    *
  40    * @return void
  41    */
  42   public static function dropAllViews() {
  43     $domain = new CRM_Core_DAO_Domain();
  44     $domain->find(TRUE);
  45     if (!$domain->locales) {
  46       return;
  47     }
  48
  49     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
  50     $tables = CRM_Core_I18n_SchemaStructure::tables();
  51
  52     foreach ($locales as $locale) {
  53       foreach ($tables as $table) {
  54         CRM_Core_DAO::executeQuery("DROP VIEW IF EXISTS {$table}_{$locale}");
  55       }
  56     }
  57   }
  58
  59   /**
  60    * Switch database from single-lang to multi (by adding
  61    * the first language and dropping the original columns).
  62    *
  63    * @param string $locale
  64    *   the first locale to create (migrate to).
  65    *
  66    * @return void
  67    */
  68   public static function makeMultilingual($locale) {
  69     $domain = new CRM_Core_DAO_Domain();
  70     $domain->find(TRUE);
  71
  72     // break early if the db is already multi-lang
  73     if ($domain->locales) {
  74       return;
  75     }
  76
  77     $dao = new CRM_Core_DAO();
  78
  79     // build the column-adding SQL queries
  80     $columns = CRM_Core_I18n_SchemaStructure::columns();
  81     $indices = CRM_Core_I18n_SchemaStructure::indices();
  82     $queries = array();
  83     foreach ($columns as $table => $hash) {
  84       // drop old indices
  85       if (isset($indices[$table])) {
  86         foreach ($indices[$table] as $index) {
  87           $queries[] = "DROP INDEX {$index['name']} ON {$table}";
  88         }
  89       }
  90       // deal with columns
  91       foreach ($hash as $column => $type) {
  92         $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
  93         $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}";
  94         $queries[] = "ALTER TABLE {$table} DROP {$column}";
  95       }
  96
  97       // add view
  98       $queries[] = self::createViewQuery($locale, $table, $dao);
  99
 100       // add new indices
 101       $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
 102     }
 103
 104     // execute the queries without i18n rewriting
 105     foreach ($queries as $query) {
 106       $dao->query($query, FALSE);
 107     }
 108
 109     // update civicrm_domain.locales
 110     $domain->locales = $locale;
 111     $domain->save();
 112   }
 113
 114   /**
 115    * Switch database from multi-lang back to single (by dropping
 116    * additional columns and views and retaining only the selected locale).
 117    *
 118    * @param string $retain
 119    *   the locale to retain.
 120    *
 121    * @return void
 122    */
 123   public static function makeSinglelingual($retain) {
 124     $domain = new CRM_Core_DAO_Domain();
 125     $domain->find(TRUE);
 126     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 127
 128     // break early if the db is already single-lang
 129     if (!$locales) {
 130       return;
 131     }
 132
 133     // lets drop all triggers first
 134     $logging = new CRM_Logging_Schema();
 135     $logging->dropTriggers();
 136
 137     // turn subsequent tables singlelingual
 138     $tables = CRM_Core_I18n_SchemaStructure::tables();
 139     foreach ($tables as $table) {
 140       self::makeSinglelingualTable($retain, $table);
 141     }
 142
 143     // update civicrm_domain.locales
 144     $domain->locales = 'NULL';
 145     $domain->save();
 146
 147     //CRM-6963 -fair assumption.
 148     global $dbLocale;
 149     $dbLocale = '';
 150
 151     // now lets rebuild all triggers
 152     CRM_Core_DAO::triggerRebuild();
 153   }
 154
 155   /**
 156    * Switch a given table from multi-lang to single (by retaining only the selected locale).
 157    *
 158    * @param string $retain
 159    *   the locale to retain.
 160    * @param string $table
 161    *   the table containing the column.
 162    * @param string $class
 163    *   schema structure class to use to recreate indices.
 164    *
 165    * @param array $triggers
 166    *
 167    * @return void
 168    */
 169   public static function makeSinglelingualTable(
 170     $retain,
 171     $table,
 172     $class = 'CRM_Core_I18n_SchemaStructure',
 173     $triggers = array()
 174   ) {
 175     $domain = new CRM_Core_DAO_Domain();
 176     $domain->find(TRUE);
 177     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 178
 179     // break early if the db is already single-lang
 180     if (!$locales) {
 181       return;
 182     }
 183
 184     $columns =& $class::columns();
 185     $indices =& $class::indices();
 186     $queries = array();
 187     $dropQueries = array();
 188     // drop indices
 189     if (isset($indices[$table])) {
 190       foreach ($indices[$table] as $index) {
 191         foreach ($locales as $loc) {
 192           $queries[] = "DROP INDEX {$index['name']}_{$loc} ON {$table}";
 193         }
 194       }
 195     }
 196
 197     // deal with columns
 198     foreach ($columns[$table] as $column => $type) {
 199       $queries[] = "ALTER TABLE {$table} ADD {$column} {$type}";
 200       $queries[] = "UPDATE {$table} SET {$column} = {$column}_{$retain}";
 201       foreach ($locales as $loc) {
 202         $dropQueries[] = "ALTER TABLE {$table} DROP {$column}_{$loc}";
 203       }
 204     }
 205
 206     // drop views
 207     foreach ($locales as $loc) {
 208       $queries[] = "DROP VIEW {$table}_{$loc}";
 209     }
 210
 211     // add original indices
 212     $queries = array_merge($queries, self::createIndexQueries(NULL, $table));
 213
 214     // execute the queries without i18n rewriting
 215     $dao = new CRM_Core_DAO();
 216     foreach ($queries as $query) {
 217       $dao->query($query, FALSE);
 218     }
 219
 220     foreach ($dropQueries as $query) {
 221       $dao->query($query, FALSE);
 222     }
 223
 224     if (!empty($triggers)) {
 225       if (CRM_Core_Config::isUpgradeMode()) {
 226         foreach ($triggers as $triggerInfo) {
 227           $when = $triggerInfo['when'];
 228           $event = $triggerInfo['event'];
 229           $triggerName = "{$table}_{$when}_{$event}";
 230           CRM_Core_DAO::executeQuery("DROP TRIGGER IF EXISTS {$triggerName}");
 231         }
 232       }
 233
 234       // invoke the meta trigger creation call
 235       CRM_Core_DAO::triggerRebuild($table);
 236     }
 237   }
 238
 239   /**
 240    * Add a new locale to a multi-lang db, setting
 241    * its values to the current default locale.
 242    *
 243    * @param string $locale
 244    *   the new locale to add.
 245    * @param string $source
 246    *   the locale to copy from.
 247    *
 248    * @return void
 249    */
 250   public static function addLocale($locale, $source) {
 251     // get the current supported locales
 252     $domain = new CRM_Core_DAO_Domain();
 253     $domain->find(TRUE);
 254     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 255
 256     // break early if the locale is already supported
 257     if (in_array($locale, $locales)) {
 258       return;
 259     }
 260
 261     $dao = new CRM_Core_DAO();
 262
 263     // build the required SQL queries
 264     $columns = CRM_Core_I18n_SchemaStructure::columns();
 265     $indices = CRM_Core_I18n_SchemaStructure::indices();
 266     $queries = array();
 267     foreach ($columns as $table => $hash) {
 268       // add new columns
 269       foreach ($hash as $column => $type) {
 270         // CRM-7854: skip existing columns
 271         if (CRM_Core_DAO::checkFieldExists($table, "{$column}_{$locale}", FALSE)) {
 272           continue;
 273         }
 274         $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
 275         $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
 276       }
 277
 278       // add view
 279       $queries[] = self::createViewQuery($locale, $table, $dao);
 280
 281       // add new indices
 282       $queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
 283     }
 284
 285     // execute the queries without i18n rewriting
 286     foreach ($queries as $query) {
 287       $dao->query($query, FALSE);
 288     }
 289
 290     // update civicrm_domain.locales
 291     $locales[] = $locale;
 292     $domain->locales = implode(CRM_Core_DAO::VALUE_SEPARATOR, $locales);
 293     $domain->save();
 294
 295     // invoke the meta trigger creation call
 296     CRM_Core_DAO::triggerRebuild();
 297   }
 298
 299   /**
 300    * Rebuild multilingual indices, views and triggers (useful for upgrades)
 301    *
 302    * @param array $locales
 303    *   locales to be rebuilt.
 304    * @param string $version
 305    *   version of schema structure to use.
 306    *
 307    * @return void
 308    */
 309   public static function rebuildMultilingualSchema($locales, $version = NULL) {
 310     if ($version) {
 311       $latest = self::getLatestSchema($version);
 312       require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
 313       $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
 314     }
 315     else {
 316       $class = 'CRM_Core_I18n_SchemaStructure';
 317     }
 318     $indices =& $class::indices();
 319     $tables =& $class::tables();
 320     $queries = array();
 321     $dao = new CRM_Core_DAO();
 322
 323     // get all of the already existing indices
 324     $existing = array();
 325     foreach (array_keys($indices) as $table) {
 326       $existing[$table] = array();
 327       $dao->query("SHOW INDEX FROM $table", FALSE);
 328       while ($dao->fetch()) {
 329         if (preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Key_name)) {
 330           $existing[$table][] = $dao->Key_name;
 331         }
 332       }
 333     }
 334
 335     // from all of the CREATE INDEX queries fetch the ones creating missing indices
 336     foreach ($locales as $locale) {
 337       foreach (array_keys($indices) as $table) {
 338         $allQueries = self::createIndexQueries($locale, $table, $class);
 339         foreach ($allQueries as $name => $query) {
 340           if (!in_array("{$name}_{$locale}", $existing[$table])) {
 341             $queries[] = $query;
 342           }
 343         }
 344       }
 345     }
 346
 347     // rebuild views
 348     foreach ($locales as $locale) {
 349       foreach ($tables as $table) {
 350         $queries[] = self::createViewQuery($locale, $table, $dao, $class);
 351       }
 352     }
 353
 354     // rebuild triggers
 355     $last = array_pop($locales);
 356
 357     foreach ($queries as $query) {
 358       $dao->query($query, FALSE);
 359     }
 360
 361     // invoke the meta trigger creation call
 362     CRM_Core_DAO::triggerRebuild();
 363   }
 364
 365   /**
 366    * Rewrite SQL query to use views to access tables with localized columns.
 367    *
 368    * @param string $query
 369    *   the query for rewrite.
 370    *
 371    * @return string
 372    *   the rewritten query
 373    */
 374   public static function rewriteQuery($query) {
 375     global $dbLocale;
 376     $tables = self::schemaStructureTables();
 377     foreach ($tables as $table) {
 378       $query = preg_replace("/([^'\"])({$table})([^_'\"])/", "\\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    *
 492    * @return array
 493    *   array of CREATE INDEX queries
 494    */
 495   private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure') {
 496     $columns =& $class::columns();
 497     $cols = array();
 498     $dao->query("DESCRIBE {$table}", FALSE);
 499     while ($dao->fetch()) {
 500       // view non-internationalized columns directly
 501       if (!in_array($dao->Field, array_keys($columns[$table])) and
 502         !preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Field)
 503       ) {
 504         $cols[] = $dao->Field;
 505       }
 506     }
 507     // view intrernationalized columns through an alias
 508     foreach ($columns[$table] as $column => $_) {
 509       $cols[] = "{$column}_{$locale} {$column}";
 510     }
 511     return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
 512   }
 513
 514   /**
 515    * @param $info
 516    * @param null $tableName
 517    */
 518   public static function triggerInfo(&$info, $tableName = NULL) {
 519     // get the current supported locales
 520     $domain = new CRM_Core_DAO_Domain();
 521     $domain->find(TRUE);
 522     if (empty($domain->locales)) {
 523       return;
 524     }
 525
 526     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 527     $locale = array_pop($locales);
 528
 529     // CRM-10027
 530     if (count($locales) == 0) {
 531       return;
 532     }
 533
 534     $currentVer = CRM_Core_BAO_Domain::version(TRUE);
 535
 536     if ($currentVer && CRM_Core_Config::isUpgradeMode()) {
 537       // take exact version so that proper schema structure file in invoked
 538       $latest = self::getLatestSchema($currentVer);
 539       require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
 540       $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
 541     }
 542     else {
 543       $class = 'CRM_Core_I18n_SchemaStructure';
 544     }
 545
 546     $columns =& $class::columns();
 547
 548     foreach ($columns as $table => $hash) {
 549       if ($tableName &&
 550         $tableName != $table
 551       ) {
 552         continue;
 553       }
 554
 555       $trigger = array();
 556
 557       foreach ($hash as $column => $_) {
 558         $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
 559         foreach ($locales as $old) {
 560           $trigger[] = "IF NEW.{$column}_{$old} IS NULL THEN SET NEW.{$column}_{$old} = NEW.{$column}_{$locale}; END IF;";
 561         }
 562         foreach ($locales as $old) {
 563           $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
 564           foreach (array_merge($locales, array(
 565             $locale,
 566           )) as $loc) {
 567             if ($loc == $old) {
 568               continue;
 569             }
 570             $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
 571           }
 572         }
 573         $trigger[] = 'END IF;';
 574       }
 575
 576       $sql = implode(' ', $trigger);
 577       $info[] = array(
 578         'table' => array($table),
 579         'when' => 'BEFORE',
 580         'event' => array('UPDATE'),
 581         'sql' => $sql,
 582       );
 583     }
 584
 585     // take care of the ON INSERT triggers
 586     foreach ($columns as $table => $hash) {
 587       $trigger = array();
 588       foreach ($hash as $column => $_) {
 589         $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
 590         foreach ($locales as $old) {
 591           $trigger[] = "SET NEW.{$column}_{$old} = NEW.{$column}_{$locale};";
 592         }
 593         foreach ($locales as $old) {
 594           $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
 595           foreach (array_merge($locales, array(
 596             $locale,
 597           )) as $loc) {
 598             if ($loc == $old) {
 599               continue;
 600             }
 601             $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
 602           }
 603         }
 604         $trigger[] = 'END IF;';
 605       }
 606
 607       $sql = implode(' ', $trigger);
 608       $info[] = array(
 609         'table' => array($table),
 610         'when' => 'BEFORE',
 611         'event' => array('INSERT'),
 612         'sql' => $sql,
 613       );
 614     }
 615   }
 616
 617 }