Skip to main content

Function Helper

A huge part of OpenSql is managed using Function Helpers, so this part is very important for developers.

Expressions​

AS(
  data,      // string
  columnName // string?
);

Example code:

AS(
  'id',
  'userId'
);

CAST​

CAST(
  data, // number | string
  type  // string
);

Example code:

{
    get: CAST('username','id')
    
}

COUNT​

COUNT(
  column // string? | string[]?
);

Example code:

{
    get: COUNT('username')
    
}

MAX​

MAX(
  column // string
);

MIN​

MIN(
  column // string
);

SUM​

SUM(
  column // string
);

AVG​

AVG(
  column // string
);

Where​

IN​

IN(
  arr,        // string[] | number[] | string&number[]
  conjunction // Cnj?
);
NOT_IN(
  arr,        // string[] | number[] | string&number[]
  conjunction // Cnj?
);

Example code:

{
  where: {
    id: IN([1, 5])
  }
}

LIKE​

LIKE(
  str, // string
  conjunction // Cnj?
);
NOT_LIKE(
  str, // string
  conjunction // Cnj?
);

Example code:

{
    where: {
        title: LIKE('%simple')
    }
}

BETWEEN​

BETWEEN(
  first,      // string | number
  second,     // string | number
  conjunction // Cnj?
)
NOT_BETWEEN(
  first,      // string | number
  second,     // string | number
  conjunction // Cnj?
)

Example code:

{
  where: {
    id: BETWEEN(1, 5)
  }
}

jsonChecker​

jsonChecker(
  key,    // string
  has     // string
)

Example code:

{
  where: {
    id: jsonChecker('c', 'name')
  }
}

Condition​

The Condition function is used along with the $having object

Condition(
  leftStatement,        // string
  rightStatement,       // string | number
  comparisonOperator,   // COP?
  conjunction           // Cnj?
);

Example code:

{
    option: {
        $having: [
            Condition('COUNT(password)', 1234)
        ]
    }
}

Date and Time​

NOW​

NOW();

DAY​

DAY(
  date // string
);

Example code:

DAY('2017-06-15');

DAYOFWEEK​

DAYOFWEEK(
  date // string
);

DAYOFYEAR​

DAYOFYEAR(
  date // string
);

DAYOFMONTH​

DAYOFMONTH(
  date // string
);

DAYNAME​

DAYNAME(
  date // string
);

Example code:

DAYNAME('2017-06-15');

UTC_DATE​

UTC_DATE();

UTC_TIME​

UTC_TIME();

CURRENT_DATE​

CURRENT_DATE();

CURRENT_TIME​

CURRENT_TIME();

UTC_TIMESTAMP​

UTC_TIMESTAMP();

Others​

UUID​

UUID();

UUID_SHORT​

UUID_SHORT();

JOIN​

LEFT​

LEFT(
  query // Query
);

INNER​

INNER(
  query // Query
);

INNER​

INNER(
  query // Query
);

FULL​

FULL(
  query // Query
);

Example code:

{
  from: 'books',
  join: [
    FULL({
        from: 'users'
    })   
  ]
}

BLOB​

BLOB​

BLOB(
  buf // Buffer
);

TINYBLOB​

TINYBLOB(
  buf // Buffer
);

LONGBLOB​

LONGBLOB(
  buf // Buffer
);

MEDIUMBLOB​

MEDIUMBLOB(
  buf // Buffer
);

JSON​

Use when you want insert data in json data type.

JSON(
  data //  number[] | string[] | JSONObject | JSONObject[]
);

Example code:

{
    data: JSON({
        username: 'root'
    })
}

UPPER​

UPPER(
  string // string
);

Example code:

{
    get: UPPER('username')
    
}

LOWER​

LOWER(
  string // string
);

Example code:

{
    get: LOWER('USERNAME')
    
}

ASCII​

ASCII(
  char // string
);

Example code:

{
    get: ASCII('A')
    
}

POINT​

POINT(
  x, // number
  y  // number
);

Example code:

{
    get: POINT(4.5, 5)
    
}

UNION​

UNION(
  query // Query
);

UNION ALL​

UNION_ALL(
  query // Query
);

Example code:

{
    from: 'users',
    union: [
        UNION({
            from: 'books'
        })
    ]
}

qCheck​

qCheck(
  value, // QCheckValueInObject | string | number
  comparisonOperator, // COP?
  conjunction // Cnj?
);

Example code:

{
    where: {
        id: qCheck(50, COP.DivEqual)
    }
}

BINARY​

BINARY(
  data // number  
);

VARBINARY​

VARBINARY(
  data // number  
);

ATTACH​

ATTACH(
  arr,        // JSONObject | string[]   
  conjunction // Cnj? 
);

Example code:

{
    where: {
        id: ATTACH([
            IN([100, 105]), 
            qCheck(50, COP.DivEqual)
        ])
    }
}

SOURCE​

SOURCE(
  name,    // string
  typeName // string?
);

Example code:

{
    get: SOURCE('username')
}

POLYGON​

POLYGON(
  str    // string
);

Example code:

{
    data: {
       p: POLYGON('0 0,10 0,10 10')
    }
}

COMMENT​

COMMENT(
  description    // string
);

Example code:

{
    column: {
       id: DataType.INT([COMMENT('This is a simple test')])
    }
}

REVERSE​

REVERSE(
  string    // string
);

DEFAULT​

DEFAULT(
  value    // string | number | boolean
);

Example code:

{
    column: {
       phoneNumber: DataType.INT([DEFAULT('NULL')])
    }
}

XML​

XML(
  data    // string
);

Example code:

{
    data: {
       id: XML('<h1>Hello World!<h1>')
    }
}

UNQUOTE​

UNQUOTE(
  extract    // string
);

Example code:

{
   get: UNQUOTE("c->'$.name'")   
}

CONTAINS​

CONTAINS(
  target,     // string | JSONObject
  candidate,  // string | JSONObject
  path        // string?
);

Example code:

{
   get: CONTAINS('{"d": 4}', '1', '$.a')   
}

EXTRACT​

EXTRACT(
  data,    // JSONObject | number[] | string
  ...path  // string[]
);

Example code:

{
   get: EXTRACT([1, 2, 3, 4, 5], '$[last-3 to last-1]')
}

CONCAT_WS​

CONCAT_WS(
  str,    // string
  arr,    // string[]
  column  // string
);

Example code:

{
   get: CONCAT_WS(' ', ['A', 'B'], 'ConcatenatedString')
}

QueryPoint​

QueryPoint(
  field    // string
);

Example code:

{
   get: QueryPoint('location')
}

LINESTRING​

LINESTRING(
  str    // string | string[]
);

Example code:

{
   get: LINESTRING([Point(0,0), Point(1,1)])
}

CHAR_LENGTH​

CHAR_LENGTH(
  data    // string | number
);

Example code:

{
   get: CHAR_LENGTH(125)
}

CURRENT_USER​

CURRENT_USER();

ST_GeomFromText​

ST_GeomFromText(
  string // string
);