nirs4all.pipeline.storage.store_queries module

Reusable SQL query builders for WorkspaceStore.

This module provides parameterised SQL constants and helper functions for building dynamic WHERE clauses. All queries use ? style positional parameters for safe parameterised execution via SQLite.

nirs4all.pipeline.storage.store_queries.build_chain_predictions_query(*, chain_id: str, partition: str | None = None, fold_id: str | None = None) → tuple[str, list[object]][source]

Build a drill-down query for individual predictions of a chain.

Parameters:

  • chain_id – Required chain identifier.

  • partition – Optional partition filter ("train", "val", "test").

  • fold_id – Optional fold identifier filter.

Returns:

(sql, params) ready for conn.execute(sql, params).

nirs4all.pipeline.storage.store_queries.build_chain_summary_count_query(*, run_id: str | list[str] | None = None, pipeline_id: str | list[str] | None = None, chain_id: str | list[str] | None = None, dataset_name: str | list[str] | None = None, model_class: str | list[str] | None = None, metric: str | None = None, task_type: str | None = None) → tuple[str, list[object]][source]

Build a COUNT(*) query over v_chain_summary.

Accepts the same optional filters as build_chain_summary_query(), enabling paginated consumers to fetch a total without re-reading rows.

Returns:

(sql, params) ready for conn.execute(sql, params).

nirs4all.pipeline.storage.store_queries.build_prediction_query(*, dataset_name: str | None = None, model_class: str | None = None, partition: str | None = None, fold_id: str | None = None, branch_id: int | None = None, pipeline_id: str | None = None, run_id: str | None = None, task_type: str | None = None, limit: int | None = None, offset: int = 0) → tuple[str, list[object]][source]

Build a full SELECT query for the predictions table.

Supports joining through pipelines when filtering by run_id.

Parameters:

task_type – Filter by task type. Supports aliases: "regression"/"reg", "classification"/"clf" (matches both binary and multiclass), "binary", "multiclass".

Returns:

(sql, params) ready for conn.execute(sql, params).

nirs4all.pipeline.storage.store_queries.build_top_chains_query(*, metric: str | None = None, n: int = 10, offset: int = 0, score_column: str = 'cv_val_score', ascending: bool = True, run_id: str | list[str] | None = None, pipeline_id: str | list[str] | None = None, dataset_name: str | list[str] | None = None, model_class: str | list[str] | None = None, score_scope: str | None = None) → tuple[str, list[object]][source]

Build a ranking query on v_chain_summary.

Parameters:

  • metric – Optional metric name filter.

  • n – Number of top results to return.

  • offset – Number of ranked rows to skip (pagination).

  • score_column – Column to sort by (e.g. "cv_val_score", "final_test_score").

  • ascending – Sort direction. True for lower-is-better metrics.

  • run_id – Optional run filter (single value or list -> IN).

  • pipeline_id – Optional pipeline filter (single value or list).

  • dataset_name – Optional dataset filter (single value or list).

  • model_class – Optional model class filter (single value or list).

Returns:

(sql, params) ready for conn.execute(sql, params).

Raises:

ValueError – If score_column is not a valid column.

nirs4all.pipeline.storage.store_queries.build_top_predictions_query(*, n: int, metric: str = 'val_score', ascending: bool = True, partition: str = 'val', dataset_name: str | None = None, task_type: str | None = None, group_by: str | None = None) → tuple[str, list[object]][source]

Build a ranking query for top-N predictions.

When group_by is set, returns top n per group using a window function.

Parameters:

  • n – Number of top predictions to return.

  • metric – Column name to rank by. Must be a valid prediction column (validated against _PREDICTION_COLUMNS).

  • ascending – Sort direction.

  • partition – Only consider this partition.

  • dataset_name – Optional dataset filter.

  • task_type – Filter by task type. Supports aliases: "regression"/"reg", "classification"/"clf" (matches both binary and multiclass), "binary", "multiclass".

  • group_by – Optional grouping column. Must be a valid prediction column if provided.

Returns:

(sql, params) ready for conn.execute(sql, params).

Raises:

ValueError – If metric or group_by is not a valid column name.