Coverage for gws-app/gws/base/database/util.py: 100%

1"""Database utilities.""" 

2 

3from typing import Optional 

4 

5import gws 

6import gws.lib.sa as sa 

7 

8 

9def text_search_clause(column, value: Optional[str], tso: Optional[gws.TextSearchOptions]) -> Optional[sa.ColumnElement]: 

10 """Create a where clause that matches a column against a search string. 

11 

12 The value is stripped. For the ``any``, ``begin`` and ``end`` search types, 

13 ``LIKE`` wildcards in the value match literally. For the ``like`` type, the value 

14 is used as a ``LIKE`` pattern, with ``\\`` as the escape character. 

15 

16 Args: 

17 column: Column expression to match. 

18 value: Search string. 

19 tso: Text search options. Without options, the value is matched exactly. 

20 

21 Returns: 

22 A clause, or ``None`` if the value is empty or shorter than the minimum length. 

23 """ 

24 

25 if value is None: 

26 return 

27 

28 value = str(value).strip() 

29 if not value: 

30 return 

31 

32 if not tso: 

33 return column == value 

34 

35 if tso.minLength and len(value) < tso.minLength: 

36 return 

37 

38 if tso.type == gws.TextSearchType.exact: 

39 return column == value 

40 

41 cs = tso.caseSensitive 

42 

43 if tso.type == gws.TextSearchType.any: 

44 return column.contains(value, autoescape=True) if cs else column.icontains(value, autoescape=True) 

45 if tso.type == gws.TextSearchType.begin: 

46 return column.startswith(value, autoescape=True) if cs else column.istartswith(value, autoescape=True) 

47 if tso.type == gws.TextSearchType.end: 

48 return column.endswith(value, autoescape=True) if cs else column.iendswith(value, autoescape=True) 

49 

50 return column.like(value, escape='\\') if cs else column.ilike(value, escape='\\') 

51 

52 

53def shape_intersects_clause(column, shape: gws.Shape, crs: gws.Crs) -> sa.ColumnElement: 

54 """Create a where clause that matches geometries intersecting a shape. 

55 

56 Args: 

57 column: Geometry column expression. 

58 shape: Shape to intersect with. 

59 crs: CRS of the column. The shape is transformed to it. 

60 

61 Returns: 

62 An ``ST_Intersects`` clause. 

63 """ 

64 

65 ewkb = shape.transformed_to(crs).to_ewkb_hex() 

66 return sa.func.st_intersects(column, sa.cast(ewkb, sa.geo.Geometry()))