<?php
/*------------------------------------------------------------------------------
Copyright (c) 2004, 2005 thaler

Permission is hereby granted, free of charge, to any person obtaining a copy of
this software and associated documentation files (the "Software"), to deal in
the Software without restriction, including without limitation the rights to
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of
the Software, and to permit persons to whom the Software is furnished to do so,
subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS
FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR
COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER
IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

$Id$

You may retrieve the latest version of this file at the tsWebEditor home page,
located at http://tswebeditor.tigris.org

Known Issues:
------------------------------------------------------------------------------*/

class pgsql extends basedb
{
  private $RefActionArr = array('a' => raNoAction,
                                'r' => raRestrict,
                                'c' => raCascade,
                                'n' => raSetNull,
                                'd' => raSetDefault);

  private $RefMatchArr = array('' => rmNoMatchType,
                               'f' => rmMatchFull,
                               '__??p' => rmMatchPartial,
                               'u' => rmMatchSimple);
                               
  private $RefActionSQLArr = array(raNoAction => 'NO ACTION',
                                   raRestrict => 'RESTRICT',
                                   raCascade => 'CASCADE',
                                   raSetNull => 'SET NULL',
                                   raSetDefault => 'SET DEFAULT');
                                   
  private $RefMatchSQLArr = array(rmNoMatchType => '',
                                  rmMatchFull => 'MATCH FULL',
                                  rmMatchPartial => '',
                                  rmMatchSimple => 'MATCH SIMPLE');
                                  
  private $StoredProcVolatileArr = array(spvImmutable => 'IMMUTABLE',
                                         spvStable => 'STABLE',
                                         spvVolatile => 'VOLATILE');
  
  private $StoredProcSecurityArr = array(spsInvoker => 'INVOKER',
                                         spsDefiner => 'DEFINER');

  private $dbschema;
  
  private $supportEscapeString = false;
  private $supportUnescapeBytea = false;

            /*testdrop:
            select
(select relname from pg_class where oid = d.classid),
(select relname from pg_class where oid = d.refclassid)

,d.* from pg_depend d
where


 refobjid in( 25496, 25492 );


--select * from pg_constraint where oid = 25500

/*25496 huogo ref_test2 NULL
25492 huogo ref_test1 NULL*/

  function __construct($properties) {
    parent::__construct($properties);

    $this->supportEscapeString = function_exists('pg_escape_string');
    $this->supportUnescapeBytea = function_exists('pg_unescape_bytea');
  }


  private function getSequs() {
    return "
SELECT
  c.oid,
  nc.nspname AS dbschema,
  c.relname AS name,
  pg_catalog.obj_description(c.oid, 'pg_class') as description
  
FROM
  pg_catalog.pg_namespace nc,
  pg_catalog.pg_class c

WHERE
  c.relnamespace = nc.oid AND
  c.relkind = 'S'
  AND nc.nspname= '" . $this->dbschema . "'";
  }

  private function getSequData($name) {
    return "
SELECT
  sequence_name,
  last_value,
  increment_by,
  CASE
    WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL
    WHEN increment_by < 0 AND max_value = -1 THEN NULL
    ELSE max_value
  END AS max_value,
  CASE
    WHEN increment_by > 0 AND min_value = 1 THEN NULL
    WHEN increment_by < 0 AND min_value = 9223372036854775807 THEN NULL
    ELSE min_value
  END AS min_value,
  cache_value,
  is_cycled,
  is_called
FROM " . $name;
  }
  
  private function getViews() {
    return "
SELECT
  c.oid,
  nc.nspname AS dbschema,
  c.relname AS name,
  pg_catalog.pg_get_viewdef(c.oid) AS definition,
  pg_catalog.obj_description(c.oid, 'pg_class') as description

FROM
  pg_catalog.pg_namespace nc,
  pg_catalog.pg_class c

WHERE
  c.relnamespace = nc.oid AND
  c.relkind = 'v'
  AND nc.nspname= '" . $this->dbschema . "'";
  }

  private function getTables() {
    return "
SELECT
  c.oid,
  nc.nspname AS dbschema,
  c.relname AS name,
  pg_catalog.obj_description(c.oid, 'pg_class') as description

FROM
  pg_catalog.pg_namespace nc,
  pg_catalog.pg_class c

WHERE
  c.relnamespace = nc.oid AND
  c.relkind = 'r'
  AND nc.nspname= '" . $this->dbschema . "'";

  }

  private function getTableCols($oid) {
    return "
SELECT
  a.attnum as attnum,
  a.attname as name,
  a.attnotnull as notnull,
  pg_catalog.format_type(a.atttypid, a.atttypmod) as datatype,
  a.attndims,
  adef.adsrc as default_value,
  pg_catalog.col_description(a.attrelid, a.attnum) as description

FROM
  pg_catalog.pg_attribute a
  LEFT JOIN pg_catalog.pg_attrdef adef
    ON adef.adrelid = a.attrelid AND adef.adnum = a.attnum

WHERE
  a.attrelid = " . $oid . "
  AND a.attnum > 0 AND NOT a.attisdropped

ORDER BY a.attnum";

  }

  private function getIndexes($oid) {
    return "
SELECT
  i.indexrelid,
  c.relname as name,
  c.relnatts as relnatts,
  pg_catalog.pg_get_expr(i.indpred, i.indexrelid, false) as pred,
  i.indisunique,
  i.indisprimary,
  am.amname as index_type,
  pg_catalog.obj_description(c.oid, 'pg_class') as description

FROM
  pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_am am
      ON c.relam = am.oid,
  pg_catalog.pg_index i

WHERE
  c.oid = i.indexrelid AND
  i.indrelid = " . $oid;
}

  private function getConsts($oid) {
    return "
SELECT
  c.conname as name,
  c.contype,
  array_to_string(c.conkey, ' ') as conkey,
  cl.relname,
  array_to_string(c.confkey, ' ') as confkey,
  c.confupdtype,
  c.confdeltype,
  c.confmatchtype,
  pg_catalog.pg_get_constraintdef(c.oid) as def,
  pg_catalog.obj_description(c.oid, 'pg_constraint') as description

FROM
  pg_catalog.pg_constraint c
    LEFT JOIN pg_catalog.pg_class cl ON c.confrelid = cl.oid

WHERE
  c.conrelid = " . $oid;
  }

  private function getTriggers($oid) {
    return "
SELECT
  t.tgname,
  t.tgtype,
  t.tgargs,
  t.tgnargs,
  p.proname,
  p.prosrc,
  t.tgfoid,
  pl.lanname,
  pg_catalog.obj_description(t.oid, 'pg_trigger') as description
FROM
  pg_catalog.pg_trigger t,
  pg_catalog.pg_proc p,
  pg_catalog.pg_language pl

WHERE
  t.tgisconstraint = false
  AND t.tgfoid = p.oid
  AND pl.oid = p.prolang
  AND t.tgrelid = " . $oid;
  }

