Yii Framework Forum: Execute sql file in migration - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Execute sql file in migration Rate Topic: ***** 2 Votes

#1 User is offline   TXGruppi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 22-July 09
  • Location:Rio de Janeiro, Brazil

Posted 17 February 2012 - 04:49 PM

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.

Attached File(s)


Tarcísio Gruppi
0

#2 User is offline   yiqing95 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 594
  • Joined: 27-December 10
  • Location:china

Posted 18 February 2012 - 01:10 AM

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

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 :lol:
0

#3 User is offline   temni 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 08-February 12
  • Location:Russia, Khb

Posted 26 March 2012 - 09:51 PM

Thank you, that works fine. Don't forget to add
'import'=>array(
		'application.components.*',
            )

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

#4 User is offline   cpttripzz 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 05-April 13

Posted 12 April 2013 - 06:04 AM

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 = '';
  }
}

0

#5 User is offline   anoopn 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 17-April 13

Posted 17 April 2013 - 07:13 AM

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.
0

#6 User is offline   daniloaz.com 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 11-June 13

Posted 11 June 2013 - 07:50 AM

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");
  }
  
}

0

#7 User is offline   Andres Felipe Diaz 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 13-August 12

Posted 19 July 2013 - 11:01 AM

Thank you guys! This worked great!
Andrés Felipe Díaz
Andrés Felipe Díaz Digital Geek Blog Trickortip.com
My LinkedIn Profile
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users