Change the way tables/version numbers are stored

[rmorse]

Hey! Just saw you close down an old issue 🚀 and that reminded me to mention a couple of things I've been doing that might be of interest to this project.

Essentially, if we have many tables, we get a lot of DB look ups on the frontend.

I think there is on every page load, for every table:

• an existence check for the table
• a wp options lookup

When you have 8 tables, thats already 16 queries (arguably, very light) before we get to using them - I've had customers complain about this kind of thing, and whether or not it affects performance, I think it can be argued that it is a few too many db interactions.

So what I've done:

• 1 wp option (json) storing all the tables and their versions, rather than separate options calls
• prevent the existence checks if the table version numbers are present, only on the frontend.

Not sure if this is out of scope but it might be a nice optimisation.

Cheers!

[JJJ]

Hey back @rmorse and sorry to hear about your experience with upgrades and exists checks.

I'd like to learn more about how you've implemented Berlin into your project.

The default auto-install/upgrade experience is designed and intended to prevent exactly what you're reporting. We want:

• no overhead outside of WordPress Admin;
• only autoloaded options inside of WordPress Admin looking for upgrade opportunities;
• and the exists() check only happens when the option was not found (to verify that the table needs to be installed for the first time vs. being upgraded.)

That all implies to me that something isn't working right for you, and we should do some digging to figure out what that is.

Up until now, Berlin did not pass an explicit value into the option(s) autoload argument, which defaulted to auto, which typically means "yes autoload it" according to historical reference and also currently wp_autoload_values_to_autoload(), though I suppose it's possible some installs (or maybe a "performance" plugin) might change or filter which keys qualify.

In 3.1.0, I've made sure the autoload argument is explicitly true, so Berlin is doing everything it can there: 6642e0a

A future version of Berlin may evolve to have a dedicated (global) database table to manage features like upgrades, locks, uninstalls, health checks, and rollbacks. It appears that many other ORMs already do this, so it's not unusual. That would eliminate the need to rely on the options API at all, though it would instead get offloaded into a single cached query on every admin page-load on-top of autoloaded options, so it's theoretically worse 😆

If you don't mind sharing your code/repo, I'll take a look!

[JJJ]

I've added a few unit tests for the behaviors I outlined above to confirm things are working as intended, at least in a closed environment.

See e3213cc.