Possibility to limit push & pull to $prefix tables ?

[taliesinpenbardd]

Hey,

First, I'm a heavy user of Wordmove and I cannot thank you enough for that gem.

I'm used to work in confined environments, but sometimes I have to work with other people with debatable habits, and right now I'm dealing with a staging server with a LOT of tables on it, not relevant to the website I'm working on. Some of them have been either hacked or contain ununderstandable content, and Wordmove blocks on the db push/pull.

Could it be possible to define in the Movefile a db prefix (even if it's redundant with the wp-config.php file) or reuse the $wpdb->prefix parameter and push/pull ONLY the tables that begin with that prefix ?

That would simplify a lot my existence, right now.
By advance, thank you.

[alessandro-fazzi]

Hello @taliesinpenbardd ,

I'm really glad you enjoy using Wordmove doing your everyday work :)

You are speaking about dump only tables with prefix or to replace strings only on table with prefix?

We're compelling about opening to the user wp search-replace's options. But working with prefixed tables only is a real pain, moreover considering that we can't assume to have wp-cli installed on remote servers...

[taliesinpenbardd]

Hey @pioneerskies,

Yes, I was speaking of the dump part. If the hacked/unreadable tables would not be comprised in the dump, everything would be fine (for me at least) with the search-replace part.

For now, when I pull the website, the dump works fine, but the search-replace part fails because of unreadable characters. So I'm left with the files and no DB (or I have to sanitize by hand the downloaded dump file, before importing it locally). And of course, I can't db-push on the staging server, because I'd erase everything else that is not in my local DB.

I'm guessing that dumping only the tables I'm working on would streamline things and the search-replace part wouldn't fail - since I know what's in the tables. And to hell with the rest of the DB.

But I'm an absolute beginner in Ruby and I don't know if it's easy or not to implement. You tell me. :)

[alessandro-fazzi]

ATM we-re issuing a search-replace with --all-tables, which approach could be over-aggressive. Removing that option would let wp-cli to do search-replace only against the WP registered tables.

In this senario the entire DB would be dumped and moved, but only WP tables would be adapted.

Remotely dump only prefixed tables would mean to pass another option in movefile.yml, since it's not that easy to read the prefix from wp-config.php w/o using wp-cli. And the command to mysqldump only prefixed table would also be an ugly one...but this is not a primary problem.

I'm thinking loudly in order to clarify ideas on the table and to spot out needs and complications; thanks for discussing w/ me about this topic :)

[taliesinpenbardd]

My pleasure, if I can modestly help to improve Wordmove.

I've seen after a quick search that it's possible to do a partial mysqldump with selected tables. I understand it wouldn't be pretty. Yet it'd be less "over-aggressive", as you said, in my opinion.

Configuring a Movefile is a one-time thing (except if you move your site from a server to another regularly, which would be surprising). Adding one option to it isn't much of a work (for the end user, of course) - and could even allow to have a different prefix between local and distant databases (strange idea, but hey, why not ?).

I'm thinking aloud too, I have no idea on how much work it would be for you to implement this behavior. Of course, as this is not an issue, you can close/requalify this ticket. :)

[alessandro-fazzi]

Hello,

now that we have slowly moved along the way and we have declared wp-cli as a dependency, we have possibility to afford this feature in a secure, standard way.

I'm thinking about solving 2 different problems here:

1. dump only tables with specific prefix
2. dump only certain tables

The config inside movefile.yml would look like

database:
  name: 'mydb'
  password: 'mypwd'
  [...]
  tables:
    - 'wp_posts'
    - 'wp_users'

or

database:
  name: 'mydb'
  password: 'mypwd'
  [...]
  tables:
    - 'wp_*'

or a mix

database:
  name: 'mydb'
  password: 'mypwd'
  [...]
  tables:
    - 'custom_prefix_*'
    - 'wp_posts'

The implementation would rely on wp db tables command; slow (wp-cli is slow to load) but rock solid. I'd cycle over the array of strings tables and construct a string from the output of

wp db tables 'custom_prefix_*' --all-tables

intercepting the use of the wildcard and passing thus to wp-cli; or simply appending 'wp_posts' because no wildcard is used.

The resulting mysqldump command would be, e.g.:

mysqldump --host=localhost --user=root --result-file="/Users/fuzzy/dev/sshwordmove/wp-content/dump.sql" --default-character-set=utf8 sshwordmove wp_posts custom_prefix_table1 custom_prefix_table2

---

Tables would be specified for each environment (local and/or remote) based on the desired behaviour. Opening to scenarios where I can pull only wp_posts from production, but wp_users and wp_posts from staging.

---

Note that import command would not be altered, since it will import whatever was previously exported.

---

What do you think about?

/cc @colin-marshall @halvardos

[taliesinpenbardd]

Hey,

I've carefully read the talks that were linked to this post. I'm not alone!

I was just considering selecting the tables I'm working on and ignoring the others - but your call on selecting only one table (when there are only legit prefix_tables*) opens new horizons to me: the ability to push/pull only posts (alongside with their categories, tags, etc.) when it would only be possible with the painful export/import process.

Could you detect the use of a wildcard from inside the Ruby code (I'm certain you can, but please bear with me) ? Then if there is a wildcard, you could use wp db tables --all-tables-with-prefix "prefix_" (with an equivalent of the PHP function trim( 'prefix_*', '*' )) and if there's none, a simple wp db tables wp_table1 wp_table2....

Anyhow, I'm glad to see I may have to suffer less in a not too distant future...

[alessandro-fazzi]

I'm moving the whole conversation inside the wider #468 , thus closing this one. Pls follow me there :)

Just a note to @taliesinpenbardd : AFAIK --all-tables-with-prefix does not accept arguments, but reads the prefix from the wp-config.php and will list all the table with that prefix, including those tables which are not registered within the $wpdb global - but this is a theme/plugin developer choice.

Anyway we're on the path.