001: /*
002:
003: Derby - Class org.apache.derby.impl.load.ColumnInfo
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 org.apache.derby.iapi.services.io.StoredFormatIds;
025:
026: import java.sql.ResultSet;
027: import java.sql.SQLException;
028: import java.sql.SQLWarning;
029: import java.sql.Statement;
030: import java.sql.PreparedStatement;
031: import java.sql.Connection;
032: import java.sql.ResultSetMetaData;
033: import java.sql.DatabaseMetaData;
034: import java.util.*;
035:
036: /**
037: *
038: * This class provides supportto create casting/conversions required to
039: * perform import. Import VTI gives all the data in VARCHAR type becuase data
040: * in the files is in CHAR format. There is no implicit cast availabile from
041: * VARCHAR to some of the types. In cases where explicit casting is allowed,
042: * columns are casted with explict cast to the type of table column; in case of
043: * double/real explicit casting is also not allowd , scalar fuction DOUBLE is
044: * used in those cases.
045: *
046: * @author suresht
047: */
048: class ColumnInfo {
049:
050: private ArrayList vtiColumnNames;
051: private ArrayList insertColumnNames;
052: private ArrayList columnTypes;
053: private int noOfColumns;
054: private ArrayList columnPositions;
055: private boolean createolumnNames = true;
056: private int expectedNumberOfCols; //number of Columns that are suppose
057: // to be in the file to imported
058: private Connection conn;
059: private String tableName;
060: private String schemaName;
061:
062: /**
063: * Initialize the column type and name information
064: * @param conn - connection to use for metadata queries
065: * @param sName - table's schema
066: * @param tName - table Name
067: * @param insertColumnList - comma seperared insert statement column list
068: * @param vtiColumnIndexes - Indexes in the file
069: * @param vtiColumnPrefix - Prefix to use to generate column names to select from VTI
070: * @exception Exception on error
071: */
072: public ColumnInfo(Connection conn, String sName, String tName,
073: String insertColumnList, String vtiColumnIndexes,
074: String vtiColumnPrefix) throws SQLException {
075:
076: vtiColumnNames = new ArrayList(1);
077: insertColumnNames = new ArrayList(1);
078: columnTypes = new ArrayList(1);
079: noOfColumns = 0;
080: this .conn = conn;
081:
082: this .schemaName = sName;
083: this .tableName = tName;
084:
085: if (insertColumnList != null) {
086: //break the comma seperated column list and initialze column info
087: //eg: C2 , C1 , C3
088: StringTokenizer st = new StringTokenizer(insertColumnList,
089: ",");
090: while (st.hasMoreTokens()) {
091: String columnName = (st.nextToken()).trim();
092: if (!initializeColumnInfo(columnName)) {
093: if (tableExists())
094: throw LoadError.invalidColumnName(columnName);
095: else {
096: String entityName = (schemaName != null ? schemaName
097: + "." + tableName
098: : tableName);
099: throw LoadError.tableNotFound(entityName);
100: }
101: }
102: }
103: } else {
104: //All columns in the table
105: if (!initializeColumnInfo(null)) {
106: String entityName = (schemaName != null ? schemaName
107: + "." + tableName : tableName);
108: throw LoadError.tableNotFound(entityName);
109: }
110: }
111:
112: //break the comma seperated column indexes for import file give by the user
113: //eg: "1, 3, 5, 7"
114: if (vtiColumnIndexes != null) {
115:
116: StringTokenizer st = new StringTokenizer(vtiColumnIndexes,
117: ",");
118: while (st.hasMoreTokens()) {
119: String columnIndex = (st.nextToken()).trim();
120: vtiColumnNames.add(vtiColumnPrefix + columnIndex);
121: int cIndex = (new Integer(columnIndex)).intValue();
122: if (cIndex > expectedNumberOfCols)
123: expectedNumberOfCols = cIndex;
124: }
125:
126: }
127:
128: //if column indexes are not specified ; create names for all collumns requested
129: if (vtiColumnNames.size() < 1) {
130: for (int index = 1; index <= noOfColumns; index++) {
131: vtiColumnNames.add(vtiColumnPrefix + index);
132: }
133: expectedNumberOfCols = noOfColumns;
134: }
135: }
136:
137: private boolean initializeColumnInfo(String columnPattern)
138: throws SQLException {
139: DatabaseMetaData dmd = conn.getMetaData();
140: ResultSet rs = dmd.getColumns(null, schemaName, tableName,
141: columnPattern);
142: boolean foundTheColumn = false;
143: while (rs.next()) {
144:
145: // 4.COLUMN_NAME String => column name
146: String columnName = rs.getString(4);
147:
148: // 5.DATA_TYPE short => SQL type from java.sql.Types
149: short dataType = rs.getShort(5);
150:
151: // 6.TYPE_NAME String => Data source dependent type name
152: String typeName = rs.getString(6);
153:
154: // 7.COLUMN_SIZE int => column size. For char or date types
155: // this is the maximum number of characters, for numeric or
156: // decimal types this is precision.
157: int columnSize = rs.getInt(7);
158:
159: // 9.DECIMAL_DIGITS int => the number of fractional digits
160: int decimalDigits = rs.getInt(9);
161:
162: // 10.NUM_PREC_RADIX int => Radix (typically either 10 or 2)
163: int numPrecRadix = rs.getInt(10);
164: foundTheColumn = true;
165: if (importExportSupportedType(dataType)) {
166:
167: insertColumnNames.add(columnName);
168: String sqlType = typeName
169: + getTypeOption(typeName, columnSize,
170: columnSize, decimalDigits);
171: columnTypes.add(noOfColumns, sqlType);
172: noOfColumns++;
173: } else {
174: rs.close();
175: throw LoadError.nonSupportedTypeColumn(columnName,
176: typeName);
177: }
178:
179: }
180:
181: rs.close();
182: return foundTheColumn;
183: }
184:
185: //return true if the given type is supported by import/export
186: public static final boolean importExportSupportedType(int type) {
187:
188: return !(type == java.sql.Types.BINARY
189: || type == java.sql.Types.BIT
190: || type == java.sql.Types.JAVA_OBJECT
191: || type == java.sql.Types.OTHER
192: || type == java.sql.Types.CLOB
193: || type == java.sql.Types.BLOB || type == StoredFormatIds.XML_TYPE_ID);
194: }
195:
196: private String getTypeOption(String type, int length,
197: int precision, int scale) {
198:
199: if ((type.equals("CHAR") || type.equals("BLOB")
200: || type.equals("CLOB") || type.equals("VARCHAR"))
201: && length != 0) {
202: return "(" + length + ")";
203: }
204:
205: if (type.equals("FLOAT") && precision != 0)
206: return "(" + precision + ")";
207:
208: //there are three format of decimal and numeric. Plain decimal, decimal(x)
209: //and decimal(x,y). x is precision and y is scale.
210: if (type.equals("DECIMAL") || type.equals("NUMERIC")) {
211: if (precision != 0 && scale == 0)
212: return "(" + precision + ")";
213: else if (precision != 0 && scale != 0)
214: return "(" + precision + "," + scale + ")";
215: else if (precision == 0 && scale != 0)
216: return "(" + scale + ")";
217: }
218:
219: if ((type.equals("DECIMAL") || type.equals("NUMERIC"))
220: && scale != 0)
221: return "(" + scale + ")";
222:
223: //no special type option
224: return "";
225: }
226:
227: /*
228: * Returns a string of columns with proper casting/conversion
229: * to be used to select from import VTI.
230: */
231: public String getColumnNamesWithCasts() {
232: StringBuffer sb = new StringBuffer();
233: boolean first = true;
234: int noOfVtiCols = vtiColumnNames.size();
235: for (int index = 0; index < noOfColumns && index < noOfVtiCols; index++) {
236: if (!first)
237: sb.append(", ");
238: else
239: first = false;
240: String type = (String) columnTypes.get(index);
241: String columnName = (String) vtiColumnNames.get(index);
242:
243: if (type.startsWith("SMALLINT")
244: || type.startsWith("INTEGER")
245: || type.startsWith("DECIMAL")
246: || type.startsWith("BIGINT")
247: || type.startsWith("NUMERIC")) {
248: //these types require explicit casting
249: sb.append(" cast" + "(" + columnName + " AS " + type
250: + ") ");
251:
252: } else {
253: //if it is DOUBLE use scalar DOUBLE function no explicit casting allowed
254: if (type.startsWith("DOUBLE")) {
255: sb.append(" DOUBLE" + "(" + columnName + ") ");
256:
257: } else {
258: //REAL: use DOUBLE function to convert from string and the cast to REAL
259: if (type.startsWith("REAL")) {
260: sb.append("cast" + "(" + " DOUBLE" + "("
261: + columnName + ") " + " AS " + "REAL"
262: + ") ");
263: } else {
264: //all other types does not need any special casting
265: sb.append(" " + columnName + " ");
266: }
267: }
268:
269: }
270: }
271:
272: //there is no column info available
273: if (first)
274: return " * ";
275: else
276: return sb.toString();
277: }
278:
279: /* returns comma seperated column Names delimited by quotes for the insert
280: * statement
281: * eg: "C1", "C2" , "C3" , "C4"
282: */
283: public String getInsertColumnNames() {
284: StringBuffer sb = new StringBuffer();
285: boolean first = true;
286: for (int index = 0; index < noOfColumns; index++) {
287: if (!first)
288: sb.append(", ");
289: else
290: first = false;
291: // column names can be SQL reserved words, so it
292: // is necessary delimit them using quotes for insert to work correctly.
293: sb.append("\"");
294: sb.append(insertColumnNames.get(index));
295: sb.append("\"");
296: }
297:
298: //there is no column info available
299: if (first)
300: return null;
301: else
302: return sb.toString();
303: }
304:
305: /*
306: Returns number of columns expected to be in the file from the user input paramters.
307: */
308: public int getExpectedNumberOfColumnsInFile() {
309: return expectedNumberOfCols;
310: }
311:
312: //Return true if the given table exists in the database
313: private boolean tableExists() throws SQLException {
314: DatabaseMetaData dmd = conn.getMetaData();
315: ResultSet rs = dmd.getTables(null, schemaName, tableName, null);
316: boolean foundTable = false;
317: if (rs.next()) {
318: //found the entry
319: foundTable = true;
320: }
321:
322: rs.close();
323: return foundTable;
324: }
325:
326: }
|