ord_schema.orm#

Base ORM objects.

Deprecated: query the derived Parquet artifacts with ord_schema.search instead; see ord_schema/orm/README.md.

ord_schema.orm.database#

Functions for creating/managing the PostgreSQL database.

ord_schema.orm.database.add_dataset(dataset: Dataset, session: Session) → None#

Ingests a dataset, writing the ord.* search index and public.* payload.

Derived data (SMILES, RDKit links) is populated separately by update_derived_data so ingest and derivation can run independently.

Parameters:
  • dataset – Dataset to add.

  • session – SQLAlchemy session.

ord_schema.orm.database.add_parquet_dataset(path: str, session: Session) → None#

Streams a Parquet Dataset into the ORM tables with COPY in one transaction.

Composes add_parquet_dataset_row (search-index row), add_parquet_reactions (the reactions), and add_parquet_dataset_metadata (the public.datasets marker). Sharded ingest calls the same primitives across worker processes; here they run serially so the whole dataset lands atomically. Derived data is populated separately by update_derived_data.

Parameters:
  • path – Path to the Parquet-serialized Dataset.

  • session – SQLAlchemy session.

ord_schema.orm.database.add_parquet_dataset_metadata(dataset_id: str, md5_hex: str, num_reactions: int, session: Session) → None#

Writes the public.datasets metadata row and populates submitted_at.

Written after the reactions are loaded, so the presence of this row marks a fully loaded dataset (get_dataset_md5 reads it); a crashed load leaves an ord.dataset row with no metadata row, which the next ingest deletes and reloads.

Parameters:
  • dataset_id – Dataset ID (public.datasets primary key).

  • md5_hex – Streaming MD5 of the Parquet file.

  • num_reactions – Reaction count from the streaming pass.

  • session – SQLAlchemy session.

ord_schema.orm.database.add_parquet_dataset_row(path: str, dataset_uuid: UUID, session: Session) → None#

Inserts scalars-only ord.dataset search-index row (no reactions/metadata).

The surrogate id is supplied by the caller so a subsequent sharded reaction load can reference it without a round trip. The public.datasets metadata row is written last, by add_parquet_dataset_metadata, so its presence marks a fully loaded dataset.

Parameters:
  • path – Path to the Parquet-serialized Dataset.

  • dataset_uuid – Surrogate primary key for the ord.dataset row.

  • session – SQLAlchemy session.

ord_schema.orm.database.add_parquet_reactions(path: str, dataset_uuid: UUID, session: Session, *, row_group: int | None = None, progress_desc: str | None = None, progress_total: int | None = None) → None#

Streams a Parquet dataset’s Reactions into the ORM tables with COPY.

Builds ORM trees a batch at a time via from_proto; each tree is assigned UUIDv7 primary keys with foreign keys wired from the relationship metadata (_collect_rows), and the rows are streamed with COPY (_copy_rows) rather than the ORM unit of work. Peak memory is bounded to one row group plus _COPY_BATCH trees. The parent ord.dataset row identified by dataset_uuid must already exist (see add_parquet_dataset_row).

Because rows are streamed with COPY rather than the unit of work, SQLAlchemy instrumentation does not run for this path: @validates methods and before_insert/after_insert event hooks on the mapper classes do not fire. add_dataset (used by ord-interface) still goes through the ORM, so any such hook must be reflected here as well to keep the two paths in sync.

Parameters:
  • path – Path to the Parquet-serialized Dataset.

  • dataset_uuid – Surrogate key of the parent ord.dataset row (foreign key target).

  • session – SQLAlchemy session.

  • row_group – If set, only that Parquet row group is loaded; the unit of parallelism for sharded ingest. None loads every reaction in the file.

  • progress_desc – If set, show a tqdm progress bar with this label (the single-process path passes it; shards leave it None so the pool-level bar is the only one).

  • progress_total – Total reaction count for the progress bar’s percentage/ETA.

ord_schema.orm.database.backfill_submission_times(session: Session) → None#

Populates submitted_at for datasets that don’t have it yet.

One-time/maintenance helper for databases loaded before the column existed; new datasets are populated at ingest by add_dataset/add_parquet_dataset.

ord_schema.orm.database.delete_dataset(dataset_id: str, session: Session) → None#

Deletes a dataset from the database.

ord_schema.orm.database.delete_orphaned_rdkit_structures(session: Session) → tuple[int, int]#

Deletes RDKit structures no derived row references, returning the counts removed.

rdkit.mols and rdkit.reactions are deduplicated by structure and shared across datasets, so a rederive cannot touch them: it rewrites the rows that point at them. A rebuild that changes a SMILES therefore links to a new structure and leaves the old one behind, referenced by nothing.

Whole-database by necessity, not by choice. A structure is orphaned only if no dataset references it, so this cannot be scoped to one dataset or one shard.

Run it only when no derivation is in flight. _update_rdkit_mols inserts structures that _link_mol_ids links in a later statement, so a concurrent load has a window where live rows look orphaned; deleting then would strand the rows that were about to reference them.

Parameters:

session – SQLAlchemy session.

Returns:

(mols_deleted, reactions_deleted).

ord_schema.orm.database.get_connection_string(database: str, username: str, password: str, host: str = 'localhost', port: int = 5432) → str#

Creates an SQLAlchemy connection string.

ord_schema.orm.database.get_dataset_md5(dataset_id: str, session: Session) → str | None#

Returns the MD5 hash of a dataset’s current version, or None if not stored.

ord_schema.orm.database.get_dataset_size(dataset_id: str, session: Session) → int#

Returns the number of reactions in a dataset.

ord_schema.orm.database.prepare_database(engine: Engine) → bool#

Prepares the database and creates the ORM table structure.

Parameters:

engine – SQLAlchemy Engine (server must be PostgreSQL 12+).

Returns:

Whether the RDKit PostgreSQL cartridge is installed.

Raises:

RuntimeError – If the server is older than PostgreSQL 12.

ord_schema.orm.database.set_submitted_at(dataset_id: str, session: Session) → None#

Sets dataset.submitted_at from the dataset’s latest reaction record event.

Reactions in a dataset share submission-pipeline timestamps, so one arbitrary reaction is representative (no full scan). Uses its last record_modified entry (record events are appended chronologically), falling back to the required record_created. Leaves NULL when the timestamp is missing or unparseable.

ord_schema.orm.database.update_derived_data(dataset_id: str, session: Session, *, rdkit_cartridge: bool = True, rederive: bool = False) → None#

Populates the derived tables for an already-ingested dataset.

Idempotent (NOT EXISTS guards), so it is safe to re-run to backfill derived data over datasets that are already present. Backfilling is not the same as rebuilding: the guards mean an existing row is never revisited, so use rederive when the derivation itself has changed.

Parameters:
  • dataset_id – Dataset to derive.

  • session – SQLAlchemy session.

  • rdkit_cartridge – Whether to populate RDKit cartridge tables and links.

  • rederive – Whether to delete existing derived rows first so they are recomputed.

ord_schema.orm.database.update_derived_tables(dataset_id: str, session: Session, *, shard: tuple[int, int] | None = None, rederive: bool = False) → None#

Populates the derived SMILES tables from the search index.

Reaction SMILES come from the ground-truth proto in public.reactions; compound SMILES from each ord.compound row’s reconstructed message. Idempotent (skips rows that already have a derived entry); runs before the RDKit pass, which reads the SMILES. Reactions and compounds are processed in batches of _DERIVED_BATCH so the heavy per-row work (proto deserialization, SMILES generation) and pending inserts stay within one batch.

When shard is (index, num_shards), only that disjoint hash-partition of the dataset’s reaction/compound ids is derived, so a large dataset can be split across worker processes (the derived-stage analog of the row-group sharding used for ingest). Idempotency makes the shards safe to run in any order or overlap.

Parameters:
  • dataset_id – Dataset to derive.

  • session – SQLAlchemy session.

  • shard – (index, num_shards) to derive one disjoint hash-partition.

  • rederive – If True, recompute every row rather than only the missing ones, and update the ones whose value changed. Rows are updated in place, never deleted and reinserted: search inner-joins through these tables, so a row that is briefly absent is a row that is briefly unsearchable. A row whose SMILES changes has its RDKit link cleared for the linking pass to redo.

ord_schema.orm.database.update_rdkit_ids(dataset_id: str, session: Session, *, shard: tuple[int, int] | None = None) → None#

Updates RDKit reaction and mol ID associations in the ORD tables.

shard (index, num_shards) restricts the links to one SMILES-hash partition, matching the insert partition in update_rdkit_tables so shard k links exactly the structures it inserted.

Raises:

RuntimeError – If the server is older than PostgreSQL 12 (the queries use AS MATERIALIZED).

ord_schema.orm.database.update_rdkit_tables(dataset_id: str, session: Session, *, shard: tuple[int, int] | None = None) → None#

Updates RDKit PostgreSQL cartridge data.

