@cap-js/db-service - npm Package Compare versions

Comparing version 2.9.0 to 2.10.0

lib/cqn2pql.js

+const cds = require('@sap/cds')
+
+const CQN2SQL = require('./cqn2sql.js').class
+
+class CQN2PQLRenderer extends CQN2SQL {
+
+  SELECT(q) {
+    this.values = undefined // inline all values
+    return (this.sql = super.SELECT(q)
+      .replaceAll('\n FROM', '\nFROM')
+      .replaceAll(/([^ ]) (FROM|WHERE|GROUP BY|HAVING|ORDER BY|LIMIT) /g, (a, b, c) => `${b}\n${c} `)
+    )
+  }
+
+  SELECT_columns(q) {
+    return super.SELECT_columns(q).map((c, i) => `${(i % 5 === 0) ? '\n  ' : ' '}${c}${/ as /i.test(c) ? '\n' : ''}`).join(',')
+  }
+
+  column_expr(x, q) {
+    // omit alias when target is a single source
+    if (q.SELECT.from.ref && x?.ref) x.ref = x.ref.slice(-1)
+    return super.column_expr(x, q)
+  }
+
+  SELECT_expand(q, sql) { return sql }
+
+  INSERT_entries(q) {
+    super.INSERT_entries(q)
+    this.sql = this.sql
+      .replaceAll(/AS (.*?)([, ])(?=[^\n])/ig, (a, b, c) => `AS ${b}${c}\n${c === ',' ? ' ' : ''}`)
+      .replaceAll(/ *= */ig, ' = ')
+      .replaceAll('value AS "$$value$$"', 'value')
+      .replaceAll(' WHERE ', '\nWHERE ')
+      .replaceAll(' SELECT ', '\nSELECT')
+      .replaceAll('(SELECT ', '(SELECT\n  ')
+      .replaceAll('))', ')\n)')
+  }
+
+  INSERT_rows(q) {
+    super.INSERT_rows(q)
+    this.sql = this.sql.replaceAll('SELECT', '\nSELECT')
+  }
+
+  UPSERT(q) {
+    super.UPSERT(q)
+    this.sql = this.sql
+      .replaceAll('INSERT', 'UPSERT')
+      .replaceAll(/AS (.*?)([, ])(?=[^\n])/ig, (a, b, c) => `AS ${b}${c}\n${c === ',' ? ' ' : ''}`)
+      .replaceAll(/ *= */ig, ' = ')
+      .replaceAll('value AS "$$value$$"', 'value')
+      .replaceAll(' WHERE ', '\nWHERE ')
+      .replaceAll(' SELECT ', '\nSELECT')
+      .replaceAll('(SELECT ', '(SELECT\n  ')
+      .replaceAll('))', ')\n)')
+  }
+
+  expr(x) {
+    const wrap = x.cast ? sql => `cast(${sql} as ${this.type4(x.cast)})` : sql => sql
+    if (typeof x === 'string') throw cds.error`Unsupported expr: ${x}`
+    if (x.param) return wrap(this.param(x))
+    if ('ref' in x) return wrap(this.ref(x))
+    if ('val' in x) return wrap(this.val(x))
+    if ('func' in x) return wrap(this.func(x))
+    if ('xpr' in x) return wrap(this.xpr(x))
+    if ('list' in x) return wrap(this.list(x))
+    if ('SELECT' in x) return wrap(`(\n    ${this.SELECT(x).replaceAll('\n', '\n    ')}\n  )`)
+    else throw cds.error`Unsupported expr: ${x}`
+  }
+
+  quote(s) { return s }
+
+  managed(columns, elements) {
+    const keys = ObjectKeys(elements).filter(e => elements[e].key && !elements[e].isAssociation)
+    const keyZero = keys[0]
+
+    const ret = super.managed(columns, elements)
+
+    ret.forEach(c => {
+      const { name, insert, update, onInsert, onUpdate } = c
+      const element = elements?.[name]
+      c.upsert = keyZero && (
+        // upsert requires the keys to be provided for the existance join (default values optional)
+        element?.key
+          // If both insert and update have the same managed definition exclude the old value check
+          || (onInsert && onUpdate && insert === update)
+          ? `${insert} as ${name}`
+          : `!OLD.${keyZero} ? ${
+          // If key of old is null execute insert
+          insert
+          } : ${
+          // Else execute managed update or keep old if no new data if provided
+          onUpdate ? update : `(${this.managed_default(name, `OLD.${name}`, update)})`
+          } as ${name}`
+      )
+      if (c.upsert) c.upsert = '\n  ' + c.upsert
+    })
+    return ret
+  }
+
+  managed_default(name, managed, src) {
+    return `!${src} ? ${managed} : ${src}`
+  }
+
+  managed_extract(name) {
+    const { UPSERT, INSERT } = this.cqn
+    const extract = !(INSERT?.entries || UPSERT?.entries) && (INSERT?.rows || UPSERT?.rows)
+      ? `value[${this.columns.indexOf(name)}]`
+      : `value[${JSON.stringify(name)}]`
+    const sql = extract
+    return { extract, sql }
+  }
+}
+
+const ObjectKeys = o => (o && [...ObjectKeys(o.__proto__), ...Object.keys(o)]) || []
+
+module.exports = CQN2PQLRenderer

