SQLite Android Bindings

package org.sqlite.customsqlitetest;

import android.content.Context;
import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.TextView;

import org.json.JSONObject;
import org.sqlite.database.DatabaseErrorHandler;
import org.sqlite.database.sqlite.SQLiteDatabase;
import org.sqlite.database.sqlite.SQLiteStatement;
import org.sqlite.database.sqlite.SQLiteDatabaseCorruptException;
import org.sqlite.database.sqlite.SQLiteOpenHelper;

import java.io.File;

        String res;

        db = SQLiteDatabase.openOrCreateDatabase(":memory:", null);
        st = db.compileStatement("SELECT sqlite_version()");
        res = st.simpleQueryForString();

        myTV.append("SQLite version " + res + "\n\n");
        db.close();
    }

    public void test_warning(String name, String warning){
        myTV.append("WARNING:" + name + ": " + warning + "\n");
    }

    public void test_result(String name, String res, String expected, long t0){
        long tot = (System.nanoTime() - t0) / 1000000;
        myTV.append(name + "... ");
        myNTest++;

        if( res.equals(expected) ){
            myTV.append("ok (" + tot + "ms)\n");
        } else {
            myNErr++;
            myTV.append("FAILED\n");
            myTV.append("   res=     \"" + res + "\"\n");
            myTV.append("   expected=\"" + expected + "\"\n");
        }
    }

        String db_path2 = DB_PATH.toString() + "2";

        db.execSQL("CREATE TABLE t1(x, y)");
        db.execSQL("INSERT INTO t1 VALUES (1, 2), (3, 4)");

        Thread t = new Thread( new Runnable() {
            public void run() {
                final long t0 = System.nanoTime();
                SQLiteStatement st = db.compileStatement("SELECT sum(x+y) FROM t1");
                String res = st.simpleQueryForString();
                test_result("thread_test_1", res, "10", t0);
            }
        });

        t.start();
        try {
            t.join();
        } catch (InterruptedException e) {
        }
        db.close();
    }

    /*
    ** Test that a database connection may be accessed from a second thread.
    */
    public void thread_test_2(){
        final long t0 = System.nanoTime();
        SQLiteDatabase.deleteDatabase(DB_PATH);
        final SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);

        db.execSQL("CREATE TABLE t1(x, y)");
        db.execSQL("INSERT INTO t1 VALUES (1, 2), (3, 4)");

        db.enableWriteAheadLogging();

            try { Thread.sleep(100); } catch(InterruptedException e) {}
        }
        if( t.isAlive() ){ res = "blocked"; }

        db.endTransaction();
        try { t.join(); } catch(InterruptedException e) {}
        if( SQLiteDatabase.hasCodec() ){
            test_result("thread_test_2", res, "blocked", t0);
        } else {
            test_result("thread_test_2", res, "concurrent", t0);
        }
        db.close();
    }

    /*
    ** Use a Cursor to loop through the results of a SELECT query.
    */
    public void csr_test_2() throws Exception {
        final long t0 = System.nanoTime();
        SQLiteDatabase.deleteDatabase(DB_PATH);
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
        String res = "";
        String expect = "";
        int i;
        int nRow = 0;

            for(bRes=c.moveToFirst(); bRes; bRes=c.moveToNext()){
                String x = c.getString(0);
                res = res + "." + x;
            }
        }else{
            test_warning("csr_test_1", "c==NULL");
        }
        test_result("csr_test_2.1", res, expect, t0);

        final long t1 = System.nanoTime();
        db.execSQL("BEGIN");
        for(i=0; i<1000; i++){
            db.execSQL("INSERT INTO t1 VALUES (X'123456'), (X'789ABC'), (X'DEF012')");
            db.execSQL("INSERT INTO t1 VALUES (45), (46), (47)");
            db.execSQL("INSERT INTO t1 VALUES (8.1), (8.2), (8.3)");
            db.execSQL("INSERT INTO t1 VALUES (NULL), (NULL), (NULL)");
        }
        db.execSQL("COMMIT");

        c = db.rawQuery("SELECT x FROM t1", null);
        if( c!=null ){
            boolean bRes;
            for(bRes=c.moveToFirst(); bRes; bRes=c.moveToNext()) nRow++;
        }else{
            test_warning("csr_test_1", "c==NULL");
        }
        test_result("csr_test_2.2", "" + nRow, "15000", t1);

        db.close();
    }

    public String string_from_t1_x(SQLiteDatabase db){
        String res = "";

        Cursor c = db.rawQuery("SELECT x FROM t1", null);
        boolean bRes;
        for(bRes=c.moveToFirst(); bRes; bRes=c.moveToNext()){
            String x = c.getString(0);
            res = res + "." + x;
        }

        return res;
    }
    public void csr_test_1() throws Exception {
        final long t0 = System.nanoTime();
        SQLiteDatabase.deleteDatabase(DB_PATH);
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
        String res = "";

        db.execSQL("CREATE TABLE t1(x)");
        db.execSQL("INSERT INTO t1 VALUES ('one'), ('two'), ('three')");

        res = string_from_t1_x(db);
        test_result("csr_test_1.1", res, ".one.two.three", t0);
        final long t1 = System.nanoTime();

        db.close();
        test_result("csr_test_1.2", db_is_encrypted(), "unencrypted", t1);
    }

    public void stmt_jrnl_test_1() throws Exception {
        final long t0 = System.nanoTime();
        SQLiteDatabase.deleteDatabase(DB_PATH);
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
        String res = "";

        db.execSQL("CREATE TABLE t1(x, y UNIQUE)");
        db.execSQL("BEGIN");
        db.execSQL("INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3)");
        db.execSQL("UPDATE t1 SET y=y+3");
        db.execSQL("COMMIT");
        db.close();
        test_result("stmt_jrnl_test_1.1", "did not crash", "did not crash", t0);
    }


    public void supp_char_test_1() throws Exception {
        final long t0 = System.nanoTime();
        SQLiteDatabase.deleteDatabase(DB_PATH);
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
        String res = "";
        String smiley = new String( Character.toChars(0x10000) );

        db.execSQL("CREATE TABLE t1(x)");
        db.execSQL("INSERT INTO t1 VALUES ('a" + smiley + "b')");

        res = string_from_t1_x(db);

        test_result("supp_char_test1." + smiley, res, ".a" + smiley + "b", t0);

        db.close();
    }

    /*
    ** If this is a SEE build, check that encrypted databases work.
    */
    public void see_test_1() throws Exception {
        final long t0 = System.nanoTime();
        if( !SQLiteDatabase.hasCodec() ) return;

        SQLiteDatabase.deleteDatabase(DB_PATH);
        String res = "";

        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
        db.execSQL("PRAGMA key = 'secretkey'");

        db.execSQL("CREATE TABLE t1(x)");
        db.execSQL("INSERT INTO t1 VALUES ('one'), ('two'), ('three')");

        res = string_from_t1_x(db);
        test_result("see_test_1.1", res, ".one.two.three", t0);
        final long t1 = System.nanoTime();
        db.close();

        test_result("see_test_1.2", db_is_encrypted(), "encrypted", t1);
        final long t2 = System.nanoTime();

        db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
        db.execSQL("PRAGMA key = 'secretkey'");
        res = string_from_t1_x(db);
        test_result("see_test_1.3", res, ".one.two.three", t2);
        final long t3 = System.nanoTime();
        db.close();

        res = "unencrypted";
        try {
            db = SQLiteDatabase.openOrCreateDatabase(DB_PATH.getPath(), null);
            string_from_t1_x(db);
        } catch ( SQLiteDatabaseCorruptException e ){
            res = "encrypted";
        } finally {
            db.close();
        }
        test_result("see_test_1.4", res, "encrypted", t3);
        final long t4 = System.nanoTime();

        res = "unencrypted";
        try {
            db = SQLiteDatabase.openOrCreateDatabase(DB_PATH.getPath(), null);
            db.execSQL("PRAGMA key = 'otherkey'");
            string_from_t1_x(db);
        } catch ( SQLiteDatabaseCorruptException e ){
            res = "encrypted";
        } finally {
            db.close();
        }
        test_result("see_test_1.5", res, "encrypted", t4);
    }
    class MyHelper extends SQLiteOpenHelper {
        public MyHelper(Context ctx){
            super(ctx, DB_PATH.getPath(), null, 1);
        }
        public void onConfigure(SQLiteDatabase db){
            db.execSQL("PRAGMA key = 'secret'");
        }
        public void onCreate(SQLiteDatabase db){
            db.execSQL("CREATE TABLE t1(x)");
        }
        public void onUpgrade(SQLiteDatabase db, int iOld, int iNew){
        }
    }

    /*
    ** Check that SQLiteOpenHelper works.
    */
    public void helper_test_1() throws Exception {
        final long t0 = System.nanoTime();
        SQLiteDatabase.deleteDatabase(DB_PATH);

        MyHelper helper = new MyHelper(this);
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("INSERT INTO t1 VALUES ('x'), ('y'), ('z')");

        String res = string_from_t1_x(db);
        test_result("helper.1", res, ".x.y.z", t0);

        helper.close();
    }

    /*
    ** If this is a SEE build, check that SQLiteOpenHelper still works.
    */
    public void see_test_2() throws Exception {
        final long t0 = System.nanoTime();
        if( !SQLiteDatabase.hasCodec() ) return;
        SQLiteDatabase.deleteDatabase(DB_PATH);

        MyHelper helper = new MyHelper(this);
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("INSERT INTO t1 VALUES ('x'), ('y'), ('z')");

        String res = string_from_t1_x(db);
        test_result("see_test_2.1", res, ".x.y.z", t0);
        final long t1 = System.nanoTime();
        test_result("see_test_2.2", db_is_encrypted(), "encrypted", t1);
        final long t2 = System.nanoTime();

        helper.close();
        helper = new MyHelper(this);
        db = helper.getReadableDatabase();
        test_result("see_test_2.3", res, ".x.y.z", t2);
        final long t3 = System.nanoTime();

        db = helper.getWritableDatabase();
        test_result("see_test_2.4", res, ".x.y.z", t3);
        final long t4 = System.nanoTime();

        test_result("see_test_2.5", db_is_encrypted(), "encrypted", t4);
        db.close();
    }

    private static boolean mLibIsLoaded = false;
    private static void loadLibrary() {
        if (!mLibIsLoaded) {
            System.loadLibrary("sqliteX");
            mLibIsLoaded = true;
        }
    }

    public void run_the_tests(View view){
        myTV.setText("");
        view.post(new Runnable() {
            @Override
            public void run() {
                run_the_tests_really();
            }
        });
    }

    public void run_the_tests_really(){
        loadLibrary();
        DB_PATH = getApplicationContext().getDatabasePath("test.db");
        DB_PATH.mkdirs();

        myTV.setText("");
        myNErr = 0;
        myNTest = 0;

        try {
            report_version();
            helper_test_1();
            supp_char_test_1();
            csr_test_1();
            csr_test_2();
            thread_test_1();
            thread_test_2();
            see_test_1();
            see_test_2();
            stmt_jrnl_test_1();
            json_test_1();

            myTV.append("\n" + myNErr + " errors from " + myNTest + " tests\n");
        } catch(Exception e) {
            myTV.append("Exception: " + e.toString() + "\n");
            myTV.append(android.util.Log.getStackTraceString(e) + "\n");
        }
    }

    public void json_test_1() throws Exception {
        SQLiteDatabase.deleteDatabase(DB_PATH);
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
        final long t0 = System.nanoTime();
        db.beginTransaction();
        String res = "";

        db.execSQL("CREATE TABLE t1(x, y)");
        JSONObject json = new JSONObject();
        json.put("Foo", 1);
        json.put("Bar", "Gum");
        db.execSQL("INSERT INTO t1 VALUES (json('" + json.toString() + "'), 1)");
        final String r1 = json.toString();
        json.put("Foo", 2);
        json.put("Bar", "Goo");
        final String r2 = json.toString();
        db.execSQL("INSERT INTO t1 VALUES (json('" + json.toString() + "'), 2)");
        json.put("Foo", 11);
        json.put("Bar", "Zoo");
        db.execSQL("INSERT INTO t1 VALUES (json('" + json.toString() + "'), 11)");

        SQLiteStatement s = db.compileStatement("Select json_extract(x, '$.Foo') from t1 where y = 1");
        res = s.simpleQueryForString();
        db.setTransactionSuccessful();
        db.endTransaction();
        test_result("json_test_1.1", res, "1", t0);

        db.beginTransaction();
        final long t1 = System.nanoTime();
        s.close();

        s = db.compileStatement("Select json_extract(x, '$.Bar') from t1 where y = 1");
        res = s.simpleQueryForString();
        db.setTransactionSuccessful();
        db.endTransaction();
        test_result("json_test_1.2", res, "Gum", t1);
        db.beginTransaction();
        final long t2 = System.nanoTime();
        s.close();

        db.execSQL("Create Unique Index t1_foo on t1(json_extract(x, '$.Foo'))");
        db.execSQL("Create Unique Index t1_bar on t1(json_extract(x, '$.Bar'))");

        s = db.compileStatement("Select x from t1 where json_extract(x, '$.Foo') > 1 order by json_extract(x, '$.Foo') limit 1");
        res = s.simpleQueryForString();
        db.setTransactionSuccessful();
        db.endTransaction();
        test_result("json_test_1.3", res, r2, t2);

        s.close();

        db.close();
    }
}