SqlDataQuery

Since: BlackBerry 10.2.0

#include <bb/cascades/datamanager/SqlDataQuery>

To link against this class, add the following line to your .pro file: LIBS += -lbbcascadesdatamanager

The default DataQuery implementation that uses an SQL select to retrieve data.

Once the properties are set they cannot be changed. The query is normally executed in a secondary thread and allowing changes to these properties once the query is in operation would cause unpredictable results.

To implement this class, you must decide how to handle error conditions. If there are SQL or other expected or unexpected runtime errors, you must determine which of these errors are catastrophic and which are recoverable. See the DataQuery class description for error handling guidelines.

Here is a QML example showing the usage of an SqlDataQuery:
import bb.cascades 1.2
import bb.cascades.datamanager 1.2
Page {
    content: Container {
        layout: StackLayout {
        }
        ListView {
            id: myListView
            dataModel: dm
            listItemComponents: [
                ListItemComponent {
                    StandardListItem {
                        title: ListItemData.firstname + " " + ListItemData.lastname
                        imageSource: ListItemData.image
                        description: ListItemData.title
                    }
                }
            ]
        }
        attachedObjects: [
            AsyncDataModel {
                id: dm
                // an example to demonstrate how to use an SqlDataQuery
                query: SqlDataQuery {
                    // the following are mandatory
                    source: "sql/contacts1k.db"
                    query: "select id, firstname, lastname, title, image, active, revision_id from contact " +
                           "where lastname like :filterLastname order by lastname, firstname"

                    // the following is: mandatory here; not used in SimpleQueryDataModel
                    countQuery: "select count(*) from contact where lastname like :filterLastname"

                    // bindValues can be used for all queries
                    bindValues: {
                        "filterLastname": "C%"
                    }

                    // the following are optional but provide improved results (see developer documentation)
                    keyColumn: "id"
                    revisionColumn: "revision_id"
                    revisionQuery: "select revision_id from revision"

                    // the following is optional but recommended for feedback when errors occur
                    onError: console.log("SQL query error: " + code + ", " + message)
                }
                onLoaded: console.log("initial model data is loaded")
            }
        ]
    }
    onCreationCompleted: {
        dm.load();
    }
}
Here is a C++ example showing the usage of an SqlDataQuery:
// 1. Create an SqlDataQuery and set its properties
SqlDataQuery *sdq = new SqlDataQuery();

// ...the following are mandatory
sdq->setSource(QDir::currentPath() + "/app/native/assets/sql/contacts1k.db");
sdq->setQuery("select id, firstname, lastname, title, image, active, revision_id from contact "
              "where lastname like :filterLastname order by lastname, firstname");

// ...the following is: mandatory here; not used in SimpleQueryDataModel
sdq->setCountQuery("select count(*) from contact where lastname like :filterLastname");

// ...bindValues can be used for all queries
QVariantMap valuesMap;
valuesMap["filterLastname"] = "C%";
sdq->setValuesToBind(valuesMap);

// ...the following are optional but provide improved results (see developer documentation)
sdq->setKeyColumn("id");
sdq->setRevisionColumn("revision_id");
sdq->setRevisionQuery("select revision_id from revision");

// ...the following is optional but recommended for feedback when errors occur
connect( sdq, SIGNAL(error(int, const QString&)),
        this, SLOT(onError(int, const QString&)) );


// 2. Create a data model and set its data query
AsyncDataModel *model = new AsyncDataModel();
model->setQuery(sdq);


// 3. Create a list view control and set its data model
ListView *listView = new ListView();
listView->setDataModel(model);


Overview

Inheritance

bb::cascades::datamanager::DataQuery
bb::cascades::datamanager::SqlDataQuery

QML properties

bindValues: QVariantMap
countQuery: QString
keyColumn: QString
query: QString
revisionColumn: QString
revisionQuery: QString
source: QUrl

QML signals

Only has inherited QML signals

onDataChangedInherited: {}
onErrorInherited: {}

Public Functions Index

SqlDataQuery (QObject *parent=0)
SqlDataQuery (const QString &query, QObject *parent=0)
virtual ~SqlDataQuery ()
QStringcountQuery () const
Q_SLOT voidemitDataChanged (int revision)
virtual boolgetData (int offset, int limit, DataRevision *revision, int *totalCount, QList< DataItem > *results)
virtual boolgetDataForRevision (int offset, int limit, const DataRevision &requestedRevision, QList< DataItem > *results)
QStringkeyColumn () const
QStringquery () const
QStringrevisionColumn () const
QStringrevisionQuery () const
voidsetCountQuery (const QString &countQuery)
voidsetKeyColumn (const QString &keyColumn)
voidsetQuery (const QString &query)
voidsetRevisionColumn (const QString &revisionColumn)
voidsetRevisionQuery (const QString &revisionQuery)
voidsetSource (const QUrl &source)
voidsetValuesToBind (const QVariantMap &nameValueMap)
QUrlsource () const
virtual QStringtoString () const
QVariantMapvaluesToBind () const
DataQuery (QObject *parent=0)Inherited

Signals Index

Only has inherited signals

voiddataChanged (DataRevision revision)Inherited
voiderror (int code, const QString &message)Inherited

Properties

QVariantMap bindValues

A map of name-to-value bindings.

Since:

BlackBerry 10.2.0

QString countQuery

An SQL query statement that returns the total count of items.

This property is mandatory when the query is used for async models. It's needed to obtain the total count of database items even when the model retains only a partial cache in memory.

Here is an example:
 "SELECT count(*) FROM contacts"

Once the property is set, it cannot be changed.

Since:

BlackBerry 10.2.0

QString keyColumn

The name of the key column in the main query which is returned for each item.

This key, if returned for each DataItem by the main query, uniquely identifies the data item. It's used by the data model to signal listeners (usually an associated list) that items have changed location or have been deleted.

Use of this property is optional, however, without keys, adding and deleting items in the database may result in poor performance of user interface visual updates.

Once the property is set, it cannot be changed.

Here is an example query:
 "SELECT key_id, revision_id, lastname, firstname FROM contacts"
with the keyColumn: "key_id"
See also:

DataItem

Since:

BlackBerry 10.2.0

QString query

The main SQL query statement.

This property is mandatory.

Once the property is set, it cannot be changed.

Since:

BlackBerry 10.2.0

QString revisionColumn

The name of the revision column in the main query which is returned for each item.

This revision, if returned for each DataItem by the main query, identifies the current state of that item. It's used in conjunction with the overall revision. When a database item is updated, its revision should be updated and the overall database revision should be updated.

This property is used to determine when items must be updated in any cached data in memory. Use of this property is optional, however, without item revisions, database changes may result in poor performance of user interface visual updates.

Once the property is set, it cannot be changed.

Here is an example query:
 "SELECT key_id, revision_id, lastname, firstname FROM contacts"
with the revisionColumn: "revision_id"
See also:

DataItem

Since:

BlackBerry 10.2.0

QString revisionQuery

An SQL query statement to return the current overall revision for the source.

This revision represents the current state of the database. It's used to ensure that data for different database states is not mixed in memory. If the data model determines that the overall revision of the data has changed, any cached data is refreshed by querying the data source again.

Use of this property is optional, however, without an overall revision, database queries will fully refresh the cache so that the data can be guaranteed to be consistent.

Here is an example:
 "SELECT revision_id FROM revision"

Once the property is set, it cannot be changed.

Since:

BlackBerry 10.2.0

QUrl source

The source URL for the path to the local database.

This property is mandatory.

Once the property is set, it cannot be changed.

Since:

BlackBerry 10.2.0

Public Functions

SqlDataQuery (

Constructs an SqlDataQuery.

Parameters
parent

The parent owner or 0. Optional and defaults to 0 if not specified.

Since:

BlackBerry 10.2.0

SqlDataQuery (

Constructs an SqlDataQuery given an SQL query statement.

Parameters
query

The main SQL query statement to use for this data query.

parent

The parent owner or 0. Optional and defaults to 0 if not specified.

Since:

BlackBerry 10.2.0

virtual~SqlDataQuery ()

Destructor.

Since:

BlackBerry 10.2.0

QString countQuery ()

Get the count query.

Return:

The countQuery string.

Since:

BlackBerry 10.2.0

Q_SLOT void emitDataChanged (
  • intrevision)

Emit the signal for data changes if using the default NumericRevision.

This method can be called from QML to notify the containing model of a source revision change.

Parameters
revision

The numeric revision of the latest source data.

Since:

BlackBerry 10.2.0

virtual bool getData (

Fetch the overall revision, total count, and a range of data items from the data source.

This is a synchronous call, but it must be possible to safely call this method from any thread. This method emits an error() signal and returns false if the data could not be successfully retrieved due to a query error, source not found, or other unexpected condition.

For more information about error handling, see the DataQuery class description. A return of false by this method should be preceded by the query emitting an error signal. This signal should be handled at the application level, leading to some appropriate action.

Parameters
offset

The index offset within the view.

limit

The number of items to retrieve.

revision

The current revision of the data source. The revision value must match the state of the data items that are returned. This pointer must not be null.

totalCount

The total number of items in the view. This pointer must not be null.

results

The list of data items which is the return data. This pointer must not be null.

Return:

True if the data is successfully retrieved, false otherwise.

Since:

BlackBerry 10.2.0

virtual bool getDataForRevision (

Fetch the data items from the data source if the data source is at the requested revision.

This is a synchronous call, but it must be safe to call this method from any thread. The error() signal is emitted on any database and query related errors only. The error() signal is not emitted if the data source doesn't match the requested revision; instead this method returns false.

For more information about error handling, see the DataQuery class description. A return of false by this method should be preceded by the query emitting an error signal. This signal should be handled at the application level, leading to some appropriate action.

Note:

The return of false by this method is always expected and not a catastrophic error since false is returned when the data source doesn't match the requested revision. The error() signal should be emitted on database and query related errors only.

Parameters
offset

The index offset within the view.

limit

The number of items to retrieve.

requestedRevision

The requested revision ID for the data source. If the data source doesn't match this revision, this method returns false.

results

The list of data items which is the return data. This pointer must not be null.

Return:

True if the data is successfully retrieved at the requested revision, false otherwise.

Since:

BlackBerry 10.2.0

QString keyColumn ()

Get the name of the key column in the main query.

Return:

The keyColumn name.

Since:

BlackBerry 10.2.0

QString query ()

Get the query.

Return:

The query string.

Since:

BlackBerry 10.2.0

QString revisionColumn ()

Get the name of the revision column in the main query.

Return:

The revisionColumn name.

Since:

BlackBerry 10.2.0

QString revisionQuery ()

Get the revision query.

Return:

The revisionQuery string.

Since:

BlackBerry 10.2.0

void setCountQuery (

Set the count query string.

Parameters
countQuery

The count query string.

Since:

BlackBerry 10.2.0

void setKeyColumn (

Set the name of the key column in the main query.

Parameters
keyColumn

The key column name.

Since:

BlackBerry 10.2.0

void setQuery (

Set the query string.

This property is mandatory.

Parameters
query

The query string.

Since:

BlackBerry 10.2.0

void setRevisionColumn (

Set the name of the revision column in the main query.

Parameters
revisionColumn

The revision column name.

Since:

BlackBerry 10.2.0

void setRevisionQuery (

Set the overall revision query string.

Parameters
revisionQuery

The revision query string.

Since:

BlackBerry 10.2.0

void setSource (

Set the source URL.

This property is mandatory.

Parameters
source

The source URL.

Since:

BlackBerry 10.2.0

void setValuesToBind (

Bind values to the queries by place holder name.

This set of value bindings are used for all queries.

Parameters
nameValueMap

A map of place holder name to value.

Since:

BlackBerry 10.2.0

QUrl source ()

Get the source URL.

Return:

The source URL.

Since:

BlackBerry 10.2.0

virtualQString toString ()

Get a string representation of the query, for debugging purposes.

Return:

The string.

Since:

BlackBerry 10.2.0

QVariantMap valuesToBind ()

Retrieve the map of place holder name to value bindings.

This set of value bindings are used for all queries.

Return:

A map of place holder name to value.

Since:

BlackBerry 10.2.0

DataQuery (Inherited

Constructs a DataQuery.

Parameters
parent

The parent owner or 0. Optional and defaults to 0 if not specified.

Since:

BlackBerry 10.2.0

Signals

(Only has inherited signals)

void dataChanged (Inherited

Emitted when the data changes.

Parameters
revision

The revision of the latest source data.

Since:

BlackBerry 10.2.0

void error (Inherited

Emitted when an error occurs when executing the query.

Parameters
code

The error code.

message

The error message.

Since:

BlackBerry 10.2.0

Last modified: 2014-09-30



Got questions about leaving a comment? Get answers from our Disqus FAQ.

comments powered by Disqus