Skip navigation
All Places > AppStudio for ArcGIS > Blog > 2018 > August
2018
In order to improve the security and performance of Android apps, Google continuously updates its policies for apps published on the Google Play store. With the recent changes announced in this blog post https://android-developers.googleblog.com/2017/12/improving-app-security-and-performance.html, Google has laid down a road map for the target API level requirement for Android apps.
 
Target API level requirements for Android Apps
  • From August 2018, Play store requires new apps to target Android API level 26 (Android 8.0) or higher in order to be published.
  • From November 2018, all apps, including updates to existing apps, need to target Android API level 26 (Android 8.0) or higher to be published in the Play store.
 
 
How does this impact AppStudio users? 
If you have recently tried to submit your apps to Google Play store, you might have encountered the following warning message:
 
In order to get Android apps built using AppStudio to meet the requirements of the Google Play store, we have updated our cloud Make servers to target Android API level 26 by default for Android apps. If the Play Store submission for your app has failed because of the target API level requirement, please rebuild your app using the Make tool in AppStudio and then submit it again to the Play Store. 
Please note that from August 2018, the Google Play Store only requires new apps to target Android API level 26. However, if you already have apps that are published in the Google Play Store with the target API level below Android API level 26, and plan to publish updates to them, you can still do so until the 1st of November 2018. After this date, any updates to existing apps also need to target API level 26. In this case, you will need to rebuild your app with AppStudio 3.0 or later to fulfill the requirement of the Google Play Store.
 
I hope this has been helpful in getting your Android apps to the store, and we look forward to making your experience with AppStudio better.

Summary

 

Consider a project which uses a SQLite database.

 

Over time requirements changes. We may be forced to change the schema of that SQLite database.

 

Unlike other RDBMS, SQLite doesn't allow you to add, modify or delete columns in a table after a table is created. You will be required to export the data, recreate the schema, then import the data. Such data migration exercises requires scripts to be built with attention to detail to ensure there is no data loss during data migration.

 

You can future proof your schema by storing data as JSON. AppStudio's SqlScalarFunction helps optimize the use of JSON.

 

Scenario

 

You've been tasked with creating a Geographic Quiz app.

Version 1 will ship with a set of countries and we want to quiz the user their knowledge of capitals.
Version 2 will ship with population and we want to quiz the user to rank countries in order.
Version 3 will ship with currency conversion quiz.

 

Initial App

 

The following is a sample app which is an initial implementation of Version 1. We will deconstruct this app and explore how it could handle the future requirements.

 

import QtQuick 2.7
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Sql 1.0

Item {
    property FileFolder sqlFolder: FileFolder { path: "~/ArcGIS/Data/Sql" }

    ListView {
        id: listView
        anchors.fill: parent
        anchors.margins: 10
        delegate: Row { spacing: 10; Text { text: name } Text { text: capital } }
    }

    SqlDatabase {
        id: db
        databaseName: sqlFolder.filePath('countries.sqlite')
        SqlScalarFunction {
            name: 'json_value'
            method: function (json_text, key) { return json_text ? JSON.parse(json_text)[key] : null; }
        }
    }

    Component.onCompleted: {
        db.open();
        db.query("DROP TABLE IF EXISTS countries ");
        db.query("CREATE TABLE IF NOT EXISTS countries (json_text TEXT)");
        db.query("CREATE INDEX countries_name ON countries ( json_value(json_text, 'name') COLLATE NOCASE )");
        db.query("INSERT INTO countries VALUES (:json_text)", { json_text: '{"name":"United States", "capital":"Washington D.C."}'} );
        db.query("INSERT INTO countries VALUES (:json_text)", { json_text: '{"name":"Australia", "capital":"Canberra", "population": 24130000}'} );
        db.query("INSERT INTO countries VALUES (:json_text)", { json_text: '{"name":"France", "capital":"Paris"}'} );

        var sql = [
            "SELECT json_value(json_text, 'name') as name, ",
            "       json_value(json_text, 'capital') as capital ",
            "FROM   countries ",
            "WHERE  name like 'United%' "
        ].join("\n");

        listView.model = db.queryModel(sql);
    }
}

 

Schema

 

You see the schema for our app is simply:

 

CREATE TABLE countries
(
    json_text TEXT
)

 

 

 

In Version 1, we want json_text to be a JSON string with the country's name and capital defined. To preview Version 2 one of the records already has population defined as well.

 

Unpacking JSON with SqlScalarFunction

 

We have a SqlScalarFunction defined which implements:

 

 

function json_value(json_text, key)
{
    return json_text ? JSON.parse(json_text)[key] : null;
}

 

 

 

So that `SQLite` can use this to unpack a JSON string and retrieve a value by its key.

 

Indexing and Querying

 

SQLite allows one to create indexes on expressions. This means we can create an index on a key extracted from the JSON string. This feature is incredible. It means we are effectively caching an extracted value so that means we can avoid repeated calculation.

 

CREATE INDEX countries_name ON countries ( json_value(json_text, 'name') COLLATE NOCASE );

SELECT json_value(json_text, 'name') AS name,
       json_value(json_text, 'capital') AS capital
FROM   countries
WHERE  name LIKE 'United%';

 

When you check the query with `EXPLAIN QUERY PLAN` we see that it is using the index:

EXPLAIN QUERY PLAN
SELECT json_value(json_text, 'name') AS name,
       json_value(json_text, 'capital') AS capital
FROM   countries
WHERE  name LIKE 'United%';

SEARCH TABLE countries USING INDEX countries_name (<expr>>? AND <expr><?)

 

Note that a collating sequence must be specified else `LIKE` where clauses will yield a full table scan.

 

Planning

 

When planning for Versions 2 and Versions 3 of the app, you'll appreciate that we can reuse the existing SQLite database and just add or update records within. We can easily drop / create indexes to accommodate new fields and new app requirements.