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
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
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
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
112     * @param dbType
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
155     * @param dbType
156     * @param tableColumns
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
189     * @param dbType
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
205     * @param dbType
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
218     * @return true if the driver is HSQL
219     * @throws SQLException
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
232     * @return true if the driver is MySQL
233     * @throws SQLException
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}