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

Comparing version 1.20.0 to 2.0.0

CHANGELOG.md

@@ -7,2 +7,22 @@ # Changelog
 
+## [2.0.0](https://github.com/cap-js/cds-dbs/compare/db-service-v1.20.0...db-service-v2.0.0) (2025-05-07)
+
+
+### ⚠ BREAKING CHANGES
+
+* update peer dependency to @sap/cds@9 ([#1178](https://github.com/cap-js/cds-dbs/issues/1178))
+
+
+### Fixed
+
+* Adopt to recurse `DistanceTo` cqn format ([#1093](https://github.com/cap-js/cds-dbs/issues/1093)) ([246e0b3](https://github.com/cap-js/cds-dbs/commit/246e0b38840f7e132ea49cae335b6be7a55354b3))
+* current_utctimestamp as default ([#1161](https://github.com/cap-js/cds-dbs/issues/1161)) ([7c6b2f5](https://github.com/cap-js/cds-dbs/commit/7c6b2f5a6837afbeb1e24daef9a49e25cf7e92f0))
+* exists within expression is properly detected ([#1156](https://github.com/cap-js/cds-dbs/issues/1156)) ([5a7b50c](https://github.com/cap-js/cds-dbs/commit/5a7b50cb02776cf6052c79bd276421dd87161882))
+* resilience for query re-use scenarios ([#1175](https://github.com/cap-js/cds-dbs/issues/1175)) ([2352767](https://github.com/cap-js/cds-dbs/commit/2352767465ea88db77dc89bcaa76e268583146e1))
+
+
+### Changed
+
+* update peer dependency to @sap/cds@9 ([#1178](https://github.com/cap-js/cds-dbs/issues/1178)) ([0507edd](https://github.com/cap-js/cds-dbs/commit/0507edd4e1dcb98983b1fb65ade1344d978b7524))
+
 ## [1.20.0](https://github.com/cap-js/cds-dbs/compare/db-service-v1.19.1...db-service-v1.20.0) (2025-04-17)
@@ -9,0 +29,0 @@

lib/cql-functions.js

 'use strict'
 
+const cds = require('@sap/cds')
+
 // OData: https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_CanonicalFunctions
@@ -21,6 +23,11 @@ const StandardFunctions = {
     }
-    arg.val = arg.__proto__.val = val
+    arg.val = val
     const refs = ref.list
-    const { toString } = ref
-    return '(' + refs.map(ref2 => this.contains(this.tolower(toString(ref2)), this.tolower(arg))).join(' or ') + ')'
+    return `(${refs.map(ref => this.expr({
+      func: 'contains',
+      args: [
+        { func: 'tolower', args: [ref] },
+        { func: 'tolower', args: [arg] },
+      ]
+    })).join(' or ')})`
   },
@@ -145,3 +152,3 @@
   now: function () {
-    return this.session_context({ val: '$now' })
+    return this.expr({ func: 'session_context', args: [{ val: '$now' }] })
   },
@@ -189,2 +196,222 @@
   current_timestamp: p => (p ? `current_timestamp(${p})` : 'current_timestamp'),
+
+  /**
+   * Generates SQL statement for the hierarchy function
+   * @param {string} [p] - 
+   * @returns {string} - SQL statement
+   */
+  HIERARCHY: function (args) {
+    let uniqueCounter = this._with?.length ?? 0
+    let src = args.xpr[1]
+
+    // Ensure that the orderBy column are exposed by the source for hierarchy sorting
+    const orderBy = args.xpr.find((_, i, arr) => /ORDER/i.test(arr[i - 2]) && /BY/i.test(arr[i - 1]))
+
+    const passThroughColumns = src.SELECT.columns.map(c => ({ ref: ['Source', this.column_name(c)] }))
+    src.as = 'H' + (uniqueCounter++)
+    src = this.expr(this.with(src))
+
+    let recursive = cds.ql(`
+SELECT
+  1 as HIERARCHY_LEVEL,
+  NODE_ID as HIERARCHY_ROOT_ID
+ FROM ${src} AS Source
+WHERE parent_ID IS NULL
+UNION ALL
+SELECT
+  Parent.HIERARCHY_LEVEL + 1,
+  Parent.HIERARCHY_ROOT_ID
+ FROM ${src} AS Source
+JOIN H${uniqueCounter} AS Parent ON Source.PARENT_ID=Parent.NODE_ID
+ORDER BY HIERARCHY_LEVEL DESC${orderBy ? `,${orderBy}` : ''}`)
+    recursive.as = 'H' + (uniqueCounter++)
+    recursive.SET.args[0].SELECT.columns = [...recursive.SET.args[0].SELECT.columns, ...passThroughColumns]
+    recursive.SET.args[1].SELECT.columns = [...recursive.SET.args[1].SELECT.columns, ...passThroughColumns]
+    recursive = this.expr(this.with(recursive))
+
+    let ranked = cds.ql(`
+SELECT
+  HIERARCHY_LEVEL,
+  row_number() over () as HIERARCHY_RANK,
+  HIERARCHY_ROOT_ID
+ FROM ${recursive} AS Source`)
+    ranked.as = 'H' + (uniqueCounter++)
+    ranked.SELECT.columns = [...ranked.SELECT.columns, ...passThroughColumns]
+    ranked = this.expr(this.with(ranked))
+
+    let Hierarchy = cds.ql(`
+SELECT
+  HIERARCHY_LEVEL,
+  HIERARCHY_RANK,
+  (SELECT HIERARCHY_RANK FROM ${ranked} AS Ranked WHERE Ranked.NODE_ID = Source.PARENT_ID) AS HIERARCHY_PARENT_RANK,
+  (SELECT HIERARCHY_RANK FROM ${ranked} AS Ranked WHERE Ranked.NODE_ID = Source.HIERARCHY_ROOT_ID) AS HIERARCHY_ROOT_RANK,
+  coalesce(
+    (SELECT MIN(HIERARCHY_RANK) FROM ${ranked} AS Ranked WHERE Ranked.HIERARCHY_RANK > Source.HIERARCHY_RANK AND Ranked.HIERARCHY_LEVEL <= Source.HIERARCHY_LEVEL),
+    (SELECT MAX(HIERARCHY_RANK) + 1 FROM ${ranked})
+  ) - Source.HIERARCHY_RANK AS HIERARCHY_TREE_SIZE
+ FROM ${ranked} AS Source`)
+    Hierarchy.as = 'H' + (uniqueCounter++)
+    Hierarchy.SELECT.columns = [...Hierarchy.SELECT.columns, ...passThroughColumns]
+    Hierarchy = this.expr(this.with(Hierarchy))
+
+    return Hierarchy
+  },
+
+  /**
+   * Generates SQL statement for the hierarchy_descendants function
+   * @param {string} [p] - 
+   * @returns {string} - SQL statement
+   */
+  HIERARCHY_DESCENDANTS: function (args) {
+    // Find Hierarchy function call source query
+    const passThroughColumns = args.xpr[1].args[0].xpr[1].SELECT.columns.map(c => ({ ref: [this.column_name(c)] }))
+    // REVISIT: currently only supports func: HIERARCHY as source
+    const src = this.expr(args.xpr[1])
+
+    let uniqueCounter = this._with?.length ?? 0
+
+    let alias = args.xpr.find((_, i, arr) => /AS/i.test(arr[i - 1]))
+    const where = args.xpr.find((a, i, arr) => a.xpr && /WHERE/i.test(arr[i - 1]) && /START/i.test(arr[i - 2]))
+    const distance = args.xpr.find((a, i, arr) => typeof a.val === 'number' && (/DISTANCE/i.test(arr[i - 1]) || /DISTANCE/i.test(arr[i - 2])))
+    const distanceFrom = args.xpr.find((a, i, arr) => /FROM/.test(a) && /DISTANCE/i.test(arr[i - 1]))
+
+    if (alias.startsWith('"') && alias.endsWith('"')) alias = alias.slice(1, -1).replace(/""/g, '"')
+
+    let HierarchyDescendants = cds.ql(`
+SELECT
+  HIERARCHY_LEVEL,
+  HIERARCHY_PARENT_RANK,
+  HIERARCHY_RANK,
+  HIERARCHY_ROOT_RANK,
+  HIERARCHY_TREE_SIZE,
+  0 as HIERARCHY_DISTANCE
+ FROM ${src} AS ![${alias}]
+UNION ALL
+SELECT
+  Source.HIERARCHY_LEVEL,
+  Source.HIERARCHY_PARENT_RANK,
+  Source.HIERARCHY_RANK,
+  Source.HIERARCHY_ROOT_RANK,
+  Source.HIERARCHY_TREE_SIZE,
+  Child.HIERARCHY_DISTANCE + 1
+ FROM ${src} AS Source
+JOIN H${uniqueCounter} AS Child ON Source.PARENT_ID=Child.NODE_ID`)
+    HierarchyDescendants.as = 'H' + uniqueCounter
+    HierarchyDescendants.SET.args[0].SELECT.where = where.xpr
+    HierarchyDescendants.SET.args[0].SELECT.columns = [...HierarchyDescendants.SET.args[0].SELECT.columns, ...passThroughColumns.map(r => ({ ref: [alias, r.ref[0]] }))]
+    HierarchyDescendants.SET.args[1].SELECT.columns = [...HierarchyDescendants.SET.args[1].SELECT.columns, ...passThroughColumns.map(r => ({ ref: ['Source', r.ref[0]] }))]
+
+    HierarchyDescendants = this.with(HierarchyDescendants)
+    HierarchyDescendants.as = 'HierarchyDescendants'
+
+    return this.expr({
+      SELECT: {
+        columns: [
+          { ref: ['HIERARCHY_LEVEL'] },
+          { ref: ['HIERARCHY_PARENT_RANK'] },
+          { ref: ['HIERARCHY_RANK'] },
+          { ref: ['HIERARCHY_ROOT_RANK'] },
+          { ref: ['HIERARCHY_TREE_SIZE'] },
+          {
+            SELECT: {
+              columns: [{ func: 'MAX', args: [{ ref: ['HIERARCHY_DISTANCE'] }] }],
+              from: HierarchyDescendants,
+              where: [{ ref: [HierarchyDescendants.as, 'HIERARCHY_RANK'] }, '=', { ref: [src, 'HIERARCHY_RANK'] }]
+            },
+            as: 'HIERARCHY_DISTANCE',
+          },
+          ...passThroughColumns,
+        ],
+        from: { ref: [src] },
+        where: [
+          { ref: ['HIERARCHY_RANK'] },
+          'IN',
+          {
+            SELECT: {
+              columns: [{ ref: ['HIERARCHY_RANK'] }],
+              from: HierarchyDescendants,
+              where: [{ ref: ['HIERARCHY_DISTANCE'] }, distanceFrom ? '>=' : '=', distance]
+            }
+          }
+        ]
+      }
+    })
+  },
+
+  /**
+   * Generates SQL statement for the hierarchy_ancestors function
+   * @param {string} [p] - 
+   * @returns {string} - SQL statement
+   */
+  HIERARCHY_ANCESTORS: function (args) {
+    // Find Hierarchy function call source query
+    const passThroughColumns = args.xpr[1].args[0].xpr[1].SELECT.columns.map(c => ({ ref: [this.column_name(c)] }))
+    // REVISIT: currently only supports func: HIERARCHY as source
+    const src = this.expr(args.xpr[1])
+
+    let uniqueCounter = this._with?.length ?? 0
+
+    let alias = args.xpr.find((_, i, arr) => /AS/i.test(arr[i - 1]))
+    const where = args.xpr.find((a, i, arr) => a.xpr && /WHERE/i.test(arr[i - 1]) && /START/i.test(arr[i - 2]))
+
+    if (alias.startsWith('"') && alias.endsWith('"')) alias = alias.slice(1, -1).replace(/""/g, '"')
+
+    let HierarchyAncestors = cds.ql(`
+SELECT
+  HIERARCHY_LEVEL,
+  HIERARCHY_PARENT_RANK,
+  HIERARCHY_RANK,
+  HIERARCHY_ROOT_RANK,
+  HIERARCHY_TREE_SIZE,
+  0 as HIERARCHY_DISTANCE
+ FROM ${src} AS ![${alias}]
+UNION ALL
+SELECT
+  Source.HIERARCHY_LEVEL,
+  Source.HIERARCHY_PARENT_RANK,
+  Source.HIERARCHY_RANK,
+  Source.HIERARCHY_ROOT_RANK,
+  Source.HIERARCHY_TREE_SIZE,
+  Child.HIERARCHY_DISTANCE - 1
+ FROM ${src} AS Source
+JOIN H${uniqueCounter} AS Child ON Source.NODE_ID=Child.PARENT_ID`)
+    HierarchyAncestors.as = 'H' + uniqueCounter
+    HierarchyAncestors.SET.args[0].SELECT.where = where.xpr
+    HierarchyAncestors.SET.args[0].SELECT.columns = [...HierarchyAncestors.SET.args[0].SELECT.columns, ...passThroughColumns.map(r => ({ ref: [alias, r.ref[0]] }))]
+    HierarchyAncestors.SET.args[1].SELECT.columns = [...HierarchyAncestors.SET.args[1].SELECT.columns, ...passThroughColumns.map(r => ({ ref: ['Source', r.ref[0]] }))]
+
+    HierarchyAncestors = this.with(HierarchyAncestors)
+    HierarchyAncestors.as = 'HierarchyAncestors'
+    return this.expr({
+      SELECT: {
+        columns: [
+          { ref: ['HIERARCHY_LEVEL'] },
+          { ref: ['HIERARCHY_PARENT_RANK'] },
+          { ref: ['HIERARCHY_RANK'] },
+          { ref: ['HIERARCHY_ROOT_RANK'] },
+          { ref: ['HIERARCHY_TREE_SIZE'] },
+          {
+            SELECT: {
+              columns: [{ func: 'MIN', args: [{ ref: ['HIERARCHY_DISTANCE'] }] }],
+              from: HierarchyAncestors,
+              where: [{ ref: [HierarchyAncestors.as, 'HIERARCHY_RANK'] }, '=', { ref: [src, 'HIERARCHY_RANK'] }]
+            },
+            as: 'HIERARCHY_DISTANCE',
+          },
+          ...passThroughColumns,
+        ],
+        from: { ref: [src] },
+        where: [
+          { ref: ['HIERARCHY_RANK'] },
+          'IN',
+          {
+            SELECT: {
+              columns: [{ ref: ['HIERARCHY_RANK'] }],
+              from: HierarchyAncestors,
+            }
+          }
+        ]
+      }
+    })
+  },
 }
@@ -191,0 +418,0 @@

lib/cqn2sql.js

@@ -85,2 +85,5 @@ const cds = require('@sap/cds')
     this[kind]((this.cqn = q)) // actual sql rendering happens here
+    if (this._with?.length) {
+      this.render_with()
+    }
     if (vars?.length && !this.values?.length) this.values = vars
@@ -99,6 +102,24 @@ if (vars && Object.keys(vars).length && !this.values?.length) this.values = vars
 
-
     return this
   }
 
+  render_with() {
+    const sql = this.sql
+    let recursive = false
+    const values = this.values
+    const prefix = this._with.map(q => {
+      const values = this.values = []
+      let sql
+      if ('SELECT' in q) sql = `${this.quote(q.as)} AS (${this.SELECT(q)})`
+      else if ('SET' in q) {
+        recursive = true
+        const { SET } = q
+        sql = `${this.quote(q.as)}(${SET.args[0].SELECT.columns?.map(c => this.quote(this.column_name(c))) || ''}) AS (${this.SELECT(SET.args[0])} ${SET.op?.toUpperCase() || 'UNION'} ${SET.all ? 'ALL' : ''} ${this.SELECT(SET.args[1])}${SET.orderBy ? ` ORDER BY ${this.orderBy(SET.orderBy)}` : ''})`
+      }
+      return { sql, values }
+    })
+    this.sql = `WITH${recursive ? ' RECURSIVE' : ''} ${prefix.map(p => p.sql)} ${sql}`
+    this.values = [...prefix.map(p => p.values).flat(), ...values]
+  }
+
   /**
@@ -263,4 +284,269 @@ * Links the incoming query with the current service model
 
-  SELECT_recurse() {
-    cds.error`Feature "recurse" queries not supported.`
+  SELECT_recurse(q) {
+    let { from, columns, where, orderBy, recurse, _internal } = q.SELECT
+
+    const requiredComputedColumns = { PARENT_ID: true, NODE_ID: true }
+    if (!_internal) requiredComputedColumns.RANK = true
+    const addComputedColumn = (name) => {
+      if (requiredComputedColumns[name]) return
+      requiredComputedColumns[name] = true
+    }
+
+    // The hierarchy functions will output the following columns. Which might clash with the entity columns
+    const reservedColumnNames = {
+      PARENT_ID: 1, NODE_ID: 1,
+      HIERARCHY_RANK: 1, HIERARCHY_DISTANCE: 1, HIERARCHY_LEVEL: 1, HIERARCHY_TREE_SIZE: 1
+    }
+    const availableComputedColumns = {
+      // Input computed columns
+      PARENT_ID: false,
+      NODE_ID: false,
+
+      // Output computed columns
+      RANK: { xpr: [{ ref: ['HIERARCHY_RANK'] }, '-', { val: 1, param: false }], as: 'RANK' },
+      Distance: { func: where?.length ? 'min' : 'max', args: [{ ref: ['HIERARCHY_DISTANCE'] }], as: 'Distance' },
+      DistanceFromRoot: { xpr: [{ ref: ['HIERARCHY_LEVEL'] }, '-', { val: 1, param: false }], as: 'DistanceFromRoot' },
+      DrillState: false,
+      LimitedDescendantCount: { xpr: [{ ref: ['HIERARCHY_TREE_SIZE'] }, '-', { val: 1, param: false }], as: 'LimitedDescendantCount' },
+    }
+
+    const columnsFiltered = columns
+      .filter(x => {
+        if (x.element?.isAssociation) return false
+        const name = this.column_name(x)
+        if (name === '$$RN$$') return false
+        // REVISIT: ensure that the selected column is one of the hierarchy computed columns by unifying their common definition
+        if (x.element?.['@Core.Computed'] && name in availableComputedColumns) {
+          addComputedColumn(name)
+          return false
+        }
+        return true
+      })
+    const columnsOut = []
+    const columnsIn = []
+    const target = q._target || q.target
+    for (const name in target.elements) {
+      const ref = { ref: [name] }
+      const element = target.elements[name]
+      if (element.virtual || element.value || element.isAssociation) continue
+      if (element['@Core.Computed'] && name in availableComputedColumns) continue
+      if (name.toUpperCase() in reservedColumnNames) ref.as = `$$${name}$$`
+      columnsIn.push(ref)
+      if (from.args || columnsFiltered.find(c => this.column_name(c) === name)) {
+        columnsOut.push(ref.as ? { ref: [ref.as], as: name } : ref)
+      }
+    }
+
+    const nodeKeys = []
+    const parentKeys = []
+    const association = target.elements[recurse.ref[0]]
+    association._foreignKeys.forEach(fk => {
+      nodeKeys.push(this.quote(fk.childElement.name))
+      parentKeys.push(this.quote(fk.parentElement.name))
+    })
+
+    columnsIn.push(
+      nodeKeys.length === 1
+        ? { ref: nodeKeys, as: 'NODE_ID' }
+        : { func: 'HIERARCHY_COMPOSITE_ID', args: nodeKeys.map(n => ({ ref: [n] })), as: 'NODE_ID' },
+      parentKeys.length === 1
+        ? { ref: parentKeys, as: 'PARENT_ID' }
+        : { func: 'HIERARCHY_COMPOSITE_ID', args: parentKeys.map(n => ({ ref: [n] })), as: 'PARENT_ID' },
+    )
+
+    if (orderBy) {
+      orderBy = orderBy.map(r => {
+        const col = r.ref.at(-1)
+        if (!columnsIn.find(c => this.column_name(c) === col)) {
+          columnsIn.push({ ref: [col] })
+        }
+        return { ...r, ref: [col] }
+      })
+    }
+
+    // In the case of join operations make sure to compute the hierarchy from the source table only
+    const stableFrom = getStableFrom(from)
+    const alias = stableFrom.as
+    const source = () => ({
+      func: 'HIERARCHY',
+      args: [{ xpr: ['SOURCE', { SELECT: { columns: columnsIn, from: stableFrom } }, ...(orderBy ? ['SIBLING', 'ORDER', 'BY', `${this.orderBy(orderBy)}`] : [])] }],
+      as: alias
+    })
+
+    const expandedByNr = { list: [] } // DistanceTo(...,null)
+    const expandedByOne = { list: [] } // DistanceTo(...,1)
+    const expandedByZero = { list: [] } // not DistanceTo(...,null)
+    let expandedFilter = []
+    let distanceType = 'DistanceFromRoot'
+    let distanceVal
+
+    if (recurse.where) {
+      distanceType = 'Distance'
+      if (recurse.where[0] === 'and') recurse.where = recurse.where.slice(1)
+      expandedFilter = [...recurse.where]
+      collectDistanceTo(expandedFilter)
+    }
+
+    const direction = where?.length ? 'ANCESTORS' : 'DESCENDANTS'
+    // Ensure that the distance value is being computed
+    if (distanceType) addComputedColumn(distanceType)
+
+    let distanceClause = []
+    if (distanceType === 'Distance') {
+      const isOne = expandedByOne.list.length
+      distanceClause = ['DISTANCE', ...(
+        isOne
+          ? [{ val: 1 }]
+          : ['FROM', { val: 1 }]
+      )]
+      where = [{ ref: ['NODE_ID'] }, 'IN', isOne ? expandedByOne : expandedByNr]
+      expandedFilter = []
+    }
+
+    availableComputedColumns.DrillState = {
+      xpr: [ // When the node doesn't have children make it a leaf
+        'CASE', 'WHEN', { ref: ['HIERARCHY_TREE_SIZE'] }, '=', { val: 1, param: false }, 'THEN', { val: 'leaf', param: false },
+        ...(where?.length // When there is a where filter the final node will always be a leaf
+          ? ['WHEN', { func: where?.length ? 'min' : 'max', args: [{ ref: ['HIERARCHY_DISTANCE'] }] }, '=', { val: 0, param: false }, 'THEN', { val: 'leaf', param: false }]
+          : []
+        ), // When having expanded by 0 level nodes make sure they are collapsed
+        ...(expandedByZero.list.length
+          ? ['WHEN', { ref: ['NODE_ID'] }, 'IN', expandedByZero, 'THEN', { val: 'collapsed', param: false }]
+          : []
+        ), // When having expanded by null or one nodes compute them as expanded
+        ...(expandedByNr.list.length || expandedByOne.list.length
+          ? ['WHEN', { ref: ['NODE_ID'] }, 'IN', { list: [...expandedByNr.list, ...expandedByOne.list] }, 'THEN', { val: 'expanded', param: false }]
+          : []
+        ), // When having expanded by one level node make its children collapsed
+        ...(expandedByOne.list.length
+          ? ['WHEN', { ref: ['PARENT_ID'] }, 'IN', expandedByOne, 'THEN', { val: 'collapsed', param: false }]
+          : []
+        ), // When using DistanceFromRoot compute all entries within the levels as expanded
+        ...(distanceType === 'DistanceFromRoot' && distanceVal
+          ? [
+            'WHEN', { ref: ['HIERARCHY_LEVEL'] }, '<>', { val: distanceVal.val + 1 },
+            'THEN', { val: 'expanded', param: false },
+          ]
+          : []
+        ), // Default to expanded when default filter behavior is truthy
+        'ELSE', { val: (recurse.where && !expandedByZero.list.length) && distanceType ? 'collapsed' : 'expanded', param: false },
+        'END',
+      ],
+      as: 'DrillState'
+    }
+
+    for (const name in requiredComputedColumns) {
+      const def = availableComputedColumns[name]
+      if (def) columnsOut.push(def)
+    }
+    if (_internal) columnsOut.push({ ref: ['NODE_ID'] })
+
+    const graph = distanceType === 'DistanceFromRoot' && !where
+      ? { SELECT: { columns: columnsOut, from: source(), where: expandedFilter } }
+      : {
+        SELECT: {
+          columns: columnsOut,
+          from: {
+            func: `HIERARCHY_${direction}`,
+            args: [{
+              xpr: [
+                'SOURCE', source(), 'AS', this.quote(alias),
+                'START', 'WHERE', {
+                  xpr: where // Requires special where logic before being put into the args
+                    ? from.args
+                      ? [{ ref: ['NODE_ID'] }, 'IN', { SELECT: { columns: [columnsIn.find(c => c.as === 'NODE_ID')], from, where: where } }]
+                      : this.is_comparator?.({ xpr: where }) ?? true ? where : [...where, '=', { val: true, param: false }]
+                    : [{ ref: ['PARENT_ID'] }, '=', { val: null }]
+                },
+                ...distanceClause
+              ]
+            }]
+          },
+          where: expandedFilter.length ? expandedFilter : undefined,
+          orderBy: [{ ref: ['HIERARCHY_RANK'], sort: 'asc' }],
+          groupBy: [{ ref: ['NODE_ID'] },{ ref: ['PARENT_ID'] }, { ref: ['HIERARCHY_RANK'] }, { ref: ['HIERARCHY_LEVEL'] }, { ref: ['HIERARCHY_TREE_SIZE'] }, ...columnsOut.filter(c => c.ref)],
+        }
+      }
+
+    // Only apply result join if the columns contain a references which doesn't start with the source alias
+    if (from.args && columns.find(c => c.ref?.[0] === alias)) {
+      graph.as = alias
+      return this.from(setStableFrom(from, graph))
+    }
+
+    return `(${this.SELECT(graph)})${alias ? ` AS ${this.quote(alias)}` : ''} `
+
+    function collectDistanceTo(where, innot = false) {
+      for (let i = 0; i < where.length; i++) {
+        const c = where[i]
+        if (c === 'not') {
+          distanceType = 'DistanceFromRoot'
+          innot = true
+        }
+        else if (c.func === 'DistanceTo') {
+          const expr = c.args[0]
+          // { func: 'HIERARCHY_COMPOSITE_ID', args: nodeKeys.map(n => ({ val: cur[n] })) }
+          const to = c.args[1].val
+          const list = to === 1
+            ? expandedByOne
+            : innot
+              ? expandedByZero
+              : expandedByNr
+
+          if (!list._where) {
+            list._where = []
+            where.splice(i, 1,
+              ...(to === 1
+                ? [{ ref: ['PARENT_ID'] }, 'IN', list]
+                : [{ ref: ['NODE_ID'] }, 'IN', {
+                  SELECT: {
+                    _internal: true,
+                    columns: [{ ref: ['NODE_ID'], element: { '@Core.Computed': true } }],
+                    from: q.SELECT.from,
+                    recurse: {
+                      ref: recurse.ref,
+                      where: list._where,
+                    },
+                  },
+                  target,
+                }])
+            )
+            i += 2
+          } else {
+            // Remove current entry from where
+            if (where[i - 1] === 'not') {
+              where.splice(i - 2, 3)
+              i -= 3
+            } else {
+              where.splice(i - 1, 2)
+              i -= 2
+            }
+          }
+          list.list.push(expr)
+          list._where.push(c)
+        }
+        else if (c.ref?.[0] === 'DistanceFromRoot') {
+          distanceType = 'DistanceFromRoot'
+          where[i] = { ref: ['HIERARCHY_LEVEL'] }
+          i += 2
+          distanceVal = where[i]
+          where[i] = { val: where[i].val + 1 }
+        }
+      }
+    }
+
+    function getStableFrom(from) {
+      if (from.args) return getStableFrom(from.args[0])
+      return from
+    }
+
+    function setStableFrom(from, src) {
+      if (from.args) {
+        const ret = { ...from }
+        ret.args = [...ret.args]
+        ret.args[0] = setStableFrom(ret.args[0], src)
+        return ret
+      }
+      return src
+    }
   }
@@ -377,5 +663,17 @@
     if (from.join) return `${this.from(from.args[0])} ${from.join} JOIN ${this.from(from.args[1])}${from.on ? ` ON ${this.where(from.on)}` : ''}`
+    if (from.func) return _aliased(this.func(from))
   }
 
   /**
+   * Renders a FROM clause into generic SQL
+   * @param {import('./infer/cqn').source} from
+   * @returns {string} SQL
+   */
+  with(query) {
+    this._with ??= []
+    this._with.push(query)
+    return { ref: [query.as] }
+  }
+
+  /**
    * Renders a FROM clause for when the query does not have a target
@@ -496,3 +794,3 @@ * @returns {string} SQL
           ? this.INSERT_values(q)
-          : INSERT.as
+          : INSERT.from || INSERT.as
             ? this.INSERT_select(q)
@@ -703,3 +1001,3 @@ : cds.error`Missing .entries, .rows, or .values in ${q}`
     this.sql = `INSERT INTO ${this.quote(entity)}${alias ? ' as ' + this.quote(alias) : ''} (${columns.map(c => this.quote(c))}) ${this.SELECT(
-      this.cqn4sql(INSERT.as),
+      this.cqn4sql(INSERT.from || INSERT.as),
     )}`
@@ -1007,3 +1305,3 @@ this.entries = [this.values]
     }
-    const fn = this.class.Functions[func]?.apply(this.class.Functions, args) || `${func}(${args})`
+    const fn = this.class.Functions[func]?.apply(this, args) || `${func}(${args})`
     if (xpr) return `${fn} ${this.xpr({ xpr })}`
@@ -1112,3 +1410,3 @@ return fn
         || this.managed_session_context(element.default?.ref?.[0])
-        || (element.default?.val !== undefined && { val: element.default.val, param: false })
+        || (element.default && { __proto__:  element.default, param: false })
       let onUpdate = this.managed_session_context(element[cdsOnUpdate]?.['='])
@@ -1115,0 +1413,0 @@

lib/infer/index.js

@@ -7,3 +7,3 @@ 'use strict'
 const { pseudos } = require('./pseudos')
-const { isCalculatedOnRead, getImplicitAlias, getModelUtils } = require('../utils')
+const { isCalculatedOnRead, getImplicitAlias, getModelUtils, defineProperty } = require('../utils')
 const cdsTypes = cds.linked({
@@ -50,3 +50,3 @@ definitions: {
 
-  const sources = inferTarget(_.from || _.into || _.entity, {})
+  const sources = inferTarget(_.into || _.from || _.entity, {}) // IMPORTANT: _.into has to go before _.from for INSERT.into().from(SELECT)
   const joinTree = new JoinTree(sources)
@@ -80,3 +80,3 @@ const aliases = Object.keys(sources)
     // also enrich original query -> writable because it may be inferred again
-    Object.defineProperty(originalQuery, 'elements', { value: elements, writable: true, configurable: true })
+    defineProperty(originalQuery, 'elements', elements)
   }
@@ -177,6 +177,3 @@ return inferred
   function setElementOnColumns(col, element) {
-    Object.defineProperty(col, 'element', {
-      value: element,
-      writable: true,
-    })
+    defineProperty(col, 'element', element)
   }
@@ -252,3 +249,3 @@
         let $baseLink
-        let rejectJoinRelevantPath
+        let needsElementsOfQueryAsBase
         // first check if token ref is resolvable in query elements
@@ -261,10 +258,7 @@ if (columns) {
           })
-          const needsElementsOfQueryAsBase =
+          needsElementsOfQueryAsBase =
             tokenPointsToQueryElements &&
-            queryElements[token.ref?.[0]] &&
-            /* expand on structure can be addressed */ !queryElements[token.ref?.[0]].$assocExpand
+            queryElements[firstStep] &&
+            /* expand on structure can be addressed */ !queryElements[firstStep].$assocExpand
 
-          // if the ref points into the query itself and follows an exposed association
-          // to a non-fk column, we must reject the ref, as we can't join with the queries own results
-          rejectJoinRelevantPath = needsElementsOfQueryAsBase
           if (needsElementsOfQueryAsBase) $baseLink = { definition: { elements: queryElements }, target: inferred }
@@ -277,3 +271,5 @@ } else {
         inferArg(token, queryElements, $baseLink, { inQueryModifier: true })
-        if (token.isJoinRelevant && rejectJoinRelevantPath) {
+        // if the ref points into the query itself and follows an exposed association
+        // to a non-fk column, we must reject the ref, as we can't join with the queries own results
+        if (token.isJoinRelevant && needsElementsOfQueryAsBase) {
           // reverse the array, find the last association and calculate the index of the association in non-reversed order
@@ -416,3 +412,6 @@ const assocIndex =
     if (arg.list) arg.list.forEach(arg => inferArg(arg, null, $baseLink, context))
-    if (arg.xpr) arg.xpr.forEach(token => inferArg(token, queryElements, $baseLink, { ...context, inXpr: true })) // e.g. function in expression
+    if (arg.xpr)
+      arg.xpr.forEach((token, i) =>
+        inferArg(token, queryElements, $baseLink, { ...context, inXpr: true, inExists: arg.xpr[i - 1] === 'exists' }),
+      ) // e.g. function in expression
 
@@ -424,7 +423,8 @@ if (!arg.ref) {
 
-    // initialize $refLinks
-    Object.defineProperty(arg, '$refLinks', {
-      value: [],
-      writable: true,
-    })
+    // Before the arg is linked, it's meta information should be cleaned up.
+    // This may be important if one manipulates the arg object
+    // __after__ a query has been fired and re-uses the manipulated query
+    defineProperty(arg, '$refLinks', [])
+    defineProperty(arg, 'isJoinRelevant', false)
+
     // if any path step points to an artifact with `@cds.persistence.skip`
@@ -440,3 +440,3 @@ // we must ignore the element from the queries elements
     if (dollarSelfRefs && firstStepIsSelf) {
-      Object.defineProperty(arg, 'inXpr', { value: true, writable: true })
+      defineProperty(arg, 'inXpr', true)
       dollarSelfRefs.push(arg)
@@ -468,3 +468,3 @@ return
                 if (inExists) {
-                  Object.defineProperty($baseLink, 'pathExpressionInsideFilter', { value: true })
+                  defineProperty($baseLink, 'pathExpressionInsideFilter', true)
                 } else {
@@ -533,3 +533,3 @@ rejectNonFkNavigation(element, element.on ? $baseLink.definition.name : nextStep)
               if (inExists) {
-                Object.defineProperty($baseLink, 'pathExpressionInsideFilter', { value: true })
+                defineProperty($baseLink, 'pathExpressionInsideFilter', true)
               } else {
@@ -550,3 +550,3 @@ rejectNonFkNavigation(element, element.on ? $baseLink.definition.name : nextStep)
           arg.$refLinks.push({ definition: { name: '$dummy', parent: arg.$refLinks[i - 1].target } })
-          Object.defineProperty(arg, 'isJoinRelevant', { value: true })
+          defineProperty(arg, 'isJoinRelevant', true)
         } else {
@@ -577,3 +577,3 @@ const notFoundIn = pseudoPath ? arg.ref[i - 1] : getFullPathForLinkedArg(arg)
           throw new Error('A filter can only be provided when navigating along associations')
-        if (!inFrom && !arg.expand) Object.defineProperty(arg, 'isJoinRelevant', { value: true })
+        if (!inFrom && !arg.expand)defineProperty(arg, 'isJoinRelevant', true)
         let skipJoinsForFilter = false
@@ -607,3 +607,3 @@ step.where.forEach(token => {
         const flatName = nameSegments.join('_')
-        Object.defineProperty(arg, 'flatName', { value: flatName, writable: true })
+        defineProperty(arg, 'flatName', flatName)
         // if column is casted, we overwrite it's origin with the new type
@@ -653,3 +653,3 @@ if (arg.cast) {
     // we need inner joins for the path expressions inside filter expressions after exists predicate
-    if ($baseLink?.pathExpressionInsideFilter) Object.defineProperty(arg, 'join', { value: 'inner' })
+    if ($baseLink?.pathExpressionInsideFilter) defineProperty(arg, 'join', 'inner')
 
@@ -674,3 +674,3 @@ // ignore whole expand if target of assoc along path has ”@cds.persistence.skip”
       if (isColumnJoinRelevant(colWithBase)) {
-        Object.defineProperty(arg, 'isJoinRelevant', { value: true })
+        defineProperty(arg, 'isJoinRelevant', true)
         joinTree.mergeColumn(colWithBase, originalQuery.outerQueries)
@@ -781,3 +781,3 @@ }
           : new cds.array({ items: new cds.struct({ elements: inferredExpandSubquery.elements }) })
-        return Object.defineProperty(res, '$assocExpand', { value: true })
+        return defineProperty(res, '$assocExpand', true)
       } else if ($leafLink.definition.elements) {
@@ -921,3 +921,3 @@ let elements = {}
           if (!calcElement.value.isJoinRelevant)
-            Object.defineProperty(step, 'isJoinRelevant', { value: true, writable: true })
+            defineProperty(step, 'isJoinRelevant',true)
           joinTree.mergeColumn(p, originalQuery.outerQueries)
@@ -928,3 +928,3 @@ } else {
           // --> for the inline column, the name is join relevant, while for the expand, it is not
-          Object.defineProperty(step, 'isJoinRelevant', { value: false, writable: true })
+          defineProperty(step, 'isJoinRelevant', false)
         }
@@ -1087,3 +1087,2 @@ }
    */
-  // REVISIT: TODO: inferred.elements should be linked
   function getCopyWithAnnos(from, base) {
@@ -1096,3 +1095,3 @@ const result = { ...base }
 
-    if (from.as && base.name !== from.as) Object.defineProperty(result, 'name', { value: from.as }) // TODO double check if this is needed
+    if (from.as && base.name !== from.as) defineProperty(result, 'name', from.as) // TODO double check if this is needed
     // in subqueries we need the linked element if an outer query accesses it
@@ -1099,0 +1098,0 @@ return Object.setPrototypeOf(result, base)

lib/InsertResults.js

@@ -29,5 +29,5 @@ const iterator = Symbol.iterator
   get [iterator]() {
-    // For INSERT.as(SELECT.from(...)) return a dummy iterator with correct length
+    // For INSERT.from(SELECT.from(...)) return a dummy iterator with correct length
     const { INSERT } = this.query
-    if (INSERT.as) {
+    if (INSERT.from || INSERT.as) {
       return (super[iterator] = function* () {
@@ -85,3 +85,3 @@ for (let i = 0; i < this.affectedRows; i++) yield {}
     const { INSERT: _ } = this.query
-    if (_.as) return (super.affectedRows = this.affectedRows4(this.results[0] || this.results))
+    if (_.from || _.as) return (super.affectedRows = this.affectedRows4(this.results[0] || this.results))
     else return (super.affectedRows = _.entries?.length || _.rows?.length || this.results.length || 1)
@@ -88,0 +88,0 @@ }

lib/SQLService.js

@@ -498,12 +498,12 @@ const cds = require('@sap/cds'),
 
+/** @param {import('@sap/cds').ql.Query} q */
 const _target_name4 = q => {
-  const target =
-    q._target_ref ||
-    q.SELECT?.from ||
-    q.INSERT?.into ||
-    q.UPSERT?.into ||
-    q.UPDATE?.entity ||
-    q.DELETE?.from ||
-    q.CREATE?.entity ||
-    q.DROP?.entity
+  const target = q._subject
+    || q.SELECT?.from
+    || q.INSERT?.into
+    || q.UPSERT?.into
+    || q.UPDATE?.entity
+    || q.DELETE?.from
+    || q.CREATE?.entity
+    || q.DROP?.entity
   if (target?.SET?.op === 'union') throw new cds.error('UNION-based queries are not supported')
@@ -510,0 +510,0 @@ if (!target?.ref) return target

lib/utils.js

@@ -65,2 +65,10 @@ 'use strict'
 
+function defineProperty(obj, prop, value) {
+  return Object.defineProperty(obj, prop, {
+    value,
+    writable: true,
+    configurable: true,
+  })
+}
+
 /**
@@ -133,3 +141,4 @@ * Shared utility functions which operate dynamically on the model / query.
   getImplicitAlias,
+  defineProperty,
   getModelUtils,
 }

package.json

 {
   "name": "@cap-js/db-service",
-  "version": "1.20.0",
+  "version": "2.0.0",
   "description": "CDS base database service",
@@ -8,3 +8,3 @@ "homepage": "https://github.com/cap-js/cds-dbs/tree/main/db-service#cds-base-database-service",
     "type": "git",
-    "url": "https://github.com/cap-js/cds-dbs"
+    "url": "git+https://github.com/cap-js/cds-dbs.git"
   },
@@ -31,5 +31,5 @@ "bugs": {
   "peerDependencies": {
-    "@sap/cds": ">=7.9"
+    "@sap/cds": ">=9"
   },
-  "license": "SEE LICENSE"
+  "license": "Apache-2.0"
 }

New alerts

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

Misc. License Issues

License

(Experimental) A package's licensing information has fine-grained problems.

Found 1 instance in 1 package

Unidentified License

License

(Experimental) Something that seems like a license was found, but its contents could not be matched with a known license.

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

339001

6.68%

Number of low license alerts

0

-100%

Number of medium license alerts

0

-100%

Lines of code

6812

7.58%

Worsened metrics

Published versions last months

1

-50%

Published versions last year

23

-4.17%

No dependency changes