def visit_binary_op(self, expr):
if isinstance(expr, Power):
op = func.pow
elif isinstance(expr, FloorDivide):
op = operator.div if six.PY2 else operator.truediv
elif isinstance(expr, (Add, Substract)) and expr.dtype == df_types.datetime:
if isinstance(expr, Add) and \
all(child.dtype == df_types.datetime for child in (expr.lhs, expr.rhs)):
raise CompileError('Cannot add two datetimes')
if isinstance(expr.rhs, DTScalar) or (isinstance(expr, Add) and expr.lhs, DTScalar):
if isinstance(expr.rhs, DTScalar):
dt, scalar = expr.lhs, expr.rhs
else:
dt, scalar = expr.rhs, expr.lhs
val = scalar.value
if isinstance(expr, Substract):
val = -val
dt_type = type(scalar).__name__[:-6]
sa_dt = self._expr_to_sqlalchemy[dt]
try:
key = DATE_KEY_DIC[dt_type]
except KeyError:
raise NotImplementedError
if self._sa_engine and self._sa_engine.name == 'mysql':
if dt_type == 'MilliSecond':
val, dt_type = val * 1000, 'MicroSecond'
sa_expr = func.date_add(sa_dt, text('interval %d %s' % (val, dt_type.lower())))
else:
sa_expr = sa_dt + timedelta(**{key: val})
self._add(expr, sa_expr)
return
else:
raise NotImplementedError
elif isinstance(expr, Substract) and expr._lhs.dtype == df_types.datetime and \
expr._rhs.dtype == df_types.datetime:
sa_expr = self._expr_to_sqlalchemy[expr._lhs] - self._expr_to_sqlalchemy[expr._rhs]
if self._sa_engine and self._sa_engine.name == 'mysql':
sa_expr = func.abs(func.microsecond(sa_expr)
.cast(types.df_type_to_sqlalchemy_type(expr.dtype))) / 1000
else:
sa_expr = func.abs(extract('MICROSECONDS', sa_expr)
.cast(types.df_type_to_sqlalchemy_type(expr.dtype))) / 1000
self._add(expr, sa_expr)
return
elif isinstance(expr, Mod):
lhs, rhs = self._expr_to_sqlalchemy[expr._lhs], self._expr_to_sqlalchemy[expr._rhs]
sa_expr = BINARY_OP[expr.node_name](lhs, rhs)
if not is_constant_scalar(expr._rhs):
sa_expr = case([(rhs > 0, func.abs(sa_expr))], else_=sa_expr)
elif expr._rhs.value > 0:
sa_expr = func.abs(sa_expr)
self._add(expr, sa_expr)
return
else:
op = BINARY_OP[expr.node_name]
lhs, rhs = self._expr_to_sqlalchemy[expr._lhs], self._expr_to_sqlalchemy[expr._rhs]
sa_expr = op(lhs, rhs)
self._add(expr, sa_expr)
class Human(Base):
'''
Human accesses the Association as a One to Many relationship
'''
__tablename__ = 'humans'
id = Column(Integer, primary_key=True)
name = Column(String)
carsAssoc = relationship('HumanCarAssociation')
e = func.date_add(func.now(), bindparam('e', func.text('INTERVAL 5 day')))
def clearOldNotifications(conn):
"""Clear old notifications
:param conn: SQLAlchemy connection object
:type conn: sqlalchemy.engine.base.Connection
"""
delete = schema.notification.delete().where( # pylint: disable=E1120
func.date_add(schema.notification.c.timestamp, text("INTERVAL 30 DAY")) < func.utc_timestamp()
)
conn.execute(delete)
def clearOldNotifications(conn):
"""Clear old notifications
:param conn: SQLAlchemy connection object
:type conn: sqlalchemy.engine.base.Connection
"""
delete = (
schema.notification # pylint: disable=E1120
.delete().where(
func.date_add(schema.notification.c.timestamp,
text("INTERVAL 30 DAY")) < func.utc_timestamp()))
conn.execute(delete)
def deleteStaleNotificationDevices(conn, days):
"""Deletes devices from notifications if they haven't been active recently.
:param conn: SQLAlchemy connection object
:type conn: sqlalchemy.engine.base.Connection
:param days: the number of days of absense before considering a device stale
:type days: int
"""
query = schema.notification_settings.delete().where( # pylint: disable=E1120
func.date_add(schema.notification_settings.c.last_timestamp,
text("INTERVAL %i DAY" % days)) < func.utc_timestamp())
conn.execute(query)
def deleteStaleNotificationDevices(conn, days):
"""Deletes devices from notifications if they haven't been active recently.
:param conn: SQLAlchemy connection object
:type conn: sqlalchemy.engine.base.Connection
:param days: the number of days of absense before considering a device stale
:type days: int
"""
query = schema.notification_settings.delete().where( # pylint: disable=E1120
func.date_add(schema.notification_settings.c.last_timestamp,
text("INTERVAL %i DAY" % days)) <
func.utc_timestamp())
conn.execute(query)
def get_interval_query(start, end, interval_key, include_end_date=False):
"""Get a query for the start & end dates of all of the periods of the given interval type.
:param start: Begin with the interval which contains this date or datetime
:param end: End with the interval which contains this date or datetime
:param interval_key: one of the INTERVAL constants (from ehr_dao.py)
:param include_end_date: should the query include `end_date` as a field?
defaults to False
:return: a `sqlalchemy.select` query
"""
if interval_key == INTERVAL_DAY:
start_field = Calendar.day
end_interval_offset = text('interval 1 day')
elif interval_key == INTERVAL_WEEK:
start_field = func.str_to_date(
func.concat(func.yearweek(Calendar.day), 'Sunday'), '%X%V%W')
end_interval_offset = text('interval 1 week')
elif interval_key == INTERVAL_MONTH:
start_field = func.str_to_date(
func.date_format(Calendar.day, "%Y%m01"), "%Y%m%d")
end_interval_offset = text('interval 1 month')
elif interval_key == INTERVAL_QUARTER:
start_field = func.date(
func.concat(
func.year(Calendar.day), '-',
func.lpad((func.quarter(Calendar.day) - 1) * 3 + 1, 2,
'0'), '-01'))
end_interval_offset = text('interval 1 quarter')
else:
raise NotImplemented(
"invalid interval: {interval}".format(interval=interval_key))
start_date_query = (select([
start_field.label('start_date')
]).where((Calendar.day >= start) & (Calendar.day <= end)).group_by(
start_field).alias('start_date_query'))
end_date_field = (func.date_add(start_date_query.c.start_date,
end_interval_offset).label('end_date'))
fields = [start_date_query.c.start_date.label('start_date')]
if include_end_date:
fields.append(end_date_field)
return select(fields).alias('interval_query')
def addNotification(conn, uid, server, metric, rowid, device, windowsize,
timestamp, acknowledged, seen):
"""Add Notification
:param conn: SQLAlchemy Connection object for executing SQL
:type conn: sqlalchemy.engine.Connection
:param uid: Notification uid
:param server: Metric server
:param metric: Metric uid
:param rowid: Metric Data row id
:param device: Device id (notification_settings.uid)
:param windowsize: Window size (seconds)
:param timestamp: Metric Data timestamp
:param acknowledged:
:param seen:
:returns: Result
:rtype: sqlalchemy.engine.ResultProxy
"""
result = None
with conn.begin():
# Secure a write lock on notification table. Other processes attempting to
# access the notification table will be blocked until the lock is released.
# This is to ensure that in the time between the first SELECT and the
# followup INSERT there are no other potentially conflicting INSERTS that
# could result in duplicated notifications. Meanwhile, other processes may
# execute concurrent inserts to metric table.
conn.execute("LOCK TABLES notification WRITE, metric READ;")
try:
# New notification is potentially a duplicate if there exists an unseen
# notification for the same metric and server created within the
# requested windowsize.
query = (
select([func.count(schema.notification.c.uid)])
.select_from(
schema.notification.outerjoin(
schema.metric,
schema.metric.c.uid == schema.notification.c.metric))
.where(
(schema.metric.c.server == server) &
(schema.notification.c.device == device) &
(schema.notification.c.seen == 0) &
(func.date_add(schema.notification.c.timestamp,
text("INTERVAL :windowsize SECOND")) >
timestamp))
)
if conn.execute(query, windowsize=windowsize).scalar() == 0:
# Previous query yielded no results, notification is unique according
# to our constraints. Insert new notification details.
ins = (schema.notification.insert().values(uid=uid,
metric=metric,
device=device,
windowsize=windowsize,
timestamp=timestamp,
acknowledged=acknowledged,
seen=seen,
rowid=rowid))
try:
result = conn.execute(ins)
except IntegrityError:
result = None
finally:
conn.execute("UNLOCK TABLES;") # Release table lock.
return result
def addNotification(conn, uid, server, metric, rowid, device, windowsize,
timestamp, acknowledged, seen):
"""Add Notification
:param conn: SQLAlchemy Connection object for executing SQL
:type conn: sqlalchemy.engine.Connection
:param uid: Notification uid
:param server: Metric server
:param metric: Metric uid
:param rowid: Metric Data row id
:param device: Device id (notification_settings.uid)
:param windowsize: Window size (seconds)
:param timestamp: Metric Data timestamp
:param acknowledged:
:param seen:
:returns: Result
:rtype: sqlalchemy.engine.ResultProxy
"""
result = None
with conn.begin():
# Secure a write lock on notification table. Other processes attempting to
# access the notification table will be blocked until the lock is released.
# This is to ensure that in the time between the first SELECT and the
# followup INSERT there are no other potentially conflicting INSERTS that
# could result in duplicated notifications. Meanwhile, other processes may
# execute concurrent inserts to metric table.
conn.execute("LOCK TABLES notification WRITE, metric READ;")
try:
# New notification is potentially a duplicate if there exists an unseen
# notification for the same metric and server created within the
# requested windowsize.
query = (select([func.count(
schema.notification.c.uid)]).select_from(
schema.notification.outerjoin(
schema.metric,
schema.metric.c.uid == schema.notification.c.metric)
).where((schema.metric.c.server == server)
& (schema.notification.c.device == device)
& (schema.notification.c.seen == 0) & (func.date_add(
schema.notification.c.timestamp,
text("INTERVAL :windowsize SECOND")) > timestamp)))
if conn.execute(query, windowsize=windowsize).scalar() == 0:
# Previous query yielded no results, notification is unique according
# to our constraints. Insert new notification details.
ins = (
schema.notification.insert().values( #pylint: disable=E1120
uid=uid,
metric=metric,
device=device,
windowsize=windowsize,
timestamp=timestamp,
acknowledged=acknowledged,
seen=seen,
rowid=rowid))
try:
result = conn.execute(ins)
except IntegrityError:
result = None
finally:
conn.execute("UNLOCK TABLES;") # Release table lock.
return result
async def command_admin(self, message):
action = message['action']
if self.player.admin:
if action == "closeFA":
player = self.player_service[message['user_id']]
if player:
self._logger.warning(
'Administrative action: %s closed game for %s',
self.player, player)
with contextlib.suppress(DisconnectedError):
await player.send_message({
"command": "notice",
"style": "kill",
})
elif action == "closelobby":
player = self.player_service[message['user_id']]
ban_fail = None
if player:
if 'ban' in message:
reason = message['ban'].get('reason', 'Unspecified')
duration = int(message['ban'].get('duration', 1))
period = message['ban'].get('period', 'SECOND').upper()
self._logger.warning(
'Administrative action: %s closed client for %s with %s ban (Reason: %s)',
self.player, player, duration, reason)
async with self._db.acquire() as conn:
try:
result = await conn.execute(
select([lobby_ban.c.reason]).where(
and_(
lobby_ban.c.idUser ==
message["user_id"],
lobby_ban.c.expires_at >
func.now())))
row = await result.fetchone()
if row:
ban_fail = row[0]
else:
if period not in [
"SECOND", "DAY", "WEEK", "MONTH"
]:
self._logger.warning(
'Tried to ban player with invalid period'
)
raise ClientError(
f"Period '{period}' is not allowed!"
)
await conn.execute(ban.insert().values(
player_id=player.id,
author_id=self.player.id,
reason=reason,
expires_at=func.date_add(
func.now(),
text(f"interval :duration {period}"
)),
level='GLOBAL'),
duration=duration)
except pymysql.MySQLError as e:
raise ClientError(
'Your ban attempt upset the database: {}'.
format(e))
else:
self._logger.warning(
'Administrative action: %s closed client for %s',
self.player, player)
if player.lobby_connection is not None:
with contextlib.suppress(DisconnectedError):
await player.lobby_connection.kick()
if ban_fail:
raise ClientError(
"Kicked the player, but he was already banned!")
elif action == "broadcast":
message_text = message.get('message')
if not message_text:
return
tasks = []
for player in self.player_service:
# Check if object still exists:
# https://docs.python.org/3/library/weakref.html#weak-reference-objects
if player.lobby_connection is not None:
tasks.append(
player.lobby_connection.send_warning(message_text))
self._logger.info("%s broadcasting message to all players: %s",
self.player.login, message_text)
await gather_without_exceptions(tasks, Exception)
if self.player.mod:
if action == "join_channel":
user_ids = message['user_ids']
channel = message['channel']
tasks = []
for user_id in user_ids:
player = self.player_service[user_id]
if player and player.lobby_connection is not None:
tasks.append(
player.send_message({
"command": "social",
"autojoin": [channel]
}))
await gather_without_exceptions(tasks, DisconnectedError)
async def command_admin(self, message):
action = message['action']
if self.player.admin:
if action == "closeFA":
player = self.player_service[message['user_id']]
if player:
self._logger.warning(
'Administrative action: %s closed game for %s',
self.player, player)
player.lobby_connection.sendJSON(
dict(command="notice", style="kill"))
player.lobby_connection.sendJSON(
dict(
command="notice",
style="info",
text=
("Your game was closed by an administrator ({admin_name}). "
"Please refer to our rules for the lobby/game here {rule_link}."
.format(admin_name=self.player.login,
rule_link=config.RULE_LINK))))
elif action == "closelobby":
player = self.player_service[message['user_id']]
ban_fail = None
if player:
if 'ban' in message:
reason = message['ban'].get('reason', 'Unspecified')
duration = int(message['ban'].get('duration', 1))
period = message['ban'].get('period', 'SECOND').upper()
self._logger.warning(
'Administrative action: %s closed client for %s with %s ban (Reason: %s)',
self.player, player, duration, reason)
async with db.engine.acquire() as conn:
try:
result = await conn.execute(
"SELECT reason from lobby_ban WHERE idUser=%s AND expires_at > NOW()",
(message['user_id']))
row = await result.fetchone()
if row:
ban_fail = row[0]
else:
if period not in [
"SECOND", "DAY", "WEEK", "MONTH"
]:
self._logger.warning(
'Tried to ban player with invalid period'
)
raise ClientError(
f"Period '{period}' is not allowed!"
)
# NOTE: Text formatting in sql string is only ok because we just checked it's value
await conn.execute(ban.insert().values(
player_id=player.id,
author_id=self.player.id,
reason=reason,
expires_at=func.date_add(
func.now(),
text(f"interval :duration {period}"
)),
level='GLOBAL'),
duration=duration)
except pymysql.MySQLError as e:
raise ClientError(
'Your ban attempt upset the database: {}'.
format(e))
else:
self._logger.warning(
'Administrative action: %s closed client for %s',
self.player, player)
player.lobby_connection.kick(message=(
"You were kicked from FAF by an administrator ({admin_name}). "
"Please refer to our rules for the lobby/game here {rule_link}."
.format(admin_name=self.player.login,
rule_link=config.RULE_LINK)))
if ban_fail:
raise ClientError(
"Kicked the player, but he was already banned!")
elif action == "broadcast":
for player in self.player_service:
try:
if player.lobby_connection:
player.lobby_connection.send_warning(
message.get('message'))
except Exception as ex:
self._logger.debug(
"Could not send broadcast message to %s: %s".
format(player, ex))
elif self.player.mod:
if action == "join_channel":
user_ids = message['user_ids']
channel = message['channel']
for user_id in user_ids:
player = self.player_service[message[user_id]]
if player:
player.lobby_connection.sendJSON(
dict(command="social", autojoin=[channel]))
def mysql_utc_after(element, compiler, **kwargs):
days, = list(element.clauses)
return compiler.process(func.date_add(utc_now(), text('interval %s day' % days.value)))