shard (index, num_shards) restricts the inserts to one hash-partition of the SMILES values, so a dataset’s RDKit work can be split across workers. Because every RDKit sub-step (here and in update_rdkit_ids) partitions by the same SMILES hash, shard k inserts exactly the structures its links will reference – disjoint key sets across shards, so concurrent shards never collide on the shared rdkit.* unique indexes (datasets still run serially).

Raises:

RuntimeError – If the server is older than PostgreSQL 12 (the queries use AS MATERIALIZED).

ord_schema.orm.derived_mappers#

ORM tables for derived, best-effort data that is not part of the proto.

These live in a separate “derived” schema to keep the proto-mirroring “ord” schema canonical. Populated by post-passes (not from_proto); the ORM works without them.

class ord_schema.orm.derived_mappers.CompoundSmiles(**kwargs)#

Bases: Base

Generated compound SMILES and its link to the deduplicated RDKit mol.

One row per ord.compound: smiles is filled by update_derived_tables, rdkit_mol_id by the RDKit linking pass.

compound_id#
rdkit_mol#
rdkit_mol_id#
smiles#
class ord_schema.orm.derived_mappers.ProductCompoundSmiles(**kwargs)#

Bases: Base

Generated product-compound SMILES and its link to the deduplicated RDKit mol.

One row per ord.product_compound; see CompoundSmiles.

product_compound_id#
rdkit_mol#
rdkit_mol_id#
smiles#
class ord_schema.orm.derived_mappers.ReactionSmiles(**kwargs)#

Bases: Base

Generated reaction SMILES and its link to the deduplicated RDKit reaction.

One row per reaction: reaction_smiles is filled by update_derived_tables, rdkit_reaction_id by the RDKit linking pass (rdkit.reactions is deduplicated by SMILES).

rdkit_reaction#
rdkit_reaction_id#
reaction_id#
reaction_smiles#

ord_schema.orm.loading#

Staged loading of ORD datasets into the ORM database.

Loading is two independent stages: ingest writes the ord.* search index and public.* payload, and derivation writes the derived.* SMILES and RDKit links. Either stage can run without the other; derivation is idempotent, so the derived-only stage backfills or recomputes derived data over already- ingested datasets. load_datasets orchestrates both stages over a glob of dataset files; the per-dataset helpers (ingest_dataset, derive_dataset, add_rdkit) are exposed for callers that compose their own pipeline.

ord_schema.orm.loading.add_rdkit(engine: Engine, dataset_id: str) → None#

Populates and links the RDKit cartridge tables for a dataset.

ord_schema.orm.loading.dataset_id_for_file(filename: str) → str#

Returns the dataset ID for a dataset file without ingesting it.

ord_schema.orm.loading.derive_dataset(dataset_id: str, *, dsn: str, rederive: bool = False) → str#

Runs the parallel-safe derived passes.

The RDKit linking pass is excluded here and run serially in add_rdkit to avoid deadlocks. Idempotent, so it is safe to re-run over already-derived datasets.

Parameters:
  • dataset_id – Dataset to derive.

  • dsn – Database connection string.

  • rederive – Whether to delete existing derived rows first so they are recomputed.

Returns:

Dataset ID.

ord_schema.orm.loading.ingest_dataset(filename: str, *, dsn: str, overwrite: bool) → str#

Ingests a single dataset (ord.* + public.*); skips unchanged datasets.

Derived data is populated separately by derive_dataset/add_rdkit.

Parameters:
  • filename – Dataset filename. .parquet inputs are streamed via database.add_parquet_dataset; other formats load the whole Dataset proto and call database.add_dataset.

  • dsn – Database connection string.

  • overwrite – If True, update the dataset if the MD5 hash has changed.

Returns:

Dataset ID.

Raises:

ValueError – If the dataset already exists in the database and overwrite is not set.

ord_schema.orm.loading.load_datasets(pattern: str, dsn: str, *, stages: Iterable[str] = ('ingest', 'derived'), overwrite: bool = False, rederive: bool = False, prune_rdkit: bool = False, n_jobs: int = 1) → None#

Runs the selected stages over the datasets matching pattern.

The parallel-safe work (ingest, SMILES) runs in a worker pool; the RDKit linking pass runs serially to avoid deadlocks. When ingest is skipped, dataset IDs are resolved from pattern so derivation runs over the already-ingested rows.

Parameters:
  • pattern – Glob for dataset filenames.

  • dsn – Database connection string.

  • stages – Stages to run (any of STAGES); defaults to all.

  • overwrite – If True, update changed datasets during ingest.

  • rederive – If True, delete existing derived rows before deriving, so rows written by an earlier version of the derivation are recomputed rather than skipped. Without it the derived stage backfills gaps but never revisits a row.

  • prune_rdkit – If True, delete RDKit structures nothing references once the RDKit pass has finished. A rebuild that changes SMILES links to new structures and strands the old ones; this collects them. Off by default because it is whole-database and unsafe to run beside a concurrent load.

  • n_jobs – Number of parallel workers.

