def query_messages(stream_id=None, updated_since=None, pending_until=None, page=0, page_size=100, pending=None, working=None, order_by="updated", order_dir="DESC", null_metadata=None): sql, params = [], [] if stream_id is not None: sql.append("stream.stream_id = %s") params.append(stream_id) if updated_since is not None: sql.append("updated >= %s") params.append(updated_since) if pending_until is not None: sql.append("pending_time < %s") params.append(pending_until) if pending is not None: sql.append("pending = %s") params.append(pending) if working is False: sql.append("(working IS NULL OR working < %s)") params.append(unix_timestamp() - 60) if null_metadata is not None: # only look for bots without metadata sql.append("bot_id IS NULL") if null_metadata: sql.append("metadata = 'null'") else: sql.append("metadata != 'null'") params.append(page_size) params.append(page_size * page) if sql: where = "AND " + " AND ".join(sql) else: where = "" keep_alive = unix_timestamp() - 60 with db.connect() as c: rows = c.query( """ SELECT stream.stream_id, message_id, bot_id, updated, pending, pending_time, text, metadata FROM message INNER JOIN stream ON message.stream_id = stream.stream_id WHERE stream.keep_alive > %s %s ORDER BY %s %s LIMIT %%s OFFSET %%s """ % (keep_alive, where, order_by, order_dir), *params) for row in rows: row.metadata = json.loads(row.metadata) if row.metadata else None return rows
def add_pending_message(stream_id, bot_id, soon=False): if soon: target_time = unix_timestamp() + random.uniform(0, 3) else: target_time = unix_timestamp() + random.uniform(5, 15) create_message(stream_id=stream_id, text="", bot_id=bot_id, pending=True, pending_time=target_time)
def get_pending_message(): candidates = query_messages(pending=True, working=False, pending_until=unix_timestamp(), order_by="pending_time", order_dir="ASC", page_size=1) if candidates: candidate = candidates[0] with db.connect() as c: c.execute("UPDATE message SET working=%s WHERE message_id=%s", unix_timestamp(), candidate.message_id) return candidate
def create_stream(stream_id=None): with db.connect() as c: if stream_id is None: return c.execute( """ INSERT INTO stream (created, bots, keep_alive) VALUES (%s, %s, %s) """, unix_timestamp(), json.dumps([]), unix_timestamp()) else: return c.execute( """ INSERT IGNORE INTO stream (stream_id, created, bots, keep_alive) VALUES (%s, %s, %s, %s) """, stream_id, unix_timestamp(), json.dumps([]), unix_timestamp())
def ping_stream(stream_id): with db.connect() as c: return c.query( """ UPDATE stream SET keep_alive=%s WHERE stream_id = %s """, unix_timestamp(), stream_id)
def update_message(message_id, text, metadata): with db.connect() as c: return c.execute( """ UPDATE message SET text=%s, metadata=%s, updated=%s, pending=False WHERE message_id=%s """, text, json.dumps(metadata), unix_timestamp(), message_id)
def create_bot(bot_id, name, sex, seed_text_path, photo_url): now = unix_timestamp() with db.connect() as c: return c.execute( """ INSERT INTO bot (bot_id, created, updated, name, sex, seed_text_path, photo_url) VALUES (%s, %s, %s, %s, %s, %s, %s) """, bot_id, now, now, name, sex, seed_text_path, photo_url)
def create_message(stream_id, text, bot_id=None, pending=False, pending_time=None, metadata=None): now = unix_timestamp() with db.connect() as c: return c.execute( """ INSERT INTO message (stream_id, bot_id, created, updated, pending, pending_time, text, metadata) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """, stream_id, bot_id, now, now, pending, pending_time, text, json.dumps(metadata))