def authenticate(cls, username, password, session=None): from sqlalchemy import func try: if int(username) == -1: acc = cls() acc._fill_trygame() return acc except: pass user = cls.find(username) if not user: return False if not cls.validate_by_password(user, password): return False # sync dz_member = user.dz_member user.id = dz_member.uid user.username = dz_member.username user.password = password_hash(password) user.email = dz_member.email user.title = dz_member.member_field.customstatus user.status = dz_member.status acc = cls() acc._fill_account(user) user.lastactivity = func.unix_timestamp() dz_member.member_status.lastactivity = func.unix_timestamp() return acc
def find_unvalidated_versions(time_delta, environment): """Find the latest deployments that are not validated in a given environment for a given amount of time """ subq = (Session.query(Package.pkg_name, Package.version, Package.revision, AppDefinition.app_type, AppDeployment.environment, AppDeployment.realized, AppDeployment.user, AppDeployment.status) .join(AppDeployment) .join(AppDefinition) .filter(AppDeployment.status!='invalidated') .filter(AppDeployment.environment==environment) .order_by(AppDeployment.realized.desc(), AppDeployment.id.desc()) .subquery(name='t_ordered')) return (Session.query(subq.c.pkg_name, subq.c.version, subq.c.revision, subq.c.appType, subq.c.environment, subq.c.realized, subq.c.user, subq.c.status) .group_by(subq.c.appType, subq.c.environment, subq.c.pkg_name) .having(and_(subq.c.status.like('%complete'), func.unix_timestamp(subq.c.realized) < func.unix_timestamp(func.now()) - time_delta)) .all())
def start_client(step): try: QSIZE = 1024 CRATIO = 120 # Common ratio for send interval progression db_manager = util.DBManager(world.config['connections']['mysql']) db = db_manager.get_db() where1 = and_(db.messages.type=='out') where2 = and_(db.messages.message_version==2) where3 = and_(func.unix_timestamp(db.messages.dtlasthandleattempt) +\ db.messages.handle_attempts * CRATIO < func.unix_timestamp( func.now())) msgs = db.messages.filter(db.messages.status==0,\ where1, where2, where3).order_by( desc(db.messages.id)).all()[0:QSIZE] global right_msgs right_msgs = msgs cnf = ETC_DIR + '/config.yml' subps.Popen(['python', '-m', 'scalrpy.messaging.messaging_client', '--start', '-c', cnf]) time.sleep(2) ps = subps.Popen(['ps -ef'], shell=True, stdout=subps.PIPE) output = ps.stdout.read() ps.stdout.close() ps.wait() assert 'messaging_client' in output except: assert False finally: db.session.close() db.session.remove()
def start_daemon(step): try: QSIZE = 1024 CRATIO = 120 # Common ratio for send interval progression db_manager = dbmanager.DBManager(world.config['connections']['mysql']) db = db_manager.get_db() where1 = and_(db.messages.type == 'out') where2 = and_(db.messages.message_version == 2) where3 = and_(func.unix_timestamp(db.messages.dtlasthandleattempt) +\ db.messages.handle_attempts * CRATIO < func.unix_timestamp( func.now())) msgs = db.messages.filter(db.messages.status==0,\ where1, where2, where3).order_by( desc(db.messages.id)).all()[0:QSIZE] world.right_msgs = [msg.messageid for msg in msgs] assert len(world.right_msgs) != 0 cnf = ETC_DIR + '/config.yml' subps.Popen([ 'python', '-m', 'scalrpy.messaging', '--start', '-vvv', '-c', cnf ]) time.sleep(2) ps = subps.Popen(['ps -ef'], shell=True, stdout=subps.PIPE) output = ps.stdout.read() ps.stdout.close() ps.wait() assert 'scalrpy.messaging --start' in output except Exception: assert False finally: db.session.close() db.session.remove()
def start_daemon(step): db_manager = dbmanager.DBManager(world.config['connections']['mysql'], autoflush=False) db = db_manager.get_db() try: CRATIO = 120 where = and_( db.messages.status == 0, db.messages.type == 'out', db.messages.message_version == 2, db.messages.message != '', db.messages.message != None, func.unix_timestamp(db.messages.dtlasthandleattempt) + db.messages.handle_attempts * CRATIO < func.unix_timestamp( func.now())) msgs = db.messages.filter(where).order_by(desc(db.messages.id)).all() world.right_msgs = [ msg.messageid for msg in msgs if msg.message_name != 'ExecScript' ] assert len(world.right_msgs) != 0 config = ETC_DIR + '/config.yml' assert lib.start_daemon('msg_sender', config) finally: db.session.remove()
class EquityOrder(Base): __tablename__ = "equity_order" __table_args__ = {"extend_existing": True} id = Column(Integer, primary_key=True) user_id = Column( Integer, # Defining the foreign key relationship between the `user_id` in this # table and the `id` column in the `user` table ForeignKey("user.id"), nullable=False, index=True, ) ticker_id = Column(Integer, ForeignKey("ticker.id"), nullable=False) order_type = Column(Enum(OrderType), nullable=False) quantity = Column(Float, nullable=False) price = Column(Float, nullable=False) created_at = Column( Integer, default=func.unix_timestamp(), onupdate=func.unix_timestamp(), nullable=False, ) # Defining a relationship between this table and the `Ticker` model so that # we can go from every row in this table to the corresponding user. # For example: We can do `EquityOrder.ticker.ticker_name` to find the # ticker name that corresponds to the ticket_id in a row of this table user = relationship("User", backref="order") ticker = relationship("Ticker", backref="order") def __init__( self, user_id: int, ticker_id: int, order_type: OrderType, quantity: Float, price: float, ): self.user_id = user_id self.ticker_id = ticker_id self.order_type = order_type self.quantity = quantity self.price = price def __repr__(self): return (f"EquityOrder(user_id={self.user_id} " f"symbol={self.ticker.ticker} " f"order_type={self.order_type} " f"quantity={self.quantity} " f"price={self.price}) ")
def _get_messages(self): db = self._db_manager.get_db() where = and_( db.messages.type == 'out', db.messages.status == 0, db.messages.messageid != '', db.messages.message_version == 2, func.unix_timestamp(db.messages.dtlasthandleattempt) + db.messages.handle_attempts * CONFIG['cratio'] < func.unix_timestamp(func.now())) messages = db.session.query( db.messages.messageid, db.messages.server_id, db.messages.message_name, db.messages.message_format, db.messages.handle_attempts, db.messages.event_id, db.messages.message).filter(where).order_by( asc(db.messages.id)).limit(500)[0:500] return messages
def get_player_graph_data(server, granularity=15, start_date=None, end_date=None): end_date = end_date or datetime.utcnow() start_date = start_date or end_date - timedelta(days=7) result = db.session.query( label( 'timestamp_group', func.round( (func.unix_timestamp(ServerStatus.timestamp) - time.timezone) / (granularity * 60) ), ), func.avg(ServerStatus.player_count) ).filter( ServerStatus.server == server, ServerStatus.timestamp >= start_date, ServerStatus.timestamp <= end_date ).group_by('timestamp_group').order_by( ServerStatus.timestamp ).all() points = [] for chunk, count in result: points.append({ 'time': int(chunk * granularity * 60 * 1000), 'player_count': int(count) }) return { 'start_time': int(calendar.timegm(start_date.timetuple()) * 1000), 'end_time': int(calendar.timegm(end_date.timetuple()) * 1000), 'points': points }
def get_player_graph_data(server, granularity=15, start_date=None, end_date=None): end_date = end_date or datetime.utcnow() start_date = start_date or end_date - timedelta(days=7) result = db.session.query( label( 'timestamp_group', func.round( (func.unix_timestamp(ServerStatus.timestamp) - time.timezone) / (granularity * 60)), ), func.avg(ServerStatus.player_count)).filter( ServerStatus.server == server, ServerStatus.timestamp >= start_date, ServerStatus.timestamp <= end_date).group_by('timestamp_group').order_by( ServerStatus.timestamp).all() points = [] for chunk, count in result: points.append({ 'time': int(chunk * granularity * 60 * 1000), 'player_count': int(count) }) return { 'start_time': int(calendar.timegm(start_date.timetuple()) * 1000), 'end_time': int(calendar.timegm(end_date.timetuple()) * 1000), 'points': points }
def sql_from_unixtime(dtfield, dtformat='%Y-%m-%d'): """ 返回一个 FROM_UINXTIME 的查询对象 :param dtfield: 用于转换的记录名称,必须是 datetime 或者 timestamp 类型 :param dtformat: 对应与 FROM_UNIXTIME 的第二个参数 :return: """ return func.from_unixtime(func.unix_timestamp(dtfield), dtformat)
def query_wind_speeds(session, cutoff): q = session.query( WindMeasurement.avg_mph.label('avg'), WindMeasurement.max_mph.label('max')).filter( WindMeasurement.epoch >= func.unix_timestamp(func.now()) - cutoff).order_by(WindMeasurement.epoch.asc()) last_24h = [(float(row.avg), float(row.max)) for row in q.all()] return last_24h
def query_groups(session, cutoff): q = session.query( func.round(WindMeasurement.avg_mph).label('avg'), func.count(WindMeasurement.avg_mph).label('count')).filter( WindMeasurement.epoch >= func.unix_timestamp(func.now()) - cutoff).group_by(func.round(WindMeasurement.avg_mph)) averages = [(int(row.avg), int(row.count)) for row in q.all()] return averages
def _get_messages(self): db = self._db_manager.get_db() where = and_( db.messages.type == 'out', db.messages.status == 0, db.messages.messageid != '', db.messages.message_version == 2, func.unix_timestamp(db.messages.dtlasthandleattempt) + db.messages.handle_attempts * CONFIG['cratio'] < func.unix_timestamp(func.now())) messages = db.session.query( db.messages.messageid, db.messages.server_id, db.messages.message_name, db.messages.message_format, db.messages.handle_attempts, db.messages.event_id, db.messages.message).filter(where).order_by(asc(db.messages.id)).limit(500)[0:500] return messages
def getPlayerCount(cls,active=False): try: if active is False: result = Db.session.query(Player) else: result = Db.session.query(Player).filter(func.unix_timestamp(Player.last_seen) >= time.time()-Config.active_player_timeframe) return result.count() except exc.SQLAlchemyError as e: out('SQL Failure - getPlayerCount:',e) cls.reconnect() return None
def query_current_vs_max(session, cutoff): q = session.query(WindMeasurement.max_mph.label('current')).order_by( WindMeasurement.epoch.desc()).limit(1) current = q.one().current q = session.query( func.coalesce(func.max( WindMeasurement.max_mph), 1).label('max')).filter( WindMeasurement.epoch >= func.unix_timestamp(func.now()) - cutoff) max = q.one().max return float(current), float(max)
def start_daemon(step): db_manager = dbmanager.DBManager(world.config['connections']['mysql'], autoflush=False) db = db_manager.get_db() try: CRATIO = 120 where = and_(db.messages.status == 0, db.messages.type == 'out', db.messages.message_version == 2, db.messages.message != '', db.messages.message != None, func.unix_timestamp(db.messages.dtlasthandleattempt) + db.messages.handle_attempts * CRATIO < func.unix_timestamp(func.now())) msgs = db.messages.filter(where).order_by(desc(db.messages.id)).all() world.right_msgs = [ msg.messageid for msg in msgs if msg.message_name != 'ExecScript'] assert len(world.right_msgs) != 0 config = ETC_DIR + '/config.yml' assert lib.start_daemon('msg_sender', config) finally: db.session.remove()
def sensord(label, start, end, field_name, functions): seconds_per_sample_wanted, table, is_base_table = mtable.optimal(label, start, end) fields = list() for agg_func in functions: agg_func_name = str(agg_func()).replace('()', '') agg_field_name = field_name if is_base_table else '%s_%s' % (field_name, agg_func_name) fields.append(agg_func(table.c[agg_field_name]).label(agg_func_name)) qry = session.query(table.c.timestamp, *fields) \ .group_by(func.round(func.unix_timestamp(table.c.timestamp).op('DIV')(seconds_per_sample_wanted))) \ .filter(table.c.timestamp >= start, table.c.timestamp <= end) \ .order_by(table.c.timestamp) if label: qry = qry.filter(table.c.probe_label == label) return qry
def query_by_grouped_time(session, cutoff, column, group_func=func.date, sql_func=func.avg): q = session.query( sql_func(column).label('value'), group_func(func.from_unixtime( WindMeasurement.epoch)).label('date')).filter( WindMeasurement.epoch >= func.unix_timestamp(func.now()) - cutoff, column >= 1).group_by('date').order_by('date') grouped_values = [(float(row.value), row.date) for row in q.all()] return grouped_values
def find_unvalidated_versions(time_delta, environment): """Find the latest deployments that are not validated in a given environment for a given amount of time """ subq = (Session.query( Package.pkg_name, Package.version, Package.revision, AppDefinition.app_type, AppDeployment.environment, AppDeployment.realized, AppDeployment.user, AppDeployment.status).join(AppDeployment).join(AppDefinition).filter( AppDeployment.status != 'invalidated').filter( AppDeployment.environment == environment).order_by( AppDeployment.realized.desc(), AppDeployment.id.desc()).subquery(name='t_ordered')) return (Session.query( subq.c.pkg_name, subq.c.version, subq.c.revision, subq.c.appType, subq.c.environment, subq.c.realized, subq.c.user, subq.c.status).group_by( subq.c.appType, subq.c.environment, subq.c.pkg_name).having( and_( subq.c.status.like('%complete'), func.unix_timestamp(subq.c.realized) < func.unix_timestamp(func.now()) - time_delta)).all())
def update(self, session, last_data_time): for period, agg_table in self.agg_map.iteritems(): last = session.query(func.max(agg_table.c.timestamp)).scalar() if not last: last = session.query(func.min(self.base_table.timestamp).label('timestamp')).scalar() if (last_data_time - last).total_seconds() < period: # print "Not data for tailed agg at", period, \ # "last", last, \ # "last_data_time", last_data_time, \ # "seconds", (last_data_time - last).total_seconds(), \ # "days", (last_data_time - last).days continue last += datetime.timedelta(seconds=period) funs = list() insp = inspect(self.base_table) for field, pvt_funs in self.pvt.iteritems(): funs.extend([fun(insp.columns[field]) for fun in pvt_funs]) qry = session.query(self.base_table.timestamp, self.base_table.probe_label, *funs) \ .group_by(func.round(func.unix_timestamp(self.base_table.timestamp).op('DIV')(period)), self.base_table.probe_label) \ .filter(self.base_table.timestamp > last) session.execute(insert(agg_table).from_select(['timestamp', 'probe_label'] + self.pvt_fields, qry))
def date_to_secs_mysql(col): return func.unix_timestamp(col)
def run(self): db_manager = util.DBManager(self.config["connections"]["mysql"]) db = db_manager.get_db() self._set_new_gm_server(block=True) timestep = 5 while True: session = db.session try: gm_adm_client = gearman.GearmanAdminClient([self.gm_host]) gm_adm_client.ping_server() # fix gearman v2.0.2 memory leak bug self.gm_client = gearman.GearmanClient([self.gm_host]) self._update_submitted_jobs() if len(self.submitted_jobs) > 5000: LOG.warning("Too much of a submitted jobs. Skip iteration") time.sleep(timestep) continue where1 = and_(db.messages.type == "out", db.messages.status == 0, db.messages.message_version == 2) where2 = and_( func.unix_timestamp(db.messages.dtlasthandleattempt) + db.messages.handle_attempts * CRATIO < func.unix_timestamp(func.now()) ) if self.submitted_jobs: where3 = and_(not_(db.messages.messageid.in_(self.submitted_jobs.keys()))) msgs = ( session.query(db.messages.messageid, db.messages.handle_attempts) .filter(where1, where2, where3) .order_by(asc(db.messages.id)) .all()[0:QSIZE] ) else: msgs = ( session.query(db.messages.messageid, db.messages.handle_attempts) .filter(where1, where2) .order_by(asc(db.messages.id)) .all()[0:QSIZE] ) for msg in msgs: # simple unique version req = self.gm_client.submit_job( "message.send", msg.messageid, unique=msg.messageid[0:64], wait_until_complete=False ) # sha256 unique version """ req = self.gm_client.submit_job('message.send', msg.messageid, unique=hashlib.sha256(msg.messageid).hexdigest(), wait_until_complete=False) """ self.gm_client.wait_until_jobs_accepted([req]) self.submitted_jobs.update( {msg.messageid: (req, int(time.time() + CRATIO * (msg.handle_attempts + 1)))} ) LOG.info("Sumbit message: msg_id:%s" % msg.messageid) except db_exc.OperationalError: LOG.error(util.exc_info()) time.sleep(5) except gearman.errors.ServerUnavailable: LOG.error(util.exc_info()) self._set_new_gm_server(block=True) except: LOG.error(util.exc_info()) raise finally: session.close() session.remove() time.sleep(timestep)
def __call__(self, user_ids, session): """ Parameters: user_ids : list of mediawiki user ids to find edit for session : sqlalchemy session open on a mediawiki database Returns: dictionary from user ids to a dictionary of the form: { 'threshold': 1 for True, 0 for False, 'time_to_threshold': number in hours or None, 'censored': 1 for True, 0 for False } """ threshold_hours = int(self.threshold_hours.data) threshold_secs = threshold_hours * 3600 number_of_edits = int(self.number_of_edits.data) Revision2 = aliased(Revision, name='r2') ordered_revisions = session \ .query( Revision.rev_user, Revision.rev_timestamp, label('number', func.count()), ) \ .join(MediawikiUser) \ .join(Page) \ .join( Revision2, and_( Revision.rev_user == Revision2.rev_user, Revision.rev_timestamp >= Revision2.rev_timestamp ) ) \ .group_by(Revision.rev_user) \ .group_by(Revision.rev_timestamp) \ .filter(Page.page_namespace.in_(self.namespaces.data)) \ .filter( func.unix_timestamp(Revision.rev_timestamp) - func.unix_timestamp(MediawikiUser.user_registration) <= threshold_secs ) o_r = self.filter(ordered_revisions, user_ids).subquery() metric = session.query( MediawikiUser.user_id, label( Threshold.id, func.IF(o_r.c.rev_timestamp != None, 1, 0) ), label( Threshold.time_to_threshold_id, func.IF( o_r.c.rev_timestamp != None, (func.unix_timestamp(o_r.c.rev_timestamp) - func.unix_timestamp(MediawikiUser.user_registration)) / 3600, None ) ), label(CENSORED, func.IF( o_r.c.rev_timestamp != None, 0, func.IF( func.unix_timestamp(MediawikiUser.user_registration) + threshold_secs > func.unix_timestamp(func.now()), 1, 0 ) )) ) \ .outerjoin( o_r, and_( MediawikiUser.user_id == o_r.c.rev_user, o_r.c.number == number_of_edits)) metric = self.filter(metric, user_ids, MediawikiUser.user_id) return { u.user_id: { Threshold.id: u.threshold, Threshold.time_to_threshold_id: u.time_to_threshold, CENSORED: u.censored, } for u in metric.all() }
def run(self): db = self.db_manager.get_db() while True: try: db.servers db.farm_roles db.farm_settings db.role_behaviors db.server_properties db.farm_role_settings break except (db_exc.OperationalError, db_exc.InternalError): logger.error(sys.exc_info()) time.sleep(10) timestep = 5 wrk_pool = gevent.pool.Pool(config["pool_size"]) while True: try: where1 = and_(db.messages.type == "out", db.messages.status == 0, db.messages.message_version == 2) where2 = and_( func.unix_timestamp(db.messages.dtlasthandleattempt) + db.messages.handle_attempts * config["cratio"] < func.unix_timestamp(func.now()) ) msgs = dict( (msg.messageid, msg) for msg in db.messages.filter(where1, where2) .order_by(asc(db.messages.id)) .all()[0 : config["qsize"]] ) if not msgs: time.sleep(timestep) continue srvs_id = [msg.server_id for msg in msgs.values()] srvs = dict((srv.server_id, srv) for srv in db.servers.filter(db.servers.server_id.in_(srvs_id)).all()) where = and_(db.server_properties.server_id.in_(srvs_id), db.server_properties.name == "scalarizr.key") keys_query = db.server_properties.filter(where).all() keys = dict((el.server_id, el.value) for el in keys_query) where = and_( db.server_properties.server_id.in_(srvs_id), db.server_properties.name == "scalarizr.ctrl_port" ) ports_query = db.server_properties.filter(where).all() ports = dict((el.server_id, el.value if el and el.value else 8013) for el in ports_query) tasks = [] for msg in msgs.values(): try: srv = srvs[msg.server_id] except KeyError: logging.warning( "Server with server_id %s dosn't exist. Delete message %s" % (msg.server_id, msg.messageid) ) db.delete(msg) continue if not self._server_is_active(srv): continue ip = { "public": srv.remote_ip, "local": srv.local_ip, "auto": srv.remote_ip if srv.remote_ip else srv.local_ip, }[config["inst_conn_policy"]] try: key = keys[msg.server_id] except KeyError: logging.error("Server %s has not scalarizr key" % msg.server_id) continue try: port = ports[msg.server_id] except KeyError: port = 8013 req_host = "%s:%s" % (ip, port) data, headers = self._encrypt(msg.server_id, key, msg.message) where = and_(db.farm_settings.farmid == srv.farm_id, db.farm_settings.name == "ec2.vpc.id") is_vpc = db.farm_settings.filter(where).first() if is_vpc: where = and_(db.role_behaviors.behavior == "router") vpc_roles = [behavior.role_id for behavior in db.role_behaviors.filter(where).all()] where = and_(db.farm_roles.role_id.in_(vpc_roles), db.farm_roles.farmid == srv.farm_id) db_farm_role = db.farm_roles.filter(where).first() if db_farm_role: logger.debug("Message:%s for VPC server:%s" % (msg.messageid, srv.server_id)) if srv.remote_ip: ip = srv.remote_ip req_host = "%s:%s" % (srv.remote_ip, port) else: where = and_( db.farm_role_settings.farm_roleid == db_farm_role.id, db.farm_role_settings.name == "router.vpc.ip", ) ip_query = db.farm_role_settings.filter(where).first() if ip_query and ip_query.value: ip = ip_query.value req_host = "%s:80" % ip headers["X-Receiver-Host"] = srv.local_ip headers["X-Receiver-Port"] = port else: ip = None if ip == None or ip == "None": logger.warning("Server: %s Null ip, delete message %s" % (srv.server_id, msg.messageid)) db.delete(msg) continue url = "http://%s/%s" % (req_host, "control") req = urllib2.Request(url, data, headers) db.session.expunge(msg) tasks.append({"msg": msg, "req": req}) wrk_pool.map_async(self._send, tasks) gevent.sleep(0) wrk_pool.join() except (db_exc.OperationalError, db_exc.InternalError): logger.error(sys.exc_info()) time.sleep(10) except Exception: logger.exception("Exception") finally: while True: try: db.commit() db.session.close() break except (db_exc.OperationalError, db_exc.InternalError): logger.error(sys.exc_info()) time.sleep(10) time.sleep(timestep)
def __call__(self, user_ids, session): """ Parameters: user_ids : list of mediawiki user ids to find edit for session : sqlalchemy session open on a mediawiki database Returns: dictionary from user ids to a dictionary of the form: { 'survived': 1 for True, 0 for False, 'censored': 1 for True, 0 for False } """ survival_hours = int(self.survival_hours.data) sunset_in_hours = int(self.sunset_in_hours.data) number_of_edits = int(self.number_of_edits.data) revisions = ( session.query(MediawikiUser.user_id, label("rev_count", func.count())) .join(Revision) .join(Page) .group_by(MediawikiUser.user_id) .filter(Page.page_namespace.in_(self.namespaces.data)) ) revisions = self.filter(revisions, user_ids, column=MediawikiUser.user_id) # sunset_in_hours is zero, so we use the first case [T+t,today] if sunset_in_hours == 0: revisions = revisions.filter( between( func.unix_timestamp(Revision.rev_timestamp) - func.unix_timestamp(MediawikiUser.user_registration), (survival_hours * 3600), func.unix_timestamp(func.now()) + 86400, ) ) # otherwise use the sunset_in_hours [T+t,T+t+s] else: revisions = revisions.filter( between( func.unix_timestamp(Revision.rev_timestamp) - func.unix_timestamp(MediawikiUser.user_registration), (survival_hours * 3600), ((survival_hours + sunset_in_hours) * 3600), ) ) revisions = revisions.subquery() revs = session.query( MediawikiUser.user_id, MediawikiUser.user_registration, label("rev_count", func.coalesce(revisions.c.rev_count, 0)), ).outerjoin(revisions, MediawikiUser.user_id == revisions.c.user_id) revs = self.filter(revs, user_ids, MediawikiUser.user_id).subquery() metric = session.query( revs.c.user_id, label(Survival.id, func.IF(revs.c.rev_count >= number_of_edits, 1, 0)), label( CENSORED, func.IF( revs.c.rev_count >= number_of_edits, 0, func.IF( func.unix_timestamp(func.now()) < func.unix_timestamp(revs.c.user_registration) + (survival_hours + sunset_in_hours) * 3600, 1, 0, ), ), ), ) data = metric.all() metric_results = {u.user_id: {Survival.id: u.survived, CENSORED: u.censored} for u in data} r = {uid: metric_results.get(uid, self.default_result) for uid in user_ids or metric_results.keys()} # self.debug_print(r, session, user_ids) return r
def grafanaUrl(uid, parameters = None): if uid == "": return current_app.config["GRAFANA_BASE_URI"] elif uid == "ActiveEventFrameSummary": if "elementTemplateId" in parameters: elementTemplate = ElementTemplate.query.get_or_404(parameters["elementTemplateId"]) return current_app.config["GRAFANA_BASE_URI"] + \ "/d/ActiveEventFrameSummary/active-event-frame-summary?orgId=1" + \ "&var-enterprise={}".format(elementTemplate.Site.Enterprise.EnterpriseId) + \ "&var-site={}".format(elementTemplate.Site.SiteId) + \ "&var-elementTemplates={}".format(elementTemplate.ElementTemplateId) + \ "&var-eventFrameTemplates=All" + \ "&var-eventFrameAttributeTemplates=All" elif "eventFrameTemplateId" in parameters: eventFrameTemplate = EventFrameTemplate.query.get_or_404(parameters["eventFrameTemplateId"]) return current_app.config["GRAFANA_BASE_URI"] + \ "/d/ActiveEventFrameSummary/active-event-frame-summary?orgId=1" + \ "&var-enterprise={}".format(eventFrameTemplate.ElementTemplate.Site.Enterprise.EnterpriseId) + \ "&var-site={}".format(eventFrameTemplate.ElementTemplate.Site.SiteId) + \ "&var-elementTemplates={}".format(eventFrameTemplate.ElementTemplate.ElementTemplateId) + \ "&var-eventFrameTemplates={}".format(eventFrameTemplate.EventFrameTemplateId) + \ "&var-eventFrameAttributeTemplates=All" elif "siteId" in parameters: site = Site.query.get_or_404(parameters["siteId"]) return current_app.config["GRAFANA_BASE_URI"] + \ "/d/ActiveEventFrameSummary/active-event-frame-summary?orgId=1" + \ "&var-enterprise={}".format(site.Enterprise.EnterpriseId) + \ "&var-site={}".format(site.SiteId) + \ "&var-elementTemplates=All" + \ "&var-eventFrameTemplates=All" + \ "&var-eventFrameAttributeTemplates=All" elif uid == "ElementSummary": if "siteId" in parameters: site = Site.query.get_or_404(parameters["siteId"]) return current_app.config["GRAFANA_BASE_URI"] + \ "/d/ElementSummary/element-summary?orgId=1" + \ "&var-enterprise={}".format(site.Enterprise.EnterpriseId) + \ "&var-site={}".format(parameters["siteId"]) + \ "&var-elementTemplates=All" + \ "&var-elements=All" + \ "&var-elementAttributeTemplates=All" elif "elementTemplateId" in parameters: elementTemplate = ElementTemplate.query.get_or_404(parameters["elementTemplateId"]) elements = "" for element in elementTemplate.Elements: elements += "&var-elements={}".format(element.ElementId) return current_app.config["GRAFANA_BASE_URI"] + \ "/d/ElementSummary/element-summary?orgId=1" + \ "&var-enterprise={}".format(elementTemplate.Site.Enterprise.EnterpriseId) + \ "&var-site={}".format(elementTemplate.Site.SiteId) + \ "&var-elementTemplates={}".format(elementTemplate.ElementTemplateId) + \ elements + \ "&var-elementAttributeTemplates=All" elif uid == "ElementValuesGraph": element = Element.query.get_or_404(parameters["elementId"]) return current_app.config["GRAFANA_BASE_URI"] + \ "/d/ElementValuesGraph/element-values-graph?orgId=1" + \ "&var-enterprise={}".format(element.ElementTemplate.Site.Enterprise.EnterpriseId) + \ "&var-site={}".format(element.ElementTemplate.Site.SiteId) + \ "&var-elementTemplates=All" + \ "&var-elements={}".format(parameters["elementId"]) + \ "&var-elementAttributeTemplates=All" + \ "&var-lookups=All" elif uid == "EventFramesGraph": eventFrame = EventFrame.query.get_or_404(parameters["eventFrameId"]) startTimestamp = EventFrame.query.with_entities(func.unix_timestamp(EventFrame.StartTimestamp)).filter_by(EventFrameId = eventFrame.EventFrameId). \ one()[0] - 1 if eventFrame.EndTimestamp: endTimestamp = EventFrame.query.with_entities(func.unix_timestamp(EventFrame.EndTimestamp)).filter_by(EventFrameId = eventFrame.EventFrameId). \ one()[0] + 1 return current_app.config["GRAFANA_BASE_URI"] + \ "/d/EventFramesGraph/event-frames-graph?orgId=1" + \ "&from={}000".format(startTimestamp) + \ "&to={}000".format(endTimestamp) + \ "&var-enterprise={}".format(eventFrame.EventFrameTemplate.ElementTemplate.Site.Enterprise.EnterpriseId) + \ "&var-site={}".format(eventFrame.EventFrameTemplate.ElementTemplate.Site.SiteId) + \ "&var-elementTemplate={}".format(eventFrame.EventFrameTemplate.ElementTemplate.ElementTemplateId) + \ "&var-eventFrameTemplate={}".format(eventFrame.EventFrameTemplate.EventFrameTemplateId) + \ "&var-eventFrame={}".format(parameters["eventFrameId"]) + \ "&var-eventFrameAttributeTemplates=All" + \ "&var-lookups=All" else: return current_app.config["GRAFANA_BASE_URI"] + \ "/d/EventFramesGraph/event-frames-graph?orgId=1" + \ "&from={}000".format(startTimestamp) + \ "&to=now" + \ "&var-enterprise={}".format(eventFrame.EventFrameTemplate.ElementTemplate.Site.Enterprise.EnterpriseId) + \ "&var-site={}".format(eventFrame.EventFrameTemplate.ElementTemplate.Site.SiteId) + \ "&var-elementTemplate={}".format(eventFrame.EventFrameTemplate.ElementTemplate.ElementTemplateId) + \ "&var-eventFrameTemplate={}".format(eventFrame.EventFrameTemplate.EventFrameTemplateId) + \ "&var-eventFrame={}".format(parameters["eventFrameId"]) + \ "&var-eventFrameAttributeTemplates=All" + \ "&var-lookups=All" elif uid == "TagValuesGraph": return current_app.config["GRAFANA_BASE_URI"] + \ "/d/TagValuesGraph/tag-values-graph?orgId=1" + \ "&var-enterprises=All" + \ "&var-sites=All" + \ "&var-areas=All" + \ "&var-tags={}".format(parameters["tagId"]) + \ "&var-lookups=All"
def __call__(self, user_ids, session): """ Parameters: user_ids : list of mediawiki user ids to find edit for session : sqlalchemy session open on a mediawiki database Returns: dictionary from user ids to a dictionary of the form: { 'threshold': 1 for True, 0 for False, 'time_to_threshold': number in hours or None, 'censored': 1 for True, 0 for False } """ threshold_hours = int(self.threshold_hours.data) threshold_secs = threshold_hours * 3600 number_of_edits = int(self.number_of_edits.data) Revision2 = aliased(Revision, name='r2') ordered_revisions = session \ .query( Revision.rev_user, Revision.rev_timestamp, label('number', func.count()), ) \ .join(MediawikiUser) \ .join(Page) \ .join( Revision2, and_( Revision.rev_user == Revision2.rev_user, Revision.rev_timestamp >= Revision2.rev_timestamp ) ) \ .group_by(Revision.rev_user) \ .group_by(Revision.rev_timestamp) \ .filter(Page.page_namespace.in_(self.namespaces.data)) \ .filter( func.unix_timestamp(Revision.rev_timestamp) - func.unix_timestamp(MediawikiUser.user_registration) <= threshold_secs ) o_r = self.filter(ordered_revisions, user_ids).subquery() metric = session.query( MediawikiUser.user_id, label( Threshold.id, func.IF(o_r.c.rev_timestamp != None, 1, 0) ), label( Threshold.time_to_threshold_id, func.IF( o_r.c.rev_timestamp != None, (func.unix_timestamp(o_r.c.rev_timestamp) - func.unix_timestamp(MediawikiUser.user_registration)) / 3600, None ) ), label(CENSORED, func.IF( o_r.c.rev_timestamp != None, 0, func.IF( func.unix_timestamp(MediawikiUser.user_registration) + threshold_secs > func.unix_timestamp(func.now()), 1, 0 ) )) ) \ .outerjoin( o_r, and_( MediawikiUser.user_id == o_r.c.rev_user, o_r.c.number == number_of_edits)) metric = self.filter(metric, user_ids, MediawikiUser.user_id) return { u.user_id: { Threshold.id : u.threshold, Threshold.time_to_threshold_id : u.time_to_threshold, CENSORED : u.censored, } for u in metric.all() }
def run(self): db = self.db_manager.get_db() while True: try: db.servers db.farm_roles db.farm_settings db.role_behaviors db.server_properties db.farm_role_settings break except (db_exc.OperationalError, db_exc.InternalError): logger.error(sys.exc_info()) time.sleep(10) timestep = 5 wrk_pool = gevent.pool.Pool(config['pool_size']) while True: try: where1 = and_(db.messages.type == 'out', db.messages.status == 0, db.messages.message_version == 2) where2 = and_( func.unix_timestamp(db.messages.dtlasthandleattempt) +\ db.messages.handle_attempts *\ config['cratio'] < func.unix_timestamp(func.now())) msgs = dict((msg.messageid, msg) for msg in\ db.messages.filter(where1, where2).order_by( asc(db.messages.id)).all()[0:config['qsize']]) if not msgs: time.sleep(timestep) continue srvs_id = [msg.server_id for msg in msgs.values()] srvs = dict((srv.server_id, srv) for srv in\ db.servers.filter(db.servers.server_id.in_(srvs_id)).all()) where = and_(db.server_properties.server_id.in_(srvs_id), db.server_properties.name == 'scalarizr.key') keys_query = db.server_properties.filter(where).all() keys = dict((el.server_id, el.value) for el in keys_query) where = and_( db.server_properties.server_id.in_(srvs_id), db.server_properties.name == 'scalarizr.ctrl_port') ports_query = db.server_properties.filter(where).all() ports = dict( (el.server_id, el.value if el and el.value else 8013) for el in ports_query) tasks = [] for msg in msgs.values(): try: srv = srvs[msg.server_id] except KeyError: logging.warning( 'Server with server_id %s dosn\'t exist. Delete message %s' % (msg.server_id, msg.messageid)) db.delete(msg) continue if not self._server_is_active(srv): continue ip = { 'public': srv.remote_ip, 'local': srv.local_ip, 'auto': srv.remote_ip if srv.remote_ip else srv.local_ip }[config['inst_conn_policy']] try: key = keys[msg.server_id] except KeyError: logging.error('Server %s has not scalarizr key' % msg.server_id) continue try: port = ports[msg.server_id] except KeyError: port = 8013 req_host = '%s:%s' % (ip, port) data, headers = self._encrypt(msg.server_id, key, msg.message) where = and_(db.farm_settings.farmid == srv.farm_id, db.farm_settings.name == 'ec2.vpc.id') is_vpc = db.farm_settings.filter(where).first() if (is_vpc): where = and_(db.role_behaviors.behavior == 'router') vpc_roles = [ behavior.role_id for behavior in db.role_behaviors.filter(where).all() ] where = and_(db.farm_roles.role_id.in_(vpc_roles), db.farm_roles.farmid == srv.farm_id) db_farm_role = db.farm_roles.filter(where).first() if db_farm_role: logger.debug('Message:%s for VPC server:%s' % (msg.messageid, srv.server_id)) if srv.remote_ip: ip = srv.remote_ip req_host = '%s:%s' % (srv.remote_ip, port) else: where = and_( db.farm_role_settings.farm_roleid == db_farm_role.id, db.farm_role_settings.name == 'router.vpc.ip') ip_query = db.farm_role_settings.filter( where).first() if ip_query and ip_query.value: ip = ip_query.value req_host = '%s:80' % ip headers['X-Receiver-Host'] = srv.local_ip headers['X-Receiver-Port'] = port else: ip = None if ip == None or ip == 'None': logger.warning( 'Server: %s Null ip, delete message %s' % (srv.server_id, msg.messageid)) db.delete(msg) continue url = 'http://%s/%s' % (req_host, 'control') req = urllib2.Request(url, data, headers) db.session.expunge(msg) tasks.append({'msg': msg, 'req': req}) wrk_pool.map_async(self._send, tasks) gevent.sleep(0) wrk_pool.join() except (db_exc.OperationalError, db_exc.InternalError): logger.error(sys.exc_info()) time.sleep(10) except Exception: logger.exception('Exception') finally: while True: try: db.commit() db.session.close() break except (db_exc.OperationalError, db_exc.InternalError): logger.error(sys.exc_info()) time.sleep(10) time.sleep(timestep)
def project_duration(pid, session, count_nr=False): jn_mapping = netutils.get_table_object('jn_mapping', session) if(not count_nr): s = select([ func.unix_timestamp(func.min(jn_mapping.c.s_ts)), func.unix_timestamp(func.max(jn_mapping.c.f_ts)) ], and_(jn_mapping.c.project_id==pid, func.unix_timestamp( jn_mapping.c.s_ts)!=0, jn_mapping.c.job_type!='blast_nr') ) else: res = session.conn.execute(s) row = res.fetchone() if(row is None): sys.stderr.write("Warning - no jobs set for \ project: " + str(pid) + "\n" ) return 0 elif(row[1] is None or row[1] is None): sys.stderr.write("Warning - received None for \ either max finish time or min \ start time.\n" ) return 0 return row[1] - row[0] def main(): parser = argparse.ArgumentParser() parser.add_argument('--project-ids', dest='pids', default=None, nargs='+', help='The project_ids of the \ projects you wish to work on.' ) parser.add_argument('--pid-range', dest='pid_range', default=None, help='A range of \ project ids specified in the \ form: start:end' ) parser.add_argument('--durations', dest='durations', default=False, const=True, action='store_const', help='Calculate run time durations\ for an entire project.' ) args = parser.parse_args() s = netutils.make_db_session() if(args.durations): pids = args.pids if(not args.pid_range is None): split = args.pid_range.split(":") start = int(split[0]) end = int(split[1]) pids = [i for i in xrange(start, end + 1)] for pid in pids: print("\t".join( [str(pid), str(project_duration(int(pid), s)) ] ) ) if __name__ == '__main__': main()
def run(self): db_manager = util.DBManager(self.config['connections']['mysql']) db = db_manager.get_db() self._set_new_gm_server(block=True) timestep = 5 while True: session = db.session try: gm_adm_client = gearman.GearmanAdminClient([self.gm_host]) gm_adm_client.ping_server() # fix gearman v2.0.2 memory leak bug self.gm_client = gearman.GearmanClient([self.gm_host]) self._update_submitted_jobs() if len(self.submitted_jobs) > 5000: LOG.warning('Too much of a submitted jobs. Skip iteration') time.sleep(timestep) continue where1 = and_(db.messages.type == 'out', db.messages.status == 0, db.messages.message_version == 2) where2 = and_( func.unix_timestamp(db.messages.dtlasthandleattempt) +\ db.messages.handle_attempts *\ CRATIO < func.unix_timestamp(func.now())) if self.submitted_jobs: where3 = and_( not_( db.messages.messageid.in_( self.submitted_jobs.keys()))) msgs = session.query( db.messages.messageid, db.messages.handle_attempts).filter( where1, where2, where3).order_by( asc(db.messages.id)).all()[0:QSIZE] else: msgs = session.query( db.messages.messageid, db.messages.handle_attempts).filter( where1, where2).order_by(asc( db.messages.id)).all()[0:QSIZE] for msg in msgs: # simple unique version req = self.gm_client.submit_job('message.send', msg.messageid, unique=msg.messageid[0:64], wait_until_complete=False) # sha256 unique version ''' req = self.gm_client.submit_job('message.send', msg.messageid, unique=hashlib.sha256(msg.messageid).hexdigest(), wait_until_complete=False) ''' self.gm_client.wait_until_jobs_accepted([req]) self.submitted_jobs.update({ msg.messageid: (req, int(time.time() + CRATIO * (msg.handle_attempts + 1))) }) LOG.info('Sumbit message: msg_id:%s' % msg.messageid) except db_exc.OperationalError: LOG.error(util.exc_info()) time.sleep(5) except gearman.errors.ServerUnavailable: LOG.error(util.exc_info()) self._set_new_gm_server(block=True) except: LOG.error(util.exc_info()) raise finally: session.close() session.remove() time.sleep(timestep)
def __call__(self, user_ids, session): """ Parameters: user_ids : list of mediawiki user ids to find edit for session : sqlalchemy session open on a mediawiki database Returns: dictionary from user ids to a dictionary of the form: { 'survived': 1 for True, 0 for False, 'censored': 1 for True, 0 for False } """ survival_hours = int(self.survival_hours.data) sunset_in_hours = int(self.sunset_in_hours.data) number_of_edits = int(self.number_of_edits.data) revisions = session \ .query( MediawikiUser.user_id, label('rev_count', func.count()) ) \ .join(Revision) \ .join(Page) \ .group_by(MediawikiUser.user_id) \ .filter(Page.page_namespace.in_(self.namespaces.data)) revisions = self.filter(revisions, user_ids, column=MediawikiUser.user_id) # sunset_in_hours is zero, so we use the first case [T+t,today] if sunset_in_hours == 0: revisions = revisions.filter( between( func.unix_timestamp(Revision.rev_timestamp) - func.unix_timestamp(MediawikiUser.user_registration), (survival_hours * 3600), func.unix_timestamp(func.now()) + 86400)) # otherwise use the sunset_in_hours [T+t,T+t+s] else: revisions = revisions.filter( between( func.unix_timestamp(Revision.rev_timestamp) - func.unix_timestamp(MediawikiUser.user_registration), (survival_hours * 3600), ((survival_hours + sunset_in_hours) * 3600))) revisions = revisions.subquery() revs = session.query( MediawikiUser.user_id, MediawikiUser.user_registration, label( 'rev_count', func.coalesce(revisions.c.rev_count, 0) ) ) \ .outerjoin(revisions, MediawikiUser.user_id == revisions.c.user_id) revs = self.filter(revs, user_ids, MediawikiUser.user_id).subquery() metric = session.query( revs.c.user_id, label(Survival.id, func.IF(revs.c.rev_count >= number_of_edits, 1, 0)), label( CENSORED, func.IF( revs.c.rev_count >= number_of_edits, 0, func.IF( func.unix_timestamp(func.now()) < func.unix_timestamp(revs.c.user_registration) + (survival_hours + sunset_in_hours) * 3600, 1, 0)))) data = metric.all() metric_results = { u.user_id: { Survival.id: u.survived, CENSORED: u.censored, } for u in data } r = { uid: metric_results.get(uid, self.default_result) for uid in user_ids or metric_results.keys() } #self.debug_print(r, session, user_ids) return r
def find_nearest(self, direction, current, bbox_text, requested_products, patterns, search_step=60): """ """ insp = reflection.Inspector.from_engine(self.__engine) existing_tables = insp.get_table_names() products = [ p for p in requested_products if Storage.get_product_table_name(p) in existing_tables ] # Spatial filter bbox_polygon = None if bbox_text is not None: west, south, east, north = bbox_text.split(',') bbox_polygon = 'POLYGON(({} {}, {} {}, {} {}, {} {}, {} {}))' bbox_polygon = bbox_polygon.format(west, north, east, north, east, south, west, south, west, north) after_delta = float('inf') before_delta = float('inf') for product_id in products: pattern = None if patterns is not None: pattern = patterns.get(product_id, None) dataset_cls = self.get_product_table(product_id) mid_ts = ((func.unix_timestamp(dataset_cls.begin_datetime) + func.unix_timestamp(dataset_cls.end_datetime)) / 2).label('mid_ts') current_ts = calendar.timegm(current.timetuple()) # Look for datasets in the future if direction in ['nearest', 'after', None]: q = self.__session.query(mid_ts) q = q.filter(mid_ts > current_ts + search_step) if bbox_polygon is not None: q = q.filter( sqlalchemy.or_( dataset_cls.shape_text == 'POINT(0 0)', func.Intersects(dataset_cls.shape_geometry, func.GeomFromText(bbox_polygon)))) if pattern is not None: q = q.filter( dataset_cls.dataset_name.like('%{}%'.format(pattern))) q = q.order_by(mid_ts) q = q.limit(1) result = q.first() if result is not None: delta = int(result[0]) - current_ts if delta < after_delta: after_delta = delta # Look for datasets in the past if direction in ['nearest', 'before', None]: q = self.__session.query(mid_ts) q = q.filter(mid_ts < current_ts - search_step) if bbox_polygon is not None: q = q.filter( sqlalchemy.or_( dataset_cls.shape_text == 'POINT(0 0)', func.Intersects(dataset_cls.shape_geometry, func.GeomFromText(bbox_polygon)))) if pattern is not None: q = q.filter( dataset_cls.dataset_name.like('%{}%'.format(pattern))) q = q.order_by(desc(mid_ts)) q = q.limit(1) result = q.first() if result is not None: delta = current_ts - int(result[0]) if delta < before_delta: before_delta = delta delta = min(after_delta, before_delta) if delta == float('inf'): new_ts = current_ts elif delta == after_delta: new_ts = current_ts + delta else: new_ts = current_ts - delta if before_delta == float('inf'): before_delta = -1 if after_delta == float('inf'): after_delta = -1 return new_ts, before_delta, after_delta