appstudio-sqldatabase-sample.qml

appstudio-sqldatabase-sample.qml

// This sample demonstrates creation, populating and querying a SQLite database.
// When run, a TextArea component, displays cities retrieved from the database:
//
// Melbourne, Australia - 4.2M - (-37.9717, 144.773)
// London, UK - 8788.0M - (51.5288, -0.24168)
// Paris, France - 2.2M - (48.859, 2.27702)
// New York City, USA - 8538.0M - (40.6977, -74.1198)
// Tokyo, Japan - 13.6M - (35.6735, 139.57)

import QtQuick 2.9
import QtQuick.Controls 2.3
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Sql 1.0

Item {
    property FileFolder sqlFolder: AppFramework.userHomeFolder.folder("ArcGIS/Data/Sql")

    TextArea {
        id: textArea
        width: parent.width
        selectByMouse: true
        wrapMode: Text.WrapAtWordBoundaryOrAnywhere
    }

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

    Component.onCompleted: {
        sqlFolder.makeFolder();
        db.open();
        db.query("DROP TABLE IF EXISTS Cities");
        db.query("CREATE TABLE IF NOT EXISTS Cities ( name TEXT, country TEXT, population INT, latitude REAL, longitude REAL ); ");
        db.query("INSERT INTO Cities VALUES (:name, :country, :population, :latitude, :longitude) ", { name: "Melbourne", country: "Australia", population: 4186000, latitude: -37.9716929, longitude: 144.7729583 } );
        db.query("INSERT INTO Cities VALUES (:name, :country, :population, :latitude, :longitude) ", { name: "London", country: "UK", population: 8788000000, latitude: 51.5287718, longitude: -0.2416804 } );
        db.query("INSERT INTO Cities VALUES (:name, :country, :population, :latitude, :longitude) ", { name: "Paris", country: "France", population: 2241000, latitude: 48.8589507, longitude: 2.2770205 } );
        db.query("INSERT INTO Cities VALUES (:name, :country, :population, :latitude, :longitude) ", { name: "New York City", country: "USA", population: 8538000000, latitude: 40.6976637, longitude: -74.1197639 } );
        db.query("INSERT INTO Cities VALUES (:name, :country, :population, :latitude, :longitude) ", { name: "Tokyo", country: "Japan", population: 13617000, latitude: 35.6735408, longitude: 139.5703047 } );
        var query = db.query("SELECT * FROM Cities");
        var ok = query.first();
        while (ok) {
            var city = query.values;
            textArea.text = textArea.text + "%1, %2 - %3M - (%4, %5)\n"
                .arg(city.name)
                .arg(city.country)
                .arg((city.population / 1000000.0).toFixed(1))
                .arg(city.latitude)
                .arg(city.longitude);
            ok = query.next();
        }
        query.finish();
    }
}