  private function getStoredProcs() {
    return "
SELECT
  p.proname,
  p.prosrc,
  p.prosecdef,
  p.proisstrict,
  p.provolatile,
  p.proretset,
  pl.lanname,
  pg_catalog.format_type(p.prorettype, NULL) AS rettype,
  pg_catalog.oidvectortypes(p.proargtypes) AS args,
  pg_catalog.obj_description(p.oid, 'pg_proc') AS description

FROM
  pg_catalog.pg_proc p
  INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
  INNER JOIN pg_catalog.pg_language pl ON pl.oid = p.prolang

WHERE
  NOT p.proisagg
  AND n.nspname = '" . $this->dbschema . "'
  AND NOT EXISTS (SELECT oid FROM pg_catalog.pg_trigger t WHERE t.tgfoid = p.oid LIMIT 1) -- hide trigger functions
    ";
  }
  
  private function escapeString($str) {
    return $this->supportEscapeString ? pg_escape_string($str) : addslashes($str);
  }
  
  function formatDataType($datatype) {
    $type = $datatype;
    $size = '';

    $p = strpos($type, '(');
    if($p !== false) {
      $type = substr($datatype, 0, $p);
      $p2 = strrpos($datatype, ')');
      $size = substr($datatype, $p + 1, $p2 - 1 - $p);
    }

    return array('datatype' => $type, 'size' => $size);
  }
  
  private function fixEOL($line) {
    return str_replace("\r", "\n", str_replace("\r\n", "\n", $line));
  }


  protected function testRequs() {
    if(!extension_loaded('pgsql')) {
      throw new Exception('Extension pgsql not loaded !');
    }
  }

  protected function OpenConnect() {
    if($this->isConnected()) $this->CloseConnect();
    
    $dbhost = $this->getProp('dbhost');
    $dbuser = $this->getProp('dbuser');
    $dbpasswd = $this->getProp('dbpasswd');
    $dbname = $this->getProp('dbname');
    $this->dbschema = $this->getProp('dbschema');
    if($this->dbschema == '')
      $this->dbschema = 'public';
    $dbport = $this->getProp('dbport');

    $connection_string = '';
    if(!empty($dbhost))
      $connection_string .= ' host=' . $dbhost;
    if(!empty($dbport))
      $connection_string .= ' port=' . $dbport;
    if(!empty($dbuser))
      $connection_string .= ' user=' . $dbuser;
    if(!empty($dbpasswd))
      $connection_string .= ' password=' . $dbpasswd;
    if(!empty($dbname))
      $connection_string .= ' dbname=' . $dbname;

    if(!$this->dblink = pg_connect($connection_string)) {
      $this->dblink = null;
      throw new Exception('Connect failed.');
    }
    
    $this->query('SET SEARCH_PATH TO "' . $this->dbschema . '"');
  }

  protected function CloseConnect() {
    if($this->isConnected())
      pg_close($this->dblink);
  }

  protected function isConnected() {
    return isset($this->dblink);
  }

  public function testConnection()
  {
    $this->OpenConnect();
    self::Hint('Connected to host ' . pg_host($this->dblink) . ', Database ' . pg_dbname($this->dblink));
  }

  public static function EscapeName($name) {
    $str = str_replace('"', '""', $name);
    return '"' . $str . '"';
  }
  
  public static function EscapeColList($colList) {
    $str = '';
    for($i = 0; $i < count($colList); $i++)
      $str .= ($i > 0 ? ', ' : '') . self::EscapeName($colList[$i]);
    return $str;
  }
  
  private function findColAttNum(Table $table, $attnum) {
    for($i = 0; $i < $table->getColList()->getCount(); $i++) {
      if($table->getColList()->getCol($i)->getProps()->get('pgsql::attnum') == $attnum) {
        return $table->getColList()->getCol($i);
      }
    }
    
    return null;
  }
  
  private function CreateFunction($name, $args, $returns, $lang, $volatility, $isstrict, $security, $procsql) {
    $sql = 'CREATE OR REPLACE FUNCTION ' . $name .
      ' (' . $args . ') RETURNS '  . $returns .
      ' LANGUAGE ' . $lang . ' ' . $this->StoredProcVolatileArr[$volatility] . ' ';

    if($isstrict == true)
      $sql .= 'RETURNS NULL ON NULL INPUT';
    else
      $sql .= 'CALLED ON NULL INPUT';

    $sql .= ' SECURITY ' . $this->StoredProcSecurityArr[$security];

    $sql .= ' AS \'' . $this->escapeString($procsql) . '\';';

    return $sql;
  }
  
  private function CreateFunctionDDL(StoredProc $storedproc, StoredProcOverload $overload, $argsStr) {
    return $this->CreateFunction(self::EscapeName($storedproc->getName()), $argsStr,
      $overload->Returns, $overload->Language, $overload->Volatility,
        ($overload->getProps()->get('pgsql::isstrict') == '1'), $overload->Security, $overload->SQL);
  }
  
  private function CreateTriggerFunctionDLL(Trigger $trg, $name) {
    return $this->CreateFunction(self::EscapeName($name), '', 'trigger',
      ($trg->getProps()->get('pgsql:lang') != '' ? $trg->getProps()->get('pgsql:lang') : 'plpgsql'),
        spvVolatile, false, spsInvoker, $trg->SQL);
  }
  
  private function CreateTriggerDDL(Trigger $trg, $funcname) {
    $sql = 'CREATE TRIGGER ' . self::EscapeName($trg->getName()) . ' ';

    if($trg->Execute == teBefore)
      $sql .= 'BEFORE';
    else
      $sql .= 'AFTER';

    $sql .= ' ';

    $ex = 0;
    if($trg->Events['i'] == true) {
      $sql .= 'INSERT';
      $ex ++;
    }

    if($trg->Events['u'] == true) {
      if($ex > 0)
        $sql .= ' OR UPDATE';
      else
        $sql .= 'UPDATE';

      $ex ++;
    }

    if($trg->Events['d'] == true) {
      if($ex > 0)
        $sql .= ' OR DELETE';
      else
        $sql .= 'DELETE';
    }

    $sql .= ' ON ' . self::EscapeName($trg->getTable()->getName()) . ' FOR EACH ';

    if($trg->_ForEach == tfeRow)
      $sql .= 'ROW';
    else
      $sql .= 'STATEMENT';

    // todo: support for functions with arguments
    $sql .= ' EXECUTE PROCEDURE ' . self::EscapeName($funcname) . '(' . $trg->getProps()->get('pgsql:args') . ');';
        
    return $sql;
  }
  
  function splitStoredProcArgs(StoredProcOverload $obj) {
    $args = explode(',', $obj->getName());
    $argsStr = '';
    foreach($args as $arg) {
      $arg = trim($arg);
      $para = strstr($arg, ' ');
      if($para == '')
        $para = $arg;
      else
        $para = trim($para);
      if($argsStr == '')
        $argsStr = $para;
      else
        $argsStr .= ', ' . $para;
    }
    return $argsStr;
  }
  
  private function WarnNoEqualObjs($obj, $tmp) {
    self::Warn('Object ' . $obj->getName() . ' is a ' . $obj->getTypeName() . ' in your design, but i found the object as ' . $tmp->getTypeName() . ' in the database. i skip the object.');
  }

