def fill_id(conn, user_id, email_id): assert user_id is not None or email_id is not None if email_id is None: email_id = conn.execute( sql('select id from email where user_id = :user_id order by ctime limit 1'), user_id=user_id ).fetchone() if email_id: email_id = email_id[0] if user_id is None: user_id = conn.execute( sql('select user_id from email where id = :email_id'), email_id=email_id ).fetchone() if user_id: user_id = user_id[0] if user_id is None: raise UserNotExistError() if email_id is None: raise EmailNotExistError() return user_id, email_id
def get_watches_bi_user_id(conn, user_id, offset=None, limit=None): result = conn.execute(sql('\n'.join([ ''' select w0.user_id, w0.query_id, w0.name, w0.ctime, q0.kind query_kind, q0.text query_text, q0.mtime query_mtime, w0.email_id, e0.text email_text, e0.label email_label, e0.activated email_activated from watch w0 inner join query q0 on w0.query_id = q0.id inner join email e0 on w0.email_id = e0.id where w0.user_id = :user_id order by w0.ctime desc ''', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), user_id=user_id, offset=offset, limit=limit) return [bunchr(**row) for row in result.fetchall()]
def is_query_active_bi_id(conn, id): return conn.execute(sql( ''' select 1 from watch as w0 where w0.query_id = :id ''' ), id=id).fetchone() is not None
def update_email_bi_id(conn, id, email, label): conn.execute( sql('update email set text = :text, label = :label where id = :id'), id=id, text=email, label=label )
def get_users(conn, offset=None, limit=None): result = conn.execute(sql('\n'.join([ 'select * from "user" order by id', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [bunchr(**row) for row in result.fetchall()]
def count_recent_notice_bi_user_id(conn, user_id, interval): return conn.execute(sql(''' select count(1) from notice where user_id = :user_id and ctime >= :begin '''), user_id=user_id, begin=datetime.utcnow() - interval).fetchone()[0]
def get_query_bi_kind_and_text(conn, kind, text): ret = conn.execute( sql('select * from query where kind = :kind and text = :text'), kind=kind, text=text ).fetchone() return None if ret is None else mq(**ret)
def add_query(conn, kind, text, result={}): ret = conn.execute(sql(''' insert into query (kind, text, result) values (:kind, :text, :result) returning * '''), kind=kind, text=text, result=Json(result)).fetchone() return ret[0]
def set_user_field_bi_id(conn, id, field, value): assert_in(field, ('name', 'email', 'openid', 'maxwatch')) conn.execute( sql('update "user" set %s = :value where id = :id' % field), id=id, value=value )
def rename_watch(conn, user_id, query_id, name): conn.execute(sql( ''' update watch set name = :name where user_id = :user_id and query_id = :query_id ''' ), user_id=user_id, query_id=query_id, name=name)
def set_next_sync_time(conn, id, time): conn.execute(sql( ''' update query set next_sync_time = :time where id = :id ''' ), id=id, time=time)
def set_next_sync_time_bi_kind_and_text(conn, kind, text, time): conn.execute(sql( ''' update query set next_sync_time = :time where kind = :kind and text = :text ''' ), kind=kind, text=text, time=time)
def count_recent_notice_bi_user_id(conn, user_id, interval): return conn.execute(sql( ''' select count(1) from notice where user_id = :user_id and ctime >= :begin ''' ), user_id=user_id, begin=datetime.utcnow() - interval).fetchone()[0]
def get_pending_notice_count_bi_user_id(conn, user_id): return conn.execute(sql(''' select count(*) from notice where user_id = :user_id and status = :status '''), user_id=user_id, status='pending').fetchone()[0]
def get_pending_notices_bi_user_id(conn, user_id, page=0, room=ROOM): result = conn.execute(sql(''' select n0.id, n0.user_id, n0.ctime, n0.status, q0.kind kind, c0.data change, n0.email from ( select * from notice where user_id = :user_id and status = :status ) n0 inner join change c0 on n0.change_id = c0.id inner join query q0 on c0.query_id = q0.id order by n0.ctime desc offset :offset limit :limit '''), user_id=user_id, status='pending', offset=page * room, limit=room) return [make(**row) for row in result.fetchall()]
def _batch_delete_content(self, volume: int, keys: List[_KEY]) -> None: q = sql(""" DELETE FROM {table} WHERE id IN :id_tuple """.format(table=self._client.get_table(volume))) id_tuple = self._to_id_tuple(keys) with self._client.get_engine(volume).connect() as conn: with conn.begin(): conn.execute(q, id_tuple=id_tuple)
def get_users(conn, offset=None, limit=None): result = conn.execute( sql('\n'.join([ 'select * from "user" order by id', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [bunchr(**row) for row in result.fetchall()]
def get_user_detail_bi_id(conn, id): return enrich(conn, conn.execute(sql( ''' select u0.*, (select count(1) from watch as w0 where w0.user_id = u0.id) watch_count from "user" as u0 where u0.id = :id ''' ), id=id).fetchone())
def rename_watch(conn, user_id, query_id, name): conn.execute(sql(''' update watch set name = :name where user_id = :user_id and query_id = :query_id '''), user_id=user_id, query_id=query_id, name=name)
def get_email_watch_states(conn, user_id, query_id): return [bunchr(**row) for row in conn.execute(sql( ''' select e0.*, (select count(1) from watch as w0 where w0.email_id = e0.id and w0.query_id = :query_id) watching from email e0 where e0.user_id = :user_id order by e0.ctime ''' ), user_id=user_id, query_id=query_id).fetchall()]
def get_user_detail_bi_id(conn, id): return enrich( conn, conn.execute(sql(''' select u0.*, (select count(1) from watch as w0 where w0.user_id = u0.id) watch_count from "user" as u0 where u0.id = :id '''), id=id).fetchone())
def get_active_query_count(conn): return conn.execute(sql( ''' select count(1) from query as q0 where exists ( select 1 from watch as w0 where w0.query_id = q0.id ) ''' )).fetchone()[0]
def add_email_bi_user_id(conn, id, email, label): # http://stackoverflow.com/a/14485817/238472 if not email or parseaddr(email) == ('', ''): raise InvalidEmailError(email) return conn.execute(sql( ''' insert into email (text, label, user_id) values (:text, :label, :user_id) returning * ''' ), text=email, label=label, user_id=id).fetchone()[0]
def get_users_detail(conn, offset=None, limit=None): result = conn.execute(sql('\n'.join([ ''' select u0.*, (select count(1) from watch as w0 where w0.user_id = u0.id) watch_count from "user" as u0 order by u0.id ''', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [bunchr(**row) for row in result.fetchall()]
def get_users_detail(conn, offset=None, limit=None): result = conn.execute( sql('\n'.join([ ''' select u0.*, (select count(1) from watch as w0 where w0.user_id = u0.id) watch_count from "user" as u0 order by u0.id ''', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [bunchr(**row) for row in result.fetchall()]
def get_email_watch_states(conn, user_id, query_id): return [ bunchr(**row) for row in conn.execute(sql(''' select e0.*, (select count(1) from watch as w0 where w0.email_id = e0.id and w0.query_id = :query_id) watching from email e0 where e0.user_id = :user_id order by e0.ctime '''), user_id=user_id, query_id=query_id).fetchall() ]
def unwatch(conn, query_id, user_id=None, email_id=None): user_id, email_id = fill_id(conn, user_id, email_id) if user_id is None or email_id is None: return conn.execute(sql( ''' delete from watch where email_id = :email_id and query_id = :query_id ''' ), email_id=email_id, query_id=query_id)
def rename_watch(conn, query_id, name, user_id=None, email_id=None): user_id, email_id = fill_id(conn, user_id, email_id) if user_id is None or email_id is None: return conn.execute(sql( ''' update watch set name = :name where email_id = :email_id and query_id = :query_id ''' ), email_id=email_id, query_id=query_id, name=name)
def unwatch(conn, query_id, user_id=None, email_id=None): user_id, email_id = fill_id(conn, user_id, email_id) if user_id is None or email_id is None: return conn.execute(sql(''' delete from watch where email_id = :email_id and query_id = :query_id '''), email_id=email_id, query_id=query_id)
def del_old_changes(conn, before, limit): conn.execute(sql( ''' delete from change where change.id in ( select c0.id from change as c0 where c0.ctime < :before limit :limit ) ''' ), before=before, limit=limit)
def rename_watch(conn, query_id, name, user_id=None, email_id=None): user_id, email_id = fill_id(conn, user_id, email_id) if user_id is None or email_id is None: return conn.execute(sql(''' update watch set name = :name where email_id = :email_id and query_id = :query_id '''), email_id=email_id, query_id=query_id, name=name)
def get_queries(conn, offset=None, limit=None, order_by=None, desc=False): ignore_none(check_order_field)(order_by) result = conn.execute(sql('\n'.join([ ''' select * from query as q0 ''', '' if order_by is None else 'order by q0.%s' % order_by, '' if order_by is None or not desc else 'desc', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [mq(**row) for row in result.fetchall()]
def add_email_bi_user_id(conn, id, email, label): # http://stackoverflow.com/a/14485817/238472 if not email or parseaddr(email) == ('', ''): raise InvalidEmailError(email) return conn.execute(sql(''' insert into email (text, label, user_id) values (:text, :label, :user_id) returning * '''), text=email, label=label, user_id=id).fetchone()[0]
def get_users_detail(conn, offset=None, limit=None, order_by=None, desc=False): ignore_none(check_order_field)(order_by) result = conn.execute(sql('\n'.join([ ''' select u0.*, (select count(1) from watch as w0 where w0.user_id = u0.id) watch_count from "user" as u0 ''', '' if order_by is None else 'order by u0.%s' % order_by, '' if order_by is None or not desc else 'desc', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [bunchr(**row) for row in result.fetchall()]
def get_users_detail(conn, offset=None, limit=None, order_by=None, desc=False): ignore_none(check_order_field)(order_by) result = conn.execute( sql('\n'.join([ ''' select u0.*, (select count(1) from watch as w0 where w0.user_id = u0.id) watch_count from "user" as u0 ''', '' if order_by is None else 'order by u0.%s' % order_by, '' if order_by is None or not desc else 'desc', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [bunchr(**row) for row in result.fetchall()]
def get_sorted_active_queries(conn, offset=None, limit=None): result = conn.execute(sql('\n'.join([ ''' select * from query as q0 where exists ( select 1 from watch as w0 where w0.query_id = q0.id ) order by q0.ctime ''', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [mq(**row) for row in result.fetchall()]
def get_need_sync_queries(conn, offset=None, limit=None): result = conn.execute(sql('\n'.join([ ''' select * from query as q0 where exists ( select 1 from watch as w0 where w0.query_id = q0.id and q0.next_sync_time is null ) or q0.next_sync_time <= (now() at time zone 'utc') order by q0.id ''', '' if offset is None else 'offset :offset', '' if limit is None else 'limit :limit' ])), **dict(offset=offset, limit=limit)) return [mq(**row) for row in result.fetchall()]
def fill_id(conn, user_id, email_id): assert user_id is not None or email_id is not None if email_id is None: email_id = conn.execute(sql( 'select id from email where user_id = :user_id order by ctime limit 1' ), user_id=user_id).fetchone() if email_id: email_id = email_id[0] if user_id is None: user_id = conn.execute( sql('select user_id from email where id = :email_id'), email_id=email_id).fetchone() if user_id: user_id = user_id[0] if user_id is None: raise UserNotExistError() if email_id is None: raise EmailNotExistError() return user_id, email_id
def watch(conn, query_id, user_id=None, email_id=None, name=None): user_id, email_id = fill_id(conn, user_id, email_id) if user_id is None or email_id is None: return conn.execute(sql( ''' insert into watch (user_id, query_id, email_id%s) select :user_id, :query_id, :email_id%s where not exists ( select 1 from watch where email_id = :email_id and query_id = :query_id ) ''' % (('', '') if name is None else (', name', ', :name')) ), user_id=user_id, query_id=query_id, email_id=email_id, name=name)
def del_inactive_queries(conn, before, limit): conn.execute(sql( ''' delete from query where query.id in ( select q0.id from query as q0 where q0.ctime < :before and not exists ( select 1 from watch as w0 where w0.query_id = q0.id ) limit :limit ) ''' ), id=id, before=before, limit=limit)
def get_watches_bi_user_id(conn, user_id): result = conn.execute(sql(''' select user_id, query_id, watch.name, watch.ctime, query.kind as query_kind, query.text as query_text, query.mtime as query_mtime from watch inner join query on watch.query_id = query.id where user_id = :user_id order by watch.ctime desc '''), user_id=user_id) return [bunchr(**row) for row in result.fetchall()]
def get_watches_bi_user_id(conn, user_id): result = conn.execute(sql( ''' select user_id, query_id, watch.name, watch.ctime, query.kind as query_kind, query.text as query_text, query.mtime as query_mtime from watch inner join query on watch.query_id = query.id where user_id = :user_id order by watch.ctime desc ''' ), user_id=user_id) return [bunchr(**row) for row in result.fetchall()]
def _batch_get_content(self, volume: int, keys: List[_KEY]) -> List[Tuple[_KEY, str]]: q = sql(""" SELECT id, content FROM {table} WHERE id IN :id_tuple """.format(table=self._client.get_table(volume))) id_tuple = self._to_id_tuple(keys) with self._client.get_engine(volume).connect() as conn: rows = list(conn.execute(q, id_tuple=id_tuple).fetchall()) result = [] for story_id, content_data in rows: key = StoryId.decode(story_id) if content_data: content = StoryData.decode_text(content_data) else: content = None result.append((key, content)) return result
def _batch_save_content(self, volume: int, items: List[Tuple[_KEY, str]]) -> None: q = sql(""" INSERT INTO {table} (id, content) VALUES (:id, :content) ON CONFLICT (id) DO UPDATE SET content = EXCLUDED.content """.format(table=self._client.get_table(volume))) params = [] for (feed_id, offset), content in items: story_id = StoryId.encode(feed_id, offset) if content: content_data = StoryData.encode_text(content) else: content_data = b'' params.append({'id': story_id, 'content': content_data}) with self._client.get_engine(volume).connect() as conn: with conn.begin(): conn.execute(q, params)
def watch(conn, query_id, user_id=None, email_id=None, name=None): user_id, email_id = fill_id(conn, user_id, email_id) if user_id is None or email_id is None: return conn.execute(sql(''' insert into watch (user_id, query_id, email_id%s) select :user_id, :query_id, :email_id%s where not exists ( select 1 from watch where email_id = :email_id and query_id = :query_id ) ''' % (('', '') if name is None else (', name', ', :name'))), user_id=user_id, query_id=query_id, email_id=email_id, name=name)
def get_pending_notices(conn): result = conn.execute(sql(''' select n0.id, n0.user_id, n0.ctime, n0.status, q0.kind as kind, c0.data as change from ( select * from notice where status = :status ) as n0 inner join change as c0 on n0.change_id = c0.id inner join query as q0 on c0.query_id = q0.id order by n0.ctime desc '''), status='pending') return [bunchr(**row) for row in result.fetchall()]
def get_notices_bi_user_id(conn, user_id, page=0, room=ROOM): result = conn.execute(sql(''' select n0.id, n0.user_id, n0.ctime, n0.status, q0.kind as kind, c0.data as change from ( select * from notice where user_id = :user_id ) as n0 inner join change as c0 on n0.change_id = c0.id inner join query as q0 on c0.query_id = q0.id order by n0.ctime desc offset :offset limit :limit '''), user_id=user_id, offset=page * room, limit=room) return [bunchr(**row) for row in result.fetchall()]
def get_pending_notices_bi_user_id(conn, user_id, page=0, room=ROOM): result = conn.execute(sql( ''' select n0.id, n0.user_id, n0.ctime, n0.status, q0.kind kind, c0.data change, n0.email from ( select * from notice where user_id = :user_id and status = :status ) n0 inner join change c0 on n0.change_id = c0.id inner join query q0 on c0.query_id = q0.id order by n0.ctime desc offset :offset limit :limit '''), user_id=user_id, status='pending', offset=page * room, limit=room) return [make(**row) for row in result.fetchall()]