How I migrated my on-prem xsa cds3+hana solution to cds6+sqlite and deployed it on cheap linux hosting

[alex_myakinkii]

tl;dr

I ported some old stuff to run locally with persistent sqlite database (as a file), and you can check it out here https://github.com/myakinkii/fitrepublic

Rationale

Almost two years ago I wrote a post about stuff I was working on.

From the domain perspective it was a fitness oriented solution to connect Coaches and Athletes at some kind of a platform (at first we also wanted to include Gyms and full billing cycle, hence some billing/payment artefacts in code).

Eventually it did not lift off as a business or startup, but after some changes in approach and features, we still made it public in CIS region as a free product which now has some real users (who we must support of course).

Now as my old hxe box had to be shut down, I needed a cheap option to run the system.

I also wanted to make local development possible.

So, full productive code is open sourced now and hosted at github repo mentioned above.

Probably someone may find it useful and play with it as a bookshop alternative.

Disclaimer

Stuff provided as is, can contain legacy or just bad code examples from cap/ui5 perspective, and all that jazz.

Also this may still be runnable in xsa or deployable as an mtar - but currently I don't have a proper hxe instance to check (though hxe sps6 seem to be avaliable atm).

Deployment

Of course, we no longer have cf environment with service registry and bindings, but for flp (admin interface) I just used some symlinks and ui5 cli with proxy middleware, while for backend apis it was ok just to set up regular services (OS level, systemd in my case) to daemonize it.

The funkiest (pun intended) thing is of course a local sqlite database I use for this - with both admin and mobile services accessing the my.db file in rw mode.

Of course, we also use nginx for cors stuff and other reverse proxy convenience (like our custom url shortener service and staging).

No docker or other containerisation technology was used.

Keep in mind please, that even when I call this stuff "productive" this is essentially just an MVP with no active planned development, which I just wanted "to port and keep running", so some aspects of "proper productive" solution are omitted.

Also I hope I do not violate SAP license (they actually even promised to make CAP opensource at recap), so this may be also interesting from the perspective of using CAP without hana or btp at all.

sqlite stuff

Schema migration

As was mentioned above, I decided to go hardcore and use sqlite as a productive database.

And of course this approach is missing one pretty important and complicated aspect of what cap+hana does for you out of the box - database schema and data migration.

Do not forget about it in case you consider using cap without hana - you may want to take a look at this blog about postgres and specifically this stuff https://github.com/mikezaschka/cds-dbm that manages schema migrations (apparently via liquibase just like hana)

Limitations/issues

From the technical limitations perspective the first thing that you find is "path expressions not supported" - which is quite obvious (this is just a relational db), but still unpleasant.

So, in case you want to use associations in your cql or cds grants it will not work.

At first I went on to create views to manually  resolve associations via explicit joins (like V_Calendar for example)

But later I found another way which was related to my concern from the previous post - that it would not seem to be possible to perform sql crud operations on entities you did not want to expose on application service level, but which were of course a part of db schema.

And it turned out that you could do exactly that - you only needed to use full entity name (including namespace from your cds file).

For example, this

cds.parse.cql(

  "SELECT from Workouts { id, description, purchase.coach.name, timestamp }"+

  "where purchase.owner.id='"+profile.id+"' and ( lower(description) like '"+like+"' or lower(purchase.coach.name) like '"+like+"' )"

)

became this (though was rewritten without join to Purchases)

cds.parse.cql(

  "SELECT from "+ns+".Workouts as W "+

  "left join "+ns+".Clients as Cl on W.client_id=Cl.id"+

  " left join "+ns+".Coaches as Ch on W.coach_id=Ch.id "+

  "{ W.id, W.description, Ch.name as coachName, W.timestamp }"+

  "where Cl.id='"+profile.id+"' and ( lower(W.description) like '"+like+"' or lower(Ch.name) like '"+like+"' )"

)

I am definitely not sure this is a recommended approach as create and update methods seem to return some "raw" db-specific results instead of "clean entities".

But still it worked.

 

