Annotated links: Episode 4 of Hands-on SAP dev with qmacro

This is a searchable description of the content of a live stream recording, specifically “Episode 4 – Debugging CAPM and a look at the cds REPL” in the “Hands-on SAP dev with qmacro” series. There are links directly to specific highlights in the video recording. For links to annotations of other episodes, please see the “Catch the replays” section of the series blog post.

This episode, titled “Debugging CAPM and a look at the cds REPL“, was streamed live on Wed 13 Feb 2019 and is approximately one hour in length. The stream recording is available on YouTube.

Below is a brief synopsis, and links to specific highlights – use these links to jump directly to particular places of interest in the recording, based on ‘hh:mm:ss’ style timestamps.

Brief synopsis

We take a look at how we can use VS Code debugging facilities to debug CAPM services, and also start to explore the cds command line tool’s REPL (Read Evaluate Print Loop) environment.

Links to specific highlights

00:01:15: Answering Pierre Dominique’s question about Wednesday streams. Basically Friday is the regular slot that I run every week, always at the same time – 0800 UTC+0 (Manchester). I run an occasional midweek slot, and that will be on Wednesdays when it does happen. See the schedule section of the main live stream blog post for details, including start times around the world.

00:03:38: Looking at the blog post #CloudFoundryFun – Upgrade Cloud Foundry With A New REPL Feature by Marius Obert, where he says “a REPL makes your application runtime more tangible”.

00:06:30: Looking at where we are in the tutorial, and opening up (in VS Code) a copy of the project at where we’d got to, in the bookshop/ directory.

00:07:30: Looking at the SQLite database using the Database Explorer extension.

00:08:15: Executing cds help repl to get a quick overview of what the REPL is and does for us. Noting also that the overview points to the regular Node.js REPL documentation … because that is what the CDS REPL is based on … a Node.js REPL with extra magic.

00:09:34: Starting a Node.js REPL and having a little play, noting that there’s command line history built in. We look in ~/.node_repl_history.

00:11:00: Looking inside the node_modules/ directory, particularly in the bin/ subdirectory, and we find the repl.js script which represents the CDS REPL, and as expected it’s just using the Node.js REPL. Lovely!

00:13:04: Starting up a CDS REPL with cds repl, and looking at the References section of the CAP documentation on the SAP Help Portal which gives us some insight into the JavaScript API.

00:14:25: Starting up tmux to manage our workspace better.

00:15:50: Instantiating a reference to the CDS package which also represents a connection, with

const cds = require('@sap/cds').connect()

Note that the CDS REPL history is in ~/.cds_repl_history as opposed to ~/.node_repl_history.

00:16:52: Exploring what we have in the cds constant, using tmux‘s pane scroll feature (Ctrl-A [) to scroll up.

00:20:05: Examining a more detailed version of the call, specifying a value ‘db’ for the connect() call, and seeing that it refers to a section in the cds stanza inside package.json.

00:21:15: Pierre Dominique reminds us that we can use some of the builtin REPL commands, for example to disconnect. Using .help to see what they are, we get:

.break    Sometimes you get stuck, this gets you out
.clear    Alias for .break
.editor   Enter editor mode
.exit     Exit the repl
.help     Print this help message
.load     Load JS from a file into the REPL session
.save     Save all evaluated commands in this REPL session to a file

00:21:50: Seeing that we can use command completion on the cds constant, and looking at cds.env, which comes from package.json.

00:22:15: Because we’re connected to the database we can use cds.entities to look at the entities in there.The output is in canonical form (CSN), and we look at the keys of the object returned with Object.keys(cds.entities) to get ['Books', 'Authors', 'Orders']. We dig in further by looking at one of the entities with cds.entities.Authors.

00:23:15: We can do more, such as cds.entities.Authors.keys and cds.entities.Authors.elements to inspect the keys and elements (respectively) of the Authors entity.

00:24:13: Looking at the documentation for cds.ql in the SAP Help Portal to see that it provides fluent API functions for writing queries in JavaScript, including INSERT.into, UPDATE, DELETE.from and SELECT.from.

00:27:30: Starting to play around with the fluent API, using command completion to help us. We try: cds.ql.SELECT, or actually (as SELECT is available globally):

SELECT.from('Books')

(which is not SQL but JavaScript, remember!) which returns:

{ SELECT: { from: {ref:['Books']} } }

which is in the canonical representation of CDS queries known as Core Query Notation (CQN), the query equivalent of Core Schema Notation (CSN).

00:30:00: Looking at loading and compiling definitions programatically, like this:

cds.load('db').then('cds.compile.to.sql')

which produces something like this:

[ 'CREATE TABLE my_bookshop_Authors (\n  ID INTEGER,\n  name NVARCHAR(5000),\n  PRIMARY KEY(ID)\n)',
  'CREATE TABLE my_bookshop_Books (\n  ID INTEGER,\n  title NVARCHAR(5000),\n  stock INTEGER,\n  author_ID INTEGER,\n  PRIMARY KEY(ID)\n)',
  'CREATE TABLE my_bookshop_Orders (\n  modifiedAt SECONDDATE,\n  createdAt SECONDDATE,\n  createdBy NVARCHAR(255),\n  modifiedBy NVARCHAR(255),\n  ID NVARCHAR(36),\n  amount INTEGER,\n  book_ID INTEGER,\n  country_code NVARCHAR(3),\n  PRIMARY KEY(ID)\n)',
  'CREATE TABLE sap_common_Countries (\n  name NVARCHAR(255),\n  descr NVARCHAR(1000),\n  code NVARCHAR(3),\n  PRIMARY KEY(code)\n)' ]

Note the use of promises here!

00:30:55: Getting back to { SELECT: { from: {ref:['Books']} } }, we continue our journey, by feeding that in to the cds.run function:

cds.run(SELECT.from('Books'))

Note however that we actually have to specify the full name of the table, i.e.

cds.run(SELECT.from('my.bookshop.Books'))

and this produces:

[ { ID: 201, title: 'Wuthering Heights', stock: 3, author_ID: 101 },
  { ID: 207, title: 'Jane Eyre', stock: 11, author_ID: 107 },
  { ID: 251, title: 'The Raven', stock: 333, author_ID: 150 },
  { ID: 252, title: 'Eleonora', stock: 555, author_ID: 150 },
  { ID: 271, title: 'Catweazle', stock: 22, author_ID: 170 } ]

Excellent!

00:32:05: We see that we don’t actually have to know the ‘my.bookshop’ prefix for ‘Books’ … we can do this:

b = cds.entities.Books
cds.run(SELECT.from(b))

and get the same result (the five records) … as b is now a representation of the Books entityset. (We see that the value of b.name is ‘my.bookshop.Books’ too).

00:33:45: Trying an insert. First we build up the CQN object (the output from each line is shown in subsequent comment lines):

add_dna = INSERT.into(cds.entities.Authors)
// -> { INSERT: { into: 'my.bookshop.Authors' } }

add_dna.columns('ID', 'name')
// -> { INSERT: { into: 'my.bookshop.Authors', columns: ['ID', 'name'] } }

add_dna.columns('ID', 'name').values(42, 'Douglas Adams')

This final evaluation gives us:

{ INSERT:
  { into: 'my.bookshop.Authors',
    columns: [ 'ID', 'name' ],
    values: [ 42, 'Douglas Adams' ] } }

00:37:10: Running the query gives us some result:

cds.run(add_dna)
// -> 1

Running a query on the Authors entity (cds.run(SELECT.from(cds.entities.Authors))) shows that the insert was successful:

[ { ID: 42, name: 'Douglas Adams' },
  { ID: 101, name: 'Emily Brontë' },
  { ID: 107, name: 'Charlote Brontë' },
  { ID: 150, name: 'Edgar Allen Poe' },
  { ID: 170, name: 'Richard Carpenter' } ]

We can also see the new record with the OData query: http://localhost:4004/catalog/Authors.

00:39:00: Moving to VS Code now for a bit of debugging.

00:40:51: Looking at the .vscode/launch.json configuration file which has some debugging settings, not CDS specific or CAP specific but general to VS Code. By the way, this is the newer version of the “Bookshop” sample project that Christian was using to demonstrate features to us in the previous episode.

00:43:23: Using npm run setup to install the Node modules and perform the deployment, as in package.json the ‘setup’ script is defined to be:

npm i && cds deploy -2 sqlite:bookshop.db

00:45:00: With ‘convention over configuration’, we have a file cat-service.js, and this means that the runtime will use the code in cat-service.js to extend the default implementation of the CRUD+Q service defined in cat-service.cds. We spend some time exploring what the different handlers are, and how to inject logic into the flow, with service provider methods like .before and .after.

00:49:25: A brief reminder of the naming convetions in the Best Practices section of the documentation on SAP Help Portal.

00:51:28: We dig into the signature of the .after method (“service.after (event, entity?, handler) : this”), noting that the single parameter each is a special case, a convenient shortcut for a per-row handler. We insert a console.log statement to see what the values are when we run it.

00:53:35: Adding a breakpoint to the console.log line, we can see that execution pauses for us to have a look around and even modify things.

00:54:47: Modifying the srv.after call to use the req pattern that makes available the inbound request:

srv.after ('READ', 'Books', (books, req) => {
  ...
})

Now in this approach we can get access, in the debug console, to all the entries that have been retrieved (in books), and also look at the actual request (in req). This is just the start, we’re just scratching the surface!