[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 postgres_extractor extends base_extractor
  19  {
  20      /**
  21      * {@inheritdoc}
  22      */
  23  	public function write_start($table_prefix)
  24      {
  25          if (!$this->is_initialized)
  26          {
  27              throw new extractor_not_initialized_exception();
  28          }
  29  
  30          $sql_data = "--\n";
  31          $sql_data .= "-- phpBB Backup Script\n";
  32          $sql_data .= "-- Dump of tables for $table_prefix\n";
  33          $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n";
  34          $sql_data .= "--\n";
  35          $sql_data .= "BEGIN TRANSACTION;\n";
  36          $this->flush($sql_data);
  37      }
  38  
  39      /**
  40      * {@inheritdoc}
  41      */
  42  	public function write_table($table_name)
  43      {
  44          static $domains_created = array();
  45  
  46          if (!$this->is_initialized)
  47          {
  48              throw new extractor_not_initialized_exception();
  49          }
  50  
  51          $sql = "SELECT a.domain_name, a.data_type, a.character_maximum_length, a.domain_default
  52              FROM INFORMATION_SCHEMA.domains a, INFORMATION_SCHEMA.column_domain_usage b
  53              WHERE a.domain_name = b.domain_name
  54                  AND b.table_name = '{$table_name}'";
  55          $result = $this->db->sql_query($sql);
  56          while ($row = $this->db->sql_fetchrow($result))
  57          {
  58              if (empty($domains_created[$row['domain_name']]))
  59              {
  60                  $domains_created[$row['domain_name']] = true;
  61                  //$sql_data = "DROP DOMAIN {$row['domain_name']};\n";
  62                  $sql_data = "CREATE DOMAIN {$row['domain_name']} as {$row['data_type']}";
  63                  if (!empty($row['character_maximum_length']))
  64                  {
  65                      $sql_data .= '(' . $row['character_maximum_length'] . ')';
  66                  }
  67                  $sql_data .= ' NOT NULL';
  68                  if (!empty($row['domain_default']))
  69                  {
  70                      $sql_data .= ' DEFAULT ' . $row['domain_default'];
  71                  }
  72                  $this->flush($sql_data . ";\n");
  73              }
  74          }
  75          $this->db->sql_freeresult($result);
  76  
  77          $sql_data = '-- Table: ' . $table_name . "\n";
  78          $sql_data .= "DROP TABLE $table_name;\n";
  79          // PGSQL does not "tightly" bind sequences and tables, we must guess...
  80          $sql = "SELECT relname
  81              FROM pg_class
  82              WHERE relkind = 'S'
  83                  AND relname = '{$table_name}_seq'";
  84          $result = $this->db->sql_query($sql);
  85          // We don't even care about storing the results. We already know the answer if we get rows back.
  86          if ($this->db->sql_fetchrow($result))
  87          {
  88              $sql_data .= "DROP SEQUENCE IF EXISTS {$table_name}_seq;\n";
  89              $sql_data .= "CREATE SEQUENCE {$table_name}_seq;\n";
  90          }
  91          $this->db->sql_freeresult($result);
  92  
  93          $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
  94              FROM pg_class c, pg_attribute a, pg_type t
  95              WHERE c.relname = '" . $this->db->sql_escape($table_name) . "'
  96                  AND a.attnum > 0
  97                  AND a.attrelid = c.oid
  98                  AND a.atttypid = t.oid
  99              ORDER BY a.attnum";
 100          $result = $this->db->sql_query($field_query);
 101  
 102          $sql_data .= "CREATE TABLE $table_name(\n";
 103          $lines = array();
 104          while ($row = $this->db->sql_fetchrow($result))
 105          {
 106              // Get the data from the table
 107              $sql_get_default = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault
 108                  FROM pg_attrdef d, pg_class c
 109                  WHERE (c.relname = '" . $this->db->sql_escape($table_name) . "')
 110                      AND (c.oid = d.adrelid)
 111                      AND d.adnum = " . $row['attnum'];
 112              $def_res = $this->db->sql_query($sql_get_default);
 113              $def_row = $this->db->sql_fetchrow($def_res);
 114              $this->db->sql_freeresult($def_res);
 115  
 116              if (empty($def_row))
 117              {
 118                  unset($row['rowdefault']);
 119              }
 120              else
 121              {
 122                  $row['rowdefault'] = $def_row['rowdefault'];
 123              }
 124  
 125              if ($row['type'] == 'bpchar')
 126              {
 127                  // Internally stored as bpchar, but isn't accepted in a CREATE TABLE statement.
 128                  $row['type'] = 'char';
 129              }
 130  
 131              $line = '  ' . $row['field'] . ' ' . $row['type'];
 132  
 133              if (strpos($row['type'], 'char') !== false)
 134              {
 135                  if ($row['lengthvar'] > 0)
 136                  {
 137                      $line .= '(' . ($row['lengthvar'] - 4) . ')';
 138                  }
 139              }
 140  
 141              if (strpos($row['type'], 'numeric') !== false)
 142              {
 143                  $line .= '(';
 144                  $line .= sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff));
 145                  $line .= ')';
 146              }
 147  
 148              if (isset($row['rowdefault']))
 149              {
 150                  $line .= ' DEFAULT ' . $row['rowdefault'];
 151              }
 152  
 153              if ($row['notnull'] == 't')
 154              {
 155                  $line .= ' NOT NULL';
 156              }
 157  
 158              $lines[] = $line;
 159          }
 160          $this->db->sql_freeresult($result);
 161  
 162          // Get the listing of primary keys.
 163          $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
 164              FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia
 165              WHERE (bc.oid = i.indrelid)
 166                  AND (ic.oid = i.indexrelid)
 167                  AND (ia.attrelid = i.indexrelid)
 168                  AND    (ta.attrelid = bc.oid)
 169                  AND (bc.relname = '" . $this->db->sql_escape($table_name) . "')
 170                  AND (ta.attrelid = i.indrelid)
 171                  AND (ta.attnum = i.indkey[ia.attnum-1])
 172              ORDER BY index_name, tab_name, column_name";
 173  
 174          $result = $this->db->sql_query($sql_pri_keys);
 175  
 176          $index_create = $index_rows = $primary_key = array();
 177  
 178          // We do this in two steps. It makes placing the comma easier
 179          while ($row = $this->db->sql_fetchrow($result))
 180          {
 181              if ($row['primary_key'] == 't')
 182              {
 183                  $primary_key[] = $row['column_name'];
 184                  $primary_key_name = $row['index_name'];
 185              }
 186              else
 187              {
 188                  // We have to store this all this info because it is possible to have a multi-column key...
 189                  // we can loop through it again and build the statement
 190                  $index_rows[$row['index_name']]['table'] = $table_name;
 191                  $index_rows[$row['index_name']]['unique'] = ($row['unique_key'] == 't') ? true : false;
 192                  $index_rows[$row['index_name']]['column_names'][] = $row['column_name'];
 193              }
 194          }
 195          $this->db->sql_freeresult($result);
 196  
 197          if (!empty($index_rows))
 198          {
 199              foreach ($index_rows as $idx_name => $props)
 200              {
 201                  $index_create[] = 'CREATE ' . ($props['unique'] ? 'UNIQUE ' : '') . "INDEX $idx_name ON $table_name (" . implode(', ', $props['column_names']) . ");";
 202              }
 203          }
 204  
 205          if (!empty($primary_key))
 206          {
 207              $lines[] = "  CONSTRAINT $primary_key_name PRIMARY KEY (" . implode(', ', $primary_key) . ")";
 208          }
 209  
 210          // Generate constraint clauses for CHECK constraints
 211          $sql_checks = "SELECT pc.conname AS index_name, pg_get_expr(pc.conbin, pc.conrelid) AS constraint_expr
 212              FROM pg_constraint pc, pg_class bc
 213              WHERE pc.conrelid = bc.oid
 214                  AND bc.relname = '" . $this->db->sql_escape($table_name) . "'
 215                  AND NOT EXISTS (
 216                      SELECT *
 217                      FROM pg_constraint AS c, pg_inherits AS i
 218                          WHERE i.inhrelid = pc.conrelid
 219                              AND c.conname = pc.conname
 220                              AND pg_get_constraintdef(c.oid) = pg_get_constraintdef(pc.oid)
 221                              AND c.conrelid = i.inhparent
 222                  )";
 223          $result = $this->db->sql_query($sql_checks);
 224  
 225          // Add the constraints to the sql file.
 226          while ($row = $this->db->sql_fetchrow($result))
 227          {
 228              if (!empty($row['constraint_expr']))
 229              {
 230                  $lines[] = '  CONSTRAINT ' . $row['index_name'] . ' CHECK ' . $row['constraint_expr'];
 231              }
 232          }
 233          $this->db->sql_freeresult($result);
 234  
 235          $sql_data .= implode(", \n", $lines);
 236          $sql_data .= "\n);\n";
 237  
 238          if (!empty($index_create))
 239          {
 240              $sql_data .= implode("\n", $index_create) . "\n\n";
 241          }
 242          $this->flush($sql_data);
 243      }
 244  
 245      /**
 246      * {@inheritdoc}
 247      */
 248  	public function write_data($table_name)
 249      {
 250          if (!$this->is_initialized)
 251          {
 252              throw new extractor_not_initialized_exception();
 253          }
 254  
 255          // Grab all of the data from current table.
 256          $sql = "SELECT *
 257              FROM $table_name";
 258          $result = $this->db->sql_query($sql);
 259  
 260          $i_num_fields = pg_num_fields($result);
 261          $seq = '';
 262  
 263          for ($i = 0; $i < $i_num_fields; $i++)
 264          {
 265              $ary_type[] = pg_field_type($result, $i);
 266              $ary_name[] = pg_field_name($result, $i);
 267  
 268              $sql = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault
 269                  FROM pg_attrdef d, pg_class c
 270                  WHERE (c.relname = '{$table_name}')
 271                      AND (c.oid = d.adrelid)
 272                      AND d.adnum = " . strval($i + 1);
 273              $result2 = $this->db->sql_query($sql);
 274              if ($row = $this->db->sql_fetchrow($result2))
 275              {
 276                  // Determine if we must reset the sequences
 277                  if (strpos($row['rowdefault'], "nextval('") === 0)
 278                  {
 279                      $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";
 280                  }
 281              }
 282          }
 283  
 284          $this->flush("COPY $table_name (" . implode(', ', $ary_name) . ') FROM stdin;' . "\n");
 285          while ($row = $this->db->sql_fetchrow($result))
 286          {
 287              $schema_vals = array();
 288  
 289              // Build the SQL statement to recreate the data.
 290              for ($i = 0; $i < $i_num_fields; $i++)
 291              {
 292                  $str_val = $row[$ary_name[$i]];
 293  
 294                  if (preg_match('#char|text|bool|bytea#i', $ary_type[$i]))
 295                  {
 296                      $str_val = str_replace(array("\n", "\t", "\r", "\b", "\f", "\v"), array('\n', '\t', '\r', '\b', '\f', '\v'), addslashes($str_val));
 297                      $str_empty = '';
 298                  }
 299                  else
 300                  {
 301                      $str_empty = '\N';
 302                  }
 303  
 304                  if (empty($str_val) && $str_val !== '0')
 305                  {
 306                      $str_val = $str_empty;
 307                  }
 308  
 309                  $schema_vals[] = $str_val;
 310              }
 311  
 312              // Take the ordered fields and their associated data and build it
 313              // into a valid sql statement to recreate that field in the data.
 314              $this->flush(implode("\t", $schema_vals) . "\n");
 315          }
 316          $this->db->sql_freeresult($result);
 317          $this->flush("\\.\n");
 318  
 319          // Write out the sequence statements
 320          $this->flush($seq);
 321      }
 322  
 323      /**
 324      * Writes closing line(s) to database backup
 325      *
 326      * @return null
 327      * @throws extractor_not_initialized_exception when calling this function before init_extractor()
 328      */
 329  	public function write_end()
 330      {
 331          if (!$this->is_initialized)
 332          {
 333              throw new extractor_not_initialized_exception();
 334          }
 335  
 336          $this->flush("COMMIT;\n");
 337          parent::write_end();
 338      }
 339  }