Hi team,

First of all thank you for your hard work.
Recently I came across an issue with 'Array of' handling for PostgreeSQL.

Here is my DB schema:
entity Settings { key id: Integer; tempThreshold: Decimal(3,1) not null; notifications: array of { description: String; frequency: Integer enum { daily = 1; weekly = 2; monthly = 3; }; message: String; } }

Which is projected as follows: entity Settings as projection on db.Settings ;

Here is the created Settings table after executing npx cds-dbm deploy --create-db :

CSV upload works fine and uploads data to the database, here is a sample CSV:

id;tempThreshold;notifications
1;36.8;[{"description": "samle notification","frequency": 2,"message": "Please mail us ASAP"}]

However oData services methods GET/POST doesn't work and return the following error:
"error": { "code": "500", "message": "Serialization Error: Value of collection property 'notifications' must be an array." }

This is of course a very rare case when array is present in db schema but might be a subject for future development.

p.s. Sample JSON payload for POST:

POST {{server}}/Settings
Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true

{
"id": 2,
"tempThreshold": "10.1",
"notifications": [{
"description": "New settings",
"frequency": 1,
"message": "String"
}]
}

Best regards,
Egor

When running:

cds deploy

currently the error message:

[ERROR] Didn't find a deployer module for 'PostgreSQL'

is returned. Let's find out what we need to implement to implement to use this instead of the cds-pg deploy command.

Hello cds-pg team,

we have a unit test calling /beershop/Beers?$count=true. That works without issues and returns the entities and the element @odata.count. But when executing a GET to /beershop/Beers/$count the user sees this error message:

<error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
  <code>42P18</code>
  <message>could not determine data type of parameter $1</message>
</error>

when running:

export DEBUG=cds-pg
npm run test:as-pg

I see this error in the backend:

[cds] - GET /beershop/Beers/$count
[cds.cds-pg] - sql >  SELECT count ( $1 ) AS "counted" FROM BeershopService_Beers ALIAS_1
[cds.cds-pg] - values >  [ '1' ]
[2021-02-04T14:31:50.199Z | ERROR | 1847903]: { error: could not determine data type of parameter $1
    at Parser.parseErrorMessage (/Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:311:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
  length: 111,
  name: 'error',
  severity: 'ERROR',
  code: '42P18',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1496',
  routine: 'exec_parse_message' }

I've created a skipped test here: Branch bug-count Test: /tests/lib/pg/service.test.js#L103

CU
Gregor

According to the readme knex is a:

multi-dialect (MSSQL, MySQL, PostgreSQL, SQLite3, Oracle (including Oracle Wallet Authentication)) query builder for Node.js

So we might be better off to translate cqn to knex instead of SQL. That way we would directly get support for other databases as well.

Due to changes on the structure cds-pg currently doesn't work with @sap/cds 5. Maybe @sjvans can guide us.

cds-dbm/dist/build/buildTaskHandlerFactory extends class @sap/cds/bin/build/buildTaskHandlerFactorybut the @sap/cds file is no longer available upon npm build (I noticed issue Friday October 2nd 2021).

This is preventing me from being able to run cds-dbm build.

This feels like an @sap/cds issue since there was no major release change which broke the cds-cdm functionality but it's also not open sourced. I wonder about the reliability of building open sourced pg-adapters with core @sap/cds dependencies in production situations.

Hello Team,

I have an entity like below

entity Students {

key id: string(10);

name: string(30);

title: localized string(40);

}

I want to do deep update on Students entity.

I do put request.

My URL is 'http://localhost:4004/v2/StudentService/Students(1)'

and Payload is :

{ id : '1',

name: 'test',

texts:[{locale: 'en',title: 'Guntur'}]

}

It shows error like below

I am using Node js with PostgreSQL Database. It's working SQLite Database.
Please help me

trying to upgrade to cds 4.6, two tests fail - errors originate in core modules, not in cds-pg.
pinging @sjvans for a heads-up (please don't make me file an incident via support launchpad 😬 )

diff --git a/package.json b/package.json
index 42377ff..a041e7c 100644
--- a/package.json
+++ b/package.json

-    "@sap/cds": "~4.4",
+    "@sap/cds": "~4.6",

$> yarn test

 FAIL  __tests__/lib/pg/service.test.js (10.917 s)
  ● [local] OData to Postgres dialect › odata: DELETE -> sql: DELETE › odata: delete single beer -> sql: delete record

    TypeError: console.warn is not a function

      at Function.warn (node_modules/@sap/cds/lib/utils/logging.js:75:57)
      at _log (node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/OData.js:71:13)
      at Object.error (node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/OData.js:83:31)
      at LoggerFacade.error (node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/okra/odata-commons/logging/LoggerFacade.js:110:20)
      at chainEndCallback (node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/okra/odata-server/invocation/CommandExecutor.js:36:24)
      at node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/okra/odata-server/invocation/CommandExecutor.js:82:13
          at runMicrotasks (<anonymous>)

  ● [local-with-schema] OData to Postgres dialect › odata: DELETE -> sql: DELETE › odata: delete single beer -> sql: delete record

    TypeError: console.warn is not a function

      at Function.warn (node_modules/@sap/cds/lib/utils/logging.js:75:57)
      at _log (node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/OData.js:71:13)
      at Object.error (node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/OData.js:83:31)
      at LoggerFacade.error (node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/okra/odata-commons/logging/LoggerFacade.js:110:20)
      at chainEndCallback (node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/okra/odata-server/invocation/CommandExecutor.js:36:24)
      at node_modules/@sap/cds-runtime/lib/cds-services/adapter/odata-v4/okra/odata-server/invocation/CommandExecutor.js:82:13
          at runMicrotasks (<anonymous>)

Hi,

I am trying to use cds-pg in my CAP project and the service is frequently crashing with following error:

2021-02-09T23:01:14.07+0000 [APP/PROC/WEB/0] ERR [cds] - GET /public/Votes?$top=11
2021-02-09T23:01:14.17+0000 [RTR/3] OUT 63bc1c00trial-dev-ps-sample-srv.cfapps.us10.hana.ondemand.com - [2021-02-09T23:01:14.065960293Z] "GET /public/Votes?$top=11 HTTP/1.1" 200 0 47 "https://63bc1c00trial-dev-ps-sample-srv.cfapps.us10.hana.ondemand.com/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:85.0) Gecko/20100101 Firefox/85.0" "-" "10.32.2.5:61045" x_forwarded_for:"-" x_forwarded_proto:"https" vcap_request_id:"4f508307-a626-45c0-6d1d-e0108901cde5" response_time:0.109506 gorouter_time:0.000157 app_id:"da9ac238-af17-454f-9b05-b9c9c32102e9" app_index:"0" x_cf_routererror:"-" x_correlationid:"-" tenantid:"-" sap_passport:"-" x_scp_request_id:"eecc0b1f-2611-4463-80f6-b1328dd8353c-60231434-EA5E382" x_cf_app_instance:"-" x_b3_traceid:"73f6b7245a5ffbd2" x_b3_spanid:"73f6b7245a5ffbd2" x_b3_parentspanid:"-" b3:"73f6b7245a5ffbd2-73f6b7245a5ffbd2"
2021-02-09T23:01:14.17+0000 [RTR/3] OUT
2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR [ERROR] Release called on client which has already been released to the pool.
2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at throwOnDoubleRelease (/home/vcap/app/node_modules/pg-pool/index.js:27:9)
2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at Client.release (/home/vcap/app/node_modules/pg-pool/index.js:294:9)
2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase.release (/home/vcap/app/node_modules/cds-pg/lib/pg/Service.js:177:15)
2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase.commit (/home/vcap/app/node_modules/@sap/cds-runtime/lib/db/Service.js:61:10)
2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at process._tickCallback (internal/process/next_tick.js:68:7)
2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! code ELIFECYCLE
2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! errno 1
2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! [email protected] start: npx cds run
2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! Exit status 1
2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR!
2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! Failed at the [email protected] start script.
2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
2021-02-09T23:01:14.19+0000 [APP/PROC/WEB/0] ERR npm ERR! A complete log of this run can be found in:
2021-02-09T23:01:14.19+0000 [APP/PROC/WEB/0] ERR npm ERR! /home/vcap/app/.npm/_logs/2021-02-09T23_01_14_190Z-debug.log
2021-02-09T23:01:14.20+0000 [APP/PROC/WEB/0] OUT Exit status 1

I am facing an issue with a virtual column.

Given

• this simple data model using a virtual field rating (./db/schema.cds):

using {
  cuid,
  managed
} from '@sap/cds/common';

namespace sap.cap.ideas;

entity Ideas : cuid, managed {
  @mandatory
  title          : String(50);

  @mandatory
  description    : String(250);

  virtual rating : Decimal;
}

• this service (./srv/ideas-service.cds):

using {sap.cap.ideas as ideas} from '../db/schema';

service IdeasService {
  entity Ideas as projection on ideas.Ideas;
}

• these fiori annotations (./app/ideas/fiori-service.cds):

using IdeasService from '../../srv/ideas-service';

annotate IdeasService.Ideas with @(
  odata.draft.enabled,
  UI.HeaderInfo          : {
    TypeName       : '{i18n>Ideas.headerInfo.typeName}',
    TypeNamePlural : '{i18n>Ideas.headerInfo.typeNamePlural}',
    Title          : {
      $Type : 'UI.DataField',
      Value : title
    },
    Description    : {
      $Type : 'UI.DataField',
      Value : description
    }
  },
  UI.SelectionFields     : [
    title,
    description,
    rating
  ],
  UI.LineItem            : [
    {
      $Type             : 'UI.DataField',
      Value             : title,
      ![@UI.Importance] : #High
    },
    {
      $Type             : 'UI.DataField',
      Value             : description,
      ![@UI.Importance] : #Medium
    },
    {
      $Type             : 'UI.DataField',
      Value             : rating,
      ![@UI.Importance] : #Medium
    },
    {
      $Type             : 'UI.DataField',
      Value             : createdAt,
      ![@UI.Importance] : #Low
    },
    {
      $Type             : 'UI.DataField',
      Value             : createdBy,
      ![@UI.Importance] : #Low
    },
    {
      $Type             : 'UI.DataField',
      Value             : modifiedAt,
      ![@UI.Importance] : #Low
    },
    {
      $Type             : 'UI.DataField',
      Value             : modifiedBy,
      ![@UI.Importance] : #Low
    }
  ],
  UI.Identification      : [
    {
      $Type : 'UI.DataField',
      Value : title,
    },
    {
      $Type : 'UI.DataField',
      Value : description,
    },
  ],
  UI.FieldGroup #Managed : {Data : [
    {Value : createdBy},
    {Value : createdAt},
    {Value : modifiedBy},
    {Value : modifiedAt}
  ]},
  UI.Facets              : [{
    ID     : 'managed',
    $Type  : 'UI.ReferenceFacet',
    Label  : '{i18n>Ideas.facet.managed}',
    Target : '@UI.FieldGroup#Managed'
  }]
) {
  @UI.Hidden
  ID;

  @title : '{i18n>Ideas.element.title}'
  title;

  @title : '{i18n>Ideas.element.rating}'
  rating;

  @title : '{i18n>Ideas.element.description}'
  @UI.MultiLineText
  description;
}

