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