11a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen/* 21a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * Copyright (C) 2007 The Android Open Source Project 31a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * 41a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * Licensed under the Apache License, Version 2.0 (the "License"); 51a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * you may not use this file except in compliance with the License. 61a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * You may obtain a copy of the License at 71a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * 81a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * http://www.apache.org/licenses/LICENSE-2.0 91a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * 101a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * Unless required by applicable law or agreed to in writing, software 111a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * distributed under the License is distributed on an "AS IS" BASIS, 121a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 131a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * See the License for the specific language governing permissions and 141a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen * limitations under the License. 151a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen */ 161a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 171a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenpackage android.database; 181a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 191a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.content.Context; 201a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.database.Cursor; 211a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.database.sqlite.SQLiteConstraintException; 221a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.database.sqlite.SQLiteDatabase; 231a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.database.sqlite.SQLiteDoneException; 241a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.database.sqlite.SQLiteStatement; 251a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.test.AndroidTestCase; 261a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.test.PerformanceTestCase; 271a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport android.test.suitebuilder.annotation.MediumTest; 281a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 291a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenimport java.io.File; 301a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 311a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyenpublic class DatabaseStatementTest extends AndroidTestCase implements PerformanceTestCase { 321a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 331a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private static final String sString1 = "this is a test"; 341a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private static final String sString2 = "and yet another test"; 351a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private static final String sString3 = "this string is a little longer, but still a test"; 361a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 371a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private static final int CURRENT_DATABASE_VERSION = 42; 381a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private SQLiteDatabase mDatabase; 391a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private File mDatabaseFile; 401a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 411a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @Override 421a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen protected void setUp() throws Exception { 431a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen super.setUp(); 441a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen File dbDir = getContext().getDir("tests", Context.MODE_PRIVATE); 451a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabaseFile = new File(dbDir, "database_test.db"); 461a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 471a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen if (mDatabaseFile.exists()) { 481a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabaseFile.delete(); 491a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 501a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); 511a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertNotNull(mDatabase); 521a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.setVersion(CURRENT_DATABASE_VERSION); 531a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 541a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 551a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @Override 561a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen protected void tearDown() throws Exception { 571a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.close(); 581a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabaseFile.delete(); 591a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen super.tearDown(); 601a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 611a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 621a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public boolean isPerformanceOnly() { 631a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen return false; 641a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 651a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 661a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen // These test can only be run once. 671a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public int startPerformance(Intermediates intermediates) { 681a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen return 1; 691a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 701a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 711a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private void populateDefaultTable() { 721a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 731a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 741a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString1 + "');"); 751a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString2 + "');"); 761a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString3 + "');"); 771a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 781a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 791a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 801a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testExecuteStatement() throws Exception { 811a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen populateDefaultTable(); 821a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement = mDatabase.compileStatement("DELETE FROM test"); 831a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.execute(); 841a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 851a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen Cursor c = mDatabase.query("test", null, null, null, null, null, null); 861a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(0, c.getCount()); 871a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.deactivate(); 881a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.close(); 891a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 901a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 911a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 921a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testSimpleQuery() throws Exception { 931a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (num INTEGER NOT NULL, str TEXT NOT NULL);"); 941a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("INSERT INTO test VALUES (1234, 'hello');"); 951a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement1 = 961a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.compileStatement("SELECT num FROM test WHERE str = ?"); 971a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement2 = 981a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.compileStatement("SELECT str FROM test WHERE num = ?"); 991a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1001a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen try { 1011a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement1.bindString(1, "hello"); 1021a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen long value = statement1.simpleQueryForLong(); 1031a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(1234, value); 1041a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1051a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement1.bindString(1, "world"); 1061a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement1.simpleQueryForLong(); 1071a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen fail("shouldn't get here"); 1081a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } catch (SQLiteDoneException e) { 1091a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen // expected 1101a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1111a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1121a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen try { 1131a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement2.bindLong(1, 1234); 1141a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen String value = statement1.simpleQueryForString(); 1151a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals("hello", value); 1161a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1171a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement2.bindLong(1, 5678); 1181a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement1.simpleQueryForString(); 1191a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen fail("shouldn't get here"); 1201a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } catch (SQLiteDoneException e) { 1211a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen // expected 1221a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1231a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1241a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement1.close(); 1251a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement2.close(); 1261a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1271a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1281a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 1291a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testStatementLongBinding() throws Exception { 1301a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 1311a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)"); 1321a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1331a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (int i = 0; i < 10; i++) { 1341a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.bindLong(1, i); 1351a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.execute(); 1361a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1371a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.close(); 1381a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1391a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen Cursor c = mDatabase.query("test", null, null, null, null, null, null); 1401a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int numCol = c.getColumnIndexOrThrow("num"); 1411a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToFirst(); 1421a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 1431a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen long num = c.getLong(numCol); 1441a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(i, num); 1451a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToNext(); 1461a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1471a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.close(); 1481a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1491a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1501a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 1511a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testStatementStringBinding() throws Exception { 1521a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (num TEXT);"); 1531a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)"); 1541a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1551a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 1561a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.bindString(1, Long.toHexString(i)); 1571a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.execute(); 1581a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1591a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.close(); 1601a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1611a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen Cursor c = mDatabase.query("test", null, null, null, null, null, null); 1621a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int numCol = c.getColumnIndexOrThrow("num"); 1631a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToFirst(); 1641a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 1651a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen String num = c.getString(numCol); 1661a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(Long.toHexString(i), num); 1671a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToNext(); 1681a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1691a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.close(); 1701a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1711a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1721a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 1731a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testStatementClearBindings() throws Exception { 1741a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 1751a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)"); 1761a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1771a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 1781a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.bindLong(1, i); 1791a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.clearBindings(); 1801a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.execute(); 1811a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1821a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.close(); 1831a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1841a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen Cursor c = mDatabase.query("test", null, null, null, null, null, "ROWID"); 1851a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int numCol = c.getColumnIndexOrThrow("num"); 1861a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertTrue(c.moveToFirst()); 1871a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 1881a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertTrue(c.isNull(numCol)); 1891a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToNext(); 1901a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1911a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.close(); 1921a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 1931a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1941a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 1951a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testSimpleStringBinding() throws Exception { 1961a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (num TEXT, value TEXT);"); 1971a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen String statement = "INSERT INTO test (num, value) VALUES (?,?)"; 1981a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 1991a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen String[] args = new String[2]; 2001a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (int i = 0; i < 2; i++) { 2011a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen args[i] = Integer.toHexString(i); 2021a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2031a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2041a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL(statement, args); 2051a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2061a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen Cursor c = mDatabase.query("test", null, null, null, null, null, null); 2071a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int numCol = c.getColumnIndexOrThrow("num"); 2081a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int valCol = c.getColumnIndexOrThrow("value"); 2091a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToFirst(); 2101a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen String num = c.getString(numCol); 2111a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(Integer.toHexString(0), num); 2121a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2131a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen String val = c.getString(valCol); 2141a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(Integer.toHexString(1), val); 2151a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.close(); 2161a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2171a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2181a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 2191a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testStatementMultipleBindings() throws Exception { 2201a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (num INTEGER, str TEXT);"); 2211a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement = 2221a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.compileStatement("INSERT INTO test (num, str) VALUES (?, ?)"); 2231a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2241a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 2251a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.bindLong(1, i); 2261a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.bindString(2, Long.toHexString(i)); 2271a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.execute(); 2281a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2291a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.close(); 2301a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2311a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen Cursor c = mDatabase.query("test", null, null, null, null, null, "ROWID"); 2321a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int numCol = c.getColumnIndexOrThrow("num"); 2331a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int strCol = c.getColumnIndexOrThrow("str"); 2341a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertTrue(c.moveToFirst()); 2351a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 2361a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen long num = c.getLong(numCol); 2371a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen String str = c.getString(strCol); 2381a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(i, num); 2391a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(Long.toHexString(i), str); 2401a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToNext(); 2411a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2421a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.close(); 2431a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2441a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2451a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private static class StatementTestThread extends Thread { 2461a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private SQLiteDatabase mDatabase; 2471a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen private SQLiteStatement mStatement; 2481a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2491a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public StatementTestThread(SQLiteDatabase db, SQLiteStatement statement) { 2501a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen super(); 2511a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase = db; 2521a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mStatement = statement; 2531a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2541a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2551a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @Override 2561a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void run() { 2571a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.beginTransaction(); 2581a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 2591a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mStatement.bindLong(1, i); 2601a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mStatement.bindString(2, Long.toHexString(i)); 2611a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mStatement.execute(); 2621a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2631a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.setTransactionSuccessful(); 2641a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.endTransaction(); 2651a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2661a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen Cursor c = mDatabase.query("test", null, null, null, null, null, "ROWID"); 2671a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int numCol = c.getColumnIndexOrThrow("num"); 2681a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int strCol = c.getColumnIndexOrThrow("str"); 2691a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertTrue(c.moveToFirst()); 2701a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen for (long i = 0; i < 10; i++) { 2711a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen long num = c.getLong(numCol); 2721a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen String str = c.getString(strCol); 2731a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(i, num); 2741a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(Long.toHexString(i), str); 2751a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToNext(); 2761a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2771a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.close(); 2781a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2791a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2801a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2811a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 2821a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testStatementMultiThreaded() throws Exception { 2831a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (num INTEGER, str TEXT);"); 2841a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement = 2851a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.compileStatement("INSERT INTO test (num, str) VALUES (?, ?)"); 2861a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2871a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen StatementTestThread thread = new StatementTestThread(mDatabase, statement); 2881a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen thread.start(); 2891a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen try { 2901a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen thread.join(); 2911a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } finally { 2921a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.close(); 2931a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2941a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 2951a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 2961a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen @MediumTest 2971a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen public void testStatementConstraint() throws Exception { 2981a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen mDatabase.execSQL("CREATE TABLE test (num INTEGER NOT NULL);"); 2991a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen SQLiteStatement statement = mDatabase.compileStatement("INSERT INTO test (num) VALUES (?)"); 3001a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 3011a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen // Try to insert NULL, which violates the constraint 3021a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen try { 3031a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.clearBindings(); 3041a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.execute(); 3051a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen fail("expected exception not thrown"); 3061a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } catch (SQLiteConstraintException e) { 3071a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen // expected 3081a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 3091a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 3101a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen // Make sure the statement can still be used 3111a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.bindLong(1, 1); 3121a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.execute(); 3131a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen statement.close(); 3141a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen 3151a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen Cursor c = mDatabase.query("test", null, null, null, null, null, null); 3161a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen int numCol = c.getColumnIndexOrThrow("num"); 3171a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.moveToFirst(); 3181a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen long num = c.getLong(numCol); 3191a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen assertEquals(1, num); 3201a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen c.close(); 3211a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen } 3221a44d5dcabc18cd5ef111f732ccff91683a1a093Neal Nguyen} 323