class UserToUserGroup(DeclarativeBase, TimeStamp):
__tablename__ = "user_to_usergroup"
user_id = C(FK(User.id, ondelete="CASCADE"), primary_key=True)
usergroup_id = C(FK(UserGroup.id, ondelete="CASCADE"), primary_key=True)
managed_by_authenticator = C(Boolean, server_default="false")
private = C(Boolean, server_default="false")
user = rel(User,
backref=bref("group_assocs",
passive_deletes=True,
cascade="all, delete-orphan"))
usergroup = rel(UserGroup,
backref=bref("user_assocs",
passive_deletes=True,
cascade="all, delete-orphan"))
__table_args__ = (
# This exclude constraint is something like a unique constraint only for rows where private is true.
# Postgres doesn't support WHERE for unique constraints (why?), so lets just use this.
# Alternatively, we could use a unique partial index to enforce the constraint.
ExcludeConstraint((user_id, "="),
using="btree",
where="private = true",
name="only_one_private_group_per_user"),
ExcludeConstraint((usergroup_id, "="),
using="btree",
where="private = true",
name="only_one_user_per_private_group"),
# XXX: missing constraint: groups cannot be used elsewhere if they are private
)
class ScheduleTable(BaseTable):
"""
Houses the schedules of users.
"""
__tablename__ = 'schedules'
# Foreign keys.
user_id = db.Column(
db.String(36),
db.ForeignKey('users.public_id'),
nullable=False
)
# Actual schedule stuff.
utc_duration = db.Column(TSRANGE, nullable=False)
local_duration = db.Column(TSTZRANGE, nullable=False)
day_number = db.Column(db.SmallInteger, nullable=False)
month_number = db.Column(db.SmallInteger, nullable=False)
# tz stuff
local_tz = db.Column(db.String, nullable=False)
ExcludeConstraint(('utc_duration', '&&'))
ExcludeConstraint(('local_duration', '&&'))
@property
def local_tz_open(self): return self.local_duration.lower
@property
def local_tz_end(self): return self.local_duration.upper
@property
def utc_open(self): return self.utc_duration.lower
@property
def utc_end(self): return self.utc_duration.upper
def __init__(self, open_date, end_date, user_id, local_tz):
super().__init__()
self.utc_duration = DateTimeRange(open_date, end_date)
self.local_duration = DateTimeTZRange(
self._localize(self.utc_duration.lower, local_tz),
self._localize(self.utc_duration.upper, local_tz),
)
self.day_number = open_date.day
self.month_number = open_date.month
self.local_tz = local_tz
self.user_id = str(user_id)
def __repr__(self):
return 'Open: {0} -> End: {1} -- For User: {2}'.format(
self.utc_open,
self.utc_end,
self.user_id
)
def test_exclude_constraint_copy(self):
m = MetaData()
cons = ExcludeConstraint(("room", "="))
tbl = Table("testtbl", m, Column("room", Integer, primary_key=True), cons)
# apparently you can't copy a ColumnCollectionConstraint until
# after it has been bound to a table...
cons_copy = cons.copy()
tbl.append_constraint(cons_copy)
self.assert_compile(
schema.AddConstraint(cons_copy), "ALTER TABLE testtbl ADD EXCLUDE USING gist " "(room WITH =)"
)
def test_exclude_constraint_copy(self):
m = MetaData()
cons = ExcludeConstraint(('room', '='))
tbl = Table('testtbl', m, Column('room', Integer, primary_key=True),
cons)
# apparently you can't copy a ColumnCollectionConstraint until
# after it has been bound to a table...
cons_copy = cons.copy()
tbl.append_constraint(cons_copy)
self.assert_compile(
schema.AddConstraint(cons_copy),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room WITH =)')
def test_exclude_constraint_copy(self):
m = MetaData()
cons = ExcludeConstraint(('room', '='))
tbl = Table('testtbl', m,
Column('room', Integer, primary_key=True),
cons)
# apparently you can't copy a ColumnCollectionConstraint until
# after it has been bound to a table...
cons_copy = cons.copy()
tbl.append_constraint(cons_copy)
self.assert_compile(schema.AddConstraint(cons_copy),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room WITH =)',
dialect=postgresql.dialect())
class Cart(Base):
"""
To store the items in cart
"""
__table_args__ = (
UniqueConstraint('item_id', 'user_id', name='_item_user_uc'),
ExcludeConstraint(
(Column('user_id'), '='),
(Column('shop_id'), '<>'),
),
)
"""Exclude constraint is used which enforce the rule that a user in a cart
can contain only one shop. See here: https://www.postgresql.org/docs/9.2/btree-gist.html#AEN146719
and, https://stackoverflow.com/a/51247705/10305905
Unique constraint is added to enforce that a user cannot add two rows with same item_id
"""
id = Column(UUID(as_uuid=True), default=uuid4, primary_key=True, index=True)
item_id = Column(Integer, ForeignKey('items.id'), nullable=False, index=True)
shop_id = Column(String(length=100), ForeignKey('shops.id'), nullable=False)
item_quantity = Column(Integer, default=1, nullable=False)
user_id = Column(Integer, ForeignKey('user.id'), nullable=False, index=True)
def test_reflection_with_exclude_constraint(self):
m = self.metadata
Table(
"t",
m,
Column("id", Integer, primary_key=True),
Column("period", TSRANGE),
ExcludeConstraint(("period", "&&"), name="quarters_period_excl"),
)
m.create_all()
insp = inspect(testing.db)
# PostgreSQL will create an implicit index for an exclude constraint.
# we don't reflect the EXCLUDE yet.
eq_(
insp.get_indexes("t"),
[{
"unique": False,
"name": "quarters_period_excl",
"duplicates_constraint": "quarters_period_excl",
"dialect_options": {
"postgresql_using": "gist"
},
"column_names": ["period"],
}],
)
# reflection corrects for the dupe
reflected = Table("t", MetaData(testing.db), autoload=True)
eq_(set(reflected.indexes), set())
class Structure(Base):
id = UUIDPKColumn()
name = Column(String, nullable=False)
# ase_structure = Column(JSONB, nullable=False)
ase_structure = Column(Text, nullable=False)
replaced_by_id = Column(UUID(as_uuid=True), ForeignKey('structure.id'))
replaced_by = relationship("Structure", remote_side=[id], lazy='joined', join_depth=2)
# this is also required to make the cascade work when deleting a structure
replaced = relationship("Structure", remote_side=[replaced_by_id], lazy='noload')
def __repr__(self):
return "<Structure(id='{}', name='{}')>".format(self.id, self.name)
def __str__(self):
if self.replaced_by:
return "{} (replaced)".format(self.name)
return self.name
__table_args__ = (
# ensure that the name is unique amongst non-replaced structures,
# and defer constraint to end of transaction to be able to replace
# structures within one transaction
ExcludeConstraint(
('name', '='),
using='btree',
where=replaced_by_id == null(),
deferrable=True, initially='DEFERRED'),
)
def test_inline_exclude_constraint(self):
from sqlalchemy.dialects.postgresql import ExcludeConstraint
autogen_context = self.autogen_context
m = MetaData()
t = Table(
"t",
m,
Column("x", String),
Column("y", String),
ExcludeConstraint(
(column("x"), ">"),
using="gist",
where="x != 2",
name="t_excl_x",
),
)
op_obj = ops.CreateTableOp.from_table(t)
eq_ignore_whitespace(
autogenerate.render_op_text(autogen_context, op_obj),
"op.create_table('t',sa.Column('x', sa.String(), nullable=True),"
"sa.Column('y', sa.String(), nullable=True),"
"postgresql.ExcludeConstraint((sa.column('x'), '>'), "
"where=sa.text(!U'x != 2'), using='gist', name='t_excl_x')"
")",
)
def test_add_exclude_constraint_case_sensitive(self):
from sqlalchemy.dialects.postgresql import ExcludeConstraint
autogen_context = self.autogen_context
m = MetaData()
t = Table(
"TTAble", m, Column("XColumn", String), Column("YColumn", String)
)
op_obj = ops.AddConstraintOp.from_constraint(
ExcludeConstraint(
(t.c.XColumn, ">"),
where=t.c.XColumn != 2,
using="gist",
name="t_excl_x",
)
)
eq_ignore_whitespace(
autogenerate.render_op_text(autogen_context, op_obj),
"op.create_exclude_constraint('t_excl_x', 'TTAble', "
"(sa.column('XColumn'), '>'), "
"where=sa.text(!U'\"XColumn\" != 2'), using='gist')",
)
def test_exclude_const_unchanged(self):
from sqlalchemy.dialects.postgresql import TSRANGE, ExcludeConstraint
m1 = MetaData()
m2 = MetaData()
Table('add_excl', m1, Column('id', Integer, primary_key=True),
Column('period', TSRANGE),
ExcludeConstraint(('period', '&&'), name='quarters_period_excl'))
Table('add_excl', m2, Column('id', Integer, primary_key=True),
Column('period', TSRANGE),
ExcludeConstraint(('period', '&&'), name='quarters_period_excl'))
diffs = self._fixture(m1, m2)
eq_(diffs, [])
def _add_check_constraints(cls, e_type, ETW, sa_table):
cls.__m_super._add_check_constraints(e_type, ETW, sa_table)
own_names = e_type._Predicates._own_names
QR = ETW.Q_Result
QX = QR.QX
for pk in e_type.P_exclusion:
if pk.name in own_names:
pred = pk.pred
columns = []
tables = set((sa_table, ))
for qs in pred.aqs:
qx = QX.Mapper(QR)(qs)
op = qx.exclude_constraint_op
columns.extend((c, op) for c in qx.XS_ATTR)
for join in qx.JOINS:
tables.update(j.table for j in join)
if len(tables) == 1:
if pk.auto_index:
for c, op in columns:
idx_name = "__".join(
[sa_table.name, c.name, "gist"])
SA.schema.Index(idx_name,
c,
postgresql_using='gist')
c_name = "__".join([sa_table.name, pk.name])
exclude = ExcludeConstraint(*columns, name=c_name)
sa_table.append_constraint(exclude)
pred.ems_check = False
class RoomHistoryEntry(IntegerIdModel):
active_during: Interval = Column(TsTzRange, nullable=False)
def disable(self, at=None):
if at is None:
at = object_session(self).scalar(select(func.current_timestamp()))
self.active_during = self.active_during - closedopen(at, None)
flag_modified(self, 'active_during')
room_id = Column(Integer, ForeignKey("room.id", ondelete="CASCADE"),
nullable=False, index=True)
room = relationship(Room, backref=backref(name="room_history_entries",
order_by='RoomHistoryEntry.id',
viewonly=True))
user_id = Column(Integer, ForeignKey(User.id, ondelete="CASCADE"),
nullable=False, index=True)
user = relationship(User, backref=backref("room_history_entries",
order_by='RoomHistoryEntry.id',
viewonly=True))
__table_args__ = (
Index('ix_room_history_entry_active_during', 'active_during', postgresql_using='gist'),
ExcludeConstraint( # there should be no two room_history_entries
(room_id, '='), # …in the same room
(user_id, '='), # …and for the same user
(active_during, '&&'), # …and overlapping durations
using='gist'
),
)
def test_inline_exclude_constraint_case_sensitive(self):
from sqlalchemy.dialects.postgresql import ExcludeConstraint
autogen_context = self.autogen_context
m = MetaData()
t = Table(
"TTable", m, Column("XColumn", String), Column("YColumn", String)
)
ExcludeConstraint(
(t.c.XColumn, ">"),
using="gist",
where='"XColumn" != 2',
name="TExclX",
)
op_obj = ops.CreateTableOp.from_table(t)
eq_ignore_whitespace(
autogenerate.render_op_text(autogen_context, op_obj),
"op.create_table('TTable',sa.Column('XColumn', sa.String(), "
"nullable=True),"
"sa.Column('YColumn', sa.String(), nullable=True),"
"postgresql.ExcludeConstraint((sa.column('XColumn'), '>'), "
"where=sa.text(!U'\"XColumn\" != 2'), using='gist', "
"name='TExclX'))",
)
class Membership(IntegerIdModel):
active_during: Interval = Column(TsTzRange, nullable=False)
def disable(self, at=None):
if at is None:
at = object_session(self).scalar(select(func.current_timestamp()))
self.active_during = self.active_during - closedopen(at, None)
flag_modified(self, 'active_during')
# many to one from Membership to Group
group_id = Column(Integer, ForeignKey(Group.id, ondelete="CASCADE"),
nullable=False, index=True)
group = relationship(Group, backref=backref("memberships",
cascade="all, delete-orphan",
order_by='Membership.id',
cascade_backrefs=False))
# many to one from Membership to User
user_id = Column(Integer, ForeignKey(User.id, ondelete="CASCADE"),
nullable=False, index=True)
user = relationship(User, backref=backref("memberships",
cascade="all, delete-orphan",
cascade_backrefs=False))
__table_args__ = (
Index('ix_membership_active_during', 'active_during', postgresql_using='gist'),
ExcludeConstraint( # there should be no two memberships…
(group_id, '='), # …with the same user
(user_id, '='), # …and the same group
(active_during, '&&'), # …and overlapping durations
using='gist'
),
)
def test_reflection_with_exclude_constraint(self):
m = self.metadata
Table(
't', m,
Column('id', Integer, primary_key=True),
Column('period', TSRANGE),
ExcludeConstraint(('period', '&&'), name='quarters_period_excl')
)
m.create_all()
insp = inspect(testing.db)
# PostgreSQL will create an implicit index for an exclude constraint.
# we don't reflect the EXCLUDE yet.
eq_(
insp.get_indexes('t'),
[{'unique': False, 'name': 'quarters_period_excl',
'duplicates_constraint': 'quarters_period_excl',
'dialect_options': {'postgresql_using': 'gist'},
'column_names': ['period']}]
)
# reflection corrects for the dupe
reflected = Table('t', MetaData(testing.db), autoload=True)
eq_(set(reflected.indexes), set())
def test_exclude_constraint_text(self):
m = MetaData()
cons = ExcludeConstraint((text('room::TEXT'), '='))
Table('testtbl', m, Column('room', String), cons)
self.assert_compile(
schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room::TEXT WITH =)')
def test_exclude_constraint_cast_quote(self):
m = MetaData()
tbl = Table('testtbl', m, Column('Room', String))
cons = ExcludeConstraint((cast(tbl.c.Room, Text), '='))
tbl.append_constraint(cons)
self.assert_compile(
schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(CAST("Room" AS TEXT) WITH =)')
def test_exclude_constraint_min(self):
m = MetaData()
tbl = Table('testtbl', m, Column('room', Integer, primary_key=True))
cons = ExcludeConstraint(('room', '='))
tbl.append_constraint(cons)
self.assert_compile(schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room WITH =)',
dialect=postgresql.dialect())
def test_exclude_constraint_when(self):
m = MetaData()
tbl = Table('testtbl', m, Column('room', String))
cons = ExcludeConstraint(('room', '='), where=tbl.c.room.in_(['12']))
tbl.append_constraint(cons)
self.assert_compile(schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room WITH =) WHERE (testtbl.room IN (\'12\'))',
dialect=postgresql.dialect())
def __table_args__(cls):
return (ExcludeConstraint(
(cls.native_transaction_id, '='), (sa.func.tsrange(
cls.issued_at - sa.text("INTERVAL '1 hour'"),
cls.issued_at,
), '&&'),
name='transaction_unique_native_tx_id'), {
'schema': schema
})
def to_constraint(self, migration_context=None):
if self._orig_constraint is not None:
return self._orig_constraint
schema_obj = schemaobj.SchemaObjects(migration_context)
t = schema_obj.table(self.table_name, schema=self.schema)
excl = ExcludeConstraint(*self.elements,
name=self.constraint_name,
where=self.where,
**self.kw)
for expr, name, oper in excl._render_exprs:
t.append_column(Column(name, NULLTYPE))
t.append_constraint(excl)
return excl
class Rate(Base):
"""
Model class to represent a pricing rate in the system.
Uses a unique constraint on the combination of the start date and the end
date to prevent duplicated rates. It also uses a check constraint to ensure
the start date is always before in time than the end date of the affected
period. Finally, it uses an exclude constraint on the start date and the
end date to prevent dates from overlapping.
"""
__tablename__ = 'rate'
__rels__ = []
__table_args__ = (
UniqueConstraint(
'date_from', 'date_to', name='rate_date_from_date_to'),
CheckConstraint('date_from < date_to'),
# Prevent dates from overlapping by using an exclusion constraint and
# the overlap operator (&&) for the daterange type
ExcludeConstraint((Column(
quoted_name('daterange(date_from, date_to)', quote=False)), '&&'),
using='gist',
name='rate_date_range'),
)
id = Column(Integer, primary_key=True)
"""Primary key. Autoincrementing integer."""
date_from = Column(Date, nullable=False)
"""Start date of the affected period of time."""
date_to = Column(Date, nullable=False)
"""End date of the affected period of time."""
base_price = Column(Float, default=0)
"""Base price for this rate to be used in calculations. Defaults to 0."""
bed_price = Column(Float, default=0)
"""The price per bed to be added to the base price. Defaults to 0."""
published = Column(Boolean, default=False)
"""Whether this rate is active or inactive. Defaults to False."""
@hybrid_property
def days(self):
"""The number of days in the date range of this rate."""
return (self.date_to - self.date_from).days
@days.expression
def days(cls):
return func.date_part('day', func.age(cls.date_to, cls.date_from))
def __repr__(self):
"""String representation of the object."""
return "<Rate(id='%s', date_from='%s', date_to='%s', published='%s')>" % (
self.id, self.date_from, self.date_to, self.published)
def test_exclude_const_unchanged(self):
from sqlalchemy.dialects.postgresql import TSRANGE, ExcludeConstraint
m1 = MetaData()
m2 = MetaData()
Table(
"add_excl",
m1,
Column("id", Integer, primary_key=True),
Column("period", TSRANGE),
ExcludeConstraint(("period", "&&"), name="quarters_period_excl"),
)
Table(
"add_excl",
m2,
Column("id", Integer, primary_key=True),
Column("period", TSRANGE),
ExcludeConstraint(("period", "&&"), name="quarters_period_excl"),
)
diffs = self._fixture(m1, m2)
eq_(diffs, [])
def to_constraint(self, migration_context=None):
if not util.sqla_100:
raise NotImplementedError(
"ExcludeConstraint not supported until SQLAlchemy 1.0")
if self._orig_constraint is not None:
return self._orig_constraint
schema_obj = schemaobj.SchemaObjects(migration_context)
t = schema_obj.table(self.table_name, schema=self.schema)
excl = ExcludeConstraint(*self.elements,
name=self.constraint_name,
where=self.where,
**self.kw)
for expr, name, oper in excl._render_exprs:
t.append_column(Column(name, NULLTYPE))
t.append_constraint(excl)
return excl
class Usage(Base):
__tablename__ = 'usage'
id_ = Column(Integer, Sequence('usage_id_seq'), primary_key=True)
resource_id = Column(String)
# tenant = Column(String, nullable=False)
tenant_id = Column(String)
volume = Column(String, nullable=False)
time = Column(TSRange, nullable=False)
created = Column(types.DateTime, nullable=False)
__table_args__ = (
ExcludeConstraint(('tenant_id', '='), ('resource_id', '='),
('time', '&&')),
ForeignKeyConstraint(["resource_id", "tenant_id"],
["resources.id", "resources.tenant_id"],
name="fk_resource",
use_alter=True),
)
resource = relationship(Resource, primaryjoin=resource_id == Resource.id)
tenant = relationship(Resource,
primaryjoin=tenant_id == Resource.tenant_id)
# resource = relationship("Resource", backref=backref("resources", order_by=created))
# tenant = relationship("Tenant", backref=backref("usage", order_by=created))
def __init__(self, resource, tenant, value, start, end):
assert start < end
assert isinstance(start, datetime.datetime)
assert isinstance(end, datetime.datetime)
assert resource.tenant is not None
assert tenant is not None
assert resource.tenant.id == tenant.id
self.resource = resource
self.tenant = resource # Same resource
self.time = "[%s,%s]" % (start, end)
self.created = datetime.datetime.now()
self.volume = value
def test_add_exclude_constraint(self):
from sqlalchemy.dialects.postgresql import ExcludeConstraint
autogen_context = self.autogen_context
m = MetaData()
t = Table('t', m, Column('x', String), Column('y', String))
op_obj = ops.AddConstraintOp.from_constraint(
ExcludeConstraint((t.c.x, ">"),
where=t.c.x != 2,
using="gist",
name="t_excl_x"))
eq_ignore_whitespace(
autogenerate.render_op_text(autogen_context, op_obj),
"op.create_exclude_constraint('t_excl_x', 't', (sa.column('x'), '>'), "
"where=sa.text(!U'x != 2'), using='gist')")
def test_exclude_constraint_full(self):
m = MetaData()
room = Column('room', Integer, primary_key=True)
tbl = Table('testtbl', m, room, Column('during', TSRANGE))
room = Column('room', Integer, primary_key=True)
cons = ExcludeConstraint((room, '='), ('during', '&&'),
name='my_name',
using='gist',
where="room > 100",
deferrable=True,
initially='immediate')
tbl.append_constraint(cons)
self.assert_compile(schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD CONSTRAINT my_name '
'EXCLUDE USING gist '
'(room WITH =, during WITH '
'&&) WHERE '
'(room > 100) DEFERRABLE INITIALLY immediate',
dialect=postgresql.dialect())
def add_constraints_and_attributes(mapper, class_):
if has_inherited_table(class_):
return
table = class_.__table__
if class_.key_columns is not None:
elements = []
for col_name in class_.key_columns:
elements.append((getattr(class_, col_name), '='))
elements.append(('period', '&&'))
table.append_constraint(ExcludeConstraint(*elements))
if class_.value_columns is None:
exclude = {'id', 'period'}
exclude.update(class_.key_columns)
class_.value_columns = [
c.name for c in table.c if c.name not in exclude
]
table.append_constraint(CheckConstraint("period != 'empty'::tsrange"))
def to_constraint(
self,
migration_context: Optional["MigrationContext"] = None
) -> "ExcludeConstraint":
if self._orig_constraint is not None:
return self._orig_constraint
schema_obj = schemaobj.SchemaObjects(migration_context)
t = schema_obj.table(self.table_name, schema=self.schema)
excl = ExcludeConstraint(*self.elements,
name=self.constraint_name,
where=self.where,
**self.kw)
for (
expr,
name,
oper,
) in excl._render_exprs: # type:ignore[attr-defined]
t.append_column(Column(name, NULLTYPE))
t.append_constraint(excl)
return excl
class Quarter(db.Model):
__tablename__ = 'quarters'
id = db.Column(db.Integer, primary_key=True)
year = db.Column(db.Integer, nullable=False)
name = db.Column(db.Text, nullable=False)
period = db.Column(ranges.DATERANGE, nullable=False)
university_id = db.Column(db.Integer,
db.ForeignKey('universities.id'),
nullable=False)
sections = db.relationship('Section', back_populates='quarter')
university = db.relationship('University', back_populates='quarters')
__table_args__ = (
ExcludeConstraint(('period', '&&')),
db.UniqueConstraint('year', 'name', 'university_id'),
db.CheckConstraint(name.in_(['Fall', 'Winter', 'Spring', 'Summer']),
name='valid_quarter'),
)
def to_dict(self):
return {
'id': self.id,
'year': self.year,
'name': self.name,
'period': {
'start': self.period.lower.strftime("%Y-%m-%d"),
'end': self.period.upper.strftime("%Y-%m-%d"),
},
}
@classmethod
def current(cls):
return Quarter.query.filter(
func.upper(Quarter.period) > datetime.now(
pytz.timezone('America/Los_Angeles'))).order_by(
asc(Quarter.period)).limit(1)
def upgrade():
# required for gist-based exclusion constraints
op.execute("CREATE EXTENSION IF NOT EXISTS btree_gist")
# drop the old stuff
op.drop_table("cluster_event_associations")
op.drop_table("event_subscriptions")
op.drop_table("events")
op.create_table(
"events",
sa.Column("id",
sa.BigInteger(),
server_default=sa.text("nextval('communities_seq')"),
nullable=False),
sa.Column("parent_node_id", sa.BigInteger(), nullable=False),
sa.Column("title", sa.String(), nullable=False),
sa.Column("creator_user_id", sa.BigInteger(), nullable=False),
sa.Column("created",
sa.DateTime(timezone=True),
server_default=sa.text("now()"),
nullable=False),
sa.Column("owner_user_id", sa.BigInteger(), nullable=True),
sa.Column("owner_cluster_id", sa.BigInteger(), nullable=True),
sa.Column("thread_id", sa.BigInteger(), nullable=False),
sa.CheckConstraint(
"(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
name=op.f("ck_events_one_owner"),
),
sa.ForeignKeyConstraint(["creator_user_id"], ["users.id"],
name=op.f("fk_events_creator_user_id_users")),
sa.ForeignKeyConstraint(
["owner_cluster_id"], ["clusters.id"],
name=op.f("fk_events_owner_cluster_id_clusters")),
sa.ForeignKeyConstraint(["owner_user_id"], ["users.id"],
name=op.f("fk_events_owner_user_id_users")),
sa.ForeignKeyConstraint(["parent_node_id"], ["nodes.id"],
name=op.f("fk_events_parent_node_id_nodes")),
sa.ForeignKeyConstraint(["thread_id"], ["threads.id"],
name=op.f("fk_events_thread_id_threads")),
sa.PrimaryKeyConstraint("id", name=op.f("pk_events")),
sa.UniqueConstraint("thread_id", name=op.f("uq_events_thread_id")),
)
op.create_index(op.f("ix_events_creator_user_id"),
"events", ["creator_user_id"],
unique=False)
op.create_index(op.f("ix_events_owner_cluster_id"),
"events", ["owner_cluster_id"],
unique=False)
op.create_index(op.f("ix_events_owner_user_id"),
"events", ["owner_user_id"],
unique=False)
op.create_index(op.f("ix_events_parent_node_id"),
"events", ["parent_node_id"],
unique=False)
op.create_table(
"cluster_event_associations",
sa.Column("id", sa.BigInteger(), nullable=False),
sa.Column("event_id", sa.BigInteger(), nullable=False),
sa.Column("cluster_id", sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(
["cluster_id"], ["clusters.id"],
name=op.f("fk_cluster_event_associations_cluster_id_clusters")),
sa.ForeignKeyConstraint(
["event_id"], ["events.id"],
name=op.f("fk_cluster_event_associations_event_id_events")),
sa.PrimaryKeyConstraint("id",
name=op.f("pk_cluster_event_associations")),
sa.UniqueConstraint(
"event_id",
"cluster_id",
name=op.f("uq_cluster_event_associations_event_id")),
)
op.create_index(op.f("ix_cluster_event_associations_cluster_id"),
"cluster_event_associations", ["cluster_id"],
unique=False)
op.create_index(op.f("ix_cluster_event_associations_event_id"),
"cluster_event_associations", ["event_id"],
unique=False)
op.create_table(
"event_occurrences",
sa.Column("id",
sa.BigInteger(),
server_default=sa.text("nextval('communities_seq')"),
nullable=False),
sa.Column("event_id", sa.BigInteger(), nullable=False),
sa.Column("creator_user_id", sa.BigInteger(), nullable=False),
sa.Column("content", sa.String(), nullable=False),
sa.Column("photo_key", sa.String(), nullable=True),
sa.Column(
"geom",
geoalchemy2.types.Geometry(geometry_type="POINT",
srid=4326,
from_text="ST_GeomFromEWKT",
name="geometry"),
nullable=True,
),
sa.Column("address", sa.String(), nullable=True),
sa.Column("link", sa.String(), nullable=True),
sa.Column("during", TSTZRANGE, nullable=False),
sa.Column("created",
sa.DateTime(timezone=True),
server_default=sa.text("now()"),
nullable=False),
sa.Column("last_edited",
sa.DateTime(timezone=True),
server_default=sa.text("now()"),
nullable=False),
sa.ForeignKeyConstraint(
["creator_user_id"], ["users.id"],
name=op.f("fk_event_occurrences_creator_user_id_users")),
sa.CheckConstraint(
"(geom IS NULL) = (address IS NULL)",
name=op.f("ck_event_occurrences_geom_iff_address"),
),
sa.CheckConstraint(
"(geom IS NULL) <> (link IS NULL)",
name=op.f("ck_event_occurrences_link_or_geom"),
),
sa.ForeignKeyConstraint(
["event_id"], ["events.id"],
name=op.f("fk_event_occurrences_event_id_events")),
sa.ForeignKeyConstraint(
["photo_key"], ["uploads.key"],
name=op.f("fk_event_occurrences_photo_key_uploads")),
sa.PrimaryKeyConstraint("id", name=op.f("pk_event_occurrences")),
ExcludeConstraint(("event_id", "="), ("during", "&&"),
name="event_occurrences_event_id_during_excl"),
)
op.create_index(op.f("ix_event_occurrences_event_id"),
"event_occurrences", ["event_id"],
unique=False)
op.create_index(op.f("ix_event_occurrences_creator_user_id"),
"event_occurrences", ["creator_user_id"],
unique=False)
op.create_table(
"event_organizers",
sa.Column("id", sa.BigInteger(), nullable=False),
sa.Column("user_id", sa.BigInteger(), nullable=False),
sa.Column("event_id", sa.BigInteger(), nullable=False),
sa.Column("joined",
sa.DateTime(timezone=True),
server_default=sa.text("now()"),
nullable=False),
sa.ForeignKeyConstraint(
["event_id"], ["events.id"],
name=op.f("fk_event_organizers_event_id_events")),
sa.ForeignKeyConstraint(
["user_id"], ["users.id"],
name=op.f("fk_event_organizers_user_id_users")),
sa.PrimaryKeyConstraint("id", name=op.f("pk_event_organizers")),
sa.UniqueConstraint("event_id",
"user_id",
name=op.f("uq_event_organizers_event_id")),
)
op.create_index(op.f("ix_event_organizers_event_id"),
"event_organizers", ["event_id"],
unique=False)
op.create_index(op.f("ix_event_organizers_user_id"),
"event_organizers", ["user_id"],
unique=False)
op.create_table(
"event_subscriptions",
sa.Column("id", sa.BigInteger(), nullable=False),
sa.Column("user_id", sa.BigInteger(), nullable=False),
sa.Column("event_id", sa.BigInteger(), nullable=False),
sa.Column("joined",
sa.DateTime(timezone=True),
server_default=sa.text("now()"),
nullable=False),
sa.ForeignKeyConstraint(
["event_id"], ["events.id"],
name=op.f("fk_event_subscriptions_event_id_events")),
sa.ForeignKeyConstraint(
["user_id"], ["users.id"],
name=op.f("fk_event_subscriptions_user_id_users")),
sa.PrimaryKeyConstraint("id", name=op.f("pk_event_subscriptions")),
sa.UniqueConstraint("event_id",
"user_id",
name=op.f("uq_event_subscriptions_event_id")),
)
op.create_index(op.f("ix_event_subscriptions_event_id"),
"event_subscriptions", ["event_id"],
unique=False)
op.create_index(op.f("ix_event_subscriptions_user_id"),
"event_subscriptions", ["user_id"],
unique=False)
op.create_table(
"event_occurrence_attendees",
sa.Column("id", sa.BigInteger(), nullable=False),
sa.Column("user_id", sa.BigInteger(), nullable=False),
sa.Column("occurrence_id", sa.BigInteger(), nullable=False),
sa.Column("responded",
sa.DateTime(timezone=True),
server_default=sa.text("now()"),
nullable=False),
sa.Column("attendee_status",
sa.Enum("going", "maybe", name="attendeestatus"),
nullable=False),
sa.ForeignKeyConstraint(
["occurrence_id"],
["event_occurrences.id"],
name=op.
f("fk_event_occurrence_attendees_occurrence_id_event_occurrences"),
),
sa.ForeignKeyConstraint(
["user_id"], ["users.id"],
name=op.f("fk_event_occurrence_attendees_user_id_users")),
sa.PrimaryKeyConstraint("id",
name=op.f("pk_event_occurrence_attendees")),
sa.UniqueConstraint(
"occurrence_id",
"user_id",
name=op.f("uq_event_occurrence_attendees_occurrence_id")),
)
op.create_index(
op.f("ix_event_occurrence_attendees_occurrence_id"),
"event_occurrence_attendees",
["occurrence_id"],
unique=False,
)
op.create_index(op.f("ix_event_occurrence_attendees_user_id"),
"event_occurrence_attendees", ["user_id"],
unique=False)
# fix up constraints
op.create_check_constraint(
"geom_iff_address",
"page_versions",
"(geom IS NULL) = (address IS NULL)",
)
# "references" is a postgres keyword so need to quote it
op.execute(
'ALTER TABLE "references" DROP CONSTRAINT ck_references_host_request_id_xor_friend_reference'
)
op.create_check_constraint(
"host_request_id_xor_friend_reference",
"references",
"(host_request_id IS NOT NULL) <> (reference_type = 'friend')",
)
op.execute("ALTER TABLE pages DROP CONSTRAINT ck_pages_one_owner")
op.create_check_constraint(
"one_owner",
"pages",
"(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)",
)
