Not sure if this is applicable to your situation - but its a quick reply.

The policy was changed and it should be possible to update between beta releases. However there was an issue for the update between beta3 and beta4 which was mentioned in the announcement.

https://docs.joomla.org/J4.x:Upgrade_to_4.0_Beta_4

I know about the announcement of Beta 4. The SQL code was wrong and I told George how to fix it :)

I hadn't seen any information in the announcements about updating between betas being updatable and couldn't find any information on https://developer.joomla.org either (I can find the announcement but it's devoid of update information). Considering that for previous J4 alphas and 3.x betas we were explicitly told they are not upgradeable I reasonably assumed this is the case.

Back to the point about beta 4, I don't see a reason why a SQL update file wasn't included for these changes after the fact. If you look at https://github.com/joomla/joomla-cms/tree/4.0-dev/administrator/components/com_admin/sql/updates/mysql there are SQL files before and after these changes but NOT a SQL file for these changes mentioned in the announcement.

If the problem is that people think MySQL will fail if they try to create an existing column, sure, but it only means that they don't know how to use a database. Off the top of my head:

SELECT COUNT(*) INTO @exists FROM information_schema.columns  WHERE table_schema = database()
AND COLUMN_NAME = 'custom_data' AND table_name = '#__extensions';

set @query = IF(@exist <= 0, 'ALTER TABLE `#__extensions` ADD COLUMN `custom_data` text NOT NULL', 
'select \'Column Exists\' status');

prepare stmt from @query;

EXECUTE stmt;

I don't know if it's possible with PostgreSQL, I'm no expert in it, but I think that this trick would work there seeing that PREPARE exists on PostgreSQL as well.

I have no idea - I was just trying to give you some sort of answer instead of the question going unanswered

I figured as much :) This issue was mostly meant to serve as my public proof that I've already said updates are possible between betas and really from any one version to a newer one, even if the newer one is an alpha, beta or RC. Joomla already has the code infrastructure. What is missing is the skill pool in database management to fully realise the code's potential.

If @wilsonge takes a look at this issue I'm pretty sure he will be able to come up with a SQL file for the changes he already documented in the release announcement. This would allow people to upgrade from beta 1, 2 and 3 (and 4, of course) to beta 5 AND serve as an example of how you handle schema changes.

I had already said in 2015 that schema updates need to be idempotent. I had already tried that for a year in my extensions, eliminating support requests about schema issues. The rest of the Joomla 4 Working Group wanted to implement a full ORM or offered no opinion. Five years later Joomla 4 has not implemented a full ORM, kept its database installation architecture and only implemented an Active Record-style ORM in the new Model classes (based on my work in FOF 3's DataModel which is based on my work in AWF which is based on Laravel 3's Eloquent and yes I did give my permission, I'm not complaining about it).

If the architecture of schema updates was up to me I'd make it a rule to have separate SQL files for DDL and DML statements. I'd always make sure that DDL is wrapped in this kind of conditional statements, making it fully idempotent. Yes, idempotency is possible without using an ORM, as I had said. Even better, by raising the minimum database requirements, Joomla 4 has made it possible to have idempotent DDL SQL without resorting to XML files, like I have to do. Awesome! Or it would be, if Joomla took advantage of it.

Having idempotent DDL statements would not only make the Database Fix feature far easier to implement but also allow it to work regardless of the recorded schema version. You just need to find all DDL SQL files and run them in chronological order. It doesn't matter which table is missing or its schema is out of date. Idempotent DDL would take care of it.

In any case, I think this is something that could be implemented as early as Joomla 4.1. Preserving backwards compatibility with 3PD extensions which do not make use of idempotent SQL would be easy and boils down to having a different naming convention for legacy, mixed-use SQL files and new, idempotent SQL files. For example, using the extensions .ddl.sql and dml.sql would signal the use of idempotent DDL SQL whereas only using plain old .sql extensions would signal legacy SQL that needs to go through the legacy Database Fix code used in 3.x and 4.0. These are simple matters to solve technically and would make a big difference in the quality of life of users who sometimes find themselves dead in the water when a host restores an old database table or a table gets corrupt and needs to be reinstalled. Just my observation from interacting with a lot of users on a daily basis.

So whilst the upgrade works the schema checker/database fixer doesn't. That was the main issue. If you add a column then remove it again it causes issues with the checker. My recollection is that many hosts don't give permissions over the information_schema table (i know on postgres it's per db so it's fine - but mysql is global).

The information_schema table is always available. You cannot limit access to it. It is a virtual database which corresponds to what the database user is already authorized to view. For further information you can of course RTFM.

Even if you create a user which only has limited privileges you get INFORMATION_SCHEMA. You can check that out in cPanel, Plesk and other home-grown control panels. You can try it on your databases at home. You only need the SHOW privilege for the code I posted to work. Before you complain that this may not be available: it MUST be available for Joomla to work. Even Joomla 3. You are introspecting the table fields e.g. in JDatabaseDriverMysqli::getTableColumns() which is used by the Table class. SHOW FULL COLUMNS is functionally equivalent to accessing the INFORMATION_SCHEMA. But if you don't believe me you can of course use my trick above selecting from the SHOW FULL COLUMNS result (it is a result set!). So your protest can be translated to "but if the database server is non-standards-compliant and Joomla cannot run it I might not be able to install Joomla on it" which is, um, pointless. Not to mention that I've yet to see a database server where you can create tables but not show them, but that's a finer point.

Further to that, I am not making stuff up. For just over 5 years I had the Reverse Engineering database dump engine in Akeeba Backup which used the INFORMATION_SCHEMA as an alternative to SHOW statements. It worked with MySQL, PostgreSQL and SQL Server just fine. I never had any reports that it didn't work but the regular dump engine (using SHOW commands) did. Considering the market penetration of my software it's sure as hell not to cause a problem. It's an ANSI SQL standard after all. BTW: the only reason I removed support for INFORMATION_SCHEMA from Akeeba Backup is that I dropped support for non-MySQL databases so I had no reason to support two database dump engines for MySQL when one would work just fine :)

Regarding the Database Fix code being confused, there are two solutions: short term and long term. Both would need to be applied.

The obvious short term (4.0) solution is removing any SQL code which dropped the column and keeping only the code which adds it. Not ideal but beats the alternative of letting the poor user short it out.

The long term (4.1 onwards) solution is what I described: separating DDL (Data Definition Language) from DML (Data Manipulation Language) queries of each SQL file into two separate files, wrap the DDL in conditionals to make it idempotent and having the DB fixer simply run through all of the idempotent DDL, by definition fixing everything that's amiss.

So, basically, the problem is that you all didn't know, didn't ask, didn't read the manual and assumed you're doing a good job. That's not engineering. That's being lazy. I know that saying it out loud makes me unpleasant to work with. I'm here in this repo to do proper software engineering, not kiss anyone's ass or participate in an echo chamber. That's who I am. If you don't want me helping you fix Joomla just ban me from the repo.

The information_schema table is always available. You cannot limit access to it. It is a virtual database which corresponds to what the database user is already authorized to view. For further information you can of course RTFM.

Fair enough - I dug this out the tracker #9170 (comment) which i guess is whenever i formed this opinion - but if it's wrong it's wrong. I have no problem with that - makes life way way easier.

The obvious short term (4.0) solution is removing any SQL code which dropped the column and keeping only the code which adds it. Not ideal but beats the alternative of letting the poor user short it out.

This is what I wanted to do but as the column already exists in J3 - my understanding of doing that would be on upgrades from Joomla 3 you'd then try and re-add the column - which would already exist and therefore fail (hence rock and hard place and I prioritised getting J3 -> j4 migrations working than beta -> beta which i thought were my options)

The long term (4.1 onwards) solution is what I described: separating DDL (Data Definition Language) from DML (Data Manipulation Language) queries of each SQL file into two separate files, wrap the DDL in conditionals to make it idempotent and having the DB fixer simply run through all of the idempotent DDL, by definition fixing everything that's amiss.

I'm personally a fan of storing the files we've executed in the DB symfony style - but yeah of course there's nothing wrong with that either. I'm sure if anyone ever contributed either way we'd merge it. I've had so many discussions about this over my time in Joomla - but no one ever wants to go near that code (and I don't really blame them)

That's fine. By the lack of response to the 4.0 upgrade path does that mean that you don't have any ideas for the immediate beta to beta release? Just the 4.1 strategy of refactoring the migration schema?

Immediate beta to beta release: I thought it was obvious based on my previous description. I'd still create a SQL file which wraps the field creation in a conditional. Since the actual query is in a PREPARE statement I believe the database fixer wouldn't even see it. Since it's wrapped in conditionals it doesn't matter if you are upgrading from 3.10 (field exists) or 4.0 pre-beta4 (the field doesn't exist). Unless you see something I am missing?

As for 4.1, yeah, refactoring strategy.

For the quick 4.0 beta to beta fix I have a slightly different approach in mind, which would have the advantage to work also with PostgreSQL and not to confuse the schema checker.

But give me a bit time, tomorrow and weekend.

I'll make a PR with a proposal then.

For more fundamental changes, I can contribute with others, but it's a bit too big project for me alone.