spacr.qt.widgets.column_picker

SQL column picker — the “SQL” button that sits beside every column field.

Anywhere spaCR asks for the name of a database column — the annotation column, the measurement used to prefilter crops, the heatmap feature, the regression’s dependent variable — the field has historically been a bare text box. Typing into it is blind: nothing tells you what the database already contains, so annotate and annotaet are equally acceptable and equally silent. The second one starts a brand new annotation pass, and two passes that should have been one then look like two annotators who agree on nothing.

This module adds the missing half of that interaction:

ColumnPickerButton

a small SQL button that can be dropped beside an existing field.

ColumnPickerDialog

the panel it opens — the tables in the database, the columns of the selected table with their declared type, and a name box that says, in words, what will happen to the name you typed: used, created, or refused.

attach_column_picker

a one-liner that wires the two onto a QLineEdit/QComboBox a screen has already laid out. It re-uses the field’s own slot in its parent layout, so a host adopts the picker without restructuring anything.

Three properties this file is built around:

• Read-only. Opening the picker reads column names and nothing else. The connection is file:…?mode=ro plus PRAGMA query_only = ON — the same pair spacr.qt.screens.db_browser and spacr.agreement use — so SQLite itself refuses a write. The dialog never creates a column: it returns a name, and the write path that owns creation (annotate_engine.ensure_annotation_column, which does it lazily with ALTER TABLE) stays where it is.

• Cheap on open. PRAGMA table_info is free; SELECT COUNT(*) over a 400 k-row measurement table is not. Opening the dialog runs no count at all. The per-table row figure comes from max(_rowid_) and is labelled an estimate; a per-column non-null count happens only when the user asks for it by name. (_rowid_, not rowid — see SchemaReader.estimate_rows(), where the difference was a full table scan followed by a ValueError.)

• Off the GUI thread. Cheap is not free, and opening the picker is four sequential sqlite round trips — open, list tables, read one table’s columns, estimate its rows — every one of which used to happen inside __init__, before the modal appeared. Measured cold on a 383 MB measurements.db that is 45 ms, and on a 1 500-table schema 87 ms, entirely between the click and any window. The button now builds the dialog with threaded=True and the reads arrive from a JobRunner. The default is still the synchronous mode, deliberately; ColumnPickerDialog says why.

• No modal errors. A missing database, a file that isn’t SQLite, a database with no tables, a name SQLite would refuse — every one of them is reported in a banner inside the dialog. The dialog itself is the one deliberate modal, and it is injectable (ColumnPickerButton. set_dialog_runner()) so a headless test drives it without ever entering an event loop.

Classes

ColumnPickerButton	The small SQL button that opens a ColumnPickerDialog.
ColumnPickerDialog	Browse a database's columns and settle on one name.
SchemaReader	Read-only access to a database's schema — names and types only.

Functions

attach_column_picker(→ ColumnPickerButton)	Put a SQL button beside field and wire it to the picker.
chip_editor(→ Optional[PySide6.QtWidgets.QWidget])	Return field when it is a chip-strip list editor, else None.
field_is_list(→ bool)	Whether field holds a list of names rather than a single one.
field_text(→ str)	Return the text of a line edit / combo box / chip strip.
field_values(→ List[str])	Return the column names field currently holds, in order.
find_existing(→ str)	Return the column matching name, or "".
near_miss(→ str)	Return the existing column name most resembles, or "".
open_reader(→ Tuple[Optional[SchemaReader], str])	Return (reader, message) — exactly one of the two is set.
quote_ident(→ str)	Double-quote a SQL identifier, escaping embedded quotes.
read_schema(→ Dict[str, Any])	Everything opening the picker needs, in one worker-thread call.
read_table(→ Dict[str, Any])	Read one table's columns and row estimate. Touches no widget.
resolve_db_path(→ str)	Turn whatever a host screen holds into a path to a database file.
set_field_text(→ bool)	Write name into field. Returns False when it could not.
set_field_values(→ bool)	Write every name in names into field. False when it could not.
validate_column_name(→ str)	Return why name cannot be a new column, or "" if it can.

Module Contents

class spacr.qt.widgets.column_picker.ColumnPickerButton(db_path_getter: Any = '', table: str | None = None, field: PySide6.QtWidgets.QWidget | None = None, parent: PySide6.QtWidgets.QWidget | None = None, text: str = 'SQL', allow_new: bool = True, multi: bool | None = None)

Bases: PySide6.QtWidgets.QToolButton

The small SQL button that opens a ColumnPickerDialog.

Parameters:

• db_path_getter – callable returning the current database path or run folder — a callable, not a string, because the path usually depends on a src field the user is still editing. A plain string is accepted and wrapped.

