123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402 |
- <!DOCTYPE html>
- <html lang="en">
- <head>
- <meta charset="utf-8">
- <title>JSDoc: Source: services/sql.js</title>
- <script src="scripts/prettify/prettify.js"> </script>
- <script src="scripts/prettify/lang-css.js"> </script>
- <!--[if lt IE 9]>
- <script src="//html5shiv.googlecode.com/svn/trunk/html5.js"></script>
- <![endif]-->
- <link type="text/css" rel="stylesheet" href="styles/prettify-tomorrow.css">
- <link type="text/css" rel="stylesheet" href="styles/jsdoc-default.css">
- </head>
- <body>
- <div id="main">
- <h1 class="page-title">Source: services/sql.js</h1>
-
-
- <section>
- <article>
- <pre class="prettyprint source linenums"><code>"use strict";
- /**
- * @module sql
- */
- const log = require('./log');
- const Database = require('better-sqlite3');
- const dataDir = require('./data_dir');
- const cls = require('./cls');
- const dbConnection = new Database(dataDir.DOCUMENT_PATH);
- dbConnection.pragma('journal_mode = WAL');
- const LOG_ALL_QUERIES = false;
- [`exit`, `SIGINT`, `SIGUSR1`, `SIGUSR2`, `SIGTERM`].forEach(eventType => {
- process.on(eventType, () => {
- if (dbConnection) {
- // closing connection is especially important to fold -wal file into the main DB file
- // (see https://sqlite.org/tempfiles.html for details)
- dbConnection.close();
- }
- });
- });
- function insert(tableName, rec, replace = false) {
- const keys = Object.keys(rec);
- if (keys.length === 0) {
- log.error("Can't insert empty object into table " + tableName);
- return;
- }
- const columns = keys.join(", ");
- const questionMarks = keys.map(p => "?").join(", ");
- const query = "INSERT " + (replace ? "OR REPLACE" : "") + " INTO " + tableName + "(" + columns + ") VALUES (" + questionMarks + ")";
- const res = execute(query, Object.values(rec));
- return res ? res.lastInsertRowid : null;
- }
- function replace(tableName, rec) {
- return insert(tableName, rec, true);
- }
- function upsert(tableName, primaryKey, rec) {
- const keys = Object.keys(rec);
- if (keys.length === 0) {
- log.error("Can't upsert empty object into table " + tableName);
- return;
- }
- const columns = keys.join(", ");
- const questionMarks = keys.map(colName => "@" + colName).join(", ");
- const updateMarks = keys.map(colName => `${colName} = @${colName}`).join(", ");
- const query = `INSERT INTO ${tableName} (${columns}) VALUES (${questionMarks})
- ON CONFLICT (${primaryKey}) DO UPDATE SET ${updateMarks}`;
- for (const idx in rec) {
- if (rec[idx] === true || rec[idx] === false) {
- rec[idx] = rec[idx] ? 1 : 0;
- }
- }
- execute(query, rec);
- }
- const statementCache = {};
- function stmt(sql) {
- if (!(sql in statementCache)) {
- statementCache[sql] = dbConnection.prepare(sql);
- }
- return statementCache[sql];
- }
- function getRow(query, params = []) {
- return wrap(query, s => s.get(params));
- }
- function getRowOrNull(query, params = []) {
- const all = getRows(query, params);
- return all.length > 0 ? all[0] : null;
- }
- function getValue(query, params = []) {
- return wrap(query, s => s.pluck().get(params));
- }
- // smaller values can result in better performance due to better usage of statement cache
- const PARAM_LIMIT = 100;
- function getManyRows(query, params) {
- let results = [];
- while (params.length > 0) {
- const curParams = params.slice(0, Math.min(params.length, PARAM_LIMIT));
- params = params.slice(curParams.length);
- const curParamsObj = {};
- let j = 1;
- for (const param of curParams) {
- curParamsObj['param' + j++] = param;
- }
- let i = 1;
- const questionMarks = curParams.map(() => ":param" + i++).join(",");
- const curQuery = query.replace(/\?\?\?/g, questionMarks);
- const statement = curParams.length === PARAM_LIMIT
- ? stmt(curQuery)
- : dbConnection.prepare(curQuery);
- const subResults = statement.all(curParamsObj);
- results = results.concat(subResults);
- }
- return results;
- }
- function getRows(query, params = []) {
- return wrap(query, s => s.all(params));
- }
- function getRawRows(query, params = []) {
- return wrap(query, s => s.raw().all(params));
- }
- function iterateRows(query, params = []) {
- if (LOG_ALL_QUERIES) {
- console.log(query);
- }
- return stmt(query).iterate(params);
- }
- function getMap(query, params = []) {
- const map = {};
- const results = getRawRows(query, params);
- for (const row of results) {
- map[row[0]] = row[1];
- }
- return map;
- }
- function getColumn(query, params = []) {
- return wrap(query, s => s.pluck().all(params));
- }
- function execute(query, params = []) {
- return wrap(query, s => s.run(params));
- }
- function executeMany(query, params) {
- if (LOG_ALL_QUERIES) {
- console.log(query);
- }
- while (params.length > 0) {
- const curParams = params.slice(0, Math.min(params.length, PARAM_LIMIT));
- params = params.slice(curParams.length);
- const curParamsObj = {};
- let j = 1;
- for (const param of curParams) {
- curParamsObj['param' + j++] = param;
- }
- let i = 1;
- const questionMarks = curParams.map(() => ":param" + i++).join(",");
- const curQuery = query.replace(/\?\?\?/g, questionMarks);
- dbConnection.prepare(curQuery).run(curParamsObj);
- }
- }
- function executeScript(query) {
- if (LOG_ALL_QUERIES) {
- console.log(query);
- }
- return dbConnection.exec(query);
- }
- function wrap(query, func) {
- const startTimestamp = Date.now();
- let result;
- if (LOG_ALL_QUERIES) {
- console.log(query);
- }
- try {
- result = func(stmt(query));
- }
- catch (e) {
- if (e.message.includes("The database connection is not open")) {
- // this often happens on killing the app which puts these alerts in front of user
- // in these cases error should be simply ignored.
- console.log(e.message);
- return null
- }
- throw e;
- }
- const milliseconds = Date.now() - startTimestamp;
- if (milliseconds >= 20) {
- if (query.includes("WITH RECURSIVE")) {
- log.info(`Slow recursive query took ${milliseconds}ms.`);
- }
- else {
- log.info(`Slow query took ${milliseconds}ms: ${query.trim().replace(/\s+/g, " ")}`);
- }
- }
- return result;
- }
- function transactional(func) {
- try {
- const ret = dbConnection.transaction(func).deferred();
- if (!dbConnection.inTransaction) { // i.e. transaction was really committed (and not just savepoint released)
- require('./ws').sendTransactionEntityChangesToAllClients();
- }
- return ret;
- }
- catch (e) {
- const entityChanges = cls.getAndClearEntityChangeIds();
- if (entityChanges.length > 0) {
- log.info("Transaction rollback dirtied the becca, forcing reload.");
- require('../becca/becca_loader').load();
- }
- throw e;
- }
- }
- function fillParamList(paramIds, truncate = true) {
- if (paramIds.length === 0) {
- return;
- }
- if (truncate) {
- execute("DELETE FROM param_list");
- }
- paramIds = Array.from(new Set(paramIds));
- if (paramIds.length > 30000) {
- fillParamList(paramIds.slice(30000), false);
- paramIds = paramIds.slice(0, 30000);
- }
- // doing it manually to avoid this showing up on the sloq query list
- const s = stmt(`INSERT INTO param_list VALUES ` + paramIds.map(paramId => `(?)`).join(','), paramIds);
- s.run(paramIds);
- }
- module.exports = {
- dbConnection,
- insert,
- replace,
- /**
- * Get single value from the given query - first column from first returned row.
- *
- * @method
- * @param {string} query - SQL query with ? used as parameter placeholder
- * @param {object[]} [params] - array of params if needed
- * @return [object] - single value
- */
- getValue,
- /**
- * Get first returned row.
- *
- * @method
- * @param {string} query - SQL query with ? used as parameter placeholder
- * @param {object[]} [params] - array of params if needed
- * @return {object} - map of column name to column value
- */
- getRow,
- getRowOrNull,
- /**
- * Get all returned rows.
- *
- * @method
- * @param {string} query - SQL query with ? used as parameter placeholder
- * @param {object[]} [params] - array of params if needed
- * @return {object[]} - array of all rows, each row is a map of column name to column value
- */
- getRows,
- getRawRows,
- iterateRows,
- getManyRows,
- /**
- * Get a map of first column mapping to second column.
- *
- * @method
- * @param {string} query - SQL query with ? used as parameter placeholder
- * @param {object[]} [params] - array of params if needed
- * @return {object} - map of first column to second column
- */
- getMap,
- /**
- * Get a first column in an array.
- *
- * @method
- * @param {string} query - SQL query with ? used as parameter placeholder
- * @param {object[]} [params] - array of params if needed
- * @return {object[]} - array of first column of all returned rows
- */
- getColumn,
- /**
- * Execute SQL
- *
- * @method
- * @param {string} query - SQL query with ? used as parameter placeholder
- * @param {object[]} [params] - array of params if needed
- */
- execute,
- executeMany,
- executeScript,
- transactional,
- upsert,
- fillParamList
- };
- </code></pre>
- </article>
- </section>
- </div>
- <nav>
- <h2><a href="index.html">Home</a></h2><h3>Modules</h3><ul><li><a href="module-sql.html">sql</a></li></ul><h3>Classes</h3><ul><li><a href="Attribute.html">Attribute</a></li><li><a href="BackendScriptApi.html">BackendScriptApi</a></li><li><a href="Branch.html">Branch</a></li><li><a href="EtapiToken.html">EtapiToken</a></li><li><a href="Note.html">Note</a></li><li><a href="NoteRevision.html">NoteRevision</a></li><li><a href="Option.html">Option</a></li><li><a href="RecentNote.html">RecentNote</a></li></ul><h3><a href="global.html">Global</a></h3>
- </nav>
- <br class="clear">
- <footer>
- Documentation generated by <a href="https://github.com/jsdoc/jsdoc">JSDoc 3.6.7</a>
- </footer>
- <script> prettyPrint(); </script>
- <script src="scripts/linenumber.js"> </script>
- </body>
- </html>
|