Helper db.js for Cordova-sqlite-storage

Posted by admin on March 30, 2016
javascript

Espino316 dbhelper-cordova-sqlite-storage is a helper for plugin Cordova-sqlite-storage (https://github.com/litehelpers/Cordova-sqlite-storage).
The goal is make more simple the use of the plugin (already excellent), because I like to use shorter more maintainable code.

**Important** In order to use this component, you must first install Cordova-sqlite-storage (https://github.com/litehelpers/Cordova-sqlite-storage).

Usage

Include

[html]

[/html]

Declaration

[javascript]
var db = new DbHelper(“mydb”);
// mydb Es el nombre de la db
[/javascript]

Script execution (i.e. create initial structure):

[javascript]
db.execScript ( “sqlscripts/setup.sql” );
[/javascript]

In sqlscripts/setup:
[sql]
CREATE TABLE IF NOT EXISTS
schools (
school_id int PRIMARY KEY ASC,
school_name text
);
[/sql]

Simple query:

[javascript]
// Variable to store the sql
var sql = “SELECT * FROM schools WHERE school_id = :schoolId”;

// Variable to store the parameters, if any
var params = {};
params[‘schoolId’] = 9901;

// Variable for store the function to apply
// Expects an object, assoc array.

// prints:
// [{ school_id: “9001”, “school_name”:”PABLO LIVAS”},{ school_id: “9002”, “school_name”:”ADOLFO PRIETO”}]
var fn = function ( result ) {
console.log( result );
};

// Call query
db.query(
sql,
params,
fn
);

/* Anothe example */
// We can also declare params simply with ?

// Variable to store the sql
var sql = “SELECT * FROM schools WHERE school_id = ?”;

// Variable to store the parameters, if any
var params = [9901];

// Variable for store the function to apply
// Expects an object, assoc array.

// prints something like:
// [{ school_id: “9001”, “school_name”:”PABLO LIVAS”},{ school_id: “9002”, “school_name”:”ADOLFO PRIETO”}]
var fn = function ( result ) {
console.log( result );
};

// Call query
db.query(
sql,
params,
fn
);
[/javascript]

To query with no parameters:
[javascript]
db.query(
sql,
null,
fn
);
[/javascript]

Inserts and updates:

The helper uses “upserts” statements. If record exists, updates, else, inserts.

[javascript]
// Name of the table
var tableName = “schools”;

// Object with data
var tableData = {
“shool_id”: 9003,
“school_name”: “MOISEIS SAENS”;
};

// Array with list of key fields
var keyFields = [“school_id”];

// Call upsert
db.upsert(tableName, tableData, keyFields);

[/javascript]

Batch inserts / updates:

Same as previous function, but the data is an array of objects.

[javascript]
// Table name
var tableName = “schools”;

// Object array (the data)
var tableData = [
{
“shool_id”: 9003,
“school_name”: “MOISEIS SAENS”;
},
{
“shool_id”: 9004,
“school_name”: “JERONIMO SILLER”;
}
];

// Array with a list of the key fields
var keyFields = [“school_id”];

// Call bulkUpsert
db.bulkUpsert(tableName, tableData, keyFields);
[/javascript]

The use is simple, useful, short and elegant.

Tags: cordova, db, helper, javascript, phonegap, plugin, sqlite