Source code

Download source archive ↓

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)