<commit_before>(ns metabase.driver.generic-sql.query-processor
  "The Query Processor is responsible for translating the Metabase Query Language into korma SQL forms."
  (:require [clojure.core.match :refer [match]]
            [clojure.tools.logging :as log]
            [korma.core :refer :all]
            [metabase.config :as config]
            [metabase.db :refer :all]
            [metabase.driver.generic-sql.native :as native]
            [metabase.driver.generic-sql.query-processor.annotate :as annotate]
            [metabase.driver.generic-sql.util :refer :all]
            (metabase.models [database :refer [Database]]
                             [field :refer [Field]]
                             [table :refer [Table]])))


(declare apply-form
         log-query
         post-process
         query-is-cumulative-sum?
         apply-cumulative-sum)

(def ^{:dynamic true, :private true} *query*
  "Query dictionary that we're currently processing"
  nil)

;; # INTERFACE

(defn process
  "Convert QUERY into a korma `select` form."
  [{{:keys [source_table] :as query} :query}]
  (when-not (zero? source_table)
    (binding [*query* query]
      (let [forms (->> (map apply-form query)                    ; call `apply-form` for each clause and strip out nil results
                       (filter identity)
                       (mapcat (fn [form] (if (vector? form) form ; some `apply-form` implementations return a vector of multiple korma forms; if only one was
                                             [form])))           ; returned wrap it in a vec so `mapcat` can build a flattened sequence of forms
                       doall)]
        (when (config/config-bool :mb-db-logging)
          (log-query query forms))
        `(let [entity# (table-id->korma-entity ~source_table)]
           (select entity# ~@forms))))))


(defn process-structured
  "Convert QUERY into a korma `select` form, execute it, and annotate the results."
  [query]
  {:pre [(integer? (:database query)) ; double check that the query being passed is valid
         (map? (:query query))
         (= (name (:type query)) "query")]}
  (->> (process query)
       eval
       (post-process query)
       (annotate/annotate query)))


(defn process-and-run
  "Process and run a query and return results."
  [{:keys [type] :as query}]
  ;; we know how to handle :native and :query (structured) type queries
  (case (keyword type)
      :native (native/process-and-run query)
      :query  (process-structured query)))


;; # IMPLEMENTATION

;; ## Query Clause Processors

(defmulti apply-form
  "Given a Query clause like

    {:aggregation [\"count\"]}

  call the matching implementation which should either return `nil` or translate it into a korma clause like

    (aggregate (count :*) :count)

  An implementation of `apply-form` may optionally return a vector of several forms to insert into the generated korma `select` form."
  (fn [[clause-name _]] clause-name))

;; ### `:aggregation`
;; ex.
;;
;;     ["distinct" 1412]
(defmethod apply-form :aggregation [[_ value]]
  (match value
    ["rows"]  nil                                  ; don't need to do anything special for `rows` - `select` selects all rows by default
    ["count"] `(aggregate (~'count :*) :count)     ; TODO - implement other types of aggregation (?)
    [_ _]     (let [[ag-type field-id] value       ; valid values to `korma.core/aggregate`: count, sum, avg, min, max, first, last
                    field (field-id->kw field-id)]
                (match (keyword ag-type)
                       :avg      `(aggregate (~'avg ~field) :avg)
                       :distinct `(aggregate (~'count (raw ~(format "DISTINCT(\"%s\")" (name field)))) :count)
                       :stddev   `(fields [(sqlfn :stddev ~field) :stddev])
                       :sum      `(aggregate (~'sum ~field) :sum)
                       :cum_sum  `[(fields ~field)     ; just make sure this field is returned + included in GROUP BY
                                   (group ~field)])))) ; cumulative sum happens in post-processing (see below)

;; ### `:breakout`
;; ex.
;;
;;     [1412 1413]
(defmethod apply-form :breakout [[_ field-ids]]
  (match field-ids
    []    nil ; empty clause
    [nil] nil ; empty clause
    _     (let [field-names (map field-id->kw field-ids)
                order-by-field-names (some->> (:order_by *query*) ; get set of names of all fields specified in `order_by`
                                              (map first)
                                              (map field-id->kw)
                                              set)]
            `[(group  ~@field-names)
              (fields ~@field-names)
              ~@(->> field-names                                                    ; Add an implicit `order :ASC` clause for every field specified in `breakout`
                     (filter (complement (partial contains? order-by-field-names))) ; that is *not* specified *explicitly* in `order_by`.
                     (map (fn [field-name]
                            `(order ~field-name :ASC))))])))

;; ### `:fields`
;; ex.
;;
;;     [1412 1413]
(defmethod apply-form :fields [[_ field-ids]]
  (let [field-names (map field-id->kw field-ids)]
    `(fields ~@field-names)))

;; ### `:filter`
;; ex.
;;
;;     ["AND"
;;       [">" 1413 1]
;;       [">=" 1412 4]]
(defn- filter-subclause->predicate
  "Given a filter SUBCLAUSE, return a Korma filter predicate form for use in korma `where`.

    (filter-subclause->predicate [\">\" 1413 1]) -> {:field_name [> 1]} "
  [[_ field-id :as subclause]]
  (if-not (> (count subclause) 4)
    ;; most filter clauses have 4 or fewer args and only 1 field-id, so handle those here
    {(field-id->kw field-id)
     (match subclause
       [">"  _ value]        ['>    value]
       ["<"  _ value]        ['<    value]
       [">=" _ value]        ['>=   value]
       ["<=" _ value]        ['<=   value]
       ["="  _ value]        ['=    value]
       ["!=" _ value]        ['not= value]
       ["NOT_NULL" _]        ['not= nil]
       ["IS_NULL" _]         ['=    nil]
       ["BETWEEN" _ min max] ['between [min max]])}
    ;; for the more obscure filtering cases we have another approach
    (let [[operation lat-field-id lon-field-id lat-max lon-min lat-min lon-max] subclause
          lat-kw (field-id->kw lat-field-id)
          lon-kw (field-id->kw lon-field-id)]
      (case (keyword operation)
        :INSIDE `(~'and
                   {~lat-kw [~'< ~lat-max]}
                   {~lat-kw [~'> ~lat-min]}
                   {~lon-kw [~'< ~lon-max]}
                   {~lon-kw [~'> ~lon-min]})))))

(defmethod apply-form :filter [[_ filter-clause]]
  (match filter-clause
    nil                  nil ; empty clause
    [nil nil]            nil ; empty clause
    []                   nil ; empty clause
    ["AND" & subclauses] `(where (~'and ~@(map filter-subclause->predicate
                                               subclauses)))<commit_msg>rewrite (filter-subclause->predicate) to be much cleaner thanks to Cam.<commit_after>(ns metabase.driver.generic-sql.query-processor
  "The Query Processor is responsible for translating the Metabase Query Language into korma SQL forms."
  (:require [clojure.core.match :refer [match]]
            [clojure.tools.logging :as log]
            [korma.core :refer :all]
            [metabase.config :as config]
            [metabase.db :refer :all]
            [metabase.driver.generic-sql.native :as native]
            [metabase.driver.generic-sql.query-processor.annotate :as annotate]
            [metabase.driver.generic-sql.util :refer :all]
            (metabase.models [database :refer [Database]]
                             [field :refer [Field]]
                             [table :refer [Table]])))


(declare apply-form
         log-query
         post-process
         query-is-cumulative-sum?
         apply-cumulative-sum)

(def ^{:dynamic true, :private true} *query*
  "Query dictionary that we're currently processing"
  nil)

;; # INTERFACE

(defn process
  "Convert QUERY into a korma `select` form."
  [{{:keys [source_table] :as query} :query}]
  (when-not (zero? source_table)
    (binding [*query* query]
      (let [forms (->> (map apply-form query)                    ; call `apply-form` for each clause and strip out nil results
                       (filter identity)
                       (mapcat (fn [form] (if (vector? form) form ; some `apply-form` implementations return a vector of multiple korma forms; if only one was
                                             [form])))           ; returned wrap it in a vec so `mapcat` can build a flattened sequence of forms
                       doall)]
        (when (config/config-bool :mb-db-logging)
          (log-query query forms))
        `(let [entity# (table-id->korma-entity ~source_table)]
           (select entity# ~@forms))))))


(defn process-structured
  "Convert QUERY into a korma `select` form, execute it, and annotate the results."
  [query]
  {:pre [(integer? (:database query)) ; double check that the query being passed is valid
         (map? (:query query))
         (= (name (:type query)) "query")]}
  (->> (process query)
       eval
       (post-process query)
       (annotate/annotate query)))


(defn process-and-run
  "Process and run a query and return results."
  [{:keys [type] :as query}]
  ;; we know how to handle :native and :query (structured) type queries
  (case (keyword type)
      :native (native/process-and-run query)
      :query  (process-structured query)))


;; # IMPLEMENTATION

;; ## Query Clause Processors

(defmulti apply-form
  "Given a Query clause like

    {:aggregation [\"count\"]}

  call the matching implementation which should either return `nil` or translate it into a korma clause like

    (aggregate (count :*) :count)

  An implementation of `apply-form` may optionally return a vector of several forms to insert into the generated korma `select` form."
  (fn [[clause-name _]] clause-name))

;; ### `:aggregation`
;; ex.
;;
;;     ["distinct" 1412]
(defmethod apply-form :aggregation [[_ value]]
  (match value
    ["rows"]  nil                                  ; don't need to do anything special for `rows` - `select` selects all rows by default
    ["count"] `(aggregate (~'count :*) :count)     ; TODO - implement other types of aggregation (?)
    [_ _]     (let [[ag-type field-id] value       ; valid values to `korma.core/aggregate`: count, sum, avg, min, max, first, last
                    field (field-id->kw field-id)]
                (match (keyword ag-type)
                       :avg      `(aggregate (~'avg ~field) :avg)
                       :distinct `(aggregate (~'count (raw ~(format "DISTINCT(\"%s\")" (name field)))) :count)
                       :stddev   `(fields [(sqlfn :stddev ~field) :stddev])
                       :sum      `(aggregate (~'sum ~field) :sum)
                       :cum_sum  `[(fields ~field)     ; just make sure this field is returned + included in GROUP BY
                                   (group ~field)])))) ; cumulative sum happens in post-processing (see below)

;; ### `:breakout`
;; ex.
;;
;;     [1412 1413]
(defmethod apply-form :breakout [[_ field-ids]]
  (match field-ids
    []    nil ; empty clause
    [nil] nil ; empty clause
    _     (let [field-names (map field-id->kw field-ids)
                order-by-field-names (some->> (:order_by *query*) ; get set of names of all fields specified in `order_by`
                                              (map first)
                                              (map field-id->kw)
                                              set)]
            `[(group  ~@field-names)
              (fields ~@field-names)
              ~@(->> field-names                                                    ; Add an implicit `order :ASC` clause for every field specified in `breakout`
                     (filter (complement (partial contains? order-by-field-names))) ; that is *not* specified *explicitly* in `order_by`.
                     (map (fn [field-name]
                            `(order ~field-name :ASC))))])))

;; ### `:fields`
;; ex.
;;
;;     [1412 1413]
(defmethod apply-form :fields [[_ field-ids]]
  (let [field-names (map field-id->kw field-ids)]
    `(fields ~@field-names)))

;; ### `:filter`
;; ex.
;;
;;     ["AND"
;;       [">" 1413 1]
;;       [">=" 1412 4]]
(defn- filter-subclause->predicate
  "Given a filter SUBCLAUSE, return a Korma filter predicate form for use in korma `where`.

    (filter-subclause->predicate [\">\" 1413 1]) -> {:field_name [> 1]} "
  [subclause]
  (match subclause
    ["INSIDE" lat-field lon-field lat-max lon-min lat-min lon-max] (let [lat-kw (field-id->kw lat-field)
                                                                         lon-kw (field-id->kw lon-field)]
                                                                     `(~'and ~@[{lat-kw ['< lat-max]}
                                                                                {lat-kw ['> lat-min]}
                                                                                {lon-kw ['< lon-max]}
                                                                                {lon-kw ['> lon-min]}]))
    [_ field-id & _] {(field-id->kw field-id)
                      (match subclause
                        [">"  _ value]        ['>    value]
                        ["<"  _ value]        ['<    value]
                        [">=" _ value]        ['>=   value]
                        ["<=" _ value]        ['<=   value]
                        ["="  _ value]        ['=    value]
                        ["!=" _ value]        ['not= value]
                        ["NOT_NULL" _]        ['not= nil]
                        ["IS_NULL" _]         ['=    nil]
                        ["BETWEEN" _ min max] ['between [min max]])}))

(defmethod apply-form :filter [[_ filter-clause]]
  (match filter-clause
    nil                  nil ; empty clause
    [nil nil]            nil ; empty clause
    []                   nil ; empty clause
    ["AND" & subclauses] `(where (~'and ~@(map filter-subclause->predicate
                                               subclauses)))
{'fraction_non_alphanumeric': 0.1351862223466816, 'fraction_numerical': 0.005250630075609073, 'mean_word_length': 1.7193984389872454, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 65, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 45, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2975206', 'n_tokens_mistral': 4212, 'n_tokens_neox': 4007, 'n_words': 1237}