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