Java > Open Source Codes > org > opensubsystems > patterns > mappeddata > persist > db > MappingDatabaseSchema


1 /*
2  * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: MappingDatabaseSchema.java,v 1.14 2007/01/10 05:16:59 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */
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 /**
52  * Database specific operations related to persistence of mapping tables.
53  * Multiple records from TABLE1 can be mapped to records in TABLE2. The total
54  * relationship between these two tables is is N:M. The same two records can be
55  * also mapped using different mapping types.
56  *
57  * Subsystems that want to create mappings should derive database schema class
58  * from this class. The only thing which is required is to implement constructor
59  * that provides attributes, that are use to construct database tables where the
60  * mappings will be stored.
61  *
62  * - 1st important rule is, that name of the table should contains part of:
63  * a.) name of the table the 1 related record is comming from
64  * b.) name of the table the M related records are comming from
65  * For example:
66  * a.) TABLE1 ... we want to map 1 record from this table
67  * b.) TABLE2 ... we want to map M records from this table
68  * mapped table name = <table prefix>_TABLE1_TABLE2
69  *
70  * - 2nd important rule is, that we will map 1 related item
71  * within the MAPPING_SCHEMA_COLUMN1 and we will map M related items
72  * within the MAPPING_SCHEMA_COLUMN2. This is important because within
73  * the method geSelectMappingRecord(int) we specify that we want to select
74  * all IDs stored in MAPPING_SCHEMA_COLUMN2 if we know ID from column
75  * MAPPING_SCHEMA_COLUMN1.
76  *
77  * This class also differs from other database schema clases, because it doesn't
78  * rely on inheritance to create database schema instance for specific database.
79  * This would require subsystem that uses this class to derive new class from
80  * each one of the database specific schemas. Instead this database schema creates
81  * directly instances of database schemas for specific databases and then executes
82  * their operation. This makes implementation of subsystems that use this schema
83  * much easier (only 1 schema class is required rather then N, one for each
84  * supported database) but when new database support is added, this class must
85  * change to hardcode support for that database.
86  *
87  * @version $Id: MappingDatabaseSchema.java,v 1.14 2007/01/10 05:16:59 bastafidli Exp $
88  * @author Julian Legeny
89  * @code.reviewer Miro Halas
90  * @code.reviewed 1.9 2006/09/25 23:18:33 jlegeny
91  */
92 public abstract class MappingDatabaseSchema extends ModifiableDatabaseSchemaImpl
93 {
94    // Constants ////////////////////////////////////////////////////////////////
95
96    /**
97     * Name identifies this schema in the database.
98     */
99    public static final String MAPPING_SCHEMA_NAME = "MAPPING";
100
101    /**
102     * Version of this schema in the database.
103     */
104    public static final int MAPPING_SCHEMA_VERSION = 1;
105    
106    /**
107     * Name identifies new column of the mapping table. This column is referenced
108     * from table name 1 and column name 1.
109     */
110    public static final String MAPPING_SCHEMA_COLUMN1 = "ID1";
111    
112    /**
113     * Name identifies new column of the mapping table. This column is referenced
114     * from table name 1 and column name 1.
115     */
116    public static final String MAPPING_SCHEMA_COLUMN2 = "ID2";
117
118    /**
119     * Maximal length of custom data.
120     */
121    public static final int MAPPING_CUSTOM_DATA_MAXLENGTH = 4000;
122
123    // Cached values ////////////////////////////////////////////////////////////
124
125    /**
126     * Name of the mapping table that will be created. Name doesn't contain
127     * database schema prefix. Maximal length of the table name has to be 18
128     * characters becouse of restriction for IBM DB2 database.
129     */
130    protected String m_strMapTableName;
131
132    /**
133     * Schema entry from which we are mapping.
134     */
135    protected Class m_schema1;
136    
137    /**
138     * Name of the table used for constructing of mapping table.
139     */
140    protected String m_strTableName1;
141
142    /**
143     * Name of the column used for constructing of mapping table.
144     */
145    protected String m_strColumnName1;
146
147    /**
148     * Schema entry from which we are mapping.
149     */
150    protected Class m_schema2;
151    
152    /**
153     * Name of the table used for constructing of mapping table.
154     */
155    protected String m_strTableName2;
156    
157    /**
158     * Name of the column used for constructing of mapping table.
159     */
160    protected String m_strColumnName2;
161
162    /**
163     * Constructed body of the constraint.
164     */
165    protected String m_strConstraintBody;
166
167    // Constructors /////////////////////////////////////////////////////////////
168
169    /**
170     * Static initializer
171     */
172    static
173    {
174       // Setup maximal length of individual fields for entities
175 MappedData.setCustomDataMaxLength(MAPPING_CUSTOM_DATA_MAXLENGTH);
176    }
177
178    /**
179     * Full constructor.
180     *
181     * @param strMapTableName - table name for mapping table
182     * @param schema1 - schema name the table 1 was defined in
183     * @param strTableName1 - name of the table 1
184     * @param strColumnName1 - name of the column 1
185     * @param schema2 - schema name the table 2 was defined in
186     * @param strTableName2 - name of the table 2
187     * @param strColumnName2 - name of the column 2
188     * @throws OSSException - an error has occured
189     */
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       // string with constructed body of constraint name
217 m_strConstraintBody = constructConstraintName();
218    }
219
220    // Public methods ///////////////////////////////////////////////////////////
221
222    /**
223     * {@inheritDoc}
224     */
225    public void create(
226       Connection cntDBConnection,
227       String strUserName
228    ) throws SQLException ,
229       OSSException
230    {
231       getDatabaseSpecificInstance().create(cntDBConnection, strUserName);
232    }
233
234    /**
235     * Insert MappedData to the database and return the generated values.
236     *
237     * @param cntDBConnection - database connection
238     * @param data - MappedData to insert
239     * @return MappedData - the same instance updated with generated values
240     * @throws OSSException - an error has occured
241     */
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    /**
253     * Update MappedData in the database and return the generated values.
254     *
255     * @param cntDBConnection - database connection
256     * @param data - MappedData to update
257     * @return MappedData - the same instance updated with generated values
258     * @throws OSSException - an error has occured
259     */
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    /**
271     * Method returns simple insert mapping record query.
272     * This method is common for all databases.
273     *
274     * @return String - simple insert mapping record query
275     * @throws OSSException - exception during getting query
276     */
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    /**
326     * Method returns simple delete mapping record query.
327     * This method is common for all databases.
328     *
329     * @return String - simple delete mapping record query
330     * @throws OSSException - exception during getting query
331     */
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    /**
350     * Method returns simple select mapping record query.
351     * This method is common for all databases.
352     *
353     * @return String - simple select mapping record query
354     * @throws OSSException - exception during getting query
355     */
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    /**
374     * Method returns simple select mapping multiple record query.
375     * This method is common for all databases.
376     *
377     * @param strColumnIDs - string representation of column IDs separated by ','
378     * @return String - simple select mapping record query
379     * @throws OSSException - exception during getting query
380     */
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    /**
401     * Get query to select mapped data by id.
402     *
403     * @param columns - list of columns to retrieve
404     * @return String - query
405     * @throws OSSException - an error has occured
406     */
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    /**
424     * Get query to delete mapped data by id.
425     *
426     * @return String - query
427     */
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    /**
443     * Convert any attribute regardless if it is in the main table or in join
444     * table into database column name.
445     *
446     * @param specific - if true - table_name.table_column, if false only table_column
447     * @param columns - specific array of columns codes
448     * @param prefixes - specific array of prefixes to prepend before each column,
449     * each prefix will be mapped with column and the same index
450     * they will be directly prepended or appended to the column
451     * name and therefore they need to contain any extra space if
452     * necessary,
453     * specify null, if no prefixes should be prepended
454     * @param postfixes - specific array of postfixes to prepend before each column,
455     * each prefix will be mapped with column and the same index
456     * they will be directly prepended or appended to the column
457     * name and therefore they need to contain any extra space if
458     * necessary,
459     * specify null, if no prefixes should be prepended
460     * @param buffer - buffer to use for column codes construction, it may
461     * already contain some value, if null, new one will be allocated
462     * @return StringBuffer - specific columns of table divided by comma
463     * with specified prefixes and postfixes
464     * @throws OSSException - error in columns find
465     */
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    // Helper methods ///////////////////////////////////////////////////////////
548
549    /**
550     * Common exception handler for insert of mapped data.
551     *
552     * @param exc - exception which has occured
553     * @throws OSSException - more descriptive exception
554     */
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           // MySQL handles login name unique constraint exception as 'KEY 2'
576 || ((exc.getMessage().toUpperCase()).endsWith("KEY 2"))
577           // IBM DB2 handles login name unique constraint exception as "2"
578 || ((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    /**
599     * Common exception handler for update of mapped data.
600     *
601     * @param exc - exception which has occured
602     * @param dbConnection - database connection
603     * @param data - data being updated
604     * @throws OSSException - more descriptive exception
605     */
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           // MySQL handles login name unique constraint exception as 'KEY 2'
616 || ((exc.getMessage().toUpperCase()).endsWith("KEY 2"))
617           // IBM DB2 handles login name unique constraint exception as "2"
618 || ((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       // This will detects conflict when two users tries to modify the same data
627 // and one of them saves the changes and then other tries to overwrite them
628 // without knowing that the data were modified
629 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    /**
649     * Method constructs sql for creating mapping table
650     *
651     * @param strIDDefinition - definition for ID column
652     * @param strColDataType - datatype of the new column
653     * @param strTextDataType - datatype of the text column (custom data)
654     * @param strTmstpDataType - datatype of the timestamp columns
655     * (creation and modification date)
656     * @param strConstraintBody - body of the constraint name
657     * @param bCascade - flag signaling if there will be added ON DELETE CASCADE
658     * to foreign key constraint definition
659     * @param bPKConstraint - flag signaling if there will be defined primary key constraint
660     * @return String - constructed command for creating mapping table
661     */
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       // construct ID column
680 buffer.append(strIDDefinition);
681       buffer.append(NL);
682       // construct new columns
683 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       // construct timestamp columns
696 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       // construct unique index constraint
703 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       // generate primary key for ID column
711 if (bPKConstraint)
712       {
713          buffer.append("CONSTRAINT ");
714          buffer.append(strConstraintBody);
715          buffer.append("_PK PRIMARY KEY (ID),");
716       }
717       // construct foreign key constraint for column 1
718 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       // construct foreign key constraint for column 2
733 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    /**
751     * Method constructs name of the constraint. This index name should be common
752     * for all DB systems. But there is restriction for IBM DB2 database, the
753     * length of the constraint name shold be max. 18 characters. We will construct
754     * this name from column name 1 and column name 2. Template is:
755     * <part_table1><part_table2>
756     * where <part_table1> and <part_table2> have length 7 characters and are
757     * constructed from table names. Prefix BF_ from the table names will be
758     * removed first. Also all others occurences of the character '_' will be
759     * removed. There are used max 7. characters from the table name: 3 characters
760     * from the begining of the table name and 4 characters from the end of the
761     * table name string.
762     *
763     * For example:
764     * we have 2 tables: BF_TAB_NAME_1 and BF_ANOTHER_TABLE
765     * constructed constraint name is TABAME1ANOABLE
766     *
767     * @return String - constructed name of the constraint
768     */
769    protected String constructConstraintName()
770    {
771       if (GlobalConstants.ERROR_CHECKING)
772       {
773          // Table name has to be at least 2 characters length
774 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          // remove database schema prefix from the table name 1
786 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          // remove database schema prefix from the table name 2
793 tableName2 = m_strTableName2.substring(getSchemaPrefix().length(),
794                                                 m_strTableName2.length());
795       }
796
797       int iTableNameLength1 = tableName1.length();
798       int iTableNameLength2 = tableName2.length();
799
800       // process table name 1 for constructing part of the constrain name
801 if (iTableNameLength1 < 8)
802       {
803          buffer.append(tableName1);
804       }
805       else
806       {
807          // first remove all '_' characters
808 tableName1 = tableName1.replaceAll("_", "");
809          iTableNameLength1 = tableName1.length();
810          // add first 3 characters
811 buffer.append(tableName1.substring(0, 3));
812          // add last 4 characters
813 buffer.append(tableName1.substring(iTableNameLength1 - 4, iTableNameLength1));
814       }
815
816       // process table name 2 for constructing part of the constrain name
817 if (iTableNameLength2 < 8)
818       {
819          buffer.append(tableName2);
820       }
821       else
822       {
823          // first remove all '_' characters
824 tableName2 = tableName2.replaceAll("_", "");
825          iTableNameLength2 = tableName2.length();
826          // add first 3 characters
827 buffer.append(tableName2.substring(0, 3));
828          // add last 4 characters
829 buffer.append(tableName2.substring(iTableNameLength2 - 4, iTableNameLength2));
830       }
831
832       return buffer.toString();
833    }
834    
835    /**
836     * Create instance of database specific schema.
837     *
838     * @return MappingDatabaseSchema - new instacne of specific database schema
839     * @throws OSSException - an error has occured
840     */
841    protected MappingDatabaseSchema getDatabaseSpecificInstance(
842    ) throws OSSException
843    {
844       // identifier of currently used database
845 String strDBIdentifier = DatabaseImpl.getInstance().getDatabaseTypeIdentifier();
846       MappingDatabaseSchema schema = null;
847       
848       // We need construct sql command for mapping table, but this command
849 // can be different for each database.
850 if (strDBIdentifier.equals(Database.HSQLDB_DATABASE_TYPE_IDENTIFIER))
851       {
852          // construct mapping schema for HSQL database
853 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          // construct mapping schema for MySQL database
860 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          // construct mapping schema for IBM DB2 database
867 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          // construct mapping table for MaxDB database
874 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          // construct mapping table for SapDB database
881 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          // construct mapping schema for IBM DB2 database
888 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          // construct mapping table for Oracle database
895 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          // construct mapping table for all other databases
902 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          // construct mapping table for Sybase ASE database
909 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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags