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 */
018package org.apache.oozie.util.db;
019
020import java.sql.Connection;
021import java.sql.SQLException;
022
023import java.sql.Blob;
024import java.sql.Timestamp;
025import java.util.List;
026
027public 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}