mikezaschka / cds-dbm Goto Github PK

In package.json is possible deploy in another schema at change de config, but when it is generate build mta, the archive generated schema "default".

{
    ...
    "cds": {
            "migrations": {
			"db": {
				"schema": {
					"default": "S4Q", // Here Change
					"clone": "_cdsdbm_clone",
					"reference": "_cdsdbm_ref"
				},
				"deploy": {
					"tmpFile": "tmp/_autodeploy.json",
					"undeployFile": "db/undeploy.json"
				}
			}
		}
     }
}

Hi all,

i think there is a problem with ssl connections that do not have a certificate. In cds-pg, ssl:true is sufficient to establish a connection. cds-dbm always throws:

error: no pg_hba.conf entry for host "xxx", user "xxx", database "xxx", SSL off
at Parser.parseErrorMessage (C:\Users\xxx\Desktop\git\sap.cap.demo\authentication\node_modules\pg-protocol\dist\parser.js:287:98)
at Parser.handlePacket (C:\Users\xxx\Desktop\git\sap.cap.demo\authentication\node_modules\pg-protocol\dist\parser.js:126:29)
at Parser.parse (C:\Users\xxx\Desktop\git\sap.cap.demo\authentication\node_modules\pg-protocol\dist\parser.js:39:38)
at Socket. (C:\Users\xxx\Desktop\git\sap.cap.demo\authentication\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) {
length: 166,
severity: 'FATAL',
code: '28000',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'auth.c',
line: '496',
routine: 'ClientAuthentication'`

i try to add ssl:true in the PostgresAdapter.ts and url ssl=true. but its not working.

my default-env.json:

{
"VCAP_SERVICES": {
"postgres": [
{
"name": "postgres",
"label": "postgres",
"tags": [
"postgres"
],
"credentials": {
"host": "xxx",
"port": "5432",
"database": "xxx",
"user": "xxx",
"password": "xxx",
"ssl": true
}
}
]
}
}

certificate is not available.

thank you very much

Hi,
It looks like there is a problem with this step (dropping view) because other views depend on it:

The error is:

Views in question:
entity Agent as projection on BusinessPartner where Role = '02';

entity ShipmentExt as projection on Shipment {
*,
Buyer.PartnerId as BuyerExtId,
Agent.PartnerId as AgentExtId,
Product.ProductID as ProductExtId
}

Hi.
I've been trying to enable postgis as an extension on my postgres db. Without the extension everything is fine. But with the extension I get the error

Reason: liquibase.exception.DatabaseException: ERROR: function postgis_typmod_dims(integer) does not exist

Full log below:
[cds-dbm] - starting delta database deployment of service db
[cds-dbm] - database postgres is already present
cds-dbm deploy [services]

Dynamically identifies changes in your cds data model and deploys them to the
database

Options:
--help Show help [boolean]
--version Show version number [boolean]
-s, --service [default: ["db"]]
-a, --auto-undeploy
-d, --dry
-l, --load-via
-c, --create-db

Error: Command failed: /home/jakob/git/fueldb/fueldb/node_modules/cds-dbm/liquibase/liquibase --username=postgres --password=postgres --url=jdbc:postgresql://localhost:5432/postgres --classpath=/home/jakob/git/fueldb/fueldb/node_modules/cds-dbm/dist/adapter/../../drivers/postgresql-42.3.2.jar --driver=org.postgresql.Driver --defaultSchemaName=_cdsdbm_clone --changeLogFile=tmp/_autodeploy.json update

at ChildProcess.exithandler (node:child_process:397:12)
at ChildProcess.emit (node:events:390:28)
at maybeClose (node:internal/child_process:1064:16)
at Process.ChildProcess._handle.onexit (node:internal/child_process:301:5) {

killed: false,
code: 255,
signal: null,
cmd: '/home/jakob/git/fueldb/fueldb/node_modules/cds-dbm/liquibase/liquibase --username=postgres --password=postgres --url=jdbc:postgresql://localhost:5432/postgres --classpath=/home/jakob/git/fueldb/fueldb/node_modules/cds-dbm/dist/adapter/../../drivers/postgresql-42.3.2.jar --driver=org.postgresql.Driver --defaultSchemaName=cdsdbm_clone --changeLogFile=tmp/autodeploy.json update ',
stderr: 'Liquibase Community 4.1.0 by Datical\n' +
'####################################################\n' +
'## _ _ _ _ ##\n' +
'## | | () () | ##\n' +
'## | | _ __ _ _ _ | |_ __ _ ___ ___ ##\n' +
"## | | | |/ | | | | | '_ \\ / _ / |/ _ \ ##\n" +
'## | || | (| | || | | |) | (| \__ \ / ##\n' +
'## \/_|\, |\,||_./ \,|/\_| ##\n' +
'## | | ##\n' +
'## || ##\n' +
'## ## \n' +
'## Get documentation at docs.liquibase.com ##\n' +
'## Get certified courses at learn.liquibase.com ## \n' +
'## Get support at liquibase.com/support ##\n' +
'## ##\n' +
'####################################################\n' +
'Starting Liquibase at 22:14:41 (version 4.1.0 #3 built at 2020-09-28 21:02+0000)\n' +
'Unexpected error running Liquibase: Migration failed for change set tmp/_autodeploy.json::1656850477167-2::jakob (generated):\n' +
' Reason: liquibase.exception.DatabaseException: ERROR: function postgis_typmod_dims(integer) does not exist\n' +
' Hint: No function matches the given name and argument types. You might need to add explicit type casts.\n' +
' Position: 199 [Failed SQL: (0) CREATE VIEW _cdsdbm_clone.geography_columns AS SELECT current_database() AS f_table_catalog,\n' +
' n.nspname AS f_table_schema,\n' +
' c.relname AS f_table_name,\n' +
' a.attname AS f_geography_column,\n' +
' postgis_typmod_dims(a.atttypmod) AS coord_dimension,\n' +
' postgis_typmod_srid(a.atttypmod) AS srid,\n' +
' postgis_typmod_type(a.atttypmod) AS type\n' +
' FROM pg_class c,\n' +
' pg_attribute a,\n' +
' pg_type t,\n' +
' pg_namespace n\n' +
WHERE ((t.typname = 'geography'::name) AND (a.attisdropped = false) AND (a.atttypid = t.oid) AND (a.attrelid = c.oid) AND (c.relnamespace = n.oid) AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"])) AND (NOT pg_is_other_temp_schema(c.relnamespace)) AND has_table_privilege(c.oid, 'SELECT'::text));]\n +
'For more information, please use the --logLevel flag\n'
}

adding title or label annotations to cds model cause cds module to crash:
TypeError: Cannot read property 'name' of undefined ocurring in core cds module.

Video Description of Error
https://www.screenpresso.com/=0Bgze

While attempting to add a multitenant deployment option, I found that jest test deployments always go to public schema regardless of what is defined in the configOptions.

Adjust the migrations.schema.default schema:

breakpoint after const updateSQL: any = await liquibase(liquibaseOptions).run(updateCmd)

deployments still go to public schema:

Hi everybody,
we were reported a strong security issue regarding the "jsonwebtoken" nodejs module in version 8.x which is used by many products and other npm modules. Our dependeny tracker offered that cds-dbm also depends on "@sap/cds" version 5.9 (from sap-cap), which also uses jsonwebtoken in vulnerable version 8.x. This bug allows remote code execution in some cases (see https://nvd.nist.gov/vuln/detail/CVE-2022-23529 from "National Vulnerability Database" for details).

Lastest version of "@sap/cds" 6.4.0 already uses "jsonwebtoken" in version 9.0, in which this bug is already fixed.

regards Matthias

Facing error while deploying to cloud foundry.

package.json
"dependencies": {
"@sap/cds": "^5",
"cds-dbm": "^0.0.36",
"cds-pg": "^0.1.29",
"express": "^4"
},
"cds": {
"build": {
"tasks": [
{
"for": "node-cf",
"src": "srv",
"options": {
"model": [
"db",
"srv",
"app"
]
}
},
{
"use": "cds-dbm/dist/build/postgres-cf",
"for": "postgres-cf",
"src": "db",
"options": {
"deployCmd": "npx cds-dbm deploy --load-via delta --auto-undeploy"
}
}
]
},
"requires": {
"db": {
"kind": "database"
},
"database": {
"dialect": "plain",
"impl": "cds-pg",
"model": [
"srv"
],
"credentials": {
"host": "...",
"port": 4136,
"database": "...",
"user": "...",
"password": "..."
}
}
},
"migrations": {
"db": {
"schema": {
"default": "public",
"clone": "_cdsdbm_clone",
"reference": "_cdsdbm_ref"
},
"deploy": {
"tmpFile": "tmp/_autodeploy.json",
"undeployFile": "db/undeploy.json"
}
}
}
}

Hi,

I'll try running

npx cds-dbm deploy -create-db --load-via delta

but (in SAP BAS) the default-env.json is not picked up. Here's my file (values replaced)

{
	"VCAP_SERVICES": {
		"postgresql-db": [
			{
				"label": "postgresql-db",
				"provider": null,
				"plan": "standard",
				"name": "wp-postgres-tool",
				"tags": [
					"relational",
					"database"
				],
				"instance_guid": "guid",
				"instance_name": "wp-postgres-tool",
				"binding_guid": "guid",
				"binding_name": null,
				"credentials": {
					"username": "user",
					"password": "pass",
					"hostname": "db.com",
					"dbname": "xxx",
					"port": "4071",
					"uri": "postgres://url",
				"syslog_drain_url": null,
				"volume_mounts": []
			}
		],

It tries to connect to localhost...

TypeError: Cannot read property 'username' of undefined
    at getCredentialsForClient (/home/user/projects/onboarding-tool-cap/node_modules/cds-dbm/dist/adapter/PostgresAdapter.js:53:26)

Hi @mikezaschka,

I've just discovered an issue in with cds-dbm with the DB schema of my pg-beershop example. There I've added entity BreweryAnalytics as projection on Brewery. This results in the view csw_breweryanalytics. This view is used in the service layer. The first deploy with cds-dbm works just fine. But when I do another deploy I get the error:

Error: Command failed: /home/gwolf/projects/pg-beershop/node_modules/cds-dbm/liquibase/liquibase --username=postgres --password=postgres --url=jdbc:postgresql://localhost:5432/beershop --classpath=/home/gwolf/projects/pg-beershop/node_modules/cds-dbm/dist/adapter/../../drivers/postgresql-42.2.8.jar --driver=org.postgresql.Driver --changeLogFile=tmp/_autodeploy.json update 

    at ChildProcess.exithandler (child_process.js:308:12)
    at ChildProcess.emit (events.js:314:20)
    at maybeClose (internal/child_process.js:1021:16)
    at Process.ChildProcess._handle.onexit (internal/child_process.js:286:5) {

When I execute the Liquibase command that is mentioned in the error I see:

gwolf@penguin:~/projects/pg-beershop$ /home/gwolf/projects/pg-beershop/node_modules/cds-dbm/liquibase/liquibase --username=postgres --password=postgres --url=jdbc:postgresql://localhost:5432/beershop --classpath=/home/gwolf/projects/pg-beershop/node_modules/cds-dbm/dist/adapter/../../drivers/postgresql-42.2.8.jar --driver=org.postgresql.Driver --changeLogFile=tmp/_autodeploy.json update 
Liquibase Community 4.1.0 by Datical
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ## 
##  Get support at liquibase.com/support          ##
##                                                ##
####################################################
Starting Liquibase at 10:42:29 (version 4.1.0 #3 built at 2020-09-28 21:02+0000)
Unexpected error running Liquibase: Migration failed for change set tmp/_autodeploy.json::1607247729779-10::gwolf (generated):
     Reason: liquibase.exception.DatabaseException: ERROR: cannot drop view csw_breweryanalytics because other objects depend on it
  Detail: view beershopservice_breweryanalytics depends on view csw_breweryanalytics
  Hint: Use DROP ... CASCADE to drop the dependent objects too. [Failed SQL: (0) DROP VIEW public.csw_breweryanalytics]
For more information, please use the --logLevel flag

Looking forward for a fix.

CU
Gregor

Hey,

Great tool. Got a first CAP project up and running on PostgreSQL Hyperscaler in no time.

When I tried converting a second project from HANA I am getting the follow error though:

error: syntax error at or near "user"

Error code '42601' seems to be a SQL syntax error.

I did a search on the user keyword within our cds files but couldn't find anything really spectaculair or uncommon.
A normal CDS build works fine and creates the gen content ( although I am not sure this is related ofc ).

Is there any way to get a better idea on why this is happening ?

Thanks,

Steven

Hi,
Potential vulnerability in dependencies jars

CVE-2022-21724
WS-2022-0080
CVE-2017-18640
In Node Package
https://www.npmjs.com/package/cds-dbm/v/0.0.27
postgresql-42.2.8.jar
h2-1.4.200.jar
https://github.com/mikezaschka/cds-dbm/tree/main/drivers
snakeyaml-1.24.jar
https://github.com/mikezaschka/cds-dbm/tree/main/liquibase/lib

postgre 42.3.2 has fixed https://mvnrepository.com/artifact/org.postgresql/postgresql
h2-2.1.210 has fixed https://mvnrepository.com/artifact/com.h2database/h2
snakeyaml-1.26 has fixed https://mvnrepository.com/artifact/org.yaml/snakeyaml

can you please update the dependencies jar with fix.

Hi @mikezaschka,

you might have seen that cds-pg was just upgraded to support @sap/cds 5.3.x. Unfortunately when using this @sap/cds version the command cds-dbm build --production fails with this output:

cds-dbm build

Generates build artifacts

Options:
  --help     Show help                                                 [boolean]
  --version  Show version number                                       [boolean]

TypeError: Cannot read property 'features' of undefined
    at Object.exports.handler (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/dist/cli/build.js:54:15)
    at /Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/node_modules/yargs/build/index.cjs:1:8973
    at j (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/node_modules/yargs/build/index.cjs:1:4852)
    at _.applyMiddlewareAndGetResult (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/node_modules/yargs/build/index.cjs:1:8942)
    at _.runCommand (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/node_modules/yargs/build/index.cjs:1:7127)
    at Bt.[runYargsParserAndExecuteCommands] (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/node_modules/yargs/build/index.cjs:1:52416)
    at Bt.parse (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/node_modules/yargs/build/index.cjs:1:36100)
    at Bt.get [as argv] (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/node_modules/yargs/build/index.cjs:1:55639)
    at Object.<anonymous> (/Users/gwolf/Documents/Projects/cap/pg-beershop/node_modules/cds-dbm/dist/cli.js:2:53)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)

Best regards
Gregor

Hi,

I am trying to upload a CSV data for a table which is defined like

entity UsersCompanies: ca.active, managed { key user: Association to one UsersInfo; key company: Association to one Companies; }

When doing a full load everything works fine.
When doing a delta load then an error is thrown

error: column "company" does not exist

The SQL statement generated is

STATEMENT: SELECT activeIs AS "activeIs", createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", user_ID AS "user_ID", company_code AS "company_code" FROM UsersCompanies ALIAS_1 WHERE user = $1 AND company = $2 LIMIT 1
which indeed has the where clause fields incorrectly defined.

It seems the associations are not correctly resolved into the proper 'company_code' and 'user_ID' fields.

Description

When running npx cds-dbm deploy I am getting this error:

Cannot find module '@sap/cds/bin/build/buildTaskHandlerFactory'
Require stack:
- /workspace/node_modules/cds-dbm/dist/build/buildTaskHandlerFactory.js
- /workspace/node_modules/cds-dbm/dist/build/buildTaskFactory.js
- /workspace/node_modules/cds-dbm/dist/cli/build.js
- /workspace/node_modules/yargs/index.cjs
- /workspace/node_modules/cds-dbm/dist/cli.js

@sap/cds/bin/build/buildTaskHandlerFactory has probably been removed with a recent version of @sap/cds.

Dependencies

{
  "dependencies": {
    "@sap/cds": "^5.4.3",
    "@sap/cds-odata-v2-adapter-proxy": "^1.7.10",
    "cds-dbm": "^0.0.27",
    "cds-pg": "^0.1.8",
    "express": "^4.17.1"
  },
  "devDependencies": {
    "@sap/eslint-plugin-cds": "^2.0.5",
    "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"
  }
}

Versions

@sap/cds: 5.4.3
@sap/cds-compiler: 2.5.2
@sap/cds-dk: 3.5.3
@sap/cds-foss: 3.0.0
@sap/cds-odata-v2-adapter-proxy: 1.7.10
@sap/eslint-plugin-cds: 2.0.5
Node.js: v14.17.6

Thanks in advance for having a look at this!

Hello,

I have two imported schemas (provider) in my CDS (wrapper) project. Both the imported schemas are different services exposed by other CDS projects. The wrapper schema has its own layer of database and filtering/massaging of underlying services. So essentially the wrapper CDS project exposes its own services that builds on top of the underlying services. In addition to that, partial entities from the underlying schema is also exposed with filtering.

the underlying project exposes a full service. the wrapper application should only use "a view" of the underlying service. In future I have plans to build other projects that utilize other aspects of the underlying service.

When testing locally all the 3 perform correctly . the wrapper CDS provides results from its own implementation and also exposes the underlying services (deployed on BTP and accessed via destination service). The problem arises when wrapper is deployed in BTP. The csn.json in gen/srv/srv does not contain the full definitions . the csn.json has only 2 definitions (1. wrapper 2. one of the underlying service). The second underlying service is not present in the csn.json of gen/srv/srv.

Just to note, all 3 definitions (wrapper and 2 underlying) are present correctly in db/csn.json. Just to tryout, I rebuild the entire thing without the CDS-DBM / CDS-PG. This csn.json in srv has the correct definitions.

Hence I am assuming this is coming from cds-dbm.

Please help

Thank you

Hi @mikezaschka,

I've tried my first steps to use cds-dbm and run it as a Tasks during MTA deployment. For that I've added a folder db-deployer to my pg-beershop. In the package.json I've defined your Git repository as the source for the npm package and tried the installation. Unfortunately that fails:

db-deployer$ npm i

npm ERR! code ENOENT
npm ERR! syscall chmod
npm ERR! path /home/gwolf/projects/pg-beershop/db-deployer/node_modules/cds-dbm/dist/cli.js
npm ERR! errno -2
npm ERR! enoent ENOENT: no such file or directory, chmod '/home/gwolf/projects/pg-beershop/db-deployer/node_modules/cds-dbm/dist/cli.js'
npm ERR! enoent This is related to npm not being able to find a file.
npm ERR! enoent 

Let me know if the intended use is different.

Best regards
Gregor

Hi @mikezaschka,

I try to get the Kyma deployment of pg-beershop more automated. For that I've added openjdk-11-jre to the Docker Image to be able to run cds-dbm. But when running:

npx cds-dbm deploy --create-db --load-via delta

inside the docker container I get the following error:

[cds-dbm] - starting delta database deployment of service db
[cds-dbm] - database beershop is already present
cds-dbm deploy [services]

Dynamically identifies changes in your cds data model and deploys them to the
database

Options:
      --help           Show help                                       [boolean]
      --version        Show version number                             [boolean]
  -s, --service                                                [default: ["db"]]
  -a, --auto-undeploy
  -d, --dry
  -l, --load-via
  -c, --create-db

{ error: relation "csw_breweryanalytics" does not exist
    at Parser.parseErrorMessage (/usr/src/app/node_modules/pg/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/usr/src/app/node_modules/pg/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/usr/src/app/node_modules/pg/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.stream.on (/usr/src/app/node_modules/pg/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  length: 120,
  name: 'error',
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '178',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '1376',
  routine: 'parserOpenTable' }

You can replicate the issue when you clone pg-beershop and run the following commands:

npm i
npm run build:docker
npm run docker:start:pg
npm run docker:start:cds
npm run docker:shell

On the shell of the docker container run:

npx cds-dbm deploy --create-db --load-via delta

Looking forward for your help. Is there any debug option I can enable? exporting DEBUG=debug did not produce more output.

Best regards
Gregor

Hi Experts,

I am trying to use cds dbm deploy command : npx cds-dbm deploy --load-via full
with latest version of sap-cds, cds-dbm, cds-pg respectively ( as per their github homepage )
"@sap/cds": "6.6.0",
"cds-pg": "0.1.35",
"cds-dbm": "0.1.2"

but it is throwing error while loading csv files with regularRegex error whereas previous versions are working properly but have vulnerability issues which the newer version got fixed with them.

Logs :
Cannot read properties of undefined (reading 'regularRegex')
at smartId (C:\Users~\srv\node_modules@sap\cds-compiler\lib\sql-identifier.js:79:9)
at _smartId (C:\Users~\srv\node_modules@sap\cds\libx_runtime\common\utils\quotingStyles.js:9:23)
at InsertBuilder.plain [as _quoteElement] (C:\Users~\srv\node_modules@sap\cds\libx_runtime\common\utils\quotingStyles.js:23:12)
at InsertBuilder._into (C:\Users\PankajValecha\TJCCode\COREEXCH\core-exchange\coreexch-srv\node_modules@sap\cds\libx_runtime\db\sql-builder\InsertBuilder.js:132:37)
at InsertBuilder.build (C:\Users\PankajValecha\TJCCode\COREEXCH\core-exchange\coreexch-srv\node_modules@sap\cds\libx_runtime\db\sql-builder\InsertBuilder.js:72:29)
at build (C:\Users~\srv\node_modules@sap\cds\libx_runtime\db\sql-builder\sqlFactory.js:65:43)
at build (C:\Users~\srv\node_modules@sap\cds\libx_runtime\db\sql-builder\sqlFactory.js:78:12)
at _cqnToSQL (C:\Users~\srv\node_modules\cds-pg\lib\pg\execute.js:148:5)
at executeInsertCQN (C:\User~\srv\node_modules\cds-pg\lib\pg\execute.js:72:32)
at PostgresDatabase._insert (C:\Users~\srv\node_modules@sap\cds\libx_runtime\db\query\insert.js:18:10) {
numericSeverity: 4
}

Can someone help me here?

Thanks, Regards
Pankaj Valecha

I wanted to learn how to use PostgreSQL with SAP-CAP Services.
For this i read Blogs from Georg and Mike. Thanx for the great work you have done so far.
For diggin deeper i cloned the "pg-beershop" demo.
First i wanted to try with local PostgreSQL running in a local docker-instance.
When i there try to deploy the database-part i get an error.

So, the command: npx cds-cbm deploy results in an error as described in the following screenshot

kind regards
Matthias

I am attempting to set non-default values in the package.json migrations section to deploy to a common schema instead of public.

Each time I build using npx cds-dbm build the default values listed in project readme are written the the /gen/db/package.json file and therefore causing an scehma incorrect deployment.

screencast of issue:
https://www.screenpresso.com/=Annjc

Hi there.

I recently submitted a pull request on cds-pg project to update the support of @sap/cds to version 6.5.0 (sapmentors/cds-pg#383).

After looking on cds-dbm project, I founded some minor changes that can be done to also support @sap/cds 6.5.0 and the new version of cds-pg.

I submitted those changes at pull request (#291). Could you guys please evaluate if it's acceptable?

Best Regards,
Rafael Yegros.

Hi,

I was trying to create project using cds-dbm. I was able to create tables and view using CAP. However I also wanted to create procedures and function like we create .hdbprocedure and .hdbfunction in sap hana. Currently I am unable to find a way to create design time artifacts for procedures or functions and then to deploy it.

Is there a way to create and deploy this?

Hi @mikezaschka and @ryegrosT8,

I've created the branch feat/update-to-cds-6 for my pg-beershop project to upgrade to the latest CAP, cds-pg and cds-dbm version. But when I try to run npm run build:cf in my project I get the output:

[INTERNAL ERROR] TypeError: Cannot read properties of undefined (reading 'build')     
at PostgresCfModuleBuilder.<anonymous> (node_modules/cds-dbm/dist/build/postgres-cf/index.js:81:97)

@ryegrosT8 did you have a working build and deploy to BTP CF?

Best Regards
Gregor

Currently the order of things in the automated changelog can crash the deployment.
Make sure, that all changes are in correct order:

• drop view statements which are based on other views
• drop view statements
• drop table statements
• create table statements
• alter table statements (add_column, drop_column, ...)
• create view statements that are not based on other views
• create view statements that are based on other views

Hi there,

I'm wondering when the initial release (v1.0.0) is scheduled? With about 40 pre-releases already, some of which include some bigger adjustments in "minor releases", it can be quite challenging to manage project dependencies. While this is expected in pre-release versions, it becomes frustrating when using automations like Renovate to update dependencies. The bot tends to automatically updates packages for minor and patch releases, which can cause issues if there are significant changes. Considering the project is "ready to be used!" (as mentioned in the README), I believe it's about time for v1.0.0 and semantic releases.

I'd love to hear your thoughts on this matter.

I'm attempting to build this repo from github. Can someone please let me know what step(s) I'm missing in order to consume/use this project from github instead of npm release?

1. set dependency in package.json to github url: "cds-dbm": "github:mikezaschka/cds-dbm#main",
2. download depedency from github url: npm install
3. rebuild cds-dbm internal dependencies: cd node_modules/cds-dbm && npm install
4. build cds-dbm for use: cd node_modules/cds-dbm && npm run build

Now, when I attempt to build postgres DB model for the root of my project: npx cds-dbm build - cds-dbm cmd is not found.

Hey,

I am currently trying to deploy a CAP application to BTP using postgresql hyperscaler option with db structured in CDS with pascal case as shown below.

After build as well, the generated CSN.json has the column names as pascal case.

While deploying the db-deployer-apt converts these pascal case fields and entity names into entirely lower case.

I am trying to insert a bulk amount of json array which has the pascal cased keys through pg-client. Hence they are failing since the column names are case sensitive and its unable to find such fields in the table.

Is there any way I could maintain the pascal casing for the column names in the postgreSQL db as well during deployment using the csn.json?

Thanks,
Vishal

I've followed the blog to create my own schema and steps. However when I'm trying to deploy my csv file it fails. t seems like it isn't picking up the custom builder for pg and instead using the default.
Here is the error. Config is below

log

TypeError: Cannot read properties of undefined (reading 'regularRegex')
at smartId (/home/jakob/git/fueldb/fueldb/node_modules/cds-pg/node_modules/@sap/cds-compiler/lib/sql-identifier.js:66:9)
at _smartId (/home/jakob/git/fueldb/fueldb/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/common/utils/quotingStyles.js:19:23)
at PGReferenceBuilder.plain [as _quoteElement] (/home/jakob/git/fueldb/fueldb/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/common/utils/quotingStyles.js:46:12)
at /home/jakob/git/fueldb/fueldb/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/db/sql-builder/ReferenceBuilder.js:106:49
at Array.map ()
at PGReferenceBuilder._parseReference (/home/jakob/git/fueldb/fueldb/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/db/sql-builder/ReferenceBuilder.js:106:34)
at PGReferenceBuilder.build (/home/jakob/git/fueldb/fueldb/node_modules/cds-pg/lib/pg/sql-builder/ReferenceBuilder.js:44:52)
at PGSelectBuilder._buildRefElement (/home/jakob/git/fueldb/fueldb/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/db/sql-builder/SelectBuilder.js:272:68)
at PGSelectBuilder._buildElement (/home/jakob/git/fueldb/fueldb/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/db/sql-builder/SelectBuilder.js:246:18)
at /home/jakob/git/fueldb/fueldb/node_modules/cds-pg/node_modules/@sap/cds/libx/_runtime/db/sql-builder/SelectBuilder.js:311:56

Package.json config

"cds": {
"build": {
"tasks": [
{
"use": "node-cf",
"for": "node-cf",
"src": "srv"
},
{
"use": "postgres-cf",
"for": "postgres-cf",
"src": "db",
"options": {
"deployCmd": "npx cds-dbm deploy --load-via delta --auto-undeploy"
}
}
]
},
"requires": {
"db": {
"kind": "database"
},
"database": {
"impl": "cds-pg",
"model": [
"srv"
],
"credentials": {
"host": "localhost",
"port": 5432,
"database": "postgres",
"user": "postgres",
"password": "postgres"
}
}
},
"migrations": {
"db": {
"schema": {
"default": "public",
"clone": "_cdsdbm_clone",
"reference": "_cdsdbm_ref"
},
"deploy": {
"tmpFile": "tmp/_autodeploy.json",
"undeployFile": "db/undeploy.json"
}
}
}
}
}

Schema:

using { cuid } from '@sap/cds/common';
namespace db;

entity FuelPrices: cuid {
SiteId: Integer;
Site_Name: String;
Site_Brand: String;
Site_Address: String;
Site_Suburb: String;
Site_State: Association to one State;
Site_Post_Code: Association to one PostCode;
Site_Latitude: String;
Site_Longitude: String;
Fuel_Type: String;
Price: Integer;
TransactionDateUtc: String;
}

entity State: cuid {
name: String;
FuelPrices: Association to many FuelPrices on FuelPrices.Site_State = $self
}

entity PostCode: cuid {
name: String;
FuelPrices: Association to many FuelPrices on FuelPrices.Site_Post_Code = $self

}

Hi community,
I changed the stack for our cap-service to cflinuxsf4 because sap has deprecated cflinuxsf3. Changing the stack for our cap-service worked without problems. But using the database-deployer throws the error, that the stack is not supported.
Here is my entry in our mta.yaml for the multitarget-application.

Any ideas?
Kind regards
Matthias

Hi,

I tried to redeploy a CAP app (which uses cds-dbm), that I already succesfully deployed some time ago.
The content of the app didn't change, only the location of the repository and therefor I needed to setup CI/CD pipelines again.

When testing my release pipeline I then came across the following error thrown by the deploy_to_postgresql task:

[APP/TASK/deploy_to_postgresql/0] ERR TypeError: Cannot read properties of undefined (reading 'definitions')
[APP/TASK/deploy_to_postgresql/0] ERR at _newUpdate (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/common/utils/resolveView.js:335:38)
[APP/TASK/deploy_to_postgresql/0] ERR at _newQuery (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/common/utils/resolveView.js:632:42)
[APP/TASK/deploy_to_postgresql/0] ERR at resolveView (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/common/utils/resolveView.js:650:20)
[APP/TASK/deploy_to_postgresql/0] ERR at _plainUpdate (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/common/utils/cqn2cqn4sql.js:944:10)
[APP/TASK/deploy_to_postgresql/0] ERR at _convertUpdate (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/common/utils/cqn2cqn4sql.js:956:12)
[APP/TASK/deploy_to_postgresql/0] ERR at cqn2cqn4sql (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/common/utils/cqn2cqn4sql.js:998:12)
[APP/TASK/deploy_to_postgresql/0] ERR at PostgresDatabase.handler [as _rewrite] (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/generic/rewrite.js:29:15)
[APP/TASK/deploy_to_postgresql/0] ERR at PostgresDatabase.handle (/home/vcap/deps/1/node_modules/@sap/cds/lib/srv/srv-dispatch.js:59:53)
[APP/TASK/deploy_to_postgresql/0] ERR at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

My project uses @sap/[email protected], [email protected] and [email protected]

The deployer app that's generated by my mta.yaml (through mbt build) gets the following package.json:

As there is no package-lock.json in the generated app, it causes the newest fitting versions to be installed for the deployer app when deploying, so I thought that might be an issue.
However, when I install the newest versions of the 3 packages above and deploy to a local postgres (in docker) the deployment works without any errors with the exact same deploy command.

Can you support here?
I'm a little bit lost as to what could be the issue.

Thanks & Best regards

npm install fails due to incompatible architecture with Apple M1 chipset. @sap/hana-client is not used anywhere else in the project so I suggest that we remove the un-used dev dependency.

Hi,

I'm facing the same issue as 307.
The difference is, that I didn't copy the db-deployer-apt from the beershop application.

The logs after the deployment are:

2023-03-06T17:07:16.41+0100 [APP/TASK/deploy_to_postgresql/0] OUT [cds-dbm] - starting delta database deployment of service db
2023-03-06T17:07:37.04+0100 [APP/TASK/deploy_to_postgresql/0] OUT [cds-dbm] - delta successfully deployed to the database
2023-03-06T17:07:37.28+0100 [APP/TASK/deploy_to_postgresql/0] OUT [cds-dbm] - loading data from /home/vcap/app/data/schwarz.sci.monitoring-MetricTypesList.csv
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR cds-dbm deploy [services]
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR Dynamically identifies changes in your cds data model and deploys them to the
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR database
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR Options:
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR --help           Show help                                       [boolean]
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR --version        Show version number                             [boolean]
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR -s, --service                                                [default: ["db"]]
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR -a, --auto-undeploy
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR -d, --dry
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR -l, --load-via
2023-03-06T17:07:37.32+0100 [APP/TASK/deploy_to_postgresql/0] ERR -c, --create-db
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR TypeError: Cannot read properties of undefined (reading 'definitions')
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PGSelectBuilder._columns (/home/vcap/deps/1/node_modules/cds-pg/lib/pg/sql-builder/SelectBuilder.js:84:32)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PGSelectBuilder.build (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/sql-builder/SelectBuilder.js:81:10)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at build (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/sql-builder/sqlFactory.js:65:43)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at build (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/sql-builder/sqlFactory.js:74:12)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at _cqnToSQL (/home/vcap/deps/1/node_modules/cds-pg/lib/pg/execute.js:148:5)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at executeSelectCQN (/home/vcap/deps/1/node_modules/cds-pg/lib/pg/execute.js:51:34)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PostgresDatabase._read (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/query/read.js:74:10)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at module.exports [as _READ] (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/generic/read.js:15:15)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at next (/home/vcap/deps/1/node_modules/@sap/cds/lib/srv/srv-dispatch.js:75:36)
2023-03-06T17:07:37.33+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PostgresDatabase.handle (/home/vcap/deps/1/node_modules/@sap/cds/lib/srv/srv-dispatch.js:79:6)
2023-03-06T17:07:37.34+0100 [APP/TASK/deploy_to_postgresql/0] ERR npm notice
2023-03-06T17:07:37.34+0100 [APP/TASK/deploy_to_postgresql/0] ERR npm notice New major version of npm available! 8.19.3 -> 9.6.0
2023-03-06T17:07:37.34+0100 [APP/TASK/deploy_to_postgresql/0] ERR npm notice Changelog: <https://github.com/npm/cli/releases/tag/v9.6.0>
2023-03-06T17:07:37.34+0100 [APP/TASK/deploy_to_postgresql/0] ERR npm notice Run `npm install -g [email protected]` to update!
2023-03-06T17:07:37.34+0100 [APP/TASK/deploy_to_postgresql/0] ERR npm notice
2023-03-06T17:07:37.35+0100 [APP/TASK/deploy_to_postgresql/0] OUT Exit status 1

I've already tried to deploy the same schema to a locally installed postgres database (via docker), by issuing the same command that the deploy task runs:

npx cds-dbm deploy --load-via delta --auto-undeploy

This doesn't produce any error on my machine, and it doesn't even call the functions that are mentioned in the callstack above.

Unfortunately I have no idea how to solve this error or what to check, as it looks to me that the deployment itself is working (see success messages in the logs above).

Can someone help me?

Thanks, Regards

In #179 this issue was identified:

But 'user' turns out to be a reserved keyword in Postgresql.
https://www.postgresql.org/docs/current/sql-keywords-appendix.html
Might be an idea to scan for those and give a warning ?

the question is where do we implement this? in cds-dbm or cds-pg.

Hi @mikezaschka,

I try to make the required adjustments for #1 in adjust-for-sapcp-postgresql-hyperscaler. But with the commit I got the following error:

husky > commit-msg (node v12.19.0)
⧗ input: fix: SAP CP connection information
✖ Please add rules to your commitlint.config.js
- Getting started guide: https://git.io/fhHij
- Example config: https://git.io/fhHip [empty-rules]

✖ found 1 problems, 0 warnings
ⓘ Get help: https://github.com/conventional-changelog/commitlint/#what-is-commitlint

I think the file commitlint.config. is missing or?

Best regards
Gregor

Many thanks for the quick response on #155 and the subsequent release. I installed and tested it but the dialect field is still missing in the package.json generated during a build. I checked the downloaded files in my node_modules folder and there the template for package.json is still the old version without the dialect field. Could you check if there went anything wrong with this release? Thanks in advance :-)

Hi @mikezaschka,

after the deployment to the SAP CP PostgreSQL Hyperscaler intance works let's try to get it also working for Azure PostgreSQL 11.

The execution of cds-dbm stops with:

Error: Command failed: /home/vcap/cds-dbm/liquibase/liquibase --username=beershop@beershop --password=xxx --url=jdbc:postgresql://beershop.postgres.database.azure.com:5432/beershop?ssl=true --classpath=/home/vcap/cds-dbm/dist/adapter/../../drivers/postgresql-42.2.8.jar --driver=org.postgresql.Driver --defaultSchemaName=_cdsdbm_clone --changeLogFile=tmp/_autodeploy.json update 

when executing this command standalone I get this error message:

Starting Liquibase at 22:08:18 (version 4.1.0 #3 built at 2020-09-28 21:02+0000)
Unexpected error running Liquibase: Migration failed for change set tmp/_autodeploy.json::1605820072864-10::gwolf (generated):
     Reason: liquibase.exception.DatabaseException: ERROR: function pg_buffercache_pages() does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 230 [Failed SQL: (0) CREATE VIEW _cdsdbm_clone.pg_buffercache AS SELECT p.bufferid,
    p.relfilenode,
    p.reltablespace,
    p.reldatabase,
    p.relforknumber,
    p.relblocknumber,
    p.isdirty,
    p.usagecount,
    p.pinning_backends
   FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, relforknumber smallint, relblocknumber bigint, isdirty boolean, usagecount smallint, pinning_backends integer);]

I've checked that the PostgreSQL extension pg_buffercache is active by running this commands via psql:

beershop=> CREATE EXTENSION IF NOT EXISTS pg_buffercache;
NOTICE:  extension "pg_buffercache" already exists, skipping
CREATE EXTENSION
beershop=> SELECT * FROM pg_extension;
      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
--------------------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql            |       10 |           11 | f              | 1.0        |           | 
 pg_stat_statements |       10 |         2200 | t              | 1.6        |           | 
 pg_buffercache     |       10 |         2200 | t              | 1.3        |           | 
(3 rows)

Perhaps some of the same magic as with SAP CP is needed.

Hi @mikezaschka.

I was not sure if I should open a new issue or add a comment on the closed Pull Request (#292).

Choose to open a new Issue and describe an workaround in case of the fix takes some time.

The problem:

After upgrade to version 0.1.0, I got the following error

$ npx cds-dbm deploy --load-via delta --auto-undeploy
node:internal/modules/cjs/loader:936
  throw err;
  ^

Error: Cannot find module 'date-format'
Require stack:
- project/node_modules/cds-dbm/dist/ChangelogGenerator.js
- project/node_modules/cds-dbm/dist/cli/generate.js
- project/node_modules/yargs/index.cjs
- project/node_modules/cds-dbm/dist/cli.js
    at Function.Module._resolveFilename (node:internal/modules/cjs/loader:933:15)
    at Function.Module._load (node:internal/modules/cjs/loader:778:27)
    at Module.require (node:internal/modules/cjs/loader:1005:19)
    at require (node:internal/modules/cjs/helpers:102:18)
    at Object.<anonymous> (project/node_modules/cds-dbm/dist/ChangelogGenerator.js:10:39)
    at Module._compile (node:internal/modules/cjs/loader:1105:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1159:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Function.Module._load (node:internal/modules/cjs/loader:822:12)
    at Module.require (node:internal/modules/cjs/loader:1005:19) {
  code: 'MODULE_NOT_FOUND',
  requireStack: [
    'project/node_modules/cds-dbm/dist/ChangelogGenerator.js',
    'project/node_modules/cds-dbm/dist/cli/generate.js',
    'project/node_modules/yargs/index.cjs',
    'project/node_modules/cds-dbm/dist/cli.js'
  ]
}

When I was running tests for the PR that helped to create this new version (#291), the class ChangelogGenerator was not exists in my dist folder:

The Dist folder generated for PR #291.

The published version on npm seems quite different:

I wondering if something goes wrong when publishing the package at npm.org. Could you check please? Maybe missing “npm run build” script automation?

Workaround:
1 - Go to node_modules/cds-dbm folder and run:

npm i
npm run build

2 - Instead of run the cli cds-dbm command-line, goes to root folder of your app and use :

node node_modules/cds-dbm/dist/cli.js deploy  --load-via delta --auto-undeploy

Best Regards.
Rafael Yegros.

Hi Mike,

I've tried the 0.0.12 version in the pg-beeshop with the deploy to SAP CP with the PostgreSQL Hyperscaler. The following credentials are provided:

  "credentials": {
    "username": "981c1fe2f6fa",
    "dbname": "OQypNRRXsOKZ",

unfurtunately that currently results in this error:

error: database "981c1fe2f6fa" does not exist
    at Parser.parseErrorMessage (/home/vcap/deps/0/node_modules/cds-dbm/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/home/vcap/deps/0/node_modules/cds-dbm/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home/vcap/deps/0/node_modules/cds-dbm/node_modules/pg-protocol/dist/parser.js:39:38)
    at TLSSocket.<anonymous> (/home/vcap/deps/0/node_modules/cds-dbm/node_modules/pg-protocol/dist/index.js:10:42)
    at TLSSocket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:295:12)
    at readableAddChunk (_stream_readable.js:271:9)
    at TLSSocket.Readable.push (_stream_readable.js:212:10)
    at TLSWrap.onStreamRead (internal/stream_base_commons.js:186:23) {
  length: 97,
  severity: 'FATAL',
  code: '3D000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postinit.c',
  line: '880',
  routine: 'InitPostgres'
}

Looks like your change to detect if the database exists takes the username instead of the dbname.

When reverting back to 0.0.11 everything is fine and I've made the Node.JS deployer working again.

Best regards
Gregor

Hello,

Multiple projects being deployed to same postgres database on Cloud Foundry cause the reference schema to be overwritten and dropping of CDS views for other project.

I have two projects both deploying to same postgres database in cloud foundry. At the time of deploy I am not able to specify the dbname. This causes both projects to refer to same database. The reference schema provided in the package.json of each project is not honored. This particular code is not going inside the if statement.

Please help

Hi

I am facing an issue when running cds-dbm deploy passing the --create-db argument:

node ➜ /workspaces/cap-ideas $ npx cds-dbm deploy --create-db    
[cds-dbm] - starting delta database deployment of service db
cds-dbm deploy [services]

Dynamically identifies changes in your cds data model and deploys them to the
database

Options:
      --help           Show help                                       [boolean]
      --version        Show version number                             [boolean]
  -s, --service                                                [default: ["db"]]
  -a, --auto-undeploy
  -d, --dry
  -l, --load-via
  -c, --create-db

error: database "slonik" 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) {
  length: 91,
  severity: 'FATAL',
  code: '3D000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postinit.c',
  line: '875',
  routine: 'InitPostgres'
}

The issue only occurs when using the --create-db argument.
Running e.g.

• npx cds-dbm deploy
• npx cds-dbm deploy --auto-undeploy

works fine.

default-env.json looks like this.

{
  "VCAP_SERVICES": {
    "postgres": [
      {
        "name": "postgres",
        "label": "postgres",
        "tags": [
          "postgres"
        ],
        "credentials": {
          "host": "postgres-svc",
          "port": "5432",
          "user": "slonik",
          "password": "topsecret",
          "database": "cap",
          "schema": "public"
        }
      }
    ]
  }
}

Looks like the database property is being ignored trying to use the default database which falls back to the user's name.

Thanks in advance,

Pascal

Hello Gregor,

I really appreciate what you have done with the pg integration into CDS. I have been trying to understand and learn this. What I am posting here is two fold (1) an issue I am facing (2) help I would like so I can start troubleshooting CDS-DBM on my own.

(1) I built a simple application using the risks/mitigations tutorial from SAP. It runs fine locally on my mac. But when I deploy to BTP that fails. I am using a standard pg-database and not trial. Here is the error I get.

(2) I am trying to figure out a way to debug this. full disclosure, I am a newbie to nodejs. But I do have many years of development experience so I think I am able to follow what is happening here.
How do I debug cds-dbm? (deploy). either locally or on BTP? Are you able to point me to some help for that please?
the deployer itself stays in stopped state.
Does it have to do with the serverjs? Do I change the start script to run the server.js and then ssh into the app?

Thank you
Durga

Hi,

I'm using cds-dbm to deploy my cds schema to a PostgreSQL service instance on our BTP account.
This has worked for many deployments up until now.
I recently made a change to my cds schema and tried to deploy it again (without changing any config of cds-dbm). This now fails with the error message in the title.

With the CF Logs for the deployer application I was able to track down the following callstack:

cds-dbm deploy [services] (STDERR, APP/TASK/deploy_to_postgresql)#

[Thu Feb 16 10:27:39 UTC 2023] at async exports.handler (/home/vcap/deps/1/node_modules/cds-dbm/dist/cli/deploy.js:38:9)
[Thu Feb 16 10:27:39 UTC 2023] at async PostgresAdapter.deploy (/home/vcap/deps/1/node_modules/cds-dbm/dist/adapter/BaseAdapter.js:121:9)
[Thu Feb 16 10:27:39 UTC 2023] at PostgresAdapter.initCds (/home/vcap/deps/1/node_modules/cds-dbm/dist/adapter/BaseAdapter.js:202:19)
[Thu Feb 16 10:27:39 UTC 2023] Error: [cds-dbm] - failed to load model undefined

Below you can find my cds / cds-dbm config (last changed 2 months ago, with the latest successful deployment on 10.02.2023):

...
"db": {
            "[production]": {
                "kind": "database",
                "dialect": "plain",
                "impl": "cds-pg",
                "model": ["srv"]
            },
...
"build": {
        "tasks": [
            {
                "for": "node-cf",
                "src": "srv",
                "options": {
                    "model": ["db", "srv", "app"]
                }
            },
            {
                "use": "cds-dbm/dist/build/postgres-cf",
                "for": "postgres-cf",
                "src": "db",
                "options": {
                    "deployCmd": "npx cds-dbm deploy --load-via delta --auto-undeploy"
                }
            }
        ]
    },
    "migrations": {
        "db": {
            "schema": {
                "default": "public",
                "clone": "_cdsdbm_clone",
                "reference": "_cdsdbm_ref"
            },
            "deploy": {
                "tmpFile": "tmp/_autodeploy.json",
                "undeployFile": "db/undeploy.json"
            }
        }
    }

Additionally an excerpt of my mta.yaml file:

build-parameters:
  before-all:
    - builder: custom
      commands:
        - tsc --skipLibCheck
        - npm install --omit=dev --ignore-scripts
        - cds build --production
        - npx rimraf srv/**/*.js
        - npx rimraf srv/**/*.js.map
        - npx rimraf test/**/*.js
        - npx rimraf test/**/*.js.map
        - npx rimraf gen/db/src/gen/data
        # - npx rimraf gen/db/data/schwarz.sci.monitoring-MetricDefinitions.csv
        - cp ./.cdsrc.json ./gen/srv
...
# --------------------- DB-Deployer MODULE -------------------
  - name: argus-sci-monitoring-db-deployer
  # ------------------------------------------------------------
    type: custom
    path: gen/db
    parameters:
      buildpacks: [https://github.com/cloudfoundry/apt-buildpack#v0.2.2, nodejs_buildpack]
      no-route: true # application doesn't need to be accessed after deployment
      no-start: true # application doesn't need to be startable
      disk-quota: 2GB
      memory: 512MB
      tasks:
        - name: deploy_to_postgresql
          command: chmod 755 deploy.sh && ./deploy.sh
          disk-quota: 2GB
          memory: 512MB
    build-parameters:
      ignore: ["node_modules/"]
    requires:
      - name: argus-sci-monitoring-database
...

I'm using @sap/cds version 5.9.8 and cds-dbm version 0.0.36.

After unsuccessfully deploying my changed cds model, I changed it back to the original state (the last successfully deployed version), but even that now fails with the same error message.

As my last change to the cds-dbm config was earlier than my last successful deployment with above config, I'm really confused as to where the error might be hiding.

Can you help me to track down the error?
Do you need further information?

Best regards & thanks in advance

Hi @frankmeertens / @mikezaschka
as suggested in #309 I also tried using the latest versions. It works locally, but when I try to deploy to the BTP, it still gives me the following error with the callstack:

2023-03-10T15:04:11.39+0100 [APP/TASK/deploy_to_postgresql/0] OUT [cds-dbm] - starting delta database deployment of service db
   2023-03-10T15:04:32.23+0100 [APP/TASK/deploy_to_postgresql/0] OUT [cds-dbm] - delta successfully deployed to the database
   2023-03-10T15:04:32.59+0100 [APP/TASK/deploy_to_postgresql/0] OUT [cds-dbm] - loading data from /home/vcap/app/data/schwarz.sci.monitoring-MetricTypesList.csv
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR cds-dbm deploy [services]
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR Dynamically identifies changes in your cds data model and deploys them to the
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR database
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR Options:
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR --help           Show help                                       [boolean]
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR --version        Show version number                             [boolean]
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR -s, --service                                                [default: ["db"]]
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR -a, --auto-undeploy
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR -d, --dry
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR -l, --load-via
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR -c, --create-db
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR TypeError: Cannot read properties of undefined (reading 'regularRegex')
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at smartId (/home/vcap/deps/1/node_modules/@sap/cds-compiler/lib/sql-identifier.js:79:9)
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at _smartId (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/common/utils/quotingStyles.js:9:23)
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PGReferenceBuilder.plain [as _quoteElement] (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/common/utils/quotingStyles.js:23:12)
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at /home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/sql-builder/ReferenceBuilder.js:97:49
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at Array.map (<anonymous>)
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PGReferenceBuilder._parseReference (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/sql-builder/ReferenceBuilder.js:97:34)
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PGReferenceBuilder.build (/home/vcap/deps/1/node_modules/cds-pg/lib/pg/sql-builder/ReferenceBuilder.js:44:52)
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PGSelectBuilder._buildRefElement (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/sql-builder/SelectBuilder.js:276:68)
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at PGSelectBuilder._buildElement (/home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/sql-builder/SelectBuilder.js:250:18)
   2023-03-10T15:04:32.64+0100 [APP/TASK/deploy_to_postgresql/0] ERR at /home/vcap/deps/1/node_modules/@sap/cds/libx/_runtime/db/sql-builder/SelectBuilder.js:315:56

After analyzing the callstack, the error should only occur when I pass a dialect that doesn't exist in the /@sap/cds-compiler/lib/sql-identifier.js sqlDialects Object.
However, as I'm passing 'plain' it should work, especially, since the calling function @sap/cds/libx/_runtime/common/utils/quotingStyles.js makes sure to not pass an undefined _dialect variable.

I'm really confused as to why this error occurs only when deploying to BTP.

Below you can find my cds config, in which I don't see any error:

...
"db": {
            "[production]": {
                "kind": "database",
                "dialect": "plain",
                "impl": "cds-pg",
                "model": ["srv"]
            },
            "[development]": {
                "kind": "database",
                "dialect": "plain",
                "impl": "cds-pg",
                "model": ["srv"],
                "credentials": {
                    "host": "localhost",
                    "port": 5432,
                    "database": "monitoring",
                    "user": "postgres",
                    "password": "postgres"
                }
            },
...

Unfortunately I have no idea on how to analyze what's happening during the deployment other than checking the logs / callstack.
Do you have an idea why this might happen?

Thanks in advance
Regards

Hi,

It seems that the max length of a table name in Postgresql is 63 bytes.

I am getting errors now during the initial deploy as it is trying to create views that already exist - because the names get truncated at 63 chars and are therefore similar.

I am not using a fancy long name but the names are that long due to the pattern 'service name_view' ( which seems logical ).

eg

ApiPublicProfilesManagersDataManagementService_DataCampaignsSteps ApiPublicProfilesManagersDataManagementService_DataCampaignsStepsStats

both end up being

apipublicprofilesmanagersdatamanagementservice_datacampaignsste

Hence the error:

ERROR: relation "apipublicprofilesmanagersdatamanagementservice_datacampaignsste" already exists STATEMENT: CREATE VIEW ApiPublicProfilesManagersDataManagementService_DataCampaignsStepsStats AS SELECT

So it is not really an issue persé but something to be aware off ...

Thanks,

Steven

Hi,

When deploying creating a new DB - using npx cds-dbm deploy --create-db - I am getting the following error thrown (logs adjusted and db name replaced with XXXX).

npx cds-dbm deploy --create-db [cds-dbm] - starting delta database deployment of service db (node:10333) UnhandledPromiseRejectionWarning: error: database "XXXX" does not exist at Parser.parseErrorMessage (/node_modules/pg-protocol/dist/parser.js:287:98) at Parser.handlePacket (/node_modules/pg-protocol/dist/parser.js:126:29) at Parser.parse (/node_modules/pg-protocol/dist/parser.js:39:38) at Socket.<anonymous> (/node_modules/pg-protocol/dist/index.js:11:42) at Socket.emit (events.js:400:28) at addChunk (internal/streams/readable.js:293:12) at readableAddChunk (internal/streams/readable.js:267:9) at Socket.Readable.push (internal/streams/readable.js:206:10) at TCP.onStreamRead (internal/stream_base_commons.js:188:23) (Use node --trace-warnings ...to show where the warning was created) (node:10333) 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:10333) [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. [cds-dbm] - created database XXX

But the database is created anyways ... so it is a bit confusing. The command seems to work but somehow fails to handle a promise rejection ?

The DB logs shows

UTC [162] FATAL: database "XXXX" does not exist

Which is prob causing this ...

Kind Regards

Steven

support for JRE packages was removed from SAP/SapMachine quite a while ago with this commit.

As a result deploy to Cloud Foundry no longer work.

Templates in "https://github.com/mikezaschka/cds-dbm/tree/main/src/build/postgres-cf/template" to be modified for "sapmachine-11-jdk" package support. This requires at least package change in "apt.yml" and line
"export JAVA_HOME=/home/vcap/deps/0/apt/usr/lib/jvm/sapmachine-11" modification in "deploy.sh".

Proposed pull request: #33