composeui.core.sqlitemodel

A model keeping the app’s data in a SQLite database of its own.

The app writes its schema and its queries, in SqliteTable classes; the model gives them a connection, runs each block of work as one transaction, and saves and opens the database with the study. It works on a scratch copy in a temporary file: the user’s study only changes when it’s saved.

class composeui.core.sqlitemodel.CSVSqliteTable

Bases: SqliteTable

A CSV table whose schema comes from an attrs row class.

row_type: ClassVar[type[AttrsInstance]]

name: ClassVar[str]

insert: ClassVar[str] = ''

source(*, headers=None)

Build a source with schema-derived types and SQLite row ids.

By default, show every field under its CSV header (or its field name). headers selects, orders and labels the fields to show. Raises KeyError for an unknown field, or ValueError if fields shadow all SQLite row-id names. Use SqliteSource directly for custom queries or explicit primary keys.

Parameters:

headers (Mapping[str, str] | None)

Return type:

SqliteSource

row_parameters(row)

Convert CSV fields through the row class and return SQL parameters.

Parameters:

row (dict[str, Any])

Return type:

Parameters

fill(text, *, batch_size=100000, limit=None, **csv_options)

Import CSV text and yield cumulative row counts.

Pass an open text file (with newline='') or another iterable of lines. The caller owns and closes it. Extra keywords go to csv.DictReader, for example delimiter=';' or fieldnames=['name', 'value']. Exhaust the generator to commit. Use contextlib.closing when stopping early to roll back rows. Nested transactions follow SqliteTable.insert_batches’s rules.

Parameters:

• text (Iterable[str])

• batch_size (int)

• limit (int | None)

• csv_options (Any)

Return type:

Generator[int]

fill_from_file(path, *, encoding='utf-8', sniff=False, sniff_sample_size=8192, batch_size=100000, limit=None, **csv_options)

Import a plain or gzip CSV file and yield cumulative submitted-row counts.

Accept a path string or path-like object, relative or absolute. Detect gzip from the content. Own and close the file; other options follow fill. Exhaust the generator to commit, or use contextlib.closing when stopping early to close the file and roll back. File and decoding errors propagate. Set sniff=True to detect the dialect from at most sniff_sample_size decoded characters (8192 by default). The size must be positive when sniffing and is ignored otherwise. Explicit CSV options override detection. Detection failure raises RefusedError.

Parameters:

• path (str | os.PathLike[str])

• encoding (str)

• sniff (bool)

• sniff_sample_size (int)

• batch_size (int)

• limit (int | None)

• csv_options (Any)

Return type:

Generator[int]

fill_from_url(url, *, encoding='utf-8', timeout=30.0, sniff=False, sniff_sample_size=8192, batch_size=100000, limit=None, **csv_options)

Import CSV from a trusted URL and yield cumulative submitted-row counts.

open_csv_url handles decoding, gzip and stream cleanup. Other options follow fill. Exhaust the generator to commit; use contextlib.closing if iteration can stop early, to close the streams and roll back. Existing rows are retained. sniff=True detects the dialect as in fill_from_file, without seeking. sniff_sample_size sets its limit in decoded characters (8192 by default).

Parameters:

• url (str)

• encoding (str)

• timeout (float)

• sniff (bool)

• sniff_sample_size (int)

• batch_size (int)

• limit (int | None)

• csv_options (Any)

Return type:

Generator[int]

class composeui.core.sqlitemodel.SqliteModel

Bases: Model

A model whose data is a SQLite database, saved and opened with the study.

Declare the tables in the class, runs = table(Runs), for the callbacks to reach (model.runs.add(...)), or add them in __init__ with add_table. A study’s database is created from the tables’ create scripts at schema_version. When a study saved at an older version is opened, migrations bring it up to date.

data_name: ClassVar[str | None] = 'data.sqlite'

the model’s file inside a saved study; None for a model with nothing to save

schema_version: ClassVar[int] = 1

the tables’ version, kept in the database (PRAGMA user_version); raise it with every change to the tables, adding the migration from the previous one

migrations: ClassVar[Mapping[int, Callable[[sqlite3.Connection], None]]] = {}

by version: the function bringing a database at that version to the next one

open_time_limit: ClassVar[float] = 30.0

seconds opening a study’s database may take, checks and migrations included: its schema comes from the file, and a view of it may never finish (e.g. a recursive one)

pragmas: ClassVar[Mapping[str, int | str]] = mappingproxy({'journal_mode': 'WAL', 'foreign_keys': 'ON', 'synchronous': 'OFF', 'recursive_triggers': 'ON'})

set on the connections of every study’s database: SQLite keeps these per connection, not in the file, and New and Open each start a connection of their own

__init__()

Return type:

None

property signals: list[Signal[Unpack[tuple[Any, ...]]]]

Get the model’s signals, its tables’ (SqliteTable.changed) among them.

add_table(table_type)

Add a table, created in the current database too, and return it.

Parameters:

table_type (type[SqliteTableT])

Return type:

SqliteTableT

connection(*, changing=None)

Give the database’s connection for one transaction.

The transaction commits once the block ends, or rolls back if the block raises, so its writes land together or not at all. A block inside another (one table’s method calling another’s) joins the outer transaction. If the block changed rows, changing’s changed is emitted after the commit. For a block in a job’s thread, it is emitted on the main thread (see Job.call_on_main_thread), where the views it refreshes are.

Parameters:

changing (SqliteTable | None)

Return type:

Generator[sqlite3.Connection]

reading()

Give a connection that only reads the database, for a long query.

Unlike connection(), it waits for no other thread’s turn, and holds none: the model’s other queries run meanwhile, e.g. a large table’s cells while a job orders its rows or writes some (SqliteSource). In the WAL journal, it reads the rows committed, during a write too. A database New or Open replace meanwhile, or the model’s close, is deleted once the query is over. Once the model is closed, raises sqlite3.ProgrammingError, as connection() does: e.g. a web download its session’s end cuts short.

The connection is reused, e.g. for the next window of a large table: read each query’s rows to the end in the block. A query left half read keeps the rows as they were when it started, for the next reader too.

Return type:

Generator[sqlite3.Connection]

new()

Start the data of a new study.

Return type:

None

save_data(path)

Write the data into the file path.

Parameters:

path (Path)

Return type:

None

open_data(path)

Open a study’s database, refused unless it is made as a new one is.

The file may come from anyone (e.g. a web upload): its schema is the file’s own, whose views and triggers would run in the app. Until it is checked, its views and triggers may call no function with side effects (trusted_schema), and the whole open is cut after open_time_limit.

Parameters:

path (Path)

Return type:

None

loaded()

React once New or Open has put both the views and the data in place.

MainView.loaded is emitted next, so the views’ callbacks read the data in place.

Return type:

None

close()

Release what the data holds (files, connections); the app is done with it.

Return type:

None

class composeui.core.sqlitemodel.SqliteSource

Bases: object

A TableSource of a query’s rows, which a large table reads a window at a time.

It takes the SqliteTable whose rows the query reads, the query itself, the column of the rows’ ids (e.g. a primary key) and the query’s columns that the table shows. SQLite sorts and filters the rows: the order is a SELECT of the ids, and the cells a SELECT of some ids’ rows, so no more rows than those shown come to Python. The table’s changed reads them again. Only the declared columns sort and filter, by name: SQLite takes no column name as a parameter, the values are. It reads on connections of its own (SqliteModel.reading): the rows committed, never waiting for a write, nor the cells for the order a job reads.

A text compares ignoring its case as a ListSource does, but text typed in ASCII alone compares in SQLite (see _folded). It sorts ignoring the case of ASCII letters alone (NOCASE). Equal cells, and the rows unsorted, keep the order of their ids. A /regex/ filter, or a text not in ASCII, calls Python on each row: index the columns that sort instead.

__init__(table, query, *, id_column, columns)

Parameters:

• table (SqliteTable)

• query (str)

• id_column (str)

• columns (Sequence[SourceColumn])

Return type:

None

property columns: Sequence[SourceColumn]

property changed: Signal

count()

Return type:

int

ordered_ids(sort, search, filters)

Parameters:

• sort (Sort | None)

• search (str)

• filters (Mapping[str, Filter])

Return type:

list[Any]

cells(ids, columns)

Parameters:

• ids (Sequence[Any])

• columns (Sequence[str])

Return type:

dict[Any, list[Any]]

class composeui.core.sqlitemodel.SqliteTable

Bases: object

A table of a SqliteModel (or a few related ones), with the queries the app needs.

create is the SQL script that creates it, run in every new study. Its methods read and write through connection(). changed is emitted once a transaction that changed rows through the table has committed, so the views that show them can refresh.

A constraint that fails in a transaction refuses it: connection() raises a RefusedError. Its message is the one of the first key of refusals found in SQLite’s message: a constraint’s name, or e.g. “UNIQUE constraint failed: room.name”. Else it is SQLite’s message.

create: ClassVar[str] = ''

