1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
|
/*
*
* Derby - Class org.apache.derbyTesting.functionTests.tests.demo.CheckToursDBTest
*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file ecept in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
* either express or implied. See the License for the specific
* language governing permissions and limitations under the License.
*/
package org.apache.derbyTesting.functionTests.tests.demo;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.security.AccessController;
import java.security.PrivilegedActionException;
import java.security.PrivilegedExceptionAction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.SupportFilesSetup;
import org.apache.derbyTesting.junit.TestConfiguration;
import toursdb.insertMaps;
/**
* This test is for testing the ToursDB database and functions
*/
public class CheckToursDBTest extends BaseJDBCTestCase {
/**
* Public constructor required for running test as standalone JUnit.
* @param name
*/
public CheckToursDBTest(String name) {
super(name);
}
public static Test basesuite(String name) {
BaseTestSuite suite = new BaseTestSuite(CheckToursDBTest.class, name);
Test test = new SupportFilesSetup(suite, new String[] {
"functionTests/tests/demo/cupisle.gif",
"functionTests/tests/demo/smallisle.gif",
"functionTests/tests/demo/witchisle.gif" });
return test;
}
public static Test suite() {
if ( JDBC.vmSupportsJSR169())
// Test executes PreparedStatement.setBigDecimal, which
// is not supported with JSR169
return new BaseTestSuite(
"empty CheckToursDBTest - *.setBigDecimal " +
"not supported with JSR169");
BaseTestSuite suite = new BaseTestSuite("CheckToursDBTest");
suite.addTest(basesuite("CheckToursDBTest:embedded"));
suite.addTest(TestConfiguration
.clientServerDecorator(basesuite("CheckToursDBTest:client")));
return suite;
}
/**
* Tear-down the fixture by removing the tables
* @throws Exception
*/ protected void tearDown() throws Exception {
Statement st = createStatement();
st.execute("DROP TABLE AIRLINES");
st.execute("DROP TABLE CITIES");
st.execute("DROP TABLE COUNTRIES");
st.execute("DROP TABLE FLIGHTAVAILABILITY");
st.execute("DROP TABLE FLIGHTS");
st.execute("DROP TABLE MAPS");
st.execute("DROP TABLE FLIGHTS_HISTORY");
st.close();
commit();
super.tearDown();
}
/**
* Test insert, update and delete on ToursDB tables
* @throws Exception
*/
public void testToursDB() throws Exception {
String[] dbfiles = { "ToursDB_schema.sql", "loadCOUNTRIES.sql",
"loadCITIES.sql", "loadAIRLINES.sql", "loadFLIGHTS1.sql",
"loadFLIGHTS2.sql", "loadFLIGHTAVAILABILITY1.sql",
"loadFLIGHTAVAILABILITY2.sql" };
for (int i = 0; i < dbfiles.length; i++) {
runScript("org/apache/derbyTesting/functionTests/tests/demo/"
+ dbfiles[i], "US-ASCII");
}
insertMapsPrivileged();
doSelect();
doUpdate();
doDelete();
}
/**
* Method to delete rows from the ToursDB tables
* @throws Exception
*/
private void doDelete() throws Exception {
String tableName[] = { "AIRLINES", "CITIES", "COUNTRIES",
"FLIGHTAVAILABILITY", "FLIGHTS", "MAPS" };
int expectedRows[] = { 2, 87, 114, 518, 542, 3 };
PreparedStatement ps = null;
for (int i = 0; i < 6; i++) {
ps = prepareStatement("delete from " + tableName[i]);
assertEquals(ps.executeUpdate(), expectedRows[i]);
}
// now quickly checking FLIGHTS_HISTORY -
// should now have a 2nd row because of trigger2
ps = prepareStatement("select STATUS from FLIGHTS_HISTORY where FLIGHT_ID IS NULL and STATUS <> 'over'");
// don't care if there are more than 1 rows...
JDBC.assertSingleValueResultSet(ps.executeQuery(),
"INSERTED FROM TRIG2");
ps = prepareStatement("delete from FLIGHTS_HISTORY");
assertEquals(ps.executeUpdate(), 2);
}
/**
* Method to update the rows in the ToursDB tables.
* @throws SQLException
*/
private void doUpdate() throws SQLException {
PreparedStatement ps = null;
ps = prepareStatement("select ECONOMY_SEATS from AIRLINES where AIRLINE = 'AA'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "20");
Statement stmt = createStatement();
stmt.execute("update AIRLINES set ECONOMY_SEATS=108 where AIRLINE = 'AA'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "108");
ps = prepareStatement("select COUNTRY from COUNTRIES where COUNTRY_ISO_CODE = 'US'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "United States");
stmt.execute("update COUNTRIES set COUNTRY='United States of America' where COUNTRY_ISO_CODE = 'US'");
JDBC.assertSingleValueResultSet(ps.executeQuery(),
"United States of America");
ps = prepareStatement("select COUNTRY from CITIES where CITY_ID = 52");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "United States");
stmt.execute("update CITIES set COUNTRY='United States of America' where COUNTRY='United States'");
JDBC.assertSingleValueResultSet(ps.executeQuery(),
"United States of America");
ps = prepareStatement("select ECONOMY_SEATS_TAKEN from FLIGHTAVAILABILITY where FLIGHT_ID = 'AA1134' and FLIGHT_DATE='2004-03-31'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "2");
stmt.execute("update FLIGHTAVAILABILITY set ECONOMY_SEATS_TAKEN=20 where FLIGHT_ID = 'AA1134' and FLIGHT_DATE='2004-03-31'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "20");
ps = prepareStatement("select AIRCRAFT from FLIGHTS where FLIGHT_ID = 'AA1183'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "B747");
stmt.execute("update FLIGHTS set AIRCRAFT='B777' where FLIGHT_ID = 'AA1134'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "B747");
ps = prepareStatement("select REGION from MAPS where MAP_NAME = 'North Ocean'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "Cup Island");
stmt.execute("update MAPS set REGION='Coffee Cup Island' where MAP_NAME = 'North Ocean'");
JDBC.assertSingleValueResultSet(ps.executeQuery(),
"Coffee Cup Island");
// Flight_history is now has 1 row, because of TRIG1
ps = prepareStatement("select STATUS from FLIGHTS_HISTORY where FLIGHT_ID = 'AA1134'");
JDBC.assertSingleValueResultSet(ps.executeQuery(),
"INSERTED FROM TRIG1");
stmt.execute("update FLIGHTS_HISTORY set STATUS='over' where FLIGHT_ID='AA1134'");
JDBC.assertSingleValueResultSet(ps.executeQuery(), "over");
}
/**
* Inserts rows in the Maps table. Calls insertMaps().
* @throws Exception
*/
public void insertMapsPrivileged() throws Exception {
try {
AccessController.doPrivileged(new PrivilegedExceptionAction<Object>() {
public Object run() throws SQLException, FileNotFoundException,
IOException {
insertMaps();
return null;
}
});
} catch (PrivilegedActionException e) {
throw e.getException();
}
}
/**
* Method to select rows from ToursDB tables
* @throws SQLException
*/
private void doSelect() throws SQLException {
String expectedRows[] = { "2", "114", "87", "518", "542", "3", "0" };
// now ensure we can select from all the tables
PreparedStatement ps = null;
String tableName[] = { "AIRLINES", "COUNTRIES", "CITIES",
"FLIGHTAVAILABILITY", "FLIGHTS", "MAPS", "FLIGHTS_HISTORY" };
for (int i = 0; i < 7; i++) {
ps = prepareStatement("select count(*) from " + tableName[i]);
JDBC.assertSingleValueResultSet(ps.executeQuery(), expectedRows[i]);
}
}
/**
* Inserts 3 rows in the Maps table.
* @throws SQLException
* @throws FileNotFoundException
* @throws IOException
*/
private void insertMaps()
throws SQLException, FileNotFoundException, IOException {
Connection conn = getConnection();
assertEquals(insertMaps.insertRows("extin", conn), 3);
}
}
|
