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    }