Paginate with offset argument
[datasette-pytables.git] / datasette_pytables / __init__.py
1 import tables
2 import datasette_connectors as dc
3 from .utils import parse_sql
4
5
6 class PyTablesConnection(dc.Connection):
7     def __init__(self, path, connector):
8         super().__init__(path, connector)
9         self.h5file = tables.open_file(path)
10
11
12 class PyTablesConnector(dc.Connector):
13     connector_type = 'pytables'
14     connection_class = PyTablesConnection
15
16     operators = {
17         'eq': '==',
18         'neq': '!=',
19         'gt': '>',
20         'gte': '>=',
21         'lt': '<',
22         'lte': '<=',
23         'and': '&',
24         'or': '|',
25         'binary_and': '&',
26         'binary_or': '|',
27     }
28
29     def table_names(self):
30         return [
31             node._v_pathname
32             for node in self.conn.h5file
33             if not(isinstance(node, tables.group.Group))
34         ]
35
36     def table_count(self, table_name):
37         table = self.conn.h5file.get_node(table_name)
38         return int(table.nrows)
39
40     def table_info(self, table_name):
41         table = self.conn.h5file.get_node(table_name)
42         colnames = ['value']
43         if isinstance(table, tables.table.Table):
44             colnames = table.colnames
45
46         return [
47             {
48                 'idx': idx,
49                 'name': colname,
50                 'primary_key': False,
51             }
52             for idx, colname in enumerate(colnames)
53         ]
54
55     def hidden_table_names(self):
56         return []
57
58     def detect_spatialite(self):
59         return False
60
61     def view_names(self):
62         return []
63
64     def detect_fts(self, table_name):
65         return False
66
67     def foreign_keys(self, table_name):
68         return []
69
70     def table_exists(self, table_name):
71         try:
72             self.conn.h5file.get_node(table_name)
73             return True
74         except:
75             return False
76
77     def table_definition(self, table_type, table_name):
78         table = self.conn.h5file.get_node(table_name)
79         colnames = ['value']
80         if isinstance(table, tables.table.Table):
81             colnames = table.colnames
82
83         return 'CREATE TABLE {} ({})'.format(
84             table_name,
85             ', '.join(colnames),
86         )
87
88     def indices_definition(self, table_name):
89         return []
90
91     def execute(
92         self,
93         sql,
94         params=None,
95         truncate=False,
96         custom_time_limit=None,
97         page_size=None,
98         log_sql_errors=True,
99     ):
100         results = []
101         truncated = False
102         description = ()
103
104         parsed_sql = parse_sql(sql, params)
105
106         while isinstance(parsed_sql['from'], dict):
107             # Pytables does not support subqueries
108             parsed_sql['from'] = parsed_sql['from']['value']['from']
109
110         table = self.conn.h5file.get_node(parsed_sql['from'])
111         table_rows = []
112         fields = parsed_sql['select']
113         colnames = ['value']
114         if type(table) is tables.table.Table:
115             colnames = table.colnames
116
117         query = ''
118         start = 0
119         end = table.nrows
120
121         def _get_field_type(field):
122             coltype = table.dtype.name
123             if type(table) is tables.table.Table:
124                 coltype = table.coltypes[field]
125             return coltype
126
127         # Use 'where' statement or get all the rows
128         def _cast_param(field, pname):
129             # Cast value to the column type
130             coltype = _get_field_type(field)
131             fcast = None
132             if coltype == 'string':
133                 fcast = str
134             elif coltype.startswith('int'):
135                 fcast = int
136             elif coltype.startswith('float'):
137                 fcast = float
138             if fcast:
139                 params[pname] = fcast(params[pname])
140
141         def _translate_where(where):
142             # Translate SQL to PyTables expression
143             nonlocal start, end
144             expr = ''
145             operator = list(where)[0]
146
147             if operator in ['and', 'or']:
148                 subexpr = [_translate_where(e) for e in where[operator]]
149                 subexpr = filter(lambda e: e, subexpr)
150                 subexpr = ["({})".format(e) for e in subexpr]
151                 expr = " {} ".format(self.operators[operator]).join(subexpr)
152             elif operator == 'exists':
153                 pass
154             elif where == {'eq': ['rowid', 'p0']}:
155                 start = int(params['p0'])
156                 end = start + 1
157             elif where == {'gt': ['rowid', 'p0']}:
158                 start = int(params['p0']) + 1
159             else:
160                 left, right = where[operator]
161
162                 if isinstance(left, dict):
163                     left = "(" + _translate_where(left) + ")"
164                 elif left in params:
165                     _cast_param(right, left)
166
167                 if isinstance(right, dict):
168                     right = "(" + _translate_where(right) + ")"
169                 elif right in params:
170                     _cast_param(left, right)
171
172                 expr = "{left} {operator} {right}".format(
173                     left=left,
174                     operator=self.operators.get(operator, operator),
175                     right=right,
176                 )
177
178             return expr
179
180         if 'where' in parsed_sql:
181             if type(parsed_sql['where']) is dict:
182                 query = _translate_where(parsed_sql['where'])
183             else:
184                 query = parsed_sql['where']
185
186         # Sort by column
187         orderby = ''
188         if 'orderby' in parsed_sql:
189             orderby = parsed_sql['orderby']
190             if type(orderby) is list:
191                 orderby = orderby[0]
192             orderby = orderby['value']
193             if orderby == 'rowid':
194                 orderby = ''
195
196         # Limit number of rows
197         limit = None
198         if 'limit' in parsed_sql:
199             limit = int(parsed_sql['limit'])
200
201         # Offset
202         offset = None
203         if 'offset' in parsed_sql:
204             offset = int(parsed_sql['offset'])
205
206         # Truncate if needed
207         if page_size and max_returned_rows and truncate:
208             if max_returned_rows == page_size:
209                 max_returned_rows += 1
210
211         # Execute query
212         if query:
213             if not ' glob ' in query:
214                 table_rows = table.where(query, params, start, end)
215         elif orderby:
216             table_rows = table.itersorted(orderby, start=start, stop=end)
217         else:
218             table_rows = table.iterrows(start, end)
219
220         # Prepare rows
221         def normalize_field_value(value):
222             if type(value) is bytes:
223                 return value.decode('utf-8')
224             elif not type(value) in (int, float, complex):
225                 return str(value)
226             else:
227                 return value
228
229         def make_get_rowid():
230             if type(table) is tables.table.Table:
231                 def get_rowid(row):
232                     return int(row.nrow)
233             else:
234                 rowid = start - 1
235                 def get_rowid(row):
236                     nonlocal rowid
237                     rowid += 1
238                     return rowid
239             return get_rowid
240
241         def make_get_row_value():
242             if type(table) is tables.table.Table:
243                 def get_row_value(row, field):
244                     return row[field]
245             else:
246                 def get_row_value(row, field):
247                     return row
248             return get_row_value
249
250         # Get results
251         get_rowid = make_get_rowid()
252         get_row_value = make_get_row_value()
253         if offset:
254             table_rows = table_rows[offset:]
255         count = 0
256         for table_row in table_rows:
257             count += 1
258             if limit is not None and count > limit:
259                 break
260             if truncate and max_returned_rows and count > max_returned_rows:
261                 truncated = True
262                 break
263             row = {}
264             for field in fields:
265                 field_name = field
266                 if isinstance(field, dict):
267                     field_name = field['value']
268                 if isinstance(field_name, dict) and 'distinct' in field_name:
269                     field_name = field_name['distinct']
270                 if field_name == 'rowid':
271                     row['rowid'] = get_rowid(table_row)
272                 elif field_name == '*':
273                     for col in colnames:
274                         row[col] = normalize_field_value(get_row_value(table_row, col))
275                 elif isinstance(field_name, dict):
276                     if field_name.get('count') == '*':
277                         row['count(*)'] = int(table.nrows)
278                     elif field_name.get('json_type'):
279                         field_name = field_name.get('json_type')
280                         row['json_type(' + field_name + ')'] = _get_field_type(field)
281                     else:
282                         raise Exception("Function not recognized")
283                 else:
284                     row[field_name] = normalize_field_value(get_row_value(table_row, field_name))
285             results.append(row)
286
287         # Prepare query description
288         for field in [f['value'] if isinstance(f, dict) else f for f in fields]:
289             if field == '*':
290                 for col in colnames:
291                     description += ((col,),)
292             else:
293                 description += ((field,),)
294
295         return results, truncated, description