Android SQLite Syntax Error creating a view with RANK()

Question

I'm trying to create a view in SQLite using RANK() window function. When I execute the following code:

val txt = "CREATE VIEW VW_CAPITULOS_ORDER AS\n" +
          "    SELECT id_libro, capitulo,\n" +
          "            RANK() OVER (\n" +
          "                ORDER BY id_libro, CAST(SUBSTR(capitulo, 1, INSTR(capitulo||' ', ' ')) AS INTEGER)\n" +
          "                ) ranking\n" +
          "        FROM tbl_capitulos;\n"

db.execSQL(txt)

and at runtime I'm receiving the following exception:

*E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.systango.bibliacatolica, PID: 20837
    java.lang.RuntimeException: Unable to start activity ComponentInfo{com.systango.bibliacatolica/com.systango.bibliacatolica.MainActivity}: android.database.sqlite.SQLiteException: near "(": syntax error (Sqlite code 1 SQLITE_ERROR): , while compiling: CREATE VIEW VW_CAPITULOS_ORDER AS
        SELECT id_libro, capitulo,
                RANK() OVER (
                    ORDER BY id_libro, CAST(SUBSTR(capitulo, 1, INSTR(capitulo||' ', ' ')) AS INTEGER)
                    ) ranking
            FROM tbl_capitulos;, (OS error - 11:Try again)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3897)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:4076)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:91)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:149)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:103)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2473)
        at android.os.Handler.dispatchMessage(Handler.java:110)
        at android.os.Looper.loop(Looper.java:219)
        at android.app.ActivityThread.main(ActivityThread.java:8349)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:513)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1055)
     Caused by: android.database.sqlite.SQLiteException: near "(": syntax error (Sqlite code 1 SQLITE_ERROR): , while compiling: CREATE VIEW VW_CAPITULOS_ORDER AS
        SELECT id_libro, capitulo,
                RANK() OVER (
                    ORDER BY id_libro, CAST(SUBSTR(capitulo, 1, INSTR(capitulo||' ', ' ')) AS INTEGER)
                    ) ranking
            FROM tbl_capitulos;, (OS error - 11:Try again)
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1030)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:637)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:610)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:66)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:33)
        at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1899)
        at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1817)*

I suspect that it's a problem with the SQLite version present in Android (checked version 3.22 on Huawei P40 Lite), or something like that. SQLite documentation says that window functions appear in version 3.25 (https://sqlite.org/windowfunctions.html). This script executes without problem in the DB Browser for SQLite app. Exist a way to use a new version of SQLite that the version that comes with the phone?

Answer 1

Your app can't change the version of SQLite of your phone.
As a workaround you can use a correlated subquery to imitate the functionality of the window function RANK():

CREATE VIEW VW_CAPITULOS_ORDER AS
SELECT c.id_libro, c.capitulo,
       (
         SELECT COUNT(*) FROM tbl_capitulos 
         WHERE id_libro < c.id_libro 
            OR (id_libro = c.id_libro AND (capitulo + 0) < (c.capitulo + 0))
       ) + 1 ranking
FROM tbl_capitulos c;

Note that you don't need to take the substring of capitulo up to the first ' ' in order to convert it to an integer.
You can do an implicit conversion by adding 0.