/****************************************************************************

**

** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies).

** All rights reserved.

** Contact: Nokia Corporation (qt-info@nokia.com)

**

** This file is part of the examples of the Qt Toolkit.

**

** $QT_BEGIN_LICENSE:LGPL$

** No Commercial Usage

** This file contains pre-release code and may not be distributed.

** You may use this file in accordance with the terms and conditions

** contained in the Technology Preview License Agreement accompanying

** this package.

**

** GNU Lesser General Public License Usage

** Alternatively, this file may be used under the terms of the GNU Lesser

** General Public License version 2.1 as published by the Free Software

** Foundation and appearing in the file LICENSE.LGPL included in the

** packaging of this file.  Please review the following information to

** ensure the GNU Lesser General Public License version 2.1 requirements

** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.

**

** In addition, as a special exception, Nokia gives you certain additional

** rights.  These rights are described in the Nokia Qt LGPL Exception

** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.

**

** If you have questions regarding the use of this file, please contact

** Nokia at qt-info@nokia.com.

**

**

**

**

**

**

**

**

** $QT_END_LICENSE$

**

****************************************************************************/

#include <QStringList>

#include "cntsqlsearch.h"

#include <QHash>

#include <QLocale>

const char KLimitLength = 15;

const int KTwoTokens = 2;

const int KOneToken = 1;

const char KLowerLimitPadding = '0';

const char KUpperLimitPadding = 'F';

const int KMinimumSearchPatternLength = 1;

const int KHexadecimalBase = 16;

#define ORDER_BY_FIRSTNAME_LASTNAME " ORDER BY first_name, last_name ASC;"

#define SELECT_CONTACT_ID			"SELECT contact_id FROM "

// TODO: Since the column names are repeated several times, replace them with

// shorter names like w, x, y & z. Also replace contact_id by id etc.

// Predictive search table's columns

const QString KColumn1 = "nbr";

const QString KColumn2 = "nbr2";

const QString KColumn3 = "nbr3";

const QString KColumn4 = "nbr4";

// Special handling for characters that originate from * and # keys

const QChar KStarChar('*');

const QChar KPlusChar('+');

const QChar KPChar('p');

const QChar KWChar('w');

const QChar KHashChar('#');

CntSqlSearch::CntSqlSearch()

	{

	}

// Basic cases:

// 1: "0", "5"

// Just one digit. Select all contact ids from the table. No need to compare

// values.

//

// 2: "123", "01", "10", "010", "00"

// No zeros which have non-zeros in their both sides

// One or zero tokens, when pattern is split using '0'.

//

// 3: "101", "1001"

// Same digit of both sides of the zero

// Two tokens, each with length of 1 and tokens are the same.

// The queries of case 3 could also be handled with case 4 query, but that

// would yield a longer SQL statement.

//

// 4: "102", "1002"

// One or more zeros in the middle, just one digit on either side of the zero(s)

// and those digits are not the same.

// Two tokens, each with length of 1 and tokens are different.

//

// 5: "1023", "0102", "1010", "00100200", "10203", "01020304050"

// Two tokens, at least one of them has length > 1.

// If tokens are identical, handle as case 3, otherwise handle as case 4.

// ("10203" -> tokens "1" and "203"

//  "0010023004560" -> tokens "001" and "23004560")

//

// 6: "10", "1000"

// In this case, query should look-up first toke and number ("10", "1000").

QString CntSqlSearch::CreatePredictiveSearch(const QString &pattern)

	int len = pattern.length();

        QString newPattern = ChangeStringPadings(pattern);

	// For best performance, handle 1 digit case first

	if (len == KMinimumSearchPatternLength)

        {

		// Case 1

        return SELECT_CONTACT_ID + SelectTable(newPattern) + ORDER_BY_FIRSTNAME_LASTNAME;

        }

    if (len <= KLimitLength && len > KMinimumSearchPatternLength)

        {

                return CreateQuery(newPattern);

		}

	return QString(""); // Invalid pattern

	}

