<?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 mysql extends basedb
{
  private $SupportViews = false;
  private $Version = 0;
  
  private $RefActionSQLArr = array(raNoAction => 'NO ACTION',
                                   raRestrict => 'RESTRICT',
                                   raCascade => 'CASCADE',
                                   raSetNull => 'SET NULL',
                                   raSetDefault => 'SET DEFAULT');

  private $RefMatchSQLArr = array(rmNoMatchType => '',
                                  rmMatchFull => 'MATCH FULL',
                                  rmMatchPartial => 'MATCH PARTIAL',
                                  rmMatchSimple => 'MATCH SIMPLE');

  protected function testRequs() {
    if(!extension_loaded('mysqli')) {
      throw new Exception('Extension mysqli 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');
    $dbschema = $this->getProp('dbschema');
    $dbport = $this->getProp('dbport');

    $this->dblink = @new mysqli($dbhost, $dbuser, $dbpasswd, $dbname, (int)$dbport);

    if(mysqli_connect_errno()) {
      $this->dblink = null;
      throw new Exception('Connect failed: ' . mysqli_connect_error());
    }

    $this->Version = $this->dblink->server_version;
    $this->SupportViews = ($this->Version > 50000);
  }

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

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

  public function testConnection()
  {
    $this->OpenConnect();
    print 'Connected to ' . $this->dblink->host_info . ', ' . $this->dblink->server_info;
  }
  
  private function query($query) {
    if(!($x = $this->dblink->query($query))) {
      throw new Exception('Cannot execute Query: ' . $this->dblink->error . CRLF . 'Query: ' . $query);
    }

    return $x;
  }
  
  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.');
  }
  
  public function DoImportDB(ObjList $ObjList, $filter) {
    if(!$this->isConnected()) $this->OpenConnect();

    // Insert Tables & Views
    if($this->SupportViews)
      $q = $this->query('SHOW FULL TABLES');
    else
      $q = $this->query('SHOW TABLES');

    $tbls = array();
      
    while(($rsettbl = $q->fetch_row())) {
      $rsettblstatus = $this->query('SHOW TABLE STATUS LIKE ' . self::EscapeStr($rsettbl[0]));
      $tblstatus = $rsettblstatus->fetch_assoc();
      $rsettblstatus->close();

      if($this->SupportViews && $rsettbl[1] == 'VIEW')  { // table is a view
        $viewname = $rsettbl[0];
        //   todo import
        if($ObjList->findObj($viewname) != null) {
          $viewname = $ObjList->freeName($viewname);
          self::Hint('View ' . $rsettbl[0] . ' renamed to ' . $viewname);
        }

        $view = new View($ObjList, $viewname);
        
        if(!empty($tblstatus['Comment']))
          $view->Comment = $tblstatus['Comment'];
        
      } else /*if($rsettbl[1] == 'BASE TABLE')*/ {
        $tblname = $rsettbl[0];
        
        if($ObjList->findObj($tblname) != null) {
          $tblname = $ObjList->freeName($tblname);
          self::Hint('Table ' . $rsettbl[0] . ' renamed to ' . $tblname);
        }

        $tbl = new Table($ObjList, $tblname);

        if(!empty($tblstatus['Comment']))
          $tbl->Comment = $tblstatus['Comment'];
          
        if(!empty($tblstatus['Type']))
          $tbl->getProps()->set('mysql::engine', $tblstatus['Type']);
          
        if(!empty($tblstatus['Engine']))
          $tbl->getProps()->set('mysql::engine', $tblstatus['Engine']);

        // Columns
        $fields = $this->query('SHOW FULL COLUMNS FROM ' . self::EscapeName($rsettbl[0]));
        while(($row = $fields->fetch_assoc())) {
          $col = $tbl->AddColumn($row['Field']);

          $t = $row['Type'];
          $p = strpos($t, '(');

          if($p === false) {
            $col->DataType = $t;
          } else {
            $col->DataType = substr($t, 0, $p);
            $p2 = strrpos($t, ')');
            
            if($p2 !== false) {
              $col->Size = substr($t, $p + 1, $p2 - 1 - $p);

              if(($attr = trim(substr($t, $p2 + 1))) != '')
                $col->getProps()->set('mysql::attr', $attr);
            } else
              self::Warn('Not a vaild data type. (' . $tbl->getName() . '.' . $col->getName() . ')');
          }

          if($row['Extra'] == 'auto_increment')
            $col->getProps()->set('mysql::auto_increment', '1');

          if(!empty($row['Default'])) {
            $col->DefaultValue = self::EscapeStr($row['Default']);
          }
          
          if(isset($row['Comment']))
            $col->Comment = $row['Comment'];

          $col->NotNull = $row['Null'] == '';
        }

        $fields->close();
        
        $tbls[$rsettbl[0]] = $tbl;
      }
    }

    $q->close();
    
    // Indexes
    foreach($tbls as $tblname => $tbl) {
      $idxs = $this->dblink->query('SHOW INDEX FROM ' . self::EscapeName($tblname));
      $index = array();

      while(($row = $idxs->fetch_assoc())) {
        $Key_name = $row['Key_name'];

        if(isset($index[$Key_name])) {
          $index[$Key_name]['cols'][] = $row['Column_name'];
        } else {
          $index[$Key_name] = array('cols' => array($row['Column_name']),
                                    'type' => $row['Index_type'],
                                    'non_unique' => $row['Non_unique']);
        }
      }
      $idxs->close();

      foreach($index as $name => $arr) {
        if($name == 'PRIMARY') { // Primary Key
          foreach($arr['cols'] as $col) {
            $c = $tbl->getColList()->findCol($col);
            if($c !== null)
              $c->PrimaryKey = true;
          }
        } else {

          $indexname = $name;
          if($ObjList->findObj($indexname) != null) {
            $indexname = $ObjList->freeName($indexname);
            self::Hint('Index ' . $name . ' renamed to ' . $indexname);
          }

          $const = new Index($ObjList, $tbl, $indexname);

          switch(strtolower($arr['type'])) {
            case 'fulltext':
              $const->IndexKind = $arr['type'] ;
              break;
            case 'spatial':
              $const->IndexKind = $arr['type'];
              break;
            default:
              $const->IndexType = $arr['type'];
          }

          if($arr['non_unique'] == '0') // unique
            $const->IndexKind = 'UNIQUE';

          foreach($arr['cols'] as $col)
            $const->AddColName($col);
        }
      }
    }

    // Update Primary Keys
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Table', $filter_i)) > -1)
        $ObjList->getObj($filter_i)->UpdatePrimaryKey();
  }
  
  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 EscapeStr($str) {
    return '\'' . $this->dblink->real_escape_string($str) . '\'';
  }
  
  private function getColumnDefinition(Column $col) {
    $sql = self::EscapeName($col->getName()) . ' ' . $col->getDataTypeSize();

    $attr = $col->getProps()->get('mysql::attr');
    if(!empty($attr))
      $sql .= ' ' . $attr;

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

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

    if($col->getProps()->get('mysql::auto_increment') == '1')
      $sql .= ' AUTO_INCREMENT';

    if(!empty($col->Comment))
      $sql .= ' COMMENT ' . $this->EscapeStr(substr($col->Comment, 0, 60));
    
    return $sql;
  }
  
  private function getIndexDefinition(Index $idx) {
    $def = '';

    if($idx->IndexKind != '')
      $def .= $idx->IndexKind . ' ';

    $def .= 'INDEX ' . self::EscapeName($idx->getName());

    if($this->Version >= 40100 && $idx->IndexType != '')
      $def .= ' USING ' . $idx->IndexType;

    $def .= ' (' . self::EscapeColList($idx->getColNames()) . ')';
    
    return $def;
  }
  
  public function DoGenerateDDL(ObjList $DataDict, ObjList $ObjList, &$ddl) {
    // -- Sequences --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Sequence', $filter_i)) > -1) {
      self::Hint('MySQL doesn\'t support Sequences.');
      break;
    }

    // -- 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()) . ' (';

        // -- Columns --
        for($i = 0; $i < $obj->getColList()->getCount(); $i++) {
          $col = $obj->getColList()->getCol($i);
          $sql .= ($i > 0 ? ',' : '') . CRLF2P . $this->getColumnDefinition($col);
        }

        // -- ConstUnique --
        // -- ConstCheck --
        // -- ConstPrimaryKey --

        $ConstSql = '';
        for($i = 0; $i < $obj->getRefListCount(); $i++) {
          $ref = $obj->getRef($i);
          if($ref instanceof Constraint) {
            $ConstSql .= ($ConstSql != '' ? ',' : '') . CRLF2P;

            if($ref instanceof ConstUnique) {
              $ConstSql .= 'CONSTRAINT UNIQUE ' . self::EscapeName($ref->getName());
              $ConstSql .= ' (' . self::EscapeColList($ref->getColNames()) . ')';
            } else if($ref instanceof ConstCheck) {
              //$ConstSql .= 'CHECK (' . $ref->getColString() . ')';
              self::Hint('MySQL doesn\'t support Check Constraints.');
            } else if($ref instanceof ConstPrimaryKey) {
              $ConstSql .= 'PRIMARY KEY (' . self::EscapeColList($ref->getColNames()) . ')';
            }
          } else if($ref instanceof Index) {
            $ConstSql .= ($ConstSql != '' ? ',' : '') . CRLF2P . $this->getIndexDefinition($ref);
          }
        }

        if($ConstSql != '')
          $sql .= ', ' . $ConstSql;

        $sql .= CRLF . ')';
        
        if($obj->getProps()->get('mysql::engine') != '') {
          $sql .= CRLF;
          $sql .= $this->Version >= 40018 ? 'ENGINE' : 'TYPE';
          $sql .= '=' . $obj->getProps()->get('mysql::engine');
        }

        if(!empty($obj->Comment))
          $sql .= CRLF . 'COMMENT=' . $this->EscapeStr(substr($obj->Comment, 0, 60));

        $ddl[] = '-- Create Table ' . $obj->getName() . CRLF . $sql . ';';
      } else if(!$tmp instanceof Table) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else { // merge
        $sqlarr = array();

        // -- 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
            $sql = 'ADD COLUMN ' . $this->getColumnDefinition($col) . ' ';

            if($i == 0)
              $sql .= 'FIRST';
            else
              $sql .= 'AFTER ' . self::EscapeName($obj->getColList()->getCol($i - 1)->getName());
              
            $sqlarr[] = $sql;
          } else {
            if($col->DataType != $dbcol->DataType || $col->Size != $dbcol->Size || $col->NotNull != $dbcol->NotNull ||
            $col->Comment != $dbcol->Comment ||
            $col->getProps()->get('mysql::attr') != $dbcol->getProps()->get('mysql::attr') ||
            $col->getProps()->get('mysql::auto_increment') != $dbcol->getProps()->get('mysql::auto_increment')) {
              $sqlarr[] = 'MODIFY ' . $this->getColumnDefinition($col);
            } else if($col->DefaultValue != $dbcol->DefaultValue) {
              $sql = 'ALTER ' .  self::EscapeName($col->getName()) . ' ';
              if(empty($col->DefaultValue)) {
                $sql .= 'DROP DEFAULT';
              } else {
                $sql .= 'SET DEFAULT ' . $col->DefaultValue;
              }

              $sqlarr[] = $sql;
            }
          }
        }


        // -- ConstUnique --
        // -- ConstCheck --
        // -- ConstPrimaryKey --

        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 ' . self::EscapeName($ref->getName());
              $ConstCols = self::EscapeColList($ref->getColNames());
            } else if($ref instanceof ConstCheck) {
              //$ConstType = 'CHECK ' . self::EscapeName($ref->getName());
              //$ConstCols = $ref->getColString();
              self::Hint('MySQL doesn\'t support Check Constraints.');
              continue;
            } 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) {
              $sqlarr[] = 'ADD ' . $ConstType . ' (' . $ConstCols . ')';
            } else if(!$dbref instanceof $ref) {
              self::WarnNoEqualObjs($ref, $dbref);
            } else {
              if($ref->getColString() != $dbref->getColString()) {
                if($ref instanceof ConstPrimaryKey) {
                  $sqlarr[] = 'DROP PRIMARY KEY';
                } else {
                  $sqlarr[] = 'DROP INDEX ' . self::EscapeName($ref->getName());
                }
                
                $sqlarr[] = 'ADD ' . $ConstType . ' (' . $ConstCols . ')';
              }
            }
          } else if($ref instanceof Index) {
            $dbref = $DataDict->findObj($ref->getName());

            if($dbref == null) {
              $sqlarr[] = 'ADD ' . $this->getIndexDefinition($ref);
            } else if(!$dbref instanceof $ref) {
              self::WarnNoEqualObjs($ref, $dbref);
            } else {
              if($ref->getColString() != $dbref->getColString() || $ref->IndexKind != $dbref->IndexKind || $ref->IndexType != $dbref->IndexType) {
                $sqlarr[] = 'DROP INDEX ' . self::EscapeName($ref->getName());
                $sqlarr[] = 'ADD ' . $this->getIndexDefinition($ref);
              }
            }
          }
        }

        $tblopts = '';
        if($obj->Comment != $tmp->Comment)
          $tblopts .= CRLF . 'COMMENT=' . $this->EscapeStr(substr($obj->Comment, 0, 60));
          
        if(count($sqlarr) > 0 || $tblopts != '') {
          $ddl[] = 'ALTER TABLE ' . self::EscapeName($obj->getName()) . CRLF2P .
            implode(',' . CRLF2P, $sqlarr) .  $tblopts . ';';
        }
      }
    }

    // -- Reference --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Reference', $filter_i)) > -1) {
      self::Hint('MySQL doesn\'t support to 100 % foreign keys. I must skip it.');
      break;
      //$obj = $ObjList->getObj($filter_i);
      //$tmp = $DataDict->findObj($obj->getName());
    }

    // -- Stored Procedures ---
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('StoredProc', $filter_i)) > -1) {
      self::Warn('Stored Procedures for MySQL not supported yet.');
      break;
    }

    // -- Triggers --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('Trigger', $filter_i)) > -1) {
      self::Warn('Triggers for MySQL not supported yet.');
      break;
    }

    // -- Views --
    $filter_i = -1;
    while(($filter_i = $ObjList->Filter('View', $filter_i)) > -1) {
      if(!$this->SupportViews) {
        self::Warn('Your MySQL Version doesn\'t support views.');
        break;
      }

      $obj = $ObjList->getObj($filter_i);
      $tmp = $DataDict->findObj($obj->getName());
      
      if($tmp != null && !($tmp instanceof View)) {
        self::WarnNoEqualObjs($obj, $tmp);
      } else {
        if($tmp != null && $tmp->SQL == $obj->SQL && $tmp->WithCheckOption == $obj->WithCheckOption &&
        $tmp->WithCheckOptionType == $obj->WithCheckOptionType)
          continue;
        
        $sql = 'CREATE OR REPLACE VIEW ' . self::EscapeName($obj->getName()) . ' AS ' . $obj->SQL;

        if($obj->WithCheckOption) {
          $sql .= ' WITH ';

          switch($obj->WithCheckOptionType) {
            case coCascade:
              $sql .= 'CASCADE';
              break;
            case coLocal:
              $sql .= 'LOCAL';
              break;
          }
          $sql .= ' CHECK OPTION';
        }
        
        $ddl[] = $sql . ';';
      }
    }


    // Table
    // View
    // *Sequence
    // Index
    // ConstUnique
    // ConstCheck
    // ConstPrimaryKey
    // Reference
  }
  
  private function query_parser($q) {
    $q = str_replace(array("\r\n", "\r"), "\n", $q);
    $qs = array();
    $l = 0;
    $in_string = '';
    $len = strlen($q);

    for($i = 0; $i < $len; $i++) {
      if($in_string == '') {

        switch($q[$i]) {
          case '\'':
          case '"':
          case '`':
            $in_string = $q[$i];
            break;
            
          case '#':
            $p = strpos($q, "\n", $i);
            if($p === false)
              $i = strlen($q);
            else
              $i = $p;

            break;

          case '-':
            if($i + 1 < $len && $q[$i + 1] == '-') {
              $p = strpos($q, "\n", $i);
              if($p === false)
                $i = strlen($q);
              else
                $i = $p;
            }

            break;

          case '/':
            if($i + 1 < $len && $q[$i + 1] == '*') {
              $p = strpos($q, "*/", $i);
              if($p === false)
                $i = strlen($q);
              else
                $i = $p + 1;
            }

            break;

          case ';':
            $qs[] = substr($q, $l, $i - $l);
            $l = $i + 1;
            //$i+=3;
            break;
        }

      } else {
        if($i + 1 < $len && (($q[$i] == '\\' && $q[$i + 1] == $in_string) || ($q[$i] == $in_string && $q[$i + 1] == $in_string))) {
          $i += 1;
        } else if($q[$i] == $in_string)
          $in_string = '';
      }
    }
    
    #print_r($qs);
    
    return $qs;
  }
  

  public function DoExecuteDDL($ddl) {
    if(!$this->isConnected()) $this->OpenConnect();
    try
    {
      $this->query('START TRANSACTION');

      foreach($this->query_parser($ddl) as $q)
        $this->query($q);

      $this->query('COMMIT');
    } catch (Exception $e) {
      $this->query('ROLLBACK');

      throw $e;
    }
  }
}

?>