You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2016/07/08 03:59:27 UTC
svn commit: r1751852 - in /db/derby/code/trunk/java:
engine/org/apache/derby/catalog/ engine/org/apache/derby/impl/load/
engine/org/apache/derby/impl/sql/catalog/
testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/
Author: bpendleton
Date: Fri Jul 8 03:59:27 2016
New Revision: 1751852
URL: http://svn.apache.org/viewvc?rev=1751852&view=rev
Log:
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.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java?rev=1751852&r1=1751851&r2=1751852&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java Fri Jul 8 03:59:27 2016
@@ -1758,6 +1758,56 @@ public class SystemProcedures {
}
+
+/**
+ * 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
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java?rev=1751852&r1=1751851&r2=1751852&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java Fri Jul 8 03:59:27 2016
@@ -182,7 +182,7 @@ public class Import extends ImportAbstra
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 @@ public class Import extends ImportAbstra
}
}
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);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1751852&r1=1751851&r2=1751852&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Fri Jul 8 03:59:27 2016
@@ -13545,6 +13545,56 @@ public final class DataDictionaryImpl
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);
+ }
+
+
+
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java?rev=1751852&r1=1751851&r2=1751852&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java Fri Jul 8 03:59:27 2016
@@ -288,7 +288,7 @@ public class Changes10_13 extends Upgrad
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 @@ public class Changes10_13 extends Upgrad
};
}
+ /**
+ * 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;
+ };
+ }
+
+
+
}