QString CntSqlSearch::SelectTable(const QString &pattern) const

	{

        QString predictivesearch;

        QStringList tokens = GetTokens(pattern);

        bool ok;

        if (pattern.length() == 0)

                {

                return "";

                }

        QString firstNumber(pattern.at(0));

        uint hex = firstNumber.toUInt(&ok, 16);

        if (!ok)

            {

            // TODO: handle error (=invalid characters in pattern)

            }

        switch (hex)

            {

            case 0:

                {

                predictivesearch = QString("predictivesearch0");

                }

            break;

            case 1:

                {

                predictivesearch = QString("predictivesearch1");

                }

            break;

            case 2:

                {

                predictivesearch = QString("predictivesearch2");

                }

            break;

            case 3:

                {

                predictivesearch = QString("predictivesearch3");

                }

            break;

            case 4:

                {

                predictivesearch = QString("predictivesearch4");

                }

            break;

            case 5:

                {

                predictivesearch = QString("predictivesearch5");

                }

            break;

            case 6:

                {

                predictivesearch = QString("predictivesearch6");

                }

            break;

            case 7:

                {

                predictivesearch = QString("predictivesearch7");

                }

            break;

            case 8:

                {

                predictivesearch = QString("predictivesearch8");

                }

            break;

            case 9:

                {

                predictivesearch = QString("predictivesearch9");

                }

             break;

            case 10:

                {

                predictivesearch = QString("predictivesearch10");

                }

            break;

            case 11:

                {

                predictivesearch = QString("predictivesearch11");

                }

            break;

                    default: // error

                            predictivesearch = "";

                            break;

            }

            return predictivesearch;

	}

// Even if there are over 2 tokens, make 2 tokens.

// If there are two or more tokens, include the leading zeros in the first

// token the and trailing zeros in the second token.

// E.g. "0010230" results tokens "001" and "230" and

// "001230045067800900" tokens "00123" and "45067800900".

QStringList CntSqlSearch::GetTokens(const QString& pattern) const

    {

    const QChar KZero('0');

    QStringList tokens = pattern.split(KZero, QString::SkipEmptyParts);

    if (tokens.count() < KTwoTokens)

        {

        return tokens;

        }

    QStringList twoTokens;

    int i(0);

    while (pattern[i] == KZero) // Skip leading zeros

        {

        ++i;

        }

    while (pattern[i] != KZero) // Skip non-zeros to find where first token ends

        {

        ++i;

        }

    twoTokens.append(pattern.left(i));

    while (pattern[i] == KZero) // Skip zeros to find where second token begins

        {

        ++i;

        }

    twoTokens.append(pattern.mid(i));

    return twoTokens;

    }

// pattern length is between KMinimumSearchPatternLength...KLimitLength

QString CntSqlSearch::CreateQuery(const QString& pattern) const

	{

	if (tokens.count() < KTwoTokens)

            {

            }

	else

            }

	}

QString CntSqlSearch::ExactMatchSearch(const QString& pattern) const

	{

	return QString(SELECT_CONTACT_ID + SelectTable(pattern) +

		           " WHERE " + ExactMatch(pattern)); 

	}

// It has been checked that tokens are different, but they might begin with

// the same digit.

