1<?php 2/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */ 3 4/** 5 * Contains the Translation2_Admin_Container_mdb class 6 * 7 * PHP versions 4 and 5 8 * 9 * LICENSE: Redistribution and use in source and binary forms, with or without 10 * modification, are permitted provided that the following conditions are met: 11 * 1. Redistributions of source code must retain the above copyright 12 * notice, this list of conditions and the following disclaimer. 13 * 2. Redistributions in binary form must reproduce the above copyright 14 * notice, this list of conditions and the following disclaimer in the 15 * documentation and/or other materials provided with the distribution. 16 * 3. The name of the author may not be used to endorse or promote products 17 * derived from this software without specific prior written permission. 18 * 19 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR IMPLIED 20 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 21 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 22 * IN NO EVENT SHALL THE FREEBSD PROJECT OR CONTRIBUTORS BE LIABLE FOR ANY 23 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 24 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 25 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 26 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 27 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF 28 * THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 29 * 30 * @category Internationalization 31 * @package Translation2 32 * @author Lorenzo Alberton <l.alberton@quipo.it> 33 * @author Ian Eure <ieure@php.net> 34 * @copyright 2004-2007 Lorenzo Alberton, Ian Eure 35 * @license http://www.debian.org/misc/bsd.license BSD License (3 Clause) 36 * @version CVS: $Id: mdb.php 305985 2010-12-05 22:55:33Z clockwerx $ 37 * @link http://pear.php.net/package/Translation2 38 */ 39 40/** 41 * require Translation2_Container_mdb class 42 */ 43require_once 'Translation2/Container/mdb.php'; 44 45/** 46 * Storage driver for storing/fetching data to/from a database 47 * 48 * This storage driver can use all databases which are supported 49 * by the PEAR::MDB abstraction layer to store and fetch data. 50 * 51 * @category Internationalization 52 * @package Translation2 53 * @author Lorenzo Alberton <l.alberton@quipo.it> 54 * @author Ian Eure <ieure@php.net> 55 * @copyright 2004-2007 Lorenzo Alberton, Ian Eure 56 * @license http://www.debian.org/misc/bsd.license BSD License (3 Clause) 57 * @link http://pear.php.net/package/Translation2 58 */ 59class Translation2_Admin_Container_mdb extends Translation2_Container_mdb 60{ 61 // {{{ addLang() 62 63 /** 64 * Creates a new table to store the strings in this language. 65 * If the table is shared with other langs, it is ALTERed to 66 * hold strings in this lang too. 67 * 68 * @param array $langData language data 69 * @param array $options options 70 * 71 * @return true|PEAR_Error 72 */ 73 function addLang($langData, $options = array()) 74 { 75 $tables = $this->db->listTables(); 76 if (PEAR::isError($tables)) { 77 return $tables; 78 } 79 80 $lang_col = $this->_getLangCol($langData['lang_id']); 81 82 if (in_array($langData['table_name'], $tables)) { 83 //table exists 84 $query = sprintf('ALTER TABLE %s ADD %s%s TEXT', 85 $this->db->quoteIdentifier($langData['table_name']), 86 $this->db->phptype == 'mssql' ? '' : 'COLUMN ', 87 $this->db->quoteIdentifier($lang_col) 88 ); 89 ++$this->_queries; 90 return $this->db->query($query); 91 } 92 93 //table does not exist 94 $queries = array(); 95 $queries[] = sprintf('CREATE TABLE %s ( ' 96 .'%s VARCHAR(%d) default NULL, ' 97 .'%s TEXT NOT NULL, ' 98 .'%s TEXT )', 99 $this->db->quoteIdentifier($langData['table_name']), 100 $this->db->quoteIdentifier($this->options['string_page_id_col']), 101 (int)$this->options['string_page_id_col_length'], 102 $this->db->quoteIdentifier($this->options['string_id_col']), 103 $this->db->quoteIdentifier($lang_col) 104 ); 105 $mysqlClause = ($this->db->phptype == 'mysql') ? '(255)' : ''; 106 107 $index_name = sprintf('%s_%s_%s_index', 108 $langData['table_name'], 109 $this->options['string_page_id_col'], 110 $this->options['string_id_col'] 111 ); 112 $queries[] = sprintf('CREATE UNIQUE INDEX %s ON %s (%s, %s%s)', 113 $this->db->quoteIdentifier($index_name), 114 $this->db->quoteIdentifier($langData['table_name']), 115 $this->db->quoteIdentifier($this->options['string_page_id_col']), 116 $this->db->quoteIdentifier($this->options['string_id_col']), 117 $mysqlClause 118 ); 119 120 $index_name = sprintf('%s_%s_index', 121 $langData['table_name'], 122 $this->options['string_page_id_col'] 123 ); 124 $queries[] = sprintf('CREATE INDEX %s ON %s (%s)', 125 $this->db->quoteIdentifier($index_name), 126 $this->db->quoteIdentifier($langData['table_name']), 127 $this->db->quoteIdentifier($this->options['string_page_id_col']) 128 ); 129 130 $index_name = sprintf('%s_%s_index', 131 $langData['table_name'], 132 $this->options['string_id_col'] 133 ); 134 $queries[] = sprintf('CREATE INDEX %s ON %s (%s%s)', 135 $this->db->quoteIdentifier($index_name), 136 $this->db->quoteIdentifier($langData['table_name']), 137 $this->db->quoteIdentifier($this->options['string_id_col']), 138 $mysqlClause 139 ); 140 141 foreach ($queries as $query) { 142 ++$this->_queries; 143 $res = $this->db->query($query); 144 if (PEAR::isError($res)) { 145 return $res; 146 } 147 } 148 return true; 149 } 150 151 // }}} 152 // {{{ addLangToList() 153 154 /** 155 * Creates a new entry in the langsAvail table. 156 * If the table doesn't exist yet, it is created. 157 * 158 * @param array $langData array('lang_id' => 'en', 159 * 'table_name' => 'i18n', 160 * 'name' => 'english', 161 * 'meta' => 'some meta info', 162 * 'error_text' => 'not available', 163 * 'encoding' => 'iso-8859-1'); 164 * 165 * @return true|PEAR_Error 166 */ 167 function addLangToList($langData) 168 { 169 $tables = $this->db->listTables(); 170 if (PEAR::isError($tables)) { 171 return $tables; 172 } 173 174 if (!in_array($this->options['langs_avail_table'], $tables)) { 175 $queries = array(); 176 $queries[] = sprintf('CREATE TABLE %s (' 177 .'%s VARCHAR(16), ' 178 .'%s VARCHAR(200), ' 179 .'%s TEXT, ' 180 .'%s VARCHAR(250), ' 181 .'%s VARCHAR(16) )', 182 $this->db->quoteIdentifier($this->options['langs_avail_table']), 183 $this->db->quoteIdentifier($this->options['lang_id_col']), 184 $this->db->quoteIdentifier($this->options['lang_name_col']), 185 $this->db->quoteIdentifier($this->options['lang_meta_col']), 186 $this->db->quoteIdentifier($this->options['lang_errmsg_col']), 187 $this->db->quoteIdentifier($this->options['lang_encoding_col']) 188 ); 189 $queries[] = sprintf('CREATE UNIQUE INDEX %s_%s_index ON %s (%s)', 190 $this->options['langs_avail_table'], 191 $this->options['lang_id_col'], 192 $this->db->quoteIdentifier($this->options['langs_avail_table']), 193 $this->db->quoteIdentifier($this->options['lang_id_col']) 194 ); 195 196 foreach ($queries as $query) { 197 ++$this->_queries; 198 $res = $this->db->query($query); 199 if (PEAR::isError($res)) { 200 return $res; 201 } 202 } 203 } 204 205 $query = sprintf('INSERT INTO %s (%s, %s, %s, %s, %s) VALUES (%s, %s, %s, %s, %s)', 206 $this->db->quoteIdentifier($this->options['langs_avail_table']), 207 $this->db->quoteIdentifier($this->options['lang_id_col']), 208 $this->db->quoteIdentifier($this->options['lang_name_col']), 209 $this->db->quoteIdentifier($this->options['lang_meta_col']), 210 $this->db->quoteIdentifier($this->options['lang_errmsg_col']), 211 $this->db->quoteIdentifier($this->options['lang_encoding_col']), 212 $this->db->getTextValue($langData['lang_id']), 213 $this->db->getTextValue($langData['name']), 214 $this->db->getTextValue($langData['meta']), 215 $this->db->getTextValue($langData['error_text']), 216 $this->db->getTextValue($langData['encoding']) 217 ); 218 219 ++$this->_queries; 220 $success = $this->db->query($query); 221 $this->options['strings_tables'][$langData['lang_id']] = $langData['table_name']; 222 return $success; 223 } 224 225 // }}} 226 // {{{ removeLang() 227 228 /** 229 * Remove the lang from the langsAvail table and drop the strings table. 230 * If the strings table holds other langs and $force==false, then 231 * only the lang column is dropped. If $force==true the whole 232 * table is dropped without any check 233 * 234 * @param string $langID language ID 235 * @param boolean $force if true, drop the whole table without further checks 236 * 237 * @return true|PEAR_Error 238 */ 239 function removeLang($langID, $force) 240 { 241 //remove from langsAvail 242 $query = sprintf('DELETE FROM %s WHERE %s = %s', 243 $this->db->quoteIdentifier($this->options['langs_avail_table']), 244 $this->db->quoteIdentifier($this->options['lang_id_col']), 245 $this->db->getTextValue($langID) 246 ); 247 ++$this->_queries; 248 $res = $this->db->query($query); 249 if (PEAR::isError($res)) { 250 return $res; 251 } 252 253 $lang_table = $this->_getLangTable($langID); 254 if ($force) { 255 //remove the whole table 256 ++$this->_queries; 257 return $this->db->query('DROP TABLE ' . $this->db->quoteIdentifier($lang_table)); 258 } 259 260 //drop only the column for this lang 261 $query = sprintf('ALTER TABLE %s DROP COLUMN %s', 262 $this->db->quoteIdentifier($lang_table), 263 $this->db->quoteIdentifier($this->_getLangCol($langID)) 264 ); 265 ++$this->_queries; 266 return $this->db->query($query); 267 } 268 269 // }}} 270 // {{{ updateLang() 271 272 /** 273 * Update the lang info in the langsAvail table 274 * 275 * @param array $langData array of language data 276 * 277 * @return true|PEAR_Error 278 */ 279 function updateLang($langData) 280 { 281 $allFields = array( 282 //'lang_id' => 'lang_id_col', 283 'name' => 'lang_name_col', 284 'meta' => 'lang_meta_col', 285 'error_text' => 'lang_errmsg_col', 286 'encoding' => 'lang_encoding_col', 287 ); 288 $updateFields = array_keys($langData); 289 $langSet = array(); 290 foreach ($allFields as $field => $col) { 291 if (in_array($field, $updateFields)) { 292 $langSet[] = $this->db->quoteIdentifier($this->options[$col]) . ' = ' . 293 $this->db->getTextValue($langData[$field]); 294 } 295 } 296 $query = sprintf('UPDATE %s SET %s WHERE %s=%s', 297 $this->db->quoteIdentifier($this->options['langs_avail_table']), 298 implode(', ', $langSet), 299 $this->db->quoteIdentifier($this->options['lang_id_col']), 300 $this->db->getTextValue($langData['lang_id']) 301 ); 302 303 ++$this->_queries; 304 $success = $this->db->query($query); 305 $this->fetchLangs(); //update memory cache 306 return $success; 307 } 308 309 // }}} 310 // {{{ add() 311 312 /** 313 * Add a new entry in the strings table. 314 * 315 * @param string $stringID string ID 316 * @param string $pageID page/group ID 317 * @param array $stringArray Associative array with string translations. 318 * Sample format: array('en' => 'sample', 'it' => 'esempio') 319 * 320 * @return true|PEAR_Error 321 */ 322 function add($stringID, $pageID, $stringArray) 323 { 324 $langs = array_intersect( 325 array_keys($stringArray), 326 $this->getLangs('ids') 327 ); 328 329 if (!count($langs)) { 330 //return error: no valid lang provided 331 return true; 332 } 333 334 // Langs may be in different tables - we need to split up queries along 335 // table lines, so we can keep DB traffic to a minimum. 336 337 $unquoted_stringID = $stringID; 338 $unquoted_pageID = $pageID; 339 $stringID = $this->db->getTextValue($stringID); 340 $pageID = is_null($pageID) ? 'NULL' : $this->db->getTextValue($pageID); 341 // Loop over the tables we need to insert into. 342 foreach ($this->_tableLangs($langs) as $table => $tableLangs) { 343 $exists = $this->_recordExists($unquoted_stringID, $unquoted_pageID, $table); 344 if (PEAR::isError($exists)) { 345 return $exists; 346 } 347 $func = $exists ? '_getUpdateQuery' : '_getInsertQuery'; 348 $query = $this->$func($table, $tableLangs, $stringID, $pageID, $stringArray); 349 350 ++$this->_queries; 351 $res = $this->db->query($query); 352 if (PEAR::isError($res)) { 353 return $res; 354 } 355 } 356 357 return true; 358 } 359 360 // }}} 361 // {{{ update() 362 363 /** 364 * Update an existing entry in the strings table. 365 * 366 * @param string $stringID string ID 367 * @param string $pageID page/group ID 368 * @param array $stringArray Associative array with string translations. 369 * Sample format: array('en' => 'sample', 'it' => 'esempio') 370 * 371 * @return true|PEAR_Error 372 */ 373 function update($stringID, $pageID, $stringArray) 374 { 375 return $this->add($stringID, $pageID, $stringArray); 376 } 377 378 // }}} 379 // {{{ _getInsertQuery() 380 381 /** 382 * Build a SQL query to INSERT a record 383 * 384 * @param string $table table name 385 * @param array &$tableLangs tables containing the languages 386 * @param string $stringID string ID 387 * @param string $pageID page/group ID 388 * @param array &$stringArray array of strings 389 * 390 * @return string INSERT query 391 * @access private 392 */ 393 function _getInsertQuery($table, &$tableLangs, $stringID, $pageID, &$stringArray) 394 { 395 $tableCols = $this->_getLangCols($tableLangs); 396 $langData = array(); 397 foreach ($tableLangs as $lang) { 398 $langData[$lang] = $this->db->getTextValue($stringArray[$lang]); 399 } 400 foreach (array_keys($tableCols) as $k) { 401 $tableCols[$k] = $this->db->quoteIdentifier($tableCols[$k]); 402 } 403 404 return sprintf('INSERT INTO %s (%s, %s, %s) VALUES (%s, %s, %s)', 405 $this->db->quoteIdentifier($table), 406 $this->db->quoteIdentifier($this->options['string_id_col']), 407 $this->db->quoteIdentifier($this->options['string_page_id_col']), 408 implode(', ', $tableCols), 409 $stringID, 410 $pageID, 411 implode(', ', $langData) 412 ); 413 } 414 415 // }}} 416 // {{{ _getUpdateQuery() 417 418 /** 419 * Build a SQL query to UPDATE a record 420 * 421 * @param string $table table name 422 * @param array &$tableLangs tables containing the languages 423 * @param string $stringID string ID 424 * @param string $pageID page/group ID 425 * @param array &$stringArray array of strings 426 * 427 * @return string UPDATE query 428 * @access private 429 */ 430 function _getUpdateQuery($table, &$tableLangs, $stringID, $pageID, &$stringArray) 431 { 432 $tableCols = $this->_getLangCols($tableLangs); 433 $langSet = array(); 434 foreach ($tableLangs as $lang) { 435 $langSet[] = $this->db->quoteIdentifier($tableCols[$lang]) . ' = ' . 436 $this->db->getTextValue($stringArray[$lang]); 437 } 438 439 return sprintf('UPDATE %s SET %s WHERE %s = %s AND %s = %s', 440 $this->db->quoteIdentifier($table), 441 implode(', ', $langSet), 442 $this->db->quoteIdentifier($this->options['string_id_col']), 443 $stringID, 444 $this->db->quoteIdentifier($this->options['string_page_id_col']), 445 $pageID 446 ); 447 } 448 449 // }}} 450 // {{{ remove() 451 452 /** 453 * Remove an entry from the strings table. 454 * 455 * @param string $stringID string ID 456 * @param string $pageID page/group ID 457 * 458 * @return boolean|PEAR_Error 459 */ 460 function remove($stringID, $pageID) 461 { 462 $tables = array_unique($this->_getLangTables()); 463 464 $stringID = $this->db->getTextValue($stringID); 465 // get the tables and skip the non existent ones 466 $dbTables = $this->db->listTables(); 467 foreach ($tables as $table) { 468 if (!in_array($table, $dbTables)) { 469 continue; 470 } 471 $query = sprintf('DELETE FROM %s WHERE %s = %s AND %s', 472 $this->db->quoteIdentifier($table), 473 $this->db->quoteIdentifier($this->options['string_id_col']), 474 $stringID, 475 $this->db->quoteIdentifier($this->options['string_page_id_col']) 476 ); 477 if (is_null($pageID)) { 478 $query .= ' IS NULL'; 479 } else { 480 $query .= ' = ' . $this->db->getTextValue($pageID); 481 } 482 483 ++$this->_queries; 484 $res = $this->db->query($query); 485 if (PEAR::isError($res)) { 486 return $res; 487 } 488 } 489 490 return true; 491 } 492 493 // }}} 494 // {{{ removePage 495 496 /** 497 * Remove all the strings in the given page/group 498 * 499 * @param string $pageID page/group ID 500 * 501 * @return mixed true on success, PEAR_Error on failure 502 */ 503 function removePage($pageID = null) 504 { 505 $tables = array_unique($this->_getLangTables()); 506 507 // get the tables and skip the non existent ones 508 $dbTables = $this->db->listTables(); 509 foreach ($tables as $table) { 510 if (!in_array($table, $dbTables)) { 511 continue; 512 } 513 $query = sprintf('DELETE FROM %s WHERE %s', 514 $this->db->quoteIdentifier($table, true), 515 $this->db->quoteIdentifier($this->options['string_page_id_col'], true) 516 ); 517 if (is_null($pageID)) { 518 $query .= ' IS NULL'; 519 } else { 520 $query .= ' = ' . $this->db->getTextValue($pageID); 521 } 522 523 ++$this->_queries; 524 $res = $this->db->query($query); 525 if (PEAR::isError($res)) { 526 return $res; 527 } 528 } 529 530 return true; 531 } 532 533 // }}} 534 // {{{ getPageNames() 535 536 /** 537 * Get a list of all the pageIDs in any table. 538 * 539 * @return array 540 */ 541 function getPageNames() 542 { 543 $pages = array(); 544 foreach ($this->_getLangTables() as $table) { 545 $query = sprintf('SELECT DISTINCT %s FROM %s', 546 $this->db->quoteIdentifier($this->options['string_page_id_col']), 547 $this->db->quoteIdentifier($table) 548 ); 549 ++$this->_queries; 550 $res = $this->db->getCol($query); 551 if (PEAR::isError($res)) { 552 return $res; 553 } 554 $pages = array_merge($pages, $res); 555 } 556 return array_unique($pages); 557 } 558 559 // }}} 560 // {{{ _tableLangs() 561 562 /** 563 * Get table -> language mapping 564 * 565 * The key of the array is the table that a language is stored in; 566 * the value is an /array/ of languages stored in that table. 567 * 568 * @param array $langs Languages to get mapping for 569 * 570 * @return array Table -> language mapping 571 * @access private 572 * @see Translation2_Container_MDB::_getLangTable() 573 */ 574 function &_tableLangs($langs) 575 { 576 $tables = array(); 577 foreach ($langs as $lang) { 578 $table = $this->_getLangTable($lang); 579 $tables[$table][] = $lang; 580 } 581 return $tables; 582 } 583 584 // }}} 585 // {{{ _getLangTables() 586 587 /** 588 * Get tables for languages 589 * 590 * This is like _getLangTable(), but it returns an array of the tables for 591 * multiple languages. 592 * 593 * @param array $langs Languages to get tables for 594 * 595 * @return array 596 * @access private 597 */ 598 function &_getLangTables($langs = null) 599 { 600 $tables = array(); 601 $langs = !is_array($langs) ? $this->getLangs('ids') : $langs; 602 foreach ($langs as $lang) { 603 $tables[] = $this->_getLangTable($lang); 604 } 605 $tables = array_unique($tables); 606 return $tables; 607 } 608 609 // }}} 610 // {{{ _getLangCols() 611 612 /** 613 * Get table columns strings are stored in 614 * 615 * This is like _getLangCol(), except it returns an array which contains 616 * the mapping for multiple languages. 617 * 618 * @param array $langs Languages to get mapping for 619 * 620 * @return array Language -> column mapping 621 * @access private 622 * @see Translation2_Container_MDB::_getLangCol() 623 */ 624 function &_getLangCols($langs) 625 { 626 $cols = array(); 627 foreach ($langs as $lang) { 628 $cols[$lang] = $this->_getLangCol($lang); 629 } 630 return $cols; 631 } 632 633 // }}} 634 // {{{ _recordExists() 635 636 /** 637 * Check if there's already a record in the table with the 638 * given (pageID, stringID) pair. 639 * 640 * @param string $stringID string ID 641 * @param string $pageID page/group ID 642 * @param string $table table name 643 * 644 * @return boolean 645 * @access private 646 */ 647 function _recordExists($stringID, $pageID, $table) 648 { 649 $stringID = $this->db->getTextValue($stringID); 650 $pageID = is_null($pageID) ? ' IS NULL' : ' = ' . $this->db->getTextValue($pageID); 651 $query = sprintf('SELECT COUNT(*) FROM %s WHERE %s=%s AND %s%s', 652 $this->db->quoteIdentifier($table), 653 $this->db->quoteIdentifier($this->options['string_id_col']), 654 $stringID, 655 $this->db->quoteIdentifier($this->options['string_page_id_col']), 656 $pageID 657 ); 658 ++$this->_queries; 659 $res = $this->db->getOne($query); 660 if (PEAR::isError($res)) { 661 return $res; 662 } 663 return ($res > 0); 664 } 665 666 // }}} 667 // {{{ _filterStringsByTable() 668 669 /** 670 * Get only the strings for the langs in the given table 671 * 672 * @param array $stringArray Associative array with string translations. 673 * Sample format: array('en' => 'sample', 'it' => 'esempio') 674 * @param string $table table name 675 * 676 * @return array strings 677 * @access private 678 */ 679 function &_filterStringsByTable($stringArray, $table) 680 { 681 $strings = array(); 682 foreach ($stringArray as $lang => $string) { 683 if ($table == $this->_getLangTable($lang)) { 684 $strings[$lang] = $string; 685 } 686 } 687 return $strings; 688 } 689 690 // }}} 691 // {{{ _getLangsInTable() 692 693 /** 694 * Get the languages sharing the given table 695 * 696 * @param string $table table name 697 * 698 * @return array 699 */ 700 function &_getLangsInTable($table) 701 { 702 $this->fetchLangs(); // force cache refresh 703 $langsInTable = array(); 704 foreach (array_keys($this->langs) as $lang) { 705 if ($table == $this->_getLangTable($lang)) { 706 $langsInTable[] = $lang; 707 } 708 } 709 return $langsInTable; 710 } 711 712 // }}} 713} 714?>