Changeset: a0f99a81ce8e for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=a0f99a81ce8e Modified Files: ChangeLog src/main/java/org/monetdb/util/SQLExporter.java Branch: default Log Message:
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). diffs (129 lines): diff --git a/ChangeLog b/ChangeLog --- 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 <[email protected]> +- 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 <[email protected]> - Improved DatabaseMetaData.getTypeInfo() output for temporal data types: sec_interval, day_interval, month_interval, date, time, timetz, diff --git a/src/main/java/org/monetdb/util/SQLExporter.java b/src/main/java/org/monetdb/util/SQLExporter.java --- 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)) + " (" + _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
