Peter Artoung Peter Artoung - 1 month ago 30
SQL Question

Console command to import SQL Files Symfony

I would like know if someone had a solution to my needs:

In my project in src / AppBundle / SQL / MySQL, I have several SQL files, which contain informations for my database, I would like know if it was possible to execute a command that will erase data tables and running my SQL files to import the default informations?

Cordially.

Answer

If i understand your question, you want to create a command for destroying your database, recreate it and execute some sql files.

namespace AppBundle\Command;

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Application;
use Symfony\Component\Console\Input\ArrayInput;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\HttpKernel\KernelInterface;

class cleanDbAndRecreateCommand extends ContainerAwareCommand {

    protected function configure() {
        $this
            ->setName('app:cleanandcreate')
            ->setDescription("Clean and recreate database.");
    }

    /**
     * @param InputInterface $input
     * @param OutputInterface $output
     * @return int|null|void
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $kernel = $this->getContainer()->get('kernel');
        $env = $input->getOption("env");
        $pdo = $this->getContainer()->get('doctrine.dbal.default_connection')->getWrappedConnection();
        $dir = $this->getContainer()->getParameter('kernel.root_dir').'/sqls/';

        //beware this method will destroy your database!!!
        $this->dropAndRecreateSchema($kernel, $output, $env);

        //list app/sqls folder
        $commands = $this->readSqlsFolder($dir);

        if (count($commands) > 0) {

            foreach ($commands as $command) {
                try {
                    $statment = $pdo->prepare($command);
                    $statment->execute();
                    $output->writeln(sprintf("<info>%s</info>", $command));
                } catch (\PDOException $e) {
                    $output->writeln(sprintf("<error>%s</error>", $command));
                    $output->writeln(sprintf("<error>%s</error>", $e->getMessage()));
                }
                $output->writeln('---------------------------------------------');
            }
        }

    }

    /**
     * @param $dir
     * @return array
     */
    private function readSqlsFolder($dir)
    {
        $commands = [];

        if ($handle = opendir($dir)) {

            while (false !== ($entry = readdir($handle))) {

                if ($entry != "." && $entry != "..") {

                    $commands[] = file_get_contents($dir.$entry);
                }
            }

            closedir($handle);
        }

        return $commands;
    }

    /**
     * @param KernelInterface $kernel
     * @param OutputInterface $output
     * @param string $env
     */
    private function dropAndRecreateSchema(KernelInterface $kernel, OutputInterface $output, $env = "dev")
    {
        $application = new Application($kernel);
        $application->setAutoExit(false);

        $input = new ArrayInput(array(
            'command' => 'doctrine:database:drop',
            '--force' => true,
            '--no-interaction' => true,
            '--env' => $env,
        ));
        $application->run($input, $output);

        $input = new ArrayInput(array(
            'command' => 'doctrine:database:create',
            '--no-interaction' => true,
            '--env' => $env,
        ));
        $application->run($input, $output);

        $input = new ArrayInput(array(
            'command' => 'doctrine:schema:create',
            '--no-interaction' => true,
            '--env' => $env,
        ));
        $application->run($input, $output);
    }
} 

For this command to execute well, you have to create a folder inside app, called "sqls" and put your sql file in it.

After this you just have to run the command

php app/console app:cleanandcreate (symfony 2.x)

php bin/console app:cleanandcreate (symfony 3.x)

If you have questions, ask :)

Comments