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]
