def delete(self): if self.user_can_delete(): with DelayCommit(db): db.seminars.delete({"shortname": self.shortname}) db.talks.delete({"seminar_id": self.shortname}) db.seminar_organizers.delete({"seminar_id": self.shortname}) for elt in db.users.search( {"seminar_subscriptions": { "$contains": self.shortname }}, ["id", "seminar_subscriptions"], ): elt["seminar_subscriptions"].remove(self.shortname) db.users.update({"id": elt["id"]}, { "seminar_subscriptions": elt["seminar_subscriptions"] }) for i, talk_sub in db._execute( SQL("SELECT {},{} FROM {} WHERE {} ? %s").format(*map( IdentifierWrapper, [ "id", "talk_subscriptions", "users", "talk_subscriptions" ], )), [self.shortname], ): del talk_sub[self.shortname] db.users.update({"id": i}, {"talk_subscriptions": talk_sub}) return True else: return False
def delete(self): if self.user_can_delete(): with DelayCommit(db): db.talks.update( { "seminar_id": self.seminar_id, "seminar_ctr": self.seminar_ctr }, { "deleted": True, "deleted_with_seminar": False }) for i, talk_sub in db._execute( SQL("SELECT {},{} FROM {} WHERE {} ? %s").format(*map( IdentifierWrapper, [ "id", "talk_subscriptions", "users", "talk_subscriptions" ], )), [self.seminar.shortname], ): if self.seminar_ctr in talk_sub[self.seminar.shortname]: talk_sub[self.seminar.shortname].remove( self.seminar_ctr) db.users.update({"id": i}, {"talk_subscriptions": talk_sub}) self.deleted = True return True else: return False
def delete(self): # We don't actually delete from the seminars and talks tables but instead just # set the deleted flag. We actually delete from seminar_organizers and subscriptions # since these are less important. if self.user_can_delete(): with DelayCommit(db): db.seminars.update({"shortname": self.shortname}, {"deleted": True}) db.talks.update({"seminar_id": self.shortname, "deleted": False}, {"deleted": True, "deleted_with_seminar": True}) for elt in db.users.search( {"seminar_subscriptions": {"$contains": self.shortname}}, ["id", "seminar_subscriptions"], ): elt["seminar_subscriptions"].remove(self.shortname) db.users.update( {"id": elt["id"]}, {"seminar_subscriptions": elt["seminar_subscriptions"]} ) for i, talk_sub in db._execute( SQL("SELECT {},{} FROM {} WHERE {} ? %s").format( *map( IdentifierWrapper, ["id", "talk_subscriptions", "users", "talk_subscriptions"], ) ), [self.shortname], ): del talk_sub[self.shortname] db.users.update({"id": i}, {"talk_subscriptions": talk_sub}) self.deleted = True return True else: return False
def registered_users(self): """ returns a list of tuples (name, affiliation, homepage, email, registration_time) in reverse order by registration time """ # FIXME: Should we use IdentifierWrapper here? query = """ SELECT users.name, users.homepage, users.affiliation, talk_registrations.registration_time FROM talk_registrations INNER JOIN users ON users.id = talk_registrations.user_id WHERE talk_registrations.seminar_id = '%s' AND talk_registrations.seminar_ctr = %d ORDER BY talk_registrations.registration_time DESC """ return list(db._execute(SQL(query % (self.seminar_id, self.seminar_ctr))))
def sanitized_table(name): cur = db._execute( SQL("SELECT name, label_col, sort, count_cutoff, id_ordered, out_of_order, " "has_extras, stats_valid, total, include_nones FROM meta_tables WHERE name=%s" ), [name]) def update(self, query, changes, resort=False, restat=False, commit=True): raise APIError({"code": "update_prohibited"}) def insert_many(self, data, resort=False, reindex=False, restat=False, commit=True): raise APIError({"code": "insert_prohibited"}) # We remove the raw argument from search and lucky keywords since these allow the execution of arbitrary SQL def search(self, *args, **kwds): kwds.pop("raw", None) return PostgresSearchTable.search(self, *args, **kwds) def lucky(self, *args, **kwds): kwds.pop("raw", None) return PostgresSearchTable.lucky(self, *args, **kwds) from seminars import count table = PostgresSearchTable(db, *cur.fetchone()) table.update = update.__get__(table) table.count = count.__get__(table) table.search = search.__get__(table) table.lucky = lucky.__get__(table) table.insert_many = insert_many.__get__(table) table.search_cols = [ col for col in table.search_cols if col in whitelisted_cols ] table.col_type = { col: typ for (col, typ) in table.col_type.items() if col in whitelisted_cols } return table
def index(): # TODO: use a join for the following query seminars = {} conferences = {} deleted_seminars = [] deleted_talks = [] def key(elt): role_key = {"organizer": 0, "curator": 1, "creator": 3} return (role_key[elt[1]], elt[0].name) for rec in db.seminar_organizers.search( {"email": ilike_query(current_user.email)}, ["seminar_id", "curator"]): semid = rec["seminar_id"] role = "curator" if rec["curator"] else "organizer" seminar = WebSeminar(semid) pair = (seminar, role) if seminar.is_conference: conferences[semid] = pair else: seminars[semid] = pair role = "creator" for semid in seminars_search({"owner": ilike_query(current_user.email)}, "shortname", include_deleted=True): if semid not in seminars and semid not in conferences: seminar = WebSeminar(semid, deleted=True) # allow deleted pair = (seminar, role) if seminar.deleted: deleted_seminars.append(seminar) elif seminar.is_conference: conferences[semid] = pair else: seminars[semid] = pair seminars = sorted(seminars.values(), key=key) conferences = sorted(conferences.values(), key=key) deleted_seminars.sort(key=lambda sem: sem.name) for semid, semctr in db._execute( # ~~* is case insensitive amtch SQL(""" SELECT DISTINCT ON ({Ttalks}.{Csemid}, {Ttalks}.{Csemctr}) {Ttalks}.{Csemid}, {Ttalks}.{Csemctr} FROM {Ttalks} INNER JOIN {Tsems} ON {Ttalks}.{Csemid} = {Tsems}.{Csname} WHERE {Tsems}.{Cowner} ~~* %s AND {Ttalks}.{Cdel} = %s AND {Tsems}.{Cdel} = %s """).format( Ttalks=IdentifierWrapper("talks"), Tsems=IdentifierWrapper("seminars"), Csemid=IdentifierWrapper("seminar_id"), Csemctr=IdentifierWrapper("seminar_ctr"), Csname=IdentifierWrapper("shortname"), Cowner=IdentifierWrapper("owner"), Cdel=IdentifierWrapper("deleted"), ), [ilike_escape(current_user.email), True, False], ): talk = WebTalk(semid, semctr, deleted=True) deleted_talks.append(talk) deleted_talks.sort(key=lambda talk: (talk.seminar.name, talk.start_time)) manage = "Manage" if current_user.is_organizer else "Create" return render_template( "create_index.html", seminars=seminars, conferences=conferences, deleted_seminars=deleted_seminars, deleted_talks=deleted_talks, institution_known=institution_known, institutions=institutions(), section=manage, subsection="home", title=manage, user_is_creator=current_user.is_creator, )