001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.db;
007:
008: import java.sql.Connection;
009: import java.sql.ResultSet;
010: import java.sql.SQLException;
011: import java.sql.Statement;
012: import java.util.Arrays;
013: import java.util.HashSet;
014:
015: import org.h2.api.Trigger;
016: import org.h2.test.TestBase;
017:
018: /**
019: * Tests for trigger and constraints.
020: */
021: public class TestTriggersConstraints extends TestBase implements
022: Trigger {
023:
024: private static boolean mustNotCallTrigger;
025:
026: public void test() throws Exception {
027: deleteDb("trigger");
028: testTriggers();
029: testConstraints();
030: }
031:
032: private void testConstraints() throws Exception {
033: Connection conn = getConnection("trigger");
034: Statement stat = conn.createStatement();
035: stat.execute("DROP TABLE IF EXISTS TEST");
036: stat
037: .execute("create table test(id int primary key, parent int)");
038: stat
039: .execute("alter table test add constraint test_parent_id foreign key(parent) references test (id) on delete cascade");
040: stat
041: .execute("insert into test select x, x/2 from system_range(0, 100)");
042: stat.execute("delete from test");
043: checkSingleValue(stat, "select count(*) from test", 0);
044: stat.execute("drop table test");
045: conn.close();
046: }
047:
048: private void testTriggers() throws Exception {
049: mustNotCallTrigger = false;
050: Connection conn = getConnection("trigger");
051: Statement stat = conn.createStatement();
052: stat.execute("DROP TABLE IF EXISTS TEST");
053: stat
054: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
055: // CREATE TRIGGER trigger {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table
056: // [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL triggeredClass
057: stat
058: .execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE BEFORE INSERT ON TEST FOR EACH ROW NOWAIT CALL \""
059: + getClass().getName() + "\"");
060: stat
061: .execute("CREATE TRIGGER IF NOT EXISTS INS_BEFORE BEFORE INSERT ON TEST FOR EACH ROW NOWAIT CALL \""
062: + getClass().getName() + "\"");
063: stat
064: .execute("CREATE TRIGGER INS_AFTER AFTER INSERT ON TEST FOR EACH ROW NOWAIT CALL \""
065: + getClass().getName() + "\"");
066: stat
067: .execute("CREATE TRIGGER UPD_BEFORE BEFORE UPDATE ON TEST FOR EACH ROW NOWAIT CALL \""
068: + getClass().getName() + "\"");
069: stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
070: ResultSet rs;
071: rs = stat.executeQuery("SCRIPT");
072: checkRows(
073: rs,
074: new String[] {
075: "CREATE FORCE TRIGGER PUBLIC.INS_BEFORE BEFORE INSERT ON PUBLIC.TEST FOR EACH ROW NOWAIT CALL \""
076: + getClass().getName() + "\";",
077: "CREATE FORCE TRIGGER PUBLIC.INS_AFTER AFTER INSERT ON PUBLIC.TEST FOR EACH ROW NOWAIT CALL \""
078: + getClass().getName() + "\";",
079: "CREATE FORCE TRIGGER PUBLIC.UPD_BEFORE BEFORE UPDATE ON PUBLIC.TEST FOR EACH ROW NOWAIT CALL \""
080: + getClass().getName() + "\";" });
081: while (rs.next()) {
082: String sql = rs.getString(1);
083: if (sql.startsWith("CREATE TRIGGER")) {
084: System.out.println(sql);
085: }
086: }
087:
088: rs = stat.executeQuery("SELECT * FROM TEST");
089: rs.next();
090: check(rs.getString(2), "Hello-updated");
091: checkFalse(rs.next());
092: stat.execute("UPDATE TEST SET NAME=NAME||'-upd'");
093: rs = stat.executeQuery("SELECT * FROM TEST");
094: rs.next();
095: check(rs.getString(2), "Hello-updated-upd-updated2");
096: checkFalse(rs.next());
097:
098: mustNotCallTrigger = true;
099: stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
100: stat.execute("DROP TRIGGER IF EXISTS INS_BEFORE");
101: try {
102: stat.execute("DROP TRIGGER INS_BEFORE");
103: error();
104: } catch (SQLException e) {
105: checkNotGeneralException(e);
106: }
107: stat.execute("DROP TRIGGER INS_AFTER");
108: stat.execute("DROP TRIGGER UPD_BEFORE");
109: stat.execute("UPDATE TEST SET NAME=NAME||'-upd-no_trigger'");
110: stat
111: .execute("INSERT INTO TEST VALUES(100, 'Insert-no_trigger')");
112: conn.close();
113:
114: conn = getConnection("trigger");
115:
116: mustNotCallTrigger = false;
117: conn.close();
118: }
119:
120: private void checkRows(ResultSet rs, String[] expected)
121: throws Exception {
122: HashSet set = new HashSet(Arrays.asList(expected));
123: while (rs.next()) {
124: set.remove(rs.getString(1));
125: }
126: if (set.size() > 0) {
127: error("set should be empty: " + set);
128: }
129: }
130:
131: private String triggerName;
132:
133: public void fire(Connection conn, Object[] oldRow, Object[] newRow)
134: throws SQLException {
135: if (mustNotCallTrigger) {
136: throw new Error("must not be called now");
137: }
138: if (conn == null) {
139: throw new Error("connection is null");
140: }
141: if (triggerName.startsWith("INS_BEFORE")) {
142: newRow[1] = newRow[1] + "-updated";
143: } else if (triggerName.startsWith("INS_AFTER")) {
144: if (!newRow[1].toString().endsWith("-updated")) {
145: throw new Error("supposed to be updated");
146: }
147: checkCommit(conn);
148: } else if (triggerName.startsWith("UPD_BEFORE")) {
149: newRow[1] = newRow[1] + "-updated2";
150: } else if (triggerName.startsWith("UPD_AFTER")) {
151: if (!newRow[1].toString().endsWith("-updated2")) {
152: throw new Error("supposed to be updated2");
153: }
154: checkCommit(conn);
155: }
156: }
157:
158: private void checkCommit(Connection conn) {
159: try {
160: conn.commit();
161: throw new Error("Commit must not work here");
162: } catch (SQLException e) {
163: try {
164: checkNotGeneralException(e);
165: } catch (Exception e2) {
166: throw new Error("Unexpected: " + e.toString());
167: }
168: }
169: try {
170: conn.createStatement().execute("CREATE TABLE X(ID INT)");
171: throw new Error(
172: "CREATE TABLE WORKED, but implicitly commits");
173: } catch (SQLException e) {
174: try {
175: checkNotGeneralException(e);
176: } catch (Exception e2) {
177: throw new Error("Unexpected: " + e.toString());
178: }
179: }
180: }
181:
182: public void init(Connection conn, String schemaName,
183: String triggerName, String tableName, boolean before,
184: int type) throws SQLException {
185: this .triggerName = triggerName;
186: if (!"TEST".equals(tableName)) {
187: throw new Error("supposed to be TEST");
188: }
189: if ((triggerName.endsWith("AFTER") && before)
190: || (triggerName.endsWith("BEFORE") && !before)) {
191: throw new Error("triggerName: " + triggerName + " before:"
192: + before);
193: }
194: if ((triggerName.startsWith("UPD") && type != UPDATE)
195: || (triggerName.startsWith("INS") && type != INSERT)
196: || (triggerName.startsWith("DEL") && type != DELETE)) {
197: throw new Error("triggerName: " + triggerName + " type:"
198: + type);
199: }
200: }
201:
202: }
|