SqlIndex QML Type

Provides functions to describe database indexes. More...

• List of all members, including inherited members

Properties

• count : int

Signals

• changed()

Methods

• SqlIndexField field(string name)
• SqlIndexField field(int index)
• string fieldName(int index)
• int indexOf(string name)

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

Signal Documentation

Method Documentation