001: /*
002:
003: Derby - Class org.apache.derby.impl.load.Import
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derby.impl.load;
023:
024: import java.sql.ResultSet;
025: import java.sql.SQLException;
026: import java.sql.SQLWarning;
027: import java.sql.Statement;
028: import java.sql.PreparedStatement;
029: import java.sql.Connection;
030: import java.sql.ResultSetMetaData;
031: import java.sql.DatabaseMetaData;
032: import java.util.*;
033:
034: /**
035: * This class implements import of data from a URL into a table.
036: * Import functions provided here in this class shouble be called through
037: * Systement Procedures. Import uses VTI , which is supprted only through
038: * Systemem procedures mechanism.
039: */
040:
041: public class Import extends ImportAbstract {
042:
043: private String inputFileName;
044:
045: /**
046: * Constructior to Invoke Import from a select statement
047: * @param inputFileName The URL of the ASCII file from which import will happen
048: * @exception Exception on error
049: */
050: public Import(String inputFileName, String columnDelimiter,
051: String characterDelimiter, String codeset,
052: int noOfColumnsExpected) throws SQLException {
053:
054: try {
055: this .inputFileName = inputFileName;
056: this .noOfColumnsExpected = noOfColumnsExpected;
057: controlFileReader = new ControlInfo();
058: controlFileReader.setControlProperties(characterDelimiter,
059: columnDelimiter, codeset);
060: doImport();
061:
062: } catch (Exception e) {
063: throw LoadError.unexpectedError(e);
064: }
065: }
066:
067: private void doImport() throws Exception {
068: if (inputFileName == null)
069: throw LoadError.dataFileNull();
070: doAllTheWork();
071:
072: }
073:
074: /**
075: * SYSCS_IMPORT_TABLE system Procedure from ij or from a Java application
076: * invokes this method to perform import to a table from a file.
077: * @param connection The Cloudscape database connection URL for the database containing the table
078: * @param schemaName The name of the schema where table to import exists
079: * @param tableName Name of the Table the data has to be imported to.
080: * @param inputFileName Name of the file from which data has to be imported.
081: * @param columnDelimiter Delimiter that seperates columns in the file
082: * @param characterDelimiter Delimiter that is used to quiote non-numeric types
083: * @param codeset Codeset of the data in the file
084: * @param replace Indicates whether the data in table has to be replaced or
085: * appended.(0 - append , > 0 Replace the data)
086: * @exception SQL Exception on errors
087: */
088:
089: public static void importTable(Connection connection,
090: String schemaName, String tableName, String inputFileName,
091: String columnDelimiter, String characterDelimiter,
092: String codeset, short replace) throws SQLException {
093:
094: performImport(connection, schemaName,
095: null, //No columnList
096: null, //No column indexes
097: tableName, inputFileName, columnDelimiter,
098: characterDelimiter, codeset, replace);
099: }
100:
101: /**
102: * SYSCS_IMPORT_DATA system Procedure from ij or from a Java application
103: * invokes this method to perform import to a table from a file.
104: * @param connection The Cloudscape database connection URL for the database containing the table
105: * @param schemaName The name of the schema where table to import exists
106: * @param tableName Name of the Table the data has to be imported to.
107: * @param insertColumnList Comma Seperated column name list to which data
108: * has to be imported from file.eg: 'c2,c2,'c3'.
109: * @param columnIndexes Comma sepearted Lit Index of the columns in the file(first column
110: starts at 1). eg: '3 ,4 , 5'
111: * @param inputFileName Name of the file from which data has to be imported.
112: * @param columnDelimiter Delimiter that seperates columns in the file
113: * @param characterDelimiter Delimiter that is used to quiote non-numeric types
114: * @param codeset Codeset of the data in the file
115: * @param replace Indicates whether the data in table has to be replaced or
116: * appended.(0 - append , > 0 Replace the data)
117: * @exception SQL Exception on errors
118: */
119: public static void importData(Connection connection,
120: String schemaName, String tableName,
121: String insertColumnList, String columnIndexes,
122: String inputFileName, String columnDelimiter,
123: String characterDelimiter, String codeset, short replace)
124: throws SQLException {
125:
126: performImport(connection, schemaName, insertColumnList,
127: columnIndexes, tableName, inputFileName,
128: columnDelimiter, characterDelimiter, codeset, replace);
129: }
130:
131: /*
132: * This function creates and executes SQL Insert statement that performs the
133: * the import using VTI.
134: * eg:
135: * insert into T1 select (cast column1 as DECIMAL), (cast column2 as
136: * INTEGER) from new org.apache.derby.impl.load.Import('extin/Tutor1.asc') as importvti;
137: *
138: */
139: private static void performImport(Connection connection,
140: String schemaName, String insertColumnList,
141: String columnIndexes, String tableName,
142: String inputFileName, String columnDelimiter,
143: String characterDelimiter, String codeset, short replace)
144: throws SQLException {
145:
146: if (connection == null)
147: throw LoadError.connectionNull();
148:
149: if (tableName == null)
150: throw LoadError.entityNameMissing();
151:
152: ColumnInfo columnInfo = new ColumnInfo(connection, schemaName,
153: tableName, insertColumnList, columnIndexes,
154: COLUMNNAMEPREFIX);
155:
156: /* special handling of single quote delimiters
157: * Single quote should be writeen with an extra quote otherwise sql will
158: * throw syntac error.
159: * i.e to recognize a quote it has to be appended with extra quote ('')
160: */
161: if (characterDelimiter != null
162: && characterDelimiter.equals("'"))
163: characterDelimiter = "''";
164: if (columnDelimiter != null && columnDelimiter.equals("'"))
165: columnDelimiter = "''";
166:
167: StringBuffer sb = new StringBuffer("new ");
168: sb.append("org.apache.derby.impl.load.Import");
169: sb.append("(");
170: sb.append((inputFileName != null ? "'" + inputFileName + "'"
171: : null));
172: sb.append(",");
173: sb.append((columnDelimiter != null ? "'" + columnDelimiter
174: + "'" : null));
175: sb.append(",");
176: sb.append((characterDelimiter != null ? "'"
177: + characterDelimiter + "'" : null));
178: sb.append(",");
179: sb.append((codeset != null ? "'" + codeset + "'" : null));
180: sb.append(", ");
181: sb.append(columnInfo.getExpectedNumberOfColumnsInFile());
182: sb.append(" )");
183:
184: String importvti = sb.toString();
185:
186: // delimit the table and schema names with quotes.
187: // because they might have been created as quoted
188: // identifiers(for example when reserved words are used, names are quoted)
189:
190: // Import procedures are to be called with case-senisitive names.
191: // Incase of delimited table names, they need to be passed as defined
192: // and when they are not delimited, they need to be passed in upper
193: // case, because all undelimited names are stored in the upper case
194: // in the database.
195:
196: String entityName = (schemaName == null ? "\"" + tableName
197: + "\"" : "\"" + schemaName + "\"" + "." + "\""
198: + tableName + "\"");
199:
200: String insertModeValue;
201: if (replace > 0)
202: insertModeValue = "replace";
203: else
204: insertModeValue = "bulkInsert";
205:
206: String cNamesWithCasts = columnInfo.getColumnNamesWithCasts();
207: String insertColumnNames = columnInfo.getInsertColumnNames();
208: if (insertColumnNames != null)
209: insertColumnNames = "(" + insertColumnNames + ") ";
210: else
211: insertColumnNames = "";
212: String insertSql = "INSERT INTO " + entityName
213: + insertColumnNames + " --DERBY-PROPERTIES insertMode="
214: + insertModeValue + "\n" + " SELECT " + cNamesWithCasts
215: + " from " + importvti + " AS importvti";
216:
217: //prepare the import statement to hit any errors before locking the table
218: PreparedStatement ips = connection.prepareStatement(insertSql);
219:
220: //lock the table before perfoming import, because there may
221: //huge number of lockes aquired that might have affect on performance
222: //and some possible dead lock scenarios.
223: Statement statement = connection.createStatement();
224: String lockSql = "LOCK TABLE " + entityName
225: + " IN EXCLUSIVE MODE";
226: statement.executeUpdate(lockSql);
227:
228: //execute the import operaton.
229: ips.executeUpdate();
230: statement.close();
231: ips.close();
232:
233: }
234:
235: /** virtual method from the abstract class
236: * @exception Exception on error
237: */
238: ImportReadData getImportReadData() throws Exception {
239: return new ImportReadData(inputFileName, controlFileReader);
240: }
241: }
|