001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.io.IOException;
034: import java.io.PrintStream;
035: import java.sql.SQLException;
036:
037: import org.hsqldb.lib.FileUtil;
038:
039: /** test various text table features
040: *
041: * @author frank.schoenheit@sun.com
042: */
043: public class TestTextTable extends TestBase {
044: java.sql.Statement m_statement;
045: java.sql.Connection m_connection;
046:
047: private class TextTableDescriptor {
048: private String m_name;
049: private String m_columnSpec;
050: private String m_separator;
051: private String m_separatorSpec;
052: private Object[][] m_data;
053:
054: public TextTableDescriptor(String name, String columnSpec,
055: String separator, String separatorSpec, Object[][] data) {
056: m_name = name;
057: m_columnSpec = columnSpec;
058: m_separator = separator;
059: m_separatorSpec = separatorSpec;
060: m_data = data;
061: }
062:
063: public final String getName() {
064: return m_name;
065: }
066:
067: public final String getColumnSpec() {
068: return m_columnSpec;
069: }
070:
071: public final String getSeparator() {
072: return m_separator;
073: }
074:
075: public final String getSeparatorSpec() {
076: return m_separatorSpec;
077: }
078:
079: public final Object[][] getData() {
080: return m_data;
081: }
082:
083: public final Object[][] appendRowData(Object[] rowData) {
084: Object[][] newData = new Object[m_data.length + 1][rowData.length];
085: for (int row = 0; row < m_data.length; ++row)
086: newData[row] = m_data[row];
087: newData[m_data.length] = rowData;
088: m_data = newData;
089: return m_data;
090: }
091:
092: /** creates a text file as described by this instance
093: */
094: private void createTextFile() {
095:
096: PrintStream textFile = null;
097: try {
098: String completeFileName = m_name + ".csv";
099: textFile = new PrintStream(FileUtil
100: .getDefaultInstance().openOutputStreamElement(
101: completeFileName));
102: new java.io.File(completeFileName).deleteOnExit();
103: } catch (IOException ex) {
104: fail(ex.toString());
105: }
106:
107: for (int row = 0; row < m_data.length; ++row) {
108:
109: StringBuffer buf = new StringBuffer();
110: int colCount = m_data[row].length;
111: for (int col = 0; col < colCount; ++col) {
112:
113: buf.append(m_data[row][col].toString());
114: if (col + 1 != colCount)
115: buf.append(m_separator);
116: }
117: textFile.println(buf.toString());
118: }
119: textFile.close();
120: }
121:
122: private String getDataSourceSpec() {
123: return m_name + ".csv;encoding=UTF-8;fs=" + m_separatorSpec;
124: }
125:
126: private void createTable(java.sql.Connection connection)
127: throws SQLException {
128: String createTable = "DROP TABLE \"" + m_name
129: + "\" IF EXISTS;";
130: createTable += "CREATE TEXT TABLE \"" + m_name + "\" ( "
131: + m_columnSpec + " );";
132: connection.createStatement().execute(createTable);
133:
134: boolean test = isReadOnly(m_name);
135:
136: String setTableSource = "SET TABLE \"" + m_name
137: + "\" SOURCE \"" + getDataSourceSpec() + "\"";
138: connection.createStatement().execute(setTableSource);
139: }
140: };
141:
142: TextTableDescriptor m_products = new TextTableDescriptor(
143: "products", "ID INTEGER PRIMARY KEY, \"name\" VARCHAR(20)",
144: "\t", "\\t", new Object[][] {
145: new Object[] { new Integer(1), "Apples" },
146: new Object[] { new Integer(2), "Oranges" } });
147:
148: TextTableDescriptor m_customers = new TextTableDescriptor(
149: "customers", "ID INTEGER PRIMARY KEY,"
150: + "\"name\" VARCHAR(50),"
151: + "\"address\" VARCHAR(50),"
152: + "\"city\" VARCHAR(50),"
153: + "\"postal\" VARCHAR(50)", ";", "\\semi",
154: new Object[][] {
155: new Object[] { new Integer(1), "Food, Inc.",
156: "Down Under", "Melbourne", "509" },
157: new Object[] { new Integer(2), "Simply Delicious",
158: "Down Under", "Melbourne", "518" },
159: new Object[] { new Integer(3), "Pure Health",
160: "10 Fish St.", "San Francisco", "94107" } });
161:
162: /** Creates a new instance of TestTextTable */
163: public TestTextTable(String testName) {
164: super (testName, null, false);
165: }
166:
167: /** sets up all text files for the test database
168: */
169: private void setupTextFiles() {
170:
171: m_products.createTextFile();
172: m_customers.createTextFile();
173: }
174:
175: /** creates the database tables needed for the test
176: */
177: private void setupDatabase() {
178:
179: try {
180: m_connection = newConnection();
181: m_statement = m_connection.createStatement();
182:
183: m_products.createTable(m_connection);
184: m_customers.createTable(m_connection);
185: } catch (SQLException ex) {
186: fail(ex.toString());
187: }
188: }
189:
190: public void setUp() {
191: super .setUp();
192: setupTextFiles();
193: setupDatabase();
194: }
195:
196: protected void tearDown() {
197: super .tearDown();
198: executeStatement("SHUTDOWN");
199: }
200:
201: /** returns the data source definition for a given text table
202: */
203: private String getDataSourceSpec(String tableName) {
204: String spec = null;
205: try {
206: java.sql.ResultSet results = m_statement
207: .executeQuery("SELECT DATA_SOURCE_DEFINTION FROM INFORMATION_SCHEMA.SYSTEM_TEXTTABLES "
208: + "WHERE TABLE_NAME='" + tableName + "'");
209: results.next();
210: spec = results.getString(1);
211: } catch (SQLException ex) {
212: fail("getDataSourceSpec(" + tableName + ") failed: "
213: + ex.toString());
214: }
215: return spec;
216: }
217:
218: /** determines whether a given table is currently read-only
219: */
220: private boolean isReadOnly(String tableName) {
221: boolean isReadOnly = true;
222: try {
223: java.sql.ResultSet systemTables = m_statement
224: .executeQuery("SELECT READ_ONLY FROM INFORMATION_SCHEMA.SYSTEM_TABLES "
225: + "WHERE TABLE_NAME='"
226: + m_products.getName() + "'");
227: systemTables.next();
228: isReadOnly = systemTables.getBoolean(1);
229: } catch (SQLException ex) {
230: fail("isReadOnly(" + tableName + ") failed: "
231: + ex.toString());
232: }
233: return isReadOnly;
234: }
235:
236: /** checks different field separators
237: */
238: private void checkSeparators() {
239:
240: String[][] separators = new String[][] {
241: // special separators
242: new String[] { ";", "\\semi" },
243: new String[] { "\"", "\\quote" },
244: new String[] { " ", "\\space" },
245: new String[] { "'", "\\apos" },
246: //new String[] { "\n", "\\n" },
247: // doesn't work as expected - seems I don't understand how this is intended to work?
248: new String[] { "\t", "\\t" },
249: new String[] { "\\", "\\" },
250: // some arbitrary separators which need not to be escaped
251: new String[] { ".", "." }, new String[] { "-", "-" },
252: new String[] { "#", "#" }, new String[] { ",", "," }
253: // unicode character
254: //new String[] { "\u1234", "\\u1234" }
255: // doesn't work. How do I specify in a FileOutputStream which encoding to use when writing
256: // strings?
257: };
258:
259: for (int i = 0; i < separators.length; ++i) {
260: String separator = separators[i][0];
261: String separatorSpec = separators[i][1];
262:
263: // create the file
264: String tableName = "customers_" + i;
265: TextTableDescriptor tempCustomersDesc = new TextTableDescriptor(
266: tableName, m_customers.getColumnSpec(), separator,
267: separatorSpec, m_customers.getData());
268: tempCustomersDesc.createTextFile();
269:
270: try {
271: tempCustomersDesc.createTable(m_connection);
272: } catch (Throwable t) {
273: fail("checkSeparators: separator '" + separatorSpec
274: + "' doesn't work: " + t.toString());
275: }
276:
277: executeStatement("SET TABLE \"" + tableName
278: + "\" SOURCE OFF");
279: executeStatement("DROP TABLE \"" + tableName + "\"");
280: }
281: }
282:
283: /** verifies the content of a given table is as expected
284: * @param tableName
285: * the name of the table whose content is to check
286: * @param expectedValues
287: * the values expected in the table
288: */
289: private void verifyTableContent(String tableName,
290: Object[][] expectedValues) {
291:
292: String selectStmt = "SELECT * FROM \"" + tableName
293: + "\" ORDER BY ID";
294: try {
295: java.sql.ResultSet results = m_statement
296: .executeQuery(selectStmt);
297:
298: int row = 0;
299: while (results.next()) {
300:
301: row = results.getRow();
302: Object[] expectedRowContent = expectedValues[row - 1];
303:
304: for (int col = 0; col < expectedRowContent.length; ++col) {
305: Object expectedValue = expectedRowContent[col];
306: Object foundValue = results.getObject(col + 1);
307: assertEquals("table " + tableName + ", row " + row
308: + ", column " + col + ":", expectedValue,
309: foundValue);
310: }
311: }
312:
313: // finally ensure that there are not more rows in the table than expected
314: assertEquals("table " + tableName + "'s row count: ",
315: expectedValues.length, row);
316: } catch (junit.framework.AssertionFailedError e) {
317: throw e;
318: } catch (Throwable t) {
319: fail("verifyTableContent(" + tableName + ") failed with "
320: + t.toString());
321: }
322: }
323:
324: /**
325: * executes a given m_statement
326: *
327: * <p>Basically, this method calls <code>m_statement.execute(sql)</code>,
328: * but wraps any <code>SQLException</code>s into a JUnit error.
329: */
330: private void executeStatement(String sql) {
331:
332: try {
333: m_statement.execute(sql);
334: } catch (SQLException ex) {
335: fail(ex.toString());
336: }
337: }
338:
339: /** verifies the initial content of the "products" text table, plus a simple insertion
340: */
341: private void verifyInitialContent() {
342:
343: verifyTableContent(m_products.getName(), m_products.getData());
344: verifyTableContent(m_customers.getName(), m_customers.getData());
345: }
346:
347: /** does some very basic insertion tests
348: */
349: private void checkInsertions() {
350:
351: // check whether inserting a value succeeds
352: executeStatement("INSERT INTO \"" + m_products.getName()
353: + "\" VALUES ( 3, 'Pears' )");
354: verifyTableContent(m_products.getName(),
355: m_products.appendRowData(new Object[] { new Integer(3),
356: "Pears" }));
357:
358: // check whether the PK constraint works
359: try {
360: m_statement.execute("INSERT INTO \"" + m_products.getName()
361: + "\" VALUES ( 1, 'Green Apples' )");
362: fail("PKs do not work as expected.");
363: } catch (SQLException e) {
364: }
365: }
366:
367: /** verifies whether implicit and explicit dis/connections from/to the text table source work
368: * as expected
369: */
370: private void checkSourceConnection() {
371:
372: String sqlSetTable = "SET TABLE \"" + m_products.getName()
373: + "\"";
374: // preconditions for the following tests
375: assertEquals(
376: "internal error: retrieving the data source does not work properly at all.",
377: m_products.getDataSourceSpec(),
378: getDataSourceSpec(m_products.getName()));
379: assertFalse(
380: "internal error: table should not be read-only, initially",
381: isReadOnly(m_products.getName()));
382:
383: // disconnect, see if the table behaves well afterwards
384: executeStatement(sqlSetTable + " SOURCE OFF");
385: assertEquals(
386: "Disconnecting a text table should not reset the table source.",
387: m_products.getDataSourceSpec(),
388: getDataSourceSpec(m_products.getName()));
389: assertTrue(
390: "Disconnecting from the table source should put the table into read-only mode.",
391: isReadOnly(m_products.getName()));
392:
393: try {
394: java.sql.ResultSet tableContent = m_statement
395: .executeQuery("SELECT * FROM \""
396: + m_products.getName() + "\"");
397: assertFalse("A disconnected table should be empty.",
398: tableContent.next());
399: } catch (SQLException ex) {
400: fail("Selecting from a disconnected table should return an empty result set.");
401: }
402:
403: // reconnect, see if the table works as expected then
404: executeStatement(sqlSetTable + " SOURCE ON");
405: verifyTableContent(m_products.getName(), m_products.getData());
406:
407: // check whether dis-/reconnecting a readonly table preserves the readonly-ness
408: executeStatement(sqlSetTable + " READONLY TRUE");
409: assertTrue("Setting the table to read-only failed.",
410: isReadOnly(m_products.getName()));
411: executeStatement(sqlSetTable + " SOURCE OFF");
412: assertTrue("Still, a disconnected table should be read-only.",
413: isReadOnly(m_products.getName()));
414: executeStatement(sqlSetTable + " SOURCE ON");
415: assertTrue(
416: "A reconnected readonly table should preserve its readonly-ness.",
417: isReadOnly(m_products.getName()));
418: executeStatement(sqlSetTable + " READONLY FALSE");
419: assertFalse("Unable to reset the readonly-ness.",
420: isReadOnly(m_products.getName()));
421:
422: // check whether setting an invalid data source sets the table to readonly, by
423: // preserving the data source
424: try {
425: // create a malformed file
426: String fileName = "malformed.csv";
427: PrintStream textFile = new PrintStream(FileUtil
428: .getDefaultInstance().openOutputStreamElement(
429: fileName));
430: textFile.println("not a number;some text");
431: textFile.close();
432: new java.io.File(fileName).deleteOnExit();
433:
434: // try setting it as source
435: String newDataSourceSpec = fileName
436: + ";encoding=UTF-8;fs=\\semi";
437: try {
438: m_statement.execute(sqlSetTable + " SOURCE \""
439: + newDataSourceSpec + "\"");
440: fail("a malformed data source was accepted silently.");
441: } catch (java.sql.SQLException es) { /* that's expected here */
442: }
443: assertTrue(
444: "A table with an invalid data source should fall back to read-only.",
445: isReadOnly(m_products.getName()));
446: assertEquals(
447: "A data source which cannot be set should nonetheless be remembered.",
448: newDataSourceSpec, getDataSourceSpec(m_products
449: .getName()));
450:
451: // the data source spec should even survive a shutdown
452: executeStatement("SHUTDOWN");
453: m_connection = newConnection();
454: m_statement = m_connection.createStatement();
455: assertEquals(
456: "A data source pointing to a mailformed file should survive a database shutdown.",
457: newDataSourceSpec, getDataSourceSpec(m_products
458: .getName()));
459: assertTrue(
460: "After shutdown and DB-reconnect, the table with a malformed source should be read-only, again.",
461: isReadOnly(m_products.getName()));
462:
463: // reconnect after fixing the file
464: textFile = new PrintStream(FileUtil.getDefaultInstance()
465: .openOutputStreamElement(fileName));
466: textFile.println("1;some text");
467: textFile.close();
468:
469: executeStatement(sqlSetTable + " SOURCE ON");
470: assertFalse(
471: "The file was fixed, reconnect was successful, so the table shouldn't be read-only.",
472: isReadOnly(m_products.getName()));
473:
474: // finally re-create the proper version of the table for any further tests
475: m_products.createTextFile();
476: m_products.createTable(m_connection);
477: verifyTableContent(m_products.getName(), m_products
478: .getData());
479: } catch (junit.framework.AssertionFailedError e) {
480: throw e;
481: } catch (Throwable t) {
482: fail("checkSourceConnection: unable to check invalid data sources, error: "
483: + t.toString());
484: }
485: }
486:
487: /** basic tests for text files
488: */
489: public void testTextFiles() {
490:
491: verifyInitialContent();
492: checkInsertions();
493: checkSeparators();
494: checkSourceConnection();
495: }
496:
497: public static void main(String[] argv) {
498:
499: runWithResult(TestTextTable.class, "testTextFiles");
500: }
501: }
|