<commit_before>(ns metabase.driver.generic-sql.sync
  "Generic implementations of `metabase.driver` `(sync-tables [db]) function that should work across any SQL database supported by Korma."
  (:require [clojure.java.jdbc :as jdbc]
            [clojure.math.numeric-tower :as math]
            [clojure.tools.logging :as log]
            [korma.core :refer :all]
            [metabase.db :refer :all]
            [metabase.driver.generic-sql.util :refer :all]
            (metabase.models [field :refer [Field]]
                             [foreign-key :refer [ForeignKey]]
                             [table :refer [Table]])))

(declare check-for-large-average-length korma-table field
         check-for-low-cardinality
         check-for-urls
         jdbc-columns
         set-table-fks-if-needed!
         set-table-pks-if-needed!
         sync-fields-create
         sync-fields-metadata
         sync-table
         table-names
         update-table-row-count!)

;; # PUBLIC INTERFACE

;; ## Syncing

(def ^:dynamic *column->base-type*
  "COLUMN->BASE-TYPE should be a map of column types returned by the DB to Field base types."
  {})

(def ^:dynamic *sql-string-length-fn*
  "This should be bound to the *keyword* name of the SQL fn that can be used to return the length of a string.

    (binding [*sql-string-length-fn* :LENGTH] ; for H2
      ...)"
  nil)

(defn sync-database
  "Sync all `Tables` + `Fields` in DATABASE."
  [{database-id :id :as database}]
  (with-jdbc-metadata [_ database]                                                    ; do a top-level connection to with-jdbc-metadata because it reuses connection
    (let [table-names (table-names database)                                          ; for all subsequent calls within its body
          table-name->id (sel :many :field->id [Table :name] :name [in table-names])]
      ;; Mark any existing `Table` objects not returned by `table-names` as inactive
      (dorun (map (fn [[table-name table-id]]
                    (when-not (contains? table-names table-name)
                      (upd Table table-id :active false)))
                  table-name->id))
      ;; Create `Table` objects for any new tables returned by `table-names`
      (dorun (map (fn [table-name]
                    (when-not (table-name->id table-name)
                      (ins Table
                        :db_id database-id
                        :name table-name
                        :active true)))
                  table-names))
      ;; Now sync the active Tables
      (let [tables (->> (sel :many Table :active true :db_id database-id)
                        (map #(assoc % :db (delay database))))]                       ; reuse DATABASE, that way we don't end up creating multiple connection pools
        ;; First, we need to make sure Active Fields are all up-to-date,
        ;; since other steps like set-table-fks-if-needed! depend on them existing
        (dorun (pmap (fn [table]
                       (sync-fields-create (korma-entity table) table))
                     tables))
        ;; Once those are g2g we can do the rest of the syncing for the Table
        (dorun (pmap (fn [table]
                       (let [korma-table (korma-entity table)]
                         (update-table-row-count! korma-table table)
                         (set-table-pks-if-needed! table)
                         (set-table-fks-if-needed! korma-table table)
                         (sync-fields-metadata korma-table table)))
                     tables))))))

(defn sync-table
  "Sync a single `Table` and its `Fields`."
  {:arglists '([table])}
  [{db :db table-name :name :as table}]
  (let [korma-table (korma-entity table)]          ; implementation is a little simpler here than SYNC-DATABASE
    (with-jdbc-metadata [_ @db]                    ; since we don't need to wait for *every* table to finish `sync-fields-create` before
      (sync-fields-create korma-table table)
      (update-table-row-count! korma-table table)
      (set-table-pks-if-needed! table)
      (set-table-fks-if-needed! korma-table table)
      (sync-fields-metadata korma-table table)
      (log/debug "Synced" table-name))))


(defn- sync-fields-create
  "Create new Fields for any that don't exist; mark ones that no longer exist as `inactive`."
  {:arglists '([korma-table table])}
  [korma-table {table-id :id, table-name :name, db :db}]
  (let [fields (jdbc-columns db table-name)
        field-names (set (map :column_name fields))
        field-name->id (sel :many :field->id [Field :name] :table_id table-id :name [in field-names])]
    ;; Mark any existing `Field` objects not returned by jdbc-columns as inactive
    (dorun (map (fn [[field-name field-id]]
                  (when-not (contains? field-names field-name)
                    (upd Field field-id :active false)))
                field-name->id))
    ;; Create `Field` objects for any new Fields returned by jdbc-columns
    (dorun (map (fn [{field-name :column_name type-name :type_name}]
                  (when-not (field-name->id field-name)
                    (ins Field
                      :table_id table-id
                      :name field-name
                      :base_type (or (*column->base-type* (keyword type-name))
                                     (throw (Exception. (str "Column '" field-name "' has an unknown type: '" type-name
                                                             "'. Please add the type mapping to corresponding driver (e.g. metabase.driver.postgres.sync).")))))))
                fields))))

(defn- sync-fields-metadata
  "Sync the metadata of all active fields for TABLE (in parallel)."
  {:arglists '([korma-table table])}
  [korma-table {table-id :id table-name :name}]
  (->> (sel :many Field :table_id table-id :active true)
       (pmap (fn [field]
               (try
                 (check-for-low-cardinality korma-table field)
                 (check-for-large-average-length korma-table field)
                 (check-for-urls korma-table field)
                 (catch Throwable e
                   (log/warn (format "Caught exception when syncing field '%s.%s':" table-name (:name field)) e)))))
       dorun))


;; ## Metadata -- Fetch Tables/Columns/PKs/FKs from DB

(defn table-names
  "Fetch a set of table names for DATABASE."
  [database]
  (with-jdbc-metadata [^java.sql.DatabaseMetaData md database]
    (->> (jdbc/result-set-seq (.getTables md nil nil nil (into-array String ["TABLE"]))) ; ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)
         (map :table_name)
         doall
         set)))
<commit_msg>extra try/catch blocks for Sync DB<commit_after>(ns metabase.driver.generic-sql.sync
  "Generic implementations of `metabase.driver` `(sync-tables [db]) function that should work across any SQL database supported by Korma."
  (:require [clojure.java.jdbc :as jdbc]
            [clojure.math.numeric-tower :as math]
            [clojure.tools.logging :as log]
            [korma.core :refer :all]
            [metabase.db :refer :all]
            [metabase.driver.generic-sql.util :refer :all]
            (metabase.models [field :refer [Field]]
                             [foreign-key :refer [ForeignKey]]
                             [table :refer [Table]])))

(declare check-for-large-average-length korma-table field
         check-for-low-cardinality
         check-for-urls
         jdbc-columns
         set-table-fks-if-needed!
         set-table-pks-if-needed!
         sync-fields-create
         sync-fields-metadata
         sync-table
         table-names
         update-table-row-count!)

;; # PUBLIC INTERFACE

;; ## Syncing

(def ^:dynamic *column->base-type*
  "COLUMN->BASE-TYPE should be a map of column types returned by the DB to Field base types."
  {})

(def ^:dynamic *sql-string-length-fn*
  "This should be bound to the *keyword* name of the SQL fn that can be used to return the length of a string.

    (binding [*sql-string-length-fn* :LENGTH] ; for H2
      ...)"
  nil)

(defn sync-database
  "Sync all `Tables` + `Fields` in DATABASE."
  [{database-id :id :as database}]
  (with-jdbc-metadata [_ database]                                                    ; do a top-level connection to with-jdbc-metadata because it reuses connection
    (let [table-names (table-names database)                                          ; for all subsequent calls within its body
          table-name->id (sel :many :field->id [Table :name] :name [in table-names])]
      ;; Mark any existing `Table` objects not returned by `table-names` as inactive
      (dorun (map (fn [[table-name table-id]]
                    (when-not (contains? table-names table-name)
                      (upd Table table-id :active false)))
                  table-name->id))
      ;; Create `Table` objects for any new tables returned by `table-names`
      (dorun (map (fn [table-name]
                    (when-not (table-name->id table-name)
                      (ins Table
                        :db_id database-id
                        :name table-name
                        :active true)))
                  table-names))
      ;; Now sync the active Tables
      (let [tables (->> (sel :many Table :active true :db_id database-id)
                        (map #(assoc % :db (delay database))))]                       ; reuse DATABASE, that way we don't end up creating multiple connection pools
        ;; First, we need to make sure Active Fields are all up-to-date,
        ;; since other steps like set-table-fks-if-needed! depend on them existing
        (dorun (pmap (fn [table]
                       (sync-fields-create (korma-entity table) table))
                     tables))
        ;; Once those are g2g we can do the rest of the syncing for the Table
        (dorun (pmap (fn [table]
                       (let [korma-table (korma-entity table)]
                         (update-table-row-count! korma-table table)
                         (set-table-pks-if-needed! table)
                         (set-table-fks-if-needed! korma-table table)
                         (sync-fields-metadata korma-table table)))
                     tables))))))

(defn sync-table
  "Sync a single `Table` and its `Fields`."
  {:arglists '([table])}
  [{db :db table-name :name :as table}]
  (let [korma-table (korma-entity table)]          ; implementation is a little simpler here than SYNC-DATABASE
    (with-jdbc-metadata [_ @db]                    ; since we don't need to wait for *every* table to finish `sync-fields-create` before
      (sync-fields-create korma-table table)
      (update-table-row-count! korma-table table)
      (set-table-pks-if-needed! table)
      (set-table-fks-if-needed! korma-table table)
      (sync-fields-metadata korma-table table)
      (log/debug "Synced" table-name))))


(defn- sync-fields-create
  "Create new Fields for any that don't exist; mark ones that no longer exist as `inactive`."
  {:arglists '([korma-table table])}
  [korma-table {table-id :id, table-name :name, db :db}]
  (try
    (let [fields (jdbc-columns db table-name)
          field-names (set (map :column_name fields))
          field-name->id (sel :many :field->id [Field :name] :table_id table-id :name [in field-names])]
      ;; Mark any existing `Field` objects not returned by jdbc-columns as inactive
      (dorun (map (fn [[field-name field-id]]
                    (when-not (contains? field-names field-name)
                      (upd Field field-id :active false)))
                  field-name->id))
      ;; Create `Field` objects for any new Fields returned by jdbc-columns
      (dorun (map (fn [{field-name :column_name type-name :type_name}]
                    (when-not (field-name->id field-name)
                      (ins Field
                        :table_id table-id
                        :name field-name
                        :base_type (or (*column->base-type* (keyword type-name))
                                       (throw (Exception. (str "Column '" field-name "' has an unknown type: '" type-name
                                                               "'. Please add the type mapping to corresponding driver (e.g. metabase.driver.postgres.sync).")))))))
                  fields)))
    (catch Throwable e
      (log/error "Caught exception in sync-fields-create:" e))))

(defn- sync-fields-metadata
  "Sync the metadata of all active fields for TABLE (in parallel)."
  {:arglists '([korma-table table])}
  [korma-table {table-id :id table-name :name}]
  (try
    (->> (sel :many Field :table_id table-id :active true)
         (pmap (fn [field]
                 (try
                   (check-for-low-cardinality korma-table field)
                   (check-for-large-average-length korma-table field)
                   (check-for-urls korma-table field)
                   (catch Throwable e
                     (log/warn (format "Caught exception when syncing field '%s.%s':" table-name (:name field)) e)))))
         dorun)
    (catch Throwable e
      (log/error "Caught exception in sync-fields-metadata:" e))))


;; ## Metadata -- Fetch Tables/Columns/PKs/FKs from DB

(defn table-names
  "Fetch a set of table names for DATABASE."
  [database]
  (with-jdbc-metadata [^java.sql.DatabaseMetaData md database]
    (->> (jdbc/result-set-seq (.getTables md nil nil nil (into-array String ["TABLE"]))) ; ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)
         (map :table_name)
         doall
         set)))

{'fraction_non_alphanumeric': 0.1174785100286533, 'fraction_numerical': 0.00029387995004040847, 'mean_word_length': 1.8967865503298573, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3300000', 'n_tokens_mistral': 4027, 'n_tokens_neox': 3842, 'n_words': 1213}