query.clj 36.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
(ns puppetlabs.puppetdb.query
  "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 \"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 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."
  (:require [clojure.string :as str]
64
            [puppetlabs.i18n.core :as i18n]
65
            [puppetlabs.kitchensink.core :as kitchensink]
66 67 68
            [puppetlabs.puppetdb.jdbc :as jdbc]
            [puppetlabs.puppetdb.honeysql :as h]
            [puppetlabs.puppetdb.utils :as utils]
69
            [puppetlabs.puppetdb.time :refer [to-timestamp]]
70
            [puppetlabs.kitchensink.core :refer [parse-number keyset valset order-by-expr?]]
71
            [puppetlabs.puppetdb.scf.storage-utils :as sutils
72 73
             :refer [db-serialize sql-as-numeric sql-array-query-string
                     legacy-sql-regexp-match sql-regexp-array-match]]
74 75
            [puppetlabs.puppetdb.jdbc
             :refer [valid-jdbc-query? limited-query-to-vec query-to-vec paged-sql count-sql get-result-count]]
76
            [puppetlabs.puppetdb.query.paging :refer [requires-paging?]]
77
            [clojure.core.match :refer [match]]
78
            [schema.core :as s]
79
            [puppetlabs.puppetdb.utils :as utils]))
80

81 82 83 84 85 86 87 88 89
(defn wrap-with-supported-fns
  [schema]
  (merge schema
         {(s/optional-key :count) s/Int
          (s/optional-key :min) s/Any
          (s/optional-key :max) s/Any
          (s/optional-key :avg) s/Any
          (s/optional-key :sum) s/Any}))

90 91 92 93
(defn execute-paged-query*
  "Helper function to executed paged queries.  Builds up the paged sql string,
  executes the query, and returns map containing the `:result` key and an
  optional `:count` key."
94
  [fail-limit query {:keys [limit offset order_by include_total] :as paging-options}]
95 96 97 98
  {:pre [(and (integer? fail-limit) (>= fail-limit 0))
         (valid-jdbc-query? query)
         ((some-fn nil? integer?) limit)
         ((some-fn nil? integer?) offset)
99 100
         ((some-fn nil? sequential?) order_by)
         (every? order-by-expr? order_by)]
101 102 103 104 105 106
   :post [(map? %)
          (vector? (:result %))
          ((some-fn nil? integer?) (:count %))]}
  (let [[sql & params] (if (string? query) [query] query)
        paged-sql      (paged-sql sql paging-options)
        result         {:result
107 108 109 110 111 112 113
                        (limited-query-to-vec
                         fail-limit
                         (apply vector paged-sql params))}]
    ;; TODO: this could also be implemented using `COUNT(*) OVER()`,
    ;; which would allow us to get the results and the count via a
    ;; single query (rather than two separate ones).  Need to do
    ;; some benchmarking to see which is faster.
114
    (if include_total
115 116 117
      (assoc result :count
             (get-result-count (apply vector (count-sql sql) params)))
      result)))
118 119 120 121 122 123 124 125

(defn execute-query*
  "Helper function to executed non-paged queries.  Returns a map containing the
  `:result` key."
  [fail-limit query]
  {:pre [(and (integer? fail-limit) (>= fail-limit 0))
         (valid-jdbc-query? query)]
   :post [(map? %)
126
          (vector? (:result %))]}
127 128
  {:result (limited-query-to-vec fail-limit query)})

129 130 131 132 133
(defn execute-query
  "Given a query and a map of paging options, adds the necessary SQL for
  implementing the paging, executes the query, and returns a map containing
  the results and metadata.

134
  The return value will contain a key `:result`, whose value is a vector of
135 136 137 138
  the query results.  If the paging options indicate that a 'total record
  count' should be returned, then the map will also include a key `:count`,
  whose value is an integer indicating the total number of results available."
  ([query paging-options] (execute-query 0 query paging-options))
139
  ([fail-limit query {:keys [limit offset order_by] :as paging-options}]
140 141 142 143 144 145 146 147
     {:pre [((some-fn string? sequential?) query)]
      :post [(map? %)
             (vector? (:result %))
             ((some-fn nil? integer?) (:count %))]}
     (let [sql-and-params (if (string? query) [query] query)]
       (if (requires-paging? paging-options)
         (execute-paged-query* fail-limit sql-and-params paging-options)
         (execute-query* fail-limit sql-and-params)))))
148

149 150 151 152 153
(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]]
154 155 156 157 158 159

  (cond
    (empty? term)
    {:where nil :params nil}

    (not op)
160 161 162
    (throw (IllegalArgumentException.
            (i18n/tru "{0} is not well-formed: queries must contain at least one operator"
                      (vec term))))
163 164 165 166

    :else
    (if-let [f (ops op)]
      (apply f args)
167 168 169
      (throw (IllegalArgumentException.
              (i18n/tru "{0} is not well-formed: query operator ''{1}'' is unknown"
                        (vec term) op))))))
170 171

(defn compile-boolean-operator*
172 173 174 175
  "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."
176 177 178 179
  [op ops & terms]
  {:pre  [(every? coll? terms)]
   :post [(string? (:where %))]}
  (when (empty? terms)
180 181
    (throw (IllegalArgumentException.
            (i18n/tru "{0} requires at least one term" op))))
182 183 184 185
  (let [compiled-terms (map #(compile-term ops %) terms)
        params (mapcat :params compiled-terms)
        query  (->> (map :where compiled-terms)
                    (map #(format "(%s)" %))
186
                    (str/join (format " %s " (str/upper-case op))))]
187
    {:where  query
188 189 190 191 192 193 194 195
     :params params}))

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

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

196 197 198 199 200 201 202 203 204
(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)))

205 206
(defn compile-not
  "Compile a NOT operator, applied to `term`. This term simply negates the
207 208
  value of `term`. Basically this function just serves as error checking for
  `negate-term*`."
209
  [version ops & terms]
210
  {:post [(string? (:where %))]}
211
  (when-not (= (count terms) 1)
212 213
    (throw (IllegalArgumentException.
            (i18n/tru "''not'' takes exactly one argument, but {0} were supplied" (count terms)))))
214
  (negate-term* ops (first terms)))
215

216 217
;; This map's keys are the queryable fields for facts, and the values are the
;;  corresponding table names where the fields reside
218 219 220 221
(def fact-columns {"certname"         "facts"
                   "name"             "facts"
                   "value"            "facts"
                   "environment"      "facts"})
222

223 224 225 226
;; This map's keys are the queryable fields for factsets, and the values are the
;;  corresponding table names where the fields reside
(def factset-columns {"certname" "factsets"
                      "environment" "factsets"
227
                      "timestamp" "factsets"
228
                      "producer_timestamp" "factsets"
229
                      "hash" "factsets"})
230

231 232
;; This map's keys are the queryable fields for resources, and the values are the
;;  corresponding table names where the fields reside
233
(def resource-columns {"certname"   "catalogs"
234
                       "environment" "catalog_resources"
235 236 237 238 239 240
                       "catalog"    "catalog_resources"
                       "resource"   "catalog_resources"
                       "type"       "catalog_resources"
                       "title"      "catalog_resources"
                       "tags"       "catalog_resources"
                       "exported"   "catalog_resources"
241 242 243
                       "file"       "catalog_resources"
                       "line"       "catalog_resources"})

244 245 246 247 248 249
(def event-columns
  {"certname"               ["reports"]
   "configuration_version"  ["reports"]
   "start_time"             ["reports" "run_start_time"]
   "end_time"               ["reports" "run_end_time"]
   "receive_time"           ["reports" "report_receive_time"]
250
   "hash"                   ["reports" "report"]
251 252 253 254 255 256 257 258 259 260 261
   "status"                 ["resource_events"]
   "timestamp"              ["resource_events"]
   "resource_type"          ["resource_events"]
   "resource_title"         ["resource_events"]
   "property"               ["resource_events"]
   "new_value"              ["resource_events"]
   "old_value"              ["resource_events"]
   "message"                ["resource_events"]
   "file"                   ["resource_events"]
   "line"                   ["resource_events"]
   "containment_path"       ["resource_events"]
262
   "containing_class"       ["resource_events"]
Wyatt Alt's avatar
Wyatt Alt committed
263
   "environment"            ["environments"]})
