39 ** |
39 ** |
40 ****************************************************************************/ |
40 ****************************************************************************/ |
41 #include <QStringList> |
41 #include <QStringList> |
42 |
42 |
43 #include "cntsqlsearch.h" |
43 #include "cntsqlsearch.h" |
|
44 #include <QHash> |
|
45 #include <QLocale> |
44 |
46 |
45 const char KLimitLength = 15; |
47 const char KLimitLength = 15; |
46 const int KTwoTokens = 2; |
48 const int KTwoTokens = 2; |
47 const int KOneToken = 1; |
49 const int KOneToken = 1; |
48 const char KLowerLimitPadding = '0'; |
50 const char KLowerLimitPadding = '0'; |
49 const char KUpperLimitPadding = 'F'; |
51 const char KUpperLimitPadding = 'F'; |
50 const int KMinimumSearchPatternLength = 1; |
52 const int KMinimumSearchPatternLength = 1; |
|
53 const int KHexadecimalBase = 16; |
51 |
54 |
52 |
55 |
53 #define ORDER_BY_FIRSTNAME_LASTNAME " ORDER BY first_name, last_name ASC;" |
56 #define ORDER_BY_FIRSTNAME_LASTNAME " ORDER BY first_name, last_name ASC;" |
54 #define SELECT_CONTACT_ID "SELECT contact_id FROM " |
57 #define SELECT_CONTACT_ID "SELECT contact_id FROM " |
55 |
58 |
59 // Predictive search table's columns |
62 // Predictive search table's columns |
60 const QString KColumn1 = "nbr"; |
63 const QString KColumn1 = "nbr"; |
61 const QString KColumn2 = "nbr2"; |
64 const QString KColumn2 = "nbr2"; |
62 const QString KColumn3 = "nbr3"; |
65 const QString KColumn3 = "nbr3"; |
63 const QString KColumn4 = "nbr4"; |
66 const QString KColumn4 = "nbr4"; |
|
67 |
|
68 // Special handling for characters that originate from * and # keys |
|
69 const QChar KStarChar('*'); |
|
70 const QChar KPlusChar('+'); |
|
71 const QChar KPChar('p'); |
|
72 const QChar KWChar('w'); |
|
73 const QChar KHashChar('#'); |
64 |
74 |
65 |
75 |
66 CntSqlSearch::CntSqlSearch() |
76 CntSqlSearch::CntSqlSearch() |
67 { |
77 { |
68 } |
78 } |
98 // In this case, query should look-up first toke and number ("10", "1000"). |
108 // In this case, query should look-up first toke and number ("10", "1000"). |
99 |
109 |
100 QString CntSqlSearch::CreatePredictiveSearch(const QString &pattern) |
110 QString CntSqlSearch::CreatePredictiveSearch(const QString &pattern) |
101 { |
111 { |
102 int len = pattern.length(); |
112 int len = pattern.length(); |
|
113 QString newPattern = ChangeStringPadings(pattern); |
103 // For best performance, handle 1 digit case first |
114 // For best performance, handle 1 digit case first |
104 if (len == KMinimumSearchPatternLength) |
115 if (len == KMinimumSearchPatternLength) |
105 { |
116 { |
106 // Case 1 |
117 // Case 1 |
107 return SELECT_CONTACT_ID + SelectTable(pattern) + ORDER_BY_FIRSTNAME_LASTNAME; |
118 return SELECT_CONTACT_ID + SelectTable(newPattern) + ORDER_BY_FIRSTNAME_LASTNAME; |
108 } |
119 } |
109 if (len <= KLimitLength && len > KMinimumSearchPatternLength) |
120 if (len <= KLimitLength && len > KMinimumSearchPatternLength) |
110 { |
121 { |
111 return CreateQuery(pattern); |
122 return CreateQuery(newPattern); |
112 } |
123 } |
113 |
124 |
114 return QString(""); // Invalid pattern |
125 return QString(""); // Invalid pattern |
115 } |
126 } |
116 |
127 |
117 QString CntSqlSearch::SelectTable(const QString &pattern) const |
128 QString CntSqlSearch::SelectTable(const QString &pattern) const |
118 { |
129 { |
119 QString predictivesearch; |
130 QString predictivesearch; |
120 if (pattern.length() == 0) |
131 QStringList tokens = GetTokens(pattern); |
121 { |
132 bool ok; |
122 return ""; |
133 if (pattern.length() == 0) |
123 } |
134 { |
124 switch (pattern.at(0).digitValue()) |
135 return ""; |
125 { |
136 } |
126 case 0: |
137 QString firstNumber(pattern.at(0)); |
|
138 uint hex = firstNumber.toUInt(&ok, 16); |
|
139 if (!ok) |
127 { |
140 { |
128 predictivesearch = QString("predictivesearch0"); |
141 // TODO: handle error (=invalid characters in pattern) |
129 } |
142 } |
130 break; |
143 switch (hex) |
131 case 1: |
|
132 { |
144 { |
133 predictivesearch = QString("predictivesearch1"); |
145 case 0: |
|
146 { |
|
147 predictivesearch = QString("predictivesearch0"); |
|
148 } |
|
149 break; |
|
150 case 1: |
|
151 { |
|
152 predictivesearch = QString("predictivesearch1"); |
|
153 } |
|
154 break; |
|
155 case 2: |
|
156 { |
|
157 predictivesearch = QString("predictivesearch2"); |
|
158 } |
|
159 break; |
|
160 case 3: |
|
161 { |
|
162 predictivesearch = QString("predictivesearch3"); |
|
163 } |
|
164 break; |
|
165 case 4: |
|
166 { |
|
167 predictivesearch = QString("predictivesearch4"); |
|
168 } |
|
169 break; |
|
170 case 5: |
|
171 { |
|
172 predictivesearch = QString("predictivesearch5"); |
|
173 } |
|
174 break; |
|
175 case 6: |
|
176 { |
|
177 predictivesearch = QString("predictivesearch6"); |
|
178 } |
|
179 break; |
|
180 case 7: |
|
181 { |
|
182 predictivesearch = QString("predictivesearch7"); |
|
183 } |
|
184 break; |
|
185 case 8: |
|
186 { |
|
187 predictivesearch = QString("predictivesearch8"); |
|
188 } |
|
189 break; |
|
190 case 9: |
|
191 { |
|
192 predictivesearch = QString("predictivesearch9"); |
|
193 } |
|
194 break; |
|
195 case 10: |
|
196 { |
|
197 predictivesearch = QString("predictivesearch10"); |
|
198 } |
|
199 break; |
|
200 case 11: |
|
201 { |
|
202 predictivesearch = QString("predictivesearch11"); |
|
203 } |
|
204 break; |
|
205 default: // error |
|
206 predictivesearch = ""; |
|
207 break; |
134 } |
208 } |
135 break; |
209 return predictivesearch; |
136 case 2: |
|
137 { |
|
138 predictivesearch = QString("predictivesearch2"); |
|
139 } |
|
140 break; |
|
141 case 3: |
|
142 { |
|
143 predictivesearch = QString("predictivesearch3"); |
|
144 } |
|
145 break; |
|
146 case 4: |
|
147 { |
|
148 predictivesearch = QString("predictivesearch4"); |
|
149 } |
|
150 break; |
|
151 case 5: |
|
152 { |
|
153 predictivesearch = QString("predictivesearch5"); |
|
154 } |
|
155 break; |
|
156 case 6: |
|
157 { |
|
158 predictivesearch = QString("predictivesearch6"); |
|
159 } |
|
160 break; |
|
161 case 7: |
|
162 { |
|
163 predictivesearch = QString("predictivesearch7"); |
|
164 } |
|
165 break; |
|
166 case 8: |
|
167 { |
|
168 predictivesearch = QString("predictivesearch8"); |
|
169 } |
|
170 break; |
|
171 case 9: |
|
172 { |
|
173 predictivesearch = QString("predictivesearch9"); |
|
174 } |
|
175 break; |
|
176 default: // error |
|
177 predictivesearch = ""; |
|
178 break; |
|
179 } |
|
180 return predictivesearch; |
|
181 } |
210 } |
182 |
211 |
183 // Even if there are over 2 tokens, make 2 tokens. |
212 // Even if there are over 2 tokens, make 2 tokens. |
184 // If there are two or more tokens, include the leading zeros in the first |
213 // If there are two or more tokens, include the leading zeros in the first |
185 // token the and trailing zeros in the second token. |
214 // token the and trailing zeros in the second token. |
186 // E.g. "0010230" results tokens "001" and "230" and |
215 // E.g. "0010230" results tokens "001" and "230" and |
187 // "001230045067800900" tokens "00123" and "45067800900". |
216 // "001230045067800900" tokens "00123" and "45067800900". |
188 QStringList CntSqlSearch::GetTokens(const QString& pattern) const |
217 QStringList CntSqlSearch::GetTokens(const QString& pattern) const |
189 { |
218 { |
190 const QChar KZero('0'); |
219 const QChar KZero('0'); |
191 QStringList tokens = pattern.split(KZero, QString::SkipEmptyParts); |
220 QStringList tokens = pattern.split(KZero, QString::SkipEmptyParts); |
192 if (tokens.count() < KTwoTokens) |
221 if (tokens.count() < KTwoTokens) |
193 { |
222 { |
194 return tokens; |
223 return tokens; |
210 { |
239 { |
211 ++i; |
240 ++i; |
212 } |
241 } |
213 twoTokens.append(pattern.mid(i)); |
242 twoTokens.append(pattern.mid(i)); |
214 return twoTokens; |
243 return twoTokens; |
215 } |
244 } |
216 |
245 |
217 // pattern length is between KMinimumSearchPatternLength...KLimitLength |
246 // pattern length is between KMinimumSearchPatternLength...KLimitLength |
218 QString CntSqlSearch::CreateQuery(const QString& pattern) const |
247 QString CntSqlSearch::CreateQuery(const QString& pattern) const |
219 { |
248 { |
220 QStringList tokens = GetTokens(pattern); |
249 QStringList tokens = GetTokens(pattern); |
221 if (tokens.count() < KTwoTokens) |
250 if (tokens.count() < KTwoTokens) |
222 { |
251 { |
223 if( tokens.count() == KOneToken && !tokens.at(0).contains("0") && !pattern.startsWith('0') && pattern.endsWith('0')) |
252 if( TestPattern(pattern, CntSqlSearch::ZerosEndOfFirstToken)) |
224 { |
253 { |
225 return IdenticalTokensSearch(pattern, tokens) + Order(tokens); // Case 6 |
254 return TwoDifferentTokensSearch(pattern, tokens); // Case 6 |
226 } |
255 } |
227 else |
256 else |
228 { |
257 { |
229 return ExactMatchSearch(pattern) + Order(tokens); // Case 2 |
258 return ExactMatchSearch(pattern) + Order(tokens); // Case 2 |
230 } |
259 } |
314 // |
343 // |
315 // Where X means: (value > lower-limit AND value < upper-limit) |
344 // Where X means: (value > lower-limit AND value < upper-limit) |
316 // and Y means: (value > lower-limit-2 AND value < upper-limit-2) |
345 // and Y means: (value > lower-limit-2 AND value < upper-limit-2) |
317 QString CntSqlSearch::SearchTokensFromOneTable(const QString& pattern, |
346 QString CntSqlSearch::SearchTokensFromOneTable(const QString& pattern, |
318 const QStringList& tokens) const |
347 const QStringList& tokens) const |
319 { |
348 { |
320 QString token = tokens.at(0); |
349 QString token = tokens.at(0); |
321 QString lower = LowerLimit(token); |
350 QString lower = LowerLimit(token); |
322 QString upper = UpperLimit(token); |
351 QString upper = UpperLimit(token); |
323 QString lower2 = LowerLimit(tokens.at(1)); |
352 QString lower2 = LowerLimit(tokens.at(1)); |
324 QString upper2 = UpperLimit(tokens.at(1)); |
353 QString upper2 = UpperLimit(tokens.at(1)); |
360 ") OR " + CompareTwoColumns(lower, upper, lower, upper)); |
389 ") OR " + CompareTwoColumns(lower, upper, lower, upper)); |
361 #endif |
390 #endif |
362 query += Order(tokens); |
391 query += Order(tokens); |
363 return query; |
392 return query; |
364 } |
393 } |
|
394 |
|
395 |
|
396 QString CntSqlSearch::TwoDifferentTokensSearch(const QString& pattern, const QStringList& tokens) const |
|
397 { |
|
398 QString token = tokens.at(0); |
|
399 QString sortPatern = pattern; |
|
400 sortPatern.truncate(pattern.length()-1); |
|
401 #if defined(USE_DEMORGAN) |
|
402 QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" + |
|
403 ExactMatch(sortPatern) + |
|
404 " AND NOT" + ExactMatch(pattern) + ")"); |
|
405 #else |
|
406 QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE (" + |
|
407 ExactMatch(sortPatern) + // exact match (e.g. "2") |
|
408 ") OR " + ExactMatch(pattern)); // exact match (e.g. "20") |
|
409 #endif |
|
410 query += Order(tokens); |
|
411 return query; |
|
412 } |
|
413 |
365 |
414 |
366 // Put individual AND / OR operations in such order that in most cases there is |
415 // Put individual AND / OR operations in such order that in most cases there is |
367 // no need to evaluate all arguments of the AND / OR. |
416 // no need to evaluate all arguments of the AND / OR. |
368 // In case of AND, put the less likely condition on the left side of AND. |
417 // In case of AND, put the less likely condition on the left side of AND. |
369 // In case of OR, put the more likely condition on the left side of OR. |
418 // In case of OR, put the more likely condition on the left side of OR. |
545 ExactMatchColumns(numbers) + ") AS PR ORDER BY PR.first_name, PR.last_name ASC;"); |
594 ExactMatchColumns(numbers) + ") AS PR ORDER BY PR.first_name, PR.last_name ASC;"); |
546 return queryString; |
595 return queryString; |
547 } |
596 } |
548 |
597 |
549 QString CntSqlSearch::ExactMatchColumns(QStringList numbers) const |
598 QString CntSqlSearch::ExactMatchColumns(QStringList numbers) const |
550 { |
599 { |
551 |
600 const int KFirstColumn = 0; |
552 QString firstColumn = numbers.at(0); |
601 const int KSecondColumn = 1; |
553 QString secondColumn = numbers.at(1); |
602 QString firstColumn = numbers.at(KFirstColumn); |
554 |
603 QString secondColumn = numbers.at(KSecondColumn); |
555 if( firstColumn.count() > 1 && secondColumn.count() > 1) |
604 |
556 { |
605 if( firstColumn.count() > 1 && secondColumn.count() > 1) |
557 return "(" + ExactMatch(numbers.at(0), SelectTable(numbers.at(0))) + " AND " + ExactMatch(numbers.at(1), SelectTable(numbers.at(1))) + ")"; |
606 { |
558 } |
607 return "(" + ExactMatch(numbers.at(KFirstColumn), SelectTable(numbers.at(KFirstColumn))) |
559 else if(firstColumn.count() > 1) |
608 + " AND " + |
560 { |
609 ExactMatch(numbers.at(KSecondColumn), SelectTable(numbers.at(KSecondColumn))) + ")"; |
561 return ExactMatch(numbers.at(0), SelectTable(numbers.at(0))); |
610 } |
562 } |
611 else if(firstColumn.count() > 1) |
563 else |
612 { |
564 { |
613 return ExactMatch(numbers.at(KFirstColumn), SelectTable(numbers.at(KFirstColumn))); |
565 return ExactMatch(numbers.at(1), SelectTable(numbers.at(1))); |
614 } |
566 } |
615 else |
567 } |
616 { |
|
617 return ExactMatch(numbers.at(KSecondColumn), SelectTable(numbers.at(KSecondColumn))); |
|
618 } |
|
619 } |
568 |
620 |
569 QString CntSqlSearch::Order(QStringList tokens) const |
621 QString CntSqlSearch::Order(QStringList tokens) const |
570 { |
622 { |
571 if (tokens.count() > 1 ) |
623 if (tokens.count() > 1 ) |
572 { |
624 { |
574 return QString(" ORDER BY " + table + ".first_name, " + table + ".last_name ASC;"); |
626 return QString(" ORDER BY " + table + ".first_name, " + table + ".last_name ASC;"); |
575 } |
627 } |
576 return QString(ORDER_BY_FIRSTNAME_LASTNAME); |
628 return QString(ORDER_BY_FIRSTNAME_LASTNAME); |
577 } |
629 } |
578 |
630 |
|
631 QString CntSqlSearch::ChangeStringPadings( const QString &pattern ) const |
|
632 { |
|
633 QString newPattern = pattern; |
|
634 if (QLocale::system().language() == QLocale::Thai) |
|
635 { |
|
636 newPattern.remove(KStarChar, Qt::CaseInsensitive); |
|
637 newPattern.remove(KPlusChar, Qt::CaseInsensitive); |
|
638 newPattern.remove(KPChar, Qt::CaseInsensitive); |
|
639 newPattern.remove(KWChar, Qt::CaseInsensitive); |
|
640 newPattern.remove(KHashChar, Qt::CaseInsensitive); |
|
641 } |
|
642 else |
|
643 { |
|
644 newPattern.replace(KStarChar, 'A'); |
|
645 newPattern.replace(KPlusChar, 'A'); |
|
646 newPattern.replace(KPChar, 'A'); |
|
647 newPattern.replace(KWChar, 'A'); |
|
648 newPattern.replace(KHashChar, 'B'); |
|
649 } |
|
650 return newPattern; |
|
651 } |
|
652 |
|
653 bool CntSqlSearch::TestPattern( const QString &pattern, SearchMethod searchMethod ) const |
|
654 { |
|
655 QStringList tokens = GetTokens(pattern); |
|
656 if (!tokens.isEmpty() && !pattern.isEmpty()) |
|
657 { |
|
658 if (CntSqlSearch::ZerosEndOfFirstToken == searchMethod) |
|
659 { |
|
660 if( tokens.count() == KOneToken && !tokens.at(0).contains("0") |
|
661 && !pattern.startsWith('0') && pattern.count('0') == 1 |
|
662 && pattern.endsWith('0')) |
|
663 { |
|
664 return true; |
|
665 } |
|
666 } |
|
667 } |
|
668 return false; |
|
669 } |
|
670 |
579 QString CntSqlSearch::Pad( const QString &pattern, char padChar ) const |
671 QString CntSqlSearch::Pad( const QString &pattern, char padChar ) const |
580 { |
672 { |
581 int padCount = KLimitLength - pattern.length(); |
673 int padCount = KLimitLength - pattern.length(); |
582 QString result; |
674 QString result; |
583 if ( padCount < 0 ) |
675 if ( padCount < 0 ) |