chotha.py

import datetime, apsw, markdown, bottle, re, logging, pubmed, ConfigParser
from bottle import route, debug, template, request

#Config file
config = ConfigParser.RawConfigParser()

#database name
dbname = None

#set up logging
logger = logging.getLogger('chotha')
logger.setLevel(logging.DEBUG)
ch = logging.StreamHandler()
logger.addHandler(ch)

# Database operations ---------------------------------------------------------
def dbq(query, bindings=None, many=False, conn=None):
  """Utility function to handle db queries. Based on query type, the function
  returns last rowid or rows (which is a list of dictionaries)"""
  if not bindings: bindings = []

  #Get the first word of the query to figure out what we should return
  cmd = query.split(' ',1)[0].upper()
  
  if conn is None:
    conn = apsw.Connection(dbname)
  c = conn.cursor()
  if many:
    c.executemany(query, bindings)
  else:
    c.execute(query, bindings)
  if cmd == 'SELECT':
    try:
      col_names = c.getdescription()
    except:
      pass #Just means returned no rows
    rows_in = c.fetchall()
    rows_out = []
    for row_in in rows_in:
      row_out = {}
      for c in range(len(col_names)):
        row_out[col_names[c][0]] = row_in[c]
      rows_out.append(row_out)
    return rows_out
  if cmd == 'INSERT':
    return conn.last_insert_rowid()

def get_source_fields(include_column_type = False):
  """Need an ordered list that starts with id. INSERT and UPDATE operations
  require us to treat the id differently compared to regular fields."""
  source_fields = [
  ['id','INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL'],
  ['abstract', 'TEXT'],
  ['address','VARCHAR(255)'],
  ['author','VARCHAR(255)'],
  ['booktitle','VARCHAR(255)'],
  ['chapter','VARCHAR(255)'],
  ['citekey','VARCHAR(255)'],
  ['doi','VARCHAR(255)'],
  ['edition','VARCHAR(255)'],
  ['editor','VARCHAR(255)'],
  ['howpublished','VARCHAR(255)'],
  ['institution','VARCHAR(255)'],
  ['journal','VARCHAR(255)'],
  ['month','VARCHAR(255)'],
  ['number','VARCHAR(255)'],
  ['organization','VARCHAR(255)'],
  ['pages','VARCHAR(255)'],
  ['publisher','VARCHAR(255)'],
  ['school','VARCHAR(255)'],
  ['series','VARCHAR(255)'],
  ['title','VARCHAR(255)'],
  ['source_type','VARCHAR(255)'],
  ['volume','VARCHAR(255)'],
  ['year','INTEGER']
  ]
  if include_column_type:
    return source_fields
  else:
    return [source_fields[n][0] for n in range(len(source_fields))]

def get_empty_source():
  """Return us a dummy source dictionary with the fields filled out with NULLS."""
  sfs = get_source_fields()
  source = {}
  for f in sfs:
    source[f] = '' 
  return source

@route('/init')
def create_database():
  """Creates a new empty database."""
  #Always start with id  
  dbq('CREATE TABLE "notes" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "title" VARCHAR(255) DEFAULT "A title", "date" DATETIME, "body" TEXT DEFAULT "A body", "key_list" VARCHAR(255) DEFAULT "", "source_id" INTEGER)')
  source_fields = get_source_fields(include_column_type=True)
  query = 'CREATE TABLE "sources" ('
  query += '"%s" %s' %(source_fields[0][0], source_fields[0][1]) 
  for n in range(1,len(source_fields)):
    query += ', "%s" %s' %(source_fields[n][0], source_fields[n][1])
  query += ')'
  dbq(query)
  dbq('CREATE TABLE "keywords" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" VARCHAR(255))')  
  dbq('CREATE TABLE "keywords_notes" ("keyword_id" INTEGER, "note_id" INTEGER, PRIMARY KEY (keyword_id, note_id))')
  trigger = \
  """CREATE TRIGGER notesource1 
  AFTER UPDATE OF title ON sources 
  BEGIN
    UPDATE notes SET title = new.title || ' (' || new.citekey || ')' WHERE source_id = new.id;
  END"""
  dbq(trigger)
  trigger = \
  """CREATE TRIGGER notesource2 
  AFTER UPDATE OF citekey ON sources 
  BEGIN
    UPDATE notes SET title = new.title || ' (' || new.citekey || ')' WHERE source_id = new.id;
  END"""
  dbq(trigger)
  dbq('CREATE TABLE "wordcloud" ("word" TEXT PRIMARY KEY, "count" INTEGER)')

#Keyword ops -------------------------------------------------------------------
def cskeystring_to_list(cskeystring):
  keyword_strings = cskeystring.split(',')
  keywords = []
  for name in keyword_strings:
    name = name.strip()
    if name == '':#Ignore blanks
      continue
    keywords.append(name)
  return keywords

def find_or_create_keywords(keywords):
  """Find or create keywords in a list and return the keyword id."""
  kids = []
  for keyword in keywords:
    name = keyword.strip()
    row = dbq('SELECT id FROM keywords WHERE name LIKE ?', (name,))
    if not len(row):
      id = dbq('INSERT INTO keywords (name) VALUES (?)', (name,))
    else:
      id = row[0]['id']
    kids.append(id)
  return kids

def save_note_keywords(note):
  """Keywords are a comma separated list, we first extract that."""

  note_id = note['id']    
  #Delete original list
  dbq('DELETE FROM keywords_notes WHERE note_id=?', (note_id,))
  #Create new list
  keywords = cskeystring_to_list(note['key_list'])
  new_kids = find_or_create_keywords(keywords)
  if len(new_kids) > 0:
    bindings = []
    for kid in new_kids:
      bindings.append([kid, note_id])
    dbq('INSERT INTO keywords_notes (keyword_id,note_id) VALUES (?,?)', 
        bindings, many=True)
  #Clean up unused keywords
  dbq('DELETE FROM keywords WHERE id NOT IN (SELECT keyword_id FROM keywords_notes)')
  

#TODO fix to remove references to sources
def fetch_conjunction_candidates(keywords=None):
  """Given the keyword, fetch a list of keywords that appear in conjunction
  with it in the database in notes and sources.
  
  Important SQL snippets:
  -> This allows us to do keyword intersection (also see below)
  SELECT kn.note_id FROM keywords_notes AS kn 
    WHERE kn.keyword_id IN 
    (SELECT k.id FROM keywords k WHERE %s) <-put in the keyword clause here
    GROUP BY kn.note_id HAVING COUNT(*) = %d
  
  -> return all the keywords attached to these notes
  SELECT DISTINCT kn.keyword_id FROM keywords_notes kn WHERE kn.note_id IN ...
  
  -> final SQL (to incorporate both notes and sources)
  SELECT k.name FROM keywords k WHERE k.id IN
   (SELECT DISTINCT kn.keyword_id FROM keywords k, keywords_notes kn WHERE kn.note_id IN
     (SELECT kn.note_id FROM keywords_notes AS kn WHERE kn.keyword_id IN 
       (SELECT k.id FROM keywords k WHERE ?) <-put in the keyword clause here
        GROUP BY kn.note_id HAVING COUNT(*) = ?) <- no of keywords
   UNION
   SELECT DISTINCT ks.keyword_id FROM keywords k, keywords_sources ks WHERE ks.source_id IN
     (SELECT ks.source_id FROM keywords_sources AS ks WHERE ks.keyword_id IN 
       (SELECT k.id FROM keywords k WHERE ?) <-put in the keyword clause here
        GROUP BY ks.source_id HAVING COUNT(*) = ?)) <- no of keywords   
  AND k.id NOT IN (SELECT k.id FROM keywords k WHERE ?) <-put in the keyword clause here
      
  """
  if not keywords: keywords = []
  arg_list = []
  if len(keywords) > 0:
    kq = ' k.name=? ' 
    for n in range(1,len(keywords)):
      kq += ' OR k.name=? ' 
    
    query = \
    'SELECT k.name FROM keywords k WHERE k.id IN \
      (SELECT DISTINCT kn.keyword_id FROM keywords k, keywords_notes kn WHERE kn.note_id IN \
        (SELECT kn.note_id FROM keywords_notes AS kn WHERE kn.keyword_id IN \
         (SELECT k.id FROM keywords k WHERE %s) \
         GROUP BY kn.note_id HAVING COUNT(*) = ?)) \
     AND k.id NOT IN (SELECT k.id FROM keywords k WHERE %s) ORDER BY k.name ASC' %(kq,kq)
    arg_list = keywords + [len(keywords)] + keywords
  else:
    query = 'SELECT k.name FROM keywords k ORDER BY k.name ASC'
  return dbq(query, arg_list)

def update_word_cloud(oldtext, newtext):
  """Pass in a list of notes. This will either create new entries or increment
  existing ones by 1 depending on whether the entry exists."""
  import wordcloud
  wordcloud.update_word_cloud(oldtext, newtext, dbname)

# Note ops ---------------------------------------------------------------------
def create_new_note(note):
  note['date'] = datetime.datetime.now().isoformat()
  fields = note.keys()
  query = 'INSERT INTO notes ('
  query += fields[0]
  for n in range(1,len(fields)):
    query += ', ' + fields[n]
  query += ') VALUES (?'
  for n in range(1,len(fields)):
    query += ',?'
  query += ')'
  bindings = []
  for n in range(len(fields)):
    bindings.append(note[fields[n]])
  note['id'] = dbq(query, bindings)  
  save_note_keywords(note)
  update_word_cloud('', note['body'])
  return note

def save_note(note):
  """We fetch the old note before saving the new note so we can update the
  wordcloud."""
  old_note = fetch_single_note(note['id'])
  dbq("UPDATE notes SET date = ?, title = ?, body = ?, key_list = ? WHERE id LIKE ?", 
      (note['date'], note['title'], note['body'], note['key_list'], note['id']))
  save_note_keywords(note)
  update_word_cloud(old_note['body'], note['body'])

#TODO handle flag for sources
def parse_notes(rows_in):
  """Given a list of row objects returned by a fetch, copy the data into a new
  dictionary after running each entry through the markdown parser."""

#  md = markdown.markdown #To save time
  md = markdown.Markdown(extensions=['chothamathml']).convert
  pnote = re.compile(r'\[(.+?)\]\[note:(\d+?)\]')#For the wiki links substitution.
  psource = re.compile(r'\[source:(.+?)\]')#For the wiki links substitution.
  
  def nice_date(date):
    nd = datetime.date(int(date[0:4]),int(date[5:7]),int(date[8:10]))
    return nd.strftime('%a %b %d, %Y')
  
  def format_body(body):
    def nlinx(match):
      return '<a href="/note/%s">%s</a>' %(match.group(2),match.group(1))

    def slinx(match):
      return '<a href="/sourcecitekey/%s">%s</a>' %(match.group(1),match.group(1))
          
    text = pnote.sub(nlinx, body)
    text = psource.sub(slinx, text)
    return md(text)
  
  rows = []
  for this_row in rows_in:
    new_row = dict(this_row)
    new_row['nicedate'] = nice_date(this_row['date'])
    new_row['html'] = format_body(this_row['body'])
    rows.append(new_row)
  return rows

def extract_sources_from_note(note):
  def sources_not_found(req_scks, rows):
    fnd_scks = [row['citekey'] for row in rows]
    miss_scks = set(req_scks) - set(fnd_scks)
    return miss_scks
    
  psource = re.compile(r'\[source:(.+?)\]')
  scks = psource.findall(note['body'])
  scks_string = ', '.join('?' for dummy in scks)
  rows = dbq('SELECT * FROM sources WHERE citekey IN (%s) ORDER BY citekey' %scks_string, scks)
  miss_scks = sources_not_found(scks, rows)
  return rows, miss_scks

