SqlHeaderDataQuery

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 HeaderDataQuery implementation that uses SQL selects to retrieve header and detail data.

Headers and items are associated as follows:
  • The query for headers must include a column named "count". This represents the count of items that belong to each header. The sum of all "count" properties should be equal to the total number of data items.

  • The query for data must provide the data in an order that aligns with the headers.

  • The headers and data are associated by grouping the data items into headers based on the "count" properties of the headers.

For example if the first header item has a count of 3, then the first three data items will be grouped under this header.

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 SqlHeaderDataQuery:
import bb.cascades 1.2
import bb.cascades.datamanager 1.2
Page {
    content: Container {
        layout: StackLayout {
        }
        ListView {
            layout: StackListLayout {
                headerMode: ListHeaderMode.Sticky
            }
            id: lv
            dataModel: dm
            listItemComponents: [
                ListItemComponent {
                    type: "header"
                    Header {
                        title: ListItemData.header
                    }
                },
                ListItemComponent {
                    type: ""
                    StandardListItem {
                        title: ListItemData.firstname + " " + ListItemData.lastname
                        imageSource: ListItemData.image
                        description: ListItemData.title
                    }
                }
            ]
        }
        attachedObjects: [
            AsyncHeaderDataModel {
                id: dm
                cacheSize: 200
                // an example to demonstrate how to use an SqlHeaderDataQuery
                query: SqlHeaderDataQuery {
                    // the following are mandatory
                    source: "sql/contacts1k.db"
                    query: "select id, firstname, lastname, title, image, active, revision_id from contact order by lastname, firstname"
                    headerQuery: "select substr(lastname, 1, 1) as header, count(*) from contact group by header"

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

                    // 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 SqlHeaderDataQuery:
// 1. Create an SqlHeaderDataQuery and set its properties
SqlHeaderDataQuery *sdq = new SqlHeaderDataQuery();

// ...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 "
              "order by lastname, firstname");
sdq->setHeaderQuery("select substr(lastname, 1, 1) as header, count(*) from contact group by header");

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

// ...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
AsyncHeaderDataModel *model = new AsyncHeaderDataModel();
model->setQuery(sdq);


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


Overview

Inheritance

QML properties

bindValues: QVariantMap
countQuery: QString
headerQuery: QString
keyColumn: QString
query: QString
revisionColumn: QString
revisionQuery: QString
scrollDownQuery: QString
scrollUpQuery: QString
source: QUrl

QML signals

Only has inherited QML signals

onDataChangedInherited: {}
onErrorInherited: {}

Public Functions Index

SqlHeaderDataQuery (QObject *parent=0)
SqlHeaderDataQuery (const QString &query, const QString &headerQuery, QObject *parent=0)
virtual ~SqlHeaderDataQuery ()
QStringcountQuery () const
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)
virtual boolgetHeaderData (int offset, int limit, DataRevision *revision, int *totalCount, QList< DataItem > *results, QList< HeaderDataItem > *headerResults)
QStringheaderQuery () const
QStringkeyColumn () const
QStringquery () const
QStringrevisionColumn () const
QStringrevisionQuery () const
QStringscrollDownQuery () const
QStringscrollUpQuery () const
voidsetCountQuery (const QString &countQuery)
voidsetHeaderQuery (const QString &query)
voidsetKeyColumn (const QString &keyColumn)
voidsetQuery (const QString &query)
voidsetRevisionColumn (const QString &revisionColumn)
voidsetRevisionQuery (const QString &revisionQuery)
voidsetScrollDownQuery (const QString &scrollDownQuery)
voidsetScrollUpQuery (const QString &scrollUpQuery)
voidsetSource (const QUrl &source)
voidsetValuesToBind (const QVariantMap &nameValueMap)
QUrlsource () const
virtual QStringtoString () const
QVariantMapvaluesToBind () const
DataQuery (QObject *parent=0)Inherited
HeaderDataQuery (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 headerQuery

The header data query for retrieving header items from the database.

Mandatory.

The query must include a numeric column named "count" or "count(*)" which identifies the count of children for each header. The sum of these counts for all items returned by the query must equal the total count for the data.

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

QString scrollDownQuery

Optional query used for improved performance when scrolling down.

Once the property is set it cannot be changed.

Since:

BlackBerry 10.2.0

QString scrollUpQuery

Optional query used for improved performance when scrolling up.

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

SqlHeaderDataQuery (

Constructor.

Parameters
parent

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

Since:

BlackBerry 10.2.0

SqlHeaderDataQuery (

Constructor.

Parameters
query

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

headerQuery

The SQL header 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~SqlHeaderDataQuery ()

Destructor.

Since:

BlackBerry 10.2.0

QString countQuery ()

Get the count query.

Return:

The countQuery string.

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

virtual bool getHeaderData (

Fetch the overall revision, total count, all header items 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. Pointer must not be null.

totalCount

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

results

The requested items. Pointer must not be null.

headerResults

The header items of the query. Pointer must not be null.

Return:

true if it can fetch from the revision, otherwise false.

Since:

BlackBerry 10.2.0

QString headerQuery ()

Get the SQL header query statement.

Return:

The SQL header query.

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

QString scrollDownQuery ()

Get the optional scrollDownQuery string used when scrolling down.

Return:

The scrollDownQuery string.

Since:

BlackBerry 10.2.0

QString scrollUpQuery ()

Get the optional scrollUpQuery string used when scrolling up.

Return:

The scrollUpQuery 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 setHeaderQuery (

Set the SQL header query statement.

Mandatory.

Parameters
query

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

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 setScrollDownQuery (

Set the optional scrollDownQuery string used for improved performance when scrolling down.

Parameters
scrollDownQuery

The query string to use when scrolling down.

Since:

BlackBerry 10.2.0

void setScrollUpQuery (

Set the optional scrollUpQuery string used for improved performance when scrolling up.

Parameters
scrollUpQuery

The query string to use when scrolling up.

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.

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

HeaderDataQuery (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