masell.flabbergast.flabbergast-stdlib.0.19.source-code.sql.o_0 Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of flabbergast-stdlib Show documentation
Show all versions of flabbergast-stdlib Show documentation
The Standard Library for the Flabbergast Configuration Language on the JVM.
The newest version!
Introduction{{{Support for interface with SQL relational databases}}}
utils_lib : From lib:utils
{{{Query an SQL database and produce a frame containing a frame for each row.
The names of the columns, which must be valid Flabbergast identifiers, will be
the attribute names in each “row” frame. The special column name
\Mono{ATTRNAME} will become the attribute name of the “row” frame in the
containing frame, otherwise, the “row” frames will be numbered. A column name
can be prefixed by \Mono{$} to allow lookups. In this case, the column value,
which must be a string, is treated as a lookup, which may contain periods, and
evaluated with contextual lookup semantics.}}}
retrieve : Template (From lib:sqlinterop ).retrieve {
{{{A connection from a \Mono{From sql:} import}}}
connection : Required
{{{A string containing the implementation-specific SQL statement.}}}
sql_query : Required
{{{The template to be used for each row. When the query is evaluated, this
template is expanded for each row and the columns, except \Mono{ATTRNAME},
from the database are overrides on the row.}}}
sql_row_tmpl : Used
}
{{{Create an SQL-statement generating template.}}}
queryifier : Template {
{{{The template to modify.}}}
base : Required
value : Template base {
{{{A frame of expressions or columns to be included in the resulting table.}}}
columns : Required
{{{A collection of expressions (or columns) that must be true to include a
row in the results.}}}
where : Template {}
{{{A collection of expressions (or columns) by which to group the resulting
columns.}}}
group_by : Template {}
{{{The collection of columns available from each of the tables specified in
\{tables}. These can be used in any place that accepts an expression. Given
there is a column \Mono{c} in a table \Mono{t}, reference it as
\Mono{column.t.c}.}}}
column :
For t : table, name : Name
Select name : t.known_columns
{{{Provides templates for construction expressions. See \{sql_provider} for details.}}}
expr : provider.expr
{{{The correct database provider from \{sql_providers}.}}}
provider : Required
{{{Expand the where template.}}}
exp_where : where {}
{{{Expand the group by template.}}}
exp_group : ({ value : group_by {} sql_aggregate_context : "GROUP BY" }).value
{{{A list of tables, inferred from \{columns}, to be included in this
query.}}}
tables :
For column : columns
Reduce For col_table : column.sql_tables, acc_table : acc, name : Name Select name : col_table ?? acc_table
With acc :
(For clause : exp_where
Reduce For where_table : clause.sql_tables, acc_table : acc, name : Name Select name : where_table ?? acc_table
With acc : {})
{{{Generate the \Mono{FROM} clause.}}}
from_clause :
For t : tables, pos : Ordinal
Reduce (If pos > 1 Then acc & ", " Else "") & t
With acc : Null
{{{Generate the columns in the \Mono{SELECT}.}}}
column_clause :
For col : columns, name : Name, pos : Ordinal
Reduce (If pos > 1 Then acc & ", " Else "") & col.sql_expr & " AS " & provider.symbol_start Enforce Str & name & provider.symbol_end Enforce Str
With acc : Null
{{{Generate the \Mono{WHERE} clause, if any.}}}
where_clause :
For clause : exp_where, position : Ordinal
Reduce (If position > 1 Then acc & " AND " Else " WHERE ") & clause.sql_expr
With acc : ""
sql_aggregate_context : Null
sql_non_aggregate_result_cols :
For x : columns Append exp_where
Reduce For acc_cols : acc, x_cols : x.sql_non_aggregate_columns, name : Name Select name : acc_cols ?? x_cols
With acc : {}
sql_group_cols :
For x : exp_group
Reduce For acc_cols : acc, x_cols : x.sql_non_aggregate_columns, name : Name Select name : acc_cols ?? x_cols
With acc : {}
sql_results_contain_aggregate :
For x : columns Append exp_where
Reduce acc || x.sql_contains_aggregate
With acc : False
ungrouped_aggregate_cols :
For group : sql_group_cols, result : sql_non_aggregate_result_cols
Where group Is Null
Reduce acc & " " & result
With acc : ""
group_clause :
If sql_results_contain_aggregate && Length (ungrouped_aggregate_cols Enforce Str) > 0
Then Error ("The following columns are not grouped:" & ungrouped_aggregate_cols)
Else
(For clause : exp_group, position : Ordinal
Reduce (If position > 1 Then acc & ", " Else " GROUP BY ") & clause.sql_expr
With acc : "")
{{{Generate the total query.}}}
sql_query :
If column_clause Is Null Then Error "No columns specified in query."
Else "SELECT " & column_clause Enforce Str & (If from_clause Is Null Then provider.null_from Else " FROM " & from_clause Enforce Str) & where_clause Enforce Str & group_clause Enforce Str
}
}
{{{Query an SQL database for the delicious records within.}}}
query : Template queryifier(base : retrieve) {
{{{A template of templates to generate tables to be embedded in the query. See \{sql_literal_table_tmpl}.}}}
literal_tables : Template {}
{{{A collection of tables provided by the database.}}}
table :
For real : provider.table_discovery(connection : connection), literal : literal_tables {}, name : Name
Select name : real ?? (literal {})
{{{The default set of columns to retreive is all known columns from the selected tables.}}}
columns :
For col : column
Reduce For acc_value : acc, col_value : col, name : Name
Select name : acc_value ?? col_value
With acc : {}
{{{The provider, automatically selected from the database connection.}}}
provider :
((For provider : sql_providers, provider_name : Name
Where provider_name == connection.provider Enforce Str
Reduce provider With acc : Null) ?? (Error "Unsupported database “\(connection.provider Enforce Str)”. This library must be mismatched to the Flabbergast runtime.")) {}
}
{{{Create an SQL query without a connection to the database. The tables and
columns available must be manually curated.}}}
offline_query : queryifier(base : Template { value : sql_query })
{{{The template to create a table for an offline query.}}}
offline_table_tmpl : Template {
{{{The name of the schema this table belongs to.}}}
table_schema : Required
{{{The name of this table in this schema.}}}
table_name : Required
{{{The columns in this table, using the \{column_tmpl}.}}}
columns : Template {}
{{{The template to use for each column.}}}
column_tmpl : Template provider.expr.base_expr_tmpl {
{{{The name of the column.}}}
column_name : Required
{{{The type of the column. If not specified, it is assumed to be a string.}}}
sql_type : sql_types.str
sql_tables : Now utils_lib.frame(sql_from, name : Id)
sql_expr : sql_from Enforce Str & "." & provider.symbol_start Enforce Str & column_name Enforce Str & provider.symbol_end Enforce Str
sql_non_aggregate_columns : utils_lib.frame(sql_expr, name : Id)
sql_contains_aggregate : False
}
known_columns : columns {}
sql_from : (If table_schema Is Null Then "" Else provider.symbol_start Enforce Str & table_schema Enforce Str & provider.symbol_end Enforce Str & ".") & provider.symbol_start Enforce Str & table_name Enforce Str & provider.symbol_end Enforce Str
}
{{{Convert a value into a SQL expression if possible.}}}
sql_marshall : utils_lib.str_concatifier(base : sql_marshall_list, delimiter : "")
{{{Convert a list of Flabbergast items to an SQL expression.}}}
sql_marshall_list : Template {
provider : Required
value :
For arg : args, arg_name : Name
Select arg_name : (TypeOf arg With sql_marshall_converters {}).value
}
sql_marshall_converters : {
bin :
Template {
value : provider.expr.base_const_tmpl {
sql_expr : provider.blob_start Enforce Str & utils_lib.bin_to_hex_str(arg, uppercase : True) Enforce Str & provider.blob_end Enforce Str
sql_type : sql_types.blob
}
}
bool :
Template {
value : provider.expr.base_const_tmpl {
sql_expr : If arg Then "TRUE" Else "FALSE"
sql_type : sql_types.bool
}
}
float :
Template {
value : provider.expr.base_const_tmpl {
sql_expr : arg To Str
sql_type : sql_types.float
}
}
frame : Template { value : arg }
int :
Template {
value : provider.expr.base_const_tmpl {
sql_expr : arg To Str
sql_type : sql_types.int
}
}
null : Template { value : Null }
template : Template { value : Error "Cannot convert template to SQL." }
str :
Template {
value : provider.expr.base_const_tmpl {
sql_expr : provider.quote_start Enforce Str & utils_lib.str_escape(arg, transformations : provider.transformations) Enforce Str & provider.quote_end Enforce Str
sql_type : sql_types.str
}
}
}
{{{Compute the SQL type of a complex expression.}}}
synthetic_sql_type : Template {
coerce : Required
id : 0
cast :
For type : sql_types
Where type Is Frame
Reduce If type.id B& coerce != 0
Then acc B| type.cast
Else acc
With acc : 0
name :
(For type : sql_types
Where type Is Frame && type.id B& coerce != 0
Reduce If acc Is Null
Then type.name Enforce Str
Else acc Enforce Str & " or " & type.name Enforce Str
With acc : Null) ?? "none"
}
{{{A table from Flabbergast to be embedded in the query. It should probably be short.}}}
sql_literal_table_tmpl : Template {
{{{A list of frames to convert to rows in the table. If any attributes are missing in some rows, nulls will be substituted. }}}
rows : Required
marshalled_rows : For row : rows Select sql_marshall_list(args : row, provider : provider)
column_types :
For row : marshalled_rows
Reduce
For r : row, a : acc, n : Name
Select n : If a Is Null || r Is Null Then r?.sql_type ?? a Else synthetic_sql_type { coerce : a.coerce B& r.sql_type.coerce }
With acc : {}
table_name : Id
known_columns :
For type : column_types, name : Name
Select name :
If type.coerce == 0
Then Error "Column “\(name)” in literal table does not have consistent type."
Else column_tmpl { sql_expr : provider.symbol_start Enforce Str & table_name Enforce Str & provider.symbol_end Enforce Str & "." & provider.symbol_start Enforce Str & name & provider.symbol_end Enforce Str sql_type : type }
column_tmpl : Template {
sql_tables : Now utils_lib.frame(sql_from, name : Id)
sql_non_aggregate_columns : utils_lib.frame(sql_expr, name : Id)
sql_contains_aggregate : False
}
sql_from : (provider.generate_literal_from {}).value
}
sql_arith_ops : {
add : { id : 1 symbol : "+" }
sub : { id : 2 symbol : "-" }
mul : { id : 4 symbol : "*" }
div : { id : 8 symbol : "/" }
mod : { id : 16 symbol : "%" }
}
{{{All the rules for arithemtic operations in SQL.}}}
sql_aritmetic_tmpl : Template {
ii : {
ops : sql_arith_ops.add.id B| sql_arith_ops.sub.id B| sql_arith_ops.mul.id B| sql_arith_ops.div.id B| sql_arith_ops.mod.id
left_type : sql_types.int.id
right_type : sql_types.int.id
result_type : sql_types.int
render : Template {
value : "(" & specials.l.sql_expr Enforce Str & sql_op.symbol Enforce Str & specials.r.sql_expr Enforce Str & ")"
}
}
nn : {
ops : sql_arith_ops.add.id B| sql_arith_ops.sub.id B| sql_arith_ops.mul.id B| sql_arith_ops.div.id
left_type : sql_types.int.id B| sql_types.float.id
right_type : sql_types.int.id B| sql_types.float.id
result_type : sql_types.float
render : Template {
value : "(" & specials.l.sql_expr Enforce Str & sql_op.symbol Enforce Str & specials.r.sql_expr Enforce Str & ")"
}
}
tv : {
ops : sql_arith_ops.add.id B| sql_arith_ops.sub.id
left_type : sql_types.timestamp.id
right_type : sql_types.interval.id
result_type : sql_types.timestamp
render : Template {
value : "(" & specials.l.sql_expr Enforce Str & sql_op.symbol Enforce Str & specials.r.sql_expr Enforce Str & ")"
}
}
tt : {
ops : sql_arith_ops.sub.id
left_type : sql_types.timestamp.id
right_type : sql_types.timestamp.id
result_type : sql_types.interval
render : Template {
value : "(" & specials.l.sql_expr Enforce Str & sql_op.symbol Enforce Str & specials.r.sql_expr Enforce Str & ")"
}
}
vi : {
ops : sql_arith_ops.mul.id
left_type : sql_types.interval.id B| sql_types.int.id
right_type : sql_types.interval.id B| sql_types.int.id
result_type : sql_types.interval
render : Template {
value : "(" & specials.l.sql_expr Enforce Str & sql_op.symbol Enforce Str & specials.r.sql_expr Enforce Str & ")"
}
}
vv : {
ops : sql_arith_ops.add.id B| sql_arith_ops.sub.id
left_type : sql_types.interval.id
right_type : sql_types.interval.id
result_type : sql_types.interval
render : Template {
value : "(" & specials.l.sql_expr Enforce Str & sql_op.symbol Enforce Str & specials.r.sql_expr Enforce Str & ")"
}
}
}
sql_types : {
blob : {
name : "BLOB"
id : 64
cast : blob.id
coerce : blob.id
}
bool : {
name : "BOOLEAN"
id : 1
cast : bool.id B| int.id B| str.id
coerce : bool.id B| str.id
}
float : {
name : "DOUBLE"
id : 2
cast : int.id B| str.id
coerce : float.id B| str.id
}
int : {
name : "INTEGER"
id : 4
cast : bool.id B| float.id B| str.id
coerce : int.id B| float.id B| str.id
}
interval : {
name : "INTERVAL"
id : 32
cast : 0
coerce : interval.id B| str.id
}
str : {
name : "VARCHAR(4096)"
id : 8
cast : bool.id B| int.id B| float.id B| str.id
coerce : str.id
}
timestamp : {
name : "TIMESTAMP"
id : 16
cast : 0
coerce : str.id B| timestamp.id
}
any : blob.id B| bool.id B| int.id B| interval.id B| float.id B| str.id B| timestamp.id
numeric : int.id B| float.id
}
{{{A template for implementing a new SQL provider.}}}
sql_provider : Template {
{{{The expression templates available for instantiation.}}}
expr : allowed_expressions {}
{{{A transformation set for string escaping. Yes, this is not standard.}}}
transformations : Required
{{{The preamble starting a string.}}}
quote_start : "'"
{{{The postamble end a string.}}}
quote_end : "'"
{{{The preamble of a symbol (table, schema, or column name).}}}
symbol_start : "\""
{{{The postamble of a symbol (table, schema, or column name).}}}
symbol_end : "\""
{{{The preamble of a blob.}}}
blob_start : "X'"
{{{The postamble of a blob.}}}
blob_end : "'"
{{{The type of a blob.}}}
blob_type : "BLOB"
{{{The \Mono{FROM} clause to use for a query with no tables.}}}
null_from : ""
{{{An SQL statement that returns tables and views as a frame of frames with a
\{sql_from} containing the string needed to include this entity in a
\Mono{FROM} clause. Other implementation-specific attributes are permitted.}}}
table_discovery : Template retrieve {
sql_row_tmpl : Template {
known_columns : column_discovery(connection : connection, table : This)
}
}
{{{An SQL statement that will return the columns for a particular table or
view as a frame of frames extending \{expr.base_expr_tmpl}. Other
implementation-specific attributes are permitted.}}}
column_discovery : Template retrieve {
table : Required
sql_row_tmpl +: {
sql_tables : Now utils_lib.frame(table.sql_from, name : GenerateId table)
}
}
{{{List of arithmetic rules this database supports.}}}
arithmetic_rules : sql_aritmetic_tmpl {}
{{{Generate a FROM clause containing literal data.}}}
generate_literal_from : Template {
column_list :
(For name : Name, type : column_types, pos : Ordinal
Reduce acc & (If pos > 1 Then "," Else "") & symbol_start Enforce Str & name & symbol_end Enforce Str
With acc : symbol_start Enforce Str & table_name Enforce Str & symbol_end Enforce Str & "(") & ")"
value_list :
For row : marshalled_rows, pos : Ordinal
Reduce
acc &
(If pos > 1 Then "," Else "") &
(For column : row, type : column_types, name : Name, pos : Ordinal
Reduce acc & (If pos > 1 Then "," Else "") & (column?.sql_expr ?? "NULL")
With acc : "(") & ")"
With acc : ""
value : "(VALUES " & value_list Enforce Str & ") AS " & column_list
}
{{{The kinds of expressions that maybe used in an SQL query.}}}
allowed_expressions : Template {
{{{A base template representing an expression or column in a SQL query.}}}
base_expr_tmpl : Template {
{{{A string containing the SQL code needed to access this column.}}}
sql_expr : Required
{{{A member of \{sql_types} that represents the Flabbergast type to which
this expression will be converted.}}}
sql_type : Required
{{{All the tables required by this expression.}}}
sql_tables : Required
{{{Any columns that are not arguments to an aggregate function.}}}
sql_non_aggregate_columns : Required
{{{Whether this expression contains an aggregate function.}}}
sql_contains_aggregate : Required
}
base_const_tmpl : Template base_expr_tmpl {
sql_tables : {}
sql_non_aggregate_columns : {}
sql_contains_aggregate : False
}
base_unary_expr_tmpl : Template base_expr_tmpl {
{{{The argument to cast.}}}
arg : Required
sql_tables : arg.sql_tables
sql_non_aggregate_columns : arg.sql_non_aggregate_columns
sql_contains_aggregate : arg.sql_contains_aggregate
}
base_unary_function_tmpl : Template base_unary_expr_tmpl {
sql_arg_type : Required
sql_func : Required
sql_expr : If sql_arg_type.id B& arg.sql_type.coerce == 0
Then Error "Cannot convert \(arg.sql_type.name) to \(sql_arg_type.name)."
Else sql_func Enforce Str & "(" & arg.sql_expr Enforce Str & ")"
}
base_binary_expr_tmpl : Template base_expr_tmpl {
left : Required
right : Required
specials : sql_marshall_list(args : { l : left r : right }, provider : provider)
sql_tables : For left_table : specials.l.sql_tables, right_table : specials.r.sql_tables, name : Name Select name : left_table ?? right_table
sql_non_aggregate_columns : For left_cols : specials.l.sql_non_aggregate_columns, right_cols : specials.r.sql_non_aggregate_columns, name : Name Select name : left_cols ?? right_cols
sql_contains_aggregate : specials.l.sql_contains_aggregate || specials.r.sql_contains_aggregate
}
base_arith_expr_tmpl : Template base_binary_expr_tmpl {
sql_rule :
For arith : provider.arithmetic_rules
Where
arith.ops B& sql_op.id != 0 &&
specials.l.sql_type.coerce B& arith.left_type != 0 &&
specials.r.sql_type.coerce B& arith.right_type != 0
Reduce acc ?? arith
With acc : Null
sql_type :
If sql_rule Is Null
Then sql_types.int # The type doesn't matter. We're just trying to avoid absurd errors.
Else sql_rule.result_type
sql_expr :
If sql_rule Is Null
Then Error "Cannot find arithmetic for \(specials.l.sql_type.name) \(sql_op.symbol) \(specials.r.sql_type.name)."
Else (sql_rule.render{}).value
}
base_args_tmpl : Template base_expr_tmpl {
sql_args : sql_marshall_list(args : args, provider : provider)
sql_tables :
For arg : sql_args
Reduce For table : arg.sql_tables, acc_table : acc, name : Name Select name : table ?? acc_table
With acc : {}
}
base_infix_expr_tmpl : Template base_args_tmpl {
{{{The expressions to be put together.}}}
args : Required
{{{The infix operation symbol.}}}
sql_op : Required
{{{The bits for the allowed types of arguments.}}}
sql_allowed_arg_type_id : Required
sql_expr :
If sql_type.coerce == 0 Then Error "Cannot compute type." Else
(For arg : sql_args, pos : Ordinal
Reduce acc & (If pos > 1 Then sql_op Else "") & arg.sql_expr
With acc : "(") & ")"
sql_type : synthetic_sql_type {
coerce :
For arg : sql_args, pos : Ordinal
Reduce
If acc B& arg.sql_type.coerce == 0
Then Error "Argument \(pos) to \(sql_op) has incompatible type \(arg.sql_type.name)."
Else acc B& arg.sql_type.coerce
With acc : sql_allowed_arg_type_id
}
sql_non_aggregate_columns :
For arg : sql_args
Reduce For acc_cols : acc, arg_cols : arg.sql_non_aggregate_columns, name : Name Select name : acc_cols ?? arg_cols
With acc : {}
sql_contains_aggregate : For arg : sql_args Reduce acc || arg.sql_contains_aggregate With acc : False
}
base_function_expr_tmpl : Template base_args_tmpl {
{{{The expressions to be put together.}}}
args : Required
{{{The function name.}}}
sql_func : Required
{{{A list of argument types.}}}
arg_types : Required
sql_expr :
(For arg : sql_args, arg_type : arg_types, pos : Ordinal
Reduce
If arg_type.id B& arg.sql_type.coerce == 0
Then Error "Incompatible type \(arg.sql_type.name) for argument \(pos) to \(sql_func). Expected \(arg_type.name)."
Else acc & (If pos > 1 Then "," Else "") & arg.sql_expr
With acc : sql_func & "(") & ")"
sql_non_aggregate_columns :
For arg : sql_args
Reduce For acc_cols : acc, arg_cols : arg.sql_non_aggregate_columns, name : Name Select name : acc_cols ?? arg_cols
With acc : {}
sql_contains_aggregate : For arg : sql_args Reduce acc || arg.sql_contains_aggregate With acc : False
}
base_aggregate_func_tmpl : Template base_expr_tmpl {
{{{The expression aggregate.}}}
arg : Required
{{{The function name.}}}
sql_func : Required
{{{The bits for the allowed types of arguments.}}}
sql_allowed_arg_type_mask : Required
{{{Extra arguments, if required.}}}
sql_extra_args : ""
sql_aggregate_context : sql_func & " function arguments"
parent_sql_aggregate_context : Lookup sql_aggregate_context In Container
sql_expr :
If !(parent_sql_aggregate_context Is Null)
Then Error "Aggregate function \(sql_func) not allowed in \(parent_sql_aggregate_context)."
Else If arg.sql_type.coerce B& sql_allowed_arg_type_mask == 0
Then Error "Bad type in \(sql_func)."
Else sql_func & "(" & arg.sql_expr & sql_extra_args & ")"
sql_tables : arg.sql_tables
sql_type : synthetic_sql_type {
coerce : arg.sql_type.coerce B& sql_allowed_arg_type_mask
}
sql_non_aggregate_columns : {}
sql_contains_aggregate : True
}
base_variadic_func_tmpl : Template base_args_tmpl {
{{{The expressions aggregate.}}}
args : Required
{{{The infix operation symbol.}}}
sql_func : Required
{{{The bits for the allowed types of arguments.}}}
sql_allowed_arg_type_id : Required
sql_expr :
If sql_type.coerce == 0 Then Error "Bad type in \(sql_func)." Else
(For arg : sql_args, pos : Ordinal
Reduce acc & (If pos > 1 Then ", " Else "") & arg.sql_expr
With acc : sql_func & "(") & ")"
sql_type : synthetic_sql_type {
coerce :
For arg : sql_args, pos : Ordinal
Reduce
If acc B& arg.sql_type.coerce == 0
Then Error "Argument \(pos) to \(sql_func) has incompatible type \(arg.sql_type.name)."
Else acc B& arg.sql_type.coerce
With acc : sql_allowed_arg_type_id
}
sql_non_aggregate_columns :
For arg : sql_args
Reduce For acc_cols : acc, arg_cols : arg.sql_non_aggregate_columns, name : Name Select name : acc_cols ?? arg_cols
With acc : {}
sql_contains_aggregate : For arg : sql_args Reduce acc || arg.sql_contains_aggregate With acc : False
}
base_compare_op_tmpl : Template base_binary_expr_tmpl {
{{{The comparison symbol.}}}
sql_op : Required
type_mask : sql_types.any
sql_type : sql_types.bool
sql_expr :
If specials.l.sql_type.coerce B& specials.r.sql_type.coerce B& type_mask == 0
Then Error "Incompatible types in comparison."
Else "(\(specials.l.sql_expr) \(sql_op) \(specials.r.sql_expr))"
}
add : Template base_arith_expr_tmpl {
sql_op : sql_arith_ops.add
}
and : Template base_infix_expr_tmpl {
sql_op : "AND"
sql_allowed_arg_type_id : sql_types.bool.id
}
avg : Template base_aggregate_func_tmpl {
sql_func : "AVG"
sql_allowed_arg_type_mask : sql_types.numeric
}
base_case_tmpl : Template base_expr_tmpl {
cases : Required
else : Null
when : Template {
test : Required
result : Required
when_specials : sql_marshall_list(args : { t : test r : result }, provider : provider)
sql_type : when_specials.r.sql_type
sql_when :
If when_specials.t.sql_type.coerce B& sql_allowed_test_type.coerce == 0
Then Error "Incompatible type \(when_specials.t.sql_type.name) used in test for WHEN. Expected \(sql_allowed_test_type.name)."
Else " WHEN \(when_specials.t.sql_expr) THEN \(when_specials.r.sql_expr)"
sql_tables : For test_table : when_specials.t.sql_tables, input_table : when_specials.r.sql_tables, name : Name Select name : test_table ?? input_table
sql_non_aggregate_columns : For test_cols : when_specials.t.sql_non_aggregate_columns, result_cols : when_specials.r.sql_non_aggregate_columns, name : Name Select name : test_cols ?? result_cols
sql_contains_aggregate : when_specials.t.sql_contains_aggregate || when_specials.r.sql_contains_aggregate
}
specials : Template {
e : else
}
sql_specials : sql_marshall_list(args : specials {}, provider : provider)
sql_expr :
(For case : cases
Reduce acc & case.sql_when
With acc : "(CASE " & input_expr) &
(If sql_specials.e Is Null Then "" Else " ELSE " & sql_specials.e.sql_expr) &
" END)"
sql_type : synthetic_sql_type {
coerce :
For case : cases, pos : Ordinal
Reduce
If acc B& case.sql_type.coerce == 0
Then Error "WHEN block \(pos) has incompatible type \(case.sql_type.name)."
Else acc B& case.sql_type.coerce
With acc : If sql_specials.e Is Null Then sql_types.any Else sql_specials.e.sql_type.coerce
}
sql_tables : For s : sql_specials
Where !(s Is Null)
Reduce For table : s.sql_tables, acc_table : acc, name : Name Select name : table ?? acc_table
With acc : (For c : cases
Reduce For table : c.sql_tables, acc_table : acc, name : Name Select name : table ?? acc_table
With acc : {})
sql_non_aggregate_columns : For s : sql_specials
Where !(s Is Null)
Reduce For acc_cols : acc, special_cols : s.sql_non_aggregate_columns, name : Name Select name : acc_cols ?? special_cols
With acc : (For c : cases
Reduce For acc_cols : acc, case_cols : c.sql_non_aggregate_columns, name : Name Select name : acc_cols ?? case_cols
With acc : {})
sql_contains_aggregate : For s : sql_specials
Where !(s Is Null)
Reduce acc || s.sql_contains_aggregate
With acc : (For c : cases
Reduce acc || c.sql_contains_aggregate
With acc : False)
}
case_bool : Template base_case_tmpl {
sql_allowed_test_type : sql_types.bool
input_expr : ""
}
case_input : Template base_case_tmpl {
input : Required
specials +: { i : input }
input_expr : sql_specials.i.sql_expr
sql_allowed_test_type : sql_specials.i.sql_type
}
cast : Template base_unary_expr_tmpl {
{{{The type to change it to.}}}
sql_type : Required
sql_expr : If sql_type.id B& arg.sql_type.cast == 0
Then Error "Cannot perform cast with provided types."
Else "(CAST(\(arg.sql_expr Enforce Str) AS \(If sql_type.id Enforce Int == sql_types.blob.id Then provider.blob_type Enforce Str Else sql_type.name Enforce Str)))"
}
count : Template base_aggregate_func_tmpl {
sql_func : "COUNT"
sql_allowed_arg_type_mask : sql_types.any
sql_type : sql_types.int
}
div : Template base_arith_expr_tmpl {
sql_op : sql_arith_ops.div
}
equal : Template base_compare_op_tmpl {
sql_op : "=="
}
extract : Template base_unary_expr_tmpl {
field : Required
year : "YEAR"
month : "MONTH"
day : "DAY"
hour : "HOUR"
minute : "MINUTE"
second : "SECOND"
sql_start : "EXTRACT("
sql_middle : " FROM "
sql_end : ")"
sql_type : sql_types.int
sql_expr :
If arg.sql_type.coerce B& sql_types.timestamp.id == 0
Then Error "Cannot extract on non-timestamp \(arg.sql_type.name)."
Else sql_start Enforce Str & field Enforce Str & sql_middle Enforce Str & arg.sql_expr Enforce Str & sql_end Enforce Str
}
ge : Template base_compare_op_tmpl {
sql_op : ">="
}
gt : Template base_compare_op_tmpl {
sql_op : ">"
}
greatest : Template base_variadic_func_tmpl {
sql_func : "GREATEST"
sql_allowed_arg_type_id : sql_types.numeric
}
interval_const : Template base_const_tmpl {
{{{The number of seconds in this interval.}}}
time : Required
sql_type : sql_types.interval
}
is_null : Template base_unary_expr_tmpl {
sql_type : sql_types.bool
sql_expr :
If sql_type.coerce == 0 Then Error "Bad type in null check."
Else "(\(arg.sql_expr) IS NULL)"
}
le : Template base_compare_op_tmpl {
sql_op : "<="
}
least : Template base_variadic_func_tmpl {
sql_func : "LEAST"
sql_allowed_arg_type_id : sql_types.numeric
}
like : Template base_binary_expr_tmpl {
{{{Whether the matching should be case-sensitive.}}}
case_sensitive : True
sql_type : sql_types.bool
sql_expr :
If specials.l.sql_type.coerce B& specials.r.sql_type.coerce B& sql_types.str.id == 0
Then Error "Incompatible types in comparison."
Else If case_sensitive
Then "(\(specials.l.sql_expr) LIKE \(specials.r.sql_expr))"
Else "(UPPER(\(specials.l.sql_expr)) LIKE UPPER(\(specials.r.sql_expr)))"
}
lower : Template base_unary_function_tmpl {
sql_arg_type : sql_types.str
sql_type : sql_types.str
sql_func : "LOWER"
}
lt : Template base_compare_op_tmpl {
sql_op : "<"
}
max : Template base_aggregate_func_tmpl {
sql_func : "MAX"
sql_allowed_arg_type_mask : sql_types.numeric
}
min : Template base_aggregate_func_tmpl {
sql_func : "MIN"
sql_allowed_arg_type_mask : sql_types.numeric
}
mod : Template base_arith_expr_tmpl {
sql_op : sql_arith_ops.mod
}
mul : Template base_arith_expr_tmpl {
sql_op : sql_arith_ops.mul
}
negate : Template base_unary_expr_tmpl {
sql_type : arg.sql_type
sql_expr :
If arg.sql_type.coerce B& sql_types.numeric != 0
Then "(- \(arg.sql_expr))"
Else Error "Argument to negation is not numeric."
}
not : Template base_unary_expr_tmpl {
sql_type : sql_types.bool
sql_expr :
If arg.sql_type.coerce B& sql_types.bool.id != 0
Then "(NOT \(arg.sql_expr))"
Else Error "Argument to NOT expression is not Boolean."
}
now : Template base_const_tmpl {
{{{Use UTC rather than local time.}}}
utc : True
sql_type : sql_types.timestamp
}
or : Template base_infix_expr_tmpl {
sql_op : "OR"
sql_allowed_arg_type_id : sql_types.bool.id
}
str_agg : Template base_aggregate_func_tmpl {
{{{The separator expression.}}}
separator : ""
sql_allowed_arg_type_mask : sql_types.str.id
specials : sql_marshall_list(args : { s : separator }, provider : provider)
sql_tables : For a : arg.sql_tables, s : specials.s.sql_tables, n : Name Select n : a ?? s
}
str_join : Template base_infix_expr_tmpl {
sql_op : "||"
sql_allowed_arg_type_id : sql_types.str.id
}
str_slice : Template base_expr_tmpl {
input : Required
start : Required
length : Required
sql_substr_syntax : "SUBSTRING(" & specials.i.sql_expr & " FROM " & specials.s.sql_expr & " FOR " & specials.l.sql_expr & ")"
specials : sql_marshall_list(args : { i : input s : start l : length }, provider : provider)
sql_type : sql_types.str
sql_expr :
If specials.i.sql_type.coerce B& sql_types.str.id == 0 Then Error "Input of string slice is not a string."
Else If specials.s.sql_type.coerce B& sql_types.int.id == 0 Then Error "Start of string slice is not an integer."
Else If specials.l.sql_type.coerce B& sql_types.int.id == 0 Then Error "Length of string slice is not an integer."
Else sql_substr_syntax
sql_tables : For input_table : specials.i.sql_tables, start_table : specials.s.sql_tables, length_table : specials.l.sql_tables, name : Name Select name : (input_table ?? start_table) ?? right_table
sql_non_aggregate_columns : For s : specials
Reduce For acc_cols : acc, special_cols : s.sql_non_aggregate_columns, name : Name Select name : acc_cols ?? special_cols
With acc : {}
sql_contains_aggregate : For s : specials
Reduce acc || s.sql_contains_aggregate
With acc : False
}
sub : Template base_arith_expr_tmpl {
sql_op : sql_arith_ops.sub
}
sum : Template base_aggregate_func_tmpl {
sql_func : "SUM"
sql_allowed_arg_type_mask : sql_types.numeric
}
unequal : Template base_compare_op_tmpl {
sql_op : "<>"
}
upper : Template base_unary_function_tmpl {
sql_arg_type : sql_types.str
sql_type : sql_types.str
sql_func : "UPPER"
}
}
}
sql_provider_with_union_literal : Template sql_provider {
generate_literal_from : Template {
value_list :
For row : marshalled_rows, pos : Ordinal
Reduce
acc &
(If pos > 1 Then " UNION " Else "") &
(For column : row, type : column_types, name : Name, pos : Ordinal
Reduce acc & (If pos > 1 Then "," Else "") & (column?.sql_expr ?? "NULL") & " AS " & symbol_start Enforce Str & name & symbol_end Enforce Str
With acc : "SELECT ") & provider.null_from Enforce Str
With acc : ""
value : "(" & value_list Enforce Str & ") AS " & symbol_start Enforce Str & table_name Enforce Str & symbol_end Enforce Str
}
}
sql_providers : {
mssql : Template sql_provider {
table_discovery +: {
sql_query : "SELECT QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) AS sql_from, QUOTENAME(table_schema, '''') as table_schema, QUOTENAME(table_name, '''') AS table_name, LOWER(LEFT(table_schema, 1)) + RIGHT(table_schema, LEN(table_schema) - 1) + '_' + table_name AS [ATTRNAME] FROM information_schema.tables WHERE UPPER(table_schema) NOT IN ('SYS', 'INFORMATION_SCHEMA') AND PATINDEX('%[^a-zA-Z0-9_]%', table_schema) = 0 AND PATINDEX('%[^a-zA-Z0-9_]%', table_name) = 0;"
}
column_discovery +: {
sql_query : "SELECT QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) + '.' + QUOTENAME(column_name) AS sql_expr, LOWER(LEFT(column_name, 1)) + RIGHT(column_name, LEN(column_name) - 1) AS [ATTRNAME], data_type, 'sql_types.' + data_xlate.lookup AS [$sql_type] FROM information_schema.columns, (VALUES ('bigint', 'int'), ('decimal', 'int'), ('int', 'int'), ('money', 'int'), ('numeric', 'int'), ('smallint', 'int'), ('smallmoney', 'int'), ('tinyint', 'int'), ('bit', 'bool'), ('float', 'float'), ('real', 'float'), ('%char%', 'str'), ('%text%', 'str'), ('%binary%', 'str'), ('%datetime%', 'timestamp'), ('time%', 'timestamp'), ('date', 'timestamp'), ('%binary', 'blob')) AS data_xlate (name, lookup) WHERE table_schema = \(table.table_schema) AND table_name = \(table.table_name) AND data_type LIKE data_xlate.name AND PATINDEX('%[^a-zA-Z0-9_]%', column_name) = 0;"
}
quote_start : "('"
quote_end : "')"
symbol_start : "["
symbol_end : "]"
blob_type : "VARBINARY(MAX)"
transformations : {
quote : utils_lib.str_transform.char_tmpl { char : "'" replacement : "''" }
nonprint : utils_lib.str_transform.range_tmpl { start : "\x00" end : "\x1F" replacement : [ "' + NCHAR(", utf8_0, ") + '" ] }
unicode : utils_lib.str_transform.range_tmpl { start : "\u0100" end : "\uFFFF" replacement : [ "' + NCHAR(", utf32, ") + '" ] mode : mode_decimal }
}
allowed_expressions +: {
extract +: {
year : "year"
month : "month"
day : "day"
hour : "hour"
minute : "minute"
second : "second"
sql_start : "DATEPART('"
sql_middle : "', "
sql_end : ")"
}
interval_const +: {
sql_expr : time Enforce Int
}
str_agg +: {
sql_func : Null
sql_extra_args : Error "String aggregation is not supported on Microsoft SQL Server/T-SQL."
}
str_join : Template base_variadic_func_tmpl {
sql_func : "CONCAT"
sql_allowed_arg_type_id : sql_types.str.id
}
str_slice +: {
sql_substr_syntax : "SUBSTRING(" & specials.i.sql_expr & ", " & specials.s.sql_expr & ", " & specials.l.sql_expr & ")"
}
now +: {
sql_expr : If utc Then "GETUTCDATE()" Else "CURRENT_TIMESTAMP"
}
}
arithmetic_rules : sql_aritmetic_tmpl {
tv : {
ops : sql_arith_ops.add.id B| sql_arith_ops.sub.id
left_type : sql_types.timestamp.id
right_type : sql_types.interval.id
result_type : sql_types.timestamp
render : Template {
value : "DATEADD('second', 0 " & sql_op.symbol Enforce Str & specials.r.sql_expr Enforce Str & ", " & specials.l.sql_expr Enforce Str & ")"
}
}
tt : {
ops : sql_arith_ops.sub.id
left_type : sql_types.timestamp.id
right_type : sql_types.timestamp.id
result_type : sql_types.interval
render : Template {
value : "DATEDIFF('second', " & specials.l.sql_expr Enforce Str & ", " & specials.r.sql_expr Enforce Str & ")"
}
}
}
}
mysql : Template sql_provider_with_union_literal {
table_discovery +: {
sql_query : "SELECT QUOTE(TABLE_SCHEMA) AS tbl_schema, QUOTE(TABLE_NAME) AS tbl_name, CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS sql_from, CONCAT(LOWER(SUBSTRING(TABLE_NAME, 0, 1)), SUBSTRING(TABLE_NAME, 1)) AS ATTRNAME FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema')"
}
column_discovery +: {
sql_query : "SELECT CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`.`', COLUMN_NAME, '`') AS sql_expr, CONCAT(LOWER(SUBSTRING(COLUMN_NAME, 1, 1)), SUBSTRING(COLUMN_NAME, 2)) AS ATTRNAME, lookup AS `$sql_type` FROM information_schema.COLUMNS, (SELECT 'bit%' AS type_pattern, 'sql_types.bool' AS lookup UNION SELECT '%int%' AS type_pattern, 'sql_types.int' AS lookup UNION SELECT 'float%' AS type_pattern, 'sql_types.float' AS lookup UNION SELECT 'double%' AS type_pattern, 'sql_types.float' AS lookup UNION SELECT 'decimal%' AS type_pattern, 'sql_types.float' AS lookup UNION SELECT '%char%' AS type_pattern, 'sql_types.str' AS lookup UNION SELECT '%blob%' AS type_pattern, 'sql_types.str' AS lookup UNION SELECT 'enum%' AS type_pattern, 'sql_types.str' AS lookup UNION SELECT 'text' AS type_pattern, 'sql_types.str' AS lookup UNION SELECT 'date' AS type_pattern, 'sql_types.timestamp' AS lookup UNION SELECT 'time%' AS type_pattern, 'sql_types.timestamp' AS lookup UNION SELECT 'year%' AS type_pattern, 'sql_types.timestamp' AS lookup UNION SELECT 'interval%' AS type_pattern, 'sql_types.interval' AS lookup UNION SELECT 'varbinary%' AS type_pattern, 'sql_types.blob' AS lookup UNION SELECT 'blob%' AS type_pattern, 'sql_types.blob' AS lookup) AS type_info WHERE COLUMN_TYPE LIKE type_pattern AND TABLE_SCHEMA=\(table.tbl_schema) AND TABLE_NAME=\(table.tbl_name)"
}
transformations : {
backslash : utils_lib.str_transform.char_tmpl { char : "\\" replacement : "\\\\" }
backspace : utils_lib.str_transform.char_tmpl { char : "\b" replacement : "\\b" }
newline : utils_lib.str_transform.char_tmpl { char : "\n" replacement : "\\n" }
null : utils_lib.str_transform.char_tmpl { char : "\x00" replacement : "\\0" }
return : utils_lib.str_transform.char_tmpl { char : "\r" replacement : "\\r" }
quote : utils_lib.str_transform.char_tmpl { char : "'" replacement : "\\'" }
double_quote : utils_lib.str_transform.char_tmpl { char : "\"" replacement : "\\\"" }
tab : utils_lib.str_transform.char_tmpl { char : "\t" replacement : "\\t" }
eof : utils_lib.str_transform.char_tmpl { char : "\x1A" replacement : "\\Z" }
}
symbol_start : "`"
symbol_end : "`"
allowed_expressions +: {
interval_const +: {
sql_expr : "INTERVAL \(time Enforce Int) SECONDS"
}
now +: {
sql_expr : If utc Then "UTC_TIMESTAMP()" Else "NOW()"
}
str_agg +: {
sql_func : "GROUP_CONCAT"
sql_extra_args : " SEPARATOR " & specials.s.sql_expr
}
str_join : Template base_variadic_func_tmpl {
sql_func : "CONCAT"
sql_allowed_arg_type_id : sql_types.str.id
}
}
}
oracle : Template sql_provider_with_union_literal {
table_discovery +: {
sql_query : "SELECT '\"' || table_name || '\"' AS \"sql_from\", table_name AS \"table_name\", REGEXP_REPLACE(REGEXP_REPLACE(LOWER(table_name), '[^a-zA-Z0-9_]', '_'), '^([^a-z])', 't\\1') AS \"ATTRNAME\" FROM user_tables"
}
column_discovery +: {
sql_query : "SELECT '\"' || table_name || '\".\"' || column_name || '\"' AS \"sql_expr\", REGEXP_REPLACE(REGEXP_REPLACE(LOWER(column_name), '[^a-zA-Z0-9_]', '_'), '^([^a-z])', 'c\\1') AS \"ATTRNAME\", data_type, 'sql_types.' || data_xlate.lookup AS \"\" FROM USER_TAB_COLUMNS , (SELECT objectschema name, objectname lookup FROM TABLE(sys.ODCIObjectList(sys.odciobject('NUMBER%', 'float'), sys.odciobject('%CHAR%', 'str'), sys.odciobject('BINARY_FLOAT', 'float'), sys.odciobject('BINARY_DOUBLE', 'float'), sys.odciobject('DATE%', 'timestamp'), sys.odciobject('TIMESTAMP%', 'timestamp'), sys.odciobject('INTERVAL%', 'interval'), sys.odciobject('%LOB', 'blob'), sys.odciobject('BFILE', 'blob')))) data_xlate WHERE table_name = '\(table_name)' AND data_type LIKE data_xlate.name"
}
transformations : {
quote : utils_lib.str_transform.char_tmpl { char : "'" replacement : "''" }
}
null_from : " FROM DUAL"
allowed_expressions +: {
interval_const +: {
sql_expr : "INTERVAL '\(time Enforce Int)' SECOND"
}
now +: {
sql_expr : If utc Then "SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)" Else "CURRENT_TIMESTAMP"
}
str_agg +: {
sql_func : "LISTAGG"
sql_extra_args : ", " & specials.s.sql_expr & ") WITHIN GROUP (ORDER BY " & arg.sql_expr
}
str_slice +: {
sql_substr_syntax : "SUBSTR(" & specials.i.sql_expr & ", " & specials.s.sql_expr & ", " & specials.l.sql_expr & ")"
}
}
generate_literal_from +: {
value : "(" & value_list Enforce Str & ") " & symbol_start Enforce Str & table_name Enforce Str & symbol_end Enforce Str
}
}
postgresql : Template sql_provider {
table_discovery +: {
sql_query : "SELECT QUOTE_IDENT(table_schema) || '.' || QUOTE_IDENT(table_name) AS sql_from, QUOTE_LITERAL(table_schema) AS table_schema, QUOTE_LITERAL(table_name) AS table_name, regexp_replace(regexp_replace(table_schema || '_' || table_name, '[^a-zA-Z0-9_]', '_'), '^([^a-z])', 't\\1') AS \"ATTRNAME\" FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema');"
}
column_discovery +: {
sql_query : "SELECT QUOTE_IDENT(table_schema) || '.' || QUOTE_IDENT(table_name) || '.' || QUOTE_IDENT(column_name) AS sql_expr, regexp_replace(regexp_replace(column_name, '[^a-zA-Z0-9_]', '_'), '^([^a-z])', 'c\\1') AS \"ATTRNAME\", data_type, 'sql_types.' || data_xlate.lookup AS \"$sql_type\" FROM information_schema.columns, (VALUES ('bigint', 'int'), ('bigserial', 'int'), ('integer', 'int'), ('interval%', 'int'), ('serial', 'int'), ('smallint', 'int'), ('boolean', 'bool'), ('numeric%', 'float'), ('double precision', 'float'), ('real', 'float'), ('bytea', 'str'), ('character%', 'bool'), ('text', 'str'), ('date', 'timestamp'), ('time%', 'timestamp'), ('interval%', 'interval'), ('bytea', 'blob') AS data_xlate (name, lookup) WHERE table_schema = \(table.table_schema) AND table_name = \(table.table_name) AND data_type LIKE data_xlate.name"
}
quote_start : "E'"
blob_start : "E'\\x"
blob_type : "bytea"
transformations : {
backslash : utils_lib.str_transform.char_tmpl { char : "\\" replacement : "\\\\" }
backspace : utils_lib.str_transform.char_tmpl { char : "\b" replacement : "\\b" }
formfeed : utils_lib.str_transform.char_tmpl { char : "\f" replacement : "\\f" }
newline : utils_lib.str_transform.char_tmpl { char : "\n" replacement : "\\n" }
null : utils_lib.str_transform.char_tmpl { char : "\x00" replacement : "\\0" }
quote : utils_lib.str_transform.char_tmpl { char : "'" replacement : "\\'" }
tab : utils_lib.str_transform.char_tmpl { char : "\t" replacement : "\\t" }
nonprint : utils_lib.str_transform.range_tmpl { start : "\x00" end : "\x1F" replacement : [ "\\x", utf8_0 ] }
unicode : utils_lib.str_transform.range_tmpl { start : "\u0100" end : "\uFFFF" replacement : [ "\\U", utf32] mode : mode_hex_upper }
}
allowed_expressions +: {
interval_const +: {
sql_expr : "INTERVAL '\(time Enforce Int)' SECONDS"
}
now +: {
sql_expr : "(NOW() \(If utc Then "AT TIME ZONE 'utc'" Else ""))"
}
str_agg +: {
sql_func : "STRING_AGG"
sql_extra_args : ", " & specials.s.sql_expr
}
}
}
sqlite : Template sql_provider_with_union_literal {
table_discovery +: {
sql_query : "SELECT name AS \"ATTRNAME\", tbl_name AS sql_from FROM sqlite_master WHERE type = 'table'"
}
column_discovery +: {
sql_query : "PRAGMA table_info(\"\(table.sql_from)\")"
# SQLite doesn't give us the power to control the format of the data we
# get back, so we get to do it locally. Fun.
value +orig:
For col : orig
Select col.name : sql_row_tmpl {
sql_expr : "\(table.sql_from).\(col.name)"
sql_type :
If col.type Is Null Then sql_types.str
Else If
col.type == "BOOLEAN" Then sql_types.bool
Else If
col.type == "INT" ||
col.type == "INTEGER" ||
col.type == "TINYINT" ||
col.type == "SMALLINT" ||
col.type == "MEDIUMINT" ||
col.type == "BIGINT" ||
col.type == "UNSIGNED BIG INT" ||
col.type == "INT2" ||
col.type == "INT8" Then sql_types.int
Else If
col.type == "REAL" ||
col.type == "DOUBLE" ||
col.type == "DOUBLE PRECISION" ||
col.type == "FLOAT" Then sql_types.float
Else If
col.type == "BLOB" Then sql_types.blob
Else sql_types.str
}
}
transformations : {
# This is kind of scary, but what's actually listed in the SQL92 spec and
# the SQLite tokenizer. It's unclear how it will deal with UTF-8
# surrogate pairs.
quote : utils_lib.str_transform.char_tmpl { char : "'" replacement : "''" }
}
allowed_expressions +: {
interval_const +: {
sql_expr : time Enforce Int
}
extract +: {
year : "Y"
month : "m"
day : "d"
hour : "H"
minute : "M"
second : "s"
sql_start : "CAST(STRFTIME('%"
sql_middle : "', "
sql_end : ") AS INTEGER)"
}
now +: {
sql_expr : "DATETIME('now', '\(If utc Then "utc" Else "localtime")')"
}
str_agg +: {
sql_func : "GROUP_CONCAT"
sql_extra_args : ", " & specials.s.sql_expr
}
str_slice +: {
sql_substr_syntax : "SUBSTR(" & specials.i.sql_expr & ", " & specials.s.sql_expr & ", " & specials.l.sql_expr & ")"
}
}
arithmetic_rules : sql_aritmetic_tmpl {
tt : {
ops : sql_arith_ops.sub.id
left_type : sql_types.timestamp.id
right_type : sql_types.timestamp.id
result_type : sql_types.interval
render : Template {
value : "(STRFTIME('%s', " & specials.l.sql_expr Enforce Str & ") - STRFTIME('%s', " & specials.r.sql_expr Enforce Str & "))"
}
}
tv : {
ops : sql_arith_ops.add.id B| sql_arith_ops.sub.id
left_type : sql_types.timestamp.id
right_type : sql_types.interval.id
result_type : sql_types.timestamp
render : Template {
value : "DATETIME(" & specials.l.sql_expr & ", '" & sql_op.symbol Enforce Str & "' || " & specials.r.sql_expr Enforce Str & " || ' seconds')"
}
}
}
}
}
{{{The default row template is empty, providing only the columns in the database.}}}
sql_row_tmpl : Template {}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy