SQLite Android Bindings
Check-in [7275688aed]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Upgrade this branch to use the enhanced test program from the api-level-9 branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7275688aed9541c3737a1386cfa4ddb9c15ca705
User & Date: dan 2017-04-27 18:00:41
Context
2017-04-29
09:24
Update the installation instructions to reflect the new support for API level 9. And that building the native libraries is now integrated with gradle. check-in: 985ad0e6f2 user: dan tags: trunk
2017-04-27
18:00
Upgrade this branch to use the enhanced test program from the api-level-9 branch. check-in: 7275688aed user: dan tags: trunk
17:50
Upgrade this branch to use the build.gradle from the api-level-9 branch. To support building both the libraries and java classes in a single step (or from within Android Studio). check-in: 3a63f88d36 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to sqlite3test/src/main/java/org/sqlite/customsqlitetest/MainActivity.java.

     4      4   import android.database.Cursor;
     5      5   import android.support.v7.app.AppCompatActivity;
     6      6   import android.os.Bundle;
     7      7   import android.util.Log;
     8      8   import android.view.View;
     9      9   import android.widget.TextView;
    10     10   
           11  +import org.json.JSONObject;
    11     12   import org.sqlite.database.DatabaseErrorHandler;
    12     13   import org.sqlite.database.sqlite.SQLiteDatabase;
    13     14   import org.sqlite.database.sqlite.SQLiteStatement;
    14     15   import org.sqlite.database.sqlite.SQLiteDatabaseCorruptException;
    15     16   import org.sqlite.database.sqlite.SQLiteOpenHelper;
    16     17   
    17     18   import java.io.File;
................................................................................
    47     48           String res;
    48     49   
    49     50           db = SQLiteDatabase.openOrCreateDatabase(":memory:", null);
    50     51           st = db.compileStatement("SELECT sqlite_version()");
    51     52           res = st.simpleQueryForString();
    52     53   
    53     54           myTV.append("SQLite version " + res + "\n\n");
           55  +        db.close();
    54     56       }
    55     57   
    56     58       public void test_warning(String name, String warning){
    57     59           myTV.append("WARNING:" + name + ": " + warning + "\n");
    58     60       }
    59     61   
    60         -    public void test_result(String name, String res, String expected){
           62  +    public void test_result(String name, String res, String expected, long t0){
           63  +        long tot = (System.nanoTime() - t0) / 1000000;
    61     64           myTV.append(name + "... ");
    62     65           myNTest++;
    63     66   
    64     67           if( res.equals(expected) ){
    65         -            myTV.append("ok\n");
           68  +            myTV.append("ok (" + tot + "ms)\n");
    66     69           } else {
    67     70               myNErr++;
    68     71               myTV.append("FAILED\n");
    69     72               myTV.append("   res=     \"" + res + "\"\n");
    70     73               myTV.append("   expected=\"" + expected + "\"\n");
    71     74           }
    72     75       }
................................................................................
   102    105           String db_path2 = DB_PATH.toString() + "2";
   103    106   
   104    107           db.execSQL("CREATE TABLE t1(x, y)");
   105    108           db.execSQL("INSERT INTO t1 VALUES (1, 2), (3, 4)");
   106    109   
   107    110           Thread t = new Thread( new Runnable() {
   108    111               public void run() {
          112  +                final long t0 = System.nanoTime();
   109    113                   SQLiteStatement st = db.compileStatement("SELECT sum(x+y) FROM t1");
   110    114                   String res = st.simpleQueryForString();
   111         -                test_result("thread_test_1", res, "10");
          115  +                test_result("thread_test_1", res, "10", t0);
   112    116               }
   113    117           });
   114    118   
   115    119           t.start();
   116    120           try {
   117    121               t.join();
   118    122           } catch (InterruptedException e) {
   119    123           }
          124  +        db.close();
   120    125       }
   121    126   
   122    127       /*
   123    128       ** Test that a database connection may be accessed from a second thread.
   124    129       */
   125    130       public void thread_test_2(){
          131  +        final long t0 = System.nanoTime();
   126    132           SQLiteDatabase.deleteDatabase(DB_PATH);
   127    133           final SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
   128    134   
   129    135           db.execSQL("CREATE TABLE t1(x, y)");
   130    136           db.execSQL("INSERT INTO t1 VALUES (1, 2), (3, 4)");
   131    137   
   132    138           db.enableWriteAheadLogging();
................................................................................
   148    154               try { Thread.sleep(100); } catch(InterruptedException e) {}
   149    155           }
   150    156           if( t.isAlive() ){ res = "blocked"; }
   151    157   
   152    158           db.endTransaction();
   153    159           try { t.join(); } catch(InterruptedException e) {}
   154    160           if( SQLiteDatabase.hasCodec() ){
   155         -            test_result("thread_test_2", res, "blocked");
          161  +            test_result("thread_test_2", res, "blocked", t0);
   156    162           } else {
   157         -            test_result("thread_test_2", res, "concurrent");
          163  +            test_result("thread_test_2", res, "concurrent", t0);
   158    164           }
          165  +        db.close();
   159    166       }
   160    167   
   161    168       /*
   162    169       ** Use a Cursor to loop through the results of a SELECT query.
   163    170       */
   164    171       public void csr_test_2() throws Exception {
          172  +        final long t0 = System.nanoTime();
   165    173           SQLiteDatabase.deleteDatabase(DB_PATH);
   166    174           SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
   167    175           String res = "";
   168    176           String expect = "";
   169    177           int i;
   170    178           int nRow = 0;
   171    179   
................................................................................
   182    190               for(bRes=c.moveToFirst(); bRes; bRes=c.moveToNext()){
   183    191                   String x = c.getString(0);
   184    192                   res = res + "." + x;
   185    193               }
   186    194           }else{
   187    195               test_warning("csr_test_1", "c==NULL");
   188    196           }
   189         -        test_result("csr_test_2.1", res, expect);
          197  +        test_result("csr_test_2.1", res, expect, t0);
   190    198   
          199  +        final long t1 = System.nanoTime();
   191    200           db.execSQL("BEGIN");
   192    201           for(i=0; i<1000; i++){
   193    202               db.execSQL("INSERT INTO t1 VALUES (X'123456'), (X'789ABC'), (X'DEF012')");
   194    203               db.execSQL("INSERT INTO t1 VALUES (45), (46), (47)");
   195    204               db.execSQL("INSERT INTO t1 VALUES (8.1), (8.2), (8.3)");
   196    205               db.execSQL("INSERT INTO t1 VALUES (NULL), (NULL), (NULL)");
   197    206           }
