Coverage for .tox/coverage/lib/python3.11/site-packages/wuttaweb/grids/filters.py: 100%

1# -*- coding: utf-8; -*-

2################################################################################

4# wuttaweb -- Web App for Wutta Framework

7# This file is part of Wutta Framework.

9# Wutta Framework is free software: you can redistribute it and/or modify it

10# under the terms of the GNU General Public License as published by the Free

11# Software Foundation, either version 3 of the License, or (at your option) any

12# later version.

13#

14# Wutta Framework is distributed in the hope that it will be useful, but

15# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or

16# FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for

17# more details.

18#

19# You should have received a copy of the GNU General Public License along with

20# Wutta Framework. If not, see <http://www.gnu.org/licenses/>.

21#

22################################################################################

23"""

24Grid Filters

25"""

27import datetime

28import logging

30import sqlalchemy as sa

32from wuttjamaican.util import UNSPECIFIED

35log = logging.getLogger(__name__)

38class VerbNotSupported(Exception):

39 """ """

41 def __init__(self, verb):

42 self.verb = verb

44 def __str__(self):

45 return f"unknown filter verb not supported: {self.verb}"

48class GridFilter:

49 """

50 Filter option for a grid. Represents both the "features" as well

51 as "state" for the filter.

53 :param request: Current :term:`request` object.

55 :param model_property: Property of a model class, representing the

56 column by which to filter. For instance,

57 ``model.Person.full_name``.

59 :param \**kwargs: Any additional kwargs will be set as attributes

60 on the filter instance.

62 Filter instances have the following attributes:

64 .. attribute:: key

66 Unique key for the filter. This often corresponds to a "column

67 name" for the grid, but not always.

69 .. attribute:: label

71 Display label for the filter field.

73 .. attribute:: data_type

75 Simplistic "data type" which the filter supports. So far this

76 will be one of:

78 * ``'string'``

79 * ``'date'``

81 Note that this mainly applies to the "value input" used by the

82 filter. There is no data type for boolean since it does not

83 need a value input; the verb is enough.

85 .. attribute:: active

87 Boolean indicating whether the filter is currently active.

89 See also :attr:`verb` and :attr:`value`.

91 .. attribute:: verb

93 Verb for current filter, if :attr:`active` is true.

95 See also :attr:`value`.

97 .. attribute:: value

99 Value for current filter, if :attr:`active` is true.

100

101 See also :attr:`verb`.

102

103 .. attribute:: default_active

104

105 Boolean indicating whether the filter should be active by

106 default, i.e. when first displaying the grid.

107

108 See also :attr:`default_verb` and :attr:`default_value`.

109

110 .. attribute:: default_verb

111

112 Filter verb to use by default. This will be auto-selected when

113 the filter is first activated, or when first displaying the

114 grid if :attr:`default_active` is true.

115

116 See also :attr:`default_value`.

117

118 .. attribute:: default_value

119

120 Filter value to use by default. This will be auto-populated

121 when the filter is first activated, or when first displaying

122 the grid if :attr:`default_active` is true.

123

124 See also :attr:`default_verb`.

125 """

126 data_type = 'string'

127 default_verbs = ['equal', 'not_equal']

128

129 default_verb_labels = {

130 'is_any': "is any",

131 'equal': "equal to",

132 'not_equal': "not equal to",

133 'greater_than': "greater than",

134 'greater_equal': "greater than or equal to",

135 'less_than': "less than",

136 'less_equal': "less than or equal to",

137 # 'between': "between",

138 'is_true': "is true",

139 'is_false': "is false",

140 'is_false_null': "is false or null",

141 'is_null': "is null",

142 'is_not_null': "is not null",

143 'contains': "contains",

144 'does_not_contain': "does not contain",

145 }

146

147 valueless_verbs = [

148 'is_any',

149 'is_true',

150 'is_false',

151 'is_false_null',

152 'is_null',

153 'is_not_null',

154 ]

155

