Select Operation Analysis of Android SQLite

Just likeAndroid SQLiteStatement compilation and execution AnalysisAll SQL statements in SQLite must be compiled to stmt and then executed.
The above article introduces SQLiteStatement compilation and execution at the android level. However, SQLiteStatement can only be used to execute SQL statements without return values or with only one row and one column (1X1), such as INSERT ALERT, the returned results such as SELECT may have multiple rows and multiple columns.
Android provides a special execution method rawQuery () for select. It also has special SQLiteQuery classes and related Cursor classes. In this article, we can see that SQLiteQuery and SQLiteStatement are essentially the same. android implements a special Execution Process for the special nature of select.

1. query

SQLiteDatabase db = mOpenHelper.getWritableDatabase();Cursor cr;cr = db.rawQuery("select * from person where age=20", null);if (cr.moveToFirst()) {    for (int i = 0; i < cr.getCount(); i++) {        cr.getString();        cr.moveToNext();    }}

2. query operations

//SQLiteDatabase.java    public Cursor rawQuery(String sql, String[] selectionArgs) {        return rawQueryWithFactory(null, sql, selectionArgs, null, null);    }    public Cursor rawQueryWithFactory(            CursorFactory cursorFactory, String sql, String[] selectionArgs,            String editTable, CancellationSignal cancellationSignal) {        acquireReference();        try {            SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(this, sql, editTable,                    cancellationSignal);  // ①            return driver.query(cursorFactory != null ? cursorFactory : mCursorFactory,                    selectionArgs);       // ②        } finally {            releaseReference();        }    }

Here, two steps are taken: Build a driver and execute it through the driver. So what is SQLiteDirectCursorDriver?

// SQLiteDirectCursorDriver.javapublic final class SQLiteDirectCursorDriver implements SQLiteCursorDriver {    private final SQLiteDatabase mDatabase;    private final String mEditTable;     private final String mSql;    private final CancellationSignal mCancellationSignal;    private SQLiteQuery mQuery;    public SQLiteDirectCursorDriver(SQLiteDatabase db, String sql, String editTable,            CancellationSignal cancellationSignal) {         mDatabase = db;        mEditTable = editTable;        mSql = sql;        mCancellationSignal = cancellationSignal;    }    public Cursor query(CursorFactory factory, String[] selectionArgs) {        final SQLiteQuery query = new SQLiteQuery(mDatabase, mSql, mCancellationSignal); //③        final Cursor cursor;        try {            query.bindAllArgsAsStrings(selectionArgs);            if (factory == null) {                 cursor = new SQLiteCursor(this, mEditTable, query); // ④            } else {                cursor = factory.newCursor(mDatabase, this, mEditTable, query);            }        } catch (RuntimeException ex) {            query.close();            throw ex;        }        mQuery = query;          return cursor;    }

It can be seen that a strange member variable of the driver is SQLiteQuery, and it does not appear in the constructor. It appears only when the driver. query is executed and assigned a value ③. Then we constructed the familiar cursor and returned it to ④.

First, let's take a look at SQLiteQuery:

// SQLiteQuery.javapublic final class SQLiteQuery extends SQLiteProgram {    private static final String TAG = "SQLiteQuery";    private final CancellationSignal mCancellationSignal;    SQLiteQuery(SQLiteDatabase db, String query, CancellationSignal cancellationSignal) {        super(db, query, null, cancellationSignal);        mCancellationSignal = cancellationSignal;    }

