paper-library-client/src/plugins/db.js

import { getDatabaseDir } from "./env";
import { formatDate } from "./utils";
const path = require("path");
const fs = require("fs");
const sqlite = require("sqlite3").verbose();

let db = undefined;

init();

function init(sqlName = "client") {
  if (db) return;
  const databasePath = getDatabaseDir();
  var orgDb = path.join(databasePath, "org.rdb");
  var clientDb = path.join(databasePath, sqlName + ".rdb");

  if (!fs.existsSync(clientDb)) {
    fs.copyFileSync(orgDb, clientDb);
  }
  db = new sqlite.Database(clientDb, sqlite.OPEN_READWRITE);
}

function serializeWhere(params) {
  const where = Object.keys(params)
    .map(key => {
      if (key.indexOf("Time") !== -1) {
        return `${key} LIKE $${key}`;
      } else {
        return `${key} = $${key}`;
      }
    })
    .join(" AND ");
  const whereData = {};
  Object.entries(params).map(([key, val]) => {
    whereData[`$${key}`] = key.indexOf("Time") !== -1 ? `${val}%` : val;
  });

  return {
    where,
    whereData
  };
}

// function serializeUpdate(params) {
//   const template = Object.keys(params)
//     .map(key => {
//       return `${key}=$${key}`;
//     })
//     .join(",");
//   const templateData = {};
//   Object.entries(params).map(([key, val]) => {
//     templateData[`$${key}`] = val;
//   });

//   return {
//     template,
//     templateData
//   };
// }

// scan
function saveUploadInfo(params) {
  const sql = `INSERT INTO scan (taskId, taskName, courseCode, courseName, teachingClassName, frontOriginImgPath, versoOriginImgPath, clientUserId, clientUsername, clientUserLoginTime, isUpload,createdTime, finishTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)`;
  const datas = [
    params.taskId,
    params.taskName,
    params.courseCode,
    params.courseName,
    params.teachingClassName,
    params.frontOriginImgPath,
    params.versoOriginImgPath,
    params.clientUserId,
    params.clientUsername,
    params.clientUserLoginTime,
    0, // isUpload
    formatDate(), // createdTime
    null
  ];
  return new Promise((resolve, reject) => {
    db.serialize(() => {
      db.run(sql, datas, function(err) {
        if (err) reject(err);
        resolve(this.lastID);
      });
    });
  });
}

function searchUploadList(params) {
  const { where, whereData } = serializeWhere(params);

  const sql = `SELECT * FROM scan WHERE ${where}`;

  return new Promise((resolve, reject) => {
    db.all(sql, whereData, (err, rows) => {
      if (err) reject("search info fail!");
      resolve(rows);
    });
  });
}

function countScanList(params) {
  const { where, whereData } = serializeWhere(params);
  const sql = `SELECT COUNT(1) AS count FROM scan WHERE ${where}`;

  return new Promise((resolve, reject) => {
    db.all(sql, whereData, (err, rows) => {
      if (err) reject("count list fail!");

      resolve(rows[0].count);
    });
  });
}

function getUploadCount(limitTime) {
  const sql = `SELECT COUNT(1) AS count FROM scan WHERE strftime('%s',finishTime, 'utc') >= '${limitTime}' AND isUpload = 1`;

  return new Promise((resolve, reject) => {
    db.all(sql, (err, rows) => {
      if (err) reject("count list fail!");

      resolve(rows[0].count);
    });
  });
}

function updateUploadState(id) {
  const sql = `UPDATE scan SET isUpload=$isUpload,finishTime=$finishTime WHERE id=$id`;
  const datas = {
    $isUpload: 1,
    $finishTime: formatDate(),
    $id: id
  };
  return new Promise((resolve, reject) => {
    db.run(sql, datas, err => {
      if (err) reject("update upload info fail!");

      resolve();
    });
  });
}

function deleteScanById(id) {
  const sql = `DELETE FROM scan WHERE id=$id`;
  const datas = {
    $id: id
  };
  return new Promise((resolve, reject) => {
    db.run(sql, datas, err => {
      if (err) reject("delete scan fail!");

      resolve(true);
    });
  });
}

// dict
function getAllDict() {
  const sql = `SELECT * FROM dict`;

  return new Promise((resolve, reject) => {
    db.all(sql, (err, rows) => {
      if (err) reject("get all dict fail!");

      const storeDict = {};
      rows.map(item => {
        storeDict[item.key] = item.val;
      });
      resolve(storeDict);
    });
  });
}

function initDict(key, val = "") {
  const sql = `INSERT INTO dict (key, val) VALUES (?,?)`;
  const datas = [key, val];

  return new Promise((resolve, reject) => {
    db.run(sql, datas, err => {
      if (err) reject(`init dict ${key} fail!`);

      resolve();
    });
  });
}

function getDict(key, defaultVal = "") {
  const sql = `SELECT * FROM dict WHERE key=?`;
  return new Promise((resolve, reject) => {
    db.get(sql, key, (err, row) => {
      if (err) reject(`get dict ${key} fail!`);
      resolve((row && row.val) || defaultVal);
    });
  });
}

function setDict(key, val) {
  const sql = `UPDATE dict SET val=? WHERE key=?`;
  return new Promise((resolve, reject) => {
    db.run(sql, [val, key], err => {
      if (err) reject(`update dict ${key} fail!`);

      resolve(true);
    });
  });
}

export default {
  init,
  // scan
  saveUploadInfo,
  searchUploadList,
  countScanList,
  getUploadCount,
  updateUploadState,
  deleteScanById,
  // dict
  getDict,
  setDict,
  getAllDict,
  initDict
};