import os.path
import sqlite3
from functools import partial
import sys
import json

sys.path.insert(0, os.path.dirname(__file__) + '/../../src')

from app import Application

DB_FILE = os.path.abspath(
    os.path.join(os.path.dirname(__file__), 'todo.sqlite'))
db_connect = partial(sqlite3.connect, DB_FILE)


def maybe_create_schema():
    db = db_connect()
    db.execute("""
        CREATE TABLE IF NOT EXISTS todos
        (id INTEGER PRIMARY KEY, todo TEXT)""")
    db.close()


def add_todo(request):
    db = db_connect()
    cur = db.cursor()
    todo = request.json["todo"]
    cur.execute("""INSERT INTO todos (todo) VALUES (?)""", (todo,))
    last_id = cur.lastrowid
    db.commit()
    db.close()

    return request.Response(json={"id": last_id, "todo": todo})


def list_todos(request):
    db = db_connect()
    cur = db.cursor()
    cur.execute("""SELECT id, todo FROM todos""")
    todos = [{"id": id, "todo": todo} for id, todo in cur]
    db.close()

    return request.Response(json={"results": todos})


def show_todo(request):
    db = db_connect()
    cur = db.cursor()
    id = int(request.match_dict['id'])
    cur.execute("""SELECT id, todo FROM todos WHERE id = ?""", (id,))
    todo = cur.fetchone()
    if not todo:
        return request.Response(404, json={})
    todo = {"id": todo[0], "todo": todo[1]}
    db.close()

    return request.Response(json=todo)


def delete_todo(request):
    db = db_connect()
    cur = db.cursor()
    id = int(request.match_dict['id'])
    cur.execute("""DELETE FROM todos WHERE id = ?""", (id,))
    if not cur.rowcount:
        return request.Response(404, json={})
    db.commit()
    db.close()

    return request.Response(json={})


if __name__ == '__main__':
    maybe_create_schema()
    app = Application()
    router = app.router
    router.add_route('/todos', list_todos, method='GET')
    router.add_route('/todos/{id}', show_todo, method='GET')
    router.add_route('/todos/{id}', delete_todo, method='DELETE')
    router.add_route('/todos', add_todo, method='POST')
    app.serve()

@@ -486,48 +486,282 @@
 def 
-add_todo(request):%0A    db = db_connect()
+cursor(request):%0A    def done_cb(request):%0A        request.extra%5B'conn'%5D.close()%0A%0A    if not 'conn' in request.extra:%0A        request.extra%5B'conn'%5D = db_connect()%0A        request.add_done_callback(done_cb)%0A%0A    return request.extra%5B'conn'%5D.cursor()%0A%0A%0A%0Adef add_todo(request):
 %0A   
@@ -763,35 +763,38 @@
 :%0A    cur = 
-db
+request
 .cursor
-()
 %0A    todo = 
@@ -918,32 +918,29 @@
     
-db.commit()%0A    db.close
+cur.connection.commit
 ()%0A%0A
@@ -1030,38 +1030,16 @@
 quest):%0A
-    db = db_connect()%0A
     cur 
@@ -1036,35 +1036,38 @@
 :%0A    cur = 
-db
+request
 .cursor
-()
 %0A    cur.exe
@@ -1163,31 +1163,16 @@
  in cur%5D
-%0A    db.close()
 %0A%0A    re
@@ -1244,38 +1244,16 @@
 quest):%0A
-    db = db_connect()%0A
     cur 
@@ -1250,35 +1250,38 @@
 :%0A    cur = 
-db
+request
 .cursor
-()
 %0A    id = in
@@ -1445,32 +1445,44 @@
 equest.Response(
+status_code=
 404, json=%7B%7D)%0A  
@@ -1522,31 +1522,16 @@
 todo%5B1%5D%7D
-%0A    db.close()
 %0A%0A    re
@@ -1591,38 +1591,16 @@
 quest):%0A
-    db = db_connect()%0A
     cur 
@@ -1605,19 +1605,22 @@
 r = 
-db
+request
 .cursor
-()
 %0A   
@@ -1773,16 +1773,28 @@
 esponse(
+status_code=
 404, jso
@@ -1807,32 +1807,29 @@
     
-db.commit()%0A    db.close
+cur.connection.commit
 ()%0A%0A
@@ -1920,16 +1920,16 @@
 chema()%0A
-
     app 
@@ -1944,16 +1944,62 @@
 ation()%0A
+    app.extend_request(cursor, property=True)%0A
     rout

update todo api to use property, extra and add_done_callback