Offline strategy

1

I am starting an Appstudio project for a client to use Android for data collection. The crews work contracts for large power utility tasked with trimming trees. Some work locations are very remote with no internet so I need to build an offline capability into the app.
I built a management backend that is created in Asp.net Core, and has an API for getting and posting Json data.
I need the crew to be able to work offline and the add or update records via Rest Api when online.

Should I consider using SQLite or json files on Android so they persist data?

I am interested in anyone’s experience with this.

2

First, a question: Do you expect this will be a native app using PhoneGap?

3

Second, will you be capturing GPS?

4

Yes, native with Phonegap

5

I would use SQlite to save data, unless your data is very simple. Being able to access the local data using SQL statements means you can do much more sophisticated reporting on the device.

6

Ok. I will be receiving static lookups and some data collection tables that I will read and write then send changes back to the server via rest api. Does SQLite have a data type of UniqueIdentifier or do I need to roll my own?

7

Possibly gps in the future

8

Yes, SQLite has AUTOINCREMENT:
https://sqlite.org/autoinc.html

9

What about a GUID to prevent PK collisions? I may have to create a function.

10

Here’s the function I use:

    guidGenerator() {
      /* eslint no-bitwise: ["error", { "allow": ["|"] }] */
      const S4 = (() => (((1 + Math.random()) * 0x10000) | 0).toString(16).substring(1));
      return `${S4()}${S4()}-${S4()}-${S4()}-${S4()}-${S4()}${S4()}${S4()}`;
    },
11

You might want to wrap that as an NSB function?

12

Just remember when you’re designing this that when there’s no “bars” there’s no GPS.

Also, when you’re calling sqllite from JavaScript, all your calls are async so you’ll need to provide callback functions.

After that, most of the core SQL syntax is available including UNIQUE index’s… it’s just done a bit differently.

// item_id, channel, type, title, text, when, contact
db.transaction(function(tx) {
  tx.executeSql("CREATE TABLE IF NOT EXISTS my_table (item_id UNSIGNED BIG INT PRIMARY KEY ASC, channel INTEGER, type INTEGER, item_when TEXT, item_read INTEGER, title TEXT, item_text TEXT, contact TEXT)", 
  		      [], serNullHandler, serOnError);
  tx.executeSql("CREATE UNIQUE INDEX IF NOT EXISTS unique_id ON my_table (item_id)", 
            [],
            serOnSuccess,
            serOnError);
});
13

It might be best to leave it in JavaScript. Here’s how you would use it in BASIC code:

JavaScript
function guidGenerator() {
  const S4 = (() => (((1 + Math.random()) * 0x10000) | 0).toString(16).substring(1));
  return `${S4()}${S4()}-${S4()}-${S4()}-${S4()}-${S4()}${S4()}${S4()}`;
}
End JavaScript

Let guid = guidGenerator()
14

I haven’t used them yet, but there are a couple of Javascript libraries that support synchronous calls to sqllite.

  • better-sqlite3
  • sqlite-sync