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/03 16:34:49 UTC
svn commit: r1751159 - 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: Sun Jul 3 16:34:49 2016
New Revision: 1751159
URL: http://svn.apache.org/viewvc?rev=1751159&view=rev
Log:
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.
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/load/ImportReadData.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.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=1751159&r1=1751158&r2=1751159&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 Sun Jul 3 16:34:49 2016
@@ -1623,6 +1623,46 @@ public class SystemProcedures {
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.
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=1751159&r1=1751158&r2=1751159&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 Sun Jul 3 16:34:49 2016
@@ -56,7 +56,7 @@ public class Import extends ImportAbstra
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 @@ public class Import extends ImportAbstra
/**
* 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 @@ public class Import extends ImportAbstra
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 @@ 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, null, //No columnList
null , //No column indexes
tableName, inputFileName, columnDelimiter,
@@ -336,7 +345,7 @@ public class Import extends ImportAbstra
* @exception Exception on error
*/
ImportReadData getImportReadData() throws Exception {
- return new ImportReadData(inputFileName, controlFileReader);
+ return new ImportReadData(inputFileName, controlFileReader, skip);
}
/*
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java?rev=1751159&r1=1751158&r2=1751159&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ImportReadData.java Sun Jul 3 16:34:49 2016
@@ -36,7 +36,8 @@ import java.sql.SQLException;
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 @@ final class ImportReadData implements ja
}
//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 @@ final class ImportReadData implements ja
//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);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java?rev=1751159&r1=1751158&r2=1751159&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java Sun Jul 3 16:34:49 2016
@@ -539,6 +539,13 @@ public class DD_Version implements Forma
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);
}
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=1751159&r1=1751158&r2=1751159&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 Sun Jul 3 16:34:49 2016
@@ -11495,6 +11495,8 @@ public final class DataDictionaryImpl
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 @@ public final class DataDictionaryImpl
}
}
+
+ /**
+ * <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.
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=1751159&r1=1751158&r2=1751159&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 Sun Jul 3 16:34:49 2016
@@ -25,6 +25,7 @@ import java.sql.Statement;
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 @@ public class Changes10_13 extends Upgrad
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 @@ public class Changes10_13 extends Upgrad
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;
+ };
+ }
+
}