ArcGIS for Developers

SqlIndex QML Type

Provides functions to describe database indexes. More...

Import Statement: import ArcGIS.AppFramework.Sql 1.0

Properties

Signals

Methods

Detailed Description

The SqlIndex component provides functions to manipulate and describe database indexes. An index refers to a single table or view in a database. Information about the fields that comprise the index can be used to generate SQL statements.

The following code snippet uses SQL CREATE TABLE to create a table called "Assets". It then determines, in QML, the primary key "PK_Assets" whose field is "Asset_Id" by using SqlDatabase, SqlTable and SqlIndex.

There is currently a limitation preventing SqlIndex from determining all indexes in a table. As a workaround, you can use SqlQuery and a SQL statement specific for your database type. For example, in the case of SQLite this information can be obtained with PRAGMA INDEX_LIST and INDEX_INFO.

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

Property Documentation

[read-only] count : int

Returns the number of fields in the index.


Signal Documentation

changed()

Signal emitted when the SqlIndex object called on by SqlTable's primaryKey property has changed.


Method Documentation

SqlIndexField field(name)

Returns the field called the given name.

The name parameter

The name of the field to return.


SqlIndexField field(index)

Returns the field at the given index name. If the index is out of range, this method returns a default-constructed value.

The index parameter

The index number of the field to return.


string fieldName(index)

Returns the name of the field at the given index position. If the field does not exist, this method returns an empty string.

The index parameter

THe index position of the field to return the name of.


int indexOf(name)

Returns the position of the field with the given name within the index. If no field with this name can be found, returns -1. If more than one field name matches, the first one is returned. Be aware that field names are not case-sensitive.

The name parameter

The name of the field to return the position of.