-
Notifications
You must be signed in to change notification settings - Fork 264
/
sqlite.ts
435 lines (397 loc) · 10.1 KB
/
sqlite.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
/**
* SQLite3 data access layer
* @category sqlite
* @module sqlite
*/
import { Database, verbose } from "sqlite3";
verbose();
import { unlink } from "fs/promises";
import {
sqlsanitize,
mkWhere,
mkSelectOptions,
} from "@saltcorn/db-common/internal";
import type {
Value,
Where,
SelectOptions,
Row,
} from "@saltcorn/db-common/internal";
import {
buildInsertSql,
mkVal,
doCount,
doDeleteWhere,
doListTables,
doListUserDefinedTables,
doListScTables,
do_add_index,
do_drop_index,
} from "@saltcorn/db-common/sqlite-commons";
let sqliteDatabase: Database | null = null;
let connectObj: any = null;
let current_filepath: string;
/**
* Initializes internals of the the sqlite module.
* It must be called after importing the module.
* @function
* @param {any} getConnectObject
*/
export const init = (getConnectObject: () => Database): void => {
if (!sqliteDatabase) {
connectObj = getConnectObject();
current_filepath = get_db_filepath();
sqliteDatabase = new Database(current_filepath);
}
};
/**
* Get sqlite path
* @function
* @returns {string|void}
*/
export const get_db_filepath = (): string => {
if (connectObj.sqlite_path) return connectObj.sqlite_path;
return "";
};
let log_sql_enabled = false;
/**
* Control Logging sql statements to console
* @param {boolean} [val = true] - if true then log sql statements to console
*/
export function set_sql_logging(val: boolean = true): void {
log_sql_enabled = val;
}
/**
* Get sql logging state
* @returns {boolean} if true then sql logging eabled
*/
export function get_sql_logging(): boolean {
return log_sql_enabled;
}
/**
* Log SQL statement to console
* @param {string} sql - SQL statement
* @param {any} [vs] - any additional parameter
*/
export function sql_log(sql: string, vs?: Value[]) {
if (log_sql_enabled)
if (typeof vs === "undefined") console.log(sql);
else console.log(sql, vs);
}
/**
* @param {string} sql
* @param {any} params
* @returns {Promise<object>}
*/
export function query(sql: string, params?: Value[]): Promise<any> {
sql_log(sql, params);
return new Promise((resolve, reject) => {
if (!sqliteDatabase) {
reject(new Error("The database connection is closed."));
return;
}
sqliteDatabase.all(sql, params, function (err: any, rows: any) {
if (err) {
reject(err);
} else {
resolve({ rows });
}
});
});
}
/**
* Change connection (close connection and open new connection from connObj)
* @param {any} connObj - connection object
* @returns {Promise<void>}
* @function
*/
export const changeConnection = async (connObj: any): Promise<void> => {
if (!sqliteDatabase) {
throw new Error("The database connection is closed.");
}
await sqliteDatabase.close();
current_filepath = connObj.sqlite_path;
sqliteDatabase = new Database(current_filepath);
};
/**
* start a transaction
* just an equivalent for the pg function
*/
export const begin = async () => {
await query(`BEGIN`);
};
/**
* commit a transaction
* just an equivalent for the pg function
*/
export const commit = async () => {
await query(`COMMIT`);
};
/**
* rollback a
* just an equivalent for the pg function
*/
export const rollback = async () => {
await query(`ROLLBACK`);
};
/**
* Close database connection
* @returns {Promise<void>}
* @function
*/
export const close = async (): Promise<void> => {
if (!sqliteDatabase) {
throw new Error("The database connection is closed.");
}
await sqliteDatabase.close();
sqliteDatabase = null;
};
/**
* Execute Select statement
* @param {string} tbl - table name
* @param {any} whereObj - where object
* @param {any} [selectopts = {}] - select options
* @returns {Promise<*>} return rows
* @function
*/
export const select = async (
tbl: string,
whereObj: Where,
selectopts: SelectOptions = {}
): Promise<Row[]> => {
const { where, values } = mkWhere(whereObj, true);
const sql = `SELECT * FROM "${sqlsanitize(tbl)}" ${where} ${mkSelectOptions(
selectopts,
values,
true
)}`;
const tq = await query(sql, values);
return tq.rows;
};
/**
*
* @returns
*/
export const listTables = async () => {
return await doListTables(query);
};
/**
*
* @returns
*/
export const listUserDefinedTables = async () => {
return await doListUserDefinedTables(query);
};
/**
*
* @returns
*/
export const listScTables = async () => {
return await doListScTables(query);
};
/**
*
* @param name
*/
export const dropTable = async (name: string) => {
await query(`DROP TABLE ${name}`);
};
/**
*
* @param tables
*/
export const dropTables = async (tables: string[]) => {
for (const table of tables) {
await dropTable(table);
}
};
// TODO Utility function - needs ti be moved out this module
/**
* Drop unique constraint
* @param {string} tbl - table name
* @param {any} obj - list of column=value pairs
* @param {string} id - primary key column value
* @returns {Promise<void>} no results
* @function
*/
export const update = async (
tbl: string,
obj: Row,
id: string | number
): Promise<void> => {
const kvs = Object.entries(obj);
if (kvs.length === 0) return;
const assigns = kvs.map(([k, v], ix) => `"${sqlsanitize(k)}"=?`).join();
let valList = kvs.map(mkVal);
valList.push(id);
const q = `update "${sqlsanitize(tbl)}" set ${assigns} where id=?`;
await query(q, valList);
};
export const updateWhere = async (
tbl: string,
obj: Row,
whereObj: Where
): Promise<void> => {
const kvs = Object.entries(obj);
if (kvs.length === 0) return;
const { where, values } = mkWhere(whereObj, true, kvs.length);
const assigns = kvs.map(([k, v], ix) => `"${sqlsanitize(k)}"=?`).join();
let valList = [...kvs.map(mkVal), ...values];
const q = `update "${sqlsanitize(tbl)}" set ${assigns} ${where}`;
await query(q, valList);
};
/**
* Delete rows in table
* @param {string} tbl - table name
* @param {any} whereObj - where object
* @returns {Promise<void>} result of delete execution
* @function
*/
export const deleteWhere = async (
tbl: string,
whereObj: Where
): Promise<void> => {
await doDeleteWhere(tbl, whereObj, query);
};
/**
* Insert rows into table
* @param {string} tbl - table name
* @param {any} obj - columns names and data
* @param {any} [opts = {}] - columns attributes
* @returns {Promise<string|void>} returns id.
* @function
*/
export const insert = async (
tbl: string,
obj: Row,
opts: { noid?: boolean; ignoreExisting?: boolean } = {}
): Promise<string | void> => {
const { sql, valList } = buildInsertSql(tbl, obj, opts);
await query(sql, valList);
if (opts.noid) return;
// TBD Support of primary key column different from id
const ids = await query("SELECT last_insert_rowid() as id");
return ids.rows[0].id;
};
/**
* Select one record
* @param {string} tbl - table name
* @param {any} where - where object
* @param {any} [selectopts = {}] - select options
* @throws {Error}
* @returns {Promise<any>} return first record from sql result
* @function
*/
export const selectOne = async (
tbl: string,
where: Where,
selectopts: SelectOptions = {}
): Promise<Row> => {
const rows = await select(tbl, where, { ...selectopts, limit: 1 });
if (rows.length === 0) {
const w = mkWhere(where, true);
throw new Error(`no ${tbl} ${w.where} are ${w.values}`);
} else return rows[0];
};
/**
* Select one record or null if no records
* @param {string} tbl - table name
* @param {any} where - where object
* @param {any} [selectopts = {}] - select options
* @returns {Promise<any>} - null if no record or first record data
* @function
*/
export const selectMaybeOne = async (
tbl: string,
where: Where,
selectopts: SelectOptions = {}
): Promise<Row | null> => {
const rows = await select(tbl, where, { ...selectopts, limit: 1 });
if (rows.length === 0) return null;
else return rows[0];
};
/**
* Get count of rows in table
* @param {string} tbl - table name
* @param {any} whereObj - where object
* @returns {Promise<number>} count of tables
* @function
*/
export const count = async (tbl: string, whereObj: Where) => {
return await doCount(tbl, whereObj, query);
};
/**
* Get version of PostgreSQL
* @returns {Promise<string>} returns version
* @function
*/
export const getVersion = async (): Promise<string> => {
const sql = `SELECT sqlite_version();`;
sql_log(sql);
const tq = await query(sql);
return tq.rows[0]["sqlite_version()"];
};
/**
* Reset DB Schema using drop schema and recreate it.
* Attention! You will lost data after call this function!
* @returns {Promise<void>} no result
* @function
*/
export const drop_reset_schema = async (): Promise<void> => {
if (!sqliteDatabase) {
throw new Error("The database connection is closed.");
}
await sqliteDatabase.close();
await unlink(current_filepath);
sqliteDatabase = new Database(current_filepath);
};
/**
* Add unique constraint
* @param table_name - table name
* @param field_names - list of columns (members of constraint)
* @returns no result
*/
export const add_unique_constraint = async (
table_name: string,
field_names: string[]
): Promise<void> => {
await do_add_index(table_name, field_names, query, true, sql_log);
};
/**
* Drop unique constraint
* @param table_name - table name
* @param field_names - list of columns (members of constraint)
* @returns no results
*/
export const drop_unique_constraint = async (
table_name: string,
field_names: string[]
): Promise<void> => {
await do_drop_index(table_name, field_names, query, true, sql_log);
};
/**
* Add index
* @param table_name - table name
* @param field_name - column name
* @returns no result
*/
export const add_index = async (
table_name: string,
field_name: string
): Promise<void> => {
await do_add_index(table_name, [field_name], query, false, sql_log);
};
/**
* Drop index
* @param table_name - table name
* @param field_name - column name
* @returns no results
*/
export const drop_index = async (
table_name: string,
field_name: string
): Promise<void> => {
await do_drop_index(table_name, [field_name], query, false, sql_log);
};
export const slugify = (s: string): string =>
s.toLowerCase().replace(/\s+/g, "-");