Dump database data using Doctrine 2

Question

Is it possible to dump a database using doctrine 2? I have read that symfony has a library which extends doctrine to do it but How could I use it in my zendframework project with Bisna Doctrine 2 Integration?

Answer 1

For Symfony2:

Type

php app/console doctrine:schema:create --dump-sql

in the command line

Answer 2

This is an old thread but I was just doing something similar in Symfony and decided to develop an actual command for it. That's more of a Symfony way of doing it and gives you more control on the output as well as allowing you access to the parameters, so you don't have to parse Yaml using bash script :)

namespace Fancy\Command;

use Fancy\Command\AbstractCommand;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Filesystem\Filesystem;
use Symfony\Component\Filesystem\Exception\IOExceptionInterface;

class DatabaseDumpCommand extends AbstractCommand
{

    /** @var OutputInterface */
    private $output;

    /** @var InputInterface */
    private $input;


    private $database;
    private $username;
    private $password;
    private $path;

    /** filesystem utility */
    private $fs;

    protected function configure()
    {
        $this->setName('fancy-pants:database:dump')
            ->setDescription('Dump database.')
            ->addArgument('file', InputArgument::REQUIRED, 'Absolute path for the file you need to dump database to.');
    }

    /**
     * @param InputInterface $input
     * @param OutputInterface $output
     * @return int|null|void
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $this->output = $output;
        $this->database = $this->getContainer()->getParameter('database_name') ; 
        $this->username = $this->getContainer()->getParameter('database_user') ; 
        $this->password = $this->getContainer()->getParameter('database_password') ; 
        $this->path = $input->getArgument('file') ; 
        $this->fs = new Filesystem() ; 
        $this->output->writeln(sprintf('<comment>Dumping <fg=green>%s</fg=green> to <fg=green>%s</fg=green> </comment>', $this->database, $this->path ));
        $this->createDirectoryIfRequired();
        $this->dumpDatabase();
        $output->writeln('<comment>All done.</comment>');
    }

    private function createDirectoryIfRequired() {
        if (! $this->fs->exists($this->path)){
            $this->fs->mkdir(dirname($this->path));
        }
    }

    private function dumpDatabase()
    {
        $cmd = sprintf('mysqldump -B %s -u %s --password=%s' // > %s'
            , $this->database
            , $this->username
            , $this->password
        );

        $result = $this->runCommand($cmd);

        if($result['exit_status'] > 0) {
            throw new \Exception('Could not dump database: ' . var_export($result['output'], true));
        }

        $this->fs->dumpFile($this->path, $result); 
    }

    /**
     * Runs a system command, returns the output, what more do you NEED?
     *
     * @param $command
     * @param $streamOutput
     * @param $outputInterface mixed
     * @return array
     */
    protected function runCommand($command)
    {
        $command .=" >&1";
        exec($command, $output, $exit_status);
        return array(
              "output"      => $output
            , "exit_status" => $exit_status
        );
    }
}

and AbstractCommand is just a class that extends symfony's ContainerAwareCommand:

namespace Fancy\Command;

use Symfony\Component\HttpFoundation\Request;

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

abstract class AbstractCommand extends ContainerAwareCommand
{
}

Answer 3

Doctrine has no database-dump feature. I agree it would be nice, but it's also not the ORM's goal.

You could dump the database using

• a PHP script
• a system mysqldump
• phpMyAdmin

Here's an article explaining those solutions.

Answer 4

I created a small script that read the parameters from app/config/parameters.yml and output all the data from a MySQL database to a file (with current datetime used as name).

Save this in the root of your Symfony project (e.g. mysqldump.sh):

#!/bin/bash

# See http://stackoverflow.com/questions/59895/can-a-bash-script-tell-what-directory-its-stored-in/23905052#23905052
ROOT=$(readlink -f $(dirname "$0"))

cd $ROOT

# Get database parameters
dbname=$(grep "database_name" ./app/config/parameters.yml | cut -d " " -f 6)
dbuser=$(grep "database_user" ./app/config/parameters.yml | cut -d " " -f 6)
dbpassword=$(grep "database_password" ./app/config/parameters.yml | cut -d " " -f 6)

filename="$(date '+%Y-%m-%d_%H-%M-%S').sql"

echo "Export $dbname database"

mysqldump -B "$dbname" -u "$dbuser" --password="$dbpassword" > "$filename"

echo "Output file :"

ls -lh "$filename"

Result when running the script:

$ bash mysqldump.sh 
Export […] database
Warning: Using a password on the command line interface can be insecure.
Output file :
-rw-rw-r-- 1 […] […] 1,8M march   1 14:39 2016-03-01_14-39-08.sql

Answer 5

For a more generic doctrine way:

protected function execute(InputInterface $input, OutputInterface $output)
{
    $conn = $this->getDoctrineConnection('default');

    $path = $input->getArgument('filepath');
    if (! is_dir(dirname($path))) {
        $fs = new Filesystem();
        $fs->mkdir(dirname($path));
    }

    $cmd = sprintf('mysqldump -u %s --password=%s %s %s > %s',
        $conn->getUsername(),
        $conn->getPassword(),
        $conn->getDatabase(),
        implode(' ', ['variables', 'config']),
        $path
    );

    exec($cmd, $output, $exit_status);
}

Answer 6

Depend on your database. if you use mysql, create a php command to utilise mysqldump

like running this
mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sql