def _upsert_identities(self, identities, user_ids): flat_identities = [] # Flatten the nested lists into a single list with user ids for id_, identity_list in zip(user_ids, identities): for identity in identity_list: identity["user_id"] = id_ flat_identities.append(identity) try: # We can't tell the difference between a constraint violation # because the row we are trying to insert already exists, and # because the identity belongs to another user. # To get around this we attempt to 'upsert' after the conflict. If # the values match, nothing happens. If they are different a # cardinality violation is raised by Postgres. self._execute_statement( self._upsert_statement( UserIdentity, flat_identities, index=["provider", "provider_unique_id"], upsert=["user_id"], ).returning(UserIdentity.id)) except ProgrammingError as err: # https://www.postgresql.org/docs/9.4/errcodes-appendix.html # 21000 == cardinality violation # This indicates the identity belongs to another user if err.orig.pgcode == "21000": raise ConflictingDataError( "Attempted to assign existing identity to a different user" ) raise
def execute(self, batch, effective_user_id=None, **_): # pylint: disable=arguments-differ if effective_user_id is None: raise CommandSequenceError( "Effective user must be configured before upserting groups") # Check that we can actually process this batch self._check_upsert_queries( batch, expected_keys=["authority", "authority_provided_id"]) static_values = { # Set the group to be owned by the effective user "creator_id": effective_user_id, # Set the group to match the specified type (private in this case) "joinable_by": self.type_flags.joinable_by, "readable_by": self.type_flags.readable_by, "writeable_by": self.type_flags.writeable_by, } # Prep the query values = [command.body.attributes for command in batch] for value in values: value.update(static_values) stmt = insert(Group).values(values) stmt = stmt.on_conflict_do_update( index_elements=["authority", "authority_provided_id"], set_={ "name": stmt.excluded.name }, ).returning(Group.id, Group.authority, Group.authority_provided_id) # Upsert the data try: group_rows = self._execute_statement(stmt).fetchall() except ProgrammingError as err: # https://www.postgresql.org/docs/9.4/errcodes-appendix.html # 21000 == cardinality violation if err.orig.pgcode == "21000": raise ConflictingDataError( "Attempted to create two groups with the same authority and id" ) from err raise # Report back return [ Report( id_, public_id=Group( authority=authority, authority_provided_id=authority_provided_id).groupid, ) for id_, authority, authority_provided_id in group_rows ]
def execute( # pylint: disable=arguments-differ self, batch, on_duplicate="continue", **_): """ Execute GroupMembershipCreateAction. :param on_duplicate: Specify behavior when a record already exists. The default is "continue" """ if on_duplicate != "continue": raise UnsupportedOperationError( "Create modes other than 'continue' have not been implemented") values = [{ "user_id": command.body.member.id, "group_id": command.body.group.id } for command in batch] stmt = insert(GroupMembership).values(values) # This update doesn't change the row, but it does count as it being # 'updated' which means we can get the values in the "RETURNING" # clause and do the select in one go stmt = stmt.on_conflict_do_update( index_elements=["user_id", "group_id"], set_={"user_id": stmt.excluded.user_id}, ) stmt = stmt.returning(GroupMembership.id) try: membership_rows = self._execute_statement(stmt).fetchall() except IntegrityError as err: # https://www.postgresql.org/docs/9.1/errcodes-appendix.html # 23503 = foreign_key_violation if err.orig.pgcode == "23503": raise ConflictingDataError( "Cannot insert group membership as either the user or " f"group specified does not exist: {err.params}") from err raise return [Report(id_) for (id_, ) in membership_rows]