def categorized():
'''create a categorized overview'''
g.db.row_factory = sqlite3.Row
cur = g.db.execute( '''SELECT id, title, tags
FROM categories
ORDER BY title ASC''' )
topics_rows = cur.fetchall()
# select all pages (entries)
cur = g.db.execute( '''SELECT id, ref, title, type, date_norm, tags
FROM entries
WHERE pub = 1
AND type IS NOT 'history'
ORDER BY date_norm DESC, time_norm DESC''' )
pages_rows = cur.fetchall()
# generate hrefs
hrefs = gen_hrefs(pages_rows)
# auto-magically categorize pages by tags
topics = []
unsorted = []
for topic in topics_rows:
topic_item = { 'id': topic['id'],
'title': topic['title'],
'tags': topic['tags'],
'pages': [] }
for page in pages_rows:
#pass
# comprehension
if len(set(map(str.strip, topic['tags'].split(','))).intersection(map(str.strip, page['tags'].split(',')))) > 0:
# match found
topic_item['pages'].append(page)
topics.append(topic_item)
# find unsorted
all_tags = []
for topic in topics_rows:
all_tags.extend(list(map(str.strip, topic['tags'].split(','))))
unsorted = []
for page in pages_rows:
if len(set(map(str.strip, page['tags'].split(','))).intersection(all_tags)) == 0:
unsorted.append(page)
topics.append( { 'id': None,
'title': "Unsorted",
'tags': [],
'pages': unsorted } )
return render_template( 'topics.html',
title = "Categorized",
topics = topics,
hrefs = hrefs )
def overview():
'''interface overview
shows:
- a list of all pages
- new page creation (done in template)
'''
if not session.get('logged_in'):
abort(401)
# get all pages
g.db.row_factory = sqlite3.Row
cur = g.db.execute('''SELECT id, type, title, date_norm, time_norm,
ref, pub
FROM entries
ORDER BY date_norm DESC, time_norm DESC''')
rows = cur.fetchall()
# get link hrefs
hrefs = gen_hrefs(rows)
# insert changelog
g.db.row_factory = sqlite3.Row
cur = g.db.execute('''SELECT id, entry_id, mod_type,
date_norm, time_norm, pub
FROM changelog
ORDER BY date_norm DESC, time_norm DESC''')
change_rows = cur.fetchall()
changes = []
for change_row in change_rows:
change = {}
for i, v in enumerate(change_row):
change.update( { change_row.keys()[i]: v } )
cur = g.db.execute('''SELECT title FROM entries
WHERE id = ?''', (change_row['entry_id'],))
entry_title = cur.fetchone()[0]
change.update( { 'entry_title': entry_title } )
changes.append(change)
# categories
g.db.row_factory = sqlite3.Row
cur = g.db.execute('''SELECT id, title, tags
FROM categories
ORDER BY id ASC''')
categories = cur.fetchall()
return render_template( 'overview.html',
entries = rows,
title = "Overview",
hrefs = hrefs,
changes = changes,
categories = categories )
def get_timeline_entries(n):
'''return n timeline entries'''
g.db.row_factory = sqlite3.Row
cur = g.db.execute('''SELECT id, ref, type, title,
date_norm, time_norm, body_html, tags
FROM entries
WHERE type != 'special'
AND pub = 1
ORDER BY date_norm DESC, time_norm DESC
LIMIT ?''', (n,))
pages_rows = cur.fetchall()
hrefs = gen_hrefs(pages_rows)
# WHERE ( type = 'article'
# OR type = 'note'
# OR type = 'latest' )
# create list (--> use list instear sqlite.Row instead ???)
pages = []
for page_row in pages_rows:
page = {}
for i, v in enumerate(page_row):
page.update( { page_row.keys()[i]: v } )
# limit page length
# more than three paragraphs
if page['body_html'].count('</p>') > 3:
body_html_cut = "</p>".join(page['body_html'].split("</p>", 3)[:3])
page['body_html_cut'] = body_html_cut
page['cut'] = True
# --> more than one image
#elif ...
pages.append(page)
#date_sets = gen_changelog(change_rows)
date_sets = []
last_date = ""
for page in pages_rows:
curr_date = page['date_norm']
if curr_date != last_date:
date_set = { 'date': curr_date,
'pages': [] }
date_sets.append(date_set)
last_date = curr_date
# add the changes to the sets
for date_set in date_sets:
for page in pages:
if page['date_norm'] == date_set['date']:
date_set['pages'].append(page)
return date_sets, hrefs