If an alias is added to a user and that alias happens to also be the name of an existing non-user participant (case-sensitive as appropriate), said participant should be "merged" into the participant linked to the user that the alias is for.
This situation could manifest, for example, when a user is quoted under an alias that hasn't yet been associated with their canonical name or a "duplicate" IRC nickname. Later, when the alias is added to the appropriate user, the database is inconsistent as there are now two participants that ultimately refer to the same unique user.
At a high level, the following must be done:
Replace all references to the overtaken participant with the aliased user's participant_id
Delete the original overtaken participant
However, there's a small challenge involved here. To accomplish step 1, any and all feature module tables that reference a participant_id will need to be accounted for, as any record referencing the overtaken participant in these tables will need to be updated to the aliased user's participant_id. There could be any number of tables among any number of individual features that would need to be updating, possibly even none at all. Because of the variable number of tables, I don't think this can be enforced entirely by the database, so the Core will likely have to participate to some degree.
Plan
When an alias is added, check for any matching participant names (taking case sensitivity into account). Make note of the participant_id.
If there was no match, then stop; there's nothing to do.
Otherwise, get a list of tables that reference participants:
SELECT name, sql FROM sqlite_master
WHERE type = 'table' AND sql LIKE '%REFERENCES%participants%'
Iterate over each returned table+schema
Use a regexp to extract the referencing column(s) in each table from the schema
Update said column in each row where the participant ID matches that of the overtaken participant to that of the user the alias belongs to
Delete the overtaken participant
Deferred foreign keys and the associated defer_foreign_keys pragma might come in handy here.
If an alias is added to a user and that alias happens to also be the name of an existing non-user participant (case-sensitive as appropriate), said participant should be "merged" into the participant linked to the user that the alias is for.
This situation could manifest, for example, when a user is quoted under an alias that hasn't yet been associated with their canonical name or a "duplicate" IRC nickname. Later, when the alias is added to the appropriate user, the database is inconsistent as there are now two participants that ultimately refer to the same unique user.
At a high level, the following must be done:
participant_id
However, there's a small challenge involved here. To accomplish step 1, any and all feature module tables that reference a
participant_id
will need to be accounted for, as any record referencing the overtaken participant in these tables will need to be updated to the aliased user'sparticipant_id
. There could be any number of tables among any number of individual features that would need to be updating, possibly even none at all. Because of the variable number of tables, I don't think this can be enforced entirely by the database, so the Core will likely have to participate to some degree.Plan
participant_id
.participants
:Deferred foreign keys and the associated
defer_foreign_keys
pragma might come in handy here.