• this config

  • .cdsrc.json:

{
  "features": {
    "fiori_preview": true
  },
  "requires": {
    "db": {
      "kind": "postgres"
    },
    "postgres": {
      "impl": "cds-pg",
      "model": [
        "app"
      ]
    },
    "cap-ratings": {
      "kind": "odata",
      "credentials": {
        "path": "/ratings",
        "requestTimeout": 30000
      }
    }
  },
  "migrations": {
    "db": {
      "schema": {
        "default": "public",
        "clone": "_cdsdbm_clone",
        "reference": "_cdsdbm_ref"
      },
      "deploy": {
        "tmpFile": "tmp/_autodeploy.json",
        "undeployFile": "db/undeploy.json"
      }
    }
  }
}

• default-env.json

{
  "VCAP_SERVICES": {
    "postgres": [
      {
        "name": "postgres",
        "label": "postgres",
        "tags": [
          "postgres"
        ],
        "credentials": {
          "host": "postgres-svc",
          "port": "5432",
          "user": "***",
          "password": "***",
          "database": "cap",
          "schema": "public"
        }
      }
    ],
    "cap-ratings": [{
      "name": "cap-ratings",
      "label": "cap-ratings",
      "tags": [
        "cap-ratings"
      ],
      "credentials": {
        "url": "http://cap-ratings-svc:5005"
      }
    }]
  },
  "DEBUG": "ideas-service"
}

I am running npx cds-dbm deploy to deploy the schema to the database.

Now when running the fiori app preview and creating a new entity I am receiving an error:

[cds] - POST /ideas/$batch
[cds] - > NEW Ideas 
[cds] - error: column "rating" does not exist
    at Parser.parseErrorMessage (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/workspaces/cap-ideas/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/workspaces/cap-ideas/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:290:12)
    at readableAddChunk (internal/streams/readable.js:265:9)
    at Socket.Readable.push (internal/streams/readable.js:204:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    at TCP.callbackTrampoline (internal/async_hooks.js:131:17) {
  length: 107,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '176',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3504',
  routine: 'errorMissingColumn',
  id: '1515520',
  level: 'ERROR',
  timestamp: 1632136335162
}

Any ideas what could be the root cause and why the service tries to persist the virtual column to the database throwing an error?
Running the service with sqlite everything works as expected.

cds -v

node ➜ /workspaces/cap-ideas $ cds -v
@sap/cds: 5.3.2
@sap/cds-compiler: 2.5.2
@sap/cds-dk: 4.4.2
@sap/cds-foss: 2.3.1
@sap/cds-odata-v2-adapter-proxy: 1.7.11
Node.js: v14.17.6
cap-ideas: 0.1.0

Thanks in advance,

Pascal

Hi,

We had a weird issue where cds would spawn ( normal cds run, watch, .. ) and it would successfully launch showing all standard log messages and then the process would terminate immediately ...

This was happening with a new onboarded dev so we initially assumed a node or cds installation issue. We spend many hours trying to pin point the issue but could not find a resolution.

Then later on it happened to me as well ...

And then I realized the issue ....

The local docker postgresql instance was running fine but it had not yet a created DB. Then the CDS process just terminates without giving any kind of (error) message.

I am not sure this is controlled by cds-pg but it would be nice to show some kind of message stating that 'Connection successful but no db 'x' was found ...'.

That would have saved us many hours and a headache ...

Thanks,

Steven

Hello,

Am I doing something wrong?
Given the files bellow I am able to post an entry to the service Stores despite the fact that the Owner_ID doesn't exist.

The same example used with sqlite would return an error code: 'ASSERT_REFERENCE_INTEGRITY'

Thank you in advance,

../db/customers.cds

namespace customers;

type CountryCode : String(3);

entity Owners {
key ID : String(3);
Name : String(50);
}

entity Stores {
key Owner : Association to Owners;
key Country : CountryCode;
key Number : Integer;
}

../srv/customers.cds

using {customers} from '../db/customers';

service ows {
entity Stores as projection on customers.Stores;
entity Owners as projection on customers.Owners;
}

POST to Stores entity

POST http://localhost:4004/ows/Stores
Content-Type: application/json

{
"Owner_ID": "TRO",
"Country": "FR",
"Number": 1
}

Hi Team,

I am trying to insert an entry in PUT method in $batch mode. I want to update the entry if already present in db, insert if not present. The response I received in postman client is 200 Ok. But, the data is not available in postgresql.

Request
--batch_1d53-d596-e511
Content-Type: multipart/mixed; boundary=changeset_892a-a8e3-582b

--changeset_892a-a8e3-582b
Content-Type: application/http
Content-Transfer-Encoding: binary

PUT data(id='5',code='5') HTTP/1.1
Accept: application/json
Content-Type: application/json

{"id":"5","code":"5","name":"Desc 005"}

--changeset_892a-a8e3-582b--

--batch_1d53-d596-e511--

Response
--changeset_892a-a8e3-582b
content-type: application/http
content-transfer-encoding: binary

HTTP/1.1 200 OK
odata-version: 4.0
content-type: application/json;odata.metadata=minimal

{"@odata.context":"$metadata#data/$entity","id":"5","code":"5","name":"Desc 005"}
--changeset_892a-a8e3-582b--

Please advice if I missed any steps.

Thank you.

Regards,
Karthi M R.

Dear Team,

Issue: Not able to use cds import in the cds-pg based CAP project

Steps I followed:-

cds import "ZPDCDS_SRV.edmx"

Repository: https://github.com/ibibhu/cap-devtoberfest.git
Refer the below change set :-
Change Set : https://github.com/ibibhu/cap-devtoberfest/commit/2216154910cbe329eaeb4b7a40266bf822ff0858

For running the project I used : -

cds serve:

I cannot see the external service

cds watch

external service is running in mock mode

but while accessing the oData it's giving the below message

I tried the same steps with a simple cap project

cds init
cds import "ZPDCDS_SRV.edmx"

cds watch or cds serve, I can get empty results and service is also exposed

Can someone please help me , is this a bug or am doing something wrong.

Goal was to consumer external api/odata in this project using "cds import"

Thanks and Regards,
Bibhu

When running:

node node_modules/cds-pg/bin/cds-pg deploy srv --to db

with the following CDS version:

cds -v
@sap/cds: 4.2.4
@sap/cds-compiler: 1.43.0
@sap/cds-dk: 3.1.2
@sap/cds-foss: 2.1.0
@sap/cds-reflect: 2.13.1
@sap/cds-runtime: 2.5.6
Node.js: v12.18.4

I get the error message:

(node:10902) UnhandledPromiseRejectionWarning: Error: Cannot find module '@sap/cds/lib/db/deploy'

That is caused by the fact that @sap/cds/lib/db/deploy was moved to @sap/cds/lib/srv/db/deploy.

By defining the core application within the .cds files, it is super easy to build and enhance an applications data model, as it is fully represented within cds. And this works pretty great until after you have deployed the first version of your application to your production database. From then on, you cannot make a full deployment of your data model anymore but need to only apply the deltas coming from new developments.

CAP has some built in functionality to compile the whole data model into various formats (cds compile --to ) and even to directly deploy it to SQLite and SAP HANA (cds deploy --to ). While the HANA adapter transforms the data model into HANA specific fragments and then utilizes the HDI deployer to handle the deltas, the SQLite adapter simply drops and recreates all known tables and views... which does not work for production environments.

It is therefore mandatory to introduce a new mechanism within this project, that supports initial as well as delta deployments (and could potentially be used by other db adapters as well). The rough requirements would be:

• still allow for full deployments in dev mode (like cds deploy --to sqlite or currently cds-pg deploy srv --to db) by leverage existing functionality like the import of csv files, etc.
• create some sort of file based (and thus versionable) internal changelog that stores information of the database development over time
• create a deployment mechanism, that stores the internal version of the database within the database and allows to only apply the missing deltas
• deltas should be autogenerated to keep the development flow clean, but they also should still be customizable (e.g. renaming a column cannot be automated and probably needs adjustments by hand)
• must be able to rollback to a previous release
• contain some sort of command line support
• must respect multitenancy

While this can also be handcrafted based on the internal CSN models, there are already tools available that may come in handy like Liqubase (https://www.liquibase.org/) and others.

Hi,

I'm working on an app which should use a pg db. I'm follwing the tutorials but have some questions:

How does cds-pg is looking up the credentials on BTP? I have boud my database to the application. So I can see the credentials.
I do not want to put them in package.json ... but without that I get an error

error: no pg_hba.conf entry for host "x.x.x.x", user "XXX", database "XXX", SSL off

Any idea how to force looking them up ?

Hello.

I have an error from time to time when opening an object (assuming that was before put into draft state) from worklist by clicking on the lineitem to navigate to the object.

2021-11-17T06:17:57.823+0000 [APP/PROC/WEB/0] OUT [cds] - > READ Applications(ID=b151377e-c2ef-4f00-9ca4-4166cd7283fe,IsActiveEntity=false) {
2021-11-17T06:17:57.823+0000 [APP/PROC/WEB/0] OUT   '$select': 'HasActiveEntity,HasDraftEntity,ID,IsActiveEntity,createdAt,createdBy,description, myInfo',
2021-11-17T06:17:57.823+0000 [APP/PROC/WEB/0] OUT   '$expand': 'DraftAdministrativeData($select=DraftIsCreatedByMe,DraftIsProcessedByMe,DraftUUID,InProcessByUser),status($select=ID,name)'
2021-11-17T06:17:57.823+0000 [APP/PROC/WEB/0] OUT }
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR [cds] - TypeError: Cannot read property '_isDraftEnabled' of undefined
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at RawToExpanded._parseRaw (/home/vcap/app/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/db/expand/rawToExpanded.js:77:49)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at RawToExpanded._parseMainResult (/home/vcap/app/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/db/expand/rawToExpanded.js:40:27)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at RawToExpanded.toExpanded (/home/vcap/app/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/db/expand/rawToExpanded.js:29:14)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at runMicrotasks (<anonymous>)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at processTicksAndRejections (internal/process/task_queues.js:95:5)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at async PostgresDatabase.module.exports [as _READ] (/home/vcap/app/node_modules/@sap/cds-runtime/lib/db/generic/read.js:26:18)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at async next (/home/vcap/app/node_modules/@sap/cds/lib/serve/Service-dispatch.js:54:17)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at async PostgresDatabase.dispatch (/home/vcap/app/node_modules/@sap/cds/lib/serve/Service-dispatch.js:52:10)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at async ApplicationService._handler (/home/vcap/app/node_modules/@sap/cds-runtime/lib/fiori/generic/read.js:1136:18)
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR     at async next (/home/vcap/app/node_modules/@sap/cds/lib/serve/Service-dispatch.js:54:17) {
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR   id: '1185446',
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR   level: 'ERROR',
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR   timestamp: 1637129877842
2021-11-17T06:17:57.844+0000 [APP/PROC/WEB/0] ERR }

When switching to the edit mode of an existing entity the server crashes with this output:

[cds] - POST /beershop-admin/$batch
[cds] - > EDIT TypeChecksWithDraft(ID=5e4ca9ef-7c4c-4b22-8e85-7cadefa02c95,IsActiveEntity=true)/BeershopAdminService.draftEdit {
  '$select': 'HasActiveEntity,HasDraftEntity,ID,IsActiveEntity,type_Boolean,type_Date,type_DateTime,type_Decimal,type_Double,type_Int32,type_Int64,type_LargeString,type_String,type_Time,type_Timestamp',
  '$expand': 'DraftAdministrativeData($select=DraftUUID,InProcessByUser)'
}
[2020-12-09T08:34:27.754Z | ERROR | 1296287]: TypeError: Cannot read property 'includes' of undefined
    at _select (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/@sap/cds-runtime/lib/cds-services/services/handlers/onDraftEdit.js:65:19)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
TypeError: Cannot read property 'includes' of undefined
    at _select (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/@sap/cds-runtime/lib/cds-services/services/handlers/onDraftEdit.js:65:19)
    at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  __crashOnError: true,
  severity: 'Error'
}

setting a breakpoint in @sap/cds-runtime/lib/cds-services/services/handlers/onDraftEdit.js:65:19 reveals that the the err object doesn't contain a query element. It seems that we fill the err object with the plain PostgreSQL error:

ode:'42601'
column:undefined
constraint:undefined
dataType:undefined
detail:undefined
file:'scan.l'
hint:undefined
internalPosition:undefined
internalQuery:undefined
length:94
line:'1176'
message:'syntax error at or near "WAIT"'
name:'error'
position:'484'
routine:'scanner_yyerror'
schema:undefined
severity:'ERROR'
stack:'error: syntax error at or near "WAIT"
    at Parser.parseErrorMessage (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:311:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)'
table:undefined
where:undefined

That's what I can provide for the moment.

as discussed in #46,
specifically https://github.com/sapmentors/cds-pg/pull/46/files#r525252191

Hello,

using a postgres SQL database on cloud foundry. In an action which retrieves data from an external service and tries to persists
them into db. For some reason the database connection gets lost... For SQLite DB this coding is working fine...

myArray.forEach(async function (atribute) {
                let system = {                    
                    name: atribute.name,
                    description: atribute.description,
                    index: atribute.index,               
                    sscId: atribute.guid
                };

                let dbEntry = await SELECT.one.from(SAPSystems).where({sscId:system.sscId});

                if (!dbEntry || dbEntry.length === 0) {
                    console.log("INSERT!" + JSON.stringify(system));
                    **await INSERT.into(SAPSystems).entries(system);**
                } else {
                    console.log("UPDATE!" + JSON.stringify(dbEntry) + " with " + JSON.stringify(system));
                    **await UPDATE(SAPSystems).with(system).where({sscId:system.sscId});**
                }                                
});

After the action finishes the connection gets lost with the following error:

2021-11-16T14:00:47.345+0000 [APP/PROC/WEB/0] ERR [ERROR] Connection terminated
2021-11-16T14:00:47.345+0000 [APP/PROC/WEB/0] ERR     at Connection.<anonymous> (/home/vcap/app/node_modules/pg/lib/client.js:132:36)
2021-11-16T14:00:47.345+0000 [APP/PROC/WEB/0] ERR     at Object.onceWrapper (events.js:482:28)
2021-11-16T14:00:47.345+0000 [APP/PROC/WEB/0] ERR     at Connection.emit (events.js:388:22)
2021-11-16T14:00:47.345+0000 [APP/PROC/WEB/0] ERR     at Socket.<anonymous> (/home/vcap/app/node_modules/pg/lib/connection.js:57:12)
2021-11-16T14:00:47.345+0000 [APP/PROC/WEB/0] ERR     at Socket.emit (events.js:388:22)
2021-11-16T14:00:47.345+0000 [APP/PROC/WEB/0] ERR     at TCP.<anonymous> (net.js:673:12)
2021-11-16T14:00:47.345+0000 [APP/PROC/WEB/0] ERR     at TCP.callbackTrampoline (internal/async_hooks.js:134:14)
2021-11-16T14:00:47.356+0000 [APP/PROC/WEB/0] ERR npm ERR! code ELIFECYCLE
2021-11-16T14:00:47.357+0000 [APP/PROC/WEB/0] ERR npm ERR! errno 1
2021-11-16T14:00:47.360+0000 [APP/PROC/WEB/0] ERR npm ERR! [email protected] start: `cds run`
2021-11-16T14:00:47.360+0000 [APP/PROC/WEB/0] ERR npm ERR! Exit status 1
2021-11-16T14:00:47.360+0000 [APP/PROC/WEB/0] ERR npm ERR! 
2021-11-16T14:00:47.360+0000 [APP/PROC/WEB/0] ERR npm ERR! Failed at the [email protected] start script.
2021-11-16T14:00:47.360+0000 [APP/PROC/WEB/0] ERR npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
2021-11-16T14:00:47.368+0000 [APP/PROC/WEB/0] ERR npm ERR! A complete log of this run can be found in:
2021-11-16T14:00:47.368+0000 [APP/PROC/WEB/0] ERR npm ERR!     /home/vcap/app/.npm/_logs/2021-11-16T14_00_47_360Z-debug.log
2021-11-16T14:00:47.379+0000 [APP/PROC/WEB/0] OUT Exit status 1
2021-11-16T14:00:47.382+0000 [CELL/SSHD/0] OUT Exit status 0

The release notes from February 2020 describe in Miscellaneous a way how the debugging can be controlled by the module name. I think we should follow this approach.

Connecting to an on-premise PostgreSQL database might be possible via the socks5 proxy feature supported by SAP BTP Connectivity service.

Resources:

• Connecting to pg via socks5 proxy: https://gist.github.com/abtris/3eb89a5b8862ee0819f23649420beb55
• Example SAP BTP Cloud Foundry NodeJS implementation of connecting to an on-premise TCP application https://blogs.sap.com/2020/07/03/how-to-connect-apache-kafka-via-sap-cloud-connector-with-sap-cloud-platform-and-consume-via-node.js-app-on-cloud-foundry/

I get the following error when executing a request locally.

λ cds serve
[cds] - model loaded from 3 file(s):

.\db\datamodel.cds
.\srv\service.cds
.......\AppData\Roaming\npm\node_modules@sap\cds-dk\node_modules@sap\cds\common.cds

[cds] - connect to db > database {
host: 'localhost',
port: '5432',
database: 'devtoberfest',
user: 'postgres',
password: '...',
schema: 'public'
}
[cds] - serving PublicService { at: '/public' }

[cds] - server listening on { url: 'http://localhost:4004' }
[cds] - launched at 24/3/2022 10:36:40, in: 1.254s
[cds] - [ terminate with ^C ]

[cds] - POST /public/Projects
[cds] - TypeError: Cannot read property 'regularRegex' of undefined
at smartId (C:\Users\nelisth\Documents\projects\postgre\node_modules@sap\cds-compiler\lib\sql-identifier.js:66:9)
at _smartId (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\common\utils\quotingStyles.js:19:23)
at InsertBuilder.plain [as _quoteElement] (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\common\utils\quotingStyles.js:44:12)
at InsertBuilder._into (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\db\sql-builder\InsertBuilder.js:115:37)
at InsertBuilder.build (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\db\sql-builder\InsertBuilder.js:68:29)
at build (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\db\sql-builder\sqlFactory.js:60:43)
at build (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\db\sql-builder\sqlFactory.js:73:12)
at _cqnToSQL (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\lib\pg\execute.js:148:5)
at executeInsertCQN (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\lib\pg\execute.js:72:32)
at PostgresDatabase._insert (C:\Users\nelisth\AppData\Roaming\npm\node_modules@sap\cds-dk\node_modules@sap\cds\libx_runtime\db\query\insert.js:18:10) {
numericSeverity: 4,
id: '1345890',
level: 'ERROR',
timestamp: 1648114602864
}
[INTERNAL ERROR] TypeError: Cannot read property 'regularRegex' of undefined
at smartId (C:\Users\nelisth\Documents\projects\postgre\node_modules@sap\cds-compiler\lib\sql-identifier.js:66:9)
at _smartId (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\common\utils\quotingStyles.js:19:23)
at InsertBuilder.plain [as _quoteElement] (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\common\utils\quotingStyles.js:44:12)
at InsertBuilder._into (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\db\sql-builder\InsertBuilder.js:115:37)
at InsertBuilder.build (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\db\sql-builder\InsertBuilder.js:68:29)
at build (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\db\sql-builder\sqlFactory.js:60:43)
at build (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\node_modules@sap\cds\libx_runtime\db\sql-builder\sqlFactory.js:73:12)
at _cqnToSQL (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\lib\pg\execute.js:148:5)
at executeInsertCQN (C:\Users\nelisth\Documents\projects\postgre\node_modules\cds-pg\lib\pg\execute.js:72:32)
at PostgresDatabase._insert (C:\Users\nelisth\AppData\Roaming\npm\node_modules@sap\cds-dk\node_modules@sap\cds\libx_runtime\db\query\insert.js:18:10)

I also had to change cds.requires.database.impl from "cds-pg" to "node_modules/cds-pg". Otherwise I was not able to start the application.

Deep insert doesnt seem to be working with the postgres database. I tried the same insert with the sqlite db and it works find.

I was able to take a look at the code and it seems that there is a select that happens before the post and this select is treating the association/composition as a normal field an trying to read it from the database.

Because this extra colum is added the and not found the further processing is stopped.

Hi Team

I came across an issue when try to use GET method to retrieve the data via OData protocol.
There are two key entities in my application, one is PurchaseOrder, the other is POLineItem, also there is a composition navigation attribute in PurchaseOrder called poLines, which is point to POLineItem.
I try to filter the PurchaseOrder data by constraining an field in POLineItem us the bellow query:
http://XXXXXXXX:XXXX/polling/PurchaseOrder?$filter=poLines/any(pl:pl/id eq afc315a6-5db5-49bb-bbb3-32d74daceda0)

but errors pops up like:

{
"error": {
"code": "42601",
"message": "syntax error at or near "foreign""
}
}
I checked the call stack and found the errors in there
/node_modules/pg-protocol/dist/parser.js:287:98

is there any other ways to filter the PurchaseOrder by filtering the filed in POLineItem? thanks.

in addition to the gh action for releasing a new version of the npm module, we need

• a release on gh
• propertly tagged w/ package version number

otherwise, numbering of npm module and gh code release is confusing.

doing would be in the gh action for publishing the npm module.

Today the @sap/cds version 5.9.0 was published. Unfortunately when updating cds-pg to this version already the simple http://localhost:4004/beershop/Beers request fails with this error:

[INTERNAL ERROR] TypeError: Cannot set property 'undefined' of undefined
    at /Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/@sap/cds/libx/_runtime/db/data-conversion/post-processing.js:162:36
    at Array.<anonymous> (/Users/gwolf/Documents/Projects/cap/cds-pg/lib/pg/data-conversion/post-processing.js:80:23)
    at _processRow (/Users/gwolf/Documents/Projects/cap/cds-pg/lib/pg/data-conversion/post-processing.js:158:14)
    at postProcess (/Users/gwolf/Documents/Projects/cap/cds-pg/lib/pg/data-conversion/post-processing.js:181:7)
    at _executeSQLReturningRows (/Users/gwolf/Documents/Projects/cap/cds-pg/lib/pg/execute.js:227:10)
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
    at async next (/Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/@sap/cds/lib/serve/Service-dispatch.js:74:17)
    at async PostgresDatabase.handle (/Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/@sap/cds/lib/serve/Service-dispatch.js:72:10)
    at async next (/Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/@sap/cds/lib/serve/Service-dispatch.js:74:17)
    at async ApplicationService.handle (/Users/gwolf/Documents/Projects/cap/cds-pg/node_modules/@sap/cds/lib/serve/Service-dispatch.js:72:10)

hope @sjvans has a tip for us.

I faced the problem that my connection information from default-env.json was not picked up when I followed the example in the README.md:

{
  "VCAP_SERVICES": {
    "postgres": [
      {
        "name": "postgres",
        "label": "postgres",
        "tags": ["postgres"],
        "credentials": {
          "host": "localhost",
          "port": "5432",
          "database": "dbname",
          "user": "postgres",
          "password": "postgres",
          "schema":"public"
        }
      }
    ]
  }
}

I figured out, when I follow the example from Gregors https://github.com/gregorwolf/pg-beershop the default-env.json is slightly different:

{
  "VCAP_SERVICES": {
    "postgres": [
      {
        "name": "postgres",
        "label": "postgres",
        "tags": ["plain", "database"],
        "credentials": {
          "host": "localhost",
          "port": "5432",
          "database": "beershop",
          "user": "postgres",
          "password": "postgres"
        }
      }
    ]
  }
}

Switching the tags from "postgres" to "plain" and "database" makes the difference. I don't know if this is an issue how cds-pg identifies the service definition or if it works as intended and the README.md needs to be adjusted.

Kind regards,
Sebastian

Hi,

recently I am getting below error when running npx cds-pg deploy app --to postgres:

node ➜ /workspaces/cap-ratings $ npm run deploy       

> [email protected] deploy
> npx cds-pg deploy app --to postgres

[cds.connect] - No service definition found for 'postgres', as required by 'cds.requires.postgres': {
  impl: 'cds-pg',
  model: [ 'app' ],
  credentials: {
    host: 'postgres-svc',
    port: '5432',
    user: 'slonik',
    password: 'topsekret',
    database: 'cap-ratings',
    schema: 'public'
  }
}
(node:19603) UnhandledPromiseRejectionWarning: Error: No service definition found for 'postgres'
    at AsyncFunction.connect.to (/workspaces/cap-ratings/node_modules/@sap/cds/lib/connect/index.js:42:11)
    at async Object.to (/workspaces/cap-ratings/node_modules/@sap/cds/lib/deploy.js:21:23)
    at async deploy (/workspaces/cap-ratings/node_modules/cds-pg/bin/cds-pg.js:11:3)
(Use `node --trace-warnings ...` to show where the warning was created)
(node:19603) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:19603) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
node ➜ /workspaces/cap-ratings $ 

Project setup look as follows:

cds

node ➜ /workspaces/cap-ratings $ cds -v
@sap/cds: 5.5.4
@sap/cds-compiler: 2.7.0
@sap/cds-dk: 4.5.3
@sap/cds-foss: 3.0.0
@sap/cds-odata-v2-adapter-proxy: 1.7.12
@sap/eslint-plugin-cds: 2.1.1
Node.js: v14.17.6
cap-ratings: 0.1.0
home: /workspaces/cap-ratings/node_modules/@sap/cds

.cdsrc.json

{
  "features": {
    "fiori_preview": true
  },
  "requires": {
    "db": {
      "kind": "postgres"
    },
    "postgres": {
      "impl": "cds-pg",
      "model": [
        "app"
      ]
    }
  }
}

default-env.json

{
  "PORT": 5005,
  "VCAP_SERVICES": {
    "postgres": [
      {
        "name": "postgres",
        "label": "postgres",
        "tags": [
          "postgres"
        ],
        "credentials": {
          "host": "postgres-svc",
          "port": "5432",
          "user": "slonik",
          "password": "topsekret",
          "database": "cap-ratings",
          "schema": "public"
        }
      }
    ]
  },
  "DEBUG": "ratings-service"
}

package.json

{
  "name": "cap-ratings",
  "version": "0.1.0",
  "description": "A simple CAP project.",
  "private": true,
  "dependencies": {
    "@sap/cds": "^5.5.4",
    "@sap/cds-odata-v2-adapter-proxy": "^1.7.12",
    "cds-pg": "^0.1.13",
    "express": "^4.17.1",
    "helmet": "^4.6.0",
    "passport": "^0.5.0"
  },
  "devDependencies": {
    "@sap/eslint-plugin-cds": "^2.1.1",
    "eslint": "^7.32.0",
    "eslint-config-standard": "^16.0.3",
    "eslint-plugin-import": "^2.24.2",
    "eslint-plugin-node": "^11.1.0",
    "eslint-plugin-promise": "^5.1.0",
    "sqlite3": "^5.0.2"
  },
  "scripts": {
    "deploy": "npx cds-pg deploy app --to postgres",
    "watch": "cds watch",
    "mock": "cds run --with-mocks --in-memory",
    "prestart": "npm run deploy",
    "start": "npx cds run",
    "build": "cds build",
    "predocker": "npm run build",
    "docker": "docker build -t pwasem/cap-ratings:latest ."
  }
}

