org.hsqldb.test.TestTextTables Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of hsqldb Show documentation
Show all versions of hsqldb Show documentation
HSQLDB - Lightweight 100% Java SQL Database Engine
/* Copyright (c) 2001-2020, The HSQL Development Group
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* Redistributions of source code must retain the above copyright notice, this
* list of conditions and the following disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice,
* this list of conditions and the following disclaimer in the documentation
* and/or other materials provided with the distribution.
*
* Neither the name of the HSQL Development Group nor the names of its
* contributors may be used to endorse or promote products derived from this
* software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
* OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
package org.hsqldb.test;
import java.io.DataOutputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.hsqldb.lib.FileUtil;
public class TestTextTables extends TestBase {
static String url = TestDirectorySettings.fileBaseURL
+ "testtext/test;sql.enforce_strict_size=true";
static String filepath = TestDirectorySettings.fileBase + "testtext/";
public TestTextTables(String name) {
super(name, url, false, false);
}
protected void setUp() throws Exception {
super.setUp();
}
private void deleteDatabaseAndSources() throws Exception {
TestUtil.deleteDatabase(filepath + "test");
TestUtil.delete(filepath + "t.txt");
TestUtil.delete(filepath + "tt.txt");
TestUtil.delete(filepath + "tident.txt");
TestUtil.delete(filepath + "tsingle.txt");
initDatabase();
}
public void testSectionOne() throws Exception {
deleteDatabaseAndSources();
initDatabase();
partA();
partD();
}
public void testSectionTwo() throws Exception {
deleteDatabaseAndSources();
initDatabase();
partB();
partD();
}
public void testSectionThree() throws Exception {
Connection conn = newConnection();
Statement st = conn.createStatement();
st.execute("SHUTDOWN SCRIPT");
}
public void testSectionFour() throws Exception {
partD();
Connection conn = newConnection();
Statement st = conn.createStatement();
st.execute("SHUTDOWN");
}
public void testSectionFive() throws Exception {
Connection conn = newConnection();
PreparedStatement ps;
try {
ps =
conn.prepareStatement("insert into tident (c2) values ?");
} catch (SQLException e) {
return;
}
for (int i = 0; i < 20; i++) {
ps.setString(1, String.valueOf(i));
ps.executeUpdate();
}
ps.close();
ps = conn.prepareStatement("insert into tsingle (c1) values ?");
for (int i = 0; i < 20; i++) {
ps.setInt(1, i + 7);
ps.executeUpdate();
}
ps.close();
Statement st = conn.createStatement();
st.execute("SHUTDOWN IMMEDIATELY");
partD();
conn = newConnection();
st = conn.createStatement();
st.execute("insert into tident values default, 'dont know'");
int count = st.executeUpdate("update tident set c2 = c2 || ' '");
assertEquals("identity table count mismatch", 21, count);
ResultSet rs = st.executeQuery("select count(*) from tsingle");
assertTrue(rs.next());
assertEquals(20, rs.getInt(1));
st.execute("set table tsingle read only");
st.execute("SHUTDOWN SCRIPT");
conn = newConnection();
st = conn.createStatement();
st.execute("SHUTDOWN SCRIPT");
}
public void testSectionSix() throws Exception {
Connection conn = newConnection();
Statement st = conn.createStatement();
st.execute("set table tsingle read write");
st.execute("SHUTDOWN SCRIPT");
conn = newConnection();
st = conn.createStatement();
st.execute("create memory table tmsingle (c1 int primary key)");
st.execute("truncate table tident restart identity");
st.execute("truncate table tsingle restart identity");
ResultSet rs = st.executeQuery("select count(*) from tident");
assertTrue(rs.next());
assertEquals(0, rs.getInt(1));
st.execute("set table tident source off");
st.execute("set table tsingle source off");
st.execute("alter table tsingle add unique(c1)");
st.execute(
"alter table tident add foreign key (c1) references tmsingle(c1)");
st.execute("set table tident source on");
st.execute("set table tsingle source on");
rs = st.executeQuery("select count(*) from tmsingle");
assertTrue(rs.next());
assertEquals(0, rs.getInt(1));
rs = st.executeQuery("select count(*) from tident");
assertTrue(rs.next());
assertEquals(0, rs.getInt(1));
PreparedStatement ps =
conn.prepareStatement("insert into tmsingle(c1) values ?");
for (int i = 0; i < 20; i++) {
ps.setInt(1, i);
ps.executeUpdate();
}
ps.close();
ps = conn.prepareStatement("insert into tident (c1, c2) values ?, ?");
for (int i = 0; i < 20; i++) {
ps.setInt(1, i);
ps.setString(2, String.valueOf(i));
ps.executeUpdate();
}
ps.close();
st = conn.createStatement();
rs = st.executeQuery("select count(*) from tmsingle");
assertTrue(rs.next());
assertEquals(20, rs.getInt(1));
rs = st.executeQuery("select count(*) from tident");
assertTrue(rs.next());
assertEquals(20, rs.getInt(1));
st.execute("SHUTDOWN SCRIPT");
conn = newConnection();
st = conn.createStatement();
rs = st.executeQuery("select count(*) from tmsingle");
assertTrue(rs.next());
assertEquals(20, rs.getInt(1));
rs = st.executeQuery("select count(*) from tident");
assertTrue(rs.next());
assertEquals(20, rs.getInt(1));
conn = newConnection();
st = conn.createStatement();
st.execute("SHUTDOWN");
}
/**
* test for malformed strings are reported by Damjan Jovanovic
*/
public void testSectionSeven() throws Exception {
deleteDatabaseAndSources();
String path = TestDirectorySettings.fileBase
+ "testtext/malformed.csv";
FileUtil.getFileUtil().delete(path);
FileOutputStream fos = new FileOutputStream(path);
DataOutputStream dos = new DataOutputStream(fos);
dos.write(new byte[] {
(byte) 0xEF, (byte) 0xBB, (byte) 0xBF
});
dos.writeBytes("\"one\",tw1o\",\"three\"\r");
dos.writeBytes("\"one\",\"tw\n2\"o,\"three\"\r");
dos.writeBytes("one\",\"tw3o\",\"thre\"e\r");
dos.close();
Connection conn = newConnection();
Statement st = conn.createStatement();
st.execute("drop table ttriple if exists");
st.execute(
"create text table ttriple(col1 varchar(20),col2 varchar(20),col3 varchar(20))");
st.execute(
"set table ttriple source 'malformed.csv;quoted=true;encoding=UTF-8'");
ResultSet rs = st.executeQuery("select * from ttriple");
st.execute("SHUTDOWN");
}
/**
* test for malformed strings are reported by Damjan Jovanovic
*/
public void testSectionEight() throws Exception {
deleteDatabaseAndSources();
String path = TestDirectorySettings.fileBase
+ "testtext/commafield.csv";
FileUtil.getFileUtil().delete(path);
FileOutputStream fos = new FileOutputStream(path);
DataOutputStream dos = new DataOutputStream(fos);
dos.write(new byte[] {
(byte) 0xEF, (byte) 0xBB, (byte) 0xBF
});
dos.writeBytes("\",\",\"col 2 line 1\"\r");
dos.writeBytes("\"col 1 line 2\",\"col 2 line 2\"\r");
dos.close();
Connection conn = newConnection();
Statement st = conn.createStatement();
st.execute("drop table tcomma if exists");
st.execute(
"create text table tcomma(col1 varchar(20),col2 varchar(20))");
st.execute(
"set table tcomma source 'commafield.csv;quoted=true;encoding=UTF-8'");
ResultSet rs = st.executeQuery("select * from tcomma");
rs.next();
String c1 = rs.getString(1);
String c2 = rs.getString(2);
rs.next();
String c21 = rs.getString(1);
String c22 = rs.getString(2);
st.execute("SHUTDOWN");
}
void initDatabase() throws Exception {
Connection conn = newConnection();
Statement st = conn.createStatement();
st.execute("set files write delay 0");
st.execute("set database transaction control locks");
}
void partA() throws Exception {
Connection conn = newConnection();
TestUtil.testScript(conn, "testrun/hsqldb/TestText01.txt");
Statement st = conn.createStatement();
st.execute("SHUTDOWN");
}
void partB() throws Exception {
Connection conn = newConnection();
TestUtil.testScript(conn, "testrun/hsqldb/TestText01.txt");
Statement st = conn.createStatement();
st.execute("SHUTDOWN IMMEDIATELY");
}
void partD() throws Exception {
Connection conn = newConnection();
TestUtil.testScript(conn, "testrun/hsqldb/TestText02.txt");
Statement st = conn.createStatement();
st.execute("SHUTDOWN");
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy