path: root/src/ivicore/doc/src/query-language.qdoc

/****************************************************************************
**
** Copyright (C) 2021 The Qt Company Ltd.
** Copyright (C) 2019 Luxoft Sweden AB
** Copyright (C) 2018 Pelagicore AG
** Contact: https://www.qt.io/licensing/
**
** This file is part of the documentation of the Qt Toolkit.
**
** $QT_BEGIN_LICENSE:FDL$
** Commercial License Usage
** Licensees holding valid commercial Qt licenses may use this file in
** accordance with the commercial license agreement provided with the
** Software or, alternatively, in accordance with the terms contained in
** a written agreement between you and The Qt Company. For licensing terms
** and conditions see https://www.qt.io/terms-conditions. For further
** information use the contact form at https://www.qt.io/contact-us.
**
** GNU Free Documentation License Usage
** Alternatively, this file may be used under the terms of the GNU Free
** Documentation License version 1.3 as published by the Free Software
** Foundation and appearing in the file included in the packaging of
** this file. Please review the following information to ensure
** the GNU Free Documentation License version 1.3 requirements
** will be met: https://www.gnu.org/licenses/fdl-1.3.html.
** $QT_END_LICENSE$
**
****************************************************************************/
/*!
\page query-language.html
\title Qt IVI Query Language
\keyword Qt IVI Query Language
\previouspage Models
\nextpage Qt IVI Simulation System
\contentspage Concepts

Automotive systems are getting bigger and including more features, particularly in entertainment
and connectivity. Modern systems can handle phone calls, access the mobile phone's address book,
and have a media player that manages a media database. Since address books and media databases
can be quite large these days, it's important for users to be able to filter, sort, and search
through them in a convenient way.

The Qt IVI Query Language provides you with a minimal language to express what you want to be
displayed and how it should be sorted. This language is independent from both, the underlying
architecture and potentially underlying database languages like SQL or PSQL. The Qt IVI Query
Language does not specify the data itself, but is only used to filter and sort.


\section1 Work with the Query Language

Currently the \l QIviSearchAndBrowseModel is the only class that supports the use of the query
language. In this case, the model's backend informs the query parser about the identifiers
available to filter and sort.

An identifier is the name of a column or property which can be used in the query language.

Suppose you use the QIviSearchAndBrowseModel to access a list of QIviAudioTrackItems, then
every property of the QIviAudioTrackItem are your identifiers.

The following query results in a search for tracks on the album "Nevermind":

\code
album='Nevermind'
\endcode

\section2 Filter and Sort

The Qt IVI Query Language has two parts:

\list 1
    \li The filter specification
    \li The sort order definition
\endlist

\code
album='Nevermind' [/trackNumber]
\endcode

In the query above, the first part is \c{album='Nevermind'}, and it indicates to only show tracks
which are from the album "Nevermind". The second part is specified by \c{[]} and defines the
order: the result should be sorted by \c trackNumber in ascending order.

\section3 Filter

To filter a result, the following operators can be used to match an identifier to a value:

\table
    \header
        \li Operator
        \li Description
    \row
        \li =
        \li Tests whether the identifier's value is equal to the given value. For strings, the
            comparison is case-sensitive.
            \note The \c{==} operator is an alias and delivers the same result.

    \row
        \li !=
        \li Tests whether the identifier's value is not equal to the given value. For strings,
            the comparison is case-sensitive.

    \row
        \li ~=
        \li Tests whether the identifier's value is equal to the given value. For strings,
            the comparison is case-insensitive.

    \row
        \li >
        \li Tests whether the lefthand-side value is greater than the righthand-side value.
            This comparison only works for numbers.

    \row
        \li >=
        \li Tests whether the lefthand-side value is greater than or equal to the righthand-side
            value. This comparison only works for numbers.

    \row
        \li <
        \li Tests whether the lefthand-side value is less than the righthand-side value. This
            comparison only works for numbers.

    \row
        \li <=
        \li Tests whether the lefthand-side value is less than or equal to the righthand-side
            value. This comparison only works for numbers.

\endtable

When you compare an identifier against a string, the string always needs to be encapsulated either
between two quotation marks - single \c{'} or double \c{"}. Numbers are supported in various
formats, including signed numbers, unsigned numbers, and floats using an exponent, such as -5.0E15.

\section3 Sort

The second part of a query is used to sort the result. It's optional and if not provided, the
backend can decide the order in which the result should be listed. To sort \c trackNumber in
ascending order, write the following:

\code
[/trackNumber]
\endcode

To sort in descending order:

\code
[\trackNumber]
\endcode

Using this query on a big list of songs may not give the intendend result, as there could be two
different tracks with the same \c trackNumber.

In this case, you can provide multiple sort orders.

\code
[\trackNumber][/album]
\endcode

The query above sorts the tracks by \c trackNumber in descending order, and the track with the same
\c trackNumber by album name in ascending order.

\section2 Conjunctions

Often, to achieve the expected result, it is not sufficient to only add a specific condition and
define the order. It may be necessary to use basic Boolean algebra, which the Qt IVI Query
Language supports. You can combine multiple filters by using AND / OR conjunctions.

The following query only lists the first 5 tracks in the album 'Metallica':

\code
album='Metallica' & trackNumber<=5
\endcode

You can also use brackets to negate filters:

\code
(album='Metallica') & !(trackNumber>5)
\endcode

\section1 Integrate with the Backend

For the backend, the query is translated from a string to a binary representation, similar to an
Abstract Syntax Tree (AST). Like the query itself, the representation is split into two parts:

\list 1
    \li A pointer to a QIviAbstractQueryTerm, which can be cast to one of the following types:
        \annotatedlist qtivi_queryterms
    \li A QList<QIviOrderTerm> that provides the order in which the user wants the result to be in.
\endlist

Usually, this C++ representation is translated into any form the backend supports.

For example, to get the results from an SQL database:

First we need a function to translate the identifiers used in the query into the column names used
in the database:

\code
QString SearchAndBrowseBackend::mapIdentifiers(const QString &type)
{
    if (type == QLatin1String("artist"))
        return QLatin1String("artistName");
    else if (type == QLatin1String("album"))
        return QLatin1String("albumName");
    else if (type == QLatin1String("track"))
        return QLatin1String("trackName");
    else
        return type;
}
\endcode

Next, we need functions to translate the QueryTerms and the OrderTerms into SQL clauses:

\code
QString SearchAndBrowseBackend::createSortOrder(const QString &type, const QList<QIviOrderTerm> &orderTerms)
{
    QStringList order;
    int i = 0;
    for (const QIviOrderTerm & term : orderTerms) {
        if (i)
            order.append(",");

        order.append(mapIdentifiers(term.propertyName()));
        if (term.isAscending())
            order.append("ASC");
        else
            order.append("DESC");

        i++;
    }

    return order.join(' ');
}

QString SearchAndBrowseBackend::createWhereClause(QIviAbstractQueryTerm *term)
{
    if (!term)
        return QString();

    switch (term->type()) {
    case QIviAbstractQueryTerm::ScopeTerm: {
        QIviScopeTerm *scope = static_cast<QIviScopeTerm*>(term);
        return QString(QLatin1String("%1 (%2)")).arg(scope->isNegated() ? "NOT" : "",createWhereClause(scope->term()));
    }
    case QIviAbstractQueryTerm::ConjunctionTerm: {
        QIviConjunctionTerm *conjunctionTerm = static_cast<QIviConjunctionTerm*>(term);
        QString conjunction = QLatin1String("AND");
        if (conjunctionTerm->conjunction() == QIviConjunctionTerm::Or)
            conjunction = QLatin1String("OR");

        QString string;
        QListIterator<QIviAbstractQueryTerm*> it(conjunctionTerm->terms());
        while (it.hasNext()) {
            string += createWhereClause(it.next());
            if (it.hasNext())
                string += QLatin1Literal(" ") + conjunction + QLatin1Literal(" ");
        }
        return string;
    }
    case QIviAbstractQueryTerm::FilterTerm: {
        QIviFilterTerm *filter = static_cast<QIviFilterTerm*>(term);
        QString operatorString;
        bool negated = filter->isNegated();
        QString value;
        if (filter->value().type() == QVariant::String)
            value = QString(QLatin1String("'%1'")).arg(filter->value().toString().replace('*', '%'));
        else
            value = filter->value().toString();

        switch (filter->operatorType()){
            case QIviFilterTerm::Equals: operatorString = QLatin1String("="); break;
            case QIviFilterTerm::EqualsCaseInsensitive: operatorString = QLatin1String("LIKE"); break;
            case QIviFilterTerm::Unequals: operatorString = QLatin1String("="); negated = !negated; break;
            case QIviFilterTerm::GreaterThan: operatorString = QLatin1String(">"); break;
            case QIviFilterTerm::GreaterEquals: operatorString = QLatin1String(">="); break;
            case QIviFilterTerm::LowerThan: operatorString = QLatin1String("<"); break;
            case QIviFilterTerm::LowerEquals: operatorString = QLatin1String("<="); break;
        }

        QStringList clause;
        if (negated)
            clause.append(QLatin1String("NOT"));
        clause.append(mapIdentifiers(filter->propertyName()));
        clause.append(operatorString);
        clause.append(value);

        return clause.join(" ");
    }
    }

    return QString();
}
\endcode

Finally, you can then create the following query:

\code
QString query = QString(QLatin1String("SELECT * FROM tracks WHERE %1 ORDER BY %2")).arg(createWhereClause(queryTerm), createSortOrder(orderTerms));
\endcode
*/