As you may know from an earlier post, in late June of this year, my website host disappeared from the web. I was able to recover everything on my main site thank goodness. The site I use to maintain evergreen links wasn’t quite so easy to recover, which I admit was totally my fault. But I was able to get the service up (YOURLS) and running and now I have a local database-driven system for keeping track of all manner of information about my books. Being forced to get organized has both expected and unexpected benefits. I am totally thrilled!

If you are an author and want some help with something like this, especially YOURLS, let me know. I’m happy to help.

MySQL GUI screenshot

Benefits!

Now I have a book database that will generate all the links I need for a new book which is super handy. While I was at it, I added in tables for all my ISBNs and various required info when books get uploaded to the various vendors, and now all this stuff is essentially automated and is going to save me so much time I’m practically giddy. I hadn’t realized what a mental burden the inefficiencies were.

Don’t worry! All this was, at most and in total, maybe a day and a half of work. I do database work for a living so I know now to build and populate a database.

Technical Details for Nerds

Once I had the link service database up and running, the next challenge was to recreate the short links and load them into the database. Trying to rebuild them manually would have been an utter nightmare and likely months of work. Instead, I built a local database designed to generate the flat files needed for a mass-upload plugin available for the service. My internal system for naming the short links meant I already knew the vast majority of the short links I needed to make and only needed to get the vendor URLS, which I already had in a spreadsheet. Once that was done, it only took a second (literally) to generate the csv files required for the plugin. It took way longer to upload the files via the plugin than it did to generate a file of hundreds of links. After that, all I needed to do was manually enter a handful of short links I’d made that didn’t conform to the standard.

This work uncovered a situation with two books where a vendor URL had inexplicably changed at the vendor and/or disappeared from the vendor site. I was able to fix both those, in one case with the assistance of the vendor’s tech support.

This weekend I installed MySQL locally, restored a backup of my short links database, and started building and testing the queries to do some direct backend updates related to cleaning up a test upload and doing future updates. SQL Server syntax is just different enough from MySQL syntax that I had to keep googling for the MySQL syntax but this morning I issued two of my backend updates.

Now I have the pieces in place to test my planned batch update process, and I’m actually pretty excited about having that capability since YOURLS isn’t set up to deal with the kind of short link system an author is likely to need; the ability to batch update a vendor URL from the pre on-sale URL to the on-sale URL, for example, across multiple short urls. I did have a plugin that was supposed to do this, but it was clunkier than I liked, and this is going to be way faster and less prone to error because I can test everything locally first.

And, of course, always backup the database first!