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