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