typo3/sysext/em/classes/database/class.tx_em_database.php

   1 <?php
   2 /***************************************************************
   3  *  Copyright notice
   4  *
   5  *  (c) 2010 Marcus Krause <marcus#exp2010@t3sec.info>
   6  *  (c) 2010 Steffen Kamper <info@sk-typo3.de>
   7  *  All rights reserved
   8  *
   9  *  This script is part of the TYPO3 project. The TYPO3 project is
  10  *  free software; you can redistribute it and/or modify
  11  *  it under the terms of the GNU General Public License as published by
  12  *  the Free Software Foundation; either version 2 of the License, or
  13  *  (at your option) any later version.
  14  *
  15  *  The GNU General Public License can be found at
  16  *  http://www.gnu.org/copyleft/gpl.html.
  17  *
  18  *  This script is distributed in the hope that it will be useful,
  19  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
  20  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  21  *  GNU General Public License for more details.
  22  *
  23  *  This copyright notice MUST APPEAR in all copies of the script!
  24  ***************************************************************/
  25 /**
  26  * class.tx_em_database.php
  27  *
  28  * Module: Extension manager - DB access
  29  *
  30  * @author  Marcus Krause <marcus#exp2010@t3sec.info>
  31  * @author  Steffen Kamper <info@sk-typo3.de>
  32  */
  33
  34 /**
  35  * DB access class for extension manager.
  36  *
  37  * Contains static methods for DB operations.
  38  *
  39  * @author        Marcus Krause <marcus#exp2010@t3sec.info>
  40  * @author        Steffen Kamper <info@sk-typo3.de>
  41  *
  42  * @since          2010-02-27
  43  * @package      TYPO3
  44  * @subpackage  EM
  45  */
  46 final class tx_em_Database {
  47
  48         const MULTI_LINEBREAKS = "\n\n\n";
  49
  50         const TABLE_REPOSITORY = 'sys_ter';
  51
  52         const TABLE_EXTENSION = 'cache_extensions';
  53
  54
  55         /**
  56          * Get the count of extensions in cache_extensions from a repository.
  57          *
  58          * If $repository parameter is obmitted, sum of all extensions will be
  59          * returned.
  60          *
  61          * @access  public
  62          * @param   integer  $repository  (optional) repository uid of extensions to count
  63          * @return  integer  sum of extensions in database
  64          */
  65         public function getExtensionCountFromRepository($repository = NULL) {
  66                 if (is_null($repository)) {
  67                         return $GLOBALS['TYPO3_DB']->exec_SELECTcountRows(
  68                                 'DISTINCT extkey',
  69                                 self::TABLE_EXTENSION
  70                         );
  71                 } else {
  72                         return $GLOBALS['TYPO3_DB']->exec_SELECTcountRows(
  73                                 'DISTINCT extkey',
  74                                 self::TABLE_EXTENSION,
  75                                 'repository=' . intval($repository)
  76                         );
  77                 }
  78         }
  79
  80         /**
  81          * Get extension list from cache_extensions
  82          *
  83          * @param int $repository
  84          * @param string $addFields
  85          * @param string $andWhere
  86          * @param string $order
  87          * @param string $limit
  88          * @return array
  89          */
  90         public function getExtensionListFromRepository($repository, $addFields = '', $andWhere = '', $order = '', $limit = '') {
  91                 $ret = array();
  92                 $temp = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
  93                         'count(*) AS count',
  94                         'cache_extensions',
  95                         'repository=' . intval($repository) . $andWhere,
  96                         'extkey'
  97                 );
  98                 $ret['count'] = count($temp);
  99
 100                 $ret['results'] = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
 101                         'cache_extensions.*, count(*) AS versions, cache_extensions.intversion AS maxintversion' .
 102                         ($addFields === '' ? '' : ',' . $addFields),
 103                         'cache_extensions JOIN cache_extensions AS ce ON cache_extensions.extkey = ce.extkey',
 104                         'cache_extensions.lastversion=1 AND cache_extensions.repository=' . intval($repository) . $andWhere,
 105                         'ce.extkey',
 106                         $order,
 107                         $limit
 108                 );
 109                 //debug($GLOBALS['TYPO3_DB']->debug_lastBuiltQuery);
 110                 return $ret;
 111         }
 112
 113         /**
 114          * Get versions of extension
 115          *
 116          * @param int $repository
 117          * @param string $extKey
 118          * @return array $versions
 119          */
 120         public function getExtensionVersionsFromRepository($repository, $extKey) {
 121                 $versions = array();
 122                 //TODO: implement
 123                 return $versions;
 124         }
 125
 126         /**
 127          * Function inserts a repository object into database.
 128          *
 129          * @access  public
 130          * @param   tx_em_Repository $repository  repository object
 131          * @return  void
 132          */
 133         public function updateRepository(tx_em_Repository $repository) {
 134                 $repositoryData = array(
 135                         'title' => $repository->getTitle(),
 136                         'description' => $repository->getDescription(),
 137                         'wsdl_url' => $repository->getWsdlUrl(),
 138                         'mirror_url' => $repository->getMirrorListUrl(),
 139                         'lastUpdated' => $repository->getLastUpdate(),
 140                         'extCount' => $repository->getExtensionCount(),
 141                 );
 142                 $GLOBALS['TYPO3_DB']->exec_UPDATEquery(
 143                         self::TABLE_REPOSITORY,
 144                         'uid=' . $repository->getId(),
 145                         $repositoryData
 146                 );
 147
 148         }
 149
 150
 151         /**
 152          * Function inserts a repository object into database.
 153          *
 154          * @access  public
 155          * @param   tx_em_Repository $repository  repository object
 156          * @return  integer  UID of the newly inserted repository object
 157          */
 158         public function insertRepository(tx_em_Repository $repository) {
 159                 $repositoryData = array(
 160                         'title' => $repository->getTitle(),
 161                         'description' => $repository->getDescription(),
 162                         'wsdl_url' => $repository->getWsdlUrl(),
 163                         'mirror_url' => $repository->getMirrorListUrl(),
 164                         'lastUpdated' => $repository->getLastUpdate(),
 165                         'extCount' => $repository->getExtensionCount(),
 166                 );
 167                 $GLOBALS['TYPO3_DB']->exec_INSERTquery(
 168                         self::TABLE_REPOSITORY,
 169                         $repositoryData
 170                 );
 171                 return $GLOBALS['TYPO3_DB']->sql_insert_id();
 172         }
 173
 174         /**
 175          * Deletes given Repository
 176          *
 177          * @param  tx_em_Repository $repository  repository object
 178          * @return void
 179          */
 180         public function deleteRepository(tx_em_Repository $repository) {
 181                 $GLOBALS['TYPO3_DB']->exec_DELETEquery(
 182                          self::TABLE_REPOSITORY,
 183                         'uid=' . $repository->getId()
 184                  );
 185         }
 186
 187         /**
 188          * Updates ExtCount and lastUpdated  in Repository eg after import
 189          * @param  int $extCount
 190          * @param int $uid
 191          * @return void
 192          */
 193         public function updateRepositoryCount($extCount, $uid = 1) {
 194                 $GLOBALS['TYPO3_DB']->exec_UPDATEquery(
 195                          self::TABLE_REPOSITORY,
 196                          'uid=' . intval($uid),
 197                          array (
 198                                 'lastUpdated' => time(),
 199                                 'extCount' => intval($extCount)
 200                          ));
 201         }
 202
 203         /**
 204          * Insert version
 205          *
 206          * @param  $arrFields
 207          * @return void
 208          */
 209         public function insertVersion(array $arrFields) {
 210                 $GLOBALS['TYPO3_DB']->exec_INSERTquery(self::TABLE_EXTENSION, $arrFields);
 211         }
 212
 213         /**
 214          * Update the lastversion field after update
 215          *
 216          * @param int $repositoryUid
 217          * @return void
 218          */
 219         public function insertLastVersion($repositoryUid = 1) {
 220                 $groupedRows = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
 221                         'extkey, version, max(intversion) maxintversion',
 222                         'cache_extensions',
 223                         'repository=' . intval($repositoryUid),
 224                         'extkey'
 225                 );
 226                 $extensions = count($groupedRows);
 227
 228                 if ($extensions > 0) {
 229                         // set all to 0
 230                         $GLOBALS['TYPO3_DB']->exec_UPDATEquery(
 231                                 'cache_extensions',
 232                                 'lastversion=1 AND repository=' . intval($repositoryUid),
 233                                 array('lastversion' => 0)
 234                         );
 235
 236                                 // Find latest version of extensions and set lastversion to 1 for these
 237                         foreach ($groupedRows as $row) {
 238                                 $GLOBALS['TYPO3_DB']->exec_UPDATEquery(
 239                                         'cache_extensions',
 240                                         'extkey="' . $row['extkey'] . '" AND intversion="' . $row['maxintversion'] . '" AND repository=' . intval($repositoryUid),
 241                                         array('lastversion' => 1)
 242                                 );
 243                         }
 244                 }
 245
 246                 return $extensions;
 247         }
 248
 249
 250         /**
 251          * Method finds and returns repository fields identified by its UID.
 252          *
 253          * @access  public
 254          * @param   int  $uid  repository UID
 255          */
 256         public function getRepositoryByUID($uid) {
 257                 $row = $GLOBALS['TYPO3_DB']->exec_SELECTgetSingleRow('*', self::TABLE_REPOSITORY, 'uid=' . intval($uid));
 258
 259                 return $row;
 260         }
 261
 262         /**
 263          * Method finds and returns repository identified by its title
 264          *
 265          * @param  $title
 266          * @return
 267          */
 268         public function getRepositoryByTitle($title) {
 269                 return $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
 270                         '*',
 271                         self::TABLE_REPOSITORY,
 272                         'title=' . $GLOBALS['TYPO3_DB']->fullQuoteStr($title,
 273                         self::TABLE_REPOSITORY)
 274                 );
 275         }
 276
 277         /**
 278          * Get available repositories
 279          *
 280          * @param string $where
 281          * @return array
 282          */
 283         public function getRepositories($where = NULL) {
 284                 return $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
 285                         '*',
 286                         self::TABLE_REPOSITORY,
 287                         $where ? $where : ''
 288                 );
 289         }
 290
 291         /**
 292          * Dump table content
 293          * Is DBAL compliant, but the dump format is written as MySQL standard. If the INSERT statements should be imported in a DBMS using other quoting than MySQL they must first be translated. t3lib_sqlengine can parse these queries correctly and translate them somehow.
 294          *
 295          * @param       string          Table name
 296          * @param       array           Field structure
 297          * @return      string          SQL Content of dump (INSERT statements)
 298          */
 299         function dumpTableContent($table, $fieldStructure) {
 300
 301                 // Substitution of certain characters (borrowed from phpMySQL):
 302                 $search = array('\\', '\'', "\x00", "\x0a", "\x0d", "\x1a");
 303                 $replace = array('\\\\', '\\\'', '\0', '\n', '\r', '\Z');
 304
 305                 $lines = array();
 306
 307                 // Select all rows from the table:
 308                 $result = $GLOBALS['TYPO3_DB']->exec_SELECTquery('*', $table, '');
 309
 310                 // Traverse the selected rows and dump each row as a line in the file:
 311                 while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($result)) {
 312                         $values = array();
 313                         foreach ($fieldStructure as $field => $structure) {
 314                                 $values[] = isset($row[$field]) ? "'" . str_replace($search, $replace, $row[$field]) . "'" : 'NULL';
 315                         }
 316                         $lines[] = 'INSERT INTO ' . $table . ' VALUES (' . implode(', ', $values) . ');';
 317                 }
 318
 319                 // Free DB result:
 320                 $GLOBALS['TYPO3_DB']->sql_free_result($result);
 321
 322                 // Implode lines and return:
 323                 return implode(LF, $lines);
 324         }
 325
 326         /**
 327          * Gets the table and field structure from database.
 328          * Which fields and which tables are determined from the ext_tables.sql file
 329          *
 330          * @param       string          Array with table.field values
 331          * @return      array           Array of tables and fields splitted.
 332          */
 333         function getTableAndFieldStructure($parts) {
 334                         /** @var $instObj t3lib_install_Sql */
 335                 $instObj = t3lib_div::makeInstance('t3lib_install_Sql');
 336                 $dbFields = $instObj->getFieldDefinitions_database(TYPO3_db);
 337
 338                 $outTables = array();
 339                 foreach ($parts as $table) {
 340                         $sub = explode('.', $table);
 341                         if ($sub[0] && isset($dbFields[$sub[0]])) {
 342                                 if ($sub[1]) {
 343                                         $key = explode('KEY:', $sub[1], 2);
 344                                         if (count($key) == 2 && !$key[0]) { // key:
 345                                                 if (isset($dbFields[$sub[0]]['keys'][$key[1]])) {
 346                                                         $outTables[$sub[0]]['keys'][$key[1]] = $dbFields[$sub[0]]['keys'][$key[1]];
 347                                                 }
 348                                         } else {
 349                                                 if (isset($dbFields[$sub[0]]['fields'][$sub[1]])) {
 350                                                         $outTables[$sub[0]]['fields'][$sub[1]] = $dbFields[$sub[0]]['fields'][$sub[1]];
 351                                                 }
 352                                         }
 353                                 } else {
 354                                         $outTables[$sub[0]] = $dbFields[$sub[0]];
 355                                 }
 356                         }
 357                 }
 358
 359                 return $outTables;
 360         }
 361
 362
 363         /**
 364          * Makes a dump of the tables/fields definitions for an extension
 365          *
 366          * @param       array           Array with table => field/key definition arrays in
 367          * @return      string          SQL for the table definitions
 368          * @see dumpStaticTables()
 369          */
 370         function dumpTableAndFieldStructure($arr) {
 371                 $tables = array();
 372
 373                 if (count($arr)) {
 374
 375                         // Get file header comment:
 376                         $tables[] = self::dumpHeader();
 377
 378                         // Traverse tables, write each table/field definition:
 379                         foreach ($arr as $table => $fieldKeyInfo) {
 380                                 $tables[] = self::dumpTableHeader($table, $fieldKeyInfo);
 381                         }
 382                 }
 383
 384                 // Return result:
 385                 return implode(LF . LF . LF, $tables);
 386         }
 387
 388         /**
 389          * Link to dump of database tables
 390          *
 391          * @param       array  $tablesArray
 392          * @param       string $extKey
 393          * @param       array  $additionalLinkParameter
 394          * @return      string          HTML
 395          */
 396         function dumpDataTablesLine($tablesArray, $extKey, $additionalLinkParameter = array()) {
 397                 $tables = array();
 398                 $tablesNA = array();
 399                 $allTables = array_keys($GLOBALS['TYPO3_DB']->admin_get_tables());
 400
 401                 foreach ($tablesArray as $tableName) {
 402                         if (in_array($tableName, $allTables)) {
 403                                 $count = $GLOBALS['TYPO3_DB']->exec_SELECTcountRows('*', $tableName);
 404                                 $tables[$tableName] = '<tr><td>&nbsp;</td><td>
 405                                         <a class="t3-link dumpLink" href="' .
 406                                                 htmlspecialchars(t3lib_div::linkThisScript(
 407                                                         array_merge(array(
 408                                                                 'CMD[dumpTables]' => $tableName,
 409                                                                 'CMD[showExt]' => $extKey,
 410                                                         ), $additionalLinkParameter)
 411                                                 )) .
 412                                                 '" title="' .
 413                                                 sprintf($GLOBALS['LANG']->sL('LLL:EXT:em/language/locallang.xml:extBackup_dump_table'),
 414                                                         $tableName) .
 415                                                 '">' . $tableName . '</a></td><td>&nbsp;&nbsp;&nbsp;</td><td>' .
 416                                                 sprintf($GLOBALS['LANG']->sL('LLL:EXT:em/language/locallang.xml:extBackup_number_of_records'),
 417                                                         $count) . '</td></tr>';
 418                         } else {
 419                                 $tablesNA[$tableName] = '<tr><td>&nbsp;</td><td>' . $tableName . '</td><td>&nbsp;</td><td>' .
 420                                                 $GLOBALS['LANG']->sL('LLL:EXT:em/language/locallang.xml:extBackup_table_not_there') . '</td></tr>';
 421                         }
 422                 }
 423                 $label = '<table border="0" cellpadding="0" cellspacing="0">' .
 424                                 implode('', array_merge($tables, $tablesNA)) .
 425                                 '</table>';
 426                 if (count($tables)) {
 427                         $label = '<a class="t3-link dumpLink" href="' .
 428                                         htmlspecialchars(t3lib_div::linkThisScript(
 429                                                 array_merge(array(
 430                                                         'CMD[dumpTables]' => implode(',', array_keys($tables)),
 431                                                         'CMD[showExt]' => $extKey
 432                                                 ), $additionalLinkParameter)
 433                                         )) .
 434                                         '" title="' . $GLOBALS['LANG']->sL('LLL:EXT:em/language/locallang.xml:extBackup_dump_all_tables') . '">' .
 435                                         $GLOBALS['LANG']->sL('LLL:EXT:em/language/locallang.xml:extBackup_download_all_data') . '</a><br /><br />' . $label;
 436                 }
 437                 else {
 438                         $label = $GLOBALS['LANG']->sL('LLL:EXT:em/language/locallang.xml:extBackup_nothing_to_dump') . '<br /><br />' . $label;
 439                 }
 440                 return $label;
 441         }
 442
 443         /**
 444          * Dump content for static tables
 445          *
 446          * @param       string          Comma list of tables from which to dump content
 447          * @return      string          Returns the content
 448          * @see dumpTableAndFieldStructure()
 449          */
 450         function dumpStaticTables($tableList) {
 451                         /** @var $instObj t3lib_install_Sql */
 452                 $instObj = t3lib_div::makeInstance('t3lib_install_Sql');
 453                 $dbFields = $instObj->getFieldDefinitions_database(TYPO3_db);
 454
 455                 $out = '';
 456                 $parts = t3lib_div::trimExplode(',', $tableList, TRUE);
 457
 458                 // Traverse the table list and dump each:
 459                 foreach ($parts as $table) {
 460                         if (is_array($dbFields[$table]['fields'])) {
 461                                 $header = self::dumpHeader();
 462                                 $tableHeader = self::dumpTableHeader($table, $dbFields[$table], TRUE);
 463                                 $insertStatements = self::dumpTableContent($table, $dbFields[$table]['fields']);
 464                                 $out .= $header . self::MULTI_LINEBREAKS .
 465                                                 $tableHeader . self::MULTI_LINEBREAKS .
 466                                                 $insertStatements . self::MULTI_LINEBREAKS;
 467                         } else {
 468                                 throw new RuntimeException(
 469                                         'TYPO3 Fatal Error: ' . $GLOBALS['LANG']->getLL('dumpStaticTables_table_not_found'),
 470                                         1270853983
 471                                 );
 472                         }
 473                 }
 474                 unset($instObj);
 475                 return $out;
 476         }
 477
 478         /**
 479          * Header comments of the SQL dump file
 480          *
 481          * @return      string          Table header
 482          */
 483         function dumpHeader() {
 484                 return trim('
 485 # TYPO3 Extension Manager dump 1.1
 486 #
 487 # Host: ' . TYPO3_db_host . '    Database: ' . TYPO3_db . '
 488 #--------------------------------------------------------
 489 ');
 490         }
 491
 492         /**
 493          * Dump CREATE TABLE definition
 494          *
 495          * @param       string          Table name
 496          * @param       array           Field and key information (as provided from Install Tool class!)
 497          * @param       boolean         If TRUE, add "DROP TABLE IF EXISTS"
 498          * @return      string          Table definition SQL
 499          */
 500         function dumpTableHeader($table, $fieldKeyInfo, $dropTableIfExists = 0) {
 501                 $lines = array();
 502                 $dump = '';
 503
 504                 // Create field definitions
 505                 if (is_array($fieldKeyInfo['fields'])) {
 506                         foreach ($fieldKeyInfo['fields'] as $fieldN => $data) {
 507                                 $lines[] = '  ' . $fieldN . ' ' . $data;
 508                         }
 509                 }
 510
 511                 // Create index key definitions
 512                 if (is_array($fieldKeyInfo['keys'])) {
 513                         foreach ($fieldKeyInfo['keys'] as $fieldN => $data) {
 514                                 $lines[] = '  ' . $data;
 515                         }
 516                 }
 517
 518                 // Compile final output:
 519                 if (count($lines)) {
 520                         $dump = trim('
 521 #
 522 # Table structure for table "' . $table . '"
 523 #
 524 ' . ($dropTableIfExists ? 'DROP TABLE IF EXISTS ' . $table . ';
 525 ' : '') . 'CREATE TABLE ' . $table . ' (
 526 ' . implode(',' . LF, $lines) . '
 527 );');
 528                 }
 529
 530                 return $dump;
 531         }
 532
 533 }
 534
 535 ?>