Another thing that was unexpected and very unpleasant was case insensitive search for non-ascii characters.

It turned out that sqlite does not support stuff like UPPER for non-ascii symbols out of the box.

And as I wanted to keep my cordova apps free of any modifications to avoid releasing new versions to app store and google play, I decided to find a way to hack it.

The problem there was that  backend received modified search string instead of original one as my frontend used caseSensitive:true flag to create model Filter (which sent uppercased string in odata $filter expression).

So I applied some "heuristics" to create new where clause for my searches that replaced original query in either before or on handlers (see Content entity handlers in index.js)

The implementation itself is pretty ugly (parsing "cqn where expressions" was not that obvious to me), but it turned out you could modify incoming request objects in your handlers.

cap services and transactions

After I tried migrating my code from cds3 to cds4 some time ago, I found out that there were some breaking changes in transactions handling regarding auto commits, but i was too lazy to investigate.

This time as I had to finally deal with it, it turned out that I needed to manually release my transactions with either commit or rollback calls or otherwise my app would stuck:

The caller of srv.tx() is responsible to commit or rollback the transaction, otherwise the transaction would never be finalized and respective physical driver connections never be released / returned to pools.

So I decided to make my lib functions unaware of this aspect and made handlers in index.js handle this logic for me instead.

This way (with some exceptions) each tx is started inside corresponding handler which may call some functions who pass it down and return Promises.

But as I did not use authentication/authorisation features of cap/cf, I implemented my own logic to perform checks and add filters or even reject some requests.

And this stuff was separated into some generic protectService function to register specific before hooks to do some filtering.

So inside basicChecks functions (which is also used in my custom express handlers) it started its own transaction, because I needed to perform a db read to try and get the Profile of user based on his/her auth parameters.

And guess what - each time it was called in read after write cap logic, my whole app got stuck (just as when I did not commit/rollback my txs).

I was able to slightly adjust my check by looking at req._ object which can have an "embedded" original request (as req._.query) and compare it with req.query.

Also this way I was able to distinguish express requests from cap odata requests.

 

To make things worse, I also had some sporadic issue with Exercise create/update which did not have any additional before handler, but still would freeze my app.

It makes sense to mention that sqlite Service implementation seem to not use connection pools or anything like that.

Instead it just marks db as busy with a boolean flag or adds a Promise to the "queue" in acquire and waits until the next completed transaction would resolve it in release - which is for some reason sometimes not called, and this is what gets us stuck as far as I could comprehend it.

if (dbc._busy) await new Promise(resolve => dbc._queued.push(resolve))

 

So, I thought of replacing standard cap stuff with my own to get rid of that standard read after write behaviour.

And of course if I would continue using Application service in this "on" handler I would get an infinite loop of my own calls to itself.

So again, it required me performing the create operation via Database service create/update call (this is how I found out it returns some other stuff than Application service).

And it seemed to fix the original issue with sporadic freezes.

 

But after I did this I got another problem - each function that would try creating the Exercise with tx that was created from app srv, would also get stuck.

Changing those handler to create db srv transaction luckily solved the problem for me (I was too lazy again to debug internals of this stuff like Contexts, Root and Nested transactions etc.).

For example, in createWorkout or cloneWorkout action handlers you can see that I now have a tx=dbSrv.tx() passed down through the whole chain of functions dealing with crud for these actions.

Conclusion

You may definitely not encounter any of problems I described here while using cap.

Still even with this issues I was able to achieve my goal with following key takeaways:

• release tx via commit/rollback or using lambda for autocommit


• db service tx possible to use as well, though can return specific results


• do not mix tx from db and app services and consider read after write behaviour to avoid your app being completely stuck


• path expressions are not supported by sqlite, but views or direct db queries can solve this issue


• in your before handlers you can modify original request (eg adjust req.query cqn object with your SELECT or where predicate).


• also you can find some "interesting" info in req._ object

Of course this is not an example of how cap was designed to be used, but probably cases like this can also help cap developers see what else people can come up with.

Take care and thanks for reading this.