js+html5 methods for manipulating sqlite databases

  • 2020-12-10 00:36:45
  • OfStack

The example of this article describes the js+html5 operation sqlite database method. To share for your reference, the details are as follows:


//copyright by lanxyou lanxyou[at]gmail.com
var lanxDB=function(dbname){
  var db=openDatabase(dbname,'1.0.0','',65536);
  return{
    // Return the database name 
    getDBName:function(){
      return dbname;
    },
    // Initializes the database and creates tables if necessary 
    init:function(tableName,colums){
      this.switchTable(tableName);
      colums.length>0?this.createTable(colums):'';
      return this;
    },
    // Create a table, colums:[name: The field name ,type: The field type ]
    createTable:function(colums){
      var sql="CREATE TABLE IF NOT EXISTS " + this._table ;
      var t;
      if (colums instanceof Array && colums.length>0){
        t=[];
        for (var i in colums){
          t.push(colums[i].name+' '+colums[i].type);
        }
        t=t.join(', ');
      }else if(typeof colums=="object"){
        t+=colums.name+' '+colums.type;
      }
      sql=sql+" ("+t+")";
      var that=this;
      db.transaction(function (t) { 
        t.executeSql(sql) ;
   })
    },
    // A switching table 
    switchTable:function(tableName){
      this._table=tableName;
      return this;
    },
    // Inserts the data and executes the callback function, supports batch inserts 
    //data for Array Each type, 1 The set of values are Object Each type, 1 a Obejct Should be the field name of the table, corresponding to the value to be saved 
    insertData:function(data,callback){
      var that=this;
      var sql="INSERT INTO "+this._table;
      if (data instanceof Array && data.length>0){
        var cols=[],qs=[];
        for (var i in data[0]){
          cols.push(i);
          qs.push('?');
        }
        sql+=" ("+cols.join(',')+") Values ("+qs.join(',')+")";
      }else{
        return false;
      }
      var p=[],
        d=data,
        pLenth=0,
        r=[];
      for (var i=0,dLength=d.length;i<dLength;i++){
        var k=[];
        for (var j in d[i]){
          k.push(d[i][j]);
        }
        p.push(k);
      }
      var queue=function(b,result){
        if (result){
          r.push(result.insertId ||result.rowsAffected);
        }
        if (p.length>0){
          db.transaction(function (t) { 
            t.executeSql(sql,p.shift(),queue,that.onfail);
          })
        }else{
          if (callback){
            callback.call(this,r);
          }
        }
      }
      queue();
    },
    _where:'',
    //where Statement that supports self-writing and in the form of object property value pairs 
    where:function(where){
      if (typeof where==='object'){
        var j=this.toArray(where);
        this._where=j.join(' and ');
      }else if (typeof where==='string'){
        this._where=where;
      }
      return this;
    },
    // Update the data, data Is the form of attribute value pair 
    updateData:function(data,callback){
      var that=this;
      var sql="Update "+this._table;
      data=this.toArray(data).join(',');
      sql+=" Set "+data+" where "+this._where;
      this.doQuery(sql,callback);
    },
    // Save the data according to the condition, update it if it exists, and insert it if it doesn't 
    saveData:function(data,callback){
      var sql="Select * from "+this._table+" where "+this._where;
      var that=this;
      this.doQuery(sql,function(r){
        if (r.length>0){
          that.updateData(data,callback);
        }else{
          that.insertData([data],callback);
        }
      });
    },
    // To get the data 
    getData:function(callback){
      var that=this;
      var sql="Select * from "+that._table;
      that._where.length>0?sql+=" where "+that._where:"";
      that.doQuery(sql,callback);
    },
    // Query, internal method 
    doQuery:function(sql,callback){
      var that=this;
      var a=[];
      var bb=function(b,result){
        if (result.rows.length){
          for (var i=0;i<result.rows.length;i++){
            a.push(result.rows.item(i));
          }
        }else{
          a.push(result.rowsAffected);
        }
        if (callback){
          callback.call(that,a);
        }
      }
      db.transaction(function (t) { 
        t.executeSql(sql,[],bb,that.onfail) ;
      })
    },
    // Deletes data according to conditions 
    deleteData:function(callback){
      var that=this;
      var sql="delete from "+that._table;
      that._where.length>0?sql+=" where "+that._where:'';
      that.doQuery(sql,callback);
    },
    // Delete table 
    dropTable:function(){
      var sql="DROP TABLE IF EXISTS "+this._table;
      this.doQuery(sql);
    },
    _error:'',
    onfail:function(t,e){
      this._error=e.message;
      console.log('----sqlite:'+e.message);
    },
    toArray:function(obj){
      var t=[];
      obj=obj || {};
      if (obj){
        for (var i in obj){
          t.push(i+"='"+obj[i]+"'");
        }
      }
      return t;
    }
  }
}
/*
examples:
var db=new lanxDB('testDB');
db.init('channel_list',[{name:'id',type:'integer primary key autoincrement'},{name:'name',type:'text'},{name:'link',type:'text'},{name:'cover',type:'text'},{name:'updatetime',type:'integer'},{name:'orders',type:'integer'}]);
db.init('feed_list',[{name:'parentid',type:'integer'},{name:'feed',type:'text'}]);
db.switchTable('channel_list').insertData([{name:'aa',link:'ss',updatetime:new Date().getTime()},{name:'bb',link:'kk',updatetime:new Date().getTime()}]);
db.where({name:'aa'}).getData(function(result){
  console.log(result);//result for Array
});
db.where({name:'aa'}).deleteData(function(result){
  console.log(result[0]);// To delete a number 
});
db.where({name:'bb'}).saveData({link:'jj'},function(result){
  console.log(result);// Affect the article number 
})
})
*/

I hope this article has been helpful in JavaScript programming.


Related articles: