0

I am currently helping a friend reorganise several hundred images on a database driven website. I have generated a list of the new, reorganised image paths offline and would like to replace each matching image reference in the sql export of the database with the new paths.

EDIT: Here is an example of what I am trying to achieve

The new_paths_list.txt is a file that I generated using a batch script after I had organised all of the existing images into folders. Prior to this all of the images were in just a few folders. A sample of this generated list might be:

image/data/product_photos/telephones/snom/snom_xyz.jpg
image/data/product_photos/telephones/gigaset/giga_xyz.jpg

A sample of my_exported_db.sql (the database exported from the website) might be:

...

,(110,32,'data/phones/snom_xyz.jpg',3),(213,50,'data/telephones/giga_xyz.jpg',0),

...

The result I want is my_exported_db.sql to be:

...

,(110,32,'data/product_photos/telephones/snom/snom_xyz.jpg',3),(213,50,'data/product_photos/telephones/gigaset/giga_xyz.jpg',0),

...

Some pseudo code to illustrate:

1/ Find the first image name in my_exported_db.sql, such as 'snom_xyz.jpg'.

2/ Find the same image name in new_paths_list.txt

3/ If it is present, copy the whole line (the path and filename)

4/ Replace the whole path in in my_exported_db.sql of this image with the copied line

5/ Repeat for all other image names in my_exported_db.sql

A regex expression that appears to match image names is:

([^)''"/])+\.(?:jpg|jpeg|gif|png)

and one to match image names, complete with path (for relative or absolute) is:

\bdata[^)''"\s]+\.(?:jpg|jpeg|gif|png)

I have looked around and have seen that Sed or Awk may be capable of doing this, but some pointers would be greatly appreciated. I understand that this will only work accurately if there are no duplicated filenames.

Oliver
  • 3
  • 2

1 Answers1

0

You can use sed to convert new_paths_list.txt into a set of sed replacement commands:

sed 's|\(.*\(/[^/]*$\)\)|s#data\2#\1#|' new_paths_list.txt > rules.sed

The file rules.sed will look like this:

s#data/snom_xyz.jpg#image/data/product_photos/telephones/snom/snom_xyz.jpg#
s#data/giga_xyz.jpg#image/data/product_photos/telephones/gigaset/giga_xyz.jpg#

Then use sed again to translate my_exported_db.sql:

sed -i -f rules.sed my_exported_db.sql

I think in some shells it's possible to combine these steps and do without rules.sed:

sed 's|\(.*\(/[^/]*$\)\)|s#data\2#\1#|' new_paths_list.txt | sed -i -f - my_exported_db.sql

but I'm not certain about that.

EDIT<:

If the images are in several directories under data/, make this change:

sed "s|image/\(.*\(/[^/]*$\)\)|s#[^']*\2#\1#|" new_paths_list.txt > rules.sed
Beta
  • 96,650
  • 16
  • 149
  • 150
  • I *almost* follow what is going on, but I am encountering a more fundamental issue in testing: in both super sed and Gnused under win8, I encounter `'\' is not recognised as a internal or external command, operable program or batch file` . Hunting around this seemed to be an escape issue, so I placed it (minus the sed) in a script.sed and instead I get a `-e expression #1, char 10: unterminated 's' command`. I will continue to play around with it...thanks for your help so far! – Oliver Sep 06 '13 at 20:44
  • After some experimentation, under windows cmd, replacing the ' with " at the beginning and end fixed the above issue. Unfortunately, the Sed script doesn't quite do what I needed. It wasn't completely true that they are all in one big folder, so the initial list generated won't be correct. I don't think Sed is capable of doing this? – Oliver Sep 07 '13 at 10:48
  • Sorry I didn't get back to you sooner about the DOS/UNIX problem. Yes, sed can handle this new wrinkle (which you really should have mentioned at the outset). But first, was using double-quotes instead of single-quotes enough to make the above sed commands work? If not, then tell me what worked. – Beta Sep 07 '13 at 23:42
  • Yes, just using the quotes meant it worked exactly as it should - it generated rules.sed with the image name preceded by s#data/ and the new paths. Because not all of the images where in /data, it needed to be aware of the whole path in the original file. I've unfortunately slipped into using something a bit more comfortable to solve this problem.. knocking it out in VBA. The modified regex expressions for VBA are `[^)''""/\s]*.(?:jpg|jpeg|gif|png)` and `data/[^)''""\s]*.(?:jpg|jpeg|gif|png)`. I'm going to have to do some more reading on Sed as it seems to be insanely powerful used properly. – Oliver Sep 08 '13 at 10:47