def fetch_single_note(id):
  rows = dbq('SELECT * FROM notes WHERE id LIKE ?', (id,))
  if len(rows) > 0: 
    return parse_notes(rows)[0]
  else:
    return None

def fetch_single_source(id):
  rows = dbq('SELECT sources.*,notes.id AS nid FROM sources,notes WHERE sources.id LIKE ? AND notes.source_id = sources.id', (id,))
  if len(rows) > 0: 
    return rows[0]
  else:
    return None

def fetch_single_source_by_citekey(citekey):
  rows = dbq('SELECT sources.*,notes.id AS nid FROM sources,notes WHERE sources.citekey LIKE ? AND notes.source_id = sources.id', (unicode(citekey,'utf-8'),))
  if len(rows) > 0: 
    return rows[0]
  else:
    return None

# Search ops -------------------------------------------------------------------
def fetch_notes_by_criteria(keywords=None, search_text='', limit=10, offset=0):
  """Returns note summary via keyword intersection and search. If either is None,
  they are ignored. If both are None all notes are returned
  
  Important SQL snippets:
  -> This allows us to select keywords as a comma separated list
  SELECT notes.*, group_concat(keywords.name) AS kwds FROM notes 
    INNER JOIN keywords_notes ON keywords_notes.note_id = notes.id 
    INNER JOIN keywords on keywords_notes.keyword_id = keywords.id 
    WHERE notes.id IN (.....) GROUP BY notes.id;
  
  -> This allows us to do keyword intersection
  SELECT notes.* FROM notes WHERE id IN
    (SELECT kn.note_id FROM keywords_notes AS kn 
    WHERE kn.keyword_id IN 
    (SELECT k.id FROM keywords k WHERE %s) <-put in the keyword clause here
    GROUP BY kn.note_id HAVING COUNT(*) = %d)
    
  --> The combined SQL is
  SELECT notes.*, group_concat(keywords.name) AS kwds FROM notes 
    INNER JOIN keywords_notes ON keywords_notes.note_id = notes.id 
    INNER JOIN keywords on keywords_notes.keyword_id = keywords.id 
    WHERE notes.id IN 
      (SELECT kn.note_id FROM keywords_notes AS kn 
       WHERE kn.keyword_id IN 
      (SELECT k.id FROM keywords k WHERE %s) <-put in the keyword clause here
       GROUP BY kn.note_id HAVING COUNT(*) = %d) 
    GROUP BY notes.id;
    
    
  """
  if not keywords: keywords = []
  query = 'SELECT id FROM notes '
  arg_list = []
  
  if search_text.startswith(':s '):
    """This lets us perform arbitrary SQL queries on our db for sources.
    Activate it by typing ":s <where clause>" in the search box. The
    where clause should omit the WHERE keyword. This is designed as a desktop
    app used by the owner, so we trust him not to sabotage himself by doing
    strange things."""
    where_clause = search_text[2:]
    query = "SELECT id FROM notes WHERE source_id IN (SELECT id FROM sources WHERE %s)" %where_clause
  else:
    #Do a regular search
    if search_text != '':
      query += ' WHERE (notes.title LIKE ? OR notes.body LIKE ?) '
      arg_list += ["%%%s%%" %search_text]
      arg_list += ["%%%s%%" %search_text]
       
    if len(keywords) > 0:
      if search_text != '':
        query += ' AND '
      else:
        query += ' WHERE '
      key_query = ' k.name=? '
      arg_list += [keywords[0]]
      for n in range(1,len(keywords)):
        key_query += ' OR k.name=? '
        arg_list += [keywords[n]]
      query += \
      """ notes.id IN
      (SELECT kn.note_id FROM keywords_notes AS kn 
      WHERE kn.keyword_id IN 
      (SELECT k.id FROM keywords k WHERE %s) 
      GROUP BY kn.note_id HAVING COUNT(*) = %d)""" %(key_query,len(keywords))
    query += ' GROUP BY notes.id'

  query += ' ORDER BY notes.date DESC'

  rows = dbq(query, arg_list)
  lrows = len(rows)
  
  query_id_list = ''
  n = offset
  while lrows > n < offset+limit:
    query_id_list += str(rows[n]['id']) + ','
    n += 1
  query_id_list = query_id_list.rstrip(',')
  query = 'SELECT * FROM notes WHERE id IN (' + query_id_list + ') ORDER BY date DESC'
  return parse_notes(dbq(query)), lrows

