"""SCHEMA: what a compartment persists in a relational database, declared where it is.
A compartment declares its database schema as a tuple of typed entries - Table
and Index - on the class attribute SCHEMA. Like COMMANDS, WORKERS, and CHANNELS,
it is declared once, validated at class definition time by the base
Compartment, and instantiated into a database adapter module (sqlite, psql, etc.)
when the compartment is initialized.
"""
from __future__ import annotations
import dataclasses
import re
from typing import Any, Optional, Sequence, Union
from .problem import (
DuplicateProblem,
IncompatibleProblem,
InvalidIdentifierProblem,
Problem,
Problems,
ReferenceProblem,
TypeMismatchProblem,
)
[docs]@dataclasses.dataclass(frozen=True)
class Column:
"""One typed column of a database table."""
name: str
type: str
nullable: bool = True
primary_key: bool = False
default: Any = None
unique: bool = False
[docs] def __init__(
self,
name: str,
type: str,
*,
nullable: bool = True,
not_null: bool = False,
primary_key: bool = False,
default: Any = None,
unique: bool = False,
) -> None:
object.__setattr__(self, 'name', str(name))
object.__setattr__(self, 'type', str(type).upper())
is_nullable = False if not_null else nullable
if primary_key:
is_nullable = False
object.__setattr__(self, 'nullable', is_nullable)
object.__setattr__(self, 'primary_key', bool(primary_key))
object.__setattr__(self, 'default', default)
object.__setattr__(self, 'unique', bool(unique))
[docs] def to_sql(self, dialect: str = 'sqlite') -> str:
"""Render column specification in DDL."""
parts = [self.name, self.type]
if self.primary_key:
parts.append('PRIMARY KEY')
elif not self.nullable:
parts.append('NOT NULL')
if self.default is not None:
parts.append(f'DEFAULT {self.default}')
if self.unique:
parts.append('UNIQUE')
return ' '.join(parts)
[docs] def problems(self, table_name: str = '') -> list[str]:
out = Problems()
loc = f' in table {table_name}' if table_name else ''
if not self.name or not isinstance(self.name, str) or not re.match(r'^[A-Za-z_]\w*$', self.name):
out.append(InvalidIdentifierProblem('SCHEMA', self.name, f'column name {self.name!r}{loc} is not a valid identifier'))
if not self.type or not isinstance(self.type, str):
out.append(Problem(f'column {self.name}{loc} must declare a SQL type', surface='SCHEMA', code='missing_type', target=self.name))
return out
[docs]@dataclasses.dataclass(frozen=True)
class Index:
"""An index over one or more columns in a table."""
name: str
columns: tuple[str, ...]
table: str = ''
unique: bool = False
[docs] def __init__(
self,
name: str,
columns: Sequence[str] | str,
*,
table: str = '',
unique: bool = False,
) -> None:
object.__setattr__(self, 'name', str(name))
cols = (columns,) if isinstance(columns, str) else tuple(str(c) for c in columns)
object.__setattr__(self, 'columns', cols)
object.__setattr__(self, 'table', str(table))
object.__setattr__(self, 'unique', bool(unique))
[docs] def bind_table(self, table: str) -> Index:
"""Return a copy of this index bound to a table name if not set."""
if self.table:
return self
return Index(self.name, self.columns, table=table, unique=self.unique)
[docs] def to_sql(self, dialect: str = 'sqlite') -> str:
"""Render CREATE INDEX statement."""
unique_clause = 'UNIQUE ' if self.unique else ''
cols = ', '.join(self.columns)
tbl = self.table or 'unknown_table'
return f'CREATE {unique_clause}INDEX IF NOT EXISTS {self.name} ON {tbl}({cols})'
[docs] def problems(self, table: Optional[Table] = None) -> list[str]:
out = Problems()
if not self.name or not isinstance(self.name, str) or not re.match(r'^[A-Za-z_]\w*$', self.name):
out.append(InvalidIdentifierProblem('SCHEMA', self.name, f'index name {self.name!r} is not a valid identifier'))
tbl = self.table or (table.name if table else '')
if not tbl:
out.append(Problem(f'index {self.name} must specify a table', surface='SCHEMA', code='missing_table', target=self.name))
if not self.columns:
out.append(Problem(f'index {self.name} must index at least one column', surface='SCHEMA', code='empty_index', target=self.name))
if table is not None:
valid_cols = {c.name for c in table.columns if isinstance(c, Column)}
for c in self.columns:
if c not in valid_cols:
out.append(ReferenceProblem('SCHEMA', f'index {self.name}', c, f'index {self.name} indexes unknown column {c!r} on table {table.name}'))
return out
[docs]@dataclasses.dataclass(frozen=True)
class Table:
"""A database table with typed columns, primary key, and optional indices."""
name: str
columns: tuple[Column, ...]
primary_key: tuple[str, ...] = ()
indices: tuple[Index, ...] = ()
without_rowid: bool = False
[docs] def __init__(
self,
name: str,
columns: Sequence[Column],
*,
primary_key: Sequence[str] | str = (),
indices: Sequence[Index] = (),
without_rowid: bool = False,
) -> None:
object.__setattr__(self, 'name', str(name))
object.__setattr__(self, 'columns', tuple(columns))
pk = (primary_key,) if isinstance(primary_key, str) else tuple(str(k) for k in primary_key)
object.__setattr__(self, 'primary_key', pk)
bound_indices = tuple(idx.bind_table(name) if isinstance(idx, Index) else idx for idx in indices)
object.__setattr__(self, 'indices', bound_indices)
object.__setattr__(self, 'without_rowid', bool(without_rowid))
[docs] def column_names(self) -> tuple[str, ...]:
return tuple(c.name for c in self.columns if isinstance(c, Column))
[docs] def pk_names(self) -> tuple[str, ...]:
if self.primary_key:
return self.primary_key
return tuple(c.name for c in self.columns if isinstance(c, Column) and c.primary_key)
[docs] def column(self, name: str) -> Optional[Column]:
for c in self.columns:
if isinstance(c, Column) and c.name == name:
return c
return None
[docs] def to_sql(self, dialect: str = 'sqlite') -> str:
"""Render CREATE TABLE statement."""
col_defs = [c.to_sql(dialect) for c in self.columns if isinstance(c, Column)]
inline_pks = [c.name for c in self.columns if isinstance(c, Column) and c.primary_key]
if self.primary_key and not inline_pks:
col_defs.append(f"PRIMARY KEY ({', '.join(self.primary_key)})")
body = ', '.join(col_defs)
sql = f'CREATE TABLE IF NOT EXISTS {self.name} ({body})'
if self.without_rowid and dialect == 'sqlite':
sql += ' WITHOUT ROWID'
return sql
[docs] def secondary_sql(self, dialect: str = 'sqlite') -> list[str]:
"""Render all attached indices as DDL statements."""
return [idx.to_sql(dialect) for idx in self.indices if isinstance(idx, Index)]
[docs] def problems(self) -> list[str]:
out = Problems()
if not self.name or not isinstance(self.name, str) or not re.match(r'^[A-Za-z_]\w*$', self.name):
out.append(InvalidIdentifierProblem('SCHEMA', self.name, f'table name {self.name!r} is not a valid identifier'))
if not self.columns:
out.append(Problem(f'table {self.name} must declare at least one column', surface='SCHEMA', code='empty_table', target=self.name))
return out
seen_cols: set[str] = set()
for c_any in self.columns:
c_val: Any = c_any
if not isinstance(c_val, Column):
out.append(TypeMismatchProblem('SCHEMA', 'Column', type(c_val).__name__, f'table {self.name} column must be Column, got {type(c_val).__name__}'))
continue
if c_val.name in seen_cols:
out.append(DuplicateProblem('SCHEMA', c_val.name, f'table {self.name} declares column {c_val.name!r} twice'))
seen_cols.add(c_val.name)
out.extend(c_val.problems(self.name))
inline_pks = [c.name for c in self.columns if isinstance(c, Column) and c.primary_key]
if inline_pks and self.primary_key:
out.append(IncompatibleProblem('SCHEMA', self.name, f'table {self.name} declares both inline PRIMARY KEY and table primary_key={self.primary_key}'))
elif self.primary_key:
for pk in self.primary_key:
if pk not in seen_cols:
out.append(ReferenceProblem('SCHEMA', f'table {self.name}', pk, f'table {self.name} primary key references unknown column {pk!r}'))
seen_indices: set[str] = set()
for idx_any in self.indices:
idx_val: Any = idx_any
if not isinstance(idx_val, Index):
out.append(TypeMismatchProblem('SCHEMA', 'Index', type(idx_val).__name__, f'table {self.name} index must be Index, got {type(idx_val).__name__}'))
continue
if idx_val.name in seen_indices:
out.append(DuplicateProblem('SCHEMA', idx_val.name, f'table {self.name} declares index {idx_val.name!r} twice'))
seen_indices.add(idx_val.name)
out.extend(idx_val.problems(self))
return out
[docs]@dataclasses.dataclass(frozen=True)
class Migration:
"""A versioned database migration."""
version: int
up: tuple[str, ...]
down: Optional[tuple[str, ...]] = None
name: str = ''
[docs] def __init__(
self,
version: int,
up: str | Sequence[str],
*,
down: Optional[str | Sequence[str]] = None,
name: str = '',
) -> None:
object.__setattr__(self, 'version', int(version))
up_sql = (up,) if isinstance(up, str) else tuple(str(s) for s in up)
object.__setattr__(self, 'up', up_sql)
down_sql = None
if down is not None:
down_sql = (down,) if isinstance(down, str) else tuple(str(s) for s in down)
object.__setattr__(self, 'down', down_sql)
object.__setattr__(self, 'name', str(name))
[docs] def to_tuple(self) -> tuple[int, str]:
"""Convert to (version, sql) pair for BaseStore compatibility."""
return (self.version, '; '.join(self.up))
[docs] def problems(self) -> list[str]:
out = Problems()
if self.version <= 0:
out.append(Problem(
f'migration version must be a positive integer, got {self.version}',
surface='MIGRATIONS',
code='invalid_version',
target=str(self.version),
))
if not self.up:
out.append(Problem(
f'migration {self.version} must declare up SQL statements',
surface='MIGRATIONS',
code='empty_migration',
target=str(self.version),
))
for stmt in self.up:
if not stmt or not stmt.strip():
out.append(Problem(
f'migration {self.version} has empty SQL statement',
surface='MIGRATIONS',
code='empty_statement',
target=str(self.version),
))
return out
SchemaEntry = Union[Table, Index]
__all__ = ['Column', 'Index', 'Table', 'Migration', 'SchemaEntry']