src/dacp_piflow_support.py
1"""Shared validation and bounded materialization for PiFlow record adaptations."""
2import pyarrow as pa
3import sqlite3
4
5MAX_BYTES = 128 * 1024 * 1024
6
7
8def params(ctx, required=(), optional=()):
9 if set(ctx.params) - set(required) - set(optional) or set(required) - set(ctx.params):
10 raise ValueError('missing or unexpected parameters')
11 return ctx.params
12
13
14def columns(text, allow_empty=False):
15 if not isinstance(text, str):
16 raise ValueError('columns must be a comma-separated string')
17 names = [s.strip() for s in text.split(',')]
18 if allow_empty and text == '':
19 return []
20 if not all(names) or len(names) != len(set(names)):
21 raise ValueError('column names must be unique and nonempty')
22 return names
23
24
25def indexes(schema, names):
26 result = [schema.get_field_index(n) for n in names]
27 if any(i < 0 for i in result):
28 raise ValueError('unknown or ambiguous column')
29 return result
30
31
32def materialize(source):
33 schema = source.schema
34 size, batches = 0, []
35 for batch in source.records():
36 size += batch.nbytes
37 if size > MAX_BYTES:
38 raise ValueError('materialized input exceeds 128 MiB')
39 batches.append(batch)
40 return pa.Table.from_batches(batches, schema=schema)
41
42
43def write_table(ctx, table, port='result'):
44 out = ctx.records_output(port, table.schema)
45 for batch in table.to_batches(max_chunksize=1024):
46 out.write(batch)
47
48
49def identifier(name):
50 return '"' + name.replace('"', '""') + '"'
51
52
53def sql_value(value):
54 import datetime
55 import decimal
56 if isinstance(value, (datetime.date, datetime.time, datetime.datetime, decimal.Decimal)):
57 return str(value)
58 if value is not None and not isinstance(value, (str, int, float, bytes)):
59 raise ValueError('SQL operators accept scalar columns only')
60 return value
61
62
63def database(table, view):
64 connection = sqlite3.connect(':memory:')
65 try:
66 if len(table.column_names) != len(set(table.column_names)) or not table.column_names:
67 raise ValueError('unique, nonempty columns required')
68 names = ','.join(identifier(n) for n in table.column_names)
69 connection.execute('CREATE TABLE ' + identifier(view) + '(' + names + ')')
70 connection.executemany('INSERT INTO ' + identifier(view) + ' VALUES (' + ','.join('?' for _ in table.column_names) + ')',
71 (tuple(sql_value(row[n]) for n in table.column_names) for row in table.to_pylist()))
72 # User SQL may only read the supplied relation and invoke scalar/aggregate functions.
73 allowed = {sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION, sqlite3.SQLITE_RECURSIVE}
74 connection.set_authorizer(lambda action, *_: sqlite3.SQLITE_OK if action in allowed else sqlite3.SQLITE_DENY)
75 ticks = [0]
76 def progress():
77 ticks[0] += 1
78 return int(ticks[0] > 10000)
79 connection.set_progress_handler(progress, 1000)
80 return connection
81 except BaseException:
82 connection.close()
83 raise
84
85
86def predicate_indices(batch, condition):
87 if not isinstance(condition, str) or not condition.strip():
88 raise ValueError('condition must be a nonempty SQLite SQL expression')
89 reserved = '__dacp_row_index'
90 if reserved in batch.schema.names:
91 raise ValueError('reserved SQL column name: ' + reserved)
92 table = pa.Table.from_batches([batch]).append_column(reserved, pa.array(range(batch.num_rows)))
93 connection = database(table, 'source')
94 try:
95 return [row[0] for row in connection.execute('SELECT ' + identifier(reserved) + ' FROM source WHERE (' + condition + ')')]
96 finally:
97 connection.close()
98
99
100def parse_csv(ctx, header, delimiter, schema):
101 import csv
102 import io
103 import itertools
104 if type(header) is not bool or not isinstance(delimiter, str) or len(delimiter) != 1:
105 raise ValueError('header must be boolean and delimiter one character')
106 if not isinstance(schema, str):
107 raise ValueError('schema must be a comma-separated string')
108 chunks = iter(ctx.input('source').bytes())
109 class Raw(io.RawIOBase):
110 def __init__(self):
111 self.pending = memoryview(b'')
112 def readable(self):
113 return True
114 def readinto(self, buffer):
115 while not self.pending:
116 try:
117 self.pending = memoryview(next(chunks))
118 except StopIteration:
119 return 0
120 count = min(len(buffer), len(self.pending))
121 buffer[:count] = self.pending[:count]
122 self.pending = self.pending[count:]
123 return count
124 with io.TextIOWrapper(io.BufferedReader(Raw()), encoding='utf-8', errors='strict', newline='') as stream:
125 reader = csv.reader(stream, delimiter=delimiter, strict=True)
126 first = next(reader, None)
127 if header:
128 names = first if first is not None else columns(schema)
129 rows = reader
130 else:
131 names = columns(schema) if schema else ['_' + str(i) for i in range(len(first or []))]
132 rows = itertools.chain([] if first is None else [first], reader)
133 if not names or any(not n for n in names) or len(names) != len(set(names)):
134 raise ValueError('CSV requires unique, nonempty column names')
135 target = pa.schema([(n, pa.string()) for n in names])
136 output = ctx.records_output('result', target)
137 batch = []
138 for values in rows:
139 if len(values) != len(names):
140 raise ValueError('CSV row width differs from schema')
141 batch.append(dict(zip(names, values)))
142 if len(batch) == 128:
143 output.write(pa.RecordBatch.from_pylist(batch, schema=target))
144 batch.clear()
145 if batch:
146 output.write(pa.RecordBatch.from_pylist(batch, schema=target))
147
148
149def query(ctx, sql):
150 from types import SimpleNamespace
151 from dacp_piflow_sql import process
152 process(SimpleNamespace(params={'sql': sql, 'viewName': 'source'}, input=ctx.input, records_output=ctx.records_output))
153
154
155def aggregate(ctx, axis, actions):
156 if not isinstance(axis, str) or not axis or not isinstance(actions, dict) or not actions:
157 raise ValueError('axis and nonempty customizedProperties mapping are required')
158 expressions = [identifier(axis)]
159 for column, action in actions.items():
160 if not isinstance(column, str) or not isinstance(action, str) or action not in {'COUNT', 'SUM', 'AVG', 'MAX', 'MIN'}:
161 raise ValueError('aggregate must be COUNT, SUM, AVG, MAX or MIN')
162 expressions.append(action + '(' + identifier(column) + ') AS ' + identifier(column + '_' + action))
163 query(ctx, 'SELECT ' + ','.join(expressions) + ' FROM source GROUP BY ' + identifier(axis) + ' ORDER BY ' + identifier(axis))