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