def _get_absent_users_roles(self) -> Tuple[Alias, Alias]:
_users_group_roles = self._get_users_group_roles_sql()
remaining_users_roles_sql = db.alias(
db.select(
[_users_group_roles.c.user_id,
_users_group_roles.c.role_id]).where(
db.or_(
_users_group_roles.c.group_id != self._group.id_,
_users_group_roles.c.role_id.notin_(
[r.id_ for r in self._roles]),
)),
"reamaining_role_sql",
)
deleted_users_roles_sql = db.alias(
db.select(
[_users_group_roles.c.user_id,
_users_group_roles.c.role_id]).where(
db.and_(
_users_group_roles.c.group_id == self._group.id_,
_users_group_roles.c.role_id.in_(
[r.id_ for r in self._roles]),
)),
"deleted_users_roles_sql",
)
return (remaining_users_roles_sql, deleted_users_roles_sql)
def _build_sql(self) -> None:
delete_users_roles_sql = db.alias(self._get_delete_users_roles(),
"delete_users_roles")
target_delete_users_roles = db.alias(
db.select([delete_users_roles_sql]).where(
delete_users_roles_sql.c.user_id.in_(
[u.id_ for u in self._users])),
"target_delete_users_roles",
)
self._debug = db.select([delete_users_roles_sql])
self._sa_sql = roles_users.delete().where(
db.and_(
roles_users.c.role_id == target_delete_users_roles.c.role_id,
roles_users.c.user_id == target_delete_users_roles.c.user_id,
))
def _build_sql(self) -> None:
user_role = db.alias(self._get_user_role_sql(), "user_role")
self._sa_sql = roles_users.delete().where(
db.and_(
roles_users.c.role_id == user_role.c.role_id,
roles_users.c.user_id == user_role.c.user_id,
))
def __get_role_sql_tuple(self) -> Tuple[Alias, Alias, Join]:
group_user_role = self.__get_group_user_role_sql()
remaining_role_sql = db.alias(
db.select([group_user_role.c.role_id]).where(
group_user_role.c.group_id.notin_(self._group_ids)),
"remaining_role_sql",
)
deleted_role_sql = db.alias(
db.select([group_user_role.c.role_id]).where(
group_user_role.c.group_id.in_(self._group_ids)),
"deleted_role_sql",
)
joined_role_sql = deleted_role_sql.outerjoin(
remaining_role_sql,
remaining_role_sql.c.role_id == deleted_role_sql.c.role_id,
)
return (remaining_role_sql, deleted_role_sql, joined_role_sql)
def _build_sql(self) -> None:
delete_users_roles_sql = db.alias(self._get_delete_users_roles(),
"delete_users_roles")
self._sa_sql = roles_users.delete().where(
db.and_(
roles_users.c.role_id == delete_users_roles_sql.c.role_id,
roles_users.c.user_id == delete_users_roles_sql.c.user_id,
))
def _get_users_roles_sql(self) -> Alias:
group_users = db.alias(
db.select([groups_users.c.user_id
]).where(groups_users.c.group_id == self._group.id_),
"group_users",
)
group_users_roles = db.join(
group_users,
groups_users,
group_users.c.user_id == groups_users.c.user_id,
).join(groups_roles,
groups_roles.c.group_id == groups_users.c.group_id)
return db.alias(
db.select([group_users.c.user_id, groups_roles.c.role_id
]).select_from(group_users_roles).distinct(),
"_users_roles",
)
def __build_sql(self) -> None:
absent_users_roles = db.alias(self._get_absent_users_roles(),
"absent_users_roles")
self._sa_sql = roles_users.insert().from_select(
["user_id", "role_id"],
db.select([absent_users_roles]).where(
absent_users_roles.c.user_id.in_([u.id_
for u in self._users])),
)
def __get_group_user_role_sql(self) -> Alias:
return db.alias(
db.select([
groups_roles.c.group_id,
groups_users.c.user_id,
groups_roles.c.role_id,
]).select_from(
db.join(
groups_roles,
groups_users,
groups_users.c.group_id == groups_roles.c.group_id,
)).where(groups_users.c.user_id == self._user.id_),
"group_user_role",
)
def _get_absent_user_roles(self) -> select:
roles_user = db.alias(
db.select([roles_users
]).where(roles_users.c.user_id == self._user.id_),
"roles_user",
)
joined_groups_user = db.outerjoin(
groups_roles, roles_user,
groups_roles.c.role_id == roles_user.c.role_id)
return (db.select([
db.func.coalesce(roles_user.c.user_id,
self._user.id_).label("user_id"),
groups_roles.c.role_id,
]).select_from(joined_groups_user).where(
db.and_(
groups_roles.c.group_id.in_([g.id_ for g in self._groups]),
roles_user.c.role_id.is_(None), # type: ignore
)).distinct())