DatabaseStatementTest.java revision 1a44d5dcabc18cd5ef111f732ccff91683a1a093 
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 android.database;
18
19import android.content.Context;
20import android.database.Cursor;
21import android.database.sqlite.SQLiteConstraintException;
22import android.database.sqlite.SQLiteDatabase;
23import android.database.sqlite.SQLiteDoneException;
24import android.database.sqlite.SQLiteStatement;
25import android.test.AndroidTestCase;
26import android.test.PerformanceTestCase;
27import android.test.suitebuilder.annotation.MediumTest;
28import android.test.suitebuilder.annotation.SmallTest;
29import junit.framework.TestCase;
30
31import java.io.File;
32
33public class DatabaseStatementTest extends AndroidTestCase implements PerformanceTestCase {
34
35    private static final String sString1 = "this is a test";
36    private static final String sString2 = "and yet another test";
37    private static final String sString3 = "this string is a little longer, but still a test";
38
39    private static final int CURRENT_DATABASE_VERSION = 42;
40    private SQLiteDatabase mDatabase;
41    private File mDatabaseFile;
42
43    @Override
44    protected void setUp() throws Exception {
45        super.setUp();
46	File dbDir = getContext().getDir("tests", Context.MODE_PRIVATE);
47	mDatabaseFile = new File(dbDir, "database_test.db");
48
49        if (mDatabaseFile.exists()) {
50            mDatabaseFile.delete();
51        }
52        mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null);
53        assertNotNull(mDatabase);
54        mDatabase.setVersion(CURRENT_DATABASE_VERSION);
55    }
56
57    @Override
58    protected void tearDown() throws Exception {
59        mDatabase.close();
60        mDatabaseFile.delete();
61        super.tearDown();
62    }
63
64    public boolean isPerformanceOnly() {
65        return false;
66    }
67
68    // These test can only be run once.
69    public int startPerformance(Intermediates intermediates) {
70        return 1;
71    }
72
73    private void populateDefaultTable() {
74        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
75
76        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString1 + "');");
77        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString2 + "');");
78        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString3 + "');");
79    }
80
81    @MediumTest
82    public void testExecuteStatement() throws Exception {
83        populateDefaultTable();
84        SQLiteStatement statement = mDatabase.compileStatement("DELETE FROM test");
85        statement.execute();
86
87        Cursor c = mDatabase.query("test", null, null, null, null, null, null);
88        assertEquals(0, c.getCount());
89        c.deactivate();
90        statement.close();
91    }
92
93    @MediumTest
94    public void testSimpleQuery() throws Exception {
95        mDatabase.execSQL("CREATE TABLE test (num INTEGER NOT NULL, str TEXT NOT NULL);");
96        mDatabase.execSQL("INSERT INTO test VALUES (1234, 'hello');");
97        SQLiteStatement statement1 =
98                mDatabase.compileStatement("SELECT num FROM test WHERE str = ?");
99        SQLiteStatement statement2 =
100                mDatabase.compileStatement("SELECT str FROM test WHERE num = ?");
101
102        try {
103            statement1.bindString(1, "hello");
104            long value = statement1.simpleQueryForLong();
105            assertEquals(1234, value);
106
107            statement1.bindString(1, "world");
108            statement1.simpleQueryForLong();
109            fail("shouldn't get here");
110        } catch (SQLiteDoneException e) {
111            // expected
112        }
113
114        try {
115            statement2.bindLong(1, 1234);
116            String value = statement1.simpleQueryForString();
117            assertEquals("hello", value);
118
119            statement2.bindLong(1, 5678);
120            statement1.simpleQueryForString();
121            fail("shouldn't get here");
122        } catch (SQLiteDoneException e) {
123            // expected
124        }
125
126        statement1.close();
127        statement2.close();
128    }
129
130    @MediumTest
131    public void testStatementLongBinding() throws Exception {
132        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
133        SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)");
134
135        for (int i = 0; i < 10; i++) {
136            statement.bindLong(1, i);
137            statement.execute();
138        }
139        statement.close();
140
141        Cursor c = mDatabase.query("test", null, null, null, null, null, null);
142        int numCol = c.getColumnIndexOrThrow("num");
143        c.moveToFirst();
144        for (long i = 0; i < 10; i++) {
145            long num = c.getLong(numCol);
146            assertEquals(i, num);
147            c.moveToNext();
148        }
149        c.close();
150    }
151
152    @MediumTest
153    public void testStatementStringBinding() throws Exception {
154        mDatabase.execSQL("CREATE TABLE test (num TEXT);");
155        SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)");
156
157        for (long i = 0; i < 10; i++) {
158            statement.bindString(1, Long.toHexString(i));
159            statement.execute();
160        }
161        statement.close();
162
163        Cursor c = mDatabase.query("test", null, null, null, null, null, null);
164        int numCol = c.getColumnIndexOrThrow("num");
165        c.moveToFirst();
166        for (long i = 0; i < 10; i++) {
167            String num = c.getString(numCol);
168            assertEquals(Long.toHexString(i), num);
169            c.moveToNext();
170        }
171        c.close();
172    }
173
174    @MediumTest
175    public void testStatementClearBindings() throws Exception {
176        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
177        SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)");
178
179        for (long i = 0; i < 10; i++) {
180            statement.bindLong(1, i);
181            statement.clearBindings();
182            statement.execute();
183        }
184        statement.close();
185
186        Cursor c = mDatabase.query("test", null, null, null, null, null, "ROWID");
187        int numCol = c.getColumnIndexOrThrow("num");
188        assertTrue(c.moveToFirst());
189        for (long i = 0; i < 10; i++) {
190            assertTrue(c.isNull(numCol));
191            c.moveToNext();
192        }
193        c.close();
194    }
195
196    @MediumTest
197    public void testSimpleStringBinding() throws Exception {
198        mDatabase.execSQL("CREATE TABLE test (num TEXT, value TEXT);");
199        String statement = "INSERT INTO test (num, value) VALUES (?,?)";
200
201        String[] args = new String[2];
202        for (int i = 0; i < 2; i++) {
203            args[i] = Integer.toHexString(i);
204        }
205
206        mDatabase.execSQL(statement, args);
207
208        Cursor c = mDatabase.query("test", null, null, null, null, null, null);
209        int numCol = c.getColumnIndexOrThrow("num");
210        int valCol = c.getColumnIndexOrThrow("value");
211        c.moveToFirst();
212        String num = c.getString(numCol);
213        assertEquals(Integer.toHexString(0), num);
214
215        String val = c.getString(valCol);
216        assertEquals(Integer.toHexString(1), val);
217        c.close();
218    }
219
220    @MediumTest
221    public void testStatementMultipleBindings() throws Exception {
222        mDatabase.execSQL("CREATE TABLE test (num INTEGER, str TEXT);");
223        SQLiteStatement statement =
224                mDatabase.compileStatement("INSERT INTO test (num, str) VALUES (?, ?)");
225
226        for (long i = 0; i < 10; i++) {
227            statement.bindLong(1, i);
228            statement.bindString(2, Long.toHexString(i));
229            statement.execute();
230        }
231        statement.close();
232
233        Cursor c = mDatabase.query("test", null, null, null, null, null, "ROWID");
234        int numCol = c.getColumnIndexOrThrow("num");
235        int strCol = c.getColumnIndexOrThrow("str");
236        assertTrue(c.moveToFirst());
237        for (long i = 0; i < 10; i++) {
238            long num = c.getLong(numCol);
239            String str = c.getString(strCol);
240            assertEquals(i, num);
241            assertEquals(Long.toHexString(i), str);
242            c.moveToNext();
243        }
244        c.close();
245    }
246
247    private static class StatementTestThread extends Thread {
248        private SQLiteDatabase mDatabase;
249        private SQLiteStatement mStatement;
250
251        public StatementTestThread(SQLiteDatabase db, SQLiteStatement statement) {
252            super();
253            mDatabase = db;
254            mStatement = statement;
255        }
256
257        @Override
258        public void run() {
259            mDatabase.beginTransaction();
260            for (long i = 0; i < 10; i++) {
261                mStatement.bindLong(1, i);
262                mStatement.bindString(2, Long.toHexString(i));
263                mStatement.execute();
264            }
265            mDatabase.setTransactionSuccessful();
266            mDatabase.endTransaction();
267
268            Cursor c = mDatabase.query("test", null, null, null, null, null, "ROWID");
269            int numCol = c.getColumnIndexOrThrow("num");
270            int strCol = c.getColumnIndexOrThrow("str");
271            assertTrue(c.moveToFirst());
272            for (long i = 0; i < 10; i++) {
273                long num = c.getLong(numCol);
274                String str = c.getString(strCol);
275                assertEquals(i, num);
276                assertEquals(Long.toHexString(i), str);
277                c.moveToNext();
278            }
279            c.close();
280        }
281    }
282
283    @MediumTest
284    public void testStatementMultiThreaded() throws Exception {
285        mDatabase.execSQL("CREATE TABLE test (num INTEGER, str TEXT);");
286        SQLiteStatement statement =
287                mDatabase.compileStatement("INSERT INTO test (num, str) VALUES (?, ?)");
288
289        StatementTestThread thread = new StatementTestThread(mDatabase, statement);
290        thread.start();
291        try {
292            thread.join();
293        } finally {
294            statement.close();
295        }
296    }
297
298    @MediumTest
299    public void testStatementConstraint() throws Exception {
300        mDatabase.execSQL("CREATE TABLE test (num INTEGER NOT NULL);");
301        SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)");
302
303        // Try to insert NULL, which violates the constraint
304        try {
305            statement.clearBindings();
306            statement.execute();
307            fail("expected exception not thrown");
308        } catch (SQLiteConstraintException e) {
309            // expected
310        }
311
312        // Make sure the statement can still be used
313        statement.bindLong(1, 1);
314        statement.execute();
315        statement.close();
316
317        Cursor c = mDatabase.query("test", null, null, null, null, null, null);
318        int numCol = c.getColumnIndexOrThrow("num");
319        c.moveToFirst();
320        long num = c.getLong(numCol);
321        assertEquals(1, num);
322        c.close();
323    }
324}
325