SQLite Android Bindings

/*
 * Copyright (C) 2009 The Android Open Source Project
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.sqlite.database;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Locale;
import java.util.concurrent.Semaphore;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import org.sqlite.database.DatabaseUtils;
import org.sqlite.database.SQLException;
import org.sqlite.database.sqlite.SQLiteCursor;
import org.sqlite.database.sqlite.SQLiteCursorDriver;
import org.sqlite.database.sqlite.SQLiteDatabase;
import org.sqlite.database.sqlite.SQLiteDatabase.CursorFactory;
import org.sqlite.database.sqlite.SQLiteException;
import org.sqlite.database.sqlite.SQLiteQuery;
import org.sqlite.database.sqlite.SQLiteStatement;
import org.sqlite.database.sqlite.SQLiteTransactionListener;
import android.test.AndroidTestCase;
import android.test.MoreAsserts;
import android.test.suitebuilder.annotation.LargeTest;
import android.test.suitebuilder.annotation.SmallTest;

public class SQLiteDatabaseTest extends AndroidTestCase {
    private SQLiteDatabase mDatabase;
    private File mDatabaseFile;
    private String mDatabaseFilePath;
    private String mDatabaseDir;

    private boolean mTransactionListenerOnBeginCalled;
    private boolean mTransactionListenerOnCommitCalled;
    private boolean mTransactionListenerOnRollbackCalled;

    private static final String DATABASE_FILE_NAME = "database_test.db";
    private static final String TABLE_NAME = "test";
    private static final int COLUMN_ID_INDEX = 0;
    private static final int COLUMN_NAME_INDEX = 1;
    private static final int COLUMN_AGE_INDEX = 2;
    private static final int COLUMN_ADDR_INDEX = 3;
    private static final String[] TEST_PROJECTION = new String[] {
            "_id",      // 0
            "name",     // 1
            "age",      // 2
            "address"   // 3
    };

    @Override
    protected void setUp() throws Exception {
        super.setUp();

        System.loadLibrary("sqliteX");
        getContext().deleteDatabase(DATABASE_FILE_NAME);
        mDatabaseFilePath = getContext().getDatabasePath(DATABASE_FILE_NAME).getPath();
        mDatabaseFile = getContext().getDatabasePath(DATABASE_FILE_NAME);
        mDatabaseDir = mDatabaseFile.getParent();
        mDatabaseFile.getParentFile().mkdirs(); // directory may not exist
        mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile, null);
        assertNotNull(mDatabase);

        mTransactionListenerOnBeginCalled = false;
        mTransactionListenerOnCommitCalled = false;
        mTransactionListenerOnRollbackCalled = false;
    }

    @Override
    protected void tearDown() throws Exception {
        mDatabase.close();
        mDatabaseFile.delete();
        super.tearDown();
    }

    public void testOpenDatabase() {
        CursorFactory factory = new CursorFactory() {
            public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery,
                    String editTable, SQLiteQuery query) {
                return new MockSQLiteCursor(db, masterQuery, editTable, query);
            }
        };

        SQLiteDatabase db = SQLiteDatabase.openDatabase(mDatabaseFilePath,
                factory, SQLiteDatabase.CREATE_IF_NECESSARY);
        assertNotNull(db);
        db.close();

        File dbFile = new File(mDatabaseDir, "database_test12345678.db");
        dbFile.delete();
        assertFalse(dbFile.exists());
        db = SQLiteDatabase.openOrCreateDatabase(dbFile.getPath(), factory);
        assertNotNull(db);
        db.close();
        dbFile.delete();

        dbFile = new File(mDatabaseDir, DATABASE_FILE_NAME);
        db = SQLiteDatabase.openOrCreateDatabase(dbFile, factory);
        assertNotNull(db);
        db.close();
        dbFile.delete();

        db = SQLiteDatabase.create(factory);
        assertNotNull(db);
        db.close();
    }

    public void testDeleteDatabase() throws IOException {
        File dbFile = new File(mDatabaseDir, "database_test12345678.db");
        File journalFile = new File(dbFile.getPath() + "-journal");
        File shmFile = new File(dbFile.getPath() + "-shm");
        File walFile = new File(dbFile.getPath() + "-wal");
        File mjFile1 = new File(dbFile.getPath() + "-mj00000000");
        File mjFile2 = new File(dbFile.getPath() + "-mj00000001");
        File innocentFile = new File(dbFile.getPath() + "-innocent");

        dbFile.createNewFile();
        journalFile.createNewFile();
        shmFile.createNewFile();
        walFile.createNewFile();
        mjFile1.createNewFile();
        mjFile2.createNewFile();
        innocentFile.createNewFile();

        boolean deleted = SQLiteDatabase.deleteDatabase(dbFile);
        assertTrue(deleted);

        assertFalse(dbFile.exists());
        assertFalse(journalFile.exists());
        assertFalse(shmFile.exists());
        assertFalse(walFile.exists());
        assertFalse(mjFile1.exists());
        assertFalse(mjFile2.exists());
        assertTrue(innocentFile.exists());

        innocentFile.delete();

        boolean deletedAgain = SQLiteDatabase.deleteDatabase(dbFile);
        assertFalse(deletedAgain);
    }

    private class MockSQLiteCursor extends SQLiteCursor {
        public MockSQLiteCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
                String editTable, SQLiteQuery query) {
            super(db, driver, editTable, query);
        }
    }

    public void testTransaction() {
        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");

        // test execSQL without any explicit transactions.
        setNum(1);
        assertNum(1);

        // Test a single-level transaction.
        setNum(0);
        assertFalse(mDatabase.inTransaction());
        mDatabase.beginTransaction();
        assertTrue(mDatabase.inTransaction());
        setNum(1);
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        assertFalse(mDatabase.inTransaction());
        assertNum(1);
        assertFalse(mDatabase.isDbLockedByCurrentThread());
        assertFalse(mDatabase.isDbLockedByOtherThreads());

        // Test a rolled-back transaction.
        setNum(0);
        assertFalse(mDatabase.inTransaction());
        mDatabase.beginTransaction();
        setNum(1);
        assertTrue(mDatabase.inTransaction());
        mDatabase.endTransaction();
        assertFalse(mDatabase.inTransaction());
        assertNum(0);
        assertFalse(mDatabase.isDbLockedByCurrentThread());
        assertFalse(mDatabase.isDbLockedByOtherThreads());

        // it should throw IllegalStateException if we end a non-existent transaction.
        assertThrowsIllegalState(new Runnable() {
            public void run() {
                mDatabase.endTransaction();
            }
        });

        // it should throw IllegalStateException if a set a non-existent transaction as clean.
        assertThrowsIllegalState(new Runnable() {
            public void run() {
                mDatabase.setTransactionSuccessful();
            }
        });

        mDatabase.beginTransaction();
        mDatabase.setTransactionSuccessful();
        // it should throw IllegalStateException if we mark a transaction as clean twice.
        assertThrowsIllegalState(new Runnable() {
            public void run() {
                mDatabase.setTransactionSuccessful();
            }
        });
        // it should throw IllegalStateException if we begin a transaction after marking the
        // parent as clean.
        assertThrowsIllegalState(new Runnable() {
            public void run() {
                mDatabase.beginTransaction();
            }
        });
        mDatabase.endTransaction();
        assertFalse(mDatabase.isDbLockedByCurrentThread());
        assertFalse(mDatabase.isDbLockedByOtherThreads());

        assertFalse(mDatabase.inTransaction());
        // Test a two-level transaction.
        setNum(0);
        mDatabase.beginTransaction();
        assertTrue(mDatabase.inTransaction());
        mDatabase.beginTransaction();
        assertTrue(mDatabase.inTransaction());
        setNum(1);
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        assertTrue(mDatabase.inTransaction());
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        assertFalse(mDatabase.inTransaction());
        assertNum(1);
        assertFalse(mDatabase.isDbLockedByCurrentThread());
        assertFalse(mDatabase.isDbLockedByOtherThreads());

        // Test rolling back an inner transaction.
        setNum(0);
        mDatabase.beginTransaction();
        mDatabase.beginTransaction();
        setNum(1);
        mDatabase.endTransaction();
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        assertNum(0);
        assertFalse(mDatabase.isDbLockedByCurrentThread());
        assertFalse(mDatabase.isDbLockedByOtherThreads());

        // Test rolling back an outer transaction.
        setNum(0);
        mDatabase.beginTransaction();
        mDatabase.beginTransaction();
        setNum(1);
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        mDatabase.endTransaction();
        assertNum(0);
        assertFalse(mDatabase.isDbLockedByCurrentThread());
        assertFalse(mDatabase.isDbLockedByOtherThreads());
    }

    private void setNum(int num) {
        mDatabase.execSQL("UPDATE test SET num = " + num);
    }

    private void assertNum(int num) {
        assertEquals(num, DatabaseUtils.longForQuery(mDatabase,
                "SELECT num FROM test", null));
    }

    private void assertThrowsIllegalState(Runnable r) {
        try {
            r.run();
            fail("did not throw expected IllegalStateException");
        } catch (IllegalStateException e) {
        }
    }

    public void testAccessMaximumSize() {
        long curMaximumSize = mDatabase.getMaximumSize();

        // the new maximum size is less than the current size.
        mDatabase.setMaximumSize(curMaximumSize - 1);
        assertEquals(curMaximumSize, mDatabase.getMaximumSize());

        // the new maximum size is more than the current size.
        mDatabase.setMaximumSize(curMaximumSize + 1);
        assertEquals(curMaximumSize + mDatabase.getPageSize(), mDatabase.getMaximumSize());
        assertTrue(mDatabase.getMaximumSize() > curMaximumSize);
    }

    public void testAccessPageSize() {
        File databaseFile = new File(mDatabaseDir, "database.db");
        if (databaseFile.exists()) {
            databaseFile.delete();
        }
        SQLiteDatabase database = null;
        try {
            database = SQLiteDatabase.openOrCreateDatabase(databaseFile.getPath(), null);

            long initialValue = database.getPageSize();
            // check that this does not throw an exception
            // setting a different page size may not be supported after the DB has been created
            database.setPageSize(initialValue);
            assertEquals(initialValue, database.getPageSize());

        } finally {
            if (database != null) {
                database.close();
                databaseFile.delete();
            }
        }
    }

    public void testCompileStatement() {
        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
                + "name TEXT, age INTEGER, address TEXT);");

        String name = "Mike";
        int age = 21;
        String address = "LA";

        // at the beginning, there is no record in the database.
        Cursor cursor = mDatabase.query("test", TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(0, cursor.getCount());

        String sql = "INSERT INTO test (name, age, address) VALUES (?, ?, ?);";
        SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
        DatabaseUtils.bindObjectToProgram(insertStatement, 1, name);
        DatabaseUtils.bindObjectToProgram(insertStatement, 2, age);
        DatabaseUtils.bindObjectToProgram(insertStatement, 3, address);
        insertStatement.execute();
        insertStatement.close();
        cursor.close();

        cursor = mDatabase.query("test", TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToNext();
        assertEquals(name, cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(age, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals(address, cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test");
        deleteStatement.execute();

        cursor = mDatabase.query("test", null, null, null, null, null, null);
        assertEquals(0, cursor.getCount());
        cursor.deactivate();
        deleteStatement.close();
        cursor.close();
    }

    public void testDelete() {
        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
                + "name TEXT, age INTEGER, address TEXT);");
        mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');");
        mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');");
        mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jim', 35, 'Chicago');");

        // delete one record.
        int count = mDatabase.delete(TABLE_NAME, "name = 'Mike'", null);
        assertEquals(1, count);

        Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null,
                null, null, null, null);
        assertNotNull(cursor);
        // there are 2 records here.
        assertEquals(2, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.moveToNext();
        assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(35, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("Chicago", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        // delete another record.
        count = mDatabase.delete(TABLE_NAME, "name = ?", new String[] { "Jack" });
        assertEquals(1, count);

        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
                null, null);
        assertNotNull(cursor);
        // there are 1 records here.
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(35, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("Chicago", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');");
        mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');");

        // delete all records.
        count = mDatabase.delete(TABLE_NAME, null, null);
        assertEquals(3, count);

        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(0, cursor.getCount());
        cursor.close();
    }

    public void testExecSQL() {
        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
                + "name TEXT, age INTEGER, address TEXT);");

        // add a new record.
        mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', 20, 'LA');");

        Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null,
                null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        // add other new record.
        mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Jack', 30, 'London');");

        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.moveToNext();
        assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        // delete a record.
        mDatabase.execSQL("DELETE FROM test WHERE name = ?;", new String[] { "Jack" });

        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        // delete a non-exist record.
        mDatabase.execSQL("DELETE FROM test WHERE name = ?;", new String[] { "Wrong Name" });

        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        try {
            // execSQL can not use for query.
            mDatabase.execSQL("SELECT * FROM test;");
            fail("should throw SQLException.");
        } catch (SQLException e) {
        }

        // make sure execSQL can't be used to execute more than 1 sql statement at a time
        mDatabase.execSQL("UPDATE test SET age = 40 WHERE name = 'Mike';" + 
                "UPDATE test SET age = 50 WHERE name = 'Mike';");
        // age should be updated to 40 not to 50
        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        // make sure sql injection is NOT allowed or has no effect when using query()
        String harmfulQuery = "name = 'Mike';UPDATE test SET age = 50 WHERE name = 'Mike'";
        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, harmfulQuery, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        // row's age column SHOULD NOT be 50
        assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();;
    }

    public void testFindEditTable() {
        String tables = "table1 table2 table3";
        assertEquals("table1", SQLiteDatabase.findEditTable(tables));

        tables = "table1,table2,table3";
        assertEquals("table1", SQLiteDatabase.findEditTable(tables));

        tables = "table1";
        assertEquals("table1", SQLiteDatabase.findEditTable(tables));

        try {
            SQLiteDatabase.findEditTable("");
            fail("should throw IllegalStateException.");
        } catch (IllegalStateException e) {
        }
    }

    public void testGetPath() {
        assertEquals(mDatabaseFilePath, mDatabase.getPath());
    }

    public void testAccessVersion() {
        mDatabase.setVersion(1);
        assertEquals(1, mDatabase.getVersion());

        mDatabase.setVersion(3);
        assertEquals(3, mDatabase.getVersion());
    }

    public void testInsert() {
        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
                + "name TEXT, age INTEGER, address TEXT);");

        ContentValues values = new ContentValues();
        values.put("name", "Jack");
        values.put("age", 20);
        values.put("address", "LA");
        mDatabase.insert(TABLE_NAME, "name", values);

        Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null,
                null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        mDatabase.insert(TABLE_NAME, "name", null);
        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
                null, null);
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.moveToNext();
        assertNull(cursor.getString(COLUMN_NAME_INDEX));
        cursor.close();

        values = new ContentValues();
        values.put("Wrong Key", "Wrong value");
        mDatabase.insert(TABLE_NAME, "name", values);
        // there are still 2 records.
        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
                null, null);
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());
        cursor.close();

        // delete all record.
        mDatabase.execSQL("DELETE FROM test;");

        values = new ContentValues();
        values.put("name", "Mike");
        values.put("age", 30);
        values.put("address", "London");
        mDatabase.insertOrThrow(TABLE_NAME, "name", values);

        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
                null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        mDatabase.insertOrThrow(TABLE_NAME, "name", null);
        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null,
                null, null);
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(30, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.moveToNext();
        assertNull(cursor.getString(COLUMN_NAME_INDEX));
        cursor.close();

        values = new ContentValues();
        values.put("Wrong Key", "Wrong value");
        try {
            mDatabase.insertOrThrow(TABLE_NAME, "name", values);
            fail("should throw SQLException.");
        } catch (SQLException e) {
        }
    }

    public void testIsOpen() {
        assertTrue(mDatabase.isOpen());

        mDatabase.close();
        assertFalse(mDatabase.isOpen());
    }

    public void testIsReadOnly() {
        assertFalse(mDatabase.isReadOnly());

        SQLiteDatabase database = null;
        try {
            database = SQLiteDatabase.openDatabase(mDatabaseFilePath, null,
                    SQLiteDatabase.OPEN_READONLY);
            assertTrue(database.isReadOnly());
        } finally {
            if (database != null) {
                database.close();
            }
        }
    }

    public void testReleaseMemory() {
        SQLiteDatabase.releaseMemory();
    }

    public void testSetLockingEnabled() {
        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");

        mDatabase.setLockingEnabled(false);

        mDatabase.beginTransaction();
        setNum(1);
        assertNum(1);
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
    }

    @SuppressWarnings("deprecation")
    public void testYieldIfContendedWhenNotContended() {
        assertFalse(mDatabase.yieldIfContended());

        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");

        // Make sure that things work outside an explicit transaction.
        setNum(1);
        assertNum(1);

        setNum(0);
        assertFalse(mDatabase.inTransaction());
        mDatabase.beginTransaction();
        assertTrue(mDatabase.inTransaction());
        assertFalse(mDatabase.yieldIfContended());
        setNum(1);
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();

        mDatabase.beginTransaction();
        assertTrue(mDatabase.inTransaction());
        assertFalse(mDatabase.yieldIfContendedSafely());
        setNum(1);
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
    }

    @SuppressWarnings("deprecation")
    public void testYieldIfContendedWhenContended() throws Exception {
        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");

        // Begin a transaction and update a value.
        mDatabase.beginTransaction();
        setNum(1);
        assertNum(1);

        // On another thread, begin a transaction there.  This causes contention
        // for use of the database.  When the main thread yields, the second thread
        // begin its own transaction.  It should perceive the new state that was
        // committed by the main thread when it yielded.
        final Semaphore s = new Semaphore(0);
        Thread t = new Thread() {
            @Override
            public void run() {
                s.release(); // let main thread continue

                mDatabase.beginTransaction();
                assertNum(1);
                setNum(2);
                assertNum(2);
                mDatabase.setTransactionSuccessful();
                mDatabase.endTransaction();
            }
        };
        t.start();

        // Wait for thread to try to begin its transaction.
        s.acquire();
        Thread.sleep(500);

        // Yield.  There should be contention for the database now, so yield will
        // return true.
        assertTrue(mDatabase.yieldIfContendedSafely());

        // Since we reacquired the transaction, the other thread must have finished
        // its transaction.  We should observe its changes and our own within this transaction.
        assertNum(2);
        setNum(3);
        assertNum(3);

        // Go ahead and finish the transaction.
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        assertNum(3);

        t.join();
    }

    public void testQuery() {
        mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " +
                "name TEXT, month INTEGER, salary INTEGER);");
        mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
                "VALUES ('Mike', '1', '1000');");
        mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
                "VALUES ('Mike', '2', '3000');");
        mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
                "VALUES ('jack', '1', '2000');");
        mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
                "VALUES ('jack', '3', '1500');");
        mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
                "VALUES ('Jim', '1', '1000');");
        mDatabase.execSQL("INSERT INTO employee (name, month, salary) " +
                "VALUES ('Jim', '3', '3500');");

        Cursor cursor = mDatabase.query(true, "employee", new String[] { "name", "sum(salary)" },
                null, null, "name", "sum(salary)>1000", "name", null);
        assertNotNull(cursor);
        assertEquals(3, cursor.getCount());

        final int COLUMN_NAME_INDEX = 0;
        final int COLUMN_SALARY_INDEX = 1;
        cursor.moveToFirst();
        assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.moveToNext();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.moveToNext();
        assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.close();

        CursorFactory factory = new CursorFactory() {
            public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery,
                    String editTable, SQLiteQuery query) {
                return new MockSQLiteCursor(db, masterQuery, editTable, query);
            }
        };
        cursor = mDatabase.queryWithFactory(factory, true, "employee",
                new String[] { "name", "sum(salary)" },
                null, null, "name", "sum(salary) > 1000", "name", null);
        assertNotNull(cursor);
        assertTrue(cursor instanceof MockSQLiteCursor);
        cursor.moveToFirst();
        assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.moveToNext();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.moveToNext();
        assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.close();

        cursor = mDatabase.query("employee", new String[] { "name", "sum(salary)" },
                null, null, "name", "sum(salary) <= 4000", "name");
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());

        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.moveToNext();
        assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.close();

        cursor = mDatabase.query("employee", new String[] { "name", "sum(salary)" },
                null, null, "name", "sum(salary) > 1000", "name", "2");
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());

        cursor.moveToFirst();
        assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.moveToNext();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX));
        cursor.close();

        String sql = "SELECT name, month FROM employee WHERE salary > ?;";
        cursor = mDatabase.rawQuery(sql, new String[] { "2000" });
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());

        final int COLUMN_MONTH_INDEX = 1;
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(2, cursor.getInt(COLUMN_MONTH_INDEX));
        cursor.moveToNext();
        assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(3, cursor.getInt(COLUMN_MONTH_INDEX));
        cursor.close();

        cursor = mDatabase.rawQueryWithFactory(factory, sql, new String[] { "2000" }, null);
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());
        assertTrue(cursor instanceof MockSQLiteCursor);
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(2, cursor.getInt(COLUMN_MONTH_INDEX));
        cursor.moveToNext();
        assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(3, cursor.getInt(COLUMN_MONTH_INDEX));
        cursor.close();
    }

    public void testReplace() {
        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, "
                + "name TEXT, age INTEGER, address TEXT);");

        ContentValues values = new ContentValues();
        values.put("name", "Jack");
        values.put("age", 20);
        values.put("address", "LA");
        mDatabase.replace(TABLE_NAME, "name", values);

        Cursor cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION,
                null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        int id = cursor.getInt(COLUMN_ID_INDEX);
        assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        values = new ContentValues();
        values.put("_id", id);
        values.put("name", "Mike");
        values.put("age", 40);
        values.put("address", "London");
        mDatabase.replace(TABLE_NAME, "name", values);

        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount()); // there is still ONLY 1 record.
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        values = new ContentValues();
        values.put("name", "Jack");
        values.put("age", 20);
        values.put("address", "LA");
        mDatabase.replaceOrThrow(TABLE_NAME, "name", values);

        cursor = mDatabase.query(TABLE_NAME, TEST_PROJECTION, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());
        cursor.moveToFirst();
        assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(40, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("London", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.moveToNext();
        assertEquals("Jack", cursor.getString(COLUMN_NAME_INDEX));
        assertEquals(20, cursor.getInt(COLUMN_AGE_INDEX));
        assertEquals("LA", cursor.getString(COLUMN_ADDR_INDEX));
        cursor.close();

        values = new ContentValues();
        values.put("Wrong Key", "Wrong value");
        try {
            mDatabase.replaceOrThrow(TABLE_NAME, "name", values);
            fail("should throw SQLException.");
        } catch (SQLException e) {
        }
    }

    public void testUpdate() {
        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");

        mDatabase.execSQL("INSERT INTO test (data) VALUES ('string1');");
        mDatabase.execSQL("INSERT INTO test (data) VALUES ('string2');");
        mDatabase.execSQL("INSERT INTO test (data) VALUES ('string3');");

        String updatedString = "this is an updated test";
        ContentValues values = new ContentValues(1);
        values.put("data", updatedString);
        assertEquals(1, mDatabase.update("test", values, "_id=1", null));
        Cursor cursor = mDatabase.query("test", null, "_id=1", null, null, null, null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        String value = cursor.getString(cursor.getColumnIndexOrThrow("data"));
        assertEquals(updatedString, value);
        cursor.close();
    }

    public void testNeedUpgrade() {
        mDatabase.setVersion(0);
        assertTrue(mDatabase.needUpgrade(1));
        mDatabase.setVersion(1);
        assertFalse(mDatabase.needUpgrade(1));
    }

    public void testSetLocale() {
//        final String[] STRINGS = {
//                "c\u00f4t\u00e9",
//                "cote",
//                "c\u00f4te",
//                "cot\u00e9",
//                "boy",
//                "dog",
//                "COTE",
//        };
//
//        mDatabase.execSQL("CREATE TABLE test (data TEXT COLLATE LOCALIZED);");
//        for (String s : STRINGS) {
//            mDatabase.execSQL("INSERT INTO test VALUES('" + s + "');");
//        }
//
//        mDatabase.setLocale(new Locale("en", "US"));
//
//        String sql = "SELECT data FROM test ORDER BY data COLLATE LOCALIZED ASC";
//        Cursor cursor = mDatabase.rawQuery(sql, null);
//        assertNotNull(cursor);
//        ArrayList<String> items = new ArrayList<String>();
//        while (cursor.moveToNext()) {
//            items.add(cursor.getString(0));
//        }
//        String[] results = items.toArray(new String[items.size()]);
//        assertEquals(STRINGS.length, results.length);
//        cursor.close();
//
//        // The database code currently uses PRIMARY collation strength,
//        // meaning that all versions of a character compare equal (regardless
//        // of case or accents), leaving the "cote" flavors in database order.
//        MoreAsserts.assertEquals(results, new String[] {
//                STRINGS[4],  // "boy"
//                STRINGS[0],  // sundry forms of "cote"
//                STRINGS[1],
//                STRINGS[2],
//                STRINGS[3],
//                STRINGS[6],  // "COTE"
//                STRINGS[5],  // "dog"
//        });
    }

    public void testOnAllReferencesReleased() {
        assertTrue(mDatabase.isOpen());
        mDatabase.releaseReference();
        assertFalse(mDatabase.isOpen());
    }

    public void testTransactionWithSQLiteTransactionListener() {
        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");

        assertEquals(mTransactionListenerOnBeginCalled, false);
        assertEquals(mTransactionListenerOnCommitCalled, false);
        assertEquals(mTransactionListenerOnRollbackCalled, false);
        mDatabase.beginTransactionWithListener(new TestSQLiteTransactionListener());

        // Assert that the transcation has started
        assertEquals(mTransactionListenerOnBeginCalled, true);
        assertEquals(mTransactionListenerOnCommitCalled, false);
        assertEquals(mTransactionListenerOnRollbackCalled, false);

        setNum(1);

        // State shouldn't have changed
        assertEquals(mTransactionListenerOnBeginCalled, true);
        assertEquals(mTransactionListenerOnCommitCalled, false);
        assertEquals(mTransactionListenerOnRollbackCalled, false);

        // commit the transaction
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();

        // the listener should have been told that commit was called
        assertEquals(mTransactionListenerOnBeginCalled, true);
        assertEquals(mTransactionListenerOnCommitCalled, true);
        assertEquals(mTransactionListenerOnRollbackCalled, false);
    }

    public void testRollbackTransactionWithSQLiteTransactionListener() {
        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");

        assertEquals(mTransactionListenerOnBeginCalled, false);
        assertEquals(mTransactionListenerOnCommitCalled, false);
        assertEquals(mTransactionListenerOnRollbackCalled, false);
        mDatabase.beginTransactionWithListener(new TestSQLiteTransactionListener());

        // Assert that the transcation has started
        assertEquals(mTransactionListenerOnBeginCalled, true);
        assertEquals(mTransactionListenerOnCommitCalled, false);
        assertEquals(mTransactionListenerOnRollbackCalled, false);

        setNum(1);

        // State shouldn't have changed
        assertEquals(mTransactionListenerOnBeginCalled, true);
        assertEquals(mTransactionListenerOnCommitCalled, false);
        assertEquals(mTransactionListenerOnRollbackCalled, false);

        // commit the transaction
        mDatabase.endTransaction();

        // the listener should have been told that commit was called
        assertEquals(mTransactionListenerOnBeginCalled, true);
        assertEquals(mTransactionListenerOnCommitCalled, false);
        assertEquals(mTransactionListenerOnRollbackCalled, true);
    }

    private class TestSQLiteTransactionListener implements SQLiteTransactionListener {
        public void onBegin() {
            mTransactionListenerOnBeginCalled = true;
        }

        public void onCommit() {
            mTransactionListenerOnCommitCalled = true;
        }

        public void onRollback() {
            mTransactionListenerOnRollbackCalled = true;
        }
    }

    public void testGroupConcat() {
        mDatabase.execSQL("CREATE TABLE test (i INT, j TEXT);");

        // insert 2 rows
        String sql = "INSERT INTO test (i) VALUES (?);";
        SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
        DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1);
        insertStatement.execute();
        DatabaseUtils.bindObjectToProgram(insertStatement, 1, 2);
        insertStatement.execute();
        insertStatement.close();

        // make sure there are 2 rows in the table
        Cursor cursor = mDatabase.rawQuery("SELECT count(*) FROM test", null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToNext();
        assertEquals(2, cursor.getInt(0));
        cursor.close();

        // concatenate column j from all the rows. should return NULL
        cursor = mDatabase.rawQuery("SELECT group_concat(j, ' ') FROM test", null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToNext();
        assertNull(cursor.getString(0));
        cursor.close();

        // drop the table
        mDatabase.execSQL("DROP TABLE test;");
        // should get no exceptions
    }

    public void testSchemaChanges() {
        mDatabase.execSQL("CREATE TABLE test (i INT, j INT);");

        // at the beginning, there is no record in the database.
        Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null);
        assertNotNull(cursor);
        assertEquals(0, cursor.getCount());
        cursor.close();

        String sql = "INSERT INTO test VALUES (?, ?);";
        SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
        DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1);
        DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2);
        insertStatement.execute();
        insertStatement.close();

        // read the data from the table and make sure it is correct
        cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToNext();
        assertEquals(1, cursor.getInt(0));
        assertEquals(2, cursor.getInt(1));
        cursor.close();

        // alter the table and execute another statement
        mDatabase.execSQL("ALTER TABLE test ADD COLUMN k int;");
        sql = "INSERT INTO test VALUES (?, ?, ?);";
        insertStatement = mDatabase.compileStatement(sql);
        DatabaseUtils.bindObjectToProgram(insertStatement, 1, 3);
        DatabaseUtils.bindObjectToProgram(insertStatement, 2, 4);
        DatabaseUtils.bindObjectToProgram(insertStatement, 3, 5);
        insertStatement.execute();
        insertStatement.close();

        // read the data from the table and make sure it is correct
        cursor = mDatabase.rawQuery("SELECT i,j,k FROM test", null);
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());
        cursor.moveToNext();
        assertEquals(1, cursor.getInt(0));
        assertEquals(2, cursor.getInt(1));
        assertNull(cursor.getString(2));
        cursor.moveToNext();
        assertEquals(3, cursor.getInt(0));
        assertEquals(4, cursor.getInt(1));
        assertEquals(5, cursor.getInt(2));
        cursor.close();

        // make sure the old statement - which should *try to reuse* cached query plan -
        // still works
        cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
        assertNotNull(cursor);
        assertEquals(2, cursor.getCount());
        cursor.moveToNext();
        assertEquals(1, cursor.getInt(0));
        assertEquals(2, cursor.getInt(1));
        cursor.moveToNext();
        assertEquals(3, cursor.getInt(0));
        assertEquals(4, cursor.getInt(1));
        cursor.close();

        SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test");
        deleteStatement.execute();
        deleteStatement.close();
    }

    public void testSchemaChangesNewTable() {
        mDatabase.execSQL("CREATE TABLE test (i INT, j INT);");

        // at the beginning, there is no record in the database.
        Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null);
        assertNotNull(cursor);
        assertEquals(0, cursor.getCount());
        cursor.close();

        String sql = "INSERT INTO test VALUES (?, ?);";
        SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
        DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1);
        DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2);
        insertStatement.execute();
        insertStatement.close();

        // read the data from the table and make sure it is correct
        cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToNext();
        assertEquals(1, cursor.getInt(0));
        assertEquals(2, cursor.getInt(1));
        cursor.close();

        // alter the table and execute another statement
        mDatabase.execSQL("CREATE TABLE test_new (i INT, j INT, k INT);");
        sql = "INSERT INTO test_new VALUES (?, ?, ?);";
        insertStatement = mDatabase.compileStatement(sql);
        DatabaseUtils.bindObjectToProgram(insertStatement, 1, 3);
        DatabaseUtils.bindObjectToProgram(insertStatement, 2, 4);
        DatabaseUtils.bindObjectToProgram(insertStatement, 3, 5);
        insertStatement.execute();
        insertStatement.close();

        // read the data from the table and make sure it is correct
        cursor = mDatabase.rawQuery("SELECT i,j,k FROM test_new", null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToNext();
        assertEquals(3, cursor.getInt(0));
        assertEquals(4, cursor.getInt(1));
        assertEquals(5, cursor.getInt(2));
        cursor.close();

        // make sure the old statement - which should *try to reuse* cached query plan -
        // still works
        cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToNext();
        assertEquals(1, cursor.getInt(0));
        assertEquals(2, cursor.getInt(1));
        cursor.close();

        SQLiteStatement deleteStatement = mDatabase.compileStatement("DELETE FROM test");
        deleteStatement.execute();
        deleteStatement.close();

        SQLiteStatement deleteStatement2 = mDatabase.compileStatement("DELETE FROM test_new");
        deleteStatement2.execute();
        deleteStatement2.close();
    }

    public void testSchemaChangesDropTable() {
        mDatabase.execSQL("CREATE TABLE test (i INT, j INT);");

        // at the beginning, there is no record in the database.
        Cursor cursor = mDatabase.rawQuery("SELECT * FROM test", null);
        assertNotNull(cursor);
        assertEquals(0, cursor.getCount());
        cursor.close();

        String sql = "INSERT INTO test VALUES (?, ?);";
        SQLiteStatement insertStatement = mDatabase.compileStatement(sql);
        DatabaseUtils.bindObjectToProgram(insertStatement, 1, 1);
        DatabaseUtils.bindObjectToProgram(insertStatement, 2, 2);
        insertStatement.execute();
        insertStatement.close();

        // read the data from the table and make sure it is correct
        cursor = mDatabase.rawQuery("SELECT i,j FROM test", null);
        assertNotNull(cursor);
        assertEquals(1, cursor.getCount());
        cursor.moveToNext();
        assertEquals(1, cursor.getInt(0));
        assertEquals(2, cursor.getInt(1));
    }

    /**
     * With sqlite's write-ahead-logging (WAL) enabled, readers get old version of data
     * from the table that a writer is modifying at the same time.
     * <p>
     * This method does the following to test this sqlite3 feature
     * <ol>
     *   <li>creates a table in the database and populates it with 5 rows of data</li>
     *   <li>do "select count(*) from this_table" and expect to receive 5</li>
     *   <li>start a writer thread who BEGINs a transaction, INSERTs a single row
     *   into this_table</li>
     *   <li>writer stops the transaction at this point, kicks off a reader thread - which will
     *       do  the above SELECT query: "select count(*) from this_table"</li>
     *   <li>this query should return value 5 - because writer is still in transaction and
     *    sqlite returns OLD version of the data</li>
     *   <li>writer ends the transaction, thus making the extra row now visible to everyone</li>
     *   <li>reader is kicked off again to do the same query. this time query should
     *   return value = 6 which includes the newly inserted row into this_table.</li>
     *</p>
     * @throws InterruptedException
     */
    @LargeTest
    public void testReaderGetsOldVersionOfDataWhenWriterIsInXact() throws InterruptedException {
        // redo setup to create WAL enabled database
        mDatabase.close();
        new File(mDatabase.getPath()).delete();
        mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null);
        boolean rslt = mDatabase.enableWriteAheadLogging();
        assertTrue(rslt);
        assertNotNull(mDatabase);

        // create a new table and insert 5 records into it.
        mDatabase.execSQL("CREATE TABLE t1 (i int, j int);");
        mDatabase.beginTransaction();
        for (int i = 0; i < 5; i++) {
            mDatabase.execSQL("insert into t1 values(?,?);", new String[] {i+"", i+""});
        }
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();

        // make sure a reader can read the above data
        ReaderQueryingData r1 = new ReaderQueryingData(5);
        r1.start();
        Thread.yield();
        try {r1.join();} catch (Exception e) {}

        WriterDoingSingleTransaction w = new WriterDoingSingleTransaction();
        w.start();
        w.join();
    }

    private class WriterDoingSingleTransaction extends Thread {
        @Override public void run() {
            // start a transaction
            mDatabase.beginTransactionNonExclusive();
            mDatabase.execSQL("insert into t1 values(?,?);", new String[] {"11", "11"});
            assertTrue(mDatabase.isOpen());

            // while the writer is in a transaction, start a reader and make sure it can still
            // read 5 rows of data (= old data prior to the current transaction)
            ReaderQueryingData r1 = new ReaderQueryingData(5);
            r1.start();
            try {r1.join();} catch (Exception e) {}

            // now, have the writer do the select count(*)
            // it should execute on the same connection as this transaction
            // and count(*) should reflect the newly inserted row
            Long l = DatabaseUtils.longForQuery(mDatabase, "select count(*) from t1", null);
            assertEquals(6, l.intValue());

            // end transaction
            mDatabase.setTransactionSuccessful();
            mDatabase.endTransaction();

            // reader should now be able to read 6 rows = new data AFTER this transaction
            r1 = new ReaderQueryingData(6);
            r1.start();
            try {r1.join();} catch (Exception e) {}
        }
    }

    private class ReaderQueryingData extends Thread {
        private int count;
        /**
         * constructor with a param to indicate the number of rows expected to be read
         */
        public ReaderQueryingData(int count) {
            this.count = count;
        }
        @Override public void run() {
            Long l = DatabaseUtils.longForQuery(mDatabase, "select count(*) from t1", null);
            assertEquals(count, l.intValue());
        }
    }

    public void testExceptionsFromEnableWriteAheadLogging() {
        // attach a database
        // redo setup to create WAL enabled database
        mDatabase.close();
        new File(mDatabase.getPath()).delete();
        mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null, null);

        // attach a database and call enableWriteAheadLogging - should not be allowed
        mDatabase.execSQL("attach database ':memory:' as memoryDb");
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
        assertFalse(mDatabase.enableWriteAheadLogging());
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());

        // enableWriteAheadLogging on memory database is not allowed
        SQLiteDatabase db = SQLiteDatabase.create(null);
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
        assertFalse(db.enableWriteAheadLogging());
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
        db.close();
    }

    public void testEnableThenDisableWriteAheadLogging() {
        // Enable WAL.
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
        assertTrue(mDatabase.enableWriteAheadLogging());
        assertTrue(mDatabase.isWriteAheadLoggingEnabled());
        assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
                .equalsIgnoreCase("WAL"));

        // Enabling when already enabled should have no observable effect.
        assertTrue(mDatabase.enableWriteAheadLogging());
        assertTrue(mDatabase.isWriteAheadLoggingEnabled());
        assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
                .equalsIgnoreCase("WAL"));

        // Disabling when there are no connections should work.
        mDatabase.disableWriteAheadLogging();
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
    }

    public void testEnableThenDisableWriteAheadLoggingUsingOpenFlag() {
        new File(mDatabase.getPath()).delete();
        mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile.getPath(), null,
                SQLiteDatabase.CREATE_IF_NECESSARY | SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING,
                null);
        assertTrue(mDatabase.isWriteAheadLoggingEnabled());
        assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
                .equalsIgnoreCase("WAL"));

        // Enabling when already enabled should have no observable effect.
        assertTrue(mDatabase.enableWriteAheadLogging());
        assertTrue(mDatabase.isWriteAheadLoggingEnabled());
        assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
                .equalsIgnoreCase("WAL"));

        // Disabling when there are no connections should work.
        mDatabase.disableWriteAheadLogging();
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
    }

    public void testEnableWriteAheadLoggingFromContextUsingModeFlag() {
        // Without the MODE_ENABLE_WRITE_AHEAD_LOGGING flag, database opens without WAL.
        getContext().deleteDatabase(DATABASE_FILE_NAME);

        File f = getContext().getDatabasePath(DATABASE_FILE_NAME);
        mDatabase = SQLiteDatabase.openOrCreateDatabase(f,null);
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
        mDatabase.close();

        // // With the MODE_ENABLE_WRITE_AHEAD_LOGGING flag, database opens with WAL.
        // getContext().deleteDatabase(DATABASE_FILE_NAME);
        // mDatabase = getContext().openOrCreateDatabase(DATABASE_FILE_NAME,
        //        Context.MODE_PRIVATE | Context.MODE_ENABLE_WRITE_AHEAD_LOGGING, null);
        // assertTrue(mDatabase.isWriteAheadLoggingEnabled());
        // mDatabase.close();
    }

    public void testEnableWriteAheadLoggingShouldThrowIfTransactionInProgress() {
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
        String oldJournalMode = DatabaseUtils.stringForQuery(
                mDatabase, "PRAGMA journal_mode", null);

        // Begin transaction.
        mDatabase.beginTransaction();

        try {
            // Attempt to enable WAL should fail.
            mDatabase.enableWriteAheadLogging();
            fail("Expected IllegalStateException");
        } catch (IllegalStateException ex) {
            // expected
        }

        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
        assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
                .equalsIgnoreCase(oldJournalMode));
    }

    public void testDisableWriteAheadLoggingShouldThrowIfTransactionInProgress() {
        // Enable WAL.
        assertFalse(mDatabase.isWriteAheadLoggingEnabled());
        assertTrue(mDatabase.enableWriteAheadLogging());
        assertTrue(mDatabase.isWriteAheadLoggingEnabled());

        // Begin transaction.
        mDatabase.beginTransaction();

        try {
            // Attempt to disable WAL should fail.
            mDatabase.disableWriteAheadLogging();
            fail("Expected IllegalStateException");
        } catch (IllegalStateException ex) {
            // expected
        }

        assertTrue(mDatabase.isWriteAheadLoggingEnabled());
        assertTrue(DatabaseUtils.stringForQuery(mDatabase, "PRAGMA journal_mode", null)
                .equalsIgnoreCase("WAL"));
    }

    public void testEnableAndDisableForeignKeys() {
        // Initially off.
        assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));

        // Enable foreign keys.
        mDatabase.setForeignKeyConstraintsEnabled(true);
        assertEquals(1, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));

        // Disable foreign keys.
        mDatabase.setForeignKeyConstraintsEnabled(false);
        assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));

        // Cannot configure foreign keys if there are transactions in progress.
        mDatabase.beginTransaction();
        try {
            mDatabase.setForeignKeyConstraintsEnabled(true);
            fail("Expected IllegalStateException");
        } catch (IllegalStateException ex) {
            // expected
        }
        assertEquals(0, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));
        mDatabase.endTransaction();

        // Enable foreign keys should work again after transaction complete.
        mDatabase.setForeignKeyConstraintsEnabled(true);
        assertEquals(1, DatabaseUtils.longForQuery(mDatabase, "PRAGMA foreign_keys", null));
    }
}

/*
 * Copyright (C) 2006 The Android Open Source Project
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.sqlite.database;

import android.content.ContentValues;
import android.content.Context;
import android.content.OperationApplicationException;
import org.sqlite.database.sqlite.SQLiteAbortException;
import org.sqlite.database.sqlite.SQLiteConstraintException;
import org.sqlite.database.sqlite.SQLiteDatabase;
import org.sqlite.database.sqlite.SQLiteDatabaseCorruptException;
import org.sqlite.database.sqlite.SQLiteDiskIOException;
import org.sqlite.database.sqlite.SQLiteException;
import org.sqlite.database.sqlite.SQLiteFullException;
import org.sqlite.database.sqlite.SQLiteProgram;
import org.sqlite.database.sqlite.SQLiteStatement;

import android.database.CursorWindow;
import android.os.OperationCanceledException;
import android.os.Parcel;
import android.os.ParcelFileDescriptor;
import android.text.TextUtils;
import android.util.Log;

import android.database.Cursor;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.PrintStream;
import java.text.Collator;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;

/**
 * Static utility methods for dealing with databases and {@link Cursor}s.
 */
public class DatabaseUtils {
    private static final String TAG = "DatabaseUtils";

    private static final boolean DEBUG = false;

    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_SELECT = 1;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_UPDATE = 2;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_ATTACH = 3;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_BEGIN = 4;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_COMMIT = 5;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_ABORT = 6;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_PRAGMA = 7;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_DDL = 8;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_UNPREPARED = 9;
    /** One of the values returned by {@link #getSqlStatementType(String)}. */
    public static final int STATEMENT_OTHER = 99;

    /**
     * Special function for writing an exception result at the header of
     * a parcel, to be used when returning an exception from a transaction.
     * exception will be re-thrown by the function in another process
     * @param reply Parcel to write to
     * @param e The Exception to be written.
     * @see Parcel#writeNoException
     * @see Parcel#writeException
     */
    public static final void writeExceptionToParcel(Parcel reply, Exception e) {
        int code = 0;
        boolean logException = true;
        if (e instanceof FileNotFoundException) {
            code = 1;
            logException = false;
        } else if (e instanceof IllegalArgumentException) {
            code = 2;
        } else if (e instanceof UnsupportedOperationException) {
            code = 3;
        } else if (e instanceof SQLiteAbortException) {
            code = 4;
        } else if (e instanceof SQLiteConstraintException) {
            code = 5;
        } else if (e instanceof SQLiteDatabaseCorruptException) {
            code = 6;
        } else if (e instanceof SQLiteFullException) {
            code = 7;
        } else if (e instanceof SQLiteDiskIOException) {
            code = 8;
        } else if (e instanceof SQLiteException) {
            code = 9;
        } else if (e instanceof OperationApplicationException) {
            code = 10;
        } else if (e instanceof OperationCanceledException) {
            code = 11;
            logException = false;
        } else {
            reply.writeException(e);
            Log.e(TAG, "Writing exception to parcel", e);
            return;
        }
        reply.writeInt(code);
        reply.writeString(e.getMessage());

        if (logException) {
            Log.e(TAG, "Writing exception to parcel", e);
        }
    }

    /**
     * Special function for reading an exception result from the header of
     * a parcel, to be used after receiving the result of a transaction.  This
     * will throw the exception for you if it had been written to the Parcel,
     * otherwise return and let you read the normal result data from the Parcel.
     * @param reply Parcel to read from
     * @see Parcel#writeNoException
     * @see Parcel#readException
     */
//    public static final void readExceptionFromParcel(Parcel reply) {
//        int code = reply.readExceptionCode();
//        if (code == 0) return;
//        String msg = reply.readString();
//        DatabaseUtils.readExceptionFromParcel(reply, msg, code);
//    }
//
//    public static void readExceptionWithFileNotFoundExceptionFromParcel(
//            Parcel reply) throws FileNotFoundException {
//        int code = reply.readExceptionCode();
//        if (code == 0) return;
//        String msg = reply.readString();
//        if (code == 1) {
//            throw new FileNotFoundException(msg);
//        } else {
//            DatabaseUtils.readExceptionFromParcel(reply, msg, code);
//        }
//    }
//
//    public static void readExceptionWithOperationApplicationExceptionFromParcel(
//            Parcel reply) throws OperationApplicationException {
//        int code = reply.readExceptionCode();
//        if (code == 0) return;
//        String msg = reply.readString();
//        if (code == 10) {
//            throw new OperationApplicationException(msg);
//        } else {
//            DatabaseUtils.readExceptionFromParcel(reply, msg, code);
//        }
//    }