def populate_new_source_from_pubmed_query(query):
  """Given a query fetch the first matching citation from pubmed."""
  source = get_empty_source()
  if query != '':
    xml = pubmed.citation_from_query(query)
    if xml is not None:
      source = pubmed.parse_pubmed_xml_to_source(xml, source = source)
    else:
      source['title'] = query
  return source

def generate_citekey(source):
  """Generates a non duplicate citekey using first author last name and year"""
  au_text = source['author']
  last_name = ''
  if au_text != '':
    au_line = au_text.split("\n")
    if au_line[0] != '':
      name_frags = au_line[0].split(',')
      if name_frags[0] != '':
        last_name = name_frags[0]
        last_name = last_name.replace("'","_") #Apostrophe is an illegal citekey character.

  citekey = 'source' + source['id'] #cite key for sources with no authors
  if last_name != '':
    base = last_name.lower() + source['year']
    succ = 1
    citekey = base
    id = source['id']
    query = "SELECT COUNT(*) FROM sources WHERE citekey=? AND id <> ?"
    row = dbq(query, (citekey,id))
    while row[0]['COUNT(*)']:
      citekey = base + '_%d' %succ
      succ += 1
      print citekey,row[0].keys()
      row = dbq(query, (citekey,id))
      
  return citekey

def create_new_source(source):
  source['citekey'] = generate_citekey(source)
  fields = get_source_fields()
  query = 'INSERT INTO sources ('
  query += fields[1] #ignore id
  for n in range(2,len(fields)):
    query += ', ' + fields[n]
  query += ') VALUES (?'
  for n in range(2,len(fields)):
    query += ',?'
  query += ')'
  bindings = []
  for n in range(1,len(fields)):
    bindings.append(source[fields[n]])
  source['id'] = dbq(query, bindings)
  return source

def save_source(source):
  if source['citekey'] == '':
    source['citekey'] = generate_citekey(source)
  fields = get_source_fields()
  query = 'UPDATE sources SET '
  query += fields[1] + '=?'
  for n in range(2,len(fields)):
    query += ',' + fields[n] + '=?'
  query += ' WHERE id LIKE ?'
  bindings = []
  for n in range(1,len(fields)):
    bindings.append(source[fields[n]])
  bindings.append(source['id'])
  dbq(query, bindings)

# Utility functions ------------------------------------------------------------
def wtemplate(tmplt,**kwargs):
  """Needed a wrapper to the template call to include common options etc."""
  #Some defaults to make life easier
  defaults = {
  'total_found': 1, 
  'cskeyword_list': '',
  'candidate_keywords': [],
  'search_text': '',
  'limit': 10,
  'offset': 0,
  'title': 'Chotha',
  'rows': [None] #Dummy, causes display to say one thing is found
  }
  for key in defaults.keys():
    if not kwargs.has_key(key):
      kwargs[key] = defaults[key]   
  kwargs['desktop_cskeyword_list'] = config.get('User','desktop')
  return template(tmplt,**kwargs)

# Common use pages -------------------------------------------------------------

