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
