DERBY-4555: Expand SYSCS_IMPORT_TABLE to accept CSV file with headers
DERBY-6892: Create new SYSCS_IMPORT_TABLE_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_TABLE_BULK, which is a variant of the
existing SYSCS_IMPORT_TABLE 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@1751159 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 5c1a320..a34baa9 100644
--- a/java/engine/org/apache/derby/catalog/SystemProcedures.java
+++ b/java/engine/org/apache/derby/catalog/SystemProcedures.java
@@ -1623,6 +1623,46 @@
conn.commit();
}
+
+
+
+
+
+/*import data from a given file to a table skipping header lines.
+ * <p>
+ * Will be called by system procedure as
+ * SYSCS_IMPORT_TABLE_BULK(IN SCHEMANAME VARCHAR(128),
+ * IN TABLENAME VARCHAR(128), 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_TABLE_BULK(
+ String schemaName,
+ String tableName,
+ String fileName,
+ String columnDelimiter,
+ String characterDelimiter,
+ String codeset,
+ short replace,
+ short skip)
+ throws SQLException
+ {
+ Connection conn = getDefaultConn();
+ try{
+ Import.importTable(conn, schemaName , tableName , fileName ,
+ columnDelimiter , characterDelimiter, codeset,
+ replace, false, skip);
+ }catch(SQLException se)
+ {
+ rollBackAndThrowSQLException(conn, se);
+ }
+ //import finished successfull, commit it.
+ conn.commit();
+ }
+
+
/**
* issue a rollback when SQLException se occurs. If SQLException ouccurs when rollback,
* the new SQLException will be added into the chain of se.
diff --git a/java/engine/org/apache/derby/impl/load/Import.java b/java/engine/org/apache/derby/impl/load/Import.java
index ce1e6c8..00482d6 100644
--- a/java/engine/org/apache/derby/impl/load/Import.java
+++ b/java/engine/org/apache/derby/impl/load/Import.java
@@ -56,7 +56,7 @@
private static Hashtable<Integer,Import> _importers = new Hashtable<Integer,Import>();
private String inputFileName;
-
+ private static short skip; //The number of header lines to be skipped
/**
* Constructor to Invoke Import from a select statement
* @param inputFileName The URL of the ASCII file from which import will happen
@@ -103,6 +103,11 @@
/**
* SYSCS_IMPORT_TABLE system Procedure from ij or from a Java application
* invokes this method to perform import to a table from a file.
+ *
+ * The extraArgs parameter is variadic, and is used when this method is
+ * called from SYSCS_IMPORT_TABLE_BULK, in which case extraArgs[0]
+ * specifies the number of header lines to skip.
+ *
* @param connection The Derby database connection URL for the database containing the table
* @param schemaName The name of the schema where table to import exists
* @param tableName Name of the Table the data has to be imported to.
@@ -121,7 +126,7 @@
String tableName, String inputFileName,
String columnDelimiter,
String characterDelimiter,String codeset,
- short replace, boolean lobsInExtFile)
+ short replace, boolean lobsInExtFile, short... extraArgs)
throws SQLException {
@@ -137,7 +142,11 @@
}
}
catch (StandardException se) { throw PublicAPI.wrapStandardException( se ); }
-
+ if(extraArgs.length>0)
+ skip=extraArgs[0];
+ else
+ skip=0;
+
performImport(connection, schemaName, null, //No columnList
null , //No column indexes
tableName, inputFileName, columnDelimiter,
@@ -336,7 +345,7 @@
* @exception Exception on error
*/
ImportReadData getImportReadData() throws Exception {
- return new ImportReadData(inputFileName, controlFileReader);
+ return new ImportReadData(inputFileName, controlFileReader, skip);
}
/*
diff --git a/java/engine/org/apache/derby/impl/load/ImportReadData.java b/java/engine/org/apache/derby/impl/load/ImportReadData.java
index f608352..50d86c8 100644
--- a/java/engine/org/apache/derby/impl/load/ImportReadData.java
+++ b/java/engine/org/apache/derby/impl/load/ImportReadData.java
@@ -36,7 +36,8 @@
final class ImportReadData implements java.security.PrivilegedExceptionAction<Object> {
//Read data from this file
private String inputFileName;
-
+ //The number of header lines to be skipped.
+ private short skipLines;
private int[] columnWidths;
private int rowWidth;
private char[] tempString;
@@ -143,8 +144,9 @@
}
//inputFileName: File to read data from
//controlFileReader: File used to interpret data in the inputFileName
- ImportReadData(String inputFileName, ControlInfo controlFileReader)
+ ImportReadData(String inputFileName, ControlInfo controlFileReader,short skipLines)
throws Exception {
+ this.skipLines=skipLines;
this.inputFileName = inputFileName;
this.controlFileReader = controlFileReader;
@@ -649,7 +651,10 @@
//do uppercase because the ui shows the values as True and False
if (hasColumnDefinition){
ignoreFirstRow();
- }
+ }
+ for(int i =0;i<skipLines;i++){
+ ignoreFirstRow();
+ }
}
if (formatCode == DEFAULT_FORMAT_CODE)
readVal=readNextDelimitedRow(returnStringArray);
diff --git a/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java b/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
index 3434b63..25540dc 100644
--- a/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
+++ b/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
@@ -539,6 +539,13 @@
bootingDictionary.create_10_12_system_procedures( tc, newlyCreatedRoutines );
}
+ if (fromMajorVersionNumber <= DataDictionary.DD_VERSION_DERBY_10_12)
+ {
+ // On upgrade from versions before 10.13, create system procedures
+ // added in 10.13.
+ bootingDictionary.create_10_13_system_procedures( tc, newlyCreatedRoutines );
+ }
+
// Grant PUBLIC access to some system routines
bootingDictionary.grantPublicAccessToSystemRoutines(newlyCreatedRoutines, tc, aid);
}
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 0b4bf90..a55c0b5 100644
--- a/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
+++ b/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
@@ -11495,6 +11495,8 @@
create_10_11_system_procedures( tc, newlyCreatedRoutines );
// add 10.12 specific system procedures
create_10_12_system_procedures( tc, newlyCreatedRoutines );
+ // add 10.13 specific system procedures
+ create_10_13_system_procedures( tc, newlyCreatedRoutines );
}
/**
@@ -13486,6 +13488,66 @@
}
}
+
+ /**
+ * <p>
+ * Create system procedures that are part of the SYSCS_UTIL schema, added in version 10.13.
+ * </p>
+ *
+ * @param tc an instance of the Transaction Controller.
+ * @param newlyCreatedRoutines set of routines we are creating (used to add permissions later on)
+ **/
+ void create_10_13_system_procedures( TransactionController tc, HashSet<String> newlyCreatedRoutines )
+ throws StandardException
+ {
+ UUID sysUtilUUID = getSystemUtilSchemaDescriptor().getUUID();
+ TypeDescriptor varchar32672Type = DataTypeDescriptor.getCatalogType( Types.VARCHAR, 32672 );
+
+ /* SYSCS_IMPORT_TABLE_BULK(IN SCHEMANAME VARCHAR(128),
+ * IN TABLENAME VARCHAR(128), 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", "fileName",
+ " columnDelimiter", "characterDelimiter",
+ "codeset", "replace", "skip"};
+
+ // procedure argument types
+ TypeDescriptor[] arg_types = {
+ CATALOG_TYPE_SYSTEM_IDENTIFIER,
+ CATALOG_TYPE_SYSTEM_IDENTIFIER,
+ varchar32672Type,
+ DataTypeDescriptor.getCatalogType(
+ Types.CHAR, 1),
+ DataTypeDescriptor.getCatalogType(
+ Types.CHAR, 1),
+ CATALOG_TYPE_SYSTEM_IDENTIFIER,
+ TypeDescriptor.SMALLINT,
+ TypeDescriptor.SMALLINT
+ };
+
+
+ createSystemProcedureOrFunction(
+ "SYSCS_IMPORT_TABLE_BULK",
+ sysUtilUUID,
+ arg_names,
+ arg_types,
+ 0,
+ 0,
+ RoutineAliasInfo.MODIFIES_SQL_DATA,
+ false,
+ false,
+ (TypeDescriptor) null,
+ newlyCreatedRoutines,
+ tc);
+ }
+
+ }
+
+
/*
** Priv block code to load net work server meta data queries.
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 5c785bc..98f6f82 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
@@ -25,6 +25,7 @@
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
/**
@@ -43,6 +44,7 @@
private static final String UPGRADE_REQUIRED = "XCL47";
private static final String CANNOT_ALTER_NON_IDENTITY_COLUMN = "42Z29";
private static final String CANNOT_MODIFY_ALWAYS_IDENTITY_COLUMN = "42Z23";
+ private static final String NO_SUCH_METHOD_ALIAS="42Y03";
//////////////////////////////////////////////////////////////////
//
@@ -226,4 +228,81 @@
break;
};
}
+ /**
+ * Test newly added system procedure to import table with header lines.
+ * DERBY-6892.
+ */
+ public void testDerby6892SkipHeaderLines() throws SQLException {
+ Statement s = createStatement();
+
+ switch (getPhase()) {
+ case PH_CREATE:
+ //table to export
+ s.execute("create table ex_pet(petName varchar(50), kindOfAnimal varchar(50) , age int)");
+
+ //table to import
+ s.execute("create table imp_pet(petName varchar(50), kindOfAnimal varchar(50) , age int)");
+
+ s.execute("insert into ex_pet values('Rover', 'Dog' , 4)");
+
+ s.execute("insert into ex_pet values('Spot', 'Cat' , 2)");
+
+ s.execute("insert into ex_pet values('Squawky','Parrot',37)");
+
+
+ SupportFilesSetup.deleteFile("pet.dat");
+ s.execute("call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EX_PET', 'pet.dat' , null, null, null) ");
+
+
+
+ //Show that SYSCS_IMPORT_TABLE_BULK
+ //does not exist
+ assertCompileError
+ (
+ NO_SUCH_METHOD_ALIAS,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'IMP_PET' , 'pet.dat' , null, null, null, 0 , 1) ");
+ break;
+
+
+ case PH_SOFT_UPGRADE:
+ //Show that SYSCS_IMPORT_TABLE_BULK
+ //does not support with soft upgrade
+ assertCompileError
+ (
+ NO_SUCH_METHOD_ALIAS,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'IMP_PET', 'pet.dat' , null, null, null, 0 , 1) "
+ );
+ break;
+
+ case PH_POST_SOFT_UPGRADE:
+ //Show that SYSCS_IMPORT_TABLE_BULK
+ //does not exist
+ assertCompileError
+ (
+ NO_SUCH_METHOD_ALIAS,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE_BULK(null, 'IMP_PET', 'pet.dat' , null, null, null, 0 , 1) "
+ );
+ break;
+
+
+ case PH_HARD_UPGRADE:
+
+ s.execute("delete from imp_pet");
+ //Show that SYSCS_IMPORT_TABLE
+ //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)");
+
+
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from IMP_PET"),
+ new String[][]
+ {
+ { "Spot", "Cat", "2"},
+ { "Squawky", "Parrot", "37"},
+ });
+
+ break;
+ };
+ }
+
}
