async def aggregation(self, container: IContainer, query: ParsedQueryInfo): select_fields = [ "json->'" + sqlq(field) + "' as " + sqlq(field) for field in query["metadata"] or [] ] # noqa sql, arguments = self.build_query(container, query, select_fields, True) txn = get_transaction() if txn is None: raise TransactionNotFound() conn = await txn.get_connection() results = [] logger.debug(f"Running search:\n{sql}\n{arguments}") async with txn.lock: records = await conn.fetch(sql, *arguments) for record in records: results.append([json.loads(record[field]) for field in query["metadata"] or []]) total = len(results) if total >= query["size"] or query["_from"] != 0: sql, arguments = self.build_count_query(container, query) logger.debug(f"Running search:\n{sql}\n{arguments}") async with txn.lock: records = await conn.fetch(sql, *arguments) total = records[0]["count"] return {"items": results, "items_total": total}
def get_default_where_clauses( self, context: IBaseObject, unrestricted: bool = False) -> typing.List[str]: clauses = [] sql_wheres = [] if unrestricted is False: users = [] principal = get_authenticated_user() if principal is None: # assume anonymous then principal = AnonymousUser() users.append(principal.id) users.extend(principal.groups) roles = get_roles_principal(context) clauses.extend([ "json->'access_users' ?| array['{}']".format("','".join( [sqlq(u) for u in users])), "json->'access_roles' ?| array['{}']".format("','".join( [sqlq(r) for r in roles])), ]) sql_wheres.append("({})".format(" OR ".join(clauses))) container = find_container(context) if container is None: raise ContainerNotFound() sql_wheres.append( f"""json->>'container_id' = '{sqlq(container.id)}'""") sql_wheres.append("""type != 'Container'""") sql_wheres.append(f"""parent_id != '{sqlq(TRASHED_ID)}'""") return sql_wheres
async def check_content_moved(event): request = event.request try: get_current_container() except ContainerNotFound: return storage = utils.get_storage() if storage is None: return tail, _, view = '/'.join(event.tail).partition('/@') if view: view = '@' + view path = os.path.join( get_content_path(request.resource), tail) query = Query.from_(aliases_table).select( aliases_table.zoid ).where( (aliases_table.path == sqlq(path)) | (aliases_table.path == sqlq(path) + '/' + sqlq(view)) ) async with storage.pool.acquire() as conn: results = await conn.fetch(str(query)) if len(results) > 0: ob = await get_object_by_oid(results[0]['zoid']) url = get_object_url(ob) if view: url += '/' + view raise HTTPMovedPermanently(url)
def build_count_query( self, context, query: ParsedQueryInfo, unrestricted: bool = False, ) -> typing.Tuple[str, typing.List[typing.Any]]: sql_arguments = [] sql_wheres = [] select_fields = ["count(*)"] arg_index = 1 for idx, where in enumerate(query["wheres"]): sql_wheres.append(where.format(arg=arg_index)) sql_arguments.append(query["wheres_arguments"][idx]) arg_index += 1 sql_wheres.extend( self.get_default_where_clauses(context, unrestricted=unrestricted)) txn = get_transaction() if txn is None: raise TransactionNotFound() sql = """select {} from {} where {}""".format(",".join(select_fields), sqlq(txn.storage.objects_table_name), " AND ".join(sql_wheres)) return sql, sql_arguments
def build_query( self, context: IResource, query: ParsedQueryInfo, select_fields: typing.List[str], distinct: typing.Optional[bool] = False, ) -> typing.Tuple[str, typing.List[typing.Any]]: if query["sort_on"] is None: # always need a sort otherwise paging never works order_by_index = get_pg_index("uuid") else: order_by_index = get_pg_index(query["sort_on"]) or BasicJsonIndex(query["sort_on"]) sql_arguments = [] sql_wheres = [] arg_index = 1 for idx, select in enumerate(query["selects"]): select_fields.append(select.format(arg=arg_index)) sql_arguments.append(query["selects_arguments"][idx]) arg_index += 1 where_arg_index = 0 for where in query["wheres"]: if isinstance(where, tuple): operator, sub_wheres = where sub_result = [] for sub_where in sub_wheres: sub_result.append(sub_where.format(arg=arg_index + where_arg_index)) sql_arguments.append(query["wheres_arguments"][where_arg_index]) where_arg_index += 1 sql_wheres.append("(" + operator.join(sub_result) + ")") else: sql_wheres.append(where.format(arg=arg_index + where_arg_index)) sql_arguments.append(query["wheres_arguments"][where_arg_index]) where_arg_index += 1 txn = get_transaction() if txn is None: raise TransactionNotFound() sql_wheres.extend(self.get_default_where_clauses(context)) order = ( order_by_index.order_by_score(query["sort_dir"]) if query["sort_on_fields"] else order_by_index.order_by(query["sort_dir"]) ) sql = """select {} {} from {} where {} {} limit {} offset {}""".format( "distinct" if distinct else "", ",".join(select_fields), sqlq(txn.storage.objects_table_name), " AND ".join(sql_wheres), "" if distinct else order, sqlq(query["size"]), sqlq(query["_from"]), ) return sql, sql_arguments