Passed
Pull Request — master (#102)
by
unknown
01:11
created

datatables.DataTables._get_regex_operator()   A

Complexity

Conditions 4

Size

Total Lines 16
Code Lines 15

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 4
eloc 15
nop 1
dl 0
loc 16
rs 9.65
c 0
b 0
f 0
1
"""Main entry file, definition of ColumnDT and DataTables."""
2
import sys
3
from collections import namedtuple
4
from logging import getLogger
5
from dateutil.parser import parse as date_parse
6
import datetime
0 ignored issues
show
introduced by
standard import "import datetime" should be placed before "from dateutil.parser import parse as date_parse"
Loading history...
7
from sqlalchemy.dialects import postgresql, mysql, sqlite
8
from sqlalchemy import func, Text, or_
9
import math
0 ignored issues
show
introduced by
standard import "import math" should be placed before "from dateutil.parser import parse as date_parse"
Loading history...
10
11
log = getLogger(__file__)
0 ignored issues
show
Coding Style Naming introduced by
The name log does not conform to the constant naming conventions ((([A-Z_][A-Z0-9_]*)|(__.*__))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
12
13
if sys.version_info > (3, 0):
14
    unicode = str
0 ignored issues
show
Coding Style Naming introduced by
The name unicode does not conform to the class naming conventions ([A-Z_][a-zA-Z0-9]+$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
15
16
17
def clean_regex(regex):
18
    """
19
    Escape any regex special characters other than alternation.
20
21
    :param regex: regex from datatables interface
22
    :type regex: str
23
    :rtype: str with regex to use with database
24
    """
25
    # copy for return
26
    ret_regex = regex
27
28
    # these characters are escaped (all except alternation | and escape \)
29
    # see http://www.regular-expressions.info/refquick.html
30
    escape_chars = '[^$.?*+(){}'
31
32
    # remove any escape chars
33
    ret_regex = ret_regex.replace('\\', '')
34
35
    # escape any characters which are used by regex
36
    # could probably concoct something incomprehensible using re.sub() but
37
    # prefer to write clear code with this loop
38
    # note expectation that no characters have already been escaped
39
    for c in escape_chars:
0 ignored issues
show
Coding Style Naming introduced by
The name c does not conform to the variable naming conventions ((([a-z][a-z0-9_]{2,30})|(_[a-z0-9_]*))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
40
        ret_regex = ret_regex.replace(c, '\\' + c)
41
42
    # remove any double alternations until these don't exist any more
43
    while True:
44
        old_regex = ret_regex
45
        ret_regex = ret_regex.replace('||', '|')
46
        if old_regex == ret_regex:
47
            break
48
49
    # if last char is alternation | remove it because this
50
    # will cause operational error
51
    # this can happen as user is typing in global search box
52
    while len(ret_regex) >= 1 and ret_regex[-1] == '|':
53
        ret_regex = ret_regex[:-1]
54
55
    # and back to the caller
56
    return ret_regex
57
58
59
search_operators = {
0 ignored issues
show
Coding Style Naming introduced by
The name search_operators does not conform to the constant naming conventions ((([A-Z_][A-Z0-9_]*)|(__.*__))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
60
    '=': lambda expr, value: expr == value,
61
    '>': lambda expr, value: expr > value,
62
    '>=': lambda expr, value: expr >= value,
63
    '<': lambda expr, value: expr < value,
64
    '<=': lambda expr, value: expr <= value,
65
}
66
67
68
def parse_query_value(combined_value):
69
    """Parse value in form of '>value' to a lambda and a value."""
70
    split = len(combined_value) - len(combined_value.lstrip('<>='))
71
    operator = combined_value[:split]
72
    if operator == '':
73
        operator = '='
74
    try:
75
        operator_func = search_operators[operator]
76
    except KeyError:
77
        raise ValueError(
78
            'Numeric query should start with operator, choose from %s'
79
            % ', '.join(search_operators.keys()))
80
    value = combined_value[split:].strip()
81
    return operator_func, value
82
83
84
def numeric_query(expr, value):
0 ignored issues
show
Coding Style introduced by
This function should have a docstring.

The coding style of this project requires that you add a docstring to this code element. Below, you find an example for methods:

class SomeClass:
    def some_method(self):
        """Do x and return foo."""

If you would like to know more about docstrings, we recommend to read PEP-257: Docstring Conventions.

Loading history...
85
    operator_func, value = parse_query_value(value)
86
    if value == '':
87
        num_value = 0
88
    else:
89
        num_value = float(value)
90
91
    return operator_func(expr, num_value)
92
93
94
def date_query(expr, value):
0 ignored issues
show
Coding Style introduced by
This function should have a docstring.

The coding style of this project requires that you add a docstring to this code element. Below, you find an example for methods:

class SomeClass:
    def some_method(self):
        """Do x and return foo."""

If you would like to know more about docstrings, we recommend to read PEP-257: Docstring Conventions.

Loading history...
95
    operator_func, value = parse_query_value(value)
96
    try:
97
        date_value = date_parse(value)
98
    except ValueError:
99
        date_value = datetime.datetime.now()
100
101
    return operator_func(expr, date_value)
102
103
104
def yadcf_range_number(expr, value):
0 ignored issues
show
Coding Style introduced by
This function should have a docstring.

The coding style of this project requires that you add a docstring to this code element. Below, you find an example for methods:

class SomeClass:
    def some_method(self):
        """Do x and return foo."""

If you would like to know more about docstrings, we recommend to read PEP-257: Docstring Conventions.

Loading history...
105
    v_from, v_to = value.split('-yadcf_delim-')
106
    v_from = float(v_from) if v_from != '' else -float('inf')
107
    v_to = float(v_to) if v_to != '' else float('inf')
108
    log.debug('yadcf_range_number: between %f and %f', v_from, v_to)
109
    return expr.between(v_from, v_to)
110
111
112
def yadcf_range_date(expr, value):
0 ignored issues
show
Coding Style introduced by
This function should have a docstring.

The coding style of this project requires that you add a docstring to this code element. Below, you find an example for methods:

class SomeClass:
    def some_method(self):
        """Do x and return foo."""

If you would like to know more about docstrings, we recommend to read PEP-257: Docstring Conventions.

Loading history...
113
    v_from, v_to = value.split('-yadcf_delim-')
114
    v_from = date_parse(v_from) if v_from != '' else datetime.date.min
115
    v_to = date_parse(v_to) if v_to != '' else datetime.date.max
116
    log.debug('yadcf_range_date: between %s and %s', v_from, v_to)
117
    return expr.between(v_from, v_to)
118
119
120
def yadcf_multi_select(expr, value):
0 ignored issues
show
Coding Style introduced by
This function should have a docstring.

The coding style of this project requires that you add a docstring to this code element. Below, you find an example for methods:

class SomeClass:
    def some_method(self):
        """Do x and return foo."""

If you would like to know more about docstrings, we recommend to read PEP-257: Docstring Conventions.

Loading history...
121
    options = value.split('|')
122
    log.debug('yadcf_multi_select: in %s', options)
123
    return expr.cast(Text).in_(options)
124
125
126
search_methods = {
0 ignored issues
show
Coding Style Naming introduced by
The name search_methods does not conform to the constant naming conventions ((([A-Z_][A-Z0-9_]*)|(__.*__))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
127
    'none': lambda expr, value: None,
128
    'string_contains': lambda expr, value: expr.ilike('%' + value + '%'),
129
    'ilike': lambda expr, value: expr.ilike(value),
130
    'like': lambda expr, value: expr.like(value),
131
    'numeric': numeric_query,
132
    'date': date_query,
133
    'yadcf_text': lambda expr, value: expr.ilike('%' + value + '%'),
134
    'yadcf_autocomplete': lambda expr, value: expr == value,
135
    'yadcf_select': lambda expr, value: expr.ilike('%' + value + '%'),
136
    'yadcf_multi_select': yadcf_multi_select,
137
    'yadcf_range_number': yadcf_range_number,
138
    'yadcf_range_number_slider': yadcf_range_number,
139
    'yadcf_range_date': yadcf_range_date
140
}
141
142
143
ColumnTuple = namedtuple(
144
    'ColumnDT',
145
    ['sqla_expr', 'column_name', 'mData', 'search_method',
146
     'nulls_order', 'global_search', 'sort_method'])
147
148
149
class InvalidParameter(Exception):
150
151
    """Class defining an invalid parameter exception."""
152
153
    pass
154
155
156
class ColumnDT(ColumnTuple):
0 ignored issues
show
Unused Code introduced by
The variable __class__ seems to be unused.
Loading history...
157
158
    """Class defining a DataTables Column with a ColumnTuple.
159
160
    :param sqla_expr: SQLAlchemy queryable attribute of object
161
        (column, column_property, hubrid property, or
162
        combined expression)
163
    :type sqla_expr: SQLAlchemy query expression
164
    :param mData: name of the mData property as defined in the
165
        DataTables javascript options (default None)
166
    :type mData: str
167
    :param search_method: Define how to interpret search values.
168
        Possible values: 'none', 'contains', 'ilike', 'like',
169
        'numeric'
170
    :type search_method: str
171
    :param nulls_order: define a sort order for the NULL values. Possible
172
        values: nullsfirst, nullslast. (default None)
173
    :type nulls_order: str
174
    :param global_search: search this column for the global search box
175
    :type global_search: bool
176
    :returns: a ColumnDT object
177
    """
178
179
    def __new__(cls, sqla_expr, column_name=None, mData=None,
0 ignored issues
show
best-practice introduced by
Too many arguments (8/5)
Loading history...
180
                search_method='string_contains', nulls_order=None,
181
                global_search=True, sort_method=None):
182
        """Set default values for mData and filter.
183
184
        On creation, sets default None values for mData and string value for
185
        filter (cause: Object representation is not JSON serializable).
186
        """
187
        # check if allowed value
188
        if nulls_order and nulls_order not in ['nullsfirst', 'nullslast']:
189
            raise ValueError('`%s` is not an allowed value for nulls_order.'
190
                             % nulls_order)
191
        if search_method not in search_methods:
192
            raise ValueError('`%s` is not an allowed value for search_method.'
193
                             % search_method)
194
195
        return super(ColumnDT, cls).__new__(
196
            cls, sqla_expr, column_name, mData, search_method,
197
            nulls_order, global_search, sort_method)
198
199
200
class DataTables:
0 ignored issues
show
best-practice introduced by
Too many instance attributes (11/7)
Loading history...
Unused Code introduced by
The variable __class__ seems to be unused.
Loading history...
201
202
    """Class defining a DataTables object.
203
204
    :param request: request containing the GET values, specified by the
205
        datatable for filtering, sorting and paging
206
    :type request: pyramid.request
207
    :param query: the query wanted to be seen in the the table
208
    :type query: sqlalchemy.orm.query.Query
209
    :param columns: columns specification for the datatables
210
    :type columns: list
211
212
    :returns: a DataTables object
213
    """
214
215
    def __init__(self, request, query, columns,
216
                 allow_regex_searches=False):
217
        """Initialize object and run the query."""
218
        self.params = dict(request)
219
        if 'sEcho' in self.params:
220
            raise ValueError(
221
                'Legace datatables not supported, upgrade to >=1.10')
222
        self.query = query
223
        self.columns = columns
224
        self.results = None
225
        self.allow_regex_searches = allow_regex_searches
226
227
        # total in the table after filtering
228
        self.cardinality_filtered = 0
229
230
        # total in the table unfiltered
231
        self.cardinality = 0
232
233
        self.yadcf_params = []
234
        self.filter_expressions = []
235
        self.error = None
236
        try:
237
            self.run()
238
        except Exception as exc:
0 ignored issues
show
Best Practice introduced by
Catching very general exceptions such as Exception is usually not recommended.

Generally, you would want to handle very specific errors in the exception handler. This ensure that you do not hide other types of errors which should be fixed.

So, unless you specifically plan to handle any error, consider adding a more specific exception.

Loading history...
239
            self.error = str(exc)
240
241
    def output_result(self):
242
        """Output results in the format needed by DataTables."""
243
        output = {}
244
        output['draw'] = str(int(self.params['draw']))
245
        output['recordsTotal'] = str(self.cardinality)
246
        output['recordsFiltered'] = str(self.cardinality_filtered)
247
        if self.error:
248
            output['error'] = self.error
249
            return output
250
251
        output['data'] = self.results
252
        for k, v in self.yadcf_params:
0 ignored issues
show
Coding Style Naming introduced by
The name v does not conform to the variable naming conventions ((([a-z][a-z0-9_]{2,30})|(_[a-z0-9_]*))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
253
            output[k] = v
254
        return output
255
256
    def _query_with_all_filters_except_one(self, query, exclude):
257
        return query.filter(
258
            *[e for i, e in enumerate(self.filter_expressions)
259
              if e is not None and i is not exclude]
260
        )
261
262
    def _set_yadcf_data(self, query):
263
        # determine values for yadcf filters
264
        for i, col in enumerate(self.columns):
265
            if col.search_method in 'yadcf_range_number_slider':
266
                v = query.add_columns(
0 ignored issues
show
Coding Style Naming introduced by
The name v does not conform to the variable naming conventions ((([a-z][a-z0-9_]{2,30})|(_[a-z0-9_]*))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
267
                    func.min(col.sqla_expr),
268
                    func.max(col.sqla_expr)
269
                ).one()
270
                self.yadcf_params.append((
271
                    'yadcf_data_{:d}'.format(i),
272
                    (math.floor(v[0]), math.ceil(v[1])))
273
                )
0 ignored issues
show
Coding Style introduced by
Wrong continued indentation (add 24 spaces).
Loading history...
274
            if col.search_method in ['yadcf_select', 'yadcf_multi_select',
275
                                     'yadcf_autocomplete']:
276
                filtered = self._query_with_all_filters_except_one(
277
                    query=query, exclude=i)
278
                v = filtered.add_columns(col.sqla_expr).distinct().all()
0 ignored issues
show
Coding Style Naming introduced by
The name v does not conform to the variable naming conventions ((([a-z][a-z0-9_]{2,30})|(_[a-z0-9_]*))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
279
                self.yadcf_params.append(
280
                    ('yadcf_data_{:d}'.format(i), [r[0] for r in sorted(set(v))]))
281
282
    def run(self):
283
        """Launch filtering, sorting and paging to output results."""
284
        query = self.query
285
286
        # count before filtering
287
        self.cardinality = query.add_columns(self.columns[0].sqla_expr).count()
288
289
        self._set_column_filter_expressions()
290
        self._set_global_filter_expression()
291
        self._set_sort_expressions()
292
        self._set_yadcf_data(query)
293
294
        # apply filters
295
        query = query.filter(
296
            *[e for e in self.filter_expressions if e is not None])
297
298
        self.cardinality_filtered = query.add_columns(
299
            self.columns[0].sqla_expr).count()
300
301
        # apply sorts
302
        query = query.order_by(
303
            *[e for e in self.sort_expressions if e is not None])
304
305
        # add paging options
306
        length = int(self.params.get('length'))
307
        if length >= 0:
308
            query = query.limit(length)
309
        elif length == -1:
310
            pass
311
        else:
312
            raise(ValueError(
313
                'Length should be a positive integer or -1 to disable'))
314
        query = query.offset(int(self.params.get('start')))
315
316
        # add columns to query
317
        query = query.add_columns(
318
            *[c.sqla_expr for c in self.columns])
319
320
        # fetch the result of the queries
321
        column_names = [col.mData if col.mData else str(i)
322
                        for i, col in enumerate(self.columns)]
323
        self.results = [{k: v for k, v in zip(
324
            column_names, row)} for row in query.all()]
325
326
    def _set_column_filter_expressions(self):
327
        """Construct the query: filtering.
328
329
        Add filtering when per column searching is used.
330
        """
331
        # per columns filters:
332
        for i in range(len(self.columns)):
333
            filter_expr = None
334
            value = self.params.get(
335
                'columns[{:d}][search][value]'.format(i), '')
336
            if value:
337
                search_func = search_methods[self.columns[i].search_method]
338
                filter_expr = search_func(self.columns[i].sqla_expr, value)
339
            self.filter_expressions.append(filter_expr)
340
341
    def _set_global_filter_expression(self):
342
        # global search filter
343
        global_search = self.params.get('search[value]', '')
344
        if global_search is '':
0 ignored issues
show
introduced by
Comparison to literal
Loading history...
345
            return
346
347
        if (self.allow_regex_searches and
348
                self.params.get('search[regex]') == 'true'):
349
            op = self._get_regex_operator()
0 ignored issues
show
Coding Style Naming introduced by
The name op does not conform to the variable naming conventions ((([a-z][a-z0-9_]{2,30})|(_[a-z0-9_]*))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
350
            val = clean_regex(global_search)
351
352
            def filter_for(col):
0 ignored issues
show
Coding Style introduced by
This function should have a docstring.

The coding style of this project requires that you add a docstring to this code element. Below, you find an example for methods:

class SomeClass:
    def some_method(self):
        """Do x and return foo."""

If you would like to know more about docstrings, we recommend to read PEP-257: Docstring Conventions.

Loading history...
353
                return col.sqla_expr.op(op)(val)
0 ignored issues
show
introduced by
The variable op does not seem to be defined for all execution paths.
Loading history...
354
        else:
355
            val = '%' + global_search + '%'
356
357
            def filter_for(col):
0 ignored issues
show
Coding Style introduced by
This function should have a docstring.

The coding style of this project requires that you add a docstring to this code element. Below, you find an example for methods:

class SomeClass:
    def some_method(self):
        """Do x and return foo."""

If you would like to know more about docstrings, we recommend to read PEP-257: Docstring Conventions.

Loading history...
358
                return col.sqla_expr.cast(Text).ilike(val)
359
360
        global_filter = [filter_for(col)
361
                         for col in self.columns if col.global_search]
362
363
        self.filter_expressions.append(or_(*global_filter))
364
365
    def _set_sort_expressions(self):
366
        """Construct the query: sorting.
367
368
        Add sorting(ORDER BY) on the columns needed to be applied on.
369
        """
370
        sort_expressions = []
371
        i = 0
372
        while self.params.get('order[{:d}][column]'.format(i), False):
373
            column_nr = int(self.params.get('order[{:d}][column]'.format(i)))
374
            column = self.columns[column_nr]
375
            direction = self.params.get('order[{:d}][dir]'.format(i))
376
            sort_expr = column.sqla_expr
377
            if column.sort_method is not None:
378
                sort_expr = column.sort_method
379
            if direction == 'asc':
380
                sort_expr = sort_expr.asc()
381
            elif direction == 'desc':
382
                sort_expr = sort_expr.desc()
383
            else:
384
                raise ValueError(
385
                    'Invalid order direction: {}'.format(direction))
386
            if column.nulls_order:
387
                if column.nulls_order == 'nullsfirst':
388
                    sort_expr = sort_expr.nullsfirst()
389
                elif column.nulls_order == 'nullslast':
390
                    sort_expr = sort_expr.nullslast()
391
                else:
392
                    raise ValueError(
393
                        'Invalid order direction: %s'.format(direction))
0 ignored issues
show
Bug introduced by
Too many arguments for format string
Loading history...
394
395
            sort_expressions.append(sort_expr)
396
            i += 1
397
        self.sort_expressions = sort_expressions
0 ignored issues
show
Coding Style introduced by
The attribute sort_expressions was defined outside __init__.

It is generally a good practice to initialize all attributes to default values in the __init__ method:

class Foo:
    def __init__(self, x=None):
        self.x = x
Loading history...
398
399
    def _get_regex_operator(self):
400
        if isinstance(
401
                self.query.session.bind.dialect,
402
                postgresql.dialect):
403
            return '~'
404
        elif isinstance(
405
                self.query.session.bind.dialect,
406
                mysql.dialect):
407
            return 'REGEXP'
408
        elif isinstance(
409
                self.query.session.bind.dialect,
410
                sqlite.dialect):
411
            return 'REGEXP'
412
        else:
413
            raise NotImplementedError(
414
                'Regex searches are not implemented for this dialect')
415