156 def __init__(

157 self,

158 request,

159 key,

160 label=None,

161 verbs=None,

162 default_active=False,

163 default_verb=None,

164 default_value=None,

165 **kwargs,

166 ):

167 self.request = request

168 self.key = key

169 self.config = self.request.wutta_config

170 self.app = self.config.get_app()

171 self.label = label or self.app.make_title(self.key)

172

173 # active

174 self.default_active = default_active

175 self.active = self.default_active

176

177 # verb

178 if verbs is not None:

179 self.verbs = verbs

180 if default_verb:

181 self.default_verb = default_verb

182

183 # value

184 self.default_value = default_value

185 self.value = self.default_value

186

187 self.__dict__.update(kwargs)

188

189 def __repr__(self):

190 verb = getattr(self, 'verb', None)

191 return (f"{self.__class__.__name__}("

192 f"key='{self.key}', "

193 f"active={self.active}, "

194 f"verb={repr(verb)}, "

195 f"value={repr(self.value)})")

196

197 def get_verbs(self):

198 """

199 Returns the list of verbs supported by the filter.

200 """

201 verbs = None

202

203 if hasattr(self, 'verbs'):

204 verbs = self.verbs

205

206 else:

207 verbs = self.default_verbs

208

209 if callable(verbs):

210 verbs = verbs()

211 verbs = list(verbs)

212

213 if self.nullable:

214 if 'is_null' not in verbs:

215 verbs.append('is_null')

216 if 'is_not_null' not in verbs:

217 verbs.append('is_not_null')

218

219 if 'is_any' not in verbs:

220 verbs.append('is_any')

221

222 return verbs

223

224 def get_verb_labels(self):

225 """

226 Returns a dict of all defined verb labels.

227 """

228 # TODO: should traverse hierarchy

229 labels = dict([(verb, verb) for verb in self.get_verbs()])

230 labels.update(self.default_verb_labels)

231 return labels

232

233 def get_valueless_verbs(self):

234 """

235 Returns a list of verb names which do not need a value.

236 """

237 return self.valueless_verbs

238

239 def get_default_verb(self):

240 """

241 Returns the default verb for the filter.

242 """

243 verb = None

244

245 if hasattr(self, 'default_verb'):

246 verb = self.default_verb

247

248 elif hasattr(self, 'verb'):

249 verb = self.verb

250

251 if not verb:

252 verbs = self.get_verbs()

253 if verbs:

254 verb = verbs[0]

255

256 return verb

257

258 def apply_filter(self, data, verb=None, value=UNSPECIFIED):

259 """

260 Filter the given data set according to a verb/value pair.

261

262 If verb and/or value are not specified, will use :attr:`verb`

263 and/or :attr:`value` instead.

264

265 This method does not directly filter the data; rather it

266 delegates (based on ``verb``) to some other method. The

267 latter may choose *not* to filter the data, e.g. if ``value``

268 is empty, in which case this may return the original data set

269 unchanged.

270

271 :returns: The (possibly) filtered data set.

272 """

273 if verb is None:

274 verb = self.verb

275 if not verb:

276 verb = self.get_default_verb()

277 log.warn("missing verb for '%s' filter, will use default verb: %s",

278 self.key, verb)

279

280 # only attempt for known verbs

281 if verb not in self.get_verbs():

282 raise VerbNotSupported(verb)

283

284 # fallback value

285 if value is UNSPECIFIED:

286 value = self.value

287

288 # locate filter method

289 func = getattr(self, f'filter_{verb}', None)

290 if not func:

291 raise VerbNotSupported(verb)

292

293 # invoke filter method

294 return func(data, value)

295

296 def filter_is_any(self, data, value):

297 """

298 This is a no-op which always ignores the value and returns the

299 data as-is.

300 """

301 return data

302

303

304class AlchemyFilter(GridFilter):

