--- /dev/null
+/*\r
+\r
+ Derby - Class org.apache.derby.impl.load.ColumnInfo\r
+\r
+ Licensed to the Apache Software Foundation (ASF) under one or more\r
+ contributor license agreements. See the NOTICE file distributed with\r
+ this work for additional information regarding copyright ownership.\r
+ The ASF licenses this file to You under the Apache License, Version 2.0\r
+ (the "License"); you may not use this file except in compliance with\r
+ the License. You may obtain a copy of the License at\r
+\r
+ http://www.apache.org/licenses/LICENSE-2.0\r
+\r
+ Unless required by applicable law or agreed to in writing, software\r
+ distributed under the License is distributed on an "AS IS" BASIS,\r
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\r
+ See the License for the specific language governing permissions and\r
+ limitations under the License.\r
+\r
+ */\r
+\r
+package org.apache.derby.impl.load;\r
+\r
+import org.apache.derby.iapi.services.io.StoredFormatIds;\r
+\r
+import java.sql.ResultSet;\r
+import java.sql.SQLException;\r
+import java.sql.SQLWarning;\r
+import java.sql.Statement;\r
+import java.sql.PreparedStatement;\r
+import java.sql.Connection;\r
+import java.sql.ResultSetMetaData;\r
+import java.sql.DatabaseMetaData;\r
+import java.util.*;\r
+\r
+/**\r
+ * \r
+ * This class provides supportto create casting/conversions required to \r
+ * perform import. Import VTI gives all the data in VARCHAR type becuase data\r
+ * in the files is in CHAR format. There is no implicit cast availabile from\r
+ * VARCHAR to some of the types. In cases where explicit casting is allowed, \r
+ * columns are casted with explict cast to the type of table column; in case of \r
+ * double/real explicit casting is also not allowd , scalar fuction DOUBLE is\r
+ * used in those cases.\r
+ * \r
+ */\r
+class ColumnInfo {\r
+\r
+ private ArrayList vtiColumnNames ;\r
+ private ArrayList insertColumnNames;\r
+ private ArrayList columnTypes ;\r
+ private ArrayList jdbcColumnTypes;\r
+ private int noOfColumns;\r
+ private ArrayList columnPositions;\r
+ private boolean createolumnNames = true;\r
+ private int expectedNumberOfCols ; //number of Columns that are suppose\r
+ // to be in the file to imported \r
+ private Connection conn;\r
+ private String tableName;\r
+ private String schemaName;\r
+\r
+ /**\r
+ * Initialize the column type and name information\r
+ * @param conn - connection to use for metadata queries\r
+ * @param sName - table's schema\r
+ * @param tName - table Name\r
+ * @param insertColumnList - comma seperared insert statement column list \r
+ * @param vtiColumnIndexes - Indexes in the file\r
+ * @param vtiColumnPrefix - Prefix to use to generate column names to select from VTI\r
+ * @exception Exception on error \r
+ */\r
+ public ColumnInfo(Connection conn,\r
+ String sName, \r
+ String tName,\r
+ String insertColumnList, \r
+ String vtiColumnIndexes,\r
+ String vtiColumnPrefix)\r
+ throws SQLException \r
+ {\r
+\r
+ vtiColumnNames = new ArrayList(1);\r
+ insertColumnNames = new ArrayList(1);\r
+ columnTypes = new ArrayList(1);\r
+ jdbcColumnTypes = new ArrayList(1);\r
+ noOfColumns = 0;\r
+ this.conn = conn;\r
+\r
+ this.schemaName = sName;\r
+ this.tableName = tName;\r
+\r
+ if(insertColumnList!=null)\r
+ {\r
+ //break the comma seperated column list and initialze column info\r
+ //eg: C2 , C1 , C3\r
+ StringTokenizer st = new StringTokenizer(insertColumnList , ",");\r
+ while (st.hasMoreTokens()) \r
+ {\r
+ String columnName = (st.nextToken()).trim();\r
+ if(!initializeColumnInfo(columnName))\r
+ {\r
+ if(tableExists())\r
+ throw LoadError.invalidColumnName(columnName);\r
+ else\r
+ {\r
+ String entityName = (schemaName !=null ? \r
+ schemaName + "." + tableName :tableName); \r
+ throw LoadError.tableNotFound(entityName);\r
+ }\r
+ }\r
+ }\r
+ }else\r
+ {\r
+ //All columns in the table\r
+ if(!initializeColumnInfo(null))\r
+ {\r
+ String entityName = (schemaName !=null ? \r
+ schemaName + "." + tableName :tableName); \r
+ throw LoadError.tableNotFound(entityName);\r
+ }\r
+ }\r
+ \r
+ \r
+ //break the comma seperated column indexes for import file give by the user\r
+ //eg: "1, 3, 5, 7"\r
+ if(vtiColumnIndexes !=null)\r
+ {\r
+ \r
+ StringTokenizer st = new StringTokenizer(vtiColumnIndexes, ",");\r
+ while (st.hasMoreTokens()) \r
+ {\r
+ String columnIndex = (st.nextToken()).trim();\r
+ vtiColumnNames.add(vtiColumnPrefix + columnIndex);\r
+ int cIndex = (new Integer(columnIndex )).intValue();\r
+ if(cIndex > expectedNumberOfCols )\r
+ expectedNumberOfCols= cIndex ;\r
+ }\r
+\r
+ }\r
+\r
+\r
+ //if column indexes are not specified ; create names for all collumns requested\r
+ if(vtiColumnNames.size() < 1)\r
+ {\r
+ for(int index = 1 ; index <= noOfColumns; index++)\r
+ {\r
+ vtiColumnNames.add(vtiColumnPrefix + index);\r
+ }\r
+ expectedNumberOfCols = noOfColumns ;\r
+ }\r
+ }\r
+\r
+\r
+ private boolean initializeColumnInfo(String columnPattern)\r
+ throws SQLException\r
+ {\r
+ DatabaseMetaData dmd = conn.getMetaData();\r
+ ResultSet rs = dmd.getColumns(null, \r
+ schemaName,\r
+ tableName,\r
+ columnPattern);\r
+ boolean foundTheColumn=false;\r
+ while (rs.next())\r
+ {\r
+\r
+ // 4.COLUMN_NAME String => column name\r
+ String columnName = rs.getString(4);\r
+\r
+ // 5.DATA_TYPE short => SQL type from java.sql.Types\r
+ short dataType = rs.getShort(5);\r
+\r
+ // 6.TYPE_NAME String => Data source dependent type name\r
+ String typeName = rs.getString(6);\r
+\r
+ \r
+ // 7.COLUMN_SIZE int => column size. For char or date types\r
+ // this is the maximum number of characters, for numeric or\r
+ // decimal types this is precision.\r
+ int columnSize = rs.getInt(7);\r
+\r
+ // 9.DECIMAL_DIGITS int => the number of fractional digits\r
+ int decimalDigits = rs.getInt(9);\r
+\r
+ // 10.NUM_PREC_RADIX int => Radix (typically either 10 or 2)\r
+ int numPrecRadix = rs.getInt(10);\r
+ foundTheColumn = true;\r
+ if(importExportSupportedType(dataType))\r
+ {\r
+\r
+ insertColumnNames.add(columnName);\r
+ String sqlType = typeName + getTypeOption(typeName , columnSize , columnSize , decimalDigits);\r
+ columnTypes.add(sqlType);\r
+ jdbcColumnTypes.add(new Integer(dataType));\r
+ noOfColumns++;\r
+ }else\r
+ {\r
+ rs.close();\r
+ throw\r
+ LoadError.nonSupportedTypeColumn(columnName,typeName);\r
+ }\r
+\r
+ }\r
+\r
+ rs.close();\r
+ return foundTheColumn;\r
+ }\r
+\r
+\r
+ //return true if the given type is supported by import/export\r
+ public static final boolean importExportSupportedType(int type){\r
+\r
+ return !(type == java.sql.Types.BIT ||\r
+ type == java.sql.Types.JAVA_OBJECT ||\r
+ type == java.sql.Types.OTHER ||\r
+ type == StoredFormatIds.XML_TYPE_ID); \r
+ }\r
+\r
+\r
+ private String getTypeOption(String type , int length , int precision , int scale)\r
+ {\r
+\r
+ if ((type.equals("CHAR") ||\r
+ type.equals("BLOB") ||\r
+ type.equals("CLOB") ||\r
+ type.equals("VARCHAR")) && length != 0)\r
+ {\r
+ return "(" + length + ")";\r
+ }\r
+\r
+ if (type.equals("FLOAT") && precision != 0)\r
+ return "(" + precision + ")";\r
+\r
+ //there are three format of decimal and numeric. Plain decimal, decimal(x)\r
+ //and decimal(x,y). x is precision and y is scale.\r
+ if (type.equals("DECIMAL") ||\r
+ type.equals("NUMERIC")) \r
+ {\r
+ if ( precision != 0 && scale == 0)\r
+ return "(" + precision + ")";\r
+ else if (precision != 0 && scale != 0)\r
+ return "(" + precision + "," + scale + ")";\r
+ else if(precision == 0 && scale!=0)\r
+ return "(" + scale + ")";\r
+ }\r
+\r
+ if ((type.equals("DECIMAL") ||\r
+ type.equals("NUMERIC")) && scale != 0)\r
+ return "(" + scale + ")";\r
+\r
+ //no special type option\r
+ return "";\r
+ }\r
+\r
+\r
+ /*\r
+ * Returns a string of columns with proper casting/conversion\r
+ * to be used to select from import VTI.\r
+ */\r
+ public String getColumnNamesWithCasts()\r
+ {\r
+ StringBuffer sb = new StringBuffer();\r
+ boolean first = true;\r
+ int noOfVtiCols = vtiColumnNames.size();\r
+ for(int index = 0 ; index < noOfColumns && index < noOfVtiCols; index++)\r
+ {\r
+ if(!first)\r
+ sb.append(", ");\r
+ else\r
+ first = false;\r
+ String type = (String) columnTypes.get(index);\r
+ String columnName = (String) vtiColumnNames.get(index);\r
+ \r
+ if(type.startsWith("SMALLINT") ||\r
+ type.startsWith("INTEGER") ||\r
+ type.startsWith("DECIMAL") ||\r
+ type.startsWith("BIGINT") ||\r
+ type.startsWith("NUMERIC")) \r
+ {\r
+ //these types require explicit casting\r
+ sb.append(" cast" + "(" + columnName + " AS " + type + ") "); \r
+\r
+ }else\r
+ {\r
+ //if it is DOUBLE use scalar DOUBLE function no explicit casting allowed\r
+ if(type.startsWith("DOUBLE"))\r
+ {\r
+ sb.append(" DOUBLE" + "(" + columnName + ") ");\r
+\r
+ }else\r
+ {\r
+ //REAL: use DOUBLE function to convert from string and the cast to REAL\r
+ if(type.startsWith("REAL"))\r
+ {\r
+ sb.append("cast" + "(" + \r
+ " DOUBLE" + "(" + columnName + ") " + \r
+ " AS " + "REAL" + ") ");\r
+ }else\r
+ {\r
+ //all other types does not need any special casting\r
+ sb.append(" " + columnName + " "); \r
+ }\r
+ }\r
+\r
+ }\r
+ }\r
+\r
+ //there is no column info available\r
+ if(first)\r
+ return " * ";\r
+ else\r
+ return sb.toString();\r
+ }\r
+\r
+ /* returns comma seperated column Names delimited by quotes for the insert \r
+ * statement\r
+ * eg: "C1", "C2" , "C3" , "C4" \r
+ */\r
+ public String getInsertColumnNames()\r
+ {\r
+ StringBuffer sb = new StringBuffer();\r
+ boolean first = true;\r
+ for(int index = 0 ; index < noOfColumns; index++)\r
+ {\r
+ if(!first)\r
+ sb.append(", ");\r
+ else\r
+ first = false;\r
+ // column names can be SQL reserved words, so it \r
+ // is necessary delimit them using quotes for insert to work correctly. \r
+ sb.append("\"");\r
+ sb.append(insertColumnNames.get(index));\r
+ sb.append("\"");\r
+ }\r
+ \r
+ //there is no column info available\r
+ if(first)\r
+ return null;\r
+ else\r
+ return sb.toString();\r
+ }\r
+\r
+ /*\r
+ Returns number of columns expected to be in the file from the user input paramters.\r
+ */\r
+ public int getExpectedNumberOfColumnsInFile()\r
+ {\r
+ return expectedNumberOfCols;\r
+ }\r
+\r
+ //Return true if the given table exists in the database\r
+ private boolean tableExists() throws SQLException\r
+ {\r
+ DatabaseMetaData dmd = conn.getMetaData();\r
+ ResultSet rs = dmd.getTables(null, schemaName, tableName, null);\r
+ boolean foundTable = false;\r
+ if(rs.next())\r
+ {\r
+ //found the entry\r
+ foundTable = true;\r
+ }\r
+ \r
+ rs.close();\r
+ return foundTable;\r
+ }\r
+\r
+\r
+ /*\r
+ * Returns the the expected vti data column types in a String format. \r
+ * Format : (COLUMN NAME : TYPE [, COLUMN NAME : TYPE]*)\r
+ * eg: COLUMN1:1 (java.sql.Types.CHAR) , COLUMN2: -1(LONGVARCHAR) , \r
+ * COLUMN3 : 2004 (BLOB)\r
+ */\r
+ public String getExpectedVtiColumnTypesAsString() {\r
+\r
+ StringBuffer vtiColumnTypes = new StringBuffer();\r
+ // expected types of data in the import file, based on \r
+ // the how columns in the data file are mapped to \r
+ // the table columns. \r
+ boolean first = true;\r
+ for (int i =0 ; i < noOfColumns && i < vtiColumnNames.size(); i++) {\r
+ if (first) \r
+ first = false;\r
+ else\r
+ vtiColumnTypes.append(",");\r
+\r
+ vtiColumnTypes.append(vtiColumnNames.get(i) + ":" + \r
+ jdbcColumnTypes.get(i));\r
+ } \r
+\r
+ if(first) {\r
+ // there is no information about column types.\r
+ return null;\r
+ }\r
+ else\r
+ return vtiColumnTypes.toString();\r
+ }\r
+\r
+\r
+ /*\r
+ * Get the expected vti data column types. This information was \r
+ * passed earlies as string to the vti. This rourine extract the \r
+ * information from the string.\r
+ * @param columnTypesStr import data column type information , \r
+ * encoded as string. \r
+ * @param noOfColumns number of columns in the import file.\r
+ * \r
+ * @see getExpectedVtiColumnTypesAsString()\r
+ */\r
+ public static int[] getExpectedVtiColumnTypes(String columnTypesStr, \r
+ int noOfColumns) \r
+ {\r
+\r
+ // extract the table column types. Break the comma seperated \r
+ // column types into java.sql.Types int values from the columnTypes \r
+ // string that got passed to the import VTI.\r
+\r
+ //eg: COLUMN1:1 (java.sql.Types.CHAR) , COLUMN2: -1(LONGVARCHAR) , \r
+ //COLUMN3 : 2004 (BLOB)\r
+\r
+ int[] vtiColumnTypes = new int[noOfColumns];\r
+\r
+ // expected column type information is only available \r
+ // for the columns that are being imported from the file.\r
+ // columns type information is not required when \r
+ // a column in the data file is not one of the \r
+ // imported column, just assume they are of VARCHAR type. \r
+ \r
+ for (int i = 0 ; i < noOfColumns ; i++)\r
+ vtiColumnTypes[i] = java.sql.Types.VARCHAR;\r
+\r
+ StringTokenizer st = new StringTokenizer(columnTypesStr , ",");\r
+ while (st.hasMoreTokens()) \r
+ {\r
+ String colTypeInfo = (st.nextToken()).trim();\r
+ int colTypeOffset = colTypeInfo.indexOf(":");\r
+\r
+ // column names format is "COLUMN" + columnNumner\r
+ int colIndex = (new Integer(colTypeInfo.substring(6, \r
+ colTypeOffset))).intValue();\r
+ int colType = (new Integer(colTypeInfo.substring(\r
+ colTypeOffset+1))).intValue();\r
+\r
+ // column numbers start with 1. Check if user by mistake has \r
+ // specified a column number that is large than than the \r
+ // number of columns exist in the file, if that is the case\r
+ // don't assign the type.\r
+ if (colIndex <= noOfColumns) \r
+ vtiColumnTypes[colIndex-1] = colType;\r
+ \r
+ }\r
+ return vtiColumnTypes;\r
+ }\r
+}\r
+\r
+\r
+\r
+\r
+\r