def test_create_or_replace_view(self):
table = create_table("table")
view = View("view", select([table.c.id]))
stmt = CreateView(view, or_replace=True)
self.assertEqual(
'CREATE OR REPLACE VIEW view AS SELECT "table".id \n'
'FROM "table"', literal_compile(stmt))
def test_create_temporary_view(self):
table = create_table("table")
view = View("view", select([table.c.id]), temporary=True)
stmt = CreateView(view)
self.assertEqual(
'CREATE TEMPORARY VIEW view AS SELECT "table".id \n'
'FROM "table"', literal_compile(stmt))
def test_create_view_with_check_option(table):
view = View("view", select(table.c.id), check_option='cascaded')
stmt = CreateView(view)
assert literal_compile(stmt) == ('CREATE VIEW view '
'AS SELECT "table".id \n'
'FROM "table" '
'WITH CASCADED CHECK OPTION')
def test_plain_create_view(self):
table = create_table("table")
view = View("view", select([table.c.id]))
stmt = CreateView(view)
self.assertEqual(
'CREATE VIEW view AS SELECT "table".id \n'
'FROM "table"', literal_compile(stmt))
def test_create_view_with_check_option(self):
table = create_table("table")
view = View("view", select([table.c.id]), check_option='cascaded')
stmt = CreateView(view)
self.assertEqual(
'CREATE VIEW view '
'AS SELECT "table".id \n'
'FROM "table" '
'WITH CASCADED CHECK OPTION', literal_compile(stmt))
def test_create_view_with_view_options(table):
view = View("view",
select(table.c.id),
view_options=[
('check_option', 'cascaded'),
('security_barrier', 't'),
])
stmt = CreateView(view)
assert literal_compile(stmt) == (
'CREATE VIEW view '
'WITH (check_option = cascaded, security_barrier = t) '
'AS SELECT "table".id \n'
'FROM "table"')
def test_create_view_with_view_options(self):
table = create_table("table")
view = View("view",
select([table.c.id]),
view_options=[
('check_option', 'cascaded'),
('security_barrier', 't'),
])
stmt = CreateView(view)
self.assertEqual(
'CREATE VIEW view '
'WITH (check_option = cascaded, security_barrier = t) '
'AS SELECT "table".id \n'
'FROM "table"', literal_compile(stmt))
def test_drop_view(self):
table = create_table("table")
view = View("view", select([table.c.id]))
stmt = DropView(view)
self.assertEqual('DROP VIEW view', literal_compile(stmt))
def test_create_or_replace_view(table):
view = View("view", select(table.c.id))
stmt = CreateView(view, or_replace=True)
assert literal_compile(stmt) == (
'CREATE OR REPLACE VIEW view AS SELECT "table".id \n'
'FROM "table"')
radusergroup = View(
name='radusergroup',
query=union_all(
# Priority 20: valid case (interface's mac w/ vlan at correct ports)
# <mac> @ <switch>/<port> → <vlan>_[un]tagged (Prio 20)
# Parsing continues because of Fall-Through:=Yes
Query([
Interface.mac.label('UserName'),
# `host()` does not print the `/32` like `text` would
func.host(Switch.management_ip).label('NASIPAddress'),
SwitchPort.name.label('NASPortId'),
# TODO: add `_tagged` instead if interface needs that
(VLAN.name + '_untagged').label('GroupName'),
literal(20).label('Priority'),
]).select_from(User)
.join(Host)
.join(Interface)
.join(Host.room)
.join(Room.connected_patch_ports)
.join(SwitchPort)
.join(Switch)
.join(Interface.ips)
.join(Subnet)
.join(VLAN)
.join(User.current_properties)
.filter(CurrentProperty.property_name == 'network_access')
.statement,
# Priority -10: Blocking reason exists
# <mac> @ <switch>/<port> → <blocking_group> (Prio -10)
# Note that Fall-Through:=No for blocking groups, so first match terminates
Query([
Interface.mac.label('UserName'),
func.host(Switch.management_ip).label('NASIPAddress'),
SwitchPort.name.label('NASPortId'),
radius_property.c.property.label('GroupName'),
literal(-10).label('Priority'),
]).select_from(User)
.join(Host)
.join(Host.interfaces)
.join(Host.room)
.join(Room.connected_patch_ports)
.join(SwitchPort)
.join(Switch)
.join(User.current_properties)
.join(radius_property,
radius_property.c.property == CurrentProperty.property_name)
.statement,
# Priority 0: No blocking reason exists → generic error group `no_network_access`
Query([
Interface.mac.label('UserName'),
func.host(Switch.management_ip).label('NASIPAddress'),
SwitchPort.name.label('NASPortId'),
literal('no_network_access').label('GroupName'),
literal(0).label('Priority'),
]).select_from(User)
.outerjoin(network_access_subq, User.id == network_access_subq.c.user_id)
.filter(network_access_subq.c.network_access == None)
.join(User.hosts)
.join(Host.interfaces)
.join(Host.room)
.join(Room.connected_patch_ports)
.join(SwitchPort)
.join(Switch)
.statement,
),
)
backref=backref("traffic_volumes",
cascade="all, delete-orphan"),
uselist=False)
packets = Column(Integer, CheckConstraint('packets >= 0'), nullable=False)
TrafficVolume.__table__.add_is_dependent_on(IP.__table__)
TrafficBalance.__table__.add_is_dependent_on(TrafficVolume.__table__)
pmacct_traffic_egress = View(
name='pmacct_traffic_egress',
query=(
Query([]).add_columns(
TrafficVolume.packets.label('packets'),
TrafficVolume.amount.label('bytes'),
TrafficVolume.timestamp.label('stamp_inserted'),
TrafficVolume.timestamp.label('stamp_updated'),
IP.address.label('ip_src')).select_from(TrafficVolume).filter_by(
type='Egress').join(IP).
statement # turns our `Selectable` into something compilable
),
)
ddl.add_view(TrafficVolume.__table__, pmacct_traffic_egress)
pmacct_expression_replacements = dict(
tv_tname=TrafficVolume.__tablename__,
tv_type=TrafficVolume.type.key,
tv_ip_id=TrafficVolume.ip_id.key,
tv_timestamp=TrafficVolume.timestamp.key,
tv_amount=TrafficVolume.amount.key,
tv_packets=TrafficVolume.packets.key,
def test_drop_view_cascade(table):
view = View("view", select(table.c.id))
stmt = DropView(view, cascade=True)
assert literal_compile(stmt) == ('DROP VIEW view CASCADE')
def test_drop_view_if_exists(table):
view = View("view", select(table.c.id))
stmt = DropView(view, if_exists=True)
assert literal_compile(stmt) == ('DROP VIEW IF EXISTS view')
def test_drop_view(table):
view = View("view", select(table.c.id))
stmt = DropView(view)
assert literal_compile(stmt) == ('DROP VIEW view')
def test_create_temporary_view(table):
view = View("view", select(table.c.id), temporary=True)
stmt = CreateView(view)
assert literal_compile(stmt) == (
'CREATE TEMPORARY VIEW view AS SELECT "table".id \n'
'FROM "table"')
def test_drop_view_if_exists(self):
table = create_table("table")
view = View("view", select([table.c.id]))
stmt = DropView(view, if_exists=True)
self.assertEqual('DROP VIEW IF EXISTS view', literal_compile(stmt))
def test_drop_view_cascade(self):
table = create_table("table")
view = View("view", select([table.c.id]))
stmt = DropView(view, cascade=True)
self.assertEqual('DROP VIEW view CASCADE', literal_compile(stmt))
uselist=False)
packets = Column(Integer, CheckConstraint('packets >= 0'),
nullable=False)
TrafficVolume.__table__.add_is_dependent_on(IP.__table__)
pmacct_traffic_egress = View(
name='pmacct_traffic_egress',
query=(
Query([])
.add_columns(TrafficVolume.packets.label('packets'),
TrafficVolume.amount.label('bytes'),
TrafficVolume.timestamp.label('stamp_inserted'),
TrafficVolume.timestamp.label('stamp_updated'),
IP.address.label('ip_src'))
.select_from(TrafficVolume)
.filter_by(type='Egress')
.join(IP)
.statement # turns our `Selectable` into something compilable
),
)
ddl.add_view(TrafficVolume.__table__, pmacct_traffic_egress)
pmacct_expression_replacements = dict(
tv_tname=TrafficVolume.__tablename__,
tv_type=TrafficVolume.type.key,
tv_ip_id=TrafficVolume.ip_id.key,
tv_timestamp=TrafficVolume.timestamp.key,
tv_amount=TrafficVolume.amount.key,
from pycroft.model.base import ModelBase
from pycroft.model.ddl import DDLManager, View
class TenancyStatus(Enum):
PROVISIONAL = 1
ESTABLISHED = 2
UNDO_PROVISIONAL = 3
UNDO_FINAL = 4
CANCELED = 5
swdd_view_ddl = DDLManager()
swdd_vo = View(name='swdd_vo',
query=select([text('*')]).select_from(text("swdd.swdd_vo")),
materialized=True)
swdd_view_ddl.add_view(ModelBase.metadata, swdd_vo)
class RentalObject(DeferredReflection, ModelBase):
__tablename__ = 'swdd_vo'
__table_args__ = {'info': dict(is_view=True)}
vo_id = Column(Integer, primary_key=True)
suchname = Column(String)
name = Column(String)
voart_id = Column(Integer)
nutzungsart_id = Column(Integer)
nutzbarvon = Column(Date)
nutzbarbis = Column(Date)
def test_plain_create_view(table):
view = View("view", select(table.c.id))
stmt = CreateView(view)
assert literal_compile(stmt) == ('CREATE VIEW view AS SELECT "table".id \n'
'FROM "table"')