1 /* 2 * Copyright (C) 2009 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.cts; 18 19 20 import android.content.ContentValues; 21 import android.content.Context; 22 import android.database.Cursor; 23 import android.database.DatabaseUtils.InsertHelper; 24 import android.database.sqlite.SQLiteDatabase; 25 import android.test.AndroidTestCase; 26 import android.test.MoreAsserts; 27 28 public class DatabaseUtils_InsertHelperTest extends AndroidTestCase { 29 private static final String TEST_TABLE_NAME = "test"; 30 private static final String DATABASE_NAME = "database_test.db"; 31 32 private SQLiteDatabase mDatabase; 33 private InsertHelper mInsertHelper; 34 35 @Override 36 protected void setUp() throws Exception { 37 super.setUp(); 38 getContext().deleteDatabase(DATABASE_NAME); 39 mDatabase = getContext().openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null); 40 assertNotNull(mDatabase); 41 mInsertHelper = new InsertHelper(mDatabase, TEST_TABLE_NAME); 42 } 43 44 @Override 45 protected void tearDown() throws Exception { 46 mInsertHelper.close(); 47 mDatabase.close(); 48 getContext().deleteDatabase(DATABASE_NAME); 49 super.tearDown(); 50 } 51 52 public void testConstructor() { 53 new InsertHelper(mDatabase, TEST_TABLE_NAME); 54 } 55 56 public void testClose() { 57 mInsertHelper.close(); 58 } 59 60 public void testGetColumnIndex() { 61 mDatabase.execSQL("CREATE TABLE " + TEST_TABLE_NAME + " (_id INTEGER PRIMARY KEY, " + 62 "name TEXT, age INTEGER, address TEXT);"); 63 assertEquals(1, mInsertHelper.getColumnIndex("_id")); 64 assertEquals(2, mInsertHelper.getColumnIndex("name")); 65 assertEquals(3, mInsertHelper.getColumnIndex("age")); 66 assertEquals(4, mInsertHelper.getColumnIndex("address")); 67 try { 68 mInsertHelper.getColumnIndex("missing_column"); 69 fail("Should throw exception (column does not exist)"); 70 } catch (IllegalArgumentException expected) { 71 // expected 72 } 73 } 74 75 public void testInsert() { 76 mDatabase.execSQL("CREATE TABLE " + TEST_TABLE_NAME + "(_id INTEGER PRIMARY KEY," + 77 " boolean_value INTEGER, int_value INTEGER, long_value INTEGER," + 78 " double_value DOUBLE, float_value DOUBLE, string_value TEXT," + 79 " blob_value BLOB, null_value TEXT);"); 80 final int booleanValueIndex = 1; 81 final int intValueIndex = 2; 82 final int longValueIndex = 3; 83 final int doubleValueIndex = 4; 84 final int floatValueIndex = 5; 85 final int stringValueIndex = 6; 86 final int blobValueIndex = 7; 87 final int nullValueIndex = 8; 88 final String[] projection = new String[] { 89 "_id", // index 0 90 "boolean_value", // index 1 91 "int_value", // index 2 92 "long_value", // index 3 93 "double_value", // index 4 94 "float_value", // index 5 95 "string_value", // index 6 96 "blob_value", // index 7 97 "null_value" // index 8 98 }; 99 100 Cursor cursor = mDatabase.query(TEST_TABLE_NAME, projection, null, null, null, null, null); 101 assertNotNull(cursor); 102 assertEquals(0, cursor.getCount()); 103 cursor.close(); 104 105 try { 106 mInsertHelper.execute(); 107 fail("Should throw exception (execute without prepare)"); 108 } catch (IllegalStateException expected) { 109 // expected 110 } 111 112 mInsertHelper.prepareForInsert(); 113 mInsertHelper.bind(mInsertHelper.getColumnIndex("boolean_value"), true); 114 mInsertHelper.bind(mInsertHelper.getColumnIndex("int_value"), 10); 115 mInsertHelper.bind(mInsertHelper.getColumnIndex("long_value"), 1000L); 116 mInsertHelper.bind(mInsertHelper.getColumnIndex("double_value"), 123.456); 117 mInsertHelper.bind(mInsertHelper.getColumnIndex("float_value"), 1.0f); 118 mInsertHelper.bind(mInsertHelper.getColumnIndex("string_value"), "test insert"); 119 byte[] blob = new byte[] { '1', '2', '3' }; 120 mInsertHelper.bind(mInsertHelper.getColumnIndex("blob_value"), blob); 121 mInsertHelper.bindNull(mInsertHelper.getColumnIndex("null_value")); 122 long id = mInsertHelper.execute(); 123 assertEquals(1, id); 124 125 cursor = mDatabase.query(TEST_TABLE_NAME, projection, null, null, null, null, null); 126 assertNotNull(cursor); 127 assertEquals(1, cursor.getCount()); 128 cursor.moveToFirst(); 129 assertEquals(1, cursor.getInt(booleanValueIndex)); 130 assertEquals(10, cursor.getInt(intValueIndex)); 131 assertEquals(1000L, cursor.getLong(longValueIndex)); 132 assertEquals(123.456, cursor.getDouble(doubleValueIndex)); 133 assertEquals(1.0f, cursor.getFloat(floatValueIndex)); 134 assertEquals("test insert", cursor.getString(stringValueIndex)); 135 byte[] value = cursor.getBlob(blobValueIndex); 136 MoreAsserts.assertEquals(blob, value); 137 assertNull(cursor.getString(nullValueIndex)); 138 cursor.close(); 139 140 // try inserting a conflicting column -> should return -1 141 mInsertHelper.prepareForInsert(); 142 mInsertHelper.bind(mInsertHelper.getColumnIndex("_id"), id); 143 assertEquals(-1, mInsertHelper.execute()); 144 145 // subsequent insert() should ignore existing bindings 146 ContentValues values = new ContentValues(); 147 values.put("boolean_value", false); 148 values.put("int_value", 123); 149 values.put("long_value", 987654L); 150 values.put("double_value", 654.321); 151 values.put("float_value", 21.1f); 152 values.put("string_value", "insert another row"); 153 values.put("blob_value", blob); 154 values.putNull("null_value"); 155 id = mInsertHelper.insert(values); 156 assertEquals(2, id); 157 cursor = mDatabase.query(TEST_TABLE_NAME, projection, "_id = " + id, 158 null, null, null, null); 159 assertNotNull(cursor); 160 cursor.moveToFirst(); 161 assertEquals(0, cursor.getInt(booleanValueIndex)); 162 assertEquals(123, cursor.getInt(intValueIndex)); 163 assertEquals(987654L, cursor.getLong(longValueIndex)); 164 assertEquals(654.321, cursor.getDouble(doubleValueIndex)); 165 assertEquals(21.1f, cursor.getFloat(floatValueIndex)); 166 assertEquals("insert another row", cursor.getString(stringValueIndex)); 167 value = cursor.getBlob(blobValueIndex); 168 MoreAsserts.assertEquals(blob, value); 169 assertNull(cursor.getString(nullValueIndex)); 170 cursor.close(); 171 172 // try inserting a conflicting column -> should return -1 173 values.put("_id", id); 174 assertEquals(-1, mInsertHelper.insert(values)); 175 } 176 177 public void testReplace() { 178 mDatabase.execSQL("CREATE TABLE " + TEST_TABLE_NAME + "(_id INTEGER PRIMARY KEY," + 179 " boolean_value INTEGER, int_value INTEGER, long_value INTEGER," + 180 " double_value DOUBLE, float_value DOUBLE, string_value TEXT," + 181 " blob_value BLOB, null_value TEXT);"); 182 final int booleanValueIndex = 1; 183 final int intValueIndex = 2; 184 final int longValueIndex = 3; 185 final int doubleValueIndex = 4; 186 final int floatValueIndex = 5; 187 final int stringValueIndex = 6; 188 final int blobValueIndex = 7; 189 final int nullValueIndex = 8; 190 final String[] projection = new String[] { 191 "_id", // index 0 192 "boolean_value", // index 1 193 "int_value", // index 2 194 "long_value", // index 3 195 "double_value", // index 4 196 "float_value", // index 5 197 "string_value", // index 6 198 "blob_value", // index 7 199 "null_value" // index 8 200 }; 201 202 Cursor cursor = mDatabase.query(TEST_TABLE_NAME, projection, null, null, null, null, null); 203 assertNotNull(cursor); 204 assertEquals(0, cursor.getCount()); 205 cursor.close(); 206 207 // without specifying a key, this becomes an insert 208 mInsertHelper.prepareForReplace(); 209 mInsertHelper.bind(mInsertHelper.getColumnIndex("boolean_value"), true); 210 mInsertHelper.bind(mInsertHelper.getColumnIndex("int_value"), 10); 211 mInsertHelper.bind(mInsertHelper.getColumnIndex("long_value"), 1000L); 212 mInsertHelper.bind(mInsertHelper.getColumnIndex("double_value"), 123.456); 213 mInsertHelper.bind(mInsertHelper.getColumnIndex("float_value"), 1.0f); 214 mInsertHelper.bind(mInsertHelper.getColumnIndex("string_value"), "test insert"); 215 byte[] blob = new byte[] { '1', '2', '3' }; 216 mInsertHelper.bind(mInsertHelper.getColumnIndex("blob_value"), blob); 217 mInsertHelper.bindNull(mInsertHelper.getColumnIndex("null_value")); 218 long id = mInsertHelper.execute(); 219 assertEquals(1, id); 220 221 cursor = mDatabase.query(TEST_TABLE_NAME, projection, null, null, null, null, null); 222 assertNotNull(cursor); 223 assertEquals(1, cursor.getCount()); 224 cursor.moveToFirst(); 225 assertEquals(1, cursor.getInt(booleanValueIndex)); 226 assertEquals(10, cursor.getInt(intValueIndex)); 227 assertEquals(1000L, cursor.getLong(longValueIndex)); 228 assertEquals(123.456, cursor.getDouble(doubleValueIndex)); 229 assertEquals(1.0f, cursor.getFloat(floatValueIndex)); 230 assertEquals("test insert", cursor.getString(stringValueIndex)); 231 byte[] value = cursor.getBlob(blobValueIndex); 232 MoreAsserts.assertEquals(blob, value); 233 assertNull(cursor.getString(nullValueIndex)); 234 cursor.close(); 235 236 mInsertHelper.prepareForReplace(); 237 mInsertHelper.bind(mInsertHelper.getColumnIndex("_id"), id); 238 mInsertHelper.bind(mInsertHelper.getColumnIndex("int_value"), 42); 239 mInsertHelper.execute(); 240 cursor = mDatabase.query(TEST_TABLE_NAME, projection, null, null, null, null, null); 241 assertNotNull(cursor); 242 assertEquals(1, cursor.getCount()); 243 cursor.moveToFirst(); 244 assertEquals(42, cursor.getInt(intValueIndex)); 245 // previous bindings are forgotten 246 assertNull(cursor.getString(stringValueIndex)); 247 cursor.close(); 248 249 // illegal primary key -> should return -1 250 mInsertHelper.prepareForReplace(); 251 mInsertHelper.bind(mInsertHelper.getColumnIndex("_id"), "illegal_id"); 252 assertEquals(-1, mInsertHelper.execute()); 253 254 ContentValues values = new ContentValues(); 255 // will replace row id 256 values.put("_id", id); 257 values.put("boolean_value", false); 258 values.put("int_value", 123); 259 values.put("long_value", 987654L); 260 values.put("double_value", 654.321); 261 values.put("float_value", 21.1f); 262 values.put("string_value", "replace the row"); 263 values.put("blob_value", blob); 264 values.putNull("null_value"); 265 id = mInsertHelper.replace(values); 266 assertEquals(1, id); 267 cursor = mDatabase.query(TEST_TABLE_NAME, projection, null, null, null, null, null); 268 assertEquals(1, cursor.getCount()); 269 assertNotNull(cursor); 270 cursor.moveToFirst(); 271 assertEquals(0, cursor.getInt(booleanValueIndex)); 272 assertEquals(123, cursor.getInt(intValueIndex)); 273 assertEquals(987654L, cursor.getLong(longValueIndex)); 274 assertEquals(654.321, cursor.getDouble(doubleValueIndex)); 275 assertEquals(21.1f, cursor.getFloat(floatValueIndex)); 276 assertEquals("replace the row", cursor.getString(stringValueIndex)); 277 value = cursor.getBlob(blobValueIndex); 278 MoreAsserts.assertEquals(blob, value); 279 assertNull(cursor.getString(nullValueIndex)); 280 cursor.close(); 281 282 // illegal primary key -> should return -1 283 values.put("_id", "illegal_id"); 284 assertEquals(-1, mInsertHelper.replace(values)); 285 } 286 } 287