As mentioned at the beginning of the article, SQLiteQuery inherits from SQLiteProgram, which is the same as SQLiteStatement. By ( Android SQLiteStatement compilation and execution AnalysisAs you can see, the constructor has gone through the SQL statement prepare process and has included the corresponding stmt in a connection pool.
As you can see in ④, if factory = null, that is, there is no custom cursor factory class (we generally do not customize it), a SQLiteCursor will be constructed directly. For more information, see the SQLiteCursor class.

// SQLiteCursor.javapublic class SQLiteCursor extends AbstractWindowedCursor {    static final String TAG = "SQLiteCursor";    static final int NO_COUNT = -1;    private final String mEditTable;    private final String[] mColumns;    private final SQLiteQuery mQuery;    private final SQLiteCursorDriver mDriver;    private int mCount = NO_COUNT;    private int mCursorWindowCapacity;    private Map
 
   mColumnNameMap;    private final Throwable mStackTrace;    public SQLiteCursor(SQLiteCursorDriver driver, String editTable, SQLiteQuery query) {        ……        mDriver = driver;        mEditTable = editTable;        mColumnNameMap = null;        mQuery = query;        mColumns = query.getColumnNames();        mRowIdColumnIndex = DatabaseUtils.findRowIdColumnIndex(mColumns);    }
 

As you can see, SQLiteCursor also maintains metadata, But it inherits from abstractintowedcursor, and the latter inherits from AbstractCursor.

// AbstractWindowedCursor.javapublic abstract class AbstractWindowedCursor extends AbstractCursor {    protected CursorWindow mWindow;}// AbstractCursor.javapublic abstract class AbstractCursor implements CrossProcessCursor {    protected int mPos;    ......}

In abstractshortwedcursor, we see CursorWindow, which is an important concept in the database.

// CursorWindow.javapublic class CursorWindow extends SQLiteClosable implements Parcelable {    public int mWindowPtr;  // ！！！    private int mStartPos;    private final String mName;    private final CloseGuard mCloseGuard = CloseGuard.get();    private static native int nativeCreate(String name, int cursorWindowSize);    private static native void nativeClear(int windowPtr);    private static native int nativeGetNumRows(int windowPtr);    private static native double nativeGetDouble(int windowPtr, int row, int column);    ……}

MWindowPtr is a pointer to the corresponding windows of sqlite in the native layer. This class also contains many native methods. Some of the window operations in sqlite should be implemented through this class.
Through inheritance, SQLiteCursor has the ability to point to the cursor window, but it is not reflected in the constructor. In addition, when the driver. query is used, the cursor at the new is directly returned. At this point, the select statement has not been actually executed through native.

3. Cursor operations

//AbstractCursor.java    public final boolean moveToFirst() {        return moveToPosition(0);    }    public final boolean moveToNext() {        return moveToPosition(mPos + 1);    }    public final boolean moveToPosition(int position) {        final int count = getCount(); // ⑤        if (position >= count) {            mPos = count;            return false;        }        if (position < 0) {            mPos = -1;            return false;        }        if (position == mPos) {            return true;        }        boolean result = onMove(mPos, position); /// ⑨        if (result == false) {            mPos = -1;        } else {            mPos = position;            if (mRowIdColumnIndex != -1) {                mCurrentRowID = Long.valueOf(getLong(mRowIdColumnIndex));            }        }        return result;    }// SQLiteCursor.java    @Override    public int getCount() {        if (mCount == NO_COUNT) {            fillWindow(0);         }        return mCount;    }    @Override    public boolean onMove(int oldPosition, int newPosition) {        if (mWindow == null || newPosition < mWindow.getStartPosition() ||                newPosition >= (mWindow.getStartPosition() + mWindow.getNumRows())) {            fillWindow(newPosition);        }        return true;    }

Here we can see that the first time moveToPosition, because mCount is-1, fillWindow (0 ).

// SQLiteCursor. java private void fillWindow (int requiredPos) {clearOrCreateWindow (getDatabase (). getPath (); // ⑥ if (mCount = NO_COUNT) {int startPos = DatabaseUtils. cursorPickFillWindowStartPosition (requiredPos, 0); // 7mcount = mQuery. fillWindow (mWindow, startPos, requiredPos, true); // optional mCursorWindowCapacity = mWindow. getNumRows (); if (Log. isLoggable (TAG, Log. DEBUG) {Log. d (TAG, "sorted ed count (*) from native_fill_window:" + mCount) ;}} else {int startPos = DatabaseUtils. cursorPickFillWindowStartPosition (requiredPos, mCursorWindowCapacity); mQuery. fillWindow (mWindow, startPos, requiredPos, false) ;}} protected void clearOrCreateWindow (String name) {if (mWindow = null) {// create CursorWindow mWindow = new CursorWindow (name);} else {mWindow. clear ();}}

In section 6, a new CursorWindow is generated and assigned to the mWindow. In this case, SQLiteCursor is in charge. As follows, nativeCreate is called during the new CursorWindow process and mWindowPtr is directed to the window on the native layer.

// CursorWindow.java    public CursorWindow(String name) {        mStartPos = 0;        mName = name != null && name.length() != 0 ? name : "
 
  ";        mWindowPtr = nativeCreate(mName, sCursorWindowSize); // ！！！        if (mWindowPtr == 0) {            throw new CursorWindowAllocationException("Cursor window allocation of " +                    (sCursorWindowSize / 1024) + " kb failed. " + printStats());        }        mCloseGuard.open("close");        recordNewWindow(Binder.getCallingPid(), mWindowPtr);    }
 

First look at the nth vertex, fillWindow ()

// SQLiteQuery.java    int fillWindow(CursorWindow window, int startPos, int requiredPos, boolean countAllRows) {         ....         int numRows = getSession().executeForCursorWindow(getSql(), getBindArgs(),                        window, startPos, requiredPos, countAllRows, getConnectionFlags(),                        mCancellationSignal);         return numRows;     }// SQLiteSeesion.java    public int executeForCursorWindow(String sql, Object[] bindArgs,            CursorWindow window, int startPos, int requiredPos, boolean countAllRows,            int connectionFlags, CancellationSignal cancellationSignal) {        acquireConnection(sql, connectionFlags, cancellationSignal);         try {            return mConnection.executeForCursorWindow(sql, bindArgs,                    window, startPos, requiredPos, countAllRows,                    cancellationSignal);         } finally {            releaseConnection();        }    }// SQLiteConnection.java    public int executeForCursorWindow(String sql, Object[] bindArgs,        CursorWindow window, int startPos, int requiredPos, boolean countAllRows,        CancellationSignal cancellationSignal) {        final PreparedStatement statement = acquirePreparedStatement(sql);        final long result = nativeExecuteForCursorWindow(   // !!!                mConnectionPtr, statement.mStatementPtr, window.mWindowPtr,                startPos, requiredPos, countAllRows);        actualPos = (int)(result >> 32);        countedRows = (int)result;        filledRows = window.getNumRows();        window.setStartPosition(actualPos);        return countedRows;        .....    }

As you can see, SQLiteConnection is still used to connect to native.

The rest of getString is relatively simple and will always call the getString of mWindow.

    public String getString(int row, int column) {        acquireReference();        try {            return nativeGetString(mWindowPtr, row - mStartPos, column);        } finally {            releaseReference();        }    }

Finally, let's take a look at section 7, that isWindow fill control.

The fill policy is involved. What if the result set is larger than window? What if a required element is not in the window?Not analyzed in detailPaste the code.

If it is the first fill, the required row is 0, that is, the fill is full of Windows starting from the first record.

Window will contain the required row and its surrounding rows. For example, if you want to add 120th elements to the result set and the window size is 90, fill in the elements of the result set 90-180 to window, 30 before 120, and 60 after. If the window does not exist, place the window in the 10th position of the window.

// DatabaseUtils.java    public static int cursorPickFillWindowStartPosition(            int cursorPosition, int cursorWindowCapacity) {        return Math.max(cursorPosition - cursorWindowCapacity / 3, 0);    }

static jlong nativeExecuteForCursorWindow(JNIEnv* env, jclass clazz,        jint connectionPtr, jint statementPtr, jint windowPtr,        jint startPos, jint requiredPos, jboolean countAllRows) {    ......    int retryCount = 0;    int totalRows = 0;    int addedRows = 0;    bool windowFull = false;    bool gotException = false;    while (!gotException && (!windowFull || countAllRows)) {        int err = sqlite3_step(statement);        if (err == SQLITE_ROW) {            LOG_WINDOW("Stepped statement %p to row %d", statement, totalRows);            retryCount = 0;            totalRows += 1;            // Skip the row if the window is full or we haven't reached the start position yet.            if (startPos >= totalRows || windowFull) {                continue;            }            CopyRowResult cpr = copyRow(env, window, statement, numColumns, startPos, addedRows);            if (cpr == CPR_FULL && addedRows && startPos + addedRows < requiredPos) {                // We filled the window before we got to the one row that we really wanted.                // Clear the window and start filling it again from here.                // TODO: Would be nicer if we could progressively replace earlier rows.                window->clear();                window->setNumColumns(numColumns);                startPos += addedRows;                addedRows = 0;                cpr = copyRow(env, window, statement, numColumns, startPos, addedRows);            }            if (cpr == CPR_OK) {                addedRows += 1;            } else if (cpr == CPR_FULL) {                windowFull = true;            } else {                gotException = true;            }        } else if (err == SQLITE_DONE) {            // All rows processed, bail            LOG_WINDOW("Processed all rows");            break;        } else if (err == SQLITE_LOCKED || err == SQLITE_BUSY) {            // The table is locked, retry            LOG_WINDOW("Database locked, retrying");            if (retryCount > 50) {                ALOGE("Bailing on database busy retry");                throw_sqlite3_exception(env, connection->db, "retrycount exceeded");                gotException = true;            } else {                // Sleep to give the thread holding the lock a chance to finish                usleep(1000);                retryCount++;            }        } else {            throw_sqlite3_exception(env, connection->db);            gotException = true;        }    }    LOG_WINDOW("Resetting statement %p after fetching %d rows and adding %d rows"            "to the window in %d bytes",            statement, totalRows, addedRows, window->size() - window->freeSpace());    sqlite3_reset(statement);    // Report the total number of rows on request.    if (startPos > totalRows) {        ALOGE("startPos %d > actual rows %d", startPos, totalRows);    }    jlong result = jlong(startPos) << 32 | jlong(totalRows);    return result;}

4. Summary

① The execution of a query statement is the same as that of a common SQL statement, and must be compiled and executed by the SQL statement.

② SQLiteQuery is compiled. After execution, SQLiteCursor is returned. The mWindow of SQLiteCursor points to the cursor window of the native layer.

③ Use SQLiteCursor to control the returned results.

④ The Execution Process is the process of building SQLiteCursor, and the result set is not written into the corresponding window.

⑤ The result set is written to the window, which occurs in the first operation similar to cursor. moveToFirst. This is everywhere in androidInertia Policy.

⑥ Sqlite itself optimizes the relationship between the result set and window, and android optimizes the result set again to cope with problems such as the result set being too large and skip reading results.Not analyzed.