[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 oracle_extractor extends base_extractor
  19  {
  20      /**
  21      * {@inheritdoc}
  22      */
  23  	public function write_table($table_name)
  24      {
  25          if (!$this->is_initialized)
  26          {
  27              throw new extractor_not_initialized_exception();
  28          }
  29  
  30          $sql_data = '-- Table: ' . $table_name . "\n";
  31          $sql_data .= "DROP TABLE $table_name\n/\n";
  32          $sql_data .= "\nCREATE TABLE $table_name (\n";
  33  
  34          $sql = "SELECT COLUMN_NAME, DATA_TYPE, DATA_PRECISION, DATA_LENGTH, NULLABLE, DATA_DEFAULT
  35              FROM ALL_TAB_COLS
  36              WHERE table_name = '{$table_name}'";
  37          $result = $this->db->sql_query($sql);
  38  
  39          $rows = array();
  40          while ($row = $this->db->sql_fetchrow($result))
  41          {
  42              $line = '  "' . $row['column_name'] . '" ' . $row['data_type'];
  43  
  44              if ($row['data_type'] !== 'CLOB')
  45              {
  46                  if ($row['data_type'] !== 'VARCHAR2' && $row['data_type'] !== 'CHAR')
  47                  {
  48                      $line .= '(' . $row['data_precision'] . ')';
  49                  }
  50                  else
  51                  {
  52                      $line .= '(' . $row['data_length'] . ')';
  53                  }
  54              }
  55  
  56              if (!empty($row['data_default']))
  57              {
  58                  $line .= ' DEFAULT ' . $row['data_default'];
  59              }
  60  
  61              if ($row['nullable'] == 'N')
  62              {
  63                  $line .= ' NOT NULL';
  64              }
  65              $rows[] = $line;
  66          }
  67          $this->db->sql_freeresult($result);
  68  
  69          $sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME
  70              FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
  71              WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
  72                  AND B.CONSTRAINT_TYPE = 'P'
  73                  AND A.TABLE_NAME = '{$table_name}'";
  74          $result = $this->db->sql_query($sql);
  75  
  76          $primary_key = array();
  77          $constraint_name = '';
  78          while ($row = $this->db->sql_fetchrow($result))
  79          {
  80              $constraint_name = '"' . $row['constraint_name'] . '"';
  81              $primary_key[] = '"' . $row['column_name'] . '"';
  82          }
  83          $this->db->sql_freeresult($result);
  84  
  85          if (count($primary_key))
  86          {
  87              $rows[] = "  CONSTRAINT {$constraint_name} PRIMARY KEY (" . implode(', ', $primary_key) . ')';
  88          }
  89  
  90          $sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME
  91              FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
  92              WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
  93                  AND B.CONSTRAINT_TYPE = 'U'
  94                  AND A.TABLE_NAME = '{$table_name}'";
  95          $result = $this->db->sql_query($sql);
  96  
  97          $unique = array();
  98          $constraint_name = '';
  99          while ($row = $this->db->sql_fetchrow($result))
 100          {
 101              $constraint_name = '"' . $row['constraint_name'] . '"';
 102              $unique[] = '"' . $row['column_name'] . '"';
 103          }
 104          $this->db->sql_freeresult($result);
 105  
 106          if (count($unique))
 107          {
 108              $rows[] = "  CONSTRAINT {$constraint_name} UNIQUE (" . implode(', ', $unique) . ')';
 109          }
 110  
 111          $sql_data .= implode(",\n", $rows);
 112          $sql_data .= "\n)\n/\n";
 113  
 114          $sql = "SELECT A.REFERENCED_NAME, C.*
 115              FROM USER_DEPENDENCIES A, USER_TRIGGERS B, USER_SEQUENCES C
 116              WHERE A.REFERENCED_TYPE = 'SEQUENCE'
 117                  AND A.NAME = B.TRIGGER_NAME
 118                  AND B.TABLE_NAME = '{$table_name}'
 119                  AND C.SEQUENCE_NAME = A.REFERENCED_NAME";
 120          $result = $this->db->sql_query($sql);
 121  
 122          $type = $this->request->variable('type', '');
 123  
 124          while ($row = $this->db->sql_fetchrow($result))
 125          {
 126              $sql_data .= "\nDROP SEQUENCE \"{$row['referenced_name']}\"\n/\n";
 127              $sql_data .= "\nCREATE SEQUENCE \"{$row['referenced_name']}\"";
 128  
 129              if ($type == 'full')
 130              {
 131                  $sql_data .= ' START WITH ' . $row['last_number'];
 132              }
 133  
 134              $sql_data .= "\n/\n";
 135          }
 136          $this->db->sql_freeresult($result);
 137  
 138          $sql = "SELECT DESCRIPTION, WHEN_CLAUSE, TRIGGER_BODY
 139              FROM USER_TRIGGERS
 140              WHERE TABLE_NAME = '{$table_name}'";
 141          $result = $this->db->sql_query($sql);
 142          while ($row = $this->db->sql_fetchrow($result))
 143          {
 144              $sql_data .= "\nCREATE OR REPLACE TRIGGER {$row['description']}WHEN ({$row['when_clause']})\n{$row['trigger_body']}\n/\n";
 145          }
 146          $this->db->sql_freeresult($result);
 147  
 148          $sql = "SELECT A.INDEX_NAME, B.COLUMN_NAME
 149              FROM USER_INDEXES A, USER_IND_COLUMNS B
 150              WHERE A.UNIQUENESS = 'NONUNIQUE'
 151                  AND A.INDEX_NAME = B.INDEX_NAME
 152                  AND B.TABLE_NAME = '{$table_name}'";
 153          $result = $this->db->sql_query($sql);
 154  
 155          $index = array();
 156  
 157          while ($row = $this->db->sql_fetchrow($result))
 158          {
 159              $index[$row['index_name']][] = $row['column_name'];
 160          }
 161  
 162          foreach ($index as $index_name => $column_names)
 163          {
 164              $sql_data .= "\nCREATE INDEX $index_name ON $table_name(" . implode(', ', $column_names) . ")\n/\n";
 165          }
 166          $this->db->sql_freeresult($result);
 167          $this->flush($sql_data);
 168      }
 169  
 170      /**
 171      * {@inheritdoc}
 172      */
 173  	public function write_data($table_name)
 174      {
 175          if (!$this->is_initialized)
 176          {
 177              throw new extractor_not_initialized_exception();
 178          }
 179  
 180          $ary_type = $ary_name = array();
 181  
 182          // Grab all of the data from current table.
 183          $sql = "SELECT *
 184              FROM $table_name";
 185          $result = $this->db->sql_query($sql);
 186  
 187          $i_num_fields = ocinumcols($result);
 188  
 189          for ($i = 0; $i < $i_num_fields; $i++)
 190          {
 191              $ary_type[$i] = ocicolumntype($result, $i + 1);
 192              $ary_name[$i] = ocicolumnname($result, $i + 1);
 193          }
 194  
 195          while ($row = $this->db->sql_fetchrow($result))
 196          {
 197              $schema_vals = $schema_fields = array();
 198  
 199              // Build the SQL statement to recreate the data.
 200              for ($i = 0; $i < $i_num_fields; $i++)
 201              {
 202                  // Oracle uses uppercase - we use lowercase
 203                  $str_val = $row[strtolower($ary_name[$i])];
 204  
 205                  if (preg_match('#char|text|bool|raw|clob#i', $ary_type[$i]))
 206                  {
 207                      $str_quote = '';
 208                      $str_empty = "''";
 209                      $str_val = sanitize_data_oracle($str_val);
 210                  }
 211                  else if (preg_match('#date|timestamp#i', $ary_type[$i]))
 212                  {
 213                      if (empty($str_val))
 214                      {
 215                          $str_quote = '';
 216                      }
 217                      else
 218                      {
 219                          $str_quote = "'";
 220                      }
 221                  }
 222                  else
 223                  {
 224                      $str_quote = '';
 225                      $str_empty = 'NULL';
 226                  }
 227  
 228                  if (empty($str_val) && $str_val !== '0')
 229                  {
 230                      $str_val = $str_empty;
 231                  }
 232  
 233                  $schema_vals[$i] = $str_quote . $str_val . $str_quote;
 234                  $schema_fields[$i] = '"' . $ary_name[$i] . '"';
 235              }
 236  
 237              // Take the ordered fields and their associated data and build it
 238              // into a valid sql statement to recreate that field in the data.
 239              $sql_data = "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ")\n/\n";
 240  
 241              $this->flush($sql_data);
 242          }
 243          $this->db->sql_freeresult($result);
 244      }
 245  
 246      /**
 247      * {@inheritdoc}
 248      */
 249  	public function write_start($table_prefix)
 250      {
 251          if (!$this->is_initialized)
 252          {
 253              throw new extractor_not_initialized_exception();
 254          }
 255  
 256          $sql_data = "--\n";
 257          $sql_data .= "-- phpBB Backup Script\n";
 258          $sql_data .= "-- Dump of tables for $table_prefix\n";
 259          $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n";
 260          $sql_data .= "--\n";
 261          $this->flush($sql_data);
 262      }
 263  }