refusals: ClassVar[Mapping[str, str]] = mappingproxy({})

by a part of SQLite’s message, the user’s message when a constraint fails, as written in the code: the view showing it translates it (see View.refusing)

__init__(model)

Parameters:

model (SqliteModel)

Return type:

None

connection()

Give the database’s connection for one transaction; see SqliteModel.connection.

Raises RefusedError when a constraint fails, the transaction rolled back.

Return type:

Generator[sqlite3.Connection]

reading()

Give a connection reading the database alone; see SqliteModel.reading.

Return type:

Generator[sqlite3.Connection]

fetch(cls, sql, parameters=())

Run the query sql and get its rows as cls instances (an attrs class).

Parameters:

• cls (type[_T])

• sql (str)

• parameters (Parameters)

Return type:

list[_T]

fetch_one(cls, sql, parameters=())

Run the query sql and get its first row as a cls instance; None if none.

Parameters:

• cls (type[_T])

• sql (str)

• parameters (Parameters)

Return type:

_T | None

fetch_column(sql, parameters=())

Run the query sql and get its rows’ first column, e.g. a list of names.

Parameters:

• sql (str)

• parameters (Parameters)

Return type:

list[Any]

insert_batches(sql, rows, *, db=None, batch_size=100000, limit=None)

Insert rows in batches and yield the cumulative number of rows submitted.

Rows are positional or named SQL parameters. Read at most limit rows, or all rows if None. batch_size must be positive; limit must be nonnegative. Counts describe submitted rows, not affected rows (e.g. with INSERT OR IGNORE).

Exhaust the generator to commit, or use contextlib.closing to roll back when stopping early. All batches share one transaction. Inside connection(), they join its transaction instead: the caller must raise to roll that transaction back. If db is supplied, use it without committing, rolling back or closing it. Pass a connection from connection() to keep the table’s change notifications.

Parameters:

• sql (str)

• rows (Iterable[Parameters])

• db (sqlite3.Connection | None)

• batch_size (int)

• limit (int | None)

Return type:

Generator[int]

composeui.core.sqlitemodel.as_parameters(row)

Get an attrs instance’s fields by name, as named parameters of a query.

Parameters:

row (object)

Return type:

dict[str, Any]

composeui.core.sqlitemodel.csv_field(name: str, *, date_format: str, default: Any = attrs.NOTHING, factory: Callable[[], Any] | None = None, converter: Any = None, init: bool = True, alias: str | None = None, kw_only: bool = False, **kwargs: Any) → datetime

composeui.core.sqlitemodel.csv_field(name: str, *, date_format: None = None, default: Any = attrs.NOTHING, factory: Callable[[], Any] | None = None, converter: Any = None, init: bool = True, alias: str | None = None, kw_only: bool = False, **kwargs: Any) → Any

Declare a CSV field, with an optional strptime format for a date or datetime.

Parameters:

• name (str)

• date_format (str | None)

• kwargs (Any)

Return type:

Any

composeui.core.sqlitemodel.define_csv_row(cls)

Define an attrs row class with typed CSV field specifiers.

Use @define_csv_row instead of @attrs.define when fields use csv_field. For mypy, enable the composeui.mypy plugin.

Parameters:

cls (_RowClassT)

Return type:

_RowClassT

composeui.core.sqlitemodel.open_csv_url(url, *, encoding='utf-8', timeout=30.0)

Open a trusted HTTP, HTTPS or file URL as CSV text, and close its streams on exit.

Detect gzip by its content, not its filename. Preserve CSV newlines and decode with encoding. timeout sets the socket timeout in seconds, not a total download deadline. Network, decompression and decoding errors propagate to the caller. Do not pass untrusted URLs: the helper does not restrict hosts or local file access.

Parameters:

• url (str)

• encoding (str)

• timeout (float)

Return type:

Generator[io.TextIOWrapper]

composeui.core.sqlitemodel.table(table_type)

Declare a table of a SqliteModel, in the model’s class.

E.g. runs = table(Runs). Each model builds its own table, as an attribute of the same name, before it creates its database. The tables are built in the order the classes declare them, a base class’s first.

Parameters:

table_type (type[SqliteTableT])

Return type:

SqliteTableT

composeui.core.sqlitemodel.unique_name(db, table, column, base)

Get base, or else “base 2”, “base 3”…, the first no row of table has.

Names differing only by case are the same name (a COLLATE NOCASE column’s), e.g. to add a “Room” whatever rooms there are already.

Parameters:

• db (Connection)

• table (str)

• column (str)

• base (str)

Return type:

str