123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436 |
- <!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 fs = require("fs-extra");
- 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 && !cls.isSlowQueryLoggingDisabled()) {
- 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 entityChangeIds = cls.getAndClearEntityChangeIds();
- if (entityChangeIds.length > 0) {
- log.info("Transaction rollback dirtied the becca, forcing reload.");
- require('../becca/becca_loader').load();
- }
- // the maxEntityChangeId has been incremented during failed transaction, need to recalculate
- require('./entity_changes').recalculateMaxEntityChangeId();
- 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(',')}`);
- s.run(paramIds);
- }
- async function copyDatabase(targetFilePath) {
- try {
- fs.unlinkSync(targetFilePath);
- } catch (e) {
- } // unlink throws exception if the file did not exist
- await dbConnection.backup(targetFilePath);
- }
- function disableSlowQueryLogging(cb) {
- const orig = cls.isSlowQueryLoggingDisabled();
- try {
- cls.disableSlowQueryLogging(true);
- return cb();
- }
- finally {
- cls.disableSlowQueryLogging(orig);
- }
- }
- 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
- * @returns [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
- * @returns {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
- * @returns {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
- * @returns {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
- * @returns {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,
- copyDatabase,
- disableSlowQueryLogging
- };
- </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="AbstractBeccaEntity.html">AbstractBeccaEntity</a></li><li><a href="BAttachment.html">BAttachment</a></li><li><a href="BAttribute.html">BAttribute</a></li><li><a href="BBranch.html">BBranch</a></li><li><a href="BEtapiToken.html">BEtapiToken</a></li><li><a href="BNote.html">BNote</a></li><li><a href="BOption.html">BOption</a></li><li><a href="BRecentNote.html">BRecentNote</a></li><li><a href="BRevision.html">BRevision</a></li><li><a href="BackendScriptApi.html">BackendScriptApi</a></li></ul><h3>Global</h3><ul><li><a href="global.html#api">api</a></li></ul>
- </nav>
- <br class="clear">
- <footer>
- Documentation generated by <a href="https://github.com/jsdoc/jsdoc">JSDoc 4.0.2</a>
- </footer>
- <script> prettyPrint(); </script>
- <script src="scripts/linenumber.js"> </script>
- </body>
- </html>
|