search

SylvainTI / wwwsqldesigner

Automatically exported from code.google.com/p/wwwsqldesigner
BSD 3-Clause "New" or "Revised" License
0 stars 0 forks source link

Index displaying #75

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
(new feature request)

Columns that features INDEX clause must be displayed, because it means that 
having a WHERE clause or an ORDER BY clause matching on them will be 
faster.

A primary key is implicitly indexed, so must not be displayed as an index, 
the actual behaviour is correct.
A foreign key MUST ALWAYS be from an indexed column to a primary key. 
That's a good practice.

The common modeling is to apply italic style on non-primary keys.
Toward another bug I made, this shall be a CSS Class like that :
   .index
   {
      font-style:italic;
   }

It would provide visibility over the foreign keys that does not match the 
good practice's scheme.

Original issue reported on code.google.com by geompse@gmail.com on 14 Mar 2010 at 1:06

GoogleCodeExporter commented 9 years ago 
- Not sure but it seems that INDEX are fetch only in mysql, do you want a patch 
to 
fetch them in postgresql ?
- Are they displayed if the data source is mysql ?

Original comment by geompse@gmail.com on 14 Mar 2010 at 1:17

GoogleCodeExporter commented 9 years ago 
Having columns which form a key italic is generally a good idea.

I would welcome an index-import patch to pgsql backend, it is a contributed 
code and
I have no working pgsql instance available.

I do not fully understand the last question, "- Are they displayed if the data 
source
is mysql ?"

Original comment by ondrej.zara on 14 Mar 2010 at 7:44

GoogleCodeExporter commented 9 years ago 
For my last question, I search the code and found that :
 - in output.xsl for mssql & sqlite there are CREATE INDEX statements
 - in ./backend/php-mysql/index.php INDEX are fetched

I'm doing the patch for pgsql

Original comment by geompse@gmail.com on 14 Mar 2010 at 9:17

GoogleCodeExporter commented 9 years ago 
To be placed right after :
            if($keyname1 != "")
                $xml .= '</key>';

Patch :

            // index
            $qstr = 'SELECT pcx."relname" as "INDEX_NAME", pa."attname" as 
"COLUMN_NAME", * FROM "pg_index" pi LEFT JOIN "pg_class" pcx ON pi."indexrelid" 
 = 
pcx."oid" LEFT JOIN "pg_class" pci ON pi."indrelid" = pci."oid" LEFT JOIN 
"pg_attribute" pa ON pa."attrelid" = pci."oid" AND pa."attnum" = 
ANY(pi."indkey") 
WHERE pci."relname" = \''.$table.'\' order by pa."attnum"';
            $result2 = pg_query($conn, $qstr);
            $idx = array();
            while ($row2 = pg_fetch_array($result2)) {
                $name = $row2["INDEX_NAME"];
                if (array_key_exists($name, $idx)) {
                    $obj = $idx[$name];
                } else {
                    $t = "INDEX";
                    if ($row2['indisunique'] == 't') { $t = "UNIQUE"; }
                    if ($row2['indisprimary'] == 't') {
                        $t = "PRIMARY";
                        break;
                    }

                    $obj = array(
                        "columns" => array(),
                        "type" => $t
                    );
                }

                $obj["columns"][] = $row2["COLUMN_NAME"];
                $idx[$name] = $obj;
            }

            foreach ($idx as $name=>$obj) {
                $xmlkey = '<key name="'.$name.'" type="'.$obj["type"].'">';
                for ($i=0;$i<count($obj["columns"]);$i++) {
                    $col = $obj["columns"][$i];
                    $xmlkey .= '<part>'.$col.'</part>';
                }
                $xmlkey .= '</key>';
                $xml .= $xmlkey;
            }

Notes :
 For no conflict, I "break;" if the index is bound to a primary key, but I leave the 
index bounds to UNIQUE constraints and other index as new keys. You must choose 
what 
to do...

Original comment by geompse@gmail.com on 14 Mar 2010 at 9:49

GoogleCodeExporter commented 9 years ago 
# For Oracle, use this query :
'SELECT ai.INDEX_NAME as "INDEX_NAME", atc.COLUMN_NAME as "COLUMN_NAME", * FROM 
ALL_INDEXES ai LEFT JOIN ALL_TAB_COLUMNS atc ON atc.TABLE_NAME = ai.TABLE_NAME 
AND 
atc.COLUMN_ID = ai.INCLUDE_COLUMN WHERE ai.TABLE_NAME = \''.$table.'\'';

Original comment by geompse@gmail.com on 14 Mar 2010 at 9:50

