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