Support for read-only MySQL generated columns

[scipilot]

I am using a MYSQL generated column to represent a UUID (see this article for details).

This means there is one binary field I populate on insert with a computed default value using a Mapper Event ( issue 109 ). This never gets written to again after insert.

Then there is a generated column which returns the text UUID on every subsequent select. It cannot be written to.

CREATE TABLE `sp_users` ( 
    ...
   `spu_uuid_bin` binary(16) DEFAULT NULL,
   `spu_uuid_text` varchar(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`spu_uuid_bin`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,

With my normal UserMapper (from the skeleton generator), I get an error from MySQL on insert as the ORM tries to populate the read-only generated text column.

PDOException: SQLSTATE[HY000]: General error: 3105 The value specified for generated column 'spu_uuid_text' in table 'sp_users' is not allowed.

I followed issue 108 which describes a similar issue. So I can remove the field from the insert in beforeInsertRow, to cure the above error:

class UserTableEvents extends TableEvents{
	// remove the generated field spu_uuid_text
	public function beforeInsertRow(Table $table, Row $row) : ?array {
		$copy = $row->getArrayCopy();
		unset($copy['spu_uuid_text']);
		return $copy;
	}

However the solution there is for PosgreSQL which has the "returning" feature. I could not use the next two event examples to restore the field to fetch the generated value after insert. The PDOStatement fetch returns a "general error", either because the text field is missing or perhaps you cannot fetch the insert in MySQL.

I think there might be a solution here - with a modified version of the modifyInsertRow and afterInsertRow example, for MySQL?

As a workaround, I turned to my Repository, and thought I could just re-fetch the row there after the insert in my add() method. However the ORM Mapper parent class caches rows with an IdentityMap for the life of the Mapper class, and I cannot see a way to clear or defeat this cache. So the cached row still doesn't have the UUID text column (removed during insert) even when re-fetched. e.g.

public function add(SubjectUser $subject) {
	...domain mapping...
	$this->atlas->insert($record);
	$record = $this->atlas->fetchRecord(UserRecord::class, $record->spu_id);
	return $record->spu_uuid_text; // field still empty from insert cache!
}

So there could be a solution here if there is a way to clear the idenity map?

Finally, I ended up duplicating my entire UserMapper folder to create a second Mapper class to represent a "UserInsertMapper" and hacked out the UUID text field. This representation is used to do the insert, and then I use the normal "UserMapper" to re-fetch, which does perform an actual Select as it's a different mapper with a different IdentityMap, not populated. While this works, there's a lot of duplication between the mappers, the refetching is less performant, and it feels like a hack.

So finally, is there a more general option/method I'm missing, for supporting readonly MySQL fields?

thanks