| 1 | /** |
| 2 | * Copyright 2007 ATG DUST Project |
| 3 | * |
| 4 | * Licensed under the Apache License, Version 2.0 (the "License"); |
| 5 | * you may not use this file except in compliance with the License. |
| 6 | * |
| 7 | * You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 |
| 8 | * |
| 9 | * Unless required by applicable law or agreed to in writing, software |
| 10 | * distributed under the License is distributed on an "AS IS" BASIS, |
| 11 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| 12 | * See the License for the specific language governing permissions and limitations under the License. |
| 13 | */ |
| 14 | |
| 15 | package atg.test.util; |
| 16 | |
| 17 | import java.io.File; |
| 18 | import java.io.IOException; |
| 19 | import java.sql.Connection; |
| 20 | import java.sql.DriverManager; |
| 21 | import java.sql.ResultSet; |
| 22 | import java.sql.ResultSetMetaData; |
| 23 | import java.sql.SQLException; |
| 24 | import java.sql.Statement; |
| 25 | import java.util.Collection; |
| 26 | import java.util.Iterator; |
| 27 | import java.util.Properties; |
| 28 | |
| 29 | import org.apache.log4j.Logger; |
| 30 | |
| 31 | import atg.adapter.gsa.GSATestUtils; |
| 32 | import atg.adapter.gsa.SQLFileParser; |
| 33 | import atg.core.util.StringUtils; |
| 34 | |
| 35 | /** |
| 36 | * Utility code for getting a connection to a database. |
| 37 | * The most common method is getHSQLDBInMemoryDBConnection. |
| 38 | * This returns a connection to an in-memory HSQL database. |
| 39 | * @author adamb |
| 40 | * |
| 41 | */ |
| 42 | public class DBUtils { |
| 43 | |
| 44 | public Connection conn; //our connnection to the db - presist for life of |
| 45 | private Properties mJDBCProperties; |
| 46 | |
| 47 | private static Logger log = Logger.getLogger(DBUtils.class); |
| 48 | // --------------------------- |
| 49 | /** |
| 50 | * Returns a Properties object preconfigured to create |
| 51 | * an HSQLDB in memory database connecting with user "sa" |
| 52 | * password "" |
| 53 | * @param pTestDBName |
| 54 | */ |
| 55 | public static Properties getHSQLDBInMemoryDBConnection(String pTestDBName) { |
| 56 | Properties props = new Properties(); |
| 57 | props.put("driver", "org.hsqldb.jdbcDriver"); |
| 58 | if(pTestDBName != null) |
| 59 | props.put("URL", "jdbc:hsqldb:mem:" + pTestDBName); |
| 60 | else |
| 61 | props.put("URL", "jdbc:hsqldb:."); |
| 62 | props.put("user", "sa"); |
| 63 | props.put("password", ""); |
| 64 | return props; |
| 65 | } |
| 66 | |
| 67 | |
| 68 | /** |
| 69 | * Returns a Properties object preconfigured to create |
| 70 | * an HSQLDB in memory database connecting with user "sa" |
| 71 | * password "" |
| 72 | * @param pTestDBName |
| 73 | */ |
| 74 | public static Properties getHSQLDBRegularDBConnection(String pTestDBName, String pHostName, Object pUser, Object pPassword) { |
| 75 | Properties props = new Properties(); |
| 76 | props.put("driver", "org.hsqldb.jdbcDriver"); |
| 77 | props.put("URL", "jdbc:hsqldb:hsql://" + pHostName+ "/"+pTestDBName); |
| 78 | props.put("user", pUser); |
| 79 | props.put("password", pPassword); |
| 80 | return props; |
| 81 | } |
| 82 | |
| 83 | /** |
| 84 | * Returns a Properties object preconfigured to create |
| 85 | * an HSQLDB in memory database connecting with user "sa" |
| 86 | * password "" |
| 87 | * @param pTestDBName |
| 88 | */ |
| 89 | public static Properties getHSQLDBFileDBConnection(String pPath) { |
| 90 | Properties props = new Properties(); |
| 91 | props.put("driver", "org.hsqldb.jdbcDriver"); |
| 92 | props.put("URL", "jdbc:hsqldb:file:" + pPath); |
| 93 | props.put("user", "sa"); |
| 94 | props.put("password", ""); |
| 95 | return props; |
| 96 | } |
| 97 | |
| 98 | // --------------------------- |
| 99 | /** |
| 100 | * Returns connection properties for MSSQL |
| 101 | * @param pHostName host name of db server |
| 102 | * @param pPort port number of db |
| 103 | * @param pDBName database name |
| 104 | * @param pUser database username |
| 105 | * @param pPassword database user's password |
| 106 | * @return |
| 107 | */ |
| 108 | |
| 109 | public static Properties getMSSQLDBConnection(String pHostName, String pPort, String pDBName, String pUser, String pPassword) { |
| 110 | Properties props = new Properties(); |
| 111 | props.put("driver", "com.inet.tds.TdsDriver"); |
| 112 | props.put("URL", "jdbc:inetdae:" + pHostName + ":" + pPort + "?database=" + pDBName); |
| 113 | props.put("user", pUser); |
| 114 | props.put("password", pPassword); |
| 115 | return props; |
| 116 | } |
| 117 | |
| 118 | // --------------------------- |
| 119 | /** |
| 120 | * Returns connection properties for mysql |
| 121 | * @param pHostName host name of db server |
| 122 | * @param pPort port number of db |
| 123 | * @param pDBName database name |
| 124 | * @param pUser database username |
| 125 | * @param pPassword database user's password |
| 126 | * @return |
| 127 | */ |
| 128 | |
| 129 | public static Properties getMySQLDBConnection(String pHostName, String pPort, String pDBName, String pUser, String pPassword) { |
| 130 | if(pPort == null)pPort = "3306"; |
| 131 | Properties props = new Properties(); |
| 132 | props.put("driver", "com.mysql.jdbc.Driver"); |
| 133 | props.put("URL", "jdbc:mysql://" + pHostName + ":" + pPort + "/" + pDBName); |
| 134 | props.put("user", pUser); |
| 135 | props.put("password", pPassword); |
| 136 | return props; |
| 137 | } |
| 138 | |
| 139 | /** |
| 140 | * @param pString |
| 141 | * @param pString2 |
| 142 | * @param pString3 |
| 143 | * @param pString4 |
| 144 | * @param pString5 |
| 145 | * @return |
| 146 | */ |
| 147 | public static Properties getDB2DBConnection(String pHostName, String pPort, String pDBName, String pUser, String pPassword) { |
| 148 | Properties props = new Properties(); |
| 149 | props.put("driver", "com.ibm.db2.jcc.DB2Driver"); |
| 150 | // props.put("driver", "COM.ibm.db2.jdbc.app.DB2Drive"); |
| 151 | props.put("URL", "jdbc:db2://" + pHostName + ":" + pPort + "/" + pDBName); |
| 152 | props.put("user", pUser); |
| 153 | props.put("password", pPassword); |
| 154 | return props; |
| 155 | } |
| 156 | // --------------------------- |
| 157 | /** |
| 158 | * Returns connection properties for MSSQL |
| 159 | * @param pHostName host name of db server |
| 160 | * @param pPort port number of db |
| 161 | * @param pDBName database name |
| 162 | * @param pUser database username |
| 163 | * @param pPassword database user's password |
| 164 | * @return |
| 165 | */ |
| 166 | |
| 167 | public static Properties getOracleDBConnection(String pHostName, String pPort, String pDBName, String pUser, String pPassword) { |
| 168 | Properties props = new Properties(); |
| 169 | props = new Properties(); |
| 170 | String port = pPort; |
| 171 | if(pPort == null) |
| 172 | port = "1521"; |
| 173 | props.put("driver", "oracle.jdbc.OracleDriver"); |
| 174 | props.put("URL", "jdbc:oracle:thin:@"+pHostName+":"+port+":"+pDBName); |
| 175 | props.put("user", pUser); |
| 176 | props.put("password", pPassword); |
| 177 | return props; |
| 178 | } |
| 179 | // --------------------------- |
| 180 | /** |
| 181 | * Returns connection properties for MSSQL |
| 182 | * @param pHostName host name of db server |
| 183 | * @param pPort port number of db |
| 184 | * @param pDBName database name |
| 185 | * @param pUser database username |
| 186 | * @param pPassword database user's password |
| 187 | * @return |
| 188 | */ |
| 189 | |
| 190 | public static Properties getSolidDBConnection(String pHostName, String pPort, String pUser, String pPassword) { |
| 191 | Properties props = new Properties(); |
| 192 | props = new Properties(); |
| 193 | String port = pPort; |
| 194 | if(pPort == null) |
| 195 | port = "1313"; |
| 196 | props.put("driver", "solid.jdbc.SolidDriver"); |
| 197 | props.put("URL", "jdbc:solid://"+pHostName+":"+port); |
| 198 | props.put("user", pUser); |
| 199 | props.put("password", pPassword); |
| 200 | return props; |
| 201 | } |
| 202 | |
| 203 | // --------------------------- |
| 204 | /** |
| 205 | * Returns connection properties for MSSQL |
| 206 | * @param pHostName host name of db server |
| 207 | * @param pPort port number of db |
| 208 | * @param pDBName database name |
| 209 | * @param pUser database username |
| 210 | * @param pPassword database user's password |
| 211 | * @return |
| 212 | */ |
| 213 | |
| 214 | public static Properties getSybaseDBConnection(String pHostName, String pPort, String pDBName, String pUser, String pPassword) { |
| 215 | Properties props = new Properties(); |
| 216 | props = new Properties(); |
| 217 | String port = pPort; |
| 218 | if(pPort == null) |
| 219 | port = "5000"; |
| 220 | props.put("driver", "com.sybase.jdbc2.jdbc.SybDriver"); |
| 221 | props.put("URL", " jdbc:sybase:Tds:"+pHostName+":"+port+"/"+pDBName); |
| 222 | props.put("user", pUser); |
| 223 | props.put("password", pPassword); |
| 224 | return props; |
| 225 | } |
| 226 | |
| 227 | /** |
| 228 | * Returns a Properties object preconfigured to create |
| 229 | * an HSQLDB in memory database connecting with user "sa" |
| 230 | * password "" |
| 231 | * @param pTestDBName |
| 232 | */ |
| 233 | public static Properties getHSQLDBInMemoryDBConnection() { |
| 234 | return getHSQLDBInMemoryDBConnection("testdb"); |
| 235 | } |
| 236 | |
| 237 | // --------------------------- |
| 238 | /** |
| 239 | * Creates a new DBUtils given a Properties object containing connection info |
| 240 | * Expected keys: |
| 241 | * URL<BR> |
| 242 | * driver<BR> |
| 243 | * user<BR> |
| 244 | * password<BR> |
| 245 | * <BR> |
| 246 | * @param pProps |
| 247 | * @throws Exception |
| 248 | */ |
| 249 | public DBUtils(Properties pProps) throws Exception { |
| 250 | this(pProps.getProperty("URL"),pProps.getProperty("driver"),pProps.getProperty("user"),pProps.getProperty("password")); |
| 251 | } |
| 252 | public String mDatabaseType = null; |
| 253 | private String mDatabaseVersion; |
| 254 | // --------------------------- |
| 255 | public DBUtils(String pURL, String pJDBCDriver, |
| 256 | String pUser, String pPassword) throws Exception { |
| 257 | |
| 258 | mJDBCProperties = new Properties(); |
| 259 | mJDBCProperties.put("driver", pJDBCDriver); |
| 260 | mJDBCProperties.put("URL", pURL); |
| 261 | mJDBCProperties.put("user", pUser); |
| 262 | mJDBCProperties.put("password",pPassword); |
| 263 | |
| 264 | // general |
| 265 | // exception |
| 266 | |
| 267 | // Load the HSQL Database Engine JDBC driver |
| 268 | // hsqldb.jar should be in the class path or made part of the current jar |
| 269 | Class.forName(pJDBCDriver); |
| 270 | |
| 271 | |
| 272 | // connect to the database. This will load the db files and start the |
| 273 | // database if it is not alread running. |
| 274 | // db_file_name_prefix is used to open or create files that hold the state |
| 275 | // of the db. |
| 276 | // It can contain directory names relative to the |
| 277 | // current working directory |
| 278 | conn = DriverManager.getConnection(pURL, // filenames |
| 279 | pUser, // username |
| 280 | pPassword); // password |
| 281 | mDatabaseType = conn.getMetaData().getDatabaseProductName(); |
| 282 | mDatabaseVersion = conn.getMetaData().getDatabaseProductVersion(); |
| 283 | log.info("Connected to " |
| 284 | + mDatabaseType + " Version: "+ mDatabaseVersion); |
| 285 | executeCreateIdGenerator(); |
| 286 | } |
| 287 | |
| 288 | public void shutdown() throws SQLException { |
| 289 | if(!conn.isClosed()){ |
| 290 | Statement st = conn.createStatement(); |
| 291 | |
| 292 | // db writes out to files and performs clean shuts down |
| 293 | // otherwise there will be an unclean shutdown |
| 294 | // when program ends |
| 295 | if (conn.getMetaData().getDatabaseProductName().startsWith("HSQL")) |
| 296 | st.execute("SHUTDOWN"); |
| 297 | conn.close(); // if there are no other open connection |
| 298 | } |
| 299 | } |
| 300 | |
| 301 | public int getRowCount(String pTable) throws SQLException { |
| 302 | Statement st = null; |
| 303 | ResultSet rs = null; |
| 304 | try { |
| 305 | st = conn.createStatement(); // statement objects can be reused with |
| 306 | |
| 307 | // repeated calls to execute but we |
| 308 | // choose to make a new one each time |
| 309 | rs = st.executeQuery("SELECT COUNT(*) FROM " + pTable); // run the query |
| 310 | |
| 311 | rs.next(); |
| 312 | int count = rs.getInt(1); |
| 313 | return count; |
| 314 | } |
| 315 | finally { |
| 316 | st.close(); // NOTE!! if you close a statement the associated ResultSet is |
| 317 | } |
| 318 | |
| 319 | } |
| 320 | |
| 321 | //use for SQL command SELECT |
| 322 | public synchronized void query(String expression) throws SQLException { |
| 323 | |
| 324 | Statement st = null; |
| 325 | ResultSet rs = null; |
| 326 | |
| 327 | st = conn.createStatement(); // statement objects can be reused with |
| 328 | |
| 329 | // repeated calls to execute but we |
| 330 | // choose to make a new one each time |
| 331 | rs = st.executeQuery(expression); // run the query |
| 332 | |
| 333 | // do something with the result set. |
| 334 | dump(rs); |
| 335 | st.close(); // NOTE!! if you close a statement the associated ResultSet is |
| 336 | |
| 337 | // closed too |
| 338 | // so you should copy the contents to some other object. |
| 339 | // the result set is invalidated also if you recycle an Statement |
| 340 | // and try to execute some other query before the result set has been |
| 341 | // completely examined. |
| 342 | } |
| 343 | |
| 344 | //use for SQL commands CREATE, DROP, INSERT and UPDATE |
| 345 | public synchronized void update(String expression) throws SQLException { |
| 346 | //log.info("DBUtils.update : " + expression); |
| 347 | Statement st = null; |
| 348 | |
| 349 | st = conn.createStatement(); // statements |
| 350 | |
| 351 | int i = st.executeUpdate(expression); // run the query |
| 352 | |
| 353 | if (i == -1) { |
| 354 | log.info("db error : " + expression); |
| 355 | } |
| 356 | |
| 357 | st.close(); |
| 358 | } // void update() |
| 359 | |
| 360 | public void dump(ResultSet rs) throws SQLException { |
| 361 | |
| 362 | // the order of the rows in a cursor |
| 363 | // are implementation dependent unless you use the SQL ORDER statement |
| 364 | ResultSetMetaData meta = rs.getMetaData(); |
| 365 | int colmax = meta.getColumnCount(); |
| 366 | int i; |
| 367 | Object o = null; |
| 368 | |
| 369 | // the result set is a cursor into the data. You can only |
| 370 | // point to one row at a time |
| 371 | // assume we are pointing to BEFORE the first row |
| 372 | // rs.next() points to next row and returns true |
| 373 | // or false if there is no next row, which breaks the loop |
| 374 | for (; rs.next();) { |
| 375 | for (i = 0; i < colmax; ++i) { |
| 376 | o = rs.getObject(i + 1); // Is SQL the first column is indexed |
| 377 | |
| 378 | // with 1 not 0 |
| 379 | System.out.print(o.toString() + " "); |
| 380 | } |
| 381 | |
| 382 | log.info(" "); |
| 383 | } |
| 384 | } //void dump( ResultSet rs ) |
| 385 | |
| 386 | /** |
| 387 | * @param db |
| 388 | * @throws SQLException |
| 389 | */ |
| 390 | public void executeCreateIdGenerator() throws SQLException { |
| 391 | try { |
| 392 | if(!isDB2()) |
| 393 | update(" create table das_id_generator (id_space_name varchar(60) not null," |
| 394 | + "seed numeric(19,0) not null, batch_size integer not null, prefix varchar(10) null," |
| 395 | + " suffix varchar(10) null, primary key (id_space_name)) "); |
| 396 | else |
| 397 | update(" create table das_id_generator (id_space_name varchar(60) not null," |
| 398 | + "seed numeric(19,0) not null, batch_size numeric(19) not null, prefix varchar(10) default null," |
| 399 | + " suffix varchar(10) default null, primary key (id_space_name)) "); |
| 400 | } catch (SQLException e) { |
| 401 | // drop and try again |
| 402 | log.info("DROPPING DAS_ID_GENERATOR"); |
| 403 | try { |
| 404 | update("drop table das_id_generator"); |
| 405 | } catch (SQLException ex) { |
| 406 | |
| 407 | } |
| 408 | if(!isDB2()) |
| 409 | update(" create table das_id_generator (id_space_name varchar(60) not null," |
| 410 | + "seed numeric(19,0) not null, batch_size integer not null, prefix varchar(10) null," |
| 411 | + " suffix varchar(10) null, primary key (id_space_name)) "); |
| 412 | else |
| 413 | update(" create table das_id_generator (id_space_name varchar(60) not null," |
| 414 | + "seed numeric(19,0) not null, batch_size numeric(19) not null, prefix varchar(10) default null," |
| 415 | + " suffix varchar(10) default null, primary key (id_space_name)) "); |
| 416 | |
| 417 | } |
| 418 | } |
| 419 | |
| 420 | public void executeSQLFile(File pFile) { |
| 421 | log.info("Attemping to execute " + pFile); |
| 422 | SQLFileParser parser = new SQLFileParser(); |
| 423 | Collection<String> c = parser.parseSQLFile(pFile.getAbsolutePath()); |
| 424 | Iterator<String> cmds = c.iterator(); |
| 425 | while (cmds.hasNext()) { |
| 426 | String cmd = cmds.next(); |
| 427 | try { |
| 428 | if ("Oracle".equals(mDatabaseType)) { |
| 429 | cmd = StringUtils.replace(cmd, "numeric", "NUMBER"); |
| 430 | cmd = StringUtils.replace(cmd, "varchar ", "VARCHAR2 "); |
| 431 | cmd = StringUtils.replace(cmd, "varchar(", "VARCHAR2("); |
| 432 | cmd = StringUtils.replace(cmd, "binary", "RAW (250)"); |
| 433 | } |
| 434 | log.info("Executing " + cmd); |
| 435 | update(cmd); |
| 436 | } |
| 437 | catch (SQLException e) { |
| 438 | log.info(e.getMessage()); |
| 439 | } |
| 440 | } |
| 441 | } |
| 442 | |
| 443 | public File createFakeXADataSource(File pRoot) throws IOException{ |
| 444 | return GSATestUtils.createFakeXADataSource(pRoot, mJDBCProperties, null); |
| 445 | } |
| 446 | |
| 447 | public File createFakeXADataSource(File pRoot, String pName) throws IOException{ |
| 448 | |
| 449 | return GSATestUtils.createFakeXADataSource(pRoot, mJDBCProperties, pName); |
| 450 | |
| 451 | } |
| 452 | |
| 453 | // --------------------------------- |
| 454 | /** |
| 455 | * @param pRoot |
| 456 | * @throws IOException |
| 457 | */ |
| 458 | public static File createJTDataSource(File pRoot) throws IOException { |
| 459 | return GSATestUtils.createJTDataSource(pRoot, null,null); |
| 460 | } |
| 461 | |
| 462 | // ------------------------------------ |
| 463 | /** |
| 464 | * Creates a new JTDataSource component. The name of the component may |
| 465 | * be specified by passing in a non null value for pName. |
| 466 | * Also the name of the FakeXADataSource may be specified by passing in a non null name. |
| 467 | * Otherwise the defaults are JTDataSource and FakeXADataSource. |
| 468 | * |
| 469 | * @param pRoot |
| 470 | * @param pName |
| 471 | * @param pFakeXAName |
| 472 | * @return |
| 473 | * @throws IOException |
| 474 | */ |
| 475 | public static File createJTDataSource(File pRoot, String pName, String pFakeXAName) |
| 476 | throws IOException { |
| 477 | return GSATestUtils.createJTDataSource(pRoot, pName, pFakeXAName); |
| 478 | } |
| 479 | |
| 480 | /** |
| 481 | * @param pProps |
| 482 | * @return |
| 483 | */ |
| 484 | public static boolean isOracle(Properties pProps) { |
| 485 | return pProps.get("driver").toString().toLowerCase().indexOf("oracle") != -1; |
| 486 | } |
| 487 | |
| 488 | /** |
| 489 | * @param pProps |
| 490 | * @return |
| 491 | */ |
| 492 | public static boolean isSybase(Properties pProps) { |
| 493 | return pProps.get("driver").toString().toLowerCase().indexOf("sybase") != -1; |
| 494 | } |
| 495 | |
| 496 | /** |
| 497 | * @param pProps |
| 498 | * @return |
| 499 | */ |
| 500 | public static boolean isMSSQLServer(Properties pProps) { |
| 501 | return pProps.get("driver").equals( "com.inet.tds.TdsDriver"); |
| 502 | } |
| 503 | |
| 504 | /** |
| 505 | * @param pProps |
| 506 | * @return |
| 507 | */ |
| 508 | public static boolean isDB2(Properties pProps) { |
| 509 | return pProps.get("driver").toString().indexOf("DB2") != -1; |
| 510 | } |
| 511 | |
| 512 | |
| 513 | /** |
| 514 | * @param pProps |
| 515 | * @return |
| 516 | */ |
| 517 | public boolean isOracle() { |
| 518 | return DBUtils.isMSSQLServer(mJDBCProperties); |
| 519 | } |
| 520 | |
| 521 | /** |
| 522 | * @param pProps |
| 523 | * @return |
| 524 | */ |
| 525 | public boolean isSybase() { |
| 526 | return DBUtils.isMSSQLServer(mJDBCProperties); |
| 527 | } |
| 528 | |
| 529 | /** |
| 530 | * @param pProps |
| 531 | * @return |
| 532 | */ |
| 533 | public boolean isMSSQLServer() { |
| 534 | return DBUtils.isMSSQLServer(mJDBCProperties); |
| 535 | } |
| 536 | |
| 537 | /** |
| 538 | * @param pProps |
| 539 | * @return |
| 540 | */ |
| 541 | public boolean isDB2() { |
| 542 | return DBUtils.isDB2(mJDBCProperties); |
| 543 | } |
| 544 | } |