Hi.
I had the same problem as Uli, so i have made a patch that adds the two
selection filters TABLE and SCHEMA to the SCRIPT SQL action. I am not able
to complete all the steps in the "submit patch" guide (as explained in
another post in the group) so any formatting issues will have to be sorted
out. Please have a look and reply any comments.
/Jacob
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/jsKZf5EWCN8J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Index: src/main/org/h2/command/Parser.java
===================================================================
--- src/main/org/h2/command/Parser.java (revision 4218)
+++ src/main/org/h2/command/Parser.java (working copy)
@@ -10,6 +10,7 @@
import java.math.BigInteger;
import java.text.Collator;
import java.util.ArrayList;
+import java.util.Collection;
import java.util.HashSet;
import org.h2.api.Trigger;
import org.h2.command.ddl.AlterIndexRename;
@@ -4689,6 +4709,31 @@
private ScriptCommand parseScript() {
ScriptCommand command = new ScriptCommand(session);
boolean data = true, passwords = true, settings = true, dropTables = false, simple = false;
+ if (readIf("SCHEMA")) {
+ java.util.Set<String> schemaNames = new HashSet<String>();
+ if (readIf("(")) {
+ do {
+ schemaNames.add(readUniqueIdentifier());
+ } while (readIf(","));
+ read(")");
+ } else {
+ schemaNames.add(readUniqueIdentifier());
+ }
+ command.setSchemaNames(schemaNames);
+ } else if (readIf("TABLE")) {
+ Collection<Table> tables = new ArrayList<Table>();
+ if (readIf("(")) {
+ do {
+ Table table = readTableOrView();
+ tables.add(table);
+ } while (readIf(","));
+ read(")");
+ } else {
+ Table table = readTableOrView();
+ tables.add(table);
+ }
+ command.setTables(tables);
+ }
if (readIf("SIMPLE")) {
simple = true;
}
Index: src/main/org/h2/command/dml/ScriptCommand.java
===================================================================
--- src/main/org/h2/command/dml/ScriptCommand.java (revision 4218)
+++ src/main/org/h2/command/dml/ScriptCommand.java (working copy)
@@ -16,10 +16,13 @@
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
+import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
+import java.util.Set;
import org.h2.command.CommandInterface;
import org.h2.command.Parser;
+import org.h2.constant.ErrorCode;
import org.h2.constant.SysProperties;
import org.h2.constraint.Constraint;
import org.h2.engine.Comment;
@@ -64,6 +67,8 @@
public class ScriptCommand extends ScriptBase {
private String charset = Constants.VERSION_MINOR < 3 ? SysProperties.FILE_ENCODING : Constants.UTF8;
+ private Set<String> schemaNames;
+ private Collection<Table> tables;
private boolean passwords;
private boolean data;
private boolean settings;
@@ -86,6 +91,14 @@
// TODO lock all tables for 'script' command
+ public void setSchemaNames(Set<String> schemaNames) {
+ this.schemaNames = schemaNames;
+ }
+
+ public void setTables(Collection<Table> tables) {
+ this.tables = tables;
+ }
+
public void setData(boolean data) {
this.data = data;
}
@@ -121,6 +134,15 @@
public ResultInterface query(int maxrows) {
session.getUser().checkAdmin();
reset();
+ Database db = session.getDatabase();
+ if (schemaNames != null) {
+ for (String schemaName : schemaNames) {
+ Schema schema = db.findSchema(schemaName);
+ if (schema == null) {
+ throw DbException.get(ErrorCode.SCHEMA_NOT_FOUND_1, schemaName);
+ }
+ }
+ }
try {
result = createResult();
deleteStore();
@@ -128,7 +150,6 @@
if (out != null) {
buffer = new byte[Constants.IO_BUFFER_SIZE];
}
- Database db = session.getDatabase();
if (settings) {
for (Setting setting : db.getAllSettings()) {
if (setting.getName().equals(SetTypes.getTypeName(SetTypes.CREATE_BUILD))) {
@@ -149,6 +170,9 @@
add(role.getCreateSQL(true), false);
}
for (Schema schema : db.getAllSchemas()) {
+ if (excludeSchema(schema)) {
+ continue;
+ }
add(schema.getCreateSQL(), false);
}
for (UserDataType datatype : db.getAllUserDataTypes()) {
@@ -158,6 +182,9 @@
add(datatype.getCreateSQL(), false);
}
for (SchemaObject obj : db.getAllSchemaObjects(DbObject.CONSTANT)) {
+ if (excludeSchema(obj.getSchema())) {
+ continue;
+ }
Constant constant = (Constant) obj;
add(constant.getCreateSQL(), false);
}
@@ -170,6 +197,12 @@
}
});
for (Table table : tables) {
+ if (excludeSchema(table.getSchema())) {
+ continue;
+ }
+ if (excludeTable(table)) {
+ continue;
+ }
if (table.isHidden()) {
continue;
}
@@ -184,6 +217,9 @@
}
}
for (SchemaObject obj : db.getAllSchemaObjects(DbObject.FUNCTION_ALIAS)) {
+ if (excludeSchema(obj.getSchema())) {
+ continue;
+ }
if (drop) {
add(obj.getDropSQL(), false);
}
@@ -196,6 +232,9 @@
add(agg.getCreateSQL(), false);
}
for (SchemaObject obj : db.getAllSchemaObjects(DbObject.SEQUENCE)) {
+ if (excludeSchema(obj.getSchema())) {
+ continue;
+ }
Sequence sequence = (Sequence) obj;
if (drop) {
add(sequence.getDropSQL(), false);
@@ -204,6 +243,12 @@
}
int count = 0;
for (Table table : tables) {
+ if (excludeSchema(table.getSchema())) {
+ continue;
+ }
+ if (excludeTable(table)) {
+ continue;
+ }
if (table.isHidden()) {
continue;
}
@@ -311,7 +356,13 @@
}
});
for (SchemaObject obj : constraints) {
+ if (excludeSchema(obj.getSchema())) {
+ continue;
+ }
Constraint constraint = (Constraint) obj;
+ if (excludeTable(constraint.getTable())) {
+ continue;
+ }
if (constraint.getTable().isHidden()) {
continue;
}
@@ -320,10 +371,25 @@
}
}
for (SchemaObject obj : db.getAllSchemaObjects(DbObject.TRIGGER)) {
+ if (excludeSchema(obj.getSchema())) {
+ continue;
+ }
TriggerObject trigger = (TriggerObject) obj;
+ if (excludeTable(trigger.getTable())) {
+ continue;
+ }
add(trigger.getCreateSQL(), false);
}
for (Right right : db.getAllRights()) {
+ Table table = right.getGrantedTable();
+ if (table != null) {
+ if (excludeSchema(table.getSchema())) {
+ continue;
+ }
+ if (excludeTable(table)) {
+ continue;
+ }
+ }
add(right.getCreateSQL(), false);
}
for (Comment comment : db.getAllComments()) {
@@ -549,6 +615,28 @@
}
}
+ private boolean excludeSchema(Schema schema) {
+ if (this.schemaNames != null && !this.schemaNames.contains(schema.getName())) {
+ return true;
+ }
+ if (this.tables != null) {
+ boolean containsTable = false;
+ for (Table table : schema.getAllTablesAndViews()) {
+ if (tables.contains(table)) {
+ table.checkSupportAlter(); // This may not be the correct way to ensure that only real tables can be used as arguments.
+ containsTable = true;
+ }
+ }
+ if (!containsTable)
+ return true;
+ }
+ return false;
+ }
+
+ private boolean excludeTable(Table table) {
+ return this.tables != null && !this.tables.contains(table);
+ }
+
private void add(String s, boolean insert) throws IOException {
if (s == null) {
return;
Index: src/test/org/h2/test/db/TestRunscript.java
===================================================================
--- src/test/org/h2/test/db/TestRunscript.java (revision 4218)
+++ src/test/org/h2/test/db/TestRunscript.java (working copy)
@@ -8,6 +8,7 @@
import java.sql.Connection;
import java.sql.PreparedStatement;
+import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
@@ -36,6 +37,14 @@
public void test() throws Exception {
testDropReferencedUserDefinedFunction();
testDropCascade();
+ testScriptExcludeSchema();
+ testScriptExcludeTable();
+ testScriptExcludeFunctionAlias();
+ testScriptExcludeConstant();
+ testScriptExcludeSequence();
+ testScriptExcludeConstraint();
+ testScriptExcludeTrigger();
+ testScriptExcludeRight();
testRunscriptFromClasspath();
testCancelScript();
testEncoding();
@@ -69,6 +78,211 @@
conn.close();
}
+ private void testScriptExcludeSchema() throws Exception {
+ deleteDb("runscript");
+ Connection conn;
+ ResultSet rs;
+ conn = getConnection("runscript");
+ Statement stat = conn.createStatement();
+ stat.execute("create schema include_schema1");
+ stat.execute("create schema exclude_schema1");
+ stat.execute("script schema include_schema1");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The schema 'exclude_schema1' should not be present in the script", rs.getString(1).indexOf("exclude_schema1".toUpperCase()) == -1);
+ }
+ rs.close();
+ stat.execute("create schema include_schema2");
+ stat.execute("script schema (include_schema1, include_schema2)");
+ rs = stat.getResultSet();
+ assertResultRowCount(3, rs); // User and one row pr schema = 3
+ rs.close();
+ conn.close();
+ }
+
+ private void testScriptExcludeTable() throws Exception {
+ deleteDb("runscript");
+ Connection conn;
+ ResultSet rs;
+ conn = getConnection("runscript");
+ Statement stat = conn.createStatement();
+ stat.execute("create schema a");
+ stat.execute("create schema b");
+ stat.execute("create schema c");
+ stat.execute("create table a.test1(x varchar, y int)");
+ stat.execute("create table a.test2(x varchar, y int)");
+ stat.execute("create table b.test1(x varchar, y int)");
+ stat.execute("create table b.test2(x varchar, y int)");
+ stat.execute("script table a.test1");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The table 'a.test2' should not be present in the script", rs.getString(1).indexOf("a.test2".toUpperCase()) == -1);
+ assertTrue("The table 'b.test1' should not be present in the script", rs.getString(1).indexOf("b.test1".toUpperCase()) == -1);
+ assertTrue("The table 'b.test2' should not be present in the script", rs.getString(1).indexOf("b.test2".toUpperCase()) == -1);
+ }
+ rs.close();
+ stat.execute("set schema b");
+ stat.execute("script table test1");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The table 'a.test1' should not be present in the script", rs.getString(1).indexOf("a.test1".toUpperCase()) == -1);
+ assertTrue("The table 'a.test2' should not be present in the script", rs.getString(1).indexOf("a.test2".toUpperCase()) == -1);
+ assertTrue("The table 'b.test2' should not be present in the script", rs.getString(1).indexOf("b.test2".toUpperCase()) == -1);
+ }
+ stat.execute("script table (a.test1, test2)");
+ rs = stat.getResultSet();
+ assertResultRowCount(7, rs); //User, Schemas 'a' & 'b' and 2 rows pr table = 7
+ rs.close();
+ conn.close();
+ }
+
+ private void testScriptExcludeFunctionAlias() throws Exception {
+ deleteDb("runscript");
+ Connection conn;
+ ResultSet rs;
+ conn = getConnection("runscript");
+ Statement stat = conn.createStatement();
+ stat.execute("create schema a");
+ stat.execute("create schema b");
+ stat.execute("create schema c");
+ stat.execute("create alias a.int_decode for \"java.lang.Integer.decode\"");
+ stat.execute("create table a.test(x varchar, y int as a.int_decode(x))");
+ stat.execute("script schema b");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The function alias 'int_decode' should not be present in the script", rs.getString(1).indexOf("int_decode".toUpperCase()) == -1);
+ }
+ rs.close();
+ conn.close();
+ }
+
+ private void testScriptExcludeConstant() throws Exception {
+ deleteDb("runscript");
+ Connection conn;
+ ResultSet rs;
+ conn = getConnection("runscript");
+ Statement stat = conn.createStatement();
+ stat.execute("create schema a");
+ stat.execute("create schema b");
+ stat.execute("create schema c");
+ stat.execute("create constant a.default_email value '[email protected]'");
+ stat.execute("create table a.test1(x varchar, email varchar default a.default_email)");
+ stat.execute("script schema b");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The constant 'default_email' should not be present in the script", rs.getString(1).indexOf("default_email".toUpperCase()) == -1);
+ }
+ rs.close();
+ conn.close();
+ }
+
+ private void testScriptExcludeSequence() throws Exception {
+ deleteDb("runscript");
+ Connection conn;
+ ResultSet rs;
+ conn = getConnection("runscript");
+ Statement stat = conn.createStatement();
+ stat.execute("create schema a");
+ stat.execute("create schema b");
+ stat.execute("create schema c");
+ stat.execute("create sequence a.seq_id");
+ stat.execute("script schema b");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The sequence 'seq_id' should not be present in the script", rs.getString(1).indexOf("seq_id".toUpperCase()) == -1);
+ }
+ rs.close();
+ conn.close();
+ }
+
+ private void testScriptExcludeConstraint() throws Exception {
+ deleteDb("runscript");
+ Connection conn;
+ ResultSet rs;
+ conn = getConnection("runscript");
+ Statement stat = conn.createStatement();
+ stat.execute("create schema a");
+ stat.execute("create schema b");
+ stat.execute("create schema c");
+ stat.execute("create table a.test1(x varchar, y int)");
+ stat.execute("alter table a.test1 add constraint unique_constraint unique (x, y) ");
+ stat.execute("script schema b");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The sequence 'unique_constraint' should not be present in the script", rs.getString(1).indexOf("unique_constraint".toUpperCase()) == -1);
+ }
+ rs.close();
+ stat.execute("create table a.test2(x varchar, y int)");
+ stat.execute("script table a.test2");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The sequence 'unique_constraint' should not be present in the script", rs.getString(1).indexOf("unique_constraint".toUpperCase()) == -1);
+ }
+ rs.close();
+ conn.close();
+ }
+
+ private void testScriptExcludeTrigger() throws Exception {
+ deleteDb("runscript");
+ Connection conn;
+ ResultSet rs;
+ conn = getConnection("runscript");
+ Statement stat = conn.createStatement();
+ stat.execute("create schema a");
+ stat.execute("create schema b");
+ stat.execute("create schema c");
+ stat.execute("create table a.test1(x varchar, y int)");
+ stat.execute("create trigger trigger_insert before insert on a.test1 for each row call \"org.h2.test.db.TestRunscript\"");
+ stat.execute("script schema b");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The trigger 'trigger_insert' should not be present in the script", rs.getString(1).indexOf("trigger_insert".toUpperCase()) == -1);
+ }
+ rs.close();
+ stat.execute("create table a.test2(x varchar, y int)");
+ stat.execute("script table a.test2");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The trigger 'trigger_insert' should not be present in the script", rs.getString(1).indexOf("trigger_insert".toUpperCase()) == -1);
+ }
+ rs.close();
+ conn.close();
+ }
+
+ private void testScriptExcludeRight() throws Exception {
+ deleteDb("runscript");
+ Connection conn;
+ ResultSet rs;
+ conn = getConnection("runscript");
+ Statement stat = conn.createStatement();
+ stat.execute("create user USER_A1 password 'test'");
+ stat.execute("create user USER_B1 password 'test'");
+ stat.execute("create schema a");
+ stat.execute("create schema b");
+ stat.execute("create schema c");
+ stat.execute("create table a.test1(x varchar, y int)");
+ stat.execute("create table b.test1(x varchar, y int)");
+ stat.execute("grant select on a.test1 to USER_A1");
+ stat.execute("grant select on b.test1 to USER_B1");
+ stat.execute("script schema b");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The grant to 'USER_A1' should not be present in the script", rs.getString(1).indexOf("to USER_A1".toUpperCase()) == -1);
+ }
+ rs.close();
+ stat.execute("create user USER_A2 password 'test'");
+ stat.execute("create table a.test2(x varchar, y int)");
+ stat.execute("grant select on a.test2 to USER_A2");
+ stat.execute("script table a.test2");
+ rs = stat.getResultSet();
+ while (rs.next()) {
+ assertTrue("The grant to 'USER_A1' should not be present in the script", rs.getString(1).indexOf("to USER_A1".toUpperCase()) == -1);
+ assertTrue("The grant to 'USER_B1' should not be present in the script", rs.getString(1).indexOf("to USER_B1".toUpperCase()) == -1);
+ }
+ rs.close();
+ conn.close();
+ }
+
private void testRunscriptFromClasspath() throws Exception {
deleteDb("runscript");
Connection conn;
