Thursday, October 27, 2022

[SOLVED] SED Match/Replace URL and Update Serialized Array Count

Issue

Below is an example snippet from a sql dump file. This specific row contains a meta_value of a Wordpress PHP serialized array. During database restores in dev., test., and qc. environments I'm using sed to replace URLs with the respective environment sub-domain.

INSERT INTO `wp_postmeta`
    (`meta_id`,
     `post_id`,
     `meta_key`,
     `meta_value`)
VALUES
    (527,
     1951,
     'ut_parallax_image',
     'a:4:{
          s:17:\"background-image\";
          s:33:\"http://example.com/background.jpg\";
          s:23:\"mobile-background-image\";
          s:37:\"www.example.com/mobile-background.jpg\";
      }')
;

However, I need to extend this to correct the string length in the serialized arrays after replace.

sed -r -e "s/:\/\/(www\.)?${domain}/:\/\/\1${1}\.${domain}/g" "/vagrant/repositories/apache/$domain/_sql/$(basename "$file")" > "/vagrant/repositories/apache/$domain/_sql/$1.$(basename "$file")"

The result should look like this for dev.:

INSERT INTO `wp_postmeta`
    (`meta_id`,
     `post_id`,
     `meta_key`,
     `meta_value`)
VALUES
    (527,
     1951,
     'ut_parallax_image',
     'a:4:{
          s:17:\"background-image\";
          s:37:\"http://dev.example.com/background.jpg\";
          s:23:\"mobile-background-image\";
          s:41:\"www.dev.example.com/mobile-background.jpg\";
      }')
;

I'd prefer to not introduce any dependancies other than sed.


Solution

WP-CLI handles serialized PHP arrays during a search-replace http://wp-cli.org/commands/search-replace/. I wanted to try a native shell solution, but having WP-CLI was worth the extra overhead in the end.



Answered By - Seth Reeser
Answer Checked By - Katrina (WPSolving Volunteer)
at