  private function query($query) {
    if(!($x = @pg_query($this->dblink, $query))) {
      throw new Exception('Cannot execute Query: ' . pg_last_error($this->dblink) . CRLF . 'Query: ' . $query);
    }
    
    return $x;
  }
  
  public function DoImportDB(ObjList $ObjList, $filter) {
    if(!$this->isConnected()) $this->OpenConnect();
    
    // Insert Sequences
    $result = $this->query($this->getSequs());
    while ($row = pg_fetch_assoc($result)) {
      $sequence = new Sequence($ObjList, $row['name']);

      if($row['description'] !== null)
        $sequence->Comment = $row['description'];
        
      $seqdata = $this->query($this->getSequData($row['name']));
      $data = pg_fetch_assoc($seqdata);
      
      $sequence->Increment = $data['increment_by'];

      if($data['min_value'] == null)
        $sequence->MinValue = 'no';
      else
        $sequence->MinValue = $data['min_value'];
        
      if($data['max_value'] == null)
        $sequence->MaxValue = 'no';
      else
        $sequence->MaxValue = $data['max_value'];
        
      if($data['is_called'] == 'f')
        $sequence->Start = $data['last_value'];

      $sequence->Cache = $data['cache_value'];
      
      pg_free_result($seqdata);
    }
    pg_free_result($result);

    // Insert Views
    $result = $this->query($this->getViews());
    while ($row = pg_fetch_assoc($result)) {
      $view = new View($ObjList, $row['name']);
      if($row['description'] !== null)
        $view->Comment = $row['description'];
      $view->SQL = substr($row['definition'], 0, -1);
    }
    pg_free_result($result);

    // Insert Tables
    $tables = array();
    $result = $this->query($this->getTables());
    while ($row = pg_fetch_assoc($result)) {
      $tbl = new Table($ObjList, $row['name']);
      $tables[$row['name']] = $row['oid'];
      if($row['description'] !== null)
        $tbl->Comment = $row['description'];
        
      // Columns
      $resultcols = $this->query($this->getTableCols($row['oid']));
      while(($rowcol = pg_fetch_assoc($resultcols))) {
        $col = $tbl->AddColumn($rowcol['name']);

        $col->getProps()->set('pgsql::attnum', $rowcol['attnum'], false);

        $coltype = $this->formatDataType($rowcol['datatype']);
        $col->DataType = $coltype['datatype'];
        $col->Size = $coltype['size'];
        
        if($rowcol['attndims'] > 0) { // is array, add [] for all dims
          $col->DataType = $col->DataType . str_repeat('[]', $rowcol['attndims'] - 1);
        }

        if($rowcol['default_value'] !== null)
          $col->DefaultValue =  $rowcol['default_value'];

        if($rowcol['description'] !== null)
          $col->Comment = $rowcol['description'];

        $col->NotNull = $rowcol['notnull'] == 't';
      }
      pg_free_result($resultcols);

      // Indexes
      $resultidxs = $this->query($this->getIndexes($row['oid']));
      while(($rowidx = pg_fetch_assoc($resultidxs))) {

        // todo: fix bug: if an index is  indisunique and no const !
        if($rowidx['indisunique'] == 'f' && $rowidx['indisprimary'] == 'f') { // don't add index from a unique/primary key const
          $idx = new Index($ObjList, $tbl, $rowidx['name']);

          $colscnt = (int)$rowidx['relnatts'];
          
          $sql = 'SELECT ';
          for($i = 1; $i <= $colscnt; $i++)
            $sql .= ($i > 1 ? ',' : '') . 'pg_catalog.pg_get_indexdef(' . $rowidx['indexrelid'] . ', ' . $i . ', true)';
          
          $idxcols = $this->query($sql);
          for($i = 0; $i < $colscnt; $i++)
            $idx->AddColName(pg_fetch_result($idxcols, 0, $i));

          pg_free_result($idxcols);
          
          if($rowidx['index_type'] !== null) {
            $idx->IndexType = $rowidx['index_type'];
          }
          
          if($rowidx['pred'] !== null) {
            $idx->getProps()->set('pgsql::pred', $rowidx['pred']);
          }
          
          if($rowidx['description'] !== null)
            $idx->Comment = $rowidx['description'];
        }
      }
      pg_free_result($resultidxs);
    }
    pg_free_result($result);

    // Consts
    foreach($tables as $name => $oid) {
      $resultconsts = $this->query($this->getConsts($oid));
      while(($rowconst = pg_fetch_assoc($resultconsts))) {
        $freename = $ObjList->freeName($rowconst['name'], true);

        if($freename != $rowconst['name']) {
          self::Hint('Constraint ' . $rowconst['name'] . ' renamed to ' . $freename);
        }

        $tbl = $ObjList->findObj($name);
        $const = null;
        
        switch($rowconst['contype']) {
          case 'c': // check constraint
            $const = new ConstCheck($ObjList, $tbl, $freename);
            $const->SQL = substr(substr($rowconst['def'], 8), 0, -2);
            break;
          case 'f': // foreign key constraint
            $tabledest = $ObjList->findObj($rowconst['relname']);
            $const = new Reference($ObjList, $tbl, $tabledest, $freename);
            
            if(isset($this->RefActionArr[$rowconst['confupdtype']]))
              $const->OnUpdate = $this->RefActionArr[$rowconst['confupdtype']];
            else
              $const->OnUpdate = raNoAction;
            
            if(isset($this->RefActionArr[$rowconst['confdeltype']]))
              $const->OnDelete = $this->RefActionArr[$rowconst['confdeltype']];
            else
              $const->OnDelete = raNoAction;
              
            if(isset($this->RefMatchArr[$rowconst['confmatchtype']]))
              $const->MatchType = $this->RefMatchArr[$rowconst['confmatchtype']];
            else
              $const->MatchType = rmNoMatchType;
            
            break;
          case 'p': // primary key constraint
            $const = new ConstPrimaryKey($ObjList, $tbl, $freename);
            break;
          case 'u': // unique constraint
            $const = new ConstUnique($ObjList, $tbl, $freename);
          
            break;
        }

        if($rowconst['conkey'] !== null) {
          $cols = explode(' ', $rowconst['conkey']);

          foreach($cols as $col) {
            $tmpcol = $this->findColAttNum($tbl, $col);

            if($tmpcol !== null)
              $const->AddColName($tmpcol->getName());
            else
              self::Warn('Cannot find Column ' . $col . ' for Constraint ' . $const->getName());
          }
        }

        if($const instanceof Reference && $rowconst['confkey'] !== null) {
          $cols = explode(' ', $rowconst['confkey']);

          foreach($cols as $col) {
            $tmpcol = $this->findColAttNum($const->getTableDest(), $col);

            if($tmpcol !== null)
              $const->AddDestColName($tmpcol->getName());
            else
              self::Warn('Cannot find Column ' . $col . ' for Constraint ' . $const->getName());
          }
        }
        
        if($rowconst['description'] !== null) {
          $const->Comment = $rowconst['description'];
        }
        
        if($const instanceof ConstPrimaryKey) {
          $const->UpdateTable();
        }
      }
      pg_free_result($resultconsts);
    }
    
    if (!defined('TRIGGER_TYPE_ROW')) define ('TRIGGER_TYPE_ROW', (1 << 0));
    if (!defined('TRIGGER_TYPE_BEFORE')) define ('TRIGGER_TYPE_BEFORE', (1 << 1));
    if (!defined('TRIGGER_TYPE_INSERT')) define ('TRIGGER_TYPE_INSERT', (1 << 2));
    if (!defined('TRIGGER_TYPE_DELETE')) define ('TRIGGER_TYPE_DELETE', (1 << 3));
    if (!defined('TRIGGER_TYPE_UPDATE')) define ('TRIGGER_TYPE_UPDATE', (1 << 4));

    // Triggers
    foreach($tables as $name => $oid) {
      $resulttg = $this->query($this->getTriggers($oid));
      while(($rowtg = pg_fetch_assoc($resulttg))) {
        $freename = $ObjList->freeName($rowtg['tgname'], true);

        if($freename != $rowtg['tgname']) {
          self::Hint('Trigger ' . $rowtg['tgname'] . ' renamed to ' . $freename);
        }
        
        $tg = new Trigger($ObjList, $freename);

        if (($rowtg['tgtype'] & TRIGGER_TYPE_BEFORE) == TRIGGER_TYPE_BEFORE)
          $tg->Execute = teBefore;
        else
          $tg->Execute = teAfter;
          
        if (($rowtg['tgtype'] & TRIGGER_TYPE_INSERT) == TRIGGER_TYPE_INSERT)
          $tg->Events['i'] = true;
          
        if (($rowtg['tgtype'] & TRIGGER_TYPE_DELETE) == TRIGGER_TYPE_DELETE)
          $tg->Events['d'] = true;
          
        if (($rowtg['tgtype'] & TRIGGER_TYPE_UPDATE) == TRIGGER_TYPE_UPDATE)
          $tg->Events['u'] = true;

        if ($rowtg['tgtype'] & TRIGGER_TYPE_ROW == TRIGGER_TYPE_ROW)
          $tg->_ForEach = tfeRow;
        else
          $tg->_ForEach = tfeStatement;

        $tg->SQL = $rowtg['prosrc'];
        
        if($rowtg['description'] !== null)
          $tg->Comment = $rowtg['description'];
        
        $tbl = $ObjList->findObj($name);
        $tg->setTable($tbl);
        
        $tg->getProps()->set('pgsql:foid', $rowtg['tgfoid'], false);
        $tg->getProps()->set('pgsql:proname', $rowtg['proname'], false);
        

        if($this->supportUnescapeBytea) {
          $args = explode("\0", pg_unescape_bytea($rowtg['tgargs']), $rowtg['tgnargs']);
          $argsStr = '';
          foreach($args as $arg) {
            if($argsStr != '')
              $argsStr .= ', ';
            $argsStr .= '\'' . $this->escapeString($arg) . '\'';
          }
          
          $tg->getProps()->set('pgsql:args', $argsStr);
        }

        $tg->getProps()->set('pgsql:lang', $rowtg['lanname']);
      }
      pg_free_result($resulttg);
    }
    
    // Stored Procedures
    $resultsp = $this->query($this->getStoredProcs());
    while(($rowsp = pg_fetch_assoc($resultsp))) {
      $sp = $ObjList->findObj($rowsp['proname']);
      
      if($sp == null) {
        $sp = new StoredProc($ObjList, $rowsp['proname']);
      } else if(!$sp instanceof StoredProc) {
        $sp = new StoredProc($ObjList, $ObjList->freeName($rowsp['proname'], true));
        self::Hint('Stored Procedure ' . $rowsp['proname'] . ' renamed to ' . $sp->getName());
      }

      $overload = new StoredProcOverload();
      $sp->getProcs()->Add($overload);

      $overload->SQL = $rowsp['prosrc'];
      $overload->Language = $rowsp['lanname'];
      $overload->Returns = $rowsp['rettype'];

      if($rowsp['args'] != '') {
        $args = explode(',', $rowsp['args']);
        $argsStr = '';
        foreach($args as $i => $arg) {
          if($i > 0)
            $argsStr .= ', ';
          $argsStr .= 'a' . $i . ' ' . $arg;
        }
        $overload->setName($argsStr);
      } else
        $overload->setName('');

      if($rowsp['proretset'] === 't')
        $overload->Returns = 'SETOF ' . $overload->Returns;
        
      if($rowsp['prosecdef'] === 't')
        $overload->Security = spsDefiner;
      else
        $overload->Security = spsInvoker;

      switch($rowsp['provolatile']) {
        case 'i':
          $overload->Volatility = spvImmutable;
          break;
        case 's':
          $overload->Volatility = spvStable;
          break;
        case 'v':
         $overload->Volatility = spvVolatile;
         break;
      }
      
      if($rowsp['proisstrict'] === 't')
        $overload->getProps()->set('pgsql::isstrict', '1');

      if($rowsp['description'] !== null)
        $overload->Comment = $rowsp['description'];
    }
    pg_free_result($resultsp);
  }
  
  private function CommentOn($objtype, $name, $comment) {
    $ret = 'COMMENT ON ' . $objtype . ' ' . $name;
    if($comment == '') {
      $ret .= ' IS NULL';
    } else {
      $ret .= ' IS \'' . $this->escapeString($comment) . '\'';
    }

    return $ret . ';';
  }
  
  public function DoGenerateDDL(ObjList $DataDict, ObjList $ObjList, &$ddl) {
    // -- Sequences --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Sequence', $filter_i)) > -1) {
      $obj = $ObjList->getObj($filter_i);
      $tmp = $DataDict->findObj($obj->getName());

      if($tmp == null) { // create obj
        $sql = 'CREATE SEQUENCE ' . self::EscapeName($obj->getName());

        if(!empty($obj->Increment))
          $sql .= CRLF . '  INCREMENT BY ' . $obj->Increment;

        if(!empty($obj->MinValue))
          if($obj->MinValue == 'no')
            $sql .= CRLF . '  NO MINVALUE';
          else
            $sql .= CRLF . '  MINVALUE ' . $obj->MinValue;

        if(!empty($obj->MaxValue))
          if($obj->MaxValue == 'no')
            $sql .= CRLF . '  NO MAXVALUE';
          else
            $sql .= CRLF . '  MAXVALUE ' . $obj->MaxValue;

        if(!empty($obj->Start))
          $sql .= CRLF . '  START WITH ' . $obj->Start;

        if(!empty($obj->Cache))
          $sql .= CRLF . '  CACHE ' . $obj->Cache;

        $ddl[] = $sql . ';';
        
        if(!empty($obj->Comment))
          $ddl[] = $this->CommentOn('SEQUENCE', self::EscapeName($obj->getName()), $obj->Comment);
      } else if(!$tmp instanceof Sequence) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else { // merge
        $sql = '';
      
        if(!empty($obj->Increment) && $obj->Increment != $tmp->Increment)
          $sql .= CRLF . '  INCREMENT BY ' . $obj->Increment;
        
        if(!empty($obj->MinValue) && $obj->MinValue != $tmp->MinValue)
          if($obj->MinValue == 'no')
            $sql .= CRLF . '  NO MINVALUE';
          else
            $sql .= CRLF . '  MINVALUE ' . $obj->MinValue;
        
        if(!empty($obj->MaxValue) && $obj->MaxValue != $tmp->MaxValue)
          if($obj->MaxValue == 'no')
            $sql .= CRLF . '  NO MAXVALUE';
          else
            $sql .= CRLF . '  MAXVALUE ' . $obj->MaxValue;
        
        if(!empty($obj->Cache) && $obj->Cache != $tmp->Cache)
          $sql .= CRLF . '  CACHE ' . $obj->Cache;
          
        if($sql != '') {
          $ddl[] = 'ALTER SEQUENCE ' . self::EscapeName($obj->getName()) . $sql . ';';
        }

        if($obj->Comment != $tmp->Comment)
          $ddl[] = $this->CommentOn('SEQUENCE', self::EscapeName($obj->getName()), $obj->Comment);
      }
    }

