commit 7c47fc3ec32c8859f26e70d907c8ffdee53f24cb
parent ed0c38d7b95518918de5e354a0ed708ce0e52101
Author: Tomas Hlavaty <tom@logand.com>
Date: Tue, 5 Mar 2013 22:40:49 +0100
more old dbquery.el code
Diffstat:
| M | dbquery.el | | | 200 | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++------------------- |
1 file changed, 152 insertions(+), 48 deletions(-)
diff --git a/dbquery.el b/dbquery.el
@@ -1,4 +1,4 @@
-;;; dbquery - Query RDBMS, result as S-expression
+;;; dbquery - Query RDBMS, S-expression output, draw ER diagrams and more.
;;; Copyright (C) 2007, 2008, 2009 Tomas Hlavaty
;;; This program is free software: you can redistribute it and/or modify
@@ -16,10 +16,43 @@
;;; See http://logand.com/sw/dbquery/index.html for documentation.
+(require 'org-table)
+
(defvar dbquery-dir nil)
+(defvar dbquery-backend nil)
+(defvar dbquery-driver nil)
+
(defvar dbquery-db-alist nil)
(defvar dbquery-db-name nil)
+(defun dbquery-shell-command (x)
+ (with-temp-buffer
+ (shell-command x (current-buffer))
+ (goto-char (point-min))
+ (read (current-buffer))))
+
+(defun dbquery-pg (host port db user pwd timeout query)
+ (dbquery-shell-command
+ (format "%sdbquery-pg \"hostaddr='%s' port='%s' dbname='%s' user='%s' password='%s' connect_timeout='%s'\" \"%s\""
+ dbquery-dir host port db user pwd timeout query)))
+
+(defun dbquery-mysql (host user pwd db query)
+ (dbquery-shell-command
+ (format "%sdbquery-mysql '%s' '%s' '%s' '%s' \"%s\""
+ dbquery-dir host user pwd db query)))
+
+(defun dbquery-sqlite (db query)
+ (dbquery-shell-command
+ (format "%sdbquery-sqlite '%s' \"%s\"" dbquery-dir db query)))
+
+(defun dbquery-java (sep jar driver cstr user pwd query)
+ (dbquery-shell-command
+ (format "java -cp '%s%s%s' DbQuery %s %s %s %s \"%s\""
+ dbquery-dir sep jar driver cstr user pwd query)))
+
+(defun dbquery (query)
+ (funcall dbquery-driver query))
+
(defun dbquery-cstr-postgres (alist)
(format "jdbc:postgresql:%s" (cdr (assoc 'sid alist))))
@@ -39,42 +72,28 @@
(if (member system-type '(ms-dos windows-nt cygwin vax-vms axp-vms)) ";" ":"))
;;; TODO fix shell escaping " and '
-(defun dbquery (query)
- (with-temp-buffer
- (let ((buf (current-buffer))
- (alist (cdr (assoc dbquery-db-name dbquery-db-alist))))
- (shell-command (format "java -cp '%s%s%s' DbQuery %s %s %s %s \"%s\""
- dbquery-dir
- (dbquery-cp-separator)
- (cdr (assoc 'jar alist))
- (cdr (assoc 'driver alist))
- (funcall (cdr (assoc 'cstr alist)) alist)
- (cdr (assoc 'username alist))
- (cdr (assoc 'password alist))
- query)
- buf)
- (when (> (point-max) (point-min))
- (while (search-forward "\\" nil t)
- (replace-match "\\\\" nil t))
- (goto-char (point-min))
- (read buf)))))
-
-(defmacro with-dbquery (query &rest body)
- (let ((table (gensym))
- (header (gensym))
- (rows (gensym)))
- `(let ((,table (dbquery ,query)))
- (when ,table
- (let ((,header (first ,table))
- (,rows (rest ,table)))
- (flet ((columns () ,header)
- (column-type (name)
- (second (assoc (upcase (format "%s" name)) ,header))))
- (dolist (row ,rows t)
- ,@body)))))))
+;; (defun dbquery (query)
+;; (with-temp-buffer
+;; (let ((buf (current-buffer))
+;; (alist (cdr (assoc dbquery-db-name dbquery-db-alist))))
+;; (shell-command (format "java -cp '%s%s%s' DbQuery %s %s %s %s \"%s\""
+;; dbquery-dir
+;; (dbquery-cp-separator)
+;; (cdr (assoc 'jar alist))
+;; (cdr (assoc 'driver alist))
+;; (funcall (cdr (assoc 'cstr alist)) alist)
+;; (cdr (assoc 'username alist))
+;; (cdr (assoc 'password alist))
+;; query)
+;; buf)
+;; (when (> (point-max) (point-min))
+;; (while (search-forward "\\" nil t)
+;; (replace-match "\\\\" nil t))
+;; (goto-char (point-min))
+;; (read buf)))))
-(defun dbquery-list-columns-postgres ()
- (dbquery "
+(defun dbquery-list-columns-pg ()
+ (dbquery "
select t.tablename, a.attname
from pg_tables as t, pg_class as c, pg_attribute as a
where t.tablename !~* 'pg_*'
@@ -84,7 +103,7 @@ select t.tablename, a.attname
and a.attrelid = c.oid
order by t.tablename, a.attnum;"))
-(defun dbquery-list-edges-postgres ()
+(defun dbquery-list-edges-pg ()
(dbquery "
select c1.relname, c2.relname
from pg_constraint k, pg_class c1, pg_class c2
@@ -93,26 +112,22 @@ select c1.relname, c2.relname
and k.conrelid = c1.oid
and k.confrelid = c2.oid;"))
-(defun dbquery-list-columns-mysql ()
- (let* ((alist (cdr (assoc dbquery-db-name dbquery-db-alist)))
- (sid (cdr (assoc 'sid alist))))
- (dbquery (format "
+(defun dbquery-list-columns-mysql (sid)
+ (dbquery (format "
select table_name, column_name
from information_schema.columns
where table_schema = '%s'
- order by tname, cname;" sid))))
+ order by tname, cname;" sid)))
-(defun dbquery-list-edges-mysql ()
- (let* ((alist (cdr (assoc dbquery-db-name dbquery-db-alist)))
- (sid (cdr (assoc 'sid alist))))
- (dbquery (format "
+(defun dbquery-list-edges-mysql (sid)
+ (dbquery (format "
select table_name, referenced_table_name
from information_schema.key_column_usage
where referenced_table_name is not null
- and table_schema = '%s';" sid))))
+ and table_schema = '%s';" sid)))
(defun dbquery-list-columns-oracle ()
- (dbquery "
+ (dbquery "
select table_name, column_name from user_tab_cols
order by table_name, column_name;"))
@@ -130,6 +145,40 @@ select c1.table_name, c2.table_name
and c3.position = c2.position
where c4.constraint_type = 'R';"))
+(defun dbquery-list-columns ()
+ (ecase dbquery-backend
+ (mysql (dbquery-list-columns-mysql
+ (cdr (assoc 'sid
+ (cdr (assoc dbquery-db-name dbquery-db-alist))))))
+ (pg (dbquery-list-columns-pg))
+ (sqlite (dbquery-list-columns-sqlite))
+ (oracle (dbquery-list-columns-oracle))))
+
+(defun dbquery-list-edges ()
+ (ecase dbquery-backend
+ (mysql (dbquery-list-edges-mysql
+ (cdr (assoc 'sid
+ (cdr (assoc dbquery-db-name dbquery-db-alist))))))
+ (pg (dbquery-list-edges-pg))
+ (sqlite (dbquery-list-edges-sqlite))
+ (oracle (dbquery-list-edges-oracle))))
+
+(defun dbquery-draw-row (x)
+ (insert "| ")
+ (let ((i -1))
+ (dolist (c x)
+ (when (plusp (incf i))
+ (insert " | "))
+ (insert (format "%s" c))))
+ (insert " |\n"))
+
+(defun dbquery-draw-table (x)
+ (insert "|----\n")
+ (dbquery-draw-row (car x))
+ (insert "|----\n")
+ (mapc 'dbquery-draw-row (cddr x))
+ (org-table-align))
+
(defun dbquery-er-dot-here ()
(interactive)
(insert "digraph \"ER\" {
@@ -162,3 +211,58 @@ label=\"" tname "|{" cname))
(dolist (edge (cdr (funcall edges)))
(insert "\"" (car edge) "\" -> \"" (cadr edge) "\"\n")))
(insert "}\n"))
+
+(defun dbquery-save-table (dir table &optional lob-fn)
+ (find-file (concat dir "/" table ".el"))
+ (erase-buffer)
+ (let ((data (dbquery (concat "select * from " table))))
+ ;; move lobs from tmp dir
+ (dolist (lob (loop
+ for (name type) in (first data)
+ for n from 0
+ when (or (equalp type "clob")
+ (equalp type "blob"))
+ collect n))
+ (dolist (row (rest data))
+ (let ((old (nth lob row)))
+ (when old
+ (multiple-value-bind (new-full new-saved)
+ (if lob-fn
+ (funcall lob-fn dir table (first (nth lob (first data)))
+ row)
+ (values (concat dir "/" table "/"
+ (file-name-nondirectory old))
+ nil))
+ (when new-full
+ (unless (file-directory-p (file-name-directory new-full))
+ (make-directory (file-name-directory new-full)))
+ (rename-file old new-full t)
+ (setf (nth lob row) (or new-saved new-full))))))))
+ ;; print table
+ (pprint data (current-buffer)))
+ (save-buffer)
+ (kill-buffer (current-buffer)))
+
+(defmacro with-dbquery (query &rest body)
+ (let ((table (gensym))
+ (header (gensym))
+ (rows (gensym)))
+ `(let ((,table (dbquery ,query)))
+ (when ,table
+ (let ((,header (first ,table))
+ (,rows (rest ,table)))
+ (flet ((columns () ,header)
+ (column-type (name)
+ (second (assoc (upcase (format "%s" name)) ,header))))
+ (dolist (row ,rows t)
+ ,@body)))))))
+
+(defun dbquery-save-database (dir &optional lob-fn)
+ (with-dbquery "select table_name from user_tables"
+ (destructuring-bind (table) row
+ (when table
+ (dbquery-save-table dir table lob-fn)))))
+
+(defun dbquery-buffer ()
+ (interactive)
+ (switch-to-buffer "*dbquery*"))
