3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
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-2018
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 if (CRM_Core_BAO_SchemaHandler
::checkIfIndexExists($table, $index['name'])) {
82 $queries[] = "DROP INDEX {$index['name']} ON {$table}";
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}";
96 $queries[] = self
::createViewQuery($locale, $table, $dao);
99 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
102 // execute the queries without i18n rewriting
103 foreach ($queries as $query) {
104 $dao->query($query, FALSE);
107 // update civicrm_domain.locales
108 $domain->locales
= $locale;
111 // CRM-21627 Updates the $dbLocale
112 CRM_Core_BAO_ConfigSetting
::applyLocale(Civi
::settings($domain->id
), $domain->locales
);
116 * Switch database from multi-lang back to single (by dropping
117 * additional columns and views and retaining only the selected locale).
119 * @param string $retain
120 * the locale to retain.
122 public static function makeSinglelingual($retain) {
123 $domain = new CRM_Core_DAO_Domain();
125 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
127 // break early if the db is already single-lang
132 // lets drop all triggers first
133 $logging = new CRM_Logging_Schema();
134 $logging->dropTriggers();
136 // turn subsequent tables singlelingual
137 $tables = CRM_Core_I18n_SchemaStructure
::tables();
138 foreach ($tables as $table) {
139 self
::makeSinglelingualTable($retain, $table);
142 // update civicrm_domain.locales
143 $domain->locales
= 'NULL';
146 //CRM-6963 -fair assumption.
150 // now lets rebuild all triggers
151 CRM_Core_DAO
::triggerRebuild();
155 * Switch a given table from multi-lang to single (by retaining only the selected locale).
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.
164 * @param array $triggers
166 public static function makeSinglelingualTable(
169 $class = 'CRM_Core_I18n_SchemaStructure',
172 $domain = new CRM_Core_DAO_Domain();
174 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
176 // break early if the db is already single-lang
181 $columns =& $class::columns();
182 $indices =& $class::indices();
184 $dropQueries = array();
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}";
194 $dao = new CRM_Core_DAO();
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}";
206 foreach ($locales as $loc) {
207 $queries[] = "DROP VIEW IF EXISTS {$table}_{$loc}";
210 // add original indices
211 $queries = array_merge($queries, self
::createIndexQueries(NULL, $table));
213 // execute the queries without i18n rewriting
214 $dao = new CRM_Core_DAO();
215 foreach ($queries as $query) {
216 $dao->query($query, FALSE);
219 foreach ($dropQueries as $query) {
220 $dao->query($query, FALSE);
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}");
233 // invoke the meta trigger creation call
234 CRM_Core_DAO
::triggerRebuild($table);
239 * Add a new locale to a multi-lang db, setting
240 * its values to the current default locale.
242 * @param string $locale
243 * the new locale to add.
244 * @param string $source
245 * the locale to copy from.
247 public static function addLocale($locale, $source) {
248 // get the current supported locales
249 $domain = new CRM_Core_DAO_Domain();
251 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
253 // break early if the locale is already supported
254 if (in_array($locale, $locales)) {
258 $dao = new CRM_Core_DAO();
260 // build the required SQL queries
261 $columns = CRM_Core_I18n_SchemaStructure
::columns();
262 $indices = CRM_Core_I18n_SchemaStructure
::indices();
264 foreach ($columns as $table => $hash) {
266 foreach ($hash as $column => $type) {
267 // CRM-7854: skip existing columns
268 if (CRM_Core_DAO
::checkFieldExists($table, "{$column}_{$locale}", FALSE)) {
271 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
272 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
276 $queries[] = self
::createViewQuery($locale, $table, $dao);
279 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
282 // execute the queries without i18n rewriting
283 foreach ($queries as $query) {
284 $dao->query($query, FALSE);
287 // update civicrm_domain.locales
288 $locales[] = $locale;
289 $domain->locales
= implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $locales);
292 // invoke the meta trigger creation call
293 CRM_Core_DAO
::triggerRebuild();
297 * Rebuild multilingual indices, views and triggers (useful for upgrades)
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
306 public static function rebuildMultilingualSchema($locales, $version = NULL, $isUpgradeMode = FALSE) {
308 $latest = self
::getLatestSchema($version);
309 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
310 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
313 $class = 'CRM_Core_I18n_SchemaStructure';
315 $indices =& $class::indices();
316 $tables =& $class::tables();
318 $dao = new CRM_Core_DAO();
320 // get all of the already existing indices
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
;
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])) {
345 foreach ($locales as $locale) {
346 foreach ($tables as $table) {
347 $queries[] = self
::createViewQuery($locale, $table, $dao, $class, $isUpgradeMode);
352 $last = array_pop($locales);
354 foreach ($queries as $query) {
355 $dao->query($query, FALSE);
358 // invoke the meta trigger creation call
359 CRM_Core_DAO
::triggerRebuild();
363 * Rewrite SQL query to use views to access tables with localized columns.
365 * @param string $query
366 * the query for rewrite.
369 * the rewritten query
371 public static function rewriteQuery($query) {
373 $tables = self
::schemaStructureTables();
374 foreach ($tables as $table) {
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);
380 // uncomment the below to rewrite the civicrm_value_* queries
381 // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
386 * @param null $version
391 public static function schemaStructureTables($version = NULL, $force = FALSE) {
392 static $_tables = NULL;
393 if ($_tables === NULL ||
$force) {
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();
403 $tables = CRM_Core_I18n_SchemaStructure
::tables();
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);
419 // fetch all the SchemaStructure versions we ship and sort by version
421 foreach (scandir(dirname(__FILE__
)) as $file) {
423 if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
424 $schemas[] = str_replace('_', '.', $matches[1]);
427 usort($schemas, 'version_compare');
429 // find the latest schema structure older than (or equal to) $version
431 $latest = array_pop($schemas);
432 } while (version_compare($latest, $version, '>'));
434 return str_replace('.', '_', $latest);
438 * CREATE INDEX queries for a given locale and table.
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.
448 * array of CREATE INDEX queries
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])) {
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}";
466 $cols = implode(', ', $index['field']);
467 $name = $index['name'];
469 $name .= '_' . $locale;
471 // CRM-7854: skip existing indices
472 if (CRM_Core_DAO
::checkConstraintExists($table, $name)) {
475 $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
481 * CREATE VIEW query for a given locale and table.
483 * @param string $locale
484 * locale of the view.
485 * @param string $table
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
494 * array of CREATE INDEX queries
496 private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure', $isUpgradeMode = FALSE) {
497 $columns =& $class::columns();
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
)
506 $cols[] = $dao->Field
;
508 $tableCols[] = $dao->Field
;
510 // view intrernationalized columns through an alias
511 foreach ($columns[$table] as $column => $_) {
512 if (!$isUpgradeMode) {
513 $cols[] = "{$column}_{$locale} {$column}";
515 elseif (in_array("{$column}_{$locale}", $tableCols)) {
516 $cols[] = "{$column}_{$locale} {$column}";
519 return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
524 * @param null $tableName
526 public static function triggerInfo(&$info, $tableName = NULL) {
527 // get the current supported locales
528 $domain = new CRM_Core_DAO_Domain();
530 if (empty($domain->locales
)) {
534 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
535 $locale = array_pop($locales);
538 if (count($locales) == 0) {
542 $currentVer = CRM_Core_BAO_Domain
::version(TRUE);
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}";
551 $class = 'CRM_Core_I18n_SchemaStructure';
554 $columns =& $class::columns();
556 foreach ($columns as $table => $hash) {
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;";
570 foreach ($locales as $old) {
571 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
572 foreach (array_merge($locales, array(
578 $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
581 $trigger[] = 'END IF;';
584 $sql = implode(' ', $trigger);
586 'table' => array($table),
588 'event' => array('UPDATE'),
593 // take care of the ON INSERT triggers
594 foreach ($columns as $table => $hash) {
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};";
601 foreach ($locales as $old) {
602 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
603 foreach (array_merge($locales, array(
609 $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
612 $trigger[] = 'END IF;';
615 $sql = implode(' ', $trigger);
617 'table' => array($table),
619 'event' => array('INSERT'),