appstudio-sqlindex-sample.qml

appstudio-sqlindex-sample.qml

// This sample demonstrates how we can programmatically extract the primary key and indexes for a table.

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

Item {
    TextArea {
        id: textArea
        anchors.fill: parent
        anchors.margins: 10
        selectByMouse: true
        function log(txt) { text = text + txt + "\n"; }
    }

    SqlDatabase {
        id: db
        databaseName: ":memory:"
    }
    
    Component.onCompleted: {
        db.open();
        db.query( [
            "CREATE TABLE Assets ",
            "( ",
            "  Asset_Id     INTEGER NOT NULL, ",
            "  Longitude    REAL    NOT NULL, ",
            "  Latitude     REAL    NOT NULL, ",
            "  Name         TEXT    NOT NULL, ",
            "  Installation REAL    NOT NULL, ",
            "  Constraint PK_Assets PRIMARY KEY (Asset_Id) ",
            ") "
        ].join("\n"));
        db.query("CREATE INDEX IX_Assets_Coordinate On Assets (Longitude, Latitude) ");
        db.query("CREATE INDEX IX_Assets_Name On Assets (Name); ");
        var table = db.table("Assets");
        var primaryKey = table.primaryKey;
        textArea.log("primaryKey.count: %1".arg(primaryKey.count));
        textArea.log("primaryKey.fieldName(0): %1".arg(primaryKey.fieldName(0)));
        // The expected output from above is:
        // primaryKey.count: 1
        // primaryKey.fieldName(0): Asset_Id
        
        // Workaround using PRAGMA INDEX_LIST to retrieve information not currently available in SqlIndex.
        var indexList = db.query( "PRAGMA INDEX_LIST('Assets')");
        var okIndexList = indexList.first();
        while (okIndexList) {
            textArea.log("indexList: %1".arg(JSON.stringify(indexList.values)));
            var indexInfo = db.query( "PRAGMA INDEX_INFO('%1')".arg(indexList.values.name) );
            var okIndexInfo = indexInfo.first();
            while (okIndexInfo) {
                textArea.log("indexInfo: %1".arg(JSON.stringify(indexInfo.values)));
                okIndexInfo = indexInfo.next();
            }
            indexInfo.finish();
            okIndexList = indexList.next();
        }
        indexList.finish();
        // The expected output from the workaround.
        // indexList: {"name":"IX_Assets_Name","origin":"c","partial":0,"seq":0,"unique":0}
        // indexInfo: {"cid":3,"name":"Name","seqno":0}
        // indexList: {"name":"IX_Assets_Coordinate","origin":"c","partial":0,"seq":1,"unique":0}
        // indexInfo: {"cid":1,"name":"Longitude","seqno":0}
        // indexInfo: {"cid":2,"name":"Latitude","seqno":1}
    }
}