SQLiteDatabaseTest.java revision bdb2b1d336367115dee931221ea77fe1833607da 
1package com.xtremelabs.robolectric.shadows;
2
3import android.content.ContentValues;
4import android.database.Cursor;
5import android.database.sqlite.SQLiteDatabase;
6import com.xtremelabs.robolectric.WithTestDefaultsRunner;
7import org.junit.After;
8import org.junit.Before;
9import org.junit.Test;
10import org.junit.runner.RunWith;
11
12import java.sql.ResultSet;
13import java.sql.Statement;
14
15import static com.xtremelabs.robolectric.Robolectric.shadowOf;
16import static org.hamcrest.CoreMatchers.equalTo;
17import static org.hamcrest.CoreMatchers.not;
18import static org.junit.Assert.assertEquals;
19import static org.junit.Assert.assertThat;
20
21@RunWith(WithTestDefaultsRunner.class)
22public class SQLiteDatabaseTest {
23    private SQLiteDatabase database;
24
25    @Before
26    public void setUp() throws Exception {
27        database = SQLiteDatabase.openDatabase("path", null, 0);
28
29        database.execSQL("CREATE TABLE table_name (\n" +
30                "  id INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
31                "  first_column VARCHAR(255),\n" +
32                "  second_column BINARY,\n" +
33                "  name VARCHAR(255),\n" +
34                "  big_int INTEGER\n" +
35                ");");
36    }
37
38    @After
39    public void tearDown() throws Exception {
40        database.close();
41    }
42
43    @Test
44    public void testInsertAndQuery() throws Exception {
45        String stringColumnValue = "column_value";
46        byte[] byteColumnValue = new byte[]{1, 2, 3};
47
48        ContentValues values = new ContentValues();
49
50        values.put("first_column", stringColumnValue);
51        values.put("second_column", byteColumnValue);
52
53        database.insert("table_name", null, values);
54
55        Cursor cursor = database.query("table_name", new String[]{"second_column", "first_column"}, null, null, null, null, null);
56
57        assertThat(cursor.moveToFirst(), equalTo(true));
58
59        byte[] byteValueFromDatabase = cursor.getBlob(0);
60        String stringValueFromDatabase = cursor.getString(1);
61
62        assertThat(stringValueFromDatabase, equalTo(stringColumnValue));
63        assertThat(byteValueFromDatabase, equalTo(byteColumnValue));
64    }
65
66    @Test
67    public void testInsertAndRawQuery() throws Exception {
68        String stringColumnValue = "column_value";
69        byte[] byteColumnValue = new byte[]{1, 2, 3};
70
71        ContentValues values = new ContentValues();
72
73        values.put("first_column", stringColumnValue);
74        values.put("second_column", byteColumnValue);
75
76        database.insert("table_name", null, values);
77
78        Cursor cursor = database.rawQuery("select second_column, first_column from table_name", null);
79
80        assertThat(cursor.moveToFirst(), equalTo(true));
81
82        byte[] byteValueFromDatabase = cursor.getBlob(0);
83        String stringValueFromDatabase = cursor.getString(1);
84
85        assertThat(stringValueFromDatabase, equalTo(stringColumnValue));
86        assertThat(byteValueFromDatabase, equalTo(byteColumnValue));
87    }
88
89    @Test
90    public void testEmptyTable() throws Exception {
91        Cursor cursor = database.query("table_name", new String[]{"second_column", "first_column"}, null, null, null, null, null);
92
93        assertThat(cursor.moveToFirst(), equalTo(false));
94    }
95
96    @Test
97    public void testInsertRowIdGeneration() throws Exception {
98        ContentValues values = new ContentValues();
99        values.put("name", "Chuck");
100
101        long id = database.insert("table_name", null, values);
102
103        assertThat(id, not(equalTo(0L)));
104    }
105
106    @Test
107    public void testInsertKeyGeneration() throws Exception {
108        ContentValues values = new ContentValues();
109        values.put("name", "Chuck");
110
111        long key = database.insertWithOnConflict("table_name", null, values, SQLiteDatabase.CONFLICT_IGNORE);
112
113        assertThat(key, not(equalTo(0L)));
114    }
115
116    @Test
117    public void testUpdate() throws Exception {
118        addChuck();
119
120        assertThat(updateName(1234L, "Buster"), equalTo(1));
121
122        Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null);
123        assertThat(cursor.moveToFirst(), equalTo(true));
124        assertThat(cursor.getCount(), equalTo(1));
125
126        assertIdAndName(cursor, 1234L, "Buster");
127    }
128
129    @Test
130    public void testUpdateNoMatch() throws Exception {
131        addChuck();
132
133        assertThat(updateName(5678L, "Buster"), equalTo(0));
134
135        Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null);
136        assertThat(cursor.moveToFirst(), equalTo(true));
137        assertThat(cursor.getCount(), equalTo(1));
138
139        assertIdAndName(cursor, 1234L, "Chuck");
140    }
141
142    @Test
143    public void testUpdateAll() throws Exception {
144        addChuck();
145        addJulie();
146
147        assertThat(updateName("Belvedere"), equalTo(2));
148
149        Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null);
150        assertThat(cursor.moveToFirst(), equalTo(true));
151        assertThat(cursor.getCount(), equalTo(2));
152
153        assertIdAndName(cursor, 1234L, "Belvedere");
154        assertThat(cursor.moveToNext(), equalTo(true));
155
156        assertIdAndName(cursor, 1235L, "Belvedere");
157        assertThat(cursor.moveToNext(), equalTo(false));
158    }
159
160    @Test
161    public void testDelete() throws Exception {
162        addChuck();
163
164        int deleted = database.delete("table_name", "id=1234", null);
165        assertThat(deleted, equalTo(1));
166
167        assertEmptyDatabase();
168    }
169
170    @Test
171    public void testDeleteNoMatch() throws Exception {
172        addChuck();
173
174        int deleted = database.delete("table_name", "id=5678", null);
175        assertThat(deleted, equalTo(0));
176
177        assertNonEmptyDatabase();
178    }
179
180    @Test
181    public void testDeleteAll() throws Exception {
182        addChuck();
183        addJulie();
184
185        int deleted = database.delete("table_name", "1", null);
186        assertThat(deleted, equalTo(2));
187
188        assertEmptyDatabase();
189    }
190
191    @Test
192    public void testExecSQL() throws Exception {
193        Statement statement;
194        ResultSet resultSet;
195
196        database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');");
197
198        statement = shadowOf(database).getConnection().createStatement();
199        resultSet = statement.executeQuery("SELECT COUNT(*) FROM table_name");
200        assertThat(resultSet.next(), equalTo(true));
201        assertThat(resultSet.getInt(1), equalTo(1));
202
203        statement = shadowOf(database).getConnection().createStatement();
204        resultSet = statement.executeQuery("SELECT * FROM table_name");
205     //   assertThat(resultSet.first(), equalTo(true));
206        assertThat(resultSet.next(), equalTo(true));
207        assertThat(resultSet.getInt(1), equalTo(1234));
208        assertThat(resultSet.getString(4), equalTo("Chuck"));
209    }
210
211    @Test(expected = android.database.SQLException.class)
212    public void testExecSQLException() throws Exception {
213        database.execSQL("INSERT INTO table_name;");    // invalid SQL
214    }
215
216    @Test
217    public void testExecSQLAutoIncrementSQLite() throws Exception {
218        database.execSQL("CREATE TABLE auto_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255));");
219
220        ContentValues values = new ContentValues();
221        values.put("name", "Chuck");
222
223        long key = database.insert("auto_table", null, values);
224        assertThat(key, not(equalTo(0L)));
225
226        long key2 = database.insert("auto_table", null, values);
227        assertThat(key2, not(equalTo(key)));
228    }
229
230    @Test(expected = IllegalStateException.class)
231    public void testClose() throws Exception {
232        database.close();
233
234        database.execSQL("INSERT INTO table_name (id, name) VALUES(1234, 'Chuck');");
235    }
236
237    @Test
238    public void testIsOpen() throws Exception {
239        assertThat(database.isOpen(), equalTo(true));
240        database.close();
241        assertThat(database.isOpen(), equalTo(false));
242    }
243
244    @Test
245    public void shouldStoreGreatBigHonkinIntegersCorrectly() throws Exception {
246        database.execSQL("INSERT INTO table_name(big_int) VALUES(1234567890123456789);");
247        Cursor cursor = database.query("table_name", new String[]{"big_int"}, null, null, null, null, null);
248        assertEquals(1234567890123456789L, cursor.getLong(0));
249    }
250
251    private void addChuck() {
252        addPerson(1234L, "Chuck");
253    }
254
255    private void addJulie() {
256        addPerson(1235L, "Julie");
257    }
258
259    private void addPerson(long id, String name) {
260        ContentValues values = new ContentValues();
261        values.put("id", id);
262        values.put("name", name);
263        database.insert("table_name", null, values);
264    }
265
266    private int updateName(long id, String name) {
267        ContentValues values = new ContentValues();
268        values.put("name", name);
269        return database.update("table_name", values, "id=" + id, null);
270    }
271
272    private int updateName(String name) {
273        ContentValues values = new ContentValues();
274        values.put("name", name);
275        return database.update("table_name", values, null, null);
276    }
277
278    private void assertIdAndName(Cursor cursor, long id, String name) {
279        long idValueFromDatabase;
280        String stringValueFromDatabase;
281
282        idValueFromDatabase = cursor.getLong(0);
283        stringValueFromDatabase = cursor.getString(1);
284        assertThat(idValueFromDatabase, equalTo(id));
285        assertThat(stringValueFromDatabase, equalTo(name));
286    }
287
288    private void assertEmptyDatabase() {
289        Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null);
290        assertThat(cursor.moveToFirst(), equalTo(false));
291        assertThat(cursor.isClosed(), equalTo(true));
292        assertThat(cursor.getCount(), equalTo(0));
293    }
294
295    private void assertNonEmptyDatabase() {
296        Cursor cursor = database.query("table_name", new String[]{"id", "name"}, null, null, null, null, null);
297        assertThat(cursor.moveToFirst(), equalTo(true));
298        assertThat(cursor.getCount(), not(equalTo(0)));
299    }
300}
301