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.PreparedStatement;
010: import java.sql.ResultSet;
011: import java.sql.Statement;
012: import java.util.ArrayList;
013:
014: import org.h2.store.FileLister;
015: import org.h2.test.TestBase;
016:
017: /**
018: * Test for big result sets.
019: */
020: public class TestBigResult extends TestBase {
021: public void test() throws Exception {
022: if (config.memory) {
023: return;
024: }
025: testLargeSubquery();
026: testLargeUpdateDelete();
027: testCloseConnectionDelete();
028: testOrderGroup();
029: testLimitBufferedResult();
030: }
031:
032: private void testLargeSubquery() throws Exception {
033: deleteDb("bigResult");
034: Connection conn = getConnection("bigResult");
035: Statement stat = conn.createStatement();
036: int len = getSize(1000, 4000);
037: stat.execute("SET MAX_MEMORY_ROWS " + (len / 10));
038: stat
039: .execute("CREATE TABLE RECOVERY(TRANSACTION_ID INT, SQL_STMT VARCHAR)");
040: stat
041: .execute("INSERT INTO RECOVERY "
042: + "SELECT X, CASE MOD(X, 2) WHEN 0 THEN 'commit' ELSE 'begin' END "
043: + "FROM SYSTEM_RANGE(1, " + len + ")");
044: ResultSet rs = stat
045: .executeQuery("SELECT * FROM RECOVERY WHERE SQL_STMT LIKE 'begin%' AND "
046: + "TRANSACTION_ID NOT IN(SELECT TRANSACTION_ID FROM RECOVERY "
047: + "WHERE SQL_STMT='commit' OR SQL_STMT='rollback')");
048: int count = 0, last = 1;
049: while (rs.next()) {
050: check(last, rs.getInt(1));
051: last += 2;
052: count++;
053: }
054: check(len / 2, count);
055: conn.close();
056: }
057:
058: private void testLargeUpdateDelete() throws Exception {
059: deleteDb("bigResult");
060: Connection conn = getConnection("bigResult");
061: Statement stat = conn.createStatement();
062: int len = getSize(10000, 100000);
063: stat.execute("SET MAX_OPERATION_MEMORY 4096");
064: stat
065: .execute("CREATE TABLE TEST AS SELECT * FROM SYSTEM_RANGE(1, "
066: + len + ")");
067: stat.execute("UPDATE TEST SET X=X+1");
068: stat.execute("DELETE FROM TEST");
069: conn.close();
070: }
071:
072: private void testCloseConnectionDelete() throws Exception {
073: deleteDb("bigResult");
074: Connection conn = getConnection("bigResult");
075: Statement stat = conn.createStatement();
076: stat.execute("SET MAX_MEMORY_ROWS 2");
077: ResultSet rs = stat
078: .executeQuery("SELECT * FROM SYSTEM_RANGE(1, 100)");
079: while (rs.next()) {
080: // ignore
081: }
082: // rs.close();
083: conn.close();
084: deleteDb("bigResult");
085: ArrayList files = FileLister.getDatabaseFiles(baseDir,
086: "bigResult", true);
087: if (files.size() > 0) {
088: error("file not deleted: " + files.get(0));
089: }
090: }
091:
092: private void testLimitBufferedResult() throws Exception {
093: deleteDb("bigResult");
094: Connection conn = getConnection("bigResult");
095: Statement stat = conn.createStatement();
096: stat.execute("DROP TABLE IF EXISTS TEST");
097: stat.execute("CREATE TABLE TEST(ID INT)");
098: for (int i = 0; i < 200; i++) {
099: stat.execute("INSERT INTO TEST(ID) VALUES(" + i + ")");
100: }
101: stat.execute("SET MAX_MEMORY_ROWS 100");
102: ResultSet rs;
103: rs = stat
104: .executeQuery("select id from test order by id limit 10 offset 85");
105: for (int i = 85; rs.next(); i++) {
106: check(i, rs.getInt(1));
107: }
108: rs = stat
109: .executeQuery("select id from test order by id limit 10 offset 95");
110: for (int i = 95; rs.next(); i++) {
111: check(i, rs.getInt(1));
112: }
113: rs = stat
114: .executeQuery("select id from test order by id limit 10 offset 105");
115: for (int i = 105; rs.next(); i++) {
116: check(i, rs.getInt(1));
117: }
118: conn.close();
119: }
120:
121: private void testOrderGroup() throws Exception {
122: deleteDb("bigResult");
123: Connection conn = getConnection("bigResult");
124: Statement stat = conn.createStatement();
125: stat.execute("DROP TABLE IF EXISTS TEST");
126: stat.execute("CREATE TABLE TEST(" + "ID INT PRIMARY KEY, "
127: + "Name VARCHAR(255), " + "FirstName VARCHAR(255), "
128: + "Points INT," + "LicenseID INT)");
129: int len = getSize(10, 5000);
130: PreparedStatement prep = conn
131: .prepareStatement("INSERT INTO TEST VALUES(?, ?, ?, ?, ?)");
132: for (int i = 0; i < len; i++) {
133: prep.setInt(1, i);
134: prep.setString(2, "Name " + i);
135: prep.setString(3, "First Name " + i);
136: prep.setInt(4, i * 10);
137: prep.setInt(5, i * i);
138: prep.execute();
139: }
140: conn.close();
141: conn = getConnection("bigResult");
142: stat = conn.createStatement();
143: stat.setMaxRows(len + 1);
144: ResultSet rs = stat
145: .executeQuery("SELECT * FROM TEST ORDER BY ID");
146: for (int i = 0; i < len; i++) {
147: rs.next();
148: check(i, rs.getInt(1));
149: check("Name " + i, rs.getString(2));
150: check("First Name " + i, rs.getString(3));
151: check(i * 10, rs.getInt(4));
152: check(i * i, rs.getInt(5));
153: }
154:
155: stat.setMaxRows(len + 1);
156: rs = stat
157: .executeQuery("SELECT * FROM TEST WHERE ID >= 1000 ORDER BY ID");
158: for (int i = 1000; i < len; i++) {
159: rs.next();
160: check(i, rs.getInt(1));
161: check("Name " + i, rs.getString(2));
162: check("First Name " + i, rs.getString(3));
163: check(i * 10, rs.getInt(4));
164: check(i * i, rs.getInt(5));
165: }
166:
167: stat.execute("SET MAX_MEMORY_ROWS 2");
168: rs = stat
169: .executeQuery("SELECT Name, SUM(ID) FROM TEST GROUP BY NAME");
170: while (rs.next()) {
171: rs.getString(1);
172: rs.getInt(2);
173: }
174:
175: conn.setAutoCommit(false);
176: stat.setMaxRows(0);
177: stat.execute("SET MAX_MEMORY_ROWS 0");
178: stat
179: .execute("CREATE TABLE DATA(ID INT, NAME VARCHAR_IGNORECASE(255))");
180: prep = conn.prepareStatement("INSERT INTO DATA VALUES(?, ?)");
181: for (int i = 0; i < len; i++) {
182: prep.setInt(1, i);
183: prep.setString(2, "" + i / 200);
184: prep.execute();
185: }
186: Statement s2 = conn.createStatement(
187: ResultSet.TYPE_SCROLL_INSENSITIVE,
188: ResultSet.CONCUR_UPDATABLE);
189: rs = s2.executeQuery("SELECT NAME FROM DATA");
190: rs.last();
191: conn.setAutoCommit(true);
192:
193: rs = s2.executeQuery("SELECT NAME FROM DATA ORDER BY ID");
194: while (rs.next()) {
195: // do nothing
196: }
197:
198: conn.close();
199: }
200:
201: }
|