Is the Q2A Core missing plugin upgrade functions?

Question

A sample would better explain the question. Suppose you have created a plugin that needs a table. So you use the init_queries($tables) function to setup the creation of the table. However, as you are very perceptive :), you realize that you might update your plugin in the future so you decide to add a setting to ^options to track your plugin version (eg: myplugin_version).

So in your init_queries method you compare the version from the settings and the hardcoded version of the plugin. If they don't match then you should upgrade up to the hardcoded (latest) version. So far so good. In order to accomplish this there is a critical step which is making sure you increase the version setting to the latest one once the plugin has successfully been installed/upgraded.

Adding it to the end of the init_queries functions seems to be a good idea because you would be setting the new plugin version value once all queries have run successfully... however, it isn't as good as it seems. The init_queries won't actually execute the queries but rather "queue" them and return that queue so that they can be executed somewhere else. That's particularly bad because if you set the new plugin version there then the future plugin version number check will return that the plugin is updated but the queries might have failed in the function that called init_queries.

And it gets worse. Even if you assume the plugin queries will succeed (should not be assumed, though) the admin plugin page will refresh and show the "module requires some database initialization" message because the init_queries function will not return an empty result but rather all the db queries needed for the update. This is good. However, as you're setting the version setting before actually running the queries, the immediately following version check will return the newly updated version so even if the user clicks on the install button the page refresh will return that the DB is up to date.

It seems an OK (and not more than that) solution would be to add an on_successful_install() function to the core in order to hook the new version update there. I have already blamed the line in github where it should be added. Now that is on gid's hands but I need to fix this now, not in a future version. I believe I have the workaround in my head but I still need to implement it. Anyway, how do you guys solved this issue in the past? Any idea will be useful.

Comments

+1 very true thoughts

---

I also ran into this issue. I know, setting the version number before the query is risky - as you cannot know if the query was successful - but this is the only way to do it with q2a 1.6.3 and before.

Answer 1

Ok. The workaround seems to be working fine. This is how to do it. As you need to update the version if and only if the queries have run successfully the only way I could find is to update the version by an additional query. This means, add to the end of your upgrade an additional query that will perform an

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

That will make sure that the version will be updated if all the previous queries run succesfully.

I haven't actually faced this situation but it makes sense to me that the version stored in the DB now will not match the cached version of the setting, if any. So in the init_queries function you'll have to check the value straight to the DB, avoiding the cache. I found some functions that reload all settings from the DB but that seems to be an overkill. I ended up creating my own function.

Anyway, I would expect the core to have some more (success / failure) event handling in a near future.

Answer 2

Possibly I am still missing a key part of your question (do you have an example repo where you are using this code?) but I don't think you need to "bypass the cache" when updating options.

You can use the qa_opt function as normal: qa_opt('option-name', 'value');

With regards to knowing when to set options, all you need to do it check whether you need to upgrade your tables. If you do, return the query to upgrade. If you don't, set the option saying it's installed correctly. Here's what I do in my widget plugin:

    function init_queries( $tableslc )

    {

        $tablename = qa_db_add_table_prefix('widgetanyw');

 

        if ( !in_array($tablename, $tableslc) )

        {

            return 'CREATE TABLE .......';

        }

 

        // we're already set up

        qa_opt( $this->opt, '1' );

        return null;

    }

 

Hope that helps!

Comments

Thanks. Check the last sentence of the 3rd paragraph in the question. I will answer assuming you meant to create an array variable in which you stored the CREATE TABLE statement and then you return the variable (otherwise you wouldn't be executing the qa_opt, at least the first time).

This works if and only if the queries succeed and you can't be sure of that. Suppose the table already exists and you are altering it to add an index, column, whatever. Now, instead of ALTER TABLE you wrote ALTRE TABLE (just to think about any error during execution). The init_queries runs again (it must have run sometime before to create the table) and it checks for the version number (you haven't added that line in your code as you are checking for the table existence), and it returns a different version number so it detects it must upgrade.

