@cap-js/hana - npm Package Compare versions

Comparing version 0.0.2 to 0.0.3

CHANGELOG.md

@@ -7,2 +7,19 @@ # Changelog
 
+## [0.0.3](https://github.com/cap-js/cds-dbs/compare/hana-v0.0.2...hana-v0.0.3) (2023-12-20)
+
+
+### Added
+
+* Compress HANA expand queries by reducing duplicated statements ([#383](https://github.com/cap-js/cds-dbs/issues/383)) ([3d29351](https://github.com/cap-js/cds-dbs/commit/3d293513fc2915a4727020e2e3bdf2cf97805200))
+
+
+### Fixed
+
+* **cqn2sql:** supporting calculated elements ([#387](https://github.com/cap-js/cds-dbs/issues/387)) ([2153fb9](https://github.com/cap-js/cds-dbs/commit/2153fb9a3910cd4afa3a91918e6cf682646492b7))
+
+
+### Performance Improvements
+
+* HANA list placeholder ([#380](https://github.com/cap-js/cds-dbs/issues/380)) ([3eadfea](https://github.com/cap-js/cds-dbs/commit/3eadfea7b94f485030cc8bd0bd298ce088586422))
+
 ## [0.0.2](https://github.com/cap-js/cds-dbs/compare/hana-v0.0.1...hana-v0.0.2) (2023-12-11)
@@ -9,0 +26,0 @@

lib/cql-functions.js

@@ -0,1 +1,6 @@
+const isTime = /^\d{1,2}:\d{1,2}:\d{1,2}$/
+const isVal = x => x && 'val' in x
+const getTimeType = x => isTime.test(x.val) ? 'TIME' : 'TIMESTAMP'
+const getTimeCast = x => isVal(x) ? `TO_${getTimeType(x)}(${x})` : x
+
 const StandardFunctions = {
@@ -28,4 +33,7 @@ tolower: x => `lower(${x})`,
   day: x => `DAYOFMONTH(${x})`,
+  hour: x => `HOUR(${getTimeCast(x)})`,
+  minute: x => `MINUTE(${getTimeCast(x)})`,
+  second: x => `SECOND(${getTimeCast(x)})`
 }
 
 module.exports = StandardFunctions

lib/HANAService.js

@@ -105,6 +105,6 @@ const fs = require('fs')
     query.SELECT.expand = 'root'
-    const { cqn, temporary, blobs } = this.cqn2sql(query, data)
+    const { cqn, temporary, blobs, withclause, values } = this.cqn2sql(query, data)
     // REVISIT: add prepare options when param:true is used
-    const sqlScript = this.wrapTemporary(temporary, blobs)
-    let rows = await this.exec(sqlScript)
+    const sqlScript = this.wrapTemporary(temporary, withclause, blobs)
+    let rows = values?.length ? await (await this.prepare(sqlScript)).all(values) : await this.exec(sqlScript)
     if (rows.length) {
@@ -135,3 +135,3 @@ rows = this.parseRows(rows)
   async onSTREAM(req) {
-    let { cqn, sql, values, temporary, blobs } = this.cqn2sql(req.query)
+    let { cqn, sql, values, temporary, withclause, blobs } = this.cqn2sql(req.query)
     // writing stream
@@ -145,3 +145,3 @@ if (req.query.STREAM.into) {
       // Full SELECT CQN support streaming
-      sql = this.wrapTemporary(temporary, blobs)
+      sql = this.wrapTemporary(temporary, withclause, blobs)
     }
@@ -155,3 +155,3 @@ const ps = await this.prepare(sql)
   // Allow for running complex expand queries in a single statement
-  wrapTemporary(temporary, blobs) {
+  wrapTemporary(temporary, withclauses, blobs) {
     const blobColumn = b => `"${b.replace(/"/g, '""')}"`
@@ -161,11 +161,8 @@
       .map(t => {
-        if (blobs.length) {
-          const localBlobs = t.blobs
-          const blobColumns = blobs.filter(b => !(b in localBlobs)).map(b => `NULL AS ${blobColumn(b)}`)
-          if (blobColumns.length) return `SELECT ${blobColumns},${t.select}`
-        }
-        return `SELECT ${t.select}`
+        const blobColumns = blobs.map(b => (b in t.blobs) ? blobColumn(b) : `NULL AS ${blobColumn(b)}`)
+        return `SELECT "_path_","_blobs_","_expands_","_json_"${blobColumns.length ? ',' : ''}${blobColumns} FROM (${t.select})`
       })
 
-    const ret = values.length === 1 ? values[0] : 'SELECT * FROM ' + values.map(v => `(${v})`).join(' UNION ALL ') + ' ORDER BY "_path_" ASC'
+    const withclause = withclauses.length ? `WITH ${withclauses} ` : ''
+    const ret = withclause + (values.length === 1 ? values[0] : 'SELECT * FROM ' + values.map(v => `(${v})`).join(' UNION ALL ') + ' ORDER BY "_path_" ASC')
     DEBUG?.(ret)
@@ -272,5 +269,14 @@ return ret
       // Collect all queries and blob columns of all queries
+      this.blobs = this.blobs || []
+      this.withclause = this.withclause || []
       this.temporary = this.temporary || []
-      this.blobs = this.blobs || []
+      this.temporaryValues = this.temporaryValues || []
 
+      const walkAlias = q => {
+        if (q.args) return q.as || walkAlias(q.args[0])
+        if (q.SELECT?.from) return walkAlias(q.SELECT?.from)
+        return q.as
+      }
+      const alias = walkAlias(q)
+      q.as = alias
       const src = q
@@ -283,3 +289,2 @@
         const { element, elements } = q
-        if (expand === 'root') this.values = undefined
 
@@ -299,3 +304,3 @@ q = cds.ql.clone(q)
           if (!columns.find(c => this.column_name(c) === '_path_'))
-            columns.push({ ref: [parent.as, '_path_'], as: '_path_' })
+            columns.push({ ref: [parent.as, '_path_'], as: '_parent_path_' })
         }
@@ -320,3 +325,3 @@
         // TODO: replace with full path partitioning
-        if (parent) over.xpr.push(`PARTITION BY ${this.ref({ ref: ['_path_'] })}`)
+        if (parent) over.xpr.push(`PARTITION BY ${this.ref({ ref: ['_parent_path_'] })}`)
         if (orderBy) over.xpr.push(` ORDER BY ${this.orderBy(orderBy, localized)}`)
@@ -330,2 +335,3 @@ const rn = { xpr: [{ func: 'ROW_NUMBER', args: [] }, 'OVER', over], as: '$$RN$$' }
         q = cds.ql.SELECT(outputColumns.map(c => (c.elements ? c : { __proto__: c, ref: [this.column_name(c)] }))).from(q)
+        q.as = q.SELECT.from.as
         Object.defineProperty(q, 'elements', { value: elements })
@@ -343,7 +349,7 @@ Object.defineProperty(q, 'element', { value: element })
                       func: 'concat',
-                      args: [{ ref: ['_path_'] }, { val: `].${q.element.name}[` }],
+                      args: [{ ref: ['_parent_path_'] }, { val: `].${q.element.name}[`, param: false }],
                     },
-                    { func: 'lpad', args: [{ ref: ['$$RN$$'] }, { val: 6 }, { val: '0' }] },
+                    { func: 'lpad', args: [{ ref: ['$$RN$$'] }, { val: 6, param: false }, { val: '0', param: false }] },
                   ]
-                  : [{ val: '$[' }, { func: 'lpad', args: [{ ref: ['$$RN$$'] }, { val: 6 }, { val: '0' }] }],
+                  : [{ val: '$[', param: false }, { func: 'lpad', args: [{ ref: ['$$RN$$'] }, { val: 6, param: false }, { val: '0', param: false }] }],
               },
@@ -359,3 +365,3 @@ ],
             one
-              ? [{ ref: ['$$RN$$'] }, '=', { val: 1 }]
+              ? [{ ref: ['$$RN$$'] }, '=', { val: 1, param: false }]
               : limit.offset?.val
@@ -390,3 +396,8 @@ ? [
         this.cqn = q
-        this.temporary.unshift({ blobs: this._blobs, select: this.sql.substring(7) })
+        this.withclause.unshift(`${this.quote(alias)} as (${this.sql})`)
+        this.temporary.unshift({ blobs: this._blobs, select: `SELECT ${this._outputColumns} FROM ${this.quote(alias)}` })
+        if (this.values) {
+          this.temporaryValues.unshift(this.values)
+          this.values = this.temporaryValues.flat()
+        }
       }
@@ -413,5 +424,3 @@
 
-                const parent = cds.ql.clone(src)
-                parent.as = parent.SELECT.from.as || parent.SELECT.from.args[0].as
-                parent.SELECT.expand = true
+                const parent = src
                 x.element._foreignKeys.forEach(k => {
@@ -425,3 +434,3 @@ if (!parent.SELECT.columns.find(c => this.column_name(c) === k.parentElement.name)) {
                   join: 'inner',
-                  args: [parent, x.SELECT.from],
+                  args: [{ ref: [parent.as], as: parent.as }, x.SELECT.from],
                   on: x.SELECT.where,
@@ -434,4 +443,7 @@ as: x.SELECT.from.as,
 
+                const values = this.values
+                this.values = []
                 parent.SELECT.expand = true
                 this.SELECT(x)
+                this.values = values
                 return false
@@ -497,16 +509,7 @@ }
         let outputColumns = ''
-        outputColumns = `${path} as "_path_",${blobs} as "_blobs_",${expands} as "_expands_",${jsonColumn}`
+        outputColumns = `_path_ as "_path_",${blobs} as "_blobs_",${expands} as "_expands_",${jsonColumn}`
         if (blobColumns.length)
           outputColumns = `${outputColumns},${blobColumns.map(b => `${this.quote(b)} as "${b.replace(/"/g, '""')}"`)}`
-        if (this.foreignKeys?.length) {
-          outputColumns += ',' + this.foreignKeys.map(c => this.column_expr({ ref: c.ref.slice(-1) }))
-        }
-
-        if (structures.length && sql.length) {
-          this._outputColumns = outputColumns
-          // Select all columns to be able to use the _outputColumns in the outer select
-          sql = `*,${rawJsonColumn}`
-        } else {
-          sql = outputColumns
-        }
+        this._outputColumns = outputColumns
+        sql = `*,${path} as _path_,${rawJsonColumn}`
       }
@@ -517,5 +520,2 @@ return sql
     SELECT_expand(_, sql) {
-      if (this._outputColumns) {
-        return `SELECT ${this._outputColumns} FROM (${sql})`
-      }
       return sql
@@ -537,2 +537,3 @@ }
 
+    // REVISIT: Find a way to avoid overriding the whole function redundantly
     INSERT_entries(q) {
@@ -549,3 +550,3 @@ this.values = undefined
       const columns = elements
-        ? ObjectKeys(elements).filter(c => c in elements && !elements[c].virtual && !elements[c].isAssociation)
+        ? ObjectKeys(elements).filter(c => c in elements && !elements[c].virtual && !elements[c].value && !elements[c].isAssociation)
         : ObjectKeys(INSERT.entries[0])
@@ -786,2 +787,14 @@ this.columns = columns.filter(elements ? c => !elements[c]?.['@cds.extension'] : () => true)
 
+    list(list) {
+      const first = list.list[0]
+      // If the list only contains of lists it is replaced with a json function and a placeholder
+      if (this.values && first.list && !first.list.find(v => !v.val)) {
+        const extraction = first.list.map((v, i) => `"${i}" ${this.constructor.InsertTypeMap[typeof v.val]()} PATH '$.${i}'`)
+        this.values.push(JSON.stringify(list.list.map(l => l.list.reduce((l, c, i) => { l[i] = c.val; return l }, {}))))
+        return `(SELECT * FROM JSON_TABLE(?, '$' COLUMNS(${extraction})))`
+      }
+      // Call super for normal SQL behavior
+      return super.list(list)
+    }
+
     quote(s) {
@@ -834,3 +847,3 @@ // REVISIT: casing in quotes when reading from entities it uppercase
         let managed
-        if (val) managed = this.func({ func: 'session_context', args: [{ val }] })
+        if (val) managed = this.func({ func: 'session_context', args: [{ val, param: false }] })
         const type = this.insertType4(element)
@@ -892,2 +905,6 @@ let extract = sql ?? `${this.quote(name)} ${type} PATH '$.${name}'`
       array: () => `NVARCHAR(2147483647)`,
+
+      // Javascript types
+      string: () => `NVARCHAR(2147483647)`,
+      number: () => `DOUBLE`
     }
@@ -894,0 +911,0 @@

package.json

 {
   "name": "@cap-js/hana",
-  "version": "0.0.2",
+  "version": "0.0.3",
   "description": "CDS database service for SAP HANA",
@@ -5,0 +5,0 @@ "homepage": "https://cap.cloud.sap/",

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

111922

increased by2.13%

Lines of code

2636

increased by0.88%

No dependency changes