class GroupEntry(DBObjectBase): __tablename__ = "replay_groups" id = Column(Integer, id_seq, primary_key=True) uuid = Column(String(36)) owner = Column(String(40), ForeignKey('players.platformid')) name = Column(String(250)) game = Column(String(40)) path = Column(LtreeType, nullable=False) type = Column(Enum(GroupEntryType)) parent = relationship( 'GroupEntry', primaryjoin=remote(path) == foreign(func.subpath(path, 0, -1)), backref='children', viewonly=True, ) date = Column(DateTime, default=datetime.datetime.utcnow) __table_args__ = (Index('ix_nodes_path', path, postgresql_using="gist"), ) def __init__(self, *args, **kwargs): engine = kwargs['engine'] del kwargs['engine'] super().__init__(*args, **kwargs) _id = engine.execute(id_seq) self.id = _id self.uuid = uuid.uuid4() parent = kwargs['parent'] if 'parent' in kwargs else None ltree_id = Ltree(str(self.id)) self.path = ltree_id if parent is None else parent.path + ltree_id def __str__(self): return self.uuid def __repr__(self): return 'Node({})'.format(self.uuid)
class Node(Base): __tablename__ = "nodes" id = Column(Integer, id_seq, primary_key=True) name = Column(String, nullable=False) path = Column(LtreeType, nullable=False) parent = relationship( "Node", primaryjoin=(remote(path) == foreign(func.subpath(path, 0, -1))), backref="children", viewonly=True, ) def __init__(self, name, parent=None): _id = engine.execute(id_seq) self.id = _id self.name = name ltree_id = Ltree(str(_id)) self.path = ltree_id if parent is None else parent.path + ltree_id __table_args__ = (Index("ix_nodes_path", path, postgresql_using="gist"), ) def __str__(self): return self.name def __repr__(self): return 'Node({})'.format(self.name)
def all(sess, nodes, flatten=False): parent_uri = '/'.join(nodes) parent_path = uri_to_ltree(parent_uri) depth = uri_depth(parent_uri) stmt = sess.query( distinct(func.subpath(Node.path, 0, depth + 1).label("subpath"))) stmt = stmt.filter(Node.path.op("<@")(parent_path)) stmt = stmt.subquery() qry = sess.query(Node) qry = qry.filter(Node.path.in_(stmt)) return qry
def all(sess, nodes, flatten=False): parent_uri = '/'.join(nodes) parent_path = uri_to_ltree(parent_uri) depth = uri_depth(parent_uri) stmt = sess.query( distinct(func.subpath(Node.path, 0, depth+1).label("subpath")) ) stmt = stmt.filter( Node.path.op("<@")(parent_path) ) stmt = stmt.subquery() qry = sess.query( Node ) qry = qry.filter( Node.path.in_(stmt) ) return qry
class Organization(Base): id = Column(Integer, primary_key=True, index=True) name = Column(String, nullable=False, index=True) slug = Column(String, nullable=False, index=True) mode = Column( Enum(schemas.OrganizationMode, values_callable=lambda obj: [e.value for e in obj]), nullable=False, server_default=schemas.OrganizationMode.OPEN.value ) working_area = Column(Geometry("MULTIPOLYGON"), nullable=True) boundary = Column(Geometry(geometry_type='MULTIPOLYGON', srid=4326), nullable=True) coords = Column(Geometry(geometry_type='POINT', srid=4326), nullable=True) osm_id = Column(Integer, nullable=True) osm_place_id = Column(Integer, nullable=True) osm_type = Column(String, nullable=True) population_size = Column(Integer, nullable=True) area_sq_km = Column(Float, nullable=True) featured = Column(Boolean, nullable=False, default=False, server_default="false") boundary_bbox_coords = Column(ARRAY(Float), nullable=True) path = Column(LtreeType, nullable=False) config = Column(JSONB, nullable=True) archived = Column(Boolean, nullable=False, server_default=expression.false()) archived_at = Column(DateTime, nullable=True) created_at = Column(DateTime, nullable=True) updated_at = Column(DateTime, nullable=True) total_trees = column_property( select([func.count(Tree.id)]) .where(Tree.organization_id == id) .correlate_except(Tree) ) parent = relationship( "Organization", primaryjoin=remote(path) == foreign(func.subpath(path, 0, -1)), backref="teams", sync_backref=False, viewonly=True, ) health_assessments = relationship("HealthAssessment", back_populates="organization") __table_args__ = (Index("ix_organization_path", path, postgresql_using="gist"),) def __init__( self, name: str, mode: str, archived: bool, featured: bool, population_size=None, area_sq_km=None, created_at=None, updated_at=None, config=None, working_area=None, boundary=None, coords=None, osm_id=None, osm_place_id=None, osm_type=None, boundary_bbox_coords=None, parent=None, archived_at=None, current_user_role=None ): # _id = engine.execute(id_seq) # self.id = _id self.name = name self.mode = mode self.config = config self.working_area = working_area self.boundary = boundary self.coords = coords self.osm_id = osm_id self.osm_place_id = osm_place_id self.osm_type = osm_type self.boundary_bbox_coords = boundary_bbox_coords self.archived = archived self.archived_at = archived_at self.featured = featured self.population_size = population_size self.area_sq_km = area_sq_km self.created_at = datetime.now() self.updated_at = datetime.now() # self.path = ( # Ltree(str(_id)) # if parent is None # else (parent.path or Ltree("_")) + Ltree(str(_id)) # ) # self.slug = Organization.initiate_unique_slug(name, _id, self.path) @property def total_members(self): get_users = enforcer.model.model["g"]["g"].rm.get_users all_users = [get_users(role, str(self.id)) for role in enforcer.get_all_roles()] return reduce(lambda total, users: total + len(users), all_users, 0) def to_current_user_schema(self): return self.to_schema() def to_schema(self): return schemas.Organization( **{ c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs if not c.key in ["path", "working_area", "config"] }, total_members=self.total_members, has_working_area=bool(self.working_area), path=str(self.path) ) @staticmethod def initiate_unique_slug(name, id, path): root_slug = Organization.get_root_slug(path) if root_slug: slug = slugify(f"{root_slug} {name}") return slug if Organization.is_slug_available(slug) else slugify(f"{slug} {id}") else: slug = slugify(f"{name}") return slug if Organization.is_slug_available(slug) else slugify(f"{slug} {id}") def is_slug_available(value): # Avoid collusions with frontend routes restricted_domains = ['home' , 'about', 'admin'] #should be set dynamically, not harcoded result = engine.execute(f"select * from public.organization where slug = '{value}';") return True if result.rowcount == 0 and value not in restricted_domains else False def get_root_slug(path): if path: if len(path) > 1: path_ids = path.path.split('.') root_id = path_ids[0] result = engine.execute(f"select * from public.organization where id = '{root_id}';") root_organization = result.first() return root_organization.slug if root_organization else None else: return None else: return None def on_name_change_rehydrate_slug(target, value, oldvalue, initiator): print("generate_unique_slug") if value and (not target.slug or value != oldvalue): root_slug = Organization.get_root_slug(target.path) if root_slug: slug = slugify(f"{root_slug} {value}") target.slug = slug if Organization.is_slug_available(slug) else slugify(f"{slug} {target.id}") else: slug = slugify(f"{value}") target.slug = slug if Organization.is_slug_available(slug) else slugify(f"{slug} {target.id}") def on_path_change_rehydrate_slug(target, value, oldvalue, initiator): print("rehydrate_slug") if value and (not target.slug or value != oldvalue): root_slug = Organization.get_root_slug(target.path) if root_slug: slug = slugify(f"{root_slug} {target.name}") target.slug = slug if Organization.is_slug_available(slug) else slugify(f"{slug} {target.id}") else: slug = slugify(f"{target.name}") target.slug = slug if Organization.is_slug_available(slug) else slugify(f"{slug} {target.id}") def randomize_slug(target, value, oldvalue, initiator): print("randomize_slug") if value and (not target.slug or value != oldvalue): target.slug = generate('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-', 21) # letters = string.ascii_letters # target.slug = ''.join(random.choice(letters) for i in range(21)) target.archived_at = datetime.now() else: target.archived_at = None Organization.on_name_change_rehydrate_slug(target, target.name, oldvalue, initiator)
def from_incremental_query(sess, query): LOG.debug('parsing incremental query %r' % query) if not query or query == 'root' or query == '/': # list the available query schemes return [ DummyNode('all'), DummyNode('date'), DummyNode('in_path'), DummyNode('md5'), DummyNode('named_queries'), DummyNode('rating'), DummyNode('tag'), DummyNode('tag_group'), ] else: if query.startswith('root'): query = query[5:] query_nodes = query.split('/') LOG.debug('Query nodes: %r' % query_nodes) # pop the query type off the beginning query_types = query_nodes.pop(0).lower() query_types = [x.strip() for x in query_types.split(',')] # handle flattened queries if query_nodes and query_nodes[-1] == "__flat__": query_nodes.pop() flatten = True else: flatten = False # Construct the different queries if len(query_types) == 1 and query_types[0] == 'all': return all(sess, query_nodes, flatten).order_by(Node.uri) if 'named_queries' in query_types and not query_nodes: nq_qry = sess.query(Query) nq_qry = nq_qry.filter(Query.label != None) nq_qry = nq_qry.order_by(Query.label) return [DummyNode(x.label) for x in nq_qry.all()] elif query_types[0] == 'named_queries': # fetch the saved query and replace the named query by that string query_name = query_nodes.pop(0) nq_qry = sess.query(Query) nq_qry = nq_qry.filter(Query.label == query_name).first() if not nq_qry: return [] prepend_nodes = nq_qry.query.split('/') query_nodes = prepend_nodes + query_nodes num_params = expected_params(query_types) if not query_nodes or len(query_nodes) < num_params: # no all query parmeters known yet. Find appropriate queries output = [] stmt = sess.query(Query.query) LOG.debug('Listing nodes starting with %r' % query) stmt = stmt.filter(query_table.c.query.startswith(query)) stmt = stmt.order_by(query_table.c.query) for row in stmt: sub_nodes = row.query.split('/') # we're in the case where the initial nodes were empty. We only return # the next element output.append(DummyNode(sub_nodes[len(query_nodes) + 1])) return output parent_uri = '/'.join(query_nodes[num_params:]) parent_path = uri_to_ltree(parent_uri) depth = uri_depth(parent_uri) if flatten: stmt = sess.query(Node) else: stmt = sess.query( distinct(func.subpath(Node.path, 0, depth + 1).label("subpath"))) stmt = stmt.filter(Node.path.op("<@")(parent_path)) # apply all filters in sequence for query_type in query_types: if query_type == 'date': stmt = dated(sess, stmt, parent_uri, query_nodes) if query_type == 'major_mimetype': stmt = major_mimetype(stmt, parent_uri, query_nodes) if query_type == 'mimetype': stmt = mimetype(stmt, parent_uri, query_nodes) if query_type == 'rating': stmt = rated(stmt, parent_uri, query_nodes) if query_type == 'aspect': stmt = aspect(stmt, parent_uri, query_nodes) if query_type == 'aspect_range': stmt = aspect_range(stmt, parent_uri, query_nodes) if query_type == 'md5': stmt = has_md5(stmt, parent_uri, query_nodes) if query_type == 'in_path': stmt = in_path(stmt, query_nodes) if query_type == 'tag': stmt = tagged(sess, stmt, parent_uri, query_nodes) if query_type == 'tag_group': stmt = in_tag_group(sess, stmt, parent_uri, query_nodes) print stmt if not flatten: stmt = stmt.subquery() qry = sess.query(Node) qry = qry.filter(Node.path.in_(stmt)) qry = qry.order_by(Node.uri) return qry return stmt.order_by(Node.uri)
def subdirs(sess, query): LOG.debug('subfolders in %s' % query) if not query or query == 'root' or query == '/': return [] # handled by from incremental_query else: if query.startswith('root'): query = query[5:] query_nodes = query.split('/') LOG.debug('Query nodes: %r' % query_nodes) # pop the query type off the beginning query_types = query_nodes.pop(0).lower() query_types = [x.strip() for x in query_types.split(',')] # handle flattened queries if query_nodes and query_nodes[-1] == "__flat__": return [] stmt = sess.query(Node) if 'named_queries' in query_types and not query_nodes: # handled by incremental_query return [] elif query_types[0] == 'named_queries': # handled by incremental_query return [] num_params = expected_params(query_types) if not query_nodes or len(query_nodes) < num_params: # todo: query not complete: offer some virtual folders output = [] return output parent_uri = '/'.join(query_nodes[num_params:]) parent_path = uri_to_ltree(parent_uri) depth = uri_depth(parent_uri) stmt = sess.query( distinct(func.subpath(Node.path, 0, depth + 1).label("subpath"))) stmt = stmt.filter(Node.path.op("<@")(parent_path)) stmt = stmt.filter(func.nlevel(Node.path) > uri_depth(parent_uri) + 1) if len(query_types) == 1 and query_types[0] == 'all': return [DummyNode(x[0].rsplit('.')[-1]) for x in stmt] # apply all filters in sequence for query_type in query_types: if query_type == 'date': stmt = dated(sess, stmt, parent_uri, query_nodes) if query_type == 'rating': stmt = rated(stmt, parent_uri, query_nodes) if query_type == 'major_mimetype': stmt = major_mimetype(stmt, parent_uri, query_nodes) if query_type == 'mimetype': stmt = mimetype(stmt, parent_uri, query_nodes) if query_type == 'aspect_range': stmt = aspect_range(stmt, parent_uri, query_nodes) if query_type == 'aspect': stmt = aspect(stmt, parent_uri, query_nodes) if query_type == 'md5': stmt = has_md5(stmt, parent_uri, query_nodes) if query_type == 'in_path': stmt = in_path(stmt, query_nodes) if query_type == 'tag': stmt = tagged(sess, stmt, parent_uri, query_nodes) if query_type == 'tag_group': stmt = in_tag_group(sess, stmt, parent_uri, query_nodes) return [DummyNode(x[0].rsplit('.', 1)[-1]) for x in stmt]
def from_incremental_query(sess, query): LOG.debug('parsing incremental query %r' % query) if not query or query == 'root' or query == '/': # list the available query schemes return [ DummyNode('all'), DummyNode('date'), DummyNode('in_path'), DummyNode('md5'), DummyNode('named_queries'), DummyNode('rating'), DummyNode('tag'), DummyNode('tag_group'), ] else: if query.startswith('root'): query = query[5:] query_nodes = query.split('/') LOG.debug('Query nodes: %r' % query_nodes) # pop the query type off the beginning query_types = query_nodes.pop(0).lower() query_types = [x.strip() for x in query_types.split(',')] # handle flattened queries if query_nodes and query_nodes[-1] == "__flat__": query_nodes.pop() flatten = True else: flatten = False # Construct the different queries if len(query_types) == 1 and query_types[0] == 'all': return all(sess, query_nodes, flatten).order_by(Node.uri) if 'named_queries' in query_types and not query_nodes: nq_qry = sess.query(Query) nq_qry = nq_qry.filter( Query.label != None ) nq_qry = nq_qry.order_by(Query.label) return [ DummyNode(x.label) for x in nq_qry.all() ] elif query_types[0] == 'named_queries': # fetch the saved query and replace the named query by that string query_name = query_nodes.pop(0) nq_qry = sess.query(Query) nq_qry = nq_qry.filter( Query.label == query_name ).first() if not nq_qry: return [] prepend_nodes = nq_qry.query.split('/') query_nodes = prepend_nodes + query_nodes num_params = expected_params(query_types) if not query_nodes or len(query_nodes) < num_params: # no all query parmeters known yet. Find appropriate queries output = [] stmt = sess.query(Query.query) LOG.debug('Listing nodes starting with %r' % query) stmt = stmt.filter(query_table.c.query.startswith(query)) stmt = stmt.order_by(query_table.c.query) for row in stmt: sub_nodes = row.query.split('/') # we're in the case where the initial nodes were empty. We only return # the next element output.append(DummyNode(sub_nodes[len(query_nodes)+1])) return output parent_uri = '/'.join(query_nodes[num_params:]) parent_path = uri_to_ltree(parent_uri) depth = uri_depth(parent_uri) if flatten: stmt = sess.query(Node) else: stmt = sess.query( distinct(func.subpath(Node.path, 0, depth+1).label("subpath")) ) stmt = stmt.filter( Node.path.op("<@")(parent_path) ) # apply all filters in sequence for query_type in query_types: if query_type == 'date': stmt = dated(sess, stmt, parent_uri, query_nodes) if query_type == 'major_mimetype': stmt = major_mimetype(stmt, parent_uri, query_nodes) if query_type == 'mimetype': stmt = mimetype(stmt, parent_uri, query_nodes) if query_type == 'rating': stmt = rated(stmt, parent_uri, query_nodes) if query_type == 'aspect': stmt = aspect(stmt, parent_uri, query_nodes) if query_type == 'aspect_range': stmt = aspect_range(stmt, parent_uri, query_nodes) if query_type == 'md5': stmt = has_md5(stmt, parent_uri, query_nodes) if query_type == 'in_path': stmt = in_path(stmt, query_nodes) if query_type == 'tag': stmt = tagged(sess, stmt, parent_uri, query_nodes) if query_type == 'tag_group': stmt = in_tag_group(sess, stmt, parent_uri, query_nodes) print stmt if not flatten: stmt = stmt.subquery() qry = sess.query( Node ) qry = qry.filter( Node.path.in_(stmt) ) qry = qry.order_by(Node.uri) return qry return stmt.order_by(Node.uri)
def subdirs(sess, query): LOG.debug('subfolders in %s' % query) if not query or query == 'root' or query == '/': return [] # handled by from incremental_query else: if query.startswith('root'): query = query[5:] query_nodes = query.split('/') LOG.debug('Query nodes: %r' % query_nodes) # pop the query type off the beginning query_types = query_nodes.pop(0).lower() query_types = [x.strip() for x in query_types.split(',')] # handle flattened queries if query_nodes and query_nodes[-1] == "__flat__": return [] stmt = sess.query(Node) if 'named_queries' in query_types and not query_nodes: # handled by incremental_query return [] elif query_types[0] == 'named_queries': # handled by incremental_query return [] num_params = expected_params(query_types) if not query_nodes or len(query_nodes) < num_params: # todo: query not complete: offer some virtual folders output = [] return output parent_uri = '/'.join(query_nodes[num_params:]) parent_path = uri_to_ltree(parent_uri) depth = uri_depth(parent_uri) stmt = sess.query( distinct(func.subpath(Node.path, 0, depth+1).label("subpath")) ) stmt = stmt.filter( Node.path.op("<@")(parent_path) ) stmt = stmt.filter( func.nlevel(Node.path) > uri_depth(parent_uri)+1) if len(query_types) == 1 and query_types[0] == 'all': return [DummyNode(x[0].rsplit('.')[-1]) for x in stmt] # apply all filters in sequence for query_type in query_types: if query_type == 'date': stmt = dated(sess, stmt, parent_uri, query_nodes) if query_type == 'rating': stmt = rated(stmt, parent_uri, query_nodes) if query_type == 'major_mimetype': stmt = major_mimetype(stmt, parent_uri, query_nodes) if query_type == 'mimetype': stmt = mimetype(stmt, parent_uri, query_nodes) if query_type == 'aspect_range': stmt = aspect_range(stmt, parent_uri, query_nodes) if query_type == 'aspect': stmt = aspect(stmt, parent_uri, query_nodes) if query_type == 'md5': stmt = has_md5(stmt, parent_uri, query_nodes) if query_type == 'in_path': stmt = in_path(stmt, query_nodes) if query_type == 'tag': stmt = tagged(sess, stmt, parent_uri, query_nodes) if query_type == 'tag_group': stmt = in_tag_group(sess, stmt, parent_uri, query_nodes) return [DummyNode(x[0].rsplit('.', 1)[-1]) for x in stmt]