Execute sql file in migration

Hi!

I made a method to execute a .sql file inside a migration.

I hope it help someone.

<?php


class TXDbMigration extends CDbMigration {

  

  protected function _infoLine($filePath, $next = null) {

    echo "\r    > execute file $filePath ..." . $next;

  }

  

  public function executeFile($filePath) {

    $this->_infoLine($filePath);

    $time=microtime(true);

    $file = new TXFile(array(

      'path' => $filePath,

    ));

    

    if (!$file->exists)

      throw new Exception("'$filePath' is not a file");

    

    try {

      if ($file->open(TXFile::READ) === false)

        throw new Exception("Can't open '$filePath'");

      

      $total = floor($file->size / 1024);

      while (!$file->endOfFile()) {

        $line = $file->readLine();

        $line = trim($line);

        if (empty($line))

          continue;

        $current = floor($file->tell() / 1024);

        $this->_infoLine($filePath, " $current of $total KB");

        $this->getDbConnection()->createCommand($line)->execute();

      }


      $file->close();

    } catch (Exception $e) {

      $file->close();

      var_dump($line);

      throw $e;

    }

    $this->_infoLine($filePath, " done (time: ".sprintf('%.3f', microtime(true)-$time)."s)\n");

  }

  

}

PS: Sorry about some english mistakes.

looks great! may be it can also as a CAction . thus we can config the path of sql file and execute it from controller ;

another feature : give a dir , iterate it and find all ".sql" files to execute .

don’t know your solution 's limitation , does’s it has size constraint

Thank you, that works fine. Don’t forget to add

'import'=>array(

		'application.components.*',

            )

into your config/console.php before you up migrations.

Very nice, Thanks alot

It is definitely a bit of pain to use the yii migration syntax when you have hundreds of existing tables

One addition I would make is allowing multi-line commands delimited by semicolon.

Here is the change I made:

Here is the change I made:

$command='';

while (!$file->endOfFile()) {

  $line = $file->readLine();

  $line = trim($line);

  if (empty($line))

    continue;

  $current = floor($file->tell() / 1024);

  $this->_infoLine($filePath, " $current of $total KB");

  $command .= $line;

  if (strpos($line,self::SQL_COMMAND_DELIMETER)){

  $this->getDbConnection()->createCommand($command)->execute();

    $command = '';

  }

}

Hi

could you please explain how to implement this one.

where to place these files and what command to be executed on command line

so that sql file can be migrated.

I’m new to yii framework , thanks for your all help.

Thanks TXGruppi and cpttripzz for your contribution. It was really useful for me. I made a small change to avoid comments before commands are treated as part of the command itself, wich results in no execution at all. Also SQL_COMMAND_DELIMETER constant was not defined anywhere:

<?php


class TXDbMigration extends CDbMigration {

  

  const SQL_COMMAND_DELIMETER = ';';

  

  protected function _infoLine($filePath, $next = null) {

    echo "\r    > execute file $filePath ..." . $next;

  }

  

  public function executeFile($filePath) {

      

    if (!isset($filePath)) return false;

    

    $this->_infoLine($filePath);

    $time=microtime(true);

    $file = new TXFile(array(

      'path' => $filePath,

    ));

    

    if (!$file->exists)

      throw new Exception("'$filePath' is not a file");

    

    try {

      if ($file->open(TXFile::READ) === false)

        throw new Exception("Can't open '$filePath'");

      

      $total = floor($file->size / 1024);

      $command = '';

      while (!$file->endOfFile()) {

        $line = $file->readLine();

        $line = trim($line);

        // Ignore line if empty line or comment

        if (empty($line) || substr($line, 0, 2) == '--')

          continue;

        $current = floor($file->tell() / 1024);

        $this->_infoLine($filePath, " $current of $total KB");

        $command .= $line . ' ';

        if (strpos($line,self::SQL_COMMAND_DELIMETER)){

            $this->getDbConnection()->createCommand($command)->execute();

            $command = '';

        }

      }


      $file->close();

    } catch (Exception $e) {

      $file->close();

      var_dump($line);

      throw $e;

    }

    $this->_infoLine($filePath, " done (time: ".sprintf('%.3f', microtime(true)-$time)."s)\n");

  }

  

}

Thank you guys! This worked great!