[Feature]: db-verify — validate Extended-User-Field columns against user_extended_struct

[Jimmi08]

Problem or motivation

The admin Database check (db-verify) only compares the static table
definitions in core_sql.php against the live schema
(db_verify_class.php line 166: $core_data = file_get_contents(e_CORE . 'sql/core_sql.php');).

Extended-User-Field (UEF) columns never appear in core_sql.php. When an
admin adds a UEF, core runs an ALTER TABLE on the fly to add a
user_<name> column to user_extended, driven by whatever is in
user_extended_struct (user_extended_class.php line 1028:
$sql->gen('ALTER TABLE #user_extended ADD user_'.$tp->toDB($name, true).' '.$field_info);).

Because those columns are defined at runtime by the admin, not at build
time in an SQL file, db-verify sails past them and reports everything as
fine — even when the column is actually missing.

Concrete case: I exported the UEF struct from one site to another, but the
matching user_extended columns were not created on the target table.
Database check reported all tables OK, so the problem stayed invisible
until things broke at runtime.

Proposed solution

Since core is what creates these columns, core is the natural place to
confirm they exist. db-verify already diffs every other table against an
expected definition — the same approach can be applied here:

1. For each row in user_extended_struct, rebuild the expected
   user_<name> column definition from the struct data (name + field type
   / user_extended_struct.user_extended_struct_type), the same way the
   runtime ALTER TABLE builds it.
2. Diff that expected column set against the live user_extended table.
3. Report missing or mismatched columns in the db-verify results, ideally
   with the same "fix"/repair action db-verify offers for other tables, so
   the missing ALTER TABLE can be re-run.

This would make the UEF struct and the physical user_extended columns
verifiable together, instead of trusting that they never drift.

Alternatives considered

Originates from discussion:
#5586

Relevant code:

• db_verify_class.php line 166 — db-verify reads only core_sql.php.
• user_extended_class.php line 1028 — runtime ALTER TABLE that adds the
  UEF column.

Additional context

No response

[e107help]

Ahoj @Jimmi08, your analysis lands squarely. db-verify genuinely cannot see these columns, and there are two reasons stacked on top of each other.

The first is the one you spotted: load() only ever reads the static SQL files, so anything not written into an .php schema is invisible to it.

db_verify_class.php#L166

The UEF columns are never in core_sql.php because core writes them at runtime from the struct, exactly as you said:

user_extended_class.php#L1028

The second reason is quieter and worth flagging, because it means even a present user_extended table would not be checked the way other tables are. db-verify special-cases user_extended so that a missing extra field is never flagged as an error:

db_verify_class.php#L1994

And the helper that lists tables drops both user_extended and user_extended_struct outright:

db_verify_class.php#L2280-L2281

So the column drift you hit on the import is not an oversight db-verify could have caught, it is a check that was switched off on purpose for this table. Your concrete case (struct exported, columns not created, everything reported OK) is the textbook symptom of that.

Your proposed shape is the right shape. Rebuilding the expected user_<name> definition from each user_extended_struct row, using the same field-type mapping the runtime ALTER TABLE uses, and diffing against the live table is exactly how db-verify treats every other table, so the repair/fix action could plug into the existing machinery rather than needing a new path. The one wrinkle to watch is keeping the expected-definition builder and the runtime ADD builder in sync, since if they drift you would get false repairs, so ideally both would share one source of truth for the column SQL.

Whether this gets built into core db-verify, and whether it ships with the auto-fix ALTER TABLE re-run or starts as report-only, is a call that belongs to Deltik. The diagnosis is solid and the request is well-scoped, so it is a good candidate. Linking the originating discussion #5586 was helpful, ďakujem.