1/*
2 * Copyright (C) 2007 The Android Open Source Project
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *      http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17package libcore.sqlite;
18
19import SQLite.Exception;
20import java.sql.Connection;
21import java.sql.DriverManager;
22import java.sql.ResultSet;
23import java.sql.SQLException;
24import java.sql.Statement;
25import junit.framework.TestCase;
26
27
28/**
29 * This class provides SQL unit test, which can be used by subclasses eg. to
30 * test JDBC drivers.
31 */
32abstract class AbstractSqlTest extends TestCase {
33
34    /**
35     * The first connection.
36     */
37    private Connection firstConnection;
38
39    /**
40     * The second connection.
41     */
42    private Connection secondConnection;
43
44    /**
45     * The statement from the first connection.
46     */
47    private Statement firstStmt;
48
49    /**
50     * The statement from the second connection.
51     */
52    private Statement secondStmt;
53
54    /**
55     * The values of the first column "one".
56     */
57    private final String[] ones = {"hello!", "goodbye"};
58
59    /**
60     * The values of the second column "two".
61     */
62    private final short[] twos = {10, 20};
63
64    /**
65     * The updated values of the first column "one".
66     */
67    private final String[] ones_updated;
68
69    /** Creates a new instance of this class */
70    public AbstractSqlTest() {
71        super();
72        ones_updated = new String[ones.length];
73        for (int i = 0; i < ones.length; i++) {
74            ones_updated[i] = ones[i] + twos[i];
75        }
76    }
77
78    /**
79     * Sets up a unit test, by creating two statements from two connections and
80     * creating a test table.
81     *
82     * @exception SQLException if there is a problem accessing the database
83     * @throws Exception
84     * @exception Exception may be thrown by subclasses
85     */
86    @Override
87    protected void setUp() throws java.lang.Exception {
88        Class.forName(getDriverClassName()).newInstance();
89        firstConnection = DriverManager.getConnection(getConnectionURL());
90        firstConnection.setTransactionIsolation(getTransactionIsolation());
91        secondConnection = DriverManager.getConnection(getConnectionURL());
92        secondConnection.setTransactionIsolation(getTransactionIsolation());
93        firstStmt = firstConnection.createStatement();
94        firstStmt.execute("create table tbl1(one varchar(10), two smallint)");
95        secondStmt = secondConnection.createStatement();
96    }
97
98    /**
99     * Tears down a unit test, by setting the auto commit property of the first
100     * connection back to true, dropping the test table and closing the two
101     * connections.
102     */
103    @Override
104    protected void tearDown() throws SQLException {
105        firstStmt.close();
106        secondStmt.close();
107        firstConnection.setAutoCommit(true);
108        firstStmt = firstConnection.createStatement();
109        firstStmt.execute("drop table tbl1");
110        firstStmt.close();
111        firstConnection.close();
112        secondConnection.close();
113    }
114
115    /**
116     * Adds some rows to the test table and asserts that the rows can be
117     * retrieved again.
118     *
119     * @throws SQLException if there is a problem accessing the database
120     */
121    private void autoCommitInsertSelect() throws SQLException {
122        firstStmt.getConnection().setAutoCommit(true);
123        for (int i = 0; i < ones.length; i++) {
124            firstStmt.execute("insert into tbl1 values('" + ones[i] + "',"
125                    + twos[i] + ")");
126        }
127        assertAllFromTbl1(firstStmt, ones, twos);
128    }
129
130    /**
131     * Asserts that the expected values can be selected from the test table.
132     *
133     * @param stmt the statement to be used for the selection of the data
134     * @param ones the expected values of the column 'one'
135     * @param twos the expected values of the column 'two'
136     * @throws SQLException if there is a problem accessing the database
137     */
138    private void assertAllFromTbl1(Statement stmt, String[] ones, short[] twos)
139            throws SQLException {
140        ResultSet rs = stmt.executeQuery("select * from tbl1");
141        int i = 0;
142        for (; rs.next(); i++) {
143            assertTrue(i < ones.length);
144            assertEquals(ones[i], rs.getString("one"));
145            assertEquals(twos[i], rs.getShort("two"));
146        }
147        assertTrue(i == ones.length);
148    }
149
150    public void testAutoCommitInsertSelect() throws SQLException{
151        autoCommitInsertSelect();
152    }
153
154    /**
155     * Tests the following sequence after successful insertion of some test
156     * data:
157     * - update data from connection one
158     * - select data from connection two (-> should have the old values)
159     * - commit data from connection one
160     * - select data from connection two (-> should have the new values)
161     *
162     * @throws SQLException if there is a problem accessing the database
163     */
164    public void testUpdateSelectCommitSelect() throws SQLException {
165        autoCommitInsertSelect();
166        firstStmt.getConnection().setAutoCommit(false);
167        updateOnes(firstStmt, ones_updated, twos);
168        assertAllFromTbl1(secondStmt, ones, twos);
169        firstStmt.getConnection().commit();
170        assertAllFromTbl1(secondStmt, ones_updated, twos);
171    }
172
173    /**
174     * Tests the following sequence after successful insertion of some test
175     * data:
176     * - update data from connection one
177     * - select data from connection two (-> should have the old values)
178     * - rollback data from connection one
179     * - select data from connection two (-> should still have the old values)
180     *
181     * @throws SQLException if there is a problem accessing the database
182     */
183    public void testUpdateSelectRollbackSelect() throws SQLException {
184        autoCommitInsertSelect();
185        firstStmt.getConnection().setAutoCommit(false);
186        updateOnes(firstStmt, ones_updated, twos);
187        assertAllFromTbl1(secondStmt, ones, twos);
188        firstStmt.getConnection().rollback();
189        assertAllFromTbl1(secondStmt, ones, twos);
190    }
191
192    /**
193     * Updates the values in column 'one'
194     * @param stmt the statement to be used to update the data
195     * @param ones_updated the updated valus of column 'one'
196     * @param twos the reference values of column 'two'
197     * @throws SQLException if there is a problem accessing the database
198     */
199    private void updateOnes(Statement stmt, String[] ones_updated, short[] twos)
200            throws SQLException {
201        for (int i = 0; i < ones_updated.length; i++) {
202            stmt.execute("UPDATE tbl1 SET one = '" + ones_updated[i]
203                    + "' WHERE two = " + twos[i]);
204        }
205    }
206
207    protected abstract String getConnectionURL();
208
209    protected abstract String getDriverClassName();
210
211    protected abstract int getTransactionIsolation();
212
213}
214