Index: sqlite3test/src/main/java/org/sqlite/customsqlitetest/MainActivity.java ================================================================== --- sqlite3test/src/main/java/org/sqlite/customsqlitetest/MainActivity.java +++ sqlite3test/src/main/java/org/sqlite/customsqlitetest/MainActivity.java @@ -6,10 +6,11 @@ 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; @@ -49,22 +50,24 @@ 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){ + 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\n"); + myTV.append("ok (" + tot + "ms)\n"); } else { myNErr++; myTV.append("FAILED\n"); myTV.append(" res= \"" + res + "\"\n"); myTV.append(" expected=\"" + expected + "\"\n"); @@ -104,27 +107,30 @@ 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"); + 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)"); @@ -150,20 +156,22 @@ if( t.isAlive() ){ res = "blocked"; } db.endTransaction(); try { t.join(); } catch(InterruptedException e) {} if( SQLiteDatabase.hasCodec() ){ - test_result("thread_test_2", res, "blocked"); + test_result("thread_test_2", res, "blocked", t0); } else { - test_result("thread_test_2", res, "concurrent"); + 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; @@ -184,12 +192,13 @@ res = res + "." + x; } }else{ test_warning("csr_test_1", "c==NULL"); } - test_result("csr_test_2.1", res, expect); + 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)"); @@ -202,11 +211,11 @@ 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"); + test_result("csr_test_2.2", "" + nRow, "15000", t1); db.close(); } public String string_from_t1_x(SQLiteDatabase db){ @@ -220,25 +229,28 @@ } 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"); + 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"); + 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)"); @@ -245,15 +257,16 @@ 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"); + 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) ); @@ -260,19 +273,20 @@ 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"); + 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 = ""; @@ -281,19 +295,22 @@ 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"); + 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"); + 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"); + 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); @@ -301,11 +318,12 @@ } catch ( SQLiteDatabaseCorruptException e ){ res = "encrypted"; } finally { db.close(); } - test_result("see_test_1.4", res, "encrypted"); + 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'"); @@ -313,11 +331,11 @@ } catch ( SQLiteDatabaseCorruptException e ){ res = "encrypted"; } finally { db.close(); } - test_result("see_test_1.5", res, "encrypted"); + test_result("see_test_1.5", res, "encrypted", t4); } class MyHelper extends SQLiteOpenHelper { public MyHelper(Context ctx){ super(ctx, DB_PATH.getPath(), null, 1); } @@ -333,52 +351,77 @@ /* ** 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"); + 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"); - test_result("see_test_2.2", db_is_encrypted(), "encrypted"); + 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"); + 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"); + 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"); + 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){ - System.loadLibrary("sqliteX"); + 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.getParentFile().mkdirs(); + DB_PATH.mkdirs(); myTV.setText(""); myNErr = 0; myNTest = 0; @@ -391,13 +434,68 @@ 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(); + } }