appstudio-sqlaggregatefunction-sample.qml

appstudio-sqlaggregatefunction-sample.qml

// appstudio-sqlaggregatefunction-sample.qml
//
// Sample demonstrating how to use SqlAggregateFunction.

import QtQuick 2.8
import QtQuick.Layouts 1.3
import QtQuick.Controls 1.4
import QtQuick.Controls 2.1
import QtGraphicalEffects 1.0
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Sql 1.0

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

    property var averageArea

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

        spacing: 10 * AppFramework.displayScaleFactor

        TableView {
            id: tableView

            property int rowHeight: 30
            
            Layout.fillWidth: true
            Layout.fillHeight: true
            
            horizontalScrollBarPolicy: Qt.ScrollBarAlwaysOn

            headerDelegate: TextField {
                text: styleData.value
                readOnly: true
                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
            }

            flickableItem.flickableDirection: Flickable.HorizontalAndVerticalFlick

            TableViewColumn {
                role: "area"
                title: qsTr("Area")
                width: 200 * AppFramework.displayScaleFactor
            }
        }

        Text {
            text: qsTr("Average Area: %1").arg(averageArea)
        }
    }

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

        SqlAggregateFunction {
            name: "average"
            initialize: average_initialize
            iterate: average_iterate
            finalize: average_finalize
        }
    }

    Component.onCompleted: {
        db.open();
        db.query("CREATE TABLE Parcel ( AREA real ); ");
        db.query("INSERT INTO Parcel VALUES (:area) ", { area: 110.0 } );
        db.query("INSERT INTO Parcel VALUES (:area) ", { area: 130.0 } );
        db.query("INSERT INTO Parcel VALUES (:area) ", { area: 170.0 } );
        db.query("INSERT INTO Parcel VALUES (:area) ", { area: 190.0 } );
        tableView.model = db.tableModel("Parcel");
        var query = db.query("SELECT average(AREA) as avg FROM Parcel ");
        var ok = query.first();
        if (ok) {
            averageArea = query.values.avg; // 150.0
        }
        query.finish();
    }

    function average_initialize() {
        var context = {
            sum: 0,
            count: 0
        }
        return context;
    }

    function average_iterate(context, value) {
        context.sum += value;
        context.count++;
    }

    function average_finalize(context) {
        return context.count ? context.sum / context.count : Number.NaN;
    }
}