Identifier Assignment Case Insensitivity

Previously in v4, Identifier Assignment was case insensitive. For example, if the Identifier Assigner generates the new Identifier foo2, and the Identifier (of the same type) Foo2 already exists. This is not conformant to AR-IdentifierAssignment-2. PE now implements a case insensitive check in IdentifierAssignmentsTable::checkAvailability(), however the corresponding indexes (for both identifiers and email_addresses) is not case insensitive, leading to worse database performance.

Creating a lowercase index in Postgres is pretty easy:

CREATE INDEX identifiers_i5 ON identifiers ((lower(identifier)), type_id);

However this syntax is not supported by DBAL since it is not portable. After manually applying this index, DBAL tries to drop it:

$ ./bin/cake database -n

DROP INDEX identifiers_i5

There's a hint in the linked Github ticket about using a "DB migration" as one possible approach, but it's not clear that we want to version our database schema just to add this capability. A better approach might be to override the schema manager.

Once DBAL is addressed, an annotation in schema.json will be needed to flag indexes as case insensitive, eg:

"identifiers": {

  "indexes": {

    "identifiers_i5": { "columns": [

      "identifiers" => [ "caseinsensitive" => true ],

      "type_id"

    ]

  }

}