1 | (ns com.github.kyleburton.sandbox.mdb |
2 | (:import (com.healthmarketscience.jackcess |
3 | Database TableBuilder |
4 | ColumnBuilder DataType Table) |
5 | (java.io File)) |
6 | (:require [com.github.kyleburton.sandbox.utils :as kutils] |
7 | [clojure.contrib.duck-streams :as ds] |
8 | [clojure.contrib.str-utils :as str])) |
9 | |
10 | (defn new-col [name type] |
11 | (let [cb (ColumnBuilder. name)] |
12 | (.setSQLType cb (.getSQLType type)) |
13 | (.toColumn cb))) |
14 | |
15 | (defmacro with-database [[var location] & body] |
16 | `(let [location# (ds/file (str ~location)) |
17 | exists# (.exists location#)] |
18 | (with-open [~var (if exists# |
19 | (Database/open location#) |
20 | (Database/create location#))] |
21 | (let [res# (do ~@body)] |
22 | (.flush ~var) |
23 | res#)))) |
24 | |
25 | (defn- ->safe-name [thing] |
26 | (.replaceAll |
27 | (cond (keyword? thing) |
28 | (.substring (str thing) 1) |
29 | (string? thing) |
30 | thing |
31 | true |
32 | (str thing)) |
33 | "[^a-zA-Z0-9]" "_")) |
34 | |
35 | (defn create-table [#^Database db #^String table-name & cols] |
36 | (let [builder (TableBuilder. table-name)] |
37 | (loop [[[name type] & cols] (partition 2 cols)] |
38 | (if name |
39 | (do |
40 | (.addColumn builder (new-col (->safe-name name) type)) |
41 | (recur cols)))) |
42 | (.toTable builder db))) |
43 | |
44 | (defn create-or-get-table [#^Database db #^String table-name & cols] |
45 | (if (.contains (.getTableNames db) |
46 | table-name) |
47 | (.getTable db table-name) |
48 | (apply create-table db table-name cols))) |
49 | |
50 | |
51 | ;; (kutils/doc-class |
52 | ;; (with-database [db (kutils/$HOME "test.mdb")] |
53 | ;; (.getTableNames db))) |
54 | |
55 | |
56 | (defn insert-row [tbl & fields] |
57 | (.addRow tbl (to-array fields))) |
58 | |
59 | ;; (class (with-database [db (kutils/$HOME "test.mdb")] db)) |
60 | |
61 | (defn row->seq [cols row] |
62 | (map #(get row (.getName %)) cols)) |
63 | |
64 | (defn for-each-row |
65 | ([#^Table tbl fn] |
66 | (let [cols (.getColumns tbl)] |
67 | (.reset tbl) |
68 | (loop [row (.getNextRow tbl)] |
69 | (if row |
70 | (do |
71 | (fn (row->seq cols row)) |
72 | (recur (.getNextRow tbl))))))) |
73 | ([#^Database db #^String tbl fn] |
74 | (for-each-row (.getTable db tbl) fn))) |
75 | |
76 | (defn table->tab-file |
77 | [#^String mdb-file #^String table-name #^String tab-file] |
78 | (with-database [db mdb-file] |
79 | (with-open [out (ds/writer tab-file)] |
80 | (binding [*out* out] |
81 | (for-each-row |
82 | db |
83 | table-name |
84 | (fn [row] |
85 | (println (str/str-join "\t" row)))))))) |
86 | |
87 | |
88 | (defn- ->map [m] |
89 | (reduce (fn [r k] |
90 | (assoc r (keyword (.toLowerCase k)) (.get m k))) |
91 | {} |
92 | (keys m))) |
93 | |
94 | ;; (with-database [db (kutils/$HOME "test.mdb")] |
95 | ;; (prn (format "tables: %s" (.getTableNames db))) |
96 | ;; (let [tbl (create-or-get-table |
97 | ;; db |
98 | ;; "TABLE_A" |
99 | ;; :id DataType/INT |
100 | ;; :fname DataType/TEXT |
101 | ;; :lname DataType/TEXT |
102 | ;; :addr1 DataType/TEXT |
103 | ;; :addr2 DataType/TEXT |
104 | ;; :city DataType/TEXT |
105 | ;; :state DataType/TEXT |
106 | ;; :zip DataType/TEXT)] |
107 | ;; (insert-row tbl 1 "Kyle" "Burton" "2700 Horizon Drive" "STE 200" "King of Prussia" "PA" "19401") |
108 | ;; (insert-row tbl 1 "Kyle" "Burton" "625 W. Rdige Pike" "STE 400" "Conshohocken" "PA" "19401") |
109 | ;; (insert-row tbl 1 "Kyle" "Burton" "123 Main St" "" "Wayne" "PA" "19401"))) |
110 | |
111 | (defn mdb->tables [mdb] |
112 | (with-database [db mdb] |
113 | (seq (.getTableNames db)))) |
114 | |
115 | ;; (mdb->tables (kutils/$HOME "test.mdb")) |
116 | |
117 | |
118 | ;; (table->tab-file |
119 | ;; (kutils/$HOME "test.mdb") |
120 | ;; "TABLE_A" |
121 | ;; (kutils/$HOME "table_a.tab")) |
122 | |
123 | (defn tab-file->table [mdb table file] |
124 | (with-database [db mdb] |
125 | (.importFile db table (ds/file file) "\t"))) |
126 | |
127 | ;; (tab-file->table (kutils/$HOME "test.mdb") |
128 | ;; "TABLE_B" |
129 | ;; (kutils/$HOME "table_a.tab")) |
130 | |
131 | (defn display-table [mdb tbl] |
132 | (with-database [db mdb] |
133 | (.display (.getTable db tbl)))) |
134 | |
135 | ;; (print (display-table (kutils/$HOME "test.mdb") "TABLE_A")) |
136 | |
137 | ;; (with-database [db (kutils/$HOME "test.mdb")] |
138 | ;; (prn (format "tables: %s" (.getTableNames db))) |
139 | ;; (let [tbl (.getTable db "TABLE_A")] |
140 | ;; (prn (format "columns[%s]: %s" |
141 | ;; (.getName tbl) |
142 | ;; (map #(.getName %) (seq (.getColumns tbl))))) |
143 | ;; (do |
144 | ;; (.reset tbl) |
145 | ;; (loop [row (.getNextRow tbl)] |
146 | ;; (if row |
147 | ;; (do |
148 | ;; (prn (format "row: %s" row)) |
149 | ;; ;(prn (format " %s" (->map row))) |
150 | ;; ;(prn (format " id: %s" (:id (->map row)))) |
151 | ;; (recur (.getNextRow tbl)))))))) |
152 | |
153 | (defmulti truncate-table (fn [& args] (map class args))) |
154 | |
155 | (defmethod truncate-table [String String] [mdb table] |
156 | (prn "S,S; opening db and delegating") |
157 | (with-database [db mdb] |
158 | (truncate-table db table))) |
159 | |
160 | (defmethod truncate-table [File String] [mdb table] |
161 | (prn "F,S; opening db and delegating") |
162 | (with-database [db mdb] |
163 | (truncate-table db table))) |
164 | |
165 | (defmethod truncate-table [Database String] [db table] |
166 | (prn "D,S; getting table and delegating") |
167 | (truncate-table db (.getTable db table))) |
168 | |
169 | ;; probably faster to drop/create the table...but I don't see how to |
170 | ;; delete a table in the api? deleteCurrentRow doesn't seem to |
171 | ;; work... |
172 | (defmethod truncate-table [Database Table] [db table] |
173 | (prn "D,T; rubber meets the road") |
174 | (.reset table) |
175 | (loop [row (.getNextRow table)] |
176 | (prn "in loop, row:" row) |
177 | (if row |
178 | (do |
179 | (prn "deleting row: " row) |
180 | (.deleteCurrentRow table) |
181 | (recur (.getNextRow table)))))) |
182 | |
183 | |
184 | ;; (mdb->tables (kutils/$HOME "tmp" "test.mdb")) |
185 | ;; (truncate-table (kutils/$HOME "tmp" "test.mdb") "TABLE_B") |
186 | |
187 | ;; (print (display-table (kutils/$HOME "test.mdb") "TABLE_A")) |
188 | ;; (print (display-table (kutils/$HOME "test.mdb") "TABLE_B")) |
189 | |
190 | ;; (with-database [db (kutils/$HOME "test.mdb")] |
191 | ;; (for-each-row db "TABLE_B" |
192 | ;; (fn [row] (prn "row=" row)))) |
193 | |