Module dado.sql
Compose SQL statements.
Functions
| escape (s) | Escape a character or a character class in a string. |
| quote (s) | Quote a value to be included in an SQL statement. |
| quotedconcat (tab) | Quote all values associated with the integer keys in a table and concat them on a string, separated by a comma (,). |
| in_quote (val) | Quote with prefix to be used in AND. |
| AND (tab) | Composes simple (almost trivial) SQL AND-expressions. |
| alike (exp, value, template) | Composes a comparison expression based on the LIKE operator. |
| isinteger (id) | Checks if the argument is an integer. |
| select (columns, tabname, cond, extra) | Builds a string with a SELECT command. |
| subselect (columns, tabname, cond, extra) | Builds a string with a SELECT command to be inserted into another SQL query. |
| insert (tabname, contents) | Builds a string with an INSERT command. |
| update (tabname, contents, cond) | Builds a string with an UPDATE command. |
| delete (tabname, cond) | Builds a string with a DELETE command. |
Functions
- escape (s)
-
Escape a character or a character class in a string.
It also removes character with codes < 32 (except \t (\9), \n (\10)
and \r (\13)).
Parameters:
- s String to be processed.
Returns:
-
String or nil if no string was given.
- quote (s)
-
Quote a value to be included in an SQL statement.
The exception is when the string is surrounded by balanced "(())";
in this case it won't be quoted.
Parameters:
- s String or number or boolean.
Returns:
-
String with quoted value.
- quotedconcat (tab)
-
Quote all values associated with the integer keys in a table and
concat them on a string, separated by a comma (,).
This function is particularly useful to produce expressions for the IN
operator.
Parameters:
- tab Table with the sequence of values.
Returns:
-
String in the for of a comma separated values.
- in_quote (val)
-
Quote with prefix to be used in AND.
If the value is a table, produces a 'in (...)' expression, considering
only the integer keys starting from 1; otherwise produces a regular
'='..quote(value) expression.
Parameters:
- val String or number or boolean or table.
Returns:
-
String with the quoted value and a prefix (' in' for table
values; '=' for other values).
- AND (tab)
-
Composes simple (almost trivial) SQL AND-expressions.
There is only one single "magic" in this function: table values are
considered arrays containing the values of an IN expression.
All other values are simply quoted in the resulting string.
Hence, for expressions which have any operator other than '=' and 'IN',
you should write them explicitly.
There is no OR-expression equivalent function (I don't know how to
express it convenently in Lua).
Parameters:
- tab Table with key-value pairs representing equalities.
Returns:
-
String with the resulting expression.
- alike (exp, value, template)
-
Composes a comparison expression based on the LIKE operator.
Parameters:
- exp String with the base-expression.
- value String with the literal or pattern-expression (it will be quoted; does not accept subselect).
- template String with the template of the expression (default = dado.sql.alike_template).
Returns:
-
String with a comparison expression.
- isinteger (id)
-
Checks if the argument is an integer.
Use this function to check whether a value can be used as a
database integer key.
Parameters:
- id String with the key to check.
Returns:
-
Boolean or Number (any number can be considered as true) or nil.
- select (columns, tabname, cond, extra)
-
Builds a string with a SELECT command.
The existing arguments will be concatenated together to form the
SQL statement.
The string "select " is added as a prefix.
If the tabname is given, the string " from " is added as a prefix.
If the cond is given, the string " where " is added as a prefix.
Parameters:
- columns String with columns list.
- tabname String with table name (optional).
- cond String with where-clause (optional).
- extra String with extra SQL text (optional).
Returns:
-
String with SELECT command.
- subselect (columns, tabname, cond, extra)
-
Builds a string with a SELECT command to be inserted into another
SQL query.
Parameters:
- columns String with columns list.
- tabname String with table name.
- cond String with where-clause (and following SQL text).
- extra String with extra SQL text.
Returns:
-
String with SELECT command.
- insert (tabname, contents)
-
Builds a string with an INSERT command.
Parameters:
- tabname String with table name or with the SQL text that follows the "insert into" prefix.
- contents Table of elements to be inserted (optional).
Returns:
-
String with INSERT command.
- update (tabname, contents, cond)
-
Builds a string with an UPDATE command.
Parameters:
- tabname String with table name.
- contents Table of elements to be updated.
- cond String with where-clause (and following SQL text).
Returns:
-
String with UPDATE command.
- delete (tabname, cond)
-
Builds a string with a DELETE command.
Parameters:
- tabname String with table name.
- cond String with where-clause (and following SQL text).
Returns:
-
String with DELETE command.