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.math.BigDecimal;
009: import java.sql.Connection;
010: import java.sql.DriverManager;
011: import java.sql.ResultSet;
012: import java.sql.ResultSetMetaData;
013: import java.sql.SQLException;
014: import java.sql.Statement;
015: import java.sql.Timestamp;
016:
017: import org.h2.test.TestBase;
018:
019: /**
020: * Tests the linked table feature (CREATE LINKED TABLE).
021: */
022: public class TestLinkedTable extends TestBase {
023:
024: public void test() throws Exception {
025: // testLinkAutoAdd();
026: testLinkOtherSchema();
027: testLinkDrop();
028: testLinkSchema();
029: testLinkEmitUpdates();
030: testLinkTable();
031: testLinkTwoTables();
032: }
033:
034: // this is not a bug, it is the documented behavior
035: // private void testLinkAutoAdd() throws Exception {
036: // Class.forName("org.h2.Driver");
037: // Connection ca =
038: // DriverManager.getConnection("jdbc:h2:mem:one", "sa", "sa");
039: // Connection cb =
040: // DriverManager.getConnection("jdbc:h2:mem:two", "sa", "sa");
041: // Statement sa = ca.createStatement();
042: // Statement sb = cb.createStatement();
043: // sa.execute("CREATE TABLE ONE (X NUMBER)");
044: // sb.execute(
045: // "CALL LINK_SCHEMA('GOOD', '', " +
046: // "'jdbc:h2:mem:one', 'sa', 'sa', 'PUBLIC'); ");
047: // sb.executeQuery("SELECT * FROM GOOD.ONE");
048: // sa.execute("CREATE TABLE TWO (X NUMBER)");
049: // sb.executeQuery("SELECT * FROM GOOD.TWO"); // FAILED
050: // ca.close();
051: // cb.close();
052: // }
053:
054: private void testLinkOtherSchema() throws Exception {
055: Class.forName("org.h2.Driver");
056: Connection ca = DriverManager.getConnection("jdbc:h2:mem:one",
057: "sa", "sa");
058: Connection cb = DriverManager.getConnection("jdbc:h2:mem:two",
059: "sa", "sa");
060: Statement sa = ca.createStatement();
061: Statement sb = cb.createStatement();
062: sa.execute("CREATE TABLE GOOD (X NUMBER)");
063: sa.execute("CREATE SCHEMA S");
064: sa.execute("CREATE TABLE S.BAD (X NUMBER)");
065: sb
066: .execute("CALL LINK_SCHEMA('G', '', 'jdbc:h2:mem:one', 'sa', 'sa', 'PUBLIC'); ");
067: sb
068: .execute("CALL LINK_SCHEMA('B', '', 'jdbc:h2:mem:one', 'sa', 'sa', 'S'); ");
069: sb.executeQuery("SELECT * FROM G.GOOD"); //OK
070: sb.executeQuery("SELECT * FROM B.BAD"); // FAILED
071: ca.close();
072: cb.close();
073: }
074:
075: private void testLinkTwoTables() throws Exception {
076: Class.forName("org.h2.Driver");
077: Connection conn = DriverManager.getConnection(
078: "jdbc:h2:mem:one", "sa", "sa");
079: Statement stat = conn.createStatement();
080: stat.execute("CREATE SCHEMA Y");
081: stat.execute("CREATE TABLE A( C INT)");
082: stat.execute("INSERT INTO A VALUES(1)");
083: stat.execute("CREATE TABLE Y.A (C INT)");
084: stat.execute("INSERT INTO Y.A VALUES(2)");
085: Connection conn2 = DriverManager
086: .getConnection("jdbc:h2:mem:two");
087: Statement stat2 = conn2.createStatement();
088: stat2
089: .execute("CREATE LINKED TABLE one('org.h2.Driver', 'jdbc:h2:mem:one', 'sa', 'sa', 'Y.A');");
090: stat2
091: .execute("CREATE LINKED TABLE two('org.h2.Driver', 'jdbc:h2:mem:one', 'sa', 'sa', 'A');");
092: ResultSet rs = stat2.executeQuery("SELECT * FROM one");
093: rs.next();
094: check(rs.getInt(1), 2);
095: rs = stat2.executeQuery("SELECT * FROM two");
096: rs.next();
097: check(rs.getInt(1), 1);
098: conn.close();
099: conn2.close();
100: }
101:
102: private void testLinkDrop() throws Exception {
103: Class.forName("org.h2.Driver");
104: Connection connA = DriverManager.getConnection("jdbc:h2:mem:a");
105: Statement statA = connA.createStatement();
106: statA.execute("CREATE TABLE TEST(ID INT)");
107: Connection connB = DriverManager.getConnection("jdbc:h2:mem:b");
108: Statement statB = connB.createStatement();
109: statB
110: .execute("CREATE LINKED TABLE TEST_LINK('', 'jdbc:h2:mem:a', '', '', 'TEST')");
111: connA.close();
112: // the connection should be closed now
113: // (and the table should disappear because the last connection was
114: // closed)
115: statB.execute("DROP TABLE TEST_LINK");
116: connA = DriverManager.getConnection("jdbc:h2:mem:a");
117: statA = connA.createStatement();
118: // table should not exist now
119: statA.execute("CREATE TABLE TEST(ID INT)");
120: connA.close();
121: connB.close();
122: }
123:
124: private void testLinkEmitUpdates() throws Exception {
125: deleteDb("linked1");
126: deleteDb("linked2");
127: Class.forName("org.h2.Driver");
128:
129: Connection conn = DriverManager.getConnection("jdbc:h2:"
130: + baseDir + "/linked1", "sa1", "abc");
131: Statement stat = conn.createStatement();
132: stat
133: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
134:
135: Connection conn2 = DriverManager.getConnection("jdbc:h2:"
136: + baseDir + "/linked2", "sa2", "def");
137: Statement stat2 = conn2.createStatement();
138: String link = "CREATE LINKED TABLE TEST_LINK_U('', 'jdbc:h2:"
139: + baseDir
140: + "/linked1', 'sa1', 'abc', 'TEST') EMIT UPDATES";
141: stat2.execute(link);
142: link = "CREATE LINKED TABLE TEST_LINK_DI('', 'jdbc:h2:"
143: + baseDir + "/linked1', 'sa1', 'abc', 'TEST')";
144: stat2.execute(link);
145: stat2
146: .executeUpdate("INSERT INTO TEST_LINK_U VALUES(1, 'Hello')");
147: stat2
148: .executeUpdate("INSERT INTO TEST_LINK_DI VALUES(2, 'World')");
149: try {
150: stat2.executeUpdate("UPDATE TEST_LINK_U SET ID=ID+1");
151: error();
152: } catch (SQLException e) {
153: checkNotGeneralException(e);
154: }
155: stat2.executeUpdate("UPDATE TEST_LINK_DI SET ID=ID+1");
156: stat2.executeUpdate("UPDATE TEST_LINK_U SET NAME=NAME || ID");
157: ResultSet rs;
158:
159: rs = stat2
160: .executeQuery("SELECT * FROM TEST_LINK_DI ORDER BY ID");
161: rs.next();
162: check(rs.getInt(1), 2);
163: check(rs.getString(2), "Hello2");
164: rs.next();
165: check(rs.getInt(1), 3);
166: check(rs.getString(2), "World3");
167: checkFalse(rs.next());
168:
169: rs = stat2
170: .executeQuery("SELECT * FROM TEST_LINK_U ORDER BY ID");
171: rs.next();
172: check(rs.getInt(1), 2);
173: check(rs.getString(2), "Hello2");
174: rs.next();
175: check(rs.getInt(1), 3);
176: check(rs.getString(2), "World3");
177: checkFalse(rs.next());
178:
179: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
180: rs.next();
181: check(rs.getInt(1), 2);
182: check(rs.getString(2), "Hello2");
183: rs.next();
184: check(rs.getInt(1), 3);
185: check(rs.getString(2), "World3");
186: checkFalse(rs.next());
187:
188: conn.close();
189: conn2.close();
190: }
191:
192: private void testLinkSchema() throws Exception {
193: deleteDb("linked1");
194: deleteDb("linked2");
195: Class.forName("org.h2.Driver");
196:
197: Connection conn = DriverManager.getConnection("jdbc:h2:"
198: + baseDir + "/linked1", "sa1", "abc");
199: Statement stat = conn.createStatement();
200: stat.execute("CREATE TABLE TEST1(ID INT PRIMARY KEY)");
201:
202: Connection conn2 = DriverManager.getConnection("jdbc:h2:"
203: + baseDir + "/linked2", "sa2", "def");
204: Statement stat2 = conn2.createStatement();
205: String link = "CALL LINK_SCHEMA('LINKED', '', 'jdbc:h2:"
206: + baseDir + "/linked1', 'sa1', 'abc', 'PUBLIC')";
207: stat2.execute(link);
208: stat2.executeQuery("SELECT * FROM LINKED.TEST1");
209:
210: stat.execute("CREATE TABLE TEST2(ID INT PRIMARY KEY)");
211: stat2.execute(link);
212: stat2.executeQuery("SELECT * FROM LINKED.TEST1");
213: stat2.executeQuery("SELECT * FROM LINKED.TEST2");
214:
215: conn.close();
216: conn2.close();
217: }
218:
219: private void testLinkTable() throws Exception {
220: deleteDb("linked1");
221: deleteDb("linked2");
222: Class.forName("org.h2.Driver");
223:
224: Connection conn = DriverManager.getConnection("jdbc:h2:"
225: + baseDir + "/linked1", "sa1", "abc");
226: Statement stat = conn.createStatement();
227: stat.execute("CREATE TEMP TABLE TEST_TEMP(ID INT PRIMARY KEY)");
228: stat
229: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(200), XT TINYINT, XD DECIMAL(10,2), XTS TIMESTAMP, XBY BINARY(255), XBO BIT, XSM SMALLINT, XBI BIGINT, XBL BLOB, XDA DATE, XTI TIME, XCL CLOB, XDO DOUBLE)");
230: stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
231: stat
232: .execute("INSERT INTO TEST VALUES(0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)");
233: stat
234: .execute("INSERT INTO TEST VALUES(1, 'Hello', -1, 10.30, '2001-02-03 11:22:33.4455', X'FF0102', TRUE, 3000, 1234567890123456789, X'1122AA', DATE '0002-01-01', TIME '00:00:00', 'J\u00fcrg', 2.25)");
235: testRow(stat, "TEST");
236: stat
237: .execute("INSERT INTO TEST VALUES(2, 'World', 30, 100.05, '2005-12-31 12:34:56.789', X'FFEECC33', FALSE, 1, -1234567890123456789, X'4455FF', DATE '9999-12-31', TIME '23:59:59', 'George', -2.5)");
238: testRow(stat, "TEST");
239: stat.execute("SELECT * FROM TEST_TEMP");
240: conn.close();
241:
242: conn = DriverManager.getConnection("jdbc:h2:" + baseDir
243: + "/linked1", "sa1", "abc");
244: stat = conn.createStatement();
245: testRow(stat, "TEST");
246: try {
247: stat.execute("SELECT * FROM TEST_TEMP");
248: error("temp table must not be persistent");
249: } catch (SQLException e) {
250: checkNotGeneralException(e);
251: }
252: conn.close();
253:
254: conn = DriverManager.getConnection("jdbc:h2:" + baseDir
255: + "/linked2", "sa2", "def");
256: stat = conn.createStatement();
257: stat
258: .execute("CREATE LINKED TABLE IF NOT EXISTS LINK_TEST('org.h2.Driver', 'jdbc:h2:"
259: + baseDir + "/linked1', 'sa1', 'abc', 'TEST')");
260: stat
261: .execute("CREATE LINKED TABLE IF NOT EXISTS LINK_TEST('org.h2.Driver', 'jdbc:h2:"
262: + baseDir + "/linked1', 'sa1', 'abc', 'TEST')");
263: testRow(stat, "LINK_TEST");
264: ResultSet rs = stat.executeQuery("SELECT * FROM LINK_TEST");
265: ResultSetMetaData meta = rs.getMetaData();
266: check(10, meta.getPrecision(1));
267: check(200, meta.getPrecision(2));
268:
269: conn.close();
270: conn = DriverManager.getConnection("jdbc:h2:" + baseDir
271: + "/linked2", "sa2", "def");
272: stat = conn.createStatement();
273:
274: stat
275: .execute("INSERT INTO LINK_TEST VALUES(3, 'Link Test', 30, 100.05, '2005-12-31 12:34:56.789', X'FFEECC33', FALSE, 1, -1234567890123456789, X'4455FF', DATE '9999-12-31', TIME '23:59:59', 'George', -2.5)");
276:
277: rs = stat.executeQuery("SELECT COUNT(*) FROM LINK_TEST");
278: rs.next();
279: check(rs.getInt(1), 4);
280:
281: rs = stat
282: .executeQuery("SELECT COUNT(*) FROM LINK_TEST WHERE NAME='Link Test'");
283: rs.next();
284: check(rs.getInt(1), 1);
285:
286: int uc = stat.executeUpdate("DELETE FROM LINK_TEST WHERE ID=3");
287: check(uc, 1);
288:
289: rs = stat.executeQuery("SELECT COUNT(*) FROM LINK_TEST");
290: rs.next();
291: check(rs.getInt(1), 3);
292:
293: rs = stat
294: .executeQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='LINK_TEST'");
295: rs.next();
296: check(rs.getString("TABLE_TYPE"), "TABLE LINK");
297:
298: rs.next();
299: rs = stat.executeQuery("SELECT * FROM LINK_TEST WHERE ID=0");
300: rs.next();
301: check(rs.getString("NAME") == null && rs.wasNull());
302: check(rs.getString("XT") == null && rs.wasNull());
303: check(rs.getInt("ID") == 0 && !rs.wasNull());
304: check(rs.getBigDecimal("XD") == null && rs.wasNull());
305: check(rs.getTimestamp("XTS") == null && rs.wasNull());
306: check(rs.getBytes("XBY") == null && rs.wasNull());
307: check(!rs.getBoolean("XBO") && rs.wasNull());
308: check(rs.getShort("XSM") == 0 && rs.wasNull());
309: check(rs.getLong("XBI") == 0 && rs.wasNull());
310: check(rs.getString("XBL") == null && rs.wasNull());
311: check(rs.getString("XDA") == null && rs.wasNull());
312: check(rs.getString("XTI") == null && rs.wasNull());
313: check(rs.getString("XCL") == null && rs.wasNull());
314: check(rs.getString("XDO") == null && rs.wasNull());
315: checkFalse(rs.next());
316:
317: stat.execute("DROP TABLE LINK_TEST");
318:
319: stat
320: .execute("CREATE LINKED TABLE LINK_TEST('org.h2.Driver', 'jdbc:h2:"
321: + baseDir
322: + "/linked1', 'sa1', 'abc', '(SELECT COUNT(*) FROM TEST)')");
323: rs = stat.executeQuery("SELECT * FROM LINK_TEST");
324: rs.next();
325: check(rs.getInt(1), 3);
326: checkFalse(rs.next());
327:
328: conn.close();
329:
330: deleteDb("linked1");
331: deleteDb("linked2");
332: }
333:
334: void testRow(Statement stat, String name) throws Exception {
335: ResultSet rs = stat.executeQuery("SELECT * FROM " + name
336: + " WHERE ID=1");
337: rs.next();
338: check(rs.getString("NAME"), "Hello");
339: check(rs.getByte("XT"), -1);
340: BigDecimal bd = rs.getBigDecimal("XD");
341: check(bd.equals(new BigDecimal("10.30")));
342: Timestamp ts = rs.getTimestamp("XTS");
343: String s = ts.toString();
344: check(s, "2001-02-03 11:22:33.4455");
345: check(ts.equals(Timestamp.valueOf("2001-02-03 11:22:33.4455")));
346: check(rs.getBytes("XBY"), new byte[] { (byte) 255, (byte) 1,
347: (byte) 2 });
348: check(rs.getBoolean("XBO"));
349: check(rs.getShort("XSM"), 3000);
350: check(rs.getLong("XBI"), 1234567890123456789L);
351: check(rs.getString("XBL"), "1122aa");
352: check(rs.getString("XDA"), "0002-01-01");
353: check(rs.getString("XTI"), "00:00:00");
354: check(rs.getString("XCL"), "J\u00fcrg");
355: check(rs.getString("XDO"), "2.25");
356:
357: }
358:
359: }
|