Skip navigation
All Places > AppStudio for ArcGIS > Blog > Authors SQuan-esristaff

AppStudio for ArcGIS

7 Posts authored by: SQuan-esristaff Employee

1. Mary had a little lamb

 

AppStudio 3.3 is based on Qt5.12.1 which includes a new JavaScript engine supporting ECMAScript 6 and ECMAScript 7. In this blog we will be looking at multiline string literals, specifically, different ways of realizing the following nursery rhyme:

 

Mary had a little lamb, little lamb, little lamb
Mary had a little lamb
Whose fleece was white as snow.

 

2. The 1 liner example

 

var str = "Mary had a little lamb, little lamb, little lamb\nMary had a little lamb\nWhose fleece was white as snow.\n"

 

The above code gives us what we want. A 3 line rhyme stored in a string. It's short, but, it is hard to read and maintain. The newline \n character makes reading the first word in the following sentence awkward.

 

3. String lists

 

var list = [ "Mary had a little lamb, little lamb, little lamb",
             "Mary had a little lamb",
             "Whose fleece was white as snow." ]
var str = list.join("\n") + "\n"

 

Using a staging list means we don't need to remember to put \n to break every line. It's looks nicer in code and easier to maintain the rhyme since we can now split the code over multiple lines. It does suffer from the fact that we need to ensure the syntax of lists is correct (i.e. the commas needed to be correctly placed). It has the advantage that we can make use of array functions such as appending to the end of the list. The joining line does look awkward but it's always the same pattern so you'll get used to it. Because we are using join, we must remember to append the last \n.

 

4. Using the files

 

var str = app.folder.readTextFile("rhyme.txt")

 

By storing strings in files, we can dramatically reduce the code that appears in your program. This reduces error significantly, and, maintenance on your rhyme can be done in a regular text edit without worrying about the Javascript side of things.

 

5. ECMAScript 6 multiline string

 

var str = `Mary had a little lamb, little lamb, little lamb
Mary had a little lamb
Whose fleece was white as snow.
`

