3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2015
33 class CRM_Core_I18n_Schema
{
36 * Drop all views (for use by CRM_Core_DAO::dropAllTables() mostly).
38 public static function dropAllViews() {
39 $domain = new CRM_Core_DAO_Domain();
41 if (!$domain->locales
) {
45 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
46 $tables = CRM_Core_I18n_SchemaStructure
::tables();
48 foreach ($locales as $locale) {
49 foreach ($tables as $table) {
50 CRM_Core_DAO
::executeQuery("DROP VIEW IF EXISTS {$table}_{$locale}");
56 * Switch database from single-lang to multi (by adding
57 * the first language and dropping the original columns).
59 * @param string $locale
60 * the first locale to create (migrate to).
62 public static function makeMultilingual($locale) {
63 $domain = new CRM_Core_DAO_Domain();
66 // break early if the db is already multi-lang
67 if ($domain->locales
) {
71 $dao = new CRM_Core_DAO();
73 // build the column-adding SQL queries
74 $columns = CRM_Core_I18n_SchemaStructure
::columns();
75 $indices = CRM_Core_I18n_SchemaStructure
::indices();
77 foreach ($columns as $table => $hash) {
79 if (isset($indices[$table])) {
80 foreach ($indices[$table] as $index) {
81 $queries[] = "DROP INDEX {$index['name']} ON {$table}";
85 foreach ($hash as $column => $type) {
86 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
87 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}";
88 $queries[] = "ALTER TABLE {$table} DROP {$column}";
92 $queries[] = self
::createViewQuery($locale, $table, $dao);
95 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
98 // execute the queries without i18n rewriting
99 foreach ($queries as $query) {
100 $dao->query($query, FALSE);
103 // update civicrm_domain.locales
104 $domain->locales
= $locale;
109 * Switch database from multi-lang back to single (by dropping
110 * additional columns and views and retaining only the selected locale).
112 * @param string $retain
113 * the locale to retain.
115 public static function makeSinglelingual($retain) {
116 $domain = new CRM_Core_DAO_Domain();
118 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
120 // break early if the db is already single-lang
125 // lets drop all triggers first
126 $logging = new CRM_Logging_Schema();
127 $logging->dropTriggers();
129 // turn subsequent tables singlelingual
130 $tables = CRM_Core_I18n_SchemaStructure
::tables();
131 foreach ($tables as $table) {
132 self
::makeSinglelingualTable($retain, $table);
135 // update civicrm_domain.locales
136 $domain->locales
= 'NULL';
139 //CRM-6963 -fair assumption.
143 // now lets rebuild all triggers
144 CRM_Core_DAO
::triggerRebuild();
148 * Switch a given table from multi-lang to single (by retaining only the selected locale).
150 * @param string $retain
151 * the locale to retain.
152 * @param string $table
153 * the table containing the column.
154 * @param string $class
155 * schema structure class to use to recreate indices.
157 * @param array $triggers
159 public static function makeSinglelingualTable(
162 $class = 'CRM_Core_I18n_SchemaStructure',
165 $domain = new CRM_Core_DAO_Domain();
167 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
169 // break early if the db is already single-lang
174 $columns =& $class::columns();
175 $indices =& $class::indices();
177 $dropQueries = array();
179 if (isset($indices[$table])) {
180 foreach ($indices[$table] as $index) {
181 foreach ($locales as $loc) {
182 $queries[] = "DROP INDEX {$index['name']}_{$loc} ON {$table}";
188 foreach ($columns[$table] as $column => $type) {
189 $queries[] = "ALTER TABLE {$table} ADD {$column} {$type}";
190 $queries[] = "UPDATE {$table} SET {$column} = {$column}_{$retain}";
191 foreach ($locales as $loc) {
192 $dropQueries[] = "ALTER TABLE {$table} DROP {$column}_{$loc}";
197 foreach ($locales as $loc) {
198 $queries[] = "DROP VIEW {$table}_{$loc}";
201 // add original indices
202 $queries = array_merge($queries, self
::createIndexQueries(NULL, $table));
204 // execute the queries without i18n rewriting
205 $dao = new CRM_Core_DAO();
206 foreach ($queries as $query) {
207 $dao->query($query, FALSE);
210 foreach ($dropQueries as $query) {
211 $dao->query($query, FALSE);
214 if (!empty($triggers)) {
215 if (CRM_Core_Config
::isUpgradeMode()) {
216 foreach ($triggers as $triggerInfo) {
217 $when = $triggerInfo['when'];
218 $event = $triggerInfo['event'];
219 $triggerName = "{$table}_{$when}_{$event}";
220 CRM_Core_DAO
::executeQuery("DROP TRIGGER IF EXISTS {$triggerName}");
224 // invoke the meta trigger creation call
225 CRM_Core_DAO
::triggerRebuild($table);
230 * Add a new locale to a multi-lang db, setting
231 * its values to the current default locale.
233 * @param string $locale
234 * the new locale to add.
235 * @param string $source
236 * the locale to copy from.
238 public static function addLocale($locale, $source) {
239 // get the current supported locales
240 $domain = new CRM_Core_DAO_Domain();
242 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
244 // break early if the locale is already supported
245 if (in_array($locale, $locales)) {
249 $dao = new CRM_Core_DAO();
251 // build the required SQL queries
252 $columns = CRM_Core_I18n_SchemaStructure
::columns();
253 $indices = CRM_Core_I18n_SchemaStructure
::indices();
255 foreach ($columns as $table => $hash) {
257 foreach ($hash as $column => $type) {
258 // CRM-7854: skip existing columns
259 if (CRM_Core_DAO
::checkFieldExists($table, "{$column}_{$locale}", FALSE)) {
262 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
263 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
267 $queries[] = self
::createViewQuery($locale, $table, $dao);
270 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
273 // execute the queries without i18n rewriting
274 foreach ($queries as $query) {
275 $dao->query($query, FALSE);
278 // update civicrm_domain.locales
279 $locales[] = $locale;
280 $domain->locales
= implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $locales);
283 // invoke the meta trigger creation call
284 CRM_Core_DAO
::triggerRebuild();
288 * Rebuild multilingual indices, views and triggers (useful for upgrades)
290 * @param array $locales
291 * locales to be rebuilt.
292 * @param string $version
293 * version of schema structure to use.
295 public static function rebuildMultilingualSchema($locales, $version = NULL) {
297 $latest = self
::getLatestSchema($version);
298 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
299 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
302 $class = 'CRM_Core_I18n_SchemaStructure';
304 $indices =& $class::indices();
305 $tables =& $class::tables();
307 $dao = new CRM_Core_DAO();
309 // get all of the already existing indices
311 foreach (array_keys($indices) as $table) {
312 $existing[$table] = array();
313 $dao->query("SHOW INDEX FROM $table", FALSE);
314 while ($dao->fetch()) {
315 if (preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Key_name
)) {
316 $existing[$table][] = $dao->Key_name
;
321 // from all of the CREATE INDEX queries fetch the ones creating missing indices
322 foreach ($locales as $locale) {
323 foreach (array_keys($indices) as $table) {
324 $allQueries = self
::createIndexQueries($locale, $table, $class);
325 foreach ($allQueries as $name => $query) {
326 if (!in_array("{$name}_{$locale}", $existing[$table])) {
334 foreach ($locales as $locale) {
335 foreach ($tables as $table) {
336 $queries[] = self
::createViewQuery($locale, $table, $dao, $class);
341 $last = array_pop($locales);
343 foreach ($queries as $query) {
344 $dao->query($query, FALSE);
347 // invoke the meta trigger creation call
348 CRM_Core_DAO
::triggerRebuild();
352 * Rewrite SQL query to use views to access tables with localized columns.
354 * @param string $query
355 * the query for rewrite.
358 * the rewritten query
360 public static function rewriteQuery($query) {
362 $tables = self
::schemaStructureTables();
363 foreach ($tables as $table) {
364 $query = preg_replace("/([^'\"])({$table})([^_'\"])/", "\\1\\2{$dbLocale}\\3", $query);
366 // uncomment the below to rewrite the civicrm_value_* queries
367 // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
372 * @param null $version
377 public static function schemaStructureTables($version = NULL, $force = FALSE) {
378 static $_tables = NULL;
379 if ($_tables === NULL ||
$force) {
381 $latest = self
::getLatestSchema($version);
382 // FIXME: Doing require_once is a must here because a call like CRM_Core_I18n_SchemaStructure_4_1_0 makes
383 // class loader look for file like - CRM/Core/I18n/SchemaStructure/4/1/0.php which is not what we want to be loaded
384 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
385 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
386 $tables =& $class::tables();
389 $tables = CRM_Core_I18n_SchemaStructure
::tables();
401 public static function getLatestSchema($version) {
402 // remove any .upgrade sub-str from version. Makes it easy to do version_compare & give right result
403 $version = str_ireplace(".upgrade", "", $version);
405 // fetch all the SchemaStructure versions we ship and sort by version
407 foreach (scandir(dirname(__FILE__
)) as $file) {
409 if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
410 $schemas[] = str_replace('_', '.', $matches[1]);
413 usort($schemas, 'version_compare');
415 // find the latest schema structure older than (or equal to) $version
417 $latest = array_pop($schemas);
418 } while (version_compare($latest, $version, '>'));
420 return str_replace('.', '_', $latest);
424 * CREATE INDEX queries for a given locale and table.
426 * @param string $locale
427 * locale for which the queries should be created (null to create original indices).
428 * @param string $table
429 * table for which the queries should be created.
430 * @param string $class
431 * schema structure class to use.
434 * array of CREATE INDEX queries
436 private static function createIndexQueries($locale, $table, $class = 'CRM_Core_I18n_SchemaStructure') {
437 $indices =& $class::indices();
438 $columns =& $class::columns();
439 if (!isset($indices[$table])) {
444 foreach ($indices[$table] as $index) {
445 $unique = isset($index['unique']) && $index['unique'] ?
'UNIQUE' : '';
446 foreach ($index['field'] as $i => $col) {
447 // if a given column is localizable, extend its name with the locale
448 if ($locale and isset($columns[$table][$col])) {
449 $index['field'][$i] = "{$col}_{$locale}";
452 $cols = implode(', ', $index['field']);
453 $name = $index['name'];
455 $name .= '_' . $locale;
457 // CRM-7854: skip existing indices
458 if (CRM_Core_DAO
::checkConstraintExists($table, $name)) {
461 $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
467 * CREATE VIEW query for a given locale and table.
469 * @param string $locale
470 * locale of the view.
471 * @param string $table
473 * @param CRM_Core_DAO $dao
474 * A DAO object to run DESCRIBE queries.
475 * @param string $class
476 * schema structure class to use.
479 * array of CREATE INDEX queries
481 private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure') {
482 $columns =& $class::columns();
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
)
490 $cols[] = $dao->Field
;
493 // view intrernationalized columns through an alias
494 foreach ($columns[$table] as $column => $_) {
495 $cols[] = "{$column}_{$locale} {$column}";
497 return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
502 * @param null $tableName
504 public static function triggerInfo(&$info, $tableName = NULL) {
505 // get the current supported locales
506 $domain = new CRM_Core_DAO_Domain();
508 if (empty($domain->locales
)) {
512 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
513 $locale = array_pop($locales);
516 if (count($locales) == 0) {
520 $currentVer = CRM_Core_BAO_Domain
::version(TRUE);
522 if ($currentVer && CRM_Core_Config
::isUpgradeMode()) {
523 // take exact version so that proper schema structure file in invoked
524 $latest = self
::getLatestSchema($currentVer);
525 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
526 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
529 $class = 'CRM_Core_I18n_SchemaStructure';
532 $columns =& $class::columns();
534 foreach ($columns as $table => $hash) {
543 foreach ($hash as $column => $_) {
544 $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
545 foreach ($locales as $old) {
546 $trigger[] = "IF NEW.{$column}_{$old} IS NULL THEN SET NEW.{$column}_{$old} = NEW.{$column}_{$locale}; END IF;";
548 foreach ($locales as $old) {
549 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
550 foreach (array_merge($locales, array(
556 $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
559 $trigger[] = 'END IF;';
562 $sql = implode(' ', $trigger);
564 'table' => array($table),
566 'event' => array('UPDATE'),
571 // take care of the ON INSERT triggers
572 foreach ($columns as $table => $hash) {
574 foreach ($hash as $column => $_) {
575 $trigger[] = "IF NEW.{$column}_{$locale} IS NOT NULL THEN";
576 foreach ($locales as $old) {
577 $trigger[] = "SET NEW.{$column}_{$old} = NEW.{$column}_{$locale};";
579 foreach ($locales as $old) {
580 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
581 foreach (array_merge($locales, array(
587 $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
590 $trigger[] = 'END IF;';
593 $sql = implode(' ', $trigger);
595 'table' => array($table),
597 'event' => array('INSERT'),