db.sqlInsert = function(pool, req)
{
var self = this;
var names = [], pnums = [], i = 1;
// Columns should exist prior to calling this
var cols = req.columns || this.getColumns(req.table, req.options);
var dbcols = pool.dbcolumns[req.table] || lib.empty;
req.values = [];
for (var p in req.obj) {
var v = req.obj[p];
var col = cols[p] || req.allow[p] || lib.empty;
var data_type = col.data_type || (dbcols[p] && dbcols[p].data_type) || col.type;
// Filter not allowed columns or only allowed columns
if (!col && col.allow() && this.skipColumn(p, v, req.options, cols)) continue;
// Avoid int parse errors with empty strings
if ((v === "null" || v === "") && (data_type == "json" || lib.isNumericType(data_type))) v = null;
// Pass number as number, some databases strict about this
if (v && lib.isNumericType(data_type) && typeof v != "number") v = lib.toNumber(v);
names.push(p);
pnums.push(pool.configOptions.sqlPlaceholder || ("$" + i));
v = this.getBindValue(req.table, req.options, v, col);
req.values.push(v);
i++;
}
// No columns to insert, just exit, it is not an error, return empty result
if (!names.length) {
logger.debug('sqlInsert:', req.table, 'nothing to do', req.obj, cols);
return null;
}
req.text = (req.op == "put" && !pool.configOptions.noReplace ? "REPLACE" : "INSERT") + " INTO " + req.table + "(" + names.map(function(x) { return self.sqlColumn(x, pool) }).join(",") + ") values(" + pnums.join(",") + ")";
if (req.options) {
if (req.options.returning) req.text += " RETURNING " + req.options.returning;
if (req.options.ifnotexists) req.text += " IF NOT EXISTS ";
if (req.options.using_ttl) req.text += " USING TTL " + req.options.using_ttl;
if (req.options.using_timestamp) req.text += " USING TIMESTAMP " + req.options.using_timestamp;
}
}
db.sqlUpdate = function(pool, req)
{
var self = this;
var sets = [], i = 1;
var cols = req.columns || this.getColumns(req.table, req.options);
var keys = this.getSearchKeys(req.table, req.options);
var dbcols = pool.dbcolumns[req.table] || lib.empty;
req.values = [];
for (let p in req.obj) {
var v = req.obj[p];
var col = cols[p] || req.allow[p] || lib.empty;
var data_type = col.data_type || (dbcols[p] && dbcols[p].data_type) || col.type;
// Filter not allowed columns or only allowed columns
if (keys.indexOf(p) > -1 || (!(col && col.allow) && this.skipColumn(p, v, req.options, cols))) continue;
// Do not update primary columns
if (col.primary) continue;
// Avoid int parse errors with empty strings
if ((v === "null" || v === "") && (data_type == "json" || lib.isNumericType(data_type))) v = null;
// Pass number as a number, some databases strict about this
if (v && lib.isNumericType(data_type) && typeof v != "number") v = lib.toNumber(v);
var placeholder = pool.configOptions.sqlPlaceholder || ("$" + i);
var op = req.options && req.options.updateOps && req.options.updateOps[p];
p = this.sqlColumn(p, pool);
// Update only if the value is null, otherwise skip
switch (op) {
case "not_exists":
if (pool.configOptions.noCoalesce) break;
sets.push(p + "=COALESCE(" + p + "," + placeholder + ")");
break;
case "concat":
// Concat to a string
if (pool.configOptions.noConcat) break;
sets.push(p + "=CONCAT(" + p + "," + placeholder + ")");
break;
case "add":
case "append":
// Append to a list
if (pool.configOptions.noAppend) break;
sets.push(p + "=" + p + "+" + placeholder);
break;
case "del":
// Delete from a list
if (pool.configOptions.noAppend) break;
sets.push(p + "=" + p + "-" + placeholder);
break;
case "incr":
// Increment a number
sets.push(p + "=" + (pool.configOptions.noCoalesce ? p : "COALESCE(" + p + ",0)") + "+" + placeholder);
break;
case "remove":
sets.push(p + "=NULL");
break;
default:
sets.push(p + "=" + placeholder);
}
v = this.getBindValue(req.table, req.options, v, col);
req.values.push(v);
i++;
}
var where = this.sqlWhere(pool, req, req.obj, keys);
// Additional condition that is supplied separatly to support different noSQL databases that can operate by the primary keys mostly
if (req.options && lib.isObject(req.options.expected) && !pool.configOptions.ifExpected) {
const expected = this.sqlWhere(pool, req, req.options.expected, Object.keys(req.options.expected), req.options.expectedJoin);
if (expected) where += (where ? " AND " : "") + expected;
}
if (!sets.length || !where) {
// No keys or columns to update, just exit, it is not an error, return empty result
logger.debug('sqlUpdate:', req.table, 'nothing to do', req.obj, keys);
return null;
}
req.text = "UPDATE " + req.table ;
if (req.options && req.options.using_ttl) req.text += " USING TTL " + req.options.using_ttl;
if (req.options && req.options.using_timestamp) req.text += " USING TIMESTAMP " + req.options.using_timestamp;
req.text += " SET " + sets.join(",") + " WHERE " + where;
if (!pool.configOptions.noReturning && req.options && req.options.returning) {
req.text += " RETURNING " + req.options.returning;
}
if (pool.configOptions.ifExpected && req.options && lib.isObject(req.options.expected)) {
const expected = Object.keys(req.options.expected).
filter(function(x) { return ["string","number"].indexOf(lib.typeName(req.options.expected[x])) > -1 }).
map(function(x) { return self.sqlColumn(x, pool) + "=" + self.sqlValue(req.options.expected[x]) }).
join(" AND ");
if (expected) req.text += " IF " + expected;
}
}