| [ Index ] |
PHP Cross Reference of phpBB-3.3.14-deutsch |
[Summary view] [Print] [Text view]
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 null 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 null 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) 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 null 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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Mon Nov 25 19:05:08 2024 | Cross-referenced by PHPXref 0.7.1 |