[BUGFIX] Untrusted GP data is unserialized in wizard_colorpicker.php and view_help.php
[Packages/TYPO3.CMS.git] / t3lib / class.t3lib_install_sql.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2011 Christian Kuhn <lolli@schwarzbu.ch>
6 * All rights reserved
7 *
8 * This script is part of the TYPO3 project. The TYPO3 project is
9 * free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * The GNU General Public License can be found at
15 * http://www.gnu.org/copyleft/gpl.html.
16 * A copy is found in the textfile GPL.txt and important notices to the license
17 * from the author is found in LICENSE.txt distributed with these scripts.
18 *
19 *
20 * This script is distributed in the hope that it will be useful,
21 * but WITHOUT ANY WARRANTY; without even the implied warranty of
22 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 * GNU General Public License for more details.
24 *
25 * This copyright notice MUST APPEAR in all copies of the script!
26 ***************************************************************/
27
28 /**
29 * Verify TYPO3 DB table structure. ainly used in install tool
30 * compare wizard and extension manager.
31 *
32 * @author Christian Kuhn <lolli@schwarzbu.ch>
33 * @package TYPO3
34 * @subpackage t3lib
35 */
36 class t3lib_install_Sql {
37
38 /**
39 * @var string Prefix of deleted tables
40 */
41 protected $deletedPrefixKey = 'zzz_deleted_'; // Prefix used for tables/fields when deleted/renamed.
42
43 /**
44 * @var array Tables starting with this name are ignored during compare
45 */
46 protected $ignoreTablePrefixes = array(
47 'cachingframework_',
48 );
49
50 /**
51 * @var float|int Multiplier of SQL field size (for char, varchar and text fields)
52 */
53 protected $multiplySize = 1;
54
55 /**
56 * @var array Caching output of $GLOBALS['TYPO3_DB']->admin_get_charsets()
57 */
58 protected $character_sets = array();
59
60 /**
61 * Constructor function
62 */
63 public function __construct() {
64 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['multiplyDBfieldSize'] >= 1 && $GLOBALS['TYPO3_CONF_VARS']['SYS']['multiplyDBfieldSize'] <= 5) {
65 $this->multiplySize = (double) $GLOBALS['TYPO3_CONF_VARS']['SYS']['multiplyDBfieldSize'];
66 }
67 }
68
69 /**
70 * Set prefix of deleted tables
71 *
72 * @param string $prefix Prefix string
73 */
74 public function setDeletedPrefixKey($prefix) {
75 $this->deletedPrefixKey = $prefix;
76 }
77
78 /**
79 * Get prefix of deleted tables
80 *
81 * @return string
82 */
83 public function getDeletedPrefixKey() {
84 return $this->deletedPrefixKey;
85 }
86
87 /**
88 * Reads the field definitions for the input SQL-file string
89 *
90 * @param string $fileContent Should be a string read from an SQL-file made with 'mysqldump [database_name] -d'
91 * @return array Array with information about table.
92 */
93 public function getFieldDefinitions_fileContent($fileContent) {
94 $lines = t3lib_div::trimExplode(LF, $fileContent, 1);
95 $table = '';
96 $total = array();
97
98 foreach ($lines as $value) {
99 if (substr($value, 0, 1) == '#') {
100 continue; // Ignore comments
101 }
102
103 if (!strlen($table)) {
104 $parts = t3lib_div::trimExplode(' ', $value, TRUE);
105 if (strtoupper($parts[0]) === 'CREATE' && strtoupper($parts[1]) === 'TABLE') {
106 $table = str_replace('`', '', $parts[2]);
107 if (TYPO3_OS == 'WIN') { // tablenames are always lowercase on windows!
108 $table = strtolower($table);
109 }
110 }
111 } else {
112 if (substr($value, 0, 1) == ')' && substr($value, -1) == ';') {
113 $ttype = array();
114 if (preg_match('/(ENGINE|TYPE)[ ]*=[ ]*([a-zA-Z]*)/', $value, $ttype)) {
115 $total[$table]['extra']['ENGINE'] = $ttype[2];
116 } // Otherwise, just do nothing: If table engine is not defined, just accept the system default.
117
118 // Set the collation, if specified
119 if (preg_match('/(COLLATE)[ ]*=[ ]*([a-zA-z0-9_-]+)/', $value, $tcollation)) {
120 $total[$table]['extra']['COLLATE'] = $tcollation[2];
121 } else {
122 // Otherwise, get the CHARACTER SET and try to find the default collation for it as returned by "SHOW CHARACTER SET" query (for details, see http://dev.mysql.com/doc/refman/5.1/en/charset-table.html)
123 if (preg_match('/(CHARSET|CHARACTER SET)[ ]*=[ ]*([a-zA-z0-9_-]+)/', $value, $tcharset)) { // Note: Keywords "DEFAULT CHARSET" and "CHARSET" are the same, so "DEFAULT" can just be ignored
124 $charset = $tcharset[2];
125 } else {
126 $charset = $GLOBALS['TYPO3_DB']->default_charset; // Fallback to default charset
127 }
128 $total[$table]['extra']['COLLATE'] = $this->getCollationForCharset($charset);
129 }
130
131 $table = ''; // Remove table marker and start looking for the next "CREATE TABLE" statement
132 } else {
133 $lineV = preg_replace('/,$/', '', $value); // Strip trailing commas
134 $lineV = str_replace('`', '', $lineV);
135 $lineV = str_replace(' ', ' ', $lineV); // Remove double blanks
136
137 $parts = explode(' ', $lineV, 2);
138 if (!preg_match('/(PRIMARY|UNIQUE|FULLTEXT|INDEX|KEY)/', $parts[0])) { // Field definition
139
140 // Make sure there is no default value when auto_increment is set
141 if (stristr($parts[1], 'auto_increment')) {
142 $parts[1] = preg_replace('/ default \'0\'/i', '', $parts[1]);
143 }
144 // "default" is always lower-case
145 if (stristr($parts[1], ' DEFAULT ')) {
146 $parts[1] = str_ireplace(' DEFAULT ', ' default ', $parts[1]);
147 }
148
149 // Change order of "default" and "NULL" statements
150 $parts[1] = preg_replace('/(.*) (default .*) (NOT NULL)/', '$1 $3 $2', $parts[1]);
151 $parts[1] = preg_replace('/(.*) (default .*) (NULL)/', '$1 $3 $2', $parts[1]);
152
153 $key = $parts[0];
154 $total[$table]['fields'][$key] = $parts[1];
155
156 } else { // Key definition
157 $search = array('/UNIQUE (INDEX|KEY)/', '/FULLTEXT (INDEX|KEY)/', '/INDEX/');
158 $replace = array('UNIQUE', 'FULLTEXT', 'KEY');
159 $lineV = preg_replace($search, $replace, $lineV);
160
161 if (preg_match('/PRIMARY|UNIQUE|FULLTEXT/', $parts[0])) {
162 $parts[1] = preg_replace('/^(KEY|INDEX) /', '', $parts[1]);
163 }
164
165 $newParts = explode(' ', $parts[1], 2);
166 $key = $parts[0] == 'PRIMARY' ? $parts[0] : $newParts[0];
167
168 $total[$table]['keys'][$key] = $lineV;
169
170 // This is a protection against doing something stupid: Only allow clearing of cache_* and index_* tables.
171 if (preg_match('/^(cache|index)_/', $table)) {
172 // Suggest to truncate (clear) this table
173 $total[$table]['extra']['CLEAR'] = 1;
174 }
175 }
176 }
177 }
178 }
179
180 $this->getFieldDefinitions_sqlContent_parseTypes($total);
181 return $total;
182 }
183
184 /**
185 * Multiplies varchars/tinytext fields in size according to $this->multiplySize
186 * Useful if you want to use UTF-8 in the database and needs to extend the field sizes in the database so UTF-8 chars are not discarded. For most charsets available as single byte sets, multiplication with 2 should be enough. For chinese, use 3.
187 *
188 * @param array $total Total array (from getFieldDefinitions_fileContent())
189 * @return void
190 * @access private
191 * @see getFieldDefinitions_fileContent()
192 */
193 protected function getFieldDefinitions_sqlContent_parseTypes(&$total) {
194
195 $mSize = (double) $this->multiplySize;
196 if ($mSize > 1) {
197
198 /** @var $sqlParser t3lib_sqlparser */
199 $sqlParser = t3lib_div::makeInstance('t3lib_sqlparser');
200 foreach ($total as $table => $cfg) {
201 if (is_array($cfg['fields'])) {
202 foreach ($cfg['fields'] as $fN => $fType) {
203 $orig_fType = $fType;
204 $fInfo = $sqlParser->parseFieldDef($fType);
205
206 switch ($fInfo['fieldType']) {
207 case 'char':
208 case 'varchar':
209 $newSize = round($fInfo['value'] * $mSize);
210
211 if ($newSize <= 255) {
212 $fInfo['value'] = $newSize;
213 } else {
214 $fInfo = array(
215 'fieldType' => 'text',
216 'featureIndex' => array(
217 'NOTNULL' => array(
218 'keyword' => 'NOT NULL'
219 )
220 )
221 );
222 // Change key definition if necessary (must use "prefix" on TEXT columns)
223 if (is_array($cfg['keys'])) {
224 foreach ($cfg['keys'] as $kN => $kType) {
225 $match = array();
226 preg_match('/^([^(]*)\(([^)]+)\)(.*)/', $kType, $match);
227 $keys = array();
228 foreach (t3lib_div::trimExplode(',', $match[2]) as $kfN) {
229 if ($fN == $kfN) {
230 $kfN .= '(' . $newSize . ')';
231 }
232 $keys[] = $kfN;
233 }
234 $total[$table]['keys'][$kN] = $match[1] . '(' . implode(',', $keys) . ')' . $match[3];
235 }
236 }
237 }
238 break;
239 case 'tinytext':
240 $fInfo['fieldType'] = 'text';
241 break;
242 }
243
244 $total[$table]['fields'][$fN] = $sqlParser->compileFieldCfg($fInfo);
245 if ($sqlParser->parse_error) {
246 throw new RuntimeException(
247 'TYPO3 Fatal Error: ' . $sqlParser->parse_error,
248 1270853961
249 );
250 }
251 }
252 }
253 }
254 }
255 }
256
257 /**
258 * Look up the default collation for specified character set based on "SHOW CHARACTER SET" output
259 *
260 * @param string $charset Character set
261 * @return string Corresponding default collation
262 */
263 public function getCollationForCharset($charset) {
264 // Load character sets, if not cached already
265 if (!count($this->character_sets)) {
266 if (method_exists($GLOBALS['TYPO3_DB'], 'admin_get_charsets')) {
267 $this->character_sets = $GLOBALS['TYPO3_DB']->admin_get_charsets();
268 } else {
269 $this->character_sets[$charset] = array(); // Add empty element to avoid that the check will be repeated
270 }
271 }
272
273 $collation = '';
274 if (isset($this->character_sets[$charset]['Default collation'])) {
275 $collation = $this->character_sets[$charset]['Default collation'];
276 }
277
278 return $collation;
279 }
280
281 /**
282 * Reads the field definitions for the current database
283 *
284 * @return array Array with information about table.
285 */
286 public function getFieldDefinitions_database() {
287 $total = array();
288 $tempKeys = array();
289 $tempKeysPrefix = array();
290
291 $GLOBALS['TYPO3_DB']->sql_select_db(TYPO3_db);
292 echo $GLOBALS['TYPO3_DB']->sql_error();
293
294 $tables = $GLOBALS['TYPO3_DB']->admin_get_tables(TYPO3_db);
295 foreach ($tables as $tableName => $tableStatus) {
296
297 // Fields:
298 $fieldInformation = $GLOBALS['TYPO3_DB']->admin_get_fields($tableName);
299 foreach ($fieldInformation as $fN => $fieldRow) {
300 $total[$tableName]['fields'][$fN] = $this->assembleFieldDefinition($fieldRow);
301 }
302
303 // Keys:
304 $keyInformation = $GLOBALS['TYPO3_DB']->admin_get_keys($tableName);
305
306 foreach ($keyInformation as $keyRow) {
307 $keyName = $keyRow['Key_name'];
308 $colName = $keyRow['Column_name'];
309 if ($keyRow['Sub_part']) {
310 $colName .= '(' . $keyRow['Sub_part'] . ')';
311 }
312 $tempKeys[$tableName][$keyName][$keyRow['Seq_in_index']] = $colName;
313 if ($keyName == 'PRIMARY') {
314 $prefix = 'PRIMARY KEY';
315 } else {
316 if ($keyRow['Index_type'] == 'FULLTEXT') {
317 $prefix = 'FULLTEXT';
318 } elseif ($keyRow['Non_unique']) {
319 $prefix = 'KEY';
320 } else {
321 $prefix = 'UNIQUE';
322 }
323 $prefix .= ' ' . $keyName;
324 }
325 $tempKeysPrefix[$tableName][$keyName] = $prefix;
326 }
327
328 // Table status (storage engine, collaction, etc.)
329 if (is_array($tableStatus)) {
330 $tableExtraFields = array(
331 'Engine' => 'ENGINE',
332 'Collation' => 'COLLATE',
333 );
334
335 foreach ($tableExtraFields as $mysqlKey => $internalKey) {
336 if (isset($tableStatus[$mysqlKey])) {
337 $total[$tableName]['extra'][$internalKey] = $tableStatus[$mysqlKey];
338 }
339 }
340 }
341 }
342
343 // Compile key information:
344 if (count($tempKeys)) {
345 foreach ($tempKeys as $table => $keyInf) {
346 foreach ($keyInf as $kName => $index) {
347 ksort($index);
348 $total[$table]['keys'][$kName] = $tempKeysPrefix[$table][$kName] . ' (' . implode(',', $index) . ')';
349 }
350 }
351 }
352
353 return $total;
354 }
355
356 /**
357 * Compares two arrays with field information and returns information about fields that are MISSING and fields that have CHANGED.
358 * FDsrc and FDcomp can be switched if you want the list of stuff to remove rather than update.
359 *
360 * @param array $FDsrc Field definitions, source (from getFieldDefinitions_fileContent())
361 * @param array $FDcomp Field definitions, comparison. (from getFieldDefinitions_database())
362 * @param string $onlyTableList Table names (in list) which is the ONLY one observed.
363 * @param boolean $ignoreNotNullWhenComparing If set, this function ignores NOT NULL statements of the SQL file field definition when comparing current field definition from database with field definition from SQL file. This way, NOT NULL statements will be executed when the field is initially created, but the SQL parser will never complain about missing NOT NULL statements afterwards.
364 * @return array Returns an array with 1) all elements from $FDsrc that is not in $FDcomp (in key 'extra') and 2) all elements from $FDsrc that is different from the ones in $FDcomp
365 */
366 public function getDatabaseExtra($FDsrc, $FDcomp, $onlyTableList = '', $ignoreNotNullWhenComparing = TRUE) {
367 $extraArr = array();
368 $diffArr = array();
369
370 if (is_array($FDsrc)) {
371 foreach ($FDsrc as $table => $info) {
372 if (!strlen($onlyTableList) || t3lib_div::inList($onlyTableList, $table)) {
373 if (!isset($FDcomp[$table])) {
374 $extraArr[$table] = $info; // If the table was not in the FDcomp-array, the result array is loaded with that table.
375 $extraArr[$table]['whole_table'] = 1;
376 } else {
377 $keyTypes = explode(',', 'extra,fields,keys');
378 foreach ($keyTypes as $theKey) {
379 if (is_array($info[$theKey])) {
380 foreach ($info[$theKey] as $fieldN => $fieldC) {
381 $fieldN = str_replace('`', '', $fieldN);
382 if ($fieldN == 'COLLATE') {
383 continue; // TODO: collation support is currently disabled (needs more testing)
384 }
385
386 if (!isset($FDcomp[$table][$theKey][$fieldN])) {
387 $extraArr[$table][$theKey][$fieldN] = $fieldC;
388 } else {
389 $fieldC = trim($fieldC);
390 if ($ignoreNotNullWhenComparing) {
391 $fieldC = str_replace(' NOT NULL', '', $fieldC);
392 $FDcomp[$table][$theKey][$fieldN] = str_replace(' NOT NULL', '', $FDcomp[$table][$theKey][$fieldN]);
393 }
394 if ($fieldC !== $FDcomp[$table][$theKey][$fieldN]) {
395 $diffArr[$table][$theKey][$fieldN] = $fieldC;
396 $diffArr_cur[$table][$theKey][$fieldN] = $FDcomp[$table][$theKey][$fieldN];
397 }
398 }
399 }
400 }
401 }
402 }
403 }
404 }
405 }
406
407 $output = array(
408 'extra' => $extraArr,
409 'diff' => $diffArr,
410 'diff_currentValues' => $diffArr_cur
411 );
412
413 return $output;
414 }
415
416 /**
417 * Returns an array with SQL-statements that is needed to update according to the diff-array
418 *
419 * @param array $diffArr Array with differences of current and needed DB settings. (from getDatabaseExtra())
420 * @param string $keyList List of fields in diff array to take notice of.
421 * @return array Array of SQL statements (organized in keys depending on type)
422 */
423 public function getUpdateSuggestions($diffArr, $keyList = 'extra,diff') {
424 $statements = array();
425 $deletedPrefixKey = $this->deletedPrefixKey;
426 $remove = 0;
427 if ($keyList == 'remove') {
428 $remove = 1;
429 $keyList = 'extra';
430 }
431 $keyList = explode(',', $keyList);
432 foreach ($keyList as $theKey) {
433 if (is_array($diffArr[$theKey])) {
434 foreach ($diffArr[$theKey] as $table => $info) {
435 $whole_table = array();
436 if (is_array($info['fields'])) {
437 foreach ($info['fields'] as $fN => $fV) {
438 if ($info['whole_table']) {
439 $whole_table[] = $fN . ' ' . $fV;
440 } else {
441 // Special case to work around MySQL problems when adding auto_increment fields:
442 if (stristr($fV, 'auto_increment')) {
443 // The field can only be set "auto_increment" if there exists a PRIMARY key of that field already.
444 // The check does not look up which field is primary but just assumes it must be the field with the auto_increment value...
445 if (isset($diffArr['extra'][$table]['keys']['PRIMARY'])) {
446 // Remove "auto_increment" from the statement - it will be suggested in a 2nd step after the primary key was created
447 $fV = str_replace(' auto_increment', '', $fV);
448 } else {
449 // In the next step, attempt to clear the table once again (2 = force)
450 $info['extra']['CLEAR'] = 2;
451 }
452 }
453 if ($theKey == 'extra') {
454 if ($remove) {
455 if (substr($fN, 0, strlen($deletedPrefixKey)) != $deletedPrefixKey) {
456 $statement = 'ALTER TABLE ' . $table . ' CHANGE ' . $fN . ' ' . $deletedPrefixKey . $fN . ' ' . $fV . ';';
457 $statements['change'][md5($statement)] = $statement;
458 } else {
459 $statement = 'ALTER TABLE ' . $table . ' DROP ' . $fN . ';';
460 $statements['drop'][md5($statement)] = $statement;
461 }
462 } else {
463 $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fN . ' ' . $fV . ';';
464 $statements['add'][md5($statement)] = $statement;
465 }
466 } elseif ($theKey == 'diff') {
467 $statement = 'ALTER TABLE ' . $table . ' CHANGE ' . $fN . ' ' . $fN . ' ' . $fV . ';';
468 $statements['change'][md5($statement)] = $statement;
469 $statements['change_currentValue'][md5($statement)] = $diffArr['diff_currentValues'][$table]['fields'][$fN];
470 }
471 }
472 }
473 }
474 if (is_array($info['keys'])) {
475 foreach ($info['keys'] as $fN => $fV) {
476 if ($info['whole_table']) {
477 $whole_table[] = $fV;
478 } else {
479 if ($theKey == 'extra') {
480 if ($remove) {
481 $statement = 'ALTER TABLE ' . $table . ($fN == 'PRIMARY' ? ' DROP PRIMARY KEY' : ' DROP KEY ' . $fN) . ';';
482 $statements['drop'][md5($statement)] = $statement;
483 } else {
484 $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fV . ';';
485 $statements['add'][md5($statement)] = $statement;
486 }
487 } elseif ($theKey == 'diff') {
488 $statement = 'ALTER TABLE ' . $table . ($fN == 'PRIMARY' ? ' DROP PRIMARY KEY' : ' DROP KEY ' . $fN) . ';';
489 $statements['change'][md5($statement)] = $statement;
490 $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fV . ';';
491 $statements['change'][md5($statement)] = $statement;
492 }
493 }
494 }
495 }
496 if (is_array($info['extra'])) {
497 $extras = array();
498 $extras_currentValue = array();
499 $clear_table = FALSE;
500
501 foreach ($info['extra'] as $fN => $fV) {
502
503 // Only consider statements which are missing in the database but don't remove existing properties
504 if (!$remove) {
505 if (!$info['whole_table']) { // If the whole table is created at once, we take care of this later by imploding all elements of $info['extra']
506 if ($fN == 'CLEAR') {
507 // Truncate table must happen later, not now
508 // Valid values for CLEAR: 1=only clear if keys are missing, 2=clear anyway (force)
509 if (count($info['keys']) || $fV == 2) {
510 $clear_table = TRUE;
511 }
512 continue;
513 } else {
514 $extras[] = $fN . '=' . $fV;
515 $extras_currentValue[] = $fN . '=' . $diffArr['diff_currentValues'][$table]['extra'][$fN];
516 }
517 }
518 }
519 }
520 if ($clear_table) {
521 $statement = 'TRUNCATE TABLE ' . $table . ';';
522 $statements['clear_table'][md5($statement)] = $statement;
523 }
524 if (count($extras)) {
525 $statement = 'ALTER TABLE ' . $table . ' ' . implode(' ', $extras) . ';';
526 $statements['change'][md5($statement)] = $statement;
527 $statements['change_currentValue'][md5($statement)] = implode(' ', $extras_currentValue);
528 }
529 }
530 if ($info['whole_table']) {
531 if ($remove) {
532 if (substr($table, 0, strlen($deletedPrefixKey)) != $deletedPrefixKey) {
533 // Ignore table operation if the table name starts with a
534 // prefix that should be ignored
535 $ignoreTable = FALSE;
536 foreach ($this->ignoreTablePrefixes as $ignorePrefix) {
537 if (substr($table, 0, strlen($ignorePrefix)) === $ignorePrefix) {
538 $ignoreTable = TRUE;
539 break;
540 }
541 }
542 if (!$ignoreTable) {
543 $statement = 'ALTER TABLE ' . $table . ' RENAME ' . $deletedPrefixKey . $table . ';';
544 $statements['change_table'][md5($statement)] = $statement;
545 }
546 } else {
547 $statement = 'DROP TABLE ' . $table . ';';
548 $statements['drop_table'][md5($statement)] = $statement;
549 }
550 // count:
551 $count = $GLOBALS['TYPO3_DB']->exec_SELECTcountRows('*', $table);
552 $statements['tables_count'][md5($statement)] = $count ? 'Records in table: ' . $count : '';
553 } else {
554 $statement = 'CREATE TABLE ' . $table . " (\n" . implode(",\n", $whole_table) . "\n)";
555 if ($info['extra']) {
556 foreach ($info['extra'] as $k => $v) {
557 if ($k == 'COLLATE' || $k == 'CLEAR') {
558 continue; // Skip these special statements. TODO: collation support is currently disabled (needs more testing)
559 }
560 $statement .= ' ' . $k . '=' . $v; // Add extra attributes like ENGINE, CHARSET, etc.
561 }
562 }
563 $statement .= ';';
564 $statements['create_table'][md5($statement)] = $statement;
565 }
566 }
567 }
568 }
569 }
570
571 return $statements;
572 }
573
574 /**
575 * Converts a result row with field information into the SQL field definition string
576 *
577 * @param array $row MySQL result row
578 * @return string Field definition
579 */
580 public function assembleFieldDefinition($row) {
581 $field = array($row['Type']);
582
583 if ($row['Null'] == 'NO') {
584 $field[] = 'NOT NULL';
585 }
586 if (!strstr($row['Type'], 'blob') && !strstr($row['Type'], 'text')) {
587 // Add a default value if the field is not auto-incremented (these fields never have a default definition)
588 if (!stristr($row['Extra'], 'auto_increment')) {
589 $field[] = 'default \'' . addslashes($row['Default']) . '\'';
590 }
591 }
592 if ($row['Extra']) {
593 $field[] = $row['Extra'];
594 }
595
596 return implode(' ', $field);
597 }
598
599 /**
600 * Returns an array where every entry is a single SQL-statement. Input must be formatted like an ordinary MySQL-dump files.
601 *
602 * @param string $sqlcode The SQL-file content. Provided that 1) every query in the input is ended with ';' and that a line in the file contains only one query or a part of a query.
603 * @param boolean $removeNonSQL If set, non-SQL content (like comments and blank lines) is not included in the final output
604 * @param string $query_regex Regex to filter SQL lines to include
605 * @return array Array of SQL statements
606 */
607 public function getStatementArray($sqlcode, $removeNonSQL = FALSE, $query_regex = '') {
608 $sqlcodeArr = explode(LF, $sqlcode);
609
610 // Based on the assumption that the sql-dump has
611 $statementArray = array();
612 $statementArrayPointer = 0;
613
614 foreach ($sqlcodeArr as $line => $lineContent) {
615 $is_set = 0;
616
617 // auto_increment fields cannot have a default value!
618 if (stristr($lineContent, 'auto_increment')) {
619 $lineContent = preg_replace('/ default \'0\'/i', '', $lineContent);
620 }
621
622 if (!$removeNonSQL || (strcmp(trim($lineContent), '') && substr(trim($lineContent), 0, 1) != '#' && substr(trim($lineContent), 0, 2) != '--')) { // '--' is seen as mysqldump comments from server version 3.23.49
623 $statementArray[$statementArrayPointer] .= $lineContent;
624 $is_set = 1;
625 }
626 if (substr(trim($lineContent), -1) == ';') {
627 if (isset($statementArray[$statementArrayPointer])) {
628 if (!trim($statementArray[$statementArrayPointer]) || ($query_regex && !preg_match('/' . $query_regex . '/i', trim($statementArray[$statementArrayPointer])))) {
629 unset($statementArray[$statementArrayPointer]);
630 }
631 }
632 $statementArrayPointer++;
633
634 } elseif ($is_set) {
635 $statementArray[$statementArrayPointer] .= LF;
636 }
637 }
638
639 return $statementArray;
640 }
641
642 /**
643 * Returns tables to create and how many records in each
644 *
645 * @param array $statements Array of SQL statements to analyse.
646 * @param boolean $insertCountFlag If set, will count number of INSERT INTO statements following that table definition
647 * @return array Array with table definitions in index 0 and count in index 1
648 */
649 public function getCreateTables($statements, $insertCountFlag = FALSE) {
650 $crTables = array();
651 $insertCount = array();
652 foreach ($statements as $line => $lineContent) {
653 $reg = array();
654 if (preg_match('/^create[[:space:]]*table[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {
655 $table = trim($reg[1]);
656 if ($table) {
657 // table names are always lowercase on Windows!
658 if (TYPO3_OS == 'WIN') {
659 $table = strtolower($table);
660 }
661 $sqlLines = explode(LF, $lineContent);
662 foreach ($sqlLines as $k => $v) {
663 if (stristr($v, 'auto_increment')) {
664 $sqlLines[$k] = preg_replace('/ default \'0\'/i', '', $v);
665 }
666 }
667 $lineContent = implode(LF, $sqlLines);
668 $crTables[$table] = $lineContent;
669 }
670 } elseif ($insertCountFlag && preg_match('/^insert[[:space:]]*into[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {
671 $nTable = trim($reg[1]);
672 $insertCount[$nTable]++;
673 }
674 }
675
676 return array($crTables, $insertCount);
677 }
678
679 /**
680 * Extracts all insert statements from $statement array where content is inserted into $table
681 *
682 * @param array $statements Array of SQL statements
683 * @param string $table Table name
684 * @return array Array of INSERT INTO statements where table match $table
685 */
686 public function getTableInsertStatements($statements, $table) {
687 $outStatements = array();
688 foreach ($statements as $line => $lineContent) {
689 $reg = array();
690 if (preg_match('/^insert[[:space:]]*into[[:space:]]*[`]?([[:alnum:]_]*)[`]?/i', substr($lineContent, 0, 100), $reg)) {
691 $nTable = trim($reg[1]);
692 if ($nTable && !strcmp($table, $nTable)) {
693 $outStatements[] = $lineContent;
694 }
695 }
696 }
697 return $outStatements;
698 }
699
700 /**
701 * Performs the queries passed from the input array.
702 *
703 * @param array $arr Array of SQL queries to execute.
704 * @param array $keyArr Array with keys that must match keys in $arr. Only where a key in this array is set and TRUE will the query be executed (meant to be passed from a form checkbox)
705 * @return mixed Array with error message from database if any occured. Otherwise TRUE if everything was executed successfully.
706 */
707 public function performUpdateQueries($arr, $keyArr) {
708 $result = array();
709 if (is_array($arr)) {
710 foreach ($arr as $key => $string) {
711 if (isset($keyArr[$key]) && $keyArr[$key]) {
712 $res = $GLOBALS['TYPO3_DB']->admin_query($string);
713 if ($res === FALSE) {
714 $result[$key] = $GLOBALS['TYPO3_DB']->sql_error();
715 } elseif (is_resource($res)) {
716 $GLOBALS['TYPO3_DB']->sql_free_result($res);
717 }
718 }
719 }
720 }
721 if (count($result) > 0) {
722 return $result;
723 } else {
724 return TRUE;
725 }
726 }
727
728 /**
729 * Returns list of tables in the database
730 *
731 * @return array List of tables.
732 * @see t3lib_db::admin_get_tables()
733 */
734 public function getListOfTables() {
735 $whichTables = $GLOBALS['TYPO3_DB']->admin_get_tables(TYPO3_db);
736 foreach ($whichTables as $key => &$value) {
737 $value = $key;
738 }
739 unset($value);
740 return $whichTables;
741 }
742 }
743
744 if (defined('TYPO3_MODE') && isset($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_install_sql.php'])) {
745 include_once($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_install_sql.php']);
746 }
747
748 ?>