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');
9 const _fileScope
= common
.fileScope(__filename
);
11 const defaultOptions
= {
13 optimizeAfterChanges: 1000,
14 queryLogLevel: 'debug',
17 const EPOCH_NOW
= '(strftime(\'%s\', \'now\'))';
19 class SQLiteDatabase
extends BaseDatabase
{
20 constructor(logger
, options
) {
21 const _scope
= _fileScope('constructor');
24 common
.setOptions(this, defaultOptions
, options
);
26 this.dbFilename
= this.connectionString
.slice('sqlite://'.length
) || ':memory:';
28 this.logger
.debug(_scope
, 'connecting', { dbFilename: this.dbFilename
});
30 this.db
= new SQLite(this.dbFilename
, {
31 verbose: (query
) => this.queryLogLevel
&& this.logger
[this.queryLogLevel
](_fileScope('statement'), 'executing', { query
}),
34 this.changesSinceLastOptimize
= BigInt(0);
39 this._closeConnection();
43 this.db
.pragma('foreign_keys = on');
44 this.db
.pragma('journal_mode = WAL');
45 this.db
.defaultSafeIntegers(true);
48 this._initStatements();
52 const _scope
= _fileScope('_initTables');
53 const tableExists
= this.db
.prepare('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=:tableName').pluck(true);
59 `CREATE TABLE IF NOT EXISTS auth (
60 id TEXT NOT NULL PRIMARY KEY,
64 `INSERT INTO auth (id, secret, password)
66 ('foo', 'secret', 'quux')`,
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,
81 'CREATE INDEX IF NOT EXISTS link_url_idx ON link(url)',
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();
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'),
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);
120 if (this.changesSinceLastOptimize
>= this.optimizeAfterChanges
) {
127 this.changesSinceLastOptimize
+= BigInt(info
.changes
);
128 this._maybeOptimize();
131 changes: Number(info
.changes
),
132 lastInsertRowid: Number(info
.lastInsertRowid
),
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
],
145 const dbCtx
= this.db
;
146 return await
fn(dbCtx
);
149 async
transaction(dbCtx
, fn
, ...rest
) {
150 dbCtx
= dbCtx
|| this.db
;
151 return await dbCtx
.transaction(async (...args
) => await
fn(...args
))(dbCtx
, ...rest
);
154 async
getAuthById(dbCtx
, id
) {
155 const _scope
= _fileScope('getAuthById');
156 this.logger
.debug(_scope
, 'called', { id
});
159 auth
= this.statement
.getAuthById
.get({ id
});
160 auth
= SQLiteDatabase
._deOphidiate(auth
);
162 this.logger
.debug(_scope
, 'get', { auth
});
166 async
insertLink(dbCtx
, id
, url
, authToken
) {
167 const _scope
= _fileScope('insertLink');
168 this.logger
.debug(_scope
, 'called', { id
, url
});
172 info
= this.statement
.insertLink
.run({ id
, url
, authToken
});
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
});
183 this.logger
.error(_scope
, 'failed to insert link', { error: e
, id
, url
});
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();
194 return this._sqliteInfo(info
);
197 static _linkToNative(link
) {
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
,
209 async
getLinkById(dbCtx
, id
) {
210 const _scope
= _fileScope('getLinkById');
211 this.logger
.debug(_scope
, 'called', { id
});
213 let link
= this.statement
.getLinkById
.get({ id
});
214 link
= SQLiteDatabase
._deOphidiate(link
);
216 this.logger
.debug(_scope
, 'get', { link
});
217 return SQLiteDatabase
._linkToNative(link
);
220 async
getLinkByUrl(dbCtx
, url
) {
221 const _scope
= _fileScope('getLinkByUrl');
222 this.logger
.debug(_scope
, 'called', { url
});
224 let link
= this.statement
.getLinkByUrl
.get({ url
});
225 link
= SQLiteDatabase
._deOphidiate(link
);
227 this.logger
.debug(_scope
, 'get', { link
});
228 return SQLiteDatabase
._linkToNative(link
);
231 async
accessLink(dbCtx
, id
) {
232 const _scope
= _fileScope('accessLink');
233 this.logger
.debug(_scope
, 'called', { id
});
235 let link
= this.statement
.getLinkById
.get({ id
});
236 link
= SQLiteDatabase
._deOphidiate(link
);
238 this.logger
.debug(_scope
, 'get', { id
, 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();
248 return SQLiteDatabase
._linkToNative(link
);
251 async
expireLink(dbCtx
, id
, expires
) {
252 const _scope
= _fileScope('expireLink');
253 this.logger
.debug(_scope
, 'called', { id
});
255 const info
= this.statement
.expireLink
.run({ id
, expires
});
256 if (info
.changes
!= 1) {
257 throw new DBErrors
.UnexpectedResult();
259 return this._sqliteInfo(info
);
262 async
updateLink(dbCtx
, id
, url
) {
263 const _scope
= _fileScope('updateLink');
264 this.logger
.debug(_scope
, 'called', { id
, url
});
266 const info
= this.statement
.updateLink
.run({ id
, url
});
267 if (info
.changes
!= 1) {
268 throw new DBErrors
.UnexpectedResult();
270 return this._sqliteInfo(info
);
273 // eslint-disable-next-line no-unused-vars
274 async
getAllLinks(dbCtx
) {
275 const _scope
= _fileScope('getAllLinks');
276 this.logger
.debug(_scope
, 'called', { });
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
));
288 module
.exports
= SQLiteDatabase
;