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)