def DateCol(default=True, update=False, **kwargs):
args = {}
# Using utcnow here caused double utc (add more hours), why? Just using now() for now,
# and assuming you're DB server is set on UTC
if default: args['server_default'] = satext("now()")
if update: args['onupdate'] = satext("now()")
return Column(TIMESTAMP(timezone=True), index=True, **args, **kwargs)
def notify_online(sess, id, status='on'):
# missing vals get server_default
sess.execute(
satext(f"""
insert into machines (id, status) values (:id, :status)
on conflict(id) do update set status=:status, updated_at=now()
"""), dict(id=id, status=status))
sess.commit()
def job_ct_on_machine(sess, id):
return sess.execute(
satext(f"""
select count(*) ct from jobs where state='working'
and machine_id=:id
-- check time in case broken/stale
and created_at > now() - interval '2 minutes'
"""), dict(id=id)).fetchone().ct
def place_in_queue(jid):
return db.session.execute(
satext(f"""
select (
(select count(*) from jobs where state in ('working', 'new') and created_at < (select created_at from jobs where id=:jid))
/ greatest((select count(*) from machines where status in ('on', 'pending')), 1)
) as ct
"""), dict(jid=jid)).fetchone().ct
def update_avg(fid):
db.session.execute(
satext("""
update fields set avg=(
select avg(value) from field_entries2 fe
where fe.field_id=:fid and fe.value is not null
) where id=:fid
"""), dict(fid=fid))
db.session.commit()
def await_row(sess, sql, args={}, wait=.5, timeout=None):
i = 0
while True:
res = sess.execute(satext(sql), args).fetchone()
if res: return res
time.sleep(wait)
if timeout and wait * i >= timeout:
return None
i += 1
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(Unicode, nullable=False)
text = Column(Unicode, nullable=False)
author = Column(Unicode)
topic = Column(Unicode)
thumbs = Column(Integer, server_default=satext("0"))
amazon = Column(Unicode)
def get_shelf(user_id, shelf):
books = db.session.execute(
satext(f"""
select b.id, b.title, b.text, b.author, b.topic, b.amazon
from books b
inner join bookshelf bs on bs.book_id=b.id
and bs.user_id=:uid and bs.shelf=:shelf
"""), dict(uid=user_id, shelf=shelf)).fetchall()
print(len(books))
return books
class Message(Base):
__tablename__ = 'messages'
id = Column(BigInteger, primary_key=True)
text = Column(UnicodeText, nullable=False)
image = Column(UnicodeText)
url = Column(UnicodeText, nullable=False)
sent_at = Column(TIMESTAMP, nullable=True, server_default=None)
is_success = Column(Boolean, nullable=True)
created_at = Column(TIMESTAMP, server_default=func.now())
updated_at = Column(TIMESTAMP, server_default=satext('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
def get_day_entries(sess, user_id, day=None):
user_id = str(user_id)
tz = User.tz(sess, user_id)
res = sess.execute(
satext(f"""
select fe.* from field_entries2 fe
where fe.user_id=:user_id
and date({tz_read})=
--use created_at rather than day in case they switch timezones
date(fe.created_at {at_tz})
"""), dict(user_id=user_id, day=day, tz=tz))
return res.fetchall()
def create_job(method, data_in={}, **kwargs):
"""
Ensures certain jobs only created once at a time. Never manually add Job() call this instead
"""
with session() as sess:
arg0 = data_in.get('args', [None])[0]
if type(arg0) != str: arg0 = None
# For entries, profiles: set ai_ran=False to queue them into the next batch,
# then arg0 isn't used downstream (was previously).
if method in ('entries', 'profiles') and arg0:
table = dict(entries='entries', profiles='users')[method]
sess.execute(
satext(f"""
update {table} set ai_ran=False where id=:id;
"""), dict(id=arg0))
sess.commit()
exists = sess.execute(
satext("""
select 1 from jobs
-- maybe if we're mid-job, things have changed; so don't incl. working? rethink
--where method=:method and state in ('new', 'working') and
where method=:method and state='new' and
case
when method='influencers' then true
when method='books' and data_in->'args'->>0=:arg0 then true
when method='entries' then true
when method='profiles' then true
when method='habitica' then true
else false
end
"""), dict(method=method, arg0=arg0)).fetchone()
if exists: return False
j = Job(method=method, data_in=data_in, **kwargs)
sess.add(j)
sess.commit()
sess.refresh(j)
return str(j.id)
def upsert(user_id, book_id, shelf):
db.session.execute(
satext("""
insert into bookshelf(book_id, user_id, shelf)
values (:book_id, :user_id, :shelf)
on conflict (book_id, user_id) do update set shelf=:shelf
"""), dict(user_id=user_id, book_id=int(book_id), shelf=shelf))
dir = dict(ai=0,
like=1,
already_read=1,
dislike=-1,
remove=0,
recommend=1)[shelf]
db.session.execute(
satext("""
update books set thumbs=thumbs+:dir where id=:bid
"""), dict(dir=dir, bid=book_id))
db.session.commit()
threading.Thread(target=Bookshelf.update_books,
args=(user_id, )).start()
def update_books(user_id):
with db():
# every x thumbs, update book recommendations
sql = """
select count(*)%8=0 as ct from bookshelf
where user_id=:uid and shelf not in ('ai', 'cosine')
"""
should_update = db.session.execute(satext(sql), {
'uid': user_id
}).fetchone().ct
if should_update:
Job.create_job(method='books',
data_in={'args': [str(user_id)]})
def take_job(sess, sql_frag):
job = sess.execute(
satext(f"""
update jobs set state='working', machine_id=:machine
where id in (
select id from jobs
where state='new' and {sql_frag}
order by created_at asc
limit 1
)
returning id, method
"""), dict(machine=vars.MACHINE)).fetchone()
sess.commit()
return job
def multiple_book_jobs(uids):
with session() as sess:
sess.execute(
satext("""
update users set last_books=null where id in :uids;
"""), dict(uids=tuple(uids)))
sess.commit()
# TODO handle this in run.py when it's consuming jobs
def delay_books(uid, i):
time.sleep(i * 60 * 5) # 5m
Job.create_job('books', data_in=dict(args=[str(uid)]))
for i, uid in enumerate(uids):
threading.Thread(target=delay_books, args=(uid, i)).start()
def wrap_job(jid, method, fn):
logger.info(f"Run job {method}")
try:
start = time.time()
res = fn()
sql = "update jobs set state='done', data_out=:data where id=:jid"
logger.info(f"Job {method} complete {time.time() - start}")
except Exception as err:
err = str(traceback.format_exc()) # str(err)
res = dict(error=err)
sql = "update jobs set state='error', data_out=:data where id=:jid"
logger.error(f"Job {method} error {time.time() - start} {err}")
with session() as sess:
sess.execute(satext(sql), dict(data=jsonb(res), jid=str(jid)))
sess.commit()
def snoop(viewer_email: str,
target_id: str,
snooping: bool = False,
entry_id: str = None,
order_by=None,
tags: List[str] = None,
days: int = None,
for_ai: bool = False):
if not snooping:
q = db.session.query(Entry).filter(Entry.user_id == target_id)
if snooping:
q = db.session.query(Entry)\
.join(EntryTag, Entry.id == EntryTag.entry_id)\
.join(ShareTag, EntryTag.tag_id == ShareTag.tag_id)\
.join(Share, ShareTag.share_id == Share.id)\
.filter(Share.email == viewer_email, Share.user_id == target_id)
# TODO use ORM partial thus far for this query command, not raw sql
sql = f"""
update shares set last_seen=now(), new_entries=0
where email=:email and user_id=:uid
"""
db.session.execute(satext(sql),
dict(email=viewer_email, uid=target_id))
db.session.commit()
if entry_id:
q = q.filter(Entry.id == entry_id)
if for_ai:
q = q.filter(Entry.no_ai.isnot(True))
if tags:
if not snooping:
# already joined otherwise
q = q.join(EntryTag, Tag)
q = q.filter(EntryTag.tag_id.in_(tags))
if days:
now = datetime.datetime.utcnow()
x_days = now - datetime.timedelta(days=days)
# build a beginning-to-end story
q = q.filter(Entry.created_at > x_days)
order_by = Entry.created_at.asc()
if order_by is None:
order_by = Entry.created_at.desc()
return q.order_by(order_by)
def update_snoopers(self):
"""Updates snoopers with n_new_entries since last_seen"""
sql = """
with news as (
select s.id, count(e.id) ct
from shares s
inner join shares_tags st on st.share_id=s.id
inner join entries_tags et on et.tag_id=st.tag_id
inner join entries e on e.id=et.entry_id
where e.user_id=:uid
and e.created_at > s.last_seen
group by s.id
)
update shares s set new_entries=n.ct from news n where n.id=s.id
"""
db.session.execute(satext(sql), {'uid': self.user_id})
db.session.commit()
class Share(Base):
__tablename__ = 'shares'
id = IDCol()
user_id = FKCol('users.id', index=True)
email = Column(EmailType, index=True) # TODO encrypt?
fields = Column(Boolean)
books = Column(Boolean)
profile = Column(Boolean)
share_tags = relationship("ShareTag", **parent_cascade)
tags_ = relationship("Tag", secondary="shares_tags")
last_seen = DateCol()
new_entries = Column(Integer, server_default=satext("0"))
@property
def tags(self):
return {t.tag_id: True for t in self.share_tags}
def upsert(sess, user_id, field_id, value, day: str = None):
"""
Create a field-entry, but if one exists for the specified day update it instead.
"""
# Timezone-handling is very complicated. Defer as much to Postgres (rather than Python) to keep
# to one system as much as possible. Below says "if they said a day (like '2020-11-19'), convert that
# to a timestamp at UTC; then convert that to their own timezone". timestamptz cast first is important, see
# https://stackoverflow.com/a/25123558/362790
tz = User.tz(sess, user_id)
res = sess.execute(
satext(f"""
insert into field_entries2 (user_id, field_id, value, day, created_at)
values (:user_id, :field_id, :value, date({tz_read}), {tz_write})
on conflict (field_id, day) do update set value=:value, dupes=null, dupe=0
returning *
"""),
dict(field_id=field_id,
value=float(value),
user_id=user_id,
day=day,
tz=tz))
sess.commit()
return res.fetchone()
def DateCol(default=True, update=False):
args = {}
if default: args['server_default'] = satext(utcnow)
if update: args['onupdate'] = satext(utcnow)
return Column(TIMESTAMP(timezone=True), index=True, **args)
def tz(sess, user_id):
return sess.execute(
satext(f"""
select coalesce(timezone, 'America/Los_Angeles') as tz
from users where id=:user_id
"""), dict(user_id=user_id)).fetchone().tz
def IDCol():
return Column(UUID(as_uuid=True),
primary_key=True,
server_default=satext("uuid_generate_v4()"))
