rlm@0: (ns coderloop.scientist-sql
rlm@0:   (:refer-clojure :only [])
rlm@0:   (:require rlm.ns-rlm rlm.light-base))
rlm@0: (rlm.ns-rlm/ns-clone rlm.light-base)
rlm@0: 
rlm@0: 
rlm@0: (use 'clojure.contrib.sql)               ;;' satisfy prettify
rlm@0: (import 'org.gjt.mm.mysql.Driver)
rlm@0: (use 'clojure.contrib.sql)
rlm@0: (import '[org.joda.time LocalDate ])
rlm@0: (undef distinct case compile drop take sort)
rlm@0: (use 'clojureql.core)
rlm@0: (import 'org.joda.time.DateMidnight)
rlm@0: (import 'java.io.File)
rlm@0: (use '[clojure.java.io :only [copy]])
rlm@0: (use '[clojure.contrib.shell-out :only [sh]])
rlm@0: (use '[clojure.string :only [split]])
rlm@0: (use '[coderloop [utils :only [md5]]])
rlm@0: 
rlm@0: (defn sql-date [date]
rlm@0:   "Convert any Joda-readable date object (including a string) to a java.sql.Date"
rlm@0:     (java.sql.Date. (.. (LocalDate. date) toDateMidnight toInstant getMillis)))
rlm@0: 
rlm@0: (def *port* 3306)
rlm@0: 
rlm@0: (def *host* "localhost")
rlm@0: 
rlm@0: (def *db-host* "localhost")
rlm@0: 
rlm@0: (def *db-name* "clojure_rlm")
rlm@0: 
rlm@0: (def *db* {:classname "com.mysql.jdbc.Driver"
rlm@0: 	   :subprotocol "mysql"
rlm@0: 	   :subname (str "//" *db-host* ":" *port* "/" *db-name*)
rlm@0: 	   :user "root"
rlm@0: 	   :password "sql1005025"})
rlm@0: 
rlm@0: (def *db2* {:classname "com.mysql.jdbc.Driver"
rlm@0: 	    :subprotocol "mysql"
rlm@0: 	    :subname (str "//" *db-host* ":" *port* "/" "test_rlm")
rlm@0: 	    :user "root"
rlm@0: 	    :password "sql1005025"})
rlm@0: 
rlm@0: 
rlm@0: (def databases [:essay_scientists :scientists :ideas :scientist_whereabouts])
rlm@0: 
rlm@0: (defmacro no-exceptions
rlm@0:   "Sweet relief like I never knew."
rlm@0:   [& forms]
rlm@0:   `(try ~@forms (catch Exception e# (.printStackTrace e#))))
rlm@0: 
rlm@0: 
rlm@0: (defn make-essay-scientists []
rlm@0:   (with-connection
rlm@0:     *db2*
rlm@0:     (no-exceptions (drop-table :essay_scientists))
rlm@0:     (create-table
rlm@0:      :essay_scientists
rlm@0:      [:id "integer"]
rlm@0:      [:name "varchar(255)"]
rlm@0:      [:surname "varchar(255)"])
rlm@0:     (insert-rows :essay_scientists
rlm@0: 		 [1 "Isaac" "Newton"]
rlm@0: 		 [2 "Albert" "Einstein"])))
rlm@0: 
rlm@0: (defn make-scientists []
rlm@0:   (with-connection
rlm@0:     *db2*
rlm@0:     (no-exceptions (drop-table :scientists))
rlm@0:     (create-table
rlm@0:      :scientists
rlm@0:      [:name "varchar(255)"]
rlm@0:      [:surname "varchar(255)"]
rlm@0:      [:birth_place "varchar(255)"]
rlm@0:      [:death_place "varchar(255)"]
rlm@0:      [:birth_date "date"]
rlm@0:      [:death_date "date"])
rlm@0:     (insert-rows
rlm@0:      :scientists
rlm@0:      ["Albert" "Einstein" "Ulm" "Princeton" "1879-3-14" "1955-4-18"]
rlm@0:      ["Galileo" "Galilei" "Pisa" "Arcetri" "1564-2-15" "1645-1-8"]
rlm@0:      ["Enrico" "Fermi" "Roma" "Chicago" "1901-9-29" "1954-9-28"]
rlm@0:      ["Isaac" "Newton" "Woolsthorpe-by-Colsterworth" "London" "1643-1-4" "1727-3-31"]
rlm@0:      ["Marx" "Planck" "Kiel" "Göttingen" "1858-4-23" "1947-10-4"])))
rlm@0: 
rlm@0: 
rlm@0: (defn make-scientist-whereabouts []
rlm@0:   (with-connection *db2*
rlm@0:     (no-exceptions (drop-table :scientist_whereabouts ))
rlm@0:     (create-table
rlm@0:      :scientist_whereabouts
rlm@0:      [:name "varchar(255)"]
rlm@0:      [:surname "varchar(255)"]
rlm@0:      [:country "varchar(255)"]
rlm@0:      [:immigration_date "date"]
rlm@0:      [:emigration_date "date"])
rlm@0:     (insert-rows
rlm@0:      :scientist_whereabouts
rlm@0:      ["Galileo" "Galilei" "France" "1602-2-18" "1604-4-20"]
rlm@0:      ["Albert" "Einstein" "Uk" "1901-10-22" "1905-5-4"]
rlm@0:      ["Marx" "Planck" "Denmark" "1901-1-31" "1945-7-4"]
rlm@0:      ["Albert" "Einstein" "France" "1908-11-7" "1930-3-12"]
rlm@0:      ["Enrico" "Fermi" "Us" "1924-12-19" "1954-9-28"]
rlm@0:      ["Isaac" "Newton" "Uk" "1688-9-8" "1690-7-12"]
rlm@0:      ["Albert" "Einstein" "US" "1935-10-22" "1955-4-18"]
rlm@0:      ["Isaac" "Newton" "Germany" "1690-9-28" "1705-4-6"])))
rlm@0: 
rlm@0: 
rlm@0: (defn make-ideas []
rlm@0:   (with-connection *db2*
rlm@0:     (no-exceptions (drop-table :ideas))
rlm@0:     (create-table
rlm@0:      :ideas
rlm@0:      [:name     "varchar(255)"]
rlm@0:      [:surname  "varchar(255)"]
rlm@0:      [:idea     "varchar(255)"])
rlm@0:     (insert-rows
rlm@0:      :ideas
rlm@0:      ["Albert" "Einstein" "Theory of relativity (general)"]
rlm@0:      ["Enrico" "Fermi" "First nuclear reactor"]
rlm@0:      ["Albert" "Einstein" "Photoelectric effect"]
rlm@0:      ["Isaac" "Newton" "Gravity"]
rlm@0:      ["Galileo" "Galilei" "Telescope"]
rlm@0:      ["Albert" "Einstein" "Theory of relativity (special)"]
rlm@0:      ["Isaac" "Newton" "Dark matter"])))
rlm@0: 
rlm@0: (defn clear-tables []
rlm@0:   (with-connection *db2* (dorun (map drop-table databases))))
rlm@0: 
rlm@0: (defn fill-tables []
rlm@0:   (make-ideas)
rlm@0:   (make-essay-scientists)
rlm@0:   (make-scientist-whereabouts)
rlm@0:   (make-scientists))
rlm@0: 
rlm@0: (defn make-empty-tables []
rlm@0:   (with-connection *db2*
rlm@0:     (create-table
rlm@0:      :ideas
rlm@0:      [:name     "varchar(255)"]
rlm@0:      [:surname  "varchar(255)"]
rlm@0:      [:idea     "varchar(255)"])
rlm@0:     (create-table
rlm@0:      :scientist_whereabouts
rlm@0:      [:name "varchar(255)"]
rlm@0:      [:surname "varchar(255)"]
rlm@0:      [:country "varchar(255)"]
rlm@0:      [:immigration_date "date"]
rlm@0:      [:emigration_date "date"])
rlm@0:     (create-table
rlm@0:      :scientists
rlm@0:      [:name "varchar(255)"]
rlm@0:      [:surname "varchar(255)"]
rlm@0:      [:birth_place "varchar(255)"]
rlm@0:      [:death_place "varchar(255)"]
rlm@0:      [:birth_date "date"]
rlm@0:      [:death_date "date"])
rlm@0:     (create-table
rlm@0:      :essay_scientists
rlm@0:      [:id "integer"]
rlm@0:      [:name "varchar(255)"]
rlm@0:      [:surname "varchar(255)"])))
rlm@0: 
rlm@0: (def fill-tables-command  "mysql -u root -p clojure_rlm < scientists-a.in")
rlm@0: 
rlm@0:     
rlm@0: 
rlm@0: (defmethod < [java.sql.Date  java.sql.Date] [a b]
rlm@0: 	   ({-1 true 0 false 1 false}
rlm@0: 	   (.compareTo a b)))
rlm@0: 
rlm@0: (defmethod > [java.sql.Date  java.sql.Date]
rlm@0:   ([a b]
rlm@0:      ({-1 true 0 false 1 false}
rlm@0:       (.compareTo a b))))
rlm@0: 
rlm@0: 
rlm@0: ;; they can have the same date of birth and death!!!
rlm@0: (defn contemps [birth death]
rlm@0:   (with-connection *db*
rlm@0:     @(select
rlm@0:       (table :scientists)
rlm@0:       (where
rlm@0:        (or
rlm@0: 	(and (<= :birth_date birth) (< birth :death_date))
rlm@0: 	(and  (< :birth_date death) (<= death :death_date))
rlm@0: 	(and  (<=  birth :birth_date) (< :birth_date death))
rlm@0: 	(and  (<  birth :death_date) (<= :death_date death)))))))
rlm@0: 
rlm@0: (defn essay-scientists []
rlm@0:   @(table *db* :essay_scientists))
rlm@0: 
rlm@0: (defn get-info [table-name s]
rlm@0:   @(select 
rlm@0:     (select (table *db* table-name)
rlm@0: 	    (where (= :name (:name s))))
rlm@0:     (where(= :surname (:surname s)))))
rlm@0: 
rlm@0: (defn century* [#^java.sql.Date d]
rlm@0:   (* 100
rlm@0:      (.getCenturyOfEra
rlm@0:       (LocalDate. d))))
rlm@0: 
rlm@0: 
rlm@0: (defn century [#^java.sql.Date d]
rlm@0:   (* 100 (clojure.core/unchecked-divide (+ 1900 (.getYear d)) 100)))
rlm@0: 
rlm@0: 
rlm@0: 
rlm@0: (defn get-scientist-data [s]
rlm@0:   (let [data (first (get-info :scientists s))
rlm@0: 	locations (get-info :scientist_whereabouts s)
rlm@0: 	ideas (map :idea (get-info :ideas s))
rlm@0: 	birth (:birth_date data)
rlm@0: 	death (:death_date data)
rlm@0: 	name (:name data)
rlm@0: 	surname (:surname data)
rlm@0: 	contemps
rlm@0: 	(remove #(and (= (:name %) name) (= (:surname %) surname))
rlm@0: 		(map #(select-keys % [:name :surname])
rlm@0: 		     (contemps birth death)))
rlm@0: 	century (century birth)]
rlm@0:     {:data data :ideas ideas :locations locations :contemps contemps :century century}))
rlm@0: 
rlm@0: (defn essay-date* [#^java.sql.Date d]
rlm@0:   (let [t (LocalDate. d)]
rlm@0:     (format "%02d/%02d/%04d" (.getDayOfMonth t) (.getMonthOfYear t) (.getYear t))))
rlm@0: 
rlm@0: (defn essay-date [#^java.sql.Date d]
rlm@0:   (format "%02d/%02d/%04d" (.getDate d) (inc (.getMonth d)) (+ 1900 (.getYear d))))
rlm@0:   
rlm@0: (defn fucked-sort [m]
rlm@0:   (let [surname (:surname m)
rlm@0: 	name (:name m)
rlm@0: 	[surname n1] (.split surname " ")
rlm@0: 	[name n2] (.split name " ")
rlm@0: 	n1 (Integer/parseInt n1)
rlm@0: 	n2 (Integer/parseInt n2)]
rlm@0:     [surname n1 name n2]))
rlm@0: 
rlm@0: (defn lexical-sort [m]
rlm@0:   (let [surname (:surname m)
rlm@0: 	name (:name m)]
rlm@0:     [surname name]))
rlm@0:   
rlm@0: 
rlm@0: (defn-memo essay [s]
rlm@0:   (let [info (get-scientist-data s)
rlm@0: 	name (:name (:data info))
rlm@0: 	surname (:surname (:data info))]
rlm@0:        (str name
rlm@0: 	    " "
rlm@0: 	    surname
rlm@0: 	    " ["
rlm@0: 	    (:birth_place (:data info))
rlm@0: 	    " "
rlm@0: 	    (essay-date (:birth_date (:data info)))
rlm@0: 	    ", "
rlm@0: 	    (:death_place (:data info))
rlm@0: 	    " "
rlm@0: 	    (essay-date (:death_date (:data info)))
rlm@0: 	    "]"
rlm@0: 	    " is one of the most famous scientists of "
rlm@0: 	    (:century info)". "
rlm@0: 	    "Between all " name "'s ideas we mention: "
rlm@0: 	    (apply str (interpose ", " (sort (:ideas info))))
rlm@0: 	    ". "
rlm@0: 	    name " lived in: "
rlm@0: 	    (apply
rlm@0: 	     str
rlm@0: 	     (interpose ", "
rlm@0: 			(map
rlm@0: 			 (fn [entry]
rlm@0: 			   (str (:country entry)
rlm@0: 				" from "
rlm@0: 				(essay-date (:immigration_date entry)) " to "
rlm@0: 				(essay-date (:emigration_date entry))))
rlm@0: 			 (sort-by :immigration_date  (:locations info)))))
rlm@0: 	    ". "
rlm@0: 	    name
rlm@0: 	    " was a contemporary of "
rlm@0: 	    (apply
rlm@0: 	     str
rlm@0: 	     (interpose
rlm@0: 	      ", "
rlm@0: 	      (map (fn [entry]
rlm@0: 		     (str (:name entry)
rlm@0: 			  " "
rlm@0: 			  (:surname entry)))
rlm@0: 		   
rlm@0: 		   (sort-by lexical-sort
rlm@0: 			    (:contemps info)))))
rlm@0: 	     ".\n\n" )))
rlm@0: 
rlm@0: (defn essays []
rlm@0:   (map essay (essay-scientists)))
rlm@0: 
rlm@0: 
rlm@0: (defn main [[username password db-name]]
rlm@0:   (let [db-host *host*
rlm@0: 	db-port *port*
rlm@0: 	db {:classname "com.mysql.jdbc.Driver"
rlm@0: 	    :subprotocol "mysql"
rlm@0: 	    :subname (str "//" db-host ":" db-port "/" db-name)
rlm@0: 	    :user username
rlm@0: 	    :password password}]
rlm@0:     (binding [*db* db]
rlm@0:       (print (apply str (essays))))))
rlm@0: 
rlm@0: (def desired-md5 "e436d54f87c86bffb5d1b38d5f3e136b")
rlm@0: 
rlm@0: (if (command-line?)
rlm@0:   (main *command-line-args*))
rlm@0:     
rlm@0:     
rlm@0: 	    
rlm@0: