Mercurial > coderloop

     1 (ns coderloop.scientist-sql

     2   (:refer-clojure :only [])

     3   (:require rlm.ns-rlm rlm.light-base))

     4 (rlm.ns-rlm/ns-clone rlm.light-base)

     5 

     6 

     7 (use 'clojure.contrib.sql)               ;;' satisfy prettify

     8 (import 'org.gjt.mm.mysql.Driver)

     9 (use 'clojure.contrib.sql)

    10 (import '[org.joda.time LocalDate ])

    11 (undef distinct case compile drop take sort)

    12 (use 'clojureql.core)

    13 (import 'org.joda.time.DateMidnight)

    14 (import 'java.io.File)

    15 (use '[clojure.java.io :only [copy]])

    16 (use '[clojure.contrib.shell-out :only [sh]])

    17 (use '[clojure.string :only [split]])

    18 (use '[coderloop [utils :only [md5]]])

    19 

    20 (defn sql-date [date]

    21   "Convert any Joda-readable date object (including a string) to a java.sql.Date"

    22     (java.sql.Date. (.. (LocalDate. date) toDateMidnight toInstant getMillis)))

    23 

    24 (def *port* 3306)

    25 

    26 (def *host* "localhost")

    27 

    28 (def *db-host* "localhost")

    29 

    30 (def *db-name* "clojure_rlm")

    31 

    32 (def *db* {:classname "com.mysql.jdbc.Driver"

    33 	   :subprotocol "mysql"

    34 	   :subname (str "//" *db-host* ":" *port* "/" *db-name*)

    35 	   :user "root"

    36 	   :password "sql1005025"})

    37 

    38 (def *db2* {:classname "com.mysql.jdbc.Driver"

    39 	    :subprotocol "mysql"

    40 	    :subname (str "//" *db-host* ":" *port* "/" "test_rlm")

    41 	    :user "root"

    42 	    :password "sql1005025"})

    43 

    44 

    45 (def databases [:essay_scientists :scientists :ideas :scientist_whereabouts])

    46 

    47 (defmacro no-exceptions

    48   "Sweet relief like I never knew."

    49   [& forms]

    50   `(try ~@forms (catch Exception e# (.printStackTrace e#))))

    51 

    52 

    53 (defn make-essay-scientists []

    54   (with-connection

    55     *db2*

    56     (no-exceptions (drop-table :essay_scientists))

    57     (create-table

    58      :essay_scientists

    59      [:id "integer"]

    60      [:name "varchar(255)"]

    61      [:surname "varchar(255)"])

    62     (insert-rows :essay_scientists

    63 		 [1 "Isaac" "Newton"]

    64 		 [2 "Albert" "Einstein"])))

    65 

    66 (defn make-scientists []

    67   (with-connection

    68     *db2*

    69     (no-exceptions (drop-table :scientists))

    70     (create-table

    71      :scientists

    72      [:name "varchar(255)"]

    73      [:surname "varchar(255)"]

    74      [:birth_place "varchar(255)"]

    75      [:death_place "varchar(255)"]

    76      [:birth_date "date"]

    77      [:death_date "date"])

    78     (insert-rows

    79      :scientists

    80      ["Albert" "Einstein" "Ulm" "Princeton" "1879-3-14" "1955-4-18"]

    81      ["Galileo" "Galilei" "Pisa" "Arcetri" "1564-2-15" "1645-1-8"]

    82      ["Enrico" "Fermi" "Roma" "Chicago" "1901-9-29" "1954-9-28"]

    83      ["Isaac" "Newton" "Woolsthorpe-by-Colsterworth" "London" "1643-1-4" "1727-3-31"]

    84      ["Marx" "Planck" "Kiel" "Göttingen" "1858-4-23" "1947-10-4"])))

    85 

    86 

    87 (defn make-scientist-whereabouts []

    88   (with-connection *db2*

    89     (no-exceptions (drop-table :scientist_whereabouts ))

    90     (create-table

    91      :scientist_whereabouts

    92      [:name "varchar(255)"]

    93      [:surname "varchar(255)"]

    94      [:country "varchar(255)"]

    95      [:immigration_date "date"]

    96      [:emigration_date "date"])

    97     (insert-rows

    98      :scientist_whereabouts

    99      ["Galileo" "Galilei" "France" "1602-2-18" "1604-4-20"]

   100      ["Albert" "Einstein" "Uk" "1901-10-22" "1905-5-4"]

   101      ["Marx" "Planck" "Denmark" "1901-1-31" "1945-7-4"]

   102      ["Albert" "Einstein" "France" "1908-11-7" "1930-3-12"]

   103      ["Enrico" "Fermi" "Us" "1924-12-19" "1954-9-28"]

   104      ["Isaac" "Newton" "Uk" "1688-9-8" "1690-7-12"]

   105      ["Albert" "Einstein" "US" "1935-10-22" "1955-4-18"]

   106      ["Isaac" "Newton" "Germany" "1690-9-28" "1705-4-6"])))

   107 

   108 

   109 (defn make-ideas []

   110   (with-connection *db2*

   111     (no-exceptions (drop-table :ideas))

   112     (create-table

   113      :ideas

   114      [:name     "varchar(255)"]

   115      [:surname  "varchar(255)"]

   116      [:idea     "varchar(255)"])

   117     (insert-rows

   118      :ideas

   119      ["Albert" "Einstein" "Theory of relativity (general)"]

   120      ["Enrico" "Fermi" "First nuclear reactor"]

   121      ["Albert" "Einstein" "Photoelectric effect"]

   122      ["Isaac" "Newton" "Gravity"]

   123      ["Galileo" "Galilei" "Telescope"]

   124      ["Albert" "Einstein" "Theory of relativity (special)"]

   125      ["Isaac" "Newton" "Dark matter"])))

   126 

   127 (defn clear-tables []

   128   (with-connection *db2* (dorun (map drop-table databases))))

   129 

   130 (defn fill-tables []

   131   (make-ideas)

   132   (make-essay-scientists)

   133   (make-scientist-whereabouts)

   134   (make-scientists))

   135 

   136 (defn make-empty-tables []

   137   (with-connection *db2*

   138     (create-table

   139      :ideas

   140      [:name     "varchar(255)"]

   141      [:surname  "varchar(255)"]

   142      [:idea     "varchar(255)"])

   143     (create-table

   144      :scientist_whereabouts

   145      [:name "varchar(255)"]

   146      [:surname "varchar(255)"]

   147      [:country "varchar(255)"]

   148      [:immigration_date "date"]

   149      [:emigration_date "date"])

   150     (create-table

   151      :scientists

   152      [:name "varchar(255)"]

   153      [:surname "varchar(255)"]

   154      [:birth_place "varchar(255)"]

   155      [:death_place "varchar(255)"]

   156      [:birth_date "date"]

   157      [:death_date "date"])

   158     (create-table

   159      :essay_scientists

   160      [:id "integer"]

   161      [:name "varchar(255)"]

   162      [:surname "varchar(255)"])))

   163 

   164 (def fill-tables-command  "mysql -u root -p clojure_rlm < scientists-a.in")

   165 

   166     

   167 

   168 (defmethod < [java.sql.Date  java.sql.Date] [a b]

   169 	   ({-1 true 0 false 1 false}

   170 	   (.compareTo a b)))

   171 

   172 (defmethod > [java.sql.Date  java.sql.Date]

   173   ([a b]

   174      ({-1 true 0 false 1 false}

   175       (.compareTo a b))))

   176 

   177 

   178 ;; they can have the same date of birth and death!!!

   179 (defn contemps [birth death]

   180   (with-connection *db*

   181     @(select

   182       (table :scientists)

   183       (where

   184        (or

   185 	(and (<= :birth_date birth) (< birth :death_date))

   186 	(and  (< :birth_date death) (<= death :death_date))

   187 	(and  (<=  birth :birth_date) (< :birth_date death))

   188 	(and  (<  birth :death_date) (<= :death_date death)))))))

   189 

   190 (defn essay-scientists []

   191   @(table *db* :essay_scientists))

   192 

   193 (defn get-info [table-name s]

   194   @(select 

   195     (select (table *db* table-name)

   196 	    (where (= :name (:name s))))

   197     (where(= :surname (:surname s)))))

   198 

   199 (defn century* [#^java.sql.Date d]

   200   (* 100

   201      (.getCenturyOfEra

   202       (LocalDate. d))))

   203 

   204 

   205 (defn century [#^java.sql.Date d]

   206   (* 100 (clojure.core/unchecked-divide (+ 1900 (.getYear d)) 100)))

   207 

   208 

   209 

   210 (defn get-scientist-data [s]

   211   (let [data (first (get-info :scientists s))

   212 	locations (get-info :scientist_whereabouts s)

   213 	ideas (map :idea (get-info :ideas s))

   214 	birth (:birth_date data)

   215 	death (:death_date data)

   216 	name (:name data)

   217 	surname (:surname data)

   218 	contemps

   219 	(remove #(and (= (:name %) name) (= (:surname %) surname))

   220 		(map #(select-keys % [:name :surname])

   221 		     (contemps birth death)))

   222 	century (century birth)]

   223     {:data data :ideas ideas :locations locations :contemps contemps :century century}))

   224 

   225 (defn essay-date* [#^java.sql.Date d]

   226   (let [t (LocalDate. d)]

   227     (format "%02d/%02d/%04d" (.getDayOfMonth t) (.getMonthOfYear t) (.getYear t))))

   228 

   229 (defn essay-date [#^java.sql.Date d]

   230   (format "%02d/%02d/%04d" (.getDate d) (inc (.getMonth d)) (+ 1900 (.getYear d))))

   231   

   232 (defn fucked-sort [m]

   233   (let [surname (:surname m)

   234 	name (:name m)

   235 	[surname n1] (.split surname " ")

   236 	[name n2] (.split name " ")

   237 	n1 (Integer/parseInt n1)

   238 	n2 (Integer/parseInt n2)]

   239     [surname n1 name n2]))

   240 

   241 (defn lexical-sort [m]

   242   (let [surname (:surname m)

   243 	name (:name m)]

   244     [surname name]))

   245   

   246 

   247 (defn-memo essay [s]

   248   (let [info (get-scientist-data s)

   249 	name (:name (:data info))

   250 	surname (:surname (:data info))]

   251        (str name

   252 	    " "

   253 	    surname

   254 	    " ["

   255 	    (:birth_place (:data info))

   256 	    " "

   257 	    (essay-date (:birth_date (:data info)))

   258 	    ", "

   259 	    (:death_place (:data info))

   260 	    " "

   261 	    (essay-date (:death_date (:data info)))

   262 	    "]"

   263 	    " is one of the most famous scientists of "

   264 	    (:century info)". "

   265 	    "Between all " name "'s ideas we mention: "

   266 	    (apply str (interpose ", " (sort (:ideas info))))

   267 	    ". "

   268 	    name " lived in: "

   269 	    (apply

   270 	     str

   271 	     (interpose ", "

   272 			(map

   273 			 (fn [entry]

   274 			   (str (:country entry)

   275 				" from "

   276 				(essay-date (:immigration_date entry)) " to "

   277 				(essay-date (:emigration_date entry))))

   278 			 (sort-by :immigration_date  (:locations info)))))

   279 	    ". "

   280 	    name

   281 	    " was a contemporary of "

   282 	    (apply

   283 	     str

   284 	     (interpose

   285 	      ", "

   286 	      (map (fn [entry]

   287 		     (str (:name entry)

   288 			  " "

   289 			  (:surname entry)))

   290 		   

   291 		   (sort-by lexical-sort

   292 			    (:contemps info)))))

   293 	     ".\n\n" )))

   294 

   295 (defn essays []

   296   (map essay (essay-scientists)))

   297 

   298 

   299 (defn main [[username password db-name]]

   300   (let [db-host *host*

   301 	db-port *port*

   302 	db {:classname "com.mysql.jdbc.Driver"

   303 	    :subprotocol "mysql"

   304 	    :subname (str "//" db-host ":" db-port "/" db-name)

   305 	    :user username

   306 	    :password password}]

   307     (binding [*db* db]

   308       (print (apply str (essays))))))

   309 

   310 (def desired-md5 "e436d54f87c86bffb5d1b38d5f3e136b")

   311 

   312 (if (command-line?)

   313   (main *command-line-args*))

   314     

   315     

   316 	    

   317
author	Robert McIntyre <rlm@mit.edu>
date	Tue, 18 Oct 2011 01:17:49 -0700
parents
children