This PR adds faker integration to slimdump with two options:

• All "official" faker properties which need no arguments like lastname or word (see https://github.com/fzaninotto/Faker#basic-usage ) when prefix FAKER_ is used (e.g. FAKER_word)
• One additional (can be extended) option "FAKER_name" which returns firstname and lastname separated by space.

Please comment before merging because I would like to add user documentation if everything else was discussed!

Of cause it is still possible to replace column value with static content:

<?xml version="1.0" ?>
<slimdump>
    <table name="users" dump="full" keep-auto-increment="false" condition="`signup_date` >= '2017-01-20 00:00:00'">
        <column name="password" dump="replace" replacement="XXXXX" />
        <column name="username" dump="replace" replacement="FAKER_name" />
    </table>
</slimdump>

Hey guys, I saw that "name" is already (is it new, I never saw it before?! :D) an "official" faker formatter.

So I deleted the self-written formatter. I decided to keep the configuration skeleton because I am sure we will need individual stuff in the further development of slimdump.

When I use Slimdump to dump the database SQL and put the contents to a file I can not import the SQL file because of the output from the ProgressBar inside Dumper. If I use verbosity 'quit' or the option --no-ansi the contents will be completely empty.

Is it possible to make the ProgressBar "listen" to the Output object if it may display its contents?

Thanks in advance!

We're using SHOW CREATE TABLE to get the table creation SQL, but that includes the AUTO_INCREMENT value (at least for some storage engines).

Depending on the use case, it would be nice to be able to get rid of that.

util.php only contains unused functions + the connect function, which is only used exactly one time in the SlimdumpCommand.php file, and I think, this is where the function belongs.

In dumptask.php function dump() a new instance of Dumper is created with output but without the buffersize variable. This causes an issue with larger queries and timeouts.

I've used keep-auto-increment="false" in schema dumps to keep things clean. My false expectation was that keep-auto-increment="false" would only remove the AUTO_INCREMENT=x part in the table generation DDL where the offset is set. But it also removes the AUTO_INCREMENT property off the column.

Hence, if the AUTO_INCREMENT column has a unique constraint (which it has to have at least in MySQL 5.x), INSERTs on tables based on such schema dumps fail if they don't specify a value for the AUTO_INCREMENT column. This seems to be a subtle source of errors.

Is this the intended behaviour? In this case, I'd like to see a warning in the documentation; otherwhise I suggest to change the behaviour / add a new option.

Sometimes, I end up with dumps like this:

CREATE TABLE A (
  `id` int(11) NOT NULL,
  `b_id` int(11) DEFAULT NULL,
  UNIQUE KEY (`b_id`),
  CONSTRAINT FOREIGN KEY (`b_id`) REFERENCES `B` (`id`)
) ENGINE=InnoDB;

CREATE TABLE B (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;

As table B does not yet exist while creating table A that references it in a constraint, the import of such dumps fail. At least for the time being, this cannot be fixed with a transaction.

Hence, I propose to postpone adding the constraints to a time when all tables have been created - as phpMyAdmin does. A working dump could look like this:

CREATE TABLE A (
  `id` int(11) NOT NULL,
  `b_id` int(11) DEFAULT NULL,
  UNIQUE KEY (`b_id`)
) ENGINE=InnoDB;

CREATE TABLE B (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;

ALTER TABLE `A` ADD CONSTRAINT FOREIGN KEY (`b_id`) REFERENCES `B` (`id`);

In our project, it is not possible to count all rows in our biggest table (very bad performances) before exporting a small subset of this table.

We need to apply the same condition as the one in the XML configuration file which for some reason is ignored when estimating the number of rows that will be exported.

Im doctrine/dbal Projekt gibt es schon einen pull-request zu diesem Problem: https://github.com/doctrine/dbal/pull/2504

Ein Beispiel: bin/slimdump mysql://user:U/js!jds*js(epupi@host:port/datebase live2dev.xml

gibt zurück: -bash: !jds*js: event not found

Das würde allein schon wegen Bash nicht funktionieren, wenn wir die Sonderzeichen also escapen: bin/slimdump mysql://user:U/js!jds*js(epupi@host:port/datebase live2dev.xml

bekommen wir: Database error: Malformed parameter "url".

da die URL nicht enkodiert ist. Dieses Problem ist jedoch phlough spezifisch.

HIER DER SLIMDUMP BETREFFENDE TEIL: angenommen wir bekommen von phlough eine enkodierte url zurück, dann würde unser slimdump Befehl folgendermaßen aussehen: bin/slimdump mysql://user:U%2Fjs%21jds%2Ajs%28epupi@host:port/datebase live2dev.xml

Dann bekommen wir [PDOException] SQLSTATE[28000] [1045] Access denied for user '...'@'localhost' (using password: YES)

Das kommt daher, dass, wie im oben genannten github pull request auf doctrine/dbal, die url nicht dekodiert wird. Es bleibt also abzuwarten, dass dieser pull request gemerged wird.

If OutputInterface::OUTPUT_RAW is not added, the strings to be written will be passed through output processors that will do some fancy replacements/addtions. That greatly increases the memory footprint so we avoid it.

Assume your database contains a table with a definition like so:

CREATE TABLE `test` (
  `some_col` int NOT NULL COMMENT '(DC2Type:some_type)'
)

In your application, you may have configured Doctrine with some special (custom) mapping type, thus the comment (DC2Type:some_type).

When trying to dump this database, slimdump fails with an exception when trying to load the schema information, since the some_type type is not known to DBAL.

It should not be necessary to configure this type either; in fact, we (probably) don't care about types that much, we just need to decide whether or not we're dealing with BLOB values.

Probably we need to come up with a clever way of how/when to register placeholder types dynamically?

I found myself in a scenario where I only wanted to replace column values if a condition was met (e.g. replace email column value only if domain is not 'example.com').

So I hacked together a Replacement element that you can use as child of a Column element. Example:

<?xml version="1.0" ?>
<column name="test" dump="replace">
    <replacement value="updated"/>
</column>

For testing the column value against a condition, the "strategy" and "constraint" attributes are available. In the example below, columns with value "baby yoda" would be replaced by "grogu":

<?xml version="1.0" ?>
<column name="test" dump="replace">
    <replacement strategy="eq" constraint="baby yoda" value="grogu"/>
</column>

At the moment, 3 comparison strategies are defined: "eq" (equals), "neq" (not equals), and "regex".

A Column can have many Replacement elements. The first one where the column value matches the constraint defines the replacement value. In the example below, "pattern" would be replaced by "A", "something" by "B" and all other values by "fallback".

<?xml version="1.0" ?>
<column name="test" dump="replace">
    <replacement strategy="regex" constraint="/^pattern$/" value="A"/>
    <replacement strategy="eq" constraint="something" value="B"/>
    <replacement value="fallback"/>
</column>

A Column with "replacement" attribute still works like it used to and it overrides any Replacement elements.

I am not sure about the API (Replacement elements with strategy and constraint attributes) nor if more people will find this useful, but it did the trick for me and I decided to share it here. Please let me know what you think.

Best.

This PR adds the ability to call more complex faker methods.

It follows an easy convention: FAKER_methodName:arguments The arguments of the faker method can be separated by a comma.

Examples:

<?xml version="1.0" ?>
<slimdump>
    <table name="users" dump="full">
        <column name="username" dump="replace" replacement="FAKER_word" />
        <column name="password" dump="replace" replacement="test" />
        <column name="amount" dump="replace" replacement="FAKER_numberBetween:1,100" />
        <column name="lastname" dump="replace" replacement="FAKER_numerify:'Helo ###'" />
        <column name="email" dump="replace" replacement="FAKER_unique->randomDigitNot:0" />
    </table>
</slimdump>

Edit: I haven't changed the README yet, as I wasn't sure if you want this PR. If this gets merged, I can prepare a PR that modifies the documentation.

Since v1.10, slimdump will also dump VIEW definitions.

One possible issue with that is that one VIEW might be built on top of another one. To make it possible to load the dump into an empty database schema, we need to take these dependencies between VIEWs into consideration.

One approach might be to build something like a dependency graph and dump the VIEWs in the right order. I'd suspect that there cannot be cycles for logical reasons.

The other solution would be to have a look at how mysqldump solves this. In fact, it creates something like temporary placeholders for views and replacing those in a final phase.

/* many lines removed for clarity ... */
CREATE TABLE `test` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!50001 CREATE VIEW `view1` AS SELECT 1 AS `id`*/;

/*!50001 CREATE VIEW `view2` AS SELECT 1 AS `id`*/;

--
-- Final view structure for view `view1`
--

/*!50001 DROP VIEW IF EXISTS `view1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`...`@`...` SQL SECURITY DEFINER */
/*!50001 VIEW `view1` AS select `view2`.`id` AS `id` from `view2` */;

--
-- Final view structure for view `view2`
--

/*!50001 DROP VIEW IF EXISTS `view2`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`...`@`...` SQL SECURITY DEFINER */
/*!50001 VIEW `view2` AS select `test`.`id` AS `id` from `test` */;

It would be helpful if we could have some version information printed on startup or with a command line switch like --version.

Not sure how to best obtain that information, though.

usecase:

Given you have a table to select which records to export, but in the end result they all need to be set to other values.

It would be awesome to have some kind of replacement table:

a —> b
c —> d

And similar.