@sap/cds-sql - npm Package Compare versions

Comparing version 0.11.0 to 1.0.3

CHANGELOG.md

@@ -9,2 +9,22 @@ # Changelog
 
+## Version 1.0.3 - 2018-11-27
+
+### Changed
+
+- Throw root cause instead of CqnParseError
+- Throw root cause instead of SqlError
+
+### Fixed
+
+- Binary generated wrong SQL
+- Complex CQN with draft and expand for Hana
+- Expand modifies copy instead of original CQN
+- Expand with missing columns
+- Expand in combination with limit
+- Post processing of DateTime and Boolean
+
+## Version 0.12.0 - 2018-10-17
+
+- Refactoring and changes due to updated dependencies
+
 ## Version 0.11.0 - 2018-10-04
@@ -11,0 +31,0 @@

lib/cds.js

@@ -5,3 +5,3 @@ const _notInjected = () => {
 
-const _validateInjection = (cds) => {
+const _validateInjection = cds => {
   if (!cds || typeof cds !== 'object') {
@@ -15,3 +15,3 @@ throw new Error('Injected value is not of type `cds`')
     linked: (...args) => {
-      const {linked} = require('@sap/cds-reflect')
+      const { linked } = require('@sap/cds-reflect')
       injection.cds.linked = linked
@@ -21,3 +21,3 @@ return linked(...args)
     reflect: (...args) => {
-      const {reflect} = require('@sap/cds-reflect')
+      const { reflect } = require('@sap/cds-reflect')
       injection.cds.reflect = reflect
@@ -33,2 +33,5 @@ return reflect(...args)
         sql: _notInjected
+      },
+      for: {
+        odata: _notInjected
       }
@@ -61,9 +64,14 @@ }
       }
+    },
+    for: {
+      odata: (csn, options) => {
+        return injection.cds.compile.for.odata(csn, options)
+      }
     }
   },
   get config () {
-    return injection.cds.config || {data: {}}
+    return injection.cds.config || { data: {} }
   },
   get builtin () {
-    return injection.cds.builtin || {types: {}}
+    return injection.cds.builtin || { types: {} }
   }
@@ -70,0 +78,0 @@ }

lib/client/BaseClient.js

@@ -12,3 +12,3 @@ const cds = require('../cds')
   constructor (toService = []) {
-    this._toService = (toService instanceof Map) ? toService : new Map(toService)
+    this._toService = toService instanceof Map ? toService : new Map(toService)
     this._initializeDefaultDataTypeMap()
@@ -30,3 +30,3 @@ }
   _initializeDefaultDataTypeMap () {
-    this._typeConversionMap = require('../util/dataTypes').typeConversionMap
+    this._typeConversionMap = require('../utils/dataTypes').typeConversionMap
   }