• table – table to preselect in the dialog.

• field – the QLineEdit/QComboBox/chip-strip editor a picked name is written into; may be None for a button that only emits picked.

• multi – this field holds a list of columns — names are appended rather than replacing what is there, and the dialog lets the user select any number of columns in one visit instead of one per press. None auto-detects (field_is_list()).

• parent – parent widget; ownership only.

• text – the button’s face. SQL everywhere today; a parameter because the button is small enough that its label is the only thing distinguishing two of them side by side.

• allow_new – let the dialog accept a name that is not in the table. False restricts the user to columns that exist, which is right for a field that must match the schema and wrong for one naming a column a later step will create.

Build a button that opens a column picker for a database.

Parameters:

• db_path_getter – the database path, or a callable returning one – the path is usually a setting the user is still editing, so it is resolved at click time rather than at construction.

• table – table to preselect in the picker.

• field – the widget the chosen column is written back into.

• parent – parent widget, or None.

• text – the button’s label.

• allow_new – let the picker name a column that does not exist yet.

• multi – pick several columns; None leaves the choice to the picker’s own default.

current_text() → str

Return the host field’s current text ("" when unbound).

db_path() → str

Return the path the getter currently reports ("" on error).

is_multi() → bool

Whether this field holds a list of columns rather than one.

Explicit multi wins. Otherwise the field decides: a chip-strip editor is list-valued whether or not it currently holds anything, which a text inspection cannot tell (an empty one reads "", and an empty list field is exactly when the user most wants to add several at once). Text fields keep the old shape test.

make_dialog() → ColumnPickerDialog

Build the dialog, wired to the current path — but do not run it.

threaded=True: this is the user-facing path, and the dialog it builds is run modally by open_picker(), so every millisecond the constructor spends in sqlite is a millisecond between the click and any window at all. See ColumnPickerDialog for the two modes and why the default is the other one.

open_picker() → str

Open the picker; on OK, write every chosen name into the field.

Returns:

the first chosen column name, or "" when cancelled. picked_many carries the whole selection.

set_dialog_runner(runner: Callable[[ColumnPickerDialog], int]) → None

Replace how the dialog is run.

The default enters a modal event loop. Tests inject a runner that inspects the dialog and returns QDialog.Accepted/Rejected without ever blocking — which is also how a host could swap in a non-modal presentation later.

Parameters:

runner – callable that takes the ColumnPickerDialog, presents it and returns its result code (QDialog.Accepted or QDialog.Rejected).

class spacr.qt.widgets.column_picker.ColumnPickerDialog(db_path: Any = '', table: str | None = None, current: str = '', parent: PySide6.QtWidgets.QWidget | None = None, allow_new: bool = True, reader: SchemaReader | None = None, threaded: bool = False, multi: bool = False)

Bases: PySide6.QtWidgets.QDialog

Browse a database’s columns and settle on one name.

The dialog answers one question — “what should this field say?” — and is explicit about the consequence of the answer: an existing column will be used, a new one will be created by whoever owns the write path. It creates nothing itself.

Two modes, and the default is the synchronous one.

threaded=False (the default) reads the schema inside __init__: when the constructor returns, the tables are listed, a table is selected, its columns are in the tree and the name box has been judged. That is what every programmatic caller and the ~60 tests in tests/qt/test_column_picker.py are written against — they construct a dialog and assert on its contents on the next line, with no event loop anywhere (the suite’s autouse fixture makes QDialog.exec raise). Defaulting to the asynchronous mode would turn every one of those into a race, so it is opt-in.

threaded=True is what the SQL button uses (ColumnPickerButton.make_dialog()), and it is the real user-facing path. The window appears immediately saying it is reading the schema, and the tables, columns and row estimate arrive from a JobRunner. That matters because opening the picker is four sequential sqlite round trips against a file that is usually cold and often on a network mount — measured at 45 ms on a 383 MB measurements.db with nothing cached and 87 ms on a 1 500-table schema, all of it dead time between the click and the window.

Both modes perform the same read_schema() calls and preserve the same result order. The synchronous runner executes its job inline.

Parameters:

• db_path – database file or run folder; may be empty.

• table – table to preselect (e.g. png_list for annotations).

• current – the field’s current value, prefilled into the name box.

• allow_new – when False, only existing columns are accepted.

• reader – an already-built SchemaReader (or a stand-in); injecting one is how tests exercise schema edge cases. Its reads are still threaded when threaded is set.

• threaded – read the schema on a worker thread. See above.