    // -- Tables --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Table', $filter_i)) > -1) {
      $obj = $ObjList->getObj($filter_i);
      $tmp = $DataDict->findObj($obj->getName());

      if($tmp == null) { // create obj
        $sql = 'CREATE TABLE ' . self::EscapeName($obj->getName()) . ' (';
        $comment = '';
        
        // -- Columns --
        for($i = 0; $i < $obj->getColList()->getCount(); $i++) {
          $col = $obj->getColList()->getCol($i);
          $sql .= ($i > 0 ? ',' : '') . CRLF2P . self::EscapeName($col->getName()) . ' ' . $col->getDataTypeSize();

          if(!empty($col->DefaultValue))
            $sql .= ' DEFAULT ' . $col->DefaultValue;

          if($col->NotNull)
            $sql .= ' NOT NULL';

          if(!empty($col->Comment))
            $comment .= $this->CommentOn('COLUMN', self::EscapeName($obj->getName()) . '.' . self::EscapeName($col->getName()), $col->Comment) . CRLF;
        }
        
        // -- ConstUnique --
        // -- ConstCheck --
        // -- ConstPrimaryKey --
        
        $ConstSql = '';
        
        for($i = 0; $i < $obj->getRefListCount(); $i++) {
          $ref = $obj->getRef($i);
          if($ref instanceof Constraint) {
            $ConstSql .= ($ConstSql != '' ? ',' : '') . CRLF2P . 'CONSTRAINT ' . self::EscapeName($ref->getName()) . ' ';
            if($ref instanceof ConstUnique) {
              $ConstSql .= 'UNIQUE';
              $ConstSql .= ' (' . self::EscapeColList($ref->getColNames()) . ')';
            } else if($ref instanceof ConstCheck) {
              $ConstSql .= 'CHECK';
              $ConstSql .= ' (' . $ref->getColString() . ')';
            } else if($ref instanceof ConstPrimaryKey) {
              $ConstSql .= 'PRIMARY KEY';
              $ConstSql .= ' (' . self::EscapeColList($ref->getColNames()) . ')';
            }
            
            if(!empty($ref->Comment))
              $comment .= $this->CommentOn('CONSTRAINT', self::EscapeName($ref->getName()) . ' ON ' . self::EscapeName($obj->getName()), $ref->Comment) . CRLF;
          }
        }
        
        if($ConstSql != '')
          $sql .= ', ' . $ConstSql;
        
        
        if(!empty($obj->Comment))
          $comment .= $this->CommentOn('TABLE', self::EscapeName($obj->getName()), $obj->Comment);

        $ddl[] = '-- Create Table ' . $obj->getName() . CRLF . $sql . CRLF . ');';
        if(!empty($comment))
          $ddl[] = $comment;

      } else if(!$tmp instanceof Table) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else { // merge
        $comment = '';
        
        // -- Columns --
        for($i = 0; $i < $obj->getColList()->getCount(); $i++) {
          $col = $obj->getColList()->getCol($i);
          $dbcol = $tmp->getColList()->findCol($col->getName());
          
          if($dbcol === null) { // add column
            $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) .
              CRLF2P . 'ADD ' . self::EscapeName($col->getName()) . ' ' . $col->getDataTypeSize() . ';';
            
            if(!empty($col->DefaultValue))
              $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) .
                CRLF2P . 'ALTER ' . self::EscapeName($col->getName()) . ' SET DEFAULT ' . $col->DefaultValue . ';';

            if($col->NotNull)
              $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) .
                CRLF2P . 'ALTER ' . self::EscapeName($col->getName()) . ' SET NOT NULL;';