@@ -54,3 +54,3 @@
     if (this._toBeDestroyed) {
-      const {errors: {InconsistentClientError}} = require('../')
+      const { errors: { InconsistentClientError } } = require('../')
       return Promise.reject(new InconsistentClientError())
@@ -69,11 +69,10 @@ }
   _addThenableToRun (promise) {
-    const then = (fn) => {
-      return promise.then(fn)
+    const then = (resolve, reject) => {
+      return promise.then(resolve).catch(reject)
     }
 
     then.run = (...args) => {
-      return promise
-        .then(() => {
-          return this.run(...args)
-        })
+      return promise.then(() => {
+        return this.run(...args)
+      })
     }
@@ -83,3 +82,3 @@
       then: then,
-      catch: (fn) => {
+      catch: fn => {
         return promise.catch(fn)
@@ -104,10 +103,9 @@ }
 
-    return this.run(this._queryForForeach(query), this._valuesForForeach(values))
-      .then((res) => {
-        if (Array.isArray(res)) {
-          res.forEach((row) => {
-            cb(row)
-          })
-        }
-      })
+    return this.run(this._queryForForeach(query), this._valuesForForeach(values)).then(res => {
+      if (Array.isArray(res)) {
+        res.forEach(row => {
+          cb(row)
+        })
+      }
+    })
   }
@@ -118,18 +116,17 @@
    * @param {Object|Promise} csn - the unreflected CSN or promise that will resolve into csn.
+   * @param {String} [dialect] - dialect, defaults to 'sqlite'
    * @returns {Promise} Promise, that resolves with undefined if successful or rejects with error if not.
    */
-  deploy (csn) {
+  deploy (csn, dialect = 'sqlite') {
     if (csn.then) {
-      return csn.then((csn) => {
-        return this._deploy(csn)
+      return csn.then(csn => {
+        return this._deploy(csn, dialect)
       })
     }
 
-    return this._deploy(csn)
+    return this._deploy(csn, dialect)
   }
 
-  _deploy (csn, dialect = 'sqlite') {
-    let names = cds.config.data && cds.config.data.sql_mapping ? cds.config.data.sql_mapping : 'plain'
-    names = (names === 'plain') ? 'flat' : 'deep'
-    const creates = cds.compile.to.sql(csn, {toSql: {dialect: dialect, names: names}})
+  _deploy (csn, dialect) {
+    const creates = cds.compile.to.sql(csn, { toSql: { dialect: dialect, names: cds.config.data.sql_mapping } })
     const sqls = this._prepareSqls(creates)
@@ -158,6 +155,6 @@
       if (type.toLowerCase() === 'view') {
-        res.drops.unshift({DROP: {view: name}})
+        res.drops.unshift({ DROP: { view: name } })
         res.creates.push(create)
       } else if (type.toLowerCase() === 'table') {
-        res.drops.push({DROP: {entity: name}})
+        res.drops.push({ DROP: { entity: name } })
         res.creates.unshift(create)
@@ -201,3 +198,3 @@ }
         SELECT: {
-          from: {ref: [entityName]}
+          from: { ref: [entityName] }
         }
@@ -204,0 +201,0 @@ }

lib/contains/containsCQNToLike.js

@@ -1,6 +0,6 @@
-const _isContains = (ref) => {
+const _isContains = ref => {
   return ref && /^(:?not )?contains$/.test(ref[0].toLowerCase())
 }
 
-const _getCQN = (cqn) => {
+const _getCQN = cqn => {
   if (cqn.SELECT) {
@@ -20,6 +20,6 @@ return cqn.SELECT
  */
-const hasContains = (cqn) => {
+const hasContains = cqn => {
   const _partialCqn = _getCQN(cqn)
 
-  return _partialCqn && _partialCqn.where ? _partialCqn.where.some(({ref}) => _isContains(ref)) : false
+  return _partialCqn && _partialCqn.where ? _partialCqn.where.some(({ ref }) => _isContains(ref)) : false
 }
@@ -31,3 +31,5 @@
     if (columns[i].ref) {
-      res.push({ref: ['lower', {args: [columns[i]]}]}, notContains ? 'not like' : 'like', {val: `%${searchText.replace(/(_|%)/g, '\\$1')}%`})
+      res.push({ ref: ['lower', { args: [columns[i]] }] }, notContains ? 'not like' : 'like', {
+        val: `%${searchText.replace(/(_|%)/g, '\\$1')}%`
+      })
       if (columns[i + 1] && columns[i + 1].ref) {
@@ -49,3 +51,7 @@ res.push(notContains ? 'and' : 'or')
       const previousElementIsNot = typeof args[i - 1] === 'string' && args[i - 1].toLowerCase() === 'not'
-      res.push('(', ..._createLikeComparison(previousElementIsNot ? !notContains : notContains, columns, args[i].val.toLowerCase()), ')')
+      res.push(
+        '(',
+        ..._createLikeComparison(previousElementIsNot ? !notContains : notContains, columns, args[i].val.toLowerCase()),
+        ')'
+      )
     } else if (args[i] === 'and' || args[i] === 'or') {
@@ -64,3 +70,3 @@ res.push(args[i])
  */
-const replaceContainsWithLike = (cqn) => {
+const replaceContainsWithLike = cqn => {
   const resCQN = {}
@@ -77,5 +83,9 @@ const _partialCqn = _getCQN(cqn)
   // Create a different where array
-  resCQN.where = _partialCqn.where.reduce((res, curr) => _isContains(curr.ref) ? [...res, '(', ..._transformCQN(curr.ref[0].toLowerCase().includes('not')
-    , curr.ref[1].args), ')'] : [...res, curr]
-    , [])
+  resCQN.where = _partialCqn.where.reduce(
+    (res, curr) =>
+      _isContains(curr.ref)
+        ? [...res, '(', ..._transformCQN(curr.ref[0].toLowerCase().includes('not'), curr.ref[1].args), ')']
+        : [...res, curr],
+    []
+  )
 
@@ -82,0 +92,0 @@ return {

lib/data-conversion/post-processing.js

@@ -1,5 +0,5 @@
-const {resolveAssociation, isAssociation, isComplex} = require('../util/associations')
+const { resolveAssociation, isAssociation, isComplex } = require('../utils/associations')
 
 // TODO: Remove workaround, once provided by CSN
-const draftAdministrativeData = require('../util/administrativeData')
+const draftAdministrativeData = require('../utils/administrativeData')
 
@@ -14,3 +14,3 @@ /**
  */
-const _getMethod = (fn) => {
+const _getMethod = fn => {
   if (typeof fn === 'function') {
@@ -72,3 +72,8 @@ return fn
 const _extractRefs = (from, as) => {
-  if (from.hasOwnProperty('join')) { // cqn with join in from
+  if (from.SELECT) {
+    return _extractRefs(from.SELECT.from, from.SELECT.as)
+  }
+
+  if (from.hasOwnProperty('join')) {
+    // cqn with join in from
     return _refs(from.args)
@@ -81,3 +86,3 @@ }
 
-  const ref = {ref: from.ref}
+  const ref = { ref: from.ref, as: from.as }
 
@@ -104,2 +109,3 @@ if (as) {
     elements.set(`${from.as}.${key}`, convertFunction)
+    elements.set(`${from.as}_${key}`, convertFunction)
   }
@@ -109,3 +115,3 @@ }
 const _filterUnique = (value, index, arr) => {
-  return (arr.indexOf(value) === index)
+  return arr.indexOf(value) === index
 }
@@ -133,3 +139,4 @@
 
-    const entity = (entityName === 'DRAFT.DraftAdministrativeData') ? draftAdministrativeData : csn.definitions[entityName]
+    const entity =
+      entityName === 'DRAFT.DraftAdministrativeData' ? draftAdministrativeData : csn.definitions[entityName]
 
@@ -142,3 +149,10 @@ for (const key of Object.keys(entity.elements)) {
         for (const complexKey of Object.keys(element.elements)) {
-          _addMapperFunction(elements, toService, `${key}_${complexKey}`, element.elements[complexKey].type, from, allCombinations)
+          _addMapperFunction(
+            elements,
+            toService,
+            `${key}_${complexKey}`,
+            element.elements[complexKey].type,
+            from,
+            allCombinations
+          )
         }
@@ -155,3 +169,3 @@ } else {
 
-const _getCastFunction = ({type}) => {
+const _getCastFunction = ({ type }) => {
   switch (type) {
@@ -182,3 +196,3 @@ case 'cds.Boolean':
       const joinedName = element.ref.join('.')
-      const name = (element.as) ? element.as : joinedName
+      const name = element.as ? element.as : joinedName
 
@@ -189,2 +203,4 @@ if (element.cast) {
         mapper.set(name, elements.get(joinedName))
+      } else if (elements.has(name)) {
+        mapper.set(name, elements.get(name))
       }
@@ -213,3 +229,3 @@ } else if (element.as && element.cast) {
 
-  if (Array.isArray(cqn.SELECT.columns) && cqn.SELECT.columns.length !== 0) {
+  if (Array.isArray(cqn.SELECT.columns) && cqn.SELECT.columns.length !== 0 && !cqn.SELECT.columns.includes('*')) {
     return _getMapperForListedElements(toService, csn, cqn)
@@ -300,4 +316,4 @@ }
 
-const _mapHasEntries = (map) => {
-  return (map && map instanceof Map && map.size !== 0)
+const _mapHasEntries = map => {
+  return map && map instanceof Map && map.size !== 0
 }
@@ -393,3 +409,3 @@
 
-const _getKeyMapForListedElements = (cqn) => {
+const _getKeyMapForListedElements = cqn => {
   const map = new Map()
@@ -416,3 +432,3 @@
 const getPropertyMapper = (csn, cqn, convertKeyNames) => {
-  const {config: {data: {sql_mapping: sqlMapping}}} = require('../cds')
+  const { config: { data: { sql_mapping: sqlMapping } } } = require('../cds')
 
@@ -423,4 +439,4 @@ if (!convertKeyNames || sqlMapping !== 'plain' || !cqn.SELECT) {
 
-  // No element/column specified
-  if (Array.isArray(cqn.SELECT.columns) && cqn.SELECT.columns.length !== 0) {
+  // No element/column or '*' specified
+  if (Array.isArray(cqn.SELECT.columns) && cqn.SELECT.columns.length !== 0 && !cqn.SELECT.columns.includes('*')) {
     return _getKeyMapForListedElements(cqn)
@@ -427,0 +443,0 @@ }

lib/errors/index.js

@@ -6,3 +6,2 @@ /**
 
-const CqnParseError = require('./CqnParseError')
 const FeatureNotSupportedError = require('./FeatureNotSupportedError')
@@ -14,3 +13,2 @@ const IllegalFunctionArgumentError = require('./IllegalFunctionArgumentError')
 const UnsupportedDataType = require('./UnsupportedDataType')
-const SqlError = require('./SqlError')
 
@@ -21,3 +19,2 @@ /**
 module.exports = {
-  CqnParseError,
   FeatureNotSupportedError,
@@ -28,4 +25,3 @@ IllegalFunctionArgumentError,
   InvalidQuotingStyleError,
-  UnsupportedDataType,
-  SqlError
+  UnsupportedDataType
 }

lib/expand/expandCQNToJoin.js

 const crypto = require('crypto')
 
-const cds = require('../cds')
-const {getOnCond} = require('../oncond/generateOnCond')
+const getColumns = require('../utils/columns')
+const { getOnCond } = require('../oncond/generateOnCond')
 
@@ -12,9 +12,6 @@ // Symbols are used to add extra information in response structure
 const IDENTIFIER = Symbol.for('identifier')
-const IS_DRAFT = Symbol.for('isDraft')
+const IS_ACTIVE = Symbol.for('isActive')
 const IS_UNION_DRAFT = Symbol.for('isUnionDraft')
 const DRAFT_COLUMNS = ['IsActiveEntity', 'HasActiveEntity', 'HasDraftEntity', 'DraftAdministrativeData_DraftUUID']
 
-// TODO: Remove workaround, once provided by CSN
-const draftAdministrativeData = require('../util/administrativeData')
-
 class JoinCQNFromExpanded {
@@ -51,3 +48,3 @@ constructor (cqn, csn, useWindow) {
     for (const arg of SELECT.from.SET.args) {
-      const {table} = this._getRef(arg.SELECT)
+      const { table } = this._getRef(arg.SELECT)
       // Do not handle non draft cases, as it will be unclear, which entity to pick from
@@ -60,15 +57,25 @@ if (table.endsWith('_drafts')) {
 
-  _isDraftExpand (table) {
-    if (this._csn.definitions[table]) {
-      return false
-    }
+  _isDraftTargetActive (table) {
+    return Boolean(this._csn.definitions[table])
+  }
 
-    if (this._csn.definitions[table.replace(/_drafts$/i, '')]) {
-      // Workaround, as associations are not redirected at service yet
-      this._draftService = table.replace(/\.\w+$/i, '')
+  _isDraftTree (table) {
+    // TODO: this is a workaround until the service is flagged as draft enabled by cds-services
+    if (!this._isDraft) {
+      this._isDraft = this._isDraftEnabled(
+        this._csn.definitions[table] || this._csn.definitions[table.replace(/_drafts$/i, '')]
+      )
 
-      return true
+      if (this._isDraft && !this._draftService) {
+        this._draftService = table.replace(/\.\w+$/i, '')
+      }
     }
+
+    return this._isDraft
   }
 
+  _isDraftEnabled (entity) {
+    return Boolean(entity['@Common.DraftNode.PreparationAction'] || entity['@Common.DraftRoot.PreparationAction'])
+  }
+
   /**
@@ -85,2 +92,4 @@ * Build first level of expanding regarding to many and all to one if not part of a nested to many expand.
     const table = unionTable || this._getRef(SELECT).table
+    const isDraftTree = this._isDraftTree(table)
+    const entity = this._getEntityForTable(table)
 
@@ -90,8 +99,9 @@ if (unionTable) {
     }
-    if (this._isDraftExpand(table)) {
-      readToOneCQN[IS_DRAFT] = true
+
+    if (isDraftTree) {
+      readToOneCQN[IS_ACTIVE] = this._isDraftTargetActive(table)
     }
 
     this._expandedToFlat({
-      entity: this._getEntityForTable(table, readToOneCQN[IS_DRAFT]),
+      entity: entity,
       givenColumns: SELECT.columns,
@@ -118,10 +128,10 @@ readToOneCQN: readToOneCQN,
   _getTableAlias (SELECT, toManyTree, unionTable) {
-    return this._createHash((toManyTree.length === 0) ? (unionTable || this._getRef(SELECT).table) : toManyTree.join(':'))
+    return this._createHash(toManyTree.length === 0 ? unionTable || this._getRef(SELECT).table : toManyTree.join(':'))
   }
 
   _getRef (SELECT) {
-    const table = (SELECT.from.hasOwnProperty('join')) ? this._getRefFromJoin(SELECT.from.args) : SELECT.from
+    const table = SELECT.from.hasOwnProperty('join') ? this._getRefFromJoin(SELECT.from.args) : SELECT.from
 
     return {
-      table: (table.SELECT) ? this._getRef(table.SELECT).table : table.ref[0],
+      table: table.SELECT ? this._getRef(table.SELECT).table : table.ref[0],
       as: table.as
@@ -132,2 +142,6 @@ }
   _getRefFromJoin (args) {
+    if (args[0].join) {
+      return this._getRefFromJoin(args[0].args)
+    }
+
     if (args[0].ref) {
@@ -148,15 +162,14 @@ return args[0]
     // Prefixed as an alias cannot start with a number at SQL.
-    return `t${crypto.createHash('md5').update(value).digest('hex')}`
+    return `t${crypto
+      .createHash('md5')
+      .update(value)
+      .digest('hex')}`
   }
 
-  _getEntityForTable (table, isDraft) {
-    if (table === 'DraftAdministrativeData') {
-      return cds.reflect(draftAdministrativeData)
+  _getEntityForTable (table) {
+    if (this._isDraft) {
+      return this._csn.definitions[table] || this._csn.definitions[table.replace(/_drafts/i, '')]
     }
 
-    if (isDraft) {
-      return cds.reflect(this._csn.definitions[table.replace(/_drafts/i, '')])
-    }
-
-    return cds.reflect(this._csn.definitions[table])
+    return this._csn.definitions[table]
   }
@@ -172,3 +185,3 @@
   _getReadToOneCQN (SELECT, tableAlias) {
-    const cqn = Object.assign({}, SELECT, {columns: []})
+    const cqn = Object.assign({}, SELECT, { columns: [], from: Object.assign({}, SELECT.from) })
 
@@ -179,3 +192,3 @@ if (cqn.from.hasOwnProperty('join')) {
       if (cqn.from.SET) {
-        this._adaptUnionArgs(cqn.from.SET.args)
+        cqn.from.SET = Object.assign({}, cqn.from.SET, { args: this._adaptUnionArgs(cqn.from.SET.args) })
       }
@@ -190,22 +203,28 @@
   _adaptJoin (tableAlias, from) {
-    const target = (from.args[0].ref) ? from.args[0] : from.args[from.args.length - 1]
-    const originalIdentifier = target.as || target.ref[0]
-    target.as = tableAlias
+    from.args = from.args.slice(0)
 
-    for (const column of from.on) {
-      if (column.ref && column.ref[0] === originalIdentifier) {
-        column.ref[0] = tableAlias
-      }
-    }
+    const index = from.args[0].ref ? 0 : from.args.length - 1
+    const target = Object.assign({}, from.args[index], { as: tableAlias })
+    const originalIdentifier = from.args[index].as || from.args[index].ref[0]
+
+    from.args[index] = target
+    from.on = from.on.map(column => {
+      return column.ref && column.ref[0] === originalIdentifier
+        ? Object.assign({}, column, { ref: [tableAlias, column.ref[1]] })
+        : column
+    })
   }
 
   _adaptUnionArgs (args) {
-    for (const arg of args) {
+    return args.map(arg => {
       if (arg.SELECT.columns) {
         // remove the expands from the sub selects, as they are joined against the unioned result
-        arg.SELECT.columns = arg.SELECT.columns.filter((element) => {
-          return (!element.expand)
+        arg = Object.assign({}, arg, { SELECT: Object.assign({}, arg.SELECT) })
+        arg.SELECT.columns = arg.SELECT.columns.filter(element => {
+          return !element.expand
         })
       }
-    }
+
+      return arg
+    })
   }
@@ -223,3 +242,3 @@
     if (cqn.where) {
-      cqn.where = cqn.where.map((element) => {
+      cqn.where = cqn.where.map(element => {
         return this._checkOrderByWhereElementRecursive(cqn, element, tableAlias)
@@ -230,3 +249,3 @@ })
     if (cqn.orderBy) {
-      cqn.orderBy = cqn.orderBy.map((element) => {
+      cqn.orderBy = cqn.orderBy.map(element => {
         return this._checkOrderByWhereElementRecursive(cqn, element, tableAlias)
@@ -253,7 +272,11 @@ })
       element = Object.assign({}, element)
-      element.xpr = element.xpr.map((nestedElement) => {
+      element.xpr = element.xpr.map(nestedElement => {
         return this._checkOrderByWhereElementRecursive(cqn, nestedElement, tableAlias)
       })
-    } else if (element.SELECT) {
-      this._adaptWhereSELECT(this._getUnionTable(cqn) || this._getRef(cqn), element.SELECT.where, tableAlias)
+    } else if (element.SELECT && element.SELECT.where) {
+      element = {
+        SELECT: Object.assign({}, element.SELECT, {
+          where: this._adaptWhereSELECT(this._getUnionTable(cqn) || this._getRef(cqn), element.SELECT.where, tableAlias)
+        })
+      }
     }
@@ -266,20 +289,13 @@
    * Change alias of most outer table query to md5 sum.
-   * @param {Object} aliasedTable
-   * @param {Array} args
-   * @param {string} tableAlias
    * @private
    */
   _adaptWhereSELECT (aliasedTable, where, tableAlias) {
-    if (!where) {
-      return
-    }
-
-    for (const element of where) {
-      if (this._elementAliasNeedsReplacement(element, aliasedTable)) {
-        element.ref[0] = tableAlias
-      }
-    }
+    return where.map(element => {
+      return this._elementAliasNeedsReplacement(element, aliasedTable)
+        ? Object.assign({}, element, { ref: [tableAlias, element.ref[1]] })
+        : element
+    })
   }
 
-  _elementAliasNeedsReplacement (element, {table, as}) {
+  _elementAliasNeedsReplacement (element, { table, as }) {
     if (!element.ref || element.ref.length !== 2) {
@@ -299,11 +315,15 @@ return false
   _functionNeedsReplacement (cqn, tableAlias, element) {
-    if (typeof element.ref[0] !== 'string' ||
+    if (
+      typeof element.ref[0] !== 'string' ||
       typeof element.ref[1] !== 'object' ||
-      !Array.isArray(element.ref[1].args)) {
+      !Array.isArray(element.ref[1].args)
+    ) {
       return
     }
 
-    element.ref[1].args = element.ref[1].args.map((arg) => {
+    element.ref[1] = Object.assign({}, element.ref[1])
+    element.ref[1].args = element.ref[1].args.map(arg => {
       if (Array.isArray(arg.list)) {
-        arg.list = arg.list.map((item) => {
+        arg = Object.assign({}, arg)
+        arg.list = arg.list.map(item => {
           return this._checkOrderByWhereElementRecursive(cqn, item, tableAlias)
@@ -331,3 +351,3 @@ })
    */
-  _expandedToFlat ({entity, givenColumns, readToOneCQN, tableAlias, toManyTree}) {
+  _expandedToFlat ({ entity, givenColumns, readToOneCQN, parentAlias, tableAlias, toManyTree }) {
     const toManyColumns = []
@@ -338,5 +358,5 @@ const mappings = this._getMappingObject(toManyTree)
       // To many can only be build, once all other columns have been processed.
-      if (this._isExpandToMany(column, entity, readToOneCQN[IS_DRAFT])) {
-        mappings[column.ref[0]] = {[TO_MANY]: true}
-        toManyColumns.push(column)
+      if (this._isExpandToMany(column, entity)) {
+        mappings[column.ref[0]] = { [TO_MANY]: true }
+        toManyColumns.push({ parentAlias: tableAlias, column: column })
 
@@ -352,5 +372,4 @@ // Expands with to one target can be processed directly
         })
-
+      } else {
         // No expand, directly add the column and its mapping.
-      } else {
         readToOneCQN.columns.push(this._addAliasToColumn(column, entity, tableAlias, mappings))
@@ -361,3 +380,3 @@ }
     // only as second step handle expand to many, or else keys might still be unknown
-    this._toMany({entity, readToOneCQN, tableAlias, toManyColumns, toManyTree, mappings})
+    this._toMany({ entity, readToOneCQN, tableAlias, toManyColumns, toManyTree, mappings })
   }
@@ -385,9 +404,6 @@
 
-  _isExpandToMany (column, entity, isDraft) {
-    // TODO: remove once the targets are service entity to service entity and DraftAdministrativeData is included
-    if (column.expand && column.ref[0] === 'DraftAdministrativeData') {
-      return false
-    }
-
-    return column.expand && cds.reflect(entity.def.elements[column.ref[0]]).is2many
+  _isExpandToMany (column, entity) {
+    return column.expand && column.ref[0] === 'DraftAdministrativeData'
+      ? false
+      : column.expand && entity.elements[column.ref[0]].is2many
   }
@@ -405,20 +421,29 @@
    */
-  _addJoinAndElements ({column, entity, readToOneCQN, toManyTree, parentAlias}) {
+  _addJoinAndElements ({ column, entity, readToOneCQN, toManyTree, parentAlias }) {
     const extendedToManyTree = toManyTree.concat(column.ref)
     const tableAlias = this._createHash(extendedToManyTree.join(':'))
-    const target = (entity.def.elements[column.ref[0]]) ? entity.def.elements[column.ref[0]].target : column.ref[0]
+    const target = entity.elements[column.ref[0]] ? entity.elements[column.ref[0]].target : column.ref[0]
 
+    // TODO: If draft union and composition target add union as to be joined
     readToOneCQN.from = {
       args: [
-        (readToOneCQN.from.SET) ? this._unionToSubQuery(readToOneCQN) : readToOneCQN.from,
-        {ref: [this._refFromRefByExpand(column.ref[0], entity.def.elements)], as: tableAlias}
+        readToOneCQN.from.SET ? this._unionToSubQuery(readToOneCQN) : readToOneCQN.from,
+        { ref: [this._refFromRefByExpand(column.ref[0], entity.elements)], as: tableAlias }
       ],
-      join: (column.ref[0] === 'DraftAdministrativeData' || !entity.def.elements[column.ref[0]].notNull) ? 'left' : 'inner',
-      on: this._getOnCond(entity.def.elements, column.ref[0], tableAlias, parentAlias, readToOneCQN[IS_DRAFT])
+      join:
+        column.ref[0] === 'DraftAdministrativeData' || !entity.elements[column.ref[0]].notNull || this._isDraft
+          ? 'left'
+          : 'inner',
+      on: this._getOnCond(entity.elements, column.ref[0], tableAlias, parentAlias, readToOneCQN)
     }
 
+    if (column.ref[0] !== 'DraftAdministrativeData') {
+      this._addJoinKeyColumnsToUnion(readToOneCQN.from.args, readToOneCQN.from.on, parentAlias)
+    }
+
     this._expandedToFlat({
-      entity: this._getEntityForTable(target, readToOneCQN[IS_DRAFT]),
+      entity: this._getEntityForTable(target),
       givenColumns: column.expand,
       readToOneCQN: readToOneCQN,
+      parentAlias: parentAlias,
       tableAlias: tableAlias,
@@ -429,2 +454,6 @@ toManyTree: extendedToManyTree
 
+  _refFromRefByExpand (column, elements) {
+    return column === 'DraftAdministrativeData' ? 'DRAFT.DraftAdministrativeData' : elements[column].target
+  }
+
   _unionToSubQuery (readToOneCQN) {
@@ -440,24 +469,103 @@ return {
 
-  _refFromRefByExpand (column, elements) {
-    return (column === 'DraftAdministrativeData') ? 'DRAFT.DraftAdministrativeData' : elements[column].target
+  _getAliases (columns) {
+    return columns.reduce((aliases, entry) => {
+      if (!entry.ref) {
+        return aliases
+      }
+
+      if (!aliases[entry.ref[0]]) {
+        aliases[entry.ref[0]] = {}
+      }
+
+      aliases[entry.ref[0]][entry[IDENTIFIER]] = entry.as
+      return aliases
+    }, {})
   }
 
-  _getOnCond (elements, column, tableAlias, parentAlias, isDraft) {
+  _getSubSelectColumns (cqn) {
+    const args = cqn.args || cqn.from.args
+
+    if (args) {
+      for (const arg of args) {
+        if (arg.ref) {
+          continue
+        }
+
+        if (arg.SELECT && arg.SELECT.columns.some(column => column[IDENTIFIER])) {
+          return arg.SELECT.columns
+        }
+
+        return this._getSubSelectColumns(arg.SELECT || arg)
+      }
+    }
+
+    const columnn = cqn.from.SELECT ? cqn.from.SELECT.columns : cqn.columns
+    return columnn.some(column => column[IDENTIFIER]) ? columnn : []
+  }
+
+  _getOnCond (elements, column, tableAlias, parentAlias, readToOneCQN) {
     if (column === 'DraftAdministrativeData') {
-      if (isDraft) {
-        return [{ref: [tableAlias, 'DraftUUID']}, '=', {ref: [parentAlias, 'DraftAdministrativeData_DraftUUID']}]
+      if (readToOneCQN[IS_ACTIVE]) {
+        return [{ ref: [tableAlias, 'DraftUUID'] }, '=', { val: null }]
       }
 
-      return [{ref: [tableAlias, 'DraftUUID']}, '=', {val: null}]
+      return [{ ref: [tableAlias, 'DraftUUID'] }, '=', { ref: [parentAlias, 'DraftAdministrativeData_DraftUUID'] }]
     }
 
-    return getOnCond(elements[column], column, this._csn, tableAlias, parentAlias)
+    // No window function/ sub select
+    const subSelectColumns = this._getSubSelectColumns(readToOneCQN)
+
+    if (subSelectColumns.length === 0) {
+      return getOnCond(elements[column], column, this._csn, tableAlias, parentAlias)
+    }
+
+    const aliases = this._getAliases(subSelectColumns)
+
+    const on = getOnCond(elements[column], column, this._csn, tableAlias, parentAlias)
+
+    for (const element of on) {
+      if (element.ref && aliases[element.ref[0]] && aliases[element.ref[0]][element.ref[1]]) {
+        element.ref[1] = aliases[element.ref[0]][element.ref[1]]
+      }
+    }
+
+    return on
   }
 
+  _addJoinKeyColumnsToUnion (args, on, parentAlias) {
+    for (const arg of args) {
+      if (arg.ref) {
+        continue
+      }
+
+      if (arg.args) {
+        this._addJoinKeyColumnsToUnion(arg.args, on, parentAlias)
+      } else if (arg.SELECT.from.SET && arg.SELECT.as === parentAlias) {
+        this._addColumns(arg.SELECT.from.SET.args, on, parentAlias)
+      }
+    }
+  }
+
+  _addColumns (args, on, parentAlias) {
+    const [{ SELECT: { columns } }] = args
+    const keyColumns = on
+      .filter(entry => {
+        return (
+          entry.ref &&
+          entry.ref[0] === parentAlias &&
+          !columns.some(column => column.ref && column.ref[0] === entry.ref[1])
+        )
+      })
+      .map(entry => ({ ref: [entry.ref[1]] }))
+
+    if (keyColumns.length === 0) return
+
+    for (const { SELECT: { columns } } of args) {
+      columns.push(...keyColumns)
+    }
+  }
+
   /**
    * Add an unique alias to each column, to avoid ambiguity.
    * Add this information to the post process config.
-   * @param column
-   * @param entity
-   * @param tableAlias
    * @returns {Object}
@@ -488,3 +596,3 @@ * @private
     // No column name specified means false
-    return (column.ref && typeof column.ref[column.ref.length - 1] !== 'string')
+    return column.ref && typeof column.ref[column.ref.length - 1] !== 'string'
   }
@@ -500,5 +608,5 @@
     // Add table alias or name to handle cases, where joined tables have same column names
-    if (this._isElement(column.ref, entity.def)) {
-      const alias = tableAlias || entity.def.name
-      aliasedElement.ref = (alias) ? [alias, column.ref[0]] : [column.ref[0]]
+    if (this._isElement(column.ref, entity)) {
+      const alias = tableAlias || entity.name
+      aliasedElement.ref = alias ? [alias, column.ref[0]] : [column.ref[0]]
     }
@@ -515,3 +623,3 @@
     if (column.as) {
-      return (column.as.startsWith(`${tableAlias}_`)) ? column.ref[column.ref.length - 1] : column.as
+      return column.as.startsWith(`${tableAlias}_`) ? column.ref[column.ref.length - 1] : column.as
     }
@@ -522,3 +630,3 @@
 
-  _isElement (ref, def) {
+  _isElement (ref, entity) {
     if (!ref || ref.length !== 1) {
@@ -529,52 +637,11 @@ return false
     // Normal element
-    if (def.elements[ref[0]]) {
+    if (entity.elements[ref[0]]) {
       return true
     }
 
-    // Auto gen column from managed association
-    if (this._isAutoGenColumn(ref[0], def.elements)) {
-      return true
-    }
-
     // Draft column
-    if (DRAFT_COLUMNS.includes(ref[0])) {
-      return true
-    }
-
-    return this._isComplexType(ref[0].split('_'), def)
+    return DRAFT_COLUMNS.includes(ref[0])
   }
 
-  _isAutoGenColumn (columnName, elements) {
-    for (const key of Object.keys(elements)) {
-      const element = elements[key]
-      if (columnName.startsWith(key) && element.type === 'cds.Association' && element.foreignKeys) {
-        // works only for single keys
-        if (columnName === `${key}_${Object.getOwnPropertyNames(element.foreignKeys)[0]}`) {
-          return true
-        }
-      }
-    }
-
-    return false
-  }
-
-  _isComplexType (parts, def) {
-    const removed = []
-
-    // Remove from the end until there is no more left
-    while (parts.length !== 0) {
-      const element = def.elements[parts.join('_')]
-
-      if (element) {
-        // If nothing has been removed, we are at the end of the nesting
-        return (removed.length === 0) ? true : this._isComplexType(removed, element)
-      }
-
-      removed.unshift(parts.pop())
-    }
-
-    return false
-  }
-
-  _getKeyNames (entity, includeForeign = false) {
+  _getKeyNames (entity) {
     const keys = entity.keys
@@ -589,8 +656,8 @@
     for (const key of Object.keys(keys)) {
-      if (!keys[key].foreignKeys) {
-        keyNames.push(key)
-      } else {
+      if (keys[key].foreignKeys) {
         for (const foreign of Object.keys(keys[key].foreignKeys)) {
           keyNames.push(`${key}_${foreign}`)
         }
+      } else {
+        keyNames.push(key)
       }
@@ -602,3 +669,3 @@ }
 
-  _toMany ({entity, readToOneCQN, tableAlias, toManyColumns, toManyTree, mappings}) {
+  _toMany ({ entity, readToOneCQN, tableAlias, toManyColumns, toManyTree, mappings }) {
     if (toManyColumns.length === 0) {
@@ -608,12 +675,16 @@ return
 
-    this._addKeysIfNeeded({entity, readToOneCQN, tableAlias})
+    this._addKeysIfNeeded({ entity, readToOneCQN, tableAlias })
 
-    for (const column of toManyColumns) {
-      this._createJoinCQNFromExpanded(this._buildExpandedCQN({
-        column,
-        entity,
-        readToOneCQN,
-        toManyTree,
-        mappings
-      }), toManyTree.concat([column.ref[0]]))
+    for (const { column, parentAlias } of toManyColumns) {
+      this._createJoinCQNFromExpanded(
+        this._buildExpandedCQN({
+          column,
+          entity,
+          readToOneCQN,
+          toManyTree,
+          mappings,
+          parentAlias
+        }),
+        toManyTree.concat([column.ref[0]])
+      )
     }
@@ -626,8 +697,16 @@ }
    */
-  _addKeysIfNeeded ({entity, readToOneCQN, tableAlias}) {
-    for (const name of this._getMissingKeys({entity, readToOneCQN, tableAlias})) {
-      readToOneCQN.columns.push({
-        as: `${tableAlias}_${name}`,
-        ref: [tableAlias, name]
-      })
+  _addKeysIfNeeded ({ entity, readToOneCQN, tableAlias }) {
+    for (const name of this._getMissingKeys({ entity, readToOneCQN, tableAlias })) {
+      if (name === 'IsActiveEntity') {
+        readToOneCQN.columns.push({
+          val: readToOneCQN[IS_ACTIVE],
+          as: 'IsActiveEntity',
+          cast: { type: 'cds.Boolean' }
+        })
+      } else {
+        readToOneCQN.columns.push({
+          as: `${tableAlias}_${name}`,
+          ref: [tableAlias, name]
+        })
+      }
     }
@@ -644,3 +723,3 @@ }
    */
-  _getMissingKeys ({entity, readToOneCQN, tableAlias}) {
+  _getMissingKeys ({ entity, readToOneCQN, tableAlias }) {
     const keyNames = this._getKeyNames(entity)
@@ -652,12 +731,4 @@
 
-    return keyNames.filter((name) => {
-      let missing = true
-
-      for (const column of readToOneCQN.columns) {
-        if (column.as === `${tableAlias}_${name}`) {
-          missing = false
-        }
-      }
-
-      return missing
+    return keyNames.filter(name => {
+      return !readToOneCQN.columns.some(column => column.as === `${tableAlias}_${name}` || column.as === name)
     })
@@ -671,9 +742,9 @@ }
    */
-  _buildExpandedCQN ({column, entity, readToOneCQN, toManyTree, mappings}) {
-    const ref = this._getJoinRef(entity.def.elements, column.ref[0], readToOneCQN[IS_DRAFT])
+  _buildExpandedCQN ({ column, entity, readToOneCQN, toManyTree, mappings, parentAlias }) {
+    const ref = this._getJoinRef(entity.elements, column.ref[0], readToOneCQN[IS_ACTIVE])
     const tableAlias = this._createHash(toManyTree.concat(column.ref).join(':'))
-    const on = getOnCond(entity.def.elements[column.ref[0]], column.ref[0], this._csn, tableAlias, 'filterExpand')
-    const filterExpand = this._getFilterExpandCQN(readToOneCQN, on)
-    const joinColumns = this._getJoinColumnsFromOnAddToMapping(mappings[column.ref[0]], readToOneCQN, on)
-    const expandedEntity = cds.reflect(this._csn.definitions[entity.def.elements[column.ref[0]].target])
+    const on = getOnCond(entity.elements[column.ref[0]], column.ref[0], this._csn, tableAlias, 'filterExpand')
+    const filterExpand = this._getFilterExpandCQN(readToOneCQN, on, parentAlias)
+    const joinColumns = this._getJoinColumnsFromOnAddToMapping(mappings[column.ref[0]], parentAlias, on)
+    const expandedEntity = this._csn.definitions[entity.elements[column.ref[0]].target]
 
@@ -683,15 +754,19 @@ const cqn = {
         join: 'inner',
-        args: [
-          {ref: [ref], as: tableAlias},
-          filterExpand
-        ],
+        args: [{ ref: [ref], as: tableAlias }, filterExpand],
         on: on
-      },
-      columns: this._getColumnsForExpand({tableAlias, columnList: column, entity: expandedEntity, joinColumns})
+      }
     }
 
-    if (readToOneCQN[IS_DRAFT] && ref.endsWith('_drafts')) {
-      cqn[IS_DRAFT] = true
+    if (typeof readToOneCQN[IS_ACTIVE] === 'boolean') {
+      cqn[IS_ACTIVE] = !ref.endsWith('_drafts')
     }
 
+    cqn.columns = this._getColumnsForExpand({
+      tableAlias,
+      columnList: column,
+      entity: expandedEntity,
+      joinColumns,
+      isActive: cqn[IS_ACTIVE]
+    })
+
     if (column.where) {
@@ -709,7 +784,7 @@ cqn.where = this._copyWhere(column.where)
 
-    return this._adaptWhereOrderBy(this._addWindowIfNeeded(cqn, column, tableAlias), tableAlias)
+    return this._adaptWhereOrderBy(this._addWindowIfNeeded(cqn, column, tableAlias, expandedEntity), tableAlias)
   }
 
-  _getJoinRef (elements, column, isDraft) {
-    if (!isDraft || elements[column].type !== 'cds.Composition') {
+  _getJoinRef (elements, column, isActive) {
+    if (typeof isActive !== 'boolean' || isActive || elements[column].type !== 'cds.Composition') {
       return elements[column].target
@@ -726,3 +801,3 @@ }
   _addLimitToCqn (cqn, column, tableAlias, expandedEntity) {
-    const columns = this._getKeyColumnForTarget(tableAlias, expandedEntity)
+    const columns = this._getKeyColumnForTarget(tableAlias, cqn[IS_ACTIVE], expandedEntity)
     const inSelect = this._getLimitInSelect(cqn, columns, column.limit, column.orderBy)
@@ -743,6 +818,8 @@
    */
-  _getKeyColumnForTarget (tableAlias, expandedEntity) {
-    return this._getKeyNames(expandedEntity).map((column) => {
-      return {ref: [tableAlias, column]}
-    })
+  _getKeyColumnForTarget (tableAlias, isActive, expandedEntity) {
+    return this._getKeyNames(expandedEntity)
+      .filter(column => typeof isActive !== 'boolean' || column !== 'IsActiveEntity')
+      .map(column => {
+        return { ref: [tableAlias, column] }
+      })
   }
@@ -754,3 +831,3 @@
         columns: this._copyColumns(columns, 'limitFilter'),
-        from: {ref: [cqn.from.args[0].ref[0]], as: 'limitFilter'},
+        from: { ref: [cqn.from.args[0].ref[0]], as: 'limitFilter' },
         where: this._convertOnToWhere(cqn.from.on, cqn.from.args[0].as, 'limitFilter'),
@@ -769,13 +846,77 @@ limit: limit
   _copyOrderBy (orderBy, alias) {
-    return orderBy.map((element) => {
-      return {ref: (element.ref.length === 1) ? [alias, element.ref[0]] : [alias, element.ref[1]], sort: element.sort}
+    return orderBy.map(element => {
+      return { ref: element.ref.length === 1 ? [alias, element.ref[0]] : [alias, element.ref[1]], sort: element.sort }
     })
   }
 
-  _addWindowIfNeeded (cqn, column, tableAlias) {
-    if (!this._useWindow || (!column.orderBy && !column.limit)) return cqn
+  _getWindowRef (tableAlias, name) {
+    return { [IDENTIFIER]: name, ref: [tableAlias, name], as: `${tableAlias}_${name}` }
+  }
 
-    cqn.columns.push(
+  _getWindowVal (tableAlias, name, val) {
+    return { [IDENTIFIER]: name, val: val, as: `${tableAlias}_${name}` }
+  }
+
+  _draftColumnsToCQN (cqn, tableAlias, expandedEntity) {
+    return DRAFT_COLUMNS.map(name => {
+      if (name === 'IsActiveEntity') {
+        return this._getWindowVal(tableAlias, name, cqn[IS_ACTIVE])
+      }
+
+      if (cqn[IS_ACTIVE]) {
+        if (name === 'DraftAdministrativeData_DraftUUID') {
+          return this._getWindowVal(tableAlias, name, null)
+        }
+        if (name === 'HasActiveEntity') {
+          return this._getWindowVal(tableAlias, name, false)
+        }
+        if (name === 'HasDraftEntity') {
+          return this._getHasDraftEntityXpr(expandedEntity, tableAlias)
+        }
+      }
+
+      return this._getWindowRef(tableAlias, name)
+    })
+  }
+
+  _getHasDraftEntityXpr (expandedEntity, tableAlias) {
+    const draftTable = `${expandedEntity.name}_drafts`
+    const where = Object.keys(expandedEntity.keys)
+      .filter(name => name !== 'IsActiveEntity')
+      .reduce((res, keyName) => {
+        res.push({ ref: [draftTable, keyName] }, '=', { ref: [tableAlias, keyName] })
+        return res
+      }, [])
+    const hasDraftQuery = {
+      SELECT: {
+        from: { ref: [draftTable] },
+        columns: [{ val: 1 }],
+        where: where
+      }
+    }
+    return {
+      xpr: ['case', 'when', hasDraftQuery, 'IS NOT NULL', 'then', 'true', 'else', 'false', 'end'],
+      as: 'HasDraftEntity'
+    }
+  }
+
+  _getWindowColumns (cqn, column, tableAlias, expandedEntity) {
+    const columns = cqn.columns
+      .filter(entry => entry.val || (entry.ref && !entry.expand && entry.ref[0] !== tableAlias))
+      .concat(
+        getColumns(expandedEntity).map(element => {
+          return this._getWindowRef(tableAlias, element.name)
+        })
+      )
+
+    if (typeof cqn[IS_ACTIVE] === 'boolean') {
+      columns.push(...this._draftColumnsToCQN(cqn, tableAlias, expandedEntity))
+    }
+
+    columns.push(
       this._getWindowXpr(
-        cqn.from.on.filter((element) => element.ref && element.ref[0] !== 'filterExpand'),
+        cqn.from.on.filter(
+          element =>
+            element.ref && element.ref[0] !== 'filterExpand' && element.ref[element.ref.length - 1] !== 'IsActiveEntity'
+        ),
         this._copyOrderBy(column.orderBy || [], tableAlias)
@@ -785,5 +926,19 @@ )
 
+    return columns
+  }
+
+  _addWindowIfNeeded (cqn, column, tableAlias, expandedEntity) {
+    if (!this._useWindow || (!column.orderBy && !column.limit)) return cqn
+
     const windowCQN = {
-      columns: cqn.columns.filter((entry) => !entry.xpr).map((entry) => {
-        return Object.assign({}, entry, {[IDENTIFIER]: entry.ref[1], ref: [entry.ref[0], entry.as]})
+      columns: cqn.columns.map(entry => {
+        if (entry.ref && entry.as) {
+          return Object.assign({}, entry, { [IDENTIFIER]: entry.ref[1], ref: [entry.ref[0], entry.as] })
+        }
+
+        if (entry.val) {
+          return { [IDENTIFIER]: entry.as, ref: [tableAlias, entry.as], as: entry.as }
+        }
+
+        return Object.assign({}, entry)
       }),
@@ -795,6 +950,13 @@ from: {
 
+    cqn.columns = this._getWindowColumns(cqn, column, tableAlias, expandedEntity)
+
     if (column.limit) {
       windowCQN.where = [
-        {ref: ['rowNumber']}, '>', {val: column.limit.offset.val},
-        'and', {ref: ['rowNumber']}, '<', {val: (column.limit.offset.val + column.limit.rows.val + 1)}
+        { ref: ['rowNumber'] },
+        '>',
+        { val: column.limit.offset.val },
+        'and',
+        { ref: ['rowNumber'] },
+        '<',
+        { val: column.limit.offset.val + column.limit.rows.val + 1 }
       ]
@@ -807,3 +969,3 @@ }
   _getWindowXpr (columns, orderBy) {
-    const xpr = [{func: 'ROW_NUMBER', args: []}, 'OVER', '(', 'PARTITION BY', ...columns]
+    const xpr = [{ func: 'ROW_NUMBER', args: [] }, 'OVER', '(', 'PARTITION BY', ...columns]
     if (orderBy.length !== 0) {
@@ -814,7 +976,7 @@ xpr.push('ORDER BY', ...orderBy)
 
-    return {xpr: xpr, as: 'rowNumber'}
+    return { xpr: xpr, as: 'rowNumber' }
   }
 
   _copyColumns (columns, alias) {
-    return columns.map((element) => {
+    return columns.map(element => {
       const column = {
@@ -833,6 +995,6 @@ ref: [alias, element.ref[element.ref.length - 1]]
   _convertOnToWhere (on, currentAlias, newAlias) {
-    return on.map((element) => {
+    return on.map(element => {
       if (typeof element === 'object') {
         return {
-          ref: [(element.ref[0] === currentAlias) ? newAlias : element.ref[0], element.ref[1]]
+          ref: [element.ref[0] === currentAlias ? newAlias : element.ref[0], element.ref[1]]
         }
@@ -846,4 +1008,4 @@ }
   _copyWhere (list) {
-    return list.map((entry) => {
-      return (typeof entry === 'object') ? this._copyObject(entry) : entry
+    return list.map(entry => {
+      return typeof entry === 'object' ? this._copyObject(entry) : entry
     })
@@ -871,3 +1033,3 @@ }
    */
-  _getFilterExpandCQN (readToOneCQN, on) {
+  _getFilterExpandCQN (readToOneCQN, on, parentAlias) {
     const columns = []
@@ -878,3 +1040,8 @@
         columns.push({
-          ref: [this._getAlias(readToOneCQN), entry.ref[1]],
+          ref: [
+            parentAlias,
+            readToOneCQN.columns.some(ref => ref[IDENTIFIER] === entry.ref[1])
+              ? `${parentAlias}_${entry.ref[1]}`
+              : entry.ref[1]
+          ],
           as: entry.ref[1]
@@ -886,3 +1053,3 @@ })
     return {
-      SELECT: Object.assign({}, readToOneCQN, {columns: columns}),
+      SELECT: Object.assign({}, readToOneCQN, { columns: columns }),
       as: 'filterExpand'
@@ -892,7 +1059,2 @@ }
 
-  _getAlias (SELECT) {
-    const {as, table} = this._getRef(SELECT)
-    return as || table
-  }
-
   /**
@@ -903,3 +1065,3 @@ * In case a column is used at a JOIN, it needs to be added to the list of selected columns.
    */
-  _getJoinColumnsFromOnAddToMapping (mapping, readToOneCQN, on) {
+  _getJoinColumnsFromOnAddToMapping (mapping, parentAlias, on) {
     const columns = []
@@ -910,3 +1072,5 @@ const columnNames = []
     for (const entry of on) {
-      if (typeof entry === 'object' && entry.ref[0] !== 'filterExpand') {
+      if (typeof entry === 'object' && entry.ref[0] === 'filterExpand') {
+        columnNames.push(`${parentAlias}_${entry.ref[1]}`)
+      } else if (typeof entry === 'object') {
         const as = entry.ref.join('_')
@@ -921,4 +1085,2 @@
         })
-      } else if (typeof entry === 'object') {
-        columnNames.push(`${this._getAlias(readToOneCQN)}_${entry.ref[1]}`)
       }
@@ -930,3 +1092,3 @@ }
       const keyValue = []
-      const keyList = (atExpanded) ? mapping[TO_MANY_KEYS] : columnNames
+      const keyList = atExpanded ? mapping[TO_MANY_KEYS] : columnNames
 
@@ -948,3 +1110,3 @@ for (const key of keyList) {
    */
-  _getColumnsForExpand ({tableAlias, columnList, entity, joinColumns}) {
+  _getColumnsForExpand ({ tableAlias, columnList, entity, joinColumns, isActive }) {
     const columns = []
@@ -957,8 +1119,8 @@ const keys = this._getKeyNames(entity)
       } else {
-        this._addToColumnList(columns, keys, tableAlias, column)
+        this._addToColumnList(columns, tableAlias, column, isActive)
       }
     }
 
-    this._addMissingJoinColumns(columns, joinColumns, keys)
-    this._addMissingKeyColumns(columns, tableAlias, keys)
+    this._addMissingJoinElements(columns, joinColumns)
+    this._addMissingKeyColumns(columns, tableAlias, keys, isActive)
 
@@ -968,7 +1130,18 @@ return columns
 
-  _addToColumnList (columns, keys, tableAlias, column) {
+  _createIsActiveEntity (isActive) {
+    return {
+      val: isActive,
+      as: 'IsActiveEntity',
+      cast: { type: 'cds.Boolean' }
+    }
+  }
+
+  _addToColumnList (columns, tableAlias, column, isActive) {
+    if (column.ref && column.ref[column.ref.length - 1] === 'IsActiveEntity' && typeof isActive === 'boolean') {
+      columns.push(this._createIsActiveEntity(isActive))
+      return
+    }
+
     const columnName = column.ref[column.ref.length - 1]
 
-    this._removeExistingKeyFromList(keys, columnName)
-
     columns.push({
@@ -978,22 +1151,18 @@ ref: [tableAlias, columnName],
     })
+  }
 
-    return columnName
+  _isNotIncludedIn (columns) {
+    return entry =>
+      !columns.some(column => (column.ref && column.ref[1] === entry) || (column.val && column.as === entry))
   }
 
-  _removeExistingKeyFromList (keys, columnName) {
-    const index = keys.indexOf(columnName)
+  /**
+   * Add join columns if they are not already existing in the list.
+   * @private
+   */
 
-    if (index === -1) {
-      return false
-    }
-
-    keys.splice(index, 1)
-
-    return true
-  }
-
-  _addMissingJoinColumns (columns, joinColumns, keys) {
+  _addMissingJoinElements (columns, joinColumns, keys) {
+    const isNotIncludedInColumns = this._isNotIncludedIn(columns)
     for (const joinColumn of joinColumns) {
-      if (!this._columnIncluded(joinColumn, columns)) {
-        this._removeExistingKeyFromList(keys, joinColumn[1])
+      if (isNotIncludedInColumns(joinColumn.ref[1])) {
         columns.push(joinColumn)
@@ -1004,12 +1173,2 @@ }
 
-  _columnIncluded (column, columns) {
-    for (const entry of columns) {
-      if (column.ref[1] === entry[1]) {
-        return true
-      }
-    }
-
-    return false
-  }
-
   /**
@@ -1019,8 +1178,12 @@ * Add key columns if they are not already existing in the list.
    */
-  _addMissingKeyColumns (columns, tableAlias, keys) {
-    for (const key of keys) {
-      columns.push({
-        ref: [tableAlias, key],
-        as: `${tableAlias}_${key}`
-      })
+  _addMissingKeyColumns (columns, tableAlias, keys, isActive) {
+    for (const key of keys.filter(this._isNotIncludedIn(columns))) {
+      if (key === 'IsActiveEntity' && typeof isActive === 'boolean') {
+        columns.push(this._createIsActiveEntity(isActive))
+      } else {
+        columns.push({
+          ref: [tableAlias, key],
+          as: `${tableAlias}_${key}`
+        })
+      }
     }
@@ -1048,3 +1211,3 @@ }
  */
-const hasExpand = (cqn) => {
+const hasExpand = cqn => {
   if (cqn && cqn.SELECT && Array.isArray(cqn.SELECT.columns)) {
@@ -1051,0 +1214,0 @@ return cqn.SELECT.columns.some(column => column.expand)

lib/expand/rawToExpanded.js

@@ -7,3 +7,3 @@ const EXPAND = Symbol.for('expand')
   constructor (configs, results) {
-    this._toManyResults = {expand: {}}
+    this._toManyResults = { expand: {} }
     this._result = []
@@ -19,6 +19,6 @@ this._configs = configs
   toExpanded () {
-    const {queries, mappings} = this._configs
+    const { queries, mappings } = this._configs
 
     for (let i = 0, length = this._results.length; i < length; i++) {
-      const {_conversionMapper: conversionMapper = new Map(), _toManyTree: toManyTree = []} = queries[i]
+      const { _conversionMapper: conversionMapper = new Map(), _toManyTree: toManyTree = [] } = queries[i]
 
@@ -39,3 +39,3 @@ if (toManyTree.length === 0) {
     for (const entry of result) {
-      const parsed = this._parseRaw({mappings, toManyTree, conversionMapper, resultCache, entry})
+      const parsed = this._parseRaw({ mappings, toManyTree, conversionMapper, resultCache, entry })
 
@@ -56,3 +56,3 @@ if (parsed) {
    */
-  _parseRaw ({mappings, toManyTree, conversionMapper, resultCache, entry}) {
+  _parseRaw ({ mappings, toManyTree, conversionMapper, resultCache, entry }) {
     let isEntityNull
@@ -72,3 +72,3 @@
           mappings: mappings[key],
-          toManyTree: toManyTree,
+          toManyTree: toManyTree.concat(key),
           conversionMapper: conversionMapper,
@@ -111,6 +111,6 @@ resultCache: this._getResultCache(toManyTree.concat(key)),
     if (isEntityNull === undefined) {
-      return (value === null || value === undefined)
+      return value === null || value === undefined
     }
 
-    return (isEntityNull === true && (value === null || value === undefined))
+    return isEntityNull === true && (value === null || value === undefined)
   }
@@ -138,3 +138,3 @@
     for (const entry of result) {
-      const parsed = this._parseRaw({mappings: expandMapping, toManyTree, conversionMapper, resultCache, entry})
+      const parsed = this._parseRaw({ mappings: expandMapping, toManyTree, conversionMapper, resultCache, entry })
 
@@ -141,0 +141,0 @@ if (parsed) {

lib/index.js

 const dependencies = {
   get BaseClient () {
     const BaseClient = require('./client/BaseClient')
-    Object.defineProperty(dependencies, 'BaseClient', {value: BaseClient})
+    Object.defineProperty(dependencies, 'BaseClient', { value: BaseClient })
     return BaseClient
@@ -9,3 +9,3 @@ },
     const errors = require('./errors')
-    Object.defineProperty(dependencies, 'errors', {value: errors})
+    Object.defineProperty(dependencies, 'errors', { value: errors })
     return errors
@@ -15,3 +15,3 @@ },
     const sqlBuilder = require('./sql-builder/')
-    Object.defineProperty(dependencies, 'builder', {value: sqlBuilder})
+    Object.defineProperty(dependencies, 'builder', { value: sqlBuilder })
     return sqlBuilder
@@ -23,3 +23,3 @@ },
 
-    Object.defineProperty(dependencies, 'expand', {value: expand})
+    Object.defineProperty(dependencies, 'expand', { value: expand })
     return expand
@@ -30,3 +30,3 @@ },
 
-    Object.defineProperty(dependencies, 'contains', {value: contains})
+    Object.defineProperty(dependencies, 'contains', { value: contains })
     return contains
@@ -36,3 +36,3 @@ },
     const postProcessing = require('./data-conversion/post-processing')
-    Object.defineProperty(dependencies, 'postProcessing', {value: postProcessing})
+    Object.defineProperty(dependencies, 'postProcessing', { value: postProcessing })
     return postProcessing
@@ -42,10 +42,5 @@ },
     const onCond = require('./oncond/generateOnCond')
-    Object.defineProperty(dependencies, 'getOnCond', {value: onCond})
+    Object.defineProperty(dependencies, 'getOnCond', { value: onCond })
     return onCond
   },
-  get utils () {
-    const utils = require('./util/columns')
-    Object.defineProperty(dependencies, 'utils', {value: utils})
-    return utils
-  },
   inject: (...args) => {
@@ -52,0 +47,0 @@ return require('./cds').inject(...args)

lib/oncond/generateOnCond.js

@@ -13,4 +13,4 @@ const _onCondfromForeignKey = (csnElement, associationName) => {
 
-const _indexOfTargetForBacklink = (arr) => {
-  return (arr[0] === '$self') ? 2 : 0
+const _indexOfTargetForBacklink = arr => {
+  return arr[0] === '$self' ? 2 : 0
 }
@@ -105,3 +105,3 @@
   }
-  return {ref: res}
+  return { ref: res }
 }
@@ -108,0 +108,0 @@

lib/sql-builder/BaseBuilder.js

 const cds = require('../cds')
-const {InvalidQuotingStyleError} = require('../').errors
+const { InvalidQuotingStyleError } = require('../').errors
 
@@ -7,7 +7,7 @@ const _slugify = name => name.replace(/::/g, '__').replace(/\./g, '_')
 const quotingStyles = {
-  'quoted': (name, delimiter) => `${delimiter}${name}${delimiter}`,
-  'plain': name => _slugify(name),
+  quoted: (name, delimiter) => `${delimiter}${name}${delimiter}`,
+  plain: name => _slugify(name),
   'all-upper': (name, delimiter) => `${delimiter}${_slugify(name.toUpperCase())}${delimiter}`,
   'all-lower': (name, delimiter) => `${delimiter}${_slugify(name.toLowerCase())}${delimiter}`,
-  'bracketed': name => `[${name}]`
+  bracketed: name => `[${name}]`
 }
@@ -27,5 +27,7 @@
    * @param {string} [options.placeholder] - The placeholder for prepared statement.
+   * @param {object} csn - The csn object
    */
-  constructor (obj, options) {
+  constructor (obj, options, csn) {
     this._obj = obj
+    this._csn = csn
     const dafaultOptions = {
@@ -36,3 +38,3 @@ placeholder: '?',
       user: 'ANONYMOUS',
-      now: {sql: 'NOW ()'}
+      now: { sql: 'NOW ()' }
     }
@@ -39,0 +41,0 @@ this._options = Object.assign(dafaultOptions, options)

lib/sql-builder/CreateBuilder.js

 const BaseBuilder = require('./BaseBuilder')
 const cds = require('../cds')
-const {isAssociation} = require('../util/associations')
-const {convertDataType} = require('../util/dataTypes')
+const { isAssociation } = require('../utils/associations')
+const { convertDataType } = require('../utils/dataTypes')
 
@@ -63,3 +63,3 @@ /**
     const SelectBuilder = require('./SelectBuilder')
-    Object.defineProperty(this, 'SelectBuilder', {value: SelectBuilder})
+    Object.defineProperty(this, 'SelectBuilder', { value: SelectBuilder })
     return SelectBuilder
@@ -92,3 +92,3 @@ }
       const select = entity.query.cql ? cds.parse.cql(entity.query.cql) : entity.query
-      const {sql, values} = new this.SelectBuilder(select, this._options).build()
+      const { sql, values } = new this.SelectBuilder(select, this._options).build()
       this._outputObj.values = values
@@ -98,3 +98,3 @@ return sql
       const columns = []
-      this._elementsForEntity(this._obj.CREATE.entity).forEach((element) => {
+      this._elementsForEntity(this._obj.CREATE.entity).forEach(element => {
         columns.push(this._quoteElement(element.column))
@@ -104,7 +104,2 @@ })
       return `SELECT ${columns.length > 0 ? columns.join(', ') : '*'} FROM ${this._quoteElement(entity.source)}`
-    } else if (entity.type) {
-      // TODO adapt when .type is documented
-      const {sql, values} = new this.SelectBuilder({SELECT: {from: {ref: [entity.name]}}}, this._options).build()
-      this._outputObj.values = values
-      return sql
     }
@@ -126,3 +121,3 @@
 
-        flattenedElements.push({column: `${prefix}_${property}`, dataType: dataType, constraints: constraints})
+        flattenedElements.push({ column: `${prefix}_${property}`, dataType: dataType, constraints: constraints })
       }
@@ -135,11 +130,11 @@ }
     if (element.foreignKeys) {
-      const associationColumns = Object.keys(element.foreignKeys).map((key) => {
+      return Object.keys(element.foreignKeys).map(key => {
         const dataType = convertDataType(this._csn.definitions[element.target].elements[key], this._csn, this._options)
         const constraints = this._addConstraints(this._csn.definitions[element.target].elements[key])
 
-        return {column: `${associationName}_${key}`, dataType: dataType, constraints: constraints}
+        return { column: `${associationName}_${key}`, dataType: dataType, constraints: constraints }
       })
+    }
 
-      return associationColumns
-    }
+    return []
   }
@@ -151,3 +146,4 @@
     if (element.default) {
-      const defaultConstraint = typeof element.default === 'string' ? ` DEFAULT '${element.default}'` : ` DEFAULT ${element.default}`
+      const defaultConstraint =
+        typeof element.default === 'string' ? ` DEFAULT '${element.default}'` : ` DEFAULT ${element.default}`
       return `${notNull}${defaultConstraint}`
@@ -163,8 +159,2 @@ }
 
-  _isComplexType (type) {
-    return !this._options.typeConversion.get(type) &&
-      this._csn.definitions[type] &&
-      this._csn.definitions[type].elements
-  }
-
   _elementsForEntity (entity, columnPrefix) {
@@ -176,3 +166,3 @@ let elements = new Map()
         if (association) {
-          association.forEach((e) => elements.set(e.column, e))
+          association.forEach(e => elements.set(e.column, e))
         }
@@ -183,18 +173,12 @@ continue
       if (entity.elements[element].elements) {
-        this._flattenStructuredElement(element, entity.elements[element].elements)
-          .forEach((e) => elements.set(e.column, e))
+        this._flattenStructuredElement(element, entity.elements[element].elements).forEach(e =>
+          elements.set(e.column, e)
+        )
         continue
       }
 
-      if (this._isComplexType(entity.elements[element].type)) {
-        const complexTypePrefix = this._combinePrefixAndElement(element, columnPrefix)
-        this._elementsForEntity(this._csn.definitions[entity.elements[element].type], complexTypePrefix)
-          .forEach((e) => elements.set(e.column, e))
-        continue
-      }
-
       const columnName = this._combinePrefixAndElement(element, columnPrefix)
       const dataType = convertDataType(entity.elements[element], this._csn, this._options)
       const constraints = this._addConstraints(entity.elements[element])
-      elements.set(columnName, {column: columnName, dataType: dataType, constraints: constraints})
+      elements.set(columnName, { column: columnName, dataType: dataType, constraints: constraints })
     }
@@ -208,3 +192,3 @@
 
-    this._elementsForEntity(this._obj.CREATE.entity).forEach((element) => {
+    this._elementsForEntity(this._obj.CREATE.entity).forEach(element => {
       if (element.dataType !== 'cds.Composition' && element.dataType !== 'cds.Association') {
@@ -218,23 +202,17 @@ elements.push(`${this._quoteElement(element.column)} ${element.dataType}${element.constraints}`)
     // .keys returns undefined if no keys are available
-    const keyElements = cds.reflect(this._obj.CREATE.entity).keys || {}
+    const keyElements = this._obj.CREATE.entity.keys || {}
 
     for (const key of Object.keys(keyElements)) {
-      keys.push(...this._constructKey(key, keyElements[key]).map((key) => this._quoteElement(key)))
+      keys.push(this._quoteElement(key))
     }
 
     if (elements.length > 0) {
-      this._outputObj.sql.push('(', `${elements.join(', ')}${keys.length > 0 ? `, PRIMARY KEY (${keys.join(', ')})` : ''}`, ')')
+      this._outputObj.sql.push(
+        '(',
+        `${elements.join(', ')}${keys.length > 0 ? `, PRIMARY KEY (${keys.join(', ')})` : ''}`,
+        ')'
+      )
     }
   }
 
-  _constructKey (key, keyElement) {
-    if (isAssociation(keyElement)) {
-      return this._association(key, keyElement).map(key => key.column)
-    } else if (this._isComplexType(keyElement.type)) {
-      return Array.from(this._elementsForEntity(this._csn.definitions[keyElement.type], key).keys())
-    } else {
-      return [key]
-    }
-  }
-
   _as () {
@@ -241,0 +219,0 @@ const as = new this.SelectBuilder(this._obj.CREATE.as).build()

lib/sql-builder/DeleteBuilder.js

@@ -19,3 +19,3 @@ const BaseBuilder = require('./BaseBuilder')
     const ExpressionBuilder = require('./ExpressionBuilder')
-    Object.defineProperty(this, 'ExpressionBuilder', {value: ExpressionBuilder})
+    Object.defineProperty(this, 'ExpressionBuilder', { value: ExpressionBuilder })
     return ExpressionBuilder
@@ -22,0 +22,0 @@ }

lib/sql-builder/DropBuilder.js

@@ -49,3 +49,4 @@ const BaseBuilder = require('./BaseBuilder')
     } else if (!this._obj.DROP.table && typeof this._obj.DROP.entity === 'object') {
-      type = (this._obj.DROP.entity.query || this._obj.DROP.entity.source || this._obj.DROP.entity.type) ? 'VIEW' : 'TABLE'
+      type =
+        this._obj.DROP.entity.query || this._obj.DROP.entity.source || this._obj.DROP.entity.type ? 'VIEW' : 'TABLE'
     }
@@ -52,0 +53,0 @@

lib/sql-builder/ExpressionBuilder.js

 const BaseBuilder = require('./BaseBuilder')
-const {InvalidCqnObjectError} = require('../').errors
+const { InvalidCqnObjectError } = require('../').errors
 
@@ -25,7 +25,8 @@ /**
    * @param {string} [options.objectKey] - The object key for the expression. It can be either "xpr" or empty string.
+   * @param {object} csn - The csn object
    * Default is an empty string.
    */
-  constructor (obj, options) {
-    super(obj, options)
-    this._options = Object.assign({objectKey: ''}, this._options)
+  constructor (obj, options, csn) {
+    super(obj, options, csn)
+    this._options = Object.assign({ objectKey: '' }, this._options)
   }
@@ -35,3 +36,3 @@
     const SelectBuilder = require('./SelectBuilder')
-    Object.defineProperty(this, 'SelectBuilder', {value: SelectBuilder})
+    Object.defineProperty(this, 'SelectBuilder', { value: SelectBuilder })
     return SelectBuilder
@@ -42,3 +43,3 @@ }
     const ReferenceBuilder = require('./ReferenceBuilder')
-    Object.defineProperty(this, 'ReferenceBuilder', {value: ReferenceBuilder})
+    Object.defineProperty(this, 'ReferenceBuilder', { value: ReferenceBuilder })
     return ReferenceBuilder
@@ -65,3 +66,5 @@ }
 
-    this._expressionObjectsToSQL(this._options.objectKey ? this._obj[this._options.objectKey] : this._obj)
+    this._expressionObjectsToSQL(
+      this._options.objectKey && this._obj[this._options.objectKey] ? this._obj[this._options.objectKey] : this._obj
+    )
 
@@ -178,3 +181,3 @@ this._outputObj.sql = this._outputObj.sql.join(' ')
 
-      if (len > 1 && (i + 1) < len) {
+      if (len > 1 && i + 1 < len) {
         this._outputObj.sql.push(',')
@@ -228,3 +231,3 @@ }
   _refOutputFromElement (element) {
-    this._addToOutputObj(new this.ReferenceBuilder(element, this._options).build(), false)
+    this._addToOutputObj(new this.ReferenceBuilder(element, this._options, this._csn).build(), false)
   }
@@ -243,3 +246,3 @@
 
-  _addToOutputObj ({sql, values}, addBrackets) {
+  _addToOutputObj ({ sql, values }, addBrackets) {
     this._outputObj.sql.push(addBrackets ? `( ${sql} )` : sql)
@@ -254,3 +257,3 @@ this._outputObj.values.push(...values)
       const result = new ExpressionBuilder([arg], this._options).build()
-      const {sql, values} = result
+      const { sql, values } = result
       args.push(sql)
@@ -257,0 +260,0 @@ this._outputObj.values.push(...values)

lib/sql-builder/InsertBuilder.js

 const BaseBuilder = require('./BaseBuilder')
-const getAnnotatedColumns = require('../util/annotations')
+const getAnnotatedColumns = require('../utils/annotations')
 
@@ -62,3 +62,4 @@ /**
     if (this._obj.INSERT.values || this._obj.INSERT.rows) {
-      if (annotatedColumns && !this._obj.INSERT.columns) { // if columns not provided get indexes from csn
+      if (annotatedColumns && !this._obj.INSERT.columns) {
+        // if columns not provided get indexes from csn
         this._getAnnotatedColumnIndexes(annotatedColumns)
@@ -121,3 +122,4 @@ }
 
-    if (annotatedColumns) { // filter out existing annotated update columns
+    if (annotatedColumns) {
+      // filter out existing annotated update columns
       this._columnsAnnotatedWithUpdateFilter(annotatedColumns)
@@ -128,3 +130,4 @@ }
 
-    if (annotatedColumns) { // add insert annotated columns
+    if (annotatedColumns) {
+      // add insert annotated columns
       this._columnAnnotatedAdded(annotatedColumns)
@@ -159,3 +162,3 @@ }
 
-      this._outputObj.values.forEach((values) => {
+      this._outputObj.values.forEach(values => {
         this._valuesAnnotatedValues(annotatedInsertColumnValues, values)
@@ -165,3 +168,5 @@ })
 
-    this._outputObj.sql.push(...this._createPlaceholderString(placeholderNum, annotatedInsertColumnValues.valuesAndSQLs))
+    this._outputObj.sql.push(
+      ...this._createPlaceholderString(placeholderNum, annotatedInsertColumnValues.valuesAndSQLs)
+    )
   }
@@ -183,6 +188,10 @@
     const flattenColumnMap = this._getFlattenColumnMap(this._obj.INSERT.entries[0], annotatedUpdateColumnNames)
-    const annotatedInsertColumnNames = this._getAnnotatedInsertColumnNames(annotatedColumns)
-      .filter(colName => !flattenColumnMap.has(colName))
+    const annotatedInsertColumnNames = this._getAnnotatedInsertColumnNames(annotatedColumns).filter(
+      colName => !flattenColumnMap.has(colName)
+    )
 
-    const annotatedInsertColumnValues = this._getAnnotatedInsertColumnValues(annotatedColumns, annotatedInsertColumnNames)
+    const annotatedInsertColumnValues = this._getAnnotatedInsertColumnValues(
+      annotatedColumns,
+      annotatedInsertColumnNames
+    )
 
@@ -214,3 +223,5 @@ columns.push(...flattenColumnMap.keys())
 
-    this._outputObj.sql.push(...this._entriesSqlString(columns, placeholderNum, annotatedInsertColumnValues.valuesAndSQLs))
+    this._outputObj.sql.push(
+      ...this._entriesSqlString(columns, placeholderNum, annotatedInsertColumnValues.valuesAndSQLs)
+    )
     this._outputObj.values = valuesArray
@@ -224,3 +235,3 @@ }
       const prefixKey = prefix ? `${prefix}_${key}` : key
-      if (typeof entry[key] === 'object' && entry[key] !== null) {
+      if (typeof entry[key] === 'object' && entry[key] !== null && !(entry[key] instanceof Buffer)) {
         const resInternal = this._getFlattenColumnMap(entry[key], annotatedUpdateColumnNames, prefixKey)
@@ -244,3 +255,8 @@
   _entriesSqlString (columns, placeholderNum, valuesAndSQLs) {
-    return ['(', columns.map(column => this._quoteElement(column)).join(', '), ')', ...this._createPlaceholderString(placeholderNum, valuesAndSQLs)]
+    return [
+      '(',
+      columns.map(column => this._quoteElement(column)).join(', '),
+      ')',
+      ...this._createPlaceholderString(placeholderNum, valuesAndSQLs)
+    ]
   }
@@ -256,3 +272,3 @@
     for (const val of valuesAndSQLs) {
-      placeholders.push((val && val.sql) ? val.sql : this._options.placeholder)
+      placeholders.push(val && val.sql ? val.sql : this._options.placeholder)
     }
@@ -264,3 +280,3 @@
   _getAnnotatedColumnIndexes (annotatedColumns) {
-    annotatedColumns.insertAnnotatedColumns.forEach((col) => {
+    annotatedColumns.insertAnnotatedColumns.forEach(col => {
       if (col.indexNo) {
@@ -270,3 +286,3 @@ this._columnIndexesToDelete.push(col.indexNo)
     })
-    annotatedColumns.updateAnnotatedColumns.forEach((col) => {
+    annotatedColumns.updateAnnotatedColumns.forEach(col => {
       if (col.indexNo) {
@@ -273,0 +289,0 @@ this._columnIndexesToDelete.push(col.indexNo)

lib/sql-builder/ReferenceBuilder.js

 const BaseBuilder = require('./BaseBuilder')
 
-const sqlFunctions = [
-  'contains'
-]
+const sqlFunctions = ['contains']
 
@@ -36,8 +34,11 @@ /**
     if (this._obj.ref.length > 1 && this._obj.ref[1].args) {
-      if (sqlFunctions.some((sqlFunction) => this._obj.ref[0].toLowerCase().includes(sqlFunction))) { // sql functions (exists,...)
+      if (sqlFunctions.some(sqlFunction => this._obj.ref[0].toLowerCase().includes(sqlFunction))) {
+        // sql functions (exists,...)
         this._handleContains()
-      } else { // unsupported function
+      } else {
+        // unsupported function
         this._aggregatedFunction()
       }
-    } else { // reference
+    } else {
+      // reference
       this._parseReference(this._obj.ref)
@@ -66,3 +67,6 @@ if (this._obj.sort) {
     } else {
-      this._outputObj.sql.push(this._obj.ref[1].args.map(e => (new ReferenceBuilder(e, this._options)).build().sql).join(', '), ')')
+      this._outputObj.sql.push(
+        this._obj.ref[1].args.map(e => new ReferenceBuilder(e, this._options).build().sql).join(', '),
+        ')'
+      )
     }
@@ -103,3 +107,3 @@ }
     } else {
-      return args[0].list.map((element) => new ReferenceBuilder(element, this._options).build().sql)
+      return args[0].list.map(element => new ReferenceBuilder(element, this._options).build().sql)
     }
@@ -106,0 +110,0 @@ }

lib/sql-builder/SelectBuilder.js

@@ -32,3 +32,3 @@ const BaseBuilder = require('./BaseBuilder')
     const ExpressionBuilder = require('./ExpressionBuilder')
-    Object.defineProperty(this, 'ExpressionBuilder', {value: ExpressionBuilder})
+    Object.defineProperty(this, 'ExpressionBuilder', { value: ExpressionBuilder })
     return ExpressionBuilder
@@ -39,3 +39,3 @@ }
     const ReferenceBuilder = require('./ReferenceBuilder')
-    Object.defineProperty(this, 'ReferenceBuilder', {value: ReferenceBuilder})
+    Object.defineProperty(this, 'ReferenceBuilder', { value: ReferenceBuilder })
     return ReferenceBuilder
@@ -137,8 +137,8 @@ }
 
-  _fromUnion ({from: {SET: set, as: fromAs}, as}) {
+  _fromUnion ({ from: { SET: set, as: fromAs }, as }) {
     const selects = []
-    const concat = (set.all) ? ' UNION ALL ' : ' UNION '
+    const concat = set.all ? ' UNION ALL ' : ' UNION '
 
     for (const select of set.args) {
-      const {sql, values} = new SelectBuilder(select, this._options).build()
+      const { sql, values } = new SelectBuilder(select, this._options).build()
 
@@ -159,5 +159,7 @@ selects.push(sql)
 
-    if (element.ref) { // ref
-      res = new this.ReferenceBuilder(element, this._options).build()
-    } else { // select
+    if (element.ref) {
+      // ref
+      res = new this.ReferenceBuilder(element, this._options, this._csn).build()
+    } else {
+      // select
       res = new SelectBuilder(element, this._options).build()
@@ -167,3 +169,4 @@ res.sql = `(${res.sql})`
 
-    if (element.as) { // identifier
+    if (element.as) {
+      // identifier
       res.sql += ` ${this._quoteElement(element.as)}`
@@ -174,9 +177,11 @@ }
 
-    if (i === 0) { // first element
+    if (i === 0) {
+      // first element
       this._outputObj.sql.push(res.sql)
-    } else { // join
+    } else {
+      // join
       this._outputObj.sql.push(parent.join.toUpperCase(), 'JOIN', res.sql)
 
       if (parent.on) {
-        const {sql, values} = new this.ExpressionBuilder(parent.on, this._options).build()
+        const { sql, values } = new this.ExpressionBuilder(parent.on, this._options).build()
 
@@ -192,10 +197,14 @@ this._outputObj.sql.push('ON', sql)
 
-    if (col.ref) { // ref
+    if (col.ref) {
+      // ref
       res = new this.ReferenceBuilder(col, this._options).build()
-    } else if (col.xpr) { // xpr
-      res = new this.ExpressionBuilder(col, Object.assign({objectKey: 'xpr'}, this._options)).build()
-    } else if (col.hasOwnProperty('SELECT')) { // SELECT
+    } else if (col.xpr) {
+      // xpr
+      res = new this.ExpressionBuilder(col, Object.assign({ objectKey: 'xpr' }, this._options)).build()
+    } else if (col.hasOwnProperty('SELECT')) {
+      // SELECT
       res = new SelectBuilder(col, this._options).build()
       res.sql = `( ${res.sql} )`
-    } else { // val
+    } else {
+      // val
       res.sql = this._val(col)
@@ -205,3 +214,4 @@ res.values = []
 
-    if (col.as) { // as
+    if (col.as) {
+      // as
       res.sql += ` AS ${this._quoteElement(col.as)}`
@@ -217,3 +227,3 @@ }
     if (Array.isArray(columns) && columns.length !== 0 && columns[0] !== '*') {
-      this._outputObj.sql.push(columns.map((col) => this._buildElement(col)).join(', '))
+      this._outputObj.sql.push(columns.map(col => this._buildElement(col)).join(', '))
     } else {
@@ -225,3 +235,3 @@ this._outputObj.sql.push('*')
   _where () {
-    const where = new this.ExpressionBuilder(this._obj.SELECT.where, this._options).build()
+    const where = new this.ExpressionBuilder(this._obj.SELECT.where, this._options, this._csn).build()
     this._outputObj.sql.push('WHERE', where.sql)
@@ -252,3 +262,3 @@ this._outputObj.values.push(...where.values)
     for (const element of this._obj.SELECT.orderBy) {
-      const {sql, values} = new this.ReferenceBuilder(element, this._options).build()
+      const { sql, values } = new this.ReferenceBuilder(element, this._options).build()
       sqls.push(sql)
@@ -255,0 +265,0 @@ this._outputObj.values.push(...values)

lib/sql-builder/sqlFactory.js

@@ -1,2 +0,2 @@
-const {IllegalFunctionArgumentError, CqnParseError} = require('../').errors
+const { IllegalFunctionArgumentError } = require('../').errors
 const DeleteBuilder = require('./DeleteBuilder')
@@ -57,3 +57,3 @@ const InsertBuilder = require('./InsertBuilder')
 
-  const build = (Builder) => {
+  const build = Builder => {
     return new Builder(cqn, options, csn).build()
@@ -67,28 +67,24 @@ }
 
-  try {
-    if (cqn.SELECT) {
-      return build(_getCustomBuilderIfExists(options, 'SELECT') || SelectBuilder)
-    }
+  if (cqn.SELECT) {
+    return build(_getCustomBuilderIfExists(options, 'SELECT') || SelectBuilder)
+  }
 
-    if (cqn.INSERT) {
-      return build(InsertBuilder)
-    }
+  if (cqn.INSERT) {
+    return build(InsertBuilder)
+  }
 
-    if (cqn.UPDATE) {
-      return build(_getCustomBuilderIfExists(options, 'UPDATE') || UpdateBuilder)
-    }
+  if (cqn.UPDATE) {
+    return build(_getCustomBuilderIfExists(options, 'UPDATE') || UpdateBuilder)
+  }
 
-    if (cqn.DELETE) {
-      return build(_getCustomBuilderIfExists(options, 'DELETE') || DeleteBuilder)
-    }
+  if (cqn.DELETE) {
+    return build(_getCustomBuilderIfExists(options, 'DELETE') || DeleteBuilder)
+  }
 
-    if (cqn.CREATE) {
-      return build(_getCustomBuilderIfExists(options, 'CREATE') || CreateBuilder)
-    }
+  if (cqn.CREATE) {
+    return build(_getCustomBuilderIfExists(options, 'CREATE') || CreateBuilder)
+  }
 
-    if (cqn.DROP) {
-      return build(_getCustomBuilderIfExists(options, 'DROP') || DropBuilder)
-    }
-  } catch (err) {
-    throw new CqnParseError(err)
+  if (cqn.DROP) {
+    return build(_getCustomBuilderIfExists(options, 'DROP') || DropBuilder)
   }
@@ -95,0 +91,0 @@

lib/sql-builder/UpdateBuilder.js

 const BaseBuilder = require('./BaseBuilder')
-const getAnnotatedColumns = require('../util/annotations')
+const getAnnotatedColumns = require('../utils/annotations')
 
@@ -27,3 +27,3 @@ /**
     const ExpressionBuilder = require('./ExpressionBuilder')
-    Object.defineProperty(this, 'ExpressionBuilder', {value: ExpressionBuilder})
+    Object.defineProperty(this, 'ExpressionBuilder', { value: ExpressionBuilder })
     return ExpressionBuilder
@@ -34,3 +34,3 @@ }
     const ReferenceBuilder = require('./ReferenceBuilder')
-    Object.defineProperty(this, 'ReferenceBuilder', {value: ReferenceBuilder})
+    Object.defineProperty(this, 'ReferenceBuilder', { value: ReferenceBuilder })
     return ReferenceBuilder
@@ -37,0 +37,0 @@ }

npm-shrinkwrap.json

 {
   "name": "@sap/cds-sql",
-  "version": "0.11.0"
+  "version": "1.0.3"
 }

package.json

@@ -1,1 +0,1 @@
-{"bundleDependencies":false,"dependencies":{},"deprecated":false,"description":"This package offers a factory method to build a SQL string from a CQN object and a BaseClient which performs default post processing to be used by the inheriting clients.","devDependencies":{"@sap/cds":"git://github.wdf.sap.corp/cdx/cds","jest":"^23.6.0","jest-junit":"^3.7.0","jsdoc-to-markdown":"*","standard":"^11.0.0","standard-reporter":"^1.0.5"},"engines":{"node":">= 6.11.0"},"jest":{"testEnvironment":"node"},"jest-junit":{"suiteName":"jest tests","output":"reports/sonar/test-reporter.xml","classNameTemplate":"{classname}-{title}","titleTemplate":"{classname}-{title}","ancestorSeparator":" › ","usePathForSuiteName":"true"},"main":"lib/index.js","name":"@sap/cds-sql","scripts":{"build":"npm run test && npm run jsdoc2md","clean":"rm -rf reports && rm -f npm-debug.log","jsdoc2md":"jsdoc2md --param-list-format list lib/**/*.js > docs/api.md","lint":"([ -d reports ] || mkdir reports) && standard --env jest && standard | standard-reporter --checkstyle > reports/eslint.jslint.xml","test":"node -v && npm run clean && npm run lint && npm run test-unit && npm run test-integration","test-integration":"jest --config=jest-integration.json && [ -e reports/sonar/test-reporter.xml ] && mv reports/sonar/test-reporter.xml reports/sonar/test-integration.xml","test-unit":"jest --config=jest-unit.json && [ -e reports/sonar/test-reporter.xml ] && mv reports/sonar/test-reporter.xml reports/sonar/test-unit.xml"},"standard":{"env":["jest"],"globals":["jest"]},"version":"0.11.0","license":"SEE LICENSE IN developer-license-3.1.txt"}
+{"bundleDependencies":false,"dependencies":{},"deprecated":false,"description":"This package offers a factory method to build a SQL string from a CQN object and a BaseClient which performs default post processing to be used by the inheriting clients.","devDependencies":{"husky":"^1.2.0","jest":"^23.6.0","jest-junit":"^5.2.0","jsdoc-to-markdown":"^4.0.1","lint-staged":"^8.1.0","prettier-standard":"^8.0.1","standard":"^12.0.1","standard-reporter":"^1.0.5"},"engines":{"node":">= 8.0.0"},"husky":{"hooks":{"pre-commit":"lint-staged"}},"jest":{"testEnvironment":"node"},"jest-junit":{"suiteName":"jest tests","output":"reports/sonar/test-reporter.xml","classNameTemplate":"{classname}-{title}","titleTemplate":"{classname}-{title}","ancestorSeparator":" › ","usePathForSuiteName":"true"},"lint-staged":{"{lib,test}/**/*.js":["prettier-standard","standard --fix","git add"]},"main":"lib/index.js","name":"@sap/cds-sql","scripts":{"build":"npm run test && npm run jsdoc2md","clean":"rm -rf reports && rm -f npm-debug.log","jsdoc2md":"jsdoc2md --param-list-format list lib/**/*.js > docs/api.md","lint":"([ -d reports ] || mkdir reports) && standard --env jest && standard | standard-reporter --checkstyle > reports/eslint.jslint.xml","prepareRelease":"node node_modules/filter-node-package package.json","snapshots":"[ -e .pipeline/snapshots.sh ] && sh .pipeline/snapshots.sh","test":"node -v && npm run clean && npm run lint && npm run test-unit && npm run test-integration","test-integration":"jest --config=jest-integration.json && [ -e reports/sonar/test-reporter.xml ] && mv reports/sonar/test-reporter.xml reports/sonar/test-integration.xml","test-unit":"jest --config=jest-unit.json && [ -e reports/sonar/test-reporter.xml ] && mv reports/sonar/test-reporter.xml reports/sonar/test-unit.xml"},"standard":{"env":["jest"],"globals":["jest"]},"version":"1.0.3","license":"SEE LICENSE IN developer-license-3.1.txt"}

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

No v1

Quality

Package is not semver >=1. This means it is not stable and does not support ^ ranges.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

151937

increased by2.05%

Lines of code

3649

increased by2.88%

Number of low quality alerts

1

decreased by-50%

Worsened metrics

Dev dependency count

8

increased by33.33%

Number of package files

37

decreased by-5.13%

No dependency changes