def _db_connect(self, view): dbc = view.get_data('dbc') d = dialogs.ConnectionDialog(__gladeFile__, self.window, self.dbpool) result, options = d.run(dbc) if result == 2: self._db_disconnect(view) elif result == 1: if dbc != None and dbc.is_connected(): self._db_disconnect(view) try: dbc = db.get_connector(options) dbc.connect() self.dbpool.append(dbc) panel = self.window.get_bottom_panel() rset = panels.ResultsetPanel(__gladeFile__, panel) view.set_data('dbc', dbc) view.set_data('resultset_panel', rset) except Exception, e: error_dialog = dialogs.ConnectionErrorDialog("\n %s \n" % (str(e)), self.window) error_dialog.run() error_dialog.destroy() exit = False
def run(self, active_connection): cnn = None result = self.dialog.run() if result == 1: try: options = self.get_options() cnn = db.get_connector(options) except db.InvalidConnectorError, e: pass
def answers_count(self): cursor = get_connector().cursor() query = """ SELECT COUNT(*) FROM answer INNER JOIN thread ON thread.id = answer.thread WHERE thread.id = %(thread_id)s """ cursor.execute(query, {'thread_id': self.id}) count = next(cursor)[0] cursor.close() return count
def years(self): """ Statistics by years """ start_year = request.args.get('start', 2000) end_year = request.args.get('end', 2018) if end_year <= start_year: abort(HTTPStatus.BAD_REQUEST) threads_query = f""" SELECT COUNT(*), YEAR(created_at) as year FROM thread WHERE YEAR(created_at) BETWEEN {start_year} AND {end_year} GROUP BY YEAR(created_at) ORDER BY year; """ cursor = get_connector().cursor() cursor.execute(threads_query) years = [] created_threads = [] for threads_count, year in cursor: years.append(year) created_threads.append(threads_count) users_query = f""" SELECT COUNT(*), YEAR(registered_at) as year FROM user WHERE YEAR(registered_at) BETWEEN {start_year} AND {end_year} GROUP BY YEAR(registered_at) ORDER BY year; """ cursor.execute(users_query) registered_users = [] for users_count, year in cursor: if year in years: registered_users.append(users_count) return jsonify({ 'years': years, 'created_threads': created_threads, 'registered_users': registered_users, })
def on_btnSave_clicked(self, widget): options = self.get_options() try: cnn = db.get_connector(options) treestore = self.treeview.get_model() new_connection = treestore.append(self.stored, [cnn]) treeselection = self.treeview.get_selection() treeselection.select_path(treestore.get_path(new_connection)) self.on_tvConnections_cursor_changed(self.treeview) self.save_connections() except db.InvalidConnectorError, e: pass
def update_selected_row(self): if not self.is_stored_connection(self.selected): return options = self.get_options() try: cnn = db.get_connector(options) model = self.treeview.get_model() model.set_value(self.selected, 0, cnn) self.save_connections() except db.InvalidConnectorError, e: pass
def threads_count(self): cursor = get_connector().cursor() query = """ SELECT COUNT(*) FROM thread INNER JOIN section ON section.id = thread.section WHERE ( section.id = %(parent_section)s OR section.parent = %(parent_section)s ) """ cursor.execute(query, {'parent_section': self.id}) count = next(cursor)[0] cursor.close() return count
def set_last_answer_time(self): cursor = get_connector().cursor() query = """ SELECT created_at FROM answer WHERE thread = %(thread_id)s ORDER BY created_at DESC LIMIT 1 """ cursor.execute(query, {'thread_id': self.id}) try: self.last_answer_time = next(cursor)[0] self.save() except StopIteration: pass
def section_view(section_id): section = Section.get(section_id) forum = Forum.get(section.forum) parent_section = Section.get(section.parent) subsections = Section.filter(parent=section.id) # Threads try: page = int(request.args.get('page')) except (TypeError, ValueError): page = 1 if not page or page < 1: page = 1 threads_count = len(Thread.filter(section=section.id)) # FIXME(a.telishev): Threads per page by search pages_count = threads_count // THREADS_PER_PAGE if page > pages_count: page = pages_count prev_page = page - 1 next_page = page + 1 if page == 1: prev_page = None if page == pages_count: next_page = None offset = (page - 1) * THREADS_PER_PAGE search = request.args.get('search', '') search_condition = f'AND thread.title LIKE "%{search}%"' if search else '' query = f""" SELECT thread.id thread_id, thread.title thread_title, thread.created_at thread_created_at, thread.last_answer_time thread_last_answer_time, user.id user_id, user.username username FROM thread INNER JOIN user ON thread.author = user.id WHERE section = %(section_id)s {search_condition} ORDER BY thread.last_answer_time DESC LIMIT %(limit)s OFFSET %(offset)s; """ cursor = get_connector().cursor() cursor.execute(query, { 'section_id': section.id, 'limit': THREADS_PER_PAGE, 'offset': offset, }) threads = { thread_id: { 'id': thread_id, 'title': thread_title, 'created_at': thread_created_at.strftime('%d %b %Y'), 'created_at_h': thread_created_at.strftime('%d %b %Y\n%H:%M:%S'), 'last_answer_time': (thread_last_answer_time.strftime('%d %b %Y\n%H:%M:%S') if thread_last_answer_time else None), 'user_id': user_id, 'username': username, } for ( thread_id, thread_title, thread_created_at, thread_last_answer_time, user_id, username, ) in cursor } if threads: answers_count_query = f""" SELECT thread.id, COUNT(*) FROM thread INNER JOIN answer on thread.id = answer.thread WHERE thread.id IN ({ ', '.join(str(thread_id) for thread_id in threads) }) GROUP BY thread.id; """ cursor.execute(answers_count_query) for thread_id, answers_count in cursor: threads[thread_id]['answers_count'] = answers_count cursor.close() return render_template( 'section.html', section=section, forum=forum, parent_section=parent_section, subsections=subsections, threads=threads.values(), search=search, next_page=next_page, curr_page=page, prev_page=prev_page, )
def thread_view(thread_id): thread = Thread.get(thread_id) forum = Forum.get(thread.forum) section = Section.get(thread.section) parent_section = Section.get(section.parent) author = User.get(thread.author) answers_query = """ SELECT answer.id answer_id, answer.text answer_text, answer.rating answer_rating, answer.created_at answer_created_at, answer.is_off_topic answer_is_off_topic, user.id user_id, user.username username, user.msg_count user_msg_count, user.msg_signature user_signature, user.registered_at user_registered_at FROM answer INNER JOIN user ON answer.author = user.id WHERE answer.thread = %(thread_id)s ORDER BY answer.created_at; """ cursor = get_connector().cursor() cursor.execute(answers_query, {'thread_id': thread.id}) answers = [ Answer( id=answer_id, text=answer_text, rating=answer_rating, created_at=answer_created_at, is_off_topic=answer_is_off_topic, author=User( id=user_id, username=username, msg_count=user_msg_count, msg_signature=user_signature, registered_at=user_registered_at, ) ) for ( answer_id, answer_text, answer_rating, answer_created_at, answer_is_off_topic, user_id, username, user_msg_count, user_signature, user_registered_at, ) in cursor ] labels_query = """ SELECT thread_label.id label_id, thread_label.text label_text FROM threads_labels INNER JOIN thread_label ON threads_labels.label = thread_label.id WHERE threads_labels.thread = %(thread_id)s; """ cursor = get_connector().cursor() cursor.execute(labels_query, {'thread_id': thread.id}) labels = [ ThreadLabel(id=label_id, text=label_text) for label_id, label_text in cursor ] cursor.close() return render_template( 'thread.html', forum=forum, parent_section=parent_section, section=section, author=author, thread=thread, labels=labels, answers=answers, )
def index_view(): # TODO(a.telishev): More ORM! threads_query = """ SELECT forum.id forum_id, forum.title forum_title, section.id section_id, section.title section_title, parent_section.id parent_section_id, parent_section.title parent_section_title FROM section LEFT JOIN section parent_section ON section.parent = parent_section.id INNER JOIN forum ON section.forum = forum.id; """ cursor = get_connector().cursor() cursor.execute(threads_query) # forums = { # forum_id: { # 'id': forum_id, # 'title': forum_title, # 'sections': { # 'section_title': { # 'title': section_title, # 'threads_count': section_threads_count, # 'answers_count': section_answers_count, # 'subsections': [{ # 'id': subsection_id, # 'title': subsection_title, # }, ...], # }, # ... # }, # }, # ... # } # forums = {} for forum_id, forum_title, section_id, section_title, parent_section_id, parent_section_title in cursor: if forum_id not in forums: forums[forum_id] = { 'id': forum_id, 'title': forum_title, 'sections': {}, } else: if not parent_section_id: continue # TODO(a.telyshev): Oh, my God :( forums[forum_id]['sections'].setdefault( parent_section_id, { 'id': parent_section_id, 'title': parent_section_title, 'subsections': [], })['subsections'].append({ 'id': section_id, 'title': section_title }) for _, forum in forums.items(): for section_title, section in forum['sections'].items(): # TODO(a.telishev): More ORM! threads_count_query = """ SELECT COUNT(*) FROM thread INNER JOIN section ON section.id = thread.section WHERE ( section.id = %(parent_section)s OR section.parent = %(parent_section)s ) """ cursor.execute(threads_count_query, {'parent_section': section['id']}) forum['sections'][section_title]['threads_count'] = next(cursor)[0] answers_count_query = """ SELECT COUNT(*) FROM answer INNER JOIN thread ON thread.id = answer.thread INNER JOIN section ON section.id = thread.section WHERE ( section.id = %(parent_section)s OR section.parent = %(parent_section)s ) """ cursor.execute(answers_count_query, {'parent_section': section['id']}) forum['sections'][section_title]['answers_count'] = next(cursor)[0] cursor.close() return render_template('index.html', forums=forums)