Raises:
  • ValueError – If stages is empty or names an unknown stage.

  • RuntimeError – If any dataset failed in any stage; the inputs are the message.

ord_schema.orm.mappers#

Table mappings for Reaction protos.

Notes

  • Foreign keys to the reaction table are done using the id column, not the ORD reaction ID (reaction_id). However, the reaction_id column is used when the reaction ID is specifically called for, as with crude inputs.

  • We use inheritance to handle messages that appear in more than one context; see https://docs.sqlalchemy.org/en/14/orm/inheritance.html. The possible constraints are:

    • Some message types are used more than once in a parent (such as Time in ReactionInput), which forces the use of either joined or single table inheritance.

    • Some messages can be repeated, while others are unique to their parent. These uniqueness constraints force the use of joined table inheritance since they are specific to their polymorphic type.

    For convenience and consistency, we use single table inheritance for all message types, regardless of whether they are used in one context or more than one context. This means that we do not enforce the second constraint in the database.

class ord_schema.orm.mappers.Mappers#

Bases: object

Container for generated mapper classes.

ord_schema.orm.mappers.build_mapper(message_type: type[Message], parents: dict[type[Message], list[tuple[type[Message], str, bool]]]) → type#

Creates a mapper class for a specific protocol buffer message type.

Parameters:
  • message_type – Protocol buffer message type.

  • parents – Dict mapping message types to lists of (parent message type, field name, unique) tuples.

Returns:

Generated mapper class.

ord_schema.orm.mappers.build_mappers() → dict[type[Message], type]#

Creates ORM mapper classes for protocol buffer message types.

Returns:

Dict mapping protocol buffer message types to mapper classes.

ord_schema.orm.mappers.from_proto(message: Message, mapper: type[Base] | None = None, key: str | None = None) → Base#

Converts a protobuf message into an ORM object.

Parameters:
  • message – Protobuf message.

  • mapper – ORM mapper class. For top-level protos like Dataset and Reaction this can be left as None; it must be provided for Child subclasses to properly handle polymorphism.

  • key – Map key (we store maps as rows of (key, value) tuples).

Returns:

ORM object.

ord_schema.orm.mappers.get_message_type(full_name: str) → Any#

Fetches the class for a protocol buffer message type.

ord_schema.orm.mappers.get_parents(message_type: type[Message]) → dict[type[Message], list[tuple[type[Message], str, bool]]]#

Returns the parent message types for each message type.

ord_schema.orm.mappers.to_proto(base: Base) → Message#

Converts an ORM object into a protobuf message.

Parameters:

base – ORM object.

Returns:

Protobuf message.

ord_schema.orm.public_mappers#

ORM table for the served Reaction proto payload, in the public schema.

The ord.* schema is the search index over reactions; the canonical serialized Reaction proto that the API serves lives here, keyed by the public reaction_id.

class ord_schema.orm.public_mappers.DatasetMetadata(**kwargs)#

Bases: Base

Per-dataset metadata for the API, keyed by dataset_id.

md5 hashes the original Dataset proto (computed at ingest, not reconstructed) and is used to skip re-importing unchanged datasets. num_reactions and submitted_at support dataset browsing; submitted_at is filled by database.set_submitted_at.

dataset_id#
md5#
num_reactions#
submitted_at#
class ord_schema.orm.public_mappers.ReactionProto(**kwargs)#

Bases: Base

Serialized Reaction proto (the served payload), keyed by reaction_id.

proto#
reaction_id#

ord_schema.orm.rdkit_mappers#

RDKit PostgreSQL cartridge functionality.

Notes

  • These tables live in a separate “rdkit” schema to avoid name conflicts between tables and extension types.

  • The RDKit-specific columns are populated by ord_schema.orm.database.add_rdkit; this allows the ORM to function normally even if if the RDKit PostgreSQL cartridge is not installed (the smiles column will be populated and the other columns will be empty).

  • Objects with this type are added to the ORM in from_proto() using the rdkit field.

class ord_schema.orm.rdkit_mappers.CString#

Bases: UserDefinedType

PostgreSQL cstring.

cache_ok: bool | None = True#

The type takes no arguments, so SQLAlchemy may cache statements that use it.

get_col_spec(**kwargs: Any) → str#

Returns the column type.

property python_type: type#

Raises NotImplementedError; this type has no Python equivalent.

