def insert(self, collaboratorGroup): ''' @see IBlogCollaboratorGroupService.insert ''' group = BlogCollaboratorGroupMapped() group.Blog = collaboratorGroup.Blog group.LastAccessOn = current_timestamp() self.session().add(group) self.session().flush((group,)) insert = InsertFromSelect(tableFor(BlogCollaboratorGroupMemberMapped), 'fk_group_id, fk_collaborator_id', select([group.Id, BlogCollaboratorMapped.blogCollaboratorId]).where(BlogCollaboratorMapped.Blog == group.Blog)) self.session().execute(insert) return group.Id
def unassignRole(self, roleId, toRoleId): """ @see: IRbacService.unassignRole """ sql = self.session().query(RoleNode).filter(RoleNode.role == toRoleId) try: parentNode = sql.first() except NoResultFound: return False # get child roleNode sql = self.session().query(RoleNode) sql = sql.filter( and_(RoleNode.role == roleId, RoleNode.left > parentNode.left, RoleNode.right < parentNode.right) ) try: childNode = sql.one() except NoResultFound: return False # parent count for child sql = self.session().query(RoleNode) sql = sql.filter(and_(RoleNode.left < childNode.left, RoleNode.right > childNode.right)) childCount = sql.count() # parent count for parent, including it sql = self.session().query(RoleNode) sql = sql.filter(and_(RoleNode.left <= parentNode.left, RoleNode.right >= parentNode.right)) parentCount = sql.count() treeWidth = childNode.right - childNode.left + 1 leftOffset = parentNode.right - childNode.left rightOffset = parentNode.right - childNode.right # copy the child subtree as last child of root if parentCount == childCount: # get root roleNode rootNode = self.session().query(RoleNode).get(self._rootId()) gap = rootNode.right - childNode.left rootNode.right = rootNode.right + treeWidth insert = InsertFromSelect( tableFor(RoleNode), "fk_role_id, lft, rgt", select([RoleNode.role, RoleNode.left + gap, RoleNode.right + gap]).where( and_(RoleNode.left >= childNode.left, RoleNode.right <= childNode.right) ), ) self.session().execute(insert) left, count = 0, 0 sql = self.session().query(RoleNode.right).filter(RoleNode.role == toRoleId).order_by(RoleNode.right.asc()) for (right,) in sql.all(): childLeft = right - leftOffset childRight = right - rightOffset # delete child subtree from crt parent sql = self.session().query(RoleNode).filter(and_(RoleNode.left >= childLeft, RoleNode.right <= childRight)) sql.delete() if count != 0: gap = count * treeWidth + leftOffset sql = self.session().query(RoleNode).filter(and_(RoleNode.left > left, RoleNode.left < childLeft)) sql.update({RoleNode.left: RoleNode.left - gap}, False) sql = self.session().query(RoleNode).filter(and_(RoleNode.right > left, RoleNode.right < childLeft)) sql.update({RoleNode.right: RoleNode.right - gap}, False) left = childRight count = count + 1 if count > 0: # last interval gap = treeWidth * count sql = self.session().query(RoleNode).filter(RoleNode.left >= childRight) sql.update({RoleNode.left: RoleNode.left - gap}, False) sql = self.session().query(RoleNode).filter(RoleNode.right >= childRight) sql.update({RoleNode.right: RoleNode.right - gap}, False) return True
def unassignRole(self, roleId, toRoleId): ''' @see: IRbacService.unassignRole ''' sql = self.session().query(RoleNode).filter(RoleNode.role == toRoleId) try: parentNode = sql.first() except NoResultFound: return False # get child roleNode sql = self.session().query(RoleNode) sql = sql.filter( and_(RoleNode.role == roleId, RoleNode.left > parentNode.left, RoleNode.right < parentNode.right)) try: childNode = sql.one() except NoResultFound: return False # parent count for child sql = self.session().query(RoleNode) sql = sql.filter( and_(RoleNode.left < childNode.left, RoleNode.right > childNode.right)) childCount = sql.count() # parent count for parent, including it sql = self.session().query(RoleNode) sql = sql.filter( and_(RoleNode.left <= parentNode.left, RoleNode.right >= parentNode.right)) parentCount = sql.count() treeWidth = childNode.right - childNode.left + 1 leftOffset = parentNode.right - childNode.left rightOffset = parentNode.right - childNode.right # copy the child subtree as last child of root if parentCount == childCount: # get root roleNode rootNode = self.session().query(RoleNode).get(self._rootId()) gap = rootNode.right - childNode.left rootNode.right = rootNode.right + treeWidth insert = InsertFromSelect( tableFor(RoleNode), 'fk_role_id, lft, rgt', select( [RoleNode.role, RoleNode.left + gap, RoleNode.right + gap]).where( and_(RoleNode.left >= childNode.left, RoleNode.right <= childNode.right))) self.session().execute(insert) left, count = 0, 0 sql = self.session().query(RoleNode.right).filter( RoleNode.role == toRoleId).order_by(RoleNode.right.asc()) for right, in sql.all(): childLeft = right - leftOffset childRight = right - rightOffset # delete child subtree from crt parent sql = self.session().query(RoleNode).filter( and_(RoleNode.left >= childLeft, RoleNode.right <= childRight)) sql.delete() if count != 0: gap = count * treeWidth + leftOffset sql = self.session().query(RoleNode).filter( and_(RoleNode.left > left, RoleNode.left < childLeft)) sql.update({RoleNode.left: RoleNode.left - gap}, False) sql = self.session().query(RoleNode).filter( and_(RoleNode.right > left, RoleNode.right < childLeft)) sql.update({RoleNode.right: RoleNode.right - gap}, False) left = childRight count = count + 1 if count > 0: # last interval gap = treeWidth * count sql = self.session().query(RoleNode).filter( RoleNode.left >= childRight) sql.update({RoleNode.left: RoleNode.left - gap}, False) sql = self.session().query(RoleNode).filter( RoleNode.right >= childRight) sql.update({RoleNode.right: RoleNode.right - gap}, False) return True
def assignRole(self, roleId, toRoleId): """ @see: IRbacService.assignRole """ child, parent = aliased(RoleNode), aliased(RoleNode) # check if the parent is in child subtree sql = self.session().query(child) sql = sql.join(parent, and_(child.left < parent.left, child.right > parent.right)) sql = sql.filter(and_(child.role == toRoleId, parent.role == roleId)) if sql.count() > 0: return False # check if has parent root sql = self.session().query(parent.id) sql = sql.join(child, and_(child.left > parent.left, child.right < parent.right)) sql = sql.filter(child.role == roleId) parentCnt = sql.count() # get child roleNode childNode = self.session().query(RoleNode).filter(RoleNode.role == roleId).first() treeWidth = childNode.right - childNode.left + 1 id = childNode.id # get the number of duplicates for parent sql = self.session().query(RoleNode).filter(RoleNode.role == toRoleId) treeCnt = sql.count() right, count = 0, 0 sql = self.session().query(RoleNode.right).filter(RoleNode.role == toRoleId).order_by(RoleNode.right.desc()) for (left,) in sql.all(): if count == 0: # last interval gap = treeWidth * treeCnt sql = self.session().query(RoleNode).filter(RoleNode.left >= left) sql.update({RoleNode.left: RoleNode.left + gap}, False) sql = self.session().query(RoleNode).filter(RoleNode.right >= left) sql.update({RoleNode.right: RoleNode.right + gap}, False) self.session().flush() # TODO: check the impact of removing: result: not working self.session().commit() # get child roleNode childNode = self.session().query(RoleNode).get(id) gap = left - childNode.left # insert insert = InsertFromSelect( tableFor(RoleNode), "fk_role_id, lft, rgt", select([RoleNode.role, RoleNode.left + gap, RoleNode.right + gap]).where( and_(RoleNode.left >= childNode.left, RoleNode.right <= childNode.right) ), ) self.session().execute(insert) right = left count = count + 1 continue gap = (treeCnt - count) * treeWidth sql = self.session().query(RoleNode).filter(and_(RoleNode.left >= left, RoleNode.left < right)) sql.update({RoleNode.left: RoleNode.left + gap}, False) sql = self.session().query(RoleNode).filter(and_(RoleNode.right >= left, RoleNode.right < right)) sql.update({RoleNode.right: RoleNode.right + gap}, False) self.session().flush() # TODO: check the impact of removing: result: not working self.session().commit() # get child roleNode sql = self.session().query(RoleNode).get(id) gap = gap + left - childNode.left # insert # TODO: for sqlite is nedeed: INSERT INTO t1 (columns) SELECT * FROM t1 # change how is specified the list of columns insert = InsertFromSelect( tableFor(RoleNode), "fk_role_id, lft, rgt", select([RoleNode.role, RoleNode.left + gap, RoleNode.right + gap]).where( and_(RoleNode.left >= childNode.left, RoleNode.right <= childNode.right) ), ) self.session().execute(insert) right = left count = count + 1 # if has root parent, delete from it if parentCnt == 1: # get child roleNode sql = self.session().query(RoleNode).get(id) left = childNode.left right = childNode.right # delete child subtree from root sql = self.session().query(RoleNode).filter(and_(RoleNode.left >= left, RoleNode.right <= right)) sql.delete() # update lft sql = self.session().query(RoleNode).filter(RoleNode.left > left) sql.update({RoleNode.left: RoleNode.left - treeWidth}, False) # update rgt sql = self.session().query(RoleNode).filter(RoleNode.right > right) sql.update({RoleNode.right: RoleNode.right - treeWidth}, False) return True
def assignRole(self, roleId, toRoleId): ''' @see: IRbacService.assignRole ''' child, parent = aliased(RoleNode), aliased(RoleNode) # check if the parent is in child subtree sql = self.session().query(child) sql = sql.join( parent, and_(child.left < parent.left, child.right > parent.right)) sql = sql.filter(and_(child.role == toRoleId, parent.role == roleId)) if sql.count() > 0: return False # check if has parent root sql = self.session().query(parent.id) sql = sql.join( child, and_(child.left > parent.left, child.right < parent.right)) sql = sql.filter(child.role == roleId) parentCnt = sql.count() # get child roleNode childNode = self.session().query(RoleNode).filter( RoleNode.role == roleId).first() treeWidth = childNode.right - childNode.left + 1 id = childNode.id # get the number of duplicates for parent sql = self.session().query(RoleNode).filter(RoleNode.role == toRoleId) treeCnt = sql.count() right, count = 0, 0 sql = self.session().query(RoleNode.right).filter( RoleNode.role == toRoleId).order_by(RoleNode.right.desc()) for left, in sql.all(): if count == 0: # last interval gap = treeWidth * treeCnt sql = self.session().query(RoleNode).filter( RoleNode.left >= left) sql.update({RoleNode.left: RoleNode.left + gap}, False) sql = self.session().query(RoleNode).filter( RoleNode.right >= left) sql.update({RoleNode.right: RoleNode.right + gap}, False) self.session().flush() # TODO: check the impact of removing: result: not working self.session().commit() # get child roleNode childNode = self.session().query(RoleNode).get(id) gap = left - childNode.left # insert insert = InsertFromSelect( tableFor(RoleNode), 'fk_role_id, lft, rgt', select([ RoleNode.role, RoleNode.left + gap, RoleNode.right + gap ]).where( and_(RoleNode.left >= childNode.left, RoleNode.right <= childNode.right))) self.session().execute(insert) right = left count = count + 1 continue gap = (treeCnt - count) * treeWidth sql = self.session().query(RoleNode).filter( and_(RoleNode.left >= left, RoleNode.left < right)) sql.update({RoleNode.left: RoleNode.left + gap}, False) sql = self.session().query(RoleNode).filter( and_(RoleNode.right >= left, RoleNode.right < right)) sql.update({RoleNode.right: RoleNode.right + gap}, False) self.session().flush() # TODO: check the impact of removing: result: not working self.session().commit() # get child roleNode sql = self.session().query(RoleNode).get(id) gap = gap + left - childNode.left # insert # TODO: for sqlite is nedeed: INSERT INTO t1 (columns) SELECT * FROM t1 # change how is specified the list of columns insert = InsertFromSelect( tableFor(RoleNode), 'fk_role_id, lft, rgt', select( [RoleNode.role, RoleNode.left + gap, RoleNode.right + gap]).where( and_(RoleNode.left >= childNode.left, RoleNode.right <= childNode.right))) self.session().execute(insert) right = left count = count + 1 # if has root parent, delete from it if parentCnt == 1: # get child roleNode sql = self.session().query(RoleNode).get(id) left = childNode.left right = childNode.right # delete child subtree from root sql = self.session().query(RoleNode).filter( and_(RoleNode.left >= left, RoleNode.right <= right)) sql.delete() # update lft sql = self.session().query(RoleNode).filter(RoleNode.left > left) sql.update({RoleNode.left: RoleNode.left - treeWidth}, False) # update rgt sql = self.session().query(RoleNode).filter(RoleNode.right > right) sql.update({RoleNode.right: RoleNode.right - treeWidth}, False) return True