1 | (ns com.github.kyleburton.sandbox.sql |
2 | (use [clojure.contrib.sql :as sql] |
3 | [com.github.kyleburton.sandbox.utils :as kutils])) |
4 | |
5 | |
6 | (defn- range-sql [end] |
7 | (range 1 (+ 1 end))) |
8 | |
9 | |
10 | (defn db-schemas |
11 | "Returns a list of the schema names in the database." |
12 | [db] |
13 | (with-connection db |
14 | (let [schemas (.getSchemas (.getMetaData (connection)))] |
15 | (loop [has-next (.next schemas) |
16 | res []] |
17 | (if has-next |
18 | (let [schema (.getString schemas 1)] |
19 | (recur (.next schemas) |
20 | (conj res schema))) |
21 | res))))) |
22 | |
23 | (defn schema-tables |
24 | "Returns a list of maps describing the tables in the database. The |
25 | maps include: :catalog, :schema, :name, :type and :remarks as per |
26 | the JDBC spec." |
27 | [db schema] |
28 | (with-connection db |
29 | (let [db-meta (.getMetaData (connection)) |
30 | tables (.getTables db-meta nil schema "%" nil)] |
31 | (loop [has-next (.next tables) |
32 | res []] |
33 | (if has-next |
34 | (let [table {:catalog (.getString tables 1) |
35 | :schema (.getString tables 2) |
36 | :name (.getString tables 3) |
37 | :type (.getString tables 4) |
38 | :remakrs (.getString tables 5)}] |
39 | (recur (.next tables) |
40 | (conj res table))) |
41 | res))))) |
42 | |
43 | (defn select-all [table] |
44 | (format "SELECT * FROM %s" table)) |
45 | |
46 | (defn describe-table |
47 | "Returns a list of column descriptions (maps) for the table. The |
48 | maps |
49 | contain: :name, :catalog, :display-zie, :type, :precision, :scale, |
50 | :is-auto-increment, :is-case-sensitive, :is-currency, |
51 | :is-definitely-writable, :is-nullable, :is-read-only, |
52 | :is-searchable, :is-signed, :is-writable." |
53 | [db table-name] |
54 | (with-connection db |
55 | (let [ps (.prepareStatement (connection) (format "SELECT * FROM %s WHERE 0 = 1" table-name)) |
56 | rs (.executeQuery ps) |
57 | rs-meta (.getMetaData rs)] |
58 | (loop [[idx & idxs] (range-sql (.getColumnCount rs-meta)) |
59 | res []] |
60 | (if idx |
61 | (recur idxs (conj res {:name (.getColumnName rs-meta idx) |
62 | :catalog (.getCatalogName rs-meta idx) |
63 | :display-size (.getColumnDisplaySize rs-meta idx) |
64 | :type (.getColumnType rs-meta idx) |
65 | :precision (.getPrecision rs-meta idx) |
66 | :scale (.getScale rs-meta idx) |
67 | :is-auto-increment (.isAutoIncrement rs-meta idx) |
68 | :is-case-sensitive (.isCaseSensitive rs-meta idx) |
69 | :is-currency (.isCurrency rs-meta idx) |
70 | :is-definitely-writable (.isDefinitelyWritable rs-meta idx) |
71 | :is-nullable (.isNullable rs-meta idx) |
72 | :is-read-only (.isReadOnly rs-meta idx) |
73 | :is-searchable (.isSearchable rs-meta idx) |
74 | :is-signed (.isSigned rs-meta idx) |
75 | :is-writable (.isWritable rs-meta idx)})) |
76 | res))))) |
77 | |
78 | |
79 | (defn rs->record [rs] |
80 | (loop [[idx & idxs] (range-sql (.getColumnCount (.getMetaData rs))) |
81 | res []] |
82 | (if idx |
83 | (recur idxs |
84 | (conj res (.getString rs idx))) |
85 | res))) |
86 | |
87 | (defn rs->map [rs] |
88 | (loop [meta (.getMetaData rs) |
89 | [idx & idxs] (range-sql (.getColumnCount meta)) |
90 | res {}] |
91 | (if idx |
92 | (recur meta |
93 | idxs |
94 | (assoc res (.getColumnName meta idx) (.getString rs idx))) |
95 | res))) |
96 | |
97 | ;; TODO: support bind variables... |
98 | (defn sql->records [db sql] |
99 | (with-connection db |
100 | (let [ps (.prepareStatement (connection) sql) |
101 | rs (.executeQuery ps)] |
102 | (loop [has-next (.next rs) |
103 | res []] |
104 | (if has-next |
105 | (let [rec (rs->record rs)] |
106 | (recur (.next rs) |
107 | (conj res rec))) |
108 | res))))) |
109 | |
110 | (defn sql->maps [db sql] |
111 | (with-connection db |
112 | (let [ps (.prepareStatement (connection) sql) |
113 | rs (.executeQuery ps)] |
114 | (loop [has-next (.next rs) |
115 | res []] |
116 | (if has-next |
117 | (let [rec (rs->map rs)] |
118 | (recur (.next rs) |
119 | (conj res rec))) |
120 | res))))) |
121 | |
122 | (defmacro do-rs-rows |
123 | [db sql var & body] |
124 | `(with-connection ~db |
125 | (let [ps# (.prepareStatement (connection) ~sql) |
126 | rs# (.executeQuery ps#) |
127 | fn# (fn [~var] ~@body)] |
128 | (loop [has-next# (.next rs#)] |
129 | (if has-next# |
130 | (do |
131 | (fn# (rs->record rs#)) |
132 | (recur (.next rs#)))))))) |
133 | |
134 | (defmacro do-rs-maps [db sql var & body] |
135 | `(with-connection ~db |
136 | (let [ps# (.prepareStatement (connection) ~sql) |
137 | rs# (.executeQuery ps#) |
138 | fn# (fn [~var] ~@body)] |
139 | (loop [has-next# (.next rs#)] |
140 | (if has-next# |
141 | (do |
142 | (fn# (rs->map rs#)) |
143 | (recur (.next rs#)))))))) |
144 | |