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