Initial PoC for db-service helpers

[BobdenOs]

Concept

CSN has a lot of complexity which can make it scary to write a database layer. The idea of the db-service helpers is to remove certain requirements to lower the difficulty of writing a db layer. By simply requiring a helper and applying it in the init of the service this functionality now no longer needs to be implemented any more.

const one = require('@cap-js/db-service/lib/helpers/one')

class JSService extends DatabaseService {
  init() {
    one(this) // Transforms cqn.SELECT.one -> cqn.SELECT.limit and returns the first row or null
    super.init()
  }
}

Layout

In this PoC the naming scheme of the helpers is as follows:

• the CQN property it implements (e.g. count, where, groupBy etc.)
• the general name of the functionality it implements (e.g. computed, projection, schema)

Capabilities

This PoC has some limited functionality (also is not fully functionally correct), but this PoC is capable of serving the bookshop and mostly sflight. It is able to show the analytics page of sflight, but filters currently result in errors.

Finding

It might not be very surprising that the performance of the PoC is not very good. It takes ~30 seconds for the sflight joins to be fully calculated. It also shows what is exactly being requested from the database. It is easy to forget the complexity that is being requested. When deploying the view which results in 7 left joins it won't indicate the complexity that the database has to resolve. When we send the SQL statement to the database it will resolve the fastest method to calculate the result for the query. Splitting the where clause, on conditions, potentially even skipping whole joins if the final query does not require their information to be calculated. This is mostly done through static analysis and could be calculated into the CQN query. While using views this cannot be done, because the entity that is being selected from is flat. So it would require to not select from the views, but instead rewrite the query to directly select from the source tables. That way the query would be bigger, but would be pre optimized. Allowing the database to skip the optimization steps and directly start executing the query. With the usual CAP APP executing the same query many times with only slight differences it would be possible to re use the optimized query between sessions. With optimized queries the PoC would be able to run sflight with similar performance to sqlite.