Mercurial > coderloop

comparison 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
comparison
equal deleted inserted replaced
-1:000000000000 0:307a81e46071
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