| |
These functions take the info for a param fetched by the functions above and add additional info about how that param
should be represented as SQL. (Specifically, they return information in this format:
{;; appropriate SQL that should be used to replace the param snippet, e.g. {{x}}
:replacement-snippet "= ?"
;; ; any prepared statement args (values for ? placeholders) needed for the replacement snippet
:prepared-statement-args [#t "2017-01-01"]}
| ( ns metabase.driver.sql.parameters.substitution
( :require
[ clojure.string :as str ]
[ metabase.driver :as driver ]
[ metabase.driver.common.parameters :as params ]
[ metabase.driver.common.parameters.dates :as params.dates ]
[ metabase.driver.common.parameters.operators :as params.ops ]
[ metabase.driver.sql.query-processor :as sql.qp ]
[ metabase.legacy-mbql.schema :as mbql.s ]
[ metabase.legacy-mbql.util :as mbql.u ]
[ metabase.lib.schema.common :as lib.schema.common ]
[ metabase.lib.schema.metadata :as lib.schema.metadata ]
[ metabase.lib.schema.parameter :as lib.schema.parameter ]
[ metabase.query-processor.error-type :as qp.error-type ]
[ metabase.query-processor.middleware.wrap-value-literals :as qp.wrap-value-literals ]
[ metabase.query-processor.util.add-alias-info :as add ]
[ metabase.util :as u ]
[ metabase.util.date-2 :as u.date ]
[ metabase.util.i18n :refer [ tru ] ]
[ metabase.util.malli :as mu ] )
( :import
( clojure.lang IPersistentVector Keyword )
( java.time.temporal Temporal )
( java.util UUID )
( metabase.driver.common.parameters Date DateRange DateTimeRange FieldFilter ReferencedCardQuery ReferencedQuerySnippet ) ) )
|
|
------------------------------------ ->prepared-substitution & default impls -------------------------------------
| |
Returns a PreparedStatementSubstitution (see schema below) for x and the given driver. This allows driver
specific parameters and SQL replacement text (usually just ?). The param value is already prepared and ready for
inlcusion in the query, such as what's needed for SQLite and timestamps.
| ( defmulti ->prepared-substitution
{ :added "0.34.0" :arglists ' ( [ driver x ] ) }
( fn [ driver x ] [ ( driver/dispatch-on-initialized-driver driver ) ( class x ) ] )
:hierarchy #' driver/hierarchy )
|
|
Represents the SQL string replace value (usually ?) and the typed parameter value
| ( def PreparedStatementSubstitution
[ :map
[ :sql-string :string ]
[ :param-values [ :maybe [ :sequential :any ] ] ] ] )
|
|
| ( mu/defn make-stmt-subs :- PreparedStatementSubstitution
"Create a `PreparedStatementSubstitution` map for `sql-string` and the `param-seq`"
[ sql-string param-seq ]
{ :sql-string sql-string
:param-values param-seq } )
|
|
Convert X to a replacement snippet info map by passing it to HoneySQL's format function.
| ( defn- honeysql->prepared-stmt-subs
[ driver x ]
( let [ honeysql ( sql.qp/->honeysql driver x )
[ snippet & args ] ( sql.qp/format-honeysql driver honeysql ) ]
( make-stmt-subs snippet args ) ) )
|
|
| ( mu/defmethod ->prepared-substitution [ :sql nil ] :- PreparedStatementSubstitution
[ driver _ ]
( honeysql->prepared-stmt-subs driver nil ) )
|
|
| ( mu/defmethod ->prepared-substitution [ :sql Object ] :- PreparedStatementSubstitution
[ driver obj ]
( honeysql->prepared-stmt-subs driver ( str obj ) ) )
|
|
| ( mu/defmethod ->prepared-substitution [ :sql Number ] :- PreparedStatementSubstitution
[ driver num ]
( honeysql->prepared-stmt-subs driver num ) )
|
|
| ( mu/defmethod ->prepared-substitution [ :sql Boolean ] :- PreparedStatementSubstitution
[ driver b ]
( honeysql->prepared-stmt-subs driver b ) )
|
|
| ( mu/defmethod ->prepared-substitution [ :sql Keyword ] :- PreparedStatementSubstitution
[ driver kwd ]
( honeysql->prepared-stmt-subs driver kwd ) )
|
|
| ( mu/defmethod ->prepared-substitution [ :sql Date ] :- PreparedStatementSubstitution
[ _driver date ]
( make-stmt-subs "?" [ date ] ) )
|
|
| ( mu/defmethod ->prepared-substitution [ :sql Temporal ] :- PreparedStatementSubstitution
[ driver t ]
( honeysql->prepared-stmt-subs driver t ) )
|
|
Returns a suitable temporal unit conversion keyword for field , param-type and the given driver.
The resulting keyword will be used to call the corresponding metabase.driver.sql.query-processor/date
implementation to convert the field .
Returns nil if the conversion is not necessary for this field and param-type combination.
Deprecated: use align-temporal-unit-with-param-type-and-value instead, as it has access to value .
| ( defmulti align-temporal-unit-with-param-type
{ :added "0.48.0" :deprecated "0.49.0" :arglists ' ( [ driver field param-type ] ) }
driver/dispatch-on-initialized-driver
:hierarchy #' driver/hierarchy )
|
|
Returns a suitable temporal unit conversion keyword for field , param-type , value and the given driver.
The resulting keyword will be used to call the corresponding metabase.driver.sql.query-processor/date
implementation to convert the field .
Returns nil if the conversion is not necessary for this field , param-type and value combination.
| ( defmulti align-temporal-unit-with-param-type-and-value
{ :added "0.49.0" :arglists ' ( [ driver field param-type value ] ) }
driver/dispatch-on-initialized-driver
:hierarchy #' driver/hierarchy )
|
|
| #_
( defmethod align-temporal-unit-with-param-type :default
[ _driver _field param-type ]
( when ( params.dates/date-type? param-type )
:day ) )
|
|
| ( defmethod align-temporal-unit-with-param-type-and-value :default
[ _driver _field param-type value ]
( when ( params.dates/date-type? param-type )
( if-let [ exclusion-type ( params.dates/exclusion-date-type param-type value ) ]
exclusion-type
:default ) ) )
|
|
------------------------------------------- ->replacement-snippet-info -------------------------------------------
| |
| ( def ^ :private ParamSnippetInfo
[ :map
[ :replacement-snippet { :optional true } :string ]
[ :prepared-statement-args { :optional true } [ :maybe [ :sequential :any ] ] ] ] )
|
|
Return information about how value should be converted to SQL, as a map with keys :replacement-snippet and
:prepared-statement-args .
(->replacement-snippet-info :h2 "ABC") -> {:replacement-snippet "?", :prepared-statement-args "ABC"}
| ( defmulti ->replacement-snippet-info
{ :added "0.33.4" :arglists ' ( [ driver value ] ) }
( fn [ driver v & _args ] [ ( driver/the-initialized-driver driver ) ( class v ) ] )
:hierarchy #' driver/hierarchy )
|
|
| ( defn- create-replacement-snippet
[ driver nil-or-obj ]
( let [ { :keys [ sql-string param-values ] } ( ->prepared-substitution driver nil-or-obj ) ]
{ :replacement-snippet sql-string
:prepared-statement-args param-values } ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql nil ]
[ driver this ]
( create-replacement-snippet driver this ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql Object ]
[ driver this ]
( create-replacement-snippet driver ( str this ) ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql Number ]
[ driver this ]
( create-replacement-snippet driver this ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql Boolean ]
[ driver this ]
( create-replacement-snippet driver this ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql Keyword ]
[ driver this ]
( if ( = this params/no-value )
{ :replacement-snippet "" }
( create-replacement-snippet driver this ) ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql UUID ]
[ _driver this ]
{ :replacement-snippet ( format "CAST('%s' AS uuid)" ( str this ) ) } )
|
|
| ( defmethod ->replacement-snippet-info [ :sql IPersistentVector ]
[ driver values ]
( let [ values ( map ( partial ->replacement-snippet-info driver ) values ) ]
{ :replacement-snippet ( str/join ", " ( map :replacement-snippet values ) )
:prepared-statement-args ( apply concat ( map :prepared-statement-args values ) ) } ) )
|
|
| ( mu/defn- maybe-parse-temporal-literal :- ( lib.schema.common/instance-of-class java.time.temporal.Temporal )
[ x ]
( condp instance? x
String ( u.date/parse x )
Temporal x
( throw ( ex-info ( tru "Don''t know how to parse {0} {1} as a temporal literal" ( class x ) ( pr-str x ) )
{ :type qp.error-type/invalid-parameter
:parameter x } ) ) ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql Date ]
[ driver { :keys [ s ] } ]
( create-replacement-snippet driver ( maybe-parse-temporal-literal s ) ) )
|
|
| ( defn- prepared-ts-subs [ driver operator date-str ]
( let [ { :keys [ sql-string param-values ] } ( ->prepared-substitution driver ( maybe-parse-temporal-literal date-str ) ) ]
{ :replacement-snippet ( str operator " " sql-string )
:prepared-statement-args param-values } ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql DateRange ]
[ driver { :keys [ start end ] } ]
( cond
( = start end )
( prepared-ts-subs driver \= start )
( nil? start )
( prepared-ts-subs driver \< end )
( nil? end )
( prepared-ts-subs driver \> start )
:else
( let [ [ start end ] ( map ( fn [ s ]
( ->prepared-substitution driver ( maybe-parse-temporal-literal s ) ) )
[ start end ] ) ]
{ :replacement-snippet ( format "BETWEEN %s AND %s" ( :sql-string start ) ( :sql-string end ) )
:prepared-statement-args ( concat ( :param-values start ) ( :param-values end ) ) } ) ) )
|
|
| ( defmethod ->replacement-snippet-info [ :sql DateTimeRange ]
[ driver { :keys [ start end ] } & [ field-identifier ] ]
( let [ [ start end ] ( map ( fn [ s ]
( when s
( ->prepared-substitution driver ( maybe-parse-temporal-literal s ) ) ) )
[ start end ] )
start-expr-native ( when start
( format "%s >= %s" field-identifier ( :sql-string start ) ) )
end-expr-native ( when end
( format "%s < %s" field-identifier ( :sql-string end ) ) ) ]
{ :replacement-snippet ( str/join " AND " ( remove nil? [ start-expr-native end-expr-native ] ) )
:prepared-statement-args ( into [ ]
( comp ( keep :param-values )
cat )
[ start end ] ) } ) )
|
|
------------------------------------- Field Filter replacement snippet info --------------------------------------
| |
| ( mu/defn- combine-replacement-snippet-maps :- ParamSnippetInfo
"Combine multiple `replacement-snippet-maps` into a single map using a SQL `AND` clause."
[ replacement-snippet-maps :- [ :maybe [ :sequential ParamSnippetInfo ] ] ]
{ :replacement-snippet ( str \( ( str/join " AND " ( map :replacement-snippet replacement-snippet-maps ) ) \) )
:prepared-statement-args ( mapcat :prepared-statement-args replacement-snippet-maps ) } )
|
|
for relative dates convert the param to a DateRange record type and call ->replacement-snippet-info on it
| ( mu/defn- date-range-field-filter->replacement-snippet-info :- ParamSnippetInfo
[ driver value ]
( ->> ( params.dates/date-string->range value )
params/map->DateRange
( ->replacement-snippet-info driver ) ) )
|
|
| ( mu/defn- field-filter->equals-clause-sql :- ParamSnippetInfo
[ driver value ]
( -> ( ->replacement-snippet-info driver value )
( update :replacement-snippet ( partial str "= " ) ) ) )
|
|
| ( mu/defn- field-filter-multiple-values->in-clause-sql :- ParamSnippetInfo
[ driver values ]
( -> ( ->replacement-snippet-info driver ( vec values ) )
( update :replacement-snippet ( partial format "IN (%s)" ) ) ) )
|
|
| ( mu/defn- honeysql->replacement-snippet-info :- ParamSnippetInfo
"Convert `hsql-form` to a replacement snippet info map by passing it to HoneySQL's `format` function."
[ driver hsql-form ]
( let [ [ snippet & args ] ( sql.qp/format-honeysql driver hsql-form ) ]
{ :replacement-snippet snippet
:prepared-statement-args args } ) )
|
|
| ( mu/defn- field->clause :- mbql.s/field
[ driver :- :keyword
field :- ::lib.schema.metadata/column
param-type :- ::lib.schema.parameter/type
value ]
[ :field
( u/the-id field )
{ :base-type ( :base-type field )
:temporal-unit ( align-temporal-unit-with-param-type-and-value driver field param-type value )
::add/source-table ( :table-id field )
::compiling-field-filter? true } ] )
|
|
| ( mu/defn- field->identifier :- ::lib.schema.common/non-blank-string
"Return an approprate snippet to represent this `field` in SQL given its param type.
For non-date Fields, this is just a quoted identifier; for dates, the SQL includes appropriately bucketing based on
the `param-type`."
[ driver field param-type value ]
( ->> ( field->clause driver field param-type value )
( sql.qp/->honeysql driver )
( honeysql->replacement-snippet-info driver )
:replacement-snippet ) )
|
|
Generate replacement snippet for field filter on datetime field. For details on how range is generated see
the docstring of [[params.dates/date-str->datetime-range]].
| ( defn- field-filter->replacement-snippet-for-datetime-field
[ driver { :keys [ field ] { :keys [ value type ] } :value :as _field-filter } ]
( letfn [ ( ->datetime-replacement-snippet-info
[ range ]
( ->replacement-snippet-info driver range ( field->identifier driver field type value ) ) ) ]
( -> ( params.dates/date-str->datetime-range value )
params/map->DateTimeRange
->datetime-replacement-snippet-info ) ) )
|
|
| ( mu/defn- field-filter->replacement-snippet-info :- ParamSnippetInfo
"Return `[replacement-snippet & prepared-statement-args]` appropriate for a field filter parameter."
[ driver { { param-type :type , value :value , :as params } :value , field :field , :as field-filter } ]
( assert ( :id field ) ( format "Why doesn't Field have an ID?\n%s" ( u/pprint-to-str field ) ) )
( letfn [ ( prepend-field [ x ]
( update x :replacement-snippet
( partial str ( field->identifier driver field param-type value ) " " ) ) )
( ->honeysql [ form ]
( sql.qp/->honeysql driver form ) ) ]
( cond
( params.ops/operator? param-type )
( ->> ( assoc params :target [ :template-tag ( field->clause driver field param-type value ) ] )
params.ops/to-clause
mbql.u/desugar-filter-clause
qp.wrap-value-literals/wrap-value-literals-in-mbql
->honeysql
( honeysql->replacement-snippet-info driver ) )
( params.dates/exclusion-date-type param-type value )
( let [ field-clause ( field->clause driver field param-type value ) ]
( ->> ( params.dates/date-string->filter value field-clause )
mbql.u/desugar-filter-clause
qp.wrap-value-literals/wrap-value-literals-in-mbql
->honeysql
( honeysql->replacement-snippet-info driver ) ) )
( and ( params.dates/date-type? param-type )
( isa? ( ( some-fn :effective-type :base-type ) field ) :type/DateTime ) )
( field-filter->replacement-snippet-for-datetime-field driver field-filter )
( params.dates/not-single-date-type? param-type ) ( prepend-field
( date-range-field-filter->replacement-snippet-info driver value ) )
( params.dates/date-type? param-type ) ( prepend-field
( field-filter->equals-clause-sql driver ( params/map->Date { :s value } ) ) )
( sequential? value ) ( prepend-field
( field-filter-multiple-values->in-clause-sql driver value ) )
:else ( prepend-field
( field-filter->equals-clause-sql driver value ) ) ) ) )
|
|
| ( mu/defmethod ->replacement-snippet-info [ :sql FieldFilter ]
[ driver :- :keyword
{ :keys [ value ] , :as field-filter } :- [ :map
[ :field ::lib.schema.metadata/column ]
[ :value :any ] ] ]
( cond
( = value params/no-value ) { :replacement-snippet "1 = 1" }
( sequential? value )
( combine-replacement-snippet-maps ( for [ v value ]
( ->replacement-snippet-info driver ( assoc field-filter :value v ) ) ) )
:else
( field-filter->replacement-snippet-info driver field-filter ) ) )
|
|
------------------------------------ Referenced Card replacement snippet info ------------------------------------
| |
| ( defmethod ->replacement-snippet-info [ :sql ReferencedCardQuery ]
[ _ { :keys [ query params ] } ]
{ :prepared-statement-args ( not-empty params )
:replacement-snippet ( sql.qp/make-nestable-sql query ) } )
|
|
---------------------------------- Native Query Snippet replacement snippet info ---------------------------------
| |
| ( defmethod ->replacement-snippet-info [ :sql ReferencedQuerySnippet ]
[ _ { :keys [ content ] } ]
{ :prepared-statement-args nil
:replacement-snippet content } )
|
|
| |