Maho SQL upgrade scripts

[justinbeaty]

about the install scripts, do you think they fit the mage_core or mage_customer module better? we will have to bump the version number of a module and that eventually will cause conflicts with openmage. Obviously we can manage those conflicts manually when they will happen but, do you have any idea if this situation would be avoidable somehow? #36 (comment)

How about adding support for app/code/*/Module_Name/sql/module_setup/maho-install-xx.y.z files? At first I thought it wasn't a good idea, but then thought about it some more:

1. Add new column to core_resource called data_version_maho.
2. In Mage_Core_Model_Resource_Setup, load these files if data_version_maho < xx.y.z <= Mage::app()->getMahoVersion()
3. Update data_version_maho to the highest xx.y.z that ran for that particular resource. Most rows in that table will have data_version_maho == null for sometime unless that resource has a maho-install script.

Some thoughts:

• The current getMahoVersion() is 24.11.0, so if I wanted to add an install script in a PR I might name my file app/code/core/Mage_Customer/sql/customer_setup/maho-install-24.10.0.php since it's still October. By using the current month instead of the next expected version, you have flexibility in the release schedule. I.e. if you had named it maho-install-24.11.0.php, but then needed to make a release in October, the script wouldn't run
• For multiple PRs to the same resource setup, you could either use 24.10.1, or just merge the two scripts when merging the main branch into the PR. The former would be a problem if it's the release month, since 24.11.1 wouldn't be run for the 24.11.0 release. So probably stick with the merging them.
• We don't need to use a from and to version in the maho-install files. I can't think of any case where we have ever wanted to use a from version lower than the current release. It can cause a discrepancy anyway depending on if the user upgrades with every version vs sporadically.
• We don't have to keep bumping those arbitrary <version> nodes in the module's config.xml

Edit: clarified that each module can have their own maho-install scripts, instead of just one huge place for them.

[justinbeaty]

If you've ever seen this video, it perfectly sums up the experience right now.

Working on the EAV code, but first need to update the persistent module, but then need to figure out install script, but then...

[fballiano]

I think it's a great idea as usual, I started coding it, I hope I'll be able to show you something super soon!

[fballiano]

if we do maho-24.9.0.php, maho-24.9.1.php etc and they are run in sequence after the magento ones... I think we don't need much more, also because I hope we won't be changing the database that much :-D

[justinbeaty]

I just realized that if you want to have just one set of upgrade scripts for all modules, can't we just make a app/code/core/Maho/Core module? Pin the ordering in app/code/core/Mage/Core/Model/Config.php like was done with other core modules.

A more custom solution is good too, though. Edit: actually, a custom solution is probably better so we don't have to stuff everything into one upgrade-24.9.0-24.11.0.php script... Carry on. 😄

[fballiano]

I thought about it and it would be easier but I don't know... we'd need to load it after the rest of the modules (which is kinda weird) and it's "another module" when we're trying to remove them... and we could add a column to a table part of a disabled module... so all and all I think it's cleaner to have a "setup script" dedicated to maho for every module.

makes sense?

[justinbeaty]

Yup, makes sense and forget I mentioned it!

[fballiano]

I've created #38

[colinmollenhour]

If you're willing to make a major change, I suggest considering moving away from version numbers and instead use dates or arbitrary strings. There are cases where dependencies between modules cause problems such that updates belonging to modules A and B need to be applied in a specific order but using only dependencies and version numbers all of A has to be applied before any of B. Using dates you can say that A.1 applies, then B.1, then A.2, etc. Most if not all mature migration libraries use dates or arbitrary sortable strings like this for a long time. You typically insert a row for each migration rather than just store the latest one so that if a feature with an earlier sort order is merged later, you don't have to rename any files. This has a lot of advantages on busy projects and if a convention is used consistently like a timestamp format it makes it possible to solve dependency issues naturally.

You could maintain BC for existing migrations to the new system without having to rename them, keeping compat with third-party modules. For example, say that all new migrations under the new system could be named like 2024-10-3-my-upgrade.php and it would be assumed that all upgrade-X-Y.php files come first.

• Apply all version numbers for each module, one module at a time using dependency tree sorting
• Then apply all non-numbered upgrades in global sort order

[justinbeaty]

You are right, so then the timestamp method is definitely less bad than the current behavior.

Was I missing anything about the backwards compatibility issue? The PR @fballiano made already does run the maho scripts after all "legacy" scripts have run, so it's really just about ordering the maho ones better.

[fballiano]

I think that the timestamp based solution would be very interesting but

• it would require a huge work of converting/testing all the old install/data scripts (a lot of time we don't have with low returned value)
• it's true that those files are never updated but it would great a great deal of incompatibility between the codebases that, at the moment, I would avoid

@justinbeaty this would mean leaving everything as it already is now, ironing out the couple of bungs you find right?

[fballiano]

BTW thanks everybody for the great ideas

[justinbeaty]

We can leave the old install scripts as they are, so that it doesn't impact the way openmage or 3rd party modules work.

It would just be changing the way maho scripts are loaded. Currently the execution order is:

1. Mage/Core/sql/maho_setup/maho-24.10.0.php
2. Mage/Core/sql/maho_setup/maho-24.10.1.php
3. Mage/Checkout/sql/maho_setup/maho-24.10.0.php

Colin proposes it should be:

1. Mage/Core/sql/maho_setup/maho-24.10.0.php
2. Mage/Checkout/sql/maho_setup/maho-24.10.0.php
3. Mage/Core/sql/maho_setup/maho-24.10.1.php

---

I think we have three options:

1. Leave as it is now, just fix bugs.

2. Change the load order, but don't create a table to track every ran script. Just keep recording the highest maho-xx.y.z.php that ran in core_resource like your implementation already does. PRs must rename maho upgrade scripts to account for earlier merged scripts. I.e. don't merge maho-24.10.1.php before maho-24.10.0.php.

3. Change the load order, and track every ran script in a new table. This would allow maho-24.10.1.php to be run first, and still later run maho-24.10.0.php. Load order would be different if the user upgrades vs fresh install.

I think that since Maho versions are already dates, we can implement any of these at a later time and stay BC, as long as we don't do stuff like make Mage/Checkout/sql/maho_setup/24.10.0 rely on changes in Mage/Core/sql/maho_setup/24.10.1 in the meantime.

I would probably vote for option 1 right now, and option 2 at a later time.

[fballiano]

I think that, for the foreseeable future, option 1 is ok, we should refrain from modifying the db too much anyway and having that freedom could even be worse 😅