Simultaneous access JAVA / CordovaJS errors

[thierrybx]

Hello,

I am trying to make an app (Android to begin, and then IOS) that one side (JAVA) executes SQL statements on a database stored on the phone (imported once from the www folder and using SQLiteOpenHelper ..) in java from a background service starting at the boot. No worries on that side.

On the other hand (CordovaJS) I check / update the same database from the same application, but in the GUI (webview) via the cordova Cordova-storage-sqlite plugin. No worries either on that side ...

But when JAVA and CordovaJS try to use the database at the same time, the app crash sometimes...

How can I do to access to the base of both sides simultaneously?
Otherwise what would be the best solution:

• Create two separate databases with the same information that I would need to synchronize regularly between JAVA and CordovaJS in the two ways? Synchronization can possibly lead to the same problem of dual access ...
• Create two bases, and communicate via flat files containing JSON data ...
• Not to use databases and move only by flat files in JSON?
• Any other ideas?

I'd rather largely retain only one database ...
Thank you in advance for review

PS: Sorry for my English, I'm French :(

[brodycj]

It _should_ be possible to get concurrent access working from both Java and Cordova Javascript but it would need adjustments on both sides, and some testing on our part. I will describe what needs to be done sometime next week.

[brodycj]

It should be possible to get concurrent access working from both Java
and Cordova Javascript

I mean concurrent access to the same sqlite database from both Java and
Cordova Javascript should be possible (with the adjustments on both sides),
and the testing needs to be done on your part.

[thierrybx]

Ok, thank you for your quick response!!

I eagerly await your advice!

In my app, the service side (java) write in some tables, and the cordova js in other tables.
Each side reads the data written by the other.

Just for information, in terms of adjustments I've done:
=> Java Side: I close a connection directly after a query on the db. For the moment I do not check if the database is locked before using it, and this must be a mistake?

=> Cordova JS Side:
if(device.platform=="Android"){
db = window.sqlitePlugin.openDatabase({name: "db.db", location: 2, createFromLocation: 1, androidDatabaseImplementation: 2, androidLockWorkaround: 1});
}
else if (device.platform=="iOS") {
db = sqlitePlugin.openDatabase({name: "db.db", location: 2, createFromLocation: 1});
}

See you soon!

[brodycj]

It would help me if I can understand your problem a little better. You write about an app, which is using Cordova, and a background service, which is using Java. Is the app acting as the background service, or are they two different things?

Are you using Java and Javascript from the same process, two different processes, or both?

If I understand your description correctly, you problem has two parts:

1. One or more tables written by Java and read by Javascript
2. One or more tables written by Javascript and read by Java

Am I correct or am I missing something?

How do you plan to make the iOS version?

So far, I can think of the following alternative solutions:

1. Keep part 1 and 2 in separate databases. For each database the reader must open the database in read-only mode and you cannot have multiple writers.
2. Java and Javascript access the same database, using the same database connection opened from the Android-sqlite-connector library
3. Expand the plugin API to allow Java code to post database operation requests and wait for the results
4. Your idea to use flat (JSON) files to communicate, and perhaps separate (JSON) files to move large parts of data, where each flat file has only one writer

Solution 1 will work, but needs an additional sqlitePlugin.openDatabase() option to specify read-only access. I cannot promise when I can add this since I have some backlog and am planning some new API features (which this can be a part of).

Solution 2 needs a Java API enhancement to allow the Java code to lookup the database handle that is opened for your database file. Also, Android-sqlite-native-driver (which is used by the Android-sqlite-connector library) has to be rebuilt with the -DSQLITE_THREADSAFE=2 flag removed or replaced with -DSQLITE_THREADSAFE=1 (or as a workaround, use a PRAGMA statement to enable true multi-threaded access).

I think solution 3 is self-explanatory.

Solution 4 should work (if you do it right). In general, in solution 1, sqlite is acting as a better version of solution 4, assuming we don't encounter locking issues.

If the Java and Javascript are in the same process, solution 3 would be the best solution since it is completely safe from any possible issues with file access and multi-threading.

I am very reluctant to provide either solution 2 or solution 3 for free, primarily due to the possible maintenance issues that can result. Also, solution 2 requires you to use the Android-sqlite-connector API to access the database from Java and will not work if you use the androidDatabaseImplementation: 2 option in window.sqlitePlugin.openDatabase().

[thierrybx]

Hello and thank you for your answer!

My application consists of a backgroung service launched at phone boot, as well as a user interface that is triggered when the user wants, or when it receives notification from the service and click on it on the notification bar.

For now the app works good. But periodically the app crash...

Service side:
The service is a plugin cordova I created. It recovers the original database in assets (www / db.db) if it is not already present in the directory Environment.getExternalStorageDirectory () + "/ db.db";
The connextion to the database is done from a DataBaseHelper java class (extends SQLiteOpenHelper).
The service update periodically the database according to some events using data from the Internet.

GUI side:
The GUI part is fed from the data in the same database.
I use your plugin to query this database with the previously mentioned options.
The GUI also allows you to update certain items in the database that will be used at the service itself.

I don't know if Java and Javascript are running in the same process... Because the service is starting at boot, and the GUI launched by the user...
Do you know how i could get the PID of the process on the cordova side? (Messages with PID console.log javascript side and Log.e Java service side)

You are right with this:

1. One or more tables written by Java and read by Javascript
2. One or more tables written by Javascript and read by Java

The two ways on the same database.

For the IOS version, i plan to do the same. But for IOS the service will not start at boot-time, but when specific system events occurs and launch it.
And the Cordova code will be the same except in the call of your plugin.

For the solution 2 i already have implement the androidDatabaseImplementation: 2 option in window.sqlitePlugin.openDatabase() and the androidLockWorkaround: 1 option.

I can very well understand that implement options 2 and 3 take a long time and you do not want to! no problemo!

For you, there would be no easy way to solve my problem?
If I do not have a choice, I will perhaps use flat files (solution 4)...

If the information I have given you can better direct you, I'd be delighted!
Thierry

[brodycj]

Do you know how i could get the PID of the process on the cordova side?
(Messages with PID console.log javascript side and Log.e Java service side)

UPDATED: For the Android app to get its own pid:

int id = android.os.Process.myPid();

as described in http://stackoverflow.com/questions/25963677/android-determinine-my-apps-process-id and documented at: http://developer.android.com/reference/android/os/Process.html#myPid%28%29

For the Cordova process to get its own PID: this can be done through a new
plugin. I can raise a bug on the Apache Cordova project to implement this
(should not be too difficult).

For the Cordova app to get the pid of the Java process: let the Java
process write its pid to a file that the Cordova process will read.

For you, there would be no easy way to solve my problem?

I suspect it should be possible to get solution 1 to work for you, if I
implement the read-only option (which should not be too difficult). But it
would require some good testing.

If I do not have a choice, I will perhaps use flat files (solution 4)...

Solution 4 might be the easiest, at least for now. Commonly done and might be maybe easier to test.

One thing: I would not rely on the file system (or a database file) for change notifications. But I suspect you have thought of this issue along with a solution already.

[thierrybx]

I've just extend a cordova plugin to get Cordova app PID, and on the other side i've extend the service to get the PID.
Both are logging in logcat using this code:

//Service side
int id = android.os.Process.myPid();
Log.e("PID-Service:",String.valueOf(id));

//Cordova javascript side
int id = android.os.Process.myPid();
Log.e("PID-CordovaAPP:",String.valueOf(id)); 

If service is launched by the app, the PID is the same for Cordova javascript and the JAVA service.
If I reboot the phone, the service starts and have a PID. And when i launch the app GUI, the PID is.... the same :-)

PID-Service:﹕ 7032
PID-CordovaAPP:﹕ 7032

Until you possibly put up the read-only option (Option 1), I'll maybe use the Solution 4.
The solution 4 also allows easier porting the application to IOS?

I'm always taking if you have other ideas or tips! :-)

See you'

PS: I also read about the content-provider to check a database, what do you think?

[brodycj]

PS: I also read about the content-provider to check a database, what do you think?

I just read about Android content provider's and it sounds like a great idea if it can solve your problem. I just found an Android content provider plugin at: https://github.com/phearme/cordova-ContentProviderPlugin

Its plugin.xml specifies Apache 2.0 license, I just asked for an explicit license statement in r-cohen/cordova-ContentProviderPlugin#2.

FYI additional resource: http://www.vogella.com/tutorials/AndroidSQLite/article.html - Android SQLite database and content provider tutorial

[thierrybx]

Thank's :-)

The only problem is for the IOS version with the "content-provider" mode. I'm wrong?

If it will be the best choise for me, i will find an other way for the ios version ;-)

[brodycj]

Unfortunately the content provider solution is Android-specific. So is the background service, if I am not mistaken. Do you want to describe, in very general terms, what you are using the background for and how you are thinking to achieve something similar for iOS?

FWIW, I also found https://github.com/Red-Folder/Cordova-Plugin-BackgroundService which is now split into two parts:

• https://github.com/Red-Folder/bgs-core
• https://github.com/Red-Folder/bgs-sample

[thierrybx]

Sorry for the response time!
Thank's again for your response! But I have no problems with the background service, and i will do it without a specific plugin.

For background services I use a class that I created that extends the class service.
This class uses a LocationManager that runs at regular intervals and makes web requests according to certain criteria.

From what I read, it is also possible to have a back-up on IOS services, but for a limited duration (minutes), and can not start at boot the phone. The way I've found to achieve what interests me is to start my service in IOS when GPS coordinates change (IOS allows it apparently).

One of my source (obviously reliable ^^):
https://developer.apple.com/library/ios/documentation/iPhone/Conceptual/iPhoneOSProgrammingGuide/BackgroundExecution/BackgroundExecution.html
==> Background mode to use: "Location updates"

[thierrybx]

I have thie error when my app is freezing:

07-31 14:58:11.268    2872-2872/com.myapp E/SQLiteDatabase﹕ Failed to open database '/data/data/com.myapp/databases/db.db'.
    android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1093)
            at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:804)
            at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:490)
            at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:464)
            at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:363)
            at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:228)
            at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:512)
            at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:206)
            at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:178)
            at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:891)
            at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:861)
            at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:696)
            at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:1450)
            at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:282)
            at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:223)
            at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
at com.myapp.BaseSQLite.open(BaseSQLite.java:37)

It occurs typically when i call the getWritableDatabase() on my SQLiteOpenHelper db.

Perhaps an answer here

What do you think of this? But with this method i have to access to the database only using DatabaseManager from javascrip (plugin) and Service side, right?

[brodycj]

What do you think of this? But with this method i have to access to the
database only using DatabaseManager from javascrip (plugin) and Service
side, right?

That sounds right, though I really have to investigate it to be sure. I may
need a few days before I can confirm.

Sent from my mobile

[thierrybx]

Thank's!

I think I will test this solution also after testing an intermediate solution: have a database on the background service side, and flat files generated for the javascriptside (by the service or the javascript side).

Have a good weekend!

[thierrybx]

Finaly i set up a bypass to this problem. I just added a try catch around the getWritableDatabase (). The whole being in a loop with a maximum number of executions set to 10, and a sleep set at 0.5 seconds. If getWritableDatabase () works, it exits the loop, and if no, it retries up to 10 times.

I know it is not nice at all, but I put this "patch" to continue to develop the app without incessant bugs ...
The retry count does not exceed 1 to 2, 3 in the worst case in my tests...

I promise I'll do something cleaner later (with your help? :-) )!

PS: is it possible to call your classes (to open DB, copy it from assets to the cache, insert/update/select) directly from my Service with a java code? It seems that your plugin (side javascript) crash unless the java service side!

[brodycj]

Do you put getWritableDatabase() in the loop only the first time you open the database, or do you have to use getWritableDatabase() for every transaction?

If you only have to use getWritableDatabase() in the beginning, it should not give you any performance issues and we can fix this at our leisure.

If you have to do this for every transaction, it will probably hurt your performance and we should look for a better solution. Note that if you use the androidLockWorkaround: 1 it will close and re-open the database after every transaction which will also give you a performance penalty.

For the future, I am thinking to change the Java structure to make it easier for the user to plug in his/her own database implementation class.

[thierrybx]

