from sqlite3 import connect as sql_connect, Cursor, Row
from typing import Any, Self, TypeVar, Generic
from plomtask.exceptions import HandledException, NotFoundException
+from plomtask.dating import valid_date
-EXPECTED_DB_VERSION = 1
+EXPECTED_DB_VERSION = 5
MIGRATIONS_DIR = 'migrations'
FILENAME_DB_SCHEMA = f'init_{EXPECTED_DB_VERSION}.sql'
PATH_DB_SCHEMA = f'{MIGRATIONS_DIR}/{FILENAME_DB_SCHEMA}'
"""To identify case of unmigrated DB file."""
-class DatabaseFile: # pylint: disable=too-few-public-methods
+class DatabaseFile:
"""Represents the sqlite3 database's file."""
+ # pylint: disable=too-few-public-methods
def __init__(self, path: str) -> None:
self.path = path
def migrate(cls, path: str) -> DatabaseFile:
"""Apply migrations from_version to EXPECTED_DB_VERSION."""
migrations = cls._available_migrations()
- from_version = cls.get_version_of_db(path)
+ from_version = cls._get_version_of_db(path)
migrations_todo = migrations[from_version+1:]
for j, filename in enumerate(migrations_todo):
with sql_connect(path) as conn:
"""Check file exists, and is of proper DB version and schema."""
if not isfile(self.path):
raise NotFoundException
- if self.user_version != EXPECTED_DB_VERSION:
+ if self._user_version != EXPECTED_DB_VERSION:
raise UnmigratedDbException()
self._validate_schema()
return migrations_list
@staticmethod
- def get_version_of_db(path: str) -> int:
+ def _get_version_of_db(path: str) -> int:
"""Get DB user_version, fail if outside expected range."""
sql_for_db_version = 'PRAGMA user_version'
with sql_connect(path) as conn:
return db_version
@property
- def user_version(self) -> int:
+ def _user_version(self) -> int:
"""Get DB user_version."""
- return self.__class__.get_version_of_db(self.path)
+ # pylint: disable=protected-access
+ # (since we remain within class)
+ return self.__class__._get_version_of_db(self.path)
def _validate_schema(self) -> None:
"""Compare found schema with what's stored at PATH_DB_SCHEMA."""
new_row += [f' {segment}']
new_row[0] = new_row[0].lstrip()
new_row[-1] = new_row[-1].lstrip()
+ if new_row[-1] != ')' and new_row[-3][-1] != ',':
+ new_row[-3] = new_row[-3] + ','
+ new_row[-2:] = [' ' + new_row[-1][:-1]] + [')']
new_rows += ['\n'.join(new_row)]
return new_rows
"""A single connection to the database."""
def __init__(self, db_file: DatabaseFile) -> None:
- self.file = db_file
- self.conn = sql_connect(self.file.path)
+ self.conn = sql_connect(db_file.path)
def commit(self) -> None:
"""Commit SQL transaction."""
"""Add commands to SQL transaction."""
return self.conn.execute(code, inputs)
+ def exec_on_vals(self, code: str, inputs: tuple[Any, ...]) -> Cursor:
+ """Wrapper around .exec appending adequate " (?, …)" to code."""
+ q_marks_from_values = '(' + ','.join(['?'] * len(inputs)) + ')'
+ return self.exec(f'{code} {q_marks_from_values}', inputs)
+
def close(self) -> None:
"""Close DB connection."""
self.conn.close()
def rewrite_relations(self, table_name: str, key: str, target: int | str,
- rows: list[list[Any]]) -> None:
- """Rewrite relations in table_name to target, with rows values."""
+ rows: list[list[Any]], key_index: int = 0) -> None:
+ # pylint: disable=too-many-arguments
+ """Rewrite relations in table_name to target, with rows values.
+
+ Note that single rows are expected without the column and value
+ identified by key and target, which are inserted inside the function
+ at key_index.
+ """
self.delete_where(table_name, key, target)
for row in rows:
- values = tuple([target] + row)
- q_marks = self.__class__.q_marks_from_values(values)
- self.exec(f'INSERT INTO {table_name} VALUES {q_marks}', values)
+ values = tuple(row[:key_index] + [target] + row[key_index:])
+ self.exec_on_vals(f'INSERT INTO {table_name} VALUES', values)
def row_where(self, table_name: str, key: str,
target: int | str) -> list[Row]:
return list(self.exec(f'SELECT * FROM {table_name} WHERE {key} = ?',
(target,)))
+ # def column_where_pattern(self,
+ # table_name: str,
+ # column: str,
+ # pattern: str,
+ # keys: list[str]) -> list[Any]:
+ # """Return column of rows where one of keys matches pattern."""
+ # targets = tuple([f'%{pattern}%'] * len(keys))
+ # haystack = ' OR '.join([f'{k} LIKE ?' for k in keys])
+ # sql = f'SELECT {column} FROM {table_name} WHERE {haystack}'
+ # return [row[0] for row in self.exec(sql, targets)]
+
def column_where(self, table_name: str, column: str, key: str,
target: int | str) -> list[Any]:
"""Return column of table where key == target."""
"""Delete from table where key == target."""
self.exec(f'DELETE FROM {table_name} WHERE {key} = ?', (target,))
- @staticmethod
- def q_marks_from_values(values: tuple[Any]) -> str:
- """Return placeholder to insert values into SQL code."""
- return '(' + ','.join(['?'] * len(values)) + ')'
-
BaseModelId = TypeVar('BaseModelId', int, str)
BaseModelInstance = TypeVar('BaseModelInstance', bound='BaseModel[Any]')
table_name = ''
to_save: list[str] = []
to_save_versioned: list[str] = []
- to_save_relations: list[tuple[str, str, str]] = []
+ to_save_relations: list[tuple[str, str, str, int]] = []
id_: None | BaseModelId
cache_: dict[BaseModelId, Self]
+ to_search: list[str] = []
def __init__(self, id_: BaseModelId | None) -> None:
if isinstance(id_, int) and id_ < 1:
msg = f'illegal {self.__class__.__name__} ID, must be >=1: {id_}'
raise HandledException(msg)
+ if isinstance(id_, str) and "" == id_:
+ msg = f'illegal {self.__class__.__name__} ID, must be non-empty'
+ raise HandledException(msg)
self.id_ = id_
+ def __hash__(self) -> int:
+ hashable = [self.id_] + [getattr(self, name) for name in self.to_save]
+ for definition in self.to_save_relations:
+ attr = getattr(self, definition[2])
+ hashable += [tuple(rel.id_ for rel in attr)]
+ for name in self.to_save_versioned:
+ hashable += [hash(getattr(self, name))]
+ return hash(tuple(hashable))
+
def __eq__(self, other: object) -> bool:
if not isinstance(other, self.__class__):
return False
- to_hash_me = tuple([self.id_] +
- [getattr(self, name) for name in self.to_save])
- to_hash_other = tuple([other.id_] +
- [getattr(other, name) for name in other.to_save])
- return hash(to_hash_me) == hash(to_hash_other)
+ return hash(self) == hash(other)
def __lt__(self, other: Any) -> bool:
if not isinstance(other, self.__class__):
items[item.id_] = item
return list(items.values())
+ @classmethod
+ def by_date_range_with_limits(cls: type[BaseModelInstance],
+ db_conn: DatabaseConnection,
+ date_range: tuple[str, str],
+ date_col: str = 'day'
+ ) -> tuple[list[BaseModelInstance], str,
+ str]:
+ """Return list of items in database within (open) date_range interval.
+
+ If no range values provided, defaults them to 'yesterday' and
+ 'tomorrow'. Knows to properly interpret these and 'today' as value.
+ """
+ start_str = date_range[0] if date_range[0] else 'yesterday'
+ end_str = date_range[1] if date_range[1] else 'tomorrow'
+ start_date = valid_date(start_str)
+ end_date = valid_date(end_str)
+ items = []
+ sql = f'SELECT id FROM {cls.table_name} '
+ sql += f'WHERE {date_col} >= ? AND {date_col} <= ?'
+ for row in db_conn.exec(sql, (start_date, end_date)):
+ items += [cls.by_id(db_conn, row[0])]
+ return items, start_date, end_date
+
+ @classmethod
+ def matching(cls: type[BaseModelInstance], db_conn: DatabaseConnection,
+ pattern: str) -> list[BaseModelInstance]:
+ """Return all objects whose .to_search match pattern."""
+ items = cls.all(db_conn)
+ if pattern:
+ filtered = []
+ for item in items:
+ for attr_name in cls.to_search:
+ toks = attr_name.split('.')
+ parent = item
+ for tok in toks:
+ attr = getattr(parent, tok)
+ parent = attr
+ if pattern in attr:
+ filtered += [item]
+ break
+ return filtered
+ return items
+
def save(self, db_conn: DatabaseConnection) -> None:
"""Write self to DB and cache and ensure .id_.
"""
values = tuple([self.id_] + [getattr(self, key)
for key in self.to_save])
- q_marks = DatabaseConnection.q_marks_from_values(values)
table_name = self.table_name
- cursor = db_conn.exec(f'REPLACE INTO {table_name} VALUES {q_marks}',
- values)
+ cursor = db_conn.exec_on_vals(f'REPLACE INTO {table_name} VALUES',
+ values)
if not isinstance(self.id_, str):
self.id_ = cursor.lastrowid # type: ignore[assignment]
self.cache()
for attr_name in self.to_save_versioned:
getattr(self, attr_name).save(db_conn)
- for table, column, attr_name in self.to_save_relations:
+ for table, column, attr_name, key_index in self.to_save_relations:
assert isinstance(self.id_, (int, str))
db_conn.rewrite_relations(table, column, self.id_,
[[i.id_] for i
- in getattr(self, attr_name)])
+ in getattr(self, attr_name)], key_index)
def remove(self, db_conn: DatabaseConnection) -> None:
"""Remove from DB and cache, including dependencies."""
raise HandledException('cannot remove unsaved item')
for attr_name in self.to_save_versioned:
getattr(self, attr_name).remove(db_conn)
- for table, column, attr_name in self.to_save_relations:
+ for table, column, attr_name, _ in self.to_save_relations:
db_conn.delete_where(table, column, self.id_)
self.uncache()
db_conn.delete_where(self.table_name, 'id', self.id_)
projects / plomtask / blobdiff