require("sqlite")

print("sqlite version:  "..sqlite.version())
print("sqlite encoding: "..sqlite.encoding())

local text = 'lalalwejwoeifj\n\r\000\000232323'

assert(sqlite.decode_binary(sqlite.encode_binary(text)) == text)

local dbname = "test.db"
local db, msg, db2

local function cleanup()
    if (db) then
        if (db:isopen()) then
            db:close()
        end
        db = nil
        os.remove(dbname)
    end
end

local function lassert(test, msg)
    if (not test) then
        cleanup()
        error(msg, 2)
    end
end

local tick
do
    local t = 0
    function tick(show)
        if (show) then
            print('Time: '..(os.clock() - t))
        end
        t = os.clock()
    end
end

---
lassert(sqlite.complete("CREATE cn ") == false, "incomplete sql statement")
lassert(sqlite.complete("CREATE ") == false, "incomplete sql statement")
lassert(sqlite.complete("CREATE ") == false, "incomplete sql statement")
lassert(sqlite.complete("CREATE ") == false, "incomplete sql statement")
---

local function busy_handler(user, tries, name)
    print("sleeping... : "..user)
    --posix.sleep(1)
    return true
end

local function progress_handler(user)
    io.write(user)
    return false
end

local check = {}

local function sum_step(user, ctx, count, arg)
    assert(check == user)
    ctx.value = (ctx.value or 0) + (arg or 0)
end

local function sum(user, ctx, count)
    assert(check == user)
    if ctx then
        return ctx.value
    end
    return 0
end

local function dosql(ret, sql, cb, user)
    local r, msg = db:exec(sql, cb, user)
    lassert(r == ret, "failed to execute sql: "..sql.." - "..(msg or ""))
end

db, msg = sqlite.open(dbname) lassert(db, msg)
db2 = sqlite.open_memory()

--db:busy_handler(busy_handler, 1)
--db2:busy_handler(busy_handler, 2)
--db:busy_handler()
--db:progress_handler(progress_handler, 1, '.')
--db2:progress_handler(progress_handler, 1, '-')
--db:progress_handler()
--db:trace(function (user, sql) print("Executing SQL: "..sql) end)
db2:trace(function (user, sql) print("Executing SQL: "..sql) end)
--db:trace()

lassert(db:create_aggregate("sun", 1, sum_step, sum, sqlite.TEXT, check), "failed to create function neg")
lassert(db2:create_aggregate("sun", 1, sum_step, sum, sqlite.TEXT, check), "failed to create function neg")
print("Database: "..tostring(db))

local first = true
local function cb(user, columns, cols, names)
---[[
    if (first) then
        for i = 1, columns do
            io.write(tostring(names[i]))
            if i < columns then
                io.write("\t")
            end
        end
        io.write("\n");
        first = false
    end
    for i = 1, columns do
        io.write(tostring(cols[i]))
        if i < columns then
            io.write("\t")
        end
    end
    io.write("\n");
--]]
    return false
end
tick(false)
print('----> db:exec')
db:exec("BEGIN")
db:exec("CREATE TABLE t1(a NUMBER,b NUMBER)")
db2:exec("CREATE TABLE t2(a NUMBER,b NUMBER)")
for i = 0, 10 do collectgarbage() end
--dosql(sqlite.OK, "CREATE TABLE t1(a NUMBER,b NUMBER)")
dosql(sqlite.OK, "INSERT INTO t1 VALUES(2,3)")
for i = 1, 3 do
    dosql(sqlite.OK, "INSERT INTO t1 VALUES(1,1)")
    db2:exec("INSERT INTO t2 VALUES("..i..",1)")
end
dosql(sqlite.OK, "END")
tick(true)
--dosql(sqlite.OK, "SELECT sun(a), sun(b) FROM t1", cb, db)
print(db:exec("select sun(a), sun(b) from t1", cb, db))

print('---> db:exec with callback')
first = true db2:exec("SELECT sun(a) FROM t2",cb)
tick(true)
print('---> db:compile vm:step/get_named_types/get_named_values/columns')
---[[
local vm = db:compile("SELECT * FROM t1")
lassert(vm:step() == sqlite.ROW, "step")
print('Number of columns:'..vm:columns())

for cn, ct in pairs(vm:get_named_types()) do
    print(cn.." : "..ct)
end
print('----')
for cn, cv in pairs(vm:get_named_values()) do
    print(cn.." : "..cv)
end
vm:finalize()
print('---> vm:get_uvalues() vm:get_unames() vm:get_utypes()')
---[[
local vm = db:compile("SELECT * FROM t1")
lassert(vm:step() == sqlite.ROW, "step")
print('Number of columns:'..vm:columns())
print(vm:get_unames())
print(vm:get_utypes())
print(vm:get_uvalues())

vm:finalize()
print('---> db:compile/step')
tick(true)
---[[
local vm = db:compile("SELECT * FROM t1")
--print(unpack(db:get_names()))
lassert(vm:step() == sqlite.ROW, "step")
print(unpack(vm:get_names()))
print(unpack(vm:get_types()))
print(unpack(vm:get_values()))
lassert(vm:step() == sqlite.ROW, "step")
print(unpack(vm:get_values()))
--table.foreach(db:get_values(), print)
--table.foreach(db:get_names(), print)
--table.foreach(db:get_types(), print)
--lassert(db:step() == sqlite.DONE, "step")
--lassert(vm:finalize() == sqlite.OK, "finalize")
--]]
print('---> reset')
vm:reset()
lassert(vm:step() == sqlite.ROW, "step")
print(unpack(vm:get_names()))
print(unpack(vm:get_types()))
print(unpack(vm:get_values()))
vm:finalize()
print('---> bind')
local vm = db:compile("SELECT *, ? FROM t1 where a == ?")
lassert(vm:bind(1, '2') == sqlite.OK, "bind")
lassert(vm:bind(2, '2') == sqlite.OK, "bind")
lassert(vm:step() == sqlite.ROW, "step")
print(unpack(vm:get_names()))
print(unpack(vm:get_values()))

lassert(vm:reset() == sqlite.OK, "reset")

lassert(vm:bind(1, 'a') == sqlite.OK, "bind")
lassert(vm:bind(2, '1') == sqlite.OK, "bind")
lassert(vm:step() == sqlite.ROW, "step")
print(unpack(vm:get_names()))
print(unpack(vm:get_values()))

lassert(vm:finalize() == sqlite.OK, "finalize")
print('---> db:urows')
local c = 0
for a, b in db:urows('select * from t1') do
    c = c + 1
    print(a.." : "..b.." : "..c)
    --break
end
print('---> db:rows')
local c = 0
for a in db:rows('select * from t1') do
    c = c + 1
    table.insert(a, c)
    print(unpack(a))
    --break
end
print('---> closing')
tick(true)
db:close_vm(true)
dosql(sqlite.OK, "DROP TABLE t1")

db:close()
print("Database: "..tostring(db))

cleanup()