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.io.BufferedInputStream;
009: import java.io.File;
010: import java.io.FileOutputStream;
011: import java.io.FileReader;
012: import java.io.InputStream;
013: import java.sql.Blob;
014: import java.sql.Connection;
015: import java.sql.DatabaseMetaData;
016: import java.sql.PreparedStatement;
017: import java.sql.ResultSet;
018: import java.sql.SQLException;
019: import java.sql.Statement;
020: import java.sql.Types;
021: import java.util.ArrayList;
022: import java.util.Properties;
023:
024: import org.h2.api.AggregateFunction;
025: import org.h2.test.TestBase;
026: import org.h2.tools.SimpleResultSet;
027: import org.h2.util.IOUtils;
028:
029: /**
030: * Tests for user defined functions and aggregates.
031: */
032: public class TestFunctions extends TestBase {
033:
034: private Statement stat;
035:
036: public void test() throws Exception {
037: testAggregate();
038: testFunctions();
039: testFileRead();
040: }
041:
042: public void testFileRead() throws Exception {
043: Connection conn = getConnection("functions");
044: stat = conn.createStatement();
045: File f = new File(baseDir + "/test.txt");
046: Properties prop = System.getProperties();
047: FileOutputStream out = new FileOutputStream(f);
048: prop.store(out, "");
049: out.close();
050: ResultSet rs = stat.executeQuery("SELECT LENGTH(FILE_READ('"
051: + baseDir + "/test.txt')) LEN");
052: rs.next();
053: check(f.length(), rs.getInt(1));
054: rs = stat.executeQuery("SELECT FILE_READ('" + baseDir
055: + "/test.txt') PROP");
056: rs.next();
057: Properties p2 = new Properties();
058: p2.load(rs.getBinaryStream(1));
059: check(prop.size(), p2.size());
060: rs = stat.executeQuery("SELECT FILE_READ('" + baseDir
061: + "/test.txt', NULL) PROP");
062: rs.next();
063: String ps = rs.getString(1);
064: FileReader r = new FileReader(f);
065: String ps2 = IOUtils.readStringAndClose(r, -1);
066: check(ps, ps2);
067: f.delete();
068: conn.close();
069: }
070:
071: public static class MedianString implements AggregateFunction {
072:
073: private ArrayList list = new ArrayList();
074:
075: public void add(Object value) throws SQLException {
076: list.add(value.toString());
077: }
078:
079: public Object getResult() throws SQLException {
080: return list.get(list.size() / 2);
081: }
082:
083: public int getType(int[] inputType) throws SQLException {
084: return Types.VARCHAR;
085: }
086:
087: public void init(Connection conn) throws SQLException {
088: }
089:
090: }
091:
092: private void testAggregate() throws Exception {
093: deleteDb("functions");
094: Connection conn = getConnection("functions");
095: stat = conn.createStatement();
096: stat.execute("CREATE AGGREGATE MEDIAN FOR \""
097: + MedianString.class.getName() + "\"");
098: stat.execute("CREATE AGGREGATE IF NOT EXISTS MEDIAN FOR \""
099: + MedianString.class.getName() + "\"");
100: ResultSet rs = stat
101: .executeQuery("SELECT MEDIAN(X) FROM SYSTEM_RANGE(1, 9)");
102: rs.next();
103: check("5", rs.getString(1));
104: conn.close();
105:
106: if (config.memory) {
107: return;
108: }
109:
110: conn = getConnection("functions");
111: stat = conn.createStatement();
112: rs = stat
113: .executeQuery("SELECT MEDIAN(X) FROM SYSTEM_RANGE(1, 9)");
114: DatabaseMetaData meta = conn.getMetaData();
115: rs = meta.getProcedures(null, null, "MEDIAN");
116: check(rs.next());
117: checkFalse(rs.next());
118: rs = stat.executeQuery("SCRIPT");
119: boolean found = false;
120: while (rs.next()) {
121: String sql = rs.getString(1);
122: if (sql.indexOf("MEDIAN") >= 0) {
123: found = true;
124: }
125: }
126: check(found);
127: stat.execute("DROP AGGREGATE MEDIAN");
128: stat.execute("DROP AGGREGATE IF EXISTS MEDIAN");
129: conn.close();
130: }
131:
132: private void testFunctions() throws Exception {
133: deleteDb("functions");
134: Connection conn = getConnection("functions");
135: stat = conn.createStatement();
136: test("abs(null)", null);
137: test("abs(1)", "1");
138: test("abs(1)", "1");
139:
140: stat
141: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
142: stat.execute("CREATE ALIAS ADD_ROW FOR \""
143: + getClass().getName() + ".addRow\"");
144: ResultSet rs;
145: rs = stat.executeQuery("CALL ADD_ROW(1, 'Hello')");
146: rs.next();
147: check(rs.getInt(1), 1);
148: rs = stat.executeQuery("SELECT * FROM TEST");
149: rs.next();
150: check(rs.getInt(1), 1);
151: check(rs.getString(2), "Hello");
152: checkFalse(rs.next());
153:
154: rs = stat.executeQuery("CALL ADD_ROW(2, 'World')");
155:
156: stat.execute("CREATE ALIAS SELECT_F FOR \""
157: + getClass().getName() + ".select\"");
158: rs = stat
159: .executeQuery("CALL SELECT_F('SELECT * FROM TEST ORDER BY ID')");
160: check(rs.getMetaData().getColumnCount(), 2);
161: rs.next();
162: check(rs.getInt(1), 1);
163: check(rs.getString(2), "Hello");
164: rs.next();
165: check(rs.getInt(1), 2);
166: check(rs.getString(2), "World");
167: checkFalse(rs.next());
168:
169: rs = stat
170: .executeQuery("SELECT NAME FROM SELECT_F('SELECT * FROM TEST ORDER BY NAME') ORDER BY NAME DESC");
171: check(rs.getMetaData().getColumnCount(), 1);
172: rs.next();
173: check(rs.getString(1), "World");
174: rs.next();
175: check(rs.getString(1), "Hello");
176: checkFalse(rs.next());
177:
178: rs = stat
179: .executeQuery("SELECT SELECT_F('SELECT * FROM TEST WHERE ID=' || ID) FROM TEST ORDER BY ID");
180: check(rs.getMetaData().getColumnCount(), 1);
181: rs.next();
182: check("((1, Hello))", rs.getString(1));
183: rs.next();
184: check("((2, World))", rs.getString(1));
185: checkFalse(rs.next());
186:
187: rs = stat
188: .executeQuery("SELECT SELECT_F('SELECT * FROM TEST ORDER BY ID') FROM DUAL");
189: check(rs.getMetaData().getColumnCount(), 1);
190: rs.next();
191: check("((1, Hello), (2, World))", rs.getString(1));
192: checkFalse(rs.next());
193:
194: try {
195: rs = stat.executeQuery("CALL SELECT_F('ERROR')");
196: error();
197: } catch (SQLException e) {
198: check("42001", e.getSQLState());
199: }
200:
201: stat.execute("CREATE ALIAS SIMPLE FOR \""
202: + getClass().getName() + ".simpleResultSet\"");
203: rs = stat.executeQuery("CALL SIMPLE(2, 1,1,1,1,1,1,1)");
204: check(rs.getMetaData().getColumnCount(), 2);
205: rs.next();
206: check(rs.getInt(1), 0);
207: check(rs.getString(2), "Hello");
208: rs.next();
209: check(rs.getInt(1), 1);
210: check(rs.getString(2), "World");
211: checkFalse(rs.next());
212:
213: rs = stat
214: .executeQuery("SELECT * FROM SIMPLE(1, 1,1,1,1,1,1,1)");
215: check(rs.getMetaData().getColumnCount(), 2);
216: rs.next();
217: check(rs.getInt(1), 0);
218: check(rs.getString(2), "Hello");
219: checkFalse(rs.next());
220:
221: stat.execute("CREATE ALIAS ARRAY FOR \"" + getClass().getName()
222: + ".getArray\"");
223: rs = stat.executeQuery("CALL ARRAY()");
224: check(rs.getMetaData().getColumnCount(), 2);
225: rs.next();
226: check(rs.getInt(1), 0);
227: check(rs.getString(2), "Hello");
228: checkFalse(rs.next());
229:
230: stat.execute("CREATE ALIAS ROOT FOR \"" + getClass().getName()
231: + ".root\"");
232: rs = stat.executeQuery("CALL ROOT(9)");
233: rs.next();
234: check(rs.getInt(1), 3);
235: checkFalse(rs.next());
236:
237: stat.execute("CREATE ALIAS MAX_ID FOR \""
238: + getClass().getName() + ".selectMaxId\"");
239: rs = stat.executeQuery("CALL MAX_ID()");
240: rs.next();
241: check(rs.getInt(1), 2);
242: checkFalse(rs.next());
243:
244: rs = stat.executeQuery("SELECT * FROM MAX_ID()");
245: rs.next();
246: check(rs.getInt(1), 2);
247: checkFalse(rs.next());
248:
249: rs = stat
250: .executeQuery("CALL CASE WHEN -9 < 0 THEN 0 ELSE ROOT(-9) END");
251: rs.next();
252: check(rs.getInt(1), 0);
253: checkFalse(rs.next());
254:
255: stat.execute("CREATE ALIAS blob2stream FOR \""
256: + getClass().getName() + ".blob2stream\"");
257: stat.execute("CREATE ALIAS stream2stream FOR \""
258: + getClass().getName() + ".stream2stream\"");
259: stat
260: .execute("CREATE TABLE TEST_BLOB(ID INT PRIMARY KEY, VALUE BLOB)");
261: stat.execute("INSERT INTO TEST_BLOB VALUES(0, null)");
262: stat
263: .execute("INSERT INTO TEST_BLOB VALUES(1, 'edd1f011edd1f011edd1f011')");
264: rs = stat
265: .executeQuery("SELECT blob2stream(VALUE) FROM TEST_BLOB");
266: while (rs.next()) {
267: }
268: rs.close();
269: rs = stat
270: .executeQuery("SELECT stream2stream(VALUE) FROM TEST_BLOB");
271: while (rs.next()) {
272: // ignore
273: }
274:
275: stat.execute("CREATE ALIAS NULL_RESULT FOR \""
276: + getClass().getName() + ".nullResultSet\"");
277: rs = stat.executeQuery("CALL NULL_RESULT()");
278: check(rs.getMetaData().getColumnCount(), 1);
279: rs.next();
280: check(rs.getString(1), null);
281: checkFalse(rs.next());
282:
283: conn.close();
284: }
285:
286: void test(String sql, String value) throws Exception {
287: ResultSet rs = stat.executeQuery("CALL " + sql);
288: rs.next();
289: String s = rs.getString(1);
290: check(value, s);
291: }
292:
293: public static BufferedInputStream blob2stream(Blob value)
294: throws SQLException {
295: if (value == null) {
296: return null;
297: }
298: BufferedInputStream bufferedInStream = new BufferedInputStream(
299: value.getBinaryStream());
300: return bufferedInStream;
301: }
302:
303: public static BufferedInputStream stream2stream(InputStream value)
304: throws SQLException {
305: if (value == null) {
306: return null;
307: }
308: BufferedInputStream bufferedInStream = new BufferedInputStream(
309: value);
310: return bufferedInStream;
311: }
312:
313: public static int addRow(Connection conn, int id, String name)
314: throws SQLException {
315: conn.createStatement().execute(
316: "INSERT INTO TEST VALUES(" + id + ", '" + name + "')");
317: ResultSet rs = conn.createStatement().executeQuery(
318: "SELECT COUNT(*) FROM TEST");
319: rs.next();
320: int result = rs.getInt(1);
321: rs.close();
322: return result;
323: }
324:
325: public static ResultSet select(Connection conn, String sql)
326: throws SQLException {
327: Statement stat = conn.createStatement(
328: ResultSet.TYPE_SCROLL_INSENSITIVE,
329: ResultSet.CONCUR_READ_ONLY);
330: return stat.executeQuery(sql);
331: }
332:
333: public static ResultSet selectMaxId(Connection conn)
334: throws SQLException {
335: return conn.createStatement().executeQuery(
336: "SELECT MAX(ID) FROM TEST");
337: }
338:
339: public static Object[] getArray() {
340: return new Object[] { new Integer(0), "Hello" };
341: }
342:
343: public static ResultSet nullResultSet(Connection conn)
344: throws SQLException {
345: PreparedStatement statement = conn
346: .prepareStatement("select null from system_range(1,1)");
347: return statement.executeQuery();
348: }
349:
350: /**
351: * Test method to create a simple result set.
352: *
353: * @param count the number of rows
354: * @param ip an int
355: * @param bp a boolean
356: * @param fp a float
357: * @param dp a double
358: * @param lp a long
359: * @param byParam a byte
360: * @param sp a short
361: * @return a result set
362: */
363: public static ResultSet simpleResultSet(Integer count, int ip,
364: boolean bp, float fp, double dp, long lp, byte byParam,
365: short sp) throws SQLException {
366: SimpleResultSet rs = new SimpleResultSet();
367: rs.addColumn("ID", Types.INTEGER, 10, 0);
368: rs.addColumn("NAME", Types.VARCHAR, 255, 0);
369: if (count == null) {
370: if (ip != 0 || bp || fp != 0.0 || dp != 0.0 || sp != 0
371: || lp != 0 || byParam != 0) {
372: throw new Error("params not 0/false");
373: }
374: }
375: if (count != null) {
376: if (ip != 1 || !bp || fp != 1.0 || dp != 1.0 || sp != 1
377: || lp != 1 || byParam != 1) {
378: throw new Error("params not 1/true");
379: }
380: if (count.intValue() >= 1) {
381: rs.addRow(new Object[] { new Integer(0), "Hello" });
382: }
383: if (count.intValue() >= 2) {
384: rs.addRow(new Object[] { new Integer(1), "World" });
385: }
386: }
387: return rs;
388: }
389:
390: public static int root(int value) {
391: if (value < 0) {
392: TestBase.logError("function called but should not", null);
393: }
394: return (int) Math.sqrt(value);
395: }
396:
397: }
|