Mercurial > hg > monetdb-java

changeset 420:a0f99a81ce8e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Extend the generation of FOREIGN KEY CONSTRAINTS with ON UPDATE and ON DELETE rules. Also fixed an issue in CREATE INDEX generation. It included entries for foreign keys which the user would have given a specific name, but the name didn't end on _fkey (as system generated fk names do).
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 28 Jan 2021 21:50:13 +0100 (2021-01-28)
parents d9f9e077cd03
children 163b784aa93b
files ChangeLog src/main/java/org/monetdb/util/SQLExporter.java
diffstat 2 files changed, 74 insertions(+), 7 deletions(-) [+]
line wrap: on
line diff
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,6 +1,13 @@
 # ChangeLog file for monetdb-java
 # This file is updated with Maddlog
 
+* Thu Jan 28 2021 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
+- The dumping of table definitions from JdbcClient program has been
+  improved. It now includes the ON UPDATE and ON DELETE rules for foreign
+  key constraints. Also it no longer generates CREATE INDEX statements
+  for foreign key constraints whose name is not system generated but
+  user specified.
+
 * Thu Jan 14 2021 Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
 - Improved DatabaseMetaData.getTypeInfo() output for temporal data
   types: sec_interval, day_interval, month_interval, date, time, timetz,
--- a/src/main/java/org/monetdb/util/SQLExporter.java
+++ b/src/main/java/org/monetdb/util/SQLExporter.java
@@ -104,6 +104,8 @@ public final class SQLExporter extends E
 				s = "INTEGER";
 			} else if (s.equals("SEC_INTERVAL")) {
 				s = "INTERVAL SECOND";
+			} else if (s.equals("DAY_INTERVAL")) {
+				s = "INTERVAL DAY";
 			} else if (s.equals("MONTH_INTERVAL")) {
 				s = "INTERVAL MONTH";
 			} else if (s.equals("TIMETZ")) {
@@ -231,19 +233,29 @@ public final class SQLExporter extends E
 		colKeySeq = cols.findColumn("KEY_SEQ");
 		final int colPkTblSch = cols.findColumn("PKTABLE_SCHEM");
 		final int colPkTblNm = cols.findColumn("PKTABLE_NAME");
+		final int colUpdRule = cols.findColumn("UPDATE_RULE");
+		final int colDelRule = cols.findColumn("DELETE_RULE");
+		final String onUpdate = " ON UPDATE ";
+		final String onDelete = " ON DELETE ";
+		final Set<String> fknames = new LinkedHashSet<String>(8);
+		final Set<String> fk = new LinkedHashSet<String>(6);
+		final Set<String> pk = new LinkedHashSet<String>(6);
 		while (cols.next()) {
 			out.println(",");
 			out.print("\tCONSTRAINT " + dq(cols.getString(colFkNm)) + " FOREIGN KEY (");
+			fknames.add(cols.getString(colFkNm));	// needed later on for exclusion of generating CREATE INDEX for them
 
-			final Set<String> fk = new LinkedHashSet<String>();
-			fk.add(cols.getString(colFkColNm).intern());
-			final Set<String> pk = new LinkedHashSet<String>();
-			pk.add(cols.getString(colPkColNm).intern());
+			fk.clear();
+			fk.add(cols.getString(colFkColNm));
+			pk.clear();
+			pk.add(cols.getString(colPkColNm));
+			final short fkUpdRule = cols.getShort(colUpdRule);
+			final short fkDelRule = cols.getShort(colDelRule);
 
 			boolean next;
 			while ((next = cols.next()) && cols.getInt(colKeySeq) != 1) {
-				fk.add(cols.getString(colFkColNm).intern());
-				pk.add(cols.getString(colPkColNm ).intern());
+				fk.add(cols.getString(colFkColNm));
+				pk.add(cols.getString(colPkColNm));
 			}
 			// go back one
 			if (next)
@@ -263,6 +275,53 @@ public final class SQLExporter extends E
 				out.print(dq(it.next()));
 			}
 			out.print(")");
+
+			// ON UPDATE { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }
+			switch (fkUpdRule) {
+				 case DatabaseMetaData.importedKeyCascade:
+					out.print(onUpdate);
+					out.print("CASCADE");
+					break;
+				 case DatabaseMetaData.importedKeyNoAction:
+					out.print(onUpdate);
+					out.print("NO ACTION");
+					break;
+				 case DatabaseMetaData.importedKeyRestrict:
+					out.print(onUpdate);
+					out.print("RESTRICT");
+					break;
+				 case DatabaseMetaData.importedKeySetNull:
+					out.print(onUpdate);
+					out.print("SET NULL");
+					break;
+				 case DatabaseMetaData.importedKeySetDefault:
+					out.print(onUpdate);
+					out.print("SET DEFAULT");
+					break;
+			}
+			// ON DELETE { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }
+			switch (fkDelRule) {
+				 case DatabaseMetaData.importedKeyCascade:
+					out.print(onDelete);
+					out.print("CASCADE");
+					break;
+				 case DatabaseMetaData.importedKeyNoAction:
+					out.print(onDelete);
+					out.print("NO ACTION");
+					break;
+				 case DatabaseMetaData.importedKeyRestrict:
+					out.print(onDelete);
+					out.print("RESTRICT");
+					break;
+				 case DatabaseMetaData.importedKeySetNull:
+					out.print(onDelete);
+					out.print("SET NULL");
+					break;
+				 case DatabaseMetaData.importedKeySetDefault:
+					out.print(onDelete);
+					out.print("SET DEFAULT");
+					break;
+			}
 		}
 		cols.close();
 
@@ -289,7 +348,8 @@ public final class SQLExporter extends E
 				// We only process non-unique indexes here.
 				// The unique indexes are already covered as UNIQUE constraints in the CREATE TABLE above
 				final String idxname = cols.getString(colIndexNm);
-				if (idxname != null && !idxname.endsWith("_fkey")) {
+				// check idxname is not in the list of fknames for this table
+				if (idxname != null && !fknames.contains(idxname)) {
 					out.print("CREATE INDEX " + dq(idxname) + " ON " +
 						dq(cols.getString(tblSchIndex)) + "." +
 						dq(cols.getString(tblNmIndex)) + " (" +