264

265 266 267 268 269 270 271 272
(def resource-event-columns
  {"certname"               ["latest_events"]
   "configuration_version"  ["latest_events"]
   "run_start_time"         ["latest_events"]
   "run_end_time"           ["latest_events"]
   "report_receive_time"    ["latest_events"]
   "hash"                   ["latest_events" "report"]
   "status"                 ["latest_events"]
273
   "corrective_change"      ["latest_events"]
274 275 276 277 278 279 280 281 282 283 284
   "timestamp"              ["latest_events"]
   "resource_type"          ["latest_events"]
   "resource_title"         ["latest_events"]
   "property"               ["latest_events"]
   "new_value"              ["latest_events"]
   "old_value"              ["latest_events"]
   "message"                ["latest_events"]
   "file"                   ["latest_events"]
   "line"                   ["latest_events"]
   "containment_path"       ["latest_events"]
   "containing_class"       ["latest_events"]
Wyatt Alt's avatar
Wyatt Alt committed
285
   "environment"            ["latest_events" "environment"]})
286

287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307
(def resource-event-in-columns
  {"certname"               ["reports"]
   "configuration_version"  ["reports"]
   "run_start_time"         ["reports" "start_time"]
   "run_end_time"           ["reports" "end_time"]
   "report_receive_time"    ["reports" "receive_time"]
   "hash"                   ["reports"]
   "status"                 ["resource_events"]
   "timestamp"              ["resource_events"]
   "resource_type"          ["resource_events"]
   "resource_title"         ["resource_events"]
   "property"               ["resource_events"]
   "new_value"              ["resource_events"]
   "old_value"              ["resource_events"]
   "message"                ["resource_events"]
   "file"                   ["resource_events"]
   "line"                   ["resource_events"]
   "containment_path"       ["resource_events"]
   "containing_class"       ["resource_events"]
   "environment"            ["environments"]})

308 309 310 311 312 313 314 315 316
(def report-columns
  "Return the queryable set of fields and corresponding table names where they reside"
  {"hash"                  "reports"
   "certname"              "reports"
   "puppet_version"        "reports"
   "report_format"         "reports"
   "configuration_version" "reports"
   "start_time"            "reports"
   "end_time"              "reports"
317
   "producer_timestamp"    "reports"
318 319 320 321 322
   "receive_time"          "reports"
   "transaction_uuid"      "reports"
   "environment"           "reports"
   "status"                "reports"})

323 324 325 326 327 328
(defn qualified-column
  "given a field and one of the column maps above, produce the fully qualified
   column name"
  [field columns]
  (let [source-table (first (utils/vector-maybe (get columns field)))]
    (format "%s.%s" source-table field)))
329

330 331 332 333
(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]
334
  (str/join ", "
335 336
            (for [[field table] col-map]
              (str table "." field))))
337

338 339 340 341 342
(defmulti queryable-fields
  "This function takes a query type (:resource, :fact, :node) and a query
   API version number, and returns a set of strings which are the names the
   fields that are legal to query"
  (fn [query-type query-api-version] query-type))
343

344 345
(defmethod queryable-fields :resource
  [_ query-api-version]
346
  (keyset resource-columns))
347

348 349 350 351
(defmethod queryable-fields :fact
  [_ _]
  (keyset fact-columns))

352 353 354 355
(defmethod queryable-fields :event
  [_ _]
  (keyset event-columns))

356 357 358 359
(defmethod queryable-fields :report
  [_ _]
  (keyset report-columns))

360
(def subquery->type
361 362
  {"select_resources" :resource
   "select_facts"     :fact})
363