            if(!empty($col->Comment))
              $comment .= $this->CommentOn('COLUMN', self::EscapeName($obj->getName()) . '.' . self::EscapeName($col->getName()), $col->Comment) . CRLF;
          } else {
            if($col->DataType != $dbcol->DataType || $col->Size != $dbcol->Size) {
              self::Warn('PostgreSQL doesn\'t support change of column data type and size (' . $obj->getName() . '.' . $col->getName() . ').');
            }
            
            if($col->DefaultValue != $dbcol->DefaultValue) {
              if(empty($col->DefaultValue)) {
                $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) .
                  CRLF2P . 'ALTER ' . self::EscapeName($col->getName()) . ' DROP DEFAULT;';
              } else {
                $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) .
                  CRLF2P . 'ALTER ' . self::EscapeName($col->getName()) . ' SET DEFAULT ' . $col->DefaultValue . ';';
              }
            }
            
            if($col->NotNull != $dbcol->NotNull) {
              if($col->NotNull) {
                $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) .
                  CRLF2P . 'ALTER ' . self::EscapeName($col->getName()) . ' SET NOT NULL;';
              } else {
                $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) .
                  CRLF2P . 'ALTER ' . self::EscapeName($col->getName()) . ' DROP NOT NULL;';
              }
            }
            
            if($col->Comment != $dbcol->Comment)
              $comment .= $this->CommentOn('COLUMN', self::EscapeName($obj->getName()) . '.' . self::EscapeName($col->getName()), $col->Comment) . CRLF;
          }
        }
        
        
        // -- ConstUnique --
        // -- ConstCheck --
        // -- ConstPrimaryKey --

        $ConstSql = '';

        for($i = 0; $i < $obj->getRefListCount(); $i++) {
          $ref = $obj->getRef($i);
          if($ref instanceof Constraint) {
            $dbref = $DataDict->findObj($ref->getName());

            $ConstType = '';
            $ConstCols = '';
            if($ref instanceof ConstUnique) {
              $ConstType = 'UNIQUE';
              $ConstCols = self::EscapeColList($ref->getColNames());
            } else if($ref instanceof ConstCheck) {
              $ConstType = 'CHECK';
              $ConstCols = $ref->getColString();
            } else if($ref instanceof ConstPrimaryKey) {
              $ConstType = 'PRIMARY KEY';
              $ConstCols = self::EscapeColList($ref->getColNames());
              
              if($dbref == null) {
                for($u = 0; $u < $tmp->getRefListCount(); $u++) {
                  if($tmp->getRef($u) instanceof ConstPrimaryKey) {
                    $dbref = $tmp->getRef($u);
                    break;
                  }
                }
              }
            }

            
            if($dbref == null) {
              $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) .
                CRLF2P . 'ADD CONSTRAINT ' . self::EscapeName($ref->getName()) . ' ' . $ConstType . ' (' . $ConstCols . ');';
                
              if(!empty($ref->Comment))
                $comment .= $this->CommentOn('CONSTRAINT', self::EscapeName($ref->getName()) . ' ON ' . self::EscapeName($obj->getName()), $ref->Comment) . CRLF;
            } else if(!$dbref instanceof $ref) {
              self::WarnNoEqualObjs($ref, $dbref);
            } else {
              if($ref->getColString() != $dbref->getColString()) {
                $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) . ' DROP CONSTRAINT ' . self::EscapeName($dbref->getName()) . ';' . CRLF .
                  'ALTER TABLE ' . self::EscapeName($obj->getName()) .
                    CRLF2P . 'ADD CONSTRAINT ' . self::EscapeName($ref->getName()) . ' ' . $ConstType . ' (' . $ConstCols . ');';
              }
              
              if($ref->Comment != $dbref->Comment)
                $comment .= $this->CommentOn('CONSTRAINT', self::EscapeName($ref->getName()) . ' ON ' . self::EscapeName($obj->getName()), $ref->Comment) . CRLF;
            }
          }
        }

        if($obj->Comment != $tmp->Comment)
          $comment .= $this->CommentOn('TABLE', self::EscapeName($obj->getName()), $obj->Comment);

        if(!empty($comment))
          $ddl[] = $comment;
      }
    }
    
    // -- Reference --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Reference', $filter_i)) > -1) {
      $obj = $ObjList->getObj($filter_i);
      $tmp = $DataDict->findObj($obj->getName());

      if($tmp == null) { // create
        $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getTableSource()->getName()) .
          CRLF2P . 'ADD CONSTRAINT ' . self::EscapeName($obj->getName()) . ' FOREIGN KEY (' . self::EscapeColList($obj->getColNamesSource()) . ')' .
          CRLF2P . '  REFERENCES ' . self::EscapeName($obj->getTableDest()->getName()) . ' (' . self::EscapeColList($obj->getColNamesDest()) . ')' .
          CRLF2P . '  ' . $this->RefMatchSQLArr[$obj->MatchType] . ' ON DELETE ' . $this->RefActionSQLArr[$obj->OnDelete] . ' ON UPDATE ' . $this->RefActionSQLArr[$obj->OnUpdate] . ';';

        if($obj->MatchType == rmMatchPartial)
          self::Warn('MATCH PARTIAL is not yet implemented by PostgreSQL (' . $obj->getName() . '). Use default.');

        if(!empty($ref->Comment))
          $ddl[] = $this->CommentOn('CONSTRAINT', self::EscapeName($obj->getName()) . ' ON ' . self::EscapeName($obj->getTableSource()->getName()), $obj->Comment);
      } else if(!$tmp instanceof Reference) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else { // merge
        if($obj->getTableSource()->getName() != $tmp->getTableSource()->getName() ||
          $obj->getTableDest()->getName() != $tmp->getTableDest()->getName() ||
          $obj->getColSrc() !=  $tmp->getColSrc() || $obj->getColDest() != $tmp->getColDest())
        {
          $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getTableSource()->getName()) . ' DROP CONSTRAINT ' . self::EscapeName($obj->getName()) . ';' . CRLF .
            'ALTER TABLE ' . self::EscapeName($obj->getTableSource()->getName()) .
            CRLF2P . 'ADD CONSTRAINT ' . self::EscapeName($obj->getName()) . ' FOREIGN KEY (' . self::EscapeColList($obj->getColNamesSource()) . ')' .
            CRLF2P . '  REFERENCES ' . self::EscapeName($obj->getTableDest()->getName()) . ' (' . self::EscapeColList($obj->getColNamesDest()) . ')' .
            CRLF2P . '  ' . $this->RefMatchSQLArr[$obj->MatchType] . ' ON DELETE ' . $this->RefActionSQLArr[$obj->OnDelete] . ' ON UPDATE ' . $this->RefActionSQLArr[$obj->OnUpdate] . ';';

          if($obj->MatchType == rmMatchPartial)
            self::Warn('MATCH PARTIAL is not yet implemented by PostgreSQL (' . $obj->getName() . '). Use default.');
        }
        
        if($obj->Comment != $tmp->Comment)
          $ddl[] = $this->CommentOn('CONSTRAINT', self::EscapeName($obj->getName()) . ' ON ' . self::EscapeName($obj->getTableSource()->getName()), $obj->Comment);
      }
    }
    

    // -- Index --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Index', $filter_i)) > -1) {
      $obj = $ObjList->getObj($filter_i);
      $tmp = $DataDict->findObj($obj->getName());

      if($tmp == null) { // create
        $sql = 'CREATE' . (strtolower($obj->IndexKind) == 'unique' ? ' UNIQUE' : '') . ' INDEX ' .
          self::EscapeName($obj->getName()) . ' ON ' . self::EscapeName($obj->getTable()->getName());

        if($obj->IndexType != '')
          $sql .= ' USING ' . $obj->IndexType;

        $sql .= '(';

        $cols = $obj->getColNames();
        for($i = 0; $i < count($cols); $i++) {
          $sql .= ($i > 0 ? ', ' : '');

          if($obj->getTable()->getColList()->findCol($cols[$i]) != null) {
            $sql .= self::EscapeName($cols[$i]);
          } else {
            $sql .= $cols[$i];
          }
        }
        $sql .= ')';

        if(($pred = $obj->getProps()->get('pgsql::pred')) != null)
          $sql .= ' WHERE (' . $pred . ')';
        
        $ddl[] = $sql . ';';

        if(!empty($obj->Comment))
          $ddl[] = $this->CommentOn('INDEX', self::EscapeName($obj->getName()), $obj->Comment);
      } else if(!$tmp instanceof Index) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else { // merge
        if($obj->getTable()->getName() != $tmp->getTable()->getName() ||
          $obj->getColString() != $tmp->getColString() ||
          $obj->getProps()->get('pgsql::pred') != $tmp->getProps()->get('pgsql::pred'))
        {
          $sql = 'DROP INDEX ' . self::EscapeName($obj->getName()) . ';' . CRLF;
          
          $sql .= 'CREATE' . (strtolower($obj->IndexKind) == 'unique' ? ' UNIQUE' : '') . ' INDEX ' .
            self::EscapeName($obj->getName()) . ' ON ' . self::EscapeName($obj->getTable()->getName());

          if($obj->IndexType != '')
            $sql .= ' USING ' . $obj->IndexType;

          $sql .= '(';

          $cols = $obj->getColNames();
          for($i = 0; $i < count($cols); $i++) {
            $sql .= ($i > 0 ? ', ' : '');

            if($obj->getTable()->getColList()->findCol($cols[$i]) != null) {
              $sql .= self::EscapeName($cols[$i]);
            } else {
              $sql .= $cols[$i];
            }
          }
          $sql .= ')';

          if(($pred = $obj->getProps()->get('pgsql::pred')) != null)
            $sql .= ' WHERE (' . $pred . ')';

          $ddl[] = $sql . ';';
          
          if(!empty($obj->Comment))
            $ddl[] = $this->CommentOn('INDEX', self::EscapeName($obj->getName()), $obj->Comment);
        } else if($obj->Comment != $tmp->Comment) {
          $ddl[] = $this->CommentOn('INDEX', self::EscapeName($obj->getName()), $obj->Comment);
        }
      }
    }

    // Load supported Languages ...
    $supportedlang = array();
    $reslang = $this->query('select lower(lanname) as lanname from pg_catalog.pg_language');
    while($rowlang = pg_fetch_assoc($reslang)) {
      $supportedlang[] = $rowlang['lanname'];
    }
    pg_free_result($reslang);

    // -- Stored Procedures ---
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('StoredProc', $filter_i)) > -1) {
      $obj = $ObjList->getObj($filter_i);
      $tmp = $DataDict->findObj($obj->getName());

      if($tmp == null) { // create
        for($i = 0; $i < $obj->getProcs()->getCount(); $i++) {
          $proc = $obj->getProcs()->getObj($i);

          if(in_array(strtolower($proc->Language), $supportedlang)) {
            // remove parameter names since it's supported only by >= 8.0
            $argsStr = $this->splitStoredProcArgs($proc);
          
            $ddl[] = $this->CreateFunctionDDL($obj, $proc, $argsStr);

            if(!empty($proc->Comment))
              $ddl[] = $this->CommentOn('FUNCTION', self::EscapeName($obj->getName()) . '(' . $argsStr . ')', $proc->Comment);
          } else
            self::Warn('Database doesn\'t support the language ' . $proc->Language . '. Stored Procedure ' . $obj->getName() . '(' . $proc->getName() . ') will be skiped.');
        }
      } else if(!$tmp instanceof StoredProc) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else { // merge
        for($i = 0; $i < $obj->getProcs()->getCount(); $i++) {
          $proc = $obj->getProcs()->getObj($i);
          
          if(!in_array(strtolower($proc->Language), $supportedlang)) {
            self::Warn('Database doesn\'t support the language ' . $proc->Language . '. Stored Procedure ' . $obj->getName() . '(' . $proc->getName() . ') will be skiped.');
            continue;
          }
          
          $argsStr = $this->splitStoredProcArgs($proc);
          
          $tmpproc = null;
          for($ii = 0; $ii < $tmp->getProcs()->getCount(); $ii++) {
            if(strtolower($this->splitStoredProcArgs($tmp->getProcs()->getObj($ii))) == strtolower($argsStr)) {
              $tmpproc = $tmp->getProcs()->getObj($ii);
              break;
            }
          }
          
          if($tmpproc == null) { // create proc
            $ddl[] = $this->CreateFunctionDDL($obj, $proc, $argsStr);

            if(!empty($proc->Comment))
              $ddl[] = $this->CommentOn('FUNCTION', self::EscapeName($obj->getName()) . '(' . $argsStr . ')', $proc->Comment);
          } else { // merge proc
            $tmpargsStr = $this->splitStoredProcArgs($tmpproc);

            if(strtolower($proc->Language) != strtolower($tmpproc->Language) || strtolower($proc->Returns) != strtolower($tmpproc->Returns) || strtolower($argsStr) != strtolower($tmpargsStr)) {
              // needs to re-create function
              $ddl[] = 'DROP FUNCTION ' . self::EscapeName($obj->getName()) . '(' . $tmpargsStr . ');';
              $ddl[] = $this->CreateFunctionDDL($obj, $proc, $argsStr);

            if(!empty($proc->Comment))
              $ddl[] = $this->CommentOn('FUNCTION', self::EscapeName($obj->getName()) . '(' . $argsStr . ')', $proc->Comment);
            } else if($this->fixEOL($proc->SQL) != $this->fixEOL($tmpproc->SQL) || $proc->Volatility != $tmpproc->Volatility || $proc->Security != $tmpproc->Security || $proc->getProps()->get('pgsql::isstrict') != $tmpproc->getProps()->get('pgsql::isstrict')) {
              $ddl[] = $this->CreateFunctionDDL($obj, $proc, $argsStr);

              if($proc->Comment != $tmpproc->Comment)
                $ddl[] = $this->CommentOn('FUNCTION', self::EscapeName($obj->getName()) . '(' . $argsStr . ')', $proc->Comment);
            } else if($proc->Comment != $tmpproc->Comment) {
              $ddl[] = $this->CommentOn('FUNCTION', self::EscapeName($obj->getName()) . '(' . $argsStr . ')', $proc->Comment);
            }
          }
        }
      }
    }
    
    // -- Triggers --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Trigger', $filter_i)) > -1) {
      $obj = $ObjList->getObj($filter_i);
      $tmp = $DataDict->findObj($obj->getName());
      
      $lang = $obj->getProps()->get('pgsql:lang');
      if($lang == '') $lang  = 'plpgsql';

      if(!in_array(strtolower($lang), $supportedlang)) {
        self::Warn('Database doesn\'t support the language ' . $lang . '. Trigger ' . $obj->getName() . ' will be skiped.');
        continue;
      }

      if($tmp == null) { // create
        $funcname = 'trg_' . $obj->getTable()->getName() . '_' . $obj->getName() . time();
        $ddl[] = $this->CreateTriggerFunctionDLL($obj, $funcname);
        
        $ddl[] = $this->CreateTriggerDDL($obj, $funcname);

        if(!empty($obj->Comment))
          $ddl[] = $this->CommentOn('TRIGGER', self::EscapeName($obj->getName()) . ' ON ' . self::EscapeName($obj->getTable()->getName()), $obj->Comment);
      } else if(!$tmp instanceof Trigger) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else { // merge
        if($this->fixEOL($obj->SQL) != $this->fixEOL($tmp->SQL))
          $createFunc = true;
        else
          $createFunc = false;

        if($obj->Execute != $tmp->Execute || $obj->_ForEach != $tmp->_ForEach ||
        $obj->Events != $tmp->Events || $obj->getTable()->getName() != $tmp->getTable()->getName() ||
        $obj->getProps()->get('pgsql:args') != $tmp->getProps()->get('pgsql:args'))
          $createTrigger = true;
        else
          $createTrigger = false;

        $funcname = $tmp->getProps()->get('pgsql:proname');
        if($createFunc) {
          // test, if function only used by this trigger else use other function name
          $tgfrst = $this->query('SELECT COUNT(*) AS cnt FROM pg_catalog.pg_trigger WHERE tgfoid = ' . $tmp->getProps()->get('pgsql:foid'));
          $tgfcnt = pg_fetch_result($tgfrst, 0, 0);
          pg_free_result($tgfrst);

          if($tgfcnt > 1) {
            $createTrigger = true;
            $funcname = 'trg_' . $obj->getTable()->getName() . '_' . $obj->getName() . time();
          }

          $ddl[] = $this->CreateTriggerFunctionDLL($obj, $funcname);
        }
        
        if($createTrigger) {
          $ddl[] = 'DROP TRIGGER ' . self::EscapeName($obj->getName()) . ' ON ' . self::EscapeName($obj->getTable()->getName()) . ';';
          $ddl[] = $this->CreateTriggerDDL($obj, $funcname);
        }

        if(($createTrigger && !empty($obj->Comment)) || $obj->Comment != $tmp->Comment)
          $ddl[] = $this->CommentOn('TRIGGER', self::EscapeName($obj->getName()) . ' ON ' . self::EscapeName($obj->getTable()->getName()), $obj->Comment);
      }
    }
    
    // -- Views --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('View', $filter_i)) > -1) {
      $obj = $ObjList->getObj($filter_i);
      $tmp = $DataDict->findObj($obj->getName());
      
      if($tmp == null) { // create
        $ddl[] = 'CREATE VIEW ' . self::EscapeName($obj->getName()) . ' AS ' . $obj->SQL . ';';

        if(!empty($obj->Comment))
          $ddl[] = $this->CommentOn('VIEW', self::EscapeName($obj->getName()), $obj->Comment);

      } else if(!$tmp instanceof View) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else { // merge
        if($obj->SQL != $tmp->SQL) {
          $ddl[] = 'DROP VIEW ' . self::EscapeName($obj->getName()) . ';' .
            CRLF . 'CREATE VIEW ' . self::EscapeName($obj->getName()) . ' AS ' . $obj->SQL . ';';

        if(!empty($obj->Comment))
          $ddl[] = $this->CommentOn('VIEW', self::EscapeName($obj->getName()), $obj->Comment);
        } else {
          if($obj->Comment != $tmp->Comment)
            $ddl[] = $this->CommentOn('VIEW', self::EscapeName($obj->getName()), $obj->Comment);
        }
      }
    }
    

    // *Table
    // *View
    // *Sequence
    // *Index
    // *ConstUnique
    // *ConstCheck
    // *ConstPrimaryKey
    // *Reference
  }
  
  public function DoExecuteDDL($ddl) {
    if(!$this->isConnected()) $this->OpenConnect();
    try
    {
      $this->query('BEGIN');
      $this->query($ddl);
      $this->query('COMMIT');
    } catch (Exception $e) {
      $this->query('ROLLBACK');
      
      throw $e;
    }
  }
}

