Hi,
For the past few days I've been working on
a small plugin which capitalizes a thread titles/post's first letter. Everything seems to be working fine but I need to know how can I undo a previously ran SQL query. The SQL query I'm talking about is: (thanks to Aries-Belgium for helping me with it!)
UPDATE ".TABLE_PREFIX."threads SET subject = CONCAT(UPPER(SUBSTRING(subject,1,1)),SUBSTRING(subject,2))
UPDATE ".TABLE_PREFIX."posts SET subject = CONCAT(UPPER(SUBSTRING(subject,1,1)),SUBSTRING(subject,2)), message = CONCAT(UPPER(SUBSTRING(message,1,1)),SUBSTRING(message,2))
For those wondering, this capitalizes the first letter of thread titles and posts. I could use exactly the same query and replace UPPER with LOWER, but that will un-capitalize the first letter of all existing threads - even if the first letter was already capitalized before installing the plugin. So my question is how can I undo that SQL query? Like if no query was actually ran. If that's possible, of course.
It's not possible. Unless you store somewhere whether the first letter was already capitalized or not. Even then, some people could be editing posts and changing their subjects.
If you want to make it undoable, then don't change it in the database at all, just make it appear differently in the forum anyway somehow.
Thanks for replying. I had a feeling it wasn't possible, but it's okay because I came to the conclusion that I shouldn't even be running that query in the first place.
You might consider doing something like the Hello World plug-in does. It just modifies the message on the forum level instead of database level.
I did that initially, but came to the conclusion that it would be better to modify things in the database as it updates all calls to the thread title and the post. Modifying the page output would require me to do a lot of editing, and even then some places would be left out because there are no hooks for them.
IMO you should not be altering data in the database that you can not revert down the line if the user decides they don't want the plugin.
In this case, you deal with it on the output leaving the database untouched or if you will be changing the data in the database then keep track of the data that you are changing in another table that you can use to revert to the old values.
Like I said, dealing with it in the output is too much work as there are a lot of places that need editing. Some places don't even have hooks which I can use to edit the page, meaning it wouldn't be 100% complete.
And what do you mean by keeping track of the data in another table? How does that work?
You could just add a column in the mybb_threads and mybb_posts tables for the original message and thread title.
hmm, I see. I think I could do that. Add a new column for the capitalized thread title/message, tell the plugin to use that value instead, and on deactivate go back to the original value.
Any other suggestions?
Doing it when outputting would be ideal but then you have to hook into every hook where threads/posts are used, if it is even possible. Not to mention that you have no control over other plugins that pull data directly from the database and don't provide any kind of extra hook. If there isn't a uniform way + a hook to fetch threads/ posts from the db, it's nearly impossible to cover all grounds.
Personally I would also use the database approach but I wouldn't bother saving the previous values from existing threads. It's going to take a lot of space and chances are that the user, who wished to have the first letter of threads/posts to be capitalized in the first place, will never uninstall that plugin. I would just warn the user that changes can't be reverted and then the user is still free to install it or not.