src/sql_views.py
1"""Read-only, fixed SQLite reports derived from Galaxy Query Tabular."""
2
3from contextlib import closing
4import math
5import sqlite3
6import time
7
8from contract import MAX_OUTPUT, lines
9from datamash import numeric
10
11
12def loaded(data, key_column, value_column, second_column, header):
13 for ordinal, row in enumerate(lines(data)[int(header):], 1):
14 fields = row.split("\t")
15 columns = max(key_column, value_column, second_column)
16 if len(fields) < columns:
17 raise ValueError("a selected input column is missing")
18 key = fields[key_column - 1]
19 if len(key) > 1024:
20 raise ValueError("table key exceeds 1024 characters")
21 value = fields[value_column - 1]
22 second = fields[second_column - 1] if second_column else ""
23 yield (ordinal, key, numeric(value) if value and value != "NA" else None,
24 numeric(second) if second and second != "NA" else None)
25
26
27def format_value(value):
28 if value is None:
29 return "NA"
30 if isinstance(value, float):
31 if not math.isfinite(value):
32 raise ValueError("numeric result is not finite")
33 return format(value or 0, ".12g")
34 return str(value)
35
36
37def execute(query, inputs, params):
38 deadline = time.monotonic() + 6
39 result = bytearray()
40 try:
41 with closing(sqlite3.connect(":memory:")) as db:
42 db.execute("PRAGMA temp_store=MEMORY")
43 db.execute("PRAGMA trusted_schema=OFF")
44 db.set_progress_handler(lambda: int(time.monotonic() >= deadline), 1000)
45 for port in ("source", "reference"):
46 if port in inputs:
47 db.execute(f"CREATE TABLE {port} (i INTEGER PRIMARY KEY, k TEXT, v REAL, w REAL)")
48 db.executemany(
49 f"INSERT INTO {port} VALUES (?, ?, ?, ?)",
50 loaded(inputs[port], params["key_column"], params["value_column"],
51 params["second_column"], params["header"]),
52 )
53 db.execute(f"CREATE INDEX {port}_key ON {port}(k)")
54 db.execute("PRAGMA query_only=ON")
55 cursor = db.execute(query, params)
56 result.extend(("\t".join(column[0] for column in cursor.description) + "\n").encode())
57 for row in cursor:
58 encoded = ("\t".join(map(format_value, row)) + "\n").encode()
59 if len(result) + len(encoded) > MAX_OUTPUT:
60 raise ValueError("SQL report exceeds 8 MiB")
61 result.extend(encoded)
62 except sqlite3.Error as error:
63 raise ValueError("SQLite rejected the report: " + str(error)) from error
64 return bytes(result)