KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > h2 > constraint > ConstraintReferential


1 /*
2  * Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html).
3  * Initial Developer: H2 Group
4  */

5 package org.h2.constraint;
6
7 import java.sql.SQLException JavaDoc;
8
9 import org.h2.command.Parser;
10 import org.h2.command.Prepared;
11 import org.h2.engine.Session;
12 import org.h2.expression.Expression;
13 import org.h2.expression.Parameter;
14 import org.h2.index.Cursor;
15 import org.h2.index.Index;
16 import org.h2.message.Message;
17 import org.h2.result.Row;
18 import org.h2.result.SearchRow;
19 import org.h2.schema.Schema;
20 import org.h2.table.Column;
21 import org.h2.table.Table;
22 import org.h2.util.ObjectArray;
23 import org.h2.util.StringUtils;
24 import org.h2.value.Value;
25 import org.h2.value.ValueNull;
26
27 /**
28  * @author Thomas
29  */

30
31 public class ConstraintReferential extends Constraint {
32     public static final int RESTRICT = 0, CASCADE = 1, SET_DEFAULT = 2, SET_NULL = 3;
33     
34     // TODO referential constraints: maybe use a own class for self referencing constraints
35

36     private int deleteAction;
37     private int updateAction;
38     private Table refTable;
39     private Index index;
40     private Index refIndex;
41     private boolean indexOwner;
42     private boolean refIndexOwner;
43     protected Column[] columns;
44     protected Column[] refColumns;
45     private String JavaDoc deleteSQL, updateSQL;
46     private boolean skipOwnTable;
47     
48     public ConstraintReferential(Schema schema, int id, String JavaDoc name, Table table) {
49         super(schema, id, name, table);
50     }
51     
52     public String JavaDoc getConstraintType() {
53         return Constraint.REFERENTIAL;
54     }
55     
56     private void appendAction(StringBuffer JavaDoc buff, int action) {
57         switch(action) {
58         case CASCADE:
59             buff.append("CASCADE");
60             break;
61         case SET_DEFAULT:
62             buff.append("SET DEFAULT");
63             break;
64         case SET_NULL:
65             buff.append("SET NULL");
66             break;
67         default:
68             throw Message.getInternalError("action="+action);
69         }
70     }
71     
72     public String JavaDoc getCreateSQLForCopy(Table table, String JavaDoc quotedName) {
73         return getCreateSQLForCopy(table, refTable, quotedName, true);
74     }
75     
76     public String JavaDoc getCreateSQLForCopy(Table table, Table refTable, String JavaDoc quotedName, boolean internalIndex) {
77         StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
78         buff.append("ALTER TABLE ");
79         String JavaDoc mainTable = table.getSQL();
80         buff.append(mainTable);
81         buff.append(" ADD CONSTRAINT ");
82         buff.append(quotedName);
83         if(comment != null) {
84             buff.append(" COMMENT ");
85             buff.append(StringUtils.quoteStringSQL(comment));
86         }
87         Column[] cols = columns;
88         Column[] refCols = refColumns;
89         buff.append(" FOREIGN KEY(");
90         for(int i=0; i<cols.length; i++) {
91             if(i>0) {
92                 buff.append(", ");
93             }
94             buff.append(cols[i].getSQL());
95         }
96         buff.append(")");
97         if(internalIndex && indexOwner && table == this.table) {
98             buff.append(" INDEX ");
99             buff.append(index.getSQL());
100         }
101         buff.append(" REFERENCES ");
102         String JavaDoc quotedRefTable;
103         if(this.table == this.refTable) {
104             // self-referencing constraints: need to use new table
105
quotedRefTable = table.getSQL();
106         } else {
107             quotedRefTable = refTable.getSQL();
108         }
109         buff.append(quotedRefTable);
110         buff.append("(");
111         for(int i=0; i<refCols.length; i++) {
112             if(i>0) {
113                 buff.append(", ");
114             }
115             buff.append(refCols[i].getSQL());
116         }
117         buff.append(")");
118         if(internalIndex && refIndexOwner && table == this.table) {
119             buff.append(" INDEX ");
120             buff.append(refIndex.getSQL());
121         }
122         if(deleteAction != RESTRICT) {
123             buff.append(" ON DELETE ");
124             appendAction(buff, deleteAction);
125         }
126         if(updateAction != RESTRICT) {
127             buff.append(" ON UPDATE ");
128             appendAction(buff, updateAction);
129         }
130         return buff.toString();
131     }
132     
133     public String JavaDoc getShortDescription() {
134         StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
135         buff.append(getName());
136         buff.append(": ");
137         buff.append(table.getSQL());
138         buff.append(" FOREIGN KEY(");
139         for(int i=0; i<columns.length; i++) {
140             if(i>0) {
141                 buff.append(", ");
142             }
143             buff.append(columns[i].getSQL());
144         }
145         buff.append(")");
146         buff.append(" REFERENCES ");
147         buff.append(refTable.getSQL());
148         buff.append("(");
149         for(int i=0; i<refColumns.length; i++) {
150             if(i>0) {
151                 buff.append(", ");
152             }
153             buff.append(refColumns[i].getSQL());
154         }
155         buff.append(")");
156         return buff.toString();
157     }
158
159     public String JavaDoc getCreateSQLWithoutIndexes() {
160         return getCreateSQLForCopy(table, refTable, getSQL(), false);
161     }
162
163     public String JavaDoc getCreateSQL() {
164         return getCreateSQLForCopy(table, getSQL());
165     }
166     
167     public void setColumns(Column[] cols) {
168         columns = cols;
169     }
170     
171     public Column[] getColumns() {
172         return columns;
173     }
174
175     public void setRefColumns(Column[] refCols) {
176         refColumns = refCols;
177     }
178     
179     public Column[] getRefColumns() {
180         return refColumns;
181     }
182
183     public void setRefTable(Table refTable) {
184         this.refTable = refTable;
185         if(refTable.getTemporary()) {
186             setTemporary(true);
187         }
188     }
189
190     public void setIndex(Index index, boolean isOwner) {
191         this.index = index;
192         this.indexOwner = isOwner;
193     }
194
195     public void setRefIndex(Index refIndex, boolean isRefOwner) {
196         this.refIndex = refIndex;
197         this.refIndexOwner = isRefOwner;
198     }
199     
200     public void removeChildrenAndResources(Session session) throws SQLException JavaDoc {
201         table.removeConstraint(this);
202         refTable.removeConstraint(this);
203         if(indexOwner) {
204             database.removeSchemaObject(session, index);
205         }
206         if(refIndexOwner) {
207             database.removeSchemaObject(session, refIndex);
208         }
209         refTable = null;
210         index = null;
211         refIndex = null;
212         columns = null;
213         refColumns = null;
214         deleteSQL = null;
215         updateSQL = null;
216         table = null;
217         invalidate();
218     }
219     
220     public void checkRow(Session session, Table t, Row oldRow, Row newRow) throws SQLException JavaDoc {
221         if(!table.getCheckForeignKeyConstraints() || !refTable.getCheckForeignKeyConstraints()) {
222             return;
223         }
224         if(t == table) {
225             if(!skipOwnTable) {
226                 checkRowOwnTable(session, newRow);
227             }
228         }
229         if(t == refTable) {
230             checkRowRefTable(session, oldRow, newRow);
231         }
232     }
233     
234     private void checkRowOwnTable(Session session, Row newRow) throws SQLException JavaDoc {
235         if(newRow==null) {
236             return;
237         }
238         boolean containsNull = false;
239         for(int i=0; i<columns.length; i++) {
240             int idx = columns[i].getColumnId();
241             Value v = newRow.getValue(idx);
242             if(v == ValueNull.INSTANCE) {
243                 containsNull = true;
244                 break;
245             }
246         }
247         if(containsNull) {
248             return;
249         }
250         if(refTable == table) {
251             // special case self referencing constraints: check the inserted row first
252
boolean self = true;
253             for(int i=0; i<columns.length; i++) {
254                 int idx = columns[i].getColumnId();
255                 Value v = newRow.getValue(idx);
256                 Column refCol = refColumns[i];
257                 int refIdx = refCol.getColumnId();
258                 Value r = newRow.getValue(refIdx);
259                 if(!database.areEqual(r, v)) {
260                     self = false;
261                     break;
262                 }
263             }
264             if(self) {
265                 return;
266             }
267         }
268         Row check = refTable.getTemplateRow();
269         for(int i=0; i<columns.length; i++) {
270             int idx = columns[i].getColumnId();
271             Value v = newRow.getValue(idx);
272             Column refCol = refColumns[i];
273             int refIdx = refCol.getColumnId();
274             check.setValue(refIdx, v.convertTo(refCol.getType()));
275         }
276         if(!found(session, refIndex, check)) {
277             throw Message.getSQLException(Message.CHECK_CONSTRAINT_VIOLATED_1, getShortDescription());
278         }
279     }
280     
281     private boolean found(Session session, Index index, SearchRow check) throws SQLException JavaDoc {
282         Cursor cursor = index.find(session, check, check);
283         while(cursor.next()) {
284             Row found = cursor.get();
285             Column[] cols = index.getColumns();
286             boolean allEqual = true;
287             for(int i=0; i<columns.length && i<cols.length; i++) {
288                 int idx = cols[i].getColumnId();
289                 Value c = check.getValue(idx);
290                 Value f = found.getValue(idx);
291                 if(database.compareTypeSave(c, f) != 0) {
292                     allEqual = false;
293                     break;
294                 }
295             }
296             if(allEqual) {
297                 return true;
298             }
299         }
300         return false;
301     }
302     
303     private boolean isEqual(Row oldRow, Row newRow) throws SQLException JavaDoc {
304         return refIndex.compareRows(oldRow, newRow)==0;
305     }
306     
307     private void checkRow(Session session, Row oldRow) throws SQLException JavaDoc {
308         if(refTable == table) {
309             // special case self referencing constraints: check the deleted row first
310
boolean self = true;
311             for(int i=0; i<columns.length; i++) {
312                 Column refCol = refColumns[i];
313                 int refIdx = refCol.getColumnId();
314                 Value v = oldRow.getValue(refIdx);
315                 int idx = columns[i].getColumnId();
316                 Value r = oldRow.getValue(idx);
317                 if(!database.areEqual(r, v)) {
318                     self = false;
319                     break;
320                 }
321             }
322             if(self) {
323                 return;
324             }
325         }
326         SearchRow check = table.getTemplateSimpleRow(false);
327         for(int i=0; i<columns.length; i++) {
328             Column refCol = refColumns[i];
329             int refIdx = refCol.getColumnId();
330             Column col = columns[i];
331             int idx = col.getColumnId();
332             Value v = oldRow.getValue(refIdx).convertTo(col.getType());
333             check.setValue(idx, v);
334         }
335         if(found(session, index, check)) {
336             throw Message.getSQLException(Message.CHECK_CONSTRAINT_VIOLATED_1, getShortDescription());
337         }
338     }
339     
340     private void checkRowRefTable(Session session, Row oldRow, Row newRow) throws SQLException JavaDoc {
341         if(oldRow == null) {
342             // this is an insert
343
return;
344         }
345         if(newRow != null && isEqual(oldRow, newRow)) {
346             // on an update, if both old and new are the same, don't do anything
347
return;
348         }
349         if(newRow == null) {
350             // this is a delete
351
if(deleteAction == RESTRICT) {
352                 checkRow(session, oldRow);
353             } else {
354                 int i=deleteAction == CASCADE ? 0 : columns.length;
355                 Prepared deleteCommand = getDelete(session);
356                 setWhere(deleteCommand, i, oldRow);
357                 updateWithSkipCheck(deleteCommand);
358             }
359         } else {
360             // this is an update
361
if(updateAction == RESTRICT) {
362                 checkRow(session, oldRow);
363             } else {
364                 Prepared updateCommand = getUpdate(session);
365                 if(updateAction == CASCADE) {
366                     ObjectArray params = updateCommand.getParameters();
367                     for(int i=0; i<columns.length; i++) {
368                         Parameter param = (Parameter)params.get(i);
369                         Column refCol = refColumns[i];
370                         param.setValue(newRow.getValue(refCol.getColumnId()));
371                     }
372                 }
373                 setWhere(updateCommand, columns.length, oldRow);
374                 updateWithSkipCheck(updateCommand);
375             }
376         }
377     }
378     
379     private void updateWithSkipCheck(Prepared prep) throws SQLException JavaDoc {
380         // TODO constraints: maybe delay the update or support delayed checks (until commit)
381
try {
382             // TODO multithreaded kernel: this works only if nobody else updates this or the ref table at the same time
383
skipOwnTable = true;
384             prep.update();
385         } finally {
386             skipOwnTable = false;
387         }
388     }
389     
390     void setWhere(Prepared command, int pos, Row row) {
391         for(int i=0; i<refColumns.length; i++) {
392             int idx = refColumns[i].getColumnId();
393             Value v = row.getValue(idx);
394             ObjectArray params = command.getParameters();
395             Parameter param = (Parameter) params.get(pos + i);
396             param.setValue(v);
397         }
398     }
399     
400     public int getDeleteAction() {
401         return deleteAction;
402     }
403
404     public void setDeleteAction(Session session, int action) throws SQLException JavaDoc {
405         if(action == deleteAction) {
406             return;
407         }
408         if(deleteAction != RESTRICT) {
409             throw Message.getSQLException(Message.CONSTRAINT_ALREADY_EXISTS_1, "ON DELETE");
410         }
411         this.deleteAction = action;
412         StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
413         if(action == CASCADE) {
414             buff.append("DELETE FROM ");
415             buff.append(table.getSQL());
416         } else {
417             appendUpdate(buff);
418         }
419         appendWhere(buff);
420         deleteSQL = buff.toString();
421     }
422     
423     private Prepared getUpdate(Session session) throws SQLException JavaDoc {
424         return prepare(session, updateSQL, updateAction);
425     }
426
427     private Prepared getDelete(Session session) throws SQLException JavaDoc {
428         return prepare(session, deleteSQL, deleteAction);
429     }
430
431     public int getUpdateAction() {
432         return updateAction;
433     }
434
435     public void setUpdateAction(Session session, int action) throws SQLException JavaDoc {
436         if(action == updateAction) {
437             return;
438         }
439         if(updateAction != RESTRICT) {
440             throw Message.getSQLException(Message.CONSTRAINT_ALREADY_EXISTS_1, "ON UPDATE");
441         }
442         this.updateAction = action;
443         StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
444         appendUpdate(buff);
445         appendWhere(buff);
446         updateSQL = buff.toString();
447     }
448     
449     private Prepared prepare(Session session, String JavaDoc sql, int action) throws SQLException JavaDoc {
450         Prepared command = session.prepare(sql);
451         if(action != CASCADE) {
452             ObjectArray params = command.getParameters();
453             for(int i=0; i<columns.length; i++) {
454                 Column column = columns[i];
455                 Parameter param = (Parameter) params.get(i);
456                 Value value;
457                 if(action == SET_NULL) {
458                     value = ValueNull.INSTANCE;
459                 } else {
460                     Expression expr = column.getDefaultExpression();
461                     if(expr ==null) {
462                         throw Message.getSQLException(Message.NO_DEFAULT_SET_1, column.getName());
463                     }
464                     value = expr.getValue(session);
465                 }
466                 param.setValue(value);
467             }
468         }
469         return command;
470     }
471     
472     private void appendUpdate(StringBuffer JavaDoc buff) {
473         buff.append("UPDATE ");
474         buff.append(table.getSQL());
475         buff.append(" SET ");
476         for(int i=0; i<columns.length; i++) {
477             if(i>0) {
478                 buff.append(" , ");
479             }
480             Column column = columns[i];
481             buff.append(Parser.quoteIdentifier(column.getName()));
482             buff.append("=?");
483         }
484     }
485     
486     private void appendWhere(StringBuffer JavaDoc buff) {
487         buff.append(" WHERE ");
488         for(int i=0; i<columns.length; i++) {
489             if(i>0) {
490                 buff.append(" AND ");
491             }
492             Column column = columns[i];
493             buff.append(Parser.quoteIdentifier(column.getName()));
494             buff.append("=?");
495         }
496     }
497     
498     public Table getRefTable() {
499         return refTable;
500     }
501
502     public boolean usesIndex(Index ind) {
503         return ind == index || ind == refIndex;
504     }
505
506     public boolean containsColumn(Column col) {
507         for(int i=0; i<columns.length; i++) {
508             if(columns[i] == col) {
509                 return true;
510             }
511         }
512         for(int i=0; i<refColumns.length; i++) {
513             if(refColumns[i] == col) {
514                 return true;
515             }
516         }
517         return false;
518     }
519
520     public boolean isBefore() {
521         return false;
522     }
523     
524 }
525
Popular Tags