class ord_schema.orm.rdkit_mappers.FingerprintType(value)#

Bases: Enum

RDKit PostgreSQL fingerprint types.

MORGAN_BFP = <sqlalchemy.sql.functions._FunctionGenerator object>#
MORGAN_SFP = <sqlalchemy.sql.functions._FunctionGenerator object>#
class ord_schema.orm.rdkit_mappers.RDKitBfp#

Bases: UserDefinedType

rdkit/rdkit.

cache_ok: bool | None = True#

The type takes no arguments, so SQLAlchemy may cache statements that use it.

get_col_spec(**kwargs: Any) → str#

Returns the column type.

property python_type: type#

Raises NotImplementedError; this type has no Python equivalent.

class ord_schema.orm.rdkit_mappers.RDKitMol#

Bases: UserDefinedType

rdkit/rdkit.

cache_ok: bool | None = True#

The type takes no arguments, so SQLAlchemy may cache statements that use it.

get_col_spec(**kwargs: Any) → str#

Returns the column type.

property python_type: type#

Raises NotImplementedError; this type has no Python equivalent.

class ord_schema.orm.rdkit_mappers.RDKitMols(**kwargs)#

Bases: Base

Table for storing compound structures and associated RDKit cartridge data.

classmethod contains_substructure(pattern: str) → ColumnElement[bool]#

Returns whether the stored mol contains the pattern substructure.

Uses the @> operator, which is backed by the GiST mol index.

id#
classmethod is_similar(other: str, fp_type: FingerprintType = FingerprintType.MORGAN_BFP) → ColumnElement[bool]#

Returns whether the stored fingerprint is similar to other.

Uses the % operator, which is backed by the GiST fingerprint index. For MORGAN_BFP the cutoff is read from the rdkit.tanimoto_threshold session setting (default 0.5). For MORGAN_SFP (sparse fingerprints) the cartridge uses Dice similarity and reads rdkit.dice_threshold instead. Set the relevant GUC via set_config before executing.

classmethod matches_smarts(pattern: str) → ColumnElement[bool]#

Returns whether the stored mol matches the SMARTS pattern.

Uses the @> operator, which is backed by the GiST mol index.

mol#
morgan_bfp#
morgan_sfp#
smiles#
classmethod tanimoto(other: str, fp_type: FingerprintType = FingerprintType.MORGAN_BFP) → ColumnElement[float]#

Returns the Tanimoto similarity between the stored fingerprint and other.

This computes the similarity for every row (no index assistance) and is intended for selecting or ordering by similarity. To filter by similarity, use is_similar, which uses the GiST fingerprint index.

class ord_schema.orm.rdkit_mappers.RDKitReaction#

Bases: UserDefinedType

rdkit/rdkit.

cache_ok: bool | None = True#

The type takes no arguments, so SQLAlchemy may cache statements that use it.

get_col_spec(**kwargs: Any) → str#

Returns the column type.

property python_type: type#

Raises NotImplementedError; this type has no Python equivalent.

class ord_schema.orm.rdkit_mappers.RDKitReactions(**kwargs)#

Bases: Base

Table for storing reaction objects and associated RDKit cartridge data.

id#
classmethod matches_smarts(pattern: str) → ColumnElement[bool]#

Returns whether the stored reaction matches the reaction SMARTS pattern.

Uses the @> operator, which is backed by the GiST reaction index.

reaction#
reaction_smiles#
class ord_schema.orm.rdkit_mappers.RDKitSfp#

Bases: UserDefinedType

rdkit/rdkit.

cache_ok: bool | None = True#

The type takes no arguments, so SQLAlchemy may cache statements that use it.

get_col_spec(**kwargs: Any) → str#

Returns the column type.

property python_type: type#

Raises NotImplementedError; this type has no Python equivalent.

ord_schema.orm.rdkit_mappers.rdkit_cartridge() → bool#

Returns whether to use RDKit PostgreSQL cartridge functionality.

ord_schema.orm.sharding#

Hash-partition predicate shared by the sharded derived passes.

Kept in its own module so any of the derived passes can use it without an import cycle.

ord_schema.orm.sharding.shard_predicate(column: str, shard: tuple[int, int] | None) → tuple[str, dict[str, int]]#

Returns an AND predicate selecting 1/num_shards of rows by column.

Partitions a dataset’s rows deterministically and disjointly by a hash of column, so independent workers can each process their shard without coordinating. shard is (index, num_shards); None disables sharding (whole dataset).

column is interpolated into the SQL, so it must be a trusted, hardcoded column reference (e.g. "ord.reaction.id") – never a user-supplied string. The shard bounds are passed as bind parameters.