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
« 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.
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"""
8from __future__ import annotations
10import re
11from typing import TYPE_CHECKING
13from downtify.db.config import Dialect
15if TYPE_CHECKING:
16 from downtify.db.core import Connection
18_IDENT_RE = re.compile(r'[A-Za-z_][A-Za-z0-9_]*')
19_TRAILING_RE = re.compile(r'[\s;]+$')
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
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
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.
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)
57 statement = f'{_TRAILING_RE.sub("", sql)} RETURNING {id_column}'
58 row = conn.execute(statement, params).fetchone()
59 return int(row[0])
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
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]
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'
101def ci_eq(col: str) -> str:
102 """Case-insensitive equality: ``lower(col) = lower(?)``."""
103 _check_column(col)
104 return f'lower({col}) = lower(?)'
107def days_between_sql(a: str, b: str, dialect: Dialect) -> str:
108 """SQL yielding the fractional days from timestamp ``b`` to ``a``.
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 )