Maybe an issue with recent @sap/cds-dk or sap/cds versions?

Thank you in advance for having a look!

Best regards,

Pascal

the release gh action should not be triggered when 1+ of the below holds true:

• node dependency updates chore(deps)
• documentation docs

@gregorwolf @d-sooter @mikezaschka any other suggestions?

Hi,
having error testing draft enabled entity in fiori UI. Request is:

and the error is:

Only main entity (Shipment) is draft enabled.

Currently the values passed to the prepared statement are only provided to the user in DEBUG mode in the function executeRawSQL of execute.js#L133. This output should be added to processExpand, processSelectSQL, processSimpleSQL.

Hi.

I downloaded the pg-beershop project and I started to test some APIs, but then I could not filter an expanded entity.
I tried doing the following query and it throw this error:

http://localhost:4004/beershop/Beers?$count=true&$select=ID,abv,ibu,name&$expand=brewery($select=ID,name)&$filter=brewery/name eq 'asd'&$skip=0&$top=30

https://ibb.co/MBy9Kcd

And it throws this error in the console:

[cds] - error: missing FROM-clause entry for table "brewery"
at Parser.parseErrorMessage (/home/alexandre/pg-beershop/node_modules/pg-protocol/dist/parser.js:287:98)
at Parser.handlePacket (/home/alexandre/pg-beershop/node_modules/pg-protocol/dist/parser.js:126:29)
at Parser.parse (/home/alexandre/pg-beershop/node_modules/pg-protocol/dist/parser.js:39:38)
at Socket. (/home/alexandre/pg-beershop/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (events.js:314:20)
at addChunk (_stream_readable.js:297:12)
at readableAddChunk (_stream_readable.js:272:9)
at Socket.Readable.push (_stream_readable.js:213:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
at TCP.callbackTrampoline (internal/async_hooks.js:126:14) {
length: 118,
severity: 'ERROR',
code: '42P01',
detail: undefined,
hint: undefined,
position: '73',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3541',
routine: 'errorMissingRTE',
id: '1541585',
level: 'ERROR',
timestamp: 1652712172856
}

Is this filter available right now or will it be implemented in the future?

Thanks

I've already created a currently skipped in tests/lib/pg/service.test.js#L53 for the $count=true parameter. A direction how it can be implemented can be found in @sap/cds-runtime/lib/db/query/read.js.

In the branch gregorwolf:feature/draft-support I've added a new entity in the service which I've annotated with @odata.draft.enabled unfortuantely already the first simple READ / GET request to the entity fails with this error:

nsole.error
[2020-09-29T20:18:44.234Z | ERROR | 1310454]: Error stacktrace: error: syntax error at or near "." at Parser.parseErrorMessage (/home/gwolf/projects/cds-pg/node_modules/pg-protocol/src/parser.ts:357:11) at Parser.handlePacket (/home/gwolf/projects/cds-pg/node_modules/pg-protocol/src/parser.ts:186:21) at Parser.parse (/home/gwolf/projects/cds-pg/node_modules/pg-protocol/src/parser.ts:101:30) at Socket. (/home/gwolf/projects/cds-pg/node_modules/pg-protocol/src/index.ts:7:48) at Socket.emit (events.js:315:20) at addChunk (_stream_readable.js:295:12) at readableAddChunk (_stream_readable.js:271:9) at Socket.Readable.push (_stream_readable.js:212:10) at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

The generated SQL is:

SELECT active AS "active" .ID AS "ID" , active AS "active" .type_Boolean AS "type_Boolean" , active AS "active" .type_Int32 AS "type_Int32" , active AS "active" .type_Int64 AS "type_Int64" , active AS "active" .type_Decimal AS "type_Decimal" , active AS "active" .type_Double AS "type_Double" , active AS "active" .type_Date AS "type_Date" , active AS "active" .type_Time AS "type_Time" , active AS "active" .type_DateTime AS "type_DateTime" , active AS "active" .type_Timestamp AS "type_Timestamp" , active AS "active" .type_String AS "type_String" , active AS "active" .type_Binary AS "type_Binary" , active AS "active" .type_LargeBinary AS "type_LargeBinary" , active AS "active" .type_LargeString AS "type_LargeString" , true AS "IsActiveEntity", false AS "HasActiveEntity", null AS "DraftAdministrativeData_DraftUUID", CASE WHEN DRAFTS.DRAFTADMINISTRATIVEDATA_DRAFTUUID IS NOT NULL THEN TRUE ELSE FALSE END AS "HasDraftEntity" FROM BeershopService_TypeChecksWithDraft active LEFT JOIN BeershopService_TypeChecksWithDraft_drafts drafts ON active.ID = drafts.ID ORDER BY active.ID ASC LIMIT 1000

Looks like the "active AS " which is added to every column must be removed. Also the space betwen i.e. "active" .ID must be removed. "active".ID should work.

The generated SQL for sqlite is:

SELECT active.ID, active.type_Boolean, active.type_Int32, active.type_Int64, active.type_Decimal, active.type_Double, active.type_Date, active.type_Time, active.type_DateTime, active.type_Timestamp, active.type_String, active.type_Binary, active.type_LargeBinary, active.type_LargeString, true AS "IsActiveEntity", false AS "HasActiveEntity", null AS "DraftAdministrativeData_DraftUUID", CASE WHEN DRAFTS.DRAFTADMINISTRATIVEDATA_DRAFTUUID IS NOT NULL THEN TRUE ELSE FALSE END AS "HasDraftEntity" FROM BeershopService_TypeChecksWithDraft active LEFT JOIN BeershopService_TypeChecksWithDraft_drafts drafts ON active.ID = drafts.ID ORDER BY active.ID COLLATE NOCASE ASC LIMIT 1000

Hi Mentors,

For my project, I wanted to use Localized languages.
I created my data model like this
entity ProductCategory { key categoryid : Integer; name : localized String; }

And than exposed it like below
@readonly entity ProductsCategory as projection on ProductCategory;

I also created some CSV to upload the sample data with file names ProductCategory.csv and ProductCategory_texts.csv

When i use it on sqlite.. I can access the localized data by passing the url parameter sap-language=DE. However the same doesn't work when i use postgresql as db. The metadata for postgresql does show the generated text table so that means the data tables are all generated fine.

Url called to get localized data
/ProductsCategory?sap-language=DE

Please note that same works fine for sqlite DB.

Thanks,
Parth

The below example fails when trying to delete an entry:

schema.cds
@restrict:[{ grant: ['READ','WRITE','DELETE'], where: 'CreatedBy = $user' }]
entity someEntity : cuid, managed
{
Foo : String;
}

service.cds
entity someEntity as projection on db.someEntity

Given a few entries have been loaded using the DELETE method causes an INTERNAL ERROR and shuts the CAP application down.

From what I can tell this issue was most likely introduced with issue #223 was fixed. An exception is thrown at line 64 in

/cds-pg/lib/pg/sql-builder/SelectBuilder.js:64:49

as this --> this._obj.SELECT.columns[0].args[0].val is undefined (in fact there are no args).

Note that if the @restrict is removed from the database definition the delete works fine.

Clark

The Beers entity uses the aspect managed. This should have the effect that the fields createdBy and modifiedBy are filled with the username. But this isn't the case. Instead ANONYMOUS is written to the database. Here is the debug output for such an INSERT:

[cds-pg] - sql >  INSERT INTO csw_Beers ( ID, name, createdAt, createdBy, modifiedAt, modifiedBy ) 
VALUES ( $1, $2, $3, $4, $5, $6 ) Returning *
[cds-pg] - values >  [
  '77011a62-5864-448d-91b4-0037f12fc4c4',
  'Testbier with POST',
  'NOW ()',
  'ANONYMOUS',
  'NOW ()',
  'ANONYMOUS'
]

Hello,

I am using the latest cds-pg version (0.0.48) after the issue #121 is fixed (Thanks to @gregorwolf and the team).

I am not able to deploy the model to the database. I am not getting the below message when I deploy with 0.0.48 version. The deployment works with 0.0.41 version.

npx cds-pg deploy srv --to db

 > filling db.data from db\data\db-data.csv
/> successfully deployed to ./postgres

https://github.com/mrkarthi/pg-app

Thank you.

Regards,
Karthi M R.

When simultaneously expanding and filtering, I ran into the error: "column reference "id" is ambiguous". This error seems to only impact when attempting to filter on an expanded CDS key field.

url: http://localhost:5001/Tenant/Shipping/Shipments(74664430-0bd0-11ec-9a03-0242ac130003)?$expand=toShipUnits($filter=ID%20eq%2007182320-0bf4-11ec-9a03-0242ac130003)

for example, swapping "ID" to "Shipment_ID" results in a successful query: http://localhost:5001/Tenant/Shipping/Shipments(74664430-0bd0-11ec-9a03-0242ac130003)?$expand=toShipUnits($filter=Shipment_ID%20eq%2007182320-0bf4-11ec-9a03-0242ac130003)

Hi,

When we define a field with type = timestamp, it is created into DB as "timestamp without time zone".

While creating a new entry from the UI side using the OData model, it passes value in TZ format in the batch request which is saved in DB without a time zone.

In a case where there is a timezone different then UTC, it is saving wrong values.

createdat and modifiedat are just showing wrong values when Application is running on other than UTC time zone.

Thanks,
Rajdeep Bhuva

I found that we lose DB superuser access after BTP deployment which is required for requirements such as running db producedures to clone and drop schemas. Question has been opened to SAP and I'm unsure if a fix is possible and if so what the timeline might be: https://answers.sap.com/questions/13386233/postgresql-on-sap-btp-authorizations.html

Is it feasible to allow cds-pg & cds-dbm to connect and manage a AWS rds postgres instance directly without going through the SAP's postgres hypescaler service? I would be happy to work on the development and testing if the original contributers see this as feasible and have any advice on that connection might work.

With the latest addition in #63 we now run the complete check 3 times with 3 different database connections. I think we should reduce that and make a test for the different connection posibilities separate from testing then the functionality.

On a previous commit I added some functionality to allow user defined schema switching.

Whenever req.user.schema was set, a small peice of extra logic was called that sets the active schema. Simplified, the logic would be:

if (arg.user.schema) {
dbc.query(`SET search_path TO '${schema}';`)
}

SET search_path is no longer making any impact on which schema is used, the default schema ("public") is always being used.

Does anyone have any insight on why the set search path has stopped having an affect or how to adjust the cds-pg logic to allow for user-defined-schemas? I'm in the last stages a blog series on getting a working multitenant example of cds-pg running and would appreciate any tips on how to troubleshoot this

Hello Team,

Count request for an entity is throwing error as below.

url :- Entity/$count
err response:- "could not determine data type of parameter $1"

when I debug the code of cds-pg I can see the query is being generated as:-
SQL:
SELECT count ( $1 ) AS "counted" FROM CoreService_Job ALIAS_1 LIMIT 1000.
param:["1"]

The inline count is working fine, SQL query is being generated for the inline count as:-
SELECT count ( 1 ) AS "counted" FROM CoreService_Job ALIAS_1

Thanks,
Rajdeep Bhuva

SAP Cloud Platform CF offers the possibility to develop multitenancy applications and CAP has built in support.

• https://cap.cloud.sap/docs/guides/multitenancy#how-it-works
• https://help.sap.com/viewer/65de2977205c403bbc107264b8eccf4b/Cloud/en-US/5e8a2b74e4f2442b8257c850ed912f48.html
• https://blogs.sap.com/2020/08/20/getting-your-head-into-cloud-application-programming-model-multitenancy/

When running on SAP CF, the PostgreSQL adapter should be able to handle the multitenancy.
I am currently not sure about all the requirements, but one is to change the schema based on the requests tenant. A possible solution could like this:

class PostgresDatabase extends cds.DatabaseService {
...

  async acquire(arg) {

    // fetch the tenant if available
    const tenant = (typeof arg === 'string' ? arg : arg.user.tenant) || 'anonymous'

    // define the used schema, maybe add some more advanced naming logic
    const schema = tenant === 'anonymous' ? 'public' : tenant;

    // change the PostgreSQL schema for the client
    this._pool.on('connect', (client) => {

      // search_path also supports naming multiple schemas so things can be split across schemas (provider, subscribers)
      client.query(`SET search_path TO ${schema};`)
    })

    const dbc = await this._pool.connect()
    return dbc
  }

...
}

For a future implementation it would be necessary to identify all the internal requirements by analyzing the cds core and come up with a concept for PostgreSQL (that maybe works for other DB adapters as well).

The CONTRIBUTING.md already contains information how to the db and adminer docker container for local development. I think we should also describe the command:

npm run test:as-pg

that starts the CAP project. Because then it's possible to try requests using the REST Client Scripts in
tests/assets/cap-proj/rest-client-test. That way it's easier to create an incidents including a test that can be replicated. Adding the test to the JavaScript test seems to be harder.

I'm starting the test for the first time using:

~/Dokumente/Projects/sapcp/cds-pg$ npm test

But this test fails with the following errors:

> [email protected] test /home/gwolf/Dokumente/Projects/sapcp/cds-pg
> run-s test:pg:up jest test:pg:down


> [email protected] test:pg:up /home/gwolf/Dokumente/Projects/sapcp/cds-pg
> docker-compose -f __tests__/__assets__/cap-proj/stack.yml up -d

Creating network "capproj_default" with the default driver
Creating capproj_adminer_1 ... 
Creating capproj_db_1 ... 
Creating capproj_adminer_1
Creating capproj_db_1 ... done

> [email protected] jest /home/gwolf/Dokumente/Projects/sapcp/cds-pg
> jest

 PASS  __tests__/cqn2pgsql.js
 FAIL  __tests__/odata.js
  ● Console

    console.error
      [2020-08-18T21:46:55.725Z | ERROR | 1957855]: Connection terminated unexpectedly

      at LoggerFacade.error (node_modules/@sap/odata-server/node_modules/@sap/odata-commons/lib/logging/LoggerFacade.js:109:26)
      at chainEndCallback (node_modules/@sap/odata-server/lib/invocation/CommandExecutor.js:37:34)
      at node_modules/@sap/odata-server/lib/invocation/CommandExecutor.js:84:25

    console.error
      [2020-08-18T21:46:55.727Z | ERROR | 1957855]: Error stacktrace: Error: Connection terminated unexpectedly     at Connection.<anonymous> (/home/gwolf/Dokumente/Projects/sapcp/cds-pg/node_modules/pg/lib/client.js:272:71)     at Object.onceWrapper (events.js:421:28)     at Connection.emit (events.js:315:20)     at Socket.<anonymous> (/home/gwolf/Dokumente/Projects/sapcp/cds-pg/node_modules/pg/lib/connection.js:102:10)     at Socket.emit (events.js:327:22)     at endReadableNT (_stream_readable.js:1220:12)     at processTicksAndRejections (internal/process/task_queues.js:84:21)

      at LoggerFacade.error (node_modules/@sap/odata-server/node_modules/@sap/odata-commons/lib/logging/LoggerFacade.js:109:26)
      at chainEndCallback (node_modules/@sap/odata-server/lib/invocation/CommandExecutor.js:38:34)
      at node_modules/@sap/odata-server/lib/invocation/CommandExecutor.js:84:25

    console.log
      {
        error: { code: '500', message: 'Connection terminated unexpectedly' }
      }

      at Object.<anonymous> (__tests__/odata.js:52:17)

    console.error
      [2020-08-18T21:46:55.748Z | ERROR | 1957855]: read ECONNRESET

      at LoggerFacade.error (node_modules/@sap/odata-server/node_modules/@sap/odata-commons/lib/logging/LoggerFacade.js:109:26)
      at chainEndCallback (node_modules/@sap/odata-server/lib/invocation/CommandExecutor.js:37:34)
      at node_modules/@sap/odata-server/lib/invocation/CommandExecutor.js:84:25

    console.error
      [2020-08-18T21:46:55.748Z | ERROR | 1957855]: Error stacktrace: Error: read ECONNRESET     at TCP.onStreamRead (internal/stream_base_commons.js:205:27)

      at LoggerFacade.error (node_modules/@sap/odata-server/node_modules/@sap/odata-commons/lib/logging/LoggerFacade.js:109:26)
      at chainEndCallback (node_modules/@sap/odata-server/lib/invocation/CommandExecutor.js:38:34)
      at node_modules/@sap/odata-server/lib/invocation/CommandExecutor.js:84:25

  ● OData to Postgres dialect › odata: GET -> sql: SELECT › odata: entityset Beers -> sql: select all beers

    expect(received).toStrictEqual(expected) // deep equality

    Expected: 200
    Received: 500

      52 |         console.log(response.body)
      53 |       } 
    > 54 |       expect(response.status).toStrictEqual(200)
         |                               ^
      55 |       // 2 beers in the shop
      56 |       expect(response.body.value.length).toStrictEqual(2)
      57 |       // at least one of them must be the "Lagerbier Hell"

      at Object.<anonymous> (__tests__/odata.js:54:31)

  ● OData to Postgres dialect › odata: GET -> sql: SELECT › odata: entityset Beers -> sql: select all beers

    TypeError: Cannot read property 'release' of undefined

      71 |    */
      72 |   async release() {
    > 73 |     return this.dbc.release(true)
         |                     ^
      74 |   }
      75 | }
      76 | 

      at PostgresDatabase.release (index.js:73:21)
      at Object.<anonymous> (__tests__/odata.js:46:20)

  ● OData to Postgres dialect › odata: GET -> sql: SELECT › odata: single entity -> sql: select record

    expect(received).toStrictEqual(expected) // deep equality

    Expected: 200
    Received: 500

      62 |       const response = await request.get('/beershop/Beers(9e1704e3-6fd0-4a5d-bfb1-13ac47f7976b)')
      63 |       // http response code
    > 64 |       expect(response.status).toStrictEqual(200)
         |                               ^
      65 |       // the beer
      66 |       expect(response.body.ID).toStrictEqual('9e1704e3-6fd0-4a5d-bfb1-13ac47f7976b')
      67 |       expect(response.body.name).toStrictEqual('Schönramer Hell')

      at Object.<anonymous> (__tests__/odata.js:64:31)

  ● OData to Postgres dialect › odata: GET -> sql: SELECT › odata: single entity -> sql: select record

    TypeError: Cannot read property 'release' of undefined

      71 |    */
      72 |   async release() {
    > 73 |     return this.dbc.release(true)
         |                     ^
      74 |   }
      75 | }
      76 | 

      at PostgresDatabase.release (index.js:73:21)
      at Object.<anonymous> (__tests__/odata.js:46:20)

Test Suites: 1 failed, 1 passed, 2 total
Tests:       2 failed, 3 skipped, 7 todo, 2 passed, 14 total
Snapshots:   0 total
Time:        2.146 s
Ran all test suites.
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] jest: `jest`
npm ERR! Exit status 1
npm ERR! 
npm ERR! Failed at the [email protected] jest script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     /home/gwolf/.npm/_logs/2020-08-18T21_46_55_888Z-debug.log
ERROR: "jest" exited with 1.
npm ERR! Test failed.  See above for more details.

when I run the test the second time it completes without error.

My node version is 12.18.3, Docker version 19.03.6, build 369ce74a3c, docker-compose version 1.17.1, build unknown

We need to support the current CAP Release