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