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)
