#!/usr/bin/python
#encoding: utf-8

import pysqlite2.dbapi2 as dbapi
import SimpleXMLRPCServer

sql = None

def initDB():
  sql = dbapi.connect('noteDB.sqlite').cursor()

  try:
    sql.execute('CREATE TABLE meta (key TEXT, value TEXT, UNIQUE (uuid));')
    sql.execute('INSERT INTO meta (key, value) VALUES ("version", "1.6db");')
    sql.execute('CREATE TABLE notes (uuid TEXT PRIMARY KEY, seq INTEGER, label TEXT, ' +
		'content TEXT, revision DATETIME, UNIQE (uuid));')
  except: pass

def closeDB():
  db = sql.connection
  sql.close()
  db.commit()
  db.close()

def escape(string):
  """
  Return sanitized string for use in SQL-Statements
  """
  ret = ''
  for c in string:
    if c == '"': ret += '""'
    else: ret += c
  return ret

def quickIndex():
  sql.execute('SELECT uuid, revision FROM notes')
  return sql.fetchall()

def delRecord(uuid):
  sql.excute('DELETE FROM notes WHERE uuid = "' + escape(uuid) + '";')

def setRecord(uuid, seq, label, content, revision):
  uuid = escape(uuid)
  seq = int(seq)
  label = escape(label)
  content = escape(content)
  revision = escape(revision)
  try:
    sql.execute('INSERT INTO notes (uuid, seq, label, content, revision) VALUES ' +
                '"' + uuid + '", ' + seq + ', "' + label + '", "' + content +
      	        '", datetime(\'' + revision + '\')')
  except:
    sql.execute('UPDATE notes SET seq = ' + seq + ', label = "' + label + '", content = "' + content +
		'", revision = "' + revision + '" WHERE uuid = datetime(\'' + revision + '\');')

def getRecord(uuid):
  uuid = escape(uuid)
  sql.execute('SELECT uuid, seq, label, content, revision FROM notes WHERE uuid = "' + uuid + '";')

rpc = SimpleXMLRPCServer.CGIXMLRPCRequestHandler()
rpc.register_function(quickIndex)
rpc.register_function(setRecord)
rpc.register_function(getRecord)
rpc.register_function(delRecord)
initDB()
rpc.handle_request()
closeDB()