001/** 002 * Licensed to the Apache Software Foundation (ASF) under one 003 * or more contributor license agreements. See the NOTICE file 004 * distributed with this work for additional information 005 * regarding copyright ownership. The ASF licenses this file 006 * to you under the Apache License, Version 2.0 (the 007 * "License"); you may not use this file except in compliance 008 * with the License. You may obtain a copy of the License at 009 * 010 * http://www.apache.org/licenses/LICENSE-2.0 011 * 012 * Unless required by applicable law or agreed to in writing, software 013 * distributed under the License is distributed on an "AS IS" BASIS, 014 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 015 * See the License for the specific language governing permissions and 016 * limitations under the License. 017 */ 018 019package org.apache.oozie.util.db; 020 021import java.sql.Connection; 022import java.sql.SQLException; 023 024import java.sql.Blob; 025import java.sql.Timestamp; 026import java.util.List; 027 028public class Schema { 029 /** 030 * Interface for DB Table 031 */ 032 public static interface Table { 033 /** 034 * Name of the Table 035 * 036 * @return Name of the Table 037 */ 038 String name(); 039 } 040 041 /** 042 * Interface or table columns 043 */ 044 public static interface Column { 045 /** 046 * Table to which the column belongs 047 * 048 * @return table name 049 */ 050 Table table(); 051 052 /** 053 * Alias to be used by the select statement for this column 054 * 055 * @return alias for column 056 */ 057 String asLabel(); 058 059 /** 060 * Name of the column 061 * 062 * @return column name 063 */ 064 String columnName(); 065 066 /** 067 * Returns the datatype of the column 068 * 069 * @return column type 070 */ 071 Class<?> getType(); 072 073 /** 074 * Returns the length of the column 075 * 076 * @return Returns the length of the column 077 */ 078 int getLength(); 079 080 /** 081 * Returns if the field is a primary key or not 082 * 083 * @return true if field is a primary key 084 */ 085 boolean isPrimaryKey(); 086 } 087 088 /** 089 * Interface for Index 090 */ 091 public static interface Index { 092 /** 093 * Column that is to be indexed 094 * 095 * @return Column that is to be indexed 096 */ 097 Column column(); 098 } 099 100 /** 101 * DB types 102 */ 103 public static enum DBType { 104 HSQL, MySQL, ORACLE; 105 } 106 107 //TODO Add the SQL Change catalog for different DBMS. 108 /** 109 * Returns the appropriate DB type for given column according to the DB Type 110 * 111 * @param column the column that need the type 112 * @param dbType the database type 113 * @return column type 114 */ 115 public static String getDbDataType(Column column, DBType dbType) { 116 String retVal = null; 117 if (String.class.equals(column.getType())) { 118 if (column.getLength() < 0) { 119 retVal = (dbType.equals(DBType.HSQL) ? "VARCHAR" : (dbType.equals(DBType.ORACLE) ? "CLOB" : "TEXT")); 120 } 121 else { 122 retVal = (dbType.equals(DBType.ORACLE) ? "VARCHAR2(" + column.getLength() + ")" : "VARCHAR(" + column.getLength() + ")"); 123 } 124 } 125 else { 126 if (Timestamp.class.equals(column.getType())) { 127 retVal = (dbType.equals(DBType.ORACLE) ? "DATE" : "DATETIME"); 128 } 129 else { 130 if (Boolean.class.equals(column.getType())) { 131 retVal = (dbType.equals(DBType.ORACLE) ? "NUMBER(3, 0)" : "BOOLEAN"); 132 } 133 else { 134 if (Long.class.equals(column.getType())) { 135 retVal = (dbType.equals(DBType.ORACLE) ? "NUMBER (19,0)" : "BIGINT"); 136 } 137 else { 138 if (Blob.class.equals(column.getType())) { 139 retVal = (dbType.equals(DBType.MySQL) ? "MEDIUMBLOB" : (dbType.equals(DBType.ORACLE) ? "BLOB" : "LONGVARBINARY")); 140 } 141 else { 142 throw new RuntimeException("Column Type[" + column.getType() + "] not mapped to any DB Data Type !!"); 143 } 144 } 145 } 146 } 147 } 148 return retVal; 149 } 150 151 /** 152 * Generates the SQL Statement for creating the table 153 * 154 * @param table the table name 155 * @param dbType the database type 156 * @param tableColumns the columns of the table 157 * @return CREATE TABLE SQL Statement 158 */ 159 public static String generateCreateTableScript(Table table, DBType dbType, List<Column> tableColumns) { 160 StringBuilder sb = new StringBuilder("CREATE TABLE ").append(table).append(" ( "); 161 StringBuilder pk = new StringBuilder(", PRIMARY KEY ( "); 162 boolean pkFlag = false; 163 String sep = ""; 164 String psep = ""; 165 for (Column column : tableColumns) { 166 sb.append(sep).append(column.columnName() + " ").append(Schema.getDbDataType(column, dbType)); 167 if (column.isPrimaryKey()) { 168 pkFlag = true; 169 pk.append(psep).append(column.columnName()); 170 psep = ", "; 171 } 172 sep = ", "; 173 } 174 if (pkFlag) { 175 pk.append(" )"); 176 sb.append(pk.toString()); 177 } 178 sb.append(" )"); 179 if (dbType == DBType.MySQL) { 180 sb.append(" ENGINE=InnoDB"); 181 } 182 return sb.toString(); 183 } 184 185 /** 186 * Generates the SQL Statement for droping the table 187 * 188 * @param table the table name 189 * @param dbType the database type 190 * @return DROP TABLE SQL Statement 191 */ 192 public static String generateDropTableScript(Table table, DBType dbType) { 193 StringBuilder sb = new StringBuilder("DROP TABLE ").append(table); 194 if (dbType == DBType.ORACLE) { 195 sb.append(" purge"); 196 } 197 return sb.toString(); 198 } 199 200 201 /** 202 * Generates the SQL statement for creating the Index 203 * 204 * @param index the index 205 * @param dbType the database type 206 * @return CREATE INDEX SQL Statement 207 */ 208 public static String generateCreateIndexScript(Index index, DBType dbType) { 209 StringBuilder sb = new StringBuilder("CREATE INDEX ").append(index).append(" ON ").append( 210 index.column().table()).append("( " + index.column().columnName() + " )"); 211 return sb.toString(); 212 } 213 214 /** 215 * Checks if the given connection's driver is HSQL Database Driver 216 * 217 * @param conn the connection 218 * @return true if the driver is HSQL 219 * @throws SQLException if the connection type could not be determined 220 */ 221 public static boolean isHsqlConnection(Connection conn) throws SQLException { 222 if (conn.getMetaData().getDriverName().toLowerCase().contains(DBType.HSQL.name().toLowerCase())) { 223 return true; 224 } 225 return false; 226 } 227 228 /** 229 * Checks if the given connection's driver is MySQL Database Driver 230 * 231 * @param conn the connection 232 * @return true if the driver is MySQL 233 * @throws SQLException if the connection type could not be determined 234 */ 235 public static boolean isMySqlConnection(Connection conn) throws SQLException { 236 if (conn.getMetaData().getDriverName().toLowerCase().contains(DBType.MySQL.name().toLowerCase())) { 237 return true; 238 } 239 return false; 240 } 241}