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

AppStudio for ArcGIS

11 Posts authored by: SQuan-esristaff Employee
SQuan-esristaff

Sorting QML ListModels

Posted by SQuan-esristaff Employee Jun 19, 2019

1. Introduction

 

 

This blog post discusses sorting the QML ListModel. It's one of my favourite problems to solve. The blog also talks about a Sorting Cities sample app. That sample app implements and demonstrates the QML ListModel sort. The rest of the blog explains how the function works.

 

 

2. Sorting Cities sample app

 

 

 

 

The Sorting Cities sample app is an AppStudio 3.3 app. It comes with five U.S. cities. The user can sort them by city name or by distance to ESRI. The sort order can be either ascending or descending.

 

Item {
    ColumnLayout {
        anchors.fill: parent
        anchors.margins: 10 * AppFramework.displayScaleFactor

        spacing: 10 * AppFramework.displayScaleFactor

        ListView {
            id: listView
            Layout.fillWidth: true
            Layout.fillHeight: true
            model: ListModel {
                id: listModel
                Component.onCompleted: {
                    add( "Hawaii", 21.289373, -157.917480 )
                    add( "Los Angeles", 34.052235, -118.243683 )
                    add( "New York City", 40.730610, -73.935242 )
                    add( "Redlands", 34.0555700, -117.1825400 )
                    add( "Seattle", 47.608013, -122.335167 )
                }
                function add(city, lon, lat) {
                    const esri = QtPositioning.coordinate( 34.0572522, -117.1945814 )
                    const coord = QtPositioning.coordinate( lon, lat )
                    const distance = coord.distanceTo( esri )
                    append( { city, coord, distance } )
                }
            }

            delegate: ItemDelegate {
                text: `${city} ${(distance / 1000).toFixed(2)} km`
            }
        }

        Button {
            text: qsTr("  City  ")
            onClicked: listModelSort( listModel,
                                      (a, b) => a.city.localeCompare(b.city) )
        }

        Button {
            text: qsTr("  City (Desc)  ")
            onClicked: listModelSort( listModel,
                                      (a, b) => - a.city.localeCompare(b.city) )
        }

        Button {
            text: qsTr("  Distance to Esri  ")
            onClicked: listModelSort( listModel,
                                      (a, b) => (a.distance - b.distance) )
        }

        Button {
            text: qsTr("  Distance to Esri (Desc)  ")
            onClicked: listModelSort( listModel,
                                      (a, b) => - (a.distance - b.distance) )
        }
    }

    function listModelSort(listModel, compareFunction) {
        let indexes = [ ...Array(listModel.count).keys() ]
        indexes.sort( (a, b) => compareFunction( listModel.get(a), listModel.get(b) ) )
        let sorted = 0
        while ( sorted < indexes.length && sorted === indexes[sorted] ) sorted++
        if ( sorted === indexes.length ) return
        for ( let i = sorted; i < indexes.length; i++ ) {
            listModel.move( indexes[i], listModel.count - 1, 1 )
            listModel.insert( indexes[i], { } )
        }
        listModel.remove( sorted, indexes.length - sorted )
    }
}

View the full SortingCities.qml on Github Gist.

 

 

3. ListModel vs Array

 

 

If you compare the ListModel QML Type with the Javascript Array type, you'll find that Array has better methods. In particular, Array has a sort method whilst ListModel does not.

 

When you assign an Array to a QML visual component, e.g. ListView, the Array behaves like a scalar value. If you were to push new records onto the Array the ListView will not update. There is no property binding between pushes to the Array and the ListView. To refresh the ListView you need to reassign the Array every time the Array changes. This will cause the ListView to repaint. This could lead to bad user experience. For instance, I may be writing an application that collates results from an online search. The results may arrive through a series of NetworkRequests to REST API end points. Each update will cause the ListView to repaint and scroll back to the top. This will be particularly annoying to the user if the user was already reading and scrolling through the results.

 

The experience is different with ListModels. When you assign a ListModel to a ListView you don't need to assign it again. When you append new records to the ListModel, the ListView will receive sufficient change notification and will automatically update to reflect that new records have arrived. If the ListModel were to change whilst the user was reading and scrolling through the ListView, the current scrolled position and the selected item will be unchanged. The user would not experience any negative user experience with the ListView.

 

In short, for good user experience, we prefer ListModels over Arrays.

 

 

4. Compare Functions as Arrow Functions

 

 

Array sort (and QML ListModel sort) requires a compare function. A compare function takes any two elements from the Array (or ListModel) and returns an integer. It can be negative, zero or positive. For example, if one wanted to compare any two numbers, one could write the following compare function:

 

function compareNumbers( a, b ) {
    return a - b
}

 

If a was greater than b then the function would return a positive number.

If a was the same as b then the function would return zero.

If a was less than b then the function would return a negative number.

 

Then, you would use the compare function as follows:

 

function compareNumbers( a, b ) {
    return a - b
}

let numbers = [ 5, 2, 11, 3, 7 ]
numbers.sort( compareNumbers )
console.log( numbers ) // [ 2, 3, 5, 7, 11 ]

 

If your compare function is simple, you can inline it with your sort call, i.e.

 

let numbers = [ 5, 2, 11, 3, 7 ]
numbers.sort( function compareNumbers( a, b ) { return a - b } )
console.log( numbers ) // [ 2, 3, 5, 7, 11 ]

 

In AppStudio 3.3, Qt 5.12.1 you can use the ECMAScript 6 arrow function syntax, i.e.

 

let numbers = [ 5, 2, 11, 3, 7 ]
numbers.sort( (a,b) => a - b )
console.log( numbers ) // [ 2, 3, 5, 7, 11 ]

 

You'll see the arrow function syntax allows us to omit the 'function' keyword, the function's name and the 'return' keyword. We make this choice if we believe such a choice improves our code readability and maintainability.


In the Sorting Cities sample app, I used the arrow function syntax for all 4 buttons, e.g.

 

Button {
    text: qsTr("  City  ")
    onClicked: listModelSort( citiesListModel,
                              (a, b) => a.city.localeCompare(b.city) )
}

 

 

5. Implementing listModelSort

 

 

There are plentiful sorting algorithms out there. Whilst we can implement one in QML / Javascript, I don't think this is a good idea. We would be implementing a sort algorithm that we would required to maintain. One of the best algorithms out there, Quicksort, is complex to transcribe correctly. Also such an implementation would be executing entirely in Javascript and will not be leveraging much from native code.


Instead, I wanted to leverage from Array sort's implementation. In a nutshell, the algorithm would be:

 

  1. Copy items from the QML ListModel to an Array
  2. Perform an Array sort
  3. Copy the resultant items from the sorted Array back to the QML ListModel

 

Turns out, we can optimized the above technique by not copying the items but linking to them via an index. i.e. The Array would be a set of integer indexes and we just be traversing it as a on the fly lookup into the QML ListModel.

 

Let's begin by creating an indexes Array the exact same size as our ListModel with numbers ranging from 0 to N - 1:

 

let indexes = [ ...Array(listModel.count).keys() ]

 

Then, we use Array's sort method to reorder the indexes Array. We use an arrow function to pick any two entries from the QML ListModel and feed them to the user supplied compare function:

 

indexes.sort( (a, b) => compareFunction( listModel.get(a), listModel.get(b) ) )

 

This reorders the indexes Array. We can now use this sorted Array to traverse the ListModel in sorted order.

 

Whilst we can stop here, I wanted to go further and use theses indexes to rearrange the QML ListModel.

 

for ( let i = 0; i < indexes.length; i++ ) {
    listModel.move( indexes[i], listModel.count - 1, 1 )
    listModel.insert( indexes[i], { } )
}
listModel.remove( 0, indexes.length )

 

The above loop iterates through the items in the ListModel in sorted order. For each item, we move it to the end of the ListModel in sorted order. Once all the items have been moved, we delete the empty holes left behind by the move. It's a heavy handed approach. In my final version I made an optimization that reduces the amount moving and deletion.

 

 

The animation above helps you visualize sorting the cities based on their distance to ESRI. We see it quickly identifies the sort order as Redlands, Los Angeles, Seattle, New York City and Hawaii. The records are tagged by an index 0...4. Next, the sorted records are moved, one by one, to the end, then the sorted records are moved back to the top deleting the temporary empty holes.

 

 

6. Closing Remarks

 

 

For good user interfaces I recommend storing your content in ListModels instead of Arrays. This is so that updates to the content will not interrupt the user experience.


Whenever you can, leverage from an existing implementation (e.g. Array sort) rather than implement your own. You minimize your coding effort and future maintenance efforts.

 

