@lanetix/list-query-mapper

Map OData 4.0 URIs into a query for other stores

Usage no npm install needed!

<script type="module">
  import lanetixListQueryMapper from 'https://cdn.skypack.dev/@lanetix/list-query-mapper';
</script>

README

Circle CI codecov js-standard-style

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:

Parallel Paths through the mapper

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.

Arrows vs Fibers (types of JOINs)

Currently supported dialects.