Skip to content

DB‐Layer Sync Agenda

Jump to bottom
Patrice Bender edited this page Oct 30, 2024 · 48 revisions

Please add new topics at the top of the document under Topic Pool

### Short Topic Title

- Duration:
- Responsible:
- Link:
- Decision:

Topic Pool

Things to discuss when "Next Sync" does not have enough items.

Next Sync

path expressions within infix filters

Problem:

OData queries may lead to queries like:

SELECT from Authors where exists books[genre.name = 'Thriller']

We generally reject path expressions within infix filter conditions…

In this POC the subquery for the exists books is enriched with the filter condition (genre.name = 'Thriller'):

SELECT from Authors as Authors where exists (
  SELECT 1 from Books as books where books.author_ID = Authors.ID AND genre.name = 'Thriller'
)

→ This query is then again transformed by cqn4sql to get the proper joins in the sub-select → This approach is a quick-win to enable the queries which used to be working with cds7 (due to native hana assocs)

→ There is a better approach, which probably performs better. The initial query could be re-written to:

SELECT from Authors where exists books[exists genre[name = 'Thriller']]

This concept of pre-pending exists predicates before path expressions and re-writing them into filter conditions could generally be applied to path-expression in where clauses, see the next section.

Another Problem and further optimizations:

Given this query:

SELECT from Authors where books.title LIKE '%Potter%'`

will return the same Authors.ID multiple times for each Harry Potter book → We get the same ID 7 times…

It is safe to say that a path expression (especially following a to-many association) implies an exists semantic.

The above query is better expressed as

SELECT from Authors where exists books[title LIKE '%Potter%']

as this will match an author only once, even if this author has multiple books which would satisfy the condition.

So we could re-write the initial query and prepend the exists predicate in front of the path expression and re-write the condition into a filter for the assoc following the exists.

  • It is not trivial to do so, what about
SELECT from Authors where toLower( books.title ) LIKE '%potter%'
  • → the query could be re-written to
SELECT from Authors where exists books[ toLower(title) LIKE '%potter%' ]

is this worth the effort?

db.model

Ad-hoc services are depending upon undefined behaviors.

const db = await cds.deploy ('<filename>') .to ('sqlite::memory:')

$count queries with assocs?

{
  "func": "count",
  "as": "$count",
  "args": [
    {
      "ref": [
        "books" // assocs are not allowed as values for expressions
      ]
    }
  ]
}

→ Maybe construct a subquery and count returned rows

2024-10-28

Unified UPSERT PR

DELETE and UPDATE without where clause

NOT IN as operator

Go over open issues

2024-09-30

Race Condition in hana/stream.test.js?

2024-08-25

hana native types

hana-native types like hana.REAL, see internal issue also related https://github.com/cap-js/cds-dbs/issues/769

→ accepted, to be supported with enxt version

postgres specific connection issues

see internal issue + mail

→ seems like weird setup

hdb/hana issue for streaming blob in and out within same transaction

https://github.com/cap-js/cds-dbs/pull/561

→ cdsnode bli created to implement a workaround

2024-04-15

Enabling On-Premise Database Connections through the Cloud with New Proxy Configuration Feature in @cap-js/postgres

Federico has successfully developed a solution for connecting to an on-premise database through the cloud by using a socket as a proxy. This approach involves redirecting the database connection to the Cloud Connector via a destination service, thereby reaching the on-premise system without directly involving the Postgres connector. Federico suggests integrating the ability to specify a destination in the database configuration into the @cap-js/postgres package. This would allow the pg client to initiate a socket connection based on the destination, rather than the standard hostname and port configuration.

very deeply nested expand queries as result of a PUT

see test 'PUT delete deep composition must not affect other same level composition in response' in runtime: cds/tests/_runtime/odata/__tests__/integration/complexAssociationsAndCompositions.test.js

→ A huge CQN is produced which leds to a hung up SQL Service, probably due to cqn4sql not terminating.

  • Duration: 15 min
  • Responsible: Lars / Patrice
  • Link: https://github.com/cap-js/cds-dbs/pull/576
  • Decision: This must be handled already by deep-queries.js, we should not construct a query like this. However, we could use recursive queries for models like this to push down the handling of the nested data to the database. This will require some more effort.

deep search

https://github.com/cap-js/cds-dbs/pull/252

  • Duration: 10 min
  • Responsible: Johannes
  • Decision: Now that we have @cap-js/hana, we could make use of this feature → DO IT

quoted mode

  • Duration: 10 min
  • Responsible: Johannes
  • Decision: We will add a quoted mode.

2024-03-18

Support deep delete for queries like:

await cds.delete({ ref: [E.name], as: 'a' }).where(`value1 < 10`)
         .where({ 'not exists': cds.read(E, ['1']).alias('b').where('b.value1 = a.value1 AND b.value2 >= 10')})
  • Duration: 10 min
  • Responsible: Johannes
  • Link: snow ticket
  • Decision: Children should be deleted; Johannes will build a test for this. Was this supported before?? → https://github.com/cap-js/cds-dbs/pull/571 → according to issue creator, it worked before.

array of - who normalizes to []?

At the moment, this is done by the protocol adapter. However, that should be solved by the database. Can compiler generate a default value?

AFC Incoming Issues

  • Duration:
  • Responsible:
  • Link:
  • Decision:

use compilers keywords via cds (cds needs to export postgres and hana keywords like it does for sqlite)

Parameterized views

  • Duration: 30 min
  • Responsible: Bob & Patrice
  • Link: https://github.com/cap-js/cds-dbs/pull/488
  • Decision:
    • Compiler removes beta and allows views with params
    • document limitations in runtimes (Write operations, Deep operations...) → Johannes
    • sync with java RT: what do they support? → also sync on documentation → Patrice
    • alternative for sqlite? → Bob
    • → We could use session variables for each view and use them whereever the parameters are followed

Whats the state of Deep UPDATE loses track of relationships

2024-03-11

search with aggregations

make cds.infer work with joins

How to handle postgres issues

  • Duration: 5 min
  • Responsible: all
  • Decision:
    • We can't help everyone in every scenario, it is okay if issues stay open for a while.
    • during reCAP we can suggest a technical committee, including externals especially for PG. → Meet regularly and sync on community, features, issues, … → Patrice

reCAP Session(s)?

  • Duration: 5 min
  • Responsible: all
  • Decision: Mail to Sebastian → we want a 40 minute timeslot for promoting the new dbs → Patrice

Add pool configuration to /docs?

  • Find better default max pool size for postgres and align with other dbs
  • Update troubleshooting docs re pool timeout error messages, Steffen / Sebastian

2024-03-04

Automatically keep dependencies up2date

Clone this wiki locally