SQLite is a very lightweight SQL database that stores its content into a single file on the hard drive. It means that it's perfectly suited for desktop application or when developing a website.

Connection to the Database

The first step is to create/open the database. Here's a sample that will use the "mybase.db" to store the database content :

class Test {
    static function main() {
        var cnx = neko.db.Sqlite.open("mybase.db");
        // ...
        cnx.close();
    }
}

Please note, if you are using SPOD Macros you will need to replace `neko.db.Sqlite.open(...)` with `sys.db.Sqlite.open(...)` as this will use the newer Connection type.

Once the database is either opened or created, a Connection object is returned. After you're done with using the connection, you can simply close it.

Creating a table

You can execute a request by using the cnx.request("QUERY") method. You can use this to create a new table for instance :

cnx.request("
        CREATE TABLE IF NOT EXISTS User (
            id INTEGER PRIMARY KEY AUTOINCREMENT, 
            name TEXT, 
            age INTEGER, 
            money DOUBLE
        )
");

Once the table is created you can use SQL queries to insert some values into it :

cnx.request("INSERT INTO User (name,age,money) VALUES ('John',32,100.45)");
cnx.request("INSERT INTO User (name,age,money) VALUES ('Bob',14,4.50)");

If you close the application, the data will be saved in the database file so next time you restart it will still be available.

Request and Result

When a request is execute, it will return a ResultSet containing all the result rows.

Here's an example :

var rset = cnx.request("SELECT * FROM User");
neko.Lib.print("Found "+rset.length+" users");
for( row in rset ) {
    neko.Lib.print("User "+row.name+" is "+row.age+" years old ");
}

If the field you select does not have a name, you can use the get methods from the result set :

var r = cnx.request("SELECT COUNT(*), SUM(money) FROM User WHERE age >= 18");
neko.Lib.print("Users count = " + r.getIntResult(0));
neko.Lib.print("Total money = " + r.getFloatResult(1));

In the case of an UPDATE request, the length with tell the number of rows modified :

var rset = cnx.request("UPDATE User SET age = age + 1");
neko.Lib.print("Updated "+rset.length+" users");

Escaping

If you want to use Strings inside your SQL requests, don't forget to escape them so they can't be forged to execute additional SQL.

For example :

var rset = cnx.request("SELECT * FROM Users WHERE name = "+cnx.quote(name));

See

The following classes documentation might be helpful :

• neko.db.Sqlite
• neko.db.Connection
• neko.db.ResultSet

See also the SPOD: Object-Database Modeling Tutorial for a more highlevel API.