|
1 /**************************************************************************** |
|
2 ** |
|
3 ** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies). |
|
4 ** All rights reserved. |
|
5 ** Contact: Nokia Corporation (qt-info@nokia.com) |
|
6 ** |
|
7 ** This file is part of the examples of the Qt Toolkit. |
|
8 ** |
|
9 ** $QT_BEGIN_LICENSE:LGPL$ |
|
10 ** No Commercial Usage |
|
11 ** This file contains pre-release code and may not be distributed. |
|
12 ** You may use this file in accordance with the terms and conditions |
|
13 ** contained in the Technology Preview License Agreement accompanying |
|
14 ** this package. |
|
15 ** |
|
16 ** GNU Lesser General Public License Usage |
|
17 ** Alternatively, this file may be used under the terms of the GNU Lesser |
|
18 ** General Public License version 2.1 as published by the Free Software |
|
19 ** Foundation and appearing in the file LICENSE.LGPL included in the |
|
20 ** packaging of this file. Please review the following information to |
|
21 ** ensure the GNU Lesser General Public License version 2.1 requirements |
|
22 ** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html. |
|
23 ** |
|
24 ** In addition, as a special exception, Nokia gives you certain additional |
|
25 ** rights. These rights are described in the Nokia Qt LGPL Exception |
|
26 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package. |
|
27 ** |
|
28 ** If you have questions regarding the use of this file, please contact |
|
29 ** Nokia at qt-info@nokia.com. |
|
30 ** |
|
31 ** |
|
32 ** |
|
33 ** |
|
34 ** |
|
35 ** |
|
36 ** |
|
37 ** |
|
38 ** $QT_END_LICENSE$ |
|
39 ** |
|
40 ****************************************************************************/ |
|
41 #include <QStringList> |
|
42 |
|
43 #include "cntsqlsearch.h" |
|
44 #include "cqwertykeymap.h" |
|
45 #include "c12keykeymap.h" |
|
46 #include <QHash> |
|
47 #include <QLocale> |
|
48 |
|
49 const char KLimitLength = 15; |
|
50 const int KTwoTokens = 2; |
|
51 const int KOneToken = 1; |
|
52 const char KLowerLimitPadding = '0'; |
|
53 const char KUpperLimitPadding = 'F'; |
|
54 const int KMinimumSearchPatternLength = 1; |
|
55 const int KHexadecimalBase = 16; |
|
56 |
|
57 |
|
58 #define ORDER_BY_FIRSTNAME_LASTNAME " ORDER BY first_name, last_name ASC;" |
|
59 #define SELECT_CONTACT_ID "SELECT contact_id FROM " |
|
60 |
|
61 // TODO: Since the column names are repeated several times, replace them with |
|
62 // shorter names like w, x, y & z. Also replace contact_id by id etc. |
|
63 |
|
64 // Predictive search table's columns |
|
65 const QString KColumn1 = "nbr"; |
|
66 const QString KColumn2 = "nbr2"; |
|
67 const QString KColumn3 = "nbr3"; |
|
68 const QString KColumn4 = "nbr4"; |
|
69 |
|
70 // Predictive search qwert table's columns |
|
71 const QString KQm1 = "n"; |
|
72 const QString KQm2 = "n2"; |
|
73 const QString KQm3 = "n3"; |
|
74 const QString KQm4 = "n4"; |
|
75 const QString KQm5 = "n5"; |
|
76 const QString KQm6 = "n6"; |
|
77 const QString KQm7 = "n7"; |
|
78 |
|
79 // Special handling for characters that originate from * and # keys |
|
80 const QChar KStarChar('*'); |
|
81 const QChar KPlusChar('+'); |
|
82 const QChar KPChar('p'); |
|
83 const QChar KWChar('w'); |
|
84 const QChar KHashChar('#'); |
|
85 |
|
86 //Predictive search table |
|
87 const QString QwertyTableName = "qm"; |
|
88 |
|
89 CntSqlSearch::CntSqlSearch() |
|
90 { |
|
91 QT_TRAP_THROWING(mQertyKeyMap = CQwertyKeyMap::NewL()); |
|
92 QT_TRAP_THROWING(mkeyKeyMap = C12keyKeyMap::NewL()); |
|
93 } |
|
94 |
|
95 CntSqlSearch::~CntSqlSearch() |
|
96 { |
|
97 delete mQertyKeyMap; |
|
98 delete mkeyKeyMap; |
|
99 } |
|
100 // Basic cases: |
|
101 // 1: "0", "5" |
|
102 // Just one digit. Select all contact ids from the table. No need to compare |
|
103 // values. |
|
104 // |
|
105 // 2: "123", "01", "10", "010", "00" |
|
106 // No zeros which have non-zeros in their both sides |
|
107 // One or zero tokens, when pattern is split using '0'. |
|
108 // |
|
109 // 3: "101", "1001" |
|
110 // Same digit of both sides of the zero |
|
111 // Two tokens, each with length of 1 and tokens are the same. |
|
112 // The queries of case 3 could also be handled with case 4 query, but that |
|
113 // would yield a longer SQL statement. |
|
114 // |
|
115 // 4: "102", "1002" |
|
116 // One or more zeros in the middle, just one digit on either side of the zero(s) |
|
117 // and those digits are not the same. |
|
118 // Two tokens, each with length of 1 and tokens are different. |
|
119 // |
|
120 // 5: "1023", "0102", "1010", "00100200", "10203", "01020304050" |
|
121 // Two tokens, at least one of them has length > 1. |
|
122 // If tokens are identical, handle as case 3, otherwise handle as case 4. |
|
123 // ("10203" -> tokens "1" and "203" |
|
124 // "0010023004560" -> tokens "001" and "23004560") |
|
125 // |
|
126 // 6: "10", "1000" |
|
127 // One token, ends with zero. |
|
128 // In this case, query should look-up first toke and number ("10", "1000"). |
|
129 |
|
130 QString CntSqlSearch::CreatePredictiveSearch(const QString &pattern) |
|
131 { |
|
132 int len = pattern.length(); |
|
133 QString newPattern; |
|
134 if (isQwerty(pattern)) |
|
135 { |
|
136 return CreateQwertyQuery(pattern); |
|
137 } |
|
138 else |
|
139 { |
|
140 newPattern = ChangeStringPadings(pattern); |
|
141 // For best performance, handle 1 digit case first |
|
142 if (len == KMinimumSearchPatternLength) |
|
143 { |
|
144 // Case 1 |
|
145 return SELECT_CONTACT_ID + SelectTable(newPattern) + ORDER_BY_FIRSTNAME_LASTNAME; |
|
146 } |
|
147 if (len <= KLimitLength && len > KMinimumSearchPatternLength) |
|
148 { |
|
149 return CreateQuery(newPattern); |
|
150 } |
|
151 return QString(""); // Invalid pattern |
|
152 } |
|
153 } |
|
154 QString CntSqlSearch::selectQweryTable(const QString &pattern) const |
|
155 { |
|
156 QString tableNumber; |
|
157 if(pattern.length() > 0) |
|
158 { |
|
159 return QwertyTableName + tableNumber.setNum(mQertyKeyMap->MapKeyNameToValue(pattern[0])); |
|
160 } |
|
161 else |
|
162 { |
|
163 return QString(""); |
|
164 } |
|
165 } |
|
166 QString CntSqlSearch::SelectTable(const QString &pattern) const |
|
167 { |
|
168 QString predictivesearch; |
|
169 QStringList tokens = GetTokens(pattern); |
|
170 bool ok; |
|
171 if (pattern.length() == 0) |
|
172 { |
|
173 return ""; |
|
174 } |
|
175 QString firstNumber(pattern.at(0)); |
|
176 uint hex = firstNumber.toUInt(&ok, 16); |
|
177 if (!ok) |
|
178 { |
|
179 // TODO: handle error (=invalid characters in pattern) |
|
180 } |
|
181 switch (hex) |
|
182 { |
|
183 case 0: |
|
184 { |
|
185 predictivesearch = QString("predictivesearch0"); |
|
186 } |
|
187 break; |
|
188 case 1: |
|
189 { |
|
190 predictivesearch = QString("predictivesearch1"); |
|
191 } |
|
192 break; |
|
193 case 2: |
|
194 { |
|
195 predictivesearch = QString("predictivesearch2"); |
|
196 } |
|
197 break; |
|
198 case 3: |
|
199 { |
|
200 predictivesearch = QString("predictivesearch3"); |
|
201 } |
|
202 break; |
|
203 case 4: |
|
204 { |
|
205 predictivesearch = QString("predictivesearch4"); |
|
206 } |
|
207 break; |
|
208 case 5: |
|
209 { |
|
210 predictivesearch = QString("predictivesearch5"); |
|
211 } |
|
212 break; |
|
213 case 6: |
|
214 { |
|
215 predictivesearch = QString("predictivesearch6"); |
|
216 } |
|
217 break; |
|
218 case 7: |
|
219 { |
|
220 predictivesearch = QString("predictivesearch7"); |
|
221 } |
|
222 break; |
|
223 case 8: |
|
224 { |
|
225 predictivesearch = QString("predictivesearch8"); |
|
226 } |
|
227 break; |
|
228 case 9: |
|
229 { |
|
230 predictivesearch = QString("predictivesearch9"); |
|
231 } |
|
232 break; |
|
233 case 10: |
|
234 { |
|
235 predictivesearch = QString("predictivesearch10"); |
|
236 } |
|
237 break; |
|
238 case 11: |
|
239 { |
|
240 predictivesearch = QString("predictivesearch11"); |
|
241 } |
|
242 break; |
|
243 default: // error |
|
244 predictivesearch = ""; |
|
245 break; |
|
246 } |
|
247 return predictivesearch; |
|
248 } |
|
249 |
|
250 // Even if there are over 2 tokens, make 2 tokens. |
|
251 // If there are two or more tokens, include the leading zeros in the first |
|
252 // token the and trailing zeros in the second token. |
|
253 // E.g. "0010230" results tokens "001" and "230" and |
|
254 // "001230045067800900" tokens "00123" and "45067800900". |
|
255 QStringList CntSqlSearch::GetTokens(const QString& pattern) const |
|
256 { |
|
257 const QChar KZero('0'); |
|
258 QStringList tokens = pattern.split(KZero, QString::SkipEmptyParts); |
|
259 if (tokens.count() < KTwoTokens) |
|
260 { |
|
261 return tokens; |
|
262 } |
|
263 |
|
264 QStringList twoTokens; |
|
265 int i(0); |
|
266 while (pattern[i] == KZero) // Skip leading zeros |
|
267 { |
|
268 ++i; |
|
269 } |
|
270 while (pattern[i] != KZero) // Skip non-zeros to find where first token ends |
|
271 { |
|
272 ++i; |
|
273 } |
|
274 twoTokens.append(pattern.left(i)); |
|
275 |
|
276 while (pattern[i] == KZero) // Skip zeros to find where second token begins |
|
277 { |
|
278 ++i; |
|
279 } |
|
280 twoTokens.append(pattern.mid(i)); |
|
281 return twoTokens; |
|
282 } |
|
283 |
|
284 QString CntSqlSearch::CreateQwertyQuery(const QString& pattern) const |
|
285 { |
|
286 QStringList qwertyString; |
|
287 qwertyString = qwertyTokens(pattern); |
|
288 if(qwertyString.count() == 1 ) |
|
289 { |
|
290 if(qwertyString.at(0).length() == 1) |
|
291 { |
|
292 return SELECT_CONTACT_ID + selectQweryTable(qwertyString.at(0)) + ORDER_BY_FIRSTNAME_LASTNAME; |
|
293 } |
|
294 else if (qwertyString.at(0).length() > 1) |
|
295 { |
|
296 return ExactMatchSearchQwerty(qwertyString.at(0)) + ORDER_BY_FIRSTNAME_LASTNAME; |
|
297 } |
|
298 else |
|
299 { |
|
300 //Empty string |
|
301 return QString(""); |
|
302 } |
|
303 } |
|
304 else |
|
305 { |
|
306 return SearchTokensFromOneTable(pattern, qwertyString, CntSqlSearch::QwertyEmail); |
|
307 } |
|
308 } |
|
309 // pattern length is between KMinimumSearchPatternLength...KLimitLength |
|
310 QString CntSqlSearch::CreateQuery(const QString& pattern) const |
|
311 { |
|
312 QStringList tokens = GetTokens(pattern); |
|
313 if (tokens.count() < KTwoTokens) |
|
314 { |
|
315 if (TestPattern(pattern, CntSqlSearch::ZerosEndOfFirstToken)) |
|
316 { |
|
317 return TwoDifferentTokensSearch(pattern, tokens); // Case 6 |
|
318 } |
|
319 |
|
320 if (TestPattern(pattern, CntSqlSearch::ZeroIsFirstNumber)) |
|
321 { |
|
322 return CompareTwoColumnsWithModifiedPattern(pattern, tokens); // Case 7 |
|
323 } |
|
324 else |
|
325 { |
|
326 return ExactMatchSearch(pattern) + Order(tokens); // Case 2 |
|
327 } |
|
328 } |
|
329 else |
|
330 { |
|
331 if (tokens.at(0) == tokens.at(1)) |
|
332 { |
|
333 return IdenticalTokensSearch(pattern, tokens); // Case 3 |
|
334 } |
|
335 else |
|
336 { |
|
337 return IntersectionSearch(pattern, tokens); // Case 4 |
|
338 } |
|
339 } |
|
340 } |
|
341 |
|
342 QString CntSqlSearch::ExactMatchSearch(const QString& pattern) const |
|
343 { |
|
344 return QString(SELECT_CONTACT_ID + SelectTable(pattern) + |
|
345 " WHERE " + ExactMatch(pattern)); |
|
346 } |
|
347 |
|
348 QString CntSqlSearch::ExactMatchSearchQwerty(const QString& pattern) const |
|
349 { |
|
350 return QString(SELECT_CONTACT_ID + selectQweryTable(pattern) + |
|
351 " WHERE " + ExactMatchQwerty(pattern)); |
|
352 } |
|
353 |
|
354 // It has been checked that tokens are different, but they might begin with |
|
355 // the same digit. |
|
356 QString CntSqlSearch::IntersectionSearch(const QString& pattern, |
|
357 const QStringList& tokens) const |
|
358 { |
|
359 #if defined(SEARCH_FROM_ONE_TABLE) |
|
360 return SearchTokensFromOneTable(pattern, tokens); |
|
361 #else |
|
362 /* Query for pattern = "205": |
|
363 SELECT predictivesearch2.contact_id FROM predictivesearch2 WHERE EXISTS |
|
364 (SELECT contact_id FROM predictivesearch5 WHERE predictivesearch2.contact_id = predictivesearch5.contact_id) |
|
365 OR |
|
366 (SELECT contact_id FROM predictivesearch2 |
|
367 WHERE(predictivesearch2.nbr>145522562959409152 AND predictivesearch2.nbr<145804037936119807) OR |
|
368 (predictivesearch2.nbr2>145522562959409152 AND predictivesearch2.nbr2<145804037936119807) OR |
|
369 (predictivesearch2.nbr3>145522562959409152 AND predictivesearch2.nbr3<145804037936119807) OR |
|
370 (predictivesearch2.nbr4>145522562959409152 AND predictivesearch2.nbr4<145804037936119807)) |
|
371 ORDER BY predictivesearch2.first_name, predictivesearch2.last_name ASC; |
|
372 |
|
373 This query works if both tokens have just one digit (e.g. "102", but not "1023") |
|
374 */ |
|
375 if (tokens.at(0).length() == KMinimumSearchPatternLength && |
|
376 tokens.at(1).length() == KMinimumSearchPatternLength) |
|
377 { |
|
378 // Case 4 |
|
379 QString firstTable = SelectTable(tokens.at(0)); |
|
380 QString secondTable = SelectTable(tokens.at(1)); |
|
381 QString query = |
|
382 "SELECT " + firstTable + ".contact_id FROM " + firstTable + |
|
383 " WHERE EXISTS (" + SELECT_CONTACT_ID + secondTable + " WHERE " + |
|
384 firstTable + ".contact_id = " + secondTable + ".contact_id) OR (" + |
|
385 SELECT_CONTACT_ID + firstTable + " WHERE " + ExactMatch(pattern, firstTable) + ")"; |
|
386 return query + Order(tokens); |
|
387 } |
|
388 if (tokens.at(0).at(0) == tokens.at(1).at(0) || |
|
389 tokens.at(0).length() > 1 && tokens.at(1).length() > 1) |
|
390 { |
|
391 // Tokens begin with same digit or both tokens are longer than one digit. |
|
392 // Must search from one table. |
|
393 return SearchTokensFromOneTable(pattern, tokens); |
|
394 } |
|
395 return CreateJoinTableSearch(pattern, tokens); // Case 5 |
|
396 #endif |
|
397 } |
|
398 |
|
399 // Find the exact match, or a column whose value is within |
|
400 // lower..upper(exclusive) and another column whose value is within |
|
401 // lower2..upper2(exclusive). |
|
402 // In this case the limits are different, so there are 12 combinations the two |
|
403 // values can exist in four columns: |
|
404 // |
|
405 // (column = X AND column2 = Y) OR |
|
406 // (column = X AND column3 = Y) OR |
|
407 // (column = X AND column4 = Y) OR |
|
408 // (column2 = X AND column3 = Y) OR |
|
409 // (column2 = X AND column4 = Y) OR |
|
410 // (column3 = X AND column4 = Y) OR |
|
411 // (column = Y AND column2 = X) OR |
|
412 // (column = Y AND column3 = X) OR |
|
413 // (column = Y AND column4 = X) OR |
|
414 // (column2 = Y AND column3 = X) OR |
|
415 // (column2 = Y AND column4 = X) OR |
|
416 // (column3 = Y AND column4 = X) |
|
417 // |
|
418 // |
|
419 // Qwert case |
|
420 // Where X means: (value > lower-limit AND value < upper-limit) |
|
421 // and Y means: (value > lower-limit-2 AND value < upper-limit-2) |
|
422 QString CntSqlSearch::SearchTokensFromOneTable(const QString& pattern, |
|
423 const QStringList& tokens, |
|
424 QueryType queryType) const |
|
425 { |
|
426 QString token; |
|
427 QString lower; |
|
428 QString upper; |
|
429 QString lower2; |
|
430 QString upper2; |
|
431 int err; |
|
432 |
|
433 if(queryType == CntSqlSearch::TwelveTable) |
|
434 { |
|
435 err = mkeyKeyMap->GetNumericLimits(tokens.at(0), lower, upper); |
|
436 if(err) |
|
437 { |
|
438 return QString(""); |
|
439 } |
|
440 err = mkeyKeyMap->GetNumericLimits(tokens.at(1), lower2, upper2); |
|
441 if(err) |
|
442 { |
|
443 return QString(""); |
|
444 } |
|
445 } |
|
446 else |
|
447 { |
|
448 err = mQertyKeyMap->GetNumericLimits(tokens.at(0), lower, upper); |
|
449 err = mQertyKeyMap->GetNumericLimits(tokens.at(1), lower2, upper2); |
|
450 if(err) |
|
451 { |
|
452 return QString(""); |
|
453 } |
|
454 } |
|
455 QString query; |
|
456 if (queryType == CntSqlSearch::TwelveTable) |
|
457 { |
|
458 query = SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" + |
|
459 ExactMatch(pattern) + " AND NOT" + |
|
460 CompareTwoColumns(lower, upper, lower2, upper2) + " AND NOT" + |
|
461 CompareTwoColumns(lower2, upper2, lower, upper) + ")"; |
|
462 } |
|
463 else |
|
464 { |
|
465 |
|
466 query = SELECT_CONTACT_ID + selectQweryTable(tokens.at(0)) + " WHERE NOT(NOT" + |
|
467 ExactMatchQwerty(pattern) + " AND NOT" + |
|
468 CompareTwoQwertyColumns(lower, upper, lower2, upper2) + " AND NOT" + |
|
469 CompareTwoQwertyColumns(lower2, upper2, lower, upper) + ")"; |
|
470 } |
|
471 query += Order(tokens, queryType); |
|
472 return query; |
|
473 } |
|
474 |
|
475 // Either an exact match is required, or tokens must be found, but not in the |
|
476 // same column. |
|
477 // Since tokens are identical, they have same limits, and one call to |
|
478 // CompareTwoColumns() is enough. |
|
479 QString CntSqlSearch::IdenticalTokensSearch(const QString& pattern, |
|
480 const QStringList& tokens) const |
|
481 { |
|
482 QString token = tokens.at(0); |
|
483 QString lower; |
|
484 QString upper; |
|
485 |
|
486 TInt err = mkeyKeyMap->GetNumericLimits(token, lower, upper); |
|
487 |
|
488 #if defined(USE_DEMORGAN) |
|
489 QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" + |
|
490 ExactMatch(pattern) + |
|
491 " AND NOT" + CompareTwoColumns(lower, upper, lower, upper) + ")"); |
|
492 #else |
|
493 QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE (" + |
|
494 ExactMatch(pattern) + // exact match (e.g. "101") |
|
495 ") OR " + CompareTwoColumns(lower, upper, lower, upper)); |
|
496 #endif |
|
497 query += Order(tokens); |
|
498 return query; |
|
499 } |
|
500 |
|
501 |
|
502 QString CntSqlSearch::TwoDifferentTokensSearch(const QString& pattern, const QStringList& tokens) const |
|
503 { |
|
504 QString token = tokens.at(0); |
|
505 QString sortPatern = pattern; |
|
506 sortPatern.truncate(pattern.length()-1); |
|
507 #if defined(USE_DEMORGAN) |
|
508 QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" + |
|
509 ExactMatch(sortPatern) + |
|
510 " AND NOT" + ExactMatch(pattern) + ")"); |
|
511 #else |
|
512 QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE (" + |
|
513 ExactMatch(sortPatern) + // exact match (e.g. "2") |
|
514 ") OR " + ExactMatch(pattern)); // exact match (e.g. "20") |
|
515 #endif |
|
516 query += Order(tokens); |
|
517 return query; |
|
518 } |
|
519 /*Modified token is search from two different tables. |
|
520 Patern is modified look-up contacts without first zero e.g exact match 05 or 5. |
|
521 SELECT contact_id |
|
522 FROM |
|
523 ( |
|
524 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 |
|
525 UNION |
|
526 SELECT predictivesearch0.contact_id, predictivesearch0.first_name, predictivesearch0.last_name FROM predictivesearch0 |
|
527 WHERE ((NOT(NOT(predictivesearch0.nbr>22517998136852479 AND predictivesearch0.nbr<27021597764222976) AND NOT(predictivesearch0.nbr2>22517998136852479 AND predictivesearch0.nbr2<27021597764222976) AND NOT(predictivesearch0.nbr3>22517998136852479 AND predictivesearch0.nbr3<27021597764222976) AND NOT(predictivesearch0.nbr4>22517998136852479 AND predictivesearch0.nbr4<27021597764222976)))) |
|
528 ) AS PR |
|
529 ORDER BY PR.first_name, PR.last_name ASC; |
|
530 */ |
|
531 |
|
532 QString CntSqlSearch::CompareTwoColumnsWithModifiedPattern(const QString& pattern, |
|
533 const QStringList& tokens) const |
|
534 { |
|
535 QString patternAfterZero = pattern.right(pattern.count() - 1); |
|
536 // It has been checked earlier that tables are not same |
|
537 QString firstTable = SelectTable(pattern); |
|
538 QString secondTable = SelectTable(patternAfterZero); |
|
539 QString queryString; |
|
540 if (patternAfterZero.count() == 1) |
|
541 { |
|
542 queryString = QString("SELECT contact_id FROM (SELECT " + secondTable + ".contact_id, " + secondTable + ".first_name, " + secondTable + ".last_name FROM " + secondTable |
|
543 + " UNION SELECT " + firstTable + ".contact_id, " + firstTable + ".first_name, " + firstTable + ".last_name FROM " + firstTable |
|
544 + " WHERE " + ModifiedMatchColumns( pattern) + ") AS PR ORDER BY PR.first_name, PR.last_name ASC;"); |
|
545 } |
|
546 else |
|
547 { |
|
548 queryString = QString("SELECT contact_id FROM (SELECT " + secondTable + ".contact_id, " + secondTable + ".first_name, " + secondTable + ".last_name FROM " + secondTable |
|
549 + " WHERE " + ModifiedMatchColumns( patternAfterZero) + |
|
550 + " UNION SELECT " + firstTable + ".contact_id, " + firstTable + ".first_name, " + firstTable + ".last_name FROM " + firstTable |
|
551 + " WHERE " + ModifiedMatchColumns( pattern) + ") AS PR ORDER BY PR.first_name, PR.last_name ASC;"); |
|
552 } |
|
553 return queryString; |
|
554 } |
|
555 |
|
556 QString CntSqlSearch::ModifiedMatchColumns(const QString& pattern) const |
|
557 { |
|
558 return "(" + ExactMatch(pattern, SelectTable(pattern)) + ")"; |
|
559 } |
|
560 |
|
561 // Put individual AND / OR operations in such order that in most cases there is |
|
562 // no need to evaluate all arguments of the AND / OR. |
|
563 // In case of AND, put the less likely condition on the left side of AND. |
|
564 // In case of OR, put the more likely condition on the left side of OR. |
|
565 // Since 2nd column is more likely to be empty, compare it before 1st column. |
|
566 // Since 1st & 2nd columns are more likely to contain a match, compare them |
|
567 // before other column combinations (1st & 3rd, 2nd & 3rd etc) |
|
568 QString CntSqlSearch::CompareTwoColumns(const QString& lower, |
|
569 const QString& upper, |
|
570 const QString& lower2, |
|
571 const QString& upper2) const |
|
572 { |
|
573 #if defined(USE_DEMORGAN) |
|
574 // Using De Morgan's law to replace ORs with ANDs: |
|
575 // A || B || C || D || E || F --> !(!A && !B && !C && !D && !E && !F) |
|
576 // |
|
577 // As A (match found in columns 1 and 2) is more likely true than other |
|
578 // combinations, then !A is more likely false than other combinations, so |
|
579 // it is put first in the AND statement. |
|
580 QString query = |
|
581 "(NOT(NOT(" + |
|
582 // 2nd & 1st column (='A') |
|
583 KColumn2 + ">" + lower2 + " AND " + KColumn2 + "<" + upper2 + " AND " + |
|
584 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
585 ") AND NOT(" + |
|
586 // 3nd & 1st column (='B') |
|
587 KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " + |
|
588 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
589 ") AND NOT(" + |
|
590 // 3rd & 2nd column (='C') |
|
591 KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " + |
|
592 KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper + |
|
593 ") AND NOT(" + |
|
594 // 4th & 1st column (='D') |
|
595 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
596 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
597 ") AND NOT(" + |
|
598 // 4th & 2nd column (='E') |
|
599 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
600 KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper + |
|
601 ") AND NOT(" + |
|
602 // 4th & 3rd column (='F') |
|
603 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
604 KColumn3 + ">" + lower + " AND " + KColumn3 + "<" + upper + ")))"; |
|
605 #else |
|
606 QString query = |
|
607 "(" + |
|
608 // 2nd & 1st column |
|
609 KColumn2 + ">" + lower2 + " AND " + KColumn2 + "<" + upper2 + " AND " + |
|
610 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
611 ") OR (" + |
|
612 // 3nd & 1st column |
|
613 KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " + |
|
614 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
615 ") OR (" + |
|
616 // 3rd & 2nd column |
|
617 KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " + |
|
618 KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper + |
|
619 ") OR (" + |
|
620 // 4th & 1st column |
|
621 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
622 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
623 ") OR (" + |
|
624 // 4th & 2nd column |
|
625 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
626 KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper + |
|
627 ") OR (" + |
|
628 // 4th & 3rd column |
|
629 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
630 KColumn3 + ">" + lower + " AND " + KColumn3 + "<" + upper + ")"; |
|
631 #endif |
|
632 return query; |
|
633 } |
|
634 |
|
635 QString CntSqlSearch::CompareTwoQwertyColumns(const QString& lower, |
|
636 const QString& upper, |
|
637 const QString& lower2, |
|
638 const QString& upper2) const |
|
639 { |
|
640 // Using De Morgan's law to replace ORs with ANDs: |
|
641 // A || B || C || D || E || F --> !(!A && !B && !C && !D && !E && !F) |
|
642 // |
|
643 // As A (match found in columns 1 and 2) is more likely true than other |
|
644 // combinations, then !A is more likely false than other combinations, so |
|
645 // it is put first in the AND statement. |
|
646 QString query = |
|
647 "(NOT(NOT(" + |
|
648 // 2nd & 1st column (='A') |
|
649 KQm2 + ">" + lower2 + " AND " + KQm2 + "<" + upper2 + " AND " + |
|
650 KQm1 + ">" + lower + " AND " + KQm1 + "<" + upper + |
|
651 ") AND NOT(" + |
|
652 // 3nd & 1st column (='B') |
|
653 KQm3 + ">" + lower2 + " AND " + KQm3 + "<" + upper2 + " AND " + |
|
654 KQm1 + ">" + lower + " AND " + KQm1 + "<" + upper + |
|
655 ") AND NOT(" + |
|
656 // 3rd & 2nd column (='C') |
|
657 KQm3 + ">" + lower2 + " AND " + KQm3 + "<" + upper2 + " AND " + |
|
658 KQm2 + ">" + lower + " AND " + KQm2 + "<" + upper + |
|
659 ") AND NOT(" + |
|
660 // 4th & 1st column (='D') |
|
661 KQm4 + ">" + lower2 + " AND " + KQm4 + "<" + upper2 + " AND " + |
|
662 KQm1 + ">" + lower + " AND " + KQm1 + "<" + upper + |
|
663 ") AND NOT(" + |
|
664 // 4th & 2nd column (='E') |
|
665 KQm4 + ">" + lower2 + " AND " + KQm4 + "<" + upper2 + " AND " + |
|
666 KQm2 + ">" + lower + " AND " + KQm2 + "<" + upper + |
|
667 ") AND NOT(" + |
|
668 // 4th & 3rd column (='F') |
|
669 KQm4 + ">" + lower2 + " AND " + KQm4 + "<" + upper2 + " AND " + |
|
670 KQm3 + ">" + lower + " AND " + KQm3 + "<" + upper + ")))"; |
|
671 // 5th & 1rd column (='G') |
|
672 KQm5 + ">" + lower2 + " AND " + KQm5 + "<" + upper2 + " AND " + |
|
673 KQm1 + ">" + lower + " AND " + KQm1 + "<" + upper + ")))"; |
|
674 // 5th & 2rd column (='H') |
|
675 KQm5 + ">" + lower2 + " AND " + KQm5 + "<" + upper2 + " AND " + |
|
676 KQm2 + ">" + lower + " AND " + KQm2 + "<" + upper + ")))"; |
|
677 // 5th & 3rd column (='I') |
|
678 KQm5 + ">" + lower2 + " AND " + KQm5 + "<" + upper2 + " AND " + |
|
679 KQm3 + ">" + lower + " AND " + KQm3 + "<" + upper + ")))"; |
|
680 // 5th & 4rd column (='I') |
|
681 KQm5 + ">" + lower2 + " AND " + KQm5 + "<" + upper2 + " AND " + |
|
682 KQm4 + ">" + lower + " AND " + KQm4 + "<" + upper + ")))"; |
|
683 // 6th & 1rd column (='J') |
|
684 KQm6 + ">" + lower2 + " AND " + KQm6 + "<" + upper2 + " AND " + |
|
685 KQm1 + ">" + lower + " AND " + KQm1 + "<" + upper + ")))"; |
|
686 // 6th & 2rd column (='K') |
|
687 KQm6 + ">" + lower2 + " AND " + KQm6 + "<" + upper2 + " AND " + |
|
688 KQm2 + ">" + lower + " AND " + KQm2 + "<" + upper + ")))"; |
|
689 // 6th & 3rd column (='L') |
|
690 KQm6 + ">" + lower2 + " AND " + KQm6 + "<" + upper2 + " AND " + |
|
691 KQm3 + ">" + lower + " AND " + KQm3 + "<" + upper + ")))"; |
|
692 // 6th & 43rd column (='M') |
|
693 KQm6 + ">" + lower2 + " AND " + KQm6 + "<" + upper2 + " AND " + |
|
694 KQm4 + ">" + lower + " AND " + KQm4 + "<" + upper + ")))"; |
|
695 // 6th & 5rd column (='M') |
|
696 KQm6 + ">" + lower2 + " AND " + KQm6 + "<" + upper2 + " AND " + |
|
697 KQm5 + ">" + lower + " AND " + KQm5 + "<" + upper + ")))"; |
|
698 // 7th & 1rd column (='N') |
|
699 KQm7 + ">" + lower2 + " AND " + KQm7 + "<" + upper2 + " AND " + |
|
700 KQm1 + ">" + lower + " AND " + KQm1 + "<" + upper + ")))"; |
|
701 // 7th & 1rd column (='O') |
|
702 KQm7 + ">" + lower2 + " AND " + KQm7 + "<" + upper2 + " AND " + |
|
703 KQm2 + ">" + lower + " AND " + KQm2 + "<" + upper + ")))"; |
|
704 // 7th & 3rd column (='P') |
|
705 KQm7 + ">" + lower2 + " AND " + KQm7 + "<" + upper2 + " AND " + |
|
706 KQm3 + ">" + lower + " AND " + KQm3 + "<" + upper + ")))"; |
|
707 // 7th & 4rd column (='Q') |
|
708 KQm7 + ">" + lower2 + " AND " + KQm7 + "<" + upper2 + " AND " + |
|
709 KQm4 + ">" + lower + " AND " + KQm4 + "<" + upper + ")))"; |
|
710 // 7th & 5rd column (='Q') |
|
711 KQm7 + ">" + lower2 + " AND " + KQm7 + "<" + upper2 + " AND " + |
|
712 KQm5 + ">" + lower + " AND " + KQm5 + "<" + upper + ")))"; |
|
713 // 7th & 5rd column (='R') |
|
714 KQm7 + ">" + lower2 + " AND " + KQm7 + "<" + upper2 + " AND " + |
|
715 KQm6 + ">" + lower + " AND " + KQm6 + "<" + upper + ")))"; |
|
716 |
|
717 return query; |
|
718 } |
|
719 QString CntSqlSearch::ExactMatch(const QString& pattern, QString table) const |
|
720 { |
|
721 QString lower; |
|
722 QString upper; |
|
723 |
|
724 int err = mkeyKeyMap->GetNumericLimits(pattern, lower, upper); |
|
725 if(err) |
|
726 { |
|
727 return QString(""); |
|
728 } |
|
729 |
|
730 if (table.length() > 0) |
|
731 { |
|
732 table += "."; |
|
733 } |
|
734 #if defined(USE_DEMORGAN) |
|
735 // Using De Morgan's law to replace ORs with ANDs: |
|
736 // column1 || column2 || column3 || column4 |
|
737 // -> |
|
738 // (NOT(NOT(column1) AND NOT(column2) AND NOT(column3) AND NOT(column4)) |
|
739 // |
|
740 // Which means: |
|
741 // (NOT(NOT(N>lower && < N<upper) AND NOT(N2>lower && < N2<upper) AND |
|
742 // NOT(N3>lower && < N3<upper) AND NOT(N4>lower && < N4<upper)) |
|
743 // |
|
744 // As KColumn1 is most likely to contain a match, "NOT(KColumn1)" is more |
|
745 // likely to be false than "NOT(KColumn2)" etc. So put KColumn1 first in the |
|
746 // AND statement. |
|
747 return QString("(NOT(NOT(" + |
|
748 table + KColumn1 + ">" + lower + " AND " + table + KColumn1 + "<" + upper + ") AND NOT(" + |
|
749 table + KColumn2 + ">" + lower + " AND " + table + KColumn2 + "<" + upper + ") AND NOT(" + |
|
750 table + KColumn3 + ">" + lower + " AND " + table + KColumn3 + "<" + upper + ") AND NOT(" + |
|
751 table + KColumn4 + ">" + lower + " AND " + table + KColumn4 + "<" + upper + ")))"); |
|
752 #else |
|
753 // Since first column has always some value, and 2nd column is more likely to |
|
754 // have a value than 3rd column etc. Put columns in that order: |
|
755 // (column 1 comparison) OR (column 2 comparison) OR (column 3 comparison) OR |
|
756 // (column 4 comparison) |
|
757 // If 1st column results true, there is no need to compare other columns etc. |
|
758 return QString("(" + |
|
759 table + KColumn1 + ">" + lower + " AND " + table + KColumn1 + "<" + upper + ") OR (" + |
|
760 table + KColumn2 + ">" + lower + " AND " + table + KColumn2 + "<" + upper + ") OR (" + |
|
761 table + KColumn3 + ">" + lower + " AND " + table + KColumn3 + "<" + upper + ") OR (" + |
|
762 table + KColumn4 + ">" + lower + " AND " + table + KColumn4 + "<" + upper + ")"); |
|
763 #endif |
|
764 } |
|
765 |
|
766 QString CntSqlSearch::ExactMatchQwerty(const QString& pattern, QString table) const |
|
767 { |
|
768 QString lower; |
|
769 QString upper; |
|
770 TInt err = mQertyKeyMap->GetNumericLimits(pattern, lower, upper); |
|
771 |
|
772 if (table.length() > 0) |
|
773 { |
|
774 table += "."; |
|
775 } |
|
776 #if defined(USE_DEMORGAN) |
|
777 // Using De Morgan's law to replace ORs with ANDs: |
|
778 // column1 || column2 || column3 || column4 |
|
779 // -> |
|
780 // (NOT(NOT(column1) AND NOT(column2) AND NOT(column3) AND NOT(column4)) |
|
781 // |
|
782 // Which means: |
|
783 // (NOT(NOT(N>lower && < N<upper) AND NOT(N2>lower && < N2<upper) AND |
|
784 // NOT(N3>lower && < N3<upper) AND NOT(N4>lower && < N4<upper)) |
|
785 // |
|
786 // As KColumn1 is most likely to contain a match, "NOT(KColumn1)" is more |
|
787 // likely to be false than "NOT(KColumn2)" etc. So put KColumn1 first in the |
|
788 // AND statement. |
|
789 return QString("(NOT(NOT(" + |
|
790 table + KQm1 + ">" + lower + " AND " + table + KQm1 + "<" + upper + ") AND NOT(" + |
|
791 table + KQm2 + ">" + lower + " AND " + table + KQm2 + "<" + upper + ") AND NOT(" + |
|
792 table + KQm3 + ">" + lower + " AND " + table + KQm3 + "<" + upper + ") AND NOT(" + |
|
793 table + KQm4 + ">" + lower + " AND " + table + KQm4 + "<" + upper + ") AND NOT(" + |
|
794 table + KQm5 + ">" + lower + " AND " + table + KQm5 + "<" + upper + ") AND NOT(" + |
|
795 table + KQm6 + ">" + lower + " AND " + table + KQm6 + "<" + upper + ") AND NOT(" + |
|
796 table + KQm7 + ">" + lower + " AND " + table + KQm7 + "<" + upper + ")))"); |
|
797 #else |
|
798 // Since first column has always some value, and 2nd column is more likely to |
|
799 // have a value than 3rd column etc. Put columns in that order: |
|
800 // (column 1 comparison) OR (column 2 comparison) OR (column 3 comparison) OR |
|
801 // (column 4 comparison) |
|
802 // If 1st column results true, there is no need to compare other columns etc. |
|
803 return QString("(" + |
|
804 table + KQm1 + ">" + lower + " AND " + table + KQm1 + "<" + upper + ") OR (" + |
|
805 table + KQm2 + ">" + lower + " AND " + table + KQm2 + "<" + upper + ") OR (" + |
|
806 table + KQm3 + ">" + lower + " AND " + table + KQm3 + "<" + upper + ") OR (" + |
|
807 table + KQm4 + ">" + lower + " AND " + table + KQm4 + "<" + upper + ") OR (" + |
|
808 table + KQm5 + ">" + lower + " AND " + table + KQm5 + "<" + upper + ") OR (" + |
|
809 table + KQm6 + ">" + lower + " AND " + table + KQm6 + "<" + upper + ") OR (" + |
|
810 table + KQm7 + ">" + lower + " AND " + table + KQm7 + "<" + upper + ")"); |
|
811 #endif |
|
812 } |
|
813 |
|
814 // TODO: if SEARCH_FROM_ONE_TABLE case is slower than the one that searches from |
|
815 // two tables, then this function is needed for cases where pattern is: |
|
816 // "1023", "12300450" |
|
817 // |
|
818 /* This query makes sub-query into table 5 and searches for a number that begins |
|
819 with 56606. but it does not support cases where both tokens are longer than one digit. |
|
820 |
|
821 SELECT predictivesearch5.contact_id FROM predictivesearch5 WHERE EXISTS (SELECT contact_id FROM predictivesearch5 |
|
822 WHERE (predictivesearch5.nbr>389005014883893248 AND predictivesearch5.nbr<389006114395521023) OR |
|
823 (predictivesearch5.nbr2>389005014883893248 AND predictivesearch5.nbr2<389006114395521023) OR |
|
824 (predictivesearch5.nbr3>389005014883893248 AND predictivesearch5.nbr3<389006114395521023) OR |
|
825 (predictivesearch5.nbr4>389005014883893248 AND predictivesearch5.nbr4<389006114395521023)) |
|
826 OR |
|
827 (SELECT predictivesearch5.contact_id FROM predictivesearch5 JOIN predictivesearch6 ON |
|
828 predictivesearch5.contact_id = predictivesearch6.contact_id |
|
829 WHERE(predictivesearch5.nbr>388998417814126592 AND predictivesearch5.nbr<389279892790837247) OR |
|
830 (predictivesearch5.nbr2>388998417814126592 AND predictivesearch5.nbr2<389279892790837247) OR |
|
831 (predictivesearch5.nbr3>388998417814126592 AND predictivesearch5.nbr3<389279892790837247) OR |
|
832 (predictivesearch5.nbr4>388998417814126592 AND predictivesearch5.nbr4<389279892790837247)) |
|
833 ORDER BY predictivesearch5.first_name, predictivesearch5.last_name ASC; |
|
834 |
|
835 SELECT contact_id |
|
836 FROM |
|
837 ( |
|
838 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 |
|
839 WHERE (predictivesearch5.nbr>387415121070129152 AND predictivesearch5.nbr<387432713256173567) OR |
|
840 (predictivesearch5.nbr2>387415121070129152 AND predictivesearch5.nbr2<387432713256173567) OR |
|
841 (predictivesearch5.nbr3>387415121070129152 AND predictivesearch5.nbr3<387432713256173567) OR |
|
842 (predictivesearch5.nbr4>387415121070129152 AND predictivesearch5.nbr4<387432713256173567) |
|
843 UNION |
|
844 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 JOIN predictivesearch6 ON predictivesearch5.contact_id = predictivesearch6.contact_id |
|
845 WHERE((predictivesearch5.nbr>387309567953862656 AND predictivesearch5.nbr<391813167581233151) OR (predictivesearch5.nbr2>387309567953862656 AND predictivesearch5.nbr2<391813167581233151) OR (predictivesearch5.nbr3>387309567953862656 AND predictivesearch5.nbr3<391813167581233151) OR (predictivesearch5.nbr4>387309567953862656 AND predictivesearch5.nbr4<391813167581233151) |
|
846 AND |
|
847 (predictivesearch6.nbr>387309567953862656 AND predictivesearch6.nbr<391813167581233151) OR (predictivesearch6.nbr2>387309567953862656 AND predictivesearch6.nbr2<391813167581233151) OR (predictivesearch6.nbr3>387309567953862656 AND predictivesearch6.nbr3<391813167581233151) OR (predictivesearch6.nbr4>387309567953862656 AND predictivesearch6.nbr4<391813167581233151)) |
|
848 ) AS PR |
|
849 ORDER BY PR.first_name, PR.last_name ASC; |
|
850 |
|
851 Here is a De Morgan version |
|
852 |
|
853 SELECT contact_id |
|
854 FROM |
|
855 ( |
|
856 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 |
|
857 WHERE NOT((NOT (predictivesearch5.nbr >= 387415121070129152 AND predictivesearch5.nbr <= 387432713256173567)) AND (NOT (predictivesearch5.nbr2 >= 387415121070129152 AND predictivesearch5.nbr2 <= 387432713256173567)) AND (NOT (predictivesearch5.nbr3 >= 387415121070129152 AND predictivesearch5.nbr3 <= 387432713256173567)) AND (NOT (predictivesearch5.nbr4 >= 387415121070129152 AND predictivesearch5.nbr4 <= 387432713256173567))) |
|
858 UNION |
|
859 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 JOIN predictivesearch6 ON predictivesearch5.contact_id = predictivesearch6.contact_id |
|
860 WHERE NOT((NOT (predictivesearch5.nbr >= 387309567953862656 AND predictivesearch5.nbr <= 391813167581233151)) AND (NOT (predictivesearch5.nbr2 >= 387309567953862656 AND predictivesearch5.nbr2 <= 391813167581233151)) AND (NOT (predictivesearch5.nbr3 >= 387309567953862656 AND predictivesearch5.nbr3 <= 391813167581233151)) AND (NOT (predictivesearch5.nbr4 >= 387309567953862656 AND predictivesearch5.nbr4 <= 391813167581233151))) |
|
861 AND |
|
862 NOT((NOT (predictivesearch6.nbr >= 387309567953862656 AND predictivesearch6.nbr <= 391813167581233151)) AND (NOT (predictivesearch6.nbr2 >= 387309567953862656 AND predictivesearch6.nbr2 <= 391813167581233151)) AND (NOT (predictivesearch6.nbr3 >= 387309567953862656 AND predictivesearch6.nbr3 <= 391813167581233151)) AND (NOT (predictivesearch6.nbr4 >= 387309567953862656 AND predictivesearch6.nbr4 <= 391813167581233151))) |
|
863 ) AS PR |
|
864 ORDER BY PR.first_name, PR.last_name ASC; |
|
865 |
|
866 */ |
|
867 QString CntSqlSearch::CreateJoinTableSearch(QString pattern, QStringList numbers) const |
|
868 { |
|
869 // It has been checked earlier that tables are not same |
|
870 QString firstTable = SelectTable(numbers.at(0)); |
|
871 QString secondTable = SelectTable(numbers.at(1)); |
|
872 |
|
873 QString queryString = QString("SELECT contact_id FROM (SELECT " |
|
874 + firstTable + ".contact_id, " + firstTable + ".first_name, " + firstTable + ".last_name FROM " + firstTable + |
|
875 " WHERE " + ExactMatch(pattern, firstTable) + |
|
876 " UNION SELECT " + firstTable + ".contact_id, " + firstTable + ".first_name, " + firstTable + ".last_name FROM " + firstTable + " JOIN " + secondTable + " ON " + firstTable + ".contact_id = " + secondTable + ".contact_id WHERE" + |
|
877 ExactMatchColumns(numbers) + ") AS PR ORDER BY PR.first_name, PR.last_name ASC;"); |
|
878 return queryString; |
|
879 } |
|
880 |
|
881 QString CntSqlSearch::ExactMatchColumns(QStringList numbers) const |
|
882 { |
|
883 const int KFirstColumn = 0; |
|
884 const int KSecondColumn = 1; |
|
885 QString firstColumn = numbers.at(KFirstColumn); |
|
886 QString secondColumn = numbers.at(KSecondColumn); |
|
887 |
|
888 if( firstColumn.count() > 1 && secondColumn.count() > 1) |
|
889 { |
|
890 return "(" + ExactMatch(numbers.at(KFirstColumn), SelectTable(numbers.at(KFirstColumn))) |
|
891 + " AND " + |
|
892 ExactMatch(numbers.at(KSecondColumn), SelectTable(numbers.at(KSecondColumn))) + ")"; |
|
893 } |
|
894 else if(firstColumn.count() > 1) |
|
895 { |
|
896 return ExactMatch(numbers.at(KFirstColumn), SelectTable(numbers.at(KFirstColumn))); |
|
897 } |
|
898 else |
|
899 { |
|
900 return ExactMatch(numbers.at(KSecondColumn), SelectTable(numbers.at(KSecondColumn))); |
|
901 } |
|
902 } |
|
903 |
|
904 QString CntSqlSearch::Order(QStringList tokens, QueryType queryType) const |
|
905 { |
|
906 QString table; |
|
907 if (tokens.count() > 1 ) |
|
908 { |
|
909 if(queryType == CntSqlSearch::QwertyEmail) |
|
910 { |
|
911 table = selectQweryTable(tokens.at(0)); |
|
912 } |
|
913 else |
|
914 { |
|
915 table = SelectTable(tokens.at(0)); |
|
916 } |
|
917 return QString(" ORDER BY " + table + ".first_name, " + table + ".last_name ASC;"); |
|
918 } |
|
919 return QString(ORDER_BY_FIRSTNAME_LASTNAME); |
|
920 } |
|
921 |
|
922 QString CntSqlSearch::ChangeStringPadings( const QString &pattern ) const |
|
923 { |
|
924 QString newPattern = pattern; |
|
925 if (QLocale::system().language() == QLocale::Thai) |
|
926 { |
|
927 newPattern.remove(KStarChar, Qt::CaseInsensitive); |
|
928 newPattern.remove(KPlusChar, Qt::CaseInsensitive); |
|
929 newPattern.remove(KPChar, Qt::CaseInsensitive); |
|
930 newPattern.remove(KWChar, Qt::CaseInsensitive); |
|
931 newPattern.remove(KHashChar, Qt::CaseInsensitive); |
|
932 } |
|
933 else |
|
934 { |
|
935 newPattern.replace(KStarChar, 'A'); |
|
936 newPattern.replace(KPlusChar, 'A'); |
|
937 newPattern.replace(KPChar, 'A'); |
|
938 newPattern.replace(KWChar, 'A'); |
|
939 newPattern.replace(KHashChar, 'B'); |
|
940 } |
|
941 return newPattern; |
|
942 } |
|
943 |
|
944 bool CntSqlSearch::TestPattern( const QString &pattern, SearchMethod searchMethod ) const |
|
945 { |
|
946 QStringList tokens = GetTokens(pattern); |
|
947 if (!tokens.isEmpty() && !pattern.isEmpty()) |
|
948 { |
|
949 if (CntSqlSearch::ZerosEndOfFirstToken == searchMethod) |
|
950 { |
|
951 if( tokens.count() == KOneToken && !tokens.at(0).contains("0") |
|
952 && !pattern.startsWith('0') && pattern.count('0') == 1 |
|
953 && pattern.endsWith('0')) |
|
954 { |
|
955 return true; |
|
956 } |
|
957 } |
|
958 if (CntSqlSearch::ZeroIsFirstNumber == searchMethod ) |
|
959 { |
|
960 if(pattern.startsWith('0') && pattern.count() > 1 |
|
961 && pattern.at(1) != '0') |
|
962 { |
|
963 return true; |
|
964 } |
|
965 } |
|
966 } |
|
967 return false; |
|
968 } |
|
969 |
|
970 QString CntSqlSearch::Pad( const QString &pattern, char padChar ) const |
|
971 { |
|
972 int padCount = KLimitLength - pattern.length(); |
|
973 QString result; |
|
974 if ( padCount < 0 ) |
|
975 { |
|
976 result = pattern.left(KLimitLength); |
|
977 } |
|
978 else |
|
979 { |
|
980 result = pattern; |
|
981 for( int i = 0; i < padCount ;i++ ) |
|
982 { |
|
983 result.append(padChar); |
|
984 } |
|
985 } |
|
986 bool ok; |
|
987 // Use signed int to prevent underflow when replaced is "00...00" |
|
988 qint64 value = result.toLongLong(&ok, KHexadecimalBase); |
|
989 if (!ok) |
|
990 { |
|
991 // TODO: handle error (=invalid characters in pattern) |
|
992 } |
|
993 |
|
994 // In order to write queries using '>' and '<' instead of '>=' and '<=', |
|
995 // expand the limit by one. |
|
996 if (padChar == KUpperLimitPadding) |
|
997 { |
|
998 ++value; |
|
999 } |
|
1000 else |
|
1001 { |
|
1002 --value; |
|
1003 } |
|
1004 |
|
1005 return QString::number(value, 10); |
|
1006 } |
|
1007 |
|
1008 bool CntSqlSearch::isQwerty(const QString &pattern) |
|
1009 { |
|
1010 QChar rs(30); |
|
1011 int rs_index = pattern.indexOf(rs); |
|
1012 int qwerty_index = pattern.indexOf("vqwerty"); |
|
1013 if(rs_index >= 0 && qwerty_index >= 0 ) |
|
1014 { |
|
1015 if(rs_index + 1 == qwerty_index) |
|
1016 { |
|
1017 return true; |
|
1018 } |
|
1019 return false; |
|
1020 } |
|
1021 return false; |
|
1022 } |
|
1023 QStringList CntSqlSearch::qwertyTokens(const QString &pattern) const |
|
1024 { |
|
1025 QString decodePatern; |
|
1026 QString keymapsString = mQertyKeyMap->GetMappedString(pattern); |
|
1027 int index = pattern.indexOf(30); |
|
1028 if(index > 0 ) |
|
1029 { |
|
1030 QString decodePatern = keymapsString.left(index); |
|
1031 return decodePatern.split(32, QString::SkipEmptyParts); |
|
1032 } |
|
1033 else |
|
1034 { |
|
1035 return QStringList(""); |
|
1036 } |
|
1037 } |
|
1038 QString CntSqlSearch::UpperLimit( const QString &pattern ) const |
|
1039 { |
|
1040 return Pad( pattern, KUpperLimitPadding ); |
|
1041 } |
|
1042 |
|
1043 QString CntSqlSearch::LowerLimit( const QString &pattern ) const |
|
1044 { |
|
1045 return Pad( pattern, KLowerLimitPadding ); |
|
1046 } |