Coverage for downtify/db/dialect.py: 98%

51 statements  

« prev     ^ index     » next       coverage.py v7.17.4, created at 2026-11-21 22:18 +0000

1"""Small helpers that hide SQLite and PostgreSQL differences. 

2 

3Each helper takes a ``Connection`` (which carries its ``dialect``) or a 

4``Dialect`` directly. SQL placeholders are ``?`` on both backends, as 

5elsewhere in ``downtify.db``. 

6""" 

7 

8from __future__ import annotations 

9 

10import re 

11from typing import TYPE_CHECKING 

12 

13from downtify.db.config import Dialect 

14 

15if TYPE_CHECKING: 

16 from downtify.db.core import Connection 

17 

18_IDENT_RE = re.compile(r'[A-Za-z_][A-Za-z0-9_]*') 

19_TRAILING_RE = re.compile(r'[\s;]+$') 

20 

21 

22def _check_identifier(name: str) -> str: 

23 """Return ``name`` if it is a plain SQL identifier, else raise.""" 

24 if not _IDENT_RE.fullmatch(name): 

25 raise ValueError(f'invalid SQL identifier: {name!r}') 

26 return name 

27 

28 

29def _check_column(col: str) -> str: 

30 """Return ``col`` if it is ``name`` or ``table.name``, else raise.""" 

31 parts = col.split('.') 

32 if len(parts) > 2: 

33 raise ValueError(f'invalid SQL column reference: {col!r}') 

34 for part in parts: 

35 _check_identifier(part) 

36 return col 

37 

38 

39def insert_returning_id( 

40 conn: Connection, 

41 sql: str, 

42 params: tuple | list = (), 

43 id_column: str = 'id', 

44) -> int: 

45 """Run an INSERT and return the new row's integer id. 

46 

47 ``sql`` must be a plain INSERT without a RETURNING clause. 

48 """ 

49 _check_identifier(id_column) 

50 if conn.dialect is Dialect.SQLITE: 

51 cursor = conn.execute(sql, params) 

52 new_id = cursor.lastrowid 

53 if new_id is None: 

54 raise RuntimeError('INSERT did not report a rowid') 

55 return int(new_id) 

56 

57 statement = f'{_TRAILING_RE.sub("", sql)} RETURNING {id_column}' 

58 row = conn.execute(statement, params).fetchone() 

59 return int(row[0]) 

60 

61 

62def table_exists(conn: Connection, name: str) -> bool: 

63 """Return True if a table called ``name`` exists in the current schema.""" 

64 if conn.dialect is Dialect.SQLITE: 

65 row = conn.execute( 

66 "select 1 from sqlite_master where type='table' and name=?", 

67 (name,), 

68 ).fetchone() 

69 else: 

70 row = conn.execute( 

71 'select 1 from information_schema.tables ' 

72 'where table_schema = current_schema() and table_name = ?', 

73 (name,), 

74 ).fetchone() 

75 return row is not None 

76 

77 

78def column_names(conn: Connection, table: str) -> list[str]: 

79 """Return the column names of ``table`` in declaration order.""" 

80 _check_identifier(table) 

81 if conn.dialect is Dialect.SQLITE: 

82 # PRAGMA cannot take bound parameters; the name is validated above. 

83 rows = conn.execute(f'PRAGMA table_info({table})').fetchall() 

84 return [row[1] for row in rows] 

85 rows = conn.execute( 

86 'select column_name from information_schema.columns ' 

87 'where table_schema = current_schema() and table_name = ? ' 

88 'order by ordinal_position', 

89 (table,), 

90 ).fetchall() 

91 return [row[0] for row in rows] 

92 

93 

94def insertion_order_col(dialect: Dialect) -> str: 

95 """Column that orders rows by insertion on each backend.""" 

96 if dialect is Dialect.SQLITE: 

97 return 'rowid' 

98 return 'id' 

99 

100 

101def ci_eq(col: str) -> str: 

102 """Case-insensitive equality: ``lower(col) = lower(?)``.""" 

103 _check_column(col) 

104 return f'lower({col}) = lower(?)' 

105 

106 

107def days_between_sql(a: str, b: str, dialect: Dialect) -> str: 

108 """SQL yielding the fractional days from timestamp ``b`` to ``a``. 

109 

110 ``a`` and ``b`` are SQL expressions producing ISO-8601 text timestamps. 

111 Both are trusted SQL fragments, not user input. 

112 """ 

113 if dialect is Dialect.SQLITE: 

114 return f'(julianday({a}) - julianday({b}))' 

115 return ( 

116 f'((EXTRACT(EPOCH FROM (({a})::timestamptz)) ' 

117 f'- EXTRACT(EPOCH FROM (({b})::timestamptz))) / 86400.0)' 

118 )