Sli2 NPM | npm.io

SLI2

SQL Like Interface for IndexedDB (and JSON)

Install

npm install sli2

Sample

var sli2 = new SLI2("test_database");

sli2.open()
.then(function() {
  sli2.upgrade(function() {
    sli2.createTable("table1");
    sli2.insertInto("table1", ["key1", "key2", "key3"])
        .values("a", 100, 200);
  })
  .then(function() {
    var query = sli2.select().from("table1");

    query.run(function(rows) {
      console.log(rows); // [{ "key1": "a", "key2": 100, "key3": 200 }]
    });
  });
});

Main Class

SLI2(String, Object)

arguments0: database_name
arguments1: (optional) initialize option
arguments1.mode: see SLI2#setMode (default is same to 'idb')

Methods

SLI2#useIndexedDB(): void

Set the related indexedDB object

SLI2#setMode(String): void

arguments0: 'idb' or 'no_idb' (can use Constants)

Setting 'idb' is using indexecDB mode. Setting 'no_idb' is not using indexedDB mode. can use only 'select' syntax.

SLI2.createDatabaseDefinition(): DatabaseDefinition

Create DatabaseDefinition instance. see DatabaseDefinition

SLI2#deleteDatabase(String, Function): Promise

arguments0: database_name
arguments1: (optional) blocking function (function())

SLI2#open(DatabaseDefinition): Promise

arguments0: (optional) DatabaseDefiniton instance.

Open indexedDB object.

SLI2#close(): Promise

Close indexedDB object.

SLI2#destroy(): Promise

Clear indexedDB object.

SLI2#getCurrentVersion(): Number

Get database version.

SLI2#begin_transaction(String|String[], String, Function): Promise

arguments0: table_name
arguments1: open mode 'readwrite' or 'readonly' (can use Constants)
arguments2: transacting process

To sustain the transaction into the third argument function. Auto commit where completing the all process, and auto rollback when error occurs. Can use only DML method. (can not use DDL)

SLI2#upgrade(Function, Function): Promise

arguments0: upgrade process (function({IDBDatabase}db, {IDBTransaction} tra))
arguments1: end process (function())

Generate the transaction, can use DDL. Useing DatabaseDefinition when database is opened, this method can not use. Each callback function(first and second argument) will be executing synchronously.

SLI2#addTask(Object, Function): void

arguments0: object with the 'exec' metod. (or null)
arguments1: called by 'exec' method of first argument. (function({any}object, {Function}after_process))

DML and DDL will be run with a delay synchronously. Define the task to be performed along the specified order. The second argument has three arguments. The first argument is result of 'exec' method. The second argument is the function to be called when the process was completed successfully. The third argument is the function to be called when the process was not successfully. By properly execute the second and third argument, to control the continuation and iterruption of the transaction.

sli2.addTask(obj, function(result, ok, ng) {
  if(result === true) {
    ok();
  } else {
    ng();
  }
});

SLI2#create_table(String, Object): DDL

Return DDL#create_table. This can also be described as 'createTable'.

SLI2#drop_table(String): DDL

Return DDL#drop_table. This can also be described as 'dropTable'.

SLI2#create_index(String, String, Object): DDL

Return DDL#create_index. This can also be described as 'createIndex'.

SLI2#drop_index(String, String): DDL

Return DDL#drop_index. This can also be described as 'dropIndex'.

SLI2#select(Function|undefined): DML

Return DML#select.

SLI2#insert_into(String[, String[]]): DML

Return DML#insert_into. This can also be described as 'insertInto'.

SLI2#delete_from(String): DML

Return DML#delete_from. This can also be described as 'deleteFrom'.

SLI2#update(String): DML

Return DML#update.

SLI2#rollback(): void

Rollback the transaction.

Querable

Querable#run(Function): Promise

arguments0: callback function (function({any}result, {Function}ok, {Function}ng))

Add specified task. Querable is inherited DML and DDL, each DML and DDL is executed being call the 'run'.

sli2.select().from("table").run();
sli2.insertInto("table1", ["key1", "key2"]).values(100, 200).run();

The first argument will be called asynchronously after 'run' execution. The first argument function has three arguments. The first argument is result of DML or DDL. The second argument is the function to be called when the process was completed successfully. The third argument is the function to be called when the process was not successfully.

sli2.select().from("table").run(function(num, ok, ng) {
  if(num > 0) {
    ok();
  } else {
    ng();
  }
});

DML

Inheirt Querable.

DML#select(Funciton): DML

argument0: (optional) callback function (function({Function}output, {Object}input))

To specify the items to select. The first argument has two arguments. The first argument is output function. By the literal to the argument, the result select them. Also, it has aggregate function to reference, do the aggregation by executing in the same way. The second argument is each record that is provided in the Object type.

sli2.select(function(out, in) {
  out(in.key1).as('alias1'); // when no set the alias, column name is '0'
  out.max(in.key2).as('alias2'); // when no set the alias, column name is '1'
});

DML#from(String|DML|Object[]|Object): DML

argument0: table name or sub-query or data set or table name and alias pair

When specify String, getting the data from the same name of the table name. When specify DML, executing as the subquery. When specify Array of Object, to handle it itself as a table. When specify Object, key the name of the table name, value as alias, to inner join.

DML#as(String): DML

set alias to table or input object.

DML#insert_into(String[, String[]]): DML

