Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 135 |
|
0.00% |
0 / 4 |
CRAP | |
0.00% |
0 / 1 |
| postgres_extractor | |
0.00% |
0 / 135 |
|
0.00% |
0 / 4 |
1560 | |
0.00% |
0 / 1 |
| write_start | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
6 | |||
| write_table | |
0.00% |
0 / 89 |
|
0.00% |
0 / 1 |
650 | |||
| write_data | |
0.00% |
0 / 33 |
|
0.00% |
0 / 1 |
110 | |||
| write_end | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
6 | |||
| 1 | <?php |
| 2 | /** |
| 3 | * |
| 4 | * This file is part of the phpBB Forum Software package. |
| 5 | * |
| 6 | * @copyright (c) phpBB Limited <https://www.phpbb.com> |
| 7 | * @license GNU General Public License, version 2 (GPL-2.0) |
| 8 | * |
| 9 | * For full copyright and license information, please see |
| 10 | * the docs/CREDITS.txt file. |
| 11 | * |
| 12 | */ |
| 13 | |
| 14 | namespace phpbb\db\extractor; |
| 15 | |
| 16 | use phpbb\db\extractor\exception\extractor_not_initialized_exception; |
| 17 | |
| 18 | class postgres_extractor extends base_extractor |
| 19 | { |
| 20 | /** |
| 21 | * {@inheritdoc} |
| 22 | */ |
| 23 | public function write_start($table_prefix) |
| 24 | { |
| 25 | if (!$this->is_initialized) |
| 26 | { |
| 27 | throw new extractor_not_initialized_exception(); |
| 28 | } |
| 29 | |
| 30 | $sql_data = "--\n"; |
| 31 | $sql_data .= "-- phpBB Backup Script\n"; |
| 32 | $sql_data .= "-- Dump of tables for $table_prefix\n"; |
| 33 | $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; |
| 34 | $sql_data .= "--\n"; |
| 35 | $sql_data .= "BEGIN TRANSACTION;\n"; |
| 36 | $this->flush($sql_data); |
| 37 | } |
| 38 | |
| 39 | /** |
| 40 | * {@inheritdoc} |
| 41 | */ |
| 42 | public function write_table($table_name) |
| 43 | { |
| 44 | static $domains_created = array(); |
| 45 | |
| 46 | if (!$this->is_initialized) |
| 47 | { |
| 48 | throw new extractor_not_initialized_exception(); |
| 49 | } |
| 50 | |
| 51 | $sql = "SELECT a.domain_name, a.data_type, a.character_maximum_length, a.domain_default |
| 52 | FROM INFORMATION_SCHEMA.domains a, INFORMATION_SCHEMA.column_domain_usage b |
| 53 | WHERE a.domain_name = b.domain_name |
| 54 | AND b.table_name = '{$table_name}'"; |
| 55 | $result = $this->db->sql_query($sql); |
| 56 | while ($row = $this->db->sql_fetchrow($result)) |
| 57 | { |
| 58 | if (empty($domains_created[$row['domain_name']])) |
| 59 | { |
| 60 | $domains_created[$row['domain_name']] = true; |
| 61 | //$sql_data = "DROP DOMAIN {$row['domain_name']};\n"; |
| 62 | $sql_data = "CREATE DOMAIN {$row['domain_name']} as {$row['data_type']}"; |
| 63 | if (!empty($row['character_maximum_length'])) |
| 64 | { |
| 65 | $sql_data .= '(' . $row['character_maximum_length'] . ')'; |
| 66 | } |
| 67 | $sql_data .= ' NOT NULL'; |
| 68 | if (!empty($row['domain_default'])) |
| 69 | { |
| 70 | $sql_data .= ' DEFAULT ' . $row['domain_default']; |
| 71 | } |
| 72 | $this->flush($sql_data . ";\n"); |
| 73 | } |
| 74 | } |
| 75 | $this->db->sql_freeresult($result); |
| 76 | |
| 77 | $sql_data = '-- Table: ' . $table_name . "\n"; |
| 78 | $sql_data .= "DROP TABLE $table_name;\n"; |
| 79 | // PGSQL does not "tightly" bind sequences and tables, we must guess... |
| 80 | $sql = "SELECT relname |
| 81 | FROM pg_class |
| 82 | WHERE relkind = 'S' |
| 83 | AND relname = '{$table_name}_seq'"; |
| 84 | $result = $this->db->sql_query($sql); |
| 85 | // We don't even care about storing the results. We already know the answer if we get rows back. |
| 86 | if ($this->db->sql_fetchrow($result)) |
| 87 | { |
| 88 | $sql_data .= "DROP SEQUENCE IF EXISTS {$table_name}_seq;\n"; |
| 89 | $sql_data .= "CREATE SEQUENCE {$table_name}_seq;\n"; |
| 90 | } |
| 91 | $this->db->sql_freeresult($result); |
| 92 | |
| 93 | $field_query = "SELECT a.attnum, a.attname as field, t.typname as type, a.attlen as length, a.atttypmod as lengthvar, a.attnotnull as notnull |
| 94 | FROM pg_class c, pg_attribute a, pg_type t |
| 95 | WHERE c.relname = '" . $this->db->sql_escape($table_name) . "' |
| 96 | AND a.attnum > 0 |
| 97 | AND a.attrelid = c.oid |
| 98 | AND a.atttypid = t.oid |
| 99 | ORDER BY a.attnum"; |
| 100 | $result = $this->db->sql_query($field_query); |
| 101 | |
| 102 | $sql_data .= "CREATE TABLE $table_name(\n"; |
| 103 | $lines = array(); |
| 104 | while ($row = $this->db->sql_fetchrow($result)) |
| 105 | { |
| 106 | // Get the data from the table |
| 107 | $sql_get_default = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault |
| 108 | FROM pg_attrdef d, pg_class c |
| 109 | WHERE (c.relname = '" . $this->db->sql_escape($table_name) . "') |
| 110 | AND (c.oid = d.adrelid) |
| 111 | AND d.adnum = " . $row['attnum']; |
| 112 | $def_res = $this->db->sql_query($sql_get_default); |
| 113 | $def_row = $this->db->sql_fetchrow($def_res); |
| 114 | $this->db->sql_freeresult($def_res); |
| 115 | |
| 116 | if (empty($def_row)) |
| 117 | { |
| 118 | unset($row['rowdefault']); |
| 119 | } |
| 120 | else |
| 121 | { |
| 122 | $row['rowdefault'] = $def_row['rowdefault']; |
| 123 | } |
| 124 | |
| 125 | if ($row['type'] == 'bpchar') |
| 126 | { |
| 127 | // Internally stored as bpchar, but isn't accepted in a CREATE TABLE statement. |
| 128 | $row['type'] = 'char'; |
| 129 | } |
| 130 | |
| 131 | $line = ' ' . $row['field'] . ' ' . $row['type']; |
| 132 | |
| 133 | if (strpos($row['type'], 'char') !== false) |
| 134 | { |
| 135 | if ($row['lengthvar'] > 0) |
| 136 | { |
| 137 | $line .= '(' . ($row['lengthvar'] - 4) . ')'; |
| 138 | } |
| 139 | } |
| 140 | |
| 141 | if (strpos($row['type'], 'numeric') !== false) |
| 142 | { |
| 143 | $line .= '('; |
| 144 | $line .= sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff)); |
| 145 | $line .= ')'; |
| 146 | } |
| 147 | |
| 148 | if (isset($row['rowdefault'])) |
| 149 | { |
| 150 | $line .= ' DEFAULT ' . $row['rowdefault']; |
| 151 | } |
| 152 | |
| 153 | if ($row['notnull'] == 't') |
| 154 | { |
| 155 | $line .= ' NOT NULL'; |
| 156 | } |
| 157 | |
| 158 | $lines[] = $line; |
| 159 | } |
| 160 | $this->db->sql_freeresult($result); |
| 161 | |
| 162 | // Get the listing of primary keys. |
| 163 | $sql_pri_keys = "SELECT ic.relname as index_name, bc.relname as tab_name, ta.attname as column_name, i.indisunique as unique_key, i.indisprimary as primary_key |
| 164 | FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia |
| 165 | WHERE (bc.oid = i.indrelid) |
| 166 | AND (ic.oid = i.indexrelid) |
| 167 | AND (ia.attrelid = i.indexrelid) |
| 168 | AND (ta.attrelid = bc.oid) |
| 169 | AND (bc.relname = '" . $this->db->sql_escape($table_name) . "') |
| 170 | AND (ta.attrelid = i.indrelid) |
| 171 | AND (ta.attnum = i.indkey[ia.attnum-1]) |
| 172 | ORDER BY index_name, tab_name, column_name"; |
| 173 | |
| 174 | $result = $this->db->sql_query($sql_pri_keys); |
| 175 | |
| 176 | $index_create = $index_rows = $primary_key = array(); |
| 177 | |
| 178 | // We do this in two steps. It makes placing the comma easier |
| 179 | while ($row = $this->db->sql_fetchrow($result)) |
| 180 | { |
| 181 | if ($row['primary_key'] == 't') |
| 182 | { |
| 183 | $primary_key[] = $row['column_name']; |
| 184 | $primary_key_name = $row['index_name']; |
| 185 | } |
| 186 | else |
| 187 | { |
| 188 | // We have to store this all this info because it is possible to have a multi-column key... |
| 189 | // we can loop through it again and build the statement |
| 190 | $index_rows[$row['index_name']]['table'] = $table_name; |
| 191 | $index_rows[$row['index_name']]['unique'] = ($row['unique_key'] == 't') ? true : false; |
| 192 | $index_rows[$row['index_name']]['column_names'][] = $row['column_name']; |
| 193 | } |
| 194 | } |
| 195 | $this->db->sql_freeresult($result); |
| 196 | |
| 197 | if (!empty($index_rows)) |
| 198 | { |
| 199 | foreach ($index_rows as $idx_name => $props) |
| 200 | { |
| 201 | $index_create[] = 'CREATE ' . ($props['unique'] ? 'UNIQUE ' : '') . "INDEX $idx_name ON $table_name (" . implode(', ', $props['column_names']) . ");"; |
| 202 | } |
| 203 | } |
| 204 | |
| 205 | if (!empty($primary_key)) |
| 206 | { |
| 207 | $lines[] = " CONSTRAINT $primary_key_name PRIMARY KEY (" . implode(', ', $primary_key) . ")"; |
| 208 | } |
| 209 | |
| 210 | // Generate constraint clauses for CHECK constraints |
| 211 | $sql_checks = "SELECT pc.conname AS index_name, pg_get_expr(pc.conbin, pc.conrelid) AS constraint_expr |
| 212 | FROM pg_constraint pc, pg_class bc |
| 213 | WHERE pc.conrelid = bc.oid |
| 214 | AND bc.relname = '" . $this->db->sql_escape($table_name) . "' |
| 215 | AND NOT EXISTS ( |
| 216 | SELECT * |
| 217 | FROM pg_constraint AS c, pg_inherits AS i |
| 218 | WHERE i.inhrelid = pc.conrelid |
| 219 | AND c.conname = pc.conname |
| 220 | AND pg_get_constraintdef(c.oid) = pg_get_constraintdef(pc.oid) |
| 221 | AND c.conrelid = i.inhparent |
| 222 | )"; |
| 223 | $result = $this->db->sql_query($sql_checks); |
| 224 | |
| 225 | // Add the constraints to the sql file. |
| 226 | while ($row = $this->db->sql_fetchrow($result)) |
| 227 | { |
| 228 | if (!empty($row['constraint_expr'])) |
| 229 | { |
| 230 | $lines[] = ' CONSTRAINT ' . $row['index_name'] . ' CHECK ' . $row['constraint_expr']; |
| 231 | } |
| 232 | } |
| 233 | $this->db->sql_freeresult($result); |
| 234 | |
| 235 | $sql_data .= implode(", \n", $lines); |
| 236 | $sql_data .= "\n);\n"; |
| 237 | |
| 238 | if (!empty($index_create)) |
| 239 | { |
| 240 | $sql_data .= implode("\n", $index_create) . "\n\n"; |
| 241 | } |
| 242 | $this->flush($sql_data); |
| 243 | } |
| 244 | |
| 245 | /** |
| 246 | * {@inheritdoc} |
| 247 | */ |
| 248 | public function write_data($table_name) |
| 249 | { |
| 250 | if (!$this->is_initialized) |
| 251 | { |
| 252 | throw new extractor_not_initialized_exception(); |
| 253 | } |
| 254 | |
| 255 | // Grab all of the data from current table. |
| 256 | $sql = "SELECT * |
| 257 | FROM $table_name"; |
| 258 | $result = $this->db->sql_query($sql); |
| 259 | |
| 260 | $i_num_fields = pg_num_fields($result); |
| 261 | $seq = ''; |
| 262 | |
| 263 | for ($i = 0; $i < $i_num_fields; $i++) |
| 264 | { |
| 265 | $ary_type[] = pg_field_type($result, $i); |
| 266 | $ary_name[] = pg_field_name($result, $i); |
| 267 | |
| 268 | $sql = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault |
| 269 | FROM pg_attrdef d, pg_class c |
| 270 | WHERE (c.relname = '{$table_name}') |
| 271 | AND (c.oid = d.adrelid) |
| 272 | AND d.adnum = " . strval($i + 1); |
| 273 | $result2 = $this->db->sql_query($sql); |
| 274 | if ($row = $this->db->sql_fetchrow($result2)) |
| 275 | { |
| 276 | // Determine if we must reset the sequences |
| 277 | if (strpos($row['rowdefault'], "nextval('") === 0) |
| 278 | { |
| 279 | $seq .= "SELECT SETVAL('{$table_name}_seq',(select case when max({$ary_name[$i]})>0 then max({$ary_name[$i]})+1 else 1 end FROM {$table_name}));\n"; |
| 280 | } |
| 281 | } |
| 282 | } |
| 283 | |
| 284 | $this->flush("COPY $table_name (" . implode(', ', $ary_name) . ') FROM stdin;' . "\n"); |
| 285 | while ($row = $this->db->sql_fetchrow($result)) |
| 286 | { |
| 287 | $schema_vals = array(); |
| 288 | |
| 289 | // Build the SQL statement to recreate the data. |
| 290 | for ($i = 0; $i < $i_num_fields; $i++) |
| 291 | { |
| 292 | $str_val = $row[$ary_name[$i]]; |
| 293 | |
| 294 | if (preg_match('#char|text|bool|bytea#i', $ary_type[$i])) |
| 295 | { |
| 296 | $str_val = str_replace(array("\n", "\t", "\r", "\b", "\f", "\v"), array('\n', '\t', '\r', '\b', '\f', '\v'), addslashes($str_val)); |
| 297 | $str_empty = ''; |
| 298 | } |
| 299 | else |
| 300 | { |
| 301 | $str_empty = '\N'; |
| 302 | } |
| 303 | |
| 304 | if (empty($str_val) && $str_val !== '0') |
| 305 | { |
| 306 | $str_val = $str_empty; |
| 307 | } |
| 308 | |
| 309 | $schema_vals[] = $str_val; |
| 310 | } |
| 311 | |
| 312 | // Take the ordered fields and their associated data and build it |
| 313 | // into a valid sql statement to recreate that field in the data. |
| 314 | $this->flush(implode("\t", $schema_vals) . "\n"); |
| 315 | } |
| 316 | $this->db->sql_freeresult($result); |
| 317 | $this->flush("\\.\n"); |
| 318 | |
| 319 | // Write out the sequence statements |
| 320 | $this->flush($seq); |
| 321 | } |
| 322 | |
| 323 | /** |
| 324 | * Writes closing line(s) to database backup |
| 325 | * |
| 326 | * @return void |
| 327 | * @throws extractor_not_initialized_exception when calling this function before init_extractor() |
| 328 | */ |
| 329 | public function write_end() |
| 330 | { |
| 331 | if (!$this->is_initialized) |
| 332 | { |
| 333 | throw new extractor_not_initialized_exception(); |
| 334 | } |
| 335 | |
| 336 | $this->flush("COMMIT;\n"); |
| 337 | parent::write_end(); |
| 338 | } |
| 339 | } |