dbquery

Query RDBMS and return S-expression
git clone https://logand.com/git/dbquery.git/
Log | Files | Refs | README

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*"))