argument0: table name
argument1: insert target columns

This can also be described as 'insertInto'.

DML#values(...any|any[]): DML

argument0: insert value

DML#delete_from(String): DML

argument0: table name

This can also be described as 'deleteFrom'.

DML#update(String): DML

argument0: table name

DML#set(Object): DML

argument0: column name and value pair

DML#join(String|DML|Object[], Kriteria|Function, String): DML

argument0: table name or sub-query or data set
argument1: data join condition.
argument2: (optional) join type, 'inner' or 'left' or 'right' (default: inner)

The second argument is instance of Kriteria. When specify function, function's first argument is instance of Kriteria. see. Kriteria

var kri = new Kriteria();
sli2.select().from("table1").join("table2", kri);

// same
sli2.select().from("table").join("table2", function($) {
  // $ is instance of Kriteria
});

DML#inner_join(DML|String|Object[], Kriteria|Function):DML

Return DML#join(argument0, argument1, 'inner'); This can also be described as 'innserJoin'.

DML#left_join(DML|String|Object[], Kriteria|Function):DML

Return DML#join(argument0, argument1, 'left'); This can also be described as 'leftJoin'.

DML#right_join(DML|String|Object[], Kriteria|Function):DML

Return DML#join(argument0, argument1, 'right'); This can also be described as 'rightJoin'.

DML#where(Kriteria|Function): DML

argument0: data extract condition.

The first argument is instance of Kriteria. When specify function, function's first argument is instance of Kriteria. see. Kriteria

var kri = new Kriteria();
sli2.select().from("table").where(kri);

// same
sli2.select().from("table").where(function($) {
  // $ is instance of Kriteria
});

DML#group_by(...String|String[]): DML

set to grouping target.

This can also be described as 'groupBy'.

DML#having(Kriteria|Function): DML

argument0: data extract conditoin after grouping.

The first argument is instance of Kriteria. When specify function, function's first argument is instance of Kriteria. see Kriteria

var kri = new Kriteria();
sli2.select().from("table").groupBy("key1", "key2").having(kri);

// same
sli2.select().from("table").groupBy("key1", "key2").having(function($) {
  // $ is instance of Kriteria
});

Note: Can not use aggregate function

DML#union(): DML

Conbining a plurality of data set by select. Summarize the duplicate data.

DML#union_all():DML

Conbining a plurality of data set by select. Not sumamrize the duplicate data.

This can also be described as 'unionAll'.

DML#order_by(...<String|Object>|Array<String|Object>): DML

The specify stirng as a column name, sort the results. When specify Object, key is column name, value is 'asc' or 'desc'.

.orderBy("key1", "key2");
.orderBy({ "key1": "asc" }, { "key2": "desc" });
.orderBy(["key1", "key2"]);
.orderBy([{ "key1": "asc" }, { "key2": "desc" }]);

This can also be described as 'orderBy'.

DML#limit(Number, Number): DML

argument0: max count or offset number of getting.
argument1: (optional) offset number of getting.

When not specify the second argument, the first argument is max count of getting. When specify the second argument, the first argument is offset number, the second argument is max count of getting.

DML#offset(Number): DML

argument0: offset number of getting.

Offset number of gettign.

DDL

DDL#create_table(String, Object): DDL

argument0: table name.
argument1: (optional) create table option.

The second argument is create table option of IndexedDB.

This can also be described as 'createTable'.

DDL#drop_table(String): DDL

argument0: table name.

This can also be described as 'dropTable'.

DDL#create_index(String, String, Object): DDL

argument0: table name.
argument1: index name.
argument2: (optional) create index option.

The third argument is create index option of IndexedDB.

This can also be described as 'createIndex'.

DDL#drop_index(String, String): DDL

argument0: table name.
argument1: index name.

This can also be described as 'dropIndex'.

Manage Class

DatabaseDefinition()

Method of DatabaseDefinition

DatabaseDefinition#setVersion(Number, Function)

argument0: version number for positive integer
argument1: update process (function({SLI2}sli2))

DatabaseDefinition#defineVersionMap(any)

To define the order of the version you want to run.

var def = SLI2.createDatabaseDefinition();
def.setVersion(1, function(sli2) { /* Something */ });
// and set the some version

def.defineVersionMap([ 1, 2, 3 ]);
// run version 1 -> 2 -> 3

// But version 3 was wrong version
def.setVersion(4, function(sli2) { /* fix setting */ });
def.setVersion(5, function(sli2) { /* correct setting */ })
def.defineVersionMap([
  [ 1, 2, 5],
  [ 3, 4, 5]
]);
// New user is run version 1(ok) -> 2(ok) -> 5(ok)
// Wrong version's user is run version 3(ng) -> 4(fix) -> 5(ok)

Constants

SLI2.USE_IDB: 'idb'
SLI2.NO_IDB: 'no_idb'
SLI2.READ_WRITE: 'readwrite'
SLI2.READ_ONLY: 'readonly'

TODO

TableSchemaを定義できるようにする
aliasを設定しないselectで、column名を取得できるようにする
limit-orderでintro_selectを行う
Kriteriaの定義方法を変える

Licence

MIT

clone criteria2idbquery kriteria

@infinitebrahmanuniverse/nolb-sli2 @everything-registry/sub-chunk-2779 imy

8 years ago

8 years ago

8 years ago

8 years ago

8 years ago