1 21 22 package org.opensubsystems.core.persist.db.driver.postgresql; 23 24 import java.sql.CallableStatement ; 25 import java.sql.Connection ; 26 import java.sql.PreparedStatement ; 27 import java.sql.ResultSet ; 28 import java.sql.SQLException ; 29 import java.sql.Statement ; 30 import java.sql.Types ; 31 import java.util.logging.Level ; 32 import java.util.logging.Logger ; 33 34 import org.opensubsystems.core.error.OSSException; 35 import org.opensubsystems.core.persist.db.driver.DatabaseTestSchema; 36 import org.opensubsystems.core.util.DatabaseUtils; 37 import org.opensubsystems.core.util.Log; 38 39 49 public class PostgreSQLDatabaseTestSchema extends DatabaseTestSchema 50 { 51 115 116 118 121 private static Logger s_logger = Log.getInstance(PostgreSQLDatabaseTestSchema.class); 122 123 125 130 public PostgreSQLDatabaseTestSchema( 131 ) throws OSSException 132 { 133 super(); 134 } 135 136 138 141 public void create( 142 Connection cntDBConnection, 143 String strUserName 144 ) throws SQLException 145 { 146 super.create(cntDBConnection, strUserName); 148 149 Statement stmQuery = null; 151 try 152 { 153 stmQuery = cntDBConnection.createStatement(); 154 155 if (stmQuery.execute("CREATE SEQUENCE generatedkey_test_seq " + 156 "INCREMENT BY 1 START WITH 1 NO CYCLE")) 157 { 158 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 160 } 161 s_logger.log(Level.FINEST, "Sequence GENERATEDKEY_TEST_SEQ created."); 162 172 173 175 if (stmQuery.execute("CREATE TYPE type_key_insertcount AS (key INTEGER, " + 176 "insert_count INTEGER)")) 177 { 178 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 180 } 181 s_logger.log(Level.FINEST, "Type TYPE_KEY_INSERTCOUNT created."); 182 192 193 195 if (stmQuery.execute("CREATE TABLE GENERATEDKEY_TEST" + NL + 196 "(" + NL + 197 " TEST_KEY INTEGER NOT NULL PRIMARY KEY," + NL + 198 " TEST_VALUE VARCHAR(50) NOT NULL" + NL + 199 ")")) 200 { 201 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 203 } 204 s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created."); 205 215 216 218 if (stmQuery.execute("CREATE OR REPLACE FUNCTION insert_generatedkey_test " + NL + 219 "(" + NL + 220 " VARCHAR(50)" + NL + 221 ")" + NL + 222 "RETURNS type_key_insertcount AS ' " + NL + 223 "DECLARE " + NL + 224 "in_value ALIAS FOR $1; " + NL + 225 "out_key INTEGER; " + NL + 226 "insert_count INTEGER; " + NL + 227 "output_result type_key_insertcount; " + NL + 228 "BEGIN " + NL + 229 " out_key := NEXTVAL(''generatedkey_test_seq''); " + NL + 230 " INSERT INTO generatedkey_test(test_key, test_value) " + NL + 231 " VALUES (out_key, in_value); " + NL + 232 " GET DIAGNOSTICS insert_count = ROW_COUNT; " + NL + 233 " output_result.key := out_key; " + NL + 234 " output_result.insert_count := insert_count; " + NL + 235 " RETURN output_result; " + NL + 236 "END " + NL + 237 "' LANGUAGE 'plpgsql';")) 238 { 239 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 241 } 242 s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created."); 243 253 254 256 if (stmQuery.execute("CREATE OR REPLACE FUNCTION insert_row_count_test " + NL + 257 "(" + NL + 258 " VARCHAR(50)" + NL + 259 ")" + NL + 260 "RETURNS INTEGER AS ' " + NL + 261 "DECLARE " + NL + 262 " in_value ALIAS FOR $1; " + NL + 263 " out_rows INTEGER; " + NL + 264 " insert_count INTEGER; " + NL + 265 "BEGIN " + NL + 266 " INSERT INTO generatedkey_test(test_key, test_value) " + NL + 267 " VALUES (NEXTVAL(''generatedkey_test_seq''), in_value); " + NL + 268 " GET DIAGNOSTICS out_rows = ROW_COUNT; " + NL + 269 " RETURN out_rows; " + NL + 270 "END " + NL + 271 "' LANGUAGE 'plpgsql';")) 272 { 273 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 275 } 276 s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created."); 277 287 288 290 if (stmQuery.execute("CREATE OR REPLACE FUNCTION update_transaction_test_value" + NL + 291 "(" + NL + 292 " VARCHAR(50), " + NL + 293 " VARCHAR(50)" + NL + 294 ") " + NL + 295 "RETURNS INTEGER AS ' " + NL + 296 "DECLARE " + NL + 297 " old_test_value ALIAS FOR $1; " + NL + 298 " new_test_value ALIAS FOR $2; " + NL + 299 " updated_count INTEGER; " + NL + 300 "BEGIN " + NL + 301 " UPDATE transaction_test SET " + NL + 302 " test_value = new_test_value " + NL + 303 " WHERE test_value = old_test_value; " + NL + 304 " GET DIAGNOSTICS updated_count = ROW_COUNT; " + NL + 305 " RETURN updated_count; " + NL + 306 "END " + NL + 307 "' LANGUAGE 'plpgsql';")) 308 { 309 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 311 } 312 s_logger.log(Level.FINEST, "Procedure UPDATE_TRANSACTION_TEST_VALUE created."); 313 323 } 324 catch (SQLException sqleExc) 325 { 326 s_logger.log(Level.WARNING, "Failed to create database test schema.", 327 sqleExc); 328 throw sqleExc; 329 } 330 finally 331 { 332 DatabaseUtils.closeStatement(stmQuery); 333 } 334 } 335 336 339 public String getInsertGeneratedKey( 340 ) 341 { 342 return "INSERT INTO generatedkey_test(test_key, test_value) " + 343 "VALUES (NEXTVAL('generatedkey_test_seq'), ?)"; 344 } 345 346 349 public int[] executeInsertGeneratedKey2( 350 Connection dbConnection, 351 String strValue 352 ) throws SQLException 353 { 354 355 PreparedStatement insertStatement = null; 356 ResultSet rsResults = null; 358 int iInsertCount = 0; 359 int iGeneratedKey = 0; 360 int[] returnValues = null; 361 362 try 363 { 364 insertStatement = dbConnection.prepareStatement( 365 "SELECT * FROM insert_generatedkey_test(?)"); 366 insertStatement.setString(1, strValue); 367 rsResults = insertStatement.executeQuery(); 368 369 if (rsResults.next()) 370 { 371 iGeneratedKey = rsResults.getInt(1); 372 iInsertCount = rsResults.getInt(2); 373 } 374 375 returnValues = new int[2]; 376 returnValues[0] = iInsertCount; 377 returnValues[1] = iGeneratedKey; 378 } 379 finally 380 { 381 DatabaseUtils.closeResultSetAndStatement(rsResults, insertStatement); 382 } 383 384 return returnValues; 385 } 386 387 390 public int executeUpdateTestValue( 391 Connection dbConnection, 392 String strOldValue, 393 String strNewValue 394 ) throws SQLException 395 { 396 CallableStatement updateStatement = null; 397 int iUpdateCount = 0; 398 399 try 400 { 401 updateStatement = dbConnection.prepareCall( 402 " { ? = call UPDATE_TRANSACTION_TEST_VALUE (?, ?) } "); 403 updateStatement.registerOutParameter(1, Types.INTEGER); 404 updateStatement.setString(2, strOldValue); 405 updateStatement.setString(3, strNewValue); 406 407 updateStatement.execute(); 408 iUpdateCount = updateStatement.getInt(1); 409 } 410 finally 411 { 412 DatabaseUtils.closeStatement(updateStatement); 413 } 414 415 return iUpdateCount; 416 } 417 418 421 public int[] executeInsertRow( 422 Connection dbConnection, 423 String strValue) 424 throws SQLException 425 { 426 CallableStatement insertStatement = null; 427 int iInsertCount = 0; 428 int iInsertCountReturnedFromSP = 0; 429 int[] returnValues = null; 430 431 try 432 { 433 insertStatement = dbConnection.prepareCall( 434 " { ? = call INSERT_ROW_COUNT_TEST (?) } "); 435 insertStatement.registerOutParameter(1, Types.INTEGER); 436 insertStatement.setString(2, strValue); 437 438 insertStatement.execute(); 441 iInsertCountReturnedFromSP = insertStatement.getInt(1); 442 443 returnValues = new int[2]; 444 445 returnValues[0] = iInsertCount; 448 449 returnValues[1] = iInsertCountReturnedFromSP; 451 } 452 finally 453 { 454 DatabaseUtils.closeStatement(insertStatement); 455 } 456 457 return returnValues; 458 } 459 460 463 public void createTestUser( 464 Connection cntAdminDBConnection, 465 String strDatabaseURL, 466 String strUserName, 467 String strUserPassword 468 ) throws SQLException 469 { 470 Statement stmQuery = null; 471 try 472 { 473 String strCreateUserQuery = "CREATE USER " + strUserName + " WITH PASSWORD '" 474 + strUserPassword + "'"; 475 476 stmQuery = cntAdminDBConnection.createStatement(); 477 478 if (stmQuery.execute(strCreateUserQuery)) 479 { 480 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 482 } 483 } 484 finally 485 { 486 DatabaseUtils.closeStatement(stmQuery); 487 } 488 } 489 490 493 public void dropTestUser( 494 Connection cntAdminDBConnection, 495 String strDatabaseURL, 496 String strUserName 497 ) throws SQLException 498 { 499 Statement stmQuery = null; 500 try 501 { 502 String strDropUserQuery = "DROP USER " + strUserName; 503 504 stmQuery = cntAdminDBConnection.createStatement(); 505 506 if (stmQuery.execute(strDropUserQuery)) 507 { 508 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 510 } 511 } 512 finally 513 { 514 DatabaseUtils.closeStatement(stmQuery); 515 } 516 } 517 } 518 | Popular Tags |