def testNameShrinking(self):
"""Test that too-long names for database entities other than tables
and columns (which we preserve, and just expect to fit) are shrunk.
"""
db = self.makeEmptyDatabase(origin=1)
with db.declareStaticTables(create=True) as context:
# Table and field names are each below the 63-char limit even when
# accounting for the prefix, but their combination (which will
# appear in sequences and constraints) is not.
tableName = "a_table_with_a_very_very_long_42_char_name"
fieldName1 = "a_column_with_a_very_very_long_43_char_name"
fieldName2 = "another_column_with_a_very_very_long_49_char_name"
context.addTable(
tableName,
ddl.TableSpec(
fields=[
ddl.FieldSpec(
fieldName1,
dtype=sqlalchemy.BigInteger,
autoincrement=True,
primaryKey=True
),
ddl.FieldSpec(
fieldName2,
dtype=sqlalchemy.String,
length=16,
nullable=False,
),
],
unique={(fieldName2,)},
)
)
# Add another table, this time dynamically, with a foreign key to the
# first table.
db.ensureTableExists(
tableName + "_b",
ddl.TableSpec(
fields=[
ddl.FieldSpec(
fieldName1 + "_b",
dtype=sqlalchemy.BigInteger,
autoincrement=True,
primaryKey=True
),
ddl.FieldSpec(
fieldName2 + "_b",
dtype=sqlalchemy.String,
length=16,
nullable=False,
),
],
foreignKeys=[
ddl.ForeignKeySpec(tableName, source=(fieldName2 + "_b",), target=(fieldName2,)),
]
)
)
def test_RangeTimespanType(self):
start = astropy.time.Time('2020-01-01T00:00:00', format="isot", scale="tai")
offset = astropy.time.TimeDelta(60, format="sec")
timestamps = [start + offset*n for n in range(3)]
timespans = [Timespan(begin=None, end=None)]
timespans.extend(Timespan(begin=None, end=t) for t in timestamps)
timespans.extend(Timespan(begin=t, end=None) for t in timestamps)
timespans.extend(Timespan(begin=a, end=b) for a, b in itertools.combinations(timestamps, 2))
db = self.makeEmptyDatabase(origin=1)
with db.declareStaticTables(create=True) as context:
tbl = context.addTable(
"tbl",
ddl.TableSpec(
fields=[
ddl.FieldSpec(name="id", dtype=sqlalchemy.Integer, primaryKey=True),
ddl.FieldSpec(name="timespan", dtype=_RangeTimespanType),
],
)
)
rows = [{"id": n, "timespan": t} for n, t in enumerate(timespans)]
db.insert(tbl, *rows)
# Test basic round-trip through database.
self.assertEqual(
rows,
[dict(row) for row in db.query(tbl.select().order_by(tbl.columns.id)).fetchall()]
)
# Test that Timespan's Python methods are consistent with our usage of
# half-open ranges and PostgreSQL operators on ranges.
def subquery(alias: str) -> sqlalchemy.sql.FromClause:
return sqlalchemy.sql.select(
[tbl.columns.id.label("id"), tbl.columns.timespan.label("timespan")]
).select_from(
tbl
).alias(alias)
sq1 = subquery("sq1")
sq2 = subquery("sq2")
query = sqlalchemy.sql.select([
sq1.columns.id.label("n1"),
sq2.columns.id.label("n2"),
sq1.columns.timespan.overlaps(sq2.columns.timespan).label("overlaps"),
])
dbResults = {
(row[query.columns.n1], row[query.columns.n2]): row[query.columns.overlaps]
for row in db.query(query)
}
pyResults = {
(n1, n2): t1.overlaps(t2)
for (n1, t1), (n2, t2) in itertools.product(enumerate(timespans), enumerate(timespans))
}
self.assertEqual(pyResults, dbResults)
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 makeTableSpec(cls):
return ddl.TableSpec(
fields=NamedValueSet([
ddl.FieldSpec(name="dataset_id",
dtype=Integer,
primaryKey=True),
ddl.FieldSpec(name="path",
dtype=String,
length=256,
nullable=False),
ddl.FieldSpec(name="formatter",
dtype=String,
length=128,
nullable=False),
ddl.FieldSpec(name="storage_class",
dtype=String,
length=64,
nullable=False),
# TODO: should checksum be Base64Bytes instead?
ddl.FieldSpec(name="checksum",
dtype=String,
length=128,
nullable=True),
ddl.FieldSpec(name="file_size", dtype=Integer, nullable=True),
]),
unique=frozenset(),
foreignKeys=[
ddl.ForeignKeySpec(table="dataset",
source=("dataset_id", ),
target=("dataset_id", ),
onDelete="CASCADE")
])
def addDatasetForeignKey(tableSpec: ddl.TableSpec,
dtype: type,
*,
name: str = "dataset",
onDelete: Optional[str] = None,
constraint: bool = True,
**kwargs: Any) -> ddl.FieldSpec:
"""Add a foreign key column for datasets and (optionally) a constraint to
a table.
This is an internal interface for the ``byDimensions`` package; external
code should use `DatasetRecordStorageManager.addDatasetForeignKey` instead.
Parameters
----------
tableSpec : `ddl.TableSpec`
Specification for the table that should reference the dataset
table. Will be modified in place.
dtype: `type`
Type of the column, same as the column type of the PK column of
a referenced table (``dataset.id``).
name: `str`, optional
A name to use for the prefix of the new field; the full name is
``{name}_id``.
onDelete: `str`, optional
One of "CASCADE" or "SET NULL", indicating what should happen to
the referencing row if the collection row is deleted. `None`
indicates that this should be an integrity error.
constraint: `bool`, optional
If `False` (`True` is default), add a field that can be joined to
the dataset primary key, but do not add a foreign key constraint.
**kwargs
Additional keyword arguments are forwarded to the `ddl.FieldSpec`
constructor (only the ``name`` and ``dtype`` arguments are
otherwise provided).
Returns
-------
idSpec : `ddl.FieldSpec`
Specification for the ID field.
"""
idFieldSpec = ddl.FieldSpec(f"{name}_id", dtype=dtype, **kwargs)
tableSpec.fields.add(idFieldSpec)
if constraint:
tableSpec.foreignKeys.append(
ddl.ForeignKeySpec("dataset",
source=(idFieldSpec.name, ),
target=("id", ),
onDelete=onDelete))
return idFieldSpec
def _makeTableSpecs(
datasets: Type[DatasetRecordStorageManager]) -> _TablesTuple:
"""Construct specifications for tables used by the monolithic datastore
bridge classes.
Parameters
----------
universe : `DimensionUniverse`
All dimensions known to the `Registry`.
datasets : subclass of `DatasetRecordStorageManager`
Manager class for datasets; used only to create foreign key fields.
Returns
-------
specs : `_TablesTuple`
A named tuple containing `ddl.TableSpec` instances.
"""
# We want the dataset_location and dataset_location_trash tables
# to have the same definition, aside from the behavior of their link
# to the dataset table: the trash table has no foreign key constraint.
dataset_location_spec = ddl.TableSpec(
doc=
("A table that provides information on whether a dataset is stored in "
"one or more Datastores. The presence or absence of a record in this "
"table itself indicates whether the dataset is present in that "
"Datastore. "),
fields=NamedValueSet([
ddl.FieldSpec(
name="datastore_name",
dtype=sqlalchemy.String,
length=256,
primaryKey=True,
nullable=False,
doc="Name of the Datastore this entry corresponds to.",
),
]),
)
dataset_location = copy.deepcopy(dataset_location_spec)
datasets.addDatasetForeignKey(dataset_location, primaryKey=True)
dataset_location_trash = copy.deepcopy(dataset_location_spec)
datasets.addDatasetForeignKey(dataset_location_trash,
primaryKey=True,
constraint=False)
return _TablesTuple(
dataset_location=dataset_location,
dataset_location_trash=dataset_location_trash,
)
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 isEmptyDatabaseActuallyWriteable(database: SqliteDatabase) -> bool:
"""Check whether we really can modify a database.
This intentionally allows any exception to be raised (not just
`ReadOnlyDatabaseError`) to deal with cases where the file is read-only
but the Database was initialized (incorrectly) with writeable=True.
"""
try:
with database.declareStaticTables(create=True) as context:
table = context.addTable(
"a",
ddl.TableSpec(fields=[ddl.FieldSpec("b", dtype=sqlalchemy.Integer, primaryKey=True)])
)
# Drop created table so that schema remains empty.
database._metadata.drop_all(database._connection, tables=[table])
return True
except Exception:
return False
def makeStaticTableSpecs(
collections: Type[CollectionManager],
universe: DimensionUniverse,
) -> StaticDatasetTablesTuple:
"""Construct all static tables used by the classes in this package.
Static tables are those that are present in all Registries and do not
depend on what DatasetTypes have been registered.
Parameters
----------
collections: `CollectionManager`
Manager object for the collections in this `Registry`.
universe : `DimensionUniverse`
Universe graph containing all dimensions known to this `Registry`.
Returns
-------
specs : `StaticDatasetTablesTuple`
A named tuple containing `ddl.TableSpec` instances.
"""
specs = StaticDatasetTablesTuple(
dataset_type=ddl.TableSpec(
fields=[
ddl.FieldSpec(
name="id",
dtype=sqlalchemy.BigInteger,
autoincrement=True,
primaryKey=True,
doc=(
"Autoincrement ID that uniquely identifies a dataset "
"type in other tables. Python code outside the "
"`Registry` class should never interact with this; "
"its existence is considered an implementation detail."
),
),
ddl.FieldSpec(
name="name",
dtype=sqlalchemy.String,
length=DATASET_TYPE_NAME_LENGTH,
nullable=False,
doc="String name that uniquely identifies a dataset type.",
),
ddl.FieldSpec(
name="storage_class",
dtype=sqlalchemy.String,
length=64,
nullable=False,
doc=("Name of the storage class associated with all "
"datasets of this type. Storage classes are "
"generally associated with a Python class, and are "
"enumerated in butler configuration.")),
ddl.FieldSpec(
name="dimensions_encoded",
dtype=ddl.Base64Bytes,
nbytes=universe.getEncodeLength(),
nullable=False,
doc=("An opaque (but reversible) encoding of the set of "
"dimensions used to identify dataset of this type."),
),
],
unique=[("name", )],
),
dataset=ddl.TableSpec(
fields=[
ddl.FieldSpec(
name="id",
dtype=sqlalchemy.BigInteger,
autoincrement=True,
primaryKey=True,
doc=
"A unique autoincrement field used as the primary key for dataset.",
),
ddl.FieldSpec(
name="dataset_type_id",
dtype=sqlalchemy.BigInteger,
nullable=False,
doc=(
"Reference to the associated entry in the dataset_type "
"table."),
),
# Foreign key field/constraint to run added below.
],
foreignKeys=[
ddl.ForeignKeySpec("dataset_type",
source=("dataset_type_id", ),
target=("id", )),
]),
)
# Add foreign key fields programmatically.
collections.addRunForeignKey(specs.dataset,
onDelete="CASCADE",
nullable=False)
return specs
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
def makeStaticTableSpecs(
collections: Type[CollectionManager],
universe: DimensionUniverse,
dtype: type,
autoincrement: bool,
) -> StaticDatasetTablesTuple:
"""Construct all static tables used by the classes in this package.
Static tables are those that are present in all Registries and do not
depend on what DatasetTypes have been registered.
Parameters
----------
collections: `CollectionManager`
Manager object for the collections in this `Registry`.
universe : `DimensionUniverse`
Universe graph containing all dimensions known to this `Registry`.
dtype: `type`
Type of the dataset ID (primary key) column.
autoincrement: `bool`
If `True` then dataset ID column will be auto-incrementing.
Returns
-------
specs : `StaticDatasetTablesTuple`
A named tuple containing `ddl.TableSpec` instances.
"""
specs = StaticDatasetTablesTuple(
dataset_type=ddl.TableSpec(
fields=[
ddl.FieldSpec(
name="id",
dtype=sqlalchemy.BigInteger,
autoincrement=True,
primaryKey=True,
doc=(
"Autoincrement ID that uniquely identifies a dataset "
"type in other tables. Python code outside the "
"`Registry` class should never interact with this; "
"its existence is considered an implementation detail."
),
),
ddl.FieldSpec(
name="name",
dtype=sqlalchemy.String,
length=DATASET_TYPE_NAME_LENGTH,
nullable=False,
doc="String name that uniquely identifies a dataset type.",
),
ddl.FieldSpec(
name="storage_class",
dtype=sqlalchemy.String,
length=64,
nullable=False,
doc=("Name of the storage class associated with all "
"datasets of this type. Storage classes are "
"generally associated with a Python class, and are "
"enumerated in butler configuration.")),
ddl.FieldSpec(
name="dimensions_key",
dtype=sqlalchemy.BigInteger,
nullable=False,
doc=(
"Unique key for the set of dimensions that identifies "
"datasets of this type."),
),
ddl.FieldSpec(
name="tag_association_table",
dtype=sqlalchemy.String,
length=128,
nullable=False,
doc=("Name of the table that holds associations between "
"datasets of this type and most types of collections."
),
),
ddl.FieldSpec(
name="calibration_association_table",
dtype=sqlalchemy.String,
length=128,
nullable=True,
doc=("Name of the table that holds associations between "
"datasets of this type and CALIBRATION collections. "
"NULL values indicate dataset types with "
"isCalibration=False."),
),
],
unique=[("name", )],
),
dataset=ddl.TableSpec(
fields=[
ddl.FieldSpec(
name="id",
dtype=dtype,
autoincrement=autoincrement,
primaryKey=True,
doc="A unique field used as the primary key for dataset.",
),
ddl.FieldSpec(
name="dataset_type_id",
dtype=sqlalchemy.BigInteger,
nullable=False,
doc=(
"Reference to the associated entry in the dataset_type "
"table."),
),
ddl.FieldSpec(
name="ingest_date",
dtype=sqlalchemy.TIMESTAMP,
default=sqlalchemy.sql.func.now(),
nullable=False,
doc="Time of dataset ingestion.",
),
# Foreign key field/constraint to run added below.
],
foreignKeys=[
ddl.ForeignKeySpec("dataset_type",
source=("dataset_type_id", ),
target=("id", )),
]),
)
# Add foreign key fields programmatically.
collections.addRunForeignKey(specs.dataset,
onDelete="CASCADE",
nullable=False)
return specs
