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.

Note: 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.

In implementing this class, the developer must make decisions about how to handle error conditions. If there are SQL or other expected or unexpected runtime errors, the developer must determine which of these errors are catastrophic and which are recoverable. See 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.

This allows values to be inserted for named place holders in each query statement. Once the property is set it cannot be changed.

Since:

BlackBerry 10.2.0

QString countQuery

An SQL query statement which will return the total count of items.

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

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, will uniquely identify the data item. It is used by the data model to signal listeners (usually an associated list) that items have changed location or been deleted.

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

Once the property is set it cannot be changed.

An example: The query: "SELECT key_id, revision_id, lastname, firstname FROM contacts" The keyColumn: "key_id"

See also:

DataItem

Since:

BlackBerry 10.2.0

QString query

The main SQL query statement.

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, will identify the current state of that item. It's used in conjunction with the overall revision. When a database item is updated its revision should also be updated as well as the overall database revision.

It 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 not be reflected in the user interface in a timely manner.

Once the property is set it cannot be changed.

An example: The query: "SELECT key_id, revision_id, lastname, firstname FROM contacts" 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 is 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 then 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 always be a full refresh of the cache so that the data can be guaranteed to be consistent.

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.

Mandatory.

Once the property is set it cannot be changed.

Since:

BlackBerry 10.2.0

Public Functions

SqlDataQuery (

Constructor.

Parameters
parent

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

Since:

BlackBerry 10.2.0

SqlDataQuery (

Constructor.

Parameters
query

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

parent

The parent owner or 0. Optional and will default 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.

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 will emit error() signal and return false if the data could not be successfully retrieved due to a query error, source not found or other unexpected condition.

See the discussion about error handling in the DataQuery class description. A return of false by this method should be both:
  • preceeded by the query emitting an error signal

  • application-level handling of the error signal 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. Pointer must not be null.

totalCount

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

results

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

Return:

Returns true if the data could be successfully retrieved, else returns false.

Since:

BlackBerry 10.2.0

virtual bool getDataForRevision (

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

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

See the discussion about error handling in the DataQuery class description. A return of false by this method should be both:
  • preceeded by the query emitting an error signal

  • application-level handling of the error signal leading to some appropriate action

IMPORTANT NOTE: The return of false by this method is always expected and never a catastrophic error since false is returned when the data source does not 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 does not match this revision, this method will fail and return false.

results

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

Return:

Returns true if the data could be successfully retrieved at the requested revision, else returns false.

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.

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.

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:

map of place holder name to value.

Since:

BlackBerry 10.2.0

DataQuery (Inherited

Constructor.

Parameters
parent

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

Since:

BlackBerry 10.2.0

Signals

(Only has inherited signals)

void dataChanged (Inherited

Signal for data changes.

Parameters
revision

the revision of the latest source data.

Since:

BlackBerry 10.2.0

void error (Inherited

Signal for error when executing the query.

Parameters
code

the error code

message

the error message

Since:

BlackBerry 10.2.0

Last modified: 2014-03-13

comments powered by Disqus