| [ Index ] |
PHP Cross Reference of phpBB 3.0 Beta 3 |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * 4 * @package acp 5 * @version $Id: acp_database.php,v 1.46 2006/11/05 19:03:24 davidmj Exp $ 6 * @copyright (c) 2005 phpBB Group 7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License 8 * 9 */ 10 11 /** 12 * @package acp 13 */ 14 class acp_database 15 { 16 var $u_action; 17 18 function main($id, $mode) 19 { 20 global $db, $user, $auth, $template, $table_prefix; 21 global $config, $phpbb_root_path, $phpbb_admin_path, $phpEx; 22 23 $user->add_lang('acp/database'); 24 25 $this->tpl_name = 'acp_database'; 26 $this->page_title = 'ACP_DATABASE'; 27 28 $action = request_var('action', ''); 29 $submit = (isset($_POST['submit'])) ? true : false; 30 31 $template->assign_vars(array( 32 'MODE' => $mode 33 )); 34 35 switch ($mode) 36 { 37 case 'backup': 38 39 switch ($action) 40 { 41 case 'download': 42 $type = request_var('type', ''); 43 $table = request_var('table', array('')); 44 $format = request_var('method', ''); 45 $where = request_var('where', ''); 46 47 $store = $download = $structure = $schema_data = false; 48 49 if ($where == 'store_and_download' || $where == 'store') 50 { 51 $store = true; 52 } 53 54 if ($where == 'store_and_download' || $where == 'download') 55 { 56 $download = true; 57 } 58 59 if ($type == 'full' || $type == 'structure') 60 { 61 $structure = true; 62 } 63 64 if ($type == 'full' || $type == 'data') 65 { 66 $schema_data = true; 67 } 68 69 @set_time_limit(1200); 70 71 $time = time(); 72 73 $filename = 'backup_' . $time; 74 75 // We set up the info needed for our on-the-fly creation :D 76 switch ($format) 77 { 78 case 'text': 79 $ext = '.sql'; 80 $open = 'fopen'; 81 $write = 'fwrite'; 82 $close = 'fclose'; 83 $oper = ''; 84 $mimetype = 'text/x-sql'; 85 break; 86 case 'bzip2': 87 $ext = '.sql.bz2'; 88 $open = 'bzopen'; 89 $write = 'bzwrite'; 90 $close = 'bzclose'; 91 $oper = 'bzcompress'; 92 $mimetype = 'application/x-bzip2'; 93 break; 94 case 'gzip': 95 $ext = '.sql.gz'; 96 $open = 'gzopen'; 97 $write = 'gzwrite'; 98 $close = 'gzclose'; 99 $oper = 'gzencode'; 100 $mimetype = 'application/x-gzip'; 101 break; 102 } 103 104 // We write the file to "store" first (and then compress the file) to not use too much 105 // memory. The server process can be easily killed by storing too much data at once. 106 107 108 if ($store == true) 109 { 110 $file = $phpbb_root_path . 'store/' . $filename . $ext; 111 112 $fp = $open($file, 'w'); 113 114 if (!$fp) 115 { 116 trigger_error('Unable to write temporary file to storage folder', E_USER_ERROR); 117 } 118 } 119 120 if ($download == true) 121 { 122 $name = $filename . $ext; 123 header('Pragma: no-cache'); 124 header("Content-Type: $mimetype; name=\"$name\""); 125 header("Content-disposition: attachment; filename=$name"); 126 } 127 128 // All of the generated queries go here 129 $sql_data = ''; 130 $sql_data .= "#\n"; 131 $sql_data .= "# phpBB Backup Script\n"; 132 $sql_data .= "# Dump of tables for $table_prefix\n"; 133 $sql_data .= "# DATE : " . gmdate("d-m-Y H:i:s", $time) . " GMT\n"; 134 $sql_data .= "#\n"; 135 136 switch ($db->sql_layer) 137 { 138 case 'sqlite': 139 $sql_data .= "BEGIN TRANSACTION;\n"; 140 $sqlite_version = sqlite_libversion(); 141 break; 142 143 case 'postgres': 144 $sql_data .= "BEGIN;\n"; 145 break; 146 147 case 'mssql': 148 case 'mssql_odbc': 149 $sql_data .= "BEGIN TRANSACTION\n"; 150 $sql_data .= "GO\n"; 151 break; 152 } 153 154 if ($structure && $db->sql_layer == 'firebird') 155 { 156 $sql = 'SELECT RDB$FUNCTION_NAME, RDB$DESCRIPTION 157 FROM RDB$FUNCTIONS 158 ORDER BY RDB$FUNCTION_NAME'; 159 $result = $db->sql_query($sql); 160 161 $rows = array(); 162 while ($row = $db->sql_fetchrow($result)) 163 { 164 $sql = 'SELECT F.RDB$FUNCTION_NAME, F.RDB$MODULE_NAME, F.RDB$ENTRYPOINT, F.RDB$RETURN_ARGUMENT, F.RDB$DESCRIPTION, FA.RDB$ARGUMENT_POSITION, FA.RDB$MECHANISM, FA.RDB$FIELD_TYPE, FA.RDB$FIELD_SCALE, FA.RDB$FIELD_LENGTH, FA.RDB$FIELD_SUB_TYPE, C.RDB$BYTES_PER_CHARACTER, C.RDB$CHARACTER_SET_NAME ,FA.RDB$FIELD_PRECISION 165 FROM RDB$FUNCTIONS F 166 LEFT JOIN RDB$FUNCTION_ARGUMENTS FA ON F.RDB$FUNCTION_NAME = FA.RDB$FUNCTION_NAME 167 LEFT JOIN RDB$CHARACTER_SETS C ON FA.RDB$CHARACTER_SET_ID = C.RDB$CHARACTER_SET_ID 168 WHERE (F.RDB$FUNCTION_NAME = ' . $row['FUNCTION_NAME'] . ') 169 ORDER BY FA.RDB$ARGUMENT_POSITION'; 170 $result2 = $db->sql_query($sql); 171 while ($row2 = $db->sql_fetchrow($result2)) 172 { 173 } 174 $db->sql_freeresult($result2); 175 } 176 $db->sql_freeresult($result); 177 } 178 179 foreach ($table as $table_name) 180 { 181 // Get the table structure 182 if ($structure) 183 { 184 switch ($db->sql_layer) 185 { 186 case 'mysqli': 187 case 'mysql4': 188 case 'mysql': 189 $sql_data .= '# Table: ' . $table_name . "\n"; 190 $sql_data .= "DROP TABLE IF EXISTS $table_name;\n"; 191 break; 192 193 case 'oracle': 194 $sql_data .= '# Table: ' . $table_name . "\n"; 195 $sql_data .= "DROP TABLE $table_name;\n"; 196 $sql_data .= '\\' . "\n"; 197 break; 198 199 case 'sqlite': 200 $sql_data .= '# Table: ' . $table_name . "\n"; 201 if (version_compare($sqlite_version, '3.0') == -1) 202 { 203 $sql_data .= "DROP TABLE $table_name;\n"; 204 } 205 else 206 { 207 $sql_data .= "DROP TABLE IF EXISTS $table_name;\n"; 208 } 209 break; 210 211 case 'postgres': 212 case 'firebird': 213 $sql_data .= '# Table: ' . $table_name . "\n"; 214 $sql_data .= "DROP TABLE $table_name;\n"; 215 break; 216 217 case 'mssql': 218 case 'mssql_odbc': 219 $sql_data .= '# Table: ' . $table_name . "\n"; 220 $sql_data .= "IF OBJECT_ID(N'$table_name', N'U') IS NOT NULL\n"; 221 $sql_data .= "DROP TABLE $table_name;\n"; 222 $sql_data .= "GO\n"; 223 break; 224 } 225 $sql_data .= $this->get_table_structure($table_name); 226 } 227 else 228 { 229 // We might wanna empty out all that junk :D 230 $sql_data .= (($db->sql_layer == 'sqlite') ? 'DELETE FROM ' : 'TRUNCATE TABLE ') . $table_name . ";\n"; 231 } 232 233 // Now write the data for the first time. :) 234 if ($store == true) 235 { 236 $write($fp, $sql_data); 237 } 238 239 if ($download == true) 240 { 241 if (!empty($oper)) 242 { 243 echo $oper($sql_data); 244 } 245 else 246 { 247 echo $sql_data; 248 } 249 } 250 251 $sql_data = ''; 252 253 // Data 254 if ($schema_data) 255 { 256 $sql_data .= "\n"; 257 258 switch ($db->sql_layer) 259 { 260 case 'mysqli': 261 262 $sql = "SELECT * 263 FROM $table_name"; 264 $result = mysqli_query($db->db_connect_id, $sql, MYSQLI_USE_RESULT); 265 if ($result != false) 266 { 267 $fields_cnt = mysqli_num_fields($result); 268 269 // Get field information 270 $field = mysqli_fetch_fields($result); 271 $field_set = array(); 272 273 for ($j = 0; $j < $fields_cnt; $j++) 274 { 275 $field_set[$j] = $field[$j]->name; 276 } 277 278 $search = array("\\", "'", "\x00", "\x0a", "\x0d", "\x1a", '"'); 279 $replace = array("\\\\", "\\'", '\0', '\n', '\r', '\Z', '\\"'); 280 $fields = implode(', ', $field_set); 281 $values = array(); 282 $schema_insert = 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES ('; 283 284 while ($row = mysqli_fetch_row($result)) 285 { 286 for ($j = 0; $j < $fields_cnt; $j++) 287 { 288 if (!isset($row[$j]) || is_null($row[$j])) 289 { 290 $values[$j] = 'NULL'; 291 } 292 else if (($field[$j]->flags & 32768) && !($field[$j]->flags & 1024)) 293 { 294 $values[$j] = $row[$j]; 295 } 296 else 297 { 298 $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'"; 299 } 300 } 301 $sql_data .= $schema_insert . implode(', ', $values) . ");\n"; 302 303 if ($store == true) 304 { 305 $write($fp, $sql_data); 306 } 307 308 if ($download == true) 309 { 310 if (!empty($oper)) 311 { 312 echo $oper($sql_data); 313 } 314 else 315 { 316 echo $sql_data; 317 } 318 } 319 $sql_data = ''; 320 321 $values = array(); 322 } 323 mysqli_free_result($result); 324 } 325 break; 326 327 case 'mysql4': 328 case 'mysql': 329 330 $sql = "SELECT * 331 FROM $table_name"; 332 $result = mysql_unbuffered_query($sql, $db->db_connect_id); 333 334 if ($result != false) 335 { 336 $fields_cnt = mysql_num_fields($result); 337 338 // Get field information 339 $field = array(); 340 for ($i = 0; $i < $fields_cnt; $i++) 341 { 342 $field[$i] = mysql_fetch_field($result, $i); 343 } 344 $field_set = array(); 345 346 for ($j = 0; $j < $fields_cnt; $j++) 347 { 348 $field_set[$j] = $field[$j]->name; 349 } 350 351 $search = array("\\", "'", "\x00", "\x0a", "\x0d", "\x1a", '"'); 352 $replace = array("\\\\", "\\'", '\0', '\n', '\r', '\Z', '\\"'); 353 $fields = implode(', ', $field_set); 354 $schema_insert = 'INSERT INTO ' . $table_name . ' (' . $fields . ') VALUES ('; 355 356 while ($row = mysql_fetch_row($result)) 357 { 358 $values = array(); 359 360 for ($j = 0; $j < $fields_cnt; $j++) 361 { 362 if (!isset($row[$j]) || is_null($row[$j])) 363 { 364 $values[$j] = 'NULL'; 365 } 366 else if ($field[$j]->numeric && ($field[$j]->type !== 'timestamp')) 367 { 368 $values[$j] = $row[$j]; 369 } 370 else 371 { 372 $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'"; 373 } 374 } 375 $sql_data .= $schema_insert . implode(', ', $values) . ");\n"; 376 377 if ($store == true) 378 { 379 $write($fp, $sql_data); 380 } 381 382 if ($download == true) 383 { 384 if (!empty($oper)) 385 { 386 echo $oper($sql_data); 387 } 388 else 389 { 390 echo $sql_data; 391 } 392 } 393 $sql_data = ''; 394 } 395 mysql_free_result($result); 396 } 397 break; 398 399 case 'sqlite': 400 // This is *not* my fault. The PHP guys forgot a call to finalize when they wrote this function. This forces all the tables to stay locked... 401 // They finally fixed it in 5.1.3 but 5.1.2 and under still have this so instead, we go and grab the column types by smashing open the sqlite_master table 402 // and grope around for things that remind us of datatypes... 403 if (version_compare(phpversion(), '5.1.3', '>=')) 404 { 405 $col_types = sqlite_fetch_column_types($db->db_connect_id, $table_name); 406 } 407 else 408 { 409 $sql = "SELECT sql 410 FROM sqlite_master 411 WHERE type = 'table' 412 AND name = '" . $table_name . "'"; 413 $table_data = sqlite_single_query($db->db_connect_id, $sql); 414 $table_data = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', '', $table_data); 415 $table_data = trim($table_data); 416 417 preg_match('#\((.*)\)#s', $table_data, $matches); 418 419 $column_list = array(); 420 $table_cols = explode(',', trim($matches[1])); 421 foreach ($table_cols as $declaration) 422 { 423 $entities = preg_split('#\s+#', trim($declaration)); 424 $column_name = preg_replace('/"?([^"]+)"?/', '\1', $entities[0]); 425 426 // Hit a primary key, those are not what we need :D 427 if (empty($entities[1])) 428 { 429 continue; 430 } 431 $col_types[$column_name] = $entities[1]; 432 } 433 } 434 435 // Unbueffered query and the foreach make this ultra fast, we wait for nothing. 436 $sql = "SELECT * 437 FROM $table_name"; 438 $result = sqlite_unbuffered_query($db->db_connect_id, $sql); 439 $rows = sqlite_fetch_all($result, SQLITE_ASSOC); 440 441 foreach ($rows as $row) 442 { 443 $names = $data = array(); 444 foreach ($row as $row_name => $row_data) 445 { 446 $names[] = $row_name; 447 448 // Figure out what this data is, escape it properly 449 if (is_null($row_data)) 450 { 451 $row_data = 'NULL'; 452 } 453 else if ($row_data == '') 454 { 455 $row_data = "''"; 456 } 457 else if (strpos($col_types[$row_name], 'text') !== false || strpos($col_types[$row_name], 'char') !== false || strpos($col_types[$row_name], 'blob') !== false) 458 { 459 $row_data = "'" . $row_data . "'"; 460 } 461 462 $data[] = $row_data; 463 } 464 $sql_data .= 'INSERT INTO ' . $table_name . ' (' . implode(', ', $names) . ') VALUES ('. implode(', ', $data) .");\n"; 465 466 if ($store == true) 467 { 468 $write($fp, $sql_data); 469 } 470 471 if ($download == true) 472 { 473 if (!empty($oper)) 474 { 475 echo $oper($sql_data); 476 } 477 else 478 { 479 echo $sql_data; 480 } 481 } 482 $sql_data = ''; 483 484 } 485 $db->sql_freeresult($result); 486 break; 487 488 case 'postgres': 489 490 $ary_type = $ary_name = array(); 491 492 // Grab all of the data from current table. 493 $sql = "SELECT * 494 FROM $table_name"; 495 $result = $db->sql_query($sql); 496 497 $i_num_fields = pg_num_fields($result); 498 $seq = ''; 499 500 for ($i = 0; $i < $i_num_fields; $i++) 501 { 502 $ary_type[$i] = pg_field_type($result, $i); 503 $ary_name[$i] = pg_field_name($result, $i); 504 505 506 $sql = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault 507 FROM pg_attrdef d, pg_class c 508 WHERE (c.relname = '{$table_name}') 509 AND (c.oid = d.adrelid) 510 AND d.adnum = " . strval($i + 1); 511 $result2 = $db->sql_query($sql); 512 if ($row = $db->sql_fetchrow($result2)) 513 { 514 // Determine if we must reset the sequences 515 if (strpos($row['rowdefault'], "nextval('") === 0) 516 { 517 $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"; 518 } 519 } 520 } 521 522 while ($row = $db->sql_fetchrow($result)) 523 { 524 $schema_vals = $schema_fields = array(); 525 526 // Build the SQL statement to recreate the data. 527 for ($i = 0; $i < $i_num_fields; $i++) 528 { 529 $str_val = $row[$ary_name[$i]]; 530 531 if (preg_match('#char|text|bool|bytea#i', $ary_type[$i])) 532 { 533 $str_quote = "'"; 534 $str_empty = ''; 535 $str_val = addslashes($str_val); 536 } 537 else if (preg_match('#date|timestamp#i', $ary_type[$i])) 538 { 539 if (empty($str_val)) 540 { 541 $str_quote = ''; 542 } 543 else 544 { 545 $str_quote = "'"; 546 } 547 } 548 else 549 { 550 $str_quote = ''; 551 $str_empty = 'NULL'; 552 } 553 554 if (empty($str_val) && $str_val !== '0') 555 { 556 $str_val = $str_empty; 557 } 558 559 $schema_vals[$i] = $str_quote . $str_val . $str_quote; 560 $schema_fields[$i] = $ary_name[$i]; 561 } 562 563 // Take the ordered fields and their associated data and build it 564 // into a valid sql statement to recreate that field in the data. 565 $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; 566 567 if ($store == true) 568 { 569 $write($fp, $sql_data); 570 } 571 572 if ($download == true) 573 { 574 if (!empty($oper)) 575 { 576 echo $oper($sql_data); 577 } 578 else 579 { 580 echo $sql_data; 581 } 582 } 583 584 $sql_data = ''; 585 586 } 587 $db->sql_freeresult($result); 588 589 // Write out the sequence statements 590 if ($store == true) 591 { 592 $write($fp, $seq); 593 } 594 595 if ($download == true) 596 { 597 if (!empty($oper)) 598 { 599 echo $oper($seq); 600 } 601 else 602 { 603 echo $seq; 604 } 605 } 606 $seq = ''; 607 break; 608 609 case 'mssql_odbc': 610 $ary_type = $ary_name = array(); 611 $ident_set = false; 612 613 // Grab all of the data from current table. 614 $sql = "SELECT * 615 FROM $table_name"; 616 $result = $db->sql_query($sql); 617 618 $retrieved_data = odbc_num_rows($result); 619 620 if ($retrieved_data) 621 { 622 $sql = "SELECT 1 as has_identity 623 FROM INFORMATION_SCHEMA.COLUMNS 624 WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; 625 $result2 = $db->sql_query($sql); 626 $row2 = $db->sql_fetchrow($result2); 627 if (!empty($row2['has_identity'])) 628 { 629 $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; 630 $ident_set = true; 631 } 632 $db->sql_freeresult($result2); 633 } 634 635 $i_num_fields = odbc_num_fields($result); 636 637 for ($i = 0; $i < $i_num_fields; $i++) 638 { 639 $ary_type[$i] = odbc_field_type($result, $i); 640 $ary_name[$i] = odbc_field_name($result, $i); 641 } 642 643 while ($row = $db->sql_fetchrow($result)) 644 { 645 $schema_vals = $schema_fields = array(); 646 647 // Build the SQL statement to recreate the data. 648 for ($i = 0; $i < $i_num_fields; $i++) 649 { 650 $str_val = $row[$ary_name[$i]]; 651 652 if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) 653 { 654 $str_quote = "'"; 655 $str_empty = ''; 656 $str_val = addslashes($str_val); 657 } 658 else if (preg_match('#date|timestamp#i', $ary_type[$i])) 659 { 660 if (empty($str_val)) 661 { 662 $str_quote = ''; 663 } 664 else 665 { 666 $str_quote = "'"; 667 } 668 } 669 else 670 { 671 $str_quote = ''; 672 $str_empty = 'NULL'; 673 } 674 675 if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) 676 { 677 $str_val = $str_empty; 678 } 679 680 $schema_vals[$i] = $str_quote . $str_val . $str_quote; 681 $schema_fields[$i] = $ary_name[$i]; 682 } 683 684 // Take the ordered fields and their associated data and build it 685 // into a valid sql statement to recreate that field in the data. 686 $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; 687 688 if ($store == true) 689 { 690 $write($fp, $sql_data); 691 } 692 693 if ($download == true) 694 { 695 if (!empty($oper)) 696 { 697 echo $oper($sql_data); 698 } 699 else 700 { 701 echo $sql_data; 702 } 703 } 704 705 $sql_data = ''; 706 707 } 708 $db->sql_freeresult($result); 709 710 if ($retrieved_data) 711 { 712 $sql_data = "\nGO\n"; 713 if ($ident_set) 714 { 715 $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; 716 } 717 } 718 break; 719 720 case 'mssql': 721 $ary_type = $ary_name = array(); 722 $ident_set = false; 723 724 // Grab all of the data from current table. 725 $sql = "SELECT * 726 FROM $table_name"; 727 $result = $db->sql_query($sql); 728 729 $retrieved_data = mssql_num_rows($result); 730 731 $i_num_fields = mssql_num_fields($result); 732 733 for ($i = 0; $i < $i_num_fields; $i++) 734 { 735 $ary_type[$i] = mssql_field_type($result, $i); 736 $ary_name[$i] = mssql_field_name($result, $i); 737 } 738 739 if ($retrieved_data) 740 { 741 $sql = "SELECT 1 as has_identity 742 FROM INFORMATION_SCHEMA.COLUMNS 743 WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1"; 744 $result2 = $db->sql_query($sql); 745 $row2 = $db->sql_fetchrow($result2); 746 if (!empty($row2['has_identity'])) 747 { 748 $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n"; 749 $ident_set = true; 750 } 751 $db->sql_freeresult($result2); 752 } 753 754 while ($row = $db->sql_fetchrow($result)) 755 { 756 $schema_vals = $schema_fields = array(); 757 758 // Build the SQL statement to recreate the data. 759 for ($i = 0; $i < $i_num_fields; $i++) 760 { 761 $str_val = $row[$ary_name[$i]]; 762 763 if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) 764 { 765 $str_quote = "'"; 766 $str_empty = ''; 767 $str_val = addslashes($str_val); 768 } 769 else if (preg_match('#date|timestamp#i', $ary_type[$i])) 770 { 771 if (empty($str_val)) 772 { 773 $str_quote = ''; 774 } 775 else 776 { 777 $str_quote = "'"; 778 } 779 } 780 else 781 { 782 $str_quote = ''; 783 $str_empty = 'NULL'; 784 } 785 786 if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val))) 787 { 788 $str_val = $str_empty; 789 } 790 791 $schema_vals[$i] = $str_quote . $str_val . $str_quote; 792 $schema_fields[$i] = $ary_name[$i]; 793 } 794 795 // Take the ordered fields and their associated data and build it 796 // into a valid sql statement to recreate that field in the data. 797 $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; 798 799 if ($store == true) 800 { 801 $write($fp, $sql_data); 802 } 803 804 if ($download == true) 805 { 806 if (!empty($oper)) 807 { 808 echo $oper($sql_data); 809 } 810 else 811 { 812 echo $sql_data; 813 } 814 } 815 816 $sql_data = ''; 817 818 } 819 $db->sql_freeresult($result); 820 821 if ($retrieved_data) 822 { 823 $sql_data = "\nGO\n"; 824 if ($ident_set) 825 { 826 $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n"; 827 } 828 } 829 break; 830 831 case 'firebird': 832 833 $ary_type = $ary_name = array(); 834 835 // Grab all of the data from current table. 836 $sql = "SELECT * 837 FROM $table_name"; 838 $result = $db->sql_query($sql); 839 840 $i_num_fields = ibase_num_fields($result); 841 842 for ($i = 0; $i < $i_num_fields; $i++) 843 { 844 $info = ibase_field_info($result, $i); 845 $ary_type[$i] = $info['type']; 846 $ary_name[$i] = $info['name']; 847 } 848 849 while ($row = $db->sql_fetchrow($result)) 850 { 851 $schema_vals = $schema_fields = array(); 852 853 // Build the SQL statement to recreate the data. 854 for ($i = 0; $i < $i_num_fields; $i++) 855 { 856 $str_val = $row[strtolower($ary_name[$i])]; 857 858 if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i])) 859 { 860 $str_quote = "'"; 861 $str_empty = ''; 862 $str_val = addslashes($str_val); 863 } 864 else if (preg_match('#date|timestamp#i', $ary_type[$i])) 865 { 866 if (empty($str_val)) 867 { 868 $str_quote = ''; 869 } 870 else 871 { 872 $str_quote = "'"; 873 } 874 } 875 else 876 { 877 $str_quote = ''; 878 $str_empty = 'NULL'; 879 } 880 881 if (empty($str_val) && $str_val !== '0') 882 { 883 $str_val = $str_empty; 884 } 885 886 $schema_vals[$i] = $str_quote . $str_val . $str_quote; 887 $schema_fields[$i] = "'" . $ary_name[$i] . "'"; 888 } 889 890 // Take the ordered fields and their associated data and build it 891 // into a valid sql statement to recreate that field in the data. 892 $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; 893 894 if ($store == true) 895 { 896 $write($fp, $sql_data); 897 } 898 899 if ($download == true) 900 { 901 if (!empty($oper)) 902 { 903 echo $oper($sql_data); 904 } 905 else 906 { 907 echo $sql_data; 908 } 909 } 910 911 $sql_data = ''; 912 913 } 914 $db->sql_freeresult($result); 915 break; 916 917 case 'oracle': 918 $ary_type = $ary_name = array(); 919 920 // Grab all of the data from current table. 921 $sql = "SELECT * 922 FROM $table_name"; 923 $result = $db->sql_query($sql); 924 925 $i_num_fields = ocinumcols($result); 926 927 for ($i = 0; $i < $i_num_fields; $i++) 928 { 929 $ary_type[$i] = ocicolumntype($result, $i); 930 $ary_name[$i] = ocicolumnname($result, $i); 931 } 932 933 while ($row = $db->sql_fetchrow($result)) 934 { 935 $schema_vals = $schema_fields = array(); 936 937 // Build the SQL statement to recreate the data. 938 for ($i = 0; $i < $i_num_fields; $i++) 939 { 940 $str_val = $row[$ary_name[$i]]; 941 942 if (preg_match('#char|text|bool|raw#i', $ary_type[$i])) 943 { 944 $str_quote = "'"; 945 $str_empty = ''; 946 $str_val = addslashes($str_val); 947 } 948 else if (preg_match('#date|timestamp#i', $ary_type[$i])) 949 { 950 if (empty($str_val)) 951 { 952 $str_quote = ''; 953 } 954 else 955 { 956 $str_quote = "'"; 957 } 958 } 959 else 960 { 961 $str_quote = ''; 962 $str_empty = 'NULL'; 963 } 964 965 if (empty($str_val) && $str_val !== '0') 966 { 967 $str_val = $str_empty; 968 } 969 970 $schema_vals[$i] = $str_quote . $str_val . $str_quote; 971 $schema_fields[$i] = '"' . $ary_name[$i] . "'"; 972 } 973 974 // Take the ordered fields and their associated data and build it 975 // into a valid sql statement to recreate that field in the data. 976 $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\n"; 977 978 if ($store == true) 979 { 980 $write($fp, $sql_data); 981 } 982 983 if ($download == true) 984 { 985 if (!empty($oper)) 986 { 987 echo $oper($sql_data); 988 } 989 else 990 { 991 echo $sql_data; 992 } 993 } 994 995 $sql_data = ''; 996 997 } 998 $db->sql_freeresult($result); 999 break; 1000 } 1001 } 1002 } 1003 1004 switch ($db->sql_layer) 1005 { 1006 case 'sqlite': 1007 case 'postgres': 1008 $sql_data .= "COMMIT;"; 1009 break; 1010 1011 case 'mssql': 1012 case 'mssql_odbc': 1013 $sql_data .= "COMMIT\nGO"; 1014 break; 1015 } 1016 1017 if ($store == true) 1018 { 1019 $write($fp, $sql_data); 1020 $close($fp); 1021 } 1022 1023 if ($download == true) 1024 { 1025 if (!empty($oper)) 1026 { 1027 echo $oper($sql_data); 1028 } 1029 else 1030 { 1031 echo $sql_data; 1032 } 1033 exit; 1034 } 1035 1036 unset($sql_data); 1037 1038 add_log('admin', 'LOG_DB_BACKUP'); 1039 trigger_error($user->lang['BACKUP_SUCCESS'] . adm_back_link($this->u_action)); 1040 break; 1041 1042 default: 1043 $tables = array(); 1044 switch ($db->sql_layer) 1045 { 1046 case 'sqlite': 1047 $sql = "SELECT name 1048 FROM sqlite_master 1049 WHERE type='table' 1050 ORDER BY name"; 1051 $result = $db->sql_query($sql); 1052 while ($row = $db->sql_fetchrow($result)) 1053 { 1054 if (strpos($row['name'], $table_prefix) === 0) 1055 { 1056 $tables[] = $row['name']; 1057 } 1058 } 1059 $db->sql_freeresult($result); 1060 break; 1061 1062 case 'mysqli': 1063 case 'mysql4': 1064 case 'mysql': 1065 $sql = "SHOW TABLES 1066 LIKE '{$table_prefix}%'"; 1067 $result = $db->sql_query($sql); 1068 while ($row = $db->sql_fetchrow($result)) 1069 { 1070 $tables[] = current($row); 1071 } 1072 $db->sql_freeresult($result); 1073 break; 1074 1075 case 'postgres': 1076 $sql = 'SELECT relname 1077 FROM pg_stat_user_tables 1078 ORDER BY relname'; 1079 $result = $db->sql_query($sql); 1080 while ($row = $db->sql_fetchrow($result)) 1081 { 1082 if (strpos($row['relname'], $table_prefix) === 0) 1083 { 1084 $tables[] = $row['relname']; 1085 } 1086 } 1087 $db->sql_freeresult($result); 1088 break; 1089 1090 case 'mssql': 1091 case 'mssql_odbc': 1092 $sql = "SELECT TABLE_NAME 1093 FROM INFORMATION_SCHEMA.TABLES 1094 WHERE TABLE_TYPE = 'BASE TABLE' 1095 ORDER BY TABLE_NAME"; 1096 $result = $db->sql_query($sql); 1097 while ($row = $db->sql_fetchrow($result)) 1098 { 1099 if (strpos($row['TABLE_NAME'], $table_prefix) === 0) 1100 { 1101 $tables[] = $row['TABLE_NAME']; 1102 } 1103 } 1104 $db->sql_freeresult($result); 1105 break; 1106 1107 case 'firebird': 1108 $sql = 'SELECT RDB$RELATION_NAME as TABLE_NAME 1109 FROM RDB$RELATIONS 1110 WHERE RDB$SYSTEM_FLAG=0 1111 AND RDB$VIEW_BLR IS NULL'; 1112 $result = $db->sql_query($sql); 1113 while ($row = $db->sql_fetchrow($result)) 1114 { 1115 if (stripos($row['table_name'], $table_prefix) === 0) 1116 { 1117 $tables[] = $row['table_name']; 1118 } 1119 } 1120 $db->sql_freeresult($result); 1121 break; 1122 1123 case 'oracle': 1124 $sql = 'SELECT TNAME as table_name 1125 FROM TAB'; 1126 $result = $db->sql_query($sql); 1127 while ($row = $db->sql_fetchrow($result)) 1128 { 1129 if (stripos($row['table_name'], $table_prefix) === 0) 1130 { 1131 $tables[] = $row['table_name']; 1132 } 1133 } 1134 $db->sql_freeresult($result); 1135 break; 1136 } 1137 1138 foreach ($tables as $table) 1139 { 1140 $template->assign_block_vars('tables', array( 1141 'TABLE' => $table 1142 )); 1143 } 1144 1145 $template->assign_vars(array( 1146 'U_ACTION' => $this->u_action . '&action=download' 1147 )); 1148 1149 $available_methods = array('gzip' => 'zlib', 'bzip2' => 'bz2'); 1150 1151 foreach ($available_methods as $type => $module) 1152 { 1153 if (!@extension_loaded($module)) 1154 { 1155 continue; 1156 } 1157 1158 $template->assign_block_vars('methods', array( 1159 'TYPE' => $type 1160 )); 1161 } 1162 1163 $template->assign_block_vars('methods', array( 1164 'TYPE' => 'text' 1165 )); 1166 break; 1167 } 1168 break; 1169 1170 case 'restore': 1171 switch ($action) 1172 { 1173 case 'submit': 1174 $delete = request_var('delete', ''); 1175 $file = request_var('file', ''); 1176 1177 preg_match('#^(backup_\d{10,})\.(sql(?:\.(?:gz|bz2))?)$#', $file, $matches); 1178 $file_name = $phpbb_root_path . 'store/' . $matches[0]; 1179 1180 if (!(file_exists($file_name) && is_readable($file_name))) 1181 { 1182 trigger_error($user->lang['BACKUP_INVALID'] . adm_back_link($this->u_action), E_USER_WARNING); 1183 } 1184 1185 if ($delete) 1186 { 1187 unlink($file_name); 1188 trigger_error($user->lang['BACKUP_DELETE'] . adm_back_link($this->u_action)); 1189 } 1190 1191 $data = file_get_contents($file_name); 1192 1193 switch ($matches[2]) 1194 { 1195 case 'sql.bz2': 1196 $data = bzdecompress($data); 1197 break; 1198 case 'sql.gz': 1199 $data = gzinflate(substr($data, 10)); 1200 break; 1201 } 1202 1203 $download = request_var('download', ''); 1204 1205 if ($download) 1206 { 1207 $name = $matches[0]; 1208 1209 switch ($matches[2]) 1210 { 1211 case 'sql': 1212 $mimetype = 'text/x-sql'; 1213 break; 1214 case 'sql.bz2': 1215 $mimetype = 'application/x-bzip2'; 1216 break; 1217 case 'sql.gz': 1218 $mimetype = 'application/x-gzip'; 1219 break; 1220 } 1221 1222 header('Pragma: no-cache'); 1223 header("Content-Type: $mimetype; name=\"$name\""); 1224 header("Content-disposition: attachment; filename=$name"); 1225 echo $data; 1226 die; 1227 } 1228 1229 if (!empty($data)) 1230 { 1231 // Strip out sql comments... 1232 remove_remarks($data); 1233 1234 // SQLite gets improved performance when you shove all of these disk write queries at once :D 1235 if ($db->sql_layer == 'sqlite') 1236 { 1237 $db->sql_query($data); 1238 } 1239 else 1240 { 1241 switch ($db->sql_layer) 1242 { 1243 case 'firebird': 1244 $delim = ';;'; 1245 break; 1246 1247 case 'mysql': 1248 case 'mysql4': 1249 case 'mysqli': 1250 case 'postgres': 1251 $delim = ';'; 1252 break; 1253 1254 case 'oracle': 1255 $delim = '/'; 1256 break; 1257 1258 case 'mssql': 1259 case 'mssql-odbc': 1260 $delim = 'GO'; 1261 break; 1262 } 1263 $pieces = split_sql_file($data, $delim); 1264 1265 $sql_count = count($pieces); 1266 for($i = 0; $i < $sql_count; $i++) 1267 { 1268 $sql = trim($pieces[$i]); 1269 1270 if (!empty($sql) && $sql[0] != '#') 1271 { 1272 $db->sql_query($sql); 1273 } 1274 } 1275 } 1276 } 1277 add_log('admin', 'LOG_DB_RESTORE'); 1278 trigger_error($user->lang['RESTORE_SUCCESS'] . adm_back_link($this->u_action)); 1279 break; 1280 1281 default: 1282 $selected = false; 1283 $methods = array('sql'); 1284 $available_methods = array('sql.gz' => 'zlib', 'sql.bz2' => 'bz2'); 1285 1286 foreach ($available_methods as $type => $module) 1287 { 1288 if (!@extension_loaded($module)) 1289 { 1290 continue; 1291 } 1292 $methods[] = $type; 1293 } 1294 1295 $dir = $phpbb_root_path . 'store/'; 1296 $dh = opendir($dir); 1297 while (($file = readdir($dh)) !== false) 1298 { 1299 if (preg_match('#^backup_(\d{10,})\.(sql(?:\.(?:gz|bz2))?)$#', $file, $matches)) 1300 { 1301 $supported = in_array($matches[2], $methods); 1302 1303 if ($supported == 'true') 1304 { 1305 $template->assign_block_vars('files', array( 1306 'FILE' => $file, 1307 'NAME' => gmdate("d-m-Y H:i:s", $matches[1]), 1308 'SUPPORTED' => $supported 1309 )); 1310 $selected = true; 1311 } 1312 } 1313 } 1314 closedir($dh); 1315 1316 if ($selected === true) 1317 { 1318 $template->assign_var('EXISTS', true); 1319 } 1320 1321 $template->assign_vars(array( 1322 'U_ACTION' => $this->u_action . '&action=submit' 1323 )); 1324 break; 1325 } 1326 break; 1327 } 1328 } 1329 1330 /** 1331 * Return table structure 1332 */ 1333 function get_table_structure($table_name) 1334 { 1335 global $db, $domains_created; 1336 1337 $sql_data = ''; 1338 1339 switch ($db->sql_layer) 1340 { 1341 case 'mysqli': 1342 case 'mysql4': 1343 case 'mysql': 1344 1345 $sql_data .= "CREATE TABLE $table_name(\n"; 1346 $rows = array(); 1347 1348 $sql = "SHOW FIELDS 1349 FROM $table_name"; 1350 $result = $db->sql_query($sql); 1351 1352 while ($row = $db->sql_fetchrow($result)) 1353 { 1354 $line = ' ' . $row['Field'] . ' ' . $row['Type']; 1355 1356 if (!is_null($row['Default'])) 1357 { 1358 $line .= " DEFAULT '{$row['Default']}'"; 1359 } 1360 1361 if ($row['Null'] != 'YES') 1362 { 1363 $line .= ' NOT NULL'; 1364 } 1365 1366 if ($row['Extra'] != '') 1367 { 1368 $line .= ' ' . $row['Extra']; 1369 } 1370 1371 $rows[] = $line; 1372 } 1373 $db->sql_freeresult($result); 1374 1375 $sql = "SHOW KEYS 1376 FROM $table_name"; 1377 1378 $result = $db->sql_query($sql); 1379 1380 $index = array(); 1381 while ($row = $db->sql_fetchrow($result)) 1382 { 1383 $kname = $row['Key_name']; 1384 1385 if ($kname != 'PRIMARY') 1386 { 1387 if ($row['Index_type'] == 'FULLTEXT') 1388 { 1389 $kname = "FULLTEXT|$kname"; 1390 } 1391 else if ($row['Non_unique'] == 0) 1392 { 1393 $kname = "UNIQUE|$kname"; 1394 } 1395 } 1396 1397 if ($row['Sub_part']) 1398 { 1399 $row['Column_name'] .= '(' . $row['Sub_part'] . ')'; 1400 } 1401 $index[$kname][] = $row['Column_name']; 1402 } 1403 $db->sql_freeresult($result); 1404 1405 foreach ($index as $key => $columns) 1406 { 1407 $line = ' '; 1408 1409 if ($key == 'PRIMARY') 1410 { 1411 $line .= 'PRIMARY KEY (' . implode(', ', $columns) . ')'; 1412 } 1413 else if (strpos($key, 'UNIQUE') === 0) 1414 { 1415 $line .= 'UNIQUE ' . substr($key, 7) . ' (' . implode(', ', $columns) . ')'; 1416 } 1417 else if (strpos($key, 'FULLTEXT') === 0) 1418 { 1419 $line .= 'FULLTEXT ' . substr($key, 9) . ' (' . implode(', ', $columns) . ')'; 1420 } 1421 else 1422 { 1423 $line .= "KEY $key (" . implode(', ', $columns) . ')'; 1424 } 1425 1426 $rows[] = $line; 1427 } 1428 1429 $sql_data .= implode(",\n", $rows); 1430 $sql_data .= "\n);\n\n"; 1431 1432 break; 1433 1434 case 'sqlite': 1435 1436 $sql = "SELECT sql 1437 FROM sqlite_master 1438 WHERE type = 'table' 1439 AND name = '" . $db->sql_escape($table_name) . "' 1440 ORDER BY type DESC, name;"; 1441 $result = $db->sql_query($sql); 1442 $row = $db->sql_fetchrow($result); 1443 $db->sql_freeresult($result); 1444 1445 // Create Table 1446 $sql_data .= $row['sql'] . "\n"; 1447 1448 $result = $db->sql_query("PRAGMA index_list('" . $db->sql_escape($table_name) . "');"); 1449 1450 $ar = array(); 1451 while ($row = $db->sql_fetchrow($result)) 1452 { 1453 $ar[] = $row; 1454 } 1455 $db->sql_freeresult($result); 1456 1457 foreach ($ar as $value) 1458 { 1459 if (strpos($value['name'], 'autoindex') !== false) 1460 { 1461 continue; 1462 } 1463 1464 $result = $db->sql_query("PRAGMA index_info('" . $db->sql_escape($value['name']) . "');"); 1465 1466 $fields = array(); 1467 while ($row = $db->sql_fetchrow($result)) 1468 { 1469 $fields[] = $row['name']; 1470 } 1471 $db->sql_freeresult($result); 1472 1473 $sql_data .= 'CREATE ' . ($value['unique'] ? 'UNIQUE ' : '') . 'INDEX ' . $value['name'] . ' on ' . $table_name . ' (' . implode(', ', $fields) . ");\n"; 1474 } 1475 1476 $sql_data .= "\n"; 1477 break; 1478 1479 case 'postgres': 1480 if (empty($domains_created)) 1481 { 1482 $domains_created = array(); 1483 } 1484 1485 $sql = "SELECT a.domain_name, a.data_type, a.character_maximum_length, a.domain_default 1486 FROM INFORMATION_SCHEMA.domains a, INFORMATION_SCHEMA.column_domain_usage b 1487 WHERE a.domain_name = b.domain_name 1488 AND b.table_name = '{$table_name}'"; 1489 $result = $db->sql_query($sql); 1490 while ($row = $db->sql_fetchrow($result)) 1491 { 1492 if (empty($domains_created[$row['domain_name']])) 1493 { 1494 $domains_created[$row['domain_name']] = true; 1495 $sql_data .= "CREATE DOMAIN {$row['domain_name']} as {$row['data_type']}"; 1496 if (!empty($row['character_maximum_length'])) 1497 { 1498 $sql_data .= '(' . $row['character_maximum_length'] . ')'; 1499 } 1500 $sql_data .= ' NOT NULL'; 1501 if (!empty($row['domain_default'])) 1502 { 1503 $sql_data .= 'DEFAULT ' . $row['domain_default']; 1504 } 1505 $sql_data .= ";\n"; 1506 } 1507 } 1508 1509 // PGSQL does not "tightly" bind sequences and tables, we must guess... 1510 $sql = "SELECT relname 1511 FROM pg_class 1512 WHERE relkind = 'S' 1513 AND relname = '{$table_name}_seq'"; 1514 $result = $db->sql_query($sql); 1515 // We don't even care about storing the results. We already know the answer if we get rows back. 1516 if ($db->sql_fetchrow($result)) 1517 { 1518 $sql_data .= "DROP SEQUENCE {$table_name}_seq;\n"; 1519 $sql_data .= "CREATE SEQUENCE {$table_name}_seq;\n"; 1520 } 1521 $db->sql_freeresult($result); 1522 1523 $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 1524 FROM pg_class c, pg_attribute a, pg_type t 1525 WHERE c.relname = '" . $db->sql_escape($table_name) . "' 1526 AND a.attnum > 0 1527 AND a.attrelid = c.oid 1528 AND a.atttypid = t.oid 1529 ORDER BY a.attnum"; 1530 $result = $db->sql_query($field_query); 1531 1532 $sql_data .= "CREATE TABLE $table_name(\n"; 1533 $lines = array(); 1534 while ($row = $db->sql_fetchrow($result)) 1535 { 1536 // Get the data from the table 1537 $sql_get_default = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault 1538 FROM pg_attrdef d, pg_class c 1539 WHERE (c.relname = '" . $db->sql_escape($table_name) . "') 1540 AND (c.oid = d.adrelid) 1541 AND d.adnum = " . $row['attnum']; 1542 $def_res = $db->sql_query($sql_get_default); 1543 1544 if (!$def_res) 1545 { 1546 unset($row['rowdefault']); 1547 } 1548 else 1549 { 1550 $row['rowdefault'] = $db->sql_fetchfield('rowdefault', 0, $def_res); 1551 } 1552 $db->sql_freeresult($def_res); 1553 1554 if ($row['type'] == 'bpchar') 1555 { 1556 // Internally stored as bpchar, but isn't accepted in a CREATE TABLE statement. 1557 $row['type'] = 'char'; 1558 } 1559 1560 $line = ' ' . $row['field'] . ' ' . $row['type']; 1561 1562 if (strpos($row['type'], 'char') !== false) 1563 { 1564 if ($row['lengthvar'] > 0) 1565 { 1566 $line .= '(' . ($row['lengthvar'] - 4) . ')'; 1567 } 1568 } 1569 1570 if (strpos($row['type'], 'numeric') !== false) 1571 { 1572 $line .= '('; 1573 $line .= sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff)); 1574 $line .= ')'; 1575 } 1576 1577 if (!empty($row['rowdefault'])) 1578 { 1579 $line .= ' DEFAULT ' . $row['rowdefault']; 1580 } 1581 1582 if ($row['notnull'] == 't') 1583 { 1584 $line .= ' NOT NULL'; 1585 } 1586 1587 $lines[] = $line; 1588 } 1589 $db->sql_freeresult($result); 1590 1591 1592 // Get the listing of primary keys. 1593 $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 1594 FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia 1595 WHERE (bc.oid = i.indrelid) 1596 AND (ic.oid = i.indexrelid) 1597 AND (ia.attrelid = i.indexrelid) 1598 AND (ta.attrelid = bc.oid) 1599 AND (bc.relname = '" . $db->sql_escape($table_name) . "') 1600 AND (ta.attrelid = i.indrelid) 1601 AND (ta.attnum = i.indkey[ia.attnum-1]) 1602 ORDER BY index_name, tab_name, column_name"; 1603 1604 $result = $db->sql_query($sql_pri_keys); 1605 1606 $index_create = $index_rows = $primary_key = array(); 1607 1608 // We do this in two steps. It makes placing the comma easier 1609 while ($row = $db->sql_fetchrow($result)) 1610 { 1611 if ($row['primary_key'] == 't') 1612 { 1613 $primary_key[] = $row['column_name']; 1614 $primary_key_name = $row['index_name']; 1615 } 1616 else 1617 { 1618 // We have to store this all this info because it is possible to have a multi-column key... 1619 // we can loop through it again and build the statement 1620 $index_rows[$row['index_name']]['table'] = $table_name; 1621 $index_rows[$row['index_name']]['unique'] = ($row['unique_key'] == 't') ? true : false; 1622 $index_rows[$row['index_name']]['column_names'][] = $row['column_name']; 1623 } 1624 } 1625 $db->sql_freeresult($result); 1626 1627 if (!empty($index_rows)) 1628 { 1629 foreach ($index_rows as $idx_name => $props) 1630 { 1631 $index_create[] = 'CREATE ' . ($props['unique'] ? 'UNIQUE ' : '') . "INDEX $idx_name ON $table_name (" . implode(', ', $props['column_names']) . ");"; 1632 } 1633 } 1634 1635 if (!empty($primary_key)) 1636 { 1637 $lines[] = " CONSTRAINT $primary_key_name PRIMARY KEY (" . implode(', ', $primary_key) . ")"; 1638 } 1639 1640 // Generate constraint clauses for CHECK constraints 1641 $sql_checks = "SELECT conname as index_name, consrc 1642 FROM pg_constraint, pg_class bc 1643 WHERE conrelid = bc.oid 1644 AND bc.relname = '" . $db->sql_escape($table_name) . "' 1645 AND NOT EXISTS ( 1646 SELECT * 1647 FROM pg_constraint as c, pg_inherits as i 1648 WHERE i.inhrelid = pg_constraint.conrelid 1649 AND c.conname = pg_constraint.conname 1650 AND c.consrc = pg_constraint.consrc 1651 AND c.conrelid = i.inhparent 1652 )"; 1653 $result = $db->sql_query($sql_checks); 1654 1655 // Add the constraints to the sql file. 1656 while ($row = $db->sql_fetchrow($result)) 1657 { 1658 if (!is_null($row['consrc'])) 1659 { 1660 $lines[] = ' CONSTRAINT ' . $row['index_name'] . ' CHECK ' . $row['consrc']; 1661 } 1662 } 1663 $db->sql_freeresult($result); 1664 1665 $sql_data .= implode(", \n", $lines); 1666 $sql_data .= "\n);\n"; 1667 1668 if (!empty($index_create)) 1669 { 1670 $sql_data .= implode("\n", $index_create) . "\n\n"; 1671 } 1672 break; 1673 1674 case 'mssql': 1675 case 'mssql_odbc': 1676 $sql_data .= "\nCREATE TABLE [$table_name] (\n"; 1677 $rows = array(); 1678 1679 $text_flag = false; 1680 1681 $sql = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY 1682 FROM INFORMATION_SCHEMA.COLUMNS 1683 WHERE TABLE_NAME = '$table_name'"; 1684 $result = $db->sql_query($sql); 1685 1686 while ($row = $db->sql_fetchrow($result)) 1687 { 1688 $line = "\t[{$row['COLUMN_NAME']}] [{$row['DATA_TYPE']}]"; 1689 1690 if ($row['DATA_TYPE'] == 'text') 1691 { 1692 $text_flag = true; 1693 } 1694 1695 if ($row['IS_IDENTITY']) 1696 { 1697 $line .= ' IDENTITY (1 , 1)'; 1698 } 1699 1700 if ($row['CHARACTER_MAXIMUM_LENGTH'] && $row['DATA_TYPE'] !== 'text') 1701 { 1702 $line .= ' (' . $row['CHARACTER_MAXIMUM_LENGTH'] . ')'; 1703 } 1704 1705 if ($row['IS_NULLABLE'] == 'YES') 1706 { 1707 $line .= ' NULL'; 1708 } 1709 else 1710 { 1711 $line .= ' NOT NULL'; 1712 } 1713 1714 if ($row['COLUMN_DEFAULT']) 1715 { 1716 $line .= ' DEFAULT ' . $row['COLUMN_DEFAULT']; 1717 } 1718 1719 $rows[] = $line; 1720 } 1721 $db->sql_freeresult($result); 1722 1723 $sql_data .= implode(",\n", $rows); 1724 $sql_data .= "\n) ON [PRIMARY]"; 1725 1726 if ($text_flag) 1727 { 1728 $sql_data .= " TEXTIMAGE_ON [PRIMARY]"; 1729 } 1730 1731 $sql_data .= "\nGO\n\n"; 1732 $rows = array(); 1733 1734 $sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME 1735 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 1736 WHERE TABLE_NAME = '$table_name'"; 1737 $result = $db->sql_query($sql); 1738 while ($row = $db->sql_fetchrow($result)) 1739 { 1740 if (!sizeof($rows)) 1741 { 1742 $sql_data .= "ALTER TABLE [$table_name] WITH NOCHECK ADD\n"; 1743 $sql_data .= "\tCONSTRAINT [{$row['CONSTRAINT_NAME']}] PRIMARY KEY CLUSTERED \n\t(\n"; 1744 } 1745 $rows[] = "\t\t[{$row['COLUMN_NAME']}]"; 1746 } 1747 if (sizeof($rows)) 1748 { 1749 $sql_data .= implode(",\n", $rows); 1750 $sql_data .= "\n\t) ON [PRIMARY] \nGO\n"; 1751 } 1752 $db->sql_freeresult($result); 1753 1754 $index = array(); 1755 $sql = "EXEC sp_statistics '$table_name'"; 1756 $result = $db->sql_query($sql); 1757 while ($row = $db->sql_fetchrow($result)) 1758 { 1759 if ($row['TYPE'] == 3) 1760 { 1761 $index[$row['INDEX_NAME']][] = '[' . $row['COLUMN_NAME'] . ']'; 1762 } 1763 } 1764 $db->sql_freeresult($result); 1765 1766 foreach ($index as $index_name => $column_name) 1767 { 1768 $index[$index_name] = implode(', ', $index[$index_name]); 1769 } 1770 1771 foreach ($index as $index_name => $columns) 1772 { 1773 $sql_data .= "\nCREATE INDEX [$index_name] ON [$table_name]($columns) ON [PRIMARY]\nGO\n"; 1774 } 1775 break; 1776 1777 case 'firebird': 1778 1779 $data_types = array(7 => 'SMALLINT', 8 => 'INTEGER', 10 => 'FLOAT', 12 => 'DATE', 13 => 'TIME', 14 => 'CHARACTER', 27 => 'DOUBLE PRECISION', 35 => 'TIMESTAMP', 37 => 'VARCHAR', 40 => 'CSTRING', 261 => 'BLOB', 701 => 'DECIMAL', 702 => 'NUMERIC'); 1780 1781 $sql_data .= "\nCREATE TABLE $table_name (\n"; 1782 1783 $sql = 'SELECT DISTINCT R.RDB$FIELD_NAME as FNAME, R.RDB$NULL_FLAG as NFLAG, R.RDB$DEFAULT_SOURCE as DSOURCE, F.RDB$FIELD_TYPE as FTYPE, F.RDB$FIELD_SUB_TYPE as STYPE, F.RDB$FIELD_LENGTH as FLEN 1784 FROM RDB$RELATION_FIELDS R 1785 JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME 1786 LEFT JOIN RDB$FIELD_DIMENSIONS D ON R.RDB$FIELD_SOURCE = D.RDB$FIELD_NAME 1787 WHERE F.RDB$SYSTEM_FLAG = 0 1788 AND R.RDB$RELATION_NAME = \''. $table_name . '\' 1789 ORDER BY R.RDB$FIELD_POSITION'; 1790 $result = $db->sql_query($sql); 1791 1792 $rows = array(); 1793 while ($row = $db->sql_fetchrow($result)) 1794 { 1795 $line = "\t" . '"' . $row['fname'] . '" ' . $data_types[$row['ftype']]; 1796 1797 if ($row['ftype'] == 261 && $row['stype'] == 1) 1798 { 1799 $line .= ' SUB_TYPE TEXT'; 1800 } 1801 1802 if ($row['ftype'] == 37 || $row['ftype'] == 14) 1803 { 1804 $line .= ' (' . $row['flen'] . ')'; 1805 } 1806 1807 if (!empty($row['dsource'])) 1808 { 1809 $line .= ' ' . $row['dsource']; 1810 } 1811 1812 if (!empty($row['nflag'])) 1813 { 1814 $line .= ' NOT NULL'; 1815 } 1816 $rows[] = $line; 1817 } 1818 $db->sql_freeresult($result); 1819 1820 $sql_data .= implode(",\n", $rows); 1821 $sql_data .= "\n);;\n"; 1822 $keys = array(); 1823 1824 $sql = 'SELECT I.RDB$FIELD_NAME as NAME 1825 FROM RDB$RELATION_CONSTRAINTS RC, RDB$INDEX_SEGMENTS I, RDB$INDICES IDX 1826 WHERE (I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME) 1827 AND (IDX.RDB$INDEX_NAME = RC.RDB$INDEX_NAME) 1828 AND (RC.RDB$RELATION_NAME = \''. $table_name . '\') 1829 ORDER BY I.RDB$FIELD_POSITION'; 1830 $result = $db->sql_query($sql); 1831 1832 while ($row = $db->sql_fetchrow($result)) 1833 { 1834 $keys[] = $row['name']; 1835 } 1836 1837 if (sizeof($keys)) 1838 { 1839 $sql_data .= "\nALTER TABLE $table_name ADD PRIMARY KEY (" . implode(', ', $keys) . ');;'; 1840 } 1841 1842 $db->sql_freeresult($result); 1843 1844 $sql = 'SELECT I.RDB$INDEX_NAME as INAME, I.RDB$UNIQUE_FLAG as UFLAG, S.RDB$FIELD_NAME as FNAME 1845 FROM RDB$INDICES I JOIN RDB$INDEX_SEGMENTS S ON S.RDB$INDEX_NAME=I.RDB$INDEX_NAME 1846 WHERE (I.RDB$SYSTEM_FLAG IS NULL OR I.RDB$SYSTEM_FLAG=0) 1847 AND I.RDB$FOREIGN_KEY IS NULL 1848 AND I.RDB$RELATION_NAME = \''. $table_name . '\' 1849 AND I.RDB$INDEX_NAME NOT STARTING WITH \'RDB$\' 1850 ORDER BY S.RDB$FIELD_POSITION'; 1851 $result = $db->sql_query($sql); 1852 1853 $index = array(); 1854 while ($row = $db->sql_fetchrow($result)) 1855 { 1856 $index[$row['iname']]['unique'] = !empty($row['uflag']); 1857 $index[$row['iname']]['values'][] = $row['fname']; 1858 } 1859 1860 foreach ($index as $index_name => $data) 1861 { 1862 $sql_data .= "\nCREATE "; 1863 if ($data['unique']) 1864 { 1865 $sql_data .= 'UNIQUE '; 1866 } 1867 $sql_data .= "INDEX $index_name ON $table_name(" . implode(', ', $data['values']) . ");;"; 1868 } 1869 $sql_data .= "\n"; 1870 1871 $db->sql_freeresult($result); 1872 1873 $sql = 'SELECT D1.RDB$DEPENDENT_NAME as DNAME, D1.RDB$FIELD_NAME as FNAME, D1.RDB$DEPENDENT_TYPE, R1.RDB$RELATION_NAME 1874 FROM RDB$DEPENDENCIES D1 1875 LEFT JOIN RDB$RELATIONS R1 ON ((D1.RDB$DEPENDENT_NAME = R1.RDB$RELATION_NAME) AND (NOT (R1.RDB$VIEW_BLR IS NULL))) 1876 WHERE (D1.RDB$DEPENDED_ON_TYPE = 0) 1877 AND (D1.RDB$DEPENDENT_TYPE <> 3) 1878 AND (D1.RDB$DEPENDED_ON_NAME = \'' . $table_name . '\') 1879 UNION SELECT DISTINCT F2.RDB$RELATION_NAME, D2.RDB$FIELD_NAME, D2.RDB$DEPENDENT_TYPE, R2.RDB$RELATION_NAME FROM RDB$DEPENDENCIES D2, RDB$RELATION_FIELDS F2 1880 LEFT JOIN RDB$RELATIONS R2 ON ((F2.RDB$RELATION_NAME = R2.RDB$RELATION_NAME) AND (NOT (R2.RDB$VIEW_BLR IS NULL))) 1881 WHERE (D2.RDB$DEPENDENT_TYPE = 3) 1882 AND (D2.RDB$DEPENDENT_NAME = F2.RDB$FIELD_SOURCE) 1883 AND (D2.RDB$DEPENDED_ON_NAME = \'' . $table_name . '\') 1884 ORDER BY 1, 2'; 1885 $result = $db->sql_query($sql); 1886 while ($row = $db->sql_fetchrow($result)) 1887 { 1888 $sql = 'SELECT T1.RDB$DEPENDED_ON_NAME as GEN, T1.RDB$FIELD_NAME, T1.RDB$DEPENDED_ON_TYPE 1889 FROM RDB$DEPENDENCIES T1 1890 WHERE (T1.RDB$DEPENDENT_NAME = \'' . $row['dname'] . '\') 1891 AND (T1.RDB$DEPENDENT_TYPE = 2 AND T1.RDB$DEPENDED_ON_TYPE = 14) 1892 UNION ALL SELECT DISTINCT D.RDB$DEPENDED_ON_NAME, D.RDB$FIELD_NAME, D.RDB$DEPENDED_ON_TYPE 1893 FROM RDB$DEPENDENCIES D, RDB$RELATION_FIELDS F 1894 WHERE (D.RDB$DEPENDENT_TYPE = 3) 1895 AND (D.RDB$DEPENDENT_NAME = F.RDB$FIELD_SOURCE) 1896 AND (F.RDB$RELATION_NAME = \'' . $row['dname'] . '\') 1897 ORDER BY 1,2'; 1898 $result2 = $db->sql_query($sql); 1899 $row2 = $db->sql_fetchrow($result2); 1900 $db->sql_freeresult($result2); 1901 $gen_name = $row2['gen']; 1902 1903 $sql_data .= "\nCREATE GENERATOR " . $gen_name . ";;"; 1904 $sql_data .= "\nSET GENERATOR " . $gen_name . " TO 0;;\n"; 1905 $sql_data .= "\nCREATE TRIGGER {$row['dname']} FOR $table_name"; 1906 $sql_data .= "\nBEFORE INSERT\nAS\nBEGIN"; 1907 $sql_data .= "\n NEW.{$row['fname']} = GEN_ID(" . $gen_name . ", 1);"; 1908 $sql_data .= "\nEND;;\n"; 1909 } 1910 1911 $db->sql_freeresult($result); 1912 break; 1913 1914 case 'oracle': 1915 $sql_data .= "\nCREATE TABLE $table_name (\n"; 1916 1917 $sql = "SELECT COLUMN_NAME, DATA_TYPE, DATA_PRECISION, DATA_LENGTH, NULLABLE, DATA_DEFAULT 1918 FROM ALL_TAB_COLS 1919 WHERE table_name = '{$table_name}'"; 1920 $result = $db->sql_query($sql); 1921 1922 $rows = array(); 1923 while ($row = $db->sql_fetchrow($result)) 1924 { 1925 $line = ' "' . $row['column_name'] . '" ' . $row['data_type']; 1926 1927 if ($row['data_type'] !== 'CLOB') 1928 { 1929 if ($row['data_type'] !== 'VARCHAR2') 1930 { 1931 $line .= '(' . $row['data_precision'] . ')'; 1932 } 1933 else 1934 { 1935 $line .= '(' . $row['data_length'] . ')'; 1936 } 1937 } 1938 1939 if (!empty($row['data_default'])) 1940 { 1941 $line .= ' DEFAULT ' . $row['data_default']; 1942 } 1943 1944 if ($row['nullable'] == 'N') 1945 { 1946 $line .= ' NOT NULL'; 1947 } 1948 $rows[] = $line; 1949 } 1950 $db->sql_freeresult($result); 1951 1952 $sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME 1953 FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B 1954 WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME 1955 AND B.CONSTRAINT_TYPE = 'P' 1956 AND A.TABLE_NAME = '{$table_name}'"; 1957 $result = $db->sql_query($sql); 1958 1959 while ($row = $db->sql_fetchrow($result)) 1960 { 1961 $rows[] = " CONSTRAINT {$row['constraint_name']} PRIMARY KEY ({$row['column_name']})"; 1962 } 1963 $db->sql_freeresult($result); 1964 1965 $sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME 1966 FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B 1967 WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME 1968 AND B.CONSTRAINT_TYPE = 'U' 1969 AND A.TABLE_NAME = '{$table_name}'"; 1970 $result = $db->sql_query($sql); 1971 1972 while ($row = $db->sql_fetchrow($result)) 1973 { 1974 $rows[] = " CONSTRAINT {$row['constraint_name']} UNIQUE ({$row['column_name']})"; 1975 } 1976 $db->sql_freeresult($result); 1977 1978 $sql_data .= implode(",\n", $rows); 1979 $sql_data .= "\n)\n\\"; 1980 1981 $sql = "SELECT A.REFERENCED_NAME 1982 FROM USER_DEPENDENCIES A, USER_TRIGGERS B 1983 WHERE A.REFERENCED_TYPE = 'SEQUENCE' 1984 AND A.NAME = B.TRIGGER_NAME 1985 AND B. TABLE_NAME = '{$table_name}'"; 1986 $result = $db->sql_query($sql); 1987 while ($row = $db->sql_fetchrow($result)) 1988 { 1989 $sql_data .= "\nCREATE SEQUENCE {$row['referenced_name']}\\\n"; 1990 } 1991 $db->sql_freeresult($result); 1992 1993 $sql = "SELECT DESCRIPTION, WHEN_CLAUSE, TRIGGER_BODY 1994 FROM USER_TRIGGERS 1995 WHERE TABLE_NAME = '{$table_name}'"; 1996 $result = $db->sql_query($sql); 1997 while ($row = $db->sql_fetchrow($result)) 1998 { 1999 $sql_data .= "\nCREATE OR REPLACE TRIGGER {$row['description']}WHEN ({$row['when_clause']})\n{$row['trigger_body']}\\"; 2000 } 2001 $db->sql_freeresult($result); 2002 2003 $sql = "SELECT A.INDEX_NAME, B.COLUMN_NAME 2004 FROM USER_INDEXES A, USER_IND_COLUMNS B 2005 WHERE A.UNIQUENESS = 'NONUNIQUE' 2006 AND A.INDEX_NAME = B.INDEX_NAME 2007 AND B.TABLE_NAME = '{$table_name}'"; 2008 $result = $db->sql_query($sql); 2009 2010 $index = array(); 2011 2012 while ($row = $db->sql_fetchrow($result)) 2013 { 2014 $index[$row['index_name']][] = $row['column_name']; 2015 } 2016 2017 foreach ($index as $index_name => $column_names) 2018 { 2019 $sql_data .= "\nCREATE INDEX $index_name ON $table_name(" . implode(', ', $column_names) . ")\n\\"; 2020 } 2021 $db->sql_freeresult($result); 2022 2023 break; 2024 } 2025 2026 return $sql_data; 2027 } 2028 } 2029 2030 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Nov 22 00:35:05 2006 | Cross-referenced by PHPXref 0.6 |