001 /*
002 * CRIMSON
003 * Copyright (c) 2006, Stephen Fisher, Susan Davidson, and Junhyong Kim,
004 * University of Pennsylvania.
005 *
006 * This program is free software; you can redistribute it and/or
007 * modify it under the terms of the GNU General Public License as
008 * published by the Free Software Foundation; either version 2 of the
009 * License, or (at your option) any later version.
010 *
011 * This program is distributed in the hope that it will be useful, but
012 * WITHOUT ANY WARRANTY; without even the implied warranty of
013 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014 * General Public License for more details.
015 *
016 * You should have received a copy of the GNU General Public License
017 * along with this program; if not, write to the Free Software
018 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
019 * 02110-1301 USA.
020 *
021 * @(#)SQL.java
022 */
023
024 package edu.upenn.crimson.io;
025
026 import edu.upenn.crimson.*;
027
028 /**
029 * Most SQL commands are contained here, to facilitate portability to
030 * different databases. This object does not do anything other than
031 * return SQL code. The running of the code and processing of the
032 * output is done by the calling methods.
033 *
034 * @author Stephen Fisher
035 * @version $Id: SQL.java,v 1.20 2009/07/16 16:19:52 fisher Exp $
036 */
037
038 public class SQL {
039
040 /** This is the list of databases we can use. */
041 private static final String[] dbTypes = { "Oracle", "MySQL" };
042
043 /**
044 * This is a name of the database: -1 = none. By using an int to
045 * store the type we simplify tests for the type.
046 */
047 private static int dbType = -1;
048
049 //--------------------------------------------------------------------------
050 // Getters and Setters
051
052 public static String[] getDBTypes() { return dbTypes; }
053
054 /** Get the type of database (oracle or mysql). */
055 public static String getDBType() {
056 if ((dbType > -1) && (dbType < dbTypes.length)) return dbTypes[dbType];
057 else return "";
058 }
059
060 /**
061 * Set the type of database (oracle or mysql). This will return
062 * false if "type" is not a valid database type or is empty. If
063 * successful, this will update the default dbType.
064 */
065 public static boolean setDBType(String type) {
066 // make sure database is closed
067 if (Database.getConnection() != null) Database.close();
068 if (Database.getConnection() != null) {
069 CrimsonUtils.printError("Can't close database connection, so the database type can be changed.");
070 return false;
071 }
072
073 if (CrimsonUtils.isEmpty(type)) {
074 dbType = -1;
075 return false;
076 } else {
077 for (int i = 0; i < dbTypes.length; i++) {
078 if (type.equalsIgnoreCase(dbTypes[i])) {
079 dbType = i;
080
081 // we found a match, so update the default
082 CrimsonUtils.setDBType(type);
083
084 // register the driver
085 try {
086 Class.forName(dbDriver());
087 } catch (ClassNotFoundException e) {
088 e.printStackTrace();
089 }
090
091 return true;
092 }
093 }
094 }
095
096 // if we get this far then the database type isn't valid.
097 String msg = "Unknown database type \'" + type + "\'. Valid types are : ";
098 for (int i = 0; i < dbTypes.length; i++) msg += dbTypes[i] + " ";
099 CrimsonUtils.printError(msg);
100
101 dbType = -1;
102 return false;
103 }
104
105 //--------------------------------------------------------------------------
106 // Miscellaneous methods
107
108 /**
109 * Get the index value of the type of database. This value can be
110 * used to get the database type from dbTypes[].
111 */
112 public static int getDBTypeValue() { return dbType; }
113
114 public static boolean isOracle() {
115 if (dbType == 0) return true;
116 else return false;
117 }
118
119 public static boolean isMySQL() {
120 if (dbType == 1) return true;
121 else return false;
122 }
123
124 public static String dbDriver() {
125 // if (isOracle()) return "oracle.jdbc.driver.OracleDriver";
126 if (isOracle()) return "oracle.jdbc.OracleDriver";
127 if (isMySQL()) return "com.mysql.jdbc.Driver";
128 return "";
129 }
130
131 public static String dbURL() {
132 if (isOracle()) {
133 // alternate form for XE that includes login in url:
134 // jdbc:oracle:thin:<username>/<password>@<host>:<port>/XE
135 // for oracle (non-XE):
136 // jdbc:oracle:thin:<username>/<password>@<host>:<port>/
137 // "jdbc:oracle:thin:@<host>:<port>/XE";
138 return "jdbc:oracle:thin:@";
139 }
140 if (isMySQL()) {
141 // "jdbc:mysql://<host>/<db>";
142 return "jdbc:mysql://";
143 }
144 return "";
145 }
146
147 /**
148 * This will return the database date, as is used to test database
149 * connectivity.
150 */
151 public static String getDate() {
152 if (isOracle()) return "select sysdate from dual";
153 if (isMySQL()) return "select curdate()";
154 return "";
155 }
156
157 /**
158 * This code will return the table name, if the table exists in
159 * the database.
160 */
161 public static String tableExists(String table) {
162 // if (isOracle()) return "select tname from tab where tname = '" + table + "'";
163 if (isOracle()) return "select table_name from all_tables where table_name = '" + table + "'";
164 if (isMySQL()) return "show tables like '" + table + "'";
165 return "";
166 }
167
168 /** This method will create all of the necessary tables. */
169 public static String createTable(String table) {
170 String sql = "CREATE TABLE " + table;
171
172 if (table.equals("TREES")) {
173 if (isOracle()) { // ORACLE
174 sql += "( id VARCHAR2(100), ";
175 sql += "model_id VARCHAR2(100) CONSTRAINT tree2_fk REFERENCES MODELS (id), ";
176 sql += "notes VARCHAR2(4000), ";
177 sql += "num_species NUMBER(32,0) DEFAULT -1, ";
178 sql += "num_leaves NUMBER(32,0) DEFAULT -1, ";
179 sql += "is_binary NUMBER(1,0) DEFAULT 0 CONSTRAINT tree6_ck CHECK(is_binary >= 0 AND is_binary <= 1), ";
180 sql += "is_ultrametric NUMBER(1,0) DEFAULT 0 CONSTRAINT tree7_ck CHECK(is_ultrametric >= 0 AND is_ultrametric <= 1), ";
181 sql += "min_level NUMBER(32,0) DEFAULT -1, ";
182 sql += "max_level NUMBER(32,0) DEFAULT -1, ";
183 sql += "min_stem_length NUMBER DEFAULT -1.0, ";
184 sql += "max_stem_length NUMBER DEFAULT -1.0, ";
185 sql += "min_temp_depth NUMBER DEFAULT -1.0, ";
186 sql += "max_temp_depth NUMBER DEFAULT -1.0, ";
187 sql += "newick CLOB DEFAULT empty_clob(), ";
188 sql += "CONSTRAINT tree1_pk PRIMARY KEY (id) ) ";
189 } else if (isMySQL()) { // MySQL
190 sql += "( id VARCHAR(100), ";
191 sql += "model_id VARCHAR(100), ";
192 sql += "notes VARCHAR(4000), ";
193 sql += "num_species INT DEFAULT -1, ";
194 sql += "num_leaves INT DEFAULT -1, ";
195 sql += "is_binary TINYINT DEFAULT 0, ";
196 sql += "is_ultrametric TINYINT DEFAULT 0, ";
197 sql += "min_level INT DEFAULT -1, ";
198 sql += "max_level INT DEFAULT -1, ";
199 sql += "min_stem_length DOUBLE DEFAULT -1.0, ";
200 sql += "max_stem_length DOUBLE DEFAULT -1.0, ";
201 sql += "min_temp_depth DOUBLE DEFAULT -1.0, ";
202 sql += "max_temp_depth DOUBLE DEFAULT -1.0, ";
203 sql += "newick LONGTEXT, ";
204 sql += "CONSTRAINT tree1_pk PRIMARY KEY (id), ";
205 sql += "CONSTRAINT tree2_fk FOREIGN KEY (model_id) REFERENCES MODELS (id) ON DELETE CASCADE, ";
206 sql += "CONSTRAINT tree6_ck CHECK (is_binary >= 0 AND is_binary <= 1), ";
207 sql += "CONSTRAINT tree7_ck CHECK (is_ultrametric >= 0 AND is_ultrametric <= 1) ) ";
208 } else {
209 return "";
210 }
211 return sql;
212
213 /*
214 } else if (table.equals("SPECIES")) {
215 if (isOracle()) { // ORACLE
216 sql += "( id VARCHAR2(100), ";
217 sql += "notes VARCHAR2(4000), ";
218 sql += "trees VARCHAR2(4000), ";
219 sql += "is_leaf NUMBER(1,0) DEFAULT 0 CONSTRAINT species4_ck CHECK(is_leaf >= 0 AND is_leaf <= 1), ";
220 sql += "CONSTRAINT species1_pk PRIMARY KEY (id) ) ";
221 } else if (isMySQL()) { // MySQL
222 sql += "( id VARCHAR(100), ";
223 sql += "notes VARCHAR(4000), ";
224 sql += "trees VARCHAR(4000), ";
225 sql += "is_leaf TINYINT DEFAULT 0, ";
226 sql += "CONSTRAINT species1_pk PRIMARY KEY (id) ) ";
227 } else {
228 return "";
229 }
230 return sql;
231 */
232
233 } else if (table.equals("PARTITIONS")) {
234 if (isOracle()) { // ORACLE
235 sql += "( id VARCHAR2(100), ";
236 sql += "tree_id VARCHAR2(100) CONSTRAINT part2_fk REFERENCES TREES (id) ON DELETE CASCADE, ";
237 sql += "model_id VARCHAR2(100) CONSTRAINT part3_fk REFERENCES MODELS (id), ";
238 sql += "notes VARCHAR2(4000), ";
239 sql += "length NUMBER DEFAULT 0 CONSTRAINT part5_ck CHECK(length >= 0), ";
240 sql += "CONSTRAINT part1_pk PRIMARY KEY(id) ) ";
241 } else if (isMySQL()) { // MySQL
242 sql += "( id VARCHAR(100), ";
243 sql += "tree_id VARCHAR(100), ";
244 sql += "model_id VARCHAR(100), ";
245 sql += "notes VARCHAR(4000), ";
246 sql += "length INT DEFAULT 0, ";
247 sql += "CONSTRAINT part1_pk PRIMARY KEY(id), ";
248 sql += "CONSTRAINT part2_fk FOREIGN KEY (tree_id) REFERENCES TREES (id) ON DELETE CASCADE, ";
249 sql += "CONSTRAINT part3_fk FOREIGN KEY (model_id) REFERENCES MODELS (id), ";
250 sql += "CONSTRAINT part5_ck CHECK (length >= 0) ) ";
251 } else {
252 return "";
253 }
254 return sql;
255
256 } else if (table.equals("PART_DATA")) {
257 if (isOracle()) { // ORACLE
258 sql += "( partition_id VARCHAR2(100) CONSTRAINT pd1_fk REFERENCES PARTITIONS (id) ON DELETE CASCADE, ";
259 sql += "species_id VARCHAR2(100), ";
260 sql += "model_id VARCHAR2(100) CONSTRAINT pd3_fk REFERENCES MODELS (id), ";
261 sql += "notes VARCHAR2(4000), ";
262 sql += "sequence CLOB DEFAULT empty_clob(), ";
263 sql += "structure CLOB DEFAULT empty_clob(), ";
264 sql += "CONSTRAINT pd1_pk PRIMARY KEY(partition_id, species_id) ) ";
265 } else if (isMySQL()) { // MySQL
266 sql += "( partition_id VARCHAR(100), ";
267 sql += "species_id VARCHAR(100), ";
268 sql += "model_id VARCHAR(100), ";
269 sql += "notes VARCHAR(4000), ";
270 sql += "sequence LONGTEXT, ";
271 sql += "structure LONGTEXT, ";
272 sql += "CONSTRAINT pd1_pk PRIMARY KEY (partition_id, species_id), ";
273 sql += "CONSTRAINT pd1_fk FOREIGN KEY (partition_id) REFERENCES PARTITIONS (id) ON DELETE CASCADE, ";
274 sql += "CONSTRAINT pd3_fk FOREIGN KEY (model_id) REFERENCES MODELS (id) ) ";
275 } else {
276 return "";
277 }
278 return sql;
279
280 } else if (table.equals("MODELS")) {
281 if (isOracle()) { // ORACLE
282 sql += "( id VARCHAR2(100), ";
283 sql += "notes VARCHAR2(4000), ";
284 sql += "details CLOB DEFAULT empty_clob(), ";
285 sql += "CONSTRAINT model1_pk PRIMARY KEY (id) ) ";
286 } else if (isMySQL()) { // MySQL
287 sql += "( id VARCHAR(100), ";
288 sql += "notes VARCHAR(4000), ";
289 sql += "details LONGTEXT, ";
290 sql += "CONSTRAINT model1_pk PRIMARY KEY (id) ) ";
291 } else {
292 return "";
293 }
294 return sql;
295
296 } else if (table.equals("QUERIES")) {
297 if (isOracle()) { // ORACLE
298 sql += "( id VARCHAR2(100), ";
299 sql += "tree_id VARCHAR2(100) CONSTRAINT query2_fk REFERENCES TREES (id), ";
300 sql += "notes VARCHAR2(4000), ";
301 sql += "leaf_select NUMBER(1,0) DEFAULT 0 CONSTRAINT query4_nn_ck NOT NULL ";
302 sql += " CHECK(leaf_select >= 0 AND leaf_select < 7), ";
303 sql += "num_leaves NUMBER(32,0) DEFAULT 0 CONSTRAINT query5_ck CHECK(num_leaves >= 0), ";
304 sql += "temp_depth_thresh NUMBER DEFAULT 0.0 CONSTRAINT query6_ck CHECK(temp_depth_thresh >= 0), ";
305 sql += "level_thresh NUMBER(32,0) DEFAULT 0 CONSTRAINT query7_ck CHECK(level_thresh >= 0), ";
306 sql += "leaves CLOB DEFAULT empty_clob(), ";
307 sql += "seq_select NUMBER(1,0) DEFAULT 0 CONSTRAINT query9_nn_ck NOT NULL ";
308 sql += " CHECK(seq_select >= 0 AND seq_select < 6), ";
309 sql += "num_pos NUMBER(32,0) DEFAULT 0 CONSTRAINT query10_ck CHECK(num_pos >= 0), ";
310 sql += "partitions CLOB DEFAULT empty_clob(), ";
311 sql += "positions CLOB DEFAULT empty_clob(), ";
312 sql += "seed NUMBER(32,0) DEFAULT -1 CONSTRAINT query13_ck CHECK(seed >= -1), ";
313 sql += "CONSTRAINT query1_pk PRIMARY KEY (id) ) ";
314 } else if (isMySQL()) { // MySQL
315 sql += "( id VARCHAR(100), ";
316 sql += "tree_id VARCHAR(100), ";
317 sql += "notes VARCHAR(4000), ";
318 sql += "leaf_select TINYINT NOT NULL DEFAULT 0, ";
319 sql += "num_leaves INT DEFAULT 0, ";
320 sql += "temp_depth_thresh DOUBLE DEFAULT 0.0, ";
321 sql += "level_thresh INT DEFAULT 0, ";
322 sql += "leaves LONGTEXT, ";
323 sql += "seq_select TINYINT NOT NULL DEFAULT 0, ";
324 sql += "num_pos INT DEFAULT 0, ";
325 sql += "partitions LONGTEXT, ";
326 sql += "positions LONGTEXT, ";
327 sql += "seed BIGINT DEFAULT -1, ";
328 sql += "CONSTRAINT query1_pk PRIMARY KEY (id), ";
329 sql += "CONSTRAINT query2_fk FOREIGN KEY (tree_id) REFERENCES TREES (id), ";
330 sql += "CONSTRAINT query4_nn_ck CHECK (leaf_select >= 0 AND leaf_select < 7), ";
331 sql += "CONSTRAINT query5_ck CHECK (num_leaves >= 0), ";
332 sql += "CONSTRAINT query6_ck CHECK (temp_depth_thresh >= 0), ";
333 sql += "CONSTRAINT query7_ck CHECK (level_thresh >= 0), ";
334 sql += "CONSTRAINT query9_nn_ck CHECK (seq_select >= 0 AND seq_select < 5), ";
335 sql += "CONSTRAINT query10_ck CHECK (num_pos >= 0), ";
336 sql += "CONSTRAINT query13_ck CHECK (seed >= -1) ) ";
337 } else {
338 return "";
339 }
340 return sql;
341 }
342 return "";
343 }
344
345 } // SQL.java