1 21 22 package org.opensubsystems.core.persist.db.driver.sybase; 23 24 import java.sql.CallableStatement ; 25 import java.sql.Connection ; 26 import java.sql.SQLException ; 27 import java.sql.Statement ; 28 import java.sql.Types ; 29 import java.util.logging.Level ; 30 import java.util.logging.Logger ; 31 32 import org.opensubsystems.core.error.OSSException; 33 import org.opensubsystems.core.persist.db.driver.DatabaseTestSchema; 34 import org.opensubsystems.core.util.DatabaseUtils; 35 import org.opensubsystems.core.util.Log; 36 37 47 public class SybaseDatabaseTestSchema extends DatabaseTestSchema 48 { 49 226 227 229 232 private static Logger s_logger = Log.getInstance(SybaseDatabaseTestSchema.class); 233 234 236 241 public SybaseDatabaseTestSchema( 242 ) throws OSSException 243 { 244 super(); 245 } 246 247 249 252 public void create( 253 Connection cntDBConnection, 254 String strUserName 255 ) throws SQLException 256 { 257 261 Statement stmQuery = null; 263 try 264 { 265 stmQuery = cntDBConnection.createStatement(); 266 267 269 if (stmQuery.execute("CREATE TABLE GENERATEDKEY_TEST" + NL + 270 "(" + NL + 271 " TEST_KEY NUMERIC(10, 0) IDENTITY NOT NULL PRIMARY KEY," + NL + 272 " TEST_VALUE VARCHAR(50) NOT NULL" + NL + 273 ")")) 274 { 275 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 277 } 278 s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created."); 279 289 290 292 if (stmQuery.execute("CREATE TABLE RESULTSET_TEST" + NL + 293 "(" + NL + 294 " RESULTSET_TEST VARCHAR(20) NOT NULL" + NL + 295 ")")) 296 { 297 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 299 } 300 s_logger.log(Level.FINEST, "Table RESULTSET_TEST created."); 301 311 312 314 if (stmQuery.execute("CREATE TABLE DATE_TEST" + NL + 315 "(" + NL + 316 " DATE_TEST SMALLDATETIME NOT NULL" + NL + 317 ")")) 318 { 319 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 321 } 322 s_logger.log(Level.FINEST, "Table DATE_TEST created."); 323 333 334 336 if (stmQuery.execute("CREATE TABLE TIME_TEST" + NL + 337 "(" + NL + 338 " TIME_TEST SMALLDATETIME NOT NULL" + NL + 339 ")")) 340 { 341 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 343 } 344 s_logger.log(Level.FINEST, "Table TIME_TEST created."); 345 355 356 358 if (stmQuery.execute("CREATE TABLE TIMESTAMP_TEST" + NL + 359 "(" + NL + 360 " TIMESTAMP_TEST DATETIME NOT NULL" + NL + 361 ")")) 362 { 363 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 365 } 366 s_logger.log(Level.FINEST, "Table TIMESTAMP_TEST created."); 367 377 378 380 if (stmQuery.execute("CREATE TABLE TRANSACTION_TEST" + NL + 381 "(" + NL + 382 " TEST_ID INTEGER NOT NULL," + NL + 383 " TEST_VALUE VARCHAR(50) NOT NULL," + NL + 384 " CONSTRAINT TEST_ID_PK PRIMARY KEY (TEST_ID)" + NL + 385 ")")) 386 { 387 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 389 } 390 s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created."); 391 401 402 404 if (stmQuery.execute("CREATE TABLE TRANSACTION_RELATED_TEST" + NL + 405 "(" + NL + 406 " TEST_REL_ID INTEGER NOT NULL," + NL + 407 " TEST_ID INTEGER NOT NULL," + NL + 408 " TEST_VALUE VARCHAR(50)," + NL + 409 " CONSTRAINT TEST_ID_FK FOREIGN KEY (TEST_ID)" + NL + 410 " REFERENCES TRANSACTION_TEST (TEST_ID)" + NL + 411 ")")) 412 { 413 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 415 } 416 s_logger.log(Level.FINEST, "Table TRANSACTION_RELATED_TEST created."); 417 427 428 430 if (stmQuery.execute("CREATE TRIGGER TRAN_DELCASCADE_TRG " + NL + 431 "ON TRANSACTION_TEST " + NL + 432 "FOR DELETE " + NL + 433 "AS " + NL + 434 " DELETE TRANSACTION_RELATED_TEST " + NL + 435 " FROM TRANSACTION_RELATED_TEST, deleted " + NL + 436 " WHERE TRANSACTION_RELATED_TEST.TEST_ID = deleted.TEST_ID")) 437 { 438 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 440 } 441 s_logger.log(Level.FINEST, "Trigger TRAN_DELCASCADE_TRG created."); 442 452 453 455 if (stmQuery.execute("CREATE TABLE DELETE_TEST" + NL + 456 "(" + 457 " TEST_ID INTEGER NOT NULL," + NL + 458 " TEST_VALUE VARCHAR(50) NOT NULL," + NL + 459 " CONSTRAINT DTEST_ID_PK PRIMARY KEY (TEST_ID)" + NL + 460 ")")) 461 { 462 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 464 } 465 s_logger.log(Level.FINEST, "Table DELETE_TEST created."); 466 476 477 479 if (stmQuery.execute("CREATE TABLE DELETE_RELATED_TEST" + NL + 480 "(" + NL + 481 " TEST_REL_ID INTEGER NOT NULL," + NL + 482 " TEST_ID INTEGER NOT NULL," + NL + 483 " TEST_VALUE VARCHAR(50)," + NL + 484 " CONSTRAINT DTEST_ID_FK FOREIGN KEY (TEST_ID)" + NL + 485 " REFERENCES DELETE_TEST (TEST_ID)" + NL + 486 ")")) 487 { 488 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 490 } 491 s_logger.log(Level.FINEST, "Table DELETE_RELATED_TEST created."); 492 502 503 505 if (stmQuery.execute("CREATE TRIGGER DELETE_DELCASCADE_TRG " + NL + 506 "ON DELETE_TEST " + NL + 507 "FOR DELETE " + NL + 508 "AS " + NL + 509 " DELETE DELETE_RELATED_TEST " + NL + 510 " FROM DELETE_RELATED_TEST, deleted " + NL + 511 " WHERE DELETE_RELATED_TEST.TEST_ID = deleted.TEST_ID")) 512 { 513 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 515 } 516 s_logger.log(Level.FINEST, "Trigger DELETE_DELCASCADE_TRG created."); 517 527 528 530 if (stmQuery.execute("CREATE TABLE ROLLBACK_TEST" + NL + 531 "(" + NL + 532 " TEST_COLUMN VARCHAR(50) NOT NULL," + NL + 533 " CONSTRAINT TEST_COLUMN_UQ UNIQUE (TEST_COLUMN)" + NL + 534 ")")) 535 { 536 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 538 } 539 s_logger.log(Level.FINEST, "Table TRANSACTION_TEST created."); 540 550 551 553 if (stmQuery.execute("CREATE TABLE OWN_FK_TEST " + NL + 554 "( " + NL + 555 " TEST_ID INTEGER NOT NULL, " + NL + 556 " FK_ID INTEGER," + NL + 557 " CONSTRAINT OWN_FK_TEST_PK PRIMARY KEY (TEST_ID), " + NL + 558 " CONSTRAINT OWN_FK_TEST_FK FOREIGN KEY (FK_ID) " + NL + 559 " REFERENCES OWN_FK_TEST (TEST_ID)" + NL + 560 ")")) 561 { 562 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 564 } 565 s_logger.log(Level.FINEST, "Table OWN_FK_TEST created."); 566 576 577 579 if (stmQuery.execute("CREATE TABLE GROUP_BASE_TEST" + NL + 580 "(" + NL + 581 " TEST_BASE_ID INTEGER NOT NULL, " + NL + 582 " TEST_BASE_VALUE INTEGER," + NL + 583 " CONSTRAINT GROUP_BASE_TEST_PK PRIMARY KEY (TEST_BASE_ID)" + NL + 584 ")")) 585 { 586 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 588 } 589 s_logger.log(Level.FINEST, "Table GROUP_BASE_TEST created."); 590 600 601 603 if (stmQuery.execute( 604 "CREATE TABLE GROUP_CHILD_TEST" + NL + 605 "(" + NL + 606 " TEST_CHILD_ID INTEGER NOT NULL, " + NL + 607 " TEST_BASE_FK_ID INTEGER NOT NULL, " + NL + 608 " TEST_CHILD_VALUE INTEGER," + NL + 609 " CONSTRAINT GROUP_CHILD_TEST_PK PRIMARY KEY (TEST_CHILD_ID)," + NL + 610 " CONSTRAINT TEST_BASE_ID_FK FOREIGN KEY (TEST_BASE_FK_ID) " + NL + 611 " REFERENCES GROUP_BASE_TEST (TEST_BASE_ID)" + NL + 612 ")")) 613 { 614 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 616 } 617 s_logger.log(Level.FINEST, "Table GROUP_CHILD_TEST created."); 618 628 629 631 if (stmQuery.execute("CREATE TRIGGER GROUP_DELCASCADE_TRG " + NL + 632 "ON GROUP_BASE_TEST " + NL + 633 "FOR DELETE " + NL + 634 "AS " + NL + 635 " DELETE GROUP_CHILD_TEST " + NL + 636 " FROM GROUP_CHILD_TEST, deleted " + NL + 637 " WHERE GROUP_CHILD_TEST.TEST_BASE_FK_ID = deleted.TEST_BASE_ID")) 638 { 639 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 641 } 642 s_logger.log(Level.FINEST, "Trigger GROUP_DELCASCADE_TRG created."); 643 653 654 656 if (stmQuery.execute("CREATE TABLE SAME_TEST1" + NL + 657 "(" + NL + 658 " ID INTEGER NOT NULL" + NL + 659 ")")) 660 { 661 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 663 } 664 s_logger.log(Level.FINEST, "Table SAME_TEST1 created."); 665 675 676 678 if (stmQuery.execute("CREATE TABLE SAME_TEST2 " + NL + 679 "(" + NL + 680 " ID INTEGER NOT NULL" + NL + 681 ")")) 682 { 683 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 685 } 686 s_logger.log(Level.FINEST, "Table SAME_TEST2 created."); 687 697 698 700 if (stmQuery.execute("CREATE TABLE POOL_TEST " + NL + 701 "(" + NL + 702 " TEST_VALUE INTEGER NOT NULL," + NL + 703 " CONSTRAINT TEST_VALUE1_PK PRIMARY KEY (TEST_VALUE)" + NL + 704 ")")) 705 { 706 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 708 } 709 s_logger.log(Level.FINEST, "Table POOL_TEST created."); 710 720 721 723 if (stmQuery.execute("CREATE TABLE POOL_TEST2 " + NL + 724 "(" + NL + 725 " TEST_VALUE INTEGER NOT NULL," + NL + 726 " CONSTRAINT TEST_VALUE2_PK PRIMARY KEY (TEST_VALUE)" + NL + 727 ")")) 728 { 729 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 731 } 732 s_logger.log(Level.FINEST, "Table POOL_TEST2 created."); 733 743 744 746 if (stmQuery.execute("CREATE TABLE QUERY_TEST " + NL + 747 "(" + NL + 748 " VALUE_1 INTEGER NOT NULL," + NL + 749 " VALUE_2 INTEGER NOT NULL" + NL + 750 ")")) 751 { 752 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 754 } 755 s_logger.log(Level.FINEST, "Table QUERY_TEST created."); 756 766 767 769 if (stmQuery.execute("CREATE TABLE QUERY_TEST_EXCEPT " + NL + 770 "(" + NL + 771 " VALUE_1 INTEGER NOT NULL" + NL + 772 ")")) 773 { 774 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 776 } 777 s_logger.log(Level.FINEST, "Table QUERY_TEST_EXCEPT created."); 778 788 789 791 if (stmQuery.execute("CREATE TABLE UNIQUE_COLUMN_TEST " + NL + 792 "(" + NL + 793 " TEST_ID INTEGER, " + NL + 794 " CONSTRAINT TEST_ID_UQ UNIQUE (TEST_ID) " + NL + 795 ")")) 796 { 797 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 799 } 800 s_logger.log(Level.FINEST, "Table UNIQUE_COLUMN_TEST created."); 801 811 812 814 if (stmQuery.execute("CREATE TABLE NULL_COLUMN_TEST " + NL + 815 "(" + NL + 816 " NAME VARCHAR(50)" + NL + 817 ")")) 818 { 819 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 821 } 822 s_logger.log(Level.FINEST, "Table NULL_COLUMN_TEST created."); 823 833 834 836 if (stmQuery.execute("CREATE PROCEDURE INSERT_GENERATEDKEY_TEST " + NL + 837 "@IN_VALUE VARCHAR(50), " + NL + 838 "@OUT_KEY INTEGER OUTPUT " + NL + 839 "AS " + NL + 840 " INSERT INTO GENERATEDKEY_TEST(TEST_VALUE) VALUES (@IN_VALUE) " + NL + 841 " SET @OUT_KEY = @@IDENTITY " + NL + 842 " RETURN @OUT_KEY")) 843 { 844 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 846 } 847 s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created."); 848 858 859 861 if (stmQuery.execute("CREATE PROCEDURE INSERT_ROW_COUNT_TEST " + NL + 862 "@IN_VALUE VARCHAR(50), " + NL + 863 "@OUT_ROWS INTEGER OUTPUT " + NL + 864 "AS " + NL + 865 " INSERT INTO GENERATEDKEY_TEST(TEST_VALUE) VALUES (@IN_VALUE) " + NL + 866 " SET @OUT_ROWS = @@ROWCOUNT " + NL + 867 " RETURN @OUT_ROWS")) 868 { 869 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 871 } 872 s_logger.log(Level.FINEST, "Procedure INSERT_GENERATEDKEY_TEST created."); 873 883 884 886 if (stmQuery.execute("CREATE PROCEDURE UPDATE_TRANSACTION_TEST_VALUE " + NL + 887 "@IN_OLD_TEST_VALUE VARCHAR(50), " + NL + 888 "@IN_NEW_TEST_VALUE VARCHAR(50) " + NL + 889 "AS " + NL + 890 " UPDATE " + strUserName + ".TRANSACTION_TEST SET " + NL + 891 " TEST_VALUE = @IN_NEW_TEST_VALUE " + NL + 892 " WHERE TEST_VALUE = @IN_OLD_TEST_VALUE " + NL + 893 " RETURN @@ROWCOUNT")) 894 895 { 896 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 898 } 899 s_logger.log(Level.FINEST, "Procedure UPDATE_TRANSACTION_TEST_VALUE created."); 900 910 911 } 912 catch (SQLException sqleExc) 913 { 914 s_logger.log(Level.WARNING, "Failed to create database test schema.", 915 sqleExc); 916 throw sqleExc; 917 } 918 finally 919 { 920 DatabaseUtils.closeStatement(stmQuery); 921 } 922 } 923 924 927 public String getInsertGeneratedKey( 928 ) 929 { 930 return "INSERT INTO GENERATEDKEY_TEST(TEST_VALUE) " + 931 "VALUES (?)"; 932 } 933 934 937 public int[] executeInsertGeneratedKey2( 938 Connection dbConnection, 939 String strValue 940 ) throws SQLException 941 { 942 CallableStatement insertStatement = null; 943 int iInsertCount = 0; 944 int iGeneratedKey = 0; 945 int[] returnValues = null; 946 947 try 948 { 949 insertStatement = dbConnection.prepareCall( 950 "EXEC INSERT_GENERATEDKEY_TEST ?, ?"); 951 insertStatement.setString(1, strValue); 952 insertStatement.registerOutParameter(2, Types.INTEGER); 953 954 iInsertCount = insertStatement.executeUpdate(); 955 iGeneratedKey = insertStatement.getInt(2); 956 957 returnValues = new int[2]; 958 returnValues[0] = iInsertCount; 959 returnValues[1] = iGeneratedKey; 960 } 961 finally 962 { 963 DatabaseUtils.closeStatement(insertStatement); 964 } 965 966 return returnValues; 967 } 968 969 972 public int executeUpdateTestValue( 973 Connection dbConnection, 974 String strOldValue, 975 String strNewValue 976 ) throws SQLException 977 { 978 CallableStatement updateStatement = null; 979 int iUpdateCount = 0; 980 981 try 982 { 983 updateStatement = dbConnection.prepareCall("EXEC UPDATE_TRANSACTION_TEST_VALUE ?, ?"); 984 updateStatement.setString(1, strOldValue); 985 updateStatement.setString(2, strNewValue); 986 987 iUpdateCount = updateStatement.executeUpdate(); 990 } 991 finally 992 { 993 DatabaseUtils.closeStatement(updateStatement); 994 } 995 996 return iUpdateCount; 997 } 998 999 1002 public int[] executeInsertRow( 1003 Connection dbConnection, 1004 String strValue) 1005 throws SQLException 1006 { 1007 CallableStatement insertStatement = null; 1008 int iInsertCount = 0; 1009 int iInsertCountReturnedFromSP = 0; 1010 int[] returnValues = null; 1011 1012 try 1013 { 1014 insertStatement = dbConnection.prepareCall("{call INSERT_ROW_COUNT_TEST (?, ?)}"); 1015 insertStatement.setString(1, strValue); 1016 insertStatement.registerOutParameter(2, Types.INTEGER); 1017 1018 iInsertCount = insertStatement.executeUpdate(); 1019 iInsertCountReturnedFromSP = insertStatement.getInt(2); 1020 1021 returnValues = new int[2]; 1022 1023 returnValues[0] = iInsertCount; 1025 1026 returnValues[1] = iInsertCountReturnedFromSP; 1028 } 1029 finally 1030 { 1031 DatabaseUtils.closeStatement(insertStatement); 1032 } 1033 1034 return returnValues; 1035 } 1036 1037 1040 public void createTestUser( 1041 Connection cntAdminDBConnection, 1042 String strDatabaseURL, 1043 String strUserName, 1044 String strUserPassword 1045 ) throws SQLException 1046 { 1047 Statement stmQuery = null; 1048 try 1049 { 1050 String strDatabaseName = ""; 1052 if (strDatabaseURL.indexOf(":Tds:") > 0 1058 || strDatabaseURL.indexOf(":jtds:") > 0) 1059 { 1060 strDatabaseName = strDatabaseURL.substring( 1062 strDatabaseURL.lastIndexOf("/") + 1, 1063 strDatabaseURL.length()); 1064 } 1065 else if (strDatabaseURL.indexOf(":bea:") > 0 1066 || strDatabaseURL.indexOf(":datadirect:") > 0) 1067 { 1068 strDatabaseName = strDatabaseURL.substring( 1070 strDatabaseURL.lastIndexOf("atabaseName=") + 1071 "atabaseName=".length(), strDatabaseURL.length()); 1072 } 1073 else 1074 { 1075 throw new SQLException ("Unsupported URL: " + strDatabaseURL); 1076 } 1077 1078 1079 String strCreateUserQuery = "USE " + strDatabaseName + " EXEC sp_addlogin '" + 1080 strUserName + "', '" + strUserPassword + 1081 "', @defdb='" + strDatabaseName + 1082 "', @deflanguage='us_english', @auth_mech = 'ASE'" + 1083 " EXEC sp_locklogin '" + strUserName + "', 'unlock'" + 1084 " EXEC sp_adduser '" + strUserName + "', '" + strUserName + 1085 "', 'public' GRANT CREATE TABLE TO " + strUserName; 1086 1087 stmQuery = cntAdminDBConnection.createStatement(); 1088 1089 boolean bOriginalAutoCommit = cntAdminDBConnection.getAutoCommit(); 1092 1093 try 1094 { 1095 if (!bOriginalAutoCommit) 1096 { 1097 cntAdminDBConnection.setAutoCommit(true); 1098 } 1099 1100 if (stmQuery.execute(strCreateUserQuery)) 1101 { 1102 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 1104 } 1105 } 1106 finally 1107 { 1108 if (!bOriginalAutoCommit) 1109 { 1110 cntAdminDBConnection.setAutoCommit(bOriginalAutoCommit); 1111 } 1112 } 1113 } 1114 finally 1115 { 1116 DatabaseUtils.closeStatement(stmQuery); 1117 } 1118 } 1119 1120 1123 public void dropTestUser( 1124 Connection cntAdminDBConnection, 1125 String strDatabaseURL, 1126 String strUserName 1127 ) throws SQLException 1128 { 1129 Statement stmQuery = null; 1130 1131 try 1132 { 1133 String strDatabaseName = strDatabaseURL.substring( 1135 strDatabaseURL.lastIndexOf("/") + 1, 1136 strDatabaseURL.length()); 1137 1138 String strDropUserQuery = "USE " + strDatabaseName + " EXEC sp_dropuser " + strUserName + 1139 " EXEC sp_droplogin " + strUserName; 1140 1141 stmQuery = cntAdminDBConnection.createStatement(); 1142 1143 if (stmQuery.execute(strDropUserQuery)) 1144 { 1145 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 1147 } 1148 } 1149 finally 1150 { 1151 DatabaseUtils.closeStatement(stmQuery); 1152 } 1153 } 1154} 1155 | Popular Tags |