Verzeichnisstruktur phpBB-3.3.15
- Veröffentlicht
- 28.08.2024
So funktioniert es
|
|
Auf das letzte Element klicken. Dies geht jeweils ein Schritt zurück |
Auf das Icon klicken, dies öffnet das Verzeichnis. Nochmal klicken schließt das Verzeichnis. |
|
|
(Beispiel Datei-Icons)
|
Auf das Icon klicken um den Quellcode anzuzeigen |
mssql.php
001 <?php
002 /**
003 *
004 * This file is part of the phpBB Forum Software package.
005 *
006 * @copyright (c) phpBB Limited <https://www.phpbb.com>
007 * @license GNU General Public License, version 2 (GPL-2.0)
008 *
009 * For full copyright and license information, please see
010 * the docs/CREDITS.txt file.
011 *
012 */
013
014 namespace phpbb\db\tools;
015
016 /**
017 * Database Tools for handling cross-db actions such as altering columns, etc.
018 * Currently not supported is returning SQL for creating tables.
019 */
020 class mssql extends tools
021 {
022 /**
023 * Is the used MS SQL Server a SQL Server 2000?
024 * @var bool
025 */
026 protected $is_sql_server_2000;
027
028 /**
029 * Get the column types for mssql based databases
030 *
031 * @return array
032 */
033 static public function get_dbms_type_map()
034 {
035 return array(
036 'mssql' => array(
037 'INT:' => '[int]',
038 'BINT' => '[float]',
039 'ULINT' => '[int]',
040 'UINT' => '[int]',
041 'UINT:' => '[int]',
042 'TINT:' => '[int]',
043 'USINT' => '[int]',
044 'BOOL' => '[int]',
045 'VCHAR' => '[varchar] (255)',
046 'VCHAR:' => '[varchar] (%d)',
047 'CHAR:' => '[char] (%d)',
048 'XSTEXT' => '[varchar] (1000)',
049 'STEXT' => '[varchar] (3000)',
050 'TEXT' => '[varchar] (8000)',
051 'MTEXT' => '[text]',
052 'XSTEXT_UNI'=> '[nvarchar] (100)',
053 'STEXT_UNI' => '[nvarchar] (255)',
054 'TEXT_UNI' => '[nvarchar] (4000)',
055 'MTEXT_UNI' => '[ntext]',
056 'TIMESTAMP' => '[int]',
057 'DECIMAL' => '[float]',
058 'DECIMAL:' => '[float]',
059 'PDECIMAL' => '[float]',
060 'PDECIMAL:' => '[float]',
061 'VCHAR_UNI' => '[nvarchar] (255)',
062 'VCHAR_UNI:'=> '[nvarchar] (%d)',
063 'VCHAR_CI' => '[nvarchar] (255)',
064 'VARBINARY' => '[varchar] (255)',
065 ),
066
067 'mssqlnative' => array(
068 'INT:' => '[int]',
069 'BINT' => '[float]',
070 'ULINT' => '[int]',
071 'UINT' => '[int]',
072 'UINT:' => '[int]',
073 'TINT:' => '[int]',
074 'USINT' => '[int]',
075 'BOOL' => '[int]',
076 'VCHAR' => '[varchar] (255)',
077 'VCHAR:' => '[varchar] (%d)',
078 'CHAR:' => '[char] (%d)',
079 'XSTEXT' => '[varchar] (1000)',
080 'STEXT' => '[varchar] (3000)',
081 'TEXT' => '[varchar] (8000)',
082 'MTEXT' => '[text]',
083 'XSTEXT_UNI'=> '[nvarchar] (100)',
084 'STEXT_UNI' => '[nvarchar] (255)',
085 'TEXT_UNI' => '[nvarchar] (4000)',
086 'MTEXT_UNI' => '[ntext]',
087 'TIMESTAMP' => '[int]',
088 'DECIMAL' => '[float]',
089 'DECIMAL:' => '[float]',
090 'PDECIMAL' => '[float]',
091 'PDECIMAL:' => '[float]',
092 'VCHAR_UNI' => '[nvarchar] (255)',
093 'VCHAR_UNI:'=> '[nvarchar] (%d)',
094 'VCHAR_CI' => '[nvarchar] (255)',
095 'VARBINARY' => '[varchar] (255)',
096 ),
097 );
098 }
099
100 /**
101 * Constructor. Set DB Object and set {@link $return_statements return_statements}.
102 *
103 * @param \phpbb\db\driver\driver_interface $db Database connection
104 * @param bool $return_statements True if only statements should be returned and no SQL being executed
105 */
106 public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false)
107 {
108 parent::__construct($db, $return_statements);
109
110 // Determine mapping database type
111 switch ($this->db->get_sql_layer())
112 {
113 case 'mssql_odbc':
114 $this->sql_layer = 'mssql';
115 break;
116
117 case 'mssqlnative':
118 $this->sql_layer = 'mssqlnative';
119 break;
120 }
121
122 $this->dbms_type_map = self::get_dbms_type_map();
123 }
124
125 /**
126 * {@inheritDoc}
127 */
128 function sql_list_tables()
129 {
130 $sql = "SELECT name
131 FROM sysobjects
132 WHERE type='U'";
133 $result = $this->db->sql_query($sql);
134
135 $tables = array();
136 while ($row = $this->db->sql_fetchrow($result))
137 {
138 $name = current($row);
139 $tables[$name] = $name;
140 }
141 $this->db->sql_freeresult($result);
142
143 return $tables;
144 }
145
146 /**
147 * {@inheritDoc}
148 */
149 function sql_create_table($table_name, $table_data)
150 {
151 // holds the DDL for a column
152 $columns = $statements = array();
153
154 if ($this->sql_table_exists($table_name))
155 {
156 return $this->_sql_run_sql($statements);
157 }
158
159 // Begin transaction
160 $statements[] = 'begin';
161
162 // Determine if we have created a PRIMARY KEY in the earliest
163 $primary_key_gen = false;
164
165 // Determine if the table requires a sequence
166 $create_sequence = false;
167
168 // Begin table sql statement
169 $table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n";
170
171 if (!isset($table_data['PRIMARY_KEY']))
172 {
173 $table_data['COLUMNS']['mssqlindex'] = array('UINT', null, 'auto_increment');
174 $table_data['PRIMARY_KEY'] = 'mssqlindex';
175 }
176
177 // Iterate through the columns to create a table
178 foreach ($table_data['COLUMNS'] as $column_name => $column_data)
179 {
180 // here lies an array, filled with information compiled on the column's data
181 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
182
183 if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "${column_name}_gen"
184 {
185 trigger_error("Index name '{$column_name}_gen' on table '$table_name' is too long. The maximum auto increment column length is 26 characters.", E_USER_ERROR);
186 }
187
188 // here we add the definition of the new column to the list of columns
189 $columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default'];
190
191 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking
192 if (!$primary_key_gen)
193 {
194 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
195 }
196
197 // create sequence DDL based off of the existence of auto incrementing columns
198 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
199 {
200 $create_sequence = $column_name;
201 }
202 }
203
204 // this makes up all the columns in the create table statement
205 $table_sql .= implode(",\n", $columns);
206
207 // Close the table for two DBMS and add to the statements
208 $table_sql .= "\n);";
209 $statements[] = $table_sql;
210
211 // we have yet to create a primary key for this table,
212 // this means that we can add the one we really wanted instead
213 if (!$primary_key_gen)
214 {
215 // Write primary key
216 if (isset($table_data['PRIMARY_KEY']))
217 {
218 if (!is_array($table_data['PRIMARY_KEY']))
219 {
220 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
221 }
222
223 // We need the data here
224 $old_return_statements = $this->return_statements;
225 $this->return_statements = true;
226
227 $primary_key_stmts = $this->sql_create_primary_key($table_name, $table_data['PRIMARY_KEY']);
228 foreach ($primary_key_stmts as $pk_stmt)
229 {
230 $statements[] = $pk_stmt;
231 }
232
233 $this->return_statements = $old_return_statements;
234 }
235 }
236
237 // Write Keys
238 if (isset($table_data['KEYS']))
239 {
240 foreach ($table_data['KEYS'] as $key_name => $key_data)
241 {
242 if (!is_array($key_data[1]))
243 {
244 $key_data[1] = array($key_data[1]);
245 }
246
247 $old_return_statements = $this->return_statements;
248 $this->return_statements = true;
249
250 $key_stmts = ($key_data[0] == 'UNIQUE') ? $this->sql_create_unique_index($table_name, $key_name, $key_data[1]) : $this->sql_create_index($table_name, $key_name, $key_data[1]);
251
252 foreach ($key_stmts as $key_stmt)
253 {
254 $statements[] = $key_stmt;
255 }
256
257 $this->return_statements = $old_return_statements;
258 }
259 }
260
261 // Commit Transaction
262 $statements[] = 'commit';
263
264 return $this->_sql_run_sql($statements);
265 }
266
267 /**
268 * {@inheritDoc}
269 */
270 function sql_list_columns($table_name)
271 {
272 $columns = array();
273
274 $sql = "SELECT c.name
275 FROM syscolumns c
276 LEFT JOIN sysobjects o ON c.id = o.id
277 WHERE o.name = '{$table_name}'";
278 $result = $this->db->sql_query($sql);
279
280 while ($row = $this->db->sql_fetchrow($result))
281 {
282 $column = strtolower(current($row));
283 $columns[$column] = $column;
284 }
285 $this->db->sql_freeresult($result);
286
287 return $columns;
288 }
289
290 /**
291 * {@inheritDoc}
292 */
293 function sql_index_exists($table_name, $index_name)
294 {
295 $sql = "EXEC sp_statistics '$table_name'";
296 $result = $this->db->sql_query($sql);
297
298 while ($row = $this->db->sql_fetchrow($result))
299 {
300 if ($row['TYPE'] == 3)
301 {
302 if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
303 {
304 $this->db->sql_freeresult($result);
305 return true;
306 }
307 }
308 }
309 $this->db->sql_freeresult($result);
310
311 return false;
312 }
313
314 /**
315 * {@inheritDoc}
316 */
317 function sql_unique_index_exists($table_name, $index_name)
318 {
319 $sql = "EXEC sp_statistics '$table_name'";
320 $result = $this->db->sql_query($sql);
321
322 while ($row = $this->db->sql_fetchrow($result))
323 {
324 // Usually NON_UNIQUE is the column we want to check, but we allow for both
325 if ($row['TYPE'] == 3)
326 {
327 if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
328 {
329 $this->db->sql_freeresult($result);
330 return true;
331 }
332 }
333 }
334 $this->db->sql_freeresult($result);
335
336 return false;
337 }
338
339 /**
340 * {@inheritDoc}
341 */
342 function sql_prepare_column_data($table_name, $column_name, $column_data)
343 {
344 if (strlen($column_name) > 30)
345 {
346 trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
347 }
348
349 // Get type
350 list($column_type, ) = $this->get_column_type($column_data[0]);
351
352 // Adjust default value if db-dependent specified
353 if (is_array($column_data[1]))
354 {
355 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
356 }
357
358 $sql = '';
359
360 $return_array = array();
361
362 $sql .= " {$column_type} ";
363 $sql_default = " {$column_type} ";
364
365 // For adding columns we need the default definition
366 if (!is_null($column_data[1]))
367 {
368 // For hexadecimal values do not use single quotes
369 if (strpos($column_data[1], '0x') === 0)
370 {
371 $return_array['default'] = 'DEFAULT (' . $column_data[1] . ') ';
372 $sql_default .= $return_array['default'];
373 }
374 else
375 {
376 $return_array['default'] = 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
377 $sql_default .= $return_array['default'];
378 }
379 }
380
381 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
382 {
383 // $sql .= 'IDENTITY (1, 1) ';
384 $sql_default .= 'IDENTITY (1, 1) ';
385 }
386
387 $return_array['textimage'] = $column_type === '[text]';
388
389 if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment'))
390 {
391 $sql .= 'NOT NULL';
392 $sql_default .= 'NOT NULL';
393 }
394 else
395 {
396 $sql .= 'NULL';
397 $sql_default .= 'NULL';
398 }
399
400 $return_array['column_type_sql_default'] = $sql_default;
401
402 $return_array['column_type_sql'] = $sql;
403
404 return $return_array;
405 }
406
407 /**
408 * {@inheritDoc}
409 */
410 function sql_column_add($table_name, $column_name, $column_data, $inline = false)
411 {
412 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
413 $statements = array();
414
415 // Does not support AFTER, only through temporary table
416 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
417
418 return $this->_sql_run_sql($statements);
419 }
420
421 /**
422 * {@inheritDoc}
423 */
424 function sql_column_remove($table_name, $column_name, $inline = false)
425 {
426 $statements = array();
427
428 // We need the data here
429 $old_return_statements = $this->return_statements;
430 $this->return_statements = true;
431
432 $indexes = $this->get_existing_indexes($table_name, $column_name);
433 $indexes = array_merge($indexes, $this->get_existing_indexes($table_name, $column_name, true));
434
435 // Drop any indexes
436 $recreate_indexes = array();
437 if (!empty($indexes))
438 {
439 foreach ($indexes as $index_name => $index_data)
440 {
441 $result = $this->sql_index_drop($table_name, $index_name);
442 $statements = array_merge($statements, $result);
443 if (count($index_data) > 1)
444 {
445 // Remove this column from the index and recreate it
446 $recreate_indexes[$index_name] = array_diff($index_data, array($column_name));
447 }
448 }
449 }
450
451 // Drop primary keys depending on this column
452 $result = $this->mssql_get_drop_default_primary_key_queries($table_name, $column_name);
453 $statements = array_merge($statements, $result);
454
455 // Drop default value constraint
456 $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
457 $statements = array_merge($statements, $result);
458
459 // Remove the column
460 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
461
462 if (!empty($recreate_indexes))
463 {
464 // Recreate indexes after we removed the column
465 foreach ($recreate_indexes as $index_name => $index_data)
466 {
467 $result = $this->sql_create_index($table_name, $index_name, $index_data);
468 $statements = array_merge($statements, $result);
469 }
470 }
471
472 $this->return_statements = $old_return_statements;
473
474 return $this->_sql_run_sql($statements);
475 }
476
477 /**
478 * {@inheritDoc}
479 */
480 function sql_index_drop($table_name, $index_name)
481 {
482 $statements = array();
483
484 $statements[] = 'DROP INDEX [' . $table_name . '].[' . $index_name . ']';
485
486 return $this->_sql_run_sql($statements);
487 }
488
489 /**
490 * {@inheritDoc}
491 */
492 function sql_table_drop($table_name)
493 {
494 $statements = array();
495
496 if (!$this->sql_table_exists($table_name))
497 {
498 return $this->_sql_run_sql($statements);
499 }
500
501 // the most basic operation, get rid of the table
502 $statements[] = 'DROP TABLE ' . $table_name;
503
504 return $this->_sql_run_sql($statements);
505 }
506
507 /**
508 * {@inheritDoc}
509 */
510 function sql_create_primary_key($table_name, $column, $inline = false)
511 {
512 $statements = array();
513
514 $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
515 $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
516 $sql .= '[' . implode("],\n\t\t[", $column) . ']';
517 $sql .= ')';
518
519 $statements[] = $sql;
520
521 return $this->_sql_run_sql($statements);
522 }
523
524 /**
525 * {@inheritDoc}
526 */
527 function sql_create_unique_index($table_name, $index_name, $column)
528 {
529 $statements = array();
530
531 if ($this->mssql_is_sql_server_2000())
532 {
533 $this->check_index_name_length($table_name, $index_name);
534 }
535
536 $statements[] = 'CREATE UNIQUE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])';
537
538 return $this->_sql_run_sql($statements);
539 }
540
541 /**
542 * {@inheritDoc}
543 */
544 function sql_create_index($table_name, $index_name, $column)
545 {
546 $statements = array();
547
548 $this->check_index_name_length($table_name, $index_name);
549
550 // remove index length
551 $column = preg_replace('#:.*$#', '', $column);
552
553 $statements[] = 'CREATE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])';
554
555 return $this->_sql_run_sql($statements);
556 }
557
558 /**
559 * {@inheritdoc}
560 */
561 protected function get_max_index_name_length()
562 {
563 if ($this->mssql_is_sql_server_2000())
564 {
565 return parent::get_max_index_name_length();
566 }
567 else
568 {
569 return 128;
570 }
571 }
572
573 /**
574 * {@inheritDoc}
575 */
576 function sql_list_index($table_name)
577 {
578 $index_array = array();
579 $sql = "EXEC sp_statistics '$table_name'";
580 $result = $this->db->sql_query($sql);
581 while ($row = $this->db->sql_fetchrow($result))
582 {
583 if ($row['TYPE'] == 3)
584 {
585 $index_array[] = strtolower($row['INDEX_NAME']);
586 }
587 }
588 $this->db->sql_freeresult($result);
589
590 return $index_array;
591 }
592
593 /**
594 * {@inheritDoc}
595 */
596 function sql_column_change($table_name, $column_name, $column_data, $inline = false)
597 {
598 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
599 $statements = array();
600
601 // We need the data here
602 $old_return_statements = $this->return_statements;
603 $this->return_statements = true;
604
605 $indexes = $this->get_existing_indexes($table_name, $column_name);
606 $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true);
607
608 // Drop any indexes
609 if (!empty($indexes) || !empty($unique_indexes))
610 {
611 $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes));
612 foreach ($drop_indexes as $index_name)
613 {
614 $result = $this->sql_index_drop($table_name, $index_name);
615 $statements = array_merge($statements, $result);
616 }
617 }
618
619 // Drop default value constraint
620 $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
621 $statements = array_merge($statements, $result);
622
623 // Change the column
624 $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
625
626 if (!empty($column_data['default']) && !$this->mssql_is_column_identity($table_name, $column_name))
627 {
628 // Add new default value constraint
629 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD CONSTRAINT [DF_' . $table_name . '_' . $column_name . '_1] ' . $column_data['default'] . ' FOR [' . $column_name . ']';
630 }
631
632 if (!empty($indexes))
633 {
634 // Recreate indexes after we changed the column
635 foreach ($indexes as $index_name => $index_data)
636 {
637 $result = $this->sql_create_index($table_name, $index_name, $index_data);
638 $statements = array_merge($statements, $result);
639 }
640 }
641
642 if (!empty($unique_indexes))
643 {
644 // Recreate unique indexes after we changed the column
645 foreach ($unique_indexes as $index_name => $index_data)
646 {
647 $result = $this->sql_create_unique_index($table_name, $index_name, $index_data);
648 $statements = array_merge($statements, $result);
649 }
650 }
651
652 $this->return_statements = $old_return_statements;
653
654 return $this->_sql_run_sql($statements);
655 }
656
657 /**
658 * Get queries to drop the default constraints of a column
659 *
660 * We need to drop the default constraints of a column,
661 * before being able to change their type or deleting them.
662 *
663 * @param string $table_name
664 * @param string $column_name
665 * @return array Array with SQL statements
666 */
667 protected function mssql_get_drop_default_constraints_queries($table_name, $column_name)
668 {
669 $statements = array();
670 if ($this->mssql_is_sql_server_2000())
671 {
672 // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
673 // Deprecated in SQL Server 2005
674 $sql = "SELECT so.name AS def_name
675 FROM sysobjects so
676 JOIN sysconstraints sc ON so.id = sc.constid
677 WHERE object_name(so.parent_obj) = '{$table_name}'
678 AND so.xtype = 'D'
679 AND sc.colid = (SELECT colid FROM syscolumns
680 WHERE id = object_id('{$table_name}')
681 AND name = '{$column_name}')";
682 }
683 else
684 {
685 $sql = "SELECT dobj.name AS def_name
686 FROM sys.columns col
687 LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
688 WHERE col.object_id = object_id('{$table_name}')
689 AND col.name = '{$column_name}'
690 AND dobj.name IS NOT NULL";
691 }
692
693 $result = $this->db->sql_query($sql);
694 while ($row = $this->db->sql_fetchrow($result))
695 {
696 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
697 }
698 $this->db->sql_freeresult($result);
699
700 return $statements;
701 }
702
703 /**
704 * Get queries to drop the primary keys depending on the specified column
705 *
706 * We need to drop primary keys depending on this column before being able
707 * to delete them.
708 *
709 * @param string $table_name
710 * @param string $column_name
711 * @return array Array with SQL statements
712 */
713 protected function mssql_get_drop_default_primary_key_queries($table_name, $column_name)
714 {
715 $statements = array();
716
717 $sql = "SELECT ccu.CONSTRAINT_NAME, ccu.COLUMN_NAME
718 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
719 JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
720 WHERE tc.TABLE_NAME = '{$table_name}'
721 AND tc.CONSTRAINT_TYPE = 'Primary Key'
722 AND ccu.COLUMN_NAME = '{$column_name}'";
723
724 $result = $this->db->sql_query($sql);
725
726 while ($primary_key = $this->db->sql_fetchrow($result))
727 {
728 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $primary_key['CONSTRAINT_NAME'] . ']';
729 }
730 $this->db->sql_freeresult($result);
731
732 return $statements;
733 }
734
735 /**
736 * Checks to see if column is an identity column
737 *
738 * Identity columns cannot have defaults set for them.
739 *
740 * @param string $table_name
741 * @param string $column_name
742 * @return bool true if identity, false if not
743 */
744 protected function mssql_is_column_identity($table_name, $column_name)
745 {
746 if ($this->mssql_is_sql_server_2000())
747 {
748 // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
749 // Deprecated in SQL Server 2005
750 $sql = "SELECT COLUMNPROPERTY(object_id('{$table_name}'), '{$column_name}', 'IsIdentity') AS is_identity";
751 }
752 else
753 {
754 $sql = "SELECT is_identity FROM sys.columns
755 WHERE object_id = object_id('{$table_name}')
756 AND name = '{$column_name}'";
757 }
758
759 $result = $this->db->sql_query($sql);
760 $is_identity = $this->db->sql_fetchfield('is_identity');
761 $this->db->sql_freeresult($result);
762
763 return (bool) $is_identity;
764 }
765
766 /**
767 * Get a list with existing indexes for the column
768 *
769 * @param string $table_name
770 * @param string $column_name
771 * @param bool $unique Should we get unique indexes or normal ones
772 * @return array Array with Index name => columns
773 */
774 public function get_existing_indexes($table_name, $column_name, $unique = false)
775 {
776 $existing_indexes = array();
777 if ($this->mssql_is_sql_server_2000())
778 {
779 // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
780 // Deprecated in SQL Server 2005
781 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name
782 FROM sysindexes ix
783 INNER JOIN sysindexkeys ixc
784 ON ixc.id = ix.id
785 AND ixc.indid = ix.indid
786 INNER JOIN syscolumns cols
787 ON cols.colid = ixc.colid
788 AND cols.id = ix.id
789 WHERE ix.id = object_id('{$table_name}')
790 AND cols.name = '{$column_name}'
791 AND INDEXPROPERTY(ix.id, ix.name, 'IsUnique') = " . ($unique ? '1' : '0');
792 }
793 else
794 {
795 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name
796 FROM sys.indexes ix
797 INNER JOIN sys.index_columns ixc
798 ON ixc.object_id = ix.object_id
799 AND ixc.index_id = ix.index_id
800 INNER JOIN sys.columns cols
801 ON cols.column_id = ixc.column_id
802 AND cols.object_id = ix.object_id
803 WHERE ix.object_id = object_id('{$table_name}')
804 AND cols.name = '{$column_name}'
805 AND ix.is_primary_key = 0
806 AND ix.is_unique = " . ($unique ? '1' : '0');
807 }
808
809 $result = $this->db->sql_query($sql);
810 while ($row = $this->db->sql_fetchrow($result))
811 {
812 if (!isset($row['is_unique']) || ($unique && $row['is_unique'] == 'UNIQUE') || (!$unique && $row['is_unique'] == 'NONUNIQUE'))
813 {
814 $existing_indexes[$row['phpbb_index_name']] = array();
815 }
816 }
817 $this->db->sql_freeresult($result);
818
819 if (empty($existing_indexes))
820 {
821 return array();
822 }
823
824 if ($this->mssql_is_sql_server_2000())
825 {
826 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
827 FROM sysindexes ix
828 INNER JOIN sysindexkeys ixc
829 ON ixc.id = ix.id
830 AND ixc.indid = ix.indid
831 INNER JOIN syscolumns cols
832 ON cols.colid = ixc.colid
833 AND cols.id = ix.id
834 WHERE ix.id = object_id('{$table_name}')
835 AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
836 }
837 else
838 {
839 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
840 FROM sys.indexes ix
841 INNER JOIN sys.index_columns ixc
842 ON ixc.object_id = ix.object_id
843 AND ixc.index_id = ix.index_id
844 INNER JOIN sys.columns cols
845 ON cols.column_id = ixc.column_id
846 AND cols.object_id = ix.object_id
847 WHERE ix.object_id = object_id('{$table_name}')
848 AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
849 }
850
851 $result = $this->db->sql_query($sql);
852 while ($row = $this->db->sql_fetchrow($result))
853 {
854 $existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
855 }
856 $this->db->sql_freeresult($result);
857
858 return $existing_indexes;
859 }
860
861 /**
862 * Is the used MS SQL Server a SQL Server 2000?
863 *
864 * @return bool
865 */
866 protected function mssql_is_sql_server_2000()
867 {
868 if ($this->is_sql_server_2000 === null)
869 {
870 $sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
871 $result = $this->db->sql_query($sql);
872 $properties = $this->db->sql_fetchrow($result);
873 $this->db->sql_freeresult($result);
874 $this->is_sql_server_2000 = $properties['mssql_version'][0] == '8';
875 }
876
877 return $this->is_sql_server_2000;
878 }
879
880 }
881