Mercurial > hg > monetdb-java

changeset 306:7c79ef41b840

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Undo change cf372fae2adb. The problem was in the dump code in JdbcClient. Improved the SQLExporter code which is used to dump a table or all tables in a schema.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 21 Aug 2019 17:05:55 +0200 (2019-08-21)
parents e74123be951f
children 05549bc7ed26
files src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java src/main/java/nl/cwi/monetdb/util/SQLExporter.java
diffstat 2 files changed, 9 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -3108,8 +3108,8 @@ public class MonetDatabaseMetaData
 		"JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
 		"JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " +
 		"JOIN \"sys\".\"columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
-		"LEFT OUTER JOIN \"sys\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\") " + 
-		"WHERE (k.\"type\" IS NULL OR k.\"type\" IN (1" + (unique ? "" : ",0") +  "))"); // primary keys are 0, unique keys are 1
+		"LEFT OUTER JOIN \"sys\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) " +	// primary (0) and unique keys (1) only
+		"WHERE 1 = 1");
 
 		if (catalog != null && !catalog.isEmpty()) {
 			// non-empty catalog selection.
--- a/src/main/java/nl/cwi/monetdb/util/SQLExporter.java
+++ b/src/main/java/nl/cwi/monetdb/util/SQLExporter.java
@@ -210,12 +210,14 @@ public class SQLExporter extends Exporte
 		cols.close();
 
 		// add unique constraint definitions
+		// we use getIndexInfo to get unique indexes, but need to exclude
+		// the indexes which are generated by the system for pkey constraints
 		cols = dbmd.getIndexInfo(catalog, schema, name, true, true);
 		int colIndexNm = cols.findColumn("INDEX_NAME");
 		int colIndexColNm = cols.findColumn("COLUMN_NAME");
 		while (cols.next()) {
 			String idxname = cols.getString(colIndexNm);
-			if (idxname != null) {
+			if (idxname != null && !idxname.endsWith("_pkey")) {
 				out.println(",");
 				out.print("\tCONSTRAINT " + dq(idxname) + " UNIQUE (" +
 					dq(cols.getString(colIndexColNm)));
@@ -278,6 +280,9 @@ public class SQLExporter extends Exporte
 		out.println(");");
 
 		// create the non unique indexes defined for this table
+		// we use getIndexInfo to get non-unique indexes, but need to exclude
+		// the indexes which are generated by the system for fkey constraints
+		// (and pkey and unique constraints but those are marked as unique and not requested)
 		cols = dbmd.getIndexInfo(catalog, schema, name, false, true);
 		colIndexNm = cols.findColumn("INDEX_NAME");
 		colIndexColNm = cols.findColumn("COLUMN_NAME");
@@ -286,7 +291,7 @@ public class SQLExporter extends Exporte
 				// We only process non-unique indexes here.
 				// The unique indexes are already covered as UNIQUE constraints in the CREATE TABLE above
 				String idxname = cols.getString(colIndexNm);
-				if (idxname != null) {
+				if (idxname != null && !idxname.endsWith("_fkey")) {
 					out.print("CREATE INDEX " + dq(idxname) + " ON " +
 						dq(cols.getString("TABLE_NAME")) + " (" +
 						dq(cols.getString(colIndexColNm)));