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 |
postgres.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 postgres extends tools
021 {
022 /**
023 * Get the column types for postgres only
024 *
025 * @return array
026 */
027 static public function get_dbms_type_map()
028 {
029 return array(
030 'postgres' => array(
031 'INT:' => 'INT4',
032 'BINT' => 'INT8',
033 'ULINT' => 'INT4', // unsigned
034 'UINT' => 'INT4', // unsigned
035 'UINT:' => 'INT4', // unsigned
036 'USINT' => 'INT2', // unsigned
037 'BOOL' => 'INT2', // unsigned
038 'TINT:' => 'INT2',
039 'VCHAR' => 'varchar(255)',
040 'VCHAR:' => 'varchar(%d)',
041 'CHAR:' => 'char(%d)',
042 'XSTEXT' => 'varchar(1000)',
043 'STEXT' => 'varchar(3000)',
044 'TEXT' => 'varchar(8000)',
045 'MTEXT' => 'TEXT',
046 'XSTEXT_UNI'=> 'varchar(100)',
047 'STEXT_UNI' => 'varchar(255)',
048 'TEXT_UNI' => 'varchar(4000)',
049 'MTEXT_UNI' => 'TEXT',
050 'TIMESTAMP' => 'INT4', // unsigned
051 'DECIMAL' => 'decimal(5,2)',
052 'DECIMAL:' => 'decimal(%d,2)',
053 'PDECIMAL' => 'decimal(6,3)',
054 'PDECIMAL:' => 'decimal(%d,3)',
055 'VCHAR_UNI' => 'varchar(255)',
056 'VCHAR_UNI:'=> 'varchar(%d)',
057 'VCHAR_CI' => 'varchar_ci',
058 'VARBINARY' => 'bytea',
059 ),
060 );
061 }
062
063 /**
064 * Constructor. Set DB Object and set {@link $return_statements return_statements}.
065 *
066 * @param \phpbb\db\driver\driver_interface $db Database connection
067 * @param bool $return_statements True if only statements should be returned and no SQL being executed
068 */
069 public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false)
070 {
071 parent::__construct($db, $return_statements);
072
073 // Determine mapping database type
074 $this->sql_layer = 'postgres';
075
076 $this->dbms_type_map = self::get_dbms_type_map();
077 }
078
079 /**
080 * {@inheritDoc}
081 */
082 function sql_list_tables()
083 {
084 $sql = 'SELECT relname
085 FROM pg_stat_user_tables';
086 $result = $this->db->sql_query($sql);
087
088 $tables = array();
089 while ($row = $this->db->sql_fetchrow($result))
090 {
091 $name = current($row);
092 $tables[$name] = $name;
093 }
094 $this->db->sql_freeresult($result);
095
096 return $tables;
097 }
098
099 /**
100 * {@inheritDoc}
101 */
102 function sql_table_exists($table_name)
103 {
104 $sql = "SELECT CAST(EXISTS(
105 SELECT * FROM information_schema.tables
106 WHERE table_schema = 'public'
107 AND table_name = '" . $this->db->sql_escape($table_name) . "'
108 ) AS INTEGER)";
109 $result = $this->db->sql_query_limit($sql, 1);
110 $row = $this->db->sql_fetchrow($result);
111 $table_exists = (booL) $row['exists'];
112 $this->db->sql_freeresult($result);
113
114 return $table_exists;
115 }
116
117 /**
118 * {@inheritDoc}
119 */
120 function sql_create_table($table_name, $table_data)
121 {
122 // holds the DDL for a column
123 $columns = $statements = array();
124
125 if ($this->sql_table_exists($table_name))
126 {
127 return $this->_sql_run_sql($statements);
128 }
129
130 // Begin transaction
131 $statements[] = 'begin';
132
133 // Determine if we have created a PRIMARY KEY in the earliest
134 $primary_key_gen = false;
135
136 // Determine if the table requires a sequence
137 $create_sequence = false;
138
139 // Begin table sql statement
140 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n";
141
142 // Iterate through the columns to create a table
143 foreach ($table_data['COLUMNS'] as $column_name => $column_data)
144 {
145 // here lies an array, filled with information compiled on the column's data
146 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
147
148 if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "${column_name}_gen"
149 {
150 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);
151 }
152
153 // here we add the definition of the new column to the list of columns
154 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql'];
155
156 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking
157 if (!$primary_key_gen)
158 {
159 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
160 }
161
162 // create sequence DDL based off of the existence of auto incrementing columns
163 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
164 {
165 $create_sequence = $column_name;
166 }
167 }
168
169 // this makes up all the columns in the create table statement
170 $table_sql .= implode(",\n", $columns);
171
172 // we have yet to create a primary key for this table,
173 // this means that we can add the one we really wanted instead
174 if (!$primary_key_gen)
175 {
176 // Write primary key
177 if (isset($table_data['PRIMARY_KEY']))
178 {
179 if (!is_array($table_data['PRIMARY_KEY']))
180 {
181 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
182 }
183
184 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
185 }
186 }
187
188 // do we need to add a sequence for auto incrementing columns?
189 if ($create_sequence)
190 {
191 $statements[] = "CREATE SEQUENCE {$table_name}_seq;";
192 }
193
194 // close the table
195 $table_sql .= "\n);";
196 $statements[] = $table_sql;
197
198 // Write Keys
199 if (isset($table_data['KEYS']))
200 {
201 foreach ($table_data['KEYS'] as $key_name => $key_data)
202 {
203 if (!is_array($key_data[1]))
204 {
205 $key_data[1] = array($key_data[1]);
206 }
207
208 $old_return_statements = $this->return_statements;
209 $this->return_statements = true;
210
211 $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]);
212
213 foreach ($key_stmts as $key_stmt)
214 {
215 $statements[] = $key_stmt;
216 }
217
218 $this->return_statements = $old_return_statements;
219 }
220 }
221
222 // Commit Transaction
223 $statements[] = 'commit';
224
225 return $this->_sql_run_sql($statements);
226 }
227
228 /**
229 * {@inheritDoc}
230 */
231 function sql_list_columns($table_name)
232 {
233 $columns = array();
234
235 $sql = "SELECT a.attname
236 FROM pg_class c, pg_attribute a
237 WHERE c.relname = '{$table_name}'
238 AND a.attnum > 0
239 AND a.attrelid = c.oid";
240 $result = $this->db->sql_query($sql);
241
242 while ($row = $this->db->sql_fetchrow($result))
243 {
244 $column = strtolower(current($row));
245 $columns[$column] = $column;
246 }
247 $this->db->sql_freeresult($result);
248
249 return $columns;
250 }
251
252 /**
253 * {@inheritDoc}
254 */
255 function sql_index_exists($table_name, $index_name)
256 {
257 $sql = "SELECT ic.relname as index_name
258 FROM pg_class bc, pg_class ic, pg_index i
259 WHERE (bc.oid = i.indrelid)
260 AND (ic.oid = i.indexrelid)
261 AND (bc.relname = '" . $table_name . "')
262 AND (i.indisunique != 't')
263 AND (i.indisprimary != 't')";
264 $result = $this->db->sql_query($sql);
265
266 while ($row = $this->db->sql_fetchrow($result))
267 {
268 // This DBMS prefixes index names with the table name
269 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']);
270
271 if (strtolower($row['index_name']) == strtolower($index_name))
272 {
273 $this->db->sql_freeresult($result);
274 return true;
275 }
276 }
277 $this->db->sql_freeresult($result);
278
279 return false;
280 }
281
282 /**
283 * {@inheritDoc}
284 */
285 function sql_unique_index_exists($table_name, $index_name)
286 {
287 $sql = "SELECT ic.relname as index_name, i.indisunique
288 FROM pg_class bc, pg_class ic, pg_index i
289 WHERE (bc.oid = i.indrelid)
290 AND (ic.oid = i.indexrelid)
291 AND (bc.relname = '" . $table_name . "')
292 AND (i.indisprimary != 't')";
293 $result = $this->db->sql_query($sql);
294
295 while ($row = $this->db->sql_fetchrow($result))
296 {
297 if ($row['indisunique'] != 't')
298 {
299 continue;
300 }
301
302 // This DBMS prefixes index names with the table name
303 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']);
304
305 if (strtolower($row['index_name']) == strtolower($index_name))
306 {
307 $this->db->sql_freeresult($result);
308 return true;
309 }
310 }
311 $this->db->sql_freeresult($result);
312
313 return false;
314 }
315
316 /**
317 * Function to prepare some column information for better usage
318 * @access private
319 */
320 function sql_prepare_column_data($table_name, $column_name, $column_data)
321 {
322 if (strlen($column_name) > 30)
323 {
324 trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
325 }
326
327 // Get type
328 list($column_type, $orig_column_type) = $this->get_column_type($column_data[0]);
329
330 // Adjust default value if db-dependent specified
331 if (is_array($column_data[1]))
332 {
333 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
334 }
335
336 $sql = " {$column_type} ";
337
338 $return_array = array(
339 'column_type' => $column_type,
340 'auto_increment' => false,
341 );
342
343 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
344 {
345 $default_val = "nextval('{$table_name}_seq')";
346 $return_array['auto_increment'] = true;
347 }
348 else if (!is_null($column_data[1]))
349 {
350 $default_val = "'" . $column_data[1] . "'";
351 $return_array['null'] = 'NOT NULL';
352 $sql .= 'NOT NULL ';
353 }
354 else
355 {
356 // Integers need to have 0 instead of empty string as default
357 if (strpos($column_type, 'INT') === 0)
358 {
359 $default_val = '0';
360 }
361 else
362 {
363 $default_val = "'" . $column_data[1] . "'";
364 }
365 $return_array['null'] = 'NULL';
366 $sql .= 'NULL ';
367 }
368
369 $return_array['default'] = $default_val;
370
371 $sql .= "DEFAULT {$default_val}";
372
373 // Unsigned? Then add a CHECK contraint
374 if (in_array($orig_column_type, $this->unsigned_types))
375 {
376 $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
377 $sql .= " CHECK ({$column_name} >= 0)";
378 }
379
380 $return_array['column_type_sql'] = $sql;
381
382 return $return_array;
383 }
384
385 /**
386 * {@inheritDoc}
387 */
388 function sql_column_add($table_name, $column_name, $column_data, $inline = false)
389 {
390 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
391 $statements = array();
392
393 // Does not support AFTER, only through temporary table
394 if (version_compare($this->db->sql_server_info(true), '8.0', '>='))
395 {
396 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
397 }
398 else
399 {
400 // old versions cannot add columns with default and null information
401 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type'] . ' ' . $column_data['constraint'];
402
403 if (isset($column_data['null']))
404 {
405 if ($column_data['null'] == 'NOT NULL')
406 {
407 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET NOT NULL';
408 }
409 }
410
411 if (isset($column_data['default']))
412 {
413 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
414 }
415 }
416
417 return $this->_sql_run_sql($statements);
418 }
419
420 /**
421 * {@inheritDoc}
422 */
423 function sql_column_remove($table_name, $column_name, $inline = false)
424 {
425 $statements = array();
426
427 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
428
429 return $this->_sql_run_sql($statements);
430 }
431
432 /**
433 * {@inheritDoc}
434 */
435 function sql_index_drop($table_name, $index_name)
436 {
437 $statements = array();
438
439 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
440
441 return $this->_sql_run_sql($statements);
442 }
443
444 /**
445 * {@inheritDoc}
446 */
447 function sql_table_drop($table_name)
448 {
449 $statements = array();
450
451 if (!$this->sql_table_exists($table_name))
452 {
453 return $this->_sql_run_sql($statements);
454 }
455
456 // the most basic operation, get rid of the table
457 $statements[] = 'DROP TABLE ' . $table_name;
458
459 // PGSQL does not "tightly" bind sequences and tables, we must guess...
460 $sql = "SELECT relname
461 FROM pg_class
462 WHERE relkind = 'S'
463 AND relname = '{$table_name}_seq'";
464 $result = $this->db->sql_query($sql);
465
466 // We don't even care about storing the results. We already know the answer if we get rows back.
467 if ($this->db->sql_fetchrow($result))
468 {
469 $statements[] = "DROP SEQUENCE IF EXISTS {$table_name}_seq;\n";
470 }
471 $this->db->sql_freeresult($result);
472
473 return $this->_sql_run_sql($statements);
474 }
475
476 /**
477 * {@inheritDoc}
478 */
479 function sql_create_primary_key($table_name, $column, $inline = false)
480 {
481 $statements = array();
482
483 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
484
485 return $this->_sql_run_sql($statements);
486 }
487
488 /**
489 * {@inheritDoc}
490 */
491 function sql_create_unique_index($table_name, $index_name, $column)
492 {
493 $statements = array();
494
495 $this->check_index_name_length($table_name, $index_name);
496
497 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
498
499 return $this->_sql_run_sql($statements);
500 }
501
502 /**
503 * {@inheritDoc}
504 */
505 function sql_create_index($table_name, $index_name, $column)
506 {
507 $statements = array();
508
509 $this->check_index_name_length($table_name, $index_name);
510
511 // remove index length
512 $column = preg_replace('#:.*$#', '', $column);
513
514 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
515
516 return $this->_sql_run_sql($statements);
517 }
518
519
520 /**
521 * {@inheritDoc}
522 */
523 function sql_list_index($table_name)
524 {
525 $index_array = array();
526
527 $sql = "SELECT ic.relname as index_name
528 FROM pg_class bc, pg_class ic, pg_index i
529 WHERE (bc.oid = i.indrelid)
530 AND (ic.oid = i.indexrelid)
531 AND (bc.relname = '" . $table_name . "')
532 AND (i.indisunique != 't')
533 AND (i.indisprimary != 't')";
534 $result = $this->db->sql_query($sql);
535
536 while ($row = $this->db->sql_fetchrow($result))
537 {
538 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']);
539
540 $index_array[] = $row['index_name'];
541 }
542 $this->db->sql_freeresult($result);
543
544 return array_map('strtolower', $index_array);
545 }
546
547 /**
548 * {@inheritDoc}
549 */
550 function sql_column_change($table_name, $column_name, $column_data, $inline = false)
551 {
552 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
553 $statements = array();
554
555 $sql = 'ALTER TABLE ' . $table_name . ' ';
556
557 $sql_array = array();
558 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
559
560 if (isset($column_data['null']))
561 {
562 if ($column_data['null'] == 'NOT NULL')
563 {
564 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
565 }
566 else if ($column_data['null'] == 'NULL')
567 {
568 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
569 }
570 }
571
572 if (isset($column_data['default']))
573 {
574 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
575 }
576
577 // we don't want to double up on constraints if we change different number data types
578 if (isset($column_data['constraint']))
579 {
580 $constraint_sql = "SELECT pg_get_constraintdef(pc.oid) AS constraint_data
581 FROM pg_constraint pc, pg_class bc
582 WHERE conrelid = bc.oid
583 AND bc.relname = '" . $this->db->sql_escape($table_name) . "'
584 AND NOT EXISTS (
585 SELECT *
586 FROM pg_constraint AS c, pg_inherits AS i
587 WHERE i.inhrelid = pc.conrelid
588 AND c.conname = pc.conname
589 AND pg_get_constraintdef(c.oid) = pg_get_constraintdef(pc.oid)
590 AND c.conrelid = i.inhparent
591 )";
592
593 $constraint_exists = false;
594
595 $result = $this->db->sql_query($constraint_sql);
596 while ($row = $this->db->sql_fetchrow($result))
597 {
598 if (trim($row['constraint_data']) == trim($column_data['constraint']))
599 {
600 $constraint_exists = true;
601 break;
602 }
603 }
604 $this->db->sql_freeresult($result);
605
606 if (!$constraint_exists)
607 {
608 $sql_array[] = 'ADD ' . $column_data['constraint'];
609 }
610 }
611
612 $sql .= implode(', ', $sql_array);
613
614 $statements[] = $sql;
615
616 return $this->_sql_run_sql($statements);
617 }
618
619 /**
620 * Get a list with existing indexes for the column
621 *
622 * @param string $table_name
623 * @param string $column_name
624 * @param bool $unique Should we get unique indexes or normal ones
625 * @return array Array with Index name => columns
626 */
627 public function get_existing_indexes($table_name, $column_name, $unique = false)
628 {
629 // Not supported
630 throw new \Exception('DBMS is not supported');
631 }
632 }
633