def build_clock_table(entity_table: sa.Table,
metadata: sa.MetaData,
schema: str,
activity_class=None) -> sa.Table:
clock_table_name = util.truncate_identifier(
"%s_clock" % entity_table.name)
clock_table = sa.Table(
clock_table_name,
metadata,
sa.Column('id',
sap.UUID(as_uuid=True),
default=uuid.uuid4,
primary_key=True),
sa.Column('tick',
sa.Integer,
nullable=False,
autoincrement=False),
sa.Column('timestamp',
sa.DateTime(True),
server_default=sa.func.current_timestamp()),
schema=schema)
entity_keys = list()
for fk in util.foreign_key_to(entity_table, nullable=False):
# this is done to support arbitrary primary key shape on entity
# We don't add additional indices on the foreign keys here because
# the uniqueness constraints will add an implicit index.
clock_table.append_column(fk)
entity_keys.append(fk.key)
tick_entity_unique_name = util.truncate_identifier(
'%s_tick_entity_id_key' % clock_table_name
)
clock_table.append_constraint(
sa.UniqueConstraint(*(entity_keys + ['tick']),
name=tick_entity_unique_name)
)
if activity_class:
activity_keys = list()
# support arbitrary shaped activity primary keys
for fk in util.foreign_key_to(activity_class.__table__,
prefix='activity',
nullable=False):
clock_table.append_column(fk)
activity_keys.append(fk.key)
# ensure we have DB constraint on clock <> activity uniqueness
clock_table.append_constraint(
sa.UniqueConstraint(*(entity_keys + activity_keys))
)
return clock_table
def build_history_table(
cls: declarative.DeclarativeMeta,
prop: T_PROPS,
schema: str = None) -> sa.Table:
"""build a sql alchemy table for given prop"""
if isinstance(prop, orm.RelationshipProperty):
columns = [util.copy_column(column) for column in prop.local_columns]
else:
columns = [util.copy_column(column) for column in prop.columns]
local_table = cls.__table__
table_name = util.truncate_identifier(
_generate_history_table_name(local_table, columns)
)
# Build the foreign key(s), specifically adding an index since we may use
# a casted foreign key in our constraints. See _exclusion_in_uuid
entity_foreign_keys = list(util.foreign_key_to(local_table, index=True))
entity_constraints = [
_exclusion_in(fk.type, fk.key)
for fk in entity_foreign_keys
]
constraints = [
sa.Index(
util.truncate_identifier('%s_effective_idx' % table_name),
'effective',
postgresql_using='gist'
),
sap.ExcludeConstraint(
*itertools.chain(entity_constraints, [('vclock', '&&')]),
name=util.truncate_identifier('%s_excl_vclock' % table_name)
),
sap.ExcludeConstraint(
*itertools.chain(entity_constraints, [('effective', '&&')]),
name=util.truncate_identifier('%s_excl_effective' % table_name)
),
]
return sa.Table(
table_name,
local_table.metadata,
sa.Column('id',
sap.UUID(as_uuid=True),
default=uuid.uuid4,
primary_key=True),
sa.Column('effective',
sap.TSTZRANGE,
default=util.effective_now,
nullable=False),
sa.Column('vclock', sap.INT4RANGE, nullable=False),
*itertools.chain(entity_foreign_keys, columns, constraints),
schema=schema or local_table.schema,
keep_existing=True
) # memoization ftw