1 21 22 package org.opensubsystems.patterns.mappeddata.persist.db; 23 24 import java.sql.Connection ; 25 import java.sql.SQLException ; 26 27 import org.opensubsystems.core.data.DataConstant; 28 import org.opensubsystems.core.error.OSSDataCreateException; 29 import org.opensubsystems.core.error.OSSDataSaveException; 30 import org.opensubsystems.core.error.OSSException; 31 import org.opensubsystems.core.error.OSSInvalidContextException; 32 import org.opensubsystems.core.error.OSSInvalidDataException; 33 import org.opensubsystems.core.persist.db.Database; 34 import org.opensubsystems.core.persist.db.DatabaseDataUtils; 35 import org.opensubsystems.core.persist.db.DatabaseImpl; 36 import org.opensubsystems.core.persist.db.DatabaseSchema; 37 import org.opensubsystems.core.persist.db.DatabaseSchemaManager; 38 import org.opensubsystems.core.persist.db.ModifiableDatabaseSchemaImpl; 39 import org.opensubsystems.core.util.GlobalConstants; 40 import org.opensubsystems.patterns.mappeddata.data.MappedData; 41 import org.opensubsystems.patterns.mappeddata.persist.db.db2.DB2MappingDatabaseSchema; 42 import org.opensubsystems.patterns.mappeddata.persist.db.hsqldb.HsqlDBMappingDatabaseSchema; 43 import org.opensubsystems.patterns.mappeddata.persist.db.maxdb.MaxDBMappingDatabaseSchema; 44 import org.opensubsystems.patterns.mappeddata.persist.db.mssql.MSSQLMappingDatabaseSchema; 45 import org.opensubsystems.patterns.mappeddata.persist.db.mysql.MySQLMappingDatabaseSchema; 46 import org.opensubsystems.patterns.mappeddata.persist.db.oracle.OracleMappingDatabaseSchema; 47 import org.opensubsystems.patterns.mappeddata.persist.db.postgresql.PostgreSQLMappingDatabaseSchema; 48 import org.opensubsystems.patterns.mappeddata.persist.db.sapdb.SapDBMappingDatabaseSchema; 49 import org.opensubsystems.patterns.mappeddata.persist.db.sybase.SybaseMappingDatabaseSchema; 50 51 92 public abstract class MappingDatabaseSchema extends ModifiableDatabaseSchemaImpl 93 { 94 96 99 public static final String MAPPING_SCHEMA_NAME = "MAPPING"; 100 101 104 public static final int MAPPING_SCHEMA_VERSION = 1; 105 106 110 public static final String MAPPING_SCHEMA_COLUMN1 = "ID1"; 111 112 116 public static final String MAPPING_SCHEMA_COLUMN2 = "ID2"; 117 118 121 public static final int MAPPING_CUSTOM_DATA_MAXLENGTH = 4000; 122 123 125 130 protected String m_strMapTableName; 131 132 135 protected Class m_schema1; 136 137 140 protected String m_strTableName1; 141 142 145 protected String m_strColumnName1; 146 147 150 protected Class m_schema2; 151 152 155 protected String m_strTableName2; 156 157 160 protected String m_strColumnName2; 161 162 165 protected String m_strConstraintBody; 166 167 169 172 static 173 { 174 MappedData.setCustomDataMaxLength(MAPPING_CUSTOM_DATA_MAXLENGTH); 176 } 177 178 190 public MappingDatabaseSchema( 191 String strMapTableName, 192 Class schema1, 193 String strTableName1, 194 String strColumnName1, 195 Class schema2, 196 String strTableName2, 197 String strColumnName2 198 ) throws OSSException 199 { 200 201 super(new DatabaseSchema[] 202 {DatabaseSchemaManager.getInstance(schema1), 203 DatabaseSchemaManager.getInstance(schema2), 204 }, 205 MAPPING_SCHEMA_NAME, MAPPING_SCHEMA_VERSION, false, 206 DataConstant.MAPPEDDATA_DATA_TYPE_OBJ, strMapTableName); 207 208 m_strMapTableName = strMapTableName; 209 m_schema1 = schema1; 210 m_strTableName1 = strTableName1; 211 m_strColumnName1 = strColumnName1; 212 m_schema2 = schema2; 213 m_strTableName2 = strTableName2; 214 m_strColumnName2 = strColumnName2; 215 216 m_strConstraintBody = constructConstraintName(); 218 } 219 220 222 225 public void create( 226 Connection cntDBConnection, 227 String strUserName 228 ) throws SQLException , 229 OSSException 230 { 231 getDatabaseSpecificInstance().create(cntDBConnection, strUserName); 232 } 233 234 242 public MappedData insertMappedData( 243 Connection cntDBConnection, 244 MappedData data 245 ) throws OSSException 246 { 247 data = getDatabaseSpecificInstance().insertMappedData(cntDBConnection, data); 248 249 return data; 250 } 251 252 260 public MappedData updateMappedData( 261 Connection cntDBConnection, 262 MappedData data 263 ) throws OSSException 264 { 265 data = getDatabaseSpecificInstance().updateMappedData(cntDBConnection, data); 266 267 return data; 268 } 269 270 277 public String getInsertMappingRecord( 278 ) throws OSSException 279 { 280 StringBuffer buffer = new StringBuffer (); 281 StringBuffer bufferSeq = new StringBuffer (); 282 String strDBIdentifier = DatabaseImpl.getInstance().getDatabaseTypeIdentifier(); 283 boolean bSpecifyID = false; 284 285 if (strDBIdentifier.equals(Database.DB2_DATABASE_TYPE_IDENTIFIER)) 286 { 287 bSpecifyID = true; 288 bufferSeq.append("NEXT VALUE FOR "); 289 bufferSeq.append(m_strMapTableName); 290 bufferSeq.append("_SEQ, "); 291 } 292 else if (strDBIdentifier.equals(Database.ORACLE_DATABASE_TYPE_IDENTIFIER)) 293 { 294 bSpecifyID = true; 295 bufferSeq.append(m_strMapTableName); 296 bufferSeq.append("_SEQ.NEXTVAL, "); 297 } 298 299 buffer.append("INSERT INTO "); 300 buffer.append(getSchemaPrefix()); 301 buffer.append(m_strMapTableName); 302 buffer.append("("); 303 if (bSpecifyID) 304 { 305 buffer.append("ID, "); 306 } 307 buffer.append(MAPPING_SCHEMA_COLUMN1); 308 buffer.append(", "); 309 buffer.append(MAPPING_SCHEMA_COLUMN2); 310 buffer.append(", MAPPING_TYPE, CUSTOM_DATA, CREATION_DATE, MODIFICATION_DATE) " + 311 "VALUES ("); 312 if (bSpecifyID) 313 { 314 buffer.append(bufferSeq); 315 } 316 buffer.append("?, ?, ?, ?, "); 317 buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall()); 318 buffer.append(", "); 319 buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall()); 320 buffer.append(")"); 321 322 return buffer.toString(); 323 } 324 325 332 public String getDeleteMappingRecord( 333 ) throws OSSException 334 { 335 StringBuffer buffer = new StringBuffer (); 336 337 buffer.append("DELETE FROM "); 338 buffer.append(getSchemaPrefix()); 339 buffer.append(m_strMapTableName); 340 buffer.append(" WHERE "); 341 buffer.append(MAPPING_SCHEMA_COLUMN1); 342 buffer.append(" = ? AND "); 343 buffer.append(MAPPING_SCHEMA_COLUMN2); 344 buffer.append(" = ? AND MAPPING_TYPE = ?"); 345 346 return buffer.toString(); 347 } 348 349 356 public String geSelectMappingRecords( 357 ) throws OSSException 358 { 359 StringBuffer buffer = new StringBuffer (); 360 361 buffer.append("SELECT "); 362 buffer.append(MAPPING_SCHEMA_COLUMN2); 363 buffer.append(" FROM "); 364 buffer.append(getSchemaPrefix()); 365 buffer.append(m_strMapTableName); 366 buffer.append(" WHERE "); 367 buffer.append(MAPPING_SCHEMA_COLUMN1); 368 buffer.append(" = ? AND MAPPING_TYPE = ?"); 369 370 return buffer.toString(); 371 } 372 373 381 public String geSelectMappingMultipleRecords( 382 String strColumnIDs 383 ) throws OSSException 384 { 385 StringBuffer buffer = new StringBuffer (); 386 387 buffer.append("SELECT "); 388 buffer.append(MAPPING_SCHEMA_COLUMN2); 389 buffer.append(" FROM "); 390 buffer.append(getSchemaPrefix()); 391 buffer.append(m_strMapTableName); 392 buffer.append(" WHERE "); 393 buffer.append(MAPPING_SCHEMA_COLUMN1); 394 buffer.append(" IN ("); 395 buffer.append(strColumnIDs); 396 buffer.append(") AND MAPPING_TYPE = ?"); 397 398 return buffer.toString(); 399 } 400 407 public String getSelectMappedDataById( 408 int[] columns 409 ) throws OSSException 410 { 411 StringBuffer buffer = new StringBuffer (); 412 413 buffer.append("select "); 414 getColumns(false, columns, null, null, buffer); 415 buffer.append(" from "); 416 buffer.append(getSchemaPrefix()); 417 buffer.append(m_strMapTableName); 418 buffer.append(" where ID = ?"); 419 420 return buffer.toString(); 421 } 422 423 428 public String getDeleteMappedDataById( 429 ) 430 { 431 StringBuffer buffer = new StringBuffer (); 432 433 buffer.append("delete from "); 434 buffer.append(getSchemaPrefix()); 435 buffer.append(m_strMapTableName); 436 buffer.append(" where ID = ?"); 437 438 return buffer.toString(); 439 } 440 441 442 466 public StringBuffer getColumns( 467 final boolean specific, 468 final int[] columns, 469 final Object [] prefixes, 470 final Object [] postfixes, 471 StringBuffer buffer 472 ) throws OSSException 473 { 474 if (buffer == null) 475 { 476 buffer = new StringBuffer (); 477 } 478 for (int iIndex = 0; iIndex < columns.length; iIndex++) 479 { 480 if (iIndex > 0) 481 { 482 buffer.append(","); 483 } 484 485 if ((prefixes != null) && (prefixes.length > 0) && (prefixes[iIndex] != null)) 486 { 487 buffer.append(prefixes[iIndex]); 488 } 489 490 if (specific) 491 { 492 buffer.append(getSchemaPrefix() + m_strMapTableName + "."); 493 } 494 495 switch(columns[iIndex]) 496 { 497 case (MappedData.COL_MAPPEDDATA_ID): 498 { 499 buffer.append("ID"); 500 break; 501 } 502 case (MappedData.COL_MAPPEDDATA_ID1): 503 { 504 buffer.append(MAPPING_SCHEMA_COLUMN1); 505 break; 506 } 507 case (MappedData.COL_MAPPEDDATA_ID2): 508 { 509 buffer.append(MAPPING_SCHEMA_COLUMN2); 510 break; 511 } 512 case (MappedData.COL_MAPPEDDATA_MAPPING_TYPE): 513 { 514 buffer.append("MAPPING_TYPE"); 515 break; 516 } 517 case (MappedData.COL_MAPPEDDATA_CUSTOM_DATA): 518 { 519 buffer.append("CUSTOM_DATA"); 520 break; 521 } 522 case (MappedData.COL_MAPPEDDATA_CREATION_DATE): 523 { 524 buffer.append("CREATION_DATE"); 525 break; 526 } 527 case (MappedData.COL_MAPPEDDATA_MODIFICATION_DATE): 528 { 529 buffer.append("MODIFICATION_DATE"); 530 break; 531 } 532 default: 533 { 534 assert false : "Unknown column ID " + columns[iIndex]; 535 } 536 } 537 538 if ((postfixes != null) && (postfixes.length > 0) && (postfixes[iIndex] != null)) 539 { 540 buffer.append(postfixes[iIndex]); 541 } 542 } 543 544 return buffer; 545 } 546 547 549 555 protected void handleInsertMappedDataException( 556 SQLException exc 557 ) throws OSSException 558 { 559 if (exc.getMessage().toUpperCase().indexOf(m_strConstraintBody + "_FK1") > -1) 560 { 561 throw new OSSInvalidContextException( 562 "Mapped ID1 to create mapped data for does not exist.", 563 exc); 564 } 565 if (exc.getMessage().toUpperCase().indexOf(m_strConstraintBody + "_FK2") > -1) 566 { 567 throw new OSSInvalidContextException( 568 "Mapped ID2 to create mapped data for does not exist.", 569 exc); 570 } 571 572 OSSInvalidDataException ideException = null; 573 574 if ((exc.getMessage().toUpperCase().indexOf(m_strConstraintBody + "_UQ") > -1) 575 || ((exc.getMessage().toUpperCase()).endsWith("KEY 2")) 577 || ((exc.getMessage().toUpperCase()).indexOf("\"2\"") > -1)) 579 { 580 ideException = OSSInvalidDataException.addException(ideException, 581 MappedData.COL_MAPPEDDATA_MAPPED_TYPE_OBJ, 582 "Inserted mapped data has to be unique.", 583 exc); 584 } 585 586 if (ideException != null) 587 { 588 throw ideException; 589 } 590 else 591 { 592 throw new OSSDataCreateException( 593 "Unexpected exception has occured while creating mapped data.", 594 exc); 595 } 596 } 597 598 606 protected void handleUpdateMappedDataException( 607 SQLException exc, 608 Connection dbConnection, 609 MappedData data 610 ) throws OSSException 611 { 612 OSSInvalidDataException ideException = null; 613 614 if ((exc.getMessage().toUpperCase().indexOf(m_strConstraintBody + "_UQ") > -1) 615 || ((exc.getMessage().toUpperCase()).endsWith("KEY 2")) 617 || ((exc.getMessage().toUpperCase()).indexOf("\"2\"") > -1)) 619 { 620 ideException = OSSInvalidDataException.addException(ideException, 621 MappedData.COL_MAPPEDDATA_MAPPED_TYPE_OBJ, 622 "Mapped data to update has to be unique.", 623 exc); 624 } 625 626 if (exc.getMessage().indexOf("[100]") > -1) 630 { 631 DatabaseDataUtils.checkUpdateError(dbConnection, "Mapped Data", 632 getSchemaPrefix() + m_strMapTableName, 633 data.getId(), data.getModificationTimestamp()); 634 } 635 636 if (ideException != null) 637 { 638 throw ideException; 639 } 640 else 641 { 642 throw new OSSDataSaveException( 643 "Unexpected exception has occured while updating mapped data.", 644 exc); 645 } 646 } 647 648 662 protected String constructSQL( 663 String strIDDefinition, 664 String strColDataType, 665 String strTextDataType, 666 String strTmstpDataType, 667 String strConstraintBody, 668 boolean bCascade, 669 boolean bPKConstraint 670 ) 671 { 672 StringBuffer buffer = new StringBuffer (); 673 674 buffer.append("create table "); 675 buffer.append(getSchemaPrefix()); 676 buffer.append(m_strMapTableName); 677 buffer.append(NL); 678 buffer.append(" (" + NL); 679 buffer.append(strIDDefinition); 681 buffer.append(NL); 682 buffer.append(MAPPING_SCHEMA_COLUMN1); 684 buffer.append(" "); 685 buffer.append(strColDataType); 686 buffer.append(" NOT NULL," + NL); 687 buffer.append(MAPPING_SCHEMA_COLUMN2); 688 buffer.append(" "); 689 buffer.append(strColDataType); 690 buffer.append(" NOT NULL," + NL); 691 buffer.append("MAPPING_TYPE INTEGER NOT NULL," + NL); 692 buffer.append("CUSTOM_DATA "); 693 buffer.append(strTextDataType); 694 buffer.append(" NULL," + NL); 695 buffer.append("CREATION_DATE "); 697 buffer.append(strTmstpDataType); 698 buffer.append(" NOT NULL," + NL); 699 buffer.append("MODIFICATION_DATE "); 700 buffer.append(strTmstpDataType); 701 buffer.append(" NOT NULL," + NL); 702 buffer.append("CONSTRAINT "); 704 buffer.append(strConstraintBody); 705 buffer.append("_UQ UNIQUE ("); 706 buffer.append(MAPPING_SCHEMA_COLUMN1); 707 buffer.append(", "); 708 buffer.append(MAPPING_SCHEMA_COLUMN2); 709 buffer.append(", MAPPING_TYPE)," + NL); 710 if (bPKConstraint) 712 { 713 buffer.append("CONSTRAINT "); 714 buffer.append(strConstraintBody); 715 buffer.append("_PK PRIMARY KEY (ID),"); 716 } 717 buffer.append("CONSTRAINT "); 719 buffer.append(strConstraintBody); 720 buffer.append("_FK1 FOREIGN KEY ("); 721 buffer.append(MAPPING_SCHEMA_COLUMN1); 722 buffer.append(") REFERENCES "); 723 buffer.append(m_strTableName1); 724 buffer.append(" ("); 725 buffer.append(m_strColumnName1); 726 buffer.append(")"); 727 if (bCascade) 728 { 729 buffer.append(" ON DELETE CASCADE"); 730 } 731 buffer.append("," + NL); 732 buffer.append("CONSTRAINT "); 734 buffer.append(strConstraintBody); 735 buffer.append("_FK2 FOREIGN KEY ("); 736 buffer.append(MAPPING_SCHEMA_COLUMN2); 737 buffer.append(") REFERENCES "); 738 buffer.append(m_strTableName2); 739 buffer.append(" ("); 740 buffer.append(m_strColumnName2); 741 buffer.append(")"); 742 if (bCascade) 743 { 744 buffer.append(" ON DELETE CASCADE"); 745 } 746 buffer.append(NL + ")"); 747 return buffer.toString(); 748 } 749 750 769 protected String constructConstraintName() 770 { 771 if (GlobalConstants.ERROR_CHECKING) 772 { 773 assert m_strTableName1.length() > 2 : "Table name 1 is too short."; 775 assert m_strTableName2.length() > 2 : "Table name 2 is too short."; 776 } 777 778 StringBuffer buffer = new StringBuffer (); 779 String tableName1 = m_strTableName1; 780 String tableName2 = m_strTableName2; 781 782 if (m_strTableName1.toUpperCase().startsWith(getSchemaPrefix()) 783 && m_strTableName1.length() > 3) 784 { 785 tableName1 = m_strTableName1.substring(getSchemaPrefix().length(), 787 m_strTableName1.length()); 788 } 789 if (m_strTableName2.toUpperCase().startsWith(getSchemaPrefix()) 790 && m_strTableName2.length() > 3) 791 { 792 tableName2 = m_strTableName2.substring(getSchemaPrefix().length(), 794 m_strTableName2.length()); 795 } 796 797 int iTableNameLength1 = tableName1.length(); 798 int iTableNameLength2 = tableName2.length(); 799 800 if (iTableNameLength1 < 8) 802 { 803 buffer.append(tableName1); 804 } 805 else 806 { 807 tableName1 = tableName1.replaceAll("_", ""); 809 iTableNameLength1 = tableName1.length(); 810 buffer.append(tableName1.substring(0, 3)); 812 buffer.append(tableName1.substring(iTableNameLength1 - 4, iTableNameLength1)); 814 } 815 816 if (iTableNameLength2 < 8) 818 { 819 buffer.append(tableName2); 820 } 821 else 822 { 823 tableName2 = tableName2.replaceAll("_", ""); 825 iTableNameLength2 = tableName2.length(); 826 buffer.append(tableName2.substring(0, 3)); 828 buffer.append(tableName2.substring(iTableNameLength2 - 4, iTableNameLength2)); 830 } 831 832 return buffer.toString(); 833 } 834 835 841 protected MappingDatabaseSchema getDatabaseSpecificInstance( 842 ) throws OSSException 843 { 844 String strDBIdentifier = DatabaseImpl.getInstance().getDatabaseTypeIdentifier(); 846 MappingDatabaseSchema schema = null; 847 848 if (strDBIdentifier.equals(Database.HSQLDB_DATABASE_TYPE_IDENTIFIER)) 851 { 852 schema = new HsqlDBMappingDatabaseSchema( 854 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 855 m_schema2, m_strTableName2, m_strColumnName2); 856 } 857 else if (strDBIdentifier.equals(Database.MYSQL_DATABASE_TYPE_IDENTIFIER)) 858 { 859 schema = new MySQLMappingDatabaseSchema( 861 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 862 m_schema2, m_strTableName2, m_strColumnName2); 863 } 864 else if (strDBIdentifier.equals(Database.DB2_DATABASE_TYPE_IDENTIFIER)) 865 { 866 schema = new DB2MappingDatabaseSchema( 868 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 869 m_schema2, m_strTableName2, m_strColumnName2); 870 } 871 else if (strDBIdentifier.equals(Database.MAXDB_DATABASE_TYPE_IDENTIFIER)) 872 { 873 schema = new MaxDBMappingDatabaseSchema( 875 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 876 m_schema2, m_strTableName2, m_strColumnName2); 877 } 878 else if (strDBIdentifier.equals(Database.SAPDB_DATABASE_TYPE_IDENTIFIER)) 879 { 880 schema = new SapDBMappingDatabaseSchema( 882 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 883 m_schema2, m_strTableName2, m_strColumnName2); 884 } 885 else if (strDBIdentifier.equals(Database.MSSQL_DATABASE_TYPE_IDENTIFIER)) 886 { 887 schema = new MSSQLMappingDatabaseSchema( 889 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 890 m_schema2, m_strTableName2, m_strColumnName2); 891 } 892 else if (strDBIdentifier.equals(Database.ORACLE_DATABASE_TYPE_IDENTIFIER)) 893 { 894 schema = new OracleMappingDatabaseSchema( 896 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 897 m_schema2, m_strTableName2, m_strColumnName2); 898 } 899 else if (strDBIdentifier.equals(Database.POSTGRESQL_DATABASE_TYPE_IDENTIFIER)) 900 { 901 schema = new PostgreSQLMappingDatabaseSchema( 903 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 904 m_schema2, m_strTableName2, m_strColumnName2); 905 } 906 else if (strDBIdentifier.equals(Database.SYBASE_DATABASE_TYPE_IDENTIFIER)) 907 { 908 schema = new SybaseMappingDatabaseSchema( 910 m_strMapTableName, m_schema1, m_strTableName1, m_strColumnName1, 911 m_schema2, m_strTableName2, m_strColumnName2); 912 } 913 else 914 { 915 assert false : "No mapping database schema available for database " 916 + strDBIdentifier; 917 } 918 919 return schema; 920 } 921 } 922 | Popular Tags |