................................................................................
   200    209           c = db.rawQuery("SELECT x FROM t1", null);
   201    210           if( c!=null ){
   202    211               boolean bRes;
   203    212               for(bRes=c.moveToFirst(); bRes; bRes=c.moveToNext()) nRow++;
   204    213           }else{
   205    214               test_warning("csr_test_1", "c==NULL");
   206    215           }
   207         -        test_result("csr_test_2.2", "" + nRow, "15000");
          216  +        test_result("csr_test_2.2", "" + nRow, "15000", t1);
   208    217   
   209    218           db.close();
   210    219       }
   211    220   
   212    221       public String string_from_t1_x(SQLiteDatabase db){
   213    222           String res = "";
   214    223   
................................................................................
   218    227               String x = c.getString(0);
   219    228               res = res + "." + x;
   220    229           }
   221    230   
   222    231           return res;
   223    232       }
   224    233       public void csr_test_1() throws Exception {
          234  +        final long t0 = System.nanoTime();
   225    235           SQLiteDatabase.deleteDatabase(DB_PATH);
   226    236           SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
   227    237           String res = "";
   228    238   
   229    239           db.execSQL("CREATE TABLE t1(x)");
   230    240           db.execSQL("INSERT INTO t1 VALUES ('one'), ('two'), ('three')");
   231    241   
   232    242           res = string_from_t1_x(db);
   233         -        test_result("csr_test_1.1", res, ".one.two.three");
          243  +        test_result("csr_test_1.1", res, ".one.two.three", t0);
          244  +        final long t1 = System.nanoTime();
   234    245   
   235    246           db.close();
   236         -        test_result("csr_test_1.2", db_is_encrypted(), "unencrypted");
          247  +        test_result("csr_test_1.2", db_is_encrypted(), "unencrypted", t1);
   237    248       }
   238    249   
   239    250       public void stmt_jrnl_test_1() throws Exception {
          251  +        final long t0 = System.nanoTime();
   240    252           SQLiteDatabase.deleteDatabase(DB_PATH);
   241    253           SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
   242    254           String res = "";
   243    255   
   244    256           db.execSQL("CREATE TABLE t1(x, y UNIQUE)");
   245    257           db.execSQL("BEGIN");
   246    258           db.execSQL("INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3)");
   247    259           db.execSQL("UPDATE t1 SET y=y+3");
   248    260           db.execSQL("COMMIT");
   249    261           db.close();
   250         -        test_result("stmt_jrnl_test_1.1", "did not crash", "did not crash");
          262  +        test_result("stmt_jrnl_test_1.1", "did not crash", "did not crash", t0);
   251    263       }
   252    264   
   253    265   
   254    266       public void supp_char_test_1() throws Exception {
          267  +        final long t0 = System.nanoTime();
   255    268           SQLiteDatabase.deleteDatabase(DB_PATH);
   256    269           SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
   257    270           String res = "";
   258    271           String smiley = new String( Character.toChars(0x10000) );
   259    272   
   260    273           db.execSQL("CREATE TABLE t1(x)");
   261    274           db.execSQL("INSERT INTO t1 VALUES ('a" + smiley + "b')");
   262    275   
   263    276           res = string_from_t1_x(db);
   264    277   
   265         -        test_result("supp_char_test1." + smiley, res, ".a" + smiley + "b");
          278  +        test_result("supp_char_test1." + smiley, res, ".a" + smiley + "b", t0);
   266    279   
   267    280           db.close();
   268    281       }
   269    282   
   270    283       /*
   271    284       ** If this is a SEE build, check that encrypted databases work.
   272    285       */
   273    286       public void see_test_1() throws Exception {
          287  +        final long t0 = System.nanoTime();
   274    288           if( !SQLiteDatabase.hasCodec() ) return;
   275    289   
   276    290           SQLiteDatabase.deleteDatabase(DB_PATH);
   277    291           String res = "";
   278    292   
   279    293           SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
   280    294           db.execSQL("PRAGMA key = 'secretkey'");
   281    295   
   282    296           db.execSQL("CREATE TABLE t1(x)");
   283    297           db.execSQL("INSERT INTO t1 VALUES ('one'), ('two'), ('three')");
   284    298   
   285    299           res = string_from_t1_x(db);
   286         -        test_result("see_test_1.1", res, ".one.two.three");
          300  +        test_result("see_test_1.1", res, ".one.two.three", t0);
          301  +        final long t1 = System.nanoTime();
   287    302           db.close();
   288    303   
   289         -        test_result("see_test_1.2", db_is_encrypted(), "encrypted");
          304  +        test_result("see_test_1.2", db_is_encrypted(), "encrypted", t1);
          305  +        final long t2 = System.nanoTime();
   290    306   
   291    307           db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
   292    308           db.execSQL("PRAGMA key = 'secretkey'");
   293    309           res = string_from_t1_x(db);
   294         -        test_result("see_test_1.3", res, ".one.two.three");
          310  +        test_result("see_test_1.3", res, ".one.two.three", t2);
          311  +        final long t3 = System.nanoTime();
   295    312           db.close();
   296    313   
   297    314           res = "unencrypted";
   298    315           try {
   299    316               db = SQLiteDatabase.openOrCreateDatabase(DB_PATH.getPath(), null);
   300    317               string_from_t1_x(db);
   301    318           } catch ( SQLiteDatabaseCorruptException e ){
   302    319               res = "encrypted";
   303    320           } finally {
   304    321               db.close();
   305    322           }
   306         -        test_result("see_test_1.4", res, "encrypted");
          323  +        test_result("see_test_1.4", res, "encrypted", t3);
          324  +        final long t4 = System.nanoTime();
   307    325   
   308    326           res = "unencrypted";
   309    327           try {
   310    328               db = SQLiteDatabase.openOrCreateDatabase(DB_PATH.getPath(), null);
   311    329               db.execSQL("PRAGMA key = 'otherkey'");
   312    330               string_from_t1_x(db);
   313    331           } catch ( SQLiteDatabaseCorruptException e ){
   314    332               res = "encrypted";
   315    333           } finally {
   316    334               db.close();
   317    335           }
   318         -        test_result("see_test_1.5", res, "encrypted");
          336  +        test_result("see_test_1.5", res, "encrypted", t4);
   319    337       }
   320    338       class MyHelper extends SQLiteOpenHelper {
   321    339           public MyHelper(Context ctx){
   322    340               super(ctx, DB_PATH.getPath(), null, 1);
   323    341           }
   324    342           public void onConfigure(SQLiteDatabase db){
   325    343               db.execSQL("PRAGMA key = 'secret'");
................................................................................
   331    349           }
   332    350       }
   333    351   
   334    352       /*
   335    353       ** Check that SQLiteOpenHelper works.
   336    354       */
   337    355       public void helper_test_1() throws Exception {
          356  +        final long t0 = System.nanoTime();
   338    357           SQLiteDatabase.deleteDatabase(DB_PATH);
   339    358   
   340    359           MyHelper helper = new MyHelper(this);
   341    360           SQLiteDatabase db = helper.getWritableDatabase();
   342    361           db.execSQL("INSERT INTO t1 VALUES ('x'), ('y'), ('z')");
   343    362   
   344    363           String res = string_from_t1_x(db);
   345         -        test_result("helper.1", res, ".x.y.z");
          364  +        test_result("helper.1", res, ".x.y.z", t0);
   346    365   
   347    366           helper.close();
   348    367       }
   349    368   
   350    369       /*
   351    370       ** If this is a SEE build, check that SQLiteOpenHelper still works.
   352    371       */
   353    372       public void see_test_2() throws Exception {
          373  +        final long t0 = System.nanoTime();
   354    374           if( !SQLiteDatabase.hasCodec() ) return;
   355    375           SQLiteDatabase.deleteDatabase(DB_PATH);
   356    376   
   357    377           MyHelper helper = new MyHelper(this);
   358    378           SQLiteDatabase db = helper.getWritableDatabase();
   359    379           db.execSQL("INSERT INTO t1 VALUES ('x'), ('y'), ('z')");
   360    380   
   361    381           String res = string_from_t1_x(db);
   362         -        test_result("see_test_2.1", res, ".x.y.z");
   363         -        test_result("see_test_2.2", db_is_encrypted(), "encrypted");
          382  +        test_result("see_test_2.1", res, ".x.y.z", t0);
          383  +        final long t1 = System.nanoTime();
          384  +        test_result("see_test_2.2", db_is_encrypted(), "encrypted", t1);
          385  +        final long t2 = System.nanoTime();
   364    386   
   365    387           helper.close();
   366    388           helper = new MyHelper(this);
   367    389           db = helper.getReadableDatabase();
   368         -        test_result("see_test_2.3", res, ".x.y.z");
          390  +        test_result("see_test_2.3", res, ".x.y.z", t2);
          391  +        final long t3 = System.nanoTime();
   369    392   
   370    393           db = helper.getWritableDatabase();
   371         -        test_result("see_test_2.4", res, ".x.y.z");
          394  +        test_result("see_test_2.4", res, ".x.y.z", t3);
          395  +        final long t4 = System.nanoTime();
   372    396   
   373         -        test_result("see_test_2.5", db_is_encrypted(), "encrypted");
          397  +        test_result("see_test_2.5", db_is_encrypted(), "encrypted", t4);
          398  +        db.close();
          399  +    }
          400  +
          401  +    private static boolean mLibIsLoaded = false;
          402  +    private static void loadLibrary() {
          403  +        if (!mLibIsLoaded) {
          404  +            System.loadLibrary("sqliteX");
          405  +            mLibIsLoaded = true;
          406  +        }
   374    407       }
   375    408   
   376    409       public void run_the_tests(View view){
   377         -        System.loadLibrary("sqliteX");
          410  +        myTV.setText("");
          411  +        view.post(new Runnable() {
          412  +            @Override
          413  +            public void run() {
          414  +                run_the_tests_really();
          415  +            }
          416  +        });
          417  +    }
          418  +
          419  +    public void run_the_tests_really(){
          420  +        loadLibrary();
   378    421           DB_PATH = getApplicationContext().getDatabasePath("test.db");
   379         -        DB_PATH.getParentFile().mkdirs();
          422  +        DB_PATH.mkdirs();
   380    423   
   381    424           myTV.setText("");
   382    425           myNErr = 0;
   383    426           myNTest = 0;
   384    427   
   385    428           try {
   386    429               report_version();
................................................................................
   389    432               csr_test_1();
   390    433               csr_test_2();
   391    434               thread_test_1();
   392    435               thread_test_2();
   393    436               see_test_1();
   394    437               see_test_2();
   395    438               stmt_jrnl_test_1();
          439  +            json_test_1();
   396    440   
   397    441               myTV.append("\n" + myNErr + " errors from " + myNTest + " tests\n");
   398    442           } catch(Exception e) {
   399    443               myTV.append("Exception: " + e.toString() + "\n");
   400    444               myTV.append(android.util.Log.getStackTraceString(e) + "\n");
   401    445           }
   402    446       }
          447  +
          448  +    public void json_test_1() throws Exception {
          449  +        SQLiteDatabase.deleteDatabase(DB_PATH);
          450  +        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH, null);
          451  +        final long t0 = System.nanoTime();
          452  +        db.beginTransaction();
          453  +        String res = "";
          454  +
          455  +        db.execSQL("CREATE TABLE t1(x, y)");
          456  +        JSONObject json = new JSONObject();
          457  +        json.put("Foo", 1);
          458  +        json.put("Bar", "Gum");
          459  +        db.execSQL("INSERT INTO t1 VALUES (json('" + json.toString() + "'), 1)");
          460  +        final String r1 = json.toString();
          461  +        json.put("Foo", 2);
          462  +        json.put("Bar", "Goo");
          463  +        final String r2 = json.toString();
          464  +        db.execSQL("INSERT INTO t1 VALUES (json('" + json.toString() + "'), 2)");
          465  +        json.put("Foo", 11);
          466  +        json.put("Bar", "Zoo");
          467  +        db.execSQL("INSERT INTO t1 VALUES (json('" + json.toString() + "'), 11)");
          468  +
          469  +        SQLiteStatement s = db.compileStatement("Select json_extract(x, '$.Foo') from t1 where y = 1");
          470  +        res = s.simpleQueryForString();
          471  +        db.setTransactionSuccessful();
          472  +        db.endTransaction();
          473  +        test_result("json_test_1.1", res, "1", t0);
          474  +
          475  +        db.beginTransaction();
          476  +        final long t1 = System.nanoTime();
          477  +        s.close();
          478  +
          479  +        s = db.compileStatement("Select json_extract(x, '$.Bar') from t1 where y = 1");
          480  +        res = s.simpleQueryForString();
          481  +        db.setTransactionSuccessful();
          482  +        db.endTransaction();
          483  +        test_result("json_test_1.2", res, "Gum", t1);
          484  +        db.beginTransaction();
          485  +        final long t2 = System.nanoTime();
          486  +        s.close();
          487  +
          488  +        db.execSQL("Create Unique Index t1_foo on t1(json_extract(x, '$.Foo'))");
          489  +        db.execSQL("Create Unique Index t1_bar on t1(json_extract(x, '$.Bar'))");
          490  +
          491  +        s = db.compileStatement("Select x from t1 where json_extract(x, '$.Foo') > 1 order by json_extract(x, '$.Foo') limit 1");
          492  +        res = s.simpleQueryForString();
          493  +        db.setTransactionSuccessful();
          494  +        db.endTransaction();
          495  +        test_result("json_test_1.3", res, r2, t2);
          496  +
          497  +        s.close();
          498  +
          499  +        db.close();
          500  +    }
   403    501   }