KIOSK

GUIDE


SQL Usage Guide

This topic describes how you can use  SQL database in the application.

SQLite Database

The SQLite database provides a way to store databases that can be queried using various SQL statements. Each Tizen application can store multiple databases and each one of them can store multiple tables. Databases can be accessed using the SQL language, which is a common standard for these kinds of applications.

Note

When working with the SQL database, this API provide Synchronous database API only.

  • To start an operation, you must wait for the previous one to be completed. This approach can be used with promise.

Usage

const url = `ws://${DB_HOSTNAME}:${DB_PORT}${DB_PATH}`;

  const sqlite3 = new SQLite3(url);
  const db = await sqlite3.open(DB_SOURCE, (err) => {
    if (err) {
      return;
    }
  });

  {
    const q = `CREATE TABLE user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name text,
            email text UNIQUE,
            password text,
            CONSTRAINT email_unique UNIQUE (email)
            )`;

    await db.run(q, [], (err) => {
      if (err) {
          `run: error: ${JSON.stringify(
            err
          )}: Table User exists in the current database. To fix, remove ${DB_SOURCE}`
        );
        return;
      }
    });
  }

Features

  • Provide native SQLite database binding using JavaScript WebAPI.

  • Straightforward query and parameter binding interface.

API


  • Database

    all
    close
    configure
    each
    exec
    get
    on
    prepare
    removeAllListeners
    removeListener
    run



  • SQLite3

    verbose



  • Statement

    all
    bind
    each
    finalize
    get
    reset
    run


Database


new Database(filename, modeopt) ? {Promise.<Database>}

Returns a new Database object and automatically opens the database. There is no separate method to open the database.

Example



let db = await new sqlite3.Database('filename.db');  


Parameters:


Name Type Attribute Description
filename string Valid values are filenames, and ":memory:" for an anonymous in-memory database
mode int <optional> One or more of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE and sqlite3.OPEN_CREATE. The default value is OPEN_READWRITE

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing a database object.
Type  Promise.<Database>


Methods

all(query, …paramopt) ? {Promise.<Array.<Object>>}

Runs the SQL query with the specified parameters and calls the callback with all result rows afterwards. The parameters are the same as the Database#run function.

Parameters:


Name Type Attribute Description
query string The SQL query to run.
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing an array of rows. If the result set is empty, it will be an empty array, otherwise it will have an object for each result row which in turn contains the values of that row, like the Database#get function.

Type  Promise.<Array.<Object>>


close() ? {Promise.<undefined>}

Close the database.


Returns:
Type   Promise.<undefined>


configure(option, value) ? {Promise.<undefined>}

Sets a configuration option for the database. Valid options are:


  • busyTimeout: provide an integer as a value. Sets a busy handler that sleeps for a specified amount of time when a table is locked. The handler will sleep multiple times until at least "ms" milliseconds of sleeping have accumulated. After at least "ms" milliseconds of sleeping, the handler returns 0 which causes sqlite3_step() to return SQLITE_BUSY. Calling this routine with an argument less than or equal to zero turns off all busy handlers.


Parameters:


Name Type Description
option string A name of an option to set.
value int Time in milliseconds.

Returns:
Type   Promise.<undefined>


each(query, …paramopt, callbackopt) ? {Promise.<Integer>}

Runs the SQL query with the specified parameters and calls the callback once for each result row.
If you know that a query only returns a very limited number of rows, it might be more convenient to use Database#all to retrieve all rows at once.


There is currently no way to abort execution.


Parameters:


Name Type Attribute Description
query string The SQL query to run.
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.
callback function <optional> (function(row):void) If the result set succeeds but is empty, the callback is never called. In all other cases, the callback is called once for every retrieved row. The order of calls correspond exactly to the order of rows in the result set.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing the number of retrieved rows.
Type   Promise.<Integer>


exec(query) ? {Promise.<undefined>}

Runs all SQL queries in the supplied string. No result rows are retrieved. The function returns the Database object to allow for function chaining. If a query fails, no subsequent statements will be executed (wrap it in a transaction if you want all or none to be executed).


Note

This function will only execute statements up to the first NULL byte. Comments are not allowed and will lead to runtime errors.


Parameters:


Name Type Description
query string The SQL query to run.

Throws:
Throw an error object if an error occurred.

Returns:
Type   Promise.<undefined>


get(query, …paramopt) ? {Promise.<(undefined|Object)>}

Runs the SQL query with the specified parameters and calls the callback with the first result row afterwards. The parameters are the same as the Database#run function.



Parameters:


Name Type Attribute Description
query string The SQL query to run.
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing undefined, if the result set is empty, otherwise an object containing the values for the first row. The property names correspond to the column names of the result set. It is impossible to access them by column index; the only supported way is by column name.

Type  Promise.<(undefined|Object)>


on(event, callback) ? {Promise.<undefined>}

Sets an event handler for the database. Valid events and callback signatures are as follows:


Event Callback Description
trace function(query):void Invoked when an SQL statement starts to execute, with a rendering of the statement text.
profile function(query, time):void Invoked when an SQL statement finishes execution, with a rendering of the statement text, and its execution time taken

To enable event handling, call sqlite3.verbose() before registering callbacks.

Parameters:


Name Type Description
event string A name of an event to handle.
callback function A callback for the event.

Returns:
Type   Promise.<undefined>


prepare(query) ? {Promise.<Statement>}

Prepares the SQL statement and optionally binds the specified parameters and calls the callback when done. The function returns a Statement object.



Parameters:


Name Type Description
query string The SQL query to run.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing a statement object.
Type  Promise.<Statement>


removeAllListeners(event)? {Promise.<undefined>}

Removes all event handlers for the database. Valid events are the same as Database#on().



Parameters:


Name Type Description
event string A name of an event to handle.

Returns:
Type  Promise.<undefined>


removeListener(event, callback)? {Promise.<undefined>}

Removes an event handler for the database. Valid events and callback signatures are the same as Database#on().



Parameters:


Name Type Description
event string A name of an event to handle.
callback function A callback for the event.

Returns:
Type  Promise.<undefined>


run(query, …paramopt) ? {Promise.<Statement>}

Runs the SQL query with the specified parameters and calls the callback afterwards. It does not retrieve any result data.



Parameters:


Name Type Attribute Description
query string The SQL query to run.
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing an executed statement
Type   Promise.<Statement>

SQLite3


Methods

(async) verbose() ? {Promise.<undefined>}

Sets the execution mode to verbose to produce long stack traces. There is no way to reset this.


Returns:
Type   Promise.<undefined>

Statement


Methods

all(…paramopt) ? {Promise.<Array.<Object>>}

Binds parameters, executes the statement and calls the callback with all result rows.
Like with Statement#run, the statement will not be finalized after executing this function.


Name Type Attribute Description
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing an array, that contains an object for each result row which in turn contains the values of that row.
Type   Promise.<Array.<Object>>


bind(…paramopt) ? {Promise.<null>}

Binds parameters to the prepared statement and calls the callback when done or when an error occurs.
Binding parameters with this function completely resets the statement object and row cursor and removes all previously bound parameters, if any.



Parameters:


Name Type Attribute Description
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.

Throws:
Throw an error object if an error occurred.

Returns:
Type  Promise.<null>


each(…paramopt, callbackopt) ? {Promise.<Integer>}

Binds parameters, executes the statement and calls the callback for each result row.
Like with Statement#run, the statement will not be finalized after executing this function.


If you know that a query only returns a very limited number of rows, it might be more convenient to use Statement#all to retrieve all rows at once. There is currently no way to abort execution!


Parameters:


Name Type Attribute Description
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.
callback function <optional> (function(row):void) If the result set succeeds but is empty, the callback is never called. In all other cases, the callback is called once for every retrieved row. The order of calls correspond exactly to the order of rows in the result set.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing the number of retrieved rows.
Type   Promise.<Integer>


finalize() ? {Promise.<null>}

Finalizes the statement. It is prefer to explicitly finalizing your statement, as you might experience long delays before the next query is executed. After the statement is finalized, all further function calls on that statement object will throw errors.


Throws:
Throw an error object if an error occurred.

Returns:
Type   Promise.<null>


get(…paramopt) ? {Promise.<Object>}

Binds parameters, executes the statement and retrieves the first result row.
Like with Statement#run, the statement will not be finalized after executing this function.


Using this method can leave the database locked, as the database awaits further calls to Statement#get to retrieve subsequent rows. To inform the database that you are finished retrieving rows, you should either finalize (with Statement#finalize) or reset (with Statement#reset) the statement.


Parameters:


Name Type Attribute Description
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing an object that contains the values for the first row.
Type  Promise.<Object>


reset() ? {Promise.<null>}


Resets the row cursor of the statement and preserves the parameter bindings. Use this function to re-execute the same query with the same bindings. This action will never fail and will always return null.


Throws:
Throw an error object if an error occurred.

Returns:
Type   Promise.<null>


run(…paramopt) ? {Promise.<Statement>}

Binds parameters and executes the statement.
If you specify bind parameters, they will be bound to the statement before it is executed. Note that the bindings and the row cursor are reset when you specify even a single bind parameter.


The behavior is identical to the Database#run method with the difference that the statement will not be finalized after it is run. This means you can run it multiple times.


Parameters:


Name Type Attribute Description
param string <optional> <repeatable> When the SQL statement contains placeholders, you can pass them in here.

Throws:
Throw an error object if an error occurred.

Returns:
A promise containing an executed statement
Type   Promise.<Statement>


위로가기