def makeTableSpecs(
cls,
collections: CollectionManager,
dimensions: DimensionRecordStorageManager,
) -> CollectionSummaryTables[ddl.TableSpec]:
"""Create specifications for all summary tables.
Parameters
----------
collections: `CollectionManager`
Manager object for the collections in this `Registry`.
dimensions : `DimensionRecordStorageManager`
Manager object for the dimensions in this `Registry`.
Returns
-------
tables : `CollectionSummaryTables` [ `ddl.TableSpec` ]
Structure containing table specifications.
"""
# Spec for collection_summary_dataset_type.
datasetTypeTableSpec = ddl.TableSpec(fields=[])
collections.addCollectionForeignKey(datasetTypeTableSpec,
primaryKey=True,
onDelete="CASCADE")
datasetTypeTableSpec.fields.add(
ddl.FieldSpec("dataset_type_id",
dtype=sqlalchemy.BigInteger,
primaryKey=True))
datasetTypeTableSpec.foreignKeys.append(
ddl.ForeignKeySpec("dataset_type",
source=("dataset_type_id", ),
target=("id", ),
onDelete="CASCADE"))
# Specs for collection_summary_<dimension>.
dimensionTableSpecs = NamedKeyDict[GovernorDimension, ddl.TableSpec]()
for dimension in dimensions.universe.getGovernorDimensions():
tableSpec = ddl.TableSpec(fields=[])
collections.addCollectionForeignKey(tableSpec,
primaryKey=True,
onDelete="CASCADE")
addDimensionForeignKey(tableSpec, dimension, primaryKey=True)
dimensionTableSpecs[dimension] = tableSpec
return CollectionSummaryTables(
datasetType=datasetTypeTableSpec,
dimensions=dimensionTableSpecs.freeze(),
)
def makeDynamicTableSpec(
datasetType: DatasetType,
collections: Type[CollectionManager]) -> ddl.TableSpec:
"""Construct the specification for a dynamic (DatasetType-dependent) table
used by the classes in this package.
Parameters
----------
datasetType : `DatasetType`
Dataset type to construct a spec for. Multiple dataset types may
share the same table.
Returns
-------
spec : `ddl.TableSpec`
Specification for the table.
"""
tableSpec = ddl.TableSpec(
fields=[
# Foreign key fields to dataset, collection, and usually dimension
# tables added below.
# The dataset_type_id field here would be redundant with the one
# in the main monolithic dataset table, but we need it here for an
# important unique constraint.
ddl.FieldSpec("dataset_type_id",
dtype=sqlalchemy.BigInteger,
nullable=False),
],
foreignKeys=[
ddl.ForeignKeySpec("dataset_type",
source=("dataset_type_id", ),
target=("id", )),
])
# We'll also have a unique constraint on dataset type, collection, and data
# ID. We only include the required part of the data ID, as that's
# sufficient and saves us from worrying about nulls in the constraint.
constraint = ["dataset_type_id"]
# Add foreign key fields to dataset table (part of the primary key)
addDatasetForeignKey(tableSpec, primaryKey=True, onDelete="CASCADE")
# Add foreign key fields to collection table (part of the primary key and
# the data ID unique constraint).
fieldSpec = collections.addCollectionForeignKey(tableSpec,
primaryKey=True,
onDelete="CASCADE")
constraint.append(fieldSpec.name)
for dimension in datasetType.dimensions.required:
fieldSpec = addDimensionForeignKey(tableSpec,
dimension=dimension,
nullable=False,
primaryKey=False)
constraint.append(fieldSpec.name)
# Actually add the unique constraint.
tableSpec.unique.add(tuple(constraint))
return tableSpec
def makeCalibTableSpec(datasetType: DatasetType, collections: Type[CollectionManager],
TimespanReprClass: Type[TimespanDatabaseRepresentation]) -> ddl.TableSpec:
"""Construct the specification for a dynamic (DatasetType-dependent) tag +
validity range table used by the classes in this package.
Parameters
----------
datasetType : `DatasetType`
Dataset type to construct a spec for. Multiple dataset types may
share the same table.
collections : `type` [ `CollectionManager` ]
`CollectionManager` subclass that can be used to construct foreign keys
to the run and/or collection tables.
Returns
-------
spec : `ddl.TableSpec`
Specification for the table.
"""
tableSpec = ddl.TableSpec(
fields=[
# This table has no natural primary key, compound or otherwise, so
# we add an autoincrement key. We may use this field a bit
# internally, but its presence is an implementation detail and it
# shouldn't appear as a foreign key in any other tables.
ddl.FieldSpec("id", dtype=sqlalchemy.BigInteger, autoincrement=True, primaryKey=True),
# Foreign key fields to dataset, collection, and usually dimension
# tables added below. The dataset_type_id field here is redundant
# with the one in the main monolithic dataset table, but this bit
# of denormalization lets us define what should be a much more
# useful index.
ddl.FieldSpec("dataset_type_id", dtype=sqlalchemy.BigInteger, nullable=False),
],
foreignKeys=[
ddl.ForeignKeySpec("dataset_type", source=("dataset_type_id",), target=("id",)),
]
)
# Record fields that should go in the temporal lookup index/constraint,
# starting with the dataset type.
index: List[Union[str, Type[TimespanDatabaseRepresentation]]] = ["dataset_type_id"]
# Add foreign key fields to dataset table (not part of the temporal
# lookup/constraint).
addDatasetForeignKey(tableSpec, nullable=False, onDelete="CASCADE")
# Add foreign key fields to collection table (part of the temporal lookup
# index/constraint).
collectionFieldSpec = collections.addCollectionForeignKey(tableSpec, nullable=False, onDelete="CASCADE")
index.append(collectionFieldSpec.name)
# Add foreign key constraint to the collection_summary_dataset_type table.
tableSpec.foreignKeys.append(
ddl.ForeignKeySpec(
"collection_summary_dataset_type",
source=(collectionFieldSpec.name, "dataset_type_id"),
target=(collectionFieldSpec.name, "dataset_type_id"),
)
)
# Add dimension fields (part of the temporal lookup index.constraint).
for dimension in datasetType.dimensions.required:
fieldSpec = addDimensionForeignKey(tableSpec, dimension=dimension, nullable=False, primaryKey=False)
index.append(fieldSpec.name)
# If this is a governor dimension, add a foreign key constraint to the
# collection_summary_<dimension> table.
if isinstance(dimension, GovernorDimension):
tableSpec.foreignKeys.append(
ddl.ForeignKeySpec(
f"collection_summary_{dimension.name}",
source=(collectionFieldSpec.name, fieldSpec.name),
target=(collectionFieldSpec.name, fieldSpec.name),
)
)
# Add validity-range field(s) (part of the temporal lookup
# index/constraint).
tsFieldSpecs = TimespanReprClass.makeFieldSpecs(nullable=False)
for fieldSpec in tsFieldSpecs:
tableSpec.fields.add(fieldSpec)
if TimespanReprClass.hasExclusionConstraint():
# This database's timespan representation can define a database-level
# constraint that prevents overlapping validity ranges for entries with
# the same DatasetType, collection, and data ID.
# This also creates an index.
index.append(TimespanReprClass)
tableSpec.exclusion.add(tuple(index))
else:
# No database-level constraint possible. We'll have to simulate that
# in our DatasetRecordStorage.certify() implementation, and just create
# a regular index here in the hope that helps with lookups.
index.extend(fieldSpec.name for fieldSpec in tsFieldSpecs)
tableSpec.indexes.add(tuple(index)) # type: ignore
return tableSpec
def makeTagTableSpec(datasetType: DatasetType,
collections: Type[CollectionManager],
dtype: type) -> ddl.TableSpec:
"""Construct the specification for a dynamic (DatasetType-dependent) tag
table used by the classes in this package.
Parameters
----------
datasetType : `DatasetType`
Dataset type to construct a spec for. Multiple dataset types may
share the same table.
collections : `type` [ `CollectionManager` ]
`CollectionManager` subclass that can be used to construct foreign keys
to the run and/or collection tables.
dtype: `type`
Type of the FK column, same as the column type of the PK column of
a referenced table (``dataset.id``).
Returns
-------
spec : `ddl.TableSpec`
Specification for the table.
"""
tableSpec = ddl.TableSpec(
fields=[
# Foreign key fields to dataset, collection, and usually dimension
# tables added below.
# The dataset_type_id field here would be redundant with the one
# in the main monolithic dataset table, but we need it here for an
# important unique constraint.
ddl.FieldSpec("dataset_type_id",
dtype=sqlalchemy.BigInteger,
nullable=False),
],
foreignKeys=[
ddl.ForeignKeySpec("dataset_type",
source=("dataset_type_id", ),
target=("id", )),
])
# We'll also have a unique constraint on dataset type, collection, and data
# ID. We only include the required part of the data ID, as that's
# sufficient and saves us from worrying about nulls in the constraint.
constraint = ["dataset_type_id"]
# Add foreign key fields to dataset table (part of the primary key)
addDatasetForeignKey(tableSpec, dtype, primaryKey=True, onDelete="CASCADE")
# Add foreign key fields to collection table (part of the primary key and
# the data ID unique constraint).
collectionFieldSpec = collections.addCollectionForeignKey(
tableSpec, primaryKey=True, onDelete="CASCADE")
constraint.append(collectionFieldSpec.name)
# Add foreign key constraint to the collection_summary_dataset_type table.
tableSpec.foreignKeys.append(
ddl.ForeignKeySpec(
"collection_summary_dataset_type",
source=(collectionFieldSpec.name, "dataset_type_id"),
target=(collectionFieldSpec.name, "dataset_type_id"),
))
for dimension in datasetType.dimensions.required:
fieldSpec = addDimensionForeignKey(tableSpec,
dimension=dimension,
nullable=False,
primaryKey=False)
constraint.append(fieldSpec.name)
# If this is a governor dimension, add a foreign key constraint to the
# collection_summary_<dimension> table.
if isinstance(dimension, GovernorDimension):
tableSpec.foreignKeys.append(
ddl.ForeignKeySpec(
f"collection_summary_{dimension.name}",
source=(collectionFieldSpec.name, fieldSpec.name),
target=(collectionFieldSpec.name, fieldSpec.name),
))
# Actually add the unique constraint.
tableSpec.unique.add(tuple(constraint))
return tableSpec