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:
013: import org.h2.test.TestBase;
014: import org.h2.util.MemoryUtils;
015:
016: /**
017: * Test for big databases.
018: */
019: public class TestBigDb extends TestBase {
020:
021: public void test() throws Exception {
022: if (config.memory) {
023: return;
024: }
025: if (config.networked && config.big) {
026: return;
027: }
028: testLargeTable();
029: testInsert();
030: testLeftSummary();
031: }
032:
033: private void testLargeTable() throws Exception {
034: deleteDb("bigDb");
035: Connection conn = getConnection("bigDb");
036: Statement stat = conn.createStatement();
037: stat.execute("CREATE CACHED TABLE TEST("
038: + "M_CODE CHAR(1) DEFAULT CAST(RAND()*9 AS INT),"
039: + "PRD_CODE CHAR(20) DEFAULT SECURE_RAND(10),"
040: + "ORG_CODE_SUPPLIER CHAR(13) DEFAULT SECURE_RAND(6),"
041: + "PRD_CODE_1 CHAR(14) DEFAULT SECURE_RAND(7),"
042: + "PRD_CODE_2 CHAR(20) DEFAULT SECURE_RAND(10),"
043: + "ORG_CODE CHAR(13) DEFAULT SECURE_RAND(6),"
044: + "SUBSTITUTED_BY CHAR(20) DEFAULT SECURE_RAND(10),"
045: + "SUBSTITUTED_BY_2 CHAR(14) DEFAULT SECURE_RAND(7),"
046: + "SUBSTITUTION_FOR CHAR(20) DEFAULT SECURE_RAND(10),"
047: + "SUBSTITUTION_FOR_2 CHAR(14) DEFAULT SECURE_RAND(7),"
048: + "TEST CHAR(2) DEFAULT SECURE_RAND(1),"
049: + "TEST_2 CHAR(2) DEFAULT SECURE_RAND(1),"
050: + "TEST_3 DECIMAL(7,2) DEFAULT RAND(),"
051: + "PRIMARY_UNIT_CODE CHAR(3) DEFAULT SECURE_RAND(1),"
052: + "RATE_PRICE_ORDER_UNIT DECIMAL(9,3) DEFAULT RAND(),"
053: + "ORDER_UNIT_CODE CHAR(3) DEFAULT SECURE_RAND(1),"
054: + "ORDER_QTY_MIN DECIMAL(6,1) DEFAULT RAND(),"
055: + "ORDER_QTY_LOT_SIZE DECIMAL(6,1) DEFAULT RAND(),"
056: + "ORDER_UNIT_CODE_2 CHAR(3) DEFAULT SECURE_RAND(1),"
057: + "PRICE_GROUP CHAR(20) DEFAULT SECURE_RAND(10),"
058: + "LEAD_TIME INTEGER DEFAULT RAND(),"
059: + "LEAD_TIME_UNIT_CODE CHAR(3) DEFAULT SECURE_RAND(1),"
060: + "PRD_GROUP CHAR(10) DEFAULT SECURE_RAND(5),"
061: + "WEIGHT_GROSS DECIMAL(7,3) DEFAULT RAND(),"
062: + "WEIGHT_UNIT_CODE CHAR(3) DEFAULT SECURE_RAND(1),"
063: + "PACK_UNIT_CODE CHAR(3) DEFAULT SECURE_RAND(1),"
064: + "PACK_LENGTH DECIMAL(7,3) DEFAULT RAND(),"
065: + "PACK_WIDTH DECIMAL(7,3) DEFAULT RAND(),"
066: + "PACK_HEIGHT DECIMAL(7,3) DEFAULT RAND(),"
067: + "SIZE_UNIT_CODE CHAR(3) DEFAULT SECURE_RAND(1),"
068: + "STATUS_CODE CHAR(3) DEFAULT SECURE_RAND(1),"
069: + "INTRA_STAT_CODE CHAR(12) DEFAULT SECURE_RAND(6),"
070: + "PRD_TITLE CHAR(50) DEFAULT SECURE_RAND(25),"
071: + "VALID_FROM DATE DEFAULT NOW(),"
072: + "MOD_DATUM DATE DEFAULT NOW())");
073: int len = getSize(10, 50000);
074: try {
075: PreparedStatement prep = conn
076: .prepareStatement("INSERT INTO TEST(PRD_CODE) VALUES('abc' || ?)");
077: long time = System.currentTimeMillis();
078: for (int i = 0; i < len; i++) {
079: if ((i % 1000) == 0) {
080: long t = System.currentTimeMillis();
081: if (t - time > 1000) {
082: time = t;
083: int free = MemoryUtils.getMemoryFree();
084: println("i: " + i + " free: " + free
085: + " used: "
086: + MemoryUtils.getMemoryUsed());
087: }
088: }
089: prep.setInt(1, i);
090: prep.execute();
091: }
092: stat
093: .execute("CREATE INDEX IDX_TEST_PRD_CODE ON TEST(PRD_CODE)");
094: ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
095: int columns = rs.getMetaData().getColumnCount();
096: while (rs.next()) {
097: for (int i = 0; i < columns; i++) {
098: rs.getString(i + 1);
099: }
100: }
101: } catch (OutOfMemoryError e) {
102: TestBase.logError("memory", e);
103: conn.close();
104: throw e;
105: }
106: conn.close();
107: }
108:
109: private void testLeftSummary() throws Exception {
110: deleteDb("bigDb");
111: Connection conn = getConnection("bigDb");
112: Statement stat = conn.createStatement();
113: stat
114: .execute("CREATE TABLE TEST(ID INT, NEG INT AS -ID, NAME VARCHAR, PRIMARY KEY(ID, NAME))");
115: stat.execute("CREATE INDEX IDX_NEG ON TEST(NEG, NAME)");
116: PreparedStatement prep = conn
117: .prepareStatement("INSERT INTO TEST(ID, NAME) VALUES(?, '1234567890')");
118: int len = getSize(10, 1000);
119: int block = getSize(3, 10);
120: int left, x = 0;
121: for (int i = 0; i < len; i++) {
122: left = x + block / 2;
123: for (int j = 0; j < block; j++) {
124: prep.setInt(1, x++);
125: prep.execute();
126: }
127: stat.execute("DELETE FROM TEST WHERE ID>" + left);
128: ResultSet rs = stat
129: .executeQuery("SELECT COUNT(*) FROM TEST");
130: rs.next();
131: int count = rs.getInt(1);
132: trace("count: " + count);
133: }
134: conn.close();
135: }
136:
137: private void testInsert() throws Exception {
138: deleteDb("bigDb");
139: Connection conn = getConnection("bigDb");
140: Statement stat = conn.createStatement();
141: stat.execute("CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR)");
142: PreparedStatement prep = conn
143: .prepareStatement("INSERT INTO TEST(NAME) VALUES('Hello World')");
144: int len = getSize(1000, 10000);
145: for (int i = 0; i < len; i++) {
146: if (i % 1000 == 0) {
147: println("rows: " + i);
148: Thread.yield();
149: }
150: prep.execute();
151: }
152: conn.close();
153: }
154:
155: }
|