async def reconcile_queries(queries, config, db, table):
select_fields = get_select_fields(config)
queries_results = {}
for query_id, query in queries.items():
limit = min(
query.get("limit", config.get("max_limit", DEFAULT_LIMIT)),
config.get("max_limit", DEFAULT_LIMIT),
)
where_clauses = ["1"]
from_clause = escape_sqlite(table)
order_by = ""
params = {}
if config["fts_table"]:
# NB this will fail if the table name has non-alphanumeric
# characters in and sqlite3 version < 3.30.0
# see: https://www.sqlite.org/src/info/00e9a8f2730eb723
from_clause = """
{table}
inner join (
SELECT "rowid", "rank"
FROM {fts_table}
WHERE {fts_table} MATCH :search_query
) as "a" on {table}."rowid" = a."rowid"
""".format(
table=escape_sqlite(table),
fts_table=escape_sqlite(config["fts_table"]),
)
order_by = "order by a.rank"
params["search_query"] = escape_fts(query["query"])
else:
where_clauses.append(
"{search_col} like :search_query".format(
search_col=escape_sqlite(config["name_field"]),
)
)
params["search_query"] = f"%{query['query']}%"
query_sql = "select {select_fields} from {from_clause} where {where_clause} {order_by} limit {limit}".format(
select_fields=",".join([escape_sqlite(f) for f in select_fields]),
from_clause=from_clause,
where_clause=" and ".join(where_clauses),
order_by=order_by,
limit=limit,
)
query_results = [
get_query_result(r, config, query)
for r in await db.execute(query_sql, params)
]
query_results = sorted(query_results, key=lambda x: -x["score"])
yield query_id, query_results
def test_escape_fts(query, expected):
assert expected == utils.escape_fts(query)
