def show_artist(artist_id):
data = {}
artist = Artist.query.get(artist_id)
if artist is None:
flash('There is no artist with enterd ID, Try with another ID')
return redirect(url_for('index'))
data["id"] = artist.id
data["name"] = artist.name
data["city"] = artist.city
data["state"] = artist.state
data["phone"] = artist.phone
data["facebook_link"] = artist.facebook_link
data["image_link"] = artist.image_link
geners = [gener for gener in Artist.query.get(artist_id).geners]
data["genres"] = geners
print(geners)
past_shows = db.session.query(
Venue.name.label('venue_name'), Venue.id.label('venue_id'),
Venue.image_link.label('venue_image_link'),
Shows.date.label('start_time')).join(Venue).filter(
Shows.date < func.current_date(), Shows.artist_id == artist_id)
upcoming_shows = db.session.query(
Venue.name.label('venue_name'), Venue.id.label('venue_id'),
Venue.image_link.label('venue_image_link'),
Shows.date.label('start_time')).join(Venue).filter(
Shows.date >= func.current_date(), Shows.artist_id == artist_id)
data["past_shows"] = list(past_shows.all())
data["past_shows_count"] = past_shows.count()
data["upcoming_shows"] = list(upcoming_shows.all())
data["upcoming_shows_count"] = upcoming_shows.count()
return render_template('pages/show_artist.html', artist=data)
def show_venue(venue_id):
data = {}
venue = Venue.query.get(venue_id)
if venue is None:
flash('There is no venue with enterd ID, Try with another ID')
return redirect(url_for('index'))
data["id"] = venue.id
data["name"] = venue.name
data["address"] = venue.address
data["city"] = venue.city
data["state"] = venue.state
data["phone"] = venue.phone
data["facebook_link"] = venue.facebook_link
data["image_link"] = venue.image_link
geners = [gener for gener in Venue.query.get(venue_id).geners]
data["genres"] = geners
past_shows = db.session.query(
Artist.name.label('artist_name'), Artist.id.label('artist_id'),
Artist.image_link.label('artist_image_link'),
Shows.date.label('start_time')).join(Artist).filter(
Shows.date < func.current_date(), Shows.venue_id == venue_id)
upcoming_shows = db.session.query(
Artist.name.label('artist_name'), Artist.id.label('artist_id'),
Artist.image_link.label('artist_image_link'),
Shows.date.label('start_time')).join(Artist).filter(
Shows.date >= func.current_date(), Shows.venue_id == venue_id)
data["past_shows"] = list(past_shows.all())
data["past_shows_count"] = past_shows.count()
data["upcoming_shows"] = list(upcoming_shows.all())
data["upcoming_shows_count"] = upcoming_shows.count()
return render_template('pages/show_venue.html', venue=data)
def search_artists():
# TODO: implement search on artists with partial string search. Ensure it is case-insensitive.
# seach for "A" should return "Guns N Petals", "Matt Quevado", and "The Wild Sax Band".
# search for "band" should return "The Wild Sax Band".
search_term = request.form["search_term"]
print(search_term)
shows_subquery = db.session.query(
Shows.artist_id,
func.count('*').label('num_upcoming_shows')).filter(
Shows.date >= func.current_date()).group_by(
Shows.artist_id).subquery()
artists_data = db.session.query(
Artist.name, Artist.id, shows_subquery.c.num_upcoming_shows).filter(
Artist.name.ilike('%' + search_term + '%')).outerjoin(
shows_subquery, Artist.id == shows_subquery.c.artist_id)
response = {}
artists_list = []
response["count"] = artists_data.count()
for artist in artists_data.all():
artists_list.append(artist)
response["data"] = artists_list
# response={
# "count": 1,
# "data": [{
# "id": 4,
# "name": "Guns N Petals",
# "num_upcoming_shows": 0,
# }]
# }
return render_template('pages/search_artists.html',
results=response,
search_term=request.form.get('search_term', ''))
def search_venues():
# TODO: implement search on artists with partial string search. Ensure it is case-insensitive.
# seach for Hop should return "The Musical Hop".
# search for "Music" should return "The Musical Hop" and "Park Square Live Music & Coffee"
search_term = request.form["search_term"]
print(search_term)
shows_subquery = db.session.query(
Shows.venue_id,
func.count('*').label('num_upcoming_shows')).filter(
Shows.date >= func.current_date()).group_by(
Shows.venue_id).subquery()
venues_data = db.session.query(
Venue.name, Venue.id, shows_subquery.c.num_upcoming_shows).filter(
Venue.name.ilike('%' + search_term + '%')).outerjoin(
shows_subquery, Venue.id == shows_subquery.c.venue_id)
response = {}
venue_list = []
response["count"] = venues_data.count()
for venue in venues_data.all():
venue_list.append(venue)
response["data"] = venue_list
# response={
# "count": 1,
# "data": [{
# "id": 2,
# "name": "The Dueling Pianos Bar",
# "num_upcoming_shows": 0,
# }]
# }
return render_template('pages/search_venues.html',
results=response,
search_term=request.form.get('search_term', ''))
class Entry(db.Model):
"""Entry model class
Each user has three rows on entries for a date - done, todo, blocking.
"""
__tablename__ = 'entries'
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.String)
type = db.Column(db.Enum('done', 'todo', 'blocking', name='entry_types'))
date = db.Column(db.Date, server_default=func.current_date())
time_created = db.Column(db.DateTime,
server_default=func.current_timestamp())
time_updated = db.Column(db.DateTime, onupdate=func.current_timestamp())
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
user = db.relationship('User',
backref=db.backref('entries', lazy='dynamic'))
__table_args__ = (db.UniqueConstraint('type',
'user_id',
'date',
name='_type_user_date_uc'), )
def __init__(self, text, entry_type, user, date=None):
self.text = text
self.type = entry_type
self.user = user
if date:
self.date = date
def __repr__(self):
return '<Entry {}:{}:{}'.format(self.user.name, self.type,
self.text[:50])
def venues():
# TODO: replace with real venues data.
# num_shows should be aggregated based on number of upcoming shows per venue.
# using https://docs.sqlalchemy.org/en/13/orm/tutorial.html
venues_cities = db.session.query(Venue.city, Venue.state).order_by(
Venue.state).distinct().all()
# print('-------------------------------------')
# print(venues_cities)
data = []
print('-------------------------------------')
for city, state in venues_cities:
sub_dict = {}
sub_dict["city"] = city
sub_dict["state"] = state
venue_list = []
shows_subquery = db.session.query(
Shows.venue_id,
func.count('*').label('num_upcoming_shows')).filter(
Shows.date >= func.current_date()).group_by(
Shows.venue_id).subquery()
venues_data = db.session.query(
Venue.id, Venue.name, Venue.city, Venue.state,
shows_subquery.c.num_upcoming_shows).outerjoin(
shows_subquery, Venue.id == shows_subquery.c.venue_id).filter(
Venue.city == city,
Venue.state == state).order_by(Venue.state,
Venue.city).all()
for v in venues_data:
venue_list.append(v)
print(venue_list, '----------------')
sub_dict["venues"] = venue_list
data.append(dict(sub_dict))
# print(data)
# data=[
# {
# "city": "San Francisco",
# "state": "CA",
# "venues": [{
# "id": 1,
# "name": "The Musical Hop",
# "num_upcoming_shows": 0,
# }, {
# "id": 3,
# "name": "Park Square Live Music & Coffee",
# "num_upcoming_shows": 1,
# }]
# }, {
# "city": "New York",
# "state": "NY",
# "venues": [{
# "id": 2,
# "name": "The Dueling Pianos Bar",
# "num_upcoming_shows": 0,
# }]
# }
# ]
return render_template('pages/venues.html', areas=data)
class Vacation(db.Model):
id = db.Column(db.Integer, primary_key=True)
vacation_date = db.Column(db.String(50), default=func.current_date())
user_id = db.Column(
db.Integer, db.ForeignKey("user.id", ondelete="CASCADE"), nullable=False
)
def __repr__(self):
return f"Vacation day in {self.vacation_date}"
class Rate(db.Model):
__table_args__ = (
UniqueConstraint('currency_id', 'date', name='_ticker__date'),
)
id = db.Column(db.Integer, primary_key=True)
currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'), nullable=False)
date = db.Column(db.Date, nullable=False, server_default=func.current_date())
rate = db.Column(db.Float(decimal_return_scale=2), nullable=False)
volume = db.Column(db.Float(decimal_return_scale=2), nullable=False)
class Project(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False, unique=True)
shortcut = db.Column(db.String(50), nullable=False, unique=True)
start_date = db.Column(db.String(50), nullable=False, default=func.current_date())
end_date = db.Column(db.String(50), nullable=False)
phase = db.Column(db.String(50), nullable=False)
active = db.Column(db.Boolean, default=True)
hour = db.relationship("Hour", backref="project", passive_deletes=True)
def __repr__(self):
return f"Project: {self.name}"
class Hour(db.Model):
id = db.Column(db.Integer, primary_key=True)
amount = db.Column(db.Integer, nullable=False)
work_date = db.Column(db.String(50), default=func.current_date())
project_shortcut = db.Column(db.String(50),
db.ForeignKey("project.shortcut"),
nullable=False)
user_id = db.Column(db.Integer,
db.ForeignKey("user.id", ondelete="CASCADE"),
nullable=False)
def __repr__(self):
return f"{self.amount} hours"
class Telemetry(Base):
id = Column(Integer, primary_key=True, index=True)
speed = Column(Float, nullable=False)
distance = Column(Float, nullable=False)
engine_temp = Column(Float, nullable=False)
creation_time = Column(DateTime, default=func.current_date(), nullable=False)
energy_cons = Column(Float, nullable=True)
rpm = Column(Integer, nullable=False)
battery = Column(Integer, nullable=True)
avg_speed= Column(Float, nullable=False)
round_id = Column(Integer, ForeignKey("round.id"))
rounds = relationship(Round, primaryjoin=round_id == Round.id)
latitude = Column(Float, nullable=False)
longitude = Column(Float, nullable=False)
def search_venues():
search_term = request.form["search_term"]
shows_subquery = db.session.query(
Shows.venue_id,
func.count('*').label('num_upcoming_shows')).filter(
Shows.date >= func.current_date()).group_by(
Shows.venue_id).subquery()
venues_data = db.session.query(
Venue.name, Venue.id, shows_subquery.c.num_upcoming_shows).filter(
Venue.name.ilike('%' + search_term + '%')).outerjoin(
shows_subquery, Venue.id == shows_subquery.c.venue_id)
response = {}
response["count"] = venues_data.count()
venue_list = [venue for venue in venues_data]
response["data"] = venue_list
return render_template('pages/search_venues.html',
results=response,
search_term=request.form.get('search_term', ''))
class Post(db.Model):
# pylint: disable=E1101
__tableneme__ = "post"
iD = db.Column(db.String, primary_key=True)
created_date = db.Column(db.DateTime(timezone=False),
server_default=func.now())
updated_date = db.Column(db.String, server_default=func.current_date())
author = db.Column(db.String, nullable=False)
html = db.Column(db.LargeBinary, nullable=False)
title = db.Column(db.String, nullable=False)
s_title = db.Column(db.String, nullable=False)
color = db.Column(db.String, nullable=False)
date = db.Column(db.String, nullable=False)
token = db.Column(db.String, nullable=False)
icon = db.Column(db.LargeBinary, nullable=True)
ver = db.Column(db.Integer, nullable=False, default=0)
def __repr__(self):
return "<post iD:{},title:{}>".format(self.iD, self.title)
def search_artists():
search_term = request.form["search_term"]
shows_subquery = db.session.query(
Shows.artist_id,
func.count('*').label('num_upcoming_shows')).filter(
Shows.date >= func.current_date()).group_by(
Shows.artist_id).subquery()
artists_data = db.session.query(
Artist.name, Artist.id, shows_subquery.c.num_upcoming_shows).filter(
Artist.name.ilike('%' + search_term + '%')).outerjoin(
shows_subquery, Artist.id == shows_subquery.c.artist_id)
response = {}
artists_list = []
response["count"] = artists_data.count()
for artist in artists_data.all():
artists_list.append(artist)
response["data"] = artists_list
return render_template('pages/search_artists.html',
results=response,
search_term=request.form.get('search_term', ''))
def venues():
venues_cities = db.session.query(Venue.city, Venue.state).order_by(
Venue.state).distinct().all()
data = []
for city, state in venues_cities:
sub_dict = {}
sub_dict["city"] = city
sub_dict["state"] = state
shows_subquery = db.session.query(
Shows.venue_id,
func.count('*').label('num_upcoming_shows')).filter(
Shows.date >= func.current_date()).group_by(
Shows.venue_id).subquery()
venues_data = db.session.query(
Venue.id, Venue.name, Venue.city, Venue.state,
shows_subquery.c.num_upcoming_shows).outerjoin(
shows_subquery, Venue.id == shows_subquery.c.venue_id).filter(
Venue.city == city,
Venue.state == state).order_by(Venue.state,
Venue.city).all()
venue_list = [venue for venue in venues_data]
sub_dict["venues"] = venue_list
data.append(dict(sub_dict))
return render_template('pages/venues.html', areas=data)
class CoreFixtures(object):
# lambdas which return a tuple of ColumnElement objects.
# must return at least two objects that should compare differently.
# to test more varieties of "difference" additional objects can be added.
fixtures = [
lambda: (
column("q"),
column("x"),
column("q", Integer),
column("q", String),
),
lambda: (~column("q", Boolean), ~column("p", Boolean)),
lambda: (
table_a.c.a.label("foo"),
table_a.c.a.label("bar"),
table_a.c.b.label("foo"),
),
lambda: (
_label_reference(table_a.c.a.desc()),
_label_reference(table_a.c.a.asc()),
),
lambda: (_textual_label_reference("a"), _textual_label_reference("b")),
lambda: (
text("select a, b from table").columns(a=Integer, b=String),
text("select a, b, c from table").columns(
a=Integer, b=String, c=Integer),
text("select a, b, c from table where foo=:bar").bindparams(
bindparam("bar", type_=Integer)),
text("select a, b, c from table where foo=:foo").bindparams(
bindparam("foo", type_=Integer)),
text("select a, b, c from table where foo=:bar").bindparams(
bindparam("bar", type_=String)),
),
lambda: (
column("q") == column("x"),
column("q") == column("y"),
column("z") == column("x"),
column("z") + column("x"),
column("z") - column("x"),
column("x") - column("z"),
column("z") > column("x"),
column("x").in_([5, 7]),
column("x").in_([10, 7, 8]),
# note these two are mathematically equivalent but for now they
# are considered to be different
column("z") >= column("x"),
column("x") <= column("z"),
column("q").between(5, 6),
column("q").between(5, 6, symmetric=True),
column("q").like("somstr"),
column("q").like("somstr", escape="\\"),
column("q").like("somstr", escape="X"),
),
lambda: (
table_a.c.a,
table_a.c.a._annotate({"orm": True}),
table_a.c.a._annotate({
"orm": True
})._annotate({"bar": False}),
table_a.c.a._annotate({
"orm": True,
"parententity": MyEntity("a", table_a)
}),
table_a.c.a._annotate({
"orm": True,
"parententity": MyEntity("b", table_a)
}),
table_a.c.a._annotate(
{
"orm": True,
"parententity": MyEntity("b", select([table_a]))
}),
table_a.c.a._annotate({
"orm":
True,
"parententity":
MyEntity("b",
select([table_a]).where(table_a.c.a == 5)),
}),
),
lambda: (
table_a,
table_a._annotate({"orm": True}),
table_a._annotate({
"orm": True
})._annotate({"bar": False}),
table_a._annotate({
"orm": True,
"parententity": MyEntity("a", table_a)
}),
table_a._annotate({
"orm": True,
"parententity": MyEntity("b", table_a)
}),
table_a._annotate({
"orm": True,
"parententity": MyEntity("b", select([table_a]))
}),
),
lambda: (
table("a", column("x"), column("y")),
table("a", column("x"), column("y"))._annotate({"orm": True}),
table("b", column("x"), column("y"))._annotate({"orm": True}),
),
lambda: (
cast(column("q"), Integer),
cast(column("q"), Float),
cast(column("p"), Integer),
),
lambda: (
bindparam("x"),
bindparam("y"),
bindparam("x", type_=Integer),
bindparam("x", type_=String),
bindparam(None),
),
lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")),
lambda: (func.foo(), func.foo(5), func.bar()),
lambda: (func.current_date(), func.current_time()),
lambda: (
func.next_value(Sequence("q")),
func.next_value(Sequence("p")),
),
lambda: (True_(), False_()),
lambda: (Null(), ),
lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)),
lambda: (FunctionElement(5), FunctionElement(5, 6)),
lambda: (func.count(), func.not_count()),
lambda: (func.char_length("abc"), func.char_length("def")),
lambda: (GenericFunction("a", "b"), GenericFunction("a")),
lambda: (CollationClause("foobar"), CollationClause("batbar")),
lambda: (
type_coerce(column("q", Integer), String),
type_coerce(column("q", Integer), Float),
type_coerce(column("z", Integer), Float),
),
lambda: (table_a.c.a, table_b.c.a),
lambda: (tuple_(1, 2), tuple_(3, 4)),
lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])),
lambda: (
func.percentile_cont(0.5).within_group(table_a.c.a),
func.percentile_cont(0.5).within_group(table_a.c.b),
func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b),
func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b,
column("q")),
),
lambda: (
func.is_equal("a", "b").as_comparison(1, 2),
func.is_equal("a", "c").as_comparison(1, 2),
func.is_equal("a", "b").as_comparison(2, 1),
func.is_equal("a", "b", "c").as_comparison(1, 2),
func.foobar("a", "b").as_comparison(1, 2),
),
lambda: (
func.row_number().over(order_by=table_a.c.a),
func.row_number().over(order_by=table_a.c.a, range_=(0, 10)),
func.row_number().over(order_by=table_a.c.a, range_=(None, 10)),
func.row_number().over(order_by=table_a.c.a, rows=(None, 20)),
func.row_number().over(order_by=table_a.c.b),
func.row_number().over(order_by=table_a.c.a,
partition_by=table_a.c.b),
),
lambda: (
func.count(1).filter(table_a.c.a == 5),
func.count(1).filter(table_a.c.a == 10),
func.foob(1).filter(table_a.c.a == 10),
),
lambda: (
and_(table_a.c.a == 5, table_a.c.b == table_b.c.a),
and_(table_a.c.a == 5, table_a.c.a == table_b.c.a),
or_(table_a.c.a == 5, table_a.c.b == table_b.c.a),
ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a),
ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a),
),
lambda: (
case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]),
case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]),
case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]),
case(whens=[
(table_a.c.a == 5, 10),
(table_a.c.b == 10, 20),
(table_a.c.a == 9, 12),
]),
case(
whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)],
else_=30,
),
case({
"wendy": "W",
"jack": "J"
}, value=table_a.c.a, else_="E"),
case({
"wendy": "W",
"jack": "J"
}, value=table_a.c.b, else_="E"),
case({
"wendy_w": "W",
"jack": "J"
}, value=table_a.c.a, else_="E"),
),
lambda: (
extract("foo", table_a.c.a),
extract("foo", table_a.c.b),
extract("bar", table_a.c.a),
),
lambda: (
Slice(1, 2, 5),
Slice(1, 5, 5),
Slice(1, 5, 10),
Slice(2, 10, 15),
),
lambda: (
select([table_a.c.a]),
select([table_a.c.a, table_a.c.b]),
select([table_a.c.b, table_a.c.a]),
select([table_a.c.b, table_a.c.a]).apply_labels(),
select([table_a.c.a]).where(table_a.c.b == 5),
select([table_a.c.a]).where(table_a.c.b == 5).where(table_a.c.a ==
10),
select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(),
select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(
nowait=True),
select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b),
select([table_a.c.a]).where(table_a.c.b == 5).correlate_except(
table_b),
),
lambda: (
future_select(table_a.c.a),
future_select(table_a.c.a).join(table_b, table_a.c.a == table_b.c.a
),
future_select(table_a.c.a).join_from(table_a, table_b, table_a.c.a
== table_b.c.a),
future_select(table_a.c.a).join_from(table_a, table_b),
future_select(table_a.c.a).join_from(table_c, table_b),
future_select(table_a.c.a).join(
table_b, table_a.c.a == table_b.c.a).join(
table_c, table_b.c.b == table_c.c.x),
future_select(table_a.c.a).join(table_b),
future_select(table_a.c.a).join(table_c),
future_select(table_a.c.a).join(table_b, table_a.c.a == table_b.c.b
),
future_select(table_a.c.a).join(table_c, table_a.c.a == table_c.c.x
),
),
lambda: (
select([table_a.c.a]).cte(),
select([table_a.c.a]).cte(recursive=True),
select([table_a.c.a]).cte(name="some_cte", recursive=True),
select([table_a.c.a]).cte(name="some_cte"),
select([table_a.c.a]).cte(name="some_cte").alias("other_cte"),
select([table_a.c.a]).cte(name="some_cte").union_all(
select([table_a.c.a])),
select([table_a.c.a]).cte(name="some_cte").union_all(
select([table_a.c.b])),
select([table_a.c.a]).lateral(),
select([table_a.c.a]).lateral(name="bar"),
table_a.tablesample(func.bernoulli(1)),
table_a.tablesample(func.bernoulli(1), seed=func.random()),
table_a.tablesample(func.bernoulli(1), seed=func.other_random()),
table_a.tablesample(func.hoho(1)),
table_a.tablesample(func.bernoulli(1), name="bar"),
table_a.tablesample(
func.bernoulli(1), name="bar", seed=func.random()),
),
lambda: (
table_a.insert(),
table_a.insert().values({})._annotate({"nocache": True}),
table_b.insert(),
table_b.insert().with_dialect_options(sqlite_foo="some value"),
table_b.insert().from_select(["a", "b"], select([table_a])),
table_b.insert().from_select(["a", "b"],
select([table_a]).where(table_a.c.a >
5)),
table_b.insert().from_select(["a", "b"], select([table_b])),
table_b.insert().from_select(["c", "d"], select([table_a])),
table_b.insert().returning(table_b.c.a),
table_b.insert().returning(table_b.c.a, table_b.c.b),
table_b.insert().inline(),
table_b.insert().prefix_with("foo"),
table_b.insert().with_hint("RUNFAST"),
table_b.insert().values(a=5, b=10),
table_b.insert().values(a=5),
table_b.insert().values({
table_b.c.a: 5,
"b": 10
})._annotate({"nocache": True}),
table_b.insert().values(a=7, b=10),
table_b.insert().values(a=5, b=10).inline(),
table_b.insert().values([{
"a": 5,
"b": 10
}, {
"a": 8,
"b": 12
}])._annotate({"nocache": True}),
table_b.insert().values([{
"a": 9,
"b": 10
}, {
"a": 8,
"b": 7
}])._annotate({"nocache": True}),
table_b.insert().values([(5, 10),
(8, 12)])._annotate({"nocache": True}),
table_b.insert().values([(5, 9),
(5, 12)])._annotate({"nocache": True}),
),
lambda: (
table_b.update(),
table_b.update().where(table_b.c.a == 5),
table_b.update().where(table_b.c.b == 5),
table_b.update().where(table_b.c.b == 5).with_dialect_options(
mysql_limit=10),
table_b.update().where(table_b.c.b == 5).with_dialect_options(
mysql_limit=10, sqlite_foo="some value"),
table_b.update().where(table_b.c.a == 5).values(a=5, b=10),
table_b.update().where(table_b.c.a == 5).values(a=5, b=10, c=12),
table_b.update().where(table_b.c.b == 5).values(a=5, b=10).
_annotate({"nocache": True}),
table_b.update().values(a=5, b=10),
table_b.update().values({
"a": 5,
table_b.c.b: 10
})._annotate({"nocache": True}),
table_b.update().values(a=7, b=10),
table_b.update().ordered_values(("a", 5), ("b", 10)),
table_b.update().ordered_values(("b", 10), ("a", 5)),
table_b.update().ordered_values((table_b.c.a, 5), ("b", 10)),
),
lambda: (
table_b.delete(),
table_b.delete().with_dialect_options(sqlite_foo="some value"),
table_b.delete().where(table_b.c.a == 5),
table_b.delete().where(table_b.c.b == 5),
),
lambda: (
values(
column("mykey", Integer),
column("mytext", String),
column("myint", Integer),
name="myvalues",
).data([(1, "textA", 99),
(2, "textB", 88)])._annotate({"nocache": True}),
values(
column("mykey", Integer),
column("mytext", String),
column("myint", Integer),
name="myothervalues",
).data([(1, "textA", 99),
(2, "textB", 88)])._annotate({"nocache": True}),
values(
column("mykey", Integer),
column("mytext", String),
column("myint", Integer),
name="myvalues",
).data([(1, "textA", 89),
(2, "textG", 88)])._annotate({"nocache": True}),
values(
column("mykey", Integer),
column("mynottext", String),
column("myint", Integer),
name="myvalues",
).data([(1, "textA", 99),
(2, "textB", 88)])._annotate({"nocache": True}),
# TODO: difference in type
# values(
# [
# column("mykey", Integer),
# column("mytext", Text),
# column("myint", Integer),
# ],
# (1, "textA", 99),
# (2, "textB", 88),
# alias_name="myvalues",
# ),
),
lambda: (
select([table_a.c.a]),
select([table_a.c.a]).prefix_with("foo"),
select([table_a.c.a]).prefix_with("foo", dialect="mysql"),
select([table_a.c.a]).prefix_with("foo", dialect="postgresql"),
select([table_a.c.a]).prefix_with("bar"),
select([table_a.c.a]).suffix_with("bar"),
),
lambda: (
select([table_a_2.c.a]),
select([table_a_2_fs.c.a]),
select([table_a_2_bs.c.a]),
),
lambda: (
select([table_a.c.a]),
select([table_a.c.a]).with_hint(None, "some hint"),
select([table_a.c.a]).with_hint(None, "some other hint"),
select([table_a.c.a]).with_hint(table_a, "some hint"),
select([table_a.c.a]).with_hint(table_a, "some hint").with_hint(
None, "some other hint"),
select([table_a.c.a]).with_hint(table_a, "some other hint"),
select([table_a.c.a]).with_hint(
table_a, "some hint", dialect_name="mysql"),
select([table_a.c.a]).with_hint(
table_a, "some hint", dialect_name="postgresql"),
),
lambda: (
table_a.join(table_b, table_a.c.a == table_b.c.a),
table_a.join(table_b,
and_(table_a.c.a == table_b.c.a, table_a.c.b == 1)),
table_a.outerjoin(table_b, table_a.c.a == table_b.c.a),
),
lambda: (
table_a.alias("a"),
table_a.alias("b"),
table_a.alias(),
table_b.alias("a"),
select([table_a.c.a]).alias("a"),
),
lambda: (
FromGrouping(table_a.alias("a")),
FromGrouping(table_a.alias("b")),
),
lambda: (
SelectStatementGrouping(select([table_a])),
SelectStatementGrouping(select([table_b])),
),
lambda: (
select([table_a.c.a]).scalar_subquery(),
select([table_a.c.a]).where(table_a.c.b == 5).scalar_subquery(),
),
lambda: (
exists().where(table_a.c.a == 5),
exists().where(table_a.c.b == 5),
),
lambda: (
union(select([table_a.c.a]), select([table_a.c.b])),
union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"),
union_all(select([table_a.c.a]), select([table_a.c.b])),
union(select([table_a.c.a])),
union(
select([table_a.c.a]),
select([table_a.c.b]).where(table_a.c.b > 5),
),
),
lambda: (
table("a", column("x"), column("y")),
table("a", column("y"), column("x")),
table("b", column("x"), column("y")),
table("a", column("x"), column("y"), column("z")),
table("a", column("x"), column("y", Integer)),
table("a", column("q"), column("y", Integer)),
),
lambda: (table_a, table_b),
]
dont_compare_values_fixtures = [
lambda: (
# note the in_(...) all have different column names becuase
# otherwise all IN expressions would compare as equivalent
column("x").in_(random_choices(range(10), k=3)),
column("y").in_(
bindparam(
"q",
random_choices(range(10), k=random.randint(0, 7)),
expanding=True,
)),
column("z").in_(random_choices(range(10), k=random.randint(0, 7))),
column("x") == random.randint(1, 10),
)
]
def _complex_fixtures():
def one():
a1 = table_a.alias()
a2 = table_b_like_a.alias()
stmt = (select([table_a.c.a, a1.c.b,
a2.c.b]).where(table_a.c.b == a1.c.b).where(
a1.c.b == a2.c.b).where(a1.c.a == 5))
return stmt
def one_diff():
a1 = table_b_like_a.alias()
a2 = table_a.alias()
stmt = (select([table_a.c.a, a1.c.b,
a2.c.b]).where(table_a.c.b == a1.c.b).where(
a1.c.b == a2.c.b).where(a1.c.a == 5))
return stmt
def two():
inner = one().subquery()
stmt = select([table_b.c.a, inner.c.a, inner.c.b]).select_from(
table_b.join(inner, table_b.c.b == inner.c.b))
return stmt
def three():
a1 = table_a.alias()
a2 = table_a.alias()
ex = exists().where(table_b.c.b == a1.c.a)
stmt = (select([a1.c.a, a2.c.a]).select_from(
a1.join(a2, a1.c.b == a2.c.b)).where(ex))
return stmt
return [one(), one_diff(), two(), three()]
fixtures.append(_complex_fixtures)
def _statements_w_context_options_fixtures():
return [
select([table_a])._add_context_option(opt1, True),
select([table_a])._add_context_option(opt1, 5),
select([table_a])._add_context_option(opt1,
True)._add_context_option(
opt2, True),
select([table_a
])._add_context_option(opt1,
True)._add_context_option(opt2, 5),
select([table_a])._add_context_option(opt3, True),
]
fixtures.append(_statements_w_context_options_fixtures)
def _statements_w_anonymous_col_names():
def one():
c = column("q")
l = c.label(None)
# new case as of Id810f485c5f7ed971529489b84694e02a3356d6d
subq = select([l]).subquery()
# this creates a ColumnClause as a proxy to the Label() that has
# an anoymous name, so the column has one too.
anon_col = subq.c[0]
# then when BindParameter is created, it checks the label
# and doesn't double up on the anonymous name which is uncachable
return anon_col > 5
def two():
c = column("p")
l = c.label(None)
# new case as of Id810f485c5f7ed971529489b84694e02a3356d6d
subq = select([l]).subquery()
# this creates a ColumnClause as a proxy to the Label() that has
# an anoymous name, so the column has one too.
anon_col = subq.c[0]
# then when BindParameter is created, it checks the label
# and doesn't double up on the anonymous name which is uncachable
return anon_col > 5
def three():
l1, l2 = table_a.c.a.label(None), table_a.c.b.label(None)
stmt = select([table_a.c.a, table_a.c.b, l1, l2])
subq = stmt.subquery()
return select([subq]).where(subq.c[2] == 10)
return (
one(),
two(),
three(),
)
fixtures.append(_statements_w_anonymous_col_names)
class CoreFixtures(object):
# lambdas which return a tuple of ColumnElement objects.
# must return at least two objects that should compare differently.
# to test more varieties of "difference" additional objects can be added.
fixtures = [
lambda: (
column("q"),
column("x"),
column("q", Integer),
column("q", String),
),
lambda: (~column("q", Boolean), ~column("p", Boolean)),
lambda: (
table_a.c.a.label("foo"),
table_a.c.a.label("bar"),
table_a.c.b.label("foo"),
),
lambda: (
_label_reference(table_a.c.a.desc()),
_label_reference(table_a.c.a.asc()),
),
lambda: (_textual_label_reference("a"), _textual_label_reference("b")),
lambda: (
text("select a, b from table").columns(a=Integer, b=String),
text("select a, b, c from table").columns(
a=Integer, b=String, c=Integer
),
text("select a, b, c from table where foo=:bar").bindparams(
bindparam("bar", type_=Integer)
),
text("select a, b, c from table where foo=:foo").bindparams(
bindparam("foo", type_=Integer)
),
text("select a, b, c from table where foo=:bar").bindparams(
bindparam("bar", type_=String)
),
),
lambda: (
column("q") == column("x"),
column("q") == column("y"),
column("z") == column("x"),
column("z") + column("x"),
column("z") - column("x"),
column("x") - column("z"),
column("z") > column("x"),
column("x").in_([5, 7]),
column("x").in_([10, 7, 8]),
# note these two are mathematically equivalent but for now they
# are considered to be different
column("z") >= column("x"),
column("x") <= column("z"),
column("q").between(5, 6),
column("q").between(5, 6, symmetric=True),
column("q").like("somstr"),
column("q").like("somstr", escape="\\"),
column("q").like("somstr", escape="X"),
),
lambda: (
table_a.c.a,
table_a.c.a._annotate({"orm": True}),
table_a.c.a._annotate({"orm": True})._annotate({"bar": False}),
table_a.c.a._annotate(
{"orm": True, "parententity": MyEntity("a", table_a)}
),
table_a.c.a._annotate(
{"orm": True, "parententity": MyEntity("b", table_a)}
),
table_a.c.a._annotate(
{"orm": True, "parententity": MyEntity("b", select([table_a]))}
),
),
lambda: (
cast(column("q"), Integer),
cast(column("q"), Float),
cast(column("p"), Integer),
),
lambda: (
bindparam("x"),
bindparam("y"),
bindparam("x", type_=Integer),
bindparam("x", type_=String),
bindparam(None),
),
lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")),
lambda: (func.foo(), func.foo(5), func.bar()),
lambda: (func.current_date(), func.current_time()),
lambda: (
func.next_value(Sequence("q")),
func.next_value(Sequence("p")),
),
lambda: (True_(), False_()),
lambda: (Null(),),
lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)),
lambda: (FunctionElement(5), FunctionElement(5, 6)),
lambda: (func.count(), func.not_count()),
lambda: (func.char_length("abc"), func.char_length("def")),
lambda: (GenericFunction("a", "b"), GenericFunction("a")),
lambda: (CollationClause("foobar"), CollationClause("batbar")),
lambda: (
type_coerce(column("q", Integer), String),
type_coerce(column("q", Integer), Float),
type_coerce(column("z", Integer), Float),
),
lambda: (table_a.c.a, table_b.c.a),
lambda: (tuple_(1, 2), tuple_(3, 4)),
lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])),
lambda: (
func.percentile_cont(0.5).within_group(table_a.c.a),
func.percentile_cont(0.5).within_group(table_a.c.b),
func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b),
func.percentile_cont(0.5).within_group(
table_a.c.a, table_a.c.b, column("q")
),
),
lambda: (
func.is_equal("a", "b").as_comparison(1, 2),
func.is_equal("a", "c").as_comparison(1, 2),
func.is_equal("a", "b").as_comparison(2, 1),
func.is_equal("a", "b", "c").as_comparison(1, 2),
func.foobar("a", "b").as_comparison(1, 2),
),
lambda: (
func.row_number().over(order_by=table_a.c.a),
func.row_number().over(order_by=table_a.c.a, range_=(0, 10)),
func.row_number().over(order_by=table_a.c.a, range_=(None, 10)),
func.row_number().over(order_by=table_a.c.a, rows=(None, 20)),
func.row_number().over(order_by=table_a.c.b),
func.row_number().over(
order_by=table_a.c.a, partition_by=table_a.c.b
),
),
lambda: (
func.count(1).filter(table_a.c.a == 5),
func.count(1).filter(table_a.c.a == 10),
func.foob(1).filter(table_a.c.a == 10),
),
lambda: (
and_(table_a.c.a == 5, table_a.c.b == table_b.c.a),
and_(table_a.c.a == 5, table_a.c.a == table_b.c.a),
or_(table_a.c.a == 5, table_a.c.b == table_b.c.a),
ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a),
ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a),
),
lambda: (
case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]),
case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]),
case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]),
case(
whens=[
(table_a.c.a == 5, 10),
(table_a.c.b == 10, 20),
(table_a.c.a == 9, 12),
]
),
case(
whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)],
else_=30,
),
case({"wendy": "W", "jack": "J"}, value=table_a.c.a, else_="E"),
case({"wendy": "W", "jack": "J"}, value=table_a.c.b, else_="E"),
case({"wendy_w": "W", "jack": "J"}, value=table_a.c.a, else_="E"),
),
lambda: (
extract("foo", table_a.c.a),
extract("foo", table_a.c.b),
extract("bar", table_a.c.a),
),
lambda: (
Slice(1, 2, 5),
Slice(1, 5, 5),
Slice(1, 5, 10),
Slice(2, 10, 15),
),
lambda: (
select([table_a.c.a]),
select([table_a.c.a, table_a.c.b]),
select([table_a.c.b, table_a.c.a]),
select([table_a.c.a]).where(table_a.c.b == 5),
select([table_a.c.a])
.where(table_a.c.b == 5)
.where(table_a.c.a == 10),
select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(),
select([table_a.c.a])
.where(table_a.c.b == 5)
.with_for_update(nowait=True),
select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b),
select([table_a.c.a])
.where(table_a.c.b == 5)
.correlate_except(table_b),
),
lambda: (
select([table_a.c.a]).cte(),
select([table_a.c.a]).cte(recursive=True),
select([table_a.c.a]).cte(name="some_cte", recursive=True),
select([table_a.c.a]).cte(name="some_cte"),
select([table_a.c.a]).cte(name="some_cte").alias("other_cte"),
select([table_a.c.a])
.cte(name="some_cte")
.union_all(select([table_a.c.a])),
select([table_a.c.a])
.cte(name="some_cte")
.union_all(select([table_a.c.b])),
select([table_a.c.a]).lateral(),
select([table_a.c.a]).lateral(name="bar"),
table_a.tablesample(func.bernoulli(1)),
table_a.tablesample(func.bernoulli(1), seed=func.random()),
table_a.tablesample(func.bernoulli(1), seed=func.other_random()),
table_a.tablesample(func.hoho(1)),
table_a.tablesample(func.bernoulli(1), name="bar"),
table_a.tablesample(
func.bernoulli(1), name="bar", seed=func.random()
),
),
lambda: (
select([table_a.c.a]),
select([table_a.c.a]).prefix_with("foo"),
select([table_a.c.a]).prefix_with("foo", dialect="mysql"),
select([table_a.c.a]).prefix_with("foo", dialect="postgresql"),
select([table_a.c.a]).prefix_with("bar"),
select([table_a.c.a]).suffix_with("bar"),
),
lambda: (
select([table_a_2.c.a]),
select([table_a_2_fs.c.a]),
select([table_a_2_bs.c.a]),
),
lambda: (
select([table_a.c.a]),
select([table_a.c.a]).with_hint(None, "some hint"),
select([table_a.c.a]).with_hint(None, "some other hint"),
select([table_a.c.a]).with_hint(table_a, "some hint"),
select([table_a.c.a])
.with_hint(table_a, "some hint")
.with_hint(None, "some other hint"),
select([table_a.c.a]).with_hint(table_a, "some other hint"),
select([table_a.c.a]).with_hint(
table_a, "some hint", dialect_name="mysql"
),
select([table_a.c.a]).with_hint(
table_a, "some hint", dialect_name="postgresql"
),
),
lambda: (
table_a.join(table_b, table_a.c.a == table_b.c.a),
table_a.join(
table_b, and_(table_a.c.a == table_b.c.a, table_a.c.b == 1)
),
table_a.outerjoin(table_b, table_a.c.a == table_b.c.a),
),
lambda: (
table_a.alias("a"),
table_a.alias("b"),
table_a.alias(),
table_b.alias("a"),
select([table_a.c.a]).alias("a"),
),
lambda: (
FromGrouping(table_a.alias("a")),
FromGrouping(table_a.alias("b")),
),
lambda: (
SelectStatementGrouping(select([table_a])),
SelectStatementGrouping(select([table_b])),
),
lambda: (
select([table_a.c.a]).scalar_subquery(),
select([table_a.c.a]).where(table_a.c.b == 5).scalar_subquery(),
),
lambda: (
exists().where(table_a.c.a == 5),
exists().where(table_a.c.b == 5),
),
lambda: (
union(select([table_a.c.a]), select([table_a.c.b])),
union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"),
union_all(select([table_a.c.a]), select([table_a.c.b])),
union(select([table_a.c.a])),
union(
select([table_a.c.a]),
select([table_a.c.b]).where(table_a.c.b > 5),
),
),
lambda: (
table("a", column("x"), column("y")),
table("a", column("y"), column("x")),
table("b", column("x"), column("y")),
table("a", column("x"), column("y"), column("z")),
table("a", column("x"), column("y", Integer)),
table("a", column("q"), column("y", Integer)),
),
lambda: (table_a, table_b),
]
dont_compare_values_fixtures = [
lambda: (
# note the in_(...) all have different column names becuase
# otherwise all IN expressions would compare as equivalent
column("x").in_(random_choices(range(10), k=3)),
column("y").in_(
bindparam(
"q",
random_choices(range(10), k=random.randint(0, 7)),
expanding=True,
)
),
column("z").in_(random_choices(range(10), k=random.randint(0, 7))),
column("x") == random.randint(1, 10),
)
]
def _complex_fixtures():
def one():
a1 = table_a.alias()
a2 = table_b_like_a.alias()
stmt = (
select([table_a.c.a, a1.c.b, a2.c.b])
.where(table_a.c.b == a1.c.b)
.where(a1.c.b == a2.c.b)
.where(a1.c.a == 5)
)
return stmt
def one_diff():
a1 = table_b_like_a.alias()
a2 = table_a.alias()
stmt = (
select([table_a.c.a, a1.c.b, a2.c.b])
.where(table_a.c.b == a1.c.b)
.where(a1.c.b == a2.c.b)
.where(a1.c.a == 5)
)
return stmt
def two():
inner = one().subquery()
stmt = select([table_b.c.a, inner.c.a, inner.c.b]).select_from(
table_b.join(inner, table_b.c.b == inner.c.b)
)
return stmt
def three():
a1 = table_a.alias()
a2 = table_a.alias()
ex = exists().where(table_b.c.b == a1.c.a)
stmt = (
select([a1.c.a, a2.c.a])
.select_from(a1.join(a2, a1.c.b == a2.c.b))
.where(ex)
)
return stmt
return [one(), one_diff(), two(), three()]
fixtures.append(_complex_fixtures)
class CompareAndCopyTest(fixtures.TestBase):
# lambdas which return a tuple of ColumnElement objects.
# must return at least two objects that should compare differently.
# to test more varieties of "difference" additional objects can be added.
fixtures = [
lambda: (
column("q"),
column("x"),
column("q", Integer),
column("q", String),
),
lambda: (~column("q", Boolean), ~column("p", Boolean)),
lambda: (
table_a.c.a.label("foo"),
table_a.c.a.label("bar"),
table_a.c.b.label("foo"),
),
lambda: (
_label_reference(table_a.c.a.desc()),
_label_reference(table_a.c.a.asc()),
),
lambda: (_textual_label_reference("a"), _textual_label_reference("b")),
lambda: (
text("select a, b from table").columns(a=Integer, b=String),
text("select a, b, c from table").columns(
a=Integer, b=String, c=Integer),
),
lambda: (
column("q") == column("x"),
column("q") == column("y"),
column("z") == column("x"),
),
lambda: (
cast(column("q"), Integer),
cast(column("q"), Float),
cast(column("p"), Integer),
),
lambda: (
bindparam("x"),
bindparam("y"),
bindparam("x", type_=Integer),
bindparam("x", type_=String),
bindparam(None),
),
lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")),
lambda: (func.foo(), func.foo(5), func.bar()),
lambda: (func.current_date(), func.current_time()),
lambda: (
func.next_value(Sequence("q")),
func.next_value(Sequence("p")),
),
lambda: (True_(), False_()),
lambda: (Null(), ),
lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)),
lambda: (FunctionElement(5), FunctionElement(5, 6)),
lambda: (func.count(), func.not_count()),
lambda: (func.char_length("abc"), func.char_length("def")),
lambda: (GenericFunction("a", "b"), GenericFunction("a")),
lambda: (CollationClause("foobar"), CollationClause("batbar")),
lambda: (
type_coerce(column("q", Integer), String),
type_coerce(column("q", Integer), Float),
type_coerce(column("z", Integer), Float),
),
lambda: (table_a.c.a, table_b.c.a),
lambda: (tuple_([1, 2]), tuple_([3, 4])),
lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])),
lambda: (
func.percentile_cont(0.5).within_group(table_a.c.a),
func.percentile_cont(0.5).within_group(table_a.c.b),
func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b),
func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b,
column("q")),
),
lambda: (
func.is_equal("a", "b").as_comparison(1, 2),
func.is_equal("a", "c").as_comparison(1, 2),
func.is_equal("a", "b").as_comparison(2, 1),
func.is_equal("a", "b", "c").as_comparison(1, 2),
func.foobar("a", "b").as_comparison(1, 2),
),
lambda: (
func.row_number().over(order_by=table_a.c.a),
func.row_number().over(order_by=table_a.c.a, range_=(0, 10)),
func.row_number().over(order_by=table_a.c.a, range_=(None, 10)),
func.row_number().over(order_by=table_a.c.a, rows=(None, 20)),
func.row_number().over(order_by=table_a.c.b),
func.row_number().over(order_by=table_a.c.a,
partition_by=table_a.c.b),
),
lambda: (
func.count(1).filter(table_a.c.a == 5),
func.count(1).filter(table_a.c.a == 10),
func.foob(1).filter(table_a.c.a == 10),
),
lambda: (
and_(table_a.c.a == 5, table_a.c.b == table_b.c.a),
and_(table_a.c.a == 5, table_a.c.a == table_b.c.a),
or_(table_a.c.a == 5, table_a.c.b == table_b.c.a),
ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a),
ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a),
),
lambda: (
case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]),
case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]),
case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]),
case(whens=[
(table_a.c.a == 5, 10),
(table_a.c.b == 10, 20),
(table_a.c.a == 9, 12),
]),
case(
whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)],
else_=30,
),
case({
"wendy": "W",
"jack": "J"
}, value=table_a.c.a, else_="E"),
case({
"wendy": "W",
"jack": "J"
}, value=table_a.c.b, else_="E"),
case({
"wendy_w": "W",
"jack": "J"
}, value=table_a.c.a, else_="E"),
),
lambda: (
extract("foo", table_a.c.a),
extract("foo", table_a.c.b),
extract("bar", table_a.c.a),
),
lambda: (
Slice(1, 2, 5),
Slice(1, 5, 5),
Slice(1, 5, 10),
Slice(2, 10, 15),
),
lambda: (
select([table_a.c.a]),
select([table_a.c.a, table_a.c.b]),
select([table_a.c.b, table_a.c.a]),
select([table_a.c.a]).where(table_a.c.b == 5),
select([table_a.c.a]).where(table_a.c.b == 5).where(table_a.c.a ==
10),
select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(),
select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(
nowait=True),
select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b),
select([table_a.c.a]).where(table_a.c.b == 5).correlate_except(
table_b),
),
lambda: (
table_a.join(table_b, table_a.c.a == table_b.c.a),
table_a.join(table_b,
and_(table_a.c.a == table_b.c.a, table_a.c.b == 1)),
table_a.outerjoin(table_b, table_a.c.a == table_b.c.a),
),
lambda: (
table_a.alias("a"),
table_a.alias("b"),
table_a.alias(),
table_b.alias("a"),
select([table_a.c.a]).alias("a"),
),
lambda: (
FromGrouping(table_a.alias("a")),
FromGrouping(table_a.alias("b")),
),
lambda: (
select([table_a.c.a]).as_scalar(),
select([table_a.c.a]).where(table_a.c.b == 5).as_scalar(),
),
lambda: (
exists().where(table_a.c.a == 5),
exists().where(table_a.c.b == 5),
),
lambda: (
union(select([table_a.c.a]), select([table_a.c.b])),
union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"),
union_all(select([table_a.c.a]), select([table_a.c.b])),
union(select([table_a.c.a])),
union(
select([table_a.c.a]),
select([table_a.c.b]).where(table_a.c.b > 5),
),
),
lambda: (
table("a", column("x"), column("y")),
table("a", column("y"), column("x")),
table("b", column("x"), column("y")),
table("a", column("x"), column("y"), column("z")),
table("a", column("x"), column("y", Integer)),
table("a", column("q"), column("y", Integer)),
),
lambda: (
Table("a", MetaData(), Column("q", Integer), Column("b", String)),
Table("b", MetaData(), Column("q", Integer), Column("b", String)),
),
]
@classmethod
def setup_class(cls):
# TODO: we need to get dialects here somehow, perhaps in test_suite?
[
importlib.import_module("sqlalchemy.dialects.%s" % d)
for d in dialects.__all__ if not d.startswith("_")
]
def test_all_present(self):
need = set(
cls for cls in class_hierarchy(ClauseElement)
if issubclass(cls, (ColumnElement, Selectable)) and "__init__" in
cls.__dict__ and not issubclass(cls, (Annotated))
and "orm" not in cls.__module__ and "crud" not in cls.__module__
and "dialects" not in cls.__module__ # TODO: dialects?
).difference({ColumnElement, UnaryExpression})
for fixture in self.fixtures:
case_a = fixture()
for elem in case_a:
for mro in type(elem).__mro__:
need.discard(mro)
is_false(bool(need), "%d Remaining classes: %r" % (len(need), need))
def test_compare(self):
for fixture in self.fixtures:
case_a = fixture()
case_b = fixture()
for a, b in itertools.combinations_with_replacement(
range(len(case_a)), 2):
if a == b:
is_true(
case_a[a].compare(case_b[b],
arbitrary_expression=True),
"%r != %r" % (case_a[a], case_b[b]),
)
else:
is_false(
case_a[a].compare(case_b[b],
arbitrary_expression=True),
"%r == %r" % (case_a[a], case_b[b]),
)
def test_cache_key(self):
def assert_params_append(assert_params):
def append(param):
if param._value_required_for_cache:
assert_params.append(param)
else:
is_(param.value, None)
return append
for fixture in self.fixtures:
case_a = fixture()
case_b = fixture()
for a, b in itertools.combinations_with_replacement(
range(len(case_a)), 2):
assert_a_params = []
assert_b_params = []
visitors.traverse_depthfirst(
case_a[a],
{},
{"bindparam": assert_params_append(assert_a_params)},
)
visitors.traverse_depthfirst(
case_b[b],
{},
{"bindparam": assert_params_append(assert_b_params)},
)
if assert_a_params:
assert_raises_message(
NotImplementedError,
"bindparams collection argument required ",
case_a[a]._cache_key,
)
if assert_b_params:
assert_raises_message(
NotImplementedError,
"bindparams collection argument required ",
case_b[b]._cache_key,
)
if not assert_a_params and not assert_b_params:
if a == b:
eq_(case_a[a]._cache_key(), case_b[b]._cache_key())
else:
ne_(case_a[a]._cache_key(), case_b[b]._cache_key())
def test_cache_key_gather_bindparams(self):
for fixture in self.fixtures:
case_a = fixture()
case_b = fixture()
# in the "bindparams" case, the cache keys for bound parameters
# with only different values will be the same, but the params
# themselves are gathered into a collection.
for a, b in itertools.combinations_with_replacement(
range(len(case_a)), 2):
a_params = {"bindparams": []}
b_params = {"bindparams": []}
if a == b:
a_key = case_a[a]._cache_key(**a_params)
b_key = case_b[b]._cache_key(**b_params)
eq_(a_key, b_key)
if a_params["bindparams"]:
for a_param, b_param in zip(a_params["bindparams"],
b_params["bindparams"]):
assert a_param.compare(b_param)
else:
a_key = case_a[a]._cache_key(**a_params)
b_key = case_b[b]._cache_key(**b_params)
if a_key == b_key:
for a_param, b_param in zip(a_params["bindparams"],
b_params["bindparams"]):
if not a_param.compare(b_param):
break
else:
assert False, "Bound parameters are all the same"
else:
ne_(a_key, b_key)
assert_a_params = []
assert_b_params = []
visitors.traverse_depthfirst(
case_a[a], {}, {"bindparam": assert_a_params.append})
visitors.traverse_depthfirst(
case_b[b], {}, {"bindparam": assert_b_params.append})
# note we're asserting the order of the params as well as
# if there are dupes or not. ordering has to be deterministic
# and matches what a traversal would provide.
eq_(a_params["bindparams"], assert_a_params)
eq_(b_params["bindparams"], assert_b_params)
def test_compare_col_identity(self):
stmt1 = (select([table_a.c.a, table_b.c.b
]).where(table_a.c.a == table_b.c.b).alias())
stmt1_c = (select([table_a.c.a, table_b.c.b
]).where(table_a.c.a == table_b.c.b).alias())
stmt2 = union(select([table_a]), select([table_b]))
stmt3 = select([table_b])
equivalents = {table_a.c.a: [table_b.c.a]}
is_false(
stmt1.compare(stmt2, use_proxies=True, equivalents=equivalents))
is_true(
stmt1.compare(stmt1_c, use_proxies=True, equivalents=equivalents))
is_true((table_a.c.a == table_b.c.b).compare(
stmt1.c.a == stmt1.c.b,
use_proxies=True,
equivalents=equivalents,
))
def test_copy_internals(self):
for fixture in self.fixtures:
case_a = fixture()
case_b = fixture()
assert case_a[0].compare(case_b[0])
clone = case_a[0]._clone()
clone._copy_internals()
assert clone.compare(case_b[0])
stack = [clone]
seen = {clone}
found_elements = False
while stack:
obj = stack.pop(0)
items = [
subelem for key, elem in clone.__dict__.items()
if key != "_is_clone_of" and elem is not None
for subelem in util.to_list(elem)
if (isinstance(subelem, (ColumnElement, ClauseList))
and subelem not in seen and not isinstance(
subelem, Immutable) and subelem is not case_a[0])
]
stack.extend(items)
seen.update(items)
if obj is not clone:
found_elements = True
# ensure the element will not compare as true
obj.compare = lambda other, **kw: False
obj.__visit_name__ = "dont_match"
if found_elements:
assert not clone.compare(case_b[0])
assert case_a[0].compare(case_b[0])
def show_venue(venue_id):
# shows the venue page with the given venue_id
# TODO: replace with real venue data from the venues table, using venue_id
data = {}
venue = Venue.query.get(venue_id)
if venue is None:
flash('There is no venue with enterd ID, Try with another ID')
return redirect(url_for('index'))
data["id"] = venue.id
data["name"] = venue.name
data["address"] = venue.address
data["city"] = venue.city
data["state"] = venue.state
data["phone"] = venue.phone
data["website"] = venue.website
data["facebook_link"] = venue.facebook_link
data["seeking_talent"] = venue.seeking_talent
data["seeking_description"] = venue.seeking_description
data["image_link"] = venue.image_link
geners = []
for g in Venue.query.get(venue_id).geners:
geners.append(g.name)
data["genres"] = geners
past_shows = db.session.query(
Artist.name.label('artist_name'), Artist.id.label('artist_id'),
Artist.image_link.label('artist_image_link'),
Shows.date.label('start_time')).join(Artist).filter(
Shows.date < func.current_date(), Shows.venue_id == venue_id)
upcoming_shows = db.session.query(
Artist.name.label('artist_name'), Artist.id.label('artist_id'),
Artist.image_link.label('artist_image_link'),
Shows.date.label('start_time')).join(Artist).filter(
Shows.date >= func.current_date(), Shows.venue_id == venue_id)
data["past_shows"] = list(past_shows.all())
data["past_shows_count"] = past_shows.count()
data["upcoming_shows"] = list(upcoming_shows.all())
data["upcoming_shows_count"] = upcoming_shows.count()
print(data)
# data1={
# "id": 1,
# "name": "The Musical Hop",
# "genres": ["Jazz", "Reggae", "Swing", "Classical", "Folk"],
# "address": "1015 Folsom Street",
# "city": "San Francisco",
# "state": "CA",
# "phone": "123-123-1234",
# "website": "https://www.themusicalhop.com",
# "facebook_link": "https://www.facebook.com/TheMusicalHop",
# "seeking_talent": True,
# "seeking_description": "We are on the lookout for a local artist to play every two weeks. Please call us.",
# "image_link": "https://images.unsplash.com/photo-1543900694-133f37abaaa5?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=400&q=60",
# "past_shows": [{
# "artist_id": 4,
# "artist_name": "Guns N Petals",
# "artist_image_link": "https://images.unsplash.com/photo-1549213783-8284d0336c4f?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=300&q=80",
# "start_time": "2019-05-21T21:30:00.000Z"
# }],
# "upcoming_shows": [],
# "past_shows_count": 1,
# "upcoming_shows_count": 0,
# }
# data2={
# "id": 2,
# "name": "The Dueling Pianos Bar",
# "genres": ["Classical", "R&B", "Hip-Hop"],
# "address": "335 Delancey Street",
# "city": "New York",
# "state": "NY",
# "phone": "914-003-1132",
# "website": "https://www.theduelingpianos.com",
# "facebook_link": "https://www.facebook.com/theduelingpianos",
# "seeking_talent": False,
# "image_link": "https://images.unsplash.com/photo-1497032205916-ac775f0649ae?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=750&q=80",
# "past_shows": [],
# "upcoming_shows": [],
# "past_shows_count": 0,
# "upcoming_shows_count": 0,
# }
# data3={
# "id": 3,
# "name": "Park Square Live Music & Coffee",
# "genres": ["Rock n Roll", "Jazz", "Classical", "Folk"],
# "address": "34 Whiskey Moore Ave",
# "city": "San Francisco",
# "state": "CA",
# "phone": "415-000-1234",
# "website": "https://www.parksquarelivemusicandcoffee.com",
# "facebook_link": "https://www.facebook.com/ParkSquareLiveMusicAndCoffee",
# "seeking_talent": False,
# "image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80",
# "past_shows": [{
# "artist_id": 5,
# "artist_name": "Matt Quevedo",
# "artist_image_link": "https://images.unsplash.com/photo-1495223153807-b916f75de8c5?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=334&q=80",
# "start_time": "2019-06-15T23:00:00.000Z"
# }],
# "upcoming_shows": [{
# "artist_id": 6,
# "artist_name": "The Wild Sax Band",
# "artist_image_link": "https://images.unsplash.com/photo-1558369981-f9ca78462e61?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=794&q=80",
# "start_time": "2035-04-01T20:00:00.000Z"
# }, {
# "artist_id": 6,
# "artist_name": "The Wild Sax Band",
# "artist_image_link": "https://images.unsplash.com/photo-1558369981-f9ca78462e61?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=794&q=80",
# "start_time": "2035-04-08T20:00:00.000Z"
# }, {
# "artist_id": 6,
# "artist_name": "The Wild Sax Band",
# "artist_image_link": "https://images.unsplash.com/photo-1558369981-f9ca78462e61?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=794&q=80",
# "start_time": "2035-04-15T20:00:00.000Z"
# }],
# "past_shows_count": 1,
# "upcoming_shows_count": 1,
# }
# data = list(filter(lambda d: d['id'] == venue_id, [data1, data2, data3]))[0]
return render_template('pages/show_venue.html', venue=data)
def show_artist(artist_id):
# shows the venue page with the given venue_id
# TODO: replace with real venue data from the venues table, using venue_id
data = {}
artist = Artist.query.get(artist_id)
if artist is None:
flash('There is no artist with enterd ID, Try with another ID')
return redirect(url_for('index'))
data["id"] = artist.id
data["name"] = artist.name
geners = []
for g in Artist.query.get(artist_id).geners:
geners.append(g.name)
data["genres"] = geners
data["city"] = artist.city
data["state"] = artist.state
data["phone"] = artist.phone
data["website"] = artist.website
data["facebook_link"] = artist.facebook_link
data["seeking_venue"] = artist.seeking_venue
data["seeking_description"] = artist.seeking_description
data["image_link"] = artist.image_link
past_shows = db.session.query(
Venue.name.label('venue_name'), Venue.id.label('venue_id'),
Venue.image_link.label('venue_image_link'),
Shows.date.label('start_time')).join(Venue).filter(
Shows.date < func.current_date(), Shows.artist_id == artist_id)
upcoming_shows = db.session.query(
Venue.name.label('venue_name'), Venue.id.label('venue_id'),
Venue.image_link.label('venue_image_link'),
Shows.date.label('start_time')).join(Venue).filter(
Shows.date >= func.current_date(), Shows.artist_id == artist_id)
data["past_shows"] = list(past_shows.all())
data["past_shows_count"] = past_shows.count()
data["upcoming_shows"] = list(upcoming_shows.all())
data["upcoming_shows_count"] = upcoming_shows.count()
# data1={
# "id": 4,
# "name": "Guns N Petals",
# "genres": ["Rock n Roll"],
# "city": "San Francisco",
# "state": "CA",
# "phone": "326-123-5000",
# "website": "https://www.gunsnpetalsband.com",
# "facebook_link": "https://www.facebook.com/GunsNPetals",
# "seeking_venue": True,
# "seeking_description": "Looking for shows to perform at in the San Francisco Bay Area!",
# "image_link": "https://images.unsplash.com/photo-1549213783-8284d0336c4f?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=300&q=80",
# "past_shows": [{
# "venue_id": 1,
# "venue_name": "The Musical Hop",
# "venue_image_link": "https://images.unsplash.com/photo-1543900694-133f37abaaa5?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=400&q=60",
# "start_time": "2019-05-21T21:30:00.000Z"
# }],
# "upcoming_shows": [],
# "past_shows_count": 1,
# "upcoming_shows_count": 0,
# }
# data2={
# "id": 5,
# "name": "Matt Quevedo",
# "genres": ["Jazz"],
# "city": "New York",
# "state": "NY",
# "phone": "300-400-5000",
# "facebook_link": "https://www.facebook.com/mattquevedo923251523",
# "seeking_venue": False,
# "image_link": "https://images.unsplash.com/photo-1495223153807-b916f75de8c5?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=334&q=80",
# "past_shows": [{
# "venue_id": 3,
# "venue_name": "Park Square Live Music & Coffee",
# "venue_image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80",
# "start_time": "2019-06-15T23:00:00.000Z"
# }],
# "upcoming_shows": [],
# "past_shows_count": 1,
# "upcoming_shows_count": 0,
# }
# data3={
# "id": 6,
# "name": "The Wild Sax Band",
# "genres": ["Jazz", "Classical"],
# "city": "San Francisco",
# "state": "CA",
# "phone": "432-325-5432",
# "seeking_venue": False,
# "image_link": "https://images.unsplash.com/photo-1558369981-f9ca78462e61?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=794&q=80",
# "past_shows": [],
# "upcoming_shows": [{
# "venue_id": 3,
# "venue_name": "Park Square Live Music & Coffee",
# "venue_image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80",
# "start_time": "2035-04-01T20:00:00.000Z"
# }, {
# "venue_id": 3,
# "venue_name": "Park Square Live Music & Coffee",
# "venue_image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80",
# "start_time": "2035-04-08T20:00:00.000Z"
# }, {
# "venue_id": 3,
# "venue_name": "Park Square Live Music & Coffee",
# "venue_image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80",
# "start_time": "2035-04-15T20:00:00.000Z"
# }],
# "past_shows_count": 0,
# "upcoming_shows_count": 3,
# }
# data = list(filter(lambda d: d['id'] == artist_id, [data1, data2, data3]))[0]
return render_template('pages/show_artist.html', artist=data)
metadata = sa.MetaData()
chats = sa.Table('chats', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('telegram_id', sa.Integer, nullable=False),
sa.Column('chat_type', sa.String(50)),
sa.Column('username', sa.String(200)),
sa.Column('first_name', sa.String(200)))
notes = sa.Table(
'notes', metadata, sa.Column('id', UUID(as_uuid=True), primary_key=True),
sa.Column('chat_id', sa.ForeignKey(chats.c.telegram_id), nullable=False),
sa.Column('note_text', sa.Text, nullable=False),
sa.Column('note_time', sa.DateTime, default=datetime.utcnow),
sa.Column('created', sa.DateTime, default=datetime.utcnow))
sermons = sa.Table('sermons', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('meta_info', sa.String(200)),
sa.Column('title', sa.String(200)),
sa.Column('sutta', sa.String(200)),
sa.Column('sermon_text', sa.Text, nullable=False))
currency_rates = sa.Table(
'currency_rates', metadata, sa.Column('rates', sa.JSON, nullable=False),
sa.Column('base', sa.String(10), primary_key=True),
sa.Column('lookup_date',
sa.Date,
primary_key=True,
default=func.current_date()))
class PriceHistory(db.Model):
__table_args__ = (UniqueConstraint('ticker_id',
'date',
name='_ticker__date'), )
id = db.Column(db.Integer, primary_key=True)
ticker_id = db.Column(db.Integer,
db.ForeignKey('ticker.id'),
nullable=False)
date = db.Column(db.Date,
nullable=False,
server_default=func.current_date())
open = db.Column(db.Float(decimal_return_scale=2), nullable=False)
close = db.Column(db.Float(decimal_return_scale=2), nullable=False)
high = db.Column(db.Float(decimal_return_scale=2), nullable=False)
low = db.Column(db.Float(decimal_return_scale=2), nullable=False)
volume = db.Column(db.Integer, nullable=False)
@classmethod
def get_or_create(cls, ticker=None, date=None, **kwargs):
instance = cls.query.filter_by(ticker_id=ticker.id, date=date).first()
if instance:
return instance, False
else:
kwargs['volume'] = kwargs['volume'].replace(',', '')
instance = cls(ticker=ticker, date=date, **kwargs)
db.session.add(instance)
return instance, True
def update(self, commit=False, **kwargs):
for key, value in kwargs.items():
setattr(self, key, value)
if commit:
db.session.commit()
@classmethod
def get_analytics(cls, ticker_name, date_from, date_to):
PricesA = aliased(cls)
PricesB = aliased(cls)
result = db.session.query(
label('open', func.abs(PricesA.open - PricesB.open)),
label('close', func.abs(PricesA.close - PricesB.close)),
label('low', func.abs(PricesA.low - PricesB.low)),
label('high',
func.abs(PricesA.close - PricesB.high))).join(Ticker, ).join(
PricesB, and_(PricesB.ticker_id == Ticker.id)).filter(
Ticker.name == ticker_name,
PricesA.date == date_from, PricesB.date == date_to)
return result
@classmethod
def get_delta(cls, ticker_name, type_price, value):
sql = text(
DELTA_SELECT.format(ticker_name=ticker_name,
type_price=type_price,
value_delta=value))
result = db.engine.execute(sql)
return result.fetchall()
def __repr__(self):
return '<{} = {}, {}'.format(self.ticker.name, self.volume, self.date)
def query_work_day_stats(
company_id,
start_date=None,
end_date=None,
first=None,
after=None,
tzname="Europe/Paris",
):
tz = gettz(tzname)
if after:
max_time, user_id_ = parse_datetime_plus_id_cursor(after)
max_date = max_time.date()
end_date = min(max_date, end_date) if end_date else max_date
query = (Activity.query.join(Mission).join(
Expenditure,
and_(
Activity.user_id == Expenditure.user_id,
Activity.mission_id == Expenditure.mission_id,
),
isouter=True,
).with_entities(
Activity.id,
Activity.user_id,
Activity.mission_id,
Mission.name,
Activity.start_time,
Activity.end_time,
Activity.type,
Expenditure.id.label("expenditure_id"),
Expenditure.type.label("expenditure_type"),
func.generate_series(
func.date_trunc(
"day",
func.timezone(
tzname,
func.timezone("UTC", Activity.start_time),
),
),
func.timezone(
tzname,
func.coalesce(
func.timezone("UTC", Activity.end_time),
func.now(),
),
),
"1 day",
).label("day"),
).filter(
Mission.company_id == company_id,
~Activity.is_dismissed,
Activity.start_time != Activity.end_time,
))
query = _apply_time_range_filters(
query,
to_datetime(start_date, tz_for_date=tz),
to_datetime(end_date,
tz_for_date=tz,
convert_dates_to_end_of_day_times=True),
)
has_next_page = False
if first:
activity_first = max(first * 5, 200)
query = query.order_by(desc("day"), desc(
Activity.user_id)).limit(activity_first + 1)
has_next_page = query.count() > activity_first
query = query.subquery()
query = (db.session.query(query).group_by(
query.c.user_id, query.c.day, query.c.mission_id,
query.c.name).with_entities(
query.c.user_id.label("user_id"),
query.c.day,
func.timezone("UTC",
func.timezone(tzname,
query.c.day)).label("utc_day_start"),
query.c.mission_id.label("mission_id"),
query.c.name.label("mission_name"),
func.min(
func.greatest(
query.c.start_time,
func.timezone("UTC", func.timezone(tzname, query.c.day)),
)).label("start_time"),
func.max(
func.least(
func.timezone(
"UTC",
func.timezone(
tzname,
query.c.day + func.cast("1 day", Interval)),
),
func.coalesce(query.c.end_time, func.now()),
)).label("end_time"),
func.bool_or(
and_(
query.c.end_time.is_(None),
query.c.day == func.current_date(),
)).label("is_running"),
*[
func.sum(
case(
[(
query.c.type == a_type.value,
extract(
"epoch",
func.least(
func.timezone(
"UTC",
func.timezone(
tzname,
query.c.day +
func.cast("1 day", Interval),
),
),
func.coalesce(query.c.end_time,
func.now()),
) - func.greatest(
query.c.start_time,
func.timezone(
"UTC",
func.timezone(tzname, query.c.day),
),
),
),
)],
else_=0,
)).label(f"{a_type.value}_duration")
for a_type in ActivityType
],
func.greatest(func.count(distinct(query.c.expenditure_id)),
1).label("n_exp_dups"),
func.count(distinct(query.c.id)).label("n_act_dups"),
*[
func.sum(
case(
[(query.c.expenditure_type == e_type.value, 1)],
else_=0,
)).label(f"n_{e_type.value}_expenditures")
for e_type in ExpenditureType
],
).subquery())
query = (db.session.query(query).group_by(
query.c.user_id, query.c.day).with_entities(
query.c.user_id.label("user_id"),
query.c.day,
func.array_agg(distinct(
query.c.mission_name)).label("mission_names"),
func.min(query.c.start_time).label("start_time"),
func.max(query.c.end_time).label("end_time"),
func.bool_or(query.c.is_running).label("is_running"),
*[
func.sum(
getattr(query.c, f"{a_type.value}_duration") /
query.c.n_exp_dups).cast(Integer).label(
f"{a_type.value}_duration") for a_type in ActivityType
],
*[
func.sum(
getattr(query.c, f"n_{e_type.value}_expenditures") /
query.c.n_act_dups).cast(Integer).label(
f"n_{e_type.value}_expenditures")
for e_type in ExpenditureType
],
).order_by(desc("day"), desc("user_id")).subquery())
query = db.session.query(query).with_entities(
*query.c,
extract("epoch", query.c.end_time -
query.c.start_time).label("service_duration"),
reduce(
lambda a, b: a + b,
[
getattr(query.c, f"{a_type.value}_duration")
for a_type in ActivityType
],
).label("total_work_duration"),
)
results = query.all()
if after:
results = [
r for r in results if r.day.date() < max_date or (
r.day.date() == max_date and r.user_id < user_id_)
]
if first:
if has_next_page:
# The last work day may be incomplete because we didn't fetch all the activities => remove it
results = results[:-1]
if len(results) > first:
results = results[:first]
has_next_page = True
return results, has_next_page