The ECMAScript 6 arrow function syntax can help you express simple sorting compare functions. Otherwise, if they're complex, then, then you should use a named compare function.

 

 

7. 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.

1. Javascript Promise

 

 

AppStudio 3.3, Qt 5.12.1 and ECMAScript 7 (which includes ECMAScript 6) introduces native Javascript Promises.

 

This blog is to give you a brief overview on what Promises are and how they matter to AppStudio and QML. This blog isn't a comprehensive guide to Promises. There already is a lot of content on the internet. I recommend you seek them out.

 

Promises is a Javascript coding pattern for working with asynchronous tasks.

 

AppStudio and QML already has signals and slots for working with asynchronous tasks. If your applications are clearly written and easy to maintain then you probably don't need to look into Promises right now.

 

However if your applications are complex with business logic spanning over a complex sequence of signals and slots then buying into Promises may be the key to reduce code complexity.

 

Note that the ArcGIS.AppFramework.Promises module is being deprecated in favour for native Javascript Promises. We advise to discontinue using ArcGIS.AppFramework.Promises module and begin removing them from your existing AppStudio apps.

 

 

2. Promise Download Sample

 

 

Let's look at an AppStudio app that simulates downloading content from the internet.

 

 

When the Button is clicked, we simulates 3 concurrent downloads. The downloads will finished 0-1000ms after the Button's onClicked handler has finished. Each download's success / failure will be independent of each other.

 

    Button {
        text: qsTr("Test Promise timeout")
        onClicked: {
            download("https://community.esri.com/groups/appstudio")
                .then( (message) => { console.log(message) } )
                .catch( (error) => { console.log(error) } )
            download("https://appstudio.arcgis.com")
                .then( (message) => { console.log(message) } )
                .catch( (error) => { console.log(error) } )
            download("https://community.esri.com/groups/survey123")
                .then( (message) => { console.log(message) } )
                .catch( (error) => { console.log(error) } )
        }
    }

    function download(url) {
        return new Promise( (resolve, reject) => {
            if (Math.random() < 0.30)
                setTimeout( reject, Math.floor(1000 * Math.random()), `Download failure ${url}` )
            else
                setTimeout( resolve, Math.floor(1000 * Math.random()), `Download success ${url}` )
        } )
    }

View the full PromiseTimeout.qml on GitHub Gist.

 

Some key features of Promises:

 

  • The task begins as soon as new Promise() { ... } is called.
  • The task is defined as (resolve, reject) => { /* body */ } where either resolve(data) or reject(error) will be called when the task completes.
  • Use then() method to supply your success handler.
  • Use catch() method to supply your failure handler.

 

You may be thinking "This looking confusing" and "Do I really need this?". I ask you to consider this: the Button's onClick handler fully describes 3 download tasks and what to do when each of them finishes. It describes this all this in one place, in the one function. If you didn't use Promises, we would have to create numerous signals and slots and spread the implementation throughout the application.

 

 

3. Promise chaining

 

 

Promise chaining is a coding pattern where you want to run your asynchronous tasks sequentially. You may need to do this if you wish to use the output of one task as an input to the next.

 

 

When the Button is clicked, we want to start the download of 3 web pages, one after the after. If downloads are successful, you will see the web page's title. If any task fails, we do not continue downloading. (N.B. here, the download function is real. The download function returns the web page's title).

 

    Button {
        text: qsTr("Test Promise chaining")
        onClicked: {
            download("https://community.esri.com/groups/appstudio")
            .then( (data) => {
                console.log(data)
                return download("https://appstudio.arcgis.com")
            } )
            .then( (data) => {
                console.log(data)
                return download("https://community.esri.com/groups/survey123")
            } )
            .then( (data) => {
                console.log(data)
            } )
            .catch( (error) => { console.log(error) } )
        }
    }

View the full PromiseChaining.qml on GitHub Gist.

 

However, due to the https://bugreports.qt.io/browse/QTBUG-71329, Promise chaining doesn't work in AppStudio 3.3, Qt 5.12.1. Please vote to have it fixed! Because of the bug, the app does not run correctly:

 

 

 

4. Promise nesting (alternative to Promise chaining)

 

 

Since Promise chaining doesn't work in AppStudio 3.3, Qt 5.12.1 lets explore some alternatives. This one demonstrates Promises nesting where each subsequent task is nested in the success of the previous task. (N.B. here the download function returns an object containing the status, responseText and title).

 

    Button {
        text: qsTr("Test Promise nesting")
        onClicked: {
            download("https://community.esri.com/groups/appstudio")
            .then( (data) => {
                console.log(data.title)
                download("https://appstudio.arcgis.com")
                .then( (data) => {
                    console.log(data.title)
                    download("https://community.esri.com/groups/survey123")
                    .then( (data) => {
                        console.log(data.title)
                    } )
                    .catch( (error) => { console.log(error) } )
                } )
                .catch( (error) => { console.log(error) } )
            } )
            .catch( (error) => { console.log(error) } )
        }
    }

View the full PromiseNesting.qml on GitHub Gist.

 

This implementation works, but it is extremely ugly. It is infamously known as callbackhell. You see each subsequent task incurs another level of indentation and another copy of the error handler.

 

 

5. Promise async/await (alternative to Promise chaining)

 

 

async/await is another alternative to Promise chaining. The main characteristic is the use of the new async and await keywords and that the main body reads sequentially and elegantly.

 

    Button {
        text: qsTr("Test Promise async/await")
        onClicked: {
            (async () => {
                try {
                    console.log((await download("https://community.esri.com/groups/appstudio")).title)
                    console.log((await download("https://appstudio.arcgis.com")).title)
                    console.log((await download("https://community.esri.com/groups/survey123")).title)
                } catch (error) {
                    console.log(error)
                }
            })()
        }
    }

View the full PromiseAsyncAwait.qml on GitHub Gist.

 

Unfortunately, this feature didn't make it to AppStudio 3.3, Qt 5.12.1. Help get this into a future release of AppStudio by voting for it at https://bugreports.qt.io/browse/QTBUG-58620.

 

 

6. Promise async/await Babel (alternative to Promise chaining)

 

 

Generator functions made it into AppStudio 3.3, Qt 5.12.1. We can use https://babeljs.io/ to convert the async/await into Generator functions. This version works in AppStudio 3.3, Qt 5.12.1. Here's the converted output:

 

    Button {
        text: qsTr("Test Promise async/await (Babel)")
        onClicked: {
            _asyncToGenerator(function*() {
                try {
                    console.log((yield download("https://appstudio.arcgis.com")).title)
                    console.log((yield download("https://community.esri.com/groups/appstudio")).title)
                    console.log((yield download("https://community.esri.com/groups/survey123")).title)
                } catch (error) {
                    console.log(error)
                }
            })()
        }
    }

View the full PromiseBabel.qml on GitHub Gist.

 

Whilst waiting for the async/await feature to be implemented, I think the above syntax is compelling.

It is extremely close to async/await syntax and, I think, is significantly better than Promise chaining.

 

To make it work, _asyncToGenerator() and asyncGeneratorStep() functions are required.

 


7. Promise.all and Promise.race

 

 

Promise.all is a special Promise that succeeds by waiting for every sub Promise to succeeds else it fails reporting the error from the first failing sub Promise.

 

 

Promise.race is a special Promise in that we are only interested in the first sub Promise that finishes the race. We aren't interested in the completion of the subsequent Promises. We will be notified the success message or failure error of that first sub Promise.

 

Surprisingly, no matter how many times you try, you find the AppStudio Home Page always wins the race.

 

 

Why is the second Promise always succeeding the race? That's because the community GeoNet page take longer to load!

 

 

8. Closing remarks

 

 

I hope this blog helps gives a good overview to Promises and why they matter in your AppStudio apps.

 

Promises, whilst new in AppStudio 3.3, Qt 5.12.1 is a feature that other Javascript developers have already been enjoying for sometime now.

 

Promises, I believe, work very well with Qt's signal and slot mechanism taking it to a whole new level.

 

Promise chaining and async/await functions do not work. We need your help to make AppStudio come closer to parity with other Javascript developer environments by voting on getting these two bugs fixed:

 

 

Whilst we wait for async/await functions, I highly recommend you use https://babeljs.io/ to get async/await features today.

 

 

9. 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.

1. Introduction

 

This blog talks about implementing your own setTimeout() function in AppStudio.

 

The blog also highlights ECMAScript 6 and ECMAScript 7 support in Qt 5.12.1 and AppStudio 3.3 features: arrow functions (my favourite), property shorthand, rest parameters and spread syntax.

 

setTimeout() is a function which executes another function or specified piece of code after waiting for the specified time delay in milliseconds.

 

The following will execute the following 4 console.log() after waiting 0 - 1000 milliseconds (determined by a random function). Each timer starts simulatenously but their completion times are random making the 4 console.log() appearing in random jumbled order.

 

    setTimeout( () => console.log("Hello"), randomDelay() )
    setTimeout( (a) => console.log(a), randomDelay(), 1 )
    setTimeout( (a, b) => console.log(a, b), randomDelay(), 21, 22 )
    setTimeout( (a, b, c) => console.log(a, b, c), randomDelay(), 31, 32, 33 )

 

randomDelay() is a function that computes a random time delay from the range of 0 – 1000 milliseconds:

 

    function randomDelay() {
        return Math.floor(1000 * Math.random())
    }

 

Here is the console output:

 

 

 

2. AppStudio implementation

 

setTimeout() is a part of other Javascript development environments, e.g. in Web development and Node.js. It exists globally on the window object.

 

However, in Qt and AppStudio, there isn’t an equivalent window object or setTimeout() function.

 

To bring over such a capability you need to implement it yourself.

 

Here is a sample AppStudio app that includes a simple implementation of setTimeout(). Everytime the button is clicked the 4 setTimeout() functions are triggered with the 4 resulting console.log() appearing in random order.

 

import QtQuick 2.12
import QtQuick.Controls 2.5
import ArcGIS.AppFramework 1.0

App {
    id: app

    width: 400 * AppFramework.displayScaleFactor
    height: 640 * AppFramework.displayScaleFactor

    StackView {
        id: stackView

        anchors.fill: parent

        initialItem: Item {
            Button {
                text: qsTr("Test")
                onClicked: {
                    setTimeout( () => console.log("Hello"), randomDelay() )
                    setTimeout( (a) => console.log(a), randomDelay(), 1 )
                    setTimeout( (a, b) => console.log(a, b), randomDelay(), 21, 22 )
                    setTimeout( (a, b, c) => console.log(a, b, c), randomDelay(), 31, 32, 33 )
                }
            }
        }
    }

    function randomDelay() {
        return Math.floor(1000 * Math.random())
    }

    function setTimeout(func, interval, ...params) {
        return setTimeoutComponent.createObject(app, { func, interval, params } )
    }

    function clearTimeout(timerObj) {
        timerObj.stop()
        timerObj.destroy()
    }

    Component {
        id: setTimeoutComponent
        Timer {
            property var func
            property var params
            running: true
            repeat: false
            onTriggered: {
                func(...params)
                destroy()
            }
        }
    }
}

 

Disclaimer: this is a minimal implementation of setTimeout() not a fully functioning implementation. It is here for the purposes of this blog, specifically to talk about aspects of ECMAScript. If you want to use this implementation you will need to adapt it accordingly.

 

This implementation of setTimeout() returns a Timer object (instead of a timerId). When the time delay specified in milliseconds has been reached the nominated Javascript function is triggered with the parameters you supplied to setTimeout(). If you wish to abort the action, you can passed the Timer object to the clearTimeout() function.

 

 

This implementation will now be deconstructed:

  • Global functions in AppStudio
  • ECMAScript 6 Arrow Functions
  • ECMAScript 6 Property shorthand
  • ECMAScript 6 Rest parameter and Spread syntax

 

3. Global functions in AppStudio

 

Functions implemented in the top level component (here, it was App) will be seen by all child components. In this example, we have a StackView which means, all pages that are pushed to the StackView will have access to the setTimeout() function.

 

4. Arrow functions

 

The first parameter to setTimeout() refers to a function or code. In our examples, we have been using the ECMAScript 6 arrow function syntax for anonymous functions. e.g. the arrow function (a, b, c) => console.log(a, b, c) appears in the following snippet:

 

setTimeout( (a, b, c) => console.log(a, b, c), randomDelay(), 31, 32, 33)

 

When used appropriately, arrow functions can help improve code readablity and maintainability.

 

For comparison, let's look at equivalent implementation to the above arrow function.

 

 

4.1 Original Anonymous functions

 

Prior to ECMAScript 6, we could always do anonymous functions, but the syntax was somewhat cumbersome with the required use of the function keyword:

 

setTimeout( function (a, b, c) { console.log(a, b, c) }, randomDelay(), 31, 32, 33 )

 

 

4.2 Using a named function

 

Sometimes, to avoid the use of the anonymous function syntax, I find it better to name things clearly and space out my code for increase readability and maintainability:

 

function testFunction(a, b, c) {
    console.log(a, b, c)
}

setTimeout(testFunction, randomDelay(), 31, 32, 33)

 

4.3 Calling console.log directly

 

For completeness, we recognize that console.log, is, itself, a function reference, so, we can supply this directly to the setTimeout() function.

 

setTimeout(console.log, randomDelay(), 31, 32, 33)

 

 

5. Property shorthand syntax

 

ECMAScript 6 introduces a property shorthand syntax for object creation.

In the following snippets, compare the code for both obj1 and obj2.

They both yield the same value, but the code for obj2 is much shorter and easier to read.

 

 

var x = 11
var y = 22
var z = 33
var obj1 = { x: x, y: y, z: z } // obj1 = { x: 11, y: 22, z: 33 }
var obj2 = { x, y, z } // obj2 = { x: 11, y: 22, z: 33 }

 

We used the shorthand syntax to simplify the following

 

setTimeoutComponent.createObject(app, { func:func, internval:interval, args:args } )

 

into:

 

setTimeoutComponent.createObject(app, { func, interval, args } )

 

 

6. Rest parameter and Spread syntax

 

ECMAScript 6 also introduces rest parameters and spread syntax.

We used it in the declaration of the setTimeout function to pick up all remaining parameters as an array.

 

function setTimeout(func, interval, ...params) {

 

when called with:

 

setTimeout( (a, b, c) => console.log(a, b, c), randomDelay(), 31, 32, 33)

 

results in:

 

func = (a, b, c) => console.log(a, b, c)
interval = randomDelay()
params = [ 31, 32, 33 ]

 

When calling func we passed the parameters using the spread syntax,  i.e.

 

func(...params)

 

which passes the parameter array as individual parameters to the function, i.e.

 

func(31, 32, 33)

 

which ultimate calls

 

console.log(31, 32, 33)

 

Closing Remarks

 

This blog discusses an AppStudio implementation of setTimeout(). It was used as a way to give you a glimpse of some of the new ECMAScript 6 features, i.e. arrow functions, property shorthand and rest parameters and spread syntax which you can utilize in your AppStudio apps.

 

I plan to talk about Promises in ECMAScript 6 and AppStudio in a future blog. That blog will reuse the setTimeout() function and arrow functions introduced here.

 

In the meantime, please help us improve Promises in AppStudio by voting on fixing Promise chains bug https://bugreports.qt.io/browse/QTBUG-71329 and by voting on implementing async/await support in https://bugreports.qt.io/browse/QTBUG-58620

 

 

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.

1. Introduction

 

This amazing clipart can be produced with a surprising short amount of code:

 

 

 

Text {
    text: "\u{1f304}"
    font.pointSize: 128
}

View EmojiSunrise.qml on GitHub Gist.

 

This blog talks about Unicode and the wonderful emojis and cliparts that are hidden within. It also covers some new syntax introduced in AppStudio 3.3, Qt 5.12.1 and ECMAScript 7 (which includes ECMAScript 6).

 

2. Use Unicode Clipart to rapidly create UI

 

You can use Unicode emojis and clipart in Buttons and MenuItems:

 

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

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

    Button {
        id: menuButton
        anchors.right: parent.right
        anchors.margins: 10
        text: "\u{2630}"
        onClicked: menu.open()
    }

    Menu {
        id: menu
        x: menuButton.x + menuButton.width - menu.width
        y: menuButton.y + menuButton.height
        MenuItem { text: "\u{1f4c2}  Open" }
        MenuItem { text: "\u{1f4be}  Save" }
        MenuItem { text: "\u{1f5a8}  Print" }
        MenuItem { text: "\u{2702}  Cut" }
        MenuItem { text: "\u{1f4cb}  Paste" }
        MenuItem { text: "\u{1f4c5}  Calendar" }
        MenuItem { text: "\u{2699}  Settings" }
        MenuItem { text: "\u{1f6aa}  Exit" }
    }
}

 

 

 

You can use emojis to improve text shown to the user:

 

import QtQuick 2.7
import ArcGIS.AppFramework 1.0

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

    ListView {
        anchors.fill: parent
        model: [
            '  \u{2139}  "Do. Or do not. There is no try." — Yoda',
            '  \u{26a0}  "It’s not wise to upset a Wookiee." - Han Solo',
            '  \u{1f534}  "It’s a trap!" - Admiral Ackbar'
        ]
        delegate: Text { text: modelData }
    }
}

 

 

3. Where is the list of emojis?

 

Using the String.fromCodePoint(codePoint) you can display any Unicode characters base on its code point number. You can use this code pattern to iterate code points searching for interesting emojis and clipart. The following shows the emojis and clipart from 1F300 to 1F3FF.

 

import QtQuick 2.7
import ArcGIS.AppFramework 1.0

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

    property int startCodePoint: 0x1f300
    property int endCodePoint: 0x1f3ff
    property int codePoint: startCodePoint

    Text {
        anchors.centerIn: parent
        text: String.fromCodePoint(codePoint)
        font.pointSize: 64
    }

    Timer {
        id: animTimer
        repeat: true
        running: true
        interval: 100
        onTriggered: codePoint = (codePoint < endCodePoint) ? codePoint + 1 : startCodePoint
    }
}

 

You can also use online lists to search for Unicode emojis and clipart, i.e. "sunrise over mountains" can be found in the following lists:

 

https://unicode.org/emoji/charts/full-emoji-list.html#1f304

https://emojipedia.org/sunrise-over-mountains/

 

Some interesting Unicode blocks are 2600 - 2BFF and 1F300 - 1F8FF.

 

For a listing of Unicode blocks refer to: https://en.wikipedia.org/wiki/Unicode_block

 

4. Character vs Code Point

 

A character is represented internally as a 16 bit number which means there can be a total of 65536.

 

Unicode allows for 17 planes, each of 65,536 characters. This gives a total of 1,114,112 possible characters (i.e. 0x to 0x10ffff).

 

If a Unicode Code Point is within a 16 bit number, it will only require 1 character to represent, otherwise, we will need two characters.

 

For instance, in AppStudio 3.2, Qt 5.11.2 and ES 5, we needed two characters to represent the sunrise over mountains emoji, i.e. "\ud83c\udf04".

 

The code point syntax "\u{1f304}" in AppStudio 3.3, Qt 5.12.1 and ECMAScript 7 (which includes ECMAScript 6) is new. It actually expands into the two characters that make it. The String.fromCodePoint() function is also new.


"\u{1f304}" === String.fromCodePoint(0x1f304)

 

we can always convert code point back into their numbers with:

 

"\u{1f304}".codePointAt(0) === 0x1f304

 

We can also convert code points to characters:

 

"\u{1f304}".length === 2
"\u{1f304}".charCodeAt(0) === 0xd83c
"\u{1f304}".charCodeAt(1) === 0xdf04
"\u{1f304}" === "\u{d83c}\u{df04}"

 


 

5. Caveats

 


5.1 Not all clipart is available or consistent cross platform

 


When viewing Unicode clipart on Windows 10 you find a set of Mahjong tiles at 1F000 - 1F02B. You might be inclined to use them in a Mahjong app:

 

 

However, when you check on other platforms, you may quickly change your mind. On Linux, we get the complete set of clipart, but, in black and white. On macOS, all of the characters appear black and white except for Mahjong Red Dragon appears in colour. On iOS, only the Mahjong Red Dragon appears whereas all other characters are unavailable. On Android, only the Mahjong Red Dragon tile is available there too. Also note the appearance of the no data tile is different on every platform.

 

On Linux:

 

 

On macOS:

 

 

On iOS:

 

 

On Android:

 

 

5.2 Issue with some CodePoints on Buttons

 

In the following code snippet we try to render the same text in Text and Button components:

 

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

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

    Column {
        Text {
            text: "\u{263a} \u{1f304} "
            font.pointSize: 32
        }

        Button {
            text: "\u{263a} \u{1f304} "
            font.pointSize: 32
            font.capitalization: Font.MixedCase
        }
    }
}

 

we see that the smiley and the face emoji renders okay on both the Text and the Button

 

 

However, the Button requires a workaround that disables the material design capitalization on Buttons. This is due to a bug in Qt 5.12.x reported in https://bugreports.qt.io/browse/QTBUG-75559. Help get this bug fix by voting on it.

 

 

Summary

 

You can find a lot of clipart or emojis that help deliver high visual impact for little expense in code.

 

Caveats are not every platform offers the same set of clipart or emojis nor are they consistent in appearance on the ones that they do offer.

 

Cross platform testing is essential.

 

 

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.

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