Java > Open Source Codes > org > apache > derby > iapi > sql > dictionary > DDUtils


1 /*
2
3    Derby - Class org.apache.derby.iapi.sql.dictionary.DDUtils
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to you under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derby.iapi.sql.dictionary;
23
24 import org.apache.derby.iapi.error.StandardException;
25
26 import org.apache.derby.iapi.reference.SQLState;
27 import org.apache.derby.iapi.sql.StatementType;
28 import java.util.Hashtable ;
29 import org.apache.derby.iapi.services.sanity.SanityManager;
30 import org.apache.derby.iapi.services.i18n.MessageService;
31 import java.util.Enumeration ;
32
33 /**
34  * Static Data dictionary utilities.
35  *
36  * @version 0.1
37  * @author Rick Hillegas
38  */
39
40 public class DDUtils
41 {
42
43     /*
44     ** For a foreign key, this is used to locate the referenced
45     ** key using the ConstraintInfo. If it doesn't find the
46     ** correct constraint it will throw an error.
47     */
48     public static ReferencedKeyConstraintDescriptor locateReferencedConstraint
49     (
50         DataDictionary dd,
51         TableDescriptor td,
52         String myConstraintName, // for error messages
53 String [] myColumnNames,
54         ConsInfo otherConstraintInfo
55     )
56         throws StandardException
57     {
58         TableDescriptor refTd = otherConstraintInfo.getReferencedTableDescriptor(dd);
59         if (refTd == null)
60         {
61                 throw StandardException.newException(SQLState.LANG_INVALID_FK_NO_REF_TAB,
62                                                 myConstraintName,
63                                                 otherConstraintInfo.getReferencedTableName());
64         }
65
66
67         ReferencedKeyConstraintDescriptor refCd = null;
68
69         /*
70         ** There were no column names specified, just find
71         ** the primary key on the table in question
72         */
73         String [] refColumnNames = otherConstraintInfo.getReferencedColumnNames();
74         if (refColumnNames == null ||
75             refColumnNames.length == 0)
76         {
77             refCd = refTd.getPrimaryKey();
78             if (refCd == null)
79             {
80                 throw StandardException.newException(SQLState.LANG_INVALID_FK_NO_PK,
81                                                 myConstraintName,
82                                                 refTd.getQualifiedName());
83             }
84
85             ColumnDescriptorList cdl = getColumnDescriptors(dd, td, myColumnNames);
86
87             /*
88             ** Check the column list length to give a more informative
89             ** error in case they aren't the same.
90             */
91             if (cdl.size() != refCd.getColumnDescriptors().size())
92             {
93                 throw StandardException.newException(SQLState.LANG_INVALID_FK_DIFFERENT_COL_COUNT,
94                                                 myConstraintName, String.valueOf(cdl.size()),
95                                                 String.valueOf(refCd.getColumnDescriptors().size()));
96             }
97     
98             /*
99             ** Make sure all types are the same.
100             */
101             if (!refCd.areColumnsComparable(cdl))
102             {
103                 throw StandardException.newException(SQLState.LANG_INVALID_FK_COL_TYPES_DO_NOT_MATCH,
104                                                 myConstraintName);
105             }
106
107             return refCd;
108         }
109
110         /*
111         ** Check the referenced columns vs. each unique or primary key to
112         ** see if they match the foreign key.
113         */
114         else
115         {
116             ConstraintDescriptor cd;
117
118             ColumnDescriptorList colDl = getColumnDescriptors(dd, td, myColumnNames);
119             ConstraintDescriptorList refCDL = dd.getConstraintDescriptors(refTd);
120
121             int refCDLSize = refCDL.size();
122             for (int index = 0; index < refCDLSize; index++)
123             {
124                 cd = refCDL.elementAt(index);
125
126                 /*
127                 ** Matches if it is not a check or fk, and
128                 ** all the types line up.
129                 */
130                 if ((cd instanceof ReferencedKeyConstraintDescriptor) &&
131                      cd.areColumnsComparable(colDl) &&
132                      columnNamesMatch(refColumnNames,
133                                         cd.getColumnDescriptors()))
134                 {
135                     return (ReferencedKeyConstraintDescriptor)cd;
136                 }
137             }
138
139             /*
140             ** If we got here, we didn't find anything
141             */
142             throw StandardException.newException(SQLState.LANG_INVALID_FK_NO_REF_KEY, myConstraintName,
143                                                         refTd.getQualifiedName());
144         }
145     }
146
147     public static ColumnDescriptorList getColumnDescriptors
148     (
149         DataDictionary dd,
150         TableDescriptor td,
151         String [] columnNames
152     )
153         throws StandardException
154     {
155         ColumnDescriptorList cdl = new ColumnDescriptorList();
156         for (int colCtr = 0; colCtr < columnNames.length; colCtr++)
157         {
158             ColumnDescriptor cd = td.getColumnDescriptor(columnNames[colCtr]);
159             cdl.add(td.getUUID(), cd);
160         }
161         return cdl;
162     }
163
164     public static boolean columnNamesMatch(String []columnNames, ColumnDescriptorList cdl)
165         throws StandardException
166     {
167         if (columnNames.length != cdl.size())
168         {
169             return false;
170         }
171         
172         String name;
173         for (int index = 0; index < columnNames.length; index++)
174         {
175             name = ((ColumnDescriptor) cdl.elementAt(index)).getColumnName();
176             if (!name.equals(columnNames[index]))
177             {
178                 return false;
179             }
180         }
181
182         return true;
183     }
184
185
186     /*
187     **checks whether the foreign key relation ships referential action
188     **is violating the restrictions we have in the current system.
189     **/
190     public static void validateReferentialActions
191     (
192         DataDictionary dd,
193         TableDescriptor td,
194         String myConstraintName, // for error messages
195 ConsInfo otherConstraintInfo,
196         String [] columnNames
197     )
198         throws StandardException
199     {
200
201
202         int refAction = otherConstraintInfo.getReferentialActionDeleteRule();
203
204         //Do not allow ON DELETE SET NULL as a referential action
205 //if none of the foreign key columns are nullable.
206 if(refAction == StatementType.RA_SETNULL)
207         {
208             boolean foundNullableColumn = false;
209             //check if we have a nullable foreign key column
210 for (int colCtr = 0; colCtr < columnNames.length; colCtr++)
211             {
212                 ColumnDescriptor cd = td.getColumnDescriptor(columnNames[colCtr]);
213                 if ((cd.getType().isNullable()))
214                 {
215                     foundNullableColumn = true;
216                     break;
217                 }
218             }
219
220             if(!foundNullableColumn)
221             {
222                 throw StandardException.newException(SQLState.LANG_INVALID_FK_COL_FOR_SETNULL,
223                                                      myConstraintName);
224             }
225         }
226
227         //check whether the foreign key relation ships referential action
228 //is not violating the restrictions we have in the current system.
229 TableDescriptor refTd = otherConstraintInfo.getReferencedTableDescriptor(dd);
230         Hashtable deleteConnHashtable = new Hashtable ();
231         //find whether the foreign key is self referencing.
232 boolean isSelfReferencingFk = (refTd.getUUID().equals(td.getUUID()));
233         String refTableName = refTd.getSchemaName() + "." + refTd.getName();
234         //look for the other foreign key constraints on this table first
235 int currentSelfRefValue = getCurrentDeleteConnections(dd, td, -1, deleteConnHashtable, false, true);
236         validateDeleteConnection(dd, td, refTd,
237                                  refAction,
238                                  deleteConnHashtable, (Hashtable ) deleteConnHashtable.clone(),
239                                  true, myConstraintName, false ,
240                                  new StringBuffer (0), refTableName,
241                                  isSelfReferencingFk,
242                                  currentSelfRefValue);
243
244         //if it not a selfreferencing key check for violation of exiting connections.
245 if(!isSelfReferencingFk)
246         {
247             checkForAnyExistingDeleteConnectionViolations(dd, td,
248                                                           refAction,
249                                                           deleteConnHashtable,
250                                                           myConstraintName);
251         }
252     }
253
254     /*
255     ** Finds the existing delete connection for the table and the referential
256     ** actions that will occur and stores the information in the hash table.
257     ** HashTable (key , value) = ( table name that this table is delete
258     ** connected to, referential action that will occur if there is a delete on
259     ** the table this table connected to[CASACDE, SETNULL , RESTRICT ...etc).)
260     **/
261
262     private static int getCurrentDeleteConnections
263     (
264      DataDictionary dd,
265      TableDescriptor td,
266      int refActionType,
267      Hashtable dch,
268      boolean prevNotCascade,
269      boolean findSelfRef
270      )
271         throws StandardException
272     {
273
274         int selfRefValue = -1; //store the self reference referential action
275
276         //make sure we get any foreign key constraints added earlier in the same statement.
277 td.emptyConstraintDescriptorList();
278         ConstraintDescriptorList cdl = dd.getConstraintDescriptors(td);
279         int cdlSize = cdl.size();
280
281         boolean passedInPrevNotCascade = prevNotCascade;
282         for (int index = 0; index < cdlSize; index++)
283         {
284                 ConstraintDescriptor cd = cdl.elementAt(index);
285
286                 //look for foreign keys
287 if ((cd instanceof ForeignKeyConstraintDescriptor))
288                 {
289                     ForeignKeyConstraintDescriptor fkcd = (ForeignKeyConstraintDescriptor) cd;
290                     String constraintName = fkcd.getConstraintName();
291                     int raDeleteRule = fkcd.getRaDeleteRule();
292                     int raUpdateRule = fkcd.getRaUpdateRule();
293
294                      if(findSelfRef && fkcd.isSelfReferencingFK())
295                      {
296                          //All self references will have same referential actions type
297 selfRefValue = raDeleteRule;
298                          findSelfRef = false;
299                      }
300
301                     ReferencedKeyConstraintDescriptor refcd =
302                         fkcd.getReferencedConstraint();
303                     TableDescriptor refTd = refcd.getTableDescriptor();
304                     int childRefAction = refActionType == -1 ? raDeleteRule : refActionType;
305                    
306                     String refTableName = refTd.getSchemaName() + "." + refTd.getName();
307                     //check with the existing references.
308 Integer rAction = ((Integer )dch.get(refTableName));
309                     if(rAction != null) // we already looked at this table
310 {
311                         prevNotCascade = passedInPrevNotCascade;
312                         continue;
313                     }
314
315                     //if we are not cascading, check whether the link before
316 //this was cascade or not. If we travel through two NON CASCADE ACTION
317 //links then the delete connection is broken(only a delete can have further
318 // referential effects)
319 if(raDeleteRule != StatementType.RA_CASCADE)
320                     {
321                         if(prevNotCascade)
322                         {
323                             prevNotCascade = passedInPrevNotCascade;
324                             continue;
325                         }
326                         else
327                             prevNotCascade = true;
328                     }
329
330                     //store the delete connection info in the hash table,
331 //note that the referential action value is not what is
332 //not specified on the current link. It is actually the
333 //value of what happens to the table whose delete
334 // connections we are finding.
335 dch.put(refTableName, (new Integer (childRefAction)));
336                     
337                     //find the next delete conectiions on this path for non
338 //self referencig delete connections.
339 if(!fkcd.isSelfReferencingFK())
340                         getCurrentDeleteConnections(dd , refTd, childRefAction,
341                                                     dch, true, false);
342                     prevNotCascade = passedInPrevNotCascade;
343                 }
344         }
345         
346         return selfRefValue;
347     }
348
349
350     /*
351     ** Following function validates whether the new foreign key relation ship
352     ** violates any restriction on the referential actions. Current refAction
353     ** implementation does not allow cases where we can possible land up
354     ** having multiple action for the same row in a table, this happens becase
355     ** user can possibly define differential action through multiple paths.
356     ** Following function throws error while creating foreign keys if the new
357     ** releations ship leads to any such conditions.
358     ** NOTE : SQL99 standard also does not cleary says what we are suppose to do
359     ** in these non determenistic cases.
360     ** Our implementation just follows what is did in DB2 and throws error
361     ** messaged similar to DB2 (sql0632N, sql0633N, sql0634N)
362     */
363
364     private static void validateDeleteConnection
365     (
366         DataDictionary dd,
367         TableDescriptor actualTd, // the table we are adding the foriegn key.
368 TableDescriptor refTd,
369         int refActionType,
370         Hashtable dch,
371         Hashtable ech, //existing delete connections
372 boolean checkImmediateRefTable,
373         String myConstraintName,
374         boolean prevNotCascade,
375         StringBuffer cycleString,
376         String currentRefTableName, //the name of the table we are referring too.
377 boolean isSelfReferencingFk,
378         int currentSelfRefValue
379         )
380         throws StandardException
381     {
382
383         Integer rAction;
384
385         String refTableName = refTd.getSchemaName() + "." + refTd.getName();
386
387
388         /*
389         ** Validate the new referentail action value with respect to the
390         ** already existing connections to this table we gathered from
391         ** the getCurrentDeleteConnections() call.
392         */
393
394         if(checkImmediateRefTable)
395         {
396             rAction = ((Integer )dch.get(refTableName));
397             
398             // check possible invalide cases incase of self referencing foreign key
399 if(isSelfReferencingFk)
400             {
401                 //All the relation ship referring to a table should have the
402 //same refaction except incase of SET NULL. In this case
403 //it is the same table , so we have to check with existing self
404 //referencing actions.
405 if(currentSelfRefValue != -1)
406                 {
407                     if(currentSelfRefValue != refActionType)
408                     {
409                         //If there is a SET NULL relation ship we can not have any
410 // other relation ship with it.
411 if(currentSelfRefValue == StatementType.RA_SETNULL)
412                             throw
413                                 generateError(SQLState.LANG_CANT_BE_DEPENDENT_ESELF,
414                                           myConstraintName, currentRefTableName);
415                         else
416                         {
417                                 /*
418                                 ** case where we can cleary say what the
419                                 ** referential actions should be. Like,
420                                 ** if there is NO ACTION relationsip
421                                 **already, new relation ship also shold be NO ACTION.
422                                 */
423                             throw
424                                 generateError(SQLState.LANG_DELETE_RULE_MUSTBE_ESELF,
425                                               myConstraintName, currentSelfRefValue);
426                         }
427                     }else
428                     {
429                         //more than one ON DELET SET NULL to the same table is not allowed
430 if(currentSelfRefValue == StatementType.RA_SETNULL &&
431                            refActionType == StatementType.RA_SETNULL)
432                         {
433                             throw
434                                 generateError(SQLState.LANG_CANT_BE_DEPENDENT_ESELF,
435                                               myConstraintName, currentRefTableName);
436                         }
437                     }
438                 }
439
440                 /*
441                 ** If the new releation ship is self referencing and if
442                 ** the current existing relation ship to other tables is
443                 ** CASCADE type them new self reference should be of type
444                 ** CASCADE, otherwise we should throw error.
445                 */
446
447                 if(isSelfReferencingFk && dch.contains(new Integer (StatementType.RA_CASCADE)) &&
448                    refActionType!= StatementType.RA_CASCADE)
449                 {
450                     throw
451                         generateError(SQLState.LANG_DELETE_RULE_MUSTBE_ECASCADE,
452                                       myConstraintName,StatementType.RA_CASCADE);
453                 }
454
455                 //end of possible error case scenarios for self reference key additions
456 return;
457             }
458         
459             //cases where the new reference is referring to another table
460
461             //check whether it matched with existing self references.
462 // If A self-referencing constraint exists with a delete rule of
463 // SET NULL, NO ACTION or RESTRICT. We can not add CASCADE
464 // relationship with another table.
465
466             if(currentSelfRefValue != -1)
467             {
468                 if(refActionType == StatementType.RA_CASCADE &&
469                    currentSelfRefValue != StatementType.RA_CASCADE)
470                 {
471                     throw generateError(SQLState.LANG_DELETE_RULE_CANT_BE_CASCADE_ESELF, myConstraintName);
472                     
473                 }
474
475             }
476
477             
478             //check for the cases with existing relationships to the
479 //referenced table
480 if(rAction != null)
481             {
482                 checkForMultiplePathInvalidCases(rAction.intValue(),
483                                                   refActionType,
484                                                   myConstraintName,currentRefTableName);
485             }
486
487             
488             //mark the current connect to the reference table to identify the cycle.
489 if(refActionType != StatementType.RA_CASCADE)
490             {
491                 prevNotCascade = true;
492             }
493             
494             /*
495             ** cycle string is used to keep track of the referential actions of
496             ** the nodes we visited, this is required to make sure that in case
497             ** of cycles , all the nodes in the cycle have same type of
498             ** referential action.
499             **/
500             cycleString = cycleString.append(refActionType);
501         }
502
503
504         boolean passedInPrevNotCascade = prevNotCascade;
505
506         //delete connection is broken for if we see ON DELET SET NULL link
507 // one level deeper than the table we are adding the foreing key
508 //Where as to check for cycles we need to go for more level also;
509 // To check cases like CASCADE CASCADE SET NULL cycle is not valid.
510 //Following variable is used make the distinction.
511 boolean multiPathCheck = true;
512
513         // check for cases where the new connection we are forming to the
514 // reference table could create invalid any cycles or mutiple paths
515 // with the delete-connections the referencing table might have already.
516 ConstraintDescriptorList refCDL = dd.getConstraintDescriptors(refTd);
517         int refCDLSize = refCDL.size();
518         for (int index = 0; index < refCDLSize; index++)
519         {
520             ConstraintDescriptor cd = refCDL.elementAt(index);
521
522             if ((cd instanceof ForeignKeyConstraintDescriptor))
523             {
524                 ForeignKeyConstraintDescriptor fkcd = (ForeignKeyConstraintDescriptor) cd;
525                 String constraintName = fkcd.getConstraintName();
526                 int raDeleteRule = fkcd.getRaDeleteRule();
527                 int raUpdateRule = fkcd.getRaUpdateRule();
528                 
529                 ReferencedKeyConstraintDescriptor refcd =
530                     fkcd.getReferencedConstraint();
531                 TableDescriptor nextRefTd = refcd.getTableDescriptor();
532
533                 //if we are not cascading, check whether the link before
534 //this was cascade or not. If we travel through two NON CASCADE ACTION
535 //links then the delete connection is broken(only a delete can have further
536 //referential effects)
537 if(raDeleteRule != StatementType.RA_CASCADE)
538                 {
539                     if(prevNotCascade)
540                     {
541                         prevNotCascade = passedInPrevNotCascade;
542                         continue;
543                     }
544                     else
545                     {
546                         prevNotCascade = true;
547                         multiPathCheck = false;
548                     }
549
550                 }
551
552                 //check whether the current link is a self referencing one
553 boolean isSelfRefLink = fkcd.isSelfReferencingFK();
554                 
555                 //check for this is non self referencing cycles case
556 //In cases of cycle, whole cycle should have the same refAction
557 // value. Other wise we should throw an exception
558 cycleString = cycleString.append(raDeleteRule);
559                 boolean isFormingCycle = (nextRefTd.getUUID().equals(actualTd.getUUID()));
560                 if(isFormingCycle)
561                 {
562                     //make sure that all the nodes in the cycle have the same
563 //referential action value, otherwise we should throw an error.
564 for(int i = 0 ; i < cycleString.length(); i++)
565                     {
566                         int otherRefAction = Character.getNumericValue(cycleString.charAt(i));
567                         if(otherRefAction != refActionType)
568                         {
569                             //cases where one of the existing relation ships in
570 //the cycle is not cascade , so we can not have
571 // cascade relation ship.
572 if(otherRefAction != StatementType.RA_CASCADE)
573                             {
574                                 throw generateError(SQLState.LANG_DELETE_RULE_CANT_BE_CASCADE_ECYCLE, myConstraintName);
575                             }
576                             else
577                             {
578                                 //possibly all the other nodes in the cycle has
579 //cascade relationsship , we can not add a non
580 //cascade relation ship.
581 throw
582                                     generateError(SQLState.LANG_CANT_BE_DEPENDENT_ECYCLE,
583                                                   myConstraintName, currentRefTableName);
584                             }
585                         }
586                     }
587                 }
588
589
590                 
591
592                 String nextRefTableName = nextRefTd.getSchemaName() + "." + nextRefTd.getName();
593                 rAction = ((Integer )ech.get(nextRefTableName));
594                 if(rAction != null)
595                 {
596                     /*
597                     ** If the table name has entry in the hash table means, there
598                     ** is already a path to this table exists from the table
599                     ** the new foreign key relation ship is being formed.
600                     ** Note: refValue in the hash table is how the table we are
601                     ** adding the new relationsship is going to affected not
602                     ** current path refvalue.
603                     **/
604                     if(!isSelfRefLink && multiPathCheck)
605                         checkForMultiplePathInvalidCases(rAction.intValue(),
606                                                          refActionType,
607                                                          myConstraintName,currentRefTableName);
608
609                 }else
610                 {
611                     rAction = ((Integer )dch.get(nextRefTableName));
612                     if(rAction == null)
613                     {
614                         if(multiPathCheck)
615                             dch.put(nextRefTableName, (new Integer (refActionType)));
616                         if(!isSelfRefLink)
617                         {
618                             validateDeleteConnection(dd, actualTd, nextRefTd,
619                                                  refActionType, dch, ech, false,
620                                                  myConstraintName,prevNotCascade,
621                                                  cycleString, currentRefTableName,
622                                                  isSelfReferencingFk, currentSelfRefValue);
623                         }
624                     }
625                 }
626                 prevNotCascade = passedInPrevNotCascade;
627                 //removes the char added for the current call
628 cycleString.setLength(cycleString.length() -1);
629                 
630             }
631         }
632     }
633
634
635     /*
636     **Check whether the mulitple path case is valid or not following
637     ** cases are invalid:
638     ** case 1: The relationship causes the table to be delete-connected to
639     ** the indicated table through multiple relationships and the
640     ** delete rule of the existing relationship is SET NULL.
641     ** case 2: The relationship would cause the table to be
642     ** delete-connected to the same table through multiple
643     ** relationships and such relationships must have the same
644     ** delete rule (NO ACTION, RESTRICT or CASCADE).
645     ** case 3: The relationship would cause another table to be
646     ** delete-connected to the same table through multiple paths
647     ** with different delete rules or with delete rule equal to SET NULL.
648     **/
649
650     private static void checkForMultiplePathInvalidCases(int currentRefAction,
651                                                           int refActionType,
652                                                           String myConstraintName,
653                                                           String currentRefTableName)
654         throws StandardException
655     {
656
657         //All the relation ship referring to a table should have the
658 //same refaction except incase of SET NULL
659 if(currentRefAction != refActionType)
660         {
661
662             //If there is a SET NULL relation ship we can not have any
663 // other relation ship with it.
664 if(currentRefAction == StatementType.RA_SETNULL)
665                 throw generateError(SQLState.LANG_CANT_BE_DEPENDENT_MPATH,
666                                     myConstraintName, currentRefTableName);
667             else
668                 //This error say what the delete rule must be for the
669 // foreign key be valid
670 throw generateError(SQLState.LANG_DELETE_RULE_MUSTBE_MPATH,
671                                     myConstraintName, currentRefAction);
672
673         }else
674         {
675             //more than one ON DELET SET NULL to the same table is not allowed
676 if(currentRefAction == StatementType.RA_SETNULL &&
677                refActionType == StatementType.RA_SETNULL)
678             {
679                 throw
680                     generateError(SQLState.LANG_CANT_BE_DEPENDENT_MPATH,
681                                   myConstraintName, currentRefTableName);
682             }
683         }
684     }
685
686
687
688     /*
689     ** Check whether the delete rule of FOREIGN KEY must not be CASCADE because
690     ** the new relationship would cause another table to be delete-connected to
691     ** the same table through multiple paths with different delete rules or with
692     ** delete rule equal to SET NULL.
693     **
694     ** For example :
695     ** t1
696     ** CASCADE / \ CASCADE
697     ** / \
698     ** t2 t3
699     ** \ /
700     ** SET NULL \ / CASCADE (Can we add this one ? NO)
701     ** \ /
702                            \t4/
703     **
704     ** existing links:
705     ** t2 references t1 ON DELETE CASCADE (fkey1)
706     ** t3 references t1 ON DELETE CASCADE (fkey2)
707     ** t2 reference t4 ON DELETE SET NULL (fkey3)
708     ** Now if if try to add a new link i.e
709     ** t4 references t3 ON DELETE SET NULL (fkey4)
710     ** Say if we add it, then if we execute 'delete from t1'
711     ** Because of referential actions , we will try to delete a row through
712     ** one path and tries to update through another path.
713     ** Nothing in standard that say whether we are suppose to delete the row
714     ** or update the row. DB2UDB raises error when we try to create the
715     ** foreign key fkey4, cloudscape also does the same.
716     **
717     ** How we catch the error case ?
718     ** Point to note here is the table(t4) we are adding the foreign key does
719     ** not have a problem in this scenarion because we are adding a
720     ** a CASACDE link , some other table(t2) that is referring
721     ** can get multiple referential action paths. We can not
722     ** this error case for self referencing links.
723     ** Algorithm:
724     ** -Gather the foreign keys that are
725     ** referring(ReferencedKeyConstraintDescriptor) to the table we are adding
726     ** foreign key, in our example case we get (fkey3 - table t2 -t4 link)
727     ** for each ReferencedKeyConstraintDescriptor
728     ** {
729     ** 1)find the delete connections of the referring table.
730     ** [getCurrentDeleteConnections() will return this hash table]
731     ** 2) we already have collected the Delete connections
732     ** in validDeleteConnections() for the actual table we are adding the
733     ** foreign key.
734     ** 3) Now check whether the referring table is also
735     ** referring any table that the table we are adding
736     ** foreign key has delete connection.
737     **
738     ** for each table referring table delete connection hash table
739     ** {
740     ** if it is there in the actual table delete connection hash table
741     ** {
742     ** //In our example case we find t1 in both the hash tables.
743     ** make sure we are having valid referential action
744     ** from the existing path and the new path we got from
745     ** new foreign key relation ship.
746     ** //In our example case t2 has CASCADE relations with t1
747     ** //Because of new foreign key added we also get
748     ** //SET NULL relation ship with t1. This is not valid
749     ** //so we should throw error.
750     ** }
751     ** }
752     ** }
753     **/
754
755
756     private static void checkForAnyExistingDeleteConnectionViolations
757     (
758      DataDictionary dd,
759      TableDescriptor td,
760      int refActionType,
761      Hashtable newDconnHashTable,
762      String myConstraintName
763      )
764     throws StandardException
765     {
766
767         //We need to check for the condition in this function only when we are
768 //adding ref action of type CASCADE
769 if(refActionType != StatementType.RA_CASCADE)
770             return;
771         
772         //find the tables that are referring to the table we
773 //are adding the foreign key and check whether we violate their existing rules.
774 String addTableName = td.getSchemaName() + "." + td.getName();;
775         ConstraintDescriptorList refCDL = dd.getConstraintDescriptors(td);
776         int refCDLSize = refCDL.size();
777         for (int index = 0; index < refCDLSize; index++)
778         {
779             ConstraintDescriptor cd = refCDL.elementAt(index);
780
781             if ((cd instanceof ReferencedKeyConstraintDescriptor))
782             {
783                 ConstraintDescriptorList fkcdl = dd.getActiveConstraintDescriptors
784                     ( ((ReferencedKeyConstraintDescriptor)cd).getForeignKeyConstraints(ConstraintDescriptor.ALL));
785     
786                 int size = fkcdl.size();
787                 if (size == 0)
788                 {
789                     continue;
790                 }
791                 
792                 //Note: More than one table can refer to the same
793 //ReferencedKeyConstraintDescriptor, so we need to find all the tables.
794 Hashtable dConnHashtable = new Hashtable ();
795                 for (int inner = 0; inner < size; inner++)
796                 {
797                     ForeignKeyConstraintDescriptor fkcd = (ForeignKeyConstraintDescriptor) fkcdl.elementAt(inner);
798                     TableDescriptor fktd = fkcd.getTableDescriptor();
799                     //Delete rule that we have to the table we are adding the
800 // foreign key relation shop
801 int raDeleteRuleToAddTable = fkcd.getRaDeleteRule();
802
803                     //This check should not be done on self referencing references.
804 if(!fkcd.isSelfReferencingFK())
805                     {
806
807                         //gather the delete connections of the table that is
808 //referring to the table we are adding foreign key relation ship
809
810                         getCurrentDeleteConnections(dd, fktd, -1, dConnHashtable, false, true);
811
812                         /*
813                         **Find out if we introduced more than one delete connection
814                         **paths to the table that are referring the table we adding
815                         **the foreign key relatiosn ship.
816                         **If we have multiple paths they should have the same type
817                         **referential action and only one SET NULL path.
818                         **/
819
820                         for (Enumeration e = dConnHashtable.keys() ; e.hasMoreElements() ;)
821                         {
822                             String tName = (String ) e.nextElement();
823                             //we should not check for the table name to which we are
824 //adding the foreign key relation ship.
825 if(!tName.equals(addTableName))
826                             {
827                                 if(newDconnHashTable.containsKey(tName))
828                                 {
829                                     int currentDeleteRule = ((Integer ) dConnHashtable.get(tName)).intValue();
830                                     if((currentDeleteRule == StatementType.RA_SETNULL
831                                         && raDeleteRuleToAddTable == StatementType.RA_SETNULL) ||
832                                        currentDeleteRule != raDeleteRuleToAddTable)
833                                     {
834                                         throw
835                                             generateError(SQLState.LANG_DELETE_RULE_CANT_BE_CASCADE_MPATH,
836                                                           myConstraintName);
837                                     }
838                                 }
839                             }
840                         }
841                     }
842                     //same hash table can be used for the other referring tables
843 //so clear the hash table.
844 dConnHashtable.clear();
845                 }
846             }
847         }
848     }
849
850
851     
852     private static StandardException generateError(String messageId,
853                                                    String myConstraintName)
854     {
855         String message = MessageService.getTextMessage(messageId);
856         return StandardException.newException(SQLState.LANG_DELETE_RULE_VIOLATION,
857                                                 myConstraintName, message);
858     }
859
860     private static StandardException generateError(String messageId,
861                                                    String myConstraintName,
862                                                    int raRule)
863     {
864         String raRuleStringId;
865         switch (raRule){
866         case StatementType.RA_CASCADE:
867             raRuleStringId = SQLState.LANG_DELETE_RULE_CASCADE;
868             break;
869         case StatementType.RA_RESTRICT:
870             raRuleStringId = SQLState.LANG_DELETE_RULE_RESTRICT;
871                 break;
872         case StatementType.RA_NOACTION:
873             raRuleStringId = SQLState.LANG_DELETE_RULE_NOACTION;
874             break;
875         case StatementType.RA_SETNULL:
876             raRuleStringId = SQLState.LANG_DELETE_RULE_SETNULL;
877             break;
878         case StatementType.RA_SETDEFAULT:
879             raRuleStringId = SQLState.LANG_DELETE_RULE_SETDEFAULT;
880             break;
881         default:
882             raRuleStringId =SQLState.LANG_DELETE_RULE_NOACTION ; // NO ACTION (default value)
883 }
884
885         String raRuleMessageString = MessageService.getTextMessage(raRuleStringId);
886         String message = MessageService.getTextMessage(messageId, raRuleMessageString);
887         return StandardException.newException(SQLState.LANG_DELETE_RULE_VIOLATION,
888                                                 myConstraintName, message);
889     }
890
891     private static StandardException generateError(String messageId,
892                                                    String myConstraintName,
893                                                    String refTableName)
894     {
895
896         String message = MessageService.getTextMessage(messageId, refTableName);
897         return StandardException.newException(SQLState.LANG_DELETE_RULE_VIOLATION,
898                                                 myConstraintName, message);
899     }
900
901 }
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags