first_overdue = split.transaction.valid_on
else:
first_overdue = False
if not first_overdue:
return 0
return fabs((first_overdue - datetime.date.today()).days)
manager.add_function(
Account.__table__,
ddl.Function(
'account_is_type',
['integer', 'account_type'],
'boolean',
"SELECT type = $2 FROM account WHERE id = $1 ",
volatility='stable',
strict=True,
))
class Transaction(IntegerIdModel):
description = Column(Text(), nullable=False)
author_id = Column(Integer,
ForeignKey("user.id",
ondelete='SET NULL',
onupdate='CASCADE'),
nullable=True)
author = relationship("User")
posted_at = Column(DateTimeTz,
nullable=False,
else f"{self.street} {self.number}", f"{self.zip_code} {city}"
]
if self.state:
state = self.state.upper(
) if self.country and self.country != DEFAULT_COUNTRY else self.state
items.append(f"{state}")
if self.country and self.country != DEFAULT_COUNTRY:
items.append(f"{self.country.upper()}")
glue = ", " if spec == "short" else "\n" if spec == "long" else spec
return glue.join(items)
manager = ddl.DDLManager()
address_remove_orphans = ddl.Function('address_remove_orphans', [],
'trigger',
""" BEGIN
delete from address
where not exists (select 1 from room where room.address_id = address.id)
and not exists (select 1 from "user" where "user".address_id = address.id);
RETURN NULL;
END;""",
volatility='volatile',
strict=True,
language='plpgsql')
manager.add_function(Address.__table__, address_remove_orphans)
# User trigger for the respective backref added in `user.py`
# Room trigger for the respective backref added in `facilities.py`
manager.register()
manager.add_function(
User.__table__,
ddl.Function(
'user_room_change_update_history', [],
'trigger',
"""
BEGIN
IF old.room_id IS DISTINCT FROM new.room_id THEN
IF old.room_id IS NOT NULL THEN
/* User was living in a room before, history entry must be ended */
/* active_during is expected to be [) */
UPDATE "room_history_entry"
SET active_during = active_during - tstzrange(CURRENT_TIMESTAMP, null, '[)')
WHERE room_id = old.room_id AND user_id = new.id
AND active_during && tstzrange(CURRENT_TIMESTAMP, null, '[)');
END IF;
IF new.room_id IS NOT NULL THEN
/* User moved to a new room. history entry must be created */
INSERT INTO "room_history_entry" (user_id, room_id, active_during)
/* We must add one second so that the user doesn't have two entries
for the same timestamp */
VALUES(new.id, new.room_id, tstzrange(CURRENT_TIMESTAMP, null, '[)'));
END IF;
END IF;
RETURN NULL;
END;
""",
volatility='volatile', strict=True, language='plpgsql'
)
)
and_(
or_(Membership.begins_at == null(),
Membership.begins_at <= literal_column('evaluation_time')),
or_(Membership.ends_at == null(),
literal_column('evaluation_time') <= Membership.ends_at),
)).join(Property).group_by(User.id, Property.name)
# granted by ≥1 membership, but also denied by ≥1 membership
.having(and_(func.bool_or(Property.granted),
~func.every(Property.granted))).statement,
)
evaluate_properties_function = ddl.Function(
'evaluate_properties',
['evaluation_time timestamp with time zone'],
'TABLE (user_id INT, property_name VARCHAR(255), denied BOOLEAN)',
str(
property_query_stmt.compile(dialect=postgresql.dialect(),
compile_kwargs={'literal_binds': True})),
volatility='stable',
)
manager.add_function(Membership.__table__, evaluate_properties_function)
current_property = View(
name='current_property',
#metadata=ModelBase.metadata,
query=(select([
literal_column('user_id'),
literal_column('property_name'),
literal_column('denied')
]).select_from(func.evaluate_properties(func.current_timestamp()))),
# Ensure that a connected switch is in the switch_room (patch_port.switch_room_id)
manager.add_function(
PatchPort.__table__,
ddl.Function('patch_port_switch_in_switch_room', [],
'trigger',
"""
DECLARE
v_patch_port patch_port;
v_switch_port_switch_host_room_id integer;
BEGIN
v_patch_port := NEW;
IF v_patch_port.switch_port_id IS NOT NULL THEN
SELECT h.room_id INTO v_switch_port_switch_host_room_id FROM patch_port pp
JOIN switch_port sp ON pp.switch_port_id = sp.id
JOIN host h ON sp.switch_id = h.id
WHERE pp.id = v_patch_port.id;
IF v_switch_port_switch_host_room_id <> v_patch_port.switch_room_id THEN
RAISE EXCEPTION 'A patch-port can only be patched to a switch that is located in the switch-room of
the patch-port';
END IF;
END IF;
RETURN NULL;
END;
""",
volatility='stable',
strict=True,
language='plpgsql'))
manager.add_constraint_trigger(
PatchPort.__table__,
manager.add_function(
User.__table__,
ddl.Function('user_room_change_update_history', [],
'trigger',
"""
BEGIN
IF old.room_id IS DISTINCT FROM new.room_id THEN
IF old.room_id IS NOT NULL THEN
/* User was living in a room before, history entry must be ended */
UPDATE "room_history_entry" SET ends_at = CURRENT_TIMESTAMP
WHERE room_id = old.room_id AND user_id = new.id AND ends_at IS NULL;
END IF;
IF new.room_id IS NOT NULL THEN
/* User moved to a new room. history entry must be created */
INSERT INTO "room_history_entry" (user_id, room_id, begins_at)
/* We must add one second so that the user doesn't have two entries
for the same timestamp */
VALUES(new.id, new.room_id, CURRENT_TIMESTAMP);
END IF;
END IF;
RETURN NULL;
END;
""",
volatility='volatile',
strict=True,
language='plpgsql'))
manager.add_trigger(
User.__table__,
Property.name.label('property_name'),
literal(True).label('denied')
]).select_from(Membership).join(PropertyGroup).join(User).filter(
Membership.active_during.contains(
literal_column('evaluation_time'))).join(Property).group_by(
User.id, Property.name)
# granted by ≥1 membership, but also denied by ≥1 membership
# NB: this does NOT include properties in the list that were ONLY denied, but never granted!
.having(and_(func.bool_or(Property.granted),
~func.every(Property.granted))).statement,
)
evaluate_properties_function = ddl.Function(
'evaluate_properties',
['evaluation_time timestamp with time zone'],
'TABLE (user_id INT, property_name VARCHAR(255), denied BOOLEAN)',
definition=property_query_stmt,
volatility='stable',
)
manager.add_function(Membership.__table__, evaluate_properties_function)
def evaluate_properties(when: datetime | None = None,
name='properties') -> TableValuedAlias:
"""A sqlalchemy `func` wrapper for the `evaluate_properties` PSQL function.
See `sqlalchemy.sql.selectable.FromClause.table_valued`.
"""
return func.evaluate_properties(when)\
.table_valued('user_id', 'property_name', 'denied', name=name)