/*'abort','abs','absolute','access','action','ada','add','admin','after','aggregate','alias','all','allocate','alter','analyse','analyze','and','any','are','array','as','asc','asensitive','assertion','assignment','asymmetric','at','atomic','authorization','avg','backward','before','begin','between','bigint','binary','bit','bitvar','bit_length','blob','boolean','both','breadth','by','c','cache','call','called','cardinality','cascade','cascaded','case','cast',
'catalog','catalog_name','chain','char','character','characteristics','character_length','character_set_catalog','character_set_name','character_set_schema','char_length','check','checked','checkpoint','class','class_origin','clob','close','cluster','coalesce','cobol','collate','collation','collation_catalog','collation_name','collation_schema','column','column_name','command_function','command_function_code','comment','commit','committed','completion','condition_number','connect',
'connection','connection_name','constraint','constraints','constraint_catalog','constraint_name','constraint_schema','constructor','contains','continue','conversion','convert','copy','corresponding','count','create','createdb','createuser','cross','cube','current','current_date','current_path','current_role','current_time','current_timestamp','current_user','cursor','cursor_name','cycle','data','database','date','datetime_interval_code','datetime_interval_precision','day','deallocate','dec','decimal','declare',
'default','defaults','deferrable','deferred','defined','definer','delete','delimiter','delimiters','depth','deref','desc','describe','descriptor','destroy','destructor','deterministic','diagnostics','dictionary','disconnect','dispatch','distinct','do','domain','double','drop','dynamic','dynamic_function','dynamic_function_code','each','else','encoding','encrypted','end','end-exec','equals','escape','every','except','exception','excluding','exclusive','exec','execute','existing','exists','explain','external','extract',
'false','fetch','final','first','float','for','force','foreign','fortran','forward','found','free','freeze','from','full','function','g','general','generated','get','global','go','goto','grant','granted','group','grouping','handler','having','hierarchy','hold','host','hour','identity','ignore','ilike','immediate','immutable','implementation','implicit','in','including','increment','index','indicator','infix','inherits','initialize','initially','inner','inout','input','insensitive','insert','instance','instantiable','instead','int','integer','intersect','interval','into','invoker','is','isnull','isolation','iterate','join','k','key','key_member','key_type','lancompiler','language',
'large','last','lateral','leading','left','length','less','level','like','limit','listen','load','local','localtime','localtimestamp','location','locator','lock','lower','m','map','match','max','maxvalue','message_length','message_octet_length','message_text','method','min','minute','minvalue','mod','mode','modifies','modify','module','month','more','move','mumps','name','names','national','natural','nchar','nclob','new','next','no','nocreatedb','nocreateuser','none','not',
'nothing','notify','notnull','null','nullable','nullif','number','numeric','object','octet_length','of','off','offset','oids','old','on','only','open','operation','operator','option','options','or','order','ordinality','out','outer','output','overlaps','overlay','overriding','owner','pad','parameter','parameters','parameter_mode','parameter_name','parameter_ordinal_position','parameter_specific_catalog','parameter_specific_name','parameter_specific_schema','partial','pascal','password','path','pendant','placing','pli','position','postfix','precision','prefix',
'preorder','prepare','preserve','primary','prior','privileges','procedural','procedure','public','read','reads','real','recheck','recursive','ref','references','referencing','reindex','relative','rename','repeatable','replace','reset','restart','restrict','result','return','returned_length','returned_octet_length','returned_sqlstate','returns',
'revoke','right','role','rollback','rollup','routine','routine_catalog','routine_name','routine_schema','row','rows','row_count','rule','savepoint','scale','schema','schema_name','scope','scroll','search','second','section','security','select','self','sensitive','sequence','serializable','server_name','session','session_user','set','setof','sets',
'share','show','similar','simple','size','smallint','some','source','space','specific','specifictype','specific_name','sql','sqlcode','sqlerror','sqlexception','sqlstate','sqlwarning','stable','start','state','statement','static','statistics','stdin','stdout','storage','strict','structure','style','subclass_origin','sublist','substring','sum','symmetric','sysid','system','system_user','table','table_name','temp','template','temporary','terminate','than','then','time','timestamp','timezone_hour','timezone_minute',
'to','toast','trailing','transaction','transactions_committed','transactions_rolled_back','transaction_active','transform','transforms','translate','translation','treat','trigger','trigger_catalog','trigger_name','trigger_schema','trim','true','truncate','trusted','type','uncommitted','under','unencrypted','union','unique','unknown','unlisten','unnamed','unnest','until','update','upper','usage','user','user_defined_type_catalog','user_defined_type_name','user_defined_type_schema','using','vacuum','valid',
'validator','value','values','varchar','variable','varying','verbose','version','view','volatile','when','whenever','where','with','without','work','write','year','zone'
*/

?>