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 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)))