QString CntSqlSearch::IntersectionSearch(const QString& pattern,

									     const QStringList& tokens) const

	{

#if defined(SEARCH_FROM_ONE_TABLE)

	return SearchTokensFromOneTable(pattern, tokens);

#else

/* Query for pattern = "205":

SELECT predictivesearch2.contact_id FROM predictivesearch2 WHERE EXISTS

(SELECT contact_id FROM predictivesearch5 WHERE predictivesearch2.contact_id = predictivesearch5.contact_id)

OR

(SELECT contact_id FROM predictivesearch2 

WHERE(predictivesearch2.nbr>145522562959409152 AND predictivesearch2.nbr<145804037936119807) OR

(predictivesearch2.nbr2>145522562959409152 AND predictivesearch2.nbr2<145804037936119807) OR

(predictivesearch2.nbr3>145522562959409152 AND predictivesearch2.nbr3<145804037936119807) OR

(predictivesearch2.nbr4>145522562959409152 AND predictivesearch2.nbr4<145804037936119807))

ORDER BY predictivesearch2.first_name, predictivesearch2.last_name ASC;

This query works if both tokens have just one digit (e.g. "102", but not "1023")

*/	

	if (tokens.at(0).length() == KMinimumSearchPatternLength &&

		tokens.at(1).length() == KMinimumSearchPatternLength) 

		{

		// Case 4

		QString firstTable = SelectTable(tokens.at(0));

		QString secondTable = SelectTable(tokens.at(1));

		QString query =

			"SELECT " + firstTable + ".contact_id FROM " + firstTable +

			" WHERE EXISTS (" + SELECT_CONTACT_ID + secondTable + " WHERE " +

			firstTable + ".contact_id = " + secondTable + ".contact_id) OR (" +

			SELECT_CONTACT_ID + firstTable + " WHERE " + ExactMatch(pattern, firstTable) + ")";

		return query  + Order(tokens);

		}

	if (tokens.at(0).at(0) == tokens.at(1).at(0) ||

	    tokens.at(0).length() > 1 && tokens.at(1).length() > 1)

		{

		// Tokens begin with same digit or both tokens are longer than one digit.

		// Must search from one table.

		return SearchTokensFromOneTable(pattern, tokens);

		}

	return CreateJoinTableSearch(pattern, tokens); // Case 5

#endif

	}

// Find the exact match, or a column whose value is within

// lower..upper(exclusive) and another column whose value is within

// lower2..upper2(exclusive).

// 

// (column = X  AND column2 = Y) OR

// (column = X  AND column3 = Y) OR

// (column = X  AND column4 = Y) OR

// (column2 = X AND column3 = Y) OR

// (column2 = X AND column4 = Y) OR

// (column3 = X AND column4 = Y) OR

// (column = Y  AND column2 = X) OR

// (column = Y  AND column3 = X) OR

// (column = Y  AND column4 = X) OR

// (column2 = Y AND column3 = X) OR

// (column2 = Y AND column4 = X) OR

// (column3 = Y AND column4 = X)

//

// Where X means: (value > lower-limit AND value < upper-limit)

// and Y means: (value > lower-limit-2 AND value < upper-limit-2)

QString CntSqlSearch::SearchTokensFromOneTable(const QString& pattern,

											   const QStringList& tokens) const

    {

	QString token = tokens.at(0);

    QString lower = LowerLimit(token);

    QString upper = UpperLimit(token);

    QString lower2 = LowerLimit(tokens.at(1));

    QString upper2 = UpperLimit(tokens.at(1));

    QString query =

#if defined(USE_DEMORGAN)

		SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" +

	    ExactMatch(pattern) + " AND NOT" +

		CompareTwoColumns(lower, upper, lower2, upper2) + " AND NOT" +

        CompareTwoColumns(lower2, upper2, lower, upper) + ")";

#else

        SELECT_CONTACT_ID + SelectTable(token) + " WHERE (" +

		// exact match (e.g. "102")

        ExactMatch(pattern) + ") OR " +

        CompareTwoColumns(lower, upper, lower2, upper2) + " OR " +

        CompareTwoColumns(lower2, upper2, lower, upper);

#endif

	query += Order(tokens);

	return query;

	}

// Either an exact match is required, or tokens must be found, but not in the

// same column.

// Since tokens are identical, they have same limits, and one call to

// CompareTwoColumns() is enough.

QString CntSqlSearch::IdenticalTokensSearch(const QString& pattern,

										    const QStringList& tokens) const

	{

	QString token = tokens.at(0);

	QString lower = LowerLimit(token);

	QString upper = UpperLimit(token);

#if defined(USE_DEMORGAN)

	QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" +

	    ExactMatch(pattern) +

        " AND NOT" + CompareTwoColumns(lower, upper, lower, upper) + ")");

#else

	QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE (" +

	    ExactMatch(pattern) +  // exact match (e.g. "101")

        ") OR " + CompareTwoColumns(lower, upper, lower, upper));

#endif

	query += Order(tokens);

	return query;

	}

QString CntSqlSearch::TwoDifferentTokensSearch(const QString& pattern, const QStringList& tokens) const

        {

        QString token = tokens.at(0);

        QString sortPatern = pattern;

        sortPatern.truncate(pattern.length()-1);

#if defined(USE_DEMORGAN)

        QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" +

            ExactMatch(sortPatern) +

        " AND NOT" + ExactMatch(pattern) + ")");

#else

        QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE (" +

            ExactMatch(sortPatern) +  // exact match (e.g. "2")

        ") OR " + ExactMatch(pattern)); // exact match (e.g. "20")

#endif

        query += Order(tokens);

        return query;

        }

// Put individual AND / OR operations in such order that in most cases there is

// no need to evaluate all arguments of the AND / OR.

// In case of AND, put the less likely condition on the left side of AND.

// In case of OR, put the more likely condition on the left side of OR.

// Since 2nd column is more likely to be empty, compare it before 1st column.

// Since 1st & 2nd columns are more likely to contain a match, compare them

// before other column combinations (1st & 3rd, 2nd & 3rd etc)

QString CntSqlSearch::CompareTwoColumns(const QString& lower,

										const QString& upper,

										const QString& lower2,

										const QString& upper2) const

	{

#if defined(USE_DEMORGAN)

	// Using De Morgan's law to replace ORs with ANDs:

	// A || B || C || D || E || F --> !(!A && !B && !C && !D && !E && !F)

	//

	// As A (match found in columns 1 and 2) is more likely true than other

	// combinations, then !A is more likely false than other combinations, so

	// it is put first in the AND statement.	

	QString query =

		"(NOT(NOT(" +

		// 2nd & 1st column (='A')

		KColumn2 + ">" + lower2 + " AND " + KColumn2 + "<" + upper2 + " AND " +

		KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper +

		") AND NOT(" +

		// 3nd & 1st column (='B')

		KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " +

		KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper +

		") AND NOT(" +

		// 3rd & 2nd column (='C')

		KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " +

		KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper +

		") AND NOT(" +

		// 4th & 1st column (='D')

		KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " +

		KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper +

		") AND NOT(" +

		// 4th & 2nd column (='E')

		KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " +

		KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper +

		") AND NOT(" +

		// 4th & 3rd column (='F')

		KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " +

		KColumn3 + ">" + lower + " AND " + KColumn3 + "<" + upper + ")))";

#else

	QString query =

		"(" +

		// 2nd & 1st column

		KColumn2 + ">" + lower2 + " AND " + KColumn2 + "<" + upper2 + " AND " +

		KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper +

		") OR (" +

		// 3nd & 1st column

		KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " +

		KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper +

		") OR (" +

		// 3rd & 2nd column

		KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " +

		KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper +

		") OR (" +

		// 4th & 1st column

		KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " +

		KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper +

		") OR (" +

		// 4th & 2nd column

		KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " +

		KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper +

		") OR (" +

		// 4th & 3rd column

		KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " +

		KColumn3 + ">" + lower + " AND " + KColumn3 + "<" + upper + ")";

#endif

	return query;

	}

QString CntSqlSearch::ExactMatch(const QString& pattern, QString table) const

    {

    QString lower = LowerLimit(pattern);

    QString upper = UpperLimit(pattern);

    if (table.length() > 0)

        {