    private static final void readExceptionFromParcel(Parcel reply, String msg, int code) {
        switch (code) {
            case 2:
                throw new IllegalArgumentException(msg);
            case 3:
                throw new UnsupportedOperationException(msg);
            case 4:
                throw new SQLiteAbortException(msg);
            case 5:
                throw new SQLiteConstraintException(msg);
            case 6:
                throw new SQLiteDatabaseCorruptException(msg);
            case 7:
                throw new SQLiteFullException(msg);
            case 8:
                throw new SQLiteDiskIOException(msg);
            case 9:
                throw new SQLiteException(msg);
            case 11:
                throw new OperationCanceledException(msg);
            default:
                reply.readException(code, msg);
        }
    }

    /**
     * Binds the given Object to the given SQLiteProgram using the proper
     * typing. For example, bind numbers as longs/doubles, and everything else
     * as a string by call toString() on it.
     *
     * @param prog the program to bind the object to
     * @param index the 1-based index to bind at
     * @param value the value to bind
     */
    public static void bindObjectToProgram(SQLiteProgram prog, int index,
            Object value) {
        if (value == null) {
            prog.bindNull(index);
        } else if (value instanceof Double || value instanceof Float) {
            prog.bindDouble(index, ((Number)value).doubleValue());
        } else if (value instanceof Number) {
            prog.bindLong(index, ((Number)value).longValue());
        } else if (value instanceof Boolean) {
            Boolean bool = (Boolean)value;
            if (bool) {
                prog.bindLong(index, 1);
            } else {
                prog.bindLong(index, 0);
            }
        } else if (value instanceof byte[]){
            prog.bindBlob(index, (byte[]) value);
        } else {
            prog.bindString(index, value.toString());
        }
    }

    /**
     * Returns data type of the given object's value.
     *<p>
     * Returned values are
     * <ul>
     *   <li>{@link Cursor#FIELD_TYPE_NULL}</li>
     *   <li>{@link Cursor#FIELD_TYPE_INTEGER}</li>
     *   <li>{@link Cursor#FIELD_TYPE_FLOAT}</li>
     *   <li>{@link Cursor#FIELD_TYPE_STRING}</li>
     *   <li>{@link Cursor#FIELD_TYPE_BLOB}</li>
     *</ul>
     *</p>
     *
     * @param obj the object whose value type is to be returned
     * @return object value type
     * @hide
     */
    public static int getTypeOfObject(Object obj) {
        if (obj == null) {
            return Cursor.FIELD_TYPE_NULL;
        } else if (obj instanceof byte[]) {
            return Cursor.FIELD_TYPE_BLOB;
        } else if (obj instanceof Float || obj instanceof Double) {
            return Cursor.FIELD_TYPE_FLOAT;
        } else if (obj instanceof Long || obj instanceof Integer
                || obj instanceof Short || obj instanceof Byte) {
            return Cursor.FIELD_TYPE_INTEGER;
        } else {
            return Cursor.FIELD_TYPE_STRING;
        }
    }

    /**
     * Fills the specified cursor window by iterating over the contents of the cursor.
     * The window is filled until the cursor is exhausted or the window runs out
     * of space.
     *
     * The original position of the cursor is left unchanged by this operation.
     *
     * @param cursor The cursor that contains the data to put in the window.
     * @param position The start position for filling the window.
     * @param window The window to fill.
     * @hide
     */
    public static void cursorFillWindow(final Cursor cursor,
            int position, final CursorWindow window) {
        if (position < 0 || position >= cursor.getCount()) {
            return;
        }
        final int oldPos = cursor.getPosition();
        final int numColumns = cursor.getColumnCount();
        window.clear();
        window.setStartPosition(position);
        window.setNumColumns(numColumns);
        if (cursor.moveToPosition(position)) {
            rowloop: do {
                if (!window.allocRow()) {
                    break;
                }
                for (int i = 0; i < numColumns; i++) {
                    final int type = cursor.getType(i);
                    final boolean success;
                    switch (type) {
                        case Cursor.FIELD_TYPE_NULL:
                            success = window.putNull(position, i);
                            break;

                        case Cursor.FIELD_TYPE_INTEGER:
                            success = window.putLong(cursor.getLong(i), position, i);
                            break;

                        case Cursor.FIELD_TYPE_FLOAT:
                            success = window.putDouble(cursor.getDouble(i), position, i);
                            break;

                        case Cursor.FIELD_TYPE_BLOB: {
                            final byte[] value = cursor.getBlob(i);
                            success = value != null ? window.putBlob(value, position, i)
                                    : window.putNull(position, i);
                            break;
                        }

                        default: // assume value is convertible to String
                        case Cursor.FIELD_TYPE_STRING: {
                            final String value = cursor.getString(i);
                            success = value != null ? window.putString(value, position, i)
                                    : window.putNull(position, i);
                            break;
                        }
                    }
                    if (!success) {
                        window.freeLastRow();
                        break rowloop;
                    }
                }
                position += 1;
            } while (cursor.moveToNext());
        }
        cursor.moveToPosition(oldPos);
    }

    /**
     * Appends an SQL string to the given StringBuilder, including the opening
     * and closing single quotes. Any single quotes internal to sqlString will
     * be escaped.
     *
     * This method is deprecated because we want to encourage everyone
     * to use the "?" binding form.  However, when implementing a
     * ContentProvider, one may want to add WHERE clauses that were
     * not provided by the caller.  Since "?" is a positional form,
     * using it in this case could break the caller because the
     * indexes would be shifted to accomodate the ContentProvider's
     * internal bindings.  In that case, it may be necessary to
     * construct a WHERE clause manually.  This method is useful for
     * those cases.
     *
     * @param sb the StringBuilder that the SQL string will be appended to
     * @param sqlString the raw string to be appended, which may contain single
     *                  quotes
     */
    public static void appendEscapedSQLString(StringBuilder sb, String sqlString) {
        sb.append('\'');
        if (sqlString.indexOf('\'') != -1) {
            int length = sqlString.length();
            for (int i = 0; i < length; i++) {
                char c = sqlString.charAt(i);
                if (c == '\'') {
                    sb.append('\'');
                }
                sb.append(c);
            }
        } else
            sb.append(sqlString);
        sb.append('\'');
    }

    /**
     * SQL-escape a string.
     */
    public static String sqlEscapeString(String value) {
        StringBuilder escaper = new StringBuilder();

        DatabaseUtils.appendEscapedSQLString(escaper, value);

        return escaper.toString();
    }

    /**
     * Appends an Object to an SQL string with the proper escaping, etc.
     */
    public static final void appendValueToSql(StringBuilder sql, Object value) {
        if (value == null) {
            sql.append("NULL");
        } else if (value instanceof Boolean) {
            Boolean bool = (Boolean)value;
            if (bool) {
                sql.append('1');
            } else {
                sql.append('0');
            }
        } else {
            appendEscapedSQLString(sql, value.toString());
        }
    }

    /**
     * Concatenates two SQL WHERE clauses, handling empty or null values.
     */
    public static String concatenateWhere(String a, String b) {
        if (TextUtils.isEmpty(a)) {
            return b;
        }
        if (TextUtils.isEmpty(b)) {
            return a;
        }

        return "(" + a + ") AND (" + b + ")";
    }

    /**
     * return the collation key
     * @param name
     * @return the collation key
     */
    public static String getCollationKey(String name) {
        byte [] arr = getCollationKeyInBytes(name);
        try {
            return new String(arr, 0, getKeyLen(arr), "ISO8859_1");
        } catch (Exception ex) {
            return "";
        }
    }