In fact, I use getWritableDatabase () for each transaction on the java side to close as soon as possible the base so that other transaction (from your cordova plugin for example) can take place without collision and be sure that the base is writable and not locked for a long time.

Maybe in my case, if I want to use only one database, I would have to use it with the same classes on both sides (Cordova / JS & Java).

Maybe this solution (mentioned two days ago) turned into specific plugin cordova for my needs with classes directly accessible in java might be the best solution? Whith this i do not have to call multiple getWritableDatabase (), and use only one connexion for both sides (from what I understand).

[brodycj]

Hi @thierrybx I may need a few weeks to implement a better solution for you. In addition, I am thinking to implement this in an enterprise version that will be available under GPL or purchased commercial licensing terms which I would offer to you for a discount (or maybe for free if you help me test it). Thanks for your patience so far.

[thierrybx]

Excellent!
If you want I can help you to test and validate this new version with test cases on android and IOS platforms!
See you very soon!
Thierry

[thierrybx]

Hi! After hours of coding, I managed to ensure that everything works from both sides without conflict! By cons I no longer passes through your plugin but by a specific plugin to my needs that plugs directly into Java classes that I created to check and update the database.

No bugs since a long time!
If you see errors or things that shock you do not hesitate to tell me!
You can reuse the code as you like;-)

Here are some excerpts of my code
-database manager

...

public class DatabaseManager {

    private int mOpenCounter;

    private static DatabaseManager instance;
    private static DataBaseHelper mDatabaseHelper;
    private SQLiteDatabase mDatabase;

    public static synchronized void initializeInstance(DataBaseHelper helper) {
        if (instance == null) {
            Log.e("InstancesSQLiteDatabase: Initialize","create DatabaseManager");
            instance = new DatabaseManager();
            mDatabaseHelper = helper;
        }
        else
        {
            Log.e("InstancesSQLiteDatabase: Initialize","DatabaseManager already in the place");
        }
    }

    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                    " is not initialized, call initializeInstance(..) method first.");
        }
        return instance;
    }

    public synchronized SQLiteDatabase openDatabase() {
        mOpenCounter++;
        Log.e("InstancesSQLiteDatabase: add",String.valueOf(mOpenCounter));
        if(mOpenCounter == 1) {
            // Opening new database
            mDatabase = mDatabaseHelper.getWritableDatabase();
        }
        return mDatabase;
    }

    public synchronized void closeDatabase() {
        mOpenCounter--;
        Log.e("InstancesSQLiteDatabase: delete",String.valueOf(mOpenCounter));
        if(mOpenCounter == 0) {
            // Closing database
            mDatabase.close();

        }
    }
}

Database Helper:

...

public class DataBaseHelper extends SQLiteOpenHelper {

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    private Context mycontext;

    private String DB_PATH="";
    private static String DB_NAME = "db.db";//the extension may be .sqlite or .db

    public DataBaseHelper(Context context) throws IOException {
        super(context,DB_NAME,null,1);
        this.mycontext=context;
        this.DB_PATH= context.getApplicationInfo().dataDir+"/databases/";
        boolean dbexist = checkdatabase();
        if (dbexist) {
            System.out.println("Database exists");
        } else {
            System.out.println("Database doesn't exist");
            createdatabase();
        }
    }

    public void createdatabase() throws IOException {
        boolean dbexist = checkdatabase();
        if(dbexist) {
            System.out.println(" Database exists.");
        } else {
            this.getReadableDatabase();
            try {
                copydatabase();
            } catch(IOException e) {
                Log.e("com.app", "The error" + e);
                throw new Error("Error copying database");
            }
        }
    }

    public boolean dbExists()
    {
        return checkdatabase();
    }

    private boolean checkdatabase() {
        boolean checkdb = false;
        try {
            String myPath = DB_PATH + DB_NAME;
            File dbfile = new File(myPath);
            checkdb = dbfile.exists();
        } catch(SQLiteException e) {
            System.out.println("Database doesn't exist");
        }
        return checkdb;
    }