364 365
(defn compile-extract
  "Compile an `extract` operator, selecting the given `field` from the compiled
366
  result of `subquery`, which must be a kind of `select` operator."
367
  [query-api-version ops field subquery]
368
  {:pre [(or (string? field) (vector? field))
369 370 371 372 373 374
         (coll? subquery)]
   :post [(map? %)
          (string? (:where %))]}
  (let [[subselect & params] (compile-term ops subquery)
        subquery-type (subquery->type (first subquery))]
    (when-not subquery-type
375 376 377
      (throw (IllegalArgumentException.
              (i18n/tru "The argument to extract must be a select operator, not ''{0}''"
                        (first subquery)))))
378
    (when-not (get (queryable-fields subquery-type query-api-version) field)
379 380 381
      (throw (IllegalArgumentException.
              (i18n/tru "Can't extract unknown {0} field ''{1}''. Acceptable fields are: {2}"
                        (name subquery-type) field (str/join ", " (sort (queryable-fields subquery-type query-api-version)))))))
382 383 384
    {:where (format "SELECT r1.%s FROM (%s) r1" field subselect)
     :params params}))

385 386 387
(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`
388
  composed with a `select`."
389
  [kind query-api-version ops field subquery]
390
  {:pre [(or (string? field) (vector? field))
391 392 393
         (coll? subquery)]
   :post [(map? %)
          (string? (:where %))]}
394
  (when-not (get (queryable-fields kind query-api-version) field)
395
    (throw (IllegalArgumentException.
396 397
             (i18n/tru "Can''t match on unknown {0} field ''{1}'' for ''in''. Acceptable fields are: {2}"
                       (name kind) field (str/join ", " (sort (queryable-fields kind query-api-version)))))))
398
  (when-not (= (first subquery) "extract")
399
    (throw (IllegalArgumentException.
400 401
            (i18n/tru "The subquery argument of ''in'' must be an ''extract'', not ''{0}''"
                      (first subquery)))))
402 403 404 405
  (let [{:keys [where] :as compiled-subquery} (compile-term ops subquery)
        columns (case kind
                  :fact fact-columns
                  :resource resource-columns
406
                  :event resource-event-in-columns
407 408 409 410 411
                  :report report-columns
                  :factset factset-columns)
        qualified-field (qualified-column field columns)]
    (assoc compiled-subquery
           :where (format "%s IN (%s)" qualified-field where))))
412 413

(defn resource-query->sql
414
  "Compile a resource query, returning a vector containing the SQL and
415
  parameters for the query. All resource columns are selected, and no order is applied."
416
  [ops query]
417
  {:post [(valid-jdbc-query? %)]}
418
  (let [{:keys [where params]} (compile-term ops query)
419
        sql (format "SELECT %s
420
                       FROM (SELECT %s as catalog, e.environment, certnames.certname, resource,
421
                                    type, title, tags, exported, file, line
422 423 424 425 426
                             FROM catalog_resources cr
                             INNER JOIN certnames ON certnames.id = cr.certname_id
                             INNER JOIN catalogs c ON c.certname = certnames.certname
                             LEFT OUTER JOIN environments e ON c.environment_id = e.id) AS catalog_resources
                       JOIN catalogs ON catalog_resources.certname = catalogs.certname
427
                     WHERE %s"
428 429 430
                    (column-map->sql resource-columns)
                    (sutils/sql-hash-as-str "c.hash")
                    where)]
431 432 433
    (apply vector sql params)))

(defn fact-query->sql
434
  "Compile a fact query, returning a vector containing the SQL and parameters
435
  for the query. All fact columns are selected, and no order is applied."
436
  [ops query]
437
  {:post [(valid-jdbc-query? %)]}
438
  (let [{:keys [where params]} (compile-term ops query)
439
        sql (format "SELECT %s FROM (
440 441
                      SELECT fs.certname,
                             fp.name as name,
442
                             fv.value,
Wyatt Alt's avatar
Wyatt Alt committed
443
                             env.environment
444 445 446
                      FROM factsets fs
                        INNER JOIN facts as f on fs.id = f.factset_id
                        INNER JOIN fact_values as fv on f.fact_value_id = fv.id
447
                        INNER JOIN fact_paths as fp on f.fact_path_id = fp.id
448
                        LEFT OUTER JOIN environments as env on fs.environment_id = env.id
449 450
                      WHERE depth = 0) AS facts
                    WHERE %s" (column-map->sql fact-columns) where)]
451 452
    (apply vector sql params)))

453 454 455 456 457
(defn certname-names-query [active]
  (if active
    "SELECT name FROM certnames WHERE deactivated IS NULL AND expired IS NULL"
    "SELECT name FROM certnames WHERE deactivated IS NOT NULL OR expired IS NOT NULL") )

458 459
(defn compile-resource-equality
  "Compile an = operator for a resource query. `path` represents the field
460
  to query against, and `value` is the value."
461
  [version & [path value :as args]]
462 463 464
  {:post [(map? %)
          (:where %)]}
  (when-not (= (count args) 2)
465 466 467
    (throw (IllegalArgumentException.
            (i18n/tru "= requires exactly two arguments, but {0} were supplied"
                      (count args)))))
468 469 470 471 472 473 474 475 476 477 478
  (match [path]
         ;; tag join. Tags are case-insensitive but always lowercase, so
         ;; lowercase the query value.
         ["tag"]
         {:where  (sql-array-query-string "tags")
          :params [(str/lower-case value)]}

         ;; node join.
         ["certname"]
         {:where  "catalogs.certname = ?"
          :params [value]}
479

480 481 482
         ["environment"]
         {:where  "catalog_resources.environment = ?"
          :params [value]}
483

484 485
         ;; {in,}active nodes.
         [["node" "active"]]
486
         {:where (format "catalogs.certname IN (%s)" (certname-names-query value))}
487

488 489 490 491
         ;; param joins.
         [["parameter" (name :guard string?)]]
         {:where  "catalog_resources.resource IN (SELECT rp.resource FROM resource_params rp WHERE rp.name = ? AND rp.value = ?)"
          :params [name (db-serialize value)]}
492

493 494 495 496
         ;; metadata match.
         [(metadata :guard #{"catalog" "resource" "type" "title" "tags" "exported" "file" "line"})]
         {:where  (format "catalog_resources.%s = ?" metadata)
          :params [value]}
497

498 499
         ;; ...else, failure
         :else (throw (IllegalArgumentException.
500 501
                       (i18n/tru "''{0}'' is not a queryable object for resources in the version {1} API"
                                 path (last (name version)))))))
502 503 504

(defn compile-resource-regexp
  "Compile an '~' predicate for a resource query, which does regexp matching.
505 506
  This is done by leveraging the correct database-specific regexp syntax to
  return only rows where the supplied `path` match the given `pattern`."
507
  [version & [path value :as args]]
508 509
  {:post [(map? %)
          (:where %)]}
510 511
  (match [path]
         ["tag"]
512
         {:where (h/sqlraw->str
513
                   (sql-regexp-array-match "tags"))
514
          :params [value]}
515

516 517
         ;; node join.
         ["certname"]
518
         {:where (legacy-sql-regexp-match "catalogs.certname")
519
          :params [value]}
520

521
         ["environment"]
522
         {:where (legacy-sql-regexp-match "catalog_resources.environment")
523
          :params [value]}
524

525 526
         ;; metadata match.
         [(metadata :guard #{"type" "title" "exported" "file"})]
527
         {:where (legacy-sql-regexp-match (format "catalog_resources.%s" metadata))
528
          :params [value]}
529

530 531
         ;; ...else, failure
         :else (throw (IllegalArgumentException.
532 533
                       (i18n/tru "''{0}'' cannot be the target of a regexp match for version {1} of the resources API"
                                 path (last (name version)))))))
534

535
(defn compile-fact-equality
536 537
  "Compile an = predicate for a fact query. `path` represents the field to
  query against, and `value` is the value."
538 539 540 541 542 543
  [version]
  (fn [path value]
    {:post [(map? %)
            (:where %)]}
    (match [path]
           ["name"]
544
           {:where "facts.name = ?"
545 546 547
            :params [value]}

           ["value"]
548
           {:where "facts.value = ? and depth = 0"
549 550 551
            :params [(str value)]}

           ["certname"]
552
           {:where "facts.certname = ?"
553
            :params [value]}
554

555
           ["environment"]
556
           {:where "facts.environment = ?"
557
            :params [value]}
558

559
           [["node" "active"]]
560
           {:where (format "facts.certname IN (%s)" (certname-names-query value))}
561

562
           :else
563
           (throw (IllegalArgumentException.
564 565
                   (i18n/tru "{0} is not a queryable object for version {1} of the facts query api"
                             path (last (name version))))))))
566

567
(defn compile-fact-regexp
568 569 570
  "Compile an '~' predicate for a fact query, which does regexp matching.  This
  is done by leveraging the correct database-specific regexp syntax to return
  only rows where the supplied `path` match the given `pattern`."
571 572 573 574 575 576
  [version]
  (fn [path pattern]
    {:pre [(string? path)
           (string? pattern)]
     :post [(map? %)
            (string? (:where %))]}
577 578
    (let [query (fn [col] {:where (legacy-sql-regexp-match col)
                           :params [pattern]})]
579 580
      (match [path]
             ["certname"]
581
             (query "facts.certname")
582

583
             ["environment"]
584
             (query "facts.environment")
585

586
             ["name"]
587
             (query "facts.name")
588

589
             ["value"]
590 591
             {:where (format "%s and depth = 0"
                             (legacy-sql-regexp-match "facts.value"))
592
              :params [pattern]}
593 594

             :else (throw (IllegalArgumentException.
595 596
                           (i18n/tru "{0} is not a valid version {1} operand for regexp comparison"
                                     path (last (name version)))))))))
597

598
(defn compile-fact-inequality
599 600 601 602
  "Compile a numeric inequality for a fact query (> < >= <=). The `value` for
  comparison must be either a number or the string representation of a number.
  The value in the database will be cast to a float or an int for comparison,
  or will be NULL if it is neither."
603
  [op path value]
604
  {:pre [(string? path)]
605 606 607 608
   :post [(map? %)
          (string? (:where %))]}
  (if-let [number (parse-number (str value))]
    (match [path]
609
           ["value"]
610
           ;; This is like convert_to_numeric(facts.value) > 0.3
611
           {:where  (format "%s %s ? and depth = 0" (sql-as-numeric "facts.value") op)
612 613 614
            :params [number]}

           :else (throw (IllegalArgumentException.
615
                         (i18n/tru "{0} is not a queryable object for facts" path))))
616
    (throw (IllegalArgumentException.
617
            (i18n/tru "Value {0} must be a number for {1} comparison." value op)))))
618

619 620 621
(defn compile-resource-event-inequality
  "Compile a timestamp inequality for a resource event query (> < >= <=).
  The `value` for comparison must be coercible to a timestamp via
622
  `puppetlabs.puppetdb.time/to-timestamp` (e.g., an ISO-8601 compatible date-time string)."
623 624 625 626
  [& [op path value :as args]]
  {:post [(map? %)
          (string? (:where %))]}
  (when-not (= (count args) 3)
627 628 629
    (throw (IllegalArgumentException.
            (i18n/tru "{0} requires exactly two arguments, but {1} were supplied"
                      op (dec (count args))))))
630 631

  (let [timestamp-fields {"timestamp"           "resource_events.timestamp"
632 633 634
                          "run_start_time"      "reports.start_time"
                          "run_end_time"        "reports.end_time"
                          "report_receive_time" "reports.receive_time"}]
635
    (match [path]
636 637 638 639
           [(field :guard (kitchensink/keyset timestamp-fields))]
           (if-let [timestamp (to-timestamp value)]
             {:where (format "%s %s ?" (timestamp-fields field) op)
              :params [(to-timestamp value)]}
640 641
             (throw (IllegalArgumentException.
                     (i18n/tru "''{0}'' is not a valid timestamp value" value))))
642

643
           :else (throw (IllegalArgumentException.
644 645
                         (i18n/tru "{0} operator does not support object ''{1}'' for resource events"
                                   op path))))))
646 647 648 649 650 651 652 653 654

(defn compile-resource-event-equality
  "Compile an = predicate for resource event query. `path` represents the field to
  query against, and `value` is the value."
  [version]
  (fn [& [path value :as args]]
    {:post [(map? %)
            (string? (:where %))]}
    (when-not (= (count args) 2)
655 656 657
      (throw (IllegalArgumentException.
              (i18n/tru "= requires exactly two arguments, but {0} were supplied"
                        (count args)))))
658
    (let [path (utils/dashes->underscores path)]
659 660
      (match [path]
             ["certname"]
661
             {:where "reports.certname = ?"
662 663 664
              :params [value]}

             ["report"]
665
             {:where (format "%s = ?" (sutils/sql-hash-as-str "reports.hash"))
666 667 668
              :params [value]}

             ["latest_report?"]
669 670 671 672
             (let [latest-report-clause (format "resource_events.report_id %s (SELECT certnames.latest_report_id FROM certnames)"
                                                (if value "IN" "NOT IN"))]
               {:where latest-report-clause
                :latest-report-clause latest-report-clause})
673

674
             ["environment"]
675
             {:where "environments.environment = ?"
676 677 678
              :params [value]}

             [(field :guard #{"report" "resource_type" "resource_title" "status"})]
679
             {:where (format "resource_events.%s = ?" field)
680 681 682 683 684
              :params [value] }

             ;; these fields allow NULL, which causes a change in semantics when
             ;; wrapped in a NOT(...) clause, so we have to be very explicit
             ;; about the NULL case.
685
             [(field :guard #{"property" "message" "file" "line" "containing_class" "corrective_change"})]
686
             (if-not (nil? value)
687
               {:where (format "resource_events.%s = ? AND resource_events.%s IS NOT NULL" field field)
688
                :params [value] }
689
               {:where (format "resource_events.%s IS NULL" field)
690 691 692 693 694 695 696 697 698
                :params nil })

             ;; these fields require special treatment for NULL (as described above),
             ;; plus a serialization step since the values can be complex data types
             [(field :guard #{"old_value" "new_value"})]
             {:where (format "resource_events.%s = ? AND resource_events.%s IS NOT NULL" field field)
              :params [(db-serialize value)] }

             :else (throw (IllegalArgumentException.
699 700
                           (i18n/tru "''{0}'' is not a queryable object for version {1} of the resource events API"
                                     path (last (name version)))))))))
701 702 703 704 705 706 707 708 709

(defn compile-resource-event-regexp
  "Compile an ~ predicate for resource event query. `path` represents the field
   to query against, and `pattern` is the regular expression to match."
  [version]
  (fn [& [path pattern :as args]]
    {:post [(map? %)
            (string? (:where %))]}
    (when-not (= (count args) 2)
710
      (throw (IllegalArgumentException.
711
               (i18n/tru "~ requires exactly two arguments, but {0} were supplied" (count args)))))
712
    (let [path (utils/dashes->underscores path)]
713 714
      (match [path]
             ["certname"]
715
             {:where (legacy-sql-regexp-match "reports.certname")
716 717
              :params [pattern]}

718
             ["environment"]
719
             {:where (legacy-sql-regexp-match "environments.environment")
720 721 722
              :params [pattern]}

             [(field :guard #{"report" "resource_type" "resource_title" "status"})]
723
             {:where  (legacy-sql-regexp-match (format "resource_events.%s" field))
724 725 726 727 728 729
              :params [pattern] }

             ;; these fields allow NULL, which causes a change in semantics when
             ;; wrapped in a NOT(...) clause, so we have to be very explicit
             ;; about the NULL case.
             [(field :guard #{"property" "message" "file" "line" "containing_class"})]
730 731
             {:where (format "%s AND resource_events.%s IS NOT NULL"
                             (legacy-sql-regexp-match (format "resource_events.%s" field))
732
                             field)
733
              :params [pattern]}
734 735

             :else (throw (IllegalArgumentException.
736 737
                           (i18n/tru "''{0}'' is not a queryable object for version {1} of the resource events API"
                                     path (last (name version)))))))))
738

739 740 741
(defn compile-event-count-equality
  "Compile an = predicate for event-count query.  The `path` represents
  the field to query against, and `value` is the value of the field."
742
  [fields & [path value :as args]]
743 744 745
  {:post [(map? %)
          (string? (:where %))]}
  (when-not (= (count args) 2)
746 747
    (throw (IllegalArgumentException.
            (i18n/tru "= requires exactly two arguments, but {0} were supplied" (count args)))))
748
  (let [db-field (utils/dashes->underscores path)]
749
    (match [db-field]
750
           [(field :guard fields)]
751 752
           {:where (format "%s = ?" field)
            :params [value]}
753

754 755
           :else (throw (IllegalArgumentException.
                         (i18n/tru "{0} is not a queryable object for event counts" path))))))
756 757 758 759

(defn compile-event-count-inequality
  "Compile an inequality for an event-counts query (> < >= <=).  The `path`
  represents the field to query against, and the `value` is the value of the field."
760
  [fields & [op path value :as args]]
761 762 763
  {:post [(map? %)
          (string? (:where %))]}
  (when-not (= (count args) 3)
764 765 766
    (throw (IllegalArgumentException.
            (i18n/tru "{0} requires exactly two arguments, but {1} were supplied"
                      op (dec (count args))))))
767
  (match [path]
768
         [(field :guard fields)]
769 770
         {:where (format "%s %s ?" field op)
          :params [value]}
771

772 773 774
         :else (throw (IllegalArgumentException.
                       (i18n/tru "{0} operator does not support object ''{1}'' for event counts"
                                 op path)))))
775

776
(declare fact-operators)
777

778 779
(defn resource-operators
  "Maps resource query operators to the functions implementing them. Returns nil
780
  if the operator isn't known."
781
  [version]
782 783 784 785 786 787 788 789 790
  (fn [op]
    (condp = (str/lower-case op)
      "=" (partial compile-resource-equality version)
      "~" (partial compile-resource-regexp version)
      "and" (partial compile-and (resource-operators version))
      "or" (partial compile-or (resource-operators version))
      "not" (partial compile-not version (resource-operators version))
      "extract" (partial compile-extract version (resource-operators version))
      "in" (partial compile-in :resource version (resource-operators version))
791 792
      "select_resources" (partial resource-query->sql (resource-operators version))
      "select_facts" (partial fact-query->sql (fact-operators version))
793
      nil)))
794 795 796

(defn fact-operators
  "Maps fact query operators to the functions implementing them. Returns nil
797
  if the operator isn't known."
798
  [version]
799 800 801 802 803 804 805 806 807 808 809 810 811 812 813
  (fn [op]
    (let [op (str/lower-case op)]
      (cond
        (#{">" "<" ">=" "<="} op)
        (partial compile-fact-inequality op)

        (= op "=") (compile-fact-equality version)
        (= op "~") (compile-fact-regexp version)
        ;; We pass this function along so the recursive calls know which set of
        ;; operators/functions to use, depending on the API version.
        (= op "and") (partial compile-and (fact-operators version))
        (= op "or") (partial compile-or (fact-operators version))
        (= op "not") (partial compile-not version (fact-operators version))
        (= op "extract") (partial compile-extract version (fact-operators version))
        (= op "in") (partial compile-in :fact version (fact-operators version))
814 815
        (= op "select_resources") (partial resource-query->sql (resource-operators version))
        (= op "select_facts") (partial fact-query->sql (fact-operators version))))))
816

817 818 819 820
(defn resource-event-ops
  "Maps resource event query operators to the functions implementing them. Returns nil
  if the operator isn't known."
  [version]
821 822 823
  (fn [op]
    (let [op (str/lower-case op)]
      (cond
824 825 826 827 828 829 830 831 832 833
       (= op "=") (compile-resource-event-equality version)
       (= op "and") (partial compile-and (resource-event-ops version))
       (= op "or") (partial compile-or (resource-event-ops version))
       (= op "not") (partial compile-not version (resource-event-ops version))
       (#{">" "<" ">=" "<="} op) (partial compile-resource-event-inequality op)
       (= op "~") (compile-resource-event-regexp version)
       (= op "extract") (partial compile-extract version (resource-event-ops version))
       (= op "in") (partial compile-in :event version (resource-event-ops version))
       (= op "select_resources") (partial resource-query->sql (resource-operators version))
       (= op "select_facts") (partial fact-query->sql (fact-operators version))))))
834

835 836 837
(defn event-count-ops
  "Maps resource event count operators to the functions implementing them.
  Returns nil if the operator is unknown."
838
  [fields op]
839
  (let [op (str/lower-case op)]
840
    (cond
841 842
     (= "=" op) (partial compile-event-count-equality fields)
     (#{">" "<" ">=" "<="} op) (partial compile-event-count-inequality fields op))))