CRM/Core/I18n/Schema.php

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