DERBY-4555: Expand SYSCS_IMPORT_TABLE to accept CSV file with headers
DERBY-6893: Create new SYSCS_IMPORT_DATA_BULK procedure
This change was contributed by Danoja Dias (danojadias at gmail dot com)
This change modifies the method Import.importTable so that it has
a varargs interface, which makes it easier to call it in a variety
of ways from different system procedures.
Additionally, the change adds a new system procedure, named
SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK, which is a variant of the
existing SYSCS_IMPORT_DATA system procedure, but has an additional
argument at the end that specifies the number of initial lines of
data in the input file to be skipped.
The anticipated use for this system procedure is to process input
data files which have been generated by a CSV-data-generating tool
which includes column header data at the start of the input file.
git-svn-id: https://svn.apache.org/repos/asf/db/derby/code/trunk@1751852 13f79535-47bb-0310-9956-ffa450edef68
diff --git a/java/engine/org/apache/derby/catalog/SystemProcedures.java b/java/engine/org/apache/derby/catalog/SystemProcedures.java
index a34baa9..f2863b2 100644
--- a/java/engine/org/apache/derby/catalog/SystemProcedures.java
+++ b/java/engine/org/apache/derby/catalog/SystemProcedures.java
@@ -1758,6 +1758,56 @@
}
+
+/**
+ * Import data from a given file into the specified table columns skipping header lines from the
+ * specified columns in the file.
+ * <p>
+ * Will be called as
+ * SYSCS_IMPORT_DATA_BULK (IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128),
+ * IN INSERTCOLUMNLIST VARCHAR(32672), IN COLUMNINDEXES VARCHAR(32672),
+ * IN FILENAME VARCHAR(32672), IN COLUMNDELIMITER CHAR(1),
+ * IN CHARACTERDELIMITER CHAR(1), IN CODESET VARCHAR(128),
+ * IN REPLACE SMALLINT, IN SKIP SMALLINT)
+ *
+ * @exception SQLException if a database error occurs
+ **/
+ public static void SYSCS_IMPORT_DATA_BULK(
+ String schemaName,
+ String tableName,
+ String insertColumnList,
+ String columnIndexes,
+ String fileName,
+ String columnDelimiter,
+ String characterDelimiter,
+ String codeset,
+ short replace,
+ short skip)
+ throws SQLException
+ {
+ Connection conn = getDefaultConn();
+ try{
+ Import.importData(conn, schemaName , tableName ,
+ insertColumnList, columnIndexes, fileName,
+ columnDelimiter, characterDelimiter,
+ codeset, replace, false, skip);
+ }catch(SQLException se)
+ {
+ rollBackAndThrowSQLException(conn, se);
+ }
+
+ //import finished successfull, commit it.
+ conn.commit();
+ }
+
+
+
+
+
+
+
+
+
/**
* Import data from a given file into the specified table columns
* from the specified columns in the file. Data for large object
diff --git a/java/engine/org/apache/derby/impl/load/Import.java b/java/engine/org/apache/derby/impl/load/Import.java
index 00482d6..035e8f3 100644
--- a/java/engine/org/apache/derby/impl/load/Import.java
+++ b/java/engine/org/apache/derby/impl/load/Import.java
@@ -182,7 +182,7 @@
String columnDelimiter,
String characterDelimiter,
String codeset, short replace,
- boolean lobsInExtFile)
+ boolean lobsInExtFile, short... extraArgs)
throws SQLException
{
/** Make sure that the current user has permission to perform this operation */
@@ -197,7 +197,10 @@
}
}
catch (StandardException se) { throw PublicAPI.wrapStandardException( se ); }
-
+ if(extraArgs.length>0)
+ skip=extraArgs[0];
+ else
+ skip=0;
performImport(connection, schemaName, insertColumnList,columnIndexes,
tableName, inputFileName, columnDelimiter,
characterDelimiter, codeset, replace, lobsInExtFile);
diff --git a/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java b/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
index a55c0b5..56e0902 100644
--- a/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
+++ b/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
@@ -13545,6 +13545,56 @@
tc);
}
+
+ /* SYSCS_IMPORT_DATA_BULK(IN SCHEMANAME VARCHAR(128),
+ * IN TABLENAME VARCHAR(128), IN INSERTCOLUMNLIST VARCHAR(32672),
+ * IN COLUMNINDEXES VARCHAR(32672), IN IN FILENAME VARCHAR(32672),
+ * IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1),
+ * IN CODESET VARCHAR(128) , IN REPLACE SMALLINT
+ * IN SKIP SMALLINT)
+ */
+ {
+ // procedure argument names
+ String[] arg_names = {"schemaName", "tableName", "insertColumnList","columnIndexes",
+ "fileName", " columnDelimiter", "characterDelimiter",
+ "codeset", "replace", "skip"};
+
+ // procedure argument types
+
+ // procedure argument types
+ TypeDescriptor[] arg_types = {
+ CATALOG_TYPE_SYSTEM_IDENTIFIER,
+ CATALOG_TYPE_SYSTEM_IDENTIFIER,
+ varchar32672Type,
+ varchar32672Type,
+ varchar32672Type,
+ DataTypeDescriptor.getCatalogType(
+ Types.CHAR, 1),
+ DataTypeDescriptor.getCatalogType(
+ Types.CHAR, 1),
+ CATALOG_TYPE_SYSTEM_IDENTIFIER,
+ TypeDescriptor.SMALLINT,
+ TypeDescriptor.SMALLINT
+ };
+
+
+ createSystemProcedureOrFunction(
+ "SYSCS_IMPORT_DATA_BULK",
+ sysUtilUUID,
+ arg_names,
+ arg_types,
+ 0,
+ 0,
+ RoutineAliasInfo.MODIFIES_SQL_DATA,
+ false,
+ false,
+ (TypeDescriptor) null,
+ newlyCreatedRoutines,
+ tc);
+ }
+
+
+
}
diff --git a/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java b/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java
index 98f6f82..af5870a 100644
--- a/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java
+++ b/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java
@@ -288,7 +288,7 @@
case PH_HARD_UPGRADE:
s.execute("delete from imp_pet");
- //Show that SYSCS_IMPORT_TABLE
+ //Show that SYSCS_IMPORT_TABLE_BULK
//accept CSV file with header lines
s.execute("call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'IMP_PET', 'pet.dat', null, null, null, 0, 1)");
@@ -305,4 +305,79 @@
};
}
+ /**
+ * Test newly added system procedure to import data with header lines.
+ * DERBY-6893.
+ */
+ public void testDerby6893SkipHeaderLines() throws SQLException {
+ Statement s = createStatement();
+ switch (getPhase()) {
+ case PH_CREATE:
+ //table to export
+ s.execute("create table exp_pet(petName varchar(50), kindOfAnimal varchar(50) , age int)");
+
+ //table to import
+ s.execute("create table im_pet(petName varchar(50), kindOfAnimal varchar(50) , age int)");
+ s.execute("insert into exp_pet values('Rover', 'Dog' , 4)");
+
+ s.execute("insert into exp_pet values('Spot', 'Cat' , 2)");
+
+ s.execute("insert into exp_pet values('Squawky','Parrot',37)");
+
+
+ SupportFilesSetup.deleteFile("pet.dat");
+ s.execute("call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'EXP_PET', 'pet.dat' , null, null, null) ");
+
+
+
+ //Show that SYSCS_IMPORT_DATA_BULK
+ //does not exist
+ assertCompileError
+ (
+ NO_SUCH_METHOD_ALIAS,
+ "call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, 'IM_PET', null, '1,2,3', 'pet.dat' , null, null, null, 0 , 1) ");
+ break;
+
+
+ case PH_SOFT_UPGRADE:
+ //Show that SYSCS_IMPORT_DATA_BULK
+ //does not support with soft upgrade
+ assertCompileError
+ (
+ NO_SUCH_METHOD_ALIAS,
+ "call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, 'IM_PET', null, '1,2,3', 'pet.dat' , null, null, null, 0 , 1) "
+ );
+ break;
+
+ case PH_POST_SOFT_UPGRADE:
+ //Show that SYSCS_IMPORT_DATA_BULK
+ //does not exist
+ assertCompileError
+ (
+ NO_SUCH_METHOD_ALIAS,
+ "call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, 'IM_PET', null, '1,2,3', 'pet.dat' , null, null, null, 0 , 1) "
+ );
+ break;
+
+ case PH_HARD_UPGRADE:
+ s.execute("delete from im_pet");
+ //Show that SYSCS_IMPORT_DATA_BULK
+ //accept CSV file with header lines
+ s.execute("call SYSCS_UTIL.SYSCS_IMPORT_DATA_BULK(null, 'IM_PET', null, '1,2,3', "
+ +" 'pet.dat' , null, null, null, 0 , 1) ");
+
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from IM_PET"),
+ new String[][]
+ {
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+
+ break;
+ };
+ }
+
+
+
}
