websql.js

WebSQL framework.

Setup

npm install --save websql.js

Usage

import { Database } from 'websql.js';

Create a SQLite database called database

const database = new Database('your_database_name', null, {
    version: '',
    desc: 'your_database_desc',
    size: 20 * 1024 * 1024
});
database.on('error', function(){
    // ...
});
database.on('connect', function(){
    // ...
});

In the browser

<script src="websql.js"></script>
<script>
var database = new WebSQL.Database('your_database_name', null, {
    version: '',
    desc: 'your_database_desc',
    size: 20 * 1024 * 1024
});
database.on('error', function(){
    // ...
});
database.on('connect', function(){
    // ...
});
</script>

API

Database(name, desc, size)

• The name is the name of the database.
• The description and size attributes are ignored,but they are required for compatibility with the WebSQL API.

database.table(name)

• The name is the name if the table. It returns a [Table] object
• events
  • error deal with error
  • ready when table is loaded, then emit it.

const table = database.table('user');
table.on('error', function(){
    // ...
});
table.on('ready', function(){
    // ...
});

database.define(table_name, table_colums)

• The table_name is the name if the table.
• The table_colums is an JSON object which container a key and a type. if type is key, it become integer primary key autoincrement.

const table = database.define('user', {
    id: 'key',
    ago: 'integer',
    ax: 'datetime'
});
// ... table events.

database.query(sql)

• The sql is a string of SQL EXPRESSION.
• It returns an object of [Query]. it also got a then method for callback.

const query = database.query('SELECT * FROM USER');
query.then(function(result, transacte){
    console.log(result, transacte);
})

Notice: you must notice that when get a rows value, you should use .item(i).

const query = database.query('SELECT * FROM USER');
query.then(function(result, transacte){
    console.log(result.rows.item(1));
})

Table Object

table(name, database, colums)

• The name is the name of the table.
• The database is the target of database.
• The colums is the fields of table.

const table = new WebSQL.Table('user', database, {
    id: 'key',
    ago: 'integer',
    ax: 'datetime'
});
table.on('ready', function(){
    // ...
})
table.on('error', function(err){
    console.log(err);
})

table.insert(data)

Push new Data into this table.

const insertQuery = table.insert({ ago: 100, ax: new Date() });
insertQuery.then(function(result){
    console.log(result.rowsAffected === 1);
});

table.update(data, where)

Update new data into this table by condition of where.

const updateQuery = table.update({ ago: 36 });
updateQuery.then(function(result){
    console.log(result.rowsAffected === 1);
})

table.delete(where)

Delete old data from table by condition of where.

const deleteQuery = table.delete('where id=1');
deleteQuery.then(function(result){
    console.log(result.rowsAffected === 1);
})

Query Object

query(sql, db)

• The sql is a string of SQL code.
• The db is the object of database.

const queryString = new WebSQL.Query('SELECT * FROM USER', database.db);
queryString.on('error', function(err){
    console.log(err);
});

query.execute()

Run the SQL code.

const queryString = new WebSQL.Query('SELECT * FROM USER', database.db);
queryString.execute();

query.then(foo)

• The foo is a callback which execute the sql code complete to emit it.

const queryString = new WebSQL.Query('SELECT * FROM USER', database.db);
queryString.execute();
queryString.then(function(result){
    console.log(result);
})

Process Object

database.process()

return a pipe queue.

var process = database.process();

process.pipe([...args])

Args can be typeof string or typeof object for Query. It will push this pipe into queue for runing.

process
    .pipe('SELECT * FROM USER')
    .pipe('INSERT INTO ...', database.query('...'), new WebSQL.Query(sql, database.db))
    .pipe(['INSERT INTO ...', database.query('...'), new WebSQL.Query(sql, database.db)])
    .pipe('INSERT INTO ...', [database.query('...'), new WebSQL.Query(sql, database.db)])
    .pipe('INSERT INTO ...', [database.query('...'), [new WebSQL.Query(sql, database.db)]]);

process.run(sync)

when set sync=true ,then it will run step by step.

run the queue.

process.run(); // process.run(true);
process.on('done', function(){
    console.log('all done');
});

Testing

• First

npm install

• Main test suite

npm run dev

• Build

npm run build

Code demo

import * as WebSQL from 'websql.js';

const constructs = {
    relation: {
        id: 'key',
        minid: 'int',
        maxid: 'int',
        desc: 'varchar(40) NOT NULL'
    }
};

const options = {
    desc: 'my database',
    size: 20 * 1024 * 1024
};

/**
 * 创建一个数据库对象
 * @param:
 *    0: {string} 数据库名
 *    1: {json} 数据库结构
 *    2: {json} 数据库配置
 * @return: {Database}
 */
const base = new WebSQL.Database('mydatabase', constructs, options);

/**
 * 绑定数据库连接错误事件
 * @param:
 *    err: {error} 错误内容
 * @return: {Database}
 */
base.on('error', function(err){
    console.log('database error:', err);
});

/**
 * 绑定数据库连接完毕后的事件
 * @return: {Database}
 */
base.on('connect', function(){
    /**
     * database.table
     * 获取一个数据库表对象
     * @param:
     *    name: {string} 表名
     * @return: {Table|undefined}
     */
    const user = this.table('user');
    const that = this;

    /**
     * 当不存在表的时候
     * 采用 database.define方法
     * 定义创建一个新的表
     */
    if ( !user ){
        user = this.define('user', {
            id: 'key',
            ago: 'integer',
            ax: 'datetime'
        });
    }

    /**
     * 绑定表加载完毕后的事件
     * @return: {Table}
     */
    user.on('ready', function(){
        /**
         * 查询一个表达式
         * @return: {Query}
         */
        that.query('SELECT * FROM USER').then((result) => {
            if ( result.rows.length > 5 ){
                // 更新操作
                this.update({ ago: 100, ax: new Date() }, 'where id=5').then((result) => console.log('update result:', result));
            }
            else{
                // 插入操作
                this.insert({ ago: 19, ax: new Date() }).then((result) => console.log('insert result:', result));
            }
        });

        // 使用批量处理
        const a = that.query("INSERT INTO user (ago,ax) VALUES (23, 'adfa')")
        a.on('end', function(){ console.log('resolved:{ago:23,ax:adfa}') });
        const b = that.query("INSERT INTO user (ago,ax) VALUES (24, 'adfa')")
        b.on('end', function(){ console.log('resolved:{ago:24,ax:adfa}') });
        const c = that.query("INSERT INTO user (ago,ax) VALUES (25, 'adfa')")
        c.on('end', function(){ console.log('resolved:{ago:25,ax:adfa}') });

        var Process = that.process();
        Process.pipe(a).pipe(b).pipe(c).run();
        Process.on('done', function(){
            console.log('all done');
        })
    });

    /**
     * 绑定表错误事件
     * @return: {Table}
     */
    user.on('error', function(err){
        console.log('table user error:', err);
    });
});