    /**
     * return the collation key in hex format
     * @param name
     * @return the collation key in hex format
     */
    public static String getHexCollationKey(String name) {
        byte[] arr = getCollationKeyInBytes(name);
        char[] keys = encodeHex(arr);
        return new String(keys, 0, getKeyLen(arr) * 2);
    }


    /**
     * Used building output as Hex
     */
    private static final char[] DIGITS = {
            '0', '1', '2', '3', '4', '5', '6', '7',
            '8', '9', 'a', 'b', 'c', 'd', 'e', 'f'
    };

    private static char[] encodeHex(byte[] input) {
        int l = input.length;
        char[] out = new char[l << 1];

        // two characters form the hex value.
        for (int i = 0, j = 0; i < l; i++) {
            out[j++] = DIGITS[(0xF0 & input[i]) >>> 4 ];
            out[j++] = DIGITS[ 0x0F & input[i] ];
        }

        return out;
    }

    private static int getKeyLen(byte[] arr) {
        if (arr[arr.length - 1] != 0) {
            return arr.length;
        } else {
            // remove zero "termination"
            return arr.length-1;
        }
    }

    private static byte[] getCollationKeyInBytes(String name) {
        if (mColl == null) {
            mColl = Collator.getInstance();
            mColl.setStrength(Collator.PRIMARY);
        }
        return mColl.getCollationKey(name).toByteArray();
    }

    private static Collator mColl = null;
    /**
     * Prints the contents of a Cursor to System.out. The position is restored
     * after printing.
     *
     * @param cursor the cursor to print
     */
    public static void dumpCursor(Cursor cursor) {
        dumpCursor(cursor, System.out);
    }

    /**
     * Prints the contents of a Cursor to a PrintSteam. The position is restored
     * after printing.
     *
     * @param cursor the cursor to print
     * @param stream the stream to print to
     */
    public static void dumpCursor(Cursor cursor, PrintStream stream) {
        stream.println(">>>>> Dumping cursor " + cursor);
        if (cursor != null) {
            int startPos = cursor.getPosition();

            cursor.moveToPosition(-1);
            while (cursor.moveToNext()) {
                dumpCurrentRow(cursor, stream);
            }
            cursor.moveToPosition(startPos);
        }
        stream.println("<<<<<");
    }

    /**
     * Prints the contents of a Cursor to a StringBuilder. The position
     * is restored after printing.
     *
     * @param cursor the cursor to print
     * @param sb the StringBuilder to print to
     */
    public static void dumpCursor(Cursor cursor, StringBuilder sb) {
        sb.append(">>>>> Dumping cursor " + cursor + "\n");
        if (cursor != null) {
            int startPos = cursor.getPosition();

            cursor.moveToPosition(-1);
            while (cursor.moveToNext()) {
                dumpCurrentRow(cursor, sb);
            }
            cursor.moveToPosition(startPos);
        }
        sb.append("<<<<<\n");
    }

    /**
     * Prints the contents of a Cursor to a String. The position is restored
     * after printing.
     *
     * @param cursor the cursor to print
     * @return a String that contains the dumped cursor
     */
    public static String dumpCursorToString(Cursor cursor) {
        StringBuilder sb = new StringBuilder();
        dumpCursor(cursor, sb);
        return sb.toString();
    }

    /**
     * Prints the contents of a Cursor's current row to System.out.
     *
     * @param cursor the cursor to print from
     */
    public static void dumpCurrentRow(Cursor cursor) {
        dumpCurrentRow(cursor, System.out);
    }

    /**
     * Prints the contents of a Cursor's current row to a PrintSteam.
     *
     * @param cursor the cursor to print
     * @param stream the stream to print to
     */
    public static void dumpCurrentRow(Cursor cursor, PrintStream stream) {
        String[] cols = cursor.getColumnNames();
        stream.println("" + cursor.getPosition() + " {");
        int length = cols.length;
        for (int i = 0; i< length; i++) {
            String value;
            try {
                value = cursor.getString(i);
            } catch (SQLiteException e) {
                // assume that if the getString threw this exception then the column is not
                // representable by a string, e.g. it is a BLOB.
                value = "<unprintable>";
            }
            stream.println("   " + cols[i] + '=' + value);
        }
        stream.println("}");
    }

    /**
     * Prints the contents of a Cursor's current row to a StringBuilder.
     *
     * @param cursor the cursor to print
     * @param sb the StringBuilder to print to
     */
    public static void dumpCurrentRow(Cursor cursor, StringBuilder sb) {
        String[] cols = cursor.getColumnNames();
        sb.append("" + cursor.getPosition() + " {\n");
        int length = cols.length;
        for (int i = 0; i < length; i++) {
            String value;
            try {
                value = cursor.getString(i);
            } catch (SQLiteException e) {
                // assume that if the getString threw this exception then the column is not
                // representable by a string, e.g. it is a BLOB.
                value = "<unprintable>";
            }
            sb.append("   " + cols[i] + '=' + value + "\n");
        }
        sb.append("}\n");
    }

    /**
     * Dump the contents of a Cursor's current row to a String.
     *
     * @param cursor the cursor to print
     * @return a String that contains the dumped cursor row
     */
    public static String dumpCurrentRowToString(Cursor cursor) {
        StringBuilder sb = new StringBuilder();
        dumpCurrentRow(cursor, sb);
        return sb.toString();
    }

    /**
     * Reads a String out of a field in a Cursor and writes it to a Map.
     *
     * @param cursor The cursor to read from
     * @param field The TEXT field to read
     * @param values The {@link ContentValues} to put the value into, with the field as the key
     */
    public static void cursorStringToContentValues(Cursor cursor, String field,
            ContentValues values) {
        cursorStringToContentValues(cursor, field, values, field);
    }

    /**
     * Reads a String out of a field in a Cursor and writes it to an InsertHelper.
     *
     * @param cursor The cursor to read from
     * @param field The TEXT field to read
     * @param inserter The InsertHelper to bind into
     * @param index the index of the bind entry in the InsertHelper
     */
    public static void cursorStringToInsertHelper(Cursor cursor, String field,
            InsertHelper inserter, int index) {
        inserter.bind(index, cursor.getString(cursor.getColumnIndexOrThrow(field)));
    }

    /**
     * Reads a String out of a field in a Cursor and writes it to a Map.
     *
     * @param cursor The cursor to read from
     * @param field The TEXT field to read
     * @param values The {@link ContentValues} to put the value into, with the field as the key
     * @param key The key to store the value with in the map
     */
    public static void cursorStringToContentValues(Cursor cursor, String field,
            ContentValues values, String key) {
        values.put(key, cursor.getString(cursor.getColumnIndexOrThrow(field)));
    }

    /**
     * Reads an Integer out of a field in a Cursor and writes it to a Map.
     *
     * @param cursor The cursor to read from
     * @param field The INTEGER field to read
     * @param values The {@link ContentValues} to put the value into, with the field as the key
     */
    public static void cursorIntToContentValues(Cursor cursor, String field, ContentValues values) {
        cursorIntToContentValues(cursor, field, values, field);
    }

    /**
     * Reads a Integer out of a field in a Cursor and writes it to a Map.
     *
     * @param cursor The cursor to read from
     * @param field The INTEGER field to read
     * @param values The {@link ContentValues} to put the value into, with the field as the key
     * @param key The key to store the value with in the map
     */
    public static void cursorIntToContentValues(Cursor cursor, String field, ContentValues values,
            String key) {
        int colIndex = cursor.getColumnIndex(field);
        if (!cursor.isNull(colIndex)) {
            values.put(key, cursor.getInt(colIndex));
        } else {
            values.put(key, (Integer) null);
        }
    }

    /**
     * Reads a Long out of a field in a Cursor and writes it to a Map.
     *
     * @param cursor The cursor to read from
     * @param field The INTEGER field to read
     * @param values The {@link ContentValues} to put the value into, with the field as the key
     */
    public static void cursorLongToContentValues(Cursor cursor, String field, ContentValues values)
    {
        cursorLongToContentValues(cursor, field, values, field);
    }

    /**
     * Reads a Long out of a field in a Cursor and writes it to a Map.
     *
     * @param cursor The cursor to read from
     * @param field The INTEGER field to read
     * @param values The {@link ContentValues} to put the value into
     * @param key The key to store the value with in the map
     */
    public static void cursorLongToContentValues(Cursor cursor, String field, ContentValues values,
            String key) {
        int colIndex = cursor.getColumnIndex(field);
        if (!cursor.isNull(colIndex)) {
            Long value = Long.valueOf(cursor.getLong(colIndex));
            values.put(key, value);
        } else {
            values.put(key, (Long) null);
        }
    }

    /**
     * Reads a Double out of a field in a Cursor and writes it to a Map.
     *
     * @param cursor The cursor to read from
     * @param field The REAL field to read
     * @param values The {@link ContentValues} to put the value into
     */
    public static void cursorDoubleToCursorValues(Cursor cursor, String field, ContentValues values)
    {
        cursorDoubleToContentValues(cursor, field, values, field);
    }

    /**
     * Reads a Double out of a field in a Cursor and writes it to a Map.
     *
     * @param cursor The cursor to read from
     * @param field The REAL field to read
     * @param values The {@link ContentValues} to put the value into
     * @param key The key to store the value with in the map
     */
    public static void cursorDoubleToContentValues(Cursor cursor, String field,
            ContentValues values, String key) {
        int colIndex = cursor.getColumnIndex(field);
        if (!cursor.isNull(colIndex)) {
            values.put(key, cursor.getDouble(colIndex));
        } else {
            values.put(key, (Double) null);
        }
    }

    /**
     * Read the entire contents of a cursor row and store them in a ContentValues.
     *
     * @param cursor the cursor to read from.
     * @param values the {@link ContentValues} to put the row into.
     */
    public static void cursorRowToContentValues(Cursor cursor, ContentValues values) {
        String[] columns = cursor.getColumnNames();
        int length = columns.length;
        for (int i = 0; i < length; i++) {
            if (cursor.getType(i) == Cursor.FIELD_TYPE_BLOB) {
                values.put(columns[i], cursor.getBlob(i));
            } else {
                values.put(columns[i], cursor.getString(i));
            }
        }
    }

    /**
     * Picks a start position for {@link Cursor#fillWindow} such that the
     * window will contain the requested row and a useful range of rows
     * around it.
     *
     * When the data set is too large to fit in a cursor window, seeking the
     * cursor can become a very expensive operation since we have to run the
     * query again when we move outside the bounds of the current window.
     *
     * We try to choose a start position for the cursor window such that
     * 1/3 of the window's capacity is used to hold rows before the requested
     * position and 2/3 of the window's capacity is used to hold rows after the
     * requested position.
     *
     * @param cursorPosition The row index of the row we want to get.
     * @param cursorWindowCapacity The estimated number of rows that can fit in
     * a cursor window, or 0 if unknown.
     * @return The recommended start position, always less than or equal to
     * the requested row.
     * @hide
     */
    public static int cursorPickFillWindowStartPosition(
            int cursorPosition, int cursorWindowCapacity) {
        return Math.max(cursorPosition - cursorWindowCapacity / 3, 0);
    }

    /**
     * Query the table for the number of rows in the table.
     * @param db the database the table is in
     * @param table the name of the table to query
     * @return the number of rows in the table
     */
    public static long queryNumEntries(SQLiteDatabase db, String table) {
        return queryNumEntries(db, table, null, null);
    }

    /**
     * Query the table for the number of rows in the table.
     * @param db the database the table is in
     * @param table the name of the table to query
     * @param selection A filter declaring which rows to return,
     *              formatted as an SQL WHERE clause (excluding the WHERE itself).
     *              Passing null will count all rows for the given table
     * @return the number of rows in the table filtered by the selection
     */
    public static long queryNumEntries(SQLiteDatabase db, String table, String selection) {
        return queryNumEntries(db, table, selection, null);
    }

    /**
     * Query the table for the number of rows in the table.
     * @param db the database the table is in
     * @param table the name of the table to query
     * @param selection A filter declaring which rows to return,
     *              formatted as an SQL WHERE clause (excluding the WHERE itself).
     *              Passing null will count all rows for the given table
     * @param selectionArgs You may include ?s in selection,
     *              which will be replaced by the values from selectionArgs,
     *              in order that they appear in the selection.
     *              The values will be bound as Strings.
     * @return the number of rows in the table filtered by the selection
     */
    public static long queryNumEntries(SQLiteDatabase db, String table, String selection,
            String[] selectionArgs) {
        String s = (!TextUtils.isEmpty(selection)) ? " where " + selection : "";
        return longForQuery(db, "select count(*) from " + table + s,
                    selectionArgs);
    }

    /**
     * Query the table to check whether a table is empty or not
     * @param db the database the table is in
     * @param table the name of the table to query
     * @return True if the table is empty
     * @hide
     */
    public static boolean queryIsEmpty(SQLiteDatabase db, String table) {
        long isEmpty = longForQuery(db, "select exists(select 1 from " + table + ")", null);
        return isEmpty == 0;
    }

    /**
     * Utility method to run the query on the db and return the value in the
     * first column of the first row.
     */
    public static long longForQuery(SQLiteDatabase db, String query, String[] selectionArgs) {
        SQLiteStatement prog = db.compileStatement(query);
        try {
            return longForQuery(prog, selectionArgs);
        } finally {
            prog.close();
        }
    }

    /**
     * Utility method to run the pre-compiled query and return the value in the
     * first column of the first row.
     */
    public static long longForQuery(SQLiteStatement prog, String[] selectionArgs) {
        prog.bindAllArgsAsStrings(selectionArgs);
        return prog.simpleQueryForLong();
    }

    /**
     * Utility method to run the query on the db and return the value in the
     * first column of the first row.
     */
    public static String stringForQuery(SQLiteDatabase db, String query, String[] selectionArgs) {
        SQLiteStatement prog = db.compileStatement(query);
        try {
            return stringForQuery(prog, selectionArgs);
        } finally {
            prog.close();
        }
    }

    /**
     * Utility method to run the pre-compiled query and return the value in the
     * first column of the first row.
     */
    public static String stringForQuery(SQLiteStatement prog, String[] selectionArgs) {
        prog.bindAllArgsAsStrings(selectionArgs);
        return prog.simpleQueryForString();
    }

    /**
     * Utility method to run the query on the db and return the blob value in the
     * first column of the first row.
     *
     * @return A read-only file descriptor for a copy of the blob value.
     */
    public static ParcelFileDescriptor blobFileDescriptorForQuery(SQLiteDatabase db,
            String query, String[] selectionArgs) {
        SQLiteStatement prog = db.compileStatement(query);
        try {
            return blobFileDescriptorForQuery(prog, selectionArgs);
        } finally {
            prog.close();
        }
    }

    /**
     * Utility method to run the pre-compiled query and return the blob value in the
     * first column of the first row.
     *
     * @return A read-only file descriptor for a copy of the blob value.
     */
    public static ParcelFileDescriptor blobFileDescriptorForQuery(SQLiteStatement prog,
            String[] selectionArgs) {
        prog.bindAllArgsAsStrings(selectionArgs);
        return prog.simpleQueryForBlobFileDescriptor();
    }

    /**
     * Reads a String out of a column in a Cursor and writes it to a ContentValues.
     * Adds nothing to the ContentValues if the column isn't present or if its value is null.
     *
     * @param cursor The cursor to read from
     * @param column The column to read
     * @param values The {@link ContentValues} to put the value into
     */
    public static void cursorStringToContentValuesIfPresent(Cursor cursor, ContentValues values,
            String column) {
        final int index = cursor.getColumnIndex(column);
        if (index != -1 && !cursor.isNull(index)) {
            values.put(column, cursor.getString(index));
        }
    }

    /**
     * Reads a Long out of a column in a Cursor and writes it to a ContentValues.
     * Adds nothing to the ContentValues if the column isn't present or if its value is null.
     *
     * @param cursor The cursor to read from
     * @param column The column to read
     * @param values The {@link ContentValues} to put the value into
     */
    public static void cursorLongToContentValuesIfPresent(Cursor cursor, ContentValues values,
            String column) {
        final int index = cursor.getColumnIndex(column);
        if (index != -1 && !cursor.isNull(index)) {
            values.put(column, cursor.getLong(index));
        }
    }

    /**
     * Reads a Short out of a column in a Cursor and writes it to a ContentValues.
     * Adds nothing to the ContentValues if the column isn't present or if its value is null.
     *
     * @param cursor The cursor to read from
     * @param column The column to read
     * @param values The {@link ContentValues} to put the value into
     */
    public static void cursorShortToContentValuesIfPresent(Cursor cursor, ContentValues values,
            String column) {
        final int index = cursor.getColumnIndex(column);
        if (index != -1 && !cursor.isNull(index)) {
            values.put(column, cursor.getShort(index));
        }
    }

    /**
     * Reads a Integer out of a column in a Cursor and writes it to a ContentValues.
     * Adds nothing to the ContentValues if the column isn't present or if its value is null.
     *
     * @param cursor The cursor to read from
     * @param column The column to read
     * @param values The {@link ContentValues} to put the value into
     */
    public static void cursorIntToContentValuesIfPresent(Cursor cursor, ContentValues values,
            String column) {
        final int index = cursor.getColumnIndex(column);
        if (index != -1 && !cursor.isNull(index)) {
            values.put(column, cursor.getInt(index));
        }
    }

    /**
     * Reads a Float out of a column in a Cursor and writes it to a ContentValues.
     * Adds nothing to the ContentValues if the column isn't present or if its value is null.
     *
     * @param cursor The cursor to read from
     * @param column The column to read
     * @param values The {@link ContentValues} to put the value into
     */
    public static void cursorFloatToContentValuesIfPresent(Cursor cursor, ContentValues values,
            String column) {
        final int index = cursor.getColumnIndex(column);
        if (index != -1 && !cursor.isNull(index)) {
            values.put(column, cursor.getFloat(index));
        }
    }

    /**
     * Reads a Double out of a column in a Cursor and writes it to a ContentValues.
     * Adds nothing to the ContentValues if the column isn't present or if its value is null.
     *
     * @param cursor The cursor to read from
     * @param column The column to read
     * @param values The {@link ContentValues} to put the value into
     */
    public static void cursorDoubleToContentValuesIfPresent(Cursor cursor, ContentValues values,
            String column) {
        final int index = cursor.getColumnIndex(column);
        if (index != -1 && !cursor.isNull(index)) {
            values.put(column, cursor.getDouble(index));
        }
    }

    /**
     * This class allows users to do multiple inserts into a table using
     * the same statement.
     * <p>
     * This class is not thread-safe.
     * </p>
     *
     * @deprecated Use {@link SQLiteStatement} instead.
     */
    @Deprecated
    public static class InsertHelper {
        private final SQLiteDatabase mDb;
        private final String mTableName;
        private HashMap<String, Integer> mColumns;
        private String mInsertSQL = null;
        private SQLiteStatement mInsertStatement = null;
        private SQLiteStatement mReplaceStatement = null;
        private SQLiteStatement mPreparedStatement = null;

        /**
         * {@hide}
         *
         * These are the columns returned by sqlite's "PRAGMA
         * table_info(...)" command that we depend on.
         */
        public static final int TABLE_INFO_PRAGMA_COLUMNNAME_INDEX = 1;

        /**
         * This field was accidentally exposed in earlier versions of the platform
         * so we can hide it but we can't remove it.
         *
         * @hide
         */
        public static final int TABLE_INFO_PRAGMA_DEFAULT_INDEX = 4;

        /**
         * @param db the SQLiteDatabase to insert into
         * @param tableName the name of the table to insert into
         */
        public InsertHelper(SQLiteDatabase db, String tableName) {
            mDb = db;
            mTableName = tableName;
        }

        private void buildSQL() throws SQLException {
            StringBuilder sb = new StringBuilder(128);
            sb.append("INSERT INTO ");
            sb.append(mTableName);
            sb.append(" (");

            StringBuilder sbv = new StringBuilder(128);
            sbv.append("VALUES (");

            int i = 1;
            Cursor cur = null;
            try {
                cur = mDb.rawQuery("PRAGMA table_info(" + mTableName + ")", null);
                mColumns = new HashMap<String, Integer>(cur.getCount());
                while (cur.moveToNext()) {
                    String columnName = cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX);
                    String defaultValue = cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX);

                    mColumns.put(columnName, i);
                    sb.append("'");
                    sb.append(columnName);
                    sb.append("'");

                    if (defaultValue == null) {
                        sbv.append("?");
                    } else {
                        sbv.append("COALESCE(?, ");
                        sbv.append(defaultValue);
                        sbv.append(")");
                    }

                    sb.append(i == cur.getCount() ? ") " : ", ");
                    sbv.append(i == cur.getCount() ? ");" : ", ");
                    ++i;
                }
            } finally {
                if (cur != null) cur.close();
            }

            sb.append(sbv);

            mInsertSQL = sb.toString();
            if (DEBUG) Log.v(TAG, "insert statement is " + mInsertSQL);
        }

        private SQLiteStatement getStatement(boolean allowReplace) throws SQLException {
            if (allowReplace) {
                if (mReplaceStatement == null) {
                    if (mInsertSQL == null) buildSQL();
                    // chop "INSERT" off the front and prepend "INSERT OR REPLACE" instead.
                    String replaceSQL = "INSERT OR REPLACE" + mInsertSQL.substring(6);
                    mReplaceStatement = mDb.compileStatement(replaceSQL);
                }
                return mReplaceStatement;
            } else {
                if (mInsertStatement == null) {
                    if (mInsertSQL == null) buildSQL();
                    mInsertStatement = mDb.compileStatement(mInsertSQL);
                }
                return mInsertStatement;
            }
        }

        /**
         * Performs an insert, adding a new row with the given values.
         *
         * @param values the set of values with which  to populate the
         * new row
         * @param allowReplace if true, the statement does "INSERT OR
         *   REPLACE" instead of "INSERT", silently deleting any
         *   previously existing rows that would cause a conflict
         *
         * @return the row ID of the newly inserted row, or -1 if an
         * error occurred
         */
        private long insertInternal(ContentValues values, boolean allowReplace) {
            // Start a transaction even though we don't really need one.
            // This is to help maintain compatibility with applications that
            // access InsertHelper from multiple threads even though they never should have.
            // The original code used to lock the InsertHelper itself which was prone
            // to deadlocks.  Starting a transaction achieves the same mutual exclusion
            // effect as grabbing a lock but without the potential for deadlocks.
            mDb.beginTransactionNonExclusive();
            try {
                SQLiteStatement stmt = getStatement(allowReplace);
                stmt.clearBindings();
                if (DEBUG) Log.v(TAG, "--- inserting in table " + mTableName);
                for (Map.Entry<String, Object> e: values.valueSet()) {
                    final String key = e.getKey();
                    int i = getColumnIndex(key);
                    DatabaseUtils.bindObjectToProgram(stmt, i, e.getValue());
                    if (DEBUG) {
                        Log.v(TAG, "binding " + e.getValue() + " to column " +
                              i + " (" + key + ")");
                    }
                }
                long result = stmt.executeInsert();
                mDb.setTransactionSuccessful();
                return result;
            } catch (SQLException e) {
                Log.e(TAG, "Error inserting " + values + " into table  " + mTableName, e);
                return -1;
            } finally {
                mDb.endTransaction();
            }
        }

        /**
         * Returns the index of the specified column. This is index is suitagble for use
         * in calls to bind().
         * @param key the column name
         * @return the index of the column
         */
        public int getColumnIndex(String key) {
            getStatement(false);
            final Integer index = mColumns.get(key);
            if (index == null) {
                throw new IllegalArgumentException("column '" + key + "' is invalid");
            }
            return index;
        }

        /**
         * Bind the value to an index. A prepareForInsert() or prepareForReplace()
         * without a matching execute() must have already have been called.
         * @param index the index of the slot to which to bind
         * @param value the value to bind
         */
        public void bind(int index, double value) {
            mPreparedStatement.bindDouble(index, value);
        }

        /**
         * Bind the value to an index. A prepareForInsert() or prepareForReplace()
         * without a matching execute() must have already have been called.
         * @param index the index of the slot to which to bind
         * @param value the value to bind
         */
        public void bind(int index, float value) {
            mPreparedStatement.bindDouble(index, value);
        }

        /**
         * Bind the value to an index. A prepareForInsert() or prepareForReplace()
         * without a matching execute() must have already have been called.
         * @param index the index of the slot to which to bind
         * @param value the value to bind
         */
        public void bind(int index, long value) {
            mPreparedStatement.bindLong(index, value);
        }

        /**
         * Bind the value to an index. A prepareForInsert() or prepareForReplace()
         * without a matching execute() must have already have been called.
         * @param index the index of the slot to which to bind
         * @param value the value to bind
         */
        public void bind(int index, int value) {
            mPreparedStatement.bindLong(index, value);
        }

        /**
         * Bind the value to an index. A prepareForInsert() or prepareForReplace()
         * without a matching execute() must have already have been called.
         * @param index the index of the slot to which to bind
         * @param value the value to bind
         */
        public void bind(int index, boolean value) {
            mPreparedStatement.bindLong(index, value ? 1 : 0);
        }

        /**
         * Bind null to an index. A prepareForInsert() or prepareForReplace()
         * without a matching execute() must have already have been called.
         * @param index the index of the slot to which to bind
         */
        public void bindNull(int index) {
            mPreparedStatement.bindNull(index);
        }

        /**
         * Bind the value to an index. A prepareForInsert() or prepareForReplace()
         * without a matching execute() must have already have been called.
         * @param index the index of the slot to which to bind
         * @param value the value to bind
         */
        public void bind(int index, byte[] value) {
            if (value == null) {
                mPreparedStatement.bindNull(index);
            } else {
                mPreparedStatement.bindBlob(index, value);
            }
        }

        /**
         * Bind the value to an index. A prepareForInsert() or prepareForReplace()
         * without a matching execute() must have already have been called.
         * @param index the index of the slot to which to bind
         * @param value the value to bind
         */
        public void bind(int index, String value) {
            if (value == null) {
                mPreparedStatement.bindNull(index);
            } else {
                mPreparedStatement.bindString(index, value);
            }
        }

        /**
         * Performs an insert, adding a new row with the given values.
         * If the table contains conflicting rows, an error is
         * returned.
         *
         * @param values the set of values with which to populate the
         * new row
         *
         * @return the row ID of the newly inserted row, or -1 if an
         * error occurred
         */
        public long insert(ContentValues values) {
            return insertInternal(values, false);
        }

        /**
         * Execute the previously prepared insert or replace using the bound values
         * since the last call to prepareForInsert or prepareForReplace.
         *
         * <p>Note that calling bind() and then execute() is not thread-safe. The only thread-safe
         * way to use this class is to call insert() or replace().
         *
         * @return the row ID of the newly inserted row, or -1 if an
         * error occurred
         */
        public long execute() {
            if (mPreparedStatement == null) {
                throw new IllegalStateException("you must prepare this inserter before calling "
                        + "execute");
            }
            try {
                if (DEBUG) Log.v(TAG, "--- doing insert or replace in table " + mTableName);
                return mPreparedStatement.executeInsert();
            } catch (SQLException e) {
                Log.e(TAG, "Error executing InsertHelper with table " + mTableName, e);
                return -1;
            } finally {
                // you can only call this once per prepare
                mPreparedStatement = null;
            }
        }

        /**
         * Prepare the InsertHelper for an insert. The pattern for this is:
         * <ul>
         * <li>prepareForInsert()
         * <li>bind(index, value);
         * <li>bind(index, value);
         * <li>...
         * <li>bind(index, value);
         * <li>execute();
         * </ul>
         */
        public void prepareForInsert() {
            mPreparedStatement = getStatement(false);
            mPreparedStatement.clearBindings();
        }

        /**
         * Prepare the InsertHelper for a replace. The pattern for this is:
         * <ul>
         * <li>prepareForReplace()
         * <li>bind(index, value);
         * <li>bind(index, value);
         * <li>...
         * <li>bind(index, value);
         * <li>execute();
         * </ul>
         */
        public void prepareForReplace() {
            mPreparedStatement = getStatement(true);
            mPreparedStatement.clearBindings();
        }

        /**
         * Performs an insert, adding a new row with the given values.
         * If the table contains conflicting rows, they are deleted
         * and replaced with the new row.
         *
         * @param values the set of values with which to populate the
         * new row
         *
         * @return the row ID of the newly inserted row, or -1 if an
         * error occurred
         */
        public long replace(ContentValues values) {
            return insertInternal(values, true);
        }

        /**
         * Close this object and release any resources associated with
         * it.  The behavior of calling <code>insert()</code> after
         * calling this method is undefined.
         */
        public void close() {
            if (mInsertStatement != null) {
                mInsertStatement.close();
                mInsertStatement = null;
            }
            if (mReplaceStatement != null) {
                mReplaceStatement.close();
                mReplaceStatement = null;
            }
            mInsertSQL = null;
            mColumns = null;
        }
    }

    /**
     * Creates a db and populates it with the sql statements in sqlStatements.
     *
     * @param context the context to use to create the db
     * @param dbName the name of the db to create
     * @param dbVersion the version to set on the db
     * @param sqlStatements the statements to use to populate the db. This should be a single string
     *   of the form returned by sqlite3's <tt>.dump</tt> command (statements separated by
     *   semicolons)
     */
    static public void createDbFromSqlStatements(
            Context context, String dbName, int dbVersion, String sqlStatements) {

        File f = context.getDatabasePath(dbName);
        f.mkdirs();
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(f, null);

        // TODO: this is not quite safe since it assumes that all semicolons at the end of a line
        // terminate statements. It is possible that a text field contains ;\n. We will have to fix
        // this if that turns out to be a problem.
        String[] statements = TextUtils.split(sqlStatements, ";\n");
        for (String statement : statements) {
            if (TextUtils.isEmpty(statement)) continue;
            db.execSQL(statement);
        }
        db.setVersion(dbVersion);
        db.close();
    }

    /**
     * Returns one of the following which represent the type of the given SQL statement.
     * <ol>
     *   <li>{@link #STATEMENT_SELECT}</li>
     *   <li>{@link #STATEMENT_UPDATE}</li>
     *   <li>{@link #STATEMENT_ATTACH}</li>
     *   <li>{@link #STATEMENT_BEGIN}</li>
     *   <li>{@link #STATEMENT_COMMIT}</li>
     *   <li>{@link #STATEMENT_ABORT}</li>
     *   <li>{@link #STATEMENT_OTHER}</li>
     * </ol>
     * @param sql the SQL statement whose type is returned by this method
     * @return one of the values listed above
     */
    public static int getSqlStatementType(String sql) {
        sql = sql.trim();
        if (sql.length() < 3) {
            return STATEMENT_OTHER;
        }
        String prefixSql = sql.substring(0, 3).toUpperCase(Locale.ROOT);
        if (prefixSql.equals("SEL")) {
            return STATEMENT_SELECT;
        } else if (prefixSql.equals("INS") ||
                prefixSql.equals("UPD") ||
                prefixSql.equals("REP") ||
                prefixSql.equals("DEL")) {
            return STATEMENT_UPDATE;
        } else if (prefixSql.equals("ATT")) {
            return STATEMENT_ATTACH;
        } else if (prefixSql.equals("COM")) {
            return STATEMENT_COMMIT;
        } else if (prefixSql.equals("END")) {
            return STATEMENT_COMMIT;
        } else if (prefixSql.equals("ROL")) {
            return STATEMENT_ABORT;
        } else if (prefixSql.equals("BEG")) {
            return STATEMENT_BEGIN;
        } else if (prefixSql.equals("PRA")) {
            return STATEMENT_PRAGMA;
        } else if (prefixSql.equals("CRE") || prefixSql.equals("DRO") ||
                prefixSql.equals("ALT")) {
            return STATEMENT_DDL;
        } else if (prefixSql.equals("ANA") || prefixSql.equals("DET")) {
            return STATEMENT_UNPREPARED;
        }
        return STATEMENT_OTHER;
    }

    /**
     * Appends one set of selection args to another. This is useful when adding a selection
     * argument to a user provided set.
     */
    public static String[] appendSelectionArgs(String[] originalValues, String[] newValues) {
        if (originalValues == null || originalValues.length == 0) {
            return newValues;
        }
        String[] result = new String[originalValues.length + newValues.length ];
        System.arraycopy(originalValues, 0, result, 0, originalValues.length);
        System.arraycopy(newValues, 0, result, originalValues.length, newValues.length);
        return result;
    }

    /**
     * Returns column index of "_id" column, or -1 if not found.
     * @hide
     */
    public static int findRowIdColumnIndex(String[] columnNames) {
        int length = columnNames.length;
        for (int i = 0; i < length; i++) {
            if (columnNames[i].equals("_id")) {
                return i;
            }
        }
        return -1;
    }
}