Then, the ALTRE TABLE statement gets queued in the variable (there could be many other SQL statements after it that get queued in the returned variable, particularly if you need to upgrade from more than one version (I have a funny switch case in there to do so, although gideon uses a while loop). Now, you've enqueued all the queries you need and then you go to the version update section of you script. You chose to directly update the DB with qa_opt. However, you are assuming the ALTRE TABLE will succeed but it won't.

Now it is actually too late. The DB is updated with the new version number but the statement has just failed. The next time the code is executed it will check for that version number and will result in no upgrade to be executed as the number will be the last one while DB has the old schema. The easiest way I can think of to fix this seems to add an event. Let me know if you need some additional clarification.

---

"I will answer assuming you meant to create an array variable..."
If you only need one query you can just return it as a string. The docs don't mention it though - it's possible when plugins were first introduced you could only return one query.

"This works if and only if the queries succeed..."
No it doesn't. You check if the table exists, and if not then return the query. So Q2A says DB initialisation is required and runs the query. If the query fails, then the table still doesn't exist. When you go back to the plugins page it says initialisation is still required and the option has not been set because that part of init_queries is never reached.

If the query worked then when you load the page, init_queries sees the table exists and instead sets the option and returns null. So the option is only set once the query has succeeded.

"Suppose the table already exists and you are altering it to add an index, column, whatever."
Your situation is a bit more complex. You would need to add a check for whether the table has the correct columns. Not sure what the most efficient way for that is, probably using 'SHOW TABLE tablename' or something.

---

Ok, I see what you suggest but I can't see how it will work. Your solution, regardless of the code you provided, would be to infer the changes needed to perform to the DB based on the DB structure (eg: If you need to add a column on a plugin update then make sure the column is not there). This might become very complex (and I guess in some situations even impossible) because it is not just a matter of schema structure (DDL) but also table data (DML).

Now, I'm not sure if we're on the same page on the fact that a plugin version number tracker is a must. Even if you manage to successfully apply the previous paragraph to your update process you will need to know from which version to update, right? (Correct me here if you consider there is another way to know which version updates should be applied without tracking a version number. I believe it is not possible to infer the plugin version based on structure or data). So if you do need a plugin version, then why would you bother in applying the complex stuff from the previous paragraph if you could only check the version number and see what queries to run? From now on I will assume the version number approach is used.

So the issue that I mention in the question arises here which is: How to keep version_number and actual table structure and data in sync? That is actually the issue I'm trying to solve. Now, let's take a look at your code and see why it won't work (don't take it the wrong way, please, just working out a solution :)

Without applying the assumption I mentioned in my previous comment (the array / string variable to store the needed queries) you will lose version sync if you DDL statement succeeds. This is because you have RETURNed the string and have not run the qa_opt. You will need to run the init queries again to fix that.

Suppose you fixed that using the array / string variable and just append the DDL statement to it and return it just after the qa_opt. That seems to be bullet proof but you will lose sync if any SQL statement fails. Basically, you queue the queries in an array (let's take the array approach to handle many queries) and then, you update the version to the new one. The queries get, at a later point, executed and they fail. You now have a newer version number set but an old database schema or data.

Also, something missing in your code would be to actually check for the version number. You're only setting it.

Following a similar approach to the one gideon used, I just checked on the version number and started the upgrade progress from that point (a switch case without breaks helps remove the loop he uses). Now, he cheats :) He does not queue statements in there. He executes them directly so if any fails everything after it fails. The plugin just queues it doesn't execute anything. So in the plugin context... how do you know the queries have actually run properly?

Well, I cheated too. I queued the version variable update too into an SQL statement. So the version variable won't be updated if any previous SQL statement fails. Only issue is that the cache will not be in sync and should be updated or invalidated. And I couldn't find a better way to do that than the one explained in question #30899. Hope this is clearer now.

---

OK it seemed from your other question that you were running a query separately to update the version, but from your response here you must be queuing it inside init_queries.

I understand your predicament here. Certainly it would be technically possible to "infer the plugin version based on structure or data" - you'd check for each thing that changed (field names, types etc). But I didn't really consider how complex it would get over many versions.

I think your suggestion for a on_successful_install function is a valid one, though perhaps a system closer to the core update functionality would work better. I've made a note to check into this further.

Thanks for taking the time to explain your situation!

---

Based on your comments, I think you fully got what I tried to explain :)

And thank you too for taking the time to listen :D