<commit_before>(ns org.akvo.dash.import.csv
  (:require [clojure.java.io :as io]
            [clojure.string :as s]
            [clojure.java.jdbc :as jdbc]
            [clojure.data.csv :as csv]
            [hugsql.core :as hugsql]
            [org.akvo.dash.util :refer [squuid]]
            [org.akvo.dash.import.common :as import])
  (:import org.postgresql.copy.CopyManager
           org.postgresql.PGConnection
           java.util.UUID))

(hugsql/def-db-fns "org/akvo/dash/import.sql")

(defn- get-cols
  ([num-cols]
   (get-cols num-cols nil))
  ([num-cols c-type]
   (s/join ", "
           (for [i (range 1 (inc num-cols))]
             (str "c" i
                  (if-not (nil? c-type)
                    (str " " c-type)
                    ""))))))

(defn- gen-table-name
  []
  (str "ds_" (s/replace (UUID/randomUUID) #"-" "_")))

(defn get-create-table-sql
  "Returns a `CREATE TABLE` statement for
  the given number table name and number of columns"
  [t-name num-cols c-type temp?]
  (format "CREATE %s %s (%s, %s)"
          (if temp? "TEMP TABLE" "TABLE")
          t-name
          (if temp? "rnum serial" "rnum integer")
          (get-cols num-cols c-type)))

(defn get-copy-sql
  "Returns a `COPY` statement for the given
  table and number of columns"
  [t-name num-cols headers?]
  (format "COPY %s (%s) FROM STDIN WITH (FORMAT CSV%s)"
          t-name
          (get-cols num-cols)
          (if headers? ", HEADER true" "")))

(defn get-insert-sql
  "Returns an `INSERT` statement for a given
  table and number of columns."
  [src-table dest-table num-cols]
  (let [cols (for [i (range 1 (inc num-cols))]
               (str "c" i))
        src-cols (map #(format "to_json(replace(%s, '\\', '\\\\'))::jsonb" %) cols)]
    (format "INSERT INTO %s (rnum, %s) SELECT rnum, %s FROM %s"
            dest-table
            (s/join ", " cols)
            (s/join ", " src-cols)
            src-table)))

(defn get-vacuum-sql
  "Returns a `VACUUM` statement for a given table"
  [table-name]
  (format "VACUUM (FULL) %s" table-name))

(defn get-drop-table-sql
  "Returns a `DROP TABLE` statement for a given table"
  [table-name]
  (format "DROP TABLE IF EXISTS %s CASCADE" table-name))

(defn get-headers
  "Returns the first line CSV a file"
  [path separator]
  (with-open [r (io/reader path)]
    (first (csv/read-csv r :separator separator))))

(defn get-num-cols
  "Returns the number of columns based on the
  first line of a CSV file"
  [path separator]
  (count (get-headers path separator)))

(defn get-column-tuples
  [col-titles]
  (vec
   (map-indexed (fn [idx title]
                  [title (str "c" (inc idx)) "text"]) col-titles)))

(defmethod import/valid? "CSV"
  [{:strs [url fileName hasColumnHeaders]}]
  (and (string? url)
       (contains? #{true false nil} hasColumnHeaders)
       (or (nil? fileName)
           (string? fileName))))

(defmethod import/authorized? "CSV"
  [claims config spec]
  true)

(defmethod import/make-dataset-data-table "CSV"
  [tenant-conn {:keys [file-upload-path]} table-name spec]
  (try
    (let [ ;; TODO a bit of "manual" integration work
          file-on-disk? (contains? spec "fileName")
          path (let [url (get spec "url")]
                 (if file-on-disk?
                   (str file-upload-path
                        "/resumed/"
                        (last (s/split url #"\/"))
                        "/file")
                   url))
          headers? (boolean (get spec "hasColumnHeaders"))
          n-cols (get-num-cols path \,)
          col-titles (if headers?
                       (get-headers path \,)
                       (vec (for [i (range 1 (inc n-cols))]
                              (str "Column " i))))
          temp-table (str table-name "_temp")]
      (jdbc/execute! tenant-conn [(get-create-table-sql temp-table n-cols "text" false)])
      (jdbc/execute! tenant-conn [(get-create-table-sql table-name n-cols "jsonb" false)])
      (with-open [conn (-> tenant-conn :datasource .getConnection)]
        (let [copy-manager (.getCopyAPI (.unwrap conn PGConnection))]
          (.copyIn copy-manager ^String (get-copy-sql temp-table n-cols headers?) (io/input-stream path))))
      (jdbc/execute! tenant-conn [(get-insert-sql temp-table table-name n-cols)])
      (jdbc/execute! tenant-conn [(get-drop-table-sql temp-table)])
      (jdbc/execute! tenant-conn [(get-vacuum-sql table-name)] :transaction? false)
      {:success? true
       :columns (get-column-tuples col-titles)})
    (catch Exception e
      {:success? false<commit_msg>[#157] Use TEMP table for initial data load (#158)<commit_after>(ns org.akvo.dash.import.csv
  (:require [clojure.java.io :as io]
            [clojure.string :as s]
            [clojure.java.jdbc :as jdbc]
            [clojure.data.csv :as csv]
            [hugsql.core :as hugsql]
            [org.akvo.dash.util :refer [squuid]]
            [org.akvo.dash.import.common :as import])
  (:import org.postgresql.copy.CopyManager
           org.postgresql.PGConnection
           java.util.UUID))

(hugsql/def-db-fns "org/akvo/dash/import.sql")

(defn- get-cols
  ([num-cols]
   (get-cols num-cols nil))
  ([num-cols c-type]
   (s/join ", "
           (for [i (range 1 (inc num-cols))]
             (str "c" i
                  (if-not (nil? c-type)
                    (str " " c-type)
                    ""))))))

(defn- gen-table-name
  []
  (str "ds_" (s/replace (UUID/randomUUID) #"-" "_")))

(defn get-create-table-sql
  "Returns a `CREATE TABLE` statement for
  the given number table name and number of columns"
  [t-name num-cols c-type temp?]
  (format "CREATE %s %s (%s, %s)"
          (if temp? "TEMP TABLE" "TABLE")
          t-name
          (if temp? "rnum serial" "rnum integer")
          (get-cols num-cols c-type)))

(defn get-copy-sql
  "Returns a `COPY` statement for the given
  table and number of columns"
  [t-name num-cols headers?]
  (format "COPY %s (%s) FROM STDIN WITH (FORMAT CSV%s)"
          t-name
          (get-cols num-cols)
          (if headers? ", HEADER true" "")))

(defn get-insert-sql
  "Returns an `INSERT` statement for a given
  table and number of columns."
  [src-table dest-table num-cols]
  (let [cols (for [i (range 1 (inc num-cols))]
               (str "c" i))
        src-cols (map #(format "to_json(replace(%s, '\\', '\\\\'))::jsonb" %) cols)]
    (format "INSERT INTO %s (rnum, %s) SELECT rnum, %s FROM %s"
            dest-table
            (s/join ", " cols)
            (s/join ", " src-cols)
            src-table)))

(defn get-vacuum-sql
  "Returns a `VACUUM` statement for a given table"
  [table-name]
  (format "VACUUM (FULL) %s" table-name))

(defn get-drop-table-sql
  "Returns a `DROP TABLE` statement for a given table"
  [table-name]
  (format "DROP TABLE IF EXISTS %s CASCADE" table-name))

(defn get-headers
  "Returns the first line CSV a file"
  [path separator]
  (with-open [r (io/reader path)]
    (first (csv/read-csv r :separator separator))))

(defn get-num-cols
  "Returns the number of columns based on the
  first line of a CSV file"
  [path separator]
  (count (get-headers path separator)))

(defn get-column-tuples
  [col-titles]
  (vec
   (map-indexed (fn [idx title]
                  [title (str "c" (inc idx)) "text"]) col-titles)))

(defmethod import/valid? "CSV"
  [{:strs [url fileName hasColumnHeaders]}]
  (and (string? url)
       (contains? #{true false nil} hasColumnHeaders)
       (or (nil? fileName)
           (string? fileName))))

(defmethod import/authorized? "CSV"
  [claims config spec]
  true)

(defmethod import/make-dataset-data-table "CSV"
  [tenant-conn {:keys [file-upload-path]} table-name spec]
  (try
    (let [ ;; TODO a bit of "manual" integration work
          file-on-disk? (contains? spec "fileName")
          path (let [url (get spec "url")]
                 (if file-on-disk?
                   (str file-upload-path
                        "/resumed/"
                        (last (s/split url #"\/"))
                        "/file")
                   url))
          headers? (boolean (get spec "hasColumnHeaders"))
          n-cols (get-num-cols path \,)
          col-titles (if headers?
                       (get-headers path \,)
                       (vec (for [i (range 1 (inc n-cols))]
                              (str "Column " i))))
          temp-table (str table-name "_temp")]
      (jdbc/execute! tenant-conn [(get-create-table-sql temp-table n-cols "text" true)])
      (jdbc/execute! tenant-conn [(get-create-table-sql table-name n-cols "jsonb" false)])
      (with-open [conn (-> tenant-conn :datasource .getConnection)]
        (let [copy-manager (.getCopyAPI (.unwrap conn PGConnection))]
          (.copyIn copy-manager ^String (get-copy-sql temp-table n-cols headers?) (io/input-stream path))))
      (jdbc/execute! tenant-conn [(get-insert-sql temp-table table-name n-cols)])
      (jdbc/execute! tenant-conn [(get-drop-table-sql temp-table)])
      (jdbc/execute! tenant-conn [(get-vacuum-sql table-name)] :transaction? false)
      {:success? true
       :columns (get-column-tuples col-titles)})
    (catch Exception e
      {:success? false
{'fraction_non_alphanumeric': 0.15196506550218342, 'fraction_numerical': 0.0013100436681222707, 'mean_word_length': 2.1384035628639944, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, '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': '4655942', 'n_tokens_mistral': 3154, 'n_tokens_neox': 3042, 'n_words': 865}