The project im currently working on makes use of Spaties Event Projector package. Recently the team at Spatie upgraded the package to Version 2.
One of the changes that was made was to separate all storable events from regular events by moving them from the
App\Events to a new
App\StorableEvents folder. In moving the new stored events a new namespace of
App\StorableEvents was created.
Whilst this isn’t a mandatory change, all new events classes created get in this new namespace and for the sake of keeping code organised, I wanted to move all of our other storable events to this new folder.
Light work until I wanted to replay my events. What I’d forgotten is that the fully qualified class names are stored on the
stored_events table that the package uses.
No big deal I thought - ill just search for
App\Events\% and replace with
App\StorableEvents\%, how hard could that be in SQL? Turns out harder than I thought but not impossible…
I wasn’t aware of a search and replace feature in MySQL, and whilst the app is still in development I could have dropped the table and started over fresh.
However I had 100’s of rows of good (vs faker generated) test event data I’d put into the system and didn’t fancy starting fresh or updating each row by hand. There had to be a better way to do it.
Usually when I’m not sure on how to do something in SQL is to reach out to my Brother Nash. Not only is he awesome with SQL hes a MySQL certified DBA too. He said he wasn’t aware of a search and replace function where you were searching and replacing a substring of a column but to look up the
REPLACE function. And if that wasn’t fruitful he could always write me a quick stored procedure.
Whilst the offer was appreciated I 100000% never want to see or use a stored procedure ever again after having to work with them on an old project for over 10 years! So I set about looking for my magical 1 liner to solve the problem.
So it turns out that after some googling the statement I wanted looked something like this:
After plugging updating the query for my use case I had:
Except it wasn’t.
Because of annoying escape characters in MySQL. The updated query with backslashes escaped looked like:
Still no luck.
I decided at this point to check if I was selecting anything at all with that WHERE LIKE query.
SELECT * FROM stored_events WHERE event_class LIKE ('App\\Events%');
More googling later and it seemed a comical amount of backslashes were needed to escape a backslash in MySQL.
For the string
App\Events I needed 6 backslashes?!?!?!?! 6!!!! Good thing I could get away without searching for
SELECT * FROM stored_events WHERE event_class LIKE ('App\\\\\\Events%');
Updating this query would now return results.
With the new magic number of 6, it seemed fair to assume that 6 slashes would be needed for all backslashes. Finally I could update the
Rows found, None changed.
It turns out that within
REPLACE a backslash only needs to be escaped once, so
\\ or 2 backslashes would suffice.
Finally I actually had the query I was looking for, and it worked!
In fairness I spent probably far to long for something I probably could have done in PHP with using Laravel Tinker. That being said its always cool to dig into some of the lesser used day to day features of a technology you use regularly.
I learned something, and hopefully this time waste will help someone else out down the road at some point too!