/**
 * SQLite
 *
 * Demonstrates SQLite library
 */

import("nexus.system.data.sqlite")


/**
 * Main
 *
 * params:  none
 * return:  nothing
 */
method main ()
{
    // create database
    path = "c:\\nexus\\test\\data\\sqlite.db"
    if {System.IO.File.exists(path) ? System.IO.File.delete(path)}
    env = System.Data.SQLite.Environment.new()
    conn = env.connect(path)
    conn.set_auto_commit(false)

    // create table
    stmt =
    [[
        CREATE TABLE test
        ("id" INTEGER PRIMARY KEY NOT NULL,
        "name" VARCHAR(64) DEFAULT NULL,
        "created" DATETIME DEFAULT NULL);
    ]]

    conn.execute(stmt)
    conn.commit()

    // insert data
    for (id; 0..500) {
        now = System.time(),
        dts = ("'" + System.date_format("%Y-%m-%d %H:%M:%S", false, now) + "'")
        stmt = "insert into test (id,name,created) values (" + id + ", 'this is sample data', " + dts + ");"
        conn.execute(stmt)
    }

    conn.commit()

    // read data
    for (id; 0..500) {
        stmt = "select * from test where id = " + id + ";"
        cursor = conn.execute(stmt)
        res = cursor.fetch()
        print(res)
    }

    // close database
    conn.close()
}


main()