3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
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-2013
35 class CRM_Core_I18n_Schema
{
38 * Drop all views (for use by CRM_Core_DAO::dropAllTables() mostly).
42 static function dropAllViews() {
43 $domain = new CRM_Core_DAO_Domain();
45 if (!$domain->locales
) {
49 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
50 $tables = CRM_Core_I18n_SchemaStructure
::tables();
52 foreach ($locales as $locale) {
53 foreach ($tables as $table) {
54 CRM_Core_DAO
::executeQuery("DROP VIEW IF EXISTS {$table}_{$locale}");
60 * Switch database from single-lang to multi (by adding
61 * the first language and dropping the original columns).
63 * @param $locale string the first locale to create (migrate to)
67 static function makeMultilingual($locale) {
68 $domain = new CRM_Core_DAO_Domain();
71 // break early if the db is already multi-lang
72 if ($domain->locales
) {
76 $dao = new CRM_Core_DAO();
78 // build the column-adding SQL queries
79 $columns = CRM_Core_I18n_SchemaStructure
::columns();
80 $indices = CRM_Core_I18n_SchemaStructure
::indices();
82 foreach ($columns as $table => $hash) {
84 if (isset($indices[$table])) {
85 foreach ($indices[$table] as $index) {
86 $queries[] = "DROP INDEX {$index['name']} ON {$table}";
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}";
97 $queries[] = self
::createViewQuery($locale, $table, $dao);
100 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
103 // execute the queries without i18n rewriting
104 foreach ($queries as $query) {
105 $dao->query($query, FALSE);
108 // update civicrm_domain.locales
109 $domain->locales
= $locale;
114 * Switch database from multi-lang back to single (by dropping
115 * additional columns and views and retaining only the selected locale).
117 * @param $retain string the locale to retain
121 static function makeSinglelingual($retain) {
122 $domain = new CRM_Core_DAO_Domain
;
124 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
126 // break early if the db is already single-lang
131 // lets drop all triggers first
132 $logging = new CRM_Logging_Schema
;
133 $logging->dropTriggers( );
135 // turn subsequent tables singlelingual
136 $tables = CRM_Core_I18n_SchemaStructure
::tables();
137 foreach ($tables as $table) {
138 self
::makeSinglelingualTable($retain, $table);
141 // update civicrm_domain.locales
142 $domain->locales
= 'NULL';
145 //CRM-6963 -fair assumption.
149 // now lets rebuild all triggers
150 CRM_Core_DAO
::triggerRebuild( );
154 * Switch a given table from multi-lang to single (by retaining only the selected locale).
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
162 static function makeSinglelingualTable(
165 $class = 'CRM_Core_I18n_SchemaStructure',
168 $domain = new CRM_Core_DAO_Domain
;
170 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
172 // break early if the db is already single-lang
177 $columns =& $class::columns();
178 $indices =& $class::indices();
180 $dropQueries = array();
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}";
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}";
200 foreach ($locales as $loc) {
201 $queries[] = "DROP VIEW {$table}_{$loc}";
204 // add original indices
205 $queries = array_merge($queries, self
::createIndexQueries(NULL, $table));
207 // execute the queries without i18n rewriting
208 $dao = new CRM_Core_DAO
;
209 foreach ($queries as $query) {
210 $dao->query($query, FALSE);
213 foreach ($dropQueries as $query) {
214 $dao->query($query, FALSE);
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}");
227 // invoke the meta trigger creation call
228 CRM_Core_DAO
::triggerRebuild($table);
233 * Add a new locale to a multi-lang db, setting
234 * its values to the current default locale.
236 * @param $locale string the new locale to add
237 * @param $source string the locale to copy from
241 static function addLocale($locale, $source) {
242 // get the current supported locales
243 $domain = new CRM_Core_DAO_Domain();
245 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
247 // break early if the locale is already supported
248 if (in_array($locale, $locales)) {
252 $dao = new CRM_Core_DAO();
254 // build the required SQL queries
255 $columns = CRM_Core_I18n_SchemaStructure
::columns();
256 $indices = CRM_Core_I18n_SchemaStructure
::indices();
258 foreach ($columns as $table => $hash) {
260 foreach ($hash as $column => $type) {
261 // CRM-7854: skip existing columns
262 if (CRM_Core_DAO
::checkFieldExists($table, "{$column}_{$locale}", FALSE)) {
265 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
266 $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
270 $queries[] = self
::createViewQuery($locale, $table, $dao);
273 $queries = array_merge($queries, array_values(self
::createIndexQueries($locale, $table)));
276 // execute the queries without i18n rewriting
277 foreach ($queries as $query) {
278 $dao->query($query, FALSE);
281 // update civicrm_domain.locales
282 $locales[] = $locale;
283 $domain->locales
= implode(CRM_Core_DAO
::VALUE_SEPARATOR
, $locales);
286 // invoke the meta trigger creation call
287 CRM_Core_DAO
::triggerRebuild();
291 * Rebuild multilingual indices, views and triggers (useful for upgrades)
293 * @param $locales array locales to be rebuilt
294 * @param $version string version of schema structure to use
298 static function rebuildMultilingualSchema($locales, $version = NULL) {
300 $latest = self
::getLatestSchema($version);
301 require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php";
302 $class = "CRM_Core_I18n_SchemaStructure_{$latest}";
305 $class = 'CRM_Core_I18n_SchemaStructure';
307 $indices =& $class::indices();
308 $tables =& $class::tables();
310 $dao = new CRM_Core_DAO
;
312 // get all of the already existing indices
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
;
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])) {
337 foreach ($locales as $locale) {
338 foreach ($tables as $table) {
339 $queries[] = self
::createViewQuery($locale, $table, $dao, $class);
344 $last = array_pop($locales);
346 foreach ($queries as $query) {
347 $dao->query($query, FALSE);
350 // invoke the meta trigger creation call
351 CRM_Core_DAO
::triggerRebuild();
355 * Rewrite SQL query to use views to access tables with localized columns.
357 * @param $query string the query for rewrite
359 * @return string the rewritten query
361 static function rewriteQuery($query) {
363 $tables = self
::schemaStructureTables();
364 foreach ($tables as $table) {
365 $query = preg_replace("/([^'\"])({$table})([^_'\"])/", "\\1\\2{$dbLocale}\\3", $query);
367 // uncomment the below to rewrite the civicrm_value_* queries
368 // $query = preg_replace("/(civicrm_value_[a-z0-9_]+_\d+)([^_])/", "\\1{$dbLocale}\\2", $query);
372 static function schemaStructureTables($version = NULL, $force = FALSE) {
373 static $_tables = NULL;
374 if ($_tables === NULL ||
$force) {
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();
384 $tables = CRM_Core_I18n_SchemaStructure
::tables();
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);
395 // fetch all the SchemaStructure versions we ship and sort by version
397 foreach (scandir(dirname(__FILE__
)) as $file) {
399 if (preg_match('/^SchemaStructure_([0-9a-z_]+)\.php$/', $file, $matches)) {
400 $schemas[] = str_replace('_', '.', $matches[1]);
403 usort($schemas, 'version_compare');
405 // find the latest schema structure older than (or equal to) $version
407 $latest = array_pop($schemas);
408 } while (version_compare($latest, $version, '>'));
410 return str_replace('.', '_', $latest);
414 * CREATE INDEX queries for a given locale and table
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
420 * @return array array of CREATE INDEX queries
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])) {
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}";
438 $cols = implode(', ', $index['field']);
439 $name = $index['name'];
441 $name .= '_' . $locale;
443 // CRM-7854: skip existing indices
444 if (CRM_Core_DAO
::checkConstraintExists($table, $name)) {
447 $queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
453 * CREATE VIEW query for a given locale and table
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
460 * @return array array of CREATE INDEX queries
462 private static function createViewQuery($locale, $table, &$dao, $class = 'CRM_Core_I18n_SchemaStructure') {
463 $columns =& $class::columns();
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
)
471 $cols[] = $dao->Field
;
474 // view intrernationalized columns through an alias
475 foreach ($columns[$table] as $column => $_) {
476 $cols[] = "{$column}_{$locale} {$column}";
478 return "CREATE OR REPLACE VIEW {$table}_{$locale} AS SELECT " . implode(', ', $cols) . " FROM {$table}";
481 static function triggerInfo(&$info, $tableName = NULL) {
482 // get the current supported locales
483 $domain = new CRM_Core_DAO_Domain();
485 if (empty($domain->locales
)) {
489 $locales = explode(CRM_Core_DAO
::VALUE_SEPARATOR
, $domain->locales
);
490 $locale = array_pop($locales);
493 if (count($locales) == 0) {
497 $currentVer = CRM_Core_BAO_Domain
::version(TRUE);
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}";
506 $class = 'CRM_Core_I18n_SchemaStructure';
509 $columns =& $class::columns();
511 foreach ($columns as $table => $hash) {
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;";
525 foreach ($locales as $old) {
526 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
527 foreach (array_merge($locales, array(
532 $trigger[] = "IF NEW.{$column}_{$loc} IS NULL THEN SET NEW.{$column}_{$loc} = NEW.{$column}_{$old}; END IF;";
535 $trigger[] = 'END IF;';
538 $sql = implode(' ', $trigger);
539 $info[] = array('table' => array($table),
541 'event' => array('UPDATE'),
546 // take care of the ON INSERT triggers
547 foreach ($columns as $table => $hash) {
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};";
554 foreach ($locales as $old) {
555 $trigger[] = "ELSEIF NEW.{$column}_{$old} IS NOT NULL THEN";
556 foreach (array_merge($locales, array(
561 $trigger[] = "SET NEW.{$column}_{$loc} = NEW.{$column}_{$old};";
564 $trigger[] = 'END IF;';
567 $sql = implode(' ', $trigger);
568 $info[] = array('table' => array($table),
570 'event' => array('INSERT'),