40 ****************************************************************************/ |
40 ****************************************************************************/ |
41 #include <QStringList> |
41 #include <QStringList> |
42 |
42 |
43 #include "cntsqlsearch.h" |
43 #include "cntsqlsearch.h" |
44 |
44 |
45 const char LimitLength = 15; |
45 const char KLimitLength = 15; |
46 const char LowerLimitPadding = '0'; |
46 const int KTwoTokens = 2; |
47 const char UpperLimitPadding = 'F'; |
47 const int KOneToken = 1; |
|
48 const char KLowerLimitPadding = '0'; |
|
49 const char KUpperLimitPadding = 'F'; |
|
50 const int KMinimumSearchPatternLength = 1; |
|
51 |
|
52 |
|
53 #define ORDER_BY_FIRSTNAME_LASTNAME " ORDER BY first_name, last_name ASC;" |
|
54 #define SELECT_CONTACT_ID "SELECT contact_id FROM " |
|
55 |
|
56 // TODO: Since the column names are repeated several times, replace them with |
|
57 // shorter names like w, x, y & z. Also replace contact_id by id etc. |
|
58 |
|
59 // Predictive search table's columns |
|
60 const QString KColumn1 = "nbr"; |
|
61 const QString KColumn2 = "nbr2"; |
|
62 const QString KColumn3 = "nbr3"; |
|
63 const QString KColumn4 = "nbr4"; |
48 |
64 |
49 |
65 |
50 CntSqlSearch::CntSqlSearch() |
66 CntSqlSearch::CntSqlSearch() |
51 { |
67 { |
52 |
68 } |
53 |
69 |
54 } |
70 // Basic cases: |
|
71 // 1: "0", "5" |
|
72 // Just one digit. Select all contact ids from the table. No need to compare |
|
73 // values. |
|
74 // |
|
75 // 2: "123", "01", "10", "010", "00" |
|
76 // No zeros which have non-zeros in their both sides |
|
77 // One or zero tokens, when pattern is split using '0'. |
|
78 // |
|
79 // 3: "101", "1001" |
|
80 // Same digit of both sides of the zero |
|
81 // Two tokens, each with length of 1 and tokens are the same. |
|
82 // The queries of case 3 could also be handled with case 4 query, but that |
|
83 // would yield a longer SQL statement. |
|
84 // |
|
85 // 4: "102", "1002" |
|
86 // One or more zeros in the middle, just one digit on either side of the zero(s) |
|
87 // and those digits are not the same. |
|
88 // Two tokens, each with length of 1 and tokens are different. |
|
89 // |
|
90 // 5: "1023", "0102", "1010", "00100200", "10203", "01020304050" |
|
91 // Two tokens, at least one of them has length > 1. |
|
92 // If tokens are identical, handle as case 3, otherwise handle as case 4. |
|
93 // ("10203" -> tokens "1" and "203" |
|
94 // "0010023004560" -> tokens "001" and "23004560") |
|
95 // |
|
96 // 6: "10", "1000" |
|
97 // Two tokens, last token ends zero. |
|
98 // In this case, query should look-up first toke and number ("10", "1000"). |
55 |
99 |
56 QString CntSqlSearch::CreatePredictiveSearch(const QString &pattern) |
100 QString CntSqlSearch::CreatePredictiveSearch(const QString &pattern) |
57 { |
101 { |
58 if (pattern.length() > 15) |
102 int len = pattern.length(); |
59 { |
103 // For best performance, handle 1 digit case first |
60 return QString(""); |
104 if (len == KMinimumSearchPatternLength) |
61 } |
105 { |
62 else if (pattern.length() == 1) |
106 // Case 1 |
63 { |
107 return SELECT_CONTACT_ID + SelectTable(pattern) + ORDER_BY_FIRSTNAME_LASTNAME; |
64 return "SELECT contact_id FROM " + SelectTableView(pattern) + " ORDER BY first_name, last_name ASC;"; |
108 } |
|
109 if (len <= KLimitLength && len > KMinimumSearchPatternLength) |
|
110 { |
|
111 return CreateQuery(pattern); |
|
112 } |
|
113 |
|
114 return QString(""); // Invalid pattern |
|
115 } |
|
116 |
|
117 QString CntSqlSearch::SelectTable(const QString &pattern) const |
|
118 { |
|
119 QString predictivesearch; |
|
120 if (pattern.length() == 0) |
|
121 { |
|
122 return ""; |
|
123 } |
|
124 switch (pattern.at(0).digitValue()) |
|
125 { |
|
126 case 0: |
|
127 { |
|
128 predictivesearch = QString("predictivesearch0"); |
|
129 } |
|
130 break; |
|
131 case 1: |
|
132 { |
|
133 predictivesearch = QString("predictivesearch1"); |
|
134 } |
|
135 break; |
|
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 } |
|
182 |
|
183 // 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 |
|
185 // token the and trailing zeros in the second token. |
|
186 // E.g. "0010230" results tokens "001" and "230" and |
|
187 // "001230045067800900" tokens "00123" and "45067800900". |
|
188 QStringList CntSqlSearch::GetTokens(const QString& pattern) const |
|
189 { |
|
190 const QChar KZero('0'); |
|
191 QStringList tokens = pattern.split(KZero, QString::SkipEmptyParts); |
|
192 if (tokens.count() < KTwoTokens) |
|
193 { |
|
194 return tokens; |
|
195 } |
|
196 |
|
197 QStringList twoTokens; |
|
198 int i(0); |
|
199 while (pattern[i] == KZero) // Skip leading zeros |
|
200 { |
|
201 ++i; |
|
202 } |
|
203 while (pattern[i] != KZero) // Skip non-zeros to find where first token ends |
|
204 { |
|
205 ++i; |
|
206 } |
|
207 twoTokens.append(pattern.left(i)); |
|
208 |
|
209 while (pattern[i] == KZero) // Skip zeros to find where second token begins |
|
210 { |
|
211 ++i; |
|
212 } |
|
213 twoTokens.append(pattern.mid(i)); |
|
214 return twoTokens; |
|
215 } |
|
216 |
|
217 // pattern length is between KMinimumSearchPatternLength...KLimitLength |
|
218 QString CntSqlSearch::CreateQuery(const QString& pattern) const |
|
219 { |
|
220 QStringList tokens = GetTokens(pattern); |
|
221 if (tokens.count() < KTwoTokens) |
|
222 { |
|
223 if( tokens.count() == KOneToken && !tokens.at(0).contains("0") && !pattern.startsWith('0') && pattern.endsWith('0')) |
|
224 { |
|
225 return IdenticalTokensSearch(pattern, tokens) + Order(tokens); // Case 6 |
|
226 } |
|
227 else |
|
228 { |
|
229 return ExactMatchSearch(pattern) + Order(tokens); // Case 2 |
|
230 } |
|
231 } |
|
232 else |
|
233 { |
|
234 if (tokens.at(0) == tokens.at(1)) |
|
235 { |
|
236 return IdenticalTokensSearch(pattern, tokens); // Case 3 |
|
237 } |
|
238 else |
|
239 { |
|
240 return IntersectionSearch(pattern, tokens); // Case 4 |
|
241 } |
|
242 } |
|
243 } |
|
244 |
|
245 QString CntSqlSearch::ExactMatchSearch(const QString& pattern) const |
|
246 { |
|
247 return QString(SELECT_CONTACT_ID + SelectTable(pattern) + |
|
248 " WHERE " + ExactMatch(pattern)); |
|
249 } |
|
250 |
|
251 // It has been checked that tokens are different, but they might begin with |
|
252 // the same digit. |
|
253 QString CntSqlSearch::IntersectionSearch(const QString& pattern, |
|
254 const QStringList& tokens) const |
|
255 { |
|
256 #if defined(SEARCH_FROM_ONE_TABLE) |
|
257 return SearchTokensFromOneTable(pattern, tokens); |
|
258 #else |
|
259 /* Query for pattern = "205": |
|
260 SELECT predictivesearch2.contact_id FROM predictivesearch2 WHERE EXISTS |
|
261 (SELECT contact_id FROM predictivesearch5 WHERE predictivesearch2.contact_id = predictivesearch5.contact_id) |
|
262 OR |
|
263 (SELECT contact_id FROM predictivesearch2 |
|
264 WHERE(predictivesearch2.nbr>145522562959409152 AND predictivesearch2.nbr<145804037936119807) OR |
|
265 (predictivesearch2.nbr2>145522562959409152 AND predictivesearch2.nbr2<145804037936119807) OR |
|
266 (predictivesearch2.nbr3>145522562959409152 AND predictivesearch2.nbr3<145804037936119807) OR |
|
267 (predictivesearch2.nbr4>145522562959409152 AND predictivesearch2.nbr4<145804037936119807)) |
|
268 ORDER BY predictivesearch2.first_name, predictivesearch2.last_name ASC; |
|
269 |
|
270 This query works if both tokens have just one digit (e.g. "102", but not "1023") |
|
271 */ |
|
272 if (tokens.at(0).length() == KMinimumSearchPatternLength && |
|
273 tokens.at(1).length() == KMinimumSearchPatternLength) |
|
274 { |
|
275 // Case 4 |
|
276 QString firstTable = SelectTable(tokens.at(0)); |
|
277 QString secondTable = SelectTable(tokens.at(1)); |
|
278 QString query = |
|
279 "SELECT " + firstTable + ".contact_id FROM " + firstTable + |
|
280 " WHERE EXISTS (" + SELECT_CONTACT_ID + secondTable + " WHERE " + |
|
281 firstTable + ".contact_id = " + secondTable + ".contact_id) OR (" + |
|
282 SELECT_CONTACT_ID + firstTable + " WHERE " + ExactMatch(pattern, firstTable) + ")"; |
|
283 return query + Order(tokens); |
|
284 } |
|
285 if (tokens.at(0).at(0) == tokens.at(1).at(0) || |
|
286 tokens.at(0).length() > 1 && tokens.at(1).length() > 1) |
|
287 { |
|
288 // Tokens begin with same digit or both tokens are longer than one digit. |
|
289 // Must search from one table. |
|
290 return SearchTokensFromOneTable(pattern, tokens); |
|
291 } |
|
292 return CreateJoinTableSearch(pattern, tokens); // Case 5 |
|
293 #endif |
|
294 } |
|
295 |
|
296 // Find the exact match, or a column whose value is within |
|
297 // lower..upper(exclusive) and another column whose value is within |
|
298 // lower2..upper2(exclusive). |
|
299 // In this case the limits are is different, so there are 12 combinations the |
|
300 // two values can exist in four columns: |
|
301 // |
|
302 // (column = X AND column2 = Y) OR |
|
303 // (column = X AND column3 = Y) OR |
|
304 // (column = X AND column4 = Y) OR |
|
305 // (column2 = X AND column3 = Y) OR |
|
306 // (column2 = X AND column4 = Y) OR |
|
307 // (column3 = X AND column4 = Y) OR |
|
308 // (column = Y AND column2 = X) OR |
|
309 // (column = Y AND column3 = X) OR |
|
310 // (column = Y AND column4 = X) OR |
|
311 // (column2 = Y AND column3 = X) OR |
|
312 // (column2 = Y AND column4 = X) OR |
|
313 // (column3 = Y AND column4 = X) |
|
314 // |
|
315 // Where X means: (value > lower-limit AND value < upper-limit) |
|
316 // and Y means: (value > lower-limit-2 AND value < upper-limit-2) |
|
317 QString CntSqlSearch::SearchTokensFromOneTable(const QString& pattern, |
|
318 const QStringList& tokens) const |
|
319 { |
|
320 QString token = tokens.at(0); |
|
321 QString lower = LowerLimit(token); |
|
322 QString upper = UpperLimit(token); |
|
323 QString lower2 = LowerLimit(tokens.at(1)); |
|
324 QString upper2 = UpperLimit(tokens.at(1)); |
|
325 |
|
326 QString query = |
|
327 #if defined(USE_DEMORGAN) |
|
328 SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" + |
|
329 ExactMatch(pattern) + " AND NOT" + |
|
330 CompareTwoColumns(lower, upper, lower2, upper2) + " AND NOT" + |
|
331 CompareTwoColumns(lower2, upper2, lower, upper) + ")"; |
|
332 #else |
|
333 SELECT_CONTACT_ID + SelectTable(token) + " WHERE (" + |
|
334 // exact match (e.g. "102") |
|
335 ExactMatch(pattern) + ") OR " + |
|
336 CompareTwoColumns(lower, upper, lower2, upper2) + " OR " + |
|
337 CompareTwoColumns(lower2, upper2, lower, upper); |
|
338 #endif |
|
339 query += Order(tokens); |
|
340 return query; |
|
341 } |
|
342 |
|
343 // Either an exact match is required, or tokens must be found, but not in the |
|
344 // same column. |
|
345 // Since tokens are identical, they have same limits, and one call to |
|
346 // CompareTwoColumns() is enough. |
|
347 QString CntSqlSearch::IdenticalTokensSearch(const QString& pattern, |
|
348 const QStringList& tokens) const |
|
349 { |
|
350 QString token = tokens.at(0); |
|
351 QString lower = LowerLimit(token); |
|
352 QString upper = UpperLimit(token); |
|
353 #if defined(USE_DEMORGAN) |
|
354 QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" + |
|
355 ExactMatch(pattern) + |
|
356 " AND NOT" + CompareTwoColumns(lower, upper, lower, upper) + ")"); |
|
357 #else |
|
358 QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE (" + |
|
359 ExactMatch(pattern) + // exact match (e.g. "101") |
|
360 ") OR " + CompareTwoColumns(lower, upper, lower, upper)); |
|
361 #endif |
|
362 query += Order(tokens); |
|
363 return query; |
|
364 } |
|
365 |
|
366 // 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. |
|
368 // 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. |
|
370 // Since 2nd column is more likely to be empty, compare it before 1st column. |
|
371 // Since 1st & 2nd columns are more likely to contain a match, compare them |
|
372 // before other column combinations (1st & 3rd, 2nd & 3rd etc) |
|
373 QString CntSqlSearch::CompareTwoColumns(const QString& lower, |
|
374 const QString& upper, |
|
375 const QString& lower2, |
|
376 const QString& upper2) const |
|
377 { |
|
378 #if defined(USE_DEMORGAN) |
|
379 // Using De Morgan's law to replace ORs with ANDs: |
|
380 // A || B || C || D || E || F --> !(!A && !B && !C && !D && !E && !F) |
|
381 // |
|
382 // As A (match found in columns 1 and 2) is more likely true than other |
|
383 // combinations, then !A is more likely false than other combinations, so |
|
384 // it is put first in the AND statement. |
|
385 QString query = |
|
386 "(NOT(NOT(" + |
|
387 // 2nd & 1st column (='A') |
|
388 KColumn2 + ">" + lower2 + " AND " + KColumn2 + "<" + upper2 + " AND " + |
|
389 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
390 ") AND NOT(" + |
|
391 // 3nd & 1st column (='B') |
|
392 KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " + |
|
393 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
394 ") AND NOT(" + |
|
395 // 3rd & 2nd column (='C') |
|
396 KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " + |
|
397 KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper + |
|
398 ") AND NOT(" + |
|
399 // 4th & 1st column (='D') |
|
400 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
401 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
402 ") AND NOT(" + |
|
403 // 4th & 2nd column (='E') |
|
404 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
405 KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper + |
|
406 ") AND NOT(" + |
|
407 // 4th & 3rd column (='F') |
|
408 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
409 KColumn3 + ">" + lower + " AND " + KColumn3 + "<" + upper + ")))"; |
|
410 #else |
|
411 QString query = |
|
412 "(" + |
|
413 // 2nd & 1st column |
|
414 KColumn2 + ">" + lower2 + " AND " + KColumn2 + "<" + upper2 + " AND " + |
|
415 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
416 ") OR (" + |
|
417 // 3nd & 1st column |
|
418 KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " + |
|
419 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
420 ") OR (" + |
|
421 // 3rd & 2nd column |
|
422 KColumn3 + ">" + lower2 + " AND " + KColumn3 + "<" + upper2 + " AND " + |
|
423 KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper + |
|
424 ") OR (" + |
|
425 // 4th & 1st column |
|
426 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
427 KColumn1 + ">" + lower + " AND " + KColumn1 + "<" + upper + |
|
428 ") OR (" + |
|
429 // 4th & 2nd column |
|
430 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
431 KColumn2 + ">" + lower + " AND " + KColumn2 + "<" + upper + |
|
432 ") OR (" + |
|
433 // 4th & 3rd column |
|
434 KColumn4 + ">" + lower2 + " AND " + KColumn4 + "<" + upper2 + " AND " + |
|
435 KColumn3 + ">" + lower + " AND " + KColumn3 + "<" + upper + ")"; |
|
436 #endif |
|
437 return query; |
|
438 } |
|
439 |
|
440 QString CntSqlSearch::ExactMatch(const QString& pattern, QString table) const |
|
441 { |
|
442 QString lower = LowerLimit(pattern); |
|
443 QString upper = UpperLimit(pattern); |
|
444 |
|
445 if (table.length() > 0) |
|
446 { |
|
447 table += "."; |
|
448 } |
|
449 #if defined(USE_DEMORGAN) |
|
450 // Using De Morgan's law to replace ORs with ANDs: |
|
451 // column1 || column2 || column3 || column4 |
|
452 // -> |
|
453 // (NOT(NOT(column1) AND NOT(column2) AND NOT(column3) AND NOT(column4)) |
|
454 // |
|
455 // Which means: |
|
456 // (NOT(NOT(N>lower && < N<upper) AND NOT(N2>lower && < N2<upper) AND |
|
457 // NOT(N3>lower && < N3<upper) AND NOT(N>lower && < N<upper)) |
|
458 // |
|
459 // As KColumn1 is most likely to contain a match, "NOT(KColumn1)" is more |
|
460 // likely to be false than "NOT(KColumn2)" etc. So put KColumn1 first in the |
|
461 // AND statement. |
|
462 return QString("(NOT(NOT(" + |
|
463 table + KColumn1 + ">" + lower + " AND " + table + KColumn1 + "<" + upper + ") AND NOT(" + |
|
464 table + KColumn2 + ">" + lower + " AND " + table + KColumn2 + "<" + upper + ") AND NOT(" + |
|
465 table + KColumn3 + ">" + lower + " AND " + table + KColumn3 + "<" + upper + ") AND NOT(" + |
|
466 table + KColumn4 + ">" + lower + " AND " + table + KColumn4 + "<" + upper + ")))"); |
|
467 #else |
|
468 // Since first column has always some value, and 2nd column is more likely to |
|
469 // have a value than 3rd column etc. Put columns in that order: |
|
470 // (column 1 comparison) OR (column 2 comparison) OR (column 3 comparison) OR |
|
471 // (column 4 comparison) |
|
472 // If 1st column results true, there is no need to compare other columns etc. |
|
473 return QString("(" + |
|
474 table + KColumn1 + ">" + lower + " AND " + table + KColumn1 + "<" + upper + ") OR (" + |
|
475 table + KColumn2 + ">" + lower + " AND " + table + KColumn2 + "<" + upper + ") OR (" + |
|
476 table + KColumn3 + ">" + lower + " AND " + table + KColumn3 + "<" + upper + ") OR (" + |
|
477 table + KColumn4 + ">" + lower + " AND " + table + KColumn4 + "<" + upper + ")"); |
|
478 #endif |
|
479 } |
|
480 |
|
481 |
|
482 // TODO: if SEARCH_FROM_ONE_TABLE case is slower than the one that searches from |
|
483 // two tables, then this function is needed for cases where pattern is: |
|
484 // "1023", "12300450" |
|
485 // |
|
486 /* This query makes sub-query into table 5 and searches for a number that begins |
|
487 with 56606. but it does not support cases where both tokens are longer than one digit. |
|
488 |
|
489 SELECT predictivesearch5.contact_id FROM predictivesearch5 WHERE EXISTS (SELECT contact_id FROM predictivesearch5 |
|
490 WHERE (predictivesearch5.nbr>389005014883893248 AND predictivesearch5.nbr<389006114395521023) OR |
|
491 (predictivesearch5.nbr2>389005014883893248 AND predictivesearch5.nbr2<389006114395521023) OR |
|
492 (predictivesearch5.nbr3>389005014883893248 AND predictivesearch5.nbr3<389006114395521023) OR |
|
493 (predictivesearch5.nbr4>389005014883893248 AND predictivesearch5.nbr4<389006114395521023)) |
|
494 OR |
|
495 (SELECT predictivesearch5.contact_id FROM predictivesearch5 JOIN predictivesearch6 ON |
|
496 predictivesearch5.contact_id = predictivesearch6.contact_id |
|
497 WHERE(predictivesearch5.nbr>388998417814126592 AND predictivesearch5.nbr<389279892790837247) OR |
|
498 (predictivesearch5.nbr2>388998417814126592 AND predictivesearch5.nbr2<389279892790837247) OR |
|
499 (predictivesearch5.nbr3>388998417814126592 AND predictivesearch5.nbr3<389279892790837247) OR |
|
500 (predictivesearch5.nbr4>388998417814126592 AND predictivesearch5.nbr4<389279892790837247)) |
|
501 ORDER BY predictivesearch5.first_name, predictivesearch5.last_name ASC; |
|
502 |
|
503 SELECT contact_id |
|
504 FROM |
|
505 ( |
|
506 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 |
|
507 WHERE (predictivesearch5.nbr>387415121070129152 AND predictivesearch5.nbr<387432713256173567) OR |
|
508 (predictivesearch5.nbr2>387415121070129152 AND predictivesearch5.nbr2<387432713256173567) OR |
|
509 (predictivesearch5.nbr3>387415121070129152 AND predictivesearch5.nbr3<387432713256173567) OR |
|
510 (predictivesearch5.nbr4>387415121070129152 AND predictivesearch5.nbr4<387432713256173567) |
|
511 UNION |
|
512 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 JOIN predictivesearch6 ON predictivesearch5.contact_id = predictivesearch6.contact_id |
|
513 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) |
|
514 AND |
|
515 (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)) |
|
516 ) AS PR |
|
517 ORDER BY PR.first_name, PR.last_name ASC; |
|
518 |
|
519 Here is a De Morgan version |
|
520 |
|
521 SELECT contact_id |
|
522 FROM |
|
523 ( |
|
524 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 |
|
525 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))) |
|
526 UNION |
|
527 SELECT predictivesearch5.contact_id, predictivesearch5.first_name, predictivesearch5.last_name FROM predictivesearch5 JOIN predictivesearch6 ON predictivesearch5.contact_id = predictivesearch6.contact_id |
|
528 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))) |
|
529 AND |
|
530 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))) |
|
531 ) AS PR |
|
532 ORDER BY PR.first_name, PR.last_name ASC; |
|
533 |
|
534 */ |
|
535 QString CntSqlSearch::CreateJoinTableSearch(QString pattern, QStringList numbers) const |
|
536 { |
|
537 // It has been checked earlier that tables are not same |
|
538 QString firstTable = SelectTable(numbers.at(0)); |
|
539 QString secondTable = SelectTable(numbers.at(1)); |
|
540 |
|
541 QString queryString = QString("SELECT contact_id FROM (SELECT " |
|
542 + firstTable + ".contact_id, " + firstTable + ".first_name, " + firstTable + ".last_name FROM " + firstTable + |
|
543 " WHERE " + ExactMatch(pattern, firstTable) + |
|
544 " UNION SELECT " + firstTable + ".contact_id, " + firstTable + ".first_name, " + firstTable + ".last_name FROM " + firstTable + " JOIN " + secondTable + " ON " + firstTable + ".contact_id = " + secondTable + ".contact_id WHERE" + |
|
545 ExactMatchColumns(numbers) + ") AS PR ORDER BY PR.first_name, PR.last_name ASC;"); |
|
546 return queryString; |
|
547 } |
|
548 |
|
549 QString CntSqlSearch::ExactMatchColumns(QStringList numbers) const |
|
550 { |
|
551 |
|
552 QString firstColumn = numbers.at(0); |
|
553 QString secondColumn = numbers.at(1); |
|
554 |
|
555 if( firstColumn.count() > 1 && secondColumn.count() > 1) |
|
556 { |
|
557 return "(" + ExactMatch(numbers.at(0), SelectTable(numbers.at(0))) + " AND " + ExactMatch(numbers.at(1), SelectTable(numbers.at(1))) + ")"; |
|
558 } |
|
559 else if(firstColumn.count() > 1) |
|
560 { |
|
561 return ExactMatch(numbers.at(0), SelectTable(numbers.at(0))); |
|
562 } |
|
563 else |
|
564 { |
|
565 return ExactMatch(numbers.at(1), SelectTable(numbers.at(1))); |
|
566 } |
|
567 } |
|
568 |
|
569 QString CntSqlSearch::Order(QStringList tokens) const |
|
570 { |
|
571 if (tokens.count() > 1 ) |
|
572 { |
|
573 QString table = SelectTable(tokens.at(0)); |
|
574 return QString(" ORDER BY " + table + ".first_name, " + table + ".last_name ASC;"); |
|
575 } |
|
576 return QString(ORDER_BY_FIRSTNAME_LASTNAME); |
|
577 } |
|
578 |
|
579 QString CntSqlSearch::Pad( const QString &pattern, char padChar ) const |
|
580 { |
|
581 int padCount = KLimitLength - pattern.length(); |
|
582 QString result; |
|
583 if ( padCount < 0 ) |
|
584 { |
|
585 result = pattern.left(KLimitLength); |
65 } |
586 } |
66 else |
587 else |
67 { |
588 { |
68 return CreateSubStringSearch(pattern); |
589 result = pattern; |
69 } |
590 for( int i = 0; i < padCount ;i++ ) |
70 } |
591 { |
71 |
592 result.append(padChar); |
72 QString CntSqlSearch::SelectTableView(const QString &pattern) |
593 } |
73 { |
594 } |
74 QString predictivesearch; |
595 const int KHexadecimalBase = 16; |
75 int index; |
596 bool ok; |
76 int num; |
597 // Use signed int to prevent underflow when replaced is "00...00" |
77 if (pattern.contains("0")) |
598 qint64 value = result.toLongLong(&ok, KHexadecimalBase); |
78 { |
599 if (!ok) |
79 index = pattern.indexOf("0"); |
600 { |
80 if(index == pattern.length() - 1 ) |
601 // TODO: handle error (=invalid characters in pattern) |
81 { |
602 } |
82 num = 0; |
603 |
83 } |
604 // In order to write queries using '>' and '<' instead of '>=' and '<=', |
84 else |
605 // expand the limit by one. |
85 { |
606 if (padChar == KUpperLimitPadding) |
86 num = pattern.at(index + 1).digitValue(); |
607 { |
87 } |
608 ++value; |
88 } |
609 } |
89 else |
610 else |
90 { |
611 { |
91 num = pattern.at(0).digitValue(); |
612 --value; |
92 } |
613 } |
93 |
614 |
94 switch (num) |
615 return QString::number(value, 10); |
95 { |
616 } |
96 |
617 |
97 case 0: |
618 QString CntSqlSearch::UpperLimit( const QString &pattern ) const |
98 { |
|
99 predictivesearch = QString("predictivesearch0"); |
|
100 } |
|
101 break; |
|
102 case 1: |
|
103 { |
|
104 predictivesearch = QString("predictivesearch1"); |
|
105 } |
|
106 break; |
|
107 case 2: |
|
108 { |
|
109 predictivesearch = QString("predictivesearch2"); |
|
110 } |
|
111 break; |
|
112 case 3: |
|
113 { |
|
114 predictivesearch = QString("predictivesearch3"); |
|
115 } |
|
116 break; |
|
117 case 4: |
|
118 { |
|
119 predictivesearch = QString("predictivesearch4"); |
|
120 } |
|
121 break; |
|
122 case 5: |
|
123 { |
|
124 predictivesearch = QString("predictivesearch5"); |
|
125 } |
|
126 break; |
|
127 case 6: |
|
128 { |
|
129 predictivesearch = QString("predictivesearch6"); |
|
130 } |
|
131 break; |
|
132 case 7: |
|
133 { |
|
134 predictivesearch = QString("predictivesearch7"); |
|
135 } |
|
136 break; |
|
137 case 8: |
|
138 { |
|
139 predictivesearch = QString("predictivesearch8"); |
|
140 } |
|
141 break; |
|
142 case 9: |
|
143 { |
|
144 predictivesearch = QString("predictivesearch9"); |
|
145 } |
|
146 break; |
|
147 } |
|
148 return predictivesearch; |
|
149 } |
|
150 |
|
151 bool CntSqlSearch::IsSubStringSearch(const QString &pattern) |
|
152 { |
|
153 const QChar zero('0'); |
|
154 if (pattern.count( "0", Qt::CaseSensitive ) == pattern.count() ) |
|
155 { |
619 { |
156 return false; |
620 return Pad( pattern, KUpperLimitPadding ); |
157 } |
621 } |
158 else if (pattern.contains(zero)) |
622 |
|
623 QString CntSqlSearch::LowerLimit( const QString &pattern ) const |
159 { |
624 { |
160 return true; |
625 return Pad( pattern, KLowerLimitPadding ); |
161 } |
626 } |
162 else |
|
163 { |
|
164 return false; |
|
165 } |
|
166 } |
|
167 |
|
168 QStringList CntSqlSearch::GetNumber(const QString &pattern) |
|
169 { |
|
170 const QChar zero('0'); |
|
171 return pattern.split(zero, QString::SkipEmptyParts); |
|
172 } |
|
173 QString CntSqlSearch::CreateSubStringSearch(const QString &pattern) |
|
174 { |
|
175 QString queryString; |
|
176 QStringList numbers; |
|
177 numbers = GetNumber(pattern); |
|
178 |
|
179 if (IsSubStringSearch(pattern) && numbers.count() > 1 ) |
|
180 { |
|
181 //Case 203 |
|
182 queryString = CreateSpaceStringSearch(numbers, pattern) + Order(numbers); |
|
183 } |
|
184 else if (IsSubStringSearch(pattern) && numbers.count() < 1 ) |
|
185 { |
|
186 //Case 01 |
|
187 queryString = CreateStringSearch(pattern) + Order(numbers); |
|
188 } |
|
189 else |
|
190 { |
|
191 //Case 33 |
|
192 queryString = CreateStringSearch(pattern) + Order(numbers); |
|
193 } |
|
194 |
|
195 return queryString; |
|
196 } |
|
197 |
|
198 QString CntSqlSearch::CreateStringSearch(const QString &pattern ) |
|
199 { |
|
200 QString queryString; |
|
201 |
|
202 |
|
203 return QString("SELECT contact_id FROM " + SelectTableView(pattern) + |
|
204 " WHERE " + CreateLimit(pattern)); |
|
205 } |
|
206 |
|
207 QString CntSqlSearch::CreateSpaceStringSearch(QStringList numbers, const QString &pattern) |
|
208 { |
|
209 /*if(numbers.at(0) == numbers.at(1)) |
|
210 { |
|
211 |
|
212 } |
|
213 else*/ |
|
214 { |
|
215 if((numbers.at(0).length() > 1 || numbers.at(1).length() > 1) && |
|
216 (pattern.startsWith('0') || pattern.endsWith('0'))) |
|
217 { |
|
218 return QString(CreateJoinTableSearch(numbers) + |
|
219 " OR (" + CreateJoinTableLimit(lowerLimit(pattern), upperLimit(pattern), SelectTableView(numbers.at(0))) + ")" + |
|
220 " OR (" + CreateJoinTableLimit(lowerLimit(pattern), upperLimit(pattern), SelectTableView(numbers.at(1)))) + ")"; |
|
221 } |
|
222 else if(numbers.at(0).length() > 1 || numbers.at(1).length() > 1 ) |
|
223 { |
|
224 return CreateJoinTableSearch(numbers); |
|
225 } |
|
226 else |
|
227 { |
|
228 return CreateSpaceSimpleSearch(numbers); |
|
229 } |
|
230 } |
|
231 } |
|
232 |
|
233 QString CntSqlSearch::CreateSpaceSimpleSearch(QStringList numbers) |
|
234 { |
|
235 QString firstTable = SelectTableView(numbers.at(0)); |
|
236 QString secondTable = SelectTableView(numbers.at(1)); |
|
237 QString queryString; |
|
238 |
|
239 queryString ="SELECT " + firstTable + ".contact_id FROM " + firstTable + " WHERE EXISTS (SELECT contact_id FROM " + secondTable + |
|
240 " WHERE " + firstTable + ".contact_id = " + secondTable + ".contact_id)"; |
|
241 return queryString; |
|
242 } |
|
243 |
|
244 QString CntSqlSearch::CreateLimit(QString pattern) |
|
245 { |
|
246 QString low = lowerLimit(pattern); |
|
247 QString upp = upperLimit(pattern); |
|
248 /*return QString("(nbr>" +low + " AND nbr<" + upp + |
|
249 ") OR (nbr2>" +low + " AND nbr2<" + upp + |
|
250 ") OR (nbr3>" +low + " AND nbr3<" + upp + |
|
251 ") OR (nbr4>" +low + " AND nbr4<" + upp + ")");*/ |
|
252 |
|
253 return "NOT((NOT (nbr >= " + low + " AND nbr <= " + upp + |
|
254 ")) AND (NOT (nbr2 >= " + low + " AND nbr2 <= " + upp + |
|
255 ")) AND (NOT (nbr3 >= " + low + " AND nbr3 <= " + upp + |
|
256 ")) AND (NOT (nbr4 >= " + low + " AND nbr4 <= " + upp + ")))"; |
|
257 } |
|
258 |
|
259 QString CntSqlSearch::CreateJoinTableSearch(QStringList numbers) |
|
260 { |
|
261 QString firstTable = SelectTableView(numbers.at(0)); |
|
262 QString secondTable = SelectTableView(numbers.at(1)); |
|
263 QString queryString; |
|
264 |
|
265 queryString = QString("SELECT " + firstTable + ".contact_id FROM " + firstTable + " JOIN " + secondTable + " ON " + firstTable +".contact_id = " + secondTable + ".contact_id WHERE"); |
|
266 |
|
267 |
|
268 if (numbers.at(0).length() > 1 && numbers.at(1).length() > 1 ) |
|
269 { |
|
270 queryString += "(" + CreateJoinTableLimit(lowerLimit(numbers.at(0)), upperLimit(numbers.at(0)), SelectTableView(numbers.at(0))) + |
|
271 ") AND (" + CreateJoinTableLimit(lowerLimit(numbers.at(1)), upperLimit(numbers.at(1)), SelectTableView(numbers.at(1))) + ")"; |
|
272 } |
|
273 else if (numbers.at(0).length() > 1 ) |
|
274 { |
|
275 queryString += CreateJoinTableLimit(lowerLimit(numbers.at(0)), upperLimit(numbers.at(0)), SelectTableView(numbers.at(0))); |
|
276 } |
|
277 else |
|
278 { |
|
279 queryString += CreateJoinTableLimit(lowerLimit(numbers.at(1)), upperLimit(numbers.at(1)), SelectTableView(numbers.at(1))); |
|
280 } |
|
281 |
|
282 return queryString; |
|
283 } |
|
284 |
|
285 QString CntSqlSearch::CreateJoinTableLimit(QString low, QString upp, QString table ) |
|
286 { |
|
287 table += "."; |
|
288 return QString("(" + table + "nbr>" + low + |
|
289 " AND " + table + "nbr<" + upp + |
|
290 ") OR (" + table + "nbr2>" + low + |
|
291 " AND " + table + "nbr2<" + upp + |
|
292 ") OR (" + table + "nbr3>" + low + |
|
293 " AND " + table + "nbr3<" + upp + |
|
294 ") OR (" + table + "nbr4>" + low + |
|
295 " AND " + table + "nbr4<" + upp + ")"); |
|
296 } |
|
297 |
|
298 QString CntSqlSearch::Order(QStringList numbers) |
|
299 { |
|
300 if (numbers.count() > 1 ) |
|
301 { |
|
302 if( numbers.at(0).length() > numbers.at(1).length() || numbers.at(0).length() == numbers.at(1).length() ) |
|
303 { |
|
304 return QString(" ORDER BY " + SelectTableView(numbers.at(0)) + ".first_name, " + SelectTableView(numbers.at(0)) + ".last_name ASC;"); |
|
305 } |
|
306 else |
|
307 { |
|
308 return QString(" ORDER BY " + SelectTableView(numbers.at(1)) + ".first_name, " + SelectTableView(numbers.at(1)) + ".last_name ASC;"); |
|
309 } |
|
310 } |
|
311 else |
|
312 { |
|
313 return QString(" ORDER BY first_name, last_name ASC;"); |
|
314 } |
|
315 } |
|
316 |
|
317 QString CntSqlSearch::pad( const QString &pattern, char padChar ) const |
|
318 { |
|
319 QString des; |
|
320 int padCount = LimitLength-pattern.length(); |
|
321 padCount = padCount < 0 ? 0 : padCount; |
|
322 |
|
323 QString result; //("0x"); |
|
324 |
|
325 if ( LimitLength-pattern.length() < 0 ) { |
|
326 result = result + pattern.left( LimitLength ); |
|
327 } else { |
|
328 result = result + pattern; |
|
329 for( int i=0;i<padCount;i++) { |
|
330 result.append( padChar ); |
|
331 } |
|
332 } |
|
333 bool ok; |
|
334 quint64 hex = result.toULongLong(&ok, 16); |
|
335 QString str = QString::number(hex, 10); |
|
336 return (str); |
|
337 } |
|
338 |
|
339 QString CntSqlSearch::upperLimit( const QString &pattern ) const |
|
340 { |
|
341 return pad( pattern, UpperLimitPadding ); |
|
342 } |
|
343 |
|
344 QString CntSqlSearch::lowerLimit( const QString &pattern ) const |
|
345 { |
|
346 return pad( pattern, LowerLimitPadding ); |
|
347 } |
|
348 |
|
349 |
|
350 |
|