    private void copydatabase() throws IOException {
        //Open your local db as the input stream
        InputStream myinput = mycontext.getAssets().open("www/"+DB_NAME);

        // Path to the just created empty db
        String outfilename = DB_PATH + DB_NAME;

        //Open the empty db as the output stream
        OutputStream myoutput = new FileOutputStream(outfilename);

        // transfer byte to inputfile to outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myinput.read(buffer))>0) {
            myoutput.write(buffer,0,length);
        }

        //Close the streams
        myoutput.flush();
        myoutput.close();
        myinput.close();
    }
}

And the class to execute stuff:

public class BaseSQLite {

    private SQLiteDatabase bdd;
    private String DB_PATH="";


    private static String DB_NAME = "db.db";//the extension may be .sqlite or .db

    public BaseSQLite(SQLiteDatabase uneBase, Context context){
        this.bdd=uneBase;
        this.DB_PATH= context.getApplicationInfo().dataDir+"/databases/";
        Log.e("BaseSQLite","Constructor");
    }

    ...    

    public boolean superStuffInBase()
    {
        Cursor c = bdd.rawQuery("select * from mySuperTableOfTheDeath;", null);

        try{
            JSONArray tab = new JSONArray();
            for(int i=0; i< c.getCount();i++)
            {
                JSONObject element = new JSONObject();
                if(i==0)
                    c.moveToFirst();
                else
                    c.moveToNext();
                //Log.e("BaseSQLite","NB Lines: "+c.getCount());
            element.put("vegetable",String.valueOf(c.getFloat(c.getColumnIndex("vegetable"))));

                tab.put(element);
            }
            c.close();
            return tab;
        }
        catch (JSONException e){
            e.printStackTrace();
        }

        return null;
    }
}

An finaly a class with static calls from cordova.exec OR JAVA service for exemple:

...
public class Utils {
   public static JSONArray getSuperStuff(Context context)
       {
           BaseSQLite theBase = new BaseSQLite(DatabaseManager.getInstance().openDatabase(),context);
           JSONArray tab=theBase.superStuffInBase();
           DatabaseManager.getInstance().closeDatabase();
           return leTabPromo;
       }
}

The call in cordova side ( cordova.exec(onsuccess,onerror, "Launcher", "getSuperStuff", []); ) :

...
public class Launcher extends CordovaPlugin {
    @Override
    public boolean execute(String action, JSONArray args, CallbackContext callbackContext) throws JSONException {
        try{
            Context context=this.cordova.getActivity().getApplicationContext();
            if(action.equals("getSuperStuff")){
                JSONArray res = Utils.getSuperStuff(context);
                callbackContext.success(res.toString());
                Log.e("getSuperStuff",res.toString());
                return true;
            }
            callbackContext.error("Invalid action");
            return false;
        } catch(Exception e) {
            System.err.println("Exception: " + e.getMessage());
            callbackContext.error("Launcher error:"+e.getMessage());
            return false;
        }
    }
}

[brodycj]

Thanks for sharing! You have now found a simpler solution to fulfill the needs of your problem and I think you have learned a lot from working on it.

Sometimes it makes sense to build the custom components exactly as you need them for your application, and sometimes it is the things that you don't share that give you the competitive edge, at least for a period.

This project aims to be very general purpose with a standardized transaction API, which unfortunately has led to its level of complexity. I am planning to make a simpler API to help reduce the complexity when I get a chance.

It would be cool to find a way to provide your solution in a more general purpose manner, and I can think of several alternatives. Of course, it would be possible to enhance the Android version of this project to support the Database Manager & Database Helper functionality, and I may do something like this for a "pro" version. But it only helps developers on the Android platform and further increases the complexity.

A second alternative would be to build an Android-specific plugin that provides the database manager/helper functionality.

Or a plugin such as @phearme/cordova-ContentProviderPlugin that does a query on a custom content provider.

I am closing this for now, may revisit this later.

[thierrybx]

Thanks for all! Discuss with you allowed me to mature my reflection.
See you!

PS: I will upgrade my plugin to do the same on the iphone platform, with dual access!
In your opinion, since I started programming on iphone, it is better for me tu use objective-c or swift?

[brodycj]

Hi @thierrybx, I did not see your PS question until now. Did you make the solution for iPhone?

If so, do you want to share it?

[thierrybx]

Hi @brodybits ! For the iPhone platform i finally use only json files. Easyer...