def _children_counts(children, session):
return [(session.query(func.count(GroupEntry.id)).filter(
GroupEntry.path.descendant_of(child.path)).filter(
func.nlevel(GroupEntry.path) > len(child.path)).filter(
GroupEntry.type == GroupEntryType.game).first()
if child.type == GroupEntryType.group else (0, ))
for child in children]
def get_teams(self, db: Session, *, parent_id: int) -> List[Organization]:
"""returns active sub-organizations (teams) given the parent"""
# positble alternative: select * from organization o where o.path ~ 'planet.*{1}';
parent = self.get(db, id=parent_id)
if not parent:
return []
return (db.query(Organization).filter(
and_(
func.nlevel(
Organization.path) == (func.nlevel(str(parent.path)) + 1),
Organization.path.descendant_of(parent.path),
Organization.archived == False,
)).all())
def get_info(uuid, session=None) -> Group:
if uuid is None:
current_user = UserManager.get_current_user()
if current_user is not None:
peak_nodes = session.query(GroupEntry).filter(
GroupEntry.owner == current_user.platformid).filter(
func.nlevel(GroupEntry.path) == 1).order_by(
GroupEntry.id).all()
children_counts = SavedGroup._children_counts(
peak_nodes, session)
return Group(None, [], [
GroupEntryJSON.create(child, descendants).__dict__
for child, (
descendants, ) in zip(peak_nodes, children_counts)
])
else:
raise NotLoggedIn()
entry = session.query(GroupEntry).filter(
GroupEntry.uuid == uuid).first()
children = session.query(GroupEntry).filter(
GroupEntry.path.descendant_of(entry.path)).order_by(
GroupEntry.id).filter(
func.nlevel(GroupEntry.path) == len(entry.path) + 1).all()
children_counts = SavedGroup._children_counts(children, session)
ancestors = session.query(GroupEntry).filter(
GroupEntry.path.ancestor_of(entry.path)).filter(
func.nlevel(GroupEntry.path) < len(entry.path)).order_by(
func.nlevel(GroupEntry.path)).all()
return Group(
GroupEntryJSON.create(entry).__dict__, [
GroupEntryJSON.create(ancestor).__dict__
for ancestor in ancestors
], [
GroupEntryJSON.create(child, descendants).__dict__
for child, (descendants, ) in zip(children, children_counts)
])
def get_root_nodes(self, db: Session) -> Optional[Organization]:
return db.query(Organization).filter(
func.nlevel(Organization.path) == 1).all()
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 is_user(cls):
return func.mod(func.nlevel(cls._path), 2) == 1
class Node(Base, BasicMixin):
__tablename__ = "node"
parent_id = Column(Integer, ForeignKey("node.id"), index=True)
parent = relationship("Node",
backref="children",
remote_side="Node.id",
lazy="joined")
_path = Column(LtreeType, server_default="")
visited_count = Column(Integer, default=1, index=True)
utterances = relationship("Utterance",
secondary="node_utterance",
lazy="joined")
node_utterances = relationship("NodeUtterance", lazy="joined")
species = Column(String)
active = Column(Boolean, default=True, index=True)
path_length = column_property(func.nlevel(_path), deferred=False)
@hybrid_property
def is_anonymous(self):
return any([x.amazon_anonymous for x in self.utterances])
@hybrid_method
def recalculate_path(self):
def recur_parent(node):
path = [node.id]
if node.parent:
return recur_parent(node.parent) + path
return path
return recur_parent(self)
@hybrid_property
def active_child_count(self):
return len([x for x in self.children if x.active])
@active_child_count.expression
def active_child_count(cls):
q = aliased(cls)
return (select([cast(func.count("id"), Float)
]).select_from(q).where(q.parent_id == cls.id).where(
q.active.is_(True)).label("child_count"))
@hybrid_property
def child_count(self):
return len(self.children)
@child_count.expression
def child_count(cls):
q = aliased(cls)
return (select([
cast(func.count("id"), Float)
]).select_from(q).where(q.parent_id == cls.id).label("child_count"))
@staticmethod
def calculate_score(rand, visits):
return (rand * 0.2 * visits) + visits
@property
def path(self):
if self._path:
return [int(x) for x in str(self._path).split(".")]
return []
@path.setter
def path(self, value):
if value:
if type(value) == list:
self._path = Ltree(".".join(map(str, value)))
else:
self._path = Ltree(str(value))
@hybrid_property
def is_user(self):
return len(self._path) % 2 == 1
@is_user.expression
def is_user(cls):
return func.mod(func.nlevel(cls._path), 2) == 1
@hybrid_property
def score(self):
visits = self.visited_count
return Node.calculate_score(random.random(), visits)
@score.expression
def score(cls):
number_of_visits = cast(cls.visited_count.label("number_of_visits"),
Float)
return Node.calculate_score(func.random(), number_of_visits)
@hybrid_property
def is_incoherent(self):
return any([
x for x in self.node_utterances
if x.incoherent_node_utterance_statuses
])
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]
session.add(Node(big_cat, parent=big))
for small_wildcat in ("ocelot", "bobcat"):
session.add(Node(small_wildcat, parent=wild))
for domestic_cat in ("persian", "bengal", "shorthair"):
session.add(Node(domestic_cat, parent=domestic))
session.flush()
# To retrieve a whole subtree:
whole_subtree = session.query(Node).filter(
Node.path.descendant_of(domestic.path)).all()
print('Whole subtree:', whole_subtree)
# [domestic, persian, bengal, shorthair]
# Get only the third layer of nodes:
third_layer = session.query(Node).filter(func.nlevel(Node.path) == 3).all()
print('Third layer:', third_layer)
# [wild, domestic, lion, tiger, jaguar]
# Get all the siblings of a node:
shorthair = session.query(Node).filter_by(name="shorthair").one()
siblings = session.query(Node).filter(
# We can use Python's slice notation on ltree paths:
Node.path.descendant_of(shorthair.path[:-1]),
func.nlevel(Node.path) == len(shorthair.path),
Node.id != shorthair.id,
).all()
print('Siblings of shorthair:', siblings)
# [persian, bengal]
# Using an LQuery to get immediate children of two parent nodes at different depths: