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