ShortSql

ShortSql is a conventional encoding system that compiles to plain to plain SQL and has been conceived to be used aa a very elastic querying system, with security in mind.

The parsing library implements a detialed validation of the input data, which grants to the developer a detailed control over the request.

ShortSql is based on indipendent blocks, separated by tildes (~). Each block is identified by the first character it uses.
The order of the blocks is not important, and except for the table block, they are all optional.

Syntax

  • @table:alias
    Required
    Alias is not supported yet (04.02.2020)

  • [field:Alias,fieldn:AliasN
    Optional, Default: *.
    List of fields to fetch, separated by commas.
    Each field can be followed by an optional alias, separated by a colon (:).
    Fields can be argument of aggregative functions, such as avg, count, max, min, sum, group_concat, distinct, that must follow the the field name and alias separated by a pipe (|). Also count_distinct is supported, which is not a valid SQL function, but outputs the combination of COUNT and DISTINCT, eg. mycol|count_distinct => COUNT ( DISTINCT mycol)

  • ]tbname:Alias||onStatement
    Optional.
    Multiple.
    Join statement. Each statement if made of two parts separated by a double pipe (||). The first part is the table name to be joined optinally followed by an alias (separated by a colon :). Alias is not supported yet (04.02.2020).
    The second part is the On statement encoded as a WHERE statement (see below).

  • ?where
    Optional The different parts of the WHERE are separated by double pipes (||). The first part is made of three elements (field name, operator, reference value) separated by single pipes (|); other parts are made of four elements, having a connector as first element.
    Brackets might be used to logically group statement parts. In that case opening bracket must placed before the field name and separad by the usual pipe (|). The closing bracket must be placed after the value, pipe-separated.
    • Field names may be provided as field, field:alias, table.field or table.field:alias
    • If value starts with a caret (^), the value will not binded nor escaped by quotes as string: it is assumed to be a table field name or a numeric value.
    • If value is enclosed in curly brackets it is assumed to be a subquery. The subquery strictly follow the ShortSQL format. Subqueries will be automatically enclosed in round brackets, so you should not include them in the ShortSql text.
  • >field:order
    Optional.
    Multiple.
    ASC || DESC (case insensitive) Sorting statement, colon separated.
    • The first element is the field name.
    • The second element is the sorting direction and is optional, having as a default: ASC. ASC or DESC (case insensitive)
  • -tot:offset
    Optional Limit statement, colon separated.
    • First element is the total numer of rows to fetch. Must be a numeral
    • Second element, optional, is the offset
  • *field1,fieldn
    Optional.
    Group statement. Comma separated list of fields to use for grouping.
    Each field shoul be a valid field and may be provided as table.field or field