· 7 years ago · Oct 04, 2018, 10:52 PM
1//@ts-check
2'use strict';
3
4/** @typedef {import('sqlite3').Database} Database */
5/** @typedef {import('sqlite3').Statement} Statement */
6
7const VERBOSE = false;
8
9const sqlite = VERBOSE ? require('sqlite3').verbose() : require('sqlite3');
10const fs = require('fs');
11
12const dbFile = 'test.sqlite';
13
14/**
15 * @param {string} path
16 *
17 * @returns {Promise<Database>}
18 */
19function openDB(path) {
20 return new Promise((resolve, reject) => {
21 const db = new sqlite.Database(dbFile, error => {
22 if (error) {
23 return reject(error);
24 }
25
26 // Re-try for up to 5s if the DB is busy
27 db.configure('busyTimeout', 5000);
28
29 // Check for errors
30 db.addListener('error', error => console.error(error));
31
32 resolve(db);
33 });
34 });
35}
36
37/**
38 * @param {Database} db
39 *
40 * @returns {Promise<void>}
41 */
42function ensureTables(db) {
43
44 // UNIQUE: ensures that all values in a column are different
45 // ON CONFLICT REPLACE: the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior
46 // to inserting or updating the current row and the command continues executing normally
47 // ON CONFLICT FAIL: when an applicable constraint violation occurs, the FAIL resolution algorithm aborts the current
48 // SQL statement with an SQLITE_CONSTRAINT error
49 // BLOB: The value is a blob of data, stored exactly as it was input.
50 // NOT NULL: Value can not be NULL
51 return exec(db, 'CREATE TABLE IF NOT EXISTS GlobalItemTable (key TEXT UNIQUE ON CONFLICT REPLACE, value BLOB NOT NULL ON CONFLICT FAIL)');
52}
53
54/**
55 * @param {Database} db
56 * @param {string} sql
57 *
58 * @returns {Promise<void>}
59 */
60function exec(db, sql) {
61 return new Promise((resolve, reject) => {
62 db.exec(sql, error => {
63 if (error) {
64 return reject(error);
65 }
66
67 resolve();
68 });
69 });
70}
71
72/**
73 * @param {Database} db
74 * @param {string} sql
75 * @param {(row) => void} callback
76 *
77 * @returns {Promise<void>}
78 */
79function each(db, sql, callback) {
80 return new Promise((resolve, reject) => {
81 db.each(sql, (error, row) => {
82 if (error) {
83 return reject(error);
84 }
85
86 callback(row);
87 }, error => {
88 if (error) {
89 return reject(error);
90 }
91
92 resolve();
93 });
94 });
95}
96
97/**
98 * @param {Database} db
99 * @param {Error?} error
100 *
101 * @returns {Promise<void>}
102 */
103function closeDB(db, error) {
104 if (error) {
105 console.error(error);
106 }
107
108 // Close DB
109 return new Promise((resolve, reject) => {
110 db.close(error => {
111 if (error) {
112 return reject(error);
113 }
114
115 resolve();
116 });
117 });
118}
119
120/**
121 * @param {Database} db
122 * @param {() => void} transactions
123 *
124 * @returns {Promise<void>}
125 */
126function transaction(db, transactions) {
127 return new Promise((resolve, reject) => {
128 db.serialize(() => {
129 db.exec('BEGIN TRANSACTION');
130
131 transactions();
132
133 db.exec('END TRANSACTION', error => {
134 if (error) {
135 return reject(error);
136 }
137
138 resolve();
139 });
140 });
141 });
142}
143
144/**
145 * @param {Database} db
146 * @param {Map<string, string>} keyValueMap
147 */
148function setItems(db, keyValueMap) {
149 return transaction(db, () => {
150 const stmt = db.prepare('INSERT INTO GlobalItemTable VALUES (?,?)');
151
152 keyValueMap.forEach((value, key) => {
153 stmt.run([key, value]);
154 });
155
156 stmt.finalize();
157 });
158}
159
160/**
161 * @param {Database} db
162 * @param {string[]} keys
163 */
164async function deleteItems(db, keys) {
165 return transaction(db, () => {
166 const stmt = db.prepare('DELETE FROM GlobalItemTable WHERE key=?');
167
168 keys.forEach(key => {
169 stmt.run(key);
170 });
171
172 stmt.finalize();
173 });
174}
175
176/**
177 * @param {Database} db
178 *
179 * @returns {Promise<Map<string, string>>}
180 */
181async function getItems(db) {
182 const items = new Map();
183
184 await each(db, 'SELECT * FROM GlobalItemTable', row => {
185 items.set(row.key, row.value);
186 });
187
188 return items;
189}
190
191class Perf {
192 constructor() {
193 this._now = Date.now();
194 }
195
196 measure(msg) {
197 const now = Date.now();
198
199 console.log(`${msg}: ${now - this._now}ms`);
200
201 this._now = now;
202 }
203}
204
205async function main() {
206 if (fs.existsSync(dbFile)) {
207 const perf = new Perf();
208 const db = await openDB(dbFile);
209 perf.measure('open existing DB');
210 await closeDB(db, null);
211
212 fs.unlinkSync(dbFile);
213 }
214
215 const map = new Map();
216 const keys = [];
217 const data = JSON.parse(fs.readFileSync('data-5MB.txt').toString());
218 Object.keys(data).forEach(key => {
219 map.set(key, data[key]);
220 keys.push(key);
221 });
222
223 const perf = new Perf();
224
225 const db = await openDB(dbFile);
226 perf.measure('openDB');
227
228 await ensureTables(db);
229 perf.measure('ensureTables');
230
231 await setItems(db, map);
232 perf.measure('setItems');
233
234 const items = await getItems(db);
235 perf.measure(`getItems (found ${items.size} items, expected ${map.size})`);
236
237 deleteItems(db, keys);
238 perf.measure('deleteItems');
239
240 await closeDB(db, null);
241 perf.measure('closeDB');
242}
243
244main().then(null, console.error);