Closed GoogleCodeExporter closed 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
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
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
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
# 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
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
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
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
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
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:
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
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
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
Argh, sorry :)
Original comment by ondrej.zara
on 15 Mar 2010 at 5:15
Finally commited as r85 :)
Original comment by ondrej.zara
on 15 Mar 2010 at 7:58
Original issue reported on code.google.com by
geompse@gmail.com
on 14 Mar 2010 at 1:06