Example #1
0
	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
Example #2
0
    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
Example #3
0
    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
Example #4
0
    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,
        })
Example #5
0
 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
Example #6
0
 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
Example #7
0
    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
Example #8
0
    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
Example #9
0
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,
    )
Example #10
0
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,
    )
Example #11
0
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)