Skip to content

Latest commit

 

History

History
320 lines (252 loc) · 7.62 KB

cqn.md

File metadata and controls

320 lines (252 loc) · 7.62 KB
Raw
shorty synopsis status uacp
Query Notation
Specification of the Core Query Notation (CQN) format that is used to capture queries as plain JavaScript objects.
released

Query Notation (CQN)

CQN is a canonical plain object representation of CDS queries. Such query objects can be obtained by parsing CQL, by using the query builder APIs, or by simply constructing respective objects directly in your code.

Examples

The following three snippets all construct the same query object:

// Parsing CQL
let query = cds.parse.cql (`SELECT from Foo`)
// Query building
let query = SELECT.from('Foo')
// Constructing CQN objects in your code
let query = {SELECT:{from:[{ref:['Foo']}]}}

That object can be executed with cds.run:

cds.run (query)

::: warning ❗ Warning Because of SQL injection, it's strongly discouraged to use cds.parse.cql in your request handlers. :::

Content

[[toc]]

SELECT

A fully equipped SELECT query is represented as an object following this template (all properties except from are optional):

SELECT = {SELECT:{
  distinct: true,
  from: source | join,
  mixin: { ...element },
  columns: projection,
  excluding: [ ...string ],
  where: _xpr,
  groupBy: [ ...expr ],
  having: _xpr,
  orderBy: [ ...ordering_term ],
  limit: { rows:expr, offset:expr },
  forUpdate: { wait: number },
  forShareLock: { wait: number },
  search: _xpr,
  count: Boolean
}}
Property Description
from a primary source or joined sources
mixin a dictionary of several CSN element definitions
columns an array of column expressions
excluding an array of names
where a predicate expression
groupBy an array of expressions
having a predicate expression
orderBy an array of ordering terms
limit a dictionary of two expressions: rows and offset
search a predicate expression
count a Boolean 
source         =  ( ref | SELECT ) + { as:string }
join           =  { join:string, args:[...source], on:_xpr }
projection     =  [ ...column_expr ]
column_expr    =  expr + { as:string, cast:def, (expand|inline):projection }
ordering_term  =  expr + { sort: 'asc'|'desc', nulls: 'first'|'last' }

Sources are references or subqueries with an optional: { #sources}

  • as – a string specifying a chosen source alias

Joins combine two sources with these properties: { #joins}

  • join is one of 'left', 'right', 'full', 'inner', or 'cross'
  • args is an array of two sources or joins
  • on is a predicate expression capturing the JOIN condition

Column Expressions are a plain string '*', or expressions with these optional additional properties: { #columns}

Ordering Terms are expressions, usually references, with one or none of... { #ordering-terms}

  • sort = 'asc' | 'desc'
  • nulls = 'first' | 'last'

Example

For example, the following query in CQL:

SELECT from samples.bookshop.Books {
  title, author.name as author,
  1 as one,
  x+2 as two : Integer,
} excluding {
  dummy
}
WHERE ID=111
GROUP BY x.y
HAVING x.y<9
ORDER BY title asc
LIMIT 11 OFFSET 22

is represented in CQN as:

CQN = {SELECT:{
  from: {ref:["samples.bookshop.Books"]},
  columns: [
    {ref:["title"]},
    {ref:["author","name"], as: "author"},
    {val:1, as: "one"},
    {xpr:[{ref:['x']}, '+', {val:2}], as: "two",
      cast: {type:"cds.Integer"}
    }
  ],
  excluding: [
    "dummy"
  ],
  where: [{ref:["ID"]}, "=", {val: 111}],
  groupBy: [{ref:["x","y"]}],
  having: [{ref:["x","y"]}, "<", {val: 9}],
  orderBy: [{ref:["title"], sort:'asc' }],
  limit: {rows:{val:11}, offset:{val:22}}
}}

UPSERT

UPSERT = {UPSERT:{
   into: (ref + { as:string }) | string,
   entries: [ ...{ ...column:any } ],
   as: SELECT
}}

INSERT

INSERT = {INSERT:{
   into: (ref + { as:string }) | string,
   columns: [ ...string ],
   values: [ ...any ],
   rows: [ ...[ ...any ] ],
   entries: [ ...{ ...column:any } ],
   as: SELECT
}}

Either and only one of the properties values or rows or entries is expected to be specified. Each of which is expected to have one or more entries:

  • values is an array of values, which positionally match to specified columns.
  • rows is an array of one or more values.
  • entries is an array of records with name-value pairs.

Examples:

CQN = {INSERT:{
  into: { ref: ['Books'] },
  columns: [ 'ID', 'title', 'author_id', 'stock' ],
  values: [ 201, 'Wuthering Heights', 101, 12 ]
}}
CQN = {INSERT:{
  into: { ref: ['Books'] },
  columns: [ 'ID', 'title', 'author_id', 'stock' ],
  rows: [
    [ 201, 'Wuthering Heights', 101, 12 ],
    [ 251, 'The Raven', 150, 333 ],
    [ 252, 'Eleonora', 150, 234 ]
  ]
}}
CQN = {INSERT:{
  into: { ref: ['Books'], as: 'NewBooks' },
  entries: [
    { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
    { ID:251, title:'The Raven', author_id:150, stock:333 },
    { ID:271, title:'Catweazle', author_id:170, stock:222 }
  ]
}}

The last one also allows to express so-called 'deep inserts'. Let's assume we want to store an author with two books:

CQN = {INSERT:{ into: { ref: ['Authors'] }, entries: [
  { ID:150, name:'Edgar Allen Poe', books:[
    { ID:251, title:'The Raven' },
    { ID:252, title:'Eleonora' }
  ] }
]}}

Instead of inserting new entries for books we might want to just add relationships to already existing books, in that case just specify one or more primary key values of the target instance.

CQN = {INSERT:{ into: { ref: ['Authors'] }, entries: [
  { ID:150, name:'Edgar Allen Poe', books:[
    251, 252,
  ] }
]}}

UPDATE

UPDATE = {UPDATE:{
   entity: ref + { as:string },
   data: { ...column:any },
   where: _xpr
}}

DELETE

DELETE = {DELETE:{
   from: ref + { as:string },
   where: _xpr
}}

CREATE

CREATE = {CREATE:{
   entity: entity | string,
   as: SELECT
}}

DROP

DROP = {DROP:{
   table: ref,
   view: ref,
   entity: ref
}}

Examples:

CQN = {DROP:{
  table: { ref: ['Books'] }
}}
CQN = {DROP:{
  view: { ref: ['Books'] }
}}
CQN = {DROP:{
  entity: { ref: ['Books'] }
}}