<commit_before>;; ## SQL query compiler
;;
;; The query compile operates in a multi-step process. Compilation begins with
;; one of the `foo-query->sql` functions. The job of these functions is
;; basically to call `compile-term` on the first term of the query to get back
;; the "compiled" form of the query, and then to turn that into a complete SQL
;; query.
;;
;; The compiled form of a query consists of a map with two keys: `where`
;; and `params`. The `where` key contains SQL for querying that
;; particular predicate, written in such a way as to be suitable for placement
;; after a `WHERE` clause in the database. `params` contains, naturally, the
;; parameters associated with that SQL expression. For instance, a resource
;; query for `["=" ["node" "name"] "foo.example.com"]` will compile to:
;;
;;     {:where "certname_catalogs.certname = ?"
;;      :params ["foo.example.com"]}
;;
;; The `where` key is then inserted into a template query to return
;; the final result as a string of SQL code.
;;
;; The compiled query components can be combined by operators such as `AND` or
;; `OR`, which return the same sort of structure. Operators which accept other
;; terms as their arguments are responsible for compiling their arguments
;; themselves. To facilitate this, those functions accept as their first
;; argument a map from operator to compile function. This allows us to have a
;; different set of operators for resources and facts, or v1 and v2 resource
;; queries, while still sharing the implementation of the operators themselves.
;;
;; Other operators include the subquery operators, `in`, `extract`, and
;; `select-resources` or `select-facts`. The `select-foo` operators implement
;; subqueries, and are simply implemented by calling their corresponding
;; `foo-query->sql` function, which means they return a complete SQL query
;; rather than the compiled query map. The `extract` function knows how to
;; handle that, and is the only place those queries are allowed as arguments.
;; `extract` is used to select a particular column from the subquery. The
;; sibling operator to `extract` is `in`, which checks that the value of
;; a certain column from the table being queried is in the result set returned
;; by `extract`. Composed, these three operators provide a complete subquery
;; facility. For example, consider this fact query:
;;
;;     ["and"
;;      ["=" ["fact" "name"] "ipaddress"]
;;      ["in" "certname"
;;       ["extract" "certname"
;;        ["select-resources" ["and"
;;                             ["=" "type" "Class"]
;;                             ["=" "title" "apache"]]]]]]
;;
;; This will perform a query (via `select-resources`) for resources matching
;; `Class[apache]`. It will then pick out the `certname` from each of those,
;; and match against the `certname` of fact rows, returning those facts which
;; have a corresponding entry in the results of `select-resources` and which
;; are named `ipaddress`. Effectively, the semantics of this query are "find
;; the ipaddress of every node with Class[apache]".
;;
;; The resulting SQL from the `foo-query->sql` functions selects all the
;; columns. Thus consumers of those functions may need to wrap that query with
;; another `SELECT` to pull out only the desired columns. Similarly for
;; applying ordering constraints.
;;
(ns com.puppetlabs.puppetdb.query
  (:require [clojure.string :as string])
  (:use [com.puppetlabs.utils :only [parse-number]]
        [com.puppetlabs.puppetdb.scf.storage :only [db-serialize sql-as-numeric sql-array-query-string sql-regexp-match sql-regexp-array-match]]
        [com.puppetlabs.jdbc :only [valid-jdbc-query?]]
        [clojure.core.match :only [match]]))

(defn compile-term
  "Compile a single query term, using `ops` as the set of legal operators. This
  function basically just checks that the operator is known, and then
  dispatches to the function implementing it."
  [ops [op & args :as term]]
  (when-not (sequential? term)
    (throw (IllegalArgumentException. (format "%s is not well-formed: queries must be an array" term))))
  (when-not op
    (throw (IllegalArgumentException. (format "%s is not well-formed: queries must contain at least one operator" term))))
  (if-let [f (ops op)]
    (apply f args)
    (throw (IllegalArgumentException. (format "%s is not well-formed: query operator '%s' is unknown" term op)))))

