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.File;
009: import java.io.FileOutputStream;
010: import java.io.IOException;
011: import java.io.InputStream;
012: import java.io.InputStreamReader;
013: import java.io.LineNumberReader;
014: import java.io.PrintStream;
015: import java.sql.Connection;
016: import java.sql.PreparedStatement;
017: import java.sql.ResultSet;
018: import java.sql.ResultSetMetaData;
019: import java.sql.SQLException;
020: import java.sql.Statement;
021: import java.util.ArrayList;
022:
023: import org.h2.test.TestAll;
024: import org.h2.test.TestBase;
025: import org.h2.util.StringUtils;
026:
027: /**
028: * This test runs a SQL script file and compares the output with the expected
029: * output.
030: */
031: public class TestScript extends TestBase {
032:
033: private boolean failFast;
034:
035: private boolean alwaysReconnect;
036: private Connection conn;
037: private Statement stat;
038: private LineNumberReader in;
039: private int line;
040: private PrintStream out;
041: private ArrayList result = new ArrayList();
042: private String putBack;
043: private StringBuffer errors;
044: private ArrayList statements;
045: private String fileName = "org/h2/test/test.in.txt";
046:
047: public ArrayList getAllStatements(TestAll conf, String file)
048: throws Exception {
049: config = conf;
050: fileName = file;
051: statements = new ArrayList();
052: test();
053: return statements;
054: }
055:
056: public void test() throws Exception {
057: if (config.networked && config.big) {
058: return;
059: }
060: alwaysReconnect = false;
061: testScript();
062: if (!config.memory) {
063: if (config.big) {
064: alwaysReconnect = true;
065: testScript();
066: }
067: }
068: }
069:
070: public void testScript() throws Exception {
071: deleteDb("script");
072: String outFile = "test.out.txt";
073: String inFile = fileName;
074: conn = getConnection("script");
075: stat = conn.createStatement();
076: out = new PrintStream(new FileOutputStream(outFile));
077: errors = new StringBuffer();
078: testFile(inFile);
079: conn.close();
080: out.close();
081: if (errors.length() > 0) {
082: throw new Exception("errors:\n" + errors.toString());
083: } else {
084: new File(outFile).delete();
085: }
086: }
087:
088: private String readLine() throws IOException {
089: if (putBack != null) {
090: String s = putBack;
091: putBack = null;
092: return s;
093: }
094: while (true) {
095: String s = in.readLine();
096: if (s == null) {
097: return s;
098: }
099: s = s.trim();
100: if (s.length() > 0) {
101: return s;
102: }
103: }
104: }
105:
106: private void testFile(String inFile) throws Exception {
107: InputStream is = getClass().getClassLoader()
108: .getResourceAsStream(inFile);
109: in = new LineNumberReader(new InputStreamReader(is, "Cp1252"));
110: StringBuffer buff = new StringBuffer();
111: while (true) {
112: String sql = readLine();
113: if (sql == null) {
114: break;
115: }
116: if (sql.startsWith("--")) {
117: write(sql);
118: } else if (sql.startsWith(">")) {
119: // do nothing
120: } else if (sql.endsWith(";")) {
121: write(sql);
122: buff.append(sql.substring(0, sql.length() - 1));
123: sql = buff.toString();
124: buff = new StringBuffer();
125: process(sql);
126: } else {
127: write(sql);
128: buff.append(sql);
129: buff.append('\n');
130: }
131: }
132: }
133:
134: private boolean containsTempTables() throws SQLException {
135: ResultSet rs = conn.getMetaData().getTables(null, null, null,
136: new String[] { "TABLE" });
137: while (rs.next()) {
138: String sql = rs.getString("SQL");
139: if (sql != null) {
140: if (sql.indexOf("TEMPORARY") >= 0) {
141: return true;
142: }
143: }
144: }
145: return false;
146: }
147:
148: private void process(String sql) throws Exception {
149: if (alwaysReconnect) {
150: if (!containsTempTables()) {
151: boolean autocommit = conn.getAutoCommit();
152: if (autocommit) {
153: conn.close();
154: conn = getConnection("script");
155: conn.setAutoCommit(autocommit);
156: stat = conn.createStatement();
157: }
158: }
159: }
160: if (statements != null) {
161: statements.add(sql);
162: }
163: if (sql.indexOf('?') == -1) {
164: processStatement(sql);
165: } else {
166: String param = readLine();
167: write(param);
168: if (!param.equals("{")) {
169: throw new Error("expected '{', got " + param + " in "
170: + sql);
171: }
172: try {
173: PreparedStatement prep = conn.prepareStatement(sql);
174: int count = 0;
175: while (true) {
176: param = readLine();
177: write(param);
178: if (param.startsWith("}")) {
179: break;
180: }
181: count += processPrepared(sql, prep, param);
182: }
183: writeResult("update count: " + count, null);
184: } catch (SQLException e) {
185: writeException(e);
186: }
187: }
188: write("");
189: }
190:
191: private void setParameter(PreparedStatement prep, int i,
192: String param) throws SQLException {
193: if (param.equalsIgnoreCase("null")) {
194: param = null;
195: }
196: prep.setString(i, param);
197: }
198:
199: private int processPrepared(String sql, PreparedStatement prep,
200: String param) throws Exception {
201: try {
202: StringBuffer buff = new StringBuffer();
203: int index = 0;
204: for (int i = 0; i < param.length(); i++) {
205: char c = param.charAt(i);
206: if (c == ',') {
207: setParameter(prep, ++index, buff.toString());
208: buff = new StringBuffer();
209: } else if (c == '"') {
210: while (true) {
211: c = param.charAt(++i);
212: if (c == '"') {
213: break;
214: }
215: buff.append(c);
216: }
217: } else if (c > ' ') {
218: buff.append(c);
219: }
220: }
221: if (buff.length() > 0) {
222: setParameter(prep, ++index, buff.toString());
223: }
224: if (prep.execute()) {
225: writeResultSet(sql, prep.getResultSet());
226: return 0;
227: }
228: return prep.getUpdateCount();
229: } catch (SQLException e) {
230: writeException(e);
231: return 0;
232: }
233: }
234:
235: private int processStatement(String sql) throws Exception {
236: try {
237: if (stat.execute(sql)) {
238: writeResultSet(sql, stat.getResultSet());
239: } else {
240: int count = stat.getUpdateCount();
241: writeResult(
242: count < 1 ? "ok" : "update count: " + count,
243: null);
244: }
245: } catch (SQLException e) {
246: writeException(e);
247: }
248: return 0;
249: }
250:
251: private String formatString(String s) {
252: if (s == null) {
253: return "null";
254: }
255: return s.replace('\n', ' ');
256: }
257:
258: private void writeResultSet(String sql, ResultSet rs)
259: throws Exception {
260: boolean ordered = StringUtils.toLowerEnglish(sql).indexOf(
261: "order by") >= 0;
262: ResultSetMetaData meta = rs.getMetaData();
263: int len = meta.getColumnCount();
264: int[] max = new int[len];
265: String[] head = new String[len];
266: for (int i = 0; i < len; i++) {
267: String label = formatString(meta.getColumnLabel(i + 1));
268: max[i] = label.length();
269: head[i] = label;
270: }
271: result.clear();
272: while (rs.next()) {
273: String[] row = new String[len];
274: for (int i = 0; i < len; i++) {
275: String data = formatString(rs.getString(i + 1));
276: if (max[i] < data.length()) {
277: max[i] = data.length();
278: }
279: row[i] = data;
280: }
281: result.add(row);
282: }
283: rs.close();
284: writeResult(format(head, max), null);
285: writeResult(format(null, max), null);
286: String[] array = new String[result.size()];
287: for (int i = 0; i < result.size(); i++) {
288: array[i] = format((String[]) result.get(i), max);
289: }
290: if (!ordered) {
291: sort(array);
292: }
293: int i = 0;
294: for (; i < array.length; i++) {
295: writeResult(array[i], null);
296: }
297: writeResult((ordered ? "rows (ordered): " : "rows: ") + i, null);
298: }
299:
300: private String format(String[] row, int[] max) throws Exception {
301: int length = max.length;
302: StringBuffer buff = new StringBuffer();
303: for (int i = 0; i < length; i++) {
304: if (i > 0) {
305: buff.append(' ');
306: }
307: if (row == null) {
308: for (int j = 0; j < max[i]; j++) {
309: buff.append('-');
310: }
311: } else {
312: int len = row[i].length();
313: buff.append(row[i]);
314: if (i < length - 1) {
315: for (int j = len; j < max[i]; j++) {
316: buff.append(' ');
317: }
318: }
319: }
320: }
321: return buff.toString();
322: }
323:
324: private void writeException(SQLException e) throws Exception {
325: writeResult("exception", e);
326: }
327:
328: private void writeResult(String s, SQLException e) throws Exception {
329: checkNotGeneralException(e);
330: s = ("> " + s).trim();
331: String compare = readLine();
332: if (compare != null && compare.startsWith(">")) {
333: if (!compare.equals(s)) {
334: errors.append("line: ");
335: errors.append(line);
336: errors.append("\n" + "exp: ");
337: errors.append(compare);
338: errors.append("\n" + "got: ");
339: errors.append(s);
340: errors.append("\n");
341: if (e != null) {
342: TestBase.logError("script", e);
343: }
344: if (failFast) {
345: TestBase.logError(errors.toString(), null);
346: conn.close();
347: System.exit(1);
348: }
349: }
350: } else {
351: putBack = compare;
352: }
353: write(s);
354:
355: }
356:
357: private void write(String s) throws Exception {
358: line++;
359: out.println(s);
360: }
361:
362: private void sort(String[] a) {
363: for (int i = 1, j, len = a.length; i < len; i++) {
364: String t = a[i];
365: for (j = i - 1; j >= 0 && t.compareTo(a[j]) < 0; j--) {
366: a[j + 1] = a[j];
367: }
368: a[j + 1] = t;
369: }
370: }
371:
372: }
|