As of AppStudio 3.3 (and Qt 5.12.x) we can now use backticks ` to surround a multiline string element. This is incredibly easy to read and maintain. Backticks also introduces a new feature called expression interpolation. Here's a simple example involving our poem:

 

var name = 'Mary';
var str = `${name} had a little lamb, little lamb, little lamb
${name} had a little lamb
Whose fleece was white as snow.
`

 

Here's a more extended form which includes operators:

 

var customer = { name: "Foo" }
var card = { amount: 7, product: "Bar", unitprice: 42 }
var str = `Hello ${customer.name},
want to buy
${card.amount} ${card.product} for
a total of
${card.amount * card.unitprice} bucks?`

 

References

 

 

Send us your feedback to appstudiofeedback@esri.com

 

Become an AppStudio for ArcGIS developer! Watch this video on how to sign up for a free trial.

 

Follow us on Twitter @AppStudioArcGIS to keep up-to-date on the latest information and let us know about your creations built using AppStudio to be featured in the AppStudio Showcase.

 

The AppStudio team periodically hosts workshops and webinars; please click on this link to leave your email if you are interested in information regarding AppStudio events.

Introduction

 

I have some strings in my database. I would like to query them. Also, I want the results quick. Simple, use SQL LIKE and put an index on it.

 

Oh wait, that's weird, it didn't work like how I want... this blog covers the common traps with searching text strings in SQLite.

 

Scenario

 

Let's look at a sample database table. This one is representing property parcels and their very famous owners.

 

CREATE TABLE parcel
(
  owner TEXT
);

INSERT INTO parcel (owner) values ('Bill Gates');
INSERT INTO parcel (owner) values ('Steve Jobs');
INSERT INTO parcel (owner) values ('Jack Dangermond');
INSERT INTO parcel (owner) values ('Steve Wozniak');
INSERT INTO parcel (owner) values ('Tim Cook');
INSERT INTO parcel (owner) values ('Mark Zuckerberg');

 

We will be running queries on the above data, similar to:

 

SELECT * FROM parcel;

 

and, at the same time, we will be studying the query's execution plan with something similar to:

 

EXPLAIN QUERY PLAN SELECT * FROM parcel;

 

Using and optimizing LIKE

 

Once you dabble in a bit of SQL you'll quickly realize that you need to use LIKE for your string searches. For example, if we want to find all owners with names beginning with the letter J we do this:

 

SELECT *
FROM parcel
WHERE owner LIKE 'j%';
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"owner":"Jack Dangermond"}

 

Here, we observe that it found the right result, i.e. Jack Dangermond, however, the query needed to do a full table scan (i.e. SCAN TABLE parcel) to find that this was the only result.

 

Let's attempt to speed this up with an index and try again:

 

CREATE INDEX ix_parcel_owner ON parcel (owner);
SELECT *
FROM parcel
WHERE owner LIKE 'j%'
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"owner":"Jack Dangermond"}

 

Well, we got the right result, i.e. Jack Dangermond, but, why is it still doing a full table scan (SCAN TABLE parcel)?

 

Why didn't it use my index ix_parcel_owner?

 

The answer is SQLite, unlike other databases, implements LIKE as a case insensitive search so we got Jack Dangermond which begins with a capital J even though our search pattern was "j%" which begins with a lowercase J. The index that we created was a case sensitive index. LIKE ignored the index because it needed a case insensitive index.

 

To fix this, we try again. We create an index specific for case insensitive searches.

 

CREATE INDEX ix_parcel_owner_collate ON parcel (owner COLLATE NOCASE);
SELECT *
FROM   parcel
WHERE  owner LIKE 'j%';
-- EXPLAIN QUERY PLAN: SEARCH TABLE parcel USING COVERING INDEX ix_parcel_owner_collate (owner>? AND owner<?)
-- OUTPUT: {"owner":"Jack Dangermond"}

 

There, we did it. The result is still Jack Dangermond, this time the index was used (SEARCH TABLE parcel USING COVERING INDEX ix_parcel_owner_collate). Take home message, COLLATE NOCASE is your friend.

 

Alternatives to LIKE for string contains searches

 

Now that we've got some success with LIKE, let's use it to find more things. Let's see if we can find all owners with the letter C anywhere in their name:

 

SELECT *
FROM parcel
WHERE owner LIKE '%c%';
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"owner":"Jack Dangermond"}
-- OUTPUT: {"owner":"Tim Cook"}
-- OUTPUT: {"owner":"Mark Zuckerberg"}

 

Okay, we got the results we wanted. Jack Dangermond, Tim Cook and Mark Zuckerberg all have the letter C is their names. However, why are we back at full table scans (SCAN TABLE parcel)? Why isn't the index (ix_parcel_owner_collate) being used any more? That's because the B-Tree index being used works like how you look up names in a phone book. It works great if you have the starting letter(s) (i.e. divide the book in half, choose the half your letter is in, divide the book in half again).

 

However, because we don't have a starting letter, we can no longer use the phone book trick. We're back scanning every record in the table. The index we created is useless for this type of query.

 

So, what can we do? Well, there's a technique you can use but it requires rewriting the query, have a look at the following:

 

SELECT *, INSTR(LOWER(owner), LOWER('c')) idx
FROM parcel;
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"idx":0,"owner":"Bill Gates"}
-- OUTPUT: {"idx":0,"owner":"Steve Jobs"}
-- OUTPUT: {"idx":3,"owner":"Jack Dangermond"}
-- OUTPUT: {"idx":0,"owner":"Steve Wozniak"}
-- OUTPUT: {"idx":5,"owner":"Tim Cook"}
-- OUTPUT: {"idx":8,"owner":"Mark Zuckerberg"}

SELECT *
FROM parcel
WHERE INSTR(LOWER(owner), LOWER('c')) > 0;
-- EXPLAIN QUERY PLAN: SCAN TABLE parcel
-- OUTPUT: {"owner":"Jack Dangermond"}
-- OUTPUT: {"owner":"Tim Cook"}
-- OUTPUT: {"owner":"Mark Zuckerberg"}

 

What is happening is we're using a function INSTR which returns the position of a substring (i.e. the letter C) in a string (i.e. owner). If the substring doesn't exist, you will simply get 0. We use the LOWER function on both the letter C and the owner to make it a case insensitive search.

 

The result is still the same as the previous version involving LIKE, i.e. we are getting all owners with the letter C in their names. However, the query plan is still doing a full table scan (SCAN TABLE parcel).

 

So, what's the point?

 

Well, SQLite allows you to index expressions! Think of it like a pre-calculated column.

 

CREATE INDEX ix_parcel_owner_instr_c ON parcel (INSTR(LOWER(owner), LOWER('c')));

 

The above statement will create an index. It may take some time. For example, if you had over 200000 records, that index may take several seconds to create. Consider that to be a good thing. Time spent here means the INSTR expression is being calculated for all records in the database once and only once. Every time we add a new record or modify an existing record, it will be the only time when that INSTR expression is calculated / recalculated.

 

That expression will never be recalculated at the time of the query. The query would just reuse the pre-calculated value that was stored in the index:

 

 

SELECT *
FROM parcel
WHERE INSTR(LOWER(owner), LOWER('c')) > 0;
-- EXPLAIN QUERY PLAN: SEARCH TABLE parcel USING INDEX ix_parcel_owner_instr_c (<expr>>?)
-- OUTPUT: {"owner":"Jack Dangermond"}
-- OUTPUT: {"owner":"Tim Cook"}
-- OUTPUT: {"owner":"Mark Zuckerberg"}

 

The above confirms we are now using the index (SEARCH TABLE parcel USING INDEX ix_parcel_owner_instr_c).

 

Code Sample

 

This AppStudio code sample was used to generate all the SQL content in this blog:

 

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

App {
    id: app
    width: 800 * AppFramework.displayScaleFactor
    height: 640 * AppFramework.displayScaleFactor

    property string logText: ""

    Flickable {
        id: flickable

        anchors.fill: parent
        anchors.margins: 10

        contentWidth: textArea.width
        contentHeight: textArea.height
        clip: true

        TextArea {
            id: textArea

            width: flickable.width
            wrapMode: Text.WrapAtWordBoundaryOrAnywhere
            selectByMouse: true
            text: logText
        }
    }

    SqlDatabase {
        id: db
        databaseName: ":memory:"
    }

    Component.onCompleted: {
        db.open();
        exec( [
            "CREATE TABLE parcel",
            "(",
            "    owner TEXT",
            ");"
            ].join("\n") );
        exec("INSERT INTO parcel (owner) values ('Bill Gates');");
        exec("INSERT INTO parcel (owner) values ('Steve Jobs');");
        exec("INSERT INTO parcel (owner) values ('Jack Dangermond');");
        exec("INSERT INTO parcel (owner) values ('Steve Wozniak');");
        exec("INSERT INTO parcel (owner) values ('Tim Cook');");
        exec("INSERT INTO parcel (owner) values ('Mark Zuckerberg');");
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE owner LIKE 'j%';"
            ].join("\n") );
        exec("CREATE INDEX ix_parcel_owner ON parcel (owner);");
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE owner LIKE 'j%';"
            ].join("\n") );
        exec("CREATE INDEX ix_parcel_owner_collate ON parcel (owner COLLATE NOCASE);");
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE owner LIKE 'j%';"
            ].join("\n") );
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE owner LIKE '%c%';"
            ].join("\n") );
        exec( [
            "SELECT *, INSTR(LOWER(owner), LOWER('c')) idx",
            "FROM parcel;",
            ].join("\n") );
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE INSTR(LOWER(owner), LOWER('c')) > 0;"
            ].join("\n") );
        exec("CREATE INDEX ix_parcel_owner_instr_c ON parcel (INSTR(LOWER(owner), LOWER('c')));");
        exec( [
            "SELECT *",
            "FROM parcel",
            "WHERE INSTR(LOWER(owner), LOWER('c')) > 0;"
            ].join("\n") );
    }

    function exec(sql) {
        logText += "\n";
        logText += sql + "\n";

        var explain = db.query("EXPLAIN QUERY PLAN " + sql);
        if (!explain.error && explain.first()) {
            logText += "-- EXPLAIN QUERY PLAN: %1\n".arg(explain.values.detail);
            explain.finish();
        }

        var query = db.query(sql);
        if (query.error) {
            logText += query.error.databaseText;
            logText += query.error.driverText;
            logText += qsTr("NativeErrorCode: %1").arg(query.error.nativeErrorCode);
            logText += qsTr("ErrorType: %1").arg(query.error.type);
            return;
        }

        var ok = query.first();
        while (ok) {
            logText += "-- OUTPUT: %1\n".arg(JSON.stringify(query.values));
            ok = query.next();
        }
        query.finish();
    }
}

 

 

Summary

 

If speed matters to you, you cannot just create an index and just leave it there.

 

You need to check whether your queries use the index with EXPLAIN QUERY PLAN. If your index isn't being used, look at your query. Look at your WHERE clause. Think of what is happening there. Rewrite your WHERE clause if necessary. Create indexes that matches your WHERE clause.

 

If necessary, be prepared to index on expressions. Don't go overboard, we didn't create an index for every letter of the alphabet. That wasn't in our requirements today. We only wanted to search for the letter C and do that better. Over time, requirements changes. We probably will become disinterested in search for the letter C, then, feel free to drop that index. However, if a new criteria becomes more important, e.g. we want to search for all owners with Jack in the first, middle or last name, then, we will create an index to help with that purpose.

Kale is not food!


Kale, apparently has many health benefits such as being high in fiber and water. However, me, personally, I find it on par on eating paper and I refuse to even accept that kale is proper food!

 

Kale rant aside, this blog post is really about coding patterns for writing conditionals in an AppStudio (QML) app.

 

"Kate is not food!" app

 

I've come up with a simple app that demonstrate the conditional coding pattern.

 

It has a combo box with the values "apple", "carrot" or "kale" where. Upon a user's selection, the app will respond with the corresponding food type: "fruit", "vegetable" or "unknown" respectively.

 

To implement the above app, there are many methods I've come across, thus, I take a comparative approach covering the most common to the most peculiar.

 

Each approach has a complete working code sample and discussed.

 

Approach 1 - if statement

import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0

App {
id: app

property alias food: foodComboBox.currentText
property string foodType: getFoodType(food)

Column {
anchors.fill: parent
anchors.margins: 10
spacing: 10


ComboBox {
id: foodComboBox
model: [ "apple", "carrot", "kale" ]
}

Text {
text: foodType
}
}

function getFoodType(food) {
if (food === "apple") {
return "fruit";
}
if (food === "carrot") {
return "vegetable";
}
return "unknown";
}
}

 

The above is a complete code sample that determines a type of food given a user's input.

 

We see there are two visual components, ComboBox component for the user to select "apple", "carrot" or "kale" and a Text component to display the corresponding food type "fruit", "vegetable" or "unknown" respectively.

 

The visual component is constant in all the apps shown in the blog. The only thing that differs is the implementation of the foodType property.

 

In this example, it is implemented with the if statement via the getFoodType function.

 

It's very obvious, very easy to read and maintain.


I'm usually against the if statement pattern:

  • Tends to invite over application of else and else if
  • Tends to force additional code block indentation
  • Tends to lead to unintended code paths
  • Requires repeated applications of the if statement to describe your business logic


I've carefully crafted the answer to not utilize any else statements. When using if statements, I prefer to implement early exit with return statement following the guard pattern removing one level of nesting for flatter code and helps avoids errors. See Guard (computer science) - Wikipedia 


When there are 3 or more cases if statements can become cumbersome to read and maintain. Terse cases may require a refactor moving code to their own functions.

 

Approach 2 - conditional expressions

 

import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0

App {
id: app

property alias food: foodComboBox.currentText
property string foodType: food === "apple" ? "fruit" : food === "carrot" ? "vegetable" : "unknown"

Column {
anchors.fill: parent
anchors.margins: 10
spacing: 10

ComboBox {
id: foodComboBox
model: [ "apple", "carrot", "kale" ]
}

Text {
text: foodType
}
}
}

 

The conditional (ternary) operator is very short and deceptively simple. It is often used in 1-liner solutions.

 

I tend to use conditional for very simplest of cases.

 

As your application complexity grows, the conditional expression rapidly becomes hard to read and maintain. They tend to obfuscate what you're trying to achieve.

 

When there are 3 or more cases, it quickly loses its appeal as a 1-liner solution.

 

 

Approach 3 - switch statements

 

import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0

App {
id: app

property alias food: foodComboBox.currentText
property string foodType: getFoodType(food)

Column {
anchors.fill: parent
anchors.margins: 10
spacing: 10

ComboBox {
id: foodComboBox
model: [ "apple", "carrot", "kale" ]
}

Text {
text: foodType
}
}

function getFoodType(food) {
switch (food) {
case "apple":
return "fruit";
case "carrot":
return "vegetable";
}
return "unknown";
}
}


The switch statement is designed to handle multiple cases. If the values for each case is concise, then, this approach works well.

 

In my apps, I find, however, as my requirement grows, the switch statement becomes longer and longer and they it ends up as code that's longer than one screenful. When that happens, we lose the conciseness of this approach. In order to get back down to something that's manageable we refactor the cases by moving the code for each cases to their own function.

 

 

Approach 4 - lookup table

 

import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0

App {
id: app

property alias food: foodComboBox.currentText
property var foodTypes: {
"apple": "fruit",
"carrot": "vegetable"
}
property string foodType: foodTypes[food] || "unknown"

Column {
anchors.fill: parent
anchors.margins: 10
spacing: 10

ComboBox {
id: foodComboBox
model: [ "apple", "carrot", "kale" ]
}

Text {
text: foodType
}
}
}

 

This approach implements a lookup table. We leverage a Javascript object's ability to hold key value pairs. The advantages to this approach is the code is declarative with minimal imperative code.

 

I used this approach when managing large list of keys whose values are simple and defined. We simply hard code the list at the top of the app, and/or deployed the list via JSON object resource that gets loaded when the app starts.

 

This is approach is easy to read and maintain. We have a limitation that the key to the lookup table must be a string.

 

Approach 5 - invoking function by name

 

import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0

App {
id: app

property alias food: foodComboBox.currentText
property string foodType: getFoodType(food)

Column {
anchors.fill: parent
anchors.margins: 10
spacing: 10

ComboBox {
id: foodComboBox
model: [ "apple", "carrot", "kale" ]
}

Text {
text: foodType
}
}

function getFoodType_apple() {
return "fruit";
}

function getFoodType_carrot() {
return "vegetable";
}

function getFoodType(food) {
var func = "getFoodType_" + food;
return func in app ? app[func]() : "unknown";
}
}


This approach uses the fact that we can invoke a Javascript function by name. It also requires us to use the name of the parent object (here it is called app). We have to construct the function name, and, if it exists we invoke that function.

 

I used this approach in app where the requirements were changing. I was building a conversion app. The requirements were changing, i.e. the list of inputs (e.g. the list of foods) will grow over time. This pattern was useful as it allowed me to focus on handling new cases as they occur.

 

property var bool use_merriam_webster: true

function getFoodType_tomato()
{
// A tomato is actually a fruit -- but it's a vegetable at the same time
// https://www.businessinsider.com.au/tomato-fruit-or-vegetable-2018-5?r=US&IR=T
if (use_merriam_webster) {
return "fruit";
}
return "vegetable";
}

 

Despite being the driver function being hard to read, it has advantage that it doesn't require maintenance to add new cases. We simply start coding the new cases and they will automatically be picked up.

 

Summary

 

For the "Kale is not food" app all 5 approaches shown above achieve the task and one may argue that there isn't much differences between them in achieving that task.

 

For more complex applications, some of the more obscure approaches such as lookup table or invocation by function name may become more appealing in long term ability to maintain and scale.

 

Generally, whenever I see code involving either the if or switch statement, I ask myself would it be worthwhile to check to see if another approach works better?. Usually, I find: yes, it is.

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.

1. Introduction

 

This AppStudio blog describes how we can use the Networking component to detect for network connectivity and classify the type of network (LAN, WIFI, Mobile Data). This allows you to build apps that can react differently dependent on what type of network they're on, e.g. ask the user whether they want to download content whilst on an expensive mobile data network.

 

2. Minimal App - All Network Configurations

Let's start with a minimal app that shows all Network connections whether active or inactive:

 

import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Networking 1.0

App {
    width: 640 * AppFramework.displayScaleFactor
    height: 480 * AppFramework.displayScaleFactor

    Flickable {
        anchors.fill: parent
        contentWidth: textArea.width
        contentHeight: textArea.height
        TextArea {
            id: textArea
            text: JSON.stringify(Networking.configurations, undefined, 2)
        }
    }
}

 

On my Windows machine, I get:

 

{
  "0": {
    "objectName": "",
    "valid": true,
    "name": "vEthernet (Internal Ethernet Port Windows Phone Emulator Internal Switch) 2",
    "identifier": "312537767",
    "bearerType": 1,
    "bearerTypeFamily": 1,
    "bearerTypeName": "Ethernet",
    "configurationType": 0,
    "connectTimeout": 30000,
    "purpose": 0,
    "roamingAvailable": false,
    "state": 14
  },
  "1": {
    "objectName": "",
    "valid": true,
    "name": "vEthernet (VMware Virtual Ethernet Adapter for VMnet8 Virtual Switch)",
    "identifier": "287176532",
    "bearerType": 1,
    "bearerTypeFamily": 1,
    "bearerTypeName": "Ethernet",
    "configurationType": 0,
    "connectTimeout": 30000,
    "purpose": 0,
    "roamingAvailable": false,
    "state": 14
  },
  "2": {
    "objectName": "",
    "valid": true,
    "name": "vEthernet (Default Switch)",
    "identifier": "287176527",
    "bearerType": 1,
    "bearerTypeFamily": 1,
    "bearerTypeName": "Ethernet",
    "configurationType": 0,
    "connectTimeout": 30000,
    "purpose": 0,
    "roamingAvailable": false,
    "state": 14
  },
  "3": {
    "objectName": "",
    "valid": true,
    "name": "vEthernet (VMware Virtual Ethernet Adapter for VMnet1 Virtual Switch)",
    "identifier": "312537825",
    "bearerType": 1,
    "bearerTypeFamily": 1,
    "bearerTypeName": "Ethernet",
    "configurationType": 0,
    "connectTimeout": 30000,
    "purpose": 0,
    "roamingAvailable": false,
    "state": 14
  },
  "4": {
    "objectName": "",
    "valid": true,
    "name": "Bluetooth Network Connection",
    "identifier": "312537798",
    "bearerType": 1,
    "bearerTypeFamily": 1,
    "bearerTypeName": "Ethernet",
    "configurationType": 0,
    "connectTimeout": 30000,
    "purpose": 0,
    "roamingAvailable": false,
    "state": 2
  },
  "5": {
    "objectName": "",
    "valid": true,
    "name": "vEthernet (Intel(R) 82579LM Gigabit Network Connection Virtual Switch)",
    "identifier": "312537802",
    "bearerType": 1,
    "bearerTypeFamily": 1,
    "bearerTypeName": "Ethernet",
    "configurationType": 0,
    "connectTimeout": 30000,
    "purpose": 0,
    "roamingAvailable": false,
    "state": 14
  }
}

 

The things we observed about Networking.configurations are:

 

  1. it's an object, not an array,
  2. every connection is listed (both active and inactive)
  3. there are signals on Network.configurations so you can use it in property binding scenarios (i.e. the moment the network changes, e.g. device is put in airplane mode, your app automatically updates)

 

3. Active Network Configurations

 

In order to filter the list down quickly, we transform the object to an array, then filter the results using Array.prototype.filter:

 

import QtQuick 2.7
import QtQuick.Controls 2.1
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Networking 1.0

App {
    width: 640 * AppFramework.displayScaleFactor
    height: 480 * AppFramework.displayScaleFactor

    Flickable {
        anchors.fill: parent
        contentWidth: textArea.width
        contentHeight: textArea.height
        TextArea {
            id: textArea
            text: JSON.stringify(
                toArray(Networking.configurations)
                    .filter(isConfigActive)
                    .map(summary),
            undefined, 2)
        }
    }

    function toArray(o) {
        return Object.keys(o).map(function (e) { return o[e]; } );
    }

    function isConfigActive(c) {
        return (c.state & NetworkConfiguration.StateActive) === NetworkConfiguration.StateActive;
    }

    function summary(c) {
        return {
            name: c.name,
            bearerType: c.bearerType
        };
    }
}

 

On my Windows machine, this reduces to an easier to read list:

 

[
  {
    "bearerType": 1,
    "name": "vEthernet (Internal Ethernet Port Windows Phone Emulator Internal Switch) 2"
  },
  {
    "bearerType": 1,
    "name": "vEthernet (VMware Virtual Ethernet Adapter for VMnet1 Virtual Switch)"
  },
  {
    "bearerType": 1,
    "name": "vEthernet (Default Switch)"
  },
  {
    "bearerType": 1,
    "name": "vEthernet (Intel(R) 82579LM Gigabit Network Connection Virtual Switch)"
  },
  {
    "bearerType": 1,
    "name": "vEthernet (VMware Virtual Ethernet Adapter for VMnet8 Virtual Switch)"
  }
]

 

On my Android device, when WIFI is enabled:

 

[
  {
    "bearerType": 2,
    "name": "WIFI"
  }
]

 

or, when Mobile Data is enabled:

 

[
  {
    "bearerType": 10,
    "name": "Mobile"
  }
]

 

On my iOS device, when WIFI is enabled:

 

[
  {
    "bearerType": 0,
    "name": "en0"
  },
  {
    "bearerType": 0,
    "name": "awdl0"
  },
  {
    "bearerType": 0,
    "name": "utun0"
  }
]

 

4. Determining if the Network is LAN, WIFI or MobileData

 

On Windows, Android and Linux the bearerType can be used to classify the network connection, e.g.

 

  • NetworkConfiguration.BearerEthernet
  • NetworkConfiguration.BearerWLAN
  • NetworkConfiguraiton.Bearer3G

 

Whilst on iOS and macOS, the bearerType may not be provided by the operating system resulting in NetworkConfiguration.BearerUnknown. To cover iOS and macOS, we note that the name of the configuration instead:

 

  • en0
  • awdl0
  • pdp_ip0

 

    function isConfigLAN(c) {
        if (c.bearerType === NetworkConfiguration.BearerEthernet) {
            return true;
        }

        if (Qt.platform.os === "osx") {
            return c.name === "en0";
        }

        return false;
    }
   
    function isConfigWIFI(c) {
        if (c.bearerType === NetworkConfiguration.BearerWLAN) {
            return true;
        }

        if (Qt.platform.os === "ios") {
            return c.name === "en0";
        }
       
        if (Qt.platform.os === "osx") {
            return c.name === "awdl0";
        }

        return false;
    }

    function isConfigMobileData(c) {
        switch (c.bearerType) {
        case NetworkConfiguration.Bearer2G:
        case NetworkConfiguration.BearerCDMA2000:
        case NetworkConfiguration.BearerWCDMA:
        case NetworkConfiguration.BearerHSPA:
        case NetworkConfiguration.BearerWiMAX:
        case NetworkConfiguration.BearerEVDO:
        case NetworkConfiguration.BearerLTE:
        case NetworkConfiguration.Bearer3G:
        case NetworkConfiguration.Bearer4G:
            return true;
        }

        if (Qt.platform.os === "ios") {
            return c.name === "pdp_ip0";
        }

        return false;
    }

    function isConfigActiveLAN(c) {
        return isConfigActive(c) && isConfigLAN(c);
    }

    function isConfigActiveWIFI(c) {
        return isConfigActive(c) && isConfigWIFI(c);
    }

    function isConfigActiveMobileData(c) {
        return isConfigActive(c) && isConfigMobileData(c);
    }

 

Now that we have these useful functions, we can quickly chain them together to determine whether we're on WIFI or MobileData. Because we convert the object to an array, we can make use of Array.prototype.some to quickly lookup a network configuration of a particular type:

 

    property var config: toArray(Networking.configurations)
    property bool isOnline: isLAN || isWIFI || isMobileData
    property bool isLAN: config.some(isConfigActiveLAN)
    property bool isWIFI: config.some(isConfigActiveWIFI)
    property bool isMobileData: config.some(isConfigActiveMobileData)
    property bool isMobileDataOnly: isMobileData && !isWIFI && !isLAN

 

 

5. Network Check Sample

 

The "Network Check" app is available for you to try which demonstrates all of the above points. You can find "Network Check" app in AppStudio.

 

  1. Launch AppStudio
  2. Select New App
  3. Click Search Icon
  4. Type: Network Check

 

Network Check

 

The "Network Check" source code is also avilable at arcgis-appstudio-samples GitHub.

Summary

 

This is the second blog about the SQL Beta feature in AppStudio 2.0. If you haven't seen the first blog, I recommend you start there.

 

In this blog, we will discuss SQLite In-Memory databases and CSV files and how this may benefit your application.

 

1. SQLite In-Memory Database

 

The ":memory:" name is used to specify the in-memory database. This is useful for applications that would like to start with a blank SQLite database every time the application is run and do not need the data to persist after the application has completed. This is useful for providing model content to populate controls.

 

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

App {
    id: app
    width: 640 * AppFramework.displayScaleFactor
    height: 480 * AppFramework.displayScaleFactor

    ComboBox {
        id: countryComboBox
        readonly property var _model: model
        textRole: "country"
    }

    SqlDatabase {
        id: db
        databaseName: ":memory:"
    }

    Component.onCompleted: {
        db.open();
        db.query("CREATE TABLE world_cities (name TEXT,subcountry TEXT, country TEXT)");
        db.query("INSERT INTO world_cities VALUES ('Melbourne', 'Victoria', 'Australia')");
        db.query("INSERT INTO world_cities VALUES ('Redlands', 'California', 'United States')");
        countryComboBox.model = db.queryModel('SELECT DISTINCT country FROM world_cities');
    }
}

 

2. Bundling CSV files in your app

 

If you have data in a CSV file, we can create a linked table in SQLite. As a linked table only the metadata, i.e. filename to the CSV file is stored in SQLite, the content will continue to reside only in the CSV file. The CSV file can be either a physical file or an embedded resource. The syntax to linking tables is as follows:

 

CREATE VIRTUAL TABLE tableName USING CSV ( 'filePath' )

 

The advantage of using technique is that you will have access to your CSV data via the SQLite query engine.

 

When we're developing the application we will be using:

 

CREATE VIRTUAL TABLE world_cities USING CSV ( 'C:/Users/Stephen/ArcGIS/AppStudio/Apps/Sample/data/world-cities.csv' )

 

When we've finalized the application and want to build it using Local Make or Cloud Make, we want to change it to:

 

CREATE VIRTUAL TABLE world_cities USING CSV ( ':/qml/data/world-cities.csv' )

 

To achieve both we will use the FileFolder object to help.

 

    SqlDatabase {
        id: db
        databaseName: ":memory:"
    }

    FileFolder {
        id: dataFolder
        url: "data"
    }

    Component.onCompleted: {
        var csvFilePath = dataFolder.filePath("world-cities.csv");
        db.open();
        db.query("CREATE VIRTUAL TABLE world_cities USING CSV ( '" + csvFilePath + "' )");
        countryComboBox.model = db.queryModel('SELECT DISTINCT country FROM world_cities');
    }

 

3. ComboBox with Huge Data

 

By leveraging from the SQLite query engine we can build quite compelling user interfaces quickly and simply. In the following example, instead of asking the user to select from a list of 20,000 cities, we can filter the list with a WHERE clause on country and subcountry.

 

 

    cityComboBox.model = db.queryModel(
          "SELECT    name "
        + "FROM      world_cities "
        + "WHERE     country = :country "
        + "AND       subcountry = :subcountry "
        + "ORDER BY  name",
          {
              country: countryComboBox.currentText,
              subcountry: subcountryComboBox.currentText
          }
    );

 

If you are experiencing a lag whenever the queries are run, then, at this point, you will need to consider using SQLite indexes. However, indexes cannot be applied to the linked table. We must make a copy of the table and then we can put indexes on the copy.

 

    Component.onCompleted: {
        var csvFilePath = dataFolder.filePath("world-cities.csv");
        db.open();
        db.query("CREATE VIRTUAL TABLE world_cities_csv USING CSV ('" + csvFilePath + "')");
        db.query("CREATE TABLE world_cities AS SELECT * FROM world_cities_csv");
        db.query("CREATE INDEX IX_world_cities_001 ON world_cities (country, subcountry, name)");
        countryComboBox.model = db.queryModel('SELECT DISTINCT country FROM world_cities');
    }

 

4. Known Issues

 

4.1. Garbage Collection on Models

 

The Javascript garbage collector is prematurely destroying models. For example, the pattern for assigning a query model to a QML component is as follows:

 

        countryComboBox.model = db.queryModel(

 

QML components, currently, do not inform the Javascript garbage collector that it has a reference to the model. The Javascript garbage collector *CAN* destroy the model whenever it feels like. This can lead to instability with the QML component. i.e. suddenly the values in the ComboBox may disappear (i.e. be replaced with blanks instead of values) or interacting with the ComboBox may crash the application.

 

A workaround is to define a Javascript property that keeps the model alive. This is the purpose of the readonly _model property in the following code snippet:

 

    ComboBox {
        id: countryComboBox
        readonly property var _model: model
        textRole: "country"
    }

 

We've informed Qt company of this issue.

 

4.2 Quoted values

 

If you have quoted values in your CSV file, the values aren't unquoted when you access them. The workaround is you have to add additional logic to identify and remove the extraneous quotes.

 

This issue is being addressed for the next release of AppStudio

 

4.3 Trailing spaces aren't being trimmed

 

If you have trailing spaces in your CSV, e.g. "country, subcountry" instead of "country,subcountry" then this will cause a problem. For the time being, you will have to trim the trailing spaces from your CSV file.

 

This issue is being addressed for the next release of AppStudio

 

5. Select City sample

 

The "Select City from CSV" app is available for you to try which demonstrates all of the above points. You can find "Select City from CSV" app in AppStudio.

 

  1. Launch AppStudio
  2. Select New App
  3. Click Search Icon
  4. Type: Select City from CSV

 

                                                  

 

The "Select City from CSV" source code is also available at arcgis-appstudio-samples GitHub

 

6. Other AppStudio SQL Blogs

 

Introduction to SQL Beta in AppStudio 2.0

Using CSV files in your application

Summary

 

SQL was introduced as a Beta feature in the AppFramework as part of AppStudio 2.0. What this means is we are looking forward to your feedback, use cases and what you want us to improve on. Future changes may affect your app and may require additional changes to support it.

 

In AppStudio 2.0 you can:

  • read/write SQLite, ODBC, PostgreSQL and MySQL databases
  • read DBF files (via SQLite virtual tables)
  • read CSV files (via SQLite virtual tables)

 

To get a taste of the SQL support, we will be covering the following.

 

  1. Minimal app - open a SQLite database
  2. Running queries
  3. Error handling
  4. Looping through results
  5. Prepared and parameterized queries
  6. Autoincrement field
  7. SqlQueryModel and SqlTableModel
  8. Including SQL Schema as an app resource 
  9. SQL Viewer app

 

1. Minimal app - open a SQLite database

 

This minimal working sample opens a SQLite database in your home directory called ArcGIS/Data/Sql/sample.sqlite. The code works on all platforms. On Android, Linux, Windows and Mac platform, the database created and can be accessed by other apps. On iOS the database will be sandboxed to your application, i.e. only your application may access it.

 

import QtQuick 2.8
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Sql 1.0

Item {
  width: 640
  height: 480

  FileFolder {
    id: fileFolder
    path: "~/ArcGIS/Data/Sql"
  }

  SqlDatabase {
    id: db
    databaseName: fileFolder.filePath("sample.sqlite")
  }

  Component.onCompleted: {
    fileFolder.makeFolder();
    db.open();
  }
}


2.  Running queries

 

To run a SQL query, we use the SqlDatabase's query method. This function is overloaded, i.e. there are multiple, very useful ways of calling query. The most simplest is passing in a single string parameter and that query will be prepared and executed all in one go.

 

var query = db.query("SELECT COUNT(*) as Count FROM sqlite_master");
if (query.first()) {
  console.log(query.values.Count);
  query.finish();
}

 

3. Error handling

 

If the query failed, the error parameter would be not null. You can test for it, and, if it exists, it will be set to a JSON error object.

 

var query = db.query("****");
if (query.error) {
  console.log(JSON.stringify(query.error, undefined, 2));
} else {
  // success
}

 

Output:

 

{
  "isValid": true,
  "type": 2,
  "databaseText": "near \"****\": syntax error",
  "driverText": "Unable to execute statement",
  "text": "near \"****\": syntax error Unable to execute statement",
  "nativeErrorCode": "1"
}

 

4. Looping through results

 

If your query is a select statement, it will return data via the values JSON object property. The values property will contain values corresponding to one row of results. To get all the results we need to access them in a loop. Note that when we iterate through results, it's always important to call finish(). If you forget, the query could lock the database in an unfinished transaction which may prevent future operations such as DROP TABLE.

 

var query = db.query("SELECT * FROM Roads");
var ok = query.first();
while (ok) {
  console.log(JSON.stringify(query.values));
  ok = query.next();
}
query.finish();

 

Output:

 

qml: {"RoadID":1,"RoadName":"Coventry","RoadType":"St"}
qml: {"RoadID":2,"RoadName":"Sturt","RoadType":"St"}
qml: {"RoadID":3,"RoadName":"Kings","RoadType":"Way"}

 

5. Prepared and parameterized queries

 

A number of commands have been overloaded to support parameterized syntax. Using parametized queries diligently can stop accidental bugs or malicious attacks via SQL injection. You can bind to a parameter via name (e.g. ":name") or via position (e.g. "?"). In practice, I always recommend binding parameters by name, because it's stricter and safer. Parameterized queries go well with prepared queries. This is when you offer one SQL statement for repeated execution. The following shows how you can use this approach to populate a table.

 

var insert = db.query();
insert.prepare("INSERT INTO Roads (RoadName, RoadType) VALUES (:name, :type)");
insert.executePrepared( { "name": "Bank", "type": "St" } );
insert.executePrepared( { "name": "Dorcas", "type": "St" } );

 

6. Autoincrement field

 

If your table has an autoincrement field you may want to query its value so that you can use it. This is useful if that field is used in a relationship. i.e. you want to populate a related table using the value of the autoincrement field. The value of the last autoincrement operation is in the insertId property.

 

var query = db.query("INSERT INTO Roads (RoadName, RoadType) VALUES ('Park', 'St')");
var roadID = query.insertId;
var query2 = db.query(
    "INSERT INTO Inspections (RoadID, Quality) VALUES (:id, :quality)",
    { "id": roadID, "quality": "good" } );

 

7. SqlQueryModel and SqlTableModel

 

SqlQueryModel and SqlTableModel are a read-only data model for SQL result sets. The following demonstrates how you can populate a TableView using a SqlQueryModel.

 

import QtQuick 2.8
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Sql 1.0

Item {
  width: 640
  height: 480

  TableView {
    id: tableView
    anchors.fill: parent
    TableViewColumn {
      role: "RoadID"
      title: "Road ID"
    }
    TableViewColumn {
      role: "RoadName"
      title: "Road Name"
    }
    TableViewColumn {
      role: "RoadType"
      title: "Road Type"
    }
  }

  FileFolder {
    id: fileFolder
    path: "~/ArcGIS/Data/Sql"
  }

  SqlDatabase {
    id: db
    databaseName: fileFolder.filePath("sample.sqlite")
  }

  Component.onCompleted: {
    fileFolder.makeFolder();
    db.open();
    var queryModel = db.queryModel("SELECT * FROM Roads");
    tableView.model = queryModel;
  }
}

 

Output:

 

SQL Viewer Demo

 

8. Including SQL Schema as an app resource

 

To reduce the amount of JavaScript code, you may choose to initialize a SQLite database using a SQL Schema written in SQL. For example the following initdb.sql resets the SQLite database with a new Roads database every time the app is run:

 

DROP TABLE IF EXISTS Roads;
CREATE TABLE Roads
(
    RoadID   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    RoadName TEXT,
    RoadType TEXT,
    CHECK (RoadType IN ('St', 'Rd', 'Way'))
);
INSERT INTO Roads (RoadName, RoadType) VALUES ('Coventry', 'St');
INSERT INTO Roads (RoadName, RoadType) VALUES ('Sturt', 'St');
INSERT INTO Roads (RoadName, RoadType) VALUES ('Kings', 'Way');
CREATE INDEX IX_Roads_001 ON Roads (RoadName);
CREATE INDEX IX_Roads_002 ON Roads (RoadType);

Qt Creator will recognize SQL files if you add the *.sql filter to the qmlproject file as shown here:

 

    Files {
        directory: "."
        recursive: true
        filter: "*.json;*.html;*.txt;*.sql"
    }

 

Place the initdb.sql file into a scripts subdirectory and use the following code to load it:

 

    FileFolder {
        id: scriptsFolder
        url: "scripts"
    }

    Component.onCompleted: {
        dataFolder.makeFolder();
        db.open();
        var lines = scriptsFolder.readTextFile("initdb.sql").split(";");
        for (var i = 0; i < lines.length; i++) {
            var sql = lines[i];
            var query = db.query(sql);
            if (query.error) {
                console.log(JSON.stringify(query.error, undefined, 2));
                continue;
            }
        }
    }

 

Note that this example recognizes we can extract SQL commands by recognize each command is terminated by a semicolon. For more sophisticated SQL Schema involving triggers we will need to adapt the loading code appropriately.

 

9. SQL Viewer

 

The "SQL Viewer" app is available for you to try which demonstrates all of the above points. You can find a "SQL Viewer" app in AppStudio:

 

  1. Launch AppStudio
  2. Select New App
  3. Click Search Icon
  4. Type: SQL Viewer

 

The "SQL Viewer" source code is also available at arcgis-appstudio-samples/SQL Viewer at v2.0 · Esri/arcgis-appstudio-samples · GitHub 

 

10. Other AppStudio SQL Blogs

 

Introduction to SQL Beta in AppStudio 2.0
Using CSV files in your application