sql

↓ Methods.

module:sql

Description:

  • SQL generation helpers, very simple functionality

Source:

Methods

(static) column(req, name) → {string}

Description:

  • Return properly quoted column name if it is a keyword

Source:
Parameters:
Name Type Description
req DbRequest

current request
name string

column name
Returns:
Type Description
string

(static) create(req) → {string}

Description:

  • Create SQL table using table definition

Source:
Parameters:
Name Type Description
req DbRequest

request object
Returns:
Type Description
string

(static) delete(req) → {string}

Description:

  • Build SQL statement for delete

Source:
Parameters:
Name Type Description
req DbRequest

request object
Returns:
Type Description
string

(static) drop(req) → {string}

Description:

  • Create SQL DROP TABLE statement

Source:
Parameters:
Name Type Description
req DbRequest

request object
Returns:
Type Description
string

(static) expr(req, name, value, columnopt) → {string}

Description:

  • Build SQL expression for the column and value to be used in WHERE, req.values will be updated with actual values for each placeholder

Source:
Parameters:
Name Type Attributes Description
req DbRequest

current request
name string

column name
value any

value to compare
column DbRequestColumn <optional>

column definition returned by module.db:prepareColumn
Returns:
Type Description
string

SQL comparison expression

(static) get(req) → {string}

Description:

  • Get one record from the database

Source:
Parameters:
Name Type Description
req DbRequest

request object

Properties
Name Type Attributes Description
options.select Array.<string> <optional>

is a list of columns or expressions to return
Returns:
Type Description
string

(static) insert(req) → {string}

Description:

  • Build SQL insert statement

Source:
Parameters:
Name Type Description
req DbRequest

request object
Returns:
Type Description
string

(static) limit(req) → {string}

Description:

  • Build SQL orderby/limit/offset conditions, config can define defaults for sorting and paging

Source:
Parameters:
Name Type Description
req DbRequest

current request
Returns:
Type Description
string

(static) quote(value) → {string}

Description:

  • Quote value to be used in SQL expressions

Source:
Parameters:
Name Type Description
value any
Returns:
Type Description
string

(static) select(req) → {string}

Description:

  • Select object from the database

Source:
Parameters:
Name Type Description
req DbRequest

request object

Properties
Name Type Attributes Description
options.select Array.<string> <optional>

is list of columns or expressions to return
Returns:
Type Description
string

(static) time() → {string}

Description:

  • Return time formatted for SQL usage as ISO, if no date specified returns current time

Source:
Returns:
Type Description
string

(static) update(req) → {string}

Description:

  • Build SQL statement for update

Source:
Parameters:
Name Type Description
req DbRequest

request object
Returns:
Type Description
string

(static) updateExpr(req, query) → {Array.<object>}

Description:

  • Build SQL expressions for the column and value to be used in UPDATE, req.values will be updated with actual value for each placeholder, primary keys are skipped

Source:
Parameters:
Name Type Description
req DbRequest

current request
query object

query object
Returns:
Type Description
Array.<object>

{ text, name, column, op, placeholder, value }

(static) upgrade(req) → {string}

Description:

  • Create ALTER TABLE ADD COLUMN statements for missing columns

Source:
Parameters:
Name Type Description
req DbRequest

request object
Returns:
Type Description
string

(static) value(value, optionsopt) → {string}

Description:

  • Return properly quoted value to be used directly in SQL expressions, format according to the type

Source:
Parameters:
Name Type Attributes Description
value any

column value to format
options string | DbRequestColumn <optional>

type or options
Returns:
Type Description
string

(static) valueIn(list, typeopt) → {string}

Description:

  • Return list in format to be used with SQL IN ()

Source:
Parameters:
Name Type Attributes Description
list any | Array.<any>

items to compare with
type string <optional>

optional data type
Returns:
Type Description
string

(static) where(req, query) → {string}

Description:

  • Build SQL where condition from the keys and object values, returns SQL statement to be used in WHERE

Source:
Parameters:
Name Type Description
req DbRequest

current request
query object

properties for the condition, in case of an array the primary keys for IN condition will be used only,

  • A property named $or or $and will be treated as a sub-expression if it is an object.
  • A property named $join will produce JOIN expression, joined table name will be inferred fron the table.column properties/values
  • If need multiple OR/AND/JOIN conditions use more $ signs, like $$or, $$$or, $$join
  • JOIN syntax:
    1. column op table.column - current table column with other table column
    2. column op value - current table column compare to a value
    3. table.column op value - other table column compare to a value
Returns:
Type Description
string

(static) where(req, list) → {string}

Description:

  • Build SQL where condition for a list of objects

Source:
Parameters:
Name Type Description
req DbRequest

current request
list Array.<object>

list of objects with properties
Returns:
Type Description
string