(defn compile-boolean-operator*
  "Compile a term for the boolean operator `op` (AND or OR) applied to
  `terms`. This is accomplished by compiling each of the `terms` and then just
  joining their `where` terms with the operator. The params are just
  concatenated."
  [op ops & terms]
  {:pre  [(every? coll? terms)]
   :post [(string? (:where %))]}
  (when (empty? terms)
    (throw (IllegalArgumentException. (str op " requires at least one term"))))
  (let [compiled-terms (map #(compile-term ops %) terms)
        params (mapcat :params compiled-terms)
        query  (->> (map :where compiled-terms)
                    (map #(format "(%s)" %))
                    (string/join (format " %s " (string/upper-case op))))]
    {:where  query
     :params params}))

(def compile-and
  (partial compile-boolean-operator* "and"))

(def compile-or
  (partial compile-boolean-operator* "or"))

(defn negate-term*
  "Compiles `term` and returns the negated version of the query."
  [ops term]
  {:pre  [(sequential? term)]
   :post [(string? (:where %))]}
  (let [compiled-term (compile-term ops term)
        query (format "NOT (%s)" (:where compiled-term))]
    (assoc compiled-term :where query)))

(defn compile-not-v1
  "Compile a v1 NOT operator, applied to `terms`. This term is true if *every*
  argument term is false. The compilation is effectively to apply an OR
  operation to the terms, and then NOT that result."
  [ops & terms]
  {:pre  [(every? sequential? terms)]
   :post [(string? (:where %))]}
  (when (empty? terms)
    (throw (IllegalArgumentException. (str "not requires at least one term"))))
  (negate-term* ops (cons "or" terms)))

(defn compile-not-v2
  "Compile a v2 NOT operator, applied to `term`. This term simply negates the
  value of `term`. Basically this function just serves as error checking for
  `negate-term*`."
  [ops & terms]
  {:post [(string? (:where %))]}
  (when-not (= (count terms) 1)
    (throw (IllegalArgumentException. (format "'not' takes exactly one argument, but %d were supplied" (count terms)))))
  (negate-term* ops (first terms)))

(def fact-columns #{"certname" "name" "value"})

(def resource-columns #{"certname" "catalog" "resource" "type" "title" "tags" "exported" "sourcefile" "sourceline"})

(def node-columns #{"name" "deactivated"})

(def selectable-columns
  {:resource resource-columns
   :fact fact-columns
   :node node-columns})

(def subquery->type
  {"select-resources" :resource
   "select-facts" :fact})

(defn compile-extract
  "Compile an `extract` operator, selecting the given `field` from the compiled
  result of `subquery`, which must be a kind of `select` operator."
  [ops field subquery]
  {:pre [(string? field)
         (coll? subquery)]
   :post [(map? %)
          (string? (:where %))]}
  (let [[subselect & params] (compile-term ops subquery)
        subquery-type (subquery->type (first subquery))]
    (when-not subquery-type
      (throw (IllegalArgumentException. (format "The argument to extract must be a select operator, not '%s'" (first subquery)))))
    (when-not (get-in selectable-columns [subquery-type field])
      (throw (IllegalArgumentException. (format "Can't extract unknown %s field '%s'. Acceptable fields are: %s" (name subquery-type) field (string/join ", " (sort (selectable-columns subquery-type)))))))
    {:where (format "SELECT r1.%s FROM (%s) r1" field subselect)
     :params params}))

(defn compile-in
  "Compile an `in` operator, selecting rows for which the value of
  `field` appears in the result given by `subquery`, which must be an `extract`
  composed with a `select`."
  [kind ops field subquery]
  {:pre [(string? field)
         (coll? subquery)]
   :post [(map? %)
          (string? (:where %))]}
  (when-not (get-in selectable-columns [kind field])
    (throw (IllegalArgumentException. (format "Can't match on unknown %s field '%s' for 'in'. Acceptable fields are: %s" (name kind) field (string/join ", " (sort (selectable-columns kind)))))))
  (when-not (= (first subquery) "extract")
    (throw (IllegalArgumentException. (format "The subquery argument of 'in' must be an 'extract', not '%s'" (first subquery)))))
  (let [{:keys [where] :as compiled-subquery} (compile-term ops subquery)]
    (assoc compiled-subquery :where (format "%s IN (%s)" field where))))

(defn resource-query->sql
  "Compile a resource query, returning a vector containing the SQL and
  parameters for the query. All resource columns are selected, and no order is applied."
  [ops query]
  {:post [valid-jdbc-query? %]}
  (let [{:keys [where params]} (compile-term ops query)
        sql (format "SELECT certname_catalogs.certname, catalog_resources.catalog, catalog_resources.resource, catalog_resources.type, catalog_resources.title, catalog_resources.tags, catalog_resources.exported, catalog_resources.sourcefile, catalog_resources.sourceline FROM catalog_resources JOIN certname_catalogs ON certname_catalogs.catalog = catalog_resources.catalog WHERE %s"  where)]
    (apply vector sql params)))

(defn fact-query->sql
  "Compile a fact query, returning a vector containing the SQL and parameters
  for the query. All fact columns are selected, and no order is applied."
  [ops query]
  {:post [valid-jdbc-query? %]}
  (let [{:keys [where params]} (compile-term ops query)
        sql (format "SELECT %s FROM certname_facts WHERE %s" (string/join ", " (map #(str "certname_facts." %) fact-columns)) where)]
    (apply vector sql params)))

(defn node-query->sql
  "Compile a node query, returning a vector containing the SQL and parameters
  for the query. All node columns are selected, and no order is applied."
  [ops query]
  {:post [valid-jdbc-query? %]}
  (let [{:keys [where params]} (compile-term ops query)
        sql (format "SELECT %s FROM certnames WHERE %s" (string/join ", " node-columns) where)]
    (apply vector sql params)))<commit_msg>Change `resource-columns`, et al, into maps that include the table names<commit_after>;; ## SQL query compiler
;;
;; The query compile operates in a multi-step process. Compilation begins with
;; one of the `foo-query->sql` functions. The job of these functions is
;; basically to call `compile-term` on the first term of the query to get back
;; the "compiled" form of the query, and then to turn that into a complete SQL
;; query.
;;
;; The compiled form of a query consists of a map with two keys: `where`
;; and `params`. The `where` key contains SQL for querying that
;; particular predicate, written in such a way as to be suitable for placement
;; after a `WHERE` clause in the database. `params` contains, naturally, the
;; parameters associated with that SQL expression. For instance, a resource
;; query for `["=" ["node" "name"] "foo.example.com"]` will compile to:
;;
;;     {:where "certname_catalogs.certname = ?"
;;      :params ["foo.example.com"]}
;;
;; The `where` key is then inserted into a template query to return
;; the final result as a string of SQL code.
;;
;; The compiled query components can be combined by operators such as `AND` or
;; `OR`, which return the same sort of structure. Operators which accept other
;; terms as their arguments are responsible for compiling their arguments
;; themselves. To facilitate this, those functions accept as their first
;; argument a map from operator to compile function. This allows us to have a
;; different set of operators for resources and facts, or v1 and v2 resource
;; queries, while still sharing the implementation of the operators themselves.
;;
;; Other operators include the subquery operators, `in`, `extract`, and
;; `select-resources` or `select-facts`. The `select-foo` operators implement
;; subqueries, and are simply implemented by calling their corresponding
;; `foo-query->sql` function, which means they return a complete SQL query
;; rather than the compiled query map. The `extract` function knows how to
;; handle that, and is the only place those queries are allowed as arguments.
;; `extract` is used to select a particular column from the subquery. The
;; sibling operator to `extract` is `in`, which checks that the value of
;; a certain column from the table being queried is in the result set returned
;; by `extract`. Composed, these three operators provide a complete subquery
;; facility. For example, consider this fact query:
;;
;;     ["and"
;;      ["=" ["fact" "name"] "ipaddress"]
;;      ["in" "certname"
;;       ["extract" "certname"
;;        ["select-resources" ["and"
;;                             ["=" "type" "Class"]
;;                             ["=" "title" "apache"]]]]]]
;;
;; This will perform a query (via `select-resources`) for resources matching
;; `Class[apache]`. It will then pick out the `certname` from each of those,
;; and match against the `certname` of fact rows, returning those facts which
;; have a corresponding entry in the results of `select-resources` and which
;; are named `ipaddress`. Effectively, the semantics of this query are "find
;; the ipaddress of every node with Class[apache]".
;;
;; The resulting SQL from the `foo-query->sql` functions selects all the
;; columns. Thus consumers of those functions may need to wrap that query with
;; another `SELECT` to pull out only the desired columns. Similarly for
;; applying ordering constraints.
;;
(ns com.puppetlabs.puppetdb.query
  (:require [clojure.string :as string])
  (:use [com.puppetlabs.utils :only [parse-number keyset]]
        [com.puppetlabs.puppetdb.scf.storage :only [db-serialize sql-as-numeric sql-array-query-string sql-regexp-match sql-regexp-array-match]]
        [com.puppetlabs.jdbc :only [valid-jdbc-query?]]
        [clojure.core.match :only [match]]))

(defn compile-term
  "Compile a single query term, using `ops` as the set of legal operators. This
  function basically just checks that the operator is known, and then
  dispatches to the function implementing it."
  [ops [op & args :as term]]
  (when-not (sequential? term)
    (throw (IllegalArgumentException. (format "%s is not well-formed: queries must be an array" term))))
  (when-not op
    (throw (IllegalArgumentException. (format "%s is not well-formed: queries must contain at least one operator" term))))
  (if-let [f (ops op)]
    (apply f args)
    (throw (IllegalArgumentException. (format "%s is not well-formed: query operator '%s' is unknown" term op)))))

(defn compile-boolean-operator*
  "Compile a term for the boolean operator `op` (AND or OR) applied to
  `terms`. This is accomplished by compiling each of the `terms` and then just
  joining their `where` terms with the operator. The params are just
  concatenated."
  [op ops & terms]
  {:pre  [(every? coll? terms)]
   :post [(string? (:where %))]}
  (when (empty? terms)
    (throw (IllegalArgumentException. (str op " requires at least one term"))))
  (let [compiled-terms (map #(compile-term ops %) terms)
        params (mapcat :params compiled-terms)
        query  (->> (map :where compiled-terms)
                    (map #(format "(%s)" %))
                    (string/join (format " %s " (string/upper-case op))))]
    {:where  query
     :params params}))

(def compile-and
  (partial compile-boolean-operator* "and"))

(def compile-or
  (partial compile-boolean-operator* "or"))

(defn negate-term*
  "Compiles `term` and returns the negated version of the query."
  [ops term]
  {:pre  [(sequential? term)]
   :post [(string? (:where %))]}
  (let [compiled-term (compile-term ops term)
        query (format "NOT (%s)" (:where compiled-term))]
    (assoc compiled-term :where query)))

(defn compile-not-v1
  "Compile a v1 NOT operator, applied to `terms`. This term is true if *every*
  argument term is false. The compilation is effectively to apply an OR
  operation to the terms, and then NOT that result."
  [ops & terms]
  {:pre  [(every? sequential? terms)]
   :post [(string? (:where %))]}
  (when (empty? terms)
    (throw (IllegalArgumentException. (str "not requires at least one term"))))
  (negate-term* ops (cons "or" terms)))

(defn compile-not-v2
  "Compile a v2 NOT operator, applied to `term`. This term simply negates the
  value of `term`. Basically this function just serves as error checking for
  `negate-term*`."
  [ops & terms]
  {:post [(string? (:where %))]}
  (when-not (= (count terms) 1)
    (throw (IllegalArgumentException. (format "'not' takes exactly one argument, but %d were supplied" (count terms)))))
  (negate-term* ops (first terms)))

;; This map's keys are the queryable fields for facts, and the values are the
;;  corresponding table names where the fields reside
(def fact-columns {"certname" "certname_facts"
                   "name"     "certname_facts"
                   "value"    "certname_facts"})

;; This map's keys are the queryable fields for resources, and the values are the
;;  corresponding table names where the fields reside
(def resource-columns {"certname"   "certname_catalogs"
                       "catalog"    "catalog_resources"
                       "resource"   "catalog_resources"
                       "type"       "catalog_resources"
                       "title"      "catalog_resources"
                       "tags"       "catalog_resources"
                       "exported"   "catalog_resources"
                       "sourcefile" "catalog_resources"
                       "sourceline" "catalog_resources"})

;; This map's keys are the queryable fields for nodes, and the values are the
;;  corresponding table names where the fields reside
(def node-columns {"name"         "certnames"
                   "deactivated"  "certnames"})

(defn column-map->sql
  "Helper function that converts one of our column maps to a SQL string suitable
  for use in a SELECT"
  [col-map]
  (string/join ", "
    (for [[field table] col-map]
      (str table "." field))))


(def queryable-fields
  {:resource (keyset resource-columns)
   :fact     (keyset fact-columns)
   :node     (keyset node-columns)})

(def subquery->type
  {"select-resources" :resource
   "select-facts" :fact})

(defn compile-extract
  "Compile an `extract` operator, selecting the given `field` from the compiled
  result of `subquery`, which must be a kind of `select` operator."
  [ops field subquery]
  {:pre [(string? field)
         (coll? subquery)]
   :post [(map? %)
          (string? (:where %))]}
  (let [[subselect & params] (compile-term ops subquery)
        subquery-type (subquery->type (first subquery))]
    (when-not subquery-type
      (throw (IllegalArgumentException. (format "The argument to extract must be a select operator, not '%s'" (first subquery)))))
    (when-not (get-in queryable-fields [subquery-type field])
      (throw (IllegalArgumentException. (format "Can't extract unknown %s field '%s'. Acceptable fields are: %s" (name subquery-type) field (string/join ", " (sort (queryable-fields subquery-type)))))))
    {:where (format "SELECT r1.%s FROM (%s) r1" field subselect)
     :params params}))

(defn compile-in
  "Compile an `in` operator, selecting rows for which the value of
  `field` appears in the result given by `subquery`, which must be an `extract`
  composed with a `select`."
  [kind ops field subquery]
  {:pre [(string? field)
         (coll? subquery)]
   :post [(map? %)
          (string? (:where %))]}
  (when-not (get-in queryable-fields [kind field])
    (throw (IllegalArgumentException. (format "Can't match on unknown %s field '%s' for 'in'. Acceptable fields are: %s" (name kind) field (string/join ", " (sort (queryable-fields kind)))))))
  (when-not (= (first subquery) "extract")
    (throw (IllegalArgumentException. (format "The subquery argument of 'in' must be an 'extract', not '%s'" (first subquery)))))
  (let [{:keys [where] :as compiled-subquery} (compile-term ops subquery)]
    (assoc compiled-subquery :where (format "%s IN (%s)" field where))))

(defn resource-query->sql
  "Compile a resource query, returning a vector containing the SQL and
  parameters for the query. All resource columns are selected, and no order is applied."
  [ops query]
  {:post [valid-jdbc-query? %]}
  (let [{:keys [where params]} (compile-term ops query)
        sql (format "SELECT %s FROM catalog_resources JOIN certname_catalogs USING(catalog) WHERE %s" (column-map->sql resource-columns) where)]
    (apply vector sql params)))

(defn fact-query->sql
  "Compile a fact query, returning a vector containing the SQL and parameters
  for the query. All fact columns are selected, and no order is applied."
  [ops query]
  {:post [valid-jdbc-query? %]}
  (let [{:keys [where params]} (compile-term ops query)
        sql (format "SELECT %s FROM certname_facts WHERE %s" (column-map->sql fact-columns) where)]
    (apply vector sql params)))

(defn node-query->sql
  "Compile a node query, returning a vector containing the SQL and parameters
  for the query. All node columns are selected, and no order is applied."
  [ops query]
  {:post [valid-jdbc-query? %]}
  (let [{:keys [where params]} (compile-term ops query)
        sql (format "SELECT %s FROM certnames WHERE %s" (column-map->sql node-columns) where)]
    (apply vector sql params)))
{'fraction_non_alphanumeric': 0.12335334915980077, 'fraction_numerical': 0.0008378718056137411, 'mean_word_length': 3.70830593907517, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7134389', 'n_tokens_mistral': 6249, 'n_tokens_neox': 6059, 'n_words': 2587}