1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/src/scientist_sql.clj	Tue Oct 18 01:17:49 2011 -0700
     1.3 @@ -0,0 +1,317 @@
     1.4 +(ns coderloop.scientist-sql
     1.5 +  (:refer-clojure :only [])
     1.6 +  (:require rlm.ns-rlm rlm.light-base))
     1.7 +(rlm.ns-rlm/ns-clone rlm.light-base)
     1.8 +
     1.9 +
    1.10 +(use 'clojure.contrib.sql)               ;;' satisfy prettify
    1.11 +(import 'org.gjt.mm.mysql.Driver)
    1.12 +(use 'clojure.contrib.sql)
    1.13 +(import '[org.joda.time LocalDate ])
    1.14 +(undef distinct case compile drop take sort)
    1.15 +(use 'clojureql.core)
    1.16 +(import 'org.joda.time.DateMidnight)
    1.17 +(import 'java.io.File)
    1.18 +(use '[clojure.java.io :only [copy]])
    1.19 +(use '[clojure.contrib.shell-out :only [sh]])
    1.20 +(use '[clojure.string :only [split]])
    1.21 +(use '[coderloop [utils :only [md5]]])
    1.22 +
    1.23 +(defn sql-date [date]
    1.24 +  "Convert any Joda-readable date object (including a string) to a java.sql.Date"
    1.25 +    (java.sql.Date. (.. (LocalDate. date) toDateMidnight toInstant getMillis)))
    1.26 +
    1.27 +(def *port* 3306)
    1.28 +
    1.29 +(def *host* "localhost")
    1.30 +
    1.31 +(def *db-host* "localhost")
    1.32 +
    1.33 +(def *db-name* "clojure_rlm")
    1.34 +
    1.35 +(def *db* {:classname "com.mysql.jdbc.Driver"
    1.36 +	   :subprotocol "mysql"
    1.37 +	   :subname (str "//" *db-host* ":" *port* "/" *db-name*)
    1.38 +	   :user "root"
    1.39 +	   :password "sql1005025"})
    1.40 +
    1.41 +(def *db2* {:classname "com.mysql.jdbc.Driver"
    1.42 +	    :subprotocol "mysql"
    1.43 +	    :subname (str "//" *db-host* ":" *port* "/" "test_rlm")
    1.44 +	    :user "root"
    1.45 +	    :password "sql1005025"})
    1.46 +
    1.47 +
    1.48 +(def databases [:essay_scientists :scientists :ideas :scientist_whereabouts])
    1.49 +
    1.50 +(defmacro no-exceptions
    1.51 +  "Sweet relief like I never knew."
    1.52 +  [& forms]
    1.53 +  `(try ~@forms (catch Exception e# (.printStackTrace e#))))
    1.54 +
    1.55 +
    1.56 +(defn make-essay-scientists []
    1.57 +  (with-connection
    1.58 +    *db2*
    1.59 +    (no-exceptions (drop-table :essay_scientists))
    1.60 +    (create-table
    1.61 +     :essay_scientists
    1.62 +     [:id "integer"]
    1.63 +     [:name "varchar(255)"]
    1.64 +     [:surname "varchar(255)"])
    1.65 +    (insert-rows :essay_scientists
    1.66 +		 [1 "Isaac" "Newton"]
    1.67 +		 [2 "Albert" "Einstein"])))
    1.68 +
    1.69 +(defn make-scientists []
    1.70 +  (with-connection
    1.71 +    *db2*
    1.72 +    (no-exceptions (drop-table :scientists))
    1.73 +    (create-table
    1.74 +     :scientists
    1.75 +     [:name "varchar(255)"]
    1.76 +     [:surname "varchar(255)"]
    1.77 +     [:birth_place "varchar(255)"]
    1.78 +     [:death_place "varchar(255)"]
    1.79 +     [:birth_date "date"]
    1.80 +     [:death_date "date"])
    1.81 +    (insert-rows
    1.82 +     :scientists
    1.83 +     ["Albert" "Einstein" "Ulm" "Princeton" "1879-3-14" "1955-4-18"]
    1.84 +     ["Galileo" "Galilei" "Pisa" "Arcetri" "1564-2-15" "1645-1-8"]
    1.85 +     ["Enrico" "Fermi" "Roma" "Chicago" "1901-9-29" "1954-9-28"]
    1.86 +     ["Isaac" "Newton" "Woolsthorpe-by-Colsterworth" "London" "1643-1-4" "1727-3-31"]
    1.87 +     ["Marx" "Planck" "Kiel" "Göttingen" "1858-4-23" "1947-10-4"])))
    1.88 +
    1.89 +
    1.90 +(defn make-scientist-whereabouts []
    1.91 +  (with-connection *db2*
    1.92 +    (no-exceptions (drop-table :scientist_whereabouts ))
    1.93 +    (create-table
    1.94 +     :scientist_whereabouts
    1.95 +     [:name "varchar(255)"]
    1.96 +     [:surname "varchar(255)"]
    1.97 +     [:country "varchar(255)"]
    1.98 +     [:immigration_date "date"]
    1.99 +     [:emigration_date "date"])
   1.100 +    (insert-rows
   1.101 +     :scientist_whereabouts
   1.102 +     ["Galileo" "Galilei" "France" "1602-2-18" "1604-4-20"]
   1.103 +     ["Albert" "Einstein" "Uk" "1901-10-22" "1905-5-4"]
   1.104 +     ["Marx" "Planck" "Denmark" "1901-1-31" "1945-7-4"]
   1.105 +     ["Albert" "Einstein" "France" "1908-11-7" "1930-3-12"]
   1.106 +     ["Enrico" "Fermi" "Us" "1924-12-19" "1954-9-28"]
   1.107 +     ["Isaac" "Newton" "Uk" "1688-9-8" "1690-7-12"]
   1.108 +     ["Albert" "Einstein" "US" "1935-10-22" "1955-4-18"]
   1.109 +     ["Isaac" "Newton" "Germany" "1690-9-28" "1705-4-6"])))
   1.110 +
   1.111 +
   1.112 +(defn make-ideas []
   1.113 +  (with-connection *db2*
   1.114 +    (no-exceptions (drop-table :ideas))
   1.115 +    (create-table
   1.116 +     :ideas
   1.117 +     [:name     "varchar(255)"]
   1.118 +     [:surname  "varchar(255)"]
   1.119 +     [:idea     "varchar(255)"])
   1.120 +    (insert-rows
   1.121 +     :ideas
   1.122 +     ["Albert" "Einstein" "Theory of relativity (general)"]
   1.123 +     ["Enrico" "Fermi" "First nuclear reactor"]
   1.124 +     ["Albert" "Einstein" "Photoelectric effect"]
   1.125 +     ["Isaac" "Newton" "Gravity"]
   1.126 +     ["Galileo" "Galilei" "Telescope"]
   1.127 +     ["Albert" "Einstein" "Theory of relativity (special)"]
   1.128 +     ["Isaac" "Newton" "Dark matter"])))
   1.129 +
   1.130 +(defn clear-tables []
   1.131 +  (with-connection *db2* (dorun (map drop-table databases))))
   1.132 +
   1.133 +(defn fill-tables []
   1.134 +  (make-ideas)
   1.135 +  (make-essay-scientists)
   1.136 +  (make-scientist-whereabouts)
   1.137 +  (make-scientists))
   1.138 +
   1.139 +(defn make-empty-tables []
   1.140 +  (with-connection *db2*
   1.141 +    (create-table
   1.142 +     :ideas
   1.143 +     [:name     "varchar(255)"]
   1.144 +     [:surname  "varchar(255)"]
   1.145 +     [:idea     "varchar(255)"])
   1.146 +    (create-table
   1.147 +     :scientist_whereabouts
   1.148 +     [:name "varchar(255)"]
   1.149 +     [:surname "varchar(255)"]
   1.150 +     [:country "varchar(255)"]
   1.151 +     [:immigration_date "date"]
   1.152 +     [:emigration_date "date"])
   1.153 +    (create-table
   1.154 +     :scientists
   1.155 +     [:name "varchar(255)"]
   1.156 +     [:surname "varchar(255)"]
   1.157 +     [:birth_place "varchar(255)"]
   1.158 +     [:death_place "varchar(255)"]
   1.159 +     [:birth_date "date"]
   1.160 +     [:death_date "date"])
   1.161 +    (create-table
   1.162 +     :essay_scientists
   1.163 +     [:id "integer"]
   1.164 +     [:name "varchar(255)"]
   1.165 +     [:surname "varchar(255)"])))
   1.166 +
   1.167 +(def fill-tables-command  "mysql -u root -p clojure_rlm < scientists-a.in")
   1.168 +
   1.169 +    
   1.170 +
   1.171 +(defmethod < [java.sql.Date  java.sql.Date] [a b]
   1.172 +	   ({-1 true 0 false 1 false}
   1.173 +	   (.compareTo a b)))
   1.174 +
   1.175 +(defmethod > [java.sql.Date  java.sql.Date]
   1.176 +  ([a b]
   1.177 +     ({-1 true 0 false 1 false}
   1.178 +      (.compareTo a b))))
   1.179 +
   1.180 +
   1.181 +;; they can have the same date of birth and death!!!
   1.182 +(defn contemps [birth death]
   1.183 +  (with-connection *db*
   1.184 +    @(select
   1.185 +      (table :scientists)
   1.186 +      (where
   1.187 +       (or
   1.188 +	(and (<= :birth_date birth) (< birth :death_date))
   1.189 +	(and  (< :birth_date death) (<= death :death_date))
   1.190 +	(and  (<=  birth :birth_date) (< :birth_date death))
   1.191 +	(and  (<  birth :death_date) (<= :death_date death)))))))
   1.192 +
   1.193 +(defn essay-scientists []
   1.194 +  @(table *db* :essay_scientists))
   1.195 +
   1.196 +(defn get-info [table-name s]
   1.197 +  @(select 
   1.198 +    (select (table *db* table-name)
   1.199 +	    (where (= :name (:name s))))
   1.200 +    (where(= :surname (:surname s)))))
   1.201 +
   1.202 +(defn century* [#^java.sql.Date d]
   1.203 +  (* 100
   1.204 +     (.getCenturyOfEra
   1.205 +      (LocalDate. d))))
   1.206 +
   1.207 +
   1.208 +(defn century [#^java.sql.Date d]
   1.209 +  (* 100 (clojure.core/unchecked-divide (+ 1900 (.getYear d)) 100)))
   1.210 +
   1.211 +
   1.212 +
   1.213 +(defn get-scientist-data [s]
   1.214 +  (let [data (first (get-info :scientists s))
   1.215 +	locations (get-info :scientist_whereabouts s)
   1.216 +	ideas (map :idea (get-info :ideas s))
   1.217 +	birth (:birth_date data)
   1.218 +	death (:death_date data)
   1.219 +	name (:name data)
   1.220 +	surname (:surname data)
   1.221 +	contemps
   1.222 +	(remove #(and (= (:name %) name) (= (:surname %) surname))
   1.223 +		(map #(select-keys % [:name :surname])
   1.224 +		     (contemps birth death)))
   1.225 +	century (century birth)]
   1.226 +    {:data data :ideas ideas :locations locations :contemps contemps :century century}))
   1.227 +
   1.228 +(defn essay-date* [#^java.sql.Date d]
   1.229 +  (let [t (LocalDate. d)]
   1.230 +    (format "%02d/%02d/%04d" (.getDayOfMonth t) (.getMonthOfYear t) (.getYear t))))
   1.231 +
   1.232 +(defn essay-date [#^java.sql.Date d]
   1.233 +  (format "%02d/%02d/%04d" (.getDate d) (inc (.getMonth d)) (+ 1900 (.getYear d))))
   1.234 +  
   1.235 +(defn fucked-sort [m]
   1.236 +  (let [surname (:surname m)
   1.237 +	name (:name m)
   1.238 +	[surname n1] (.split surname " ")
   1.239 +	[name n2] (.split name " ")
   1.240 +	n1 (Integer/parseInt n1)
   1.241 +	n2 (Integer/parseInt n2)]
   1.242 +    [surname n1 name n2]))
   1.243 +
   1.244 +(defn lexical-sort [m]
   1.245 +  (let [surname (:surname m)
   1.246 +	name (:name m)]
   1.247 +    [surname name]))
   1.248 +  
   1.249 +
   1.250 +(defn-memo essay [s]
   1.251 +  (let [info (get-scientist-data s)
   1.252 +	name (:name (:data info))
   1.253 +	surname (:surname (:data info))]
   1.254 +       (str name
   1.255 +	    " "
   1.256 +	    surname
   1.257 +	    " ["
   1.258 +	    (:birth_place (:data info))
   1.259 +	    " "
   1.260 +	    (essay-date (:birth_date (:data info)))
   1.261 +	    ", "
   1.262 +	    (:death_place (:data info))
   1.263 +	    " "
   1.264 +	    (essay-date (:death_date (:data info)))
   1.265 +	    "]"
   1.266 +	    " is one of the most famous scientists of "
   1.267 +	    (:century info)". "
   1.268 +	    "Between all " name "'s ideas we mention: "
   1.269 +	    (apply str (interpose ", " (sort (:ideas info))))
   1.270 +	    ". "
   1.271 +	    name " lived in: "
   1.272 +	    (apply
   1.273 +	     str
   1.274 +	     (interpose ", "
   1.275 +			(map
   1.276 +			 (fn [entry]
   1.277 +			   (str (:country entry)
   1.278 +				" from "
   1.279 +				(essay-date (:immigration_date entry)) " to "
   1.280 +				(essay-date (:emigration_date entry))))
   1.281 +			 (sort-by :immigration_date  (:locations info)))))
   1.282 +	    ". "
   1.283 +	    name
   1.284 +	    " was a contemporary of "
   1.285 +	    (apply
   1.286 +	     str
   1.287 +	     (interpose
   1.288 +	      ", "
   1.289 +	      (map (fn [entry]
   1.290 +		     (str (:name entry)
   1.291 +			  " "
   1.292 +			  (:surname entry)))
   1.293 +		   
   1.294 +		   (sort-by lexical-sort
   1.295 +			    (:contemps info)))))
   1.296 +	     ".\n\n" )))
   1.297 +
   1.298 +(defn essays []
   1.299 +  (map essay (essay-scientists)))
   1.300 +
   1.301 +
   1.302 +(defn main [[username password db-name]]
   1.303 +  (let [db-host *host*
   1.304 +	db-port *port*
   1.305 +	db {:classname "com.mysql.jdbc.Driver"
   1.306 +	    :subprotocol "mysql"
   1.307 +	    :subname (str "//" db-host ":" db-port "/" db-name)
   1.308 +	    :user username
   1.309 +	    :password password}]
   1.310 +    (binding [*db* db]
   1.311 +      (print (apply str (essays))))))
   1.312 +
   1.313 +(def desired-md5 "e436d54f87c86bffb5d1b38d5f3e136b")
   1.314 +
   1.315 +(if (command-line?)
   1.316 +  (main *command-line-args*))
   1.317 +    
   1.318 +    
   1.319 +	    
   1.320 +