initial commit
[urlittler] / src / db / sqlite / index.js
1 'use strict';
2
3 const SQLite = require('better-sqlite3');
4 // XXX: const NodeCleanup = require('node-cleanup');
5 const BaseDatabase = require('../base');
6 const common = require('../../common');
7 const DBErrors = require('../errors');
8
9 const _fileScope = common.fileScope(__filename);
10
11 const defaultOptions = {
12 connectionString: '',
13 optimizeAfterChanges: 1000,
14 queryLogLevel: 'debug',
15 };
16
17 const EPOCH_NOW = '(strftime(\'%s\', \'now\'))';
18
19 class SQLiteDatabase extends BaseDatabase {
20 constructor(logger, options) {
21 const _scope = _fileScope('constructor');
22
23 super(logger);
24 common.setOptions(this, defaultOptions, options);
25
26 this.dbFilename = this.connectionString.slice('sqlite://'.length) || ':memory:';
27
28 this.logger.debug(_scope, 'connecting', { dbFilename: this.dbFilename });
29
30 this.db = new SQLite(this.dbFilename, {
31 verbose: (query) => this.queryLogLevel && this.logger[this.queryLogLevel](_fileScope('statement'), 'executing', { query }),
32 });
33
34 this.changesSinceLastOptimize = BigInt(0);
35
36 /*
37 NodeCleanup(() => {
38 this._optimize();
39 this._closeConnection();
40 });
41 */
42
43 this.db.pragma('foreign_keys = on');
44 this.db.pragma('journal_mode = WAL');
45 this.db.defaultSafeIntegers(true);
46
47 this._initTables();
48 this._initStatements();
49 }
50
51 _initTables() {
52 const _scope = _fileScope('_initTables');
53 const tableExists = this.db.prepare('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=:tableName').pluck(true);
54
55 const tables = [
56 {
57 name: 'auth',
58 statements: [
59 `CREATE TABLE IF NOT EXISTS auth (
60 id TEXT NOT NULL PRIMARY KEY,
61 secret TEXT NOT NULL,
62 password TEXT
63 )`,
64 `INSERT INTO auth (id, secret, password)
65 VALUES
66 ('foo', 'secret', 'quux')`,
67 ],
68 },
69 {
70 name: 'link',
71 statements: [
72 `CREATE TABLE IF NOT EXISTS link (
73 id TEXT NOT NULL PRIMARY KEY,
74 url TEXT NOT NULL UNIQUE,
75 created INTEGER NOT NULL DEFAULT ${EPOCH_NOW},
76 last_access INTEGER NOT NULL DEFAULT 0,
77 accesses INTEGER NOT NULL DEFAULT 0,
78 expires INTEGER,
79 auth_token TEXT
80 )`,
81 'CREATE INDEX IF NOT EXISTS link_url_idx ON link(url)',
82 ],
83 },
84 ];
85
86 tables.forEach((t) => {
87 const table = tableExists.get({ tableName: t.name });
88 if (table === undefined) {
89 t.statements.forEach((s) => {
90 this.logger.info(_scope, 'creating table', { tableName: t.name });
91 this.db.prepare(s).run();
92 });
93 }
94 });
95 }
96
97 _initStatements() {
98 this.statement = {
99 getAuthById: this.db.prepare('SELECT * FROM auth WHERE id = :id'),
100 getLinkById: this.db.prepare('SELECT * FROM link WHERE id = :id'),
101 getLinkByUrl: this.db.prepare('SELECT * FROM link WHERE url = :url'),
102 insertLink: this.db.prepare('INSERT INTO link (id, url, auth_token) VALUES (:id, :url, :authToken)'),
103 updateLink: this.db.prepare('UPDATE link SET url = :url WHERE id = :id'),
104 incrementLinkAccess: this.db.prepare(`UPDATE link SET accesses = accesses + 1, last_access = ${EPOCH_NOW} WHERE id = :id`),
105 expireLink: this.db.prepare('UPDATE link SET expires = :expires WHERE id = :id'),
106 linkGetAll: this.db.prepare('SELECT * FROM link'),
107 };
108 }
109
110 _optimize() {
111 const _scope = _fileScope('_optimize');
112 this.logger.debug(_scope, 'called', {});
113 const optimizations = this.db.prepare('SELECT * FROM pragma_optimize(0x03)').all();
114 this.logger.debug(_scope, 'pragma preview', { optimizations });
115 this.db.pragma('optimize');
116 this.changesSinceLastOptimize = BigInt(0);
117 }
118
119 _maybeOptimize() {
120 if (this.changesSinceLastOptimize >= this.optimizeAfterChanges) {
121 this._optimize();
122 }
123 }
124
125 _sqliteInfo(info) {
126 if (info.changes) {
127 this.changesSinceLastOptimize += BigInt(info.changes);
128 this._maybeOptimize();
129 }
130 return {
131 changes: Number(info.changes),
132 lastInsertRowid: Number(info.lastInsertRowid),
133 };
134 }
135
136 static _deOphidiate(row) {
137 return row && Object.keys(row).reduce((snakelessRow, k) => Object.assign(snakelessRow, {
138 // eslint-disable-next-line security/detect-object-injection
139 [BaseDatabase._camelfy(k)]: row[k],
140 }), {});
141 }
142
143
144 async context(fn) {
145 const dbCtx = this.db;
146 return await fn(dbCtx);
147 }
148
149 async transaction(dbCtx, fn, ...rest) {
150 dbCtx = dbCtx || this.db;
151 return await dbCtx.transaction(async (...args) => await fn(...args))(dbCtx, ...rest);
152 }
153
154 async getAuthById(dbCtx, id) {
155 const _scope = _fileScope('getAuthById');
156 this.logger.debug(_scope, 'called', { id });
157
158 let auth;
159 auth = this.statement.getAuthById.get({ id });
160 auth = SQLiteDatabase._deOphidiate(auth);
161
162 this.logger.debug(_scope, 'get', { auth });
163 return auth;
164 }
165
166 async insertLink(dbCtx, id, url, authToken) {
167 const _scope = _fileScope('insertLink');
168 this.logger.debug(_scope, 'called', { id, url });
169
170 let info;
171 try {
172 info = this.statement.insertLink.run({ id, url, authToken });
173 } catch (e) {
174 switch (e.code) {
175 case 'SQLITE_CONSTRAINT_UNIQUE':
176 case 'SQLITE_CONSTRAINT_PRIMARYKEY': {
177 this.logger.debug(_scope, 'updating existing id', { id, url });
178 info = this.statement.updateLink.run({ id, url });
179 break;
180 }
181
182 default: {
183 this.logger.error(_scope, 'failed to insert link', { error: e, id, url });
184 throw e;
185 }
186 }
187 }
188 this.logger.debug(_scope, 'run', { info });
189 if (info.changes != 1) {
190 this.logger.error(_scope, 'failed to insert link', { id, url, info });
191 throw new DBErrors.UnexpectedResult();
192 }
193
194 return this._sqliteInfo(info);
195 }
196
197 static _linkToNative(link) {
198 return link && {
199 id: link.id,
200 url: link.url,
201 created: Number(link.created),
202 lastAccess: Number(link.lastAccess),
203 accesses: Number(link.accesses),
204 expires: ('expires' in link) ? Number(link.expires) : undefined,
205 authToken: link.authToken,
206 };
207 }
208
209 async getLinkById(dbCtx, id) {
210 const _scope = _fileScope('getLinkById');
211 this.logger.debug(_scope, 'called', { id });
212
213 let link = this.statement.getLinkById.get({ id });
214 link = SQLiteDatabase._deOphidiate(link);
215
216 this.logger.debug(_scope, 'get', { link });
217 return SQLiteDatabase._linkToNative(link);
218 }
219
220 async getLinkByUrl(dbCtx, url) {
221 const _scope = _fileScope('getLinkByUrl');
222 this.logger.debug(_scope, 'called', { url });
223
224 let link = this.statement.getLinkByUrl.get({ url });
225 link = SQLiteDatabase._deOphidiate(link);
226
227 this.logger.debug(_scope, 'get', { link });
228 return SQLiteDatabase._linkToNative(link);
229 }
230
231 async accessLink(dbCtx, id) {
232 const _scope = _fileScope('accessLink');
233 this.logger.debug(_scope, 'called', { id });
234
235 let link = this.statement.getLinkById.get({ id });
236 link = SQLiteDatabase._deOphidiate(link);
237
238 this.logger.debug(_scope, 'get', { id, link });
239
240 if (link) {
241 const info = this.statement.incrementLinkAccess.run({ id });
242 this.logger.debug(_scope, 'increment', { id, info });
243 if (info.changes != 1) {
244 this.logger.error(_scope, 'failed to increment link access', { id, link, info });
245 throw new DBErrors.UnexpectedResult();
246 }
247 }
248 return SQLiteDatabase._linkToNative(link);
249 }
250
251 async expireLink(dbCtx, id, expires) {
252 const _scope = _fileScope('expireLink');
253 this.logger.debug(_scope, 'called', { id });
254
255 const info = this.statement.expireLink.run({ id, expires });
256 if (info.changes != 1) {
257 throw new DBErrors.UnexpectedResult();
258 }
259 return this._sqliteInfo(info);
260 }
261
262 async updateLink(dbCtx, id, url) {
263 const _scope = _fileScope('updateLink');
264 this.logger.debug(_scope, 'called', { id, url });
265
266 const info = this.statement.updateLink.run({ id, url });
267 if (info.changes != 1) {
268 throw new DBErrors.UnexpectedResult();
269 }
270 return this._sqliteInfo(info);
271 }
272
273 // eslint-disable-next-line no-unused-vars
274 async getAllLinks(dbCtx) {
275 const _scope = _fileScope('getAllLinks');
276 this.logger.debug(_scope, 'called', { });
277
278 let links;
279 links = this.statement.linkGetAll.all({});
280 links = links.map((l) => SQLiteDatabase._deOphidiate(l));
281 this.logger.debug(_scope, 'get', { links });
282 links = links.map((l) => SQLiteDatabase._linkToNative(l));
283 return links;
284 }
285
286 }
287
288 module.exports = SQLiteDatabase;