c1c50a891f61e7bbaf4672bcafac367ca8c5dd02
[Packages/TYPO3.CMS.git] / 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 ?>