db:dump - Limit product export

[mvenghaus]

Feature description

It would be nice if there would be an option to limit the products on export.

Problem and motivation

If you have a lot of products and stores it's a pain to update the whole db. It most cases you only need a few products to successfully work in local development.

Or is there already on option and I missed it!?

Maybe something like this:

n98-magerun2 db:dump --strip="@development" --limit="@catalog_product:1000 custom_tables_*:1000 custom_table:1000"

[cmuench]

@mvenghaus Nice idea. We use mysqldump in the background. I do not know if mysqldump supports that.

[mvenghaus]

@cmuench it should be possible with "--where"

[mvenghaus]

@cmuench .. i made a simple test and it worked. To make it work I've used a view to store the ids because LIMIT is not available in a subquery in mysql 5.7. But maybe my approach is too complicated ;)

mysql -u root -D lamp -e "DROP VIEW IF EXISTS product_ids; CREATE VIEW product_ids AS SELECT entity_id FROM catalog_product_entity ORDER BY entity_id LIMIT 20;"

mysqldump -u root --lock-tables=false -w "entity_id IN (SELECT entity_id FROM product_ids)" lamp catalog_product_entity >> dump.sql

mysqldump -u root --lock-tables=false -w "entity_id IN (SELECT entity_id FROM product_ids)" lamp catalog_product_entity_varchar >> dump.sql

mysql -u root -D lamp -e "DROP VIEW IF EXISTS product_ids;"

[cmuench]

@mvenghaus IMHO we should prevent sub-queries. Most of the ppl use db:dump for quick exports/backups.
Currently the db:dump command requires some refactoring work. Maybe we can optimize it first and then add additional features.

[peterjaap]

I've thought of this as well, but then for the sales_order table. Here's how I approached it:

1. Run magerun2 db:dump --strip=@stripped --only-command
2. Add --ignore-table=sales_order to the second command (the one that dumps the actual data)
3. Now add mysqldump --single-transaction --quick -h'db' -u'peterjaap' 'database_here' sales_order --where='entity_id<100'| LANG=C LC_CTYPE=C LC_ALL=C sed -E 's/DEFINER[ ]*=[ ]*[^`]+`@`[^`]+/DEFINER=CURRENT_USER/g' >> 'peterjaap_database_here.sql'

You'll end up with these 3 queries;

mysqldump --single-transaction --quick --no-data -h'db'  -u'peterjaap' 'peterjaap_database_here' email_abandoned_cart email_automation email_campaign email_catalog email_contact email_contact_consent email_failed_auth email_importer email_order email_review email_rules email_wishlist persistent_session report_compared_product_index report_event report_viewed_product_aggregated_daily report_viewed_product_aggregated_monthly report_viewed_product_aggregated_yearly report_viewed_product_index reporting_counts reporting_module_status reporting_orders reporting_system_updates reporting_users session | LANG=C LC_CTYPE=C LC_ALL=C sed -E 's/DEFINER[ ]*=[ ]*`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' > 'peterjaap_database_here.sql'

mysqldump --single-transaction --quick --ignore-table=peterjaap_database_here.email_abandoned_cart --ignore-table=peterjaap_database_here.email_automation --ignore-table=peterjaap_database_here.email_campaign --ignore-table=peterjaap_database_here.email_catalog --ignore-table=peterjaap_database_here.email_contact --ignore-table=peterjaap_database_here.email_contact_consent --ignore-table=peterjaap_database_here.email_failed_auth --ignore-table=peterjaap_database_here.email_importer --ignore-table=peterjaap_database_here.email_order --ignore-table=peterjaap_database_here.email_review --ignore-table=peterjaap_database_here.email_rules --ignore-table=peterjaap_database_here.email_wishlist --ignore-table=peterjaap_database_here.persistent_session --ignore-table=peterjaap_database_here.report_compared_product_index --ignore-table=peterjaap_database_here.report_event --ignore-table=peterjaap_database_here.report_viewed_product_aggregated_daily --ignore-table=peterjaap_database_here.report_viewed_product_aggregated_monthly --ignore-table=peterjaap_database_here.report_viewed_product_aggregated_yearly --ignore-table=peterjaap_database_here.report_viewed_product_index --ignore-table=peterjaap_database_here.reporting_counts --ignore-table=peterjaap_database_here.reporting_module_status --ignore-table=peterjaap_database_here.reporting_orders --ignore-table=peterjaap_database_here.reporting_system_updates --ignore-table=peterjaap_database_here.reporting_users --ignore-table=peterjaap_database_here.session --ignore-table=sales_order -h'db'  -u'peterjaap' 'peterjaap_database_here' | LANG=C LC_CTYPE=C LC_ALL=C sed -E 's/DEFINER[ ]*=[ ]*`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' >> 'peterjaap_database_here.sql'

mysqldump --single-transaction --quick -h'db'  -u'peterjaap' 'peterjaap_database_here' sales_order --where='entity_id<100'| LANG=C LC_CTYPE=C LC_ALL=C sed -E 's/DEFINER[ ]*=[ ]*`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' >> 'peterjaap_database_here.sql'

Which will create a partial dump of only the first 100 orders (of course this needs to be done for all other sales_order* tables as well).

These steps should be automated in the db:dump command. We just need to think a correct syntax. My proposal (with an array argument) is:

magerun2 db:dump --strip=@stripped --where "sales_order.entity_id < 100" "catalog_product_entity.type_id = 'simple'"

In the code, we can take that argument array, loop over it and explode on . to separate table from column and explode on any of the operands (=, <, <=, etc) to get the "where" statement. And then append that to the mysqldump command.