305 """

306 Filter option for a grid with SQLAlchemy query data.

307

308 This is a subclass of :class:`GridFilter`. It requires a

309 ``model_property`` to know how to filter the query.

310

311 :param model_property: Property of a model class, representing the

312 column by which to filter. For instance,

313 ``model.Person.full_name``.

314

315 :param nullable: Boolean indicating whether the filter should

316 include ``is_null`` and ``is_not_null`` verbs. If not

317 specified, the column will be inspected and use its nullable

318 flag.

319 """

320

321 def __init__(self, *args, **kwargs):

322 nullable = kwargs.pop('nullable', None)

323 super().__init__(*args, **kwargs)

324

325 self.nullable = nullable

326 if self.nullable is None:

327 columns = self.model_property.prop.columns

328 if len(columns) == 1:

329 self.nullable = columns[0].nullable

330

331 def coerce_value(self, value):

332 """

333 Coerce the given value to the correct type/format for use with

334 the filter.

335

336 Default logic returns value as-is; subclass may override.

337 """

338 return value

339

340 def filter_equal(self, query, value):

341 """

342 Filter data with an equal (``=``) condition.

343 """

344 value = self.coerce_value(value)

345 if value is None:

346 return query

347

348 return query.filter(self.model_property == value)

349

350 def filter_not_equal(self, query, value):

351 """

352 Filter data with a not equal (``!=``) condition.

353 """

354 value = self.coerce_value(value)

355 if value is None:

356 return query

357

358 # sql probably excludes null values from results, but user

359 # probably does not expect that, so explicitly include them.

360 return query.filter(sa.or_(

361 self.model_property == None,

362 self.model_property != value,

363 ))

364

365 def filter_greater_than(self, query, value):

366 """

367 Filter data with a greater than (``>``) condition.

368 """

369 value = self.coerce_value(value)

370 if value is None:

371 return query

372 return query.filter(self.model_property > value)

373

374 def filter_greater_equal(self, query, value):

375 """

376 Filter data with a greater than or equal (``>=``) condition.

377 """

378 value = self.coerce_value(value)

379 if value is None:

380 return query

381 return query.filter(self.model_property >= value)

382

383 def filter_less_than(self, query, value):

384 """

385 Filter data with a less than (``<``) condition.

386 """

387 value = self.coerce_value(value)

388 if value is None:

389 return query

390 return query.filter(self.model_property < value)

391

392 def filter_less_equal(self, query, value):

393 """

394 Filter data with a less than or equal (``<=``) condition.

395 """

396 value = self.coerce_value(value)

397 if value is None:

398 return query

399 return query.filter(self.model_property <= value)

400

401 def filter_is_null(self, query, value):

402 """

403 Filter data with an ``IS NULL`` query. The value is ignored.

404 """

405 return query.filter(self.model_property == None)

406

407 def filter_is_not_null(self, query, value):

408 """

409 Filter data with an ``IS NOT NULL`` query. The value is

410 ignored.

411 """

412 return query.filter(self.model_property != None)

413

414

415class StringAlchemyFilter(AlchemyFilter):

416 """

417 SQLAlchemy filter option for a text data column.

418

419 Subclass of :class:`AlchemyFilter`.

420 """

421 default_verbs = ['contains', 'does_not_contain',

422 'equal', 'not_equal']

423

424 def coerce_value(self, value):

425 """ """

426 if value is not None:

427 value = str(value)

428 if value:

429 return value

430

431 def filter_contains(self, query, value):

432 """

433 Filter data with an ``ILIKE`` condition.

434 """

435 value = self.coerce_value(value)

436 if not value:

437 return query

438

439 criteria = []

440 for val in value.split():

441 val = val.replace('_', r'\_')

442 val = f'%{val}%'

443 criteria.append(self.model_property.ilike(val))

444

445 return query.filter(sa.and_(*criteria))

446

447 def filter_does_not_contain(self, query, value):

448 """

449 Filter data with a ``NOT ILIKE`` condition.

450 """

451 value = self.coerce_value(value)

452 if not value:

453 return query

454

455 criteria = []

456 for val in value.split():

457 val = val.replace('_', r'\_')

