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 andpublic.*payload.Derived data (SMILES, RDKit links) is populated separately by
update_derived_dataso 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), andadd_parquet_dataset_metadata(thepublic.datasetsmarker). Sharded ingest calls the same primitives across worker processes; here they run serially so the whole dataset lands atomically. Derived data is populated separately byupdate_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.datasetsmetadata row and populatessubmitted_at.Written after the reactions are loaded, so the presence of this row marks a fully loaded dataset (
get_dataset_md5reads it); a crashed load leaves anord.datasetrow with no metadata row, which the next ingest deletes and reloads.- Parameters:
dataset_id – Dataset ID (
public.datasetsprimary 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.datasetsearch-index row (no reactions/metadata).The surrogate
idis supplied by the caller so a subsequent sharded reaction load can reference it without a round trip. Thepublic.datasetsmetadata row is written last, byadd_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.datasetrow.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 withCOPY(_copy_rows) rather than the ORM unit of work. Peak memory is bounded to one row group plus_COPY_BATCHtrees. The parentord.datasetrow identified bydataset_uuidmust already exist (seeadd_parquet_dataset_row).Because rows are streamed with COPY rather than the unit of work, SQLAlchemy instrumentation does not run for this path:
@validatesmethods andbefore_insert/after_insertevent 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.datasetrow (foreign key target).session – SQLAlchemy session.
row_group – If set, only that Parquet row group is loaded; the unit of parallelism for sharded ingest.
Noneloads 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_atfor 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.molsandrdkit.reactionsare 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_molsinserts structures that_link_mol_idslinks 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_atfrom 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_modifiedentry (record events are appended chronologically), falling back to the requiredrecord_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
rederivewhen 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
shardis(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 inupdate_rdkit_tablesso shardklinks 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 inupdate_rdkit_ids) partitions by the same SMILES hash, shardkinserts exactly the structures its links will reference – disjoint key sets across shards, so concurrent shards never collide on the sharedrdkit.*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:
BaseGenerated 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:
BaseGenerated 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:
BaseGenerated 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_rdkitto 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.
.parquetinputs are streamed viadatabase.add_parquet_dataset; other formats load the whole Dataset proto and calldatabase.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
patternso 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
stagesis 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:
objectContainer 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:
BasePer-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#
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:
UserDefinedTypePostgreSQL 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:
EnumRDKit 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- 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- 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:
BaseTable for storing compound structures and associated RDKit cartridge data.
- classmethod contains_substructure(pattern: str) ColumnElement[bool]#
Returns whether the stored mol contains the
patternsubstructure.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. ForMORGAN_BFPthe cutoff is read from therdkit.tanimoto_thresholdsession setting (default 0.5). ForMORGAN_SFP(sparse fingerprints) the cartridge uses Dice similarity and readsrdkit.dice_thresholdinstead. Set the relevant GUC viaset_configbefore 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- 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:
BaseTable 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- 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
ANDpredicate selecting 1/num_shards of rows bycolumn.Partitions a dataset’s rows deterministically and disjointly by a hash of
column, so independent workers can each process their shard without coordinating.shardis(index, num_shards);Nonedisables sharding (whole dataset).columnis 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.