system/schema/pgsql/connection.php

<?php
namespace Habari;

class PGSQLConnection extends DatabaseConnection
{
  public function connect( $connect_string, $db_user, $db_pass )
  {
    // If something went wrong, we don't need to exec the specific commands.
    if ( !parent::connect( $connect_string, $db_user, $db_pass ) ) {
      return false;
    }

    return true;
  }

  function sql_t( $sql )
  {
    $sql = preg_replace_callback( '%concat\(([^)]+?)\)%i', array( &$this, 'replace_concat' ), $sql );
    $sql = preg_replace( '%DATE_SUB\s*\(\s*NOW\(\s*\)\s*,\s*INTERVAL\s+([0-9]+)\s+DAY\s*\)%ims', 'NOW() - INTERVAL \'${1} DAYS\'', $sql );
    $sql = preg_replace( '%OPTIMIZE TABLE ([^ ]*)%i', 'VACUUM ${1};', $sql );
    //$sql= preg_replace( '%YEAR\s*\(\s*([^ ]*)\s*\)%ims', 'date_part(\'year\', ${1})', $sql );
    //$sql= preg_replace( '%MONTH\s*\(\s*([^ ]*)\s*\)%ims', 'date_part(\'month\', ${1})', $sql );
    //$sql= preg_replace( '%DAY\s*\(\s*([^ ]*)\s*\)%ims', 'date_part(\'day\', ${1})', $sql );
    $sql = preg_replace( '%YEAR\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'date_part(\'year\', \'epoch\'::timestamptz + ${1} * \'1 second\'::interval)', $sql );
    $sql = preg_replace( '%MONTH\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'date_part(\'month\',  \'epoch\'::timestamptz + ${1} * \'1 second\'::interval)', $sql );
    $sql = preg_replace( '%DAY\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'date_part(\'day\',  \'epoch\'::timestamptz + ${1} * \'1 second\'::interval)', $sql );
    $sql = preg_replace('%LIKE\s+((\'|").*\2)%iUms', 'ILIKE \1', $sql);
    $sql = preg_replace( '%RAND\s*\(\s*\)%i', 'RANDOM()', $sql );
    return $sql;
  }

  function replace_concat( $matches )
  {
    $innards = explode( ',', $matches[1] );
    return implode( ' || ', $innards );
  }

  function dbdelta( $queries, $execute = true, $silent = true, $doinserts = false )
  {
    if ( !is_array( $queries ) ) {
      $queries = explode( ';', $queries );
      if ( '' == $queries[count( $queries ) - 1] ) {
        array_pop( $queries );
      }
    }

    $cseqqueries = array();
    $cqueries = array();
    $alterseqqueries = array();
    $indexqueries = array();
    $iqueries = array();
    $for_update = array();
    $indices = array();

    foreach ( $queries as $qry ) {
      if ( preg_match( "|CREATE TABLE\s+(\w*)|", $qry, $matches ) ) {
        $cqueries[strtolower( $matches[1] )] = $qry;
        $for_update[$matches[1]] = 'Created table ' . $matches[1];
      }
      else if ( preg_match( "|CREATE (UNIQUE )?INDEX ([^ ]*) ON ([^ ]*)|", $qry, $matches ) ) {
        $indexqueries[strtolower( $matches[3] )] = $qry;
      }
      else if ( preg_match( "|CREATE DATABASE ([^ ]*)|", $qry, $matches ) ) {
        array_unshift( $cqueries, $qry );
      }
      else if ( preg_match( "|CREATE SEQUENCE ([^ ;]*)|", $qry, $matches ) ) {
        $cseqqueries[strtolower( $matches[1] )] = $qry;
      }
      else if ( preg_match( "|ALTER SEQUENCE ([^ ]*)|", $qry, $matches ) ) {
        $alterseqqueries[] = $qry;
      }
      else if ( preg_match( "|INSERT INTO ([^ ]*)|", $qry, $matches ) ) {
        $iqueries[] = $qry;
      }
      else if ( preg_match( "|UPDATE ([^ ]*)|", $qry, $matches ) ) {
        $iqueries[] = $qry;
      }
      else {
        // Unrecognized query type
      }
    }

    // Checking sequence
    if ( $seqnames = $this->get_results(
      "SELECT c.relname as name,
        CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END AS type
         FROM pg_catalog.pg_class c
         JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('S','')
        AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
        AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 1,2;" ) ) {
      foreach ( ( array ) $seqnames as $seqname ) {
        if ( array_key_exists( strtolower( $seqname->name ), $cseqqueries ) ) {
          unset( $cseqqueries[$seqname->name] );
        }
      }
    }

    if ( $tables = $this->get_column(
      "SELECT table_name
         FROM information_schema.tables
         WHERE table_type = 'BASE TABLE'
         AND table_schema NOT IN ('pg_catalog', 'information_schema');" ) ) {
      foreach ( $tables as $table ) {
        if ( array_key_exists( strtolower( $table ), $cqueries ) ) {
          unset( $cfields );
          $cfields = array();
          unset( $indices );
          $indices = array();
          preg_match( "|\((.*)\)|ms", $cqueries[strtolower( $table )], $match2 );
          $qryline = trim( $match2[1] );

          $flds = explode( "\n", $qryline );
          foreach ( $flds as $fld ) {
            preg_match( "|^([^ ]*)|", trim( $fld ), $fvals );
            $fieldname = $fvals[1];
            $validfield = true;
            switch ( strtolower( $fieldname ) ) {
              case '':
              case 'primary':
              case 'index':
              case 'fulltext':
              case 'unique':
              case 'key':
                $validfield = false;
                $indices[] = trim( trim( $fld ), ", \n" );
                break;
            }
            $fld = trim( $fld );
            if ( $validfield ) {
              $cfields[strtolower( $fieldname )] = trim( $fld, ", \n" );
            }
          }
          if ( isset( $indexqueries[$table] ) ) {
            preg_match( "|CREATE (UNIQUE )?INDEX ([^ ]*) ON ([^ ]*) \((.*)\)|ms", $indexqueries[$table], $matches );
            if ( $matches ) {
              $indices[] = ' (' . preg_replace( '/\s/ms', '', $matches[4] ) . ')';
            }
          }
          $tablefields = $this->get_results(
            "SELECT column_name AS field,
                  data_type AS type,
                  column_default AS default,
                  is_nullable AS null,
                  character_maximum_length AS length,
                  numeric_precision
               FROM information_schema.columns
               WHERE table_name = '{$table}'
               ORDER BY ordinal_position;" );

          foreach ( ( array ) $tablefields as $tablefield ) {
            if ( array_key_exists( strtolower( $tablefield->field ), $cfields ) ) {
              preg_match( '/^(.*) (.*)( |$)/U', $cfields[strtolower( $tablefield->field )], $matches );
              $cfieldname = $matches[1];
              $cfieldtype = $matches[2];
              $fieldtype = $tablefield->type;

              if ( stripos( $fieldtype, 'character' ) === false ) {
                // do nothing
              }
              else {
                if ( stripos( $fieldtype, 'varying' ) > 0 ) {
                  $fieldtype = 'varchar(' . $tablefield->length . ')';
                }
                else {
                  $fieldtype = 'char(' . $tablefield->length . ')';
                }
              }
              if ( stripos( $fieldtype, 'timestamp' ) === false ) {
                // do nothing
              }
              else {
                $fieldtype = 'timestamp';
              }
              if ( strtolower( $fieldtype ) != strtolower( $cfieldtype ) ) {
                $cqueries[] = "ALTER TABLE {$table} ALTER COLUMN " . $cfieldname . " TYPE " . $cfieldtype;
                $for_update[$table.'.'.$tablefield->field] = "Changed type of {$table}.{$tablefield->field} from {$tablefield->type} to {$fieldtype}";
              }
              if ( preg_match( "| DEFAULT ([^ ]*)|i", $cfields[strtolower( $tablefield->field )], $matches ) ) {
                $default_value = $matches[1];
                if ( strpos( '::', $tablefield->default) === false ) {
                  $tablefield_default = $tablefield->default;
                }
                else {
                  preg_match( '|(.*)::|', $tablefield->default, $matches );
                  $tablefield_default = $matches[1] . ( preg_match( '|^nextval|i', $matches[1] ) > 0 ? ')' : '' );
                }
                if ( $tablefield_default != $default_value ) {
                  $cqueries[] = "ALTER TABLE {$table} ALTER COLUMN {$tablefield->field} SET DEFAULT {$default_value}";
                  $for_update[$table.'.'.$tablefield->field] = "Changed default value of {$table}.{$tablefield->field} from {$tablefield->default} to {$default_value}";
                }
              }
              elseif ( strlen( $tablefield->default) > 0 ) {
                $cqueries[] = "ALTER TABLE {$table} ALTER COLUMN {$tablefield->field} DROP DEFAULT";
                $for_update[$table.'.'.$tablefield->field] = "Dropped default value of {$table}.{$tablefield->field}";
              }
              unset( $cfields[strtolower( $tablefield->field )] );
            }
            else {
              // This field exists in the table, but not in the creation queries?
            }
          }
          foreach ( $cfields as $fieldname => $fielddef ) {
            $cqueries[] = "ALTER TABLE {$table} ADD COLUMN $fielddef";
            $for_update[$table.'.'.$fieldname] = 'Added column '.$table.'.'.$fieldname;
          }
          $tableindices = $this->get_results(
            "SELECT c2.relname AS key_name,
                  i.indisprimary AS is_primary,
                  i.indisunique AS is_unique,
                  i.indisclustered AS is_clustered,
                  i.indisvalid AS is_valid,
                  pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS index_def,
                  c2.reltablespace
               FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
                  pg_catalog.pg_index i
               WHERE c.oid = (
                SELECT c.oid
                  FROM pg_catalog.pg_class c
                  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                  WHERE c.relname = '{$table}'
                  AND pg_catalog.pg_table_is_visible(c.oid)
                )
                AND c.oid = i.indrelid AND i.indexrelid = c2.oid
                ORDER BY i.indisprimary DESC, i.indisunique DESC,
                  c2.relname;" );

          if ( $tableindices ) {
            unset( $index_ary );
            $index_ary = array();
            foreach ( ( array ) $tableindices as $tableindex ) {
              $keyname = $tableindex->key_name;
              preg_match( '/\((.*)\)/', $tableindex->index_def, $matches );
              $fieldnames = str_replace( '"', '', str_replace( ' ', '', $matches[1] ) );
              $index_ary[$keyname]['fieldnames'] = $fieldnames;
              $index_ary[$keyname]['unique'] = ( $tableindex->is_unique == true ) ? true : false;
              $index_ary[$keyname]['primary'] = ( $tableindex->is_primary == true ) ? true : false;
            }

            foreach ( $index_ary as $index_name => $index_data ) {
              $index_string = '';
              if ( $index_data['primary'] ) {
                $index_string .= 'PRIMARY KEY ';
              }
              else if ( $index_data['unique'] ) {
                $index_string .= 'UNIQUE ';
              }
              $index_columns = $index_data['fieldnames'];

              $index_string = rtrim( $index_string, ' ' );
              $index_string .= ' (' . $index_columns . ')';
              if ( !( ( $aindex = array_search( $index_string, $indices ) ) === false ) ) {
                unset( $indices[$aindex] );
                unset( $indexqueries[$table] );
              }
              else {
                if ( $index_data['unique'] ) {
                  $cqueries[] = "ALTER TABLE {$table} DROP CONSTRAINT {$index_name}";
                }
                else {
                  $cqueries[] = "DROP INDEX {$index_name}";
                }
              }
            }
          }
          foreach ( $indices as $index ) {
            $cqueries[] = "ALTER TABLE {$table} ADD $index";
            $for_update[$table . '.' . $fieldname] = 'Added index ' . $table . ' ' . $index;
          }
          unset( $cqueries[strtolower( $table )] );
          unset( $for_update[strtolower( $table )] );
        }
        else {
        }
      }
    }

    $allqueries = array_merge( $cseqqueries, $cqueries, $alterseqqueries );
    if ( $doinserts ) {
      $allqueries = array_merge( $allqueries, $iqueries );
    }
    foreach ( $indexqueries as $tablename => $indexquery ) {
      $allqueries = array_merge( $allqueries, ( array ) $indexquery );
    }

    if ( $execute ) {
      foreach ( $allqueries as $query ) {
        if ( !$this->exec( $query ) ) {
          $this->get_errors();
          return false;
        }
      }
    }

    if ( !$silent ) {
      if ( count( $for_update ) > 0 ) {
        echo "<ul>\n";
        foreach ( $for_update as $upgrade ) {
          echo "<li>{$upgrade}</li>\n";
        }
        echo "</ul>\n";
      }
      else {
        echo "<ul><li>" . _t('No Upgrades') . "</li></ul>";
      }
    }
    return $for_update;
  }

  public function upgrade_pre( $old_version, $upgrade_path = '' )
  {
    return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/pre');
  }

  public function upgrade_post( $old_version, $upgrade_path = '' )
  {
    return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/post');
  }
}
?>