GoogleCodeExporter commented 9 years ago 
Please attach a .diff file, instead of posting the fixed code right into the 
comment
text. A .diff is better in many ways, especially in this scenario, where I am 
not
very familiar with the code to be patched :)

Original comment by ondrej.zara on 15 Mar 2010 at 7:08

GoogleCodeExporter commented 9 years ago 
Is that what you want ?

9:16 root ~/wwwsqldesigner/wwwsqldesigner-read-only# svn diff backend/php-
postgresql/index.php
Index: backend/php-postgresql/index.php
===================================================================
--- backend/php-postgresql/index.php    (révision 82)
+++ backend/php-postgresql/index.php    (copie de travail)
@@ -148,6 +148,50 @@
                                $keyname1 = $keyname;
                        }
                        if ($keyname1 != "") { $xml .= '</key>'; }
+
+            // index
+            $qstr = 'SELECT pcx."relname" as "INDEX_NAME", pa."attname" as
+"COLUMN_NAME", * FROM "pg_index" pi LEFT JOIN "pg_class" pcx ON 
pi."indexrelid"  =
+pcx."oid" LEFT JOIN "pg_class" pci ON pi."indrelid" = pci."oid" LEFT JOIN
+"pg_attribute" pa ON pa."attrelid" = pci."oid" AND pa."attnum" = 
ANY(pi."indkey")
+WHERE pci."relname" = \''.$table.'\' order by pa."attnum"';
+            $result2 = pg_query($conn, $qstr);
+            $idx = array();
+            while ($row2 = pg_fetch_array($result2)) {
+                $name = $row2["INDEX_NAME"];
+                if (array_key_exists($name, $idx)) {
+                    $obj = $idx[$name];
+                } else {
+                    $t = "INDEX";
+                    if ($row2['indisunique'] == 't') {
+                        $t = "UNIQUE";
+                        break;
+                    }
+                    if ($row2['indisprimary'] == 't') {
+                        $t = "PRIMARY";
+                        break;
+                    }
+
+                    $obj = array(
+                        "columns" => array(),
+                        "type" => $t
+                    );
+                }
+
+                $obj["columns"][] = $row2["COLUMN_NAME"];
+                $idx[$name] = $obj;
+            }
+
+            foreach ($idx as $name=>$obj) {
+                $xmlkey = '<key name="'.$name.'" type="'.$obj["type"].'">';
+                for ($i=0;$i<count($obj["columns"]);$i++) {
+                    $col = $obj["columns"][$i];
+                    $xmlkey .= '<part>'.$col.'</part>';
+                }
+                $xmlkey .= '</key>';
+                $xml .= $xmlkey;
+            }
+
                        $xml .= "</table>";

                }

Original comment by geompse@gmail.com on 15 Mar 2010 at 8:18

GoogleCodeExporter commented 9 years ago 
Yes, thanks :) Also, it might be more comfortable to actually *attach* the file 
to a
comment, instead of pasting the text inside.

Original comment by ondrej.zara on 15 Mar 2010 at 9:15

GoogleCodeExporter commented 9 years ago 
Added in r83. Please verify the code is correct (had to patch manually, because
google code comments have wrong indentation).

Original comment by ondrej.zara on 15 Mar 2010 at 9:24

GoogleCodeExporter commented 9 years ago 
Not cool. There is an extra char : (LEFT JOIN1) instead of (LEFT JOIN)

diff attached ! :)

Original comment by geompse@gmail.com on 15 Mar 2010 at 12:56

Attachments:

GoogleCodeExporter commented 9 years ago 
I cannot reopen the issue, is that correct to comment here are would you rather 
like 
another issue to be open instead ?

Original comment by geompse@gmail.com on 15 Mar 2010 at 12:57

GoogleCodeExporter commented 9 years ago 
Fixed in r84, thanks - this way the diffs should work :)

It is generally best to continue in this issue, although you cannot re-open it. 
If
the problem persists, I will re-open it myself.

Original comment by ondrej.zara on 15 Mar 2010 at 3:08

GoogleCodeExporter commented 9 years ago 
hem... wait a minute ! Are indexes in italic ?
The XML now has the index keys when imported from a postgresql database... 
but...

you're going too fast =D

Original comment by geompse@gmail.com on 15 Mar 2010 at 3:52

GoogleCodeExporter commented 9 years ago 
Argh, sorry :)

Original comment by ondrej.zara on 15 Mar 2010 at 5:15

GoogleCodeExporter commented 9 years ago 
Finally commited as r85 :)

Original comment by ondrej.zara on 15 Mar 2010 at 7:58