Skip to content

Commit 6d6bab7

Browse files
Implement CSV output mode (#92)
This change implements a new output mode for CSV files. The new command option `--output-csv=...` enables CSV output mode, and must be given the path to a directory. The output mode will create `.csv` files in the given directory, named according to the dumped tables. This output format does not support output redirection from `stdout` as the default MySQL SQL format does. CSV files will be created only for tables that contain data. In other words, `schema` type dumps will skip the table in question. Also, dumping views/triggers makes no sense for CSV files, they will be skipped as well. How to best write binary (BLOB) data and/or NULL values in CSV files is probably highly controversial. For now, we'll just go with the [`0x...` hex literals supported by MySQL](https://dev.mysql.com/doc/refman/8.0/en/hexadecimal-literals.html). Maybe having binary data in CSV files is not a sane idea in the first place. Co-authored-by: Matthias Pigulla <mp@webfactory.de>
1 parent 2119d7c commit 6d6bab7

File tree

3 files changed

+114
-7
lines changed

3 files changed

+114
-7
lines changed

README.md

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -67,6 +67,16 @@ do this by using the cli-parameter `single-line-insert-statements`. This can spe
6767
Example:
6868
`slimdump --single-line-insert-statements {DSN} {config-file}`
6969

70+
#### output-csv
71+
72+
This option turns on the CSV (comma separated values) output mode. It must be given the path to a directory where `.csv` files will be created. The files are named according to tables, e. g. `my_table.csv`.
73+
74+
CSV files contain only data. They are not created for views, triggers, or tables dumped with the `schema` dump mode. Also, no files will be created for empty tables.
75+
76+
Since this output format needs to write to different files for different tables, redirecting `stdout` output (as can be done for the default MySQL SQL mode) is not possible.
77+
78+
**Experimental Feature** CSV support is a new, [experimental feature](https://github.com/webfactory/slimdump/pull/92). The output formatting may change at any time.
79+
7080
## Configuration
7181
Configuration is stored in XML format somewhere in your filesystem. As a benefit, you could add the configuration to your repository to share a quickstart to your database dump with your coworkers.
7282

@@ -254,4 +264,4 @@ If you're a developer looking for new challenges, we'd like to hear from you! Ot
254264
- <https://www.webfactory.de>
255265
- <https://twitter.com/webfactory>
256266

257-
Copyright 2014-2020 webfactory GmbH, Bonn. Code released under [the MIT license](LICENSE).
267+
Copyright 2014-2022 webfactory GmbH, Bonn. Code released under [the MIT license](LICENSE).
Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,78 @@
1+
<?php
2+
3+
namespace Webfactory\Slimdump\Database;
4+
5+
use Doctrine\DBAL\Connection;
6+
use Doctrine\DBAL\Schema;
7+
use InvalidArgumentException;
8+
use Webfactory\Slimdump\Config\Table;
9+
10+
class CsvOutputFormatDriver implements OutputFormatDriverInterface
11+
{
12+
/**
13+
* @var Connection
14+
*/
15+
private $connection;
16+
17+
/**
18+
* @var string
19+
*/
20+
private $directory;
21+
22+
/**
23+
* @var ?resource
24+
*/
25+
private $outputFile = null;
26+
27+
/**
28+
* @var bool
29+
*/
30+
private $firstLine = true;
31+
32+
public function __construct(string $directory, Connection $connection)
33+
{
34+
if (!is_dir($directory) || !is_writable($directory)) {
35+
throw new InvalidArgumentException(sprintf('The directoy "%s" does not exist or is not writeable', $directory));
36+
}
37+
38+
$this->directory = $directory;
39+
$this->connection = $connection;
40+
}
41+
42+
public function beginDump(): void
43+
{
44+
}
45+
46+
public function endDump(): void
47+
{
48+
}
49+
50+
public function dumpViewDefinition(Schema\View $asset, Table $config): void
51+
{
52+
}
53+
54+
public function beginTableDataDump(Schema\Table $asset, Table $config): void
55+
{
56+
$this->firstLine = true;
57+
$this->outputFile = fopen($this->directory.\DIRECTORY_SEPARATOR.$asset->getName().'.csv', 'w');
58+
}
59+
60+
public function endTableDataDump(Schema\Table $asset, Table $config): void
61+
{
62+
fclose($this->outputFile);
63+
}
64+
65+
public function dumpTableStructure(Schema\Table $asset, Table $config): void
66+
{
67+
}
68+
69+
public function dumpTableRow(array $row, Schema\Table $asset, Table $config): void
70+
{
71+
if ($this->firstLine) {
72+
fputcsv($this->outputFile, array_keys($row));
73+
$this->firstLine = false;
74+
}
75+
76+
fputcsv($this->outputFile, $row);
77+
}
78+
}

src/Webfactory/Slimdump/SlimdumpCommand.php

Lines changed: 25 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -15,11 +15,14 @@
1515
use Symfony\Component\Console\Output\NullOutput;
1616
use Symfony\Component\Console\Output\OutputInterface;
1717
use Webfactory\Slimdump\Config\ConfigBuilder;
18+
use Webfactory\Slimdump\Database\CsvOutputFormatDriver;
1819
use Webfactory\Slimdump\Database\MysqlOutputFormatDriver;
1920
use Webfactory\Slimdump\Database\OutputFormatDriverInterface;
2021

2122
final class SlimdumpCommand extends Command
2223
{
24+
const OUTPUT_CSV = 'output-csv';
25+
2326
protected function configure()
2427
{
2528
$this
@@ -35,6 +38,12 @@ protected function configure()
3538
InputArgument::IS_ARRAY | InputArgument::REQUIRED,
3639
'Configuration files (at least one).'
3740
)
41+
->addOption(
42+
self::OUTPUT_CSV,
43+
null,
44+
InputOption::VALUE_REQUIRED,
45+
'Output .csv files. Requires a directory path where files will be created (no stdout).'
46+
)
3847
->addOption(
3948
'buffer-size',
4049
'b',
@@ -62,13 +71,20 @@ protected function configure()
6271
*/
6372
protected function execute(InputInterface $input, OutputInterface $output)
6473
{
74+
$progressOutput = $this->createProgressOutput($input, $output);
75+
6576
$connection = $this->createConnection($input);
6677
$connection->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
67-
$this->setMaxExecutionTimeUnlimited($connection, $output);
78+
$this->setMaxExecutionTimeUnlimited($connection, $progressOutput);
6879

6980
$config = ConfigBuilder::createConfigurationFromConsecutiveFiles($input->getArgument('config'));
70-
$outputFormatDriver = $this->createOutputDriverMysql($input, $output, $connection);
71-
$progressOutput = $this->createProgressOutput($input, $output);
81+
82+
if ($dir = $input->getOption(self::OUTPUT_CSV)) {
83+
$outputFormatDriver = $this->createOutputDriverCsv($dir, $connection);
84+
$progressOutput->writeln('<comment>CSV output format is still experimental – format details may change at any time</comment>');
85+
} else {
86+
$outputFormatDriver = $this->createOutputDriverMysql($input, $output, $connection);
87+
}
7288

7389
$dumptask = new DumpTask($connection, $config, $outputFormatDriver, $progressOutput);
7490
$dumptask->dump();
@@ -84,6 +100,11 @@ private function createOutputDriverMysql(InputInterface $input, OutputInterface
84100
return new MysqlOutputFormatDriver($output, $connection, $bufferSize, $singleLineInsertStatements);
85101
}
86102

103+
private function createOutputDriverCsv(string $directory, Connection $connection): OutputFormatDriverInterface
104+
{
105+
return new CsvOutputFormatDriver($directory, $connection);
106+
}
107+
87108
private function parseBufferSize(?string $bufferSize): ?int
88109
{
89110
if (null === $bufferSize) {
@@ -117,9 +138,7 @@ private function setMaxExecutionTimeUnlimited(Connection $connection, OutputInte
117138

118139
if ($maxExecutionTimeInfo && 0 != $maxExecutionTimeInfo['Value']) {
119140
$connection->executeQuery('SET SESSION max_execution_time = 0');
120-
if ($output instanceof ConsoleOutputInterface) {
121-
$output->getErrorOutput()->writeln('<info>The MySQL "max_execution_time" timeout setting has been disabled for the current database connection.</info>');
122-
}
141+
$output->writeln('<info>The MySQL "max_execution_time" timeout setting has been disabled for the current database connection.</info>');
123142
}
124143
}
125144

0 commit comments

Comments
 (0)