nv-pg-ir v1.0.16

nv-pg-ir

• generate some pgsql from js-syntax

install

• npm install nv-pg-ir

usage

    const pgir = require("nv-pg-ir");

where

    var cd = `
        ( 
           (a>1 && b<=20)           ||
           includes(text(c),'xyz')  
        ) &&
        (
           d.e[1] === [10,20,30]
        ) 
    `

    > pgir.where.to_pg_where(cd)
     
        WHERE (
          ((((a > 1) AND (b <= 20)) OR (strpos(c::text,'xyz')>0)) AND ((d->'e'->1)::jsonb = '[10,20,30]'))
        ) 
  
  > pgir.to_pg_where(`contains(ary(1,2,3,4,5,6),id)`)
      'WHERE (id=ANY(ARRAY[1,2,3,4,5,6]))'
  > 

    var alias_mp = {
        a: 'low',
        b: 'high',
        c: 'name',
        d: 'data' 
    }


    > pgir.where.to_pg_cond(cd,alias_mp)
    "((((low > 1) AND (high <= 20)) OR (strpos(name::text,'xyz')>0)) AND ((data->'e'->1)::jsonb = '[10,20,30]'))"
    >

  

  > pgir.to_pg_where(`id===1 && c1.name===jstr("topic1")`)
       `WHERE (((id = 1) AND ((c1->'name')::jsonb = '"topic1"'::jsonb)))`

   > pgir.to_pg_where(`id===1 && integer(c1.a.b) === 100`)
       "WHERE (((id = 1) AND ((c1->'a'->'b')::jsonb::integer = 100)))"

  # single quote 
  > pgir.to_pg_where(` a.b.c === [1,2,"x'x",{a:"y'y"},[[["'"]]]]`)
     `WHERE (((a->'b'->'c')::jsonb = '[1,2,"x''x",{"a":"y''y"},[[["''"]]]]'))`


    jsonpg=# select '[1,2,"x''x",{"a":"y''y"},[[["''"]]]]'::jsonb->2;
     ?column?
    ----------
     "x'x"
    (1 row)

supported binop:

    > , >= , === ,!== , <= , < , || , &&  

supported unaop:
    
    !    

supported cast:
 
     'String'  
     'text'    
     'jsonb'  
     'jstr' 
     'bool'    
     'integer' 
     'bigint'  
     'float4'  
     'float8'       

 supported func, currently only 4:
 
    includes                         //string  
    contains(ary(...),right)         //array
    any 
    ary

 supported js-data-type:
     number,bigint
     string, 
     boolean,
     null,undefined,
     json

 supported js-ast-nodes (for safe validate):

    Identifier

    NullLiteral
    BooleanLiteral 
    StringLiteral
    NumericLiteral 
    BigIntLiteral

    ObjectExpression
    ArrayExpression
    ObjectProperty


    UnaryExpression     !
    LogicalExpression   ||   && 
    BinaryExpression    === !== > >= < <=

    CallExpression
         callee MUST BE Identifier (for filter unsafe such as fs.)
         callee.name    includes text jsonb bool integer bigint float4 float8 String 
         
    MemberExpression 
          

jsonb

    > pgir.to_get_plstr(`extra["b"].c.d[0]`)
    "(extra->'b'->'c'->'d'->0)::jsonb"
    >
    >

    /*
            jsonpg=# select extra from expr;
                                  extra
            -------------------------------------------------
             {"b": {"c": {"d": [[10, 20, 30], "yy", "zz"]}}}
             {"b": {"c": {"d": [[10, 20, 30], "yy", "zz"]}}}
             {"b": {"c": {"d": [[10, 20, 30], "yy", "zz"]}}}
            (3 rows)

            jsonpg=# select (extra->'b'->'c'->'d'->0)::jsonb from expr;
                jsonb
            --------------
             [10, 20, 30]
             [10, 20, 30]
             [10, 20, 30]
            (3 rows)

            jsonpg=#

    */


    > var cd = `extra["b"].c.d[0]={a:100,"b":[1,true,false,null,'str'],["c"]:3333,5555:5555}`;
    >
    > pgir.to_set_pgcmd(cd)
      jsonb_set("extra"::jsonb,('{' || 'b' || ',' || 'c' || ',' || 'd' || ',' || 0 || '}')::text[],'{"5555":5555,"a":100,"b":[1,true,false,null,"str"],"c":3333}')
    >


    /*
            jsonpg=# select extra from expr;
                                                                  extra
            -----------------------------------------------------------------------------------------------------------------
             {"b": {"c": {"d": {"0": [10, 20, 30], "a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}}}}
             {"b": {"c": {"d": {"0": [10, 20, 30], "a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}}}}
             {"b": {"c": {"d": {"0": [10, 20, 30], "a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}}}}
            (3 rows)

            jsonpg=#
            jsonpg=# update expr set extra=jsonb_set("extra"::jsonb,('{' || 'b' || ',' || 'c' || ',' || 'd' || ',' || 0 || '}')::text[],'{"5555":5555,"a":100,"b":[1,true,false,null,"str"],"c":3333}');
            UPDATE 3
            jsonpg=# select extra from expr;
                                                                                               extra
            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
             {"b": {"c": {"d": {"0": {"a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}, "a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}}}}
             {"b": {"c": {"d": {"0": {"a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}, "a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}}}}
             {"b": {"c": {"d": {"0": {"a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}, "a": 100, "b": [1, true, false, null, "str"], "c": 3333, "5555": 5555}}}}
            (3 rows)

            jsonpg=#

    */

APIS

where

    {
      _is_safe_nd: [Function: _is_safe_nd],
      is_safe_code: [Function: is_safe_code],
      _to_pg_where: [Function: _to_pg_where],
      to_pg_where: [Function: to_pg_where]
    }

jsonb

    {
      _mem_expr_nd_to_plstr: [Function: _mem_expr_nd_to_plstr],
      to_get_plstr: [Function: to_get_plstr],
      _to_set_desc: [Function: _to_set_desc],
      to_set_pgcmd_with_jval: [Function: to_set_pgcmd_with_jval],
      to_set_pgcmd: [Function: to_set_pgcmd]
    }

ir

    {
      BUILTIN_METHOD_IRS: { 
          includes: [Function: str.includes] ,
          any: [Function: any],
          ary: [Function: ary],
          contains: [Function: ary.contains]
      },
      CAST_IRS: {
        String: [Function: String],
        text: [Function: text],
        jsonb: [Function: jsonb],
        jstr: [Function: jstr],
        bool: [Function: bool],
        integer: [Function: integer],
        bigint: [Function: bigint],
        float4: [Function: float4],
        float8: [Function: float8]
      },
      EXPR_IRS: {
        '!': [Function: !],
        '&&': [Function: &&],
        '||': [Function: ||],
        '===': [Function: ===],
        '!==': [Function: !==],
        '>': [Function: >],
        '>=': [Function: >=],
        '<': [Function: <],
        '<=': [Function: <=]
      },
      PRIMJ_ND_IRS: {
        num_lit: [Function: num_lit],
        str_lit: [Function: str_lit],
        bl_lit: [Function: bl_lit],
        nul_lit: [Function: nul_lit]
      }
    }

for more irs, refer to nv-pg-fetch'str nv-pg-fetch'type

LICENSE

• ISC