appstudio-sqltablemodel-sample.qml

appstudio-sqltablemodel-sample.qml

// This sample demonstrates creation, populating and querying a SQLite database.
// When run the TableView component is populated with a data from the Cities table.
//
// Melbourne, Australia, -37.9716929, 144.7729583
// London, UK, 51.5287718, -0.2416804
// Paris, France, 48.8589507, 2.2770205
// New York City, USA, 40.6976637, -74.1197639
// Tokyo, Japan, 35.6735408, 139.5703047

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

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

    TableView {
        id: tableView

        anchors.fill: parent

        horizontalScrollBarPolicy: Qt.ScrollBarAlwaysOn
        flickableItem.flickableDirection: Flickable.HorizontalAndVerticalFlick

        headerDelegate: TextField {
            text: styleData.value
            readOnly: true
            font.pointSize: 10
            background: LinearGradient {
                start: Qt.point(0, 0)
                end: Qt.point(0, height)
                gradient: Gradient {
                    GradientStop { position: 0.0; color: "white" }
                    GradientStop { position: 1.0; color: "#e0e0e0" }
                }
            }
        }

        rowDelegate: Rectangle {
            color: styleData.selected ? "#0077CC" : styleData.row & 1 ? "white" : "#f5f5f5"
            height: 20 * AppFramework.displayScaleFactor
        }

        itemDelegate: Text {
            text: styleData.value
            color: styleData.textColor
            elide: styleData.elideMode
        }

        Component {
            id: columnComponent
            TableViewColumn { width: 100 * AppFramework.displayScaleFactor }
        }

        function setColumnsByModel(model) {
            while (columnCount > 0)
                removeColumn(0);
            model.roleNames.forEach(addColumnByName);
        }

        function addColumnByName(name) {
            addColumn(columnComponent.createObject(tableView, { role: name, title: capitalize(name) } ));
        }
    }

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

    function toUpperCase(c) { return c.toUpperCase(); }
    function capitalize(str) { return str.replace(/^./, toUpperCase); }

    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, latitude REAL, longitude REAL ); ");
        db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "Melbourne", country: "Australia", latitude: -37.9716929, longitude: 144.7729583 } );
        db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "London", country: "UK", latitude: 51.5287718, longitude: -0.2416804 } );
        db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "Paris", country: "France", latitude: 48.8589507, longitude: 2.2770205 } );
        db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "New York City", country: "USA", latitude: 40.6976637, longitude: -74.1197639 } );
        db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "Tokyo", country: "Japan", latitude: 35.6735408, longitude: 139.5703047 } );
        tableView.model = sqlTableModel = db.tableModel("Cities");
        tableView.setColumnsByModel(sqlTableModel);
    }
}