• multi – let the user select several columns at once and return every one of them (chosen_columns()). For fields that hold a list of columns — exclude, annotation_columns — where one name per press meant reopening the dialog, and re-reading the schema, once per column the user wanted.

• parent – parent widget; ownership only.

Build the dialog and read the database’s schema into it.

Parameters:

• db_path – database or run folder to read.

• table – table to select on opening, if it exists.

• current – column name to start with in the name box.

• parent – parent widget, or None.

• allow_new – let the user name a column that does not exist yet.

• reader – an already-open SchemaReader to use instead of opening db_path.

• threaded – read the schema on a worker thread. Left False, the read runs inline and the dialog is fully populated by the time the constructor returns, which is the default mode’s contract.

• multi – pick several columns rather than one; the tree’s selection becomes the answer instead of the name box.

accept() → None

Take the chosen columns and close.

action() → str

Return what OK would do: use/create/confirm/ invalid/unchecked.

active_jobs() → int

How many reader threads are still winding down.

banner_text() → str

Return the inline problem banner ("" when there is none).

chosen_column() → str

Return the name currently in the box, trimmed.

chosen_columns() → List[str]

Every column OK would hand back, in order and without repeats.

Always non-empty when chosen_column() is — a single-select dialog is the one-element case of this, so a caller can use this alone. In multi mode the highlighted rows are the answer, with the typed name added when it is not one of them (that is how a column that does not exist yet is named).

chosen_table() → str

Return the selected table name, or "".

column_names() → List[str]

Return the columns of the selected table.

confirm_box() → spacr.qt.widgets.toggle.Toggle

Return the “create it anyway” checkbox (visible only on a near-miss).

confirm_offered() → bool

Return whether the “create it anyway” box is being offered.

done(result: int) → None

Close, and let no reader thread outlive the dialog.

done rather than closeEvent because it is the one funnel: accept, reject and the window’s close button all arrive here, and a dialog dismissed halfway through its first read is the ordinary case — the user clicked SQL on the wrong field. JobRunner.shutdown drops the results and waits briefly so nothing destroys a running QThread.

Parameters:

result – the dialog result code, passed unchanged to QDialog.done.

executed_sql() → List[str]

Return every statement the dialog’s reader has run.

The assertion hook for “what did opening this cost”. Threaded, the statements are appended on a worker thread, so the snapshot is taken under the reader’s lock — see SchemaReader.executed_sql(). It is still only complete once the load is (is_busy() says when).

is_accept_enabled() → bool

Return whether OK is currently clickable.

is_busy() → bool

True while a schema or table read has not been delivered.

is_multi() → bool

Whether this dialog returns several columns.

name_edit() → PySide6.QtWidgets.QLineEdit

Return the name box, for hosts that want to prefill or focus it.

near_miss_column() → str

Return the existing column the typed name resembles, or "".

select_column(name: str) → bool

Select name in the column list (and fill the name box).

Parameters:

name – column name, converted with str() and matched exactly against the first column of the list.

select_columns(names: Sequence[str]) → List[str]

Highlight several columns at once. Returns the ones that existed.

Parameters:

names – column names to select, converted with str() and matched exactly; the previous selection is cleared first.

select_table(name: str) → bool

Select name in the table list. Returns False if absent.

Parameters:

name – table name, converted with str() and matched exactly against the table list.

set_name(text: str) → None

Type text into the name box (as if the user had).

Parameters:

text – text for the name box, converted with str().

status_text() → str

Return the sentence under the name box.

table_names() → List[str]

Return the tables listed in the dialog.

visible_column_names() → List[str]

Return the columns not hidden by the filter box.

class spacr.qt.widgets.column_picker.SchemaReader(path: str)

Read-only access to a database’s schema — names and types only.

Every method opens and closes its own connection, which keeps the object cheap to hold and impossible to leave a transaction open on. mode=ro makes SQLite refuse writes; PRAGMA query_only refuses them a second time, including schema changes smuggled in through a temp attachment.

Every method may be called from a worker thread — the dialog reads its schema off the GUI thread — and each one opens its own connection, so nothing is shared across threads except executed, which is guarded.

Parameters:

path – database file or run folder (see resolve_db_path()).

Variables:

executed – every statement this reader has run, in order — the hook a test uses to prove that opening the picker costs no COUNT(*).

Open a read-only schema reader over a measurements database.

Parameters:

path – database or run folder; resolved through spaCR’s project layout.

column_info(table: str) → List[Tuple[str, str]]

Return [(column name, declared type), …] for table.

PRAGMA table_info reads the stored schema text; it never touches a data page, so this stays free on a huge table.

Parameters:

table – name of the table or view; it is quoted as an identifier.

Raises:

sqlite3.Error – for a view whose base table has been dropped — SQLite resolves the view here and says so.

count_non_null(table: str, column: str) → int

Return how many rows have a value in column.

This one is a real scan — it exists only behind an explicit button, never on the path that opens the dialog.

Parameters:

• table – name of the table or view to scan; quoted as an identifier.

• column – name of the column whose non-NULL values are counted; quoted as an identifier.

estimate_rows(table: str) → int | None

Return an O(1) estimate of the row count, or None.

max(_rowid_) is answered from the right-hand edge of the b-tree without a scan. Deleted rows leave gaps, so it is an estimate and every caller must label it one. None for a view, a WITHOUT ROWID table, or an empty table.

It is spelt _rowid_, and that is load-bearing. Every spaCR object table declares a column called rowID — the plate row, 'r1', 'r2' — and SQLite identifiers are case-insensitive, so a bare rowid resolves to that column rather than to the row id. The version this replaces asked for max(rowid) and got two things wrong at once on every real measurements database: it scanned the whole table to take the maximum of a text column (measured: 21 ms warm, 41 ms cold, on a 200 000-row table — precisely the COUNT(*) cost this method exists to avoid), and then int('r16') raised ValueError, which is not a sqlite3.Error and so escaped the caller’s handler into the Qt event loop. spacr.predictions, spacr.foreign and spacr.data_manager all carry a comment about this shadowing; this method had not got the memo.

The remaining spellings are tried in turn for the pathological table that declares _rowid_ as well, and a value that will not convert is treated as a shadowed column rather than as an answer.

Parameters:

table – name of the table or view; it is quoted as an identifier.

executed_sql() → List[str]

Return a consistent snapshot of executed.

probe() → None

Open the file once so “that isn’t a database” surfaces early.

Raises:

sqlite3.Error – when the file is not a SQLite database or cannot be opened.

tables() → List[str]

Return the user tables and views, alphabetically.

spacr.qt.widgets.column_picker.attach_column_picker(field: PySide6.QtWidgets.QWidget, db_path_getter: Any, table: str | None = None, *, text: str = 'SQL', allow_new: bool = True, multi: bool | None = None, tooltip: str | None = None, layout: PySide6.QtWidgets.QLayout | None = None, on_pick: Callable[[str], None] | None = None) → ColumnPickerButton

Put a SQL button beside field and wire it to the picker.

Designed to be a single line a host screen adds after it has already laid the field out:

attach_column_picker(self._ann_col,
                     lambda: self._src_edit.text(), "png_list")

The field keeps its place in its parent layout: the button and the field are wrapped in a small horizontal box that takes over the field’s original slot, so a QFormLayout row keeps its label and a caller that stored a reference to the field keeps using it unchanged. When the field is not in a layout yet, the button is returned unplaced, and the caller positions it.

Parameters:

• field – the QLineEdit/QComboBox the user types into.

• db_path_getter – callable (or string) giving the database path or run folder. Called each time the button is pressed, so a path the user edits later is picked up.

• table – table to preselect — "png_list" for annotation columns, "cell"/"object" for measurements.

• text – button label.

• allow_new – allow naming a column that does not exist yet.

• multi – append rather than replace (None auto-detects a list-valued field).

• tooltip – override the button’s tooltip.

• layout – the layout holding field, for the case where the host builds a QFormLayout before installing it on a widget (so field.parentWidget() is still None). Normally unnecessary — the field’s own parent is searched.

• on_pick – extra callback invoked with the chosen name.

Returns:

the created ColumnPickerButton.

spacr.qt.widgets.column_picker.chip_editor(field: PySide6.QtWidgets.QWidget | None) → PySide6.QtWidgets.QWidget | None

Return field when it is a chip-strip list editor, else None.

Duck-typed on purpose: the editor lives in spacr.qt.screens.settings_model and importing a screen from a widget would invert the dependency (and, in practice, cycle). The test is “not a text field, but speaks get_value/set_value” — the settings screen’s _ScalarEdit and _ListEdit speak those too, but they are QLineEdit subclasses and are caught by the branch above.

Parameters:

field – the widget to test, or None; line edits and combo boxes always yield None.

spacr.qt.widgets.column_picker.field_is_list(field: PySide6.QtWidgets.QWidget | None) → bool

Whether field holds a list of names rather than a single one.

Parameters:

field – the settings field: a QLineEdit, a QComboBox, a chip-strip list editor, or None.

spacr.qt.widgets.column_picker.field_text(field: PySide6.QtWidgets.QWidget | None) → str

Return the text of a line edit / combo box / chip strip.

Parameters:

field – the settings field: a QLineEdit, a QComboBox, a chip-strip list editor, or None. Any other widget reads as an empty string.

spacr.qt.widgets.column_picker.field_values(field: PySide6.QtWidgets.QWidget | None) → List[str]

Return the column names field currently holds, in order.

Parameters:

field – the settings field: a QLineEdit, a QComboBox, a chip-strip list editor, or None. Text is split on commas, and a bracketed [...] list has its quotes stripped.

spacr.qt.widgets.column_picker.find_existing(name: str, columns: Sequence[str]) → str

Return the column matching name, or "".

Case-insensitively: SQLite resolves identifiers without regard to case, so Annotate is annotate and adding it would fail with “duplicate column name” rather than create a second column.

spacr.qt.widgets.column_picker.near_miss(name: str, columns: Sequence[str], cutoff: float = NEAR_MISS_CUTOFF) → str

Return the existing column name most resembles, or "".

A new name one keystroke away from an existing one is almost never a new column; it is the old one, misspelt. Uses difflib.get_close_matches() with the same cutoff spacr.cli applies to mistyped setting names.

Parameters:

• name – the candidate new column.

• columns – the columns the table already has.

Returns:

the closest existing column, or "" when name is either already a column or genuinely unlike all of them.

spacr.qt.widgets.column_picker.open_reader(path: Any) → Tuple[SchemaReader | None, str]

Return (reader, message) — exactly one of the two is set.

Turning every failure into a sentence here is what lets the dialog render problems inline instead of raising into a modal.

spacr.qt.widgets.column_picker.quote_ident(name: str) → str

Double-quote a SQL identifier, escaping embedded quotes.

Only ever called with a name taken from the live schema; the quoting keeps legal-but-awkward names (cell_channel_1 (raw)) working.

spacr.qt.widgets.column_picker.read_schema(db_path: Any, reader: SchemaReader | None = None, preferred: str = '') → Dict[str, Any]

Everything opening the picker needs, in one worker-thread call.

Opens the database, lists its tables, picks the one to show and reads that table — the four synchronous sqlite round trips that used to sit between the click on SQL and the dialog appearing.

Parameters:

• db_path – file, run folder, or measurements folder.

• reader – use this reader instead of opening db_path.

• preferred – table to select if the database has it.

Returns:

{"reader", "error", "tables", "schema_error", "table"} plus the keys read_table() returns for the chosen table.

spacr.qt.widgets.column_picker.read_table(reader: SchemaReader | None, table: str) → Dict[str, Any]

Read one table’s columns and row estimate. Touches no widget.

Returns plain data so the same result can come back from a worker thread or from an inline call, and the painting code does not have to know which it was.

Parameters:

• reader – open schema reader for the database; None returns the empty payload.

• table – table name to read; None or an empty name returns the empty payload.

Returns:

{"table", "columns", "rows", "error"}. error is the sentence to put in the banner; an empty columns list with no error means the table honestly has none.

spacr.qt.widgets.column_picker.resolve_db_path(path: str) → str

Turn whatever a host screen holds into a path to a database file.

Hosts variously hold src (a run folder), src/measurements or the database itself, so all three resolve here rather than in each caller.

Parameters:

path – file, run folder, or measurements folder.

Returns:

absolute path to a .db file (existence not guaranteed — a file path is returned as given so the caller can report it).

Raises:

ValueError – when path is empty.

spacr.qt.widgets.column_picker.set_field_text(field: PySide6.QtWidgets.QWidget | None, name: str, append: bool = False) → bool

Write name into field. Returns False when it could not.

Parameters:

• field – QLineEdit (including the settings screen’s _ScalarEdit/_ListEdit subclasses) or QComboBox.

• name – the column name to write.

• append – add to the existing value instead of replacing it, keeping the field’s own list style (['a', 'b'] or a, b).

spacr.qt.widgets.column_picker.set_field_values(field: PySide6.QtWidgets.QWidget | None, names: Sequence[str], append: bool = False) → bool

Write every name in names into field. False when it could not.

The multi-column half of set_field_text(). A chip strip is set from a real list — no punctuation round trip — and anything else keeps its own list style through _appended().

Parameters:

• field – the settings field: a QLineEdit, a QComboBox, a chip-strip list editor, or None. None returns False.

• names – column names to write; blank entries are dropped, and an empty result returns False.

spacr.qt.widgets.column_picker.validate_column_name(name: str) → str

Return why name cannot be a new column, or "" if it can.

The point is to fail here, with a sentence, rather than three screens later inside an ALTER TABLE the user never sees.

Parameters:

name – candidate column name as typed.

Returns:

an explanation, or the empty string when the name is fine.