electron - electron-vue 使用sqlite3的基本例子 sqlite db run exec 的各种操作

如题。参考：https://stackoverflow.com/questions/17954743/node-js-and-express-passing-sqlite-data-to-one-of-my-views

API:  https://github.com/mapbox/node-sqlite3/wiki/API#databaseallsql-param--callback

<template>
  <div id="wrapper">
    {{sql_result}}
    <ul>
      <li v-for='item in items' :key='item.id'>{{item.info}}</li>
    </ul>
  </div>
</template>

<script>
  export default {
    data() {
      return {
        sql_result: 'asdf',
        items: []
      }
    },
    computed: {
    }, 
    components: {
    },
    methods: {
    },
    mounted() {
      var sqlite3 = require('sqlite3').verbose();
      var db = new sqlite3.Database(':memory:');
      let that = this;

      db.serialize(function() {
        db.run("CREATE TABLE lorem (info TEXT)");

        var stmt = db.prepare("INSERT INTO lorem VALUES (?)");
        for (var i = 0; i < 10; i++) {
            stmt.run("Ipsum " + i);
        }
        stmt.finalize();

        db.each("SELECT rowid AS id, info FROM lorem", function(err, row) {
            console.log(row.id + ": " + row.info);
            that.items.push(row)
            that.sql_result += row.info + ","
        }, function(err, row_numbers){
            console.info('in call backs');

            console.info('err: ' + err);
            console.info('row_nubmers: ' + row_numbers)
            this.items = row_numbers
            console.info("sql_result: " + this.sql_result);
        });
      });

      db.close();
      console.info('done')
    }
  }
</script>

再看一段代码： 例子2

下面的代码中，删掉数据库的值之后，等300ms，再刷新当前页面

1. 代码是顺序执行的 ( in serialize 0 .. 1... 2... )

2. init() 是一个独立的方法， 在mounted 中调用

3. sleep, db_tool 都是放在了  render/main.js 中。

      to_delete(id) {
        console.info("-=--- in delete")

        let db = this.$database_tool.get_db()
        let that = this
        console.info("=== in serialize 0")
        db.serialize(function(){
          db.run('delete from categories where id = ?', id)
          that.$message.success("操作成功")
          console.info("=== in serialize 1")
        })
        console.info("=== in serialize 2")
        db.close();
        that.$sleep(300).then( () => {
          that.init();
        })
      }

render/main.js: 

// 引入 this.$database_tool
import {database_tool} from '@/lib/database_tool.js'
Vue.prototype.$database_tool = database_tool

const sleep = (waitTimeInMs) => new Promise(resolve => setTimeout(resolve, waitTimeInMs));
Vue.prototype.$sleep = sleep

lib/database_tool.js

var sqlite3 = require('sqlite3').verbose();
const DB_NAME = 'database.sqlite3'


let database_tool = {
  get_db: function(){
    return new sqlite3.Database(DB_NAME);
  },

  check_or_setup_database: function (){
    console.info("== in check_or_setup_database")
    var db = this.get_db()
    let that = this
    //  可以把这里改成true
    let is_init_data = false

    db.close()
  },

  close_db: function(){
    this.get_db().close()
  }

}

export {database_tool}

更新：2种传参方法  

      // As an array.
      db.run("UPDATE tbl SET name = ? WHERE id = ?", [ "bar", 2 ]);

      // As an object with named parameters.
      db.run("UPDATE tbl SET name = $name WHERE id = $id", {
          $id: 2,
          $name: "bar"
      });

更新3： serialize 中的查询 是顺序执行的（待商榷！）

      write_single_row_for_csv(row){
        let that = this
        let db = that.$database_tool.get_db()

        db.serialize(function(){

          let industry_id = 0
          db.get(`select * from industries where name = "${row['industry_name']}"`, [], function(error, row){
            industry_id = row.id
          })

          let material_id = 0
          db.get(`select * from materials where name = "${row['material_name']}"`, [], function(error, row){
            material_id = row.id
          })

          let sql = `
            insert into emission_ratios(name, material_id, industry_id, algorithm_id, value,
              english_name, product_name,
              created_at, updated_at)
            values("${row['name']}", ${material_id}, ${industry_id}, ${row['algorithm_id']}, ${row['value']},
              "${row['english_name']}", "${row['product_name']}",
              datetime('now'), datetime('now') )
          `
          console.info(sql)

          db.run(sql)
        })
        db.close();
      },