dbquery

commit be9299b7ea8f078591ab13faa5b1ce315f5ed551
parent d91b597cd21325fc789c448d28d96d10a3d1f1ed
Author: tomas <tomas@logand.com>
Date:   Sat, 15 Aug 2009 18:20:22 +0200

drawing er diagram with dot

Diffstat:
M
dbquery.el
 | 
90
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1 file changed, 90 insertions(+), 0 deletions(-)
diff --git a/dbquery.el b/dbquery.el
@@ -70,3 +70,93 @@
                     (second (assoc (upcase (format "%s" name)) ,header))))
              (dolist (row ,rows t)
                ,@body)))))))
+
+(defun dbquery-list-columns-postgres ()
+   (dbquery "
+select t.tablename, a.attname
+  from pg_tables as t, pg_class as c, pg_attribute as a
+ where t.tablename !~* 'pg_*'
+   and t.tablename !~* 'sql_*'
+   and c.relname = t.tablename
+   and a.attnum > 0
+   and a.attrelid = c.oid
+ order by t.tablename, a.attnum;"))
+
+(defun dbquery-list-edges-postgres ()
+  (dbquery "
+select c1.relname, c2.relname
+  from pg_constraint k, pg_class c1, pg_class c2
+ where 0 < k.confrelid
+   and 0 < k.confrelid
+   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 "
+select table_name, column_name
+  from information_schema.columns
+ where table_schema = '%s'
+ 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 "
+select table_name, referenced_table_name
+  from information_schema.key_column_usage
+ where referenced_table_name is not null
+   and table_schema = '%s';" sid))))
+
+(defun dbquery-list-columns-oracle ()
+   (dbquery "
+select table_name, column_name from user_tab_cols
+ order by table_name, column_name;"))
+
+(defun dbquery-list-edges-oracle ()
+  (dbquery "
+select c1.table_name, c2.table_name
+  from user_tab_columns c1
+  join user_cons_columns c3
+    on c1.table_name = c3.table_name
+   and c1.column_name = c3.column_name
+  join user_constraints c4
+    on c3.constraint_name = c4.constraint_name
+  join user_cons_columns c2
+    on c4.r_constraint_name = c2.constraint_name
+   and c3.position = c2.position
+ where c4.constraint_type = 'R';"))
+
+(defun dbquery-er-dot-here ()
+  (interactive)
+  (insert "digraph \"ER\" {
+rankdir=LR;
+nodesep=0.1;
+node [shape=box,fontname=helvetica,fontsize=10,width=.01,height=.01];
+")
+  (let* ((tname0 nil)
+         (alist (cdr (assoc dbquery-db-name dbquery-db-alist)))
+         (is-rel-table (or (cdr (assoc 'is-rel-table alist))
+                           (lambda (tname) nil)))
+         (edges (or (cdr (assoc 'list-edges alist))
+                       (lambda () nil))))
+    (dolist (row (cdr (funcall (cdr (assoc 'list-columns alist)))))
+      (destructuring-bind (tname cname) row
+        (if (equal tname0 tname)
+            (insert "\\l" cname)
+            (when tname0
+              (insert "\\l}\"
+];
+"))
+            (insert "\"" tname "\" [
+shape=" (if (funcall is-rel-table tname) "M" "") "record
+label=\"" tname "|{" cname))
+        (setq tname0 tname)))
+    (when tname0
+      (insert "\\l}\"
+];
+"))
+    (dolist (edge (cdr (funcall edges)))
+      (insert "\"" (car edge) "\" -> \"" (cadr edge) "\"\n")))
+  (insert "}\n"))