@route('/')  
def index_page():
  """Main page served by chotha. If called by itself it pulls out all the notes
  and papers in reverse time order, showing the  ."""
  search_text = request.GET.get('search_text', '')
  cskeyword_list = request.GET.get('cskeyword_list', '')
  offset = int(request.GET.get('offset',0))
  limit = int(request.GET.get('limit',10))

  current_keywords = cskeystring_to_list(cskeyword_list)
  rows, Nrows = fetch_notes_by_criteria(keywords = current_keywords, 
                                 search_text = search_text, 
                                 offset=offset, limit=limit)
  #Nrows is the total number of results
  candidate_keywords = fetch_conjunction_candidates(current_keywords)
  
  title = ''
  if search_text != '':
    title += '"' + search_text + '"'
  if cskeyword_list != '':
    if search_text != '':
      title += '+'
    title += cskeyword_list
  if title == '':
    title = 'Home'
  output = wtemplate('index', rows=rows, candidate_keywords=candidate_keywords,
                    total_found = Nrows, 
                    cskeyword_list = cskeyword_list,
                    search_text = search_text,
                    limit = limit,
                    offset = offset,
                    title = title,
                    view='list')
  return output

@route('/new')
def new_page():
  """Returns page with slot for new entry."""
  cskeyword_list = request.GET.get('cskeyword_list', '')
  output = wtemplate('index', cskeyword_list = cskeyword_list,
                    title = "New entry",
                    view='new')
  return output
  
@route('/note/:id')
def show_note_page(id):
  note = fetch_single_note(id)
  title = note['title']
  output = wtemplate('index', note=note, 
                    title=title,
                    view='note')
  return output

@route('/notesourceexport/:id')
def export_sources_from_note(id):
  """We're trying out something fancy. In the note itself we set the file name
  and the mode in the first and second line."""  
  def get_filename_and_mode(note):
    lines = note['body'].splitlines()
    fname = mode = None
    for line in lines:
      if line.startswith('File:'):
        fname = line[5:].strip()
        if mode is not None:
          break
      if line.startswith('Mode:'):
        mode = line[5:].strip()
        if fname is not None:
          break
      
    if fname is None:
      fname = 'chotha.ris'
    if mode is None:
      mode = 'ris'
    
    return fname, mode
    
  #fname = config.get('User','msword_source_fname')
  note = fetch_single_note(id)
  fname, mode = get_filename_and_mode(note)
  #fname = '/Users/kghose/Research/2011/Papers/SpatialIntegration/v01/Supplementary/supp.bib'
  #mode = 'bibtex'
  sources, missing_citekeys = extract_sources_from_note(note)
  import citation_export as ce
  if mode =='word':
    refcount = ce.export_MSWord_XML(fname, sources)
  elif mode =='bibtex':
    refcount = ce.export_BibTeX(fname, sources)
  elif mode =='ris':
    refcount = ce.export_RIS(fname, sources)    

  message = """<p>Wrote <b>%d</b> of <b>%d</b> valid sources present in this 
  note to <b>%s</b> as <b>%s</b>.</p>""" %(refcount, len(sources), fname, mode)
  if len(missing_citekeys) > 0:
    message += """<p>The following sources were requested but not found in the 
    database: """
    for ck in missing_citekeys:
      message += "<b>%s</b> " % ck

  output = wtemplate('index', note=note, 
                    title='Exported sources from note',
                    message = message,
                    view='note')
  
  return output

@route('/source/:id')
def show_source_page(id):

  source = fetch_single_source(id)
  output = wtemplate('index', source=source,
                    title='%s' %source['citekey'], 
                    view='source')
  return output

@route('/sourcecitekey/:citekey')
def show_source_page_citekey(citekey):

  source = fetch_single_source_by_citekey(citekey)
  if source is not None:
    title = '%s' %source['citekey']
    output = wtemplate('index', source=source,
                      title=title, view='source')

  else:
    ntitle = citekey[:-4] + ' ' + citekey[-4:]
    output = wtemplate('index',
                  title = "New entry",
                  ntitle = ntitle,  #sets up a title for the note
                  ispaper = 'CHECKED', #Checks the "this is a paper" checkbox
                  view='new',
                  message='No such source in database. Create?')
    
  return output


#We use POST for creating/editing the entries because these operations have 
#lasting observable effects on the state of the world
#
@route('/new', method='POST')
def create_note_action():

  title = unicode(request.POST.get('title', '').strip(),'utf_8')
  body = unicode(request.POST.get('body', '').strip(),'utf_8')
  key_list = unicode(request.POST.get('key_list', '').strip(),'utf_8')
  ispaper = request.POST.get('ispaper', 'no')
  note = {'title': title, 'body': body, 'key_list': key_list}
  if ispaper=='yes':
    #Now, we should create the sidecar citation object and open for editing
    #The title is interpreted as a search string, and the first hit is 
    #returned. It is best to enter the doi or pmid
    #If blank populate will just present us with a blank source forms for us 
    #to fill out
    query = title
    source = populate_new_source_from_pubmed_query(query)
    source = create_new_source(source)#Now we have the id
    note['source_id'] = source['id']
    
  note = create_new_note(note)

  if ispaper=='yes':
    #We get a chance to see and edit the citation component
    return edit_source(id=source['id'])
  else:
    request.GET.append('cskeyword_list',key_list)
    return index_page()

@route('/edit/:id')
def edit_note(id=None):
  
  note = fetch_single_note(id)
  output = wtemplate('index', note=note, 
                    title='Editing %s' %note['title'], view='edit')
  return output

@route('/editsource/:id')
def edit_source(id=None):
  
  source = dbq('SELECT sources.*,notes.id AS nid FROM sources,notes WHERE sources.id LIKE ? AND notes.source_id = sources.id', (id,))[0]
  output = wtemplate('index', source=source,
                    title='Editing %s' %source['citekey'], view='editsource')
  return output
  
@route('/save/:id', method='POST')
def save_note_action(id=None):

  date = request.POST.get('date', '').strip()
  title = unicode(request.POST.get('title', '').strip(),'utf_8')
  body = unicode(request.POST.get('body', '').strip(),'utf_8')
  key_list = unicode(request.POST.get('key_list', '').strip(),'utf_8')  
  note = {'id': int(id), 'date': date, 'title': title, 'body': body, 'key_list': key_list}
  save_note(note)
  note = fetch_single_note(note['id'])
  output = wtemplate('index', note=note,
                    title='Saved %s' %note['title'], view='note')  
  return output

@route('/savesource', method='POST')
def save_source_action():
  fields = get_source_fields()
  source = get_empty_source()
  for f in fields:
    val = request.POST.get(f, None)
    if val is not None:
      source[f] = unicode(val.strip(),'utf_8')
  save_source(source)
  source = fetch_single_source(source['id']) #We need the note id
  output = wtemplate('index', source=source,
                    title='Saved %s' %source['citekey'], view='source')  
  return output

@route('/refetchsource', method='POST')
def refetch_source_action():
  query = unicode(request.POST.get('query', '').strip(),'utf_8')
  id = request.POST.get('id','').strip()
  source = populate_new_source_from_pubmed_query(query)
  source['id'] = id #Need if before we can generate citekey  
  source['citekey'] = generate_citekey(source)
  output = wtemplate('index', source=source,
                    title='Editing %s' %source['citekey'], view='editsource')
  return output

@route('/wordcloud')
def wordcloud_page():
  wordcloud = dbq('SELECT word, count FROM wordcloud ORDER BY word ASC')
  output = wtemplate('templates/wordcloud', wordcloud=wordcloud,
                    title='wordcloud', view='wordcloud')
  return output

@route('/keyword_frequency')
def keyword_frequency_page():
  keyword_freq = dbq('SELECT k.name, count(kn.rowid),  kn.keyword_id FROM keywords as k, keywords_notes as kn WHERE k.id = kn.keyword_id GROUP BY kn.keyword_id ORDER BY count(kn.rowid) DESC')
  #keyword_freq = dbq('SELECT k.name, count(kn.rowid),  kn.keyword_id FROM keywords as k, keywords_notes as kn WHERE k.id = kn.keyword_id GROUP BY kn.keyword_id ORDER BY k.name')
  output = wtemplate('templates/keyword_frequency', keyf=keyword_freq,
    title='Keyword frequency', view='')
  return output


@route('/rebuildwordcloud')
def rebuild_wordcloud_page():
  """May take a VERY long time."""
  import wordcloud
  notes = dbq('SELECT body FROM notes')
  wordcloud.rebuild_wordcloud(notes, dbname)
  return wordcloud_page()
  

# Configuration helpers --------------------------------------------------------
def create_default_config_file():
  config.add_section('Basic')
  config.set('Basic', 'dbname', 'rriki_example.sqlite3')
  config.set('Basic', 'host', 'localhost')
  config.set('Basic', 'port', '3020')
  
  config.add_section('Advanced')  
  config.set('Advanced', 'debug', 'True')
  config.set('Advanced', 'reloader', 'True')
  
  config.add_section('User')
  config.set('User','desktop','')
  config.set('User','msword_source_fname','')
  
  with open('chotha.cfg', 'wb') as configfile:
    config.write(configfile)  

def load_config():
  result = config.read('chotha.cfg')
  if not len(result):
    create_default_config_file()

def save_config():
  with open('chotha.cfg', 'wb') as configfile:
    config.write(configfile)  
  

# Configuration pages ---------------------------------------------------------  
@route('/selectdb', method='POST')
def select_database():
  newdbname = request.POST.get('newdbname', '').strip()
  import os
  if os.path.exists(newdbname):
    logger.info('Found db %s. Switching' %newdbname)    
    globals()['dbname']=newdbname
    config.set('Basic', 'dbname', newdbname)
    save_config()
    return index_page()
  else:
    logger.info('Did not find db %s. Creating new' %newdbname)        
    return new_database()

@route('/createdb', method='POST')
def new_database():
  newdbname = request.POST.get('newdbname', '').strip()
  logger.info('Creating %s' %newdbname)  
  globals()['dbname']=newdbname
  create_database()
  config.set('Basic', 'dbname', newdbname)
  save_config()
  return index_page()

@route('/options/setdesktop/')
def set_desktop():
  """Set the current keyword conjunction list as the desktop. Passed in via GET."""
  cskeyword_list = request.GET.get('cskeyword_list', '')
  config.set('User', 'desktop', cskeyword_list)
  save_config()
  return index_page()

@route('/config')
def show_config_page():
  """Show a page with some configuration data and some simple stats."""
  dbinfo = {
    'note count': dbq("SELECT COUNT(id) FROM NOTES WHERE source_id IS NULL")[0][
                  "COUNT(id)"], 'source count':
      dbq("SELECT COUNT(id) FROM NOTES WHERE source_id IS NOT NULL")[0][
      "COUNT(id)"], 'sqlite version': apsw.sqlitelibversion()}
  from sys import version
  dbinfo['python version'] = version
  
  cfg = {'dbinfo': dbinfo,
         'cfg file': config}
  return template('templates/config', cfg=cfg)
  
# File stuff -------------------------------------------------------------------
@bottle.route('/static/:filename#.*#')
def static_file(filename):
  return bottle.static_file(filename, root='./static')


# For testing only -------------------------------------------------------------
@route('/createtestdb/:newdbname')
def new_testing_database(newdbname='chotha_test.sqlite3'):
  globals()['dbname']=newdbname
  create_database()
  #populate_database_with_test_data()
  config.set('Basic', 'dbname', newdbname)
  save_config()
  return index_page()

def populate_database_with_test_data():
  """Populate the tables with some deterministic data"""  
  query = """INSERT INTO notes (title,date) VALUES ('Michaela','2010-12-31');
  INSERT INTO sources (title,citekey) VALUES ('A paper','crusty1956');  
  INSERT INTO notes (title,date,source_id) VALUES ('A paper','2010-12-31',1);"""
  dbq(query)
  
  
if __name__ == "__main__":
  load_config()
  deb = config.getboolean('Advanced', 'debug')
  host = config.get('Basic','host')
  port = config.getint('Basic', 'port')
  relo = config.getboolean('Advanced', 'reloader')
  globals()['dbname'] = config.get('Basic','dbname')
  debug(deb)
  bottle.run(host=host, port=port,reloader=relo)
  #bottle.run(host='localhost', port=8080)