CHANGELOG.md

@@ -7,2 +7,18 @@ # Changelog
 
+## [2.10.0](https://github.com/cap-js/cds-dbs/compare/db-service-v2.9.0...db-service-v2.10.0) (2026-04-22)
+
+
+### Added
+
+* `cds.features.count_as_string` ([#1556](https://github.com/cap-js/cds-dbs/issues/1556)) ([00e0e60](https://github.com/cap-js/cds-dbs/commit/00e0e60d68edf0d42c1fce2fae3bb1286aca131e))
+* **cqn4sql:** support for enums ([#1527](https://github.com/cap-js/cds-dbs/issues/1527)) ([27c4279](https://github.com/cap-js/cds-dbs/commit/27c4279c495fce8344c785e4489e3116d1a52c55))
+* pql ([#1532](https://github.com/cap-js/cds-dbs/issues/1532)) ([943f76a](https://github.com/cap-js/cds-dbs/commit/943f76a3e4405eb91f0f4b929590212500c49c30))
+
+
+### Fixed
+
+* `$self` reference to func column in `having` ([#1539](https://github.com/cap-js/cds-dbs/issues/1539)) ([9eac576](https://github.com/cap-js/cds-dbs/commit/9eac5762fc4d254a1bc54bded1dd6a492299f576)), closes [#1528](https://github.com/cap-js/cds-dbs/issues/1528)
+* foreign key not included in wildcard select from subquery ([#1540](https://github.com/cap-js/cds-dbs/issues/1540)) ([0fde4ed](https://github.com/cap-js/cds-dbs/commit/0fde4eda21a389c68982f348e9e7c3680c00dcb3)), closes [#1127](https://github.com/cap-js/cds-dbs/issues/1127)
+* sqlite generated key is named lastInsertRowid ([#1501](https://github.com/cap-js/cds-dbs/issues/1501)) ([a4d3437](https://github.com/cap-js/cds-dbs/commit/a4d34378297c8afdb13abb7e664165012c36eb8f))
+
 ## [2.9.0](https://github.com/cap-js/cds-dbs/compare/db-service-v2.8.2...db-service-v2.9.0) (2026-03-09)
@@ -9,0 +25,0 @@

lib/cql-functions.js

@@ -296,3 +296,3 @@ 'use strict'
  FROM ${ranked} AS Source`)
-    Hierarchy.as = 'H' + (uniqueCounter++)
+    Hierarchy.as = `H${uniqueCounter}`
     Hierarchy.SELECT.columns = [...Hierarchy.SELECT.columns, ...passThroughColumns]
@@ -299,0 +299,0 @@ Hierarchy = this.expr(this.with(Hierarchy))

lib/cqn2sql.js

@@ -8,8 +8,9 @@ const cds = require('@sap/cds')
 const _strict_booleans = _simple_queries < 2
+// REVISIT: make string the default in next major
+const _count_as_string = cds.env.features.count_as_string
+const _count = _count_as_string ? { func: 'count', cast: { type: 'cds.String' } } : { func: 'count' }
 
 const { Readable } = require('stream')
 
-const DEBUG = cds.debug('sql|sqlite')
-const LOG_SQL = cds.log('sql')
-const LOG_SQLITE = cds.log('sqlite')
+const DEBUG = cds.log('sql|sqlite')
 
@@ -98,3 +99,3 @@ class CQN2SQLRenderer {
 
-    if (DEBUG && (LOG_SQL._debug || LOG_SQLITE._debug)) {
+    if (DEBUG._debug) {
       let values = sanitize_values && (this.entries || this.values?.length > 0) ? ['***'] : this.entries || this.values || []
@@ -104,3 +105,3 @@ if (values && !Array.isArray(values)) {
       }
-      DEBUG(this.sql, values)
+      DEBUG.debug(this.sql, values)
     }
@@ -663,3 +664,3 @@
     const countQuery = cds.ql.clone(q, {
-      columns: [{ func: 'count' }],
+      columns: [_count],
       one: 0, limit: 0, orderBy: 0, expand: 0, count: 0
@@ -666,0 +667,0 @@ })

lib/infer/index.js

@@ -14,3 +14,3 @@ 'use strict'
  */
-function infer(originalQuery, model) {
+function infer(originalQuery, model, useTechnicalAlias = true) {
   if (!model) throw new Error('Please specify a model')
@@ -38,3 +38,3 @@ const inferred = originalQuery
 
-  const sources = inferTarget(_.into || _.from || _.entity, {}) // IMPORTANT: _.into has to go before _.from for INSERT.into().from(SELECT)
+  const sources = inferTarget(_.into || _.from || _.entity, {}, useTechnicalAlias) // IMPORTANT: _.into has to go before _.from for INSERT.into().from(SELECT)
   const aliases = Object.keys(sources)
@@ -85,3 +85,3 @@ const target = aliases.length === 1 ? getDefinitionFromSources(sources, aliases[0]) : originalQuery
    */
-  function inferTarget(from, querySources, useTechnicalAlias = true) {
+  function inferTarget(from, querySources, useTechnicalAlias) {
     const { ref } = from
@@ -88,0 +88,0 @@ // Given a from clause `Root:parent[$main.name = name].parent as Foo`

lib/InsertResults.js

@@ -72,5 +72,8 @@ const iterator = Symbol.iterator
     // If no generated keys in entries/rows/values we might have database-generated keys
-    const rows = this.results.slice(0, this.affectedRows) // only up to # of root entries
     return (super[iterator] = function* () {
-      for (const each of rows) yield { [k1]: this.insertedRowId4(each) } // REVISIT: sqlite only returns a single lastID per row -> how is that with others?
+      for (const row of this.results) {
+        const affectedRows = this.affectedRows4(row) - 1
+        const lastInsertRowid = this.insertedRowId4(row)
+        for (let i = lastInsertRowid - affectedRows; i<=lastInsertRowid;i++) yield { [k1]: i }
+      }
     })
@@ -103,3 +106,3 @@ }
   insertedRowId4(result) {
-    return result.lastID
+    return result.lastInsertRowid
   }
@@ -106,0 +109,0 @@

lib/SQLService.js

@@ -1,3 +0,3 @@
-const cds = require('@sap/cds'),
-  DEBUG = cds.debug('sql|db')
+const cds = require('@sap/cds')
+const DEBUG = cds.log('sql|db')
 const { Readable, Transform } = require('stream')
@@ -9,2 +9,6 @@ const { pipeline } = require('stream/promises')
 
+// REVISIT: make string the default in next major
+const _count_as_string = cds.env.features.count_as_string
+const _count = _count_as_string ? { func: 'count', cast: { type: 'cds.String' } } : { func: 'count' }
+
 const BINARY_TYPES = {
@@ -21,3 +25,3 @@ 'cds.Binary': 1,
  */
-const _hasProps = (obj) => { 
+const _hasProps = (obj) => {
   if (!obj) return false
@@ -79,3 +83,3 @@ for (const p in obj) {
     if (!Array.isArray(rows)) rows = [rows]
-    if (!rows.length || !Object.keys(rows[0]).length) return   
+    if (!rows.length || !Object.keys(rows[0]).length) return
 
@@ -155,3 +159,3 @@ let changes = false
       if (!iterator) {
-         this._changeToStreams(cqn.SELECT.columns, rows, query.SELECT.one)
+        this._changeToStreams(cqn.SELECT.columns, rows, query.SELECT.one)
       } else if (objectMode) {
@@ -205,3 +209,3 @@ const converter = (row) => this._changeToStreams(cqn.SELECT.columns, row, true)
     // REVISIT: results isn't an array, when no entries -> how could that work? when do we have no entries?
-    return results.reduce((total, affectedRows) => (total += affectedRows.changes), 0)
+    return results.reduce((total, affectedRows) => total + affectedRows.changes, 0)
   }
@@ -304,3 +308,3 @@
   async onEVENT({ event }) {
-    DEBUG?.(event) // in the other cases above DEBUG happens in cqn2sql
+    if(DEBUG._debug) DEBUG.debug(event) // in the other cases above DEBUG happens in cqn2sql
     return await this.exec(event)
@@ -315,3 +319,3 @@ }
     if (typeof query === 'string') {
-      DEBUG?.(query, data)
+      if(DEBUG._debug) DEBUG.debug(query, data)
       const ps = await this.prepare(query)
@@ -336,3 +340,3 @@ const exec = this.hasResults(query) ? d => ps.all(d) : d => ps.run(d)
    * @param {unknown[]} ret - Results of the original query
-   * @returns {Promise<number>}
+   * @returns {Promise<number|string>}
    */
@@ -344,3 +348,3 @@ async count(query, ret) {
       const [max, offset = 0] = one ? [1] : _ ? [_.rows?.val, _.offset?.val] : []
-      if (max === undefined || (n < max && (n || !offset))) return n + offset
+      if (max === undefined || (n < max && (n || !offset))) return _count_as_string ? `${n + offset}` : n + offset
     }
@@ -351,7 +355,7 @@
     let columns = []
-    if((having?.length || groupBy?.length)) {
+    if (having?.length || groupBy?.length) {
       columns = query.SELECT.columns.filter(c => !c.expand)
     }
     if (columns.length === 0) columns.push({ val: 1 })
-    const cq = SELECT.one([{ func: 'count' }]).from(
+    const cq = SELECT.one([_count]).from(
       cds.ql.clone(query, {
@@ -374,3 +378,3 @@ columns,
    */
-  foreach (query, callback) {
+  foreach(query, callback) {
     return query.foreach(callback)
@@ -405,6 +409,4 @@ }
 
-  /** @param {unknown[]} args */
   constructor(...args) {
     super(...args)
-    /** @type {unknown} */
     this.class = new.target // for IntelliSense
@@ -415,9 +417,9 @@ }
    * @param {import('@sap/cds/apis/cqn').Query} query
-   * @param {unknown} values
    * @returns {typeof SQLService.CQN2SQL}
    */
   cqn2sql(query, values) {
-    let q = this.cqn4sql(query)
-    let cqn2sql = new this.class.CQN2SQL(this)
-    return cqn2sql.render(q, values)
+    const cqn2sql = new this.class.CQN2SQL(this)
+    const q = this.cqn4sql(query)
+    const sql = cqn2sql.render(q, values)
+    return sql
   }
@@ -429,3 +431,3 @@
    */
-  cqn4sql(q) {
+  cqn4sql(q, useTechnicalAlias=true) {
     if (
@@ -437,3 +439,3 @@ !cds.env.features.db_strict &&
     ) return q
-    else return cqn4sql(q, this.model)
+    else return cqn4sql(q, this.model, useTechnicalAlias)
   }
@@ -527,29 +529,68 @@
 
-const sqls = new (class extends SQLService {
-  get factory() {
-    return null
+
+// Add support for cqn2pql if debug logging for pql is enabled, or if running in the REPL.
+const DEBUG_PQL = cds.log('pql')
+if (DEBUG_PQL._debug || cds.repl) {
+
+  // Add helper method to convert CQN to PQL, used below...
+  SQLService.prototype.cqn2pql = function cqn2pql (query, values) {
+    const CQN2PQL = cqn2pql.renderer ??= require('./cqn2pql')
+    return new CQN2PQL(this).render(query, values)
   }
 
-  get model() {
-    return cds.model
+  // Add support for logging generated PQL if debug logging for pql is enabled.
+  if (DEBUG_PQL._debug) {
+    const $super = SQLService.prototype.cqn2sql
+    SQLService.prototype.cqn2sql = function (query, values) {
+      const q2 = this.cqn4sql(query, false) // FIXME: calling cqn4sql twice per query is utterly expensive, isn't it ?!?
+      const pql = this.cqn2pql(q2, values)
+      DEBUG_PQL.debug(pql.sql, pql.values ?? '')
+      return $super.call(this, query, values)
+    }
   }
-})()
-cds.extend(cds.ql.Query).with(
-  class {
-    forSQL() {
-      let cqn = (cds.db || sqls).cqn4sql(this)
-      return this.flat(cqn)
+
+  // If running in the REPL, extend cds.ql.Query with helpers to inspect queries. 
+  if (cds.repl) {
+
+    cds.extend(cds.ql.Query).with(
+      class {
+        forSQL() {
+          const cqn = db.srv.cqn4sql(this)
+          return this.flat(cqn)
+        }
+        forSql() { return this.forSQL() }
+        toSQL() {
+          if (this.SELECT) this.SELECT.expand = 'root' // Enforces using json functions always for top-level SELECTS
+          const { sql, values } = db.srv.cqn2sql(this)
+          return { sql, values } // skipping .cqn property
+        }
+        toSql() {
+          const { sql } = this.toSQL()
+          return sql
+        }
+        toPQL() {
+          const { sql, values } = db.srv.cqn2pql(this)
+          return { sql, values } // skipping .cqn property
+        }
+        toPql() {
+          const { sql } = this.toPQL()
+          return sql
+        }
+      }
+    )
+
+    /** 
+     * Dummy SQL service used in extensions to cds.ql above, 
+     * if no real SQL service is available yet through cds.db. 
+     */
+    class db extends SQLService {
+      /** @returns {SQLService} */ 
+      static get srv() { return cds.db || (this.singleton ??= new this) }
+      get factory() { return null }
+      get model() { return cds.model }
     }
-    toSQL() {
-      if (this.SELECT) this.SELECT.expand = 'root' // Enforces using json functions always for top-level SELECTS
-      let { sql, values } = (cds.db || sqls).cqn2sql(this)
-      return { sql, values } // skipping .cqn property
-    }
-    toSql() {
-      return this.toSQL().sql
-    }
-  },
-)
+  }
+}
 
 Object.assign(SQLService, { _target_name4 })
 module.exports = SQLService

package.json

 {
   "name": "@cap-js/db-service",
-  "version": "2.9.0",
+  "version": "2.10.0",
   "description": "CDS base database service",
@@ -5,0 +5,0 @@ "homepage": "https://github.com/cap-js/cds-dbs/tree/main/db-service#cds-base-database-service",

New alerts

Environment variable access

Supply chain risk

Package accesses environment variables, which may be a sign of credential stuffing or data theft.

Found 2 instances in 1 package

Long strings

Supply chain risk

Contains long string literals, which may be a sign of obfuscated or packed code.

Found 1 instance in 1 package

URL strings

Supply chain risk

Package contains fragments of external URLs or IP addresses, which the package may be accessing at runtime.

Found 1 instance in 1 package

Fixed alerts

Environment variable access

Supply chain risk

Package accesses environment variables, which may be a sign of credential stuffing or data theft.

Found 2 instances in 1 package

Long strings

Supply chain risk

Contains long string literals, which may be a sign of obfuscated or packed code.

Found 1 instance in 1 package

URL strings

Supply chain risk

Package contains fragments of external URLs or IP addresses, which the package may be accessing at runtime.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

400228

3.03%

Number of package files

24

4.35%

Lines of code

7916

3.25%

No dependency changes