From 1d1d54f4567b48b11a058f3b94743930ff1d08e9 Mon Sep 17 00:00:00 2001
From: Alan Knowles <alan@akbkhome.com>
Date: Mon, 27 Feb 2012 12:56:20 +0800
Subject: [PATCH] dbgenerate.js

---
 dbgenerate.js | 29 +++++++++++++++++++++++------
 1 file changed, 23 insertions(+), 6 deletions(-)

diff --git a/dbgenerate.js b/dbgenerate.js
index a6aac2d5e..790707b8c 100644
--- a/dbgenerate.js
+++ b/dbgenerate.js
@@ -237,11 +237,27 @@ switch(cfg.DBTYPE) {
             "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " + 
             "WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')" +
             "AND pg_catalog.pg_table_is_visible(c.oid) ";
-         query_describe_table =  "SELECT " + 
-                "attname as Field, " +
-                "opcname , atttypmod  " +
-                "FROM pg_attribute,pg_type,    pg_opclass " + 
-                " WHERE attrelid=typrelid AND atttypid=opcdeftype AND typname= '%s' ";
+         query_describe_table =  
+                "SELECT f.attnum AS number, f.attname AS name, f.attnum, " +
+                "f.attnotnull AS notnull, pg_catalog.format_type(f.atttypid,f.atttypmod) AS type, " +
+                "CASE WHEN p.contype = 'p' THEN 't' ELSE 'f' END AS primarykey, " +
+                "CASE WHEN p.contype = 'u' THEN 't' ELSE 'f' END AS uniquekey, " +
+                "CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey, " +
+                "CASE WHEN p.contype = 'f' THEN p.confkey END AS foreignkey_fieldnum, " +
+                "CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey, " +
+                "CASE WHEN p.contype = 'f' THEN p.conkey END AS foreignkey_connnum, " +
+                "CASE WHEN f.atthasdef = 't' THEN d.adsrc END AS default " +
+                "FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid " +
+                "        JOIN pg_type t ON t.oid = f.atttypid " +
+                "        LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum " +
+                "        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace " +
+                "        LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY ( p.conkey ) " +
+                "        LEFT JOIN pg_class AS g ON p.confrelid = g.oid " +
+                "WHERE c.relkind = 'r'::char AND n.nspname = '%n' " +
+                "AND c.relname = '%s' AND f.attnum > 0 ORDER BY number";
+                
+                
+                
         break;
 /*
            "Field": "province",
@@ -263,7 +279,8 @@ var readers = [];
 tables.forEach(function(table) {
     //print(table);
     var schema = Gda.execute_select_command(cnc,
-            query_describe_table.replace(/%s/, table) ).fetchAll();
+            query_describe_table.replace(/%s/, table).replace(/%n/,'public')
+            ).fetchAll();
     
     
     var reader = []; 
-- 
2.39.2