Fixpoint

I got a bit of a nerve pinched the other day by a seemingly innocent offer of help fixing a broken link. The pushback I gave at the time still looks valid from a certain angle, if not that important; yet the exchange exposed the sore spot that I hadn't even taken a relatively simple step in maintaining my old content, never mind consistency of new production. So, since there wasn't otherwise anything presently on fire - at least not besides the couple things on a low smolder in the corner where I can see them - I set aside those plans for the day that could be set aside and dug into updating my links to the #ossasepia channel logs formerly served by ossabot and its flask of pythons, based on the pointers provided by Diana. Since I took the time to work it out, I'll expand on those pointers to show my full process in hopes that it may be of use.

1. I downloaded, inspected and extracted the linked oldnewloglinks.tar.gz.⁽ⁱ⁾

2. It appeared that the old and new message ID numbers matched as expected, but I verified:

sed 's/[^# ]*#/#/g' ossapub.txt eulorapub.txt | awk '$1 != $2 { print }'

3. Thus, I chose to simplify the mapping and consequently reduce the number of blog-wide search-and-replaces by removing the line numbers and deduplicating; then to this I added a substitution to correct URL prefixes for the #eulora links:

sed 's/#[0-9]\+/#/g' ossapub.txt | uniq >ossapub-simp.txt
sed 's/#[0-9]\+/#/g' eulorapub.txt | uniq | sed 's:/ossasepia/:/eulora/:' >eulorapub-simp.txt

This brought the count from 206376 down to 1688.

4. I expanded this into a big update script using a template SQL command:

awk -v q="'" '{ print "update posts set post_content=replace(post_content," q $1 q "," q $2 q ");" }' ossapub-simp.txt eulorapub-simp.txt >replacements.sql

5. After a sanity check on the result I uploaded it to the server, then fed it to the database:

mysql fixpoint <replacements.sql

For my modestly sized blog this step took 34 seconds.

6. I checked some articles manually to make sure the results were as expected, but still wanted a way to see exactly what was changed by the hidden workings of the machine, so I did a fresh backup and set to compare it with the one made before. One trick to make "diff" sorta-work with MySQL dumps or other files with really long lines is to preprocess the input, breaking it up on syntactic or word boundaries to force it into diff's line-based view of the world. The following example of such munging also cleans up the linefeed characters that MySQL so uninvitedly backslashifies (and I haven't a clue how carriage returns got in there too):

sed -r 's/,/,\n/g; s/([^\\])\\n/\1\n/g; s/([^\\])\\r/\1/g'

This being hideous but likely to come in handy again, I named it as a script on my system.

All in all, the results had come out to a whole... 35 replacements. The #trilema links will be a tougher but perhaps juicier target to automate.

In case you'd like to work directly from my simplified mappings: ossapub-simp.txt, eulorapub-simp.txt.

1. I usually do a tar tvf FILENAME prior to extracting an unfamiliar tarball, to check for any weird paths that might clobber something unexpectedly in the working directory. [^]