@lanetix/list-query-mapper

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.

Arrows vs Fibers (types of JOINs)

• these are either $expand or an expand transformation under the apply $apply=expand(...)
• Here is a description of how constraints work.

Currently supported dialects.

• Postgres
• Javascript