dbquery.el (9055B)
1 ;;; dbquery - Query RDBMS, S-expression output, draw ER diagrams and more. 2 ;;; Copyright (C) 2007, 2008, 2009 Tomas Hlavaty 3 4 ;;; This program is free software: you can redistribute it and/or modify 5 ;;; it under the terms of the GNU General Public License as published by 6 ;;; the Free Software Foundation, either version 3 of the License, or 7 ;;; (at your option) any later version. 8 ;;; 9 ;;; This program is distributed in the hope that it will be useful, 10 ;;; but WITHOUT ANY WARRANTY; without even the implied warranty of 11 ;;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 ;;; GNU General Public License for more details. 13 ;;; 14 ;;; You should have received a copy of the GNU General Public License 15 ;;; along with this program. If not, see <http://www.gnu.org/licenses/>. 16 17 ;;; See http://logand.com/sw/dbquery/index.html for documentation. 18 19 (require 'org-table) 20 21 (defvar dbquery-dir nil) 22 (defvar dbquery-backend nil) 23 (defvar dbquery-driver nil) 24 25 (defvar dbquery-db-alist nil) 26 (defvar dbquery-db-name nil) 27 28 (defun dbquery-shell-command (x) 29 (with-temp-buffer 30 (shell-command x (current-buffer)) 31 (goto-char (point-min)) 32 (read (current-buffer)))) 33 34 (defun dbquery-pg (host port db user pwd timeout query) 35 (dbquery-shell-command 36 (format "%sdbquery-pg \"hostaddr='%s' port='%s' dbname='%s' user='%s' password='%s' connect_timeout='%s'\" \"%s\"" 37 dbquery-dir host port db user pwd timeout query))) 38 39 (defun dbquery-mysql (host user pwd db query) 40 (dbquery-shell-command 41 (format "%sdbquery-mysql '%s' '%s' '%s' '%s' \"%s\"" 42 dbquery-dir host user pwd db query))) 43 44 (defun dbquery-sqlite (db query) 45 (dbquery-shell-command 46 (format "%sdbquery-sqlite '%s' \"%s\"" dbquery-dir db query))) 47 48 (defun dbquery-java (sep jar driver cstr user pwd query) 49 (dbquery-shell-command 50 (format "java -cp '%s%s%s' DbQuery %s %s %s %s \"%s\"" 51 dbquery-dir sep jar driver cstr user pwd query))) 52 53 (defun dbquery (query) 54 (funcall dbquery-driver query)) 55 56 (defun dbquery-cstr-postgres (alist) 57 (format "jdbc:postgresql:%s" (cdr (assoc 'sid alist)))) 58 59 (defun dbquery-cstr-mysql (alist) 60 (format "jdbc:mysql://%s:%s/%s" 61 (cdr (assoc 'host alist)) 62 (cdr (assoc 'port alist)) 63 (cdr (assoc 'sid alist)))) 64 65 (defun dbquery-cstr-oracle (alist) 66 (format "jdbc:oracle:thin:@%s:%s:%s" 67 (cdr (assoc 'host alist)) 68 (cdr (assoc 'port alist)) 69 (cdr (assoc 'sid alist)))) 70 71 (defun dbquery-cp-separator () 72 (if (member system-type '(ms-dos windows-nt cygwin vax-vms axp-vms)) ";" ":")) 73 74 ;;; TODO fix shell escaping " and ' 75 ;; (defun dbquery (query) 76 ;; (with-temp-buffer 77 ;; (let ((buf (current-buffer)) 78 ;; (alist (cdr (assoc dbquery-db-name dbquery-db-alist)))) 79 ;; (shell-command (format "java -cp '%s%s%s' DbQuery %s %s %s %s \"%s\"" 80 ;; dbquery-dir 81 ;; (dbquery-cp-separator) 82 ;; (cdr (assoc 'jar alist)) 83 ;; (cdr (assoc 'driver alist)) 84 ;; (funcall (cdr (assoc 'cstr alist)) alist) 85 ;; (cdr (assoc 'username alist)) 86 ;; (cdr (assoc 'password alist)) 87 ;; query) 88 ;; buf) 89 ;; (when (> (point-max) (point-min)) 90 ;; (while (search-forward "\\" nil t) 91 ;; (replace-match "\\\\" nil t)) 92 ;; (goto-char (point-min)) 93 ;; (read buf))))) 94 95 (defun dbquery-list-columns-pg () 96 (dbquery " 97 select t.tablename, a.attname 98 from pg_tables as t, pg_class as c, pg_attribute as a 99 where t.tablename !~* 'pg_*' 100 and t.tablename !~* 'sql_*' 101 and c.relname = t.tablename 102 and a.attnum > 0 103 and a.attrelid = c.oid 104 order by t.tablename, a.attnum;")) 105 106 (defun dbquery-list-edges-pg () 107 (dbquery " 108 select c1.relname, c2.relname 109 from pg_constraint k, pg_class c1, pg_class c2 110 where 0 < k.confrelid 111 and 0 < k.confrelid 112 and k.conrelid = c1.oid 113 and k.confrelid = c2.oid;")) 114 115 (defun dbquery-list-columns-mysql (sid) 116 (dbquery (format " 117 select table_name, column_name 118 from information_schema.columns 119 where table_schema = '%s' 120 order by tname, cname;" sid))) 121 122 (defun dbquery-list-edges-mysql (sid) 123 (dbquery (format " 124 select table_name, referenced_table_name 125 from information_schema.key_column_usage 126 where referenced_table_name is not null 127 and table_schema = '%s';" sid))) 128 129 (defun dbquery-list-columns-oracle () 130 (dbquery " 131 select table_name, column_name from user_tab_cols 132 order by table_name, column_name;")) 133 134 (defun dbquery-list-edges-oracle () 135 (dbquery " 136 select c1.table_name, c2.table_name 137 from user_tab_columns c1 138 join user_cons_columns c3 139 on c1.table_name = c3.table_name 140 and c1.column_name = c3.column_name 141 join user_constraints c4 142 on c3.constraint_name = c4.constraint_name 143 join user_cons_columns c2 144 on c4.r_constraint_name = c2.constraint_name 145 and c3.position = c2.position 146 where c4.constraint_type = 'R';")) 147 148 (defun dbquery-list-columns () 149 (ecase dbquery-backend 150 (mysql (dbquery-list-columns-mysql 151 (cdr (assoc 'sid 152 (cdr (assoc dbquery-db-name dbquery-db-alist)))))) 153 (pg (dbquery-list-columns-pg)) 154 (sqlite (dbquery-list-columns-sqlite)) 155 (oracle (dbquery-list-columns-oracle)))) 156 157 (defun dbquery-list-edges () 158 (ecase dbquery-backend 159 (mysql (dbquery-list-edges-mysql 160 (cdr (assoc 'sid 161 (cdr (assoc dbquery-db-name dbquery-db-alist)))))) 162 (pg (dbquery-list-edges-pg)) 163 (sqlite (dbquery-list-edges-sqlite)) 164 (oracle (dbquery-list-edges-oracle)))) 165 166 (defun dbquery-draw-row (x) 167 (insert "| ") 168 (let ((i -1)) 169 (dolist (c x) 170 (when (plusp (incf i)) 171 (insert " | ")) 172 (insert (format "%s" c)))) 173 (insert " |\n")) 174 175 (defun dbquery-draw-table (x) 176 (insert "|----\n") 177 (dbquery-draw-row (car x)) 178 (insert "|----\n") 179 (mapc 'dbquery-draw-row (cddr x)) 180 (org-table-align)) 181 182 (defun dbquery-er-dot-here () 183 (interactive) 184 (insert "digraph \"ER\" { 185 rankdir=LR; 186 nodesep=0.1; 187 node [shape=box,fontname=helvetica,fontsize=10,width=.01,height=.01]; 188 ") 189 (let* ((tname0 nil) 190 (alist (cdr (assoc dbquery-db-name dbquery-db-alist))) 191 (is-rel-table (or (cdr (assoc 'is-rel-table alist)) 192 (lambda (tname) nil))) 193 (edges (or (cdr (assoc 'list-edges alist)) 194 (lambda () nil)))) 195 (dolist (row (cdr (funcall (cdr (assoc 'list-columns alist))))) 196 (destructuring-bind (tname cname) row 197 (if (equal tname0 tname) 198 (insert "\\l" cname) 199 (when tname0 200 (insert "\\l}\" 201 ]; 202 ")) 203 (insert "\"" tname "\" [ 204 shape=" (if (funcall is-rel-table tname) "M" "") "record 205 label=\"" tname "|{" cname)) 206 (setq tname0 tname))) 207 (when tname0 208 (insert "\\l}\" 209 ]; 210 ")) 211 (dolist (edge (cdr (funcall edges))) 212 (insert "\"" (car edge) "\" -> \"" (cadr edge) "\"\n"))) 213 (insert "}\n")) 214 215 (defun dbquery-save-table (dir table &optional lob-fn) 216 (find-file (concat dir "/" table ".el")) 217 (erase-buffer) 218 (let ((data (dbquery (concat "select * from " table)))) 219 ;; move lobs from tmp dir 220 (dolist (lob (loop 221 for (name type) in (first data) 222 for n from 0 223 when (or (equalp type "clob") 224 (equalp type "blob")) 225 collect n)) 226 (dolist (row (rest data)) 227 (let ((old (nth lob row))) 228 (when old 229 (multiple-value-bind (new-full new-saved) 230 (if lob-fn 231 (funcall lob-fn dir table (first (nth lob (first data))) 232 row) 233 (values (concat dir "/" table "/" 234 (file-name-nondirectory old)) 235 nil)) 236 (when new-full 237 (unless (file-directory-p (file-name-directory new-full)) 238 (make-directory (file-name-directory new-full))) 239 (rename-file old new-full t) 240 (setf (nth lob row) (or new-saved new-full)))))))) 241 ;; print table 242 (pprint data (current-buffer))) 243 (save-buffer) 244 (kill-buffer (current-buffer))) 245 246 (defmacro with-dbquery (query &rest body) 247 (let ((table (gensym)) 248 (header (gensym)) 249 (rows (gensym))) 250 `(let ((,table (dbquery ,query))) 251 (when ,table 252 (let ((,header (first ,table)) 253 (,rows (rest ,table))) 254 (flet ((columns () ,header) 255 (column-type (name) 256 (second (assoc (upcase (format "%s" name)) ,header)))) 257 (dolist (row ,rows t) 258 ,@body))))))) 259 260 (defun dbquery-save-database (dir &optional lob-fn) 261 (with-dbquery "select table_name from user_tables" 262 (destructuring-bind (table) row 263 (when table 264 (dbquery-save-table dir table lob-fn))))) 265 266 (defun dbquery-buffer () 267 (interactive) 268 (switch-to-buffer "*dbquery*"))