From c3f18f8b34bd8403a040a263bc8354bc7a9b4eae Mon Sep 17 00:00:00 2001
From: journey-ad <journey.adc@gmail.com>
Date: Mon, 6 Mar 2023 18:57:41 +0800
Subject: perf: Delayed writing to database

- Implement delayed writing feature to database for improved performance and reduced write frequency
- Update docs
---
 db/mongodb.js | 18 +++++++++++++++++-
 db/sqlite.js  | 59 ++++++++++++++++++++++++++++++++++-------------------------
 2 files changed, 51 insertions(+), 26 deletions(-)

(limited to 'db')

diff --git a/db/mongodb.js b/db/mongodb.js
index 57f1b72..4119c7a 100644
--- a/db/mongodb.js
+++ b/db/mongodb.js
@@ -32,8 +32,24 @@ function setNum(name, num) {
           .exec()
 }
 
+function setNumMulti(counters) {
+  const bulkOps = counters.map(obj => {
+    const { name, num } = obj
+    return {
+      updateOne: {
+        filter: { name },
+        update: { name, num },
+        upsert: true
+      }
+    }
+  })
+
+  return Count.bulkWrite(bulkOps, { ordered : false })
+}
+
 module.exports = {
   getNum,
   getAll,
-  setNum
+  setNum,
+  setNumMulti
 }
diff --git a/db/sqlite.js b/db/sqlite.js
index 0912101..4b09dd1 100644
--- a/db/sqlite.js
+++ b/db/sqlite.js
@@ -1,11 +1,11 @@
 'use strict'
 
 const path = require('path')
-const sqlite3 = require('sqlite3')
+const Database = require('better-sqlite3')
 
-const db = new sqlite3.Database(path.resolve(__dirname, '../count.db'))
+const db = new Database(path.resolve(__dirname, '../count.db'))
 
-db.run(`CREATE TABLE IF NOT EXISTS tb_count (
+db.exec(`CREATE TABLE IF NOT EXISTS tb_count (
     id    INTEGER      PRIMARY KEY AUTOINCREMENT
                        NOT NULL
                        UNIQUE,
@@ -17,44 +17,53 @@ db.run(`CREATE TABLE IF NOT EXISTS tb_count (
 
 function getNum(name) {
   return new Promise((resolve, reject) => {
-    db.get('SELECT `name`, `num` from tb_count WHERE `name` = ?', name, (err, row) => {
-      if (err) reject(err)
-
-      resolve(row || { name, num: 0 })
-    })
+    const stmt = db.prepare('SELECT `name`, `num` from tb_count WHERE `name` = ?')
+    const row = stmt.get(name)
+    resolve(row || { name, num: 0 })
   })
 }
 
 function getAll(name) {
   return new Promise((resolve, reject) => {
-    db.get('SELECT * from tb_count', (err, row) => {
-      if (err) reject(err)
-
-      resolve(row)
-    })
+    const stmt = db.prepare('SELECT * from tb_count')
+    const rows = stmt.all()
+    resolve(rows)
   })
 }
 
 function setNum(name, num) {
   return new Promise((resolve, reject) => {
-    db.run(`INSERT INTO tb_count(\`name\`, \`num\`)
+    db.exec(`INSERT INTO tb_count(\`name\`, \`num\`)
             VALUES($name, $num)
             ON CONFLICT(name) DO
             UPDATE SET \`num\` = $num;`
-      , {
-        $name: name,
-        $num: num
-      }
-      , (err, row) => {
-        if (err) reject(err)
-
-        resolve(row)
-      })
+      ,
+      { $name: name, $num: num }
+    )
+
+    resolve()
+  })
+}
+
+function setNumMulti(counters) {
+  return new Promise((resolve, reject) => {
+    const stmt = db.prepare(`INSERT INTO tb_count(\`name\`, \`num\`)
+    VALUES($name, $num)
+    ON CONFLICT(name) DO
+    UPDATE SET \`num\` = $num;`)
+
+    const setMany = db.transaction((counters) => {
+      for (const counter of counters) stmt.run(counter)
+    })
+
+    setMany(counters)
+    resolve()
   })
 }
 
 module.exports = {
   getNum,
   getAll,
-  setNum
-}
\ No newline at end of file
+  setNum,
+  setNumMulti
+}
-- 
cgit v1.2.3