Mercurial > coderloop

annotate src/scientist_sql.clj @ 0:307a81e46071 tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
initial committ
author Robert McIntyre <rlm@mit.edu>
date Tue, 18 Oct 2011 01:17:49 -0700
parents
children
rev   line source
rlm@0 1 (ns coderloop.scientist-sql
rlm@0 2 (:refer-clojure :only [])
rlm@0 3 (:require rlm.ns-rlm rlm.light-base))
rlm@0 4 (rlm.ns-rlm/ns-clone rlm.light-base)
rlm@0 5
rlm@0 6
rlm@0 7 (use 'clojure.contrib.sql) ;;' satisfy prettify
rlm@0 8 (import 'org.gjt.mm.mysql.Driver)
rlm@0 9 (use 'clojure.contrib.sql)
rlm@0 10 (import '[org.joda.time LocalDate ])
rlm@0 11 (undef distinct case compile drop take sort)
rlm@0 12 (use 'clojureql.core)
rlm@0 13 (import 'org.joda.time.DateMidnight)
rlm@0 14 (import 'java.io.File)
rlm@0 15 (use '[clojure.java.io :only [copy]])
rlm@0 16 (use '[clojure.contrib.shell-out :only [sh]])
rlm@0 17 (use '[clojure.string :only [split]])
rlm@0 18 (use '[coderloop [utils :only [md5]]])
rlm@0 19
rlm@0 20 (defn sql-date [date]
rlm@0 21 "Convert any Joda-readable date object (including a string) to a java.sql.Date"
rlm@0 22 (java.sql.Date. (.. (LocalDate. date) toDateMidnight toInstant getMillis)))
rlm@0 23
rlm@0 24 (def *port* 3306)
rlm@0 25
rlm@0 26 (def *host* "localhost")
rlm@0 27
rlm@0 28 (def *db-host* "localhost")
rlm@0 29
rlm@0 30 (def *db-name* "clojure_rlm")
rlm@0 31
rlm@0 32 (def *db* {:classname "com.mysql.jdbc.Driver"
rlm@0 33 :subprotocol "mysql"
rlm@0 34 :subname (str "//" *db-host* ":" *port* "/" *db-name*)
rlm@0 35 :user "root"
rlm@0 36 :password "sql1005025"})
rlm@0 37
rlm@0 38 (def *db2* {:classname "com.mysql.jdbc.Driver"
rlm@0 39 :subprotocol "mysql"
rlm@0 40 :subname (str "//" *db-host* ":" *port* "/" "test_rlm")
rlm@0 41 :user "root"
rlm@0 42 :password "sql1005025"})
rlm@0 43
rlm@0 44
rlm@0 45 (def databases [:essay_scientists :scientists :ideas :scientist_whereabouts])
rlm@0 46
rlm@0 47 (defmacro no-exceptions
rlm@0 48 "Sweet relief like I never knew."
rlm@0 49 [& forms]
rlm@0 50 `(try ~@forms (catch Exception e# (.printStackTrace e#))))
rlm@0 51
rlm@0 52
rlm@0 53 (defn make-essay-scientists []
rlm@0 54 (with-connection
rlm@0 55 *db2*
rlm@0 56 (no-exceptions (drop-table :essay_scientists))
rlm@0 57 (create-table
rlm@0 58 :essay_scientists
rlm@0 59 [:id "integer"]
rlm@0 60 [:name "varchar(255)"]
rlm@0 61 [:surname "varchar(255)"])
rlm@0 62 (insert-rows :essay_scientists
rlm@0 63 [1 "Isaac" "Newton"]
rlm@0 64 [2 "Albert" "Einstein"])))
rlm@0 65
rlm@0 66 (defn make-scientists []
rlm@0 67 (with-connection
rlm@0 68 *db2*
rlm@0 69 (no-exceptions (drop-table :scientists))
rlm@0 70 (create-table
rlm@0 71 :scientists
rlm@0 72 [:name "varchar(255)"]
rlm@0 73 [:surname "varchar(255)"]
rlm@0 74 [:birth_place "varchar(255)"]
rlm@0 75 [:death_place "varchar(255)"]
rlm@0 76 [:birth_date "date"]
rlm@0 77 [:death_date "date"])
rlm@0 78 (insert-rows
rlm@0 79 :scientists
rlm@0 80 ["Albert" "Einstein" "Ulm" "Princeton" "1879-3-14" "1955-4-18"]
rlm@0 81 ["Galileo" "Galilei" "Pisa" "Arcetri" "1564-2-15" "1645-1-8"]
rlm@0 82 ["Enrico" "Fermi" "Roma" "Chicago" "1901-9-29" "1954-9-28"]
rlm@0 83 ["Isaac" "Newton" "Woolsthorpe-by-Colsterworth" "London" "1643-1-4" "1727-3-31"]
rlm@0 84 ["Marx" "Planck" "Kiel" "Göttingen" "1858-4-23" "1947-10-4"])))
rlm@0 85
rlm@0 86
rlm@0 87 (defn make-scientist-whereabouts []
rlm@0 88 (with-connection *db2*
rlm@0 89 (no-exceptions (drop-table :scientist_whereabouts ))
rlm@0 90 (create-table
rlm@0 91 :scientist_whereabouts
rlm@0 92 [:name "varchar(255)"]
rlm@0 93 [:surname "varchar(255)"]
rlm@0 94 [:country "varchar(255)"]
rlm@0 95 [:immigration_date "date"]
rlm@0 96 [:emigration_date "date"])
rlm@0 97 (insert-rows
rlm@0 98 :scientist_whereabouts
rlm@0 99 ["Galileo" "Galilei" "France" "1602-2-18" "1604-4-20"]
rlm@0 100 ["Albert" "Einstein" "Uk" "1901-10-22" "1905-5-4"]
rlm@0 101 ["Marx" "Planck" "Denmark" "1901-1-31" "1945-7-4"]
rlm@0 102 ["Albert" "Einstein" "France" "1908-11-7" "1930-3-12"]
rlm@0 103 ["Enrico" "Fermi" "Us" "1924-12-19" "1954-9-28"]
rlm@0 104 ["Isaac" "Newton" "Uk" "1688-9-8" "1690-7-12"]
rlm@0 105 ["Albert" "Einstein" "US" "1935-10-22" "1955-4-18"]
rlm@0 106 ["Isaac" "Newton" "Germany" "1690-9-28" "1705-4-6"])))
rlm@0 107
rlm@0 108
rlm@0 109 (defn make-ideas []
rlm@0 110 (with-connection *db2*
rlm@0 111 (no-exceptions (drop-table :ideas))
rlm@0 112 (create-table
rlm@0 113 :ideas
rlm@0 114 [:name "varchar(255)"]
rlm@0 115 [:surname "varchar(255)"]
rlm@0 116 [:idea "varchar(255)"])
rlm@0 117 (insert-rows
rlm@0 118 :ideas
rlm@0 119 ["Albert" "Einstein" "Theory of relativity (general)"]
rlm@0 120 ["Enrico" "Fermi" "First nuclear reactor"]
rlm@0 121 ["Albert" "Einstein" "Photoelectric effect"]
rlm@0 122 ["Isaac" "Newton" "Gravity"]
rlm@0 123 ["Galileo" "Galilei" "Telescope"]
rlm@0 124 ["Albert" "Einstein" "Theory of relativity (special)"]
rlm@0 125 ["Isaac" "Newton" "Dark matter"])))
rlm@0 126
rlm@0 127 (defn clear-tables []
rlm@0 128 (with-connection *db2* (dorun (map drop-table databases))))
rlm@0 129
rlm@0 130 (defn fill-tables []
rlm@0 131 (make-ideas)
rlm@0 132 (make-essay-scientists)
rlm@0 133 (make-scientist-whereabouts)
rlm@0 134 (make-scientists))
rlm@0 135
rlm@0 136 (defn make-empty-tables []
rlm@0 137 (with-connection *db2*
rlm@0 138 (create-table
rlm@0 139 :ideas
rlm@0 140 [:name "varchar(255)"]
rlm@0 141 [:surname "varchar(255)"]
rlm@0 142 [:idea "varchar(255)"])
rlm@0 143 (create-table
rlm@0 144 :scientist_whereabouts
rlm@0 145 [:name "varchar(255)"]
rlm@0 146 [:surname "varchar(255)"]
rlm@0 147 [:country "varchar(255)"]
rlm@0 148 [:immigration_date "date"]
rlm@0 149 [:emigration_date "date"])
rlm@0 150 (create-table
rlm@0 151 :scientists
rlm@0 152 [:name "varchar(255)"]
rlm@0 153 [:surname "varchar(255)"]
rlm@0 154 [:birth_place "varchar(255)"]
rlm@0 155 [:death_place "varchar(255)"]
rlm@0 156 [:birth_date "date"]
rlm@0 157 [:death_date "date"])
rlm@0 158 (create-table
rlm@0 159 :essay_scientists
rlm@0 160 [:id "integer"]
rlm@0 161 [:name "varchar(255)"]
rlm@0 162 [:surname "varchar(255)"])))
rlm@0 163
rlm@0 164 (def fill-tables-command "mysql -u root -p clojure_rlm < scientists-a.in")
rlm@0 165
rlm@0 166
rlm@0 167
rlm@0 168 (defmethod < [java.sql.Date java.sql.Date] [a b]
rlm@0 169 ({-1 true 0 false 1 false}
rlm@0 170 (.compareTo a b)))
rlm@0 171
rlm@0 172 (defmethod > [java.sql.Date java.sql.Date]
rlm@0 173 ([a b]
rlm@0 174 ({-1 true 0 false 1 false}
rlm@0 175 (.compareTo a b))))
rlm@0 176
rlm@0 177
rlm@0 178 ;; they can have the same date of birth and death!!!
rlm@0 179 (defn contemps [birth death]
rlm@0 180 (with-connection *db*
rlm@0 181 @(select
rlm@0 182 (table :scientists)
rlm@0 183 (where
rlm@0 184 (or
rlm@0 185 (and (<= :birth_date birth) (< birth :death_date))
rlm@0 186 (and (< :birth_date death) (<= death :death_date))
rlm@0 187 (and (<= birth :birth_date) (< :birth_date death))
rlm@0 188 (and (< birth :death_date) (<= :death_date death)))))))
rlm@0 189
rlm@0 190 (defn essay-scientists []
rlm@0 191 @(table *db* :essay_scientists))
rlm@0 192
rlm@0 193 (defn get-info [table-name s]
rlm@0 194 @(select
rlm@0 195 (select (table *db* table-name)
rlm@0 196 (where (= :name (:name s))))
rlm@0 197 (where(= :surname (:surname s)))))
rlm@0 198
rlm@0 199 (defn century* [#^java.sql.Date d]
rlm@0 200 (* 100
rlm@0 201 (.getCenturyOfEra
rlm@0 202 (LocalDate. d))))
rlm@0 203
rlm@0 204
rlm@0 205 (defn century [#^java.sql.Date d]
rlm@0 206 (* 100 (clojure.core/unchecked-divide (+ 1900 (.getYear d)) 100)))
rlm@0 207
rlm@0 208
rlm@0 209
rlm@0 210 (defn get-scientist-data [s]
rlm@0 211 (let [data (first (get-info :scientists s))
rlm@0 212 locations (get-info :scientist_whereabouts s)
rlm@0 213 ideas (map :idea (get-info :ideas s))
rlm@0 214 birth (:birth_date data)
rlm@0 215 death (:death_date data)
rlm@0 216 name (:name data)
rlm@0 217 surname (:surname data)
rlm@0 218 contemps
rlm@0 219 (remove #(and (= (:name %) name) (= (:surname %) surname))
rlm@0 220 (map #(select-keys % [:name :surname])
rlm@0 221 (contemps birth death)))
rlm@0 222 century (century birth)]
rlm@0 223 {:data data :ideas ideas :locations locations :contemps contemps :century century}))
rlm@0 224
rlm@0 225 (defn essay-date* [#^java.sql.Date d]
rlm@0 226 (let [t (LocalDate. d)]
rlm@0 227 (format "%02d/%02d/%04d" (.getDayOfMonth t) (.getMonthOfYear t) (.getYear t))))
rlm@0 228
rlm@0 229 (defn essay-date [#^java.sql.Date d]
rlm@0 230 (format "%02d/%02d/%04d" (.getDate d) (inc (.getMonth d)) (+ 1900 (.getYear d))))
rlm@0 231
rlm@0 232 (defn fucked-sort [m]
rlm@0 233 (let [surname (:surname m)
rlm@0 234 name (:name m)
rlm@0 235 [surname n1] (.split surname " ")
rlm@0 236 [name n2] (.split name " ")
rlm@0 237 n1 (Integer/parseInt n1)
rlm@0 238 n2 (Integer/parseInt n2)]
rlm@0 239 [surname n1 name n2]))
rlm@0 240
rlm@0 241 (defn lexical-sort [m]
rlm@0 242 (let [surname (:surname m)
rlm@0 243 name (:name m)]
rlm@0 244 [surname name]))
rlm@0 245
rlm@0 246
rlm@0 247 (defn-memo essay [s]
rlm@0 248 (let [info (get-scientist-data s)
rlm@0 249 name (:name (:data info))
rlm@0 250 surname (:surname (:data info))]
rlm@0 251 (str name
rlm@0 252 " "
rlm@0 253 surname
rlm@0 254 " ["
rlm@0 255 (:birth_place (:data info))
rlm@0 256 " "
rlm@0 257 (essay-date (:birth_date (:data info)))
rlm@0 258 ", "
rlm@0 259 (:death_place (:data info))
rlm@0 260 " "
rlm@0 261 (essay-date (:death_date (:data info)))
rlm@0 262 "]"
rlm@0 263 " is one of the most famous scientists of "
rlm@0 264 (:century info)". "
rlm@0 265 "Between all " name "'s ideas we mention: "
rlm@0 266 (apply str (interpose ", " (sort (:ideas info))))
rlm@0 267 ". "
rlm@0 268 name " lived in: "
rlm@0 269 (apply
rlm@0 270 str
rlm@0 271 (interpose ", "
rlm@0 272 (map
rlm@0 273 (fn [entry]
rlm@0 274 (str (:country entry)
rlm@0 275 " from "
rlm@0 276 (essay-date (:immigration_date entry)) " to "
rlm@0 277 (essay-date (:emigration_date entry))))
rlm@0 278 (sort-by :immigration_date (:locations info)))))
rlm@0 279 ". "
rlm@0 280 name
rlm@0 281 " was a contemporary of "
rlm@0 282 (apply
rlm@0 283 str
rlm@0 284 (interpose
rlm@0 285 ", "
rlm@0 286 (map (fn [entry]
rlm@0 287 (str (:name entry)
rlm@0 288 " "
rlm@0 289 (:surname entry)))
rlm@0 290
rlm@0 291 (sort-by lexical-sort
rlm@0 292 (:contemps info)))))
rlm@0 293 ".\n\n" )))
rlm@0 294
rlm@0 295 (defn essays []
rlm@0 296 (map essay (essay-scientists)))
rlm@0 297
rlm@0 298
rlm@0 299 (defn main [[username password db-name]]
rlm@0 300 (let [db-host *host*
rlm@0 301 db-port *port*
rlm@0 302 db {:classname "com.mysql.jdbc.Driver"
rlm@0 303 :subprotocol "mysql"
rlm@0 304 :subname (str "//" db-host ":" db-port "/" db-name)
rlm@0 305 :user username
rlm@0 306 :password password}]
rlm@0 307 (binding [*db* db]
rlm@0 308 (print (apply str (essays))))))
rlm@0 309
rlm@0 310 (def desired-md5 "e436d54f87c86bffb5d1b38d5f3e136b")
rlm@0 311
rlm@0 312 (if (command-line?)
rlm@0 313 (main *command-line-args*))
rlm@0 314
rlm@0 315
rlm@0 316
rlm@0 317