458 val = f'%{val}%'

459 criteria.append(~self.model_property.ilike(val))

460

461 # sql probably excludes null values from results, but user

462 # probably does not expect that, so explicitly include them.

463 return query.filter(sa.or_(

464 self.model_property == None,

465 sa.and_(*criteria)))

466

467

468class NumericAlchemyFilter(AlchemyFilter):

469 """

470 SQLAlchemy filter option for a numeric data column.

471

472 Subclass of :class:`AlchemyFilter`.

473 """

474 default_verbs = ['equal', 'not_equal',

475 'greater_than', 'greater_equal',

476 'less_than', 'less_equal']

477

478

479class IntegerAlchemyFilter(NumericAlchemyFilter):

480 """

481 SQLAlchemy filter option for an integer data column.

482

483 Subclass of :class:`NumericAlchemyFilter`.

484 """

485

486 def coerce_value(self, value):

487 """ """

488 if value:

489 try:

490 return int(value)

491 except:

492 pass

493

494

495class BooleanAlchemyFilter(AlchemyFilter):

496 """

497 SQLAlchemy filter option for a boolean data column.

498

499 Subclass of :class:`AlchemyFilter`.

500 """

501 default_verbs = ['is_true', 'is_false']

502

503 def get_verbs(self):

504 """ """

505

506 # get basic verbs from caller, or default list

507 verbs = getattr(self, 'verbs', self.default_verbs)

508 if callable(verbs):

509 verbs = verbs()

510 verbs = list(verbs)

511

512 # add some more if column is nullable

513 if self.nullable:

514 for verb in ('is_false_null', 'is_null', 'is_not_null'):

515 if verb not in verbs:

516 verbs.append(verb)

517

518 # add wildcard

519 if 'is_any' not in verbs:

520 verbs.append('is_any')

521

522 return verbs

523

524 def coerce_value(self, value):

525 """ """

526 if value is not None:

527 return bool(value)

528

529 def filter_is_true(self, query, value):

530 """

531 Filter data with an "is true" condition. The value is

532 ignored.

533 """

534 return query.filter(self.model_property == True)

535

536 def filter_is_false(self, query, value):

537 """

538 Filter data with an "is false" condition. The value is

539 ignored.

540 """

541 return query.filter(self.model_property == False)

542

543 def filter_is_false_null(self, query, value):

544 """

545 Filter data with "is false or null" condition. The value is

546 ignored.

547 """

548 return query.filter(sa.or_(self.model_property == False,

549 self.model_property == None))

550

551

552class DateAlchemyFilter(AlchemyFilter):

553 """

554 SQLAlchemy filter option for a

555 :class:`sqlalchemy:sqlalchemy.types.Date` column.

556

557 Subclass of :class:`AlchemyFilter`.

558 """

559 data_type = 'date'

560 default_verbs = [

561 'equal',

562 'not_equal',

563 'greater_than',

564 'greater_equal',

565 'less_than',

566 'less_equal',

567 # 'between',

568 ]

569

570 default_verb_labels = {

571 'equal': "on",

572 'not_equal': "not on",

573 'greater_than': "after",

574 'greater_equal': "on or after",

575 'less_than': "before",

576 'less_equal': "on or before",

577 # 'between': "between",

578 }

579

580 def coerce_value(self, value):

581 """ """

582 if value:

583 if isinstance(value, datetime.date):

584 return value

585

586 try:

587 dt = datetime.datetime.strptime(value, '%Y-%m-%d')

588 except ValueError:

589 log.warning("invalid date value: %s", value)

590 else:

591 return dt.date()

592

593

594default_sqlalchemy_filters = {

595 None: AlchemyFilter,

596 sa.String: StringAlchemyFilter,

597 sa.Text: StringAlchemyFilter,

598 sa.Numeric: NumericAlchemyFilter,

599 sa.Integer: IntegerAlchemyFilter,

600 sa.Boolean: BooleanAlchemyFilter,

601 sa.Date: DateAlchemyFilter,

602}