1package com.xtremelabs.robolectric.shadows;
2
3
4import android.database.sqlite.SQLiteCursor;
5import com.xtremelabs.robolectric.Robolectric;
6import com.xtremelabs.robolectric.WithTestDefaultsRunner;
7import com.xtremelabs.robolectric.util.DatabaseConfig;
8import org.junit.After;
9import org.junit.Before;
10import org.junit.Test;
11import org.junit.runner.RunWith;
12
13import java.sql.Connection;
14import java.sql.PreparedStatement;
15import java.sql.ResultSet;
16import java.sql.Statement;
17
18import static org.hamcrest.CoreMatchers.equalTo;
19import static org.hamcrest.CoreMatchers.notNullValue;
20import static org.junit.Assert.assertThat;
21
22@RunWith(WithTestDefaultsRunner.class)
23public class SQLiteCursorTest {
24
25    private Connection connection;
26    private ResultSet resultSet;
27    private SQLiteCursor cursor;
28
29    @Before
30    public void setUp() throws Exception {
31    	connection = DatabaseConfig.getMemoryConnection();
32
33        Statement statement = connection.createStatement();
34        statement.execute("CREATE TABLE table_name(" +
35                "id INTEGER PRIMARY KEY, name VARCHAR(255), long_value BIGINT," +
36                "float_value REAL, double_value DOUBLE, blob_value BINARY, clob_value CLOB );");
37
38        addPeople();
39        setupCursor();
40    }
41
42    @After
43    public void tearDown() throws Exception {
44        connection.close();
45    }
46
47    @Test
48    public void testGetColumnNames() throws Exception {
49        String[] columnNames = cursor.getColumnNames();
50
51        assertColumnNames(columnNames);
52    }
53
54    @Test
55    public void testGetColumnNamesEmpty() throws Exception {
56        setupEmptyResult();
57        String[] columnNames = cursor.getColumnNames();
58
59        // Column names are present even with an empty result.
60        assertThat(columnNames, notNullValue());
61        assertColumnNames(columnNames);
62    }
63
64    @Test
65    public void testGetColumnIndex() throws Exception {
66        assertThat(cursor.getColumnIndex("id"), equalTo(0));
67        assertThat(cursor.getColumnIndex("name"), equalTo(1));
68    }
69
70    @Test
71    public void testGetColumnIndexNotFound() throws Exception {
72        assertThat(cursor.getColumnIndex("Fred"), equalTo(-1));
73    }
74
75    @Test
76    public void testGetColumnIndexEmpty() throws Exception {
77        setupEmptyResult();
78
79        assertThat(cursor.getColumnIndex("id"), equalTo(0));
80        assertThat(cursor.getColumnIndex("name"), equalTo(1));
81    }
82
83    @Test
84    public void testGetColumnIndexOrThrow() throws Exception {
85        assertThat(cursor.getColumnIndexOrThrow("id"), equalTo(0));
86        assertThat(cursor.getColumnIndexOrThrow("name"), equalTo(1));
87    }
88
89    @Test(expected = IllegalArgumentException.class)
90    public void testGetColumnIndexOrThrowNotFound() throws Exception {
91        cursor.getColumnIndexOrThrow("Fred");
92    }
93
94    @Test
95    public void testGetColumnIndexOrThrowEmpty() throws Exception {
96        setupEmptyResult();
97
98        assertThat(cursor.getColumnIndexOrThrow("name"), equalTo(1));
99    }
100
101    @Test(expected = IllegalArgumentException.class)
102    public void testGetColumnIndexOrThrowNotFoundEmpty() throws Exception {
103        setupEmptyResult();
104
105        cursor.getColumnIndexOrThrow("Fred");
106    }
107
108    @Test
109    public void testMoveToFirst() throws Exception {
110        assertThat(cursor.moveToFirst(), equalTo(true));
111        assertThat(cursor.getInt(0), equalTo(1234));
112        assertThat(cursor.getString(1), equalTo("Chuck"));
113    }
114
115    @Test
116    public void testMoveToFirstEmpty() throws Exception {
117        setupEmptyResult();
118
119        assertThat(cursor.moveToFirst(), equalTo(false));
120    }
121
122    @Test
123    public void testMoveToNext() throws Exception {
124        cursor.moveToFirst();
125
126        assertThat(cursor.moveToNext(), equalTo(true));
127        assertThat(cursor.getInt(0), equalTo(1235));
128        assertThat(cursor.getString(1), equalTo("Julie"));
129    }
130
131    @Test
132    public void testMoveToNextPastEnd() throws Exception {
133        cursor.moveToFirst();
134
135        cursor.moveToNext();
136        cursor.moveToNext();
137        cursor.moveToNext();
138
139        assertThat(cursor.moveToNext(), equalTo(false));
140    }
141
142    @Test
143    public void testMoveBackwards() throws Exception {
144    	assertThat(cursor.getPosition(), equalTo(-1));
145
146        cursor.moveToFirst();
147        assertThat(cursor.getPosition(), equalTo(0));
148        cursor.moveToNext();
149        assertThat(cursor.getPosition(), equalTo(1));
150        cursor.moveToNext();
151        assertThat(cursor.getPosition(), equalTo(2));
152
153        cursor.moveToFirst();
154        assertThat(cursor.getPosition(), equalTo(0));
155        cursor.moveToNext();
156        assertThat(cursor.getPosition(), equalTo(1));
157        cursor.moveToNext();
158        assertThat(cursor.getPosition(), equalTo(2));
159
160        cursor.moveToPosition(1);
161        assertThat(cursor.getPosition(), equalTo(1));
162    }
163
164    @Test
165    public void testMoveToNextEmpty() throws Exception {
166        setupEmptyResult();
167
168        cursor.moveToFirst();
169        assertThat(cursor.moveToNext(), equalTo(false));
170    }
171
172    @Test
173    public void testMoveToPrevious() throws Exception {
174    	cursor.moveToFirst();
175    	cursor.moveToNext();
176
177    	assertThat(cursor.moveToPrevious(), equalTo(true));
178        assertThat(cursor.getInt(0), equalTo(1234));
179        assertThat(cursor.getString(1), equalTo("Chuck"));
180    }
181
182    @Test
183    public void testMoveToPreviousPastStart() throws Exception {
184    	cursor.moveToFirst();
185
186    	// Possible to move cursor before the first item
187    	assertThat(cursor.moveToPrevious(), equalTo(true));
188    	// After that, attempts to move cursor back return false
189    	assertThat(cursor.moveToPrevious(), equalTo(false));
190    }
191
192    @Test
193    public void testMoveToPreviousEmpty() throws Exception {
194        setupEmptyResult();
195    	cursor.moveToFirst();
196
197    	assertThat(cursor.moveToPrevious(), equalTo(false));
198    }
199
200    @Test
201    public void testGetPosition() throws Exception {
202        cursor.moveToFirst();
203        assertThat(cursor.getPosition(), equalTo(0));
204
205        cursor.moveToNext();
206        assertThat(cursor.getPosition(), equalTo(1));
207    }
208
209    @Test
210    public void testGetBlob() throws Exception {
211        String sql = "UPDATE table_name set blob_value=? where id=1234";
212        byte[] byteData = sql.getBytes();
213
214        PreparedStatement statement = connection.prepareStatement(sql);
215        statement.setObject(1, byteData);
216        statement.executeUpdate();
217
218        setupCursor();
219        cursor.moveToFirst();
220
221        byte[] retrievedByteData = cursor.getBlob(5);
222        assertThat(byteData.length, equalTo(retrievedByteData.length));
223
224        for (int i = 0; i < byteData.length; i++) {
225            assertThat(byteData[i], equalTo(retrievedByteData[i]));
226        }
227    }
228
229    @Test
230    public void testGetClob() throws Exception {
231        String sql = "UPDATE table_name set clob_value=? where id=1234";
232        String s = "Don't CLOBber my data, please. Thank you.";
233
234        PreparedStatement statement = connection.prepareStatement(sql);
235        statement.setObject(1, s);
236        statement.executeUpdate();
237
238        setupCursor();
239        cursor.moveToFirst();
240
241        String actual = cursor.getString(6);
242        assertThat(s, equalTo(actual));
243    }
244
245    @Test
246    public void testGetString() throws Exception {
247        cursor.moveToFirst();
248
249        String[] data = {"Chuck", "Julie", "Chris"};
250
251        for (String aData : data) {
252            assertThat(cursor.getString(1), equalTo(aData));
253            cursor.moveToNext();
254        }
255    }
256
257    @Test
258    public void testGetInt() throws Exception {
259        cursor.moveToFirst();
260
261        int[] data = {1234, 1235, 1236};
262
263        for (int aData : data) {
264            assertThat(cursor.getInt(0), equalTo(aData));
265            cursor.moveToNext();
266        }
267    }
268
269    @Test
270    public void testGetLong() throws Exception {
271        cursor.moveToFirst();
272
273        assertThat(cursor.getLong(2), equalTo(3463L));
274    }
275
276    @Test
277    public void testGetFloat() throws Exception {
278        cursor.moveToFirst();
279
280        assertThat(cursor.getFloat(3), equalTo((float) 1.5));
281    }
282
283    @Test
284    public void testGetDouble() throws Exception {
285        cursor.moveToFirst();
286
287        assertThat(cursor.getDouble(4), equalTo(3.14159));
288    }
289
290    @Test
291    public void testClose() throws Exception {
292        assertThat(cursor.isClosed(), equalTo(false));
293        cursor.close();
294        assertThat(cursor.isClosed(), equalTo(true));
295    }
296
297    @Test
298    public void testIsNullWhenNull() throws Exception {
299        cursor.moveToFirst();
300        assertThat(cursor.moveToNext(), equalTo(true));
301
302        assertThat(cursor.isNull(cursor.getColumnIndex("id")), equalTo(false));
303        assertThat(cursor.isNull(cursor.getColumnIndex("name")), equalTo(false));
304
305        assertThat(cursor.isNull(cursor.getColumnIndex("long_value")), equalTo(true));
306        assertThat(cursor.isNull(cursor.getColumnIndex("float_value")), equalTo(true));
307        assertThat(cursor.isNull(cursor.getColumnIndex("double_value")), equalTo(true));
308    }
309
310    @Test
311    public void testIsNullWhenNotNull() throws Exception {
312        cursor.moveToFirst();
313
314        for (int i = 0; i < 5; i++) {
315            assertThat(cursor.isNull(i), equalTo(false));
316        }
317    }
318
319    @Test
320    public void testIsNullWhenIndexOutOfBounds() throws Exception {
321        cursor.moveToFirst();
322
323        // column index 5 is out-of-bounds
324        assertThat(cursor.isNull(5), equalTo(true));
325    }
326
327    private void addPeople() throws Exception {
328        String[] inserts = {
329                "INSERT INTO table_name (id, name, long_value, float_value, double_value) VALUES(1234, 'Chuck', 3463, 1.5, 3.14159);",
330                "INSERT INTO table_name (id, name) VALUES(1235, 'Julie');",
331                "INSERT INTO table_name (id, name) VALUES(1236, 'Chris');"
332        };
333
334        for (String insert : inserts) {
335            connection.createStatement().executeUpdate(insert);
336        }
337    }
338
339    private void setupCursor() throws Exception {
340        Statement statement = connection.createStatement(DatabaseConfig.getResultSetType(), ResultSet.CONCUR_READ_ONLY);
341        String sql ="SELECT * FROM table_name;";
342        resultSet = statement.executeQuery("SELECT * FROM table_name;");
343        cursor = new SQLiteCursor(null, null, null, null);
344        Robolectric.shadowOf(cursor).setResultSet(resultSet, sql);
345    }
346
347    private void setupEmptyResult() throws Exception {
348        Statement statement = connection.createStatement();
349        statement.executeUpdate("DELETE FROM table_name;");
350
351        setupCursor();
352    }
353
354    private void assertColumnNames(String[] columnNames) {
355        assertThat(columnNames.length, equalTo(7));
356        assertThat(columnNames[0], equalTo("id"));
357        assertThat(columnNames[1], equalTo("name"));
358        assertThat(columnNames[2], equalTo("long_value"));
359        assertThat(columnNames[3], equalTo("float_value"));
360        assertThat(columnNames[4], equalTo("double_value"));
361        assertThat(columnNames[5], equalTo("blob_value"));
362        assertThat(columnNames[6], equalTo("clob_value"));
363    }
364
365}
366