def do_issue_new_session_id(uid, sid): models.db().execute(""" DELETE FROM MobileSession WHERE user_id = ?""", (uid, )) models.db().execute(""" INSERT INTO MobileSession (user_id, session_id, expire) VALUES (?, ?, datetime('now', '+1 month'))""", (uid, sid))
def post(body): models.db().execute(""" INSERT INTO BBS (user_id, when_, body) VALUES (?, ?, ?)""", ( models.user().id, datetime.now(), sanitize_html(body))) models.db().commit()
def insert_payment_histories(action, seasons): updater = models.user().id models.db().executemany(""" INSERT INTO TaxPaymentHistory ( year, user_id, season, action, updater_user_id) VALUES (?, ?, ?, ?, ?)""", [(year, user_id, s, action, updater) for s in seasons])
def update(sid, when_, body): models.db().execute(""" UPDATE Schedule SET when_ = ?, body = ? WHERE id = ?""", (when_, body, sid)) models.db().commit()
def update(id, title, begin_show, end_show, body): models.db().execute(""" UPDATE Notice SET title = ?, begin_show = ?, end_show = ?, body = ? WHERE id = ?""", (title, begin_show, end_show, body, id)) models.db().commit()
def handle_join_request(id): models.db().execute( """ UPDATE JoinRequest SET handled = 1 WHERE id = ?""", (id,), ) models.db().commit()
def update_entry(sid, comment, entry): models.db().execute(""" UPDATE Entry SET is_entry = ?, comment = ?, when_ = CURRENT_TIMESTAMP WHERE user_id = ? AND schedule_id = ?""", (entry, comment, models.user().id, sid)) models.db().commit()
def insert(name, home, email, sex, age, car, has_racket, holiday, experience, comment): models.db().execute( """ INSERT INTO JoinRequest (when_, name, home, email, sex, age, car, has_racket, holiday, experience, comment) VALUES (datetime('now', 'localtime'), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", (name, home, email, sex, age, car, has_racket, holiday, experience, comment), ) models.db().commit()
def insert(title, feature_image_url, description, body): with models.db(): models.db().execute(""" INSERT INTO Report (when_, author_id, title, feature_image_url, description, description_html, body, body_html) VALUES (datetime('now', 'localtime'), ?, ?, ?, ?, ?, ?, ?)""", (models.user().id, title, feature_image_url, description, sanitize_markdown(description), body, sanitize_markdown(body))) return models.db().execute(""" SELECT id FROM Report WHERE when_ = (SELECT MAX(when_) FROM Report)""").fetchone()[0]
def count_schedules(type): cur = models.db().execute(""" SELECT COUNT(*) FROM Schedule WHERE type = ? AND when_ >= datetime('now', 'localtime')""", (type, )) return cur.fetchone()[0]
def update_logged_in(self): db = models.db() db.execute(""" UPDATE User SET logged_in = datetime('now', 'localtime') WHERE id = ?""", (self.id, )) db.commit()
def find_scheduled(): cur = models.db().execute(""" SELECT * FROM Notice WHERE end_show >= date('now', 'localtime') ORDER BY begin_show""") return [from_row(r) for r in cur.fetchall()]
def find_guest_by_id(guest_id): return models.db().execute(""" SELECT GuestEntry.id, GuestEntry.name, GuestEntry.schedule_id, GuestEntry.comment, GuestEntry.inviter_id, User.name AS inviter_name FROM GuestEntry, User WHERE GuestEntry.id = ? AND inviter_id = User.id""", (guest_id, )).fetchone()
def is_valid_session_id(uid, sid): cur = models.db().execute(""" SELECT user_id FROM MobileSession WHERE user_id = ? AND session_id = ? AND expire > CURRENT_TIMESTAMP""", (uid, sid)) return cur.fetchone() is not None
def issue_new_session_id(uid): for sid in generate_session_id(6): try: with models.db(): do_issue_new_session_id(uid, sid) return sid except IntegrityError: pass # not unique
def is_entered(self, schedule): cur = models.db().execute(""" SELECT user_id FROM Entry WHERE user_id = ? AND schedule_id = ? AND is_entry = 1""", (self.id, schedule['id'])) return cur.fetchone() is not None
def update_payments(year, user_id, new_paid): records = models.db().execute(""" SELECT Tax.season FROM Tax WHERE Tax.year = ? AND Tax.user_id = ?""", (year, user_id)).fetchall() curr_paid = [r['season'] for r in records] insertions = [s for s in new_paid if s not in curr_paid] deletions = [s for s in curr_paid if s not in new_paid] # Tax テーブルの更新 models.db().executemany(""" INSERT INTO Tax (year, user_id, season) VALUES (?, ?, ?)""", [(year, user_id, s) for s in insertions]) models.db().executemany(""" DELETE FROM Tax WHERE year = ? AND user_id = ? AND season = ?""", [(year, user_id, s) for s in deletions]) # TaxPaymentHistory テーブルの更新 def insert_payment_histories(action, seasons): updater = models.user().id models.db().executemany(""" INSERT INTO TaxPaymentHistory ( year, user_id, season, action, updater_user_id) VALUES (?, ?, ?, ?, ?)""", [(year, user_id, s, action, updater) for s in seasons]) insert_payment_histories(1, insertions) insert_payment_histories(2, deletions) models.db().commit()
def find_not_handled(): cur = models.db().execute( """ SELECT * FROM JoinRequest WHERE handled = 0 ORDER BY when_ DESC""" ) return cur.fetchall()
def find_posts(begin): records = app.config['BBS_PER_PAGE'] return models.db().execute(""" SELECT User.name AS user_name, BBS.when_, BBS.body FROM User, BBS WHERE BBS.user_id = User.id ORDER BY BBS.when_ DESC LIMIT ?, ?""", (begin, records))
def find_group_by_sex(): # 特殊ユーザは一覧上に表示させないように無視する users = models.db().execute(''' SELECT * FROM User WHERE id <> ? ORDER BY sex, id''', (SPECIAL_USER, )).fetchall() bysex = {} for sex, us in groupby(users, lambda u: u['sex']): bysex[sex] = [from_row(u) for u in us] return bysex.get(SEX_MALE, []), bysex.get(SEX_FEMALE, [])
def count_has_not_handled(): ret = ( models.db() .execute( """ SELECT COUNT(*) FROM JoinRequest WHERE handled = 0""" ) .fetchone() ) return ret[0]
def update(self, title, feature_image_url, description, body): desc_html = sanitize_markdown(description) body_html = sanitize_markdown(body) models.db().execute(""" UPDATE Report SET when_ = datetime('now', 'localtime'), title = ?, feature_image_url = ?, description = ?, description_html = ?, body = ?, body_html = ? WHERE id = ?""", (title, feature_image_url, description, desc_html, body, body_html, self.id)) models.db().commit() self.title = title self.description = description self.description_html = desc_html self.body = body self.body_html = body_html
def newer_id(self): if self._newer_id_gotten: return self._newer_id newer = models.db().execute(""" SELECT id FROM Report WHERE when_ > ? ORDER BY when_""", (self.when, )).fetchone() self._newer_id = newer['id'] if newer else None self._newer_id_gotten = True return self._newer_id
def older_id(self): if self._older_id_gotten: return self._older_id older = models.db().execute(""" SELECT id FROM Report WHERE when_ < ? ORDER BY when_ DESC""", (self.when, )).fetchone() self._older_id = older['id'] if older else None self._older_id_gotten = True return self._older_id
def insert(name, password, sex, permission): check_unique_password(password) try: db = models.db() c = db.cursor() c.execute(""" INSERT INTO User (name, password, sex, permission) VALUES (?, ?, ?, ?)""", (name, password, sex, permission)) db.commit() return c.lastrowid except IntegrityError, e: raise NotUniquePassword() # そうとは限らないけど。。。
def find_all_group_by_publication(): sayings = models.db().execute(""" SELECT * FROM Saying ORDER BY private""").fetchall() bypublication = {} for private, ss in groupby(sayings, lambda s: s['private']): bypublication[private] = list(ss) public = bypublication.get(0, []) private = bypublication.get(1, []) return public, private
def update(id, password, sex, permission): check_unique_password(password, id) try: db = models.db() db.execute(""" UPDATE User SET password = ?, sex = ?, permission = ? WHERE id = ?""", (password, sex, permission, id)) db.commit() except IntegrityError, e: raise NotUniquePassword() # そうとは限らないけど。。。
def find_by_id(sid, with_entry=True): cur = models.db().execute(""" SELECT * FROM Schedule WHERE id = ?""", (sid, )) schedule = dict(cur.fetchone()) if with_entry: es, gs = entries.find_by_schedule(sid) schedule['entries'] = es schedule['guests'] = gs return schedule
def has_not_registered_schedule_yet(self): cur = models.db().execute(""" SELECT Schedule.id FROM Schedule LEFT OUTER JOIN (SELECT * FROM Entry WHERE user_id = ?) AS Entry ON Schedule.id = Entry.schedule_id WHERE Schedule.when_ >= datetime('now', 'localtime') AND Schedule.type = ? GROUP BY Schedule.id HAVING COUNT(Entry.user_id) = 0""", (self.id, scheds.TYPE_PRACTICE)) return cur.fetchone() is not None
def find_by_year(year): taxes = models.db().execute(""" SELECT User.name, User.id AS user_id, Tax.year, Tax.season FROM User LEFT OUTER JOIN (SELECT * FROM Tax WHERE year = ?) AS Tax ON User.id = Tax.user_id WHERE User.id <> -1 ORDER BY User.sex, User.id""", (year, )).fetchall() payments = [] id_and_name = lambda x: (x['user_id'], x['name']) for (user_id, name), paid_seasons in groupby(taxes, id_and_name): p = Payment(user_id, name) for paid_season in paid_seasons: p.pay(year, paid_season['season']) payments.append(p) histories = models.db().execute(""" SELECT User.name, Updater.name AS updater, TaxPaymentHistory.year, TaxPaymentHistory.season, TaxPaymentHistory.action, TaxPaymentHistory.when_ FROM TaxPaymentHistory INNER JOIN User ON User.id = TaxPaymentHistory.user_id INNER JOIN User AS Updater ON Updater.id = TaxPaymentHistory.updater_user_id WHERE TaxPaymentHistory.year = ? ORDER BY TaxPaymentHistory.when_ DESC LIMIT 20""", (year, )).fetchall() return PaymentStats(year, payments, histories)