@lanetix/list-query-mapper NPM

node-lanetix-list-query-mapper

An adapter that takes an OData URI for querying our stores and maps the payload to a contract.

Operations

Requirements

npm auth token

npm login (From any directory)
Use your own npmjs login
cd ~/ open .npmrc in your favorite text editor
copy the token that appears after //registry.npmjs.org/:_authToken= export NPM_TOKEN=<paste token here>
You can also write this line to your .bashrc, .bash_profile, .zshrc, etc...
npm install

Installation

npm install -S @lanetix/list-query-mapper

Run Tests

npm install

npm run test

Usage

Odata --> pg results

// PG version
import createMapper from '@lanetix/list-query-mapper'
import getType from 'promiseToReturnTypeInformation'
import get from 'thingThatExecutesPgQuery'
import getOrganizationConfig from 'records/src/lib/get-organization-config'

export default function getListView: (uri, ...options) {
  const mapper = createMapper('pg')
  const context = {
    organization_config: getOrganizationConfig(settings.organization_id),
    options.schema_name,
    recordTypeBuilder: getType,
    options.recordType,
    options.typeInformation,
    user  // user object from the route, not the SQL transaction settings
  }

  return mapper.mapFromOdataUri(uri, context)
    .then(get)
    .then(mapper.mapToOdataPayload)
}

Odata --> pg fragment (using `$apply`)

// PG version
import createMapper from '@lanetix/list-query-mapper'
import getType from 'promiseToReturnTypeInformation'
import get from 'thingThatExecutesPgQuery'
import getOrganizationConfig from 'records/src/lib/get-organization-config'
import should from 'should'

const uri = "$apply=compute(concat( name, 'e') as elephant)"

export default function (options) {
  const mapper = createMapper('pg')
  const context = {
    organization_config: getOrganizationConfig(settings.organization_id),
    options.schema_name,
    recordTypeBuilder: getType,
    options.recordType,
    options.typeInformation,
    user  // user object from the route, not the SQL transaction settings
  }

  const { values, apply: { fragments }} = mapper.mapFromOdataUri(uri, context)
  should(values).deepEqual(['e'])
  const expected = [{
    fragment: '(CONCAT(name, $1::text))',
    alias: 'elephant',
    type: 'string'
  }]
  should(fragments).deepEqual(expected)
}

3 Interfaces: uri -> SQL, ast -> SQL, SQL -> pgResults

mapFromOdataUri

// uri -> SQL
const sqlFromUri = mapper.mapFromOdataUri(uri, context)
const { query, values } = sqlFromUri

mapFromOdataAst

// ast -> SQL
import parser from '@lanetix/odata-parser'
import astTransformer from '@lanetix/odata-ast-transformations'

let ast
try {
  ast = parser.parse(uri)
} catch (e) {
  throw new Error(e)
}

// optional: do stuff to your ast
const transformedAst = astTransformer.intersectFilters(
  ast, ["$expand=favorite($select=id;$filter=id eq 123)","$filter=name ne 'harry'"]
)

// convert into SQL
const sqlFromAst = mapper.mapFromOdataAst(transformedAst, context)
const { query, values } = sqlFromAst

mapToOdataPayload

import get from 'thingThatExecutesPgQuery'

const { rows } = mapper.mapFromOdataUri(uri, context)
  .then(get)
  .then(mapper.mapToOdataPayload)

REPL

npm run repl

Commands in this module are of the format: methodName(ast, context).

The repl may be used by either: 1. Set the ast, set the context, then call the withAst.methodName(). 2. Set the uri, set the context, then call the withUri.methodName().

In order to set the ast, you may use 2 approaches:

setAstJSON({"tag":"yes"}) // must be a minified JSON format
setAstFILE('./test-ast.json')

In order to set the uri, you may use 2 approaches:

setUriTEXT("$select=id,lanetix/archived,lanetix/id&$filter=name eq 'Antwan'")
setUriFILE('./test-uri.json')

In order to set the context, you may use 2 approaches:

setContextFILE('./test/data/foo-context.json') // common sense way
setContextJSON({...crazy huge context file...}) // lunatic way

Result: mapper output is a query (SQL) and the values.

Here is an example, with I/O shown:

Denises-MacBook-Pro:node-lanetix-list-query-mapper$ npm run repl

> @lanetix/list-query-mapper@13.3.0 repl /Users/denise/env/dev/node-lanetix-list-query-mapper
> babel-node repl.js

Welcome to the Lanetix odata-ast-transformations REPL.
Please refer to the README.md for the appropriate usage.

> setContextFILE('./test/data/foo-context.json')
Context is set.

> setUriTEXT("$select=id,trash,bass,fess,nullable_string,name,lanetix/archived,lanetix/id&$filter=name eq 'Antwan'")
Set URI:
 $select=id,trash,bass,fess,nullable_string,name,lanetix/archived,lanetix/id&$filter=name eq 'Antwan'

> withUri.mapFromOdataUri()

> SQL:
SELECT id, trash, bass, fess, nullable_string, name, json_build_object('archived', (lanetix).archived, 'id', (lanetix).id)::jsonb as lanetix
                   FROM records_2731111."view.foo"  WHERE (lower(name) COLLATE "C") = ($1::text)
VALUES:
["antwan"]
> q
Denises-MacBook-Pro:node-lanetix-list-query-mapper$

Concept

Takes a URI that follows OData 4.0 URI Specification and returns data from PG.
Converts the Abstract Syntax Tree (AST) to the final form will require using visitors and mapping.

File Structure

src/mappers entry point into the mappers.
src/lib/ the mapper files for each SQL dialect.

OData Structure

lx has not implemented all of the odata spec. Here is a summary of what is implemented.
GET recordType?$queryOption&$queryOption
query options:
- $search = search feature. odata node type 'functioncall' with func 'substringof'. creates pg sql for LIKE.
- $select = a list of fieldNames
```
$select=name,close_date,chance_to_win,contract_length_years
```
- $apply = for set transformations. Also has the compute transformation (for any commonExpn).
```
recordType?$apply=concat(set_1, set_2)
recordType?$apply=compute(commonExpn as aliasName)
recordType?$apply=compute(concat(name, "is a frog") as aliasName)
```
  - this query option can not be used in combination with any other query option.
- $filter = boolean expressions, connected by and|or.
  - currently we support:
    - math operators add|sub|mul
    - functions listed in src/lib/pg/operators/functioncall
    - comparison operators eq|ne|lt|gt|le|ge
    - conditional operators and|or
  - limitations:
    - no operator precedence. use parans instead. (pegjs)
    - no mod. no div. (divide-by-zero issue)
    - the use of and|or is limited (pegjs, operator precedence). Instead use either:
```
( exp AND exp AND exp) OR ( exp AND exp AND exp)
( exp OR exp OR exp) AND ( exp OR exp OR exp)
```
- $expand = related fields. Will then include it's $select.
```
&$expand=project_account_id($select=lanetix/id;$expand=owner($select=lanetix/id,first_name))
```
- $orderby = orderby. Takes list of properties and asc|desc.
```
&$orderby=name asc
```
- $count
- $skip = offset. Takes integer. Used in pagination.
- $top = limit. Takes integer. Used in pagination.
```
&$top=20
```
use of parans and whitespace:
- mapper doesn't care. this is the parser.
- we have some weirdness not yet fixed.
- for example odata, see: https://github.com/lanetix/node-odata-parser/blob/master/test/parser.specs.js

Parallel Paths through the mapper

There are 2 parallel paths through the mapper. $select&$filter&expand versus $apply
$select&$filter&expand more closely resembles the syntax of SQL, with a $select for the projections SELECT <column>, a $filter for the WHERE <predicate>, an $expand for the JOIN <relation>, and other query options (e.g. $top for the LIMIT <int>). OASIS spec: http://docs.oasis-open.org/odata/odata/v4.0/os/part2-url-conventions/odata-v4.0-os-part2-url-conventions.html
The $apply is syntactically describing the set transformations to occur. Cannot be combined with other query options. (But it has it's only transformations for filter, expand, and aggregate.) OASIS spec: http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/cs02/odata-data-aggregation-ext-v4.0-cs02.html
Only the $apply has groupby and aggregate functions explicitly in the abnf.
NOTE: the mapper code which handles predicates (e.g. for the $filter or the $apply=filter()) is the same for each path.
Here is the README for the $apply.

Differences from OData specs

We use the parameter-alias as a Lanetix lx-parameter-alias. Uses similar syntax for the identifier (@lx_myTeam), but the user cannot provide an expression which the identifier is equal to (unlike the OData spec).
```
$filter=nullable_integer eq @lx_myUser_Id and nullable_integer eq @lx_myTeam and
nullable_integer eq @lx_myOrg_Id and nullable_string eq @lx_myUser_Timezone
```

We use the route (api access) url a bit different from the spec.

// per odata spec
// http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html
http://localhost/Products?$orderby=Name

// Lx
http://localhost/v1/insights/o?recordType=products&odata=$orderby=name asc

The odata spec has an implied SELECT * if no $select query option is provided. vs. Lanetix requires a $select query option. Because returning all relation attributes is too large. In aggregations, we also require an explicit select, by use of a final compute transformation. $apply=transformation1()/transformation2()/compute(path as alias1, path as alias2)
When referring to related fields (related/related/field), for both the $select&$filter&expand and the $apply, Lanetix requires that an expand be explicitly stated. The OASIS spec is unclear (for all circumstances) as to whether or not this is a typically requirement, but Lx definitely requires it.
The $apply SPEC also includes other deviations. See here.