ColumnInfo


1   /*
2   
3      Derby - Class org.apache.derby.impl.load.ColumnInfo
4   
5      Licensed to the Apache Software Foundation (ASF) under one or more
6      contributor license agreements.  See the NOTICE file distributed with
7      this work for additional information regarding copyright ownership.
8      The ASF licenses this file to You under the Apache License, Version 2.0
9      (the "License"); you may not use this file except in compliance with
10     the License.  You may obtain a copy of the License at
11  
12        http://www.apache.org/licenses/LICENSE-2.0
13  
14     Unless required by applicable law or agreed to in writing, software
15     distributed under the License is distributed on an "AS IS" BASIS,
16     WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17     See the License for the specific language governing permissions and
18     limitations under the License.
19  
20   */
21  
22  package org.apache.derby.impl.load;
23  
24  import org.apache.derby.iapi.services.io.StoredFormatIds;
25  
26  import java.sql.ResultSet  ;
27  import java.sql.SQLException  ;
28  import java.sql.SQLWarning  ;
29  import java.sql.Statement  ;
30  import java.sql.PreparedStatement  ;
31  import java.sql.Connection  ;
32  import java.sql.ResultSetMetaData  ;
33  import java.sql.DatabaseMetaData  ;
34  import java.util.*;
35  
36  /**
37   *  
38   * This class provides supportto  create casting/conversions required to 
39   * perform import. Import VTI  gives all the data in VARCHAR type becuase data
40   * in the files is in CHAR format. There is no implicit cast availabile from
41   * VARCHAR to some of the types. In cases where explicit casting is allowed, 
42   * columns are casted with  explict cast to the type of table column; in case of 
43   * double/real explicit casting is also not allowd , scalar fuction DOUBLE is
44   * used in those cases.
45   *  
46   * @author suresht
47   */
48  class ColumnInfo {
49  
50      private ArrayList vtiColumnNames ;
51      private ArrayList insertColumnNames;
52      private ArrayList columnTypes ;
53      private int noOfColumns;
54      private ArrayList columnPositions;
55      private boolean createolumnNames = true;
56      private int expectedNumberOfCols ; //number of Columns that are suppose
57                                             // to be in the file to imported  
58      private Connection   conn;
59      private String   tableName;
60      private String   schemaName;
61  
62      /**
63       * Initialize the column type and name  information
64       * @param conn  - connection to use for metadata queries
65       * @param sName - table's schema
66       * @param tName - table Name
67       * @param insertColumnList - comma seperared insert statement column list 
68       * @param  vtiColumnIndexes - Indexes in the file
69       * @param  vtiColumnPrefix - Prefix to use to generate column names to select from VTI
70       * @exception Exception on error 
71       */
72      public ColumnInfo(Connection   conn,
73                        String   sName, 
74                        String   tName,
75                        String   insertColumnList, 
76                        String   vtiColumnIndexes,
77                        String   vtiColumnPrefix)
78          throws SQLException   
79      {
80  
81          vtiColumnNames = new ArrayList(1);
82          insertColumnNames = new ArrayList(1);
83          columnTypes = new ArrayList(1);
84          noOfColumns = 0;
85          this.conn = conn;
86  
87          this.schemaName = sName;
88          this.tableName =  tName;
89  
90          if(insertColumnList!=null)
91          {
92              //break the comma seperated column list and initialze column info
93              //eg: C2 , C1 , C3
94              StringTokenizer st = new StringTokenizer(insertColumnList , ",");
95              while (st.hasMoreTokens()) 
96              {
97                  String   columnName = (st.nextToken()).trim();
98                  if(!initializeColumnInfo(columnName))
99                  {
100                     if(tableExists())
101                         throw  LoadError.invalidColumnName(columnName);
102                     else
103                     {
104                         String   entityName = (schemaName !=null ? 
105                                              schemaName + "." + tableName :tableName); 
106                         throw LoadError.tableNotFound(entityName);
107                     }
108                 }
109             }
110         }else
111         {
112             //All columns in the table
113             if(!initializeColumnInfo(null))
114             {
115                 String   entityName = (schemaName !=null ? 
116                                      schemaName + "." + tableName :tableName); 
117                 throw LoadError.tableNotFound(entityName);
118             }
119         }
120         
121                 
122         //break the comma seperated column indexes for import file give by the user
123         //eg: "1, 3, 5, 7"
124         if(vtiColumnIndexes !=null)
125         {
126             
127             StringTokenizer st = new StringTokenizer(vtiColumnIndexes, ",");
128             while (st.hasMoreTokens()) 
129             {
130                 String   columnIndex  = (st.nextToken()).trim();
131                 vtiColumnNames.add(vtiColumnPrefix + columnIndex);
132                 int cIndex = (new Integer  (columnIndex )).intValue();
133                 if(cIndex > expectedNumberOfCols )
134                     expectedNumberOfCols= cIndex ;
135             }
136 
137         }
138 
139 
140         //if column indexes are not specified  ; create names for all collumns requested
141         if(vtiColumnNames.size() < 1)
142         {
143             for(int index = 1 ; index <= noOfColumns; index++)
144             {
145                 vtiColumnNames.add(vtiColumnPrefix + index);
146             }
147             expectedNumberOfCols = noOfColumns ;
148         }
149     }
150 
151 
152     private boolean initializeColumnInfo(String   columnPattern)
153         throws SQLException  
154     {
155         DatabaseMetaData   dmd = conn.getMetaData();
156         ResultSet   rs = dmd.getColumns(null, 
157                                       schemaName,
158                                       tableName,
159                                       columnPattern);
160         boolean foundTheColumn=false;
161         while (rs.next())
162         {
163 
164             // 4.COLUMN_NAME String => column name
165             String   columnName = rs.getString(4);
166 
167             // 5.DATA_TYPE short => SQL type from java.sql.Types
168             short dataType = rs.getShort(5);
169 
170             // 6.TYPE_NAME String => Data source dependent type name
171             String   typeName = rs.getString(6);
172 
173             
174             // 7.COLUMN_SIZE int => column size. For char or date types
175             // this is the maximum number of characters, for numeric or
176             // decimal types this is precision.
177             int columnSize = rs.getInt(7);
178 
179             // 9.DECIMAL_DIGITS int => the number of fractional digits
180             int decimalDigits = rs.getInt(9);
181 
182             // 10.NUM_PREC_RADIX int => Radix (typically either 10 or 2)
183             int numPrecRadix = rs.getInt(10);
184             foundTheColumn = true;
185             if(importExportSupportedType(dataType))
186             {
187 
188                 insertColumnNames.add(columnName);
189                 String   sqlType = typeName + getTypeOption(typeName , columnSize , columnSize , decimalDigits);
190                 columnTypes.add(noOfColumns , sqlType);
191                 noOfColumns++;
192             }else
193             {
194                 rs.close();
195                 throw
196                     LoadError.nonSupportedTypeColumn(columnName,typeName);
197             }
198 
199         }
200 
201         rs.close();
202         return foundTheColumn;
203     }
204 
205 
206     //return true if the given type is supported by import/export
207     public  static final boolean importExportSupportedType(int type){
208 
209         return !(type == java.sql.Types.BINARY ||
210                  type == java.sql.Types.BIT ||
211                  type == java.sql.Types.JAVA_OBJECT ||
212                  type == java.sql.Types.OTHER ||
213                  type == java.sql.Types.CLOB ||
214                  type == java.sql.Types.BLOB ||
215                  type == StoredFormatIds.XML_TYPE_ID); 
216     }
217 
218 
219     private String   getTypeOption(String   type , int length , int precision , int scale)
220     {
221 
222             if ((type.equals("CHAR") ||
223                  type.equals("BLOB") ||
224                  type.equals("CLOB") ||
225                  type.equals("VARCHAR")) && length != 0)
226             {
227                  return "(" + length + ")";
228             }
229 
230             if (type.equals("FLOAT")  && precision != 0)
231                 return  "(" + precision + ")";
232 
233             //there are three format of decimal and numeric. Plain decimal, decimal(x)
234             //and decimal(x,y). x is precision and y is scale.
235             if (type.equals("DECIMAL") ||
236                 type.equals("NUMERIC")) 
237             {
238                 if ( precision != 0 && scale == 0)
239                     return "(" + precision + ")";
240                 else if (precision != 0 && scale != 0)
241                     return "(" + precision + "," + scale + ")";
242                 else if(precision == 0 && scale!=0)
243                     return "(" + scale + ")";
244             }
245 
246             if ((type.equals("DECIMAL") ||
247                  type.equals("NUMERIC")) && scale != 0)
248                 return "(" + scale + ")";
249 
250             //no special type option
251             return "";
252     }
253 
254 
255     /*
256      * Returns a  string of columns with proper casting/conversion
257      * to be used to select from import VTI.
258      */
259     public String   getColumnNamesWithCasts()
260     {
261         StringBuffer   sb = new StringBuffer  ();
262         boolean first = true;
263         int noOfVtiCols =  vtiColumnNames.size();
264         for(int index = 0 ; index < noOfColumns && index < noOfVtiCols; index++)
265         {
266             if(!first)
267                 sb.append(", ");
268             else
269                 first = false;
270             String   type = (String  ) columnTypes.get(index);
271             String   columnName = (String  ) vtiColumnNames.get(index);
272            
273             if(type.startsWith("SMALLINT") ||
274                type.startsWith("INTEGER") ||
275                type.startsWith("DECIMAL") ||
276                type.startsWith("BIGINT") ||
277                type.startsWith("NUMERIC"))                            
278             {
279                 //these types require explicit casting
280                 sb.append(" cast" + "(" + columnName + " AS " + type + ") "); 
281 
282             }else
283             {
284                 //if it is DOUBLE use scalar DOUBLE function no explicit casting allowed
285                 if(type.startsWith("DOUBLE"))
286                 {
287                     sb.append(" DOUBLE" + "(" + columnName + ") ");
288 
289                 }else
290                 {
291                     //REAL: use DOUBLE function to convert from string and the cast to REAL
292                     if(type.startsWith("REAL"))
293                     {
294                         sb.append("cast" + "(" + 
295                                   " DOUBLE" + "(" + columnName + ") " + 
296                                   " AS " +  "REAL" + ") ");
297                     }else
298                     {
299                         //all other types does  not need any special casting
300                         sb.append(" " + columnName +  " "); 
301                     }
302                 }
303 
304             }
305         }
306 
307         //there is no column info available
308         if(first)
309             return " * ";
310         else
311             return sb.toString();
312     }
313 
314     /* returns comma seperated column Names delimited by quotes for the insert 
315      * statement
316      * eg: "C1", "C2" , "C3" , "C4" 
317      */
318     public String   getInsertColumnNames()
319     {
320         StringBuffer   sb = new StringBuffer  ();
321         boolean first = true;
322         for(int index = 0 ; index < noOfColumns; index++)
323         {
324             if(!first)
325                 sb.append(", ");
326             else
327                 first = false;
328             // column names can be SQL reserved words, so it 
329             // is necessary delimit them using quotes for insert to work correctly. 
330             sb.append("\"");
331             sb.append(insertColumnNames.get(index));
332             sb.append("\"");
333         }
334     
335         //there is no column info available
336         if(first)
337             return null;
338         else
339             return sb.toString();
340     }
341 
342     /*
343       Returns number of columns expected to be in  the file from the user input paramters.
344      */
345     public int getExpectedNumberOfColumnsInFile()
346     {
347         return expectedNumberOfCols;
348     }
349 
350     //Return true if the given table exists in the database
351     private boolean tableExists() throws SQLException  
352     {
353         DatabaseMetaData   dmd = conn.getMetaData();
354         ResultSet   rs = dmd.getTables(null, schemaName, tableName, null);
355         boolean foundTable = false;
356         if(rs.next())
357         {
358             //found the entry
359             foundTable = true;
360         }
361         
362         rs.close();
363         return foundTable;
364     }
365 
366 }
367 
368 
369 
370 
371 
372
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags