[ Index ]

PHP Cross Reference of phpBB-3.3.7-deutsch

title

Body

[close]

/phpbb/db/extractor/ -> mssql_extractor.php (source)

   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  }


Generated: Thu Mar 24 21:31:15 2022 Cross-referenced by PHPXref 0.7.1