Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 177 |
|
0.00% |
0 / 6 |
CRAP | |
0.00% |
0 / 1 |
| mssql_extractor | |
0.00% |
0 / 177 |
|
0.00% |
0 / 6 |
3306 | |
0.00% |
0 / 1 |
| write_end | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
6 | |||
| write_start | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
6 | |||
| write_table | |
0.00% |
0 / 57 |
|
0.00% |
0 / 1 |
306 | |||
| write_data | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
12 | |||
| write_data_mssqlnative | |
0.00% |
0 / 54 |
|
0.00% |
0 / 1 |
306 | |||
| write_data_odbc | |
0.00% |
0 / 47 |
|
0.00% |
0 / 1 |
272 | |||
| 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 mssql_extractor extends base_extractor |
| 19 | { |
| 20 | /** |
| 21 | * Writes closing line(s) to database backup |
| 22 | * |
| 23 | * @return void |
| 24 | * @throws extractor_not_initialized_exception when calling this function before init_extractor() |
| 25 | */ |
| 26 | public function write_end() |
| 27 | { |
| 28 | if (!$this->is_initialized) |
| 29 | { |
| 30 | throw new extractor_not_initialized_exception(); |
| 31 | } |
| 32 | |
| 33 | $this->flush("COMMIT\nGO\n"); |
| 34 | parent::write_end(); |
| 35 | } |
| 36 | |
| 37 | /** |
| 38 | * {@inheritdoc} |
| 39 | */ |
| 40 | public function write_start($table_prefix) |
| 41 | { |
| 42 | if (!$this->is_initialized) |
| 43 | { |
| 44 | throw new extractor_not_initialized_exception(); |
| 45 | } |
| 46 | |
| 47 | $sql_data = "--\n"; |
| 48 | $sql_data .= "-- phpBB Backup Script\n"; |
| 49 | $sql_data .= "-- Dump of tables for $table_prefix\n"; |
| 50 | $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n"; |
| 51 | $sql_data .= "--\n"; |
| 52 | $sql_data .= "BEGIN TRANSACTION\n"; |
| 53 | $sql_data .= "GO\n"; |
| 54 | $this->flush($sql_data); |
| 55 | } |
| 56 | |
| 57 | /** |
| 58 | * {@inheritdoc} |
| 59 | */ |
| 60 | public function write_table($table_name) |
| 61 | { |
| 62 | if (!$this->is_initialized) |
| 63 | { |
| 64 | throw new extractor_not_initialized_exception(); |
| 65 | } |
| 66 | |
| 67 | $sql_data = '-- Table: ' . $table_name . "\n"; |
| 68 | $sql_data .= "IF OBJECT_ID(N'$table_name', N'U') IS NOT NULL\n"; |
| 69 | $sql_data .= "DROP TABLE $table_name;\n"; |
| 70 | $sql_data .= "GO\n"; |
| 71 | $sql_data .= "\nCREATE TABLE [$table_name] (\n"; |
| 72 | $rows = array(); |
| 73 | |
| 74 | $text_flag = false; |
| 75 | |
| 76 | $sql = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY |
| 77 | FROM INFORMATION_SCHEMA.COLUMNS |
| 78 | WHERE TABLE_NAME = '$table_name'"; |
| 79 | $result = $this->db->sql_query($sql); |
| 80 | |
| 81 | while ($row = $this->db->sql_fetchrow($result)) |
| 82 | { |
| 83 | $line = "\t[{$row['COLUMN_NAME']}] [{$row['DATA_TYPE']}]"; |
| 84 | |
| 85 | if ($row['DATA_TYPE'] == 'text') |
| 86 | { |
| 87 | $text_flag = true; |
| 88 | } |
| 89 | |
| 90 | if ($row['IS_IDENTITY']) |
| 91 | { |
| 92 | $line .= ' IDENTITY (1 , 1)'; |
| 93 | } |
| 94 | |
| 95 | if ($row['CHARACTER_MAXIMUM_LENGTH'] && $row['DATA_TYPE'] !== 'text') |
| 96 | { |
| 97 | $line .= ' (' . $row['CHARACTER_MAXIMUM_LENGTH'] . ')'; |
| 98 | } |
| 99 | |
| 100 | if ($row['IS_NULLABLE'] == 'YES') |
| 101 | { |
| 102 | $line .= ' NULL'; |
| 103 | } |
| 104 | else |
| 105 | { |
| 106 | $line .= ' NOT NULL'; |
| 107 | } |
| 108 | |
| 109 | if ($row['COLUMN_DEFAULT']) |
| 110 | { |
| 111 | $line .= ' DEFAULT ' . $row['COLUMN_DEFAULT']; |
| 112 | } |
| 113 | |
| 114 | $rows[] = $line; |
| 115 | } |
| 116 | $this->db->sql_freeresult($result); |
| 117 | |
| 118 | $sql_data .= implode(",\n", $rows); |
| 119 | $sql_data .= "\n) ON [PRIMARY]"; |
| 120 | |
| 121 | if ($text_flag) |
| 122 | { |
| 123 | $sql_data .= " TEXTIMAGE_ON [PRIMARY]"; |
| 124 | } |
| 125 | |
| 126 | $sql_data .= "\nGO\n\n"; |
| 127 | $rows = array(); |
| 128 | |
| 129 | $sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME |
| 130 | FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE |
| 131 | WHERE TABLE_NAME = '$table_name'"; |
| 132 | $result = $this->db->sql_query($sql); |
| 133 | while ($row = $this->db->sql_fetchrow($result)) |
| 134 | { |
| 135 | if (!count($rows)) |
| 136 | { |
| 137 | $sql_data .= "ALTER TABLE [$table_name] WITH NOCHECK ADD\n"; |
| 138 | $sql_data .= "\tCONSTRAINT [{$row['CONSTRAINT_NAME']}] PRIMARY KEY CLUSTERED \n\t(\n"; |
| 139 | } |
| 140 | $rows[] = "\t\t[{$row['COLUMN_NAME']}]"; |
| 141 | } |
| 142 | if (count($rows)) |
| 143 | { |
| 144 | $sql_data .= implode(",\n", $rows); |
| 145 | $sql_data .= "\n\t) ON [PRIMARY] \nGO\n"; |
| 146 | } |
| 147 | $this->db->sql_freeresult($result); |
| 148 | |
| 149 | $index = array(); |
| 150 | $sql = "EXEC sp_statistics '$table_name'"; |
| 151 | $result = $this->db->sql_query($sql); |
| 152 | while ($row = $this->db->sql_fetchrow($result)) |
| 153 | { |
| 154 | if ($row['TYPE'] == 3) |
| 155 | { |
| 156 | $index[$row['INDEX_NAME']][] = '[' . $row['COLUMN_NAME'] . ']'; |
| 157 | } |
| 158 | } |
| 159 | $this->db->sql_freeresult($result); |
| 160 | |
| 161 | foreach ($index as $index_name => $column_name) |
| 162 | { |
| 163 | $index[$index_name] = implode(', ', $column_name); |
| 164 | } |
| 165 | |
| 166 | foreach ($index as $index_name => $columns) |
| 167 | { |
| 168 | $sql_data .= "\nCREATE INDEX [$index_name] ON [$table_name]($columns) ON [PRIMARY]\nGO\n"; |
| 169 | } |
| 170 | $this->flush($sql_data); |
| 171 | } |
| 172 | |
| 173 | /** |
| 174 | * {@inheritdoc} |
| 175 | */ |
| 176 | public function write_data($table_name) |
| 177 | { |
| 178 | if (!$this->is_initialized) |
| 179 | { |
| 180 | throw new extractor_not_initialized_exception(); |
| 181 | } |
| 182 | |
| 183 | if ($this->db->get_sql_layer() === 'mssqlnative') |
| 184 | { |
| 185 | $this->write_data_mssqlnative($table_name); |
| 186 | } |
| 187 | else |
| 188 | { |
| 189 | $this->write_data_odbc($table_name); |
| 190 | } |
| 191 | } |
| 192 | |
| 193 | /** |
| 194 | * Extracts data from database table (for MSSQL Native driver) |
| 195 | * |
| 196 | * @param string $table_name name of the database table |
| 197 | * @return void |
| 198 | * @throws extractor_not_initialized_exception when calling this function before init_extractor() |
| 199 | */ |
| 200 | protected function write_data_mssqlnative($table_name) |
| 201 | { |
| 202 | if (!$this->is_initialized || !$this->db instanceof \phpbb\db\driver\mssqlnative) |
| 203 | { |
| 204 | throw new extractor_not_initialized_exception(); |
| 205 | } |
| 206 | |
| 207 | $ary_type = $ary_name = array(); |
| 208 | $ident_set = false; |
| 209 | $sql_data = ''; |
| 210 | |
| 211 | // Grab all of the data from current table. |
| 212 | $sql = "SELECT * FROM $table_name"; |
| 213 | $this->db->mssqlnative_set_query_options(array('Scrollable' => SQLSRV_CURSOR_STATIC)); |
| 214 | $result = $this->db->sql_query($sql); |
| 215 | |
| 216 | $retrieved_data = $this->db->mssqlnative_num_rows($result); |
| 217 | |
| 218 | if (!$retrieved_data) |
| 219 | { |
| 220 | $this->db->sql_freeresult($result); |
| 221 | return; |
| 222 | } |
| 223 | |
| 224 | $sql = "SELECT COLUMN_NAME, DATA_TYPE |
| 225 | FROM INFORMATION_SCHEMA.COLUMNS |
| 226 | WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '" . $this->db->sql_escape($table_name) . "'"; |
| 227 | $result_fields = $this->db->sql_query($sql); |
| 228 | |
| 229 | $i_num_fields = 0; |
| 230 | while ($row = $this->db->sql_fetchrow($result_fields)) |
| 231 | { |
| 232 | $ary_type[$i_num_fields] = $row['DATA_TYPE']; |
| 233 | $ary_name[$i_num_fields] = $row['COLUMN_NAME']; |
| 234 | $i_num_fields++; |
| 235 | } |
| 236 | $this->db->sql_freeresult($result_fields); |
| 237 | |
| 238 | $sql = "SELECT 1 as has_identity |
| 239 | FROM INFORMATION_SCHEMA.COLUMNS |
| 240 | WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; |
| 241 | $result2 = $this->db->sql_query($sql); |
| 242 | $row2 = $this->db->sql_fetchrow($result2); |
| 243 | |
| 244 | if (!empty($row2['has_identity'])) |
| 245 | { |
| 246 | $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; |
| 247 | $ident_set = true; |
| 248 | } |
| 249 | $this->db->sql_freeresult($result2); |
| 250 | |
| 251 | while ($row = $this->db->sql_fetchrow($result)) |
| 252 | { |
| 253 | $schema_vals = $schema_fields = array(); |
| 254 | |
| 255 | // Build the SQL statement to recreate the data. |
| 256 | for ($i = 0; $i < $i_num_fields; $i++) |
| 257 | { |
| 258 | $str_val = $row[$ary_name[$i]]; |
| 259 | |
| 260 | // defaults to type number - better quote just to be safe, so check for is_int too |
| 261 | if (is_int($ary_type[$i]) || preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) |
| 262 | { |
| 263 | $str_quote = ''; |
| 264 | $str_empty = "''"; |
| 265 | $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); |
| 266 | } |
| 267 | else if (preg_match('#date|timestamp#i', $ary_type[$i])) |
| 268 | { |
| 269 | if (empty($str_val)) |
| 270 | { |
| 271 | $str_quote = ''; |
| 272 | } |
| 273 | else |
| 274 | { |
| 275 | $str_quote = "'"; |
| 276 | } |
| 277 | } |
| 278 | else |
| 279 | { |
| 280 | $str_quote = ''; |
| 281 | $str_empty = 'NULL'; |
| 282 | } |
| 283 | |
| 284 | if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) |
| 285 | { |
| 286 | $str_val = $str_empty; |
| 287 | } |
| 288 | |
| 289 | $schema_vals[$i] = $str_quote . $str_val . $str_quote; |
| 290 | $schema_fields[$i] = $ary_name[$i]; |
| 291 | } |
| 292 | |
| 293 | // Take the ordered fields and their associated data and build it |
| 294 | // into a valid sql statement to recreate that field in the data. |
| 295 | $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; |
| 296 | |
| 297 | $this->flush($sql_data); |
| 298 | $sql_data = ''; |
| 299 | } |
| 300 | $this->db->sql_freeresult($result); |
| 301 | |
| 302 | if ($ident_set) |
| 303 | { |
| 304 | $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; |
| 305 | } |
| 306 | $this->flush($sql_data); |
| 307 | } |
| 308 | |
| 309 | /** |
| 310 | * Extracts data from database table (for ODBC driver) |
| 311 | * |
| 312 | * @param string $table_name name of the database table |
| 313 | * @return void |
| 314 | * @throws extractor_not_initialized_exception when calling this function before init_extractor() |
| 315 | */ |
| 316 | protected function write_data_odbc($table_name) |
| 317 | { |
| 318 | if (!$this->is_initialized) |
| 319 | { |
| 320 | throw new extractor_not_initialized_exception(); |
| 321 | } |
| 322 | |
| 323 | $ary_type = $ary_name = array(); |
| 324 | $ident_set = false; |
| 325 | $sql_data = ''; |
| 326 | |
| 327 | // Grab all of the data from current table. |
| 328 | $sql = "SELECT * |
| 329 | FROM $table_name"; |
| 330 | $result = $this->db->sql_query($sql); |
| 331 | |
| 332 | $retrieved_data = odbc_num_rows($result); |
| 333 | |
| 334 | if ($retrieved_data) |
| 335 | { |
| 336 | $sql = "SELECT 1 as has_identity |
| 337 | FROM INFORMATION_SCHEMA.COLUMNS |
| 338 | WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; |
| 339 | $result2 = $this->db->sql_query($sql); |
| 340 | $row2 = $this->db->sql_fetchrow($result2); |
| 341 | if (!empty($row2['has_identity'])) |
| 342 | { |
| 343 | $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; |
| 344 | $ident_set = true; |
| 345 | } |
| 346 | $this->db->sql_freeresult($result2); |
| 347 | } |
| 348 | |
| 349 | $i_num_fields = odbc_num_fields($result); |
| 350 | |
| 351 | for ($i = 0; $i < $i_num_fields; $i++) |
| 352 | { |
| 353 | $ary_type[$i] = odbc_field_type($result, $i + 1); |
| 354 | $ary_name[$i] = odbc_field_name($result, $i + 1); |
| 355 | } |
| 356 | |
| 357 | while ($row = $this->db->sql_fetchrow($result)) |
| 358 | { |
| 359 | $schema_vals = $schema_fields = array(); |
| 360 | |
| 361 | // Build the SQL statement to recreate the data. |
| 362 | for ($i = 0; $i < $i_num_fields; $i++) |
| 363 | { |
| 364 | $str_val = $row[$ary_name[$i]]; |
| 365 | |
| 366 | if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) |
| 367 | { |
| 368 | $str_quote = ''; |
| 369 | $str_empty = "''"; |
| 370 | $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val)); |
| 371 | } |
| 372 | else if (preg_match('#date|timestamp#i', $ary_type[$i])) |
| 373 | { |
| 374 | if (empty($str_val)) |
| 375 | { |
| 376 | $str_quote = ''; |
| 377 | } |
| 378 | else |
| 379 | { |
| 380 | $str_quote = "'"; |
| 381 | } |
| 382 | } |
| 383 | else |
| 384 | { |
| 385 | $str_quote = ''; |
| 386 | $str_empty = 'NULL'; |
| 387 | } |
| 388 | |
| 389 | if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) |
| 390 | { |
| 391 | $str_val = $str_empty; |
| 392 | } |
| 393 | |
| 394 | $schema_vals[$i] = $str_quote . $str_val . $str_quote; |
| 395 | $schema_fields[$i] = $ary_name[$i]; |
| 396 | } |
| 397 | |
| 398 | // Take the ordered fields and their associated data and build it |
| 399 | // into a valid sql statement to recreate that field in the data. |
| 400 | $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n"; |
| 401 | |
| 402 | $this->flush($sql_data); |
| 403 | |
| 404 | $sql_data = ''; |
| 405 | |
| 406 | } |
| 407 | $this->db->sql_freeresult($result); |
| 408 | |
| 409 | if ($retrieved_data && $ident_set) |
| 410 | { |
| 411 | $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; |
| 412 | } |
| 413 | $this->flush($sql_data); |
| 414 | } |
| 415 | } |