|
1 /* |
|
2 ** 2002 February 23 |
|
3 ** |
|
4 ** The author disclaims copyright to this source code. In place of |
|
5 ** a legal notice, here is a blessing: |
|
6 ** |
|
7 ** May you do good and not evil. |
|
8 ** May you find forgiveness for yourself and forgive others. |
|
9 ** May you share freely, never taking more than you give. |
|
10 ** |
|
11 ************************************************************************* |
|
12 ** This file contains the C functions that implement various SQL |
|
13 ** functions of SQLite. |
|
14 ** |
|
15 ** There is only one exported symbol in this file - the function |
|
16 ** sqliteRegisterBuildinFunctions() found at the bottom of the file. |
|
17 ** All other code has file scope. |
|
18 ** |
|
19 ** $Id: func.c,v 1.196 2008/07/28 19:34:53 drh Exp $ |
|
20 */ |
|
21 #include "sqliteInt.h" |
|
22 #include <ctype.h> |
|
23 #include <stdlib.h> |
|
24 #include <assert.h> |
|
25 #include "vdbeInt.h" |
|
26 |
|
27 |
|
28 /* |
|
29 ** Return the collating function associated with a function. |
|
30 */ |
|
31 static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){ |
|
32 return context->pColl; |
|
33 } |
|
34 |
|
35 /* |
|
36 ** Implementation of the non-aggregate min() and max() functions |
|
37 */ |
|
38 static void minmaxFunc( |
|
39 sqlite3_context *context, |
|
40 int argc, |
|
41 sqlite3_value **argv |
|
42 ){ |
|
43 int i; |
|
44 int mask; /* 0 for min() or 0xffffffff for max() */ |
|
45 int iBest; |
|
46 CollSeq *pColl; |
|
47 |
|
48 if( argc==0 ) return; |
|
49 mask = sqlite3_user_data(context)==0 ? 0 : -1; |
|
50 pColl = sqlite3GetFuncCollSeq(context); |
|
51 assert( pColl ); |
|
52 assert( mask==-1 || mask==0 ); |
|
53 iBest = 0; |
|
54 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return; |
|
55 for(i=1; i<argc; i++){ |
|
56 if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return; |
|
57 if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){ |
|
58 iBest = i; |
|
59 } |
|
60 } |
|
61 sqlite3_result_value(context, argv[iBest]); |
|
62 } |
|
63 |
|
64 /* |
|
65 ** Return the type of the argument. |
|
66 */ |
|
67 static void typeofFunc( |
|
68 sqlite3_context *context, |
|
69 int argc, |
|
70 sqlite3_value **argv |
|
71 ){ |
|
72 const char *z = 0; |
|
73 switch( sqlite3_value_type(argv[0]) ){ |
|
74 case SQLITE_NULL: z = "null"; break; |
|
75 case SQLITE_INTEGER: z = "integer"; break; |
|
76 case SQLITE_TEXT: z = "text"; break; |
|
77 case SQLITE_FLOAT: z = "real"; break; |
|
78 case SQLITE_BLOB: z = "blob"; break; |
|
79 } |
|
80 sqlite3_result_text(context, z, -1, SQLITE_STATIC); |
|
81 } |
|
82 |
|
83 |
|
84 /* |
|
85 ** Implementation of the length() function |
|
86 */ |
|
87 static void lengthFunc( |
|
88 sqlite3_context *context, |
|
89 int argc, |
|
90 sqlite3_value **argv |
|
91 ){ |
|
92 int len; |
|
93 |
|
94 assert( argc==1 ); |
|
95 switch( sqlite3_value_type(argv[0]) ){ |
|
96 case SQLITE_BLOB: |
|
97 case SQLITE_INTEGER: |
|
98 case SQLITE_FLOAT: { |
|
99 sqlite3_result_int(context, sqlite3_value_bytes(argv[0])); |
|
100 break; |
|
101 } |
|
102 case SQLITE_TEXT: { |
|
103 const unsigned char *z = sqlite3_value_text(argv[0]); |
|
104 if( z==0 ) return; |
|
105 len = 0; |
|
106 while( *z ){ |
|
107 len++; |
|
108 SQLITE_SKIP_UTF8(z); |
|
109 } |
|
110 sqlite3_result_int(context, len); |
|
111 break; |
|
112 } |
|
113 default: { |
|
114 sqlite3_result_null(context); |
|
115 break; |
|
116 } |
|
117 } |
|
118 } |
|
119 |
|
120 /* |
|
121 ** Implementation of the abs() function |
|
122 */ |
|
123 static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
124 assert( argc==1 ); |
|
125 switch( sqlite3_value_type(argv[0]) ){ |
|
126 case SQLITE_INTEGER: { |
|
127 i64 iVal = sqlite3_value_int64(argv[0]); |
|
128 if( iVal<0 ){ |
|
129 if( (iVal<<1)==0 ){ |
|
130 sqlite3_result_error(context, "integer overflow", -1); |
|
131 return; |
|
132 } |
|
133 iVal = -iVal; |
|
134 } |
|
135 sqlite3_result_int64(context, iVal); |
|
136 break; |
|
137 } |
|
138 case SQLITE_NULL: { |
|
139 sqlite3_result_null(context); |
|
140 break; |
|
141 } |
|
142 default: { |
|
143 double rVal = sqlite3_value_double(argv[0]); |
|
144 if( rVal<0 ) rVal = -rVal; |
|
145 sqlite3_result_double(context, rVal); |
|
146 break; |
|
147 } |
|
148 } |
|
149 } |
|
150 |
|
151 /* |
|
152 ** Implementation of the substr() function. |
|
153 ** |
|
154 ** substr(x,p1,p2) returns p2 characters of x[] beginning with p1. |
|
155 ** p1 is 1-indexed. So substr(x,1,1) returns the first character |
|
156 ** of x. If x is text, then we actually count UTF-8 characters. |
|
157 ** If x is a blob, then we count bytes. |
|
158 ** |
|
159 ** If p1 is negative, then we begin abs(p1) from the end of x[]. |
|
160 */ |
|
161 static void substrFunc( |
|
162 sqlite3_context *context, |
|
163 int argc, |
|
164 sqlite3_value **argv |
|
165 ){ |
|
166 const unsigned char *z; |
|
167 const unsigned char *z2; |
|
168 int len; |
|
169 int p0type; |
|
170 i64 p1, p2; |
|
171 |
|
172 assert( argc==3 || argc==2 ); |
|
173 p0type = sqlite3_value_type(argv[0]); |
|
174 if( p0type==SQLITE_BLOB ){ |
|
175 len = sqlite3_value_bytes(argv[0]); |
|
176 z = sqlite3_value_blob(argv[0]); |
|
177 if( z==0 ) return; |
|
178 assert( len==sqlite3_value_bytes(argv[0]) ); |
|
179 }else{ |
|
180 z = sqlite3_value_text(argv[0]); |
|
181 if( z==0 ) return; |
|
182 len = 0; |
|
183 for(z2=z; *z2; len++){ |
|
184 SQLITE_SKIP_UTF8(z2); |
|
185 } |
|
186 } |
|
187 p1 = sqlite3_value_int(argv[1]); |
|
188 if( argc==3 ){ |
|
189 p2 = sqlite3_value_int(argv[2]); |
|
190 }else{ |
|
191 p2 = sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH]; |
|
192 } |
|
193 if( p1<0 ){ |
|
194 p1 += len; |
|
195 if( p1<0 ){ |
|
196 p2 += p1; |
|
197 p1 = 0; |
|
198 } |
|
199 }else if( p1>0 ){ |
|
200 p1--; |
|
201 } |
|
202 if( p1+p2>len ){ |
|
203 p2 = len-p1; |
|
204 } |
|
205 if( p0type!=SQLITE_BLOB ){ |
|
206 while( *z && p1 ){ |
|
207 SQLITE_SKIP_UTF8(z); |
|
208 p1--; |
|
209 } |
|
210 for(z2=z; *z2 && p2; p2--){ |
|
211 SQLITE_SKIP_UTF8(z2); |
|
212 } |
|
213 sqlite3_result_text(context, (char*)z, z2-z, SQLITE_TRANSIENT); |
|
214 }else{ |
|
215 if( p2<0 ) p2 = 0; |
|
216 sqlite3_result_blob(context, (char*)&z[p1], p2, SQLITE_TRANSIENT); |
|
217 } |
|
218 } |
|
219 |
|
220 /* |
|
221 ** Implementation of the round() function |
|
222 */ |
|
223 static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
224 int n = 0; |
|
225 double r; |
|
226 char zBuf[500]; /* larger than the %f representation of the largest double */ |
|
227 assert( argc==1 || argc==2 ); |
|
228 if( argc==2 ){ |
|
229 if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return; |
|
230 n = sqlite3_value_int(argv[1]); |
|
231 if( n>30 ) n = 30; |
|
232 if( n<0 ) n = 0; |
|
233 } |
|
234 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return; |
|
235 r = sqlite3_value_double(argv[0]); |
|
236 sqlite3_snprintf(sizeof(zBuf),zBuf,"%.*f",n,r); |
|
237 sqlite3AtoF(zBuf, &r); |
|
238 sqlite3_result_double(context, r); |
|
239 } |
|
240 |
|
241 /* |
|
242 ** Allocate nByte bytes of space using sqlite3_malloc(). If the |
|
243 ** allocation fails, call sqlite3_result_error_nomem() to notify |
|
244 ** the database handle that malloc() has failed. |
|
245 */ |
|
246 static void *contextMalloc(sqlite3_context *context, i64 nByte){ |
|
247 char *z; |
|
248 if( nByte>sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH] ){ |
|
249 sqlite3_result_error_toobig(context); |
|
250 z = 0; |
|
251 }else{ |
|
252 z = sqlite3Malloc(nByte); |
|
253 if( !z && nByte>0 ){ |
|
254 sqlite3_result_error_nomem(context); |
|
255 } |
|
256 } |
|
257 return z; |
|
258 } |
|
259 |
|
260 /* |
|
261 ** Implementation of the upper() and lower() SQL functions. |
|
262 */ |
|
263 static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
264 char *z1; |
|
265 const char *z2; |
|
266 int i, n; |
|
267 if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return; |
|
268 z2 = (char*)sqlite3_value_text(argv[0]); |
|
269 n = sqlite3_value_bytes(argv[0]); |
|
270 /* Verify that the call to _bytes() does not invalidate the _text() pointer */ |
|
271 assert( z2==(char*)sqlite3_value_text(argv[0]) ); |
|
272 if( z2 ){ |
|
273 z1 = contextMalloc(context, ((i64)n)+1); |
|
274 if( z1 ){ |
|
275 memcpy(z1, z2, n+1); |
|
276 for(i=0; z1[i]; i++){ |
|
277 z1[i] = toupper(z1[i]); |
|
278 } |
|
279 sqlite3_result_text(context, z1, -1, sqlite3_free); |
|
280 } |
|
281 } |
|
282 } |
|
283 static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
284 char *z1; |
|
285 const char *z2; |
|
286 int i, n; |
|
287 if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return; |
|
288 z2 = (char*)sqlite3_value_text(argv[0]); |
|
289 n = sqlite3_value_bytes(argv[0]); |
|
290 /* Verify that the call to _bytes() does not invalidate the _text() pointer */ |
|
291 assert( z2==(char*)sqlite3_value_text(argv[0]) ); |
|
292 if( z2 ){ |
|
293 z1 = contextMalloc(context, ((i64)n)+1); |
|
294 if( z1 ){ |
|
295 memcpy(z1, z2, n+1); |
|
296 for(i=0; z1[i]; i++){ |
|
297 z1[i] = tolower(z1[i]); |
|
298 } |
|
299 sqlite3_result_text(context, z1, -1, sqlite3_free); |
|
300 } |
|
301 } |
|
302 } |
|
303 |
|
304 /* |
|
305 ** Implementation of the IFNULL(), NVL(), and COALESCE() functions. |
|
306 ** All three do the same thing. They return the first non-NULL |
|
307 ** argument. |
|
308 */ |
|
309 static void ifnullFunc( |
|
310 sqlite3_context *context, |
|
311 int argc, |
|
312 sqlite3_value **argv |
|
313 ){ |
|
314 int i; |
|
315 for(i=0; i<argc; i++){ |
|
316 if( SQLITE_NULL!=sqlite3_value_type(argv[i]) ){ |
|
317 sqlite3_result_value(context, argv[i]); |
|
318 break; |
|
319 } |
|
320 } |
|
321 } |
|
322 |
|
323 /* |
|
324 ** Implementation of random(). Return a random integer. |
|
325 */ |
|
326 static void randomFunc( |
|
327 sqlite3_context *context, |
|
328 int argc, |
|
329 sqlite3_value **argv |
|
330 ){ |
|
331 sqlite_int64 r; |
|
332 sqlite3_randomness(sizeof(r), &r); |
|
333 if( (r<<1)==0 ) r = 0; /* Prevent 0x8000.... as the result so that we */ |
|
334 /* can always do abs() of the result */ |
|
335 sqlite3_result_int64(context, r); |
|
336 } |
|
337 |
|
338 /* |
|
339 ** Implementation of randomblob(N). Return a random blob |
|
340 ** that is N bytes long. |
|
341 */ |
|
342 static void randomBlob( |
|
343 sqlite3_context *context, |
|
344 int argc, |
|
345 sqlite3_value **argv |
|
346 ){ |
|
347 int n; |
|
348 unsigned char *p; |
|
349 assert( argc==1 ); |
|
350 n = sqlite3_value_int(argv[0]); |
|
351 if( n<1 ){ |
|
352 n = 1; |
|
353 } |
|
354 p = contextMalloc(context, n); |
|
355 if( p ){ |
|
356 sqlite3_randomness(n, p); |
|
357 sqlite3_result_blob(context, (char*)p, n, sqlite3_free); |
|
358 } |
|
359 } |
|
360 |
|
361 /* |
|
362 ** Implementation of the last_insert_rowid() SQL function. The return |
|
363 ** value is the same as the sqlite3_last_insert_rowid() API function. |
|
364 */ |
|
365 static void last_insert_rowid( |
|
366 sqlite3_context *context, |
|
367 int arg, |
|
368 sqlite3_value **argv |
|
369 ){ |
|
370 sqlite3 *db = sqlite3_context_db_handle(context); |
|
371 sqlite3_result_int64(context, sqlite3_last_insert_rowid(db)); |
|
372 } |
|
373 |
|
374 /* |
|
375 ** Implementation of the changes() SQL function. The return value is the |
|
376 ** same as the sqlite3_changes() API function. |
|
377 */ |
|
378 static void changes( |
|
379 sqlite3_context *context, |
|
380 int arg, |
|
381 sqlite3_value **argv |
|
382 ){ |
|
383 sqlite3 *db = sqlite3_context_db_handle(context); |
|
384 sqlite3_result_int(context, sqlite3_changes(db)); |
|
385 } |
|
386 |
|
387 /* |
|
388 ** Implementation of the total_changes() SQL function. The return value is |
|
389 ** the same as the sqlite3_total_changes() API function. |
|
390 */ |
|
391 static void total_changes( |
|
392 sqlite3_context *context, |
|
393 int arg, |
|
394 sqlite3_value **argv |
|
395 ){ |
|
396 sqlite3 *db = sqlite3_context_db_handle(context); |
|
397 sqlite3_result_int(context, sqlite3_total_changes(db)); |
|
398 } |
|
399 |
|
400 /* |
|
401 ** A structure defining how to do GLOB-style comparisons. |
|
402 */ |
|
403 struct compareInfo { |
|
404 u8 matchAll; |
|
405 u8 matchOne; |
|
406 u8 matchSet; |
|
407 u8 noCase; |
|
408 }; |
|
409 |
|
410 /* |
|
411 ** For LIKE and GLOB matching on EBCDIC machines, assume that every |
|
412 ** character is exactly one byte in size. Also, all characters are |
|
413 ** able to participate in upper-case-to-lower-case mappings in EBCDIC |
|
414 ** whereas only characters less than 0x80 do in ASCII. |
|
415 */ |
|
416 #if defined(SQLITE_EBCDIC) |
|
417 # define sqlite3Utf8Read(A,B,C) (*(A++)) |
|
418 # define GlogUpperToLower(A) A = sqlite3UpperToLower[A] |
|
419 #else |
|
420 # define GlogUpperToLower(A) if( A<0x80 ){ A = sqlite3UpperToLower[A]; } |
|
421 #endif |
|
422 |
|
423 static const struct compareInfo globInfo = { '*', '?', '[', 0 }; |
|
424 /* The correct SQL-92 behavior is for the LIKE operator to ignore |
|
425 ** case. Thus 'a' LIKE 'A' would be true. */ |
|
426 static const struct compareInfo likeInfoNorm = { '%', '_', 0, 1 }; |
|
427 /* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator |
|
428 ** is case sensitive causing 'a' LIKE 'A' to be false */ |
|
429 static const struct compareInfo likeInfoAlt = { '%', '_', 0, 0 }; |
|
430 |
|
431 /* |
|
432 ** Compare two UTF-8 strings for equality where the first string can |
|
433 ** potentially be a "glob" expression. Return true (1) if they |
|
434 ** are the same and false (0) if they are different. |
|
435 ** |
|
436 ** Globbing rules: |
|
437 ** |
|
438 ** '*' Matches any sequence of zero or more characters. |
|
439 ** |
|
440 ** '?' Matches exactly one character. |
|
441 ** |
|
442 ** [...] Matches one character from the enclosed list of |
|
443 ** characters. |
|
444 ** |
|
445 ** [^...] Matches one character not in the enclosed list. |
|
446 ** |
|
447 ** With the [...] and [^...] matching, a ']' character can be included |
|
448 ** in the list by making it the first character after '[' or '^'. A |
|
449 ** range of characters can be specified using '-'. Example: |
|
450 ** "[a-z]" matches any single lower-case letter. To match a '-', make |
|
451 ** it the last character in the list. |
|
452 ** |
|
453 ** This routine is usually quick, but can be N**2 in the worst case. |
|
454 ** |
|
455 ** Hints: to match '*' or '?', put them in "[]". Like this: |
|
456 ** |
|
457 ** abc[*]xyz Matches "abc*xyz" only |
|
458 */ |
|
459 static int patternCompare( |
|
460 const u8 *zPattern, /* The glob pattern */ |
|
461 const u8 *zString, /* The string to compare against the glob */ |
|
462 const struct compareInfo *pInfo, /* Information about how to do the compare */ |
|
463 const int esc /* The escape character */ |
|
464 ){ |
|
465 int c, c2; |
|
466 int invert; |
|
467 int seen; |
|
468 u8 matchOne = pInfo->matchOne; |
|
469 u8 matchAll = pInfo->matchAll; |
|
470 u8 matchSet = pInfo->matchSet; |
|
471 u8 noCase = pInfo->noCase; |
|
472 int prevEscape = 0; /* True if the previous character was 'escape' */ |
|
473 |
|
474 while( (c = sqlite3Utf8Read(zPattern,0,&zPattern))!=0 ){ |
|
475 if( !prevEscape && c==matchAll ){ |
|
476 while( (c=sqlite3Utf8Read(zPattern,0,&zPattern)) == matchAll |
|
477 || c == matchOne ){ |
|
478 if( c==matchOne && sqlite3Utf8Read(zString, 0, &zString)==0 ){ |
|
479 return 0; |
|
480 } |
|
481 } |
|
482 if( c==0 ){ |
|
483 return 1; |
|
484 }else if( c==esc ){ |
|
485 c = sqlite3Utf8Read(zPattern, 0, &zPattern); |
|
486 if( c==0 ){ |
|
487 return 0; |
|
488 } |
|
489 }else if( c==matchSet ){ |
|
490 assert( esc==0 ); /* This is GLOB, not LIKE */ |
|
491 assert( matchSet<0x80 ); /* '[' is a single-byte character */ |
|
492 while( *zString && patternCompare(&zPattern[-1],zString,pInfo,esc)==0 ){ |
|
493 SQLITE_SKIP_UTF8(zString); |
|
494 } |
|
495 return *zString!=0; |
|
496 } |
|
497 while( (c2 = sqlite3Utf8Read(zString,0,&zString))!=0 ){ |
|
498 if( noCase ){ |
|
499 GlogUpperToLower(c2); |
|
500 GlogUpperToLower(c); |
|
501 while( c2 != 0 && c2 != c ){ |
|
502 c2 = sqlite3Utf8Read(zString, 0, &zString); |
|
503 GlogUpperToLower(c2); |
|
504 } |
|
505 }else{ |
|
506 while( c2 != 0 && c2 != c ){ |
|
507 c2 = sqlite3Utf8Read(zString, 0, &zString); |
|
508 } |
|
509 } |
|
510 if( c2==0 ) return 0; |
|
511 if( patternCompare(zPattern,zString,pInfo,esc) ) return 1; |
|
512 } |
|
513 return 0; |
|
514 }else if( !prevEscape && c==matchOne ){ |
|
515 if( sqlite3Utf8Read(zString, 0, &zString)==0 ){ |
|
516 return 0; |
|
517 } |
|
518 }else if( c==matchSet ){ |
|
519 int prior_c = 0; |
|
520 assert( esc==0 ); /* This only occurs for GLOB, not LIKE */ |
|
521 seen = 0; |
|
522 invert = 0; |
|
523 c = sqlite3Utf8Read(zString, 0, &zString); |
|
524 if( c==0 ) return 0; |
|
525 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern); |
|
526 if( c2=='^' ){ |
|
527 invert = 1; |
|
528 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern); |
|
529 } |
|
530 if( c2==']' ){ |
|
531 if( c==']' ) seen = 1; |
|
532 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern); |
|
533 } |
|
534 while( c2 && c2!=']' ){ |
|
535 if( c2=='-' && zPattern[0]!=']' && zPattern[0]!=0 && prior_c>0 ){ |
|
536 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern); |
|
537 if( c>=prior_c && c<=c2 ) seen = 1; |
|
538 prior_c = 0; |
|
539 }else{ |
|
540 if( c==c2 ){ |
|
541 seen = 1; |
|
542 } |
|
543 prior_c = c2; |
|
544 } |
|
545 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern); |
|
546 } |
|
547 if( c2==0 || (seen ^ invert)==0 ){ |
|
548 return 0; |
|
549 } |
|
550 }else if( esc==c && !prevEscape ){ |
|
551 prevEscape = 1; |
|
552 }else{ |
|
553 c2 = sqlite3Utf8Read(zString, 0, &zString); |
|
554 if( noCase ){ |
|
555 GlogUpperToLower(c); |
|
556 GlogUpperToLower(c2); |
|
557 } |
|
558 if( c!=c2 ){ |
|
559 return 0; |
|
560 } |
|
561 prevEscape = 0; |
|
562 } |
|
563 } |
|
564 return *zString==0; |
|
565 } |
|
566 |
|
567 /* |
|
568 ** Count the number of times that the LIKE operator (or GLOB which is |
|
569 ** just a variation of LIKE) gets called. This is used for testing |
|
570 ** only. |
|
571 */ |
|
572 #ifdef SQLITE_TEST |
|
573 int sqlite3_like_count = 0; |
|
574 #endif |
|
575 |
|
576 |
|
577 /* |
|
578 ** Implementation of the like() SQL function. This function implements |
|
579 ** the build-in LIKE operator. The first argument to the function is the |
|
580 ** pattern and the second argument is the string. So, the SQL statements: |
|
581 ** |
|
582 ** A LIKE B |
|
583 ** |
|
584 ** is implemented as like(B,A). |
|
585 ** |
|
586 ** This same function (with a different compareInfo structure) computes |
|
587 ** the GLOB operator. |
|
588 */ |
|
589 static void likeFunc( |
|
590 sqlite3_context *context, |
|
591 int argc, |
|
592 sqlite3_value **argv |
|
593 ){ |
|
594 const unsigned char *zA, *zB; |
|
595 int escape = 0; |
|
596 sqlite3 *db = sqlite3_context_db_handle(context); |
|
597 |
|
598 zB = sqlite3_value_text(argv[0]); |
|
599 zA = sqlite3_value_text(argv[1]); |
|
600 |
|
601 /* Limit the length of the LIKE or GLOB pattern to avoid problems |
|
602 ** of deep recursion and N*N behavior in patternCompare(). |
|
603 */ |
|
604 if( sqlite3_value_bytes(argv[0]) > |
|
605 db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] ){ |
|
606 sqlite3_result_error(context, "LIKE or GLOB pattern too complex", -1); |
|
607 return; |
|
608 } |
|
609 assert( zB==sqlite3_value_text(argv[0]) ); /* Encoding did not change */ |
|
610 |
|
611 if( argc==3 ){ |
|
612 /* The escape character string must consist of a single UTF-8 character. |
|
613 ** Otherwise, return an error. |
|
614 */ |
|
615 const unsigned char *zEsc = sqlite3_value_text(argv[2]); |
|
616 if( zEsc==0 ) return; |
|
617 if( sqlite3Utf8CharLen((char*)zEsc, -1)!=1 ){ |
|
618 sqlite3_result_error(context, |
|
619 "ESCAPE expression must be a single character", -1); |
|
620 return; |
|
621 } |
|
622 escape = sqlite3Utf8Read(zEsc, 0, &zEsc); |
|
623 } |
|
624 if( zA && zB ){ |
|
625 struct compareInfo *pInfo = sqlite3_user_data(context); |
|
626 #ifdef SQLITE_TEST |
|
627 sqlite3_like_count++; |
|
628 #endif |
|
629 |
|
630 sqlite3_result_int(context, patternCompare(zB, zA, pInfo, escape)); |
|
631 } |
|
632 } |
|
633 |
|
634 /* |
|
635 ** Implementation of the NULLIF(x,y) function. The result is the first |
|
636 ** argument if the arguments are different. The result is NULL if the |
|
637 ** arguments are equal to each other. |
|
638 */ |
|
639 static void nullifFunc( |
|
640 sqlite3_context *context, |
|
641 int argc, |
|
642 sqlite3_value **argv |
|
643 ){ |
|
644 CollSeq *pColl = sqlite3GetFuncCollSeq(context); |
|
645 if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){ |
|
646 sqlite3_result_value(context, argv[0]); |
|
647 } |
|
648 } |
|
649 |
|
650 /* |
|
651 ** Implementation of the VERSION(*) function. The result is the version |
|
652 ** of the SQLite library that is running. |
|
653 */ |
|
654 static void versionFunc( |
|
655 sqlite3_context *context, |
|
656 int argc, |
|
657 sqlite3_value **argv |
|
658 ){ |
|
659 sqlite3_result_text(context, sqlite3_version, -1, SQLITE_STATIC); |
|
660 } |
|
661 |
|
662 /* Array for converting from half-bytes (nybbles) into ASCII hex |
|
663 ** digits. */ |
|
664 static const char hexdigits[] = { |
|
665 '0', '1', '2', '3', '4', '5', '6', '7', |
|
666 '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' |
|
667 }; |
|
668 |
|
669 /* |
|
670 ** EXPERIMENTAL - This is not an official function. The interface may |
|
671 ** change. This function may disappear. Do not write code that depends |
|
672 ** on this function. |
|
673 ** |
|
674 ** Implementation of the QUOTE() function. This function takes a single |
|
675 ** argument. If the argument is numeric, the return value is the same as |
|
676 ** the argument. If the argument is NULL, the return value is the string |
|
677 ** "NULL". Otherwise, the argument is enclosed in single quotes with |
|
678 ** single-quote escapes. |
|
679 */ |
|
680 static void quoteFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
681 if( argc<1 ) return; |
|
682 switch( sqlite3_value_type(argv[0]) ){ |
|
683 case SQLITE_NULL: { |
|
684 sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC); |
|
685 break; |
|
686 } |
|
687 case SQLITE_INTEGER: |
|
688 case SQLITE_FLOAT: { |
|
689 sqlite3_result_value(context, argv[0]); |
|
690 break; |
|
691 } |
|
692 case SQLITE_BLOB: { |
|
693 char *zText = 0; |
|
694 char const *zBlob = sqlite3_value_blob(argv[0]); |
|
695 int nBlob = sqlite3_value_bytes(argv[0]); |
|
696 assert( zBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */ |
|
697 zText = (char *)contextMalloc(context, (2*(i64)nBlob)+4); |
|
698 if( zText ){ |
|
699 int i; |
|
700 for(i=0; i<nBlob; i++){ |
|
701 zText[(i*2)+2] = hexdigits[(zBlob[i]>>4)&0x0F]; |
|
702 zText[(i*2)+3] = hexdigits[(zBlob[i])&0x0F]; |
|
703 } |
|
704 zText[(nBlob*2)+2] = '\''; |
|
705 zText[(nBlob*2)+3] = '\0'; |
|
706 zText[0] = 'X'; |
|
707 zText[1] = '\''; |
|
708 sqlite3_result_text(context, zText, -1, SQLITE_TRANSIENT); |
|
709 sqlite3_free(zText); |
|
710 } |
|
711 break; |
|
712 } |
|
713 case SQLITE_TEXT: { |
|
714 int i,j; |
|
715 u64 n; |
|
716 const unsigned char *zArg = sqlite3_value_text(argv[0]); |
|
717 char *z; |
|
718 |
|
719 if( zArg==0 ) return; |
|
720 for(i=0, n=0; zArg[i]; i++){ if( zArg[i]=='\'' ) n++; } |
|
721 z = contextMalloc(context, ((i64)i)+((i64)n)+3); |
|
722 if( z ){ |
|
723 z[0] = '\''; |
|
724 for(i=0, j=1; zArg[i]; i++){ |
|
725 z[j++] = zArg[i]; |
|
726 if( zArg[i]=='\'' ){ |
|
727 z[j++] = '\''; |
|
728 } |
|
729 } |
|
730 z[j++] = '\''; |
|
731 z[j] = 0; |
|
732 sqlite3_result_text(context, z, j, sqlite3_free); |
|
733 } |
|
734 } |
|
735 } |
|
736 } |
|
737 |
|
738 /* |
|
739 ** The hex() function. Interpret the argument as a blob. Return |
|
740 ** a hexadecimal rendering as text. |
|
741 */ |
|
742 static void hexFunc( |
|
743 sqlite3_context *context, |
|
744 int argc, |
|
745 sqlite3_value **argv |
|
746 ){ |
|
747 int i, n; |
|
748 const unsigned char *pBlob; |
|
749 char *zHex, *z; |
|
750 assert( argc==1 ); |
|
751 pBlob = sqlite3_value_blob(argv[0]); |
|
752 n = sqlite3_value_bytes(argv[0]); |
|
753 assert( pBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */ |
|
754 z = zHex = contextMalloc(context, ((i64)n)*2 + 1); |
|
755 if( zHex ){ |
|
756 for(i=0; i<n; i++, pBlob++){ |
|
757 unsigned char c = *pBlob; |
|
758 *(z++) = hexdigits[(c>>4)&0xf]; |
|
759 *(z++) = hexdigits[c&0xf]; |
|
760 } |
|
761 *z = 0; |
|
762 sqlite3_result_text(context, zHex, n*2, sqlite3_free); |
|
763 } |
|
764 } |
|
765 |
|
766 /* |
|
767 ** The zeroblob(N) function returns a zero-filled blob of size N bytes. |
|
768 */ |
|
769 static void zeroblobFunc( |
|
770 sqlite3_context *context, |
|
771 int argc, |
|
772 sqlite3_value **argv |
|
773 ){ |
|
774 i64 n; |
|
775 assert( argc==1 ); |
|
776 n = sqlite3_value_int64(argv[0]); |
|
777 if( n>SQLITE_MAX_LENGTH ){ |
|
778 sqlite3_result_error_toobig(context); |
|
779 }else{ |
|
780 sqlite3_result_zeroblob(context, n); |
|
781 } |
|
782 } |
|
783 |
|
784 /* |
|
785 ** The replace() function. Three arguments are all strings: call |
|
786 ** them A, B, and C. The result is also a string which is derived |
|
787 ** from A by replacing every occurance of B with C. The match |
|
788 ** must be exact. Collating sequences are not used. |
|
789 */ |
|
790 static void replaceFunc( |
|
791 sqlite3_context *context, |
|
792 int argc, |
|
793 sqlite3_value **argv |
|
794 ){ |
|
795 const unsigned char *zStr; /* The input string A */ |
|
796 const unsigned char *zPattern; /* The pattern string B */ |
|
797 const unsigned char *zRep; /* The replacement string C */ |
|
798 unsigned char *zOut; /* The output */ |
|
799 int nStr; /* Size of zStr */ |
|
800 int nPattern; /* Size of zPattern */ |
|
801 int nRep; /* Size of zRep */ |
|
802 i64 nOut; /* Maximum size of zOut */ |
|
803 int loopLimit; /* Last zStr[] that might match zPattern[] */ |
|
804 int i, j; /* Loop counters */ |
|
805 |
|
806 assert( argc==3 ); |
|
807 zStr = sqlite3_value_text(argv[0]); |
|
808 if( zStr==0 ) return; |
|
809 nStr = sqlite3_value_bytes(argv[0]); |
|
810 assert( zStr==sqlite3_value_text(argv[0]) ); /* No encoding change */ |
|
811 zPattern = sqlite3_value_text(argv[1]); |
|
812 if( zPattern==0 || zPattern[0]==0 ) return; |
|
813 nPattern = sqlite3_value_bytes(argv[1]); |
|
814 assert( zPattern==sqlite3_value_text(argv[1]) ); /* No encoding change */ |
|
815 zRep = sqlite3_value_text(argv[2]); |
|
816 if( zRep==0 ) return; |
|
817 nRep = sqlite3_value_bytes(argv[2]); |
|
818 assert( zRep==sqlite3_value_text(argv[2]) ); |
|
819 nOut = nStr + 1; |
|
820 assert( nOut<SQLITE_MAX_LENGTH ); |
|
821 zOut = contextMalloc(context, (i64)nOut); |
|
822 if( zOut==0 ){ |
|
823 return; |
|
824 } |
|
825 loopLimit = nStr - nPattern; |
|
826 for(i=j=0; i<=loopLimit; i++){ |
|
827 if( zStr[i]!=zPattern[0] || memcmp(&zStr[i], zPattern, nPattern) ){ |
|
828 zOut[j++] = zStr[i]; |
|
829 }else{ |
|
830 u8 *zOld; |
|
831 sqlite3 *db = sqlite3_context_db_handle(context); |
|
832 nOut += nRep - nPattern; |
|
833 if( nOut>=db->aLimit[SQLITE_LIMIT_LENGTH] ){ |
|
834 sqlite3_result_error_toobig(context); |
|
835 sqlite3DbFree(db, zOut); |
|
836 return; |
|
837 } |
|
838 zOld = zOut; |
|
839 zOut = sqlite3_realloc(zOut, (int)nOut); |
|
840 if( zOut==0 ){ |
|
841 sqlite3_result_error_nomem(context); |
|
842 sqlite3DbFree(db, zOld); |
|
843 return; |
|
844 } |
|
845 memcpy(&zOut[j], zRep, nRep); |
|
846 j += nRep; |
|
847 i += nPattern-1; |
|
848 } |
|
849 } |
|
850 assert( j+nStr-i+1==nOut ); |
|
851 memcpy(&zOut[j], &zStr[i], nStr-i); |
|
852 j += nStr - i; |
|
853 assert( j<=nOut ); |
|
854 zOut[j] = 0; |
|
855 sqlite3_result_text(context, (char*)zOut, j, sqlite3_free); |
|
856 } |
|
857 |
|
858 /* |
|
859 ** Implementation of the TRIM(), LTRIM(), and RTRIM() functions. |
|
860 ** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both. |
|
861 */ |
|
862 static void trimFunc( |
|
863 sqlite3_context *context, |
|
864 int argc, |
|
865 sqlite3_value **argv |
|
866 ){ |
|
867 const unsigned char *zIn; /* Input string */ |
|
868 const unsigned char *zCharSet; /* Set of characters to trim */ |
|
869 int nIn; /* Number of bytes in input */ |
|
870 int flags; /* 1: trimleft 2: trimright 3: trim */ |
|
871 int i; /* Loop counter */ |
|
872 unsigned char *aLen; /* Length of each character in zCharSet */ |
|
873 unsigned char **azChar; /* Individual characters in zCharSet */ |
|
874 int nChar; /* Number of characters in zCharSet */ |
|
875 |
|
876 if( sqlite3_value_type(argv[0])==SQLITE_NULL ){ |
|
877 return; |
|
878 } |
|
879 zIn = sqlite3_value_text(argv[0]); |
|
880 if( zIn==0 ) return; |
|
881 nIn = sqlite3_value_bytes(argv[0]); |
|
882 assert( zIn==sqlite3_value_text(argv[0]) ); |
|
883 if( argc==1 ){ |
|
884 static const unsigned char lenOne[] = { 1 }; |
|
885 static const unsigned char *azOne[] = { (u8*)" " }; |
|
886 nChar = 1; |
|
887 aLen = (u8*)lenOne; |
|
888 azChar = (unsigned char **)azOne; |
|
889 zCharSet = 0; |
|
890 }else if( (zCharSet = sqlite3_value_text(argv[1]))==0 ){ |
|
891 return; |
|
892 }else{ |
|
893 const unsigned char *z; |
|
894 for(z=zCharSet, nChar=0; *z; nChar++){ |
|
895 SQLITE_SKIP_UTF8(z); |
|
896 } |
|
897 if( nChar>0 ){ |
|
898 azChar = contextMalloc(context, ((i64)nChar)*(sizeof(char*)+1)); |
|
899 if( azChar==0 ){ |
|
900 return; |
|
901 } |
|
902 aLen = (unsigned char*)&azChar[nChar]; |
|
903 for(z=zCharSet, nChar=0; *z; nChar++){ |
|
904 azChar[nChar] = (unsigned char *)z; |
|
905 SQLITE_SKIP_UTF8(z); |
|
906 aLen[nChar] = z - azChar[nChar]; |
|
907 } |
|
908 } |
|
909 } |
|
910 if( nChar>0 ){ |
|
911 flags = SQLITE_PTR_TO_INT(sqlite3_user_data(context)); |
|
912 if( flags & 1 ){ |
|
913 while( nIn>0 ){ |
|
914 int len; |
|
915 for(i=0; i<nChar; i++){ |
|
916 len = aLen[i]; |
|
917 if( memcmp(zIn, azChar[i], len)==0 ) break; |
|
918 } |
|
919 if( i>=nChar ) break; |
|
920 zIn += len; |
|
921 nIn -= len; |
|
922 } |
|
923 } |
|
924 if( flags & 2 ){ |
|
925 while( nIn>0 ){ |
|
926 int len; |
|
927 for(i=0; i<nChar; i++){ |
|
928 len = aLen[i]; |
|
929 if( len<=nIn && memcmp(&zIn[nIn-len],azChar[i],len)==0 ) break; |
|
930 } |
|
931 if( i>=nChar ) break; |
|
932 nIn -= len; |
|
933 } |
|
934 } |
|
935 if( zCharSet ){ |
|
936 sqlite3_free(azChar); |
|
937 } |
|
938 } |
|
939 sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT); |
|
940 } |
|
941 |
|
942 #ifdef SQLITE_SOUNDEX |
|
943 /* |
|
944 ** Compute the soundex encoding of a word. |
|
945 */ |
|
946 static void soundexFunc( |
|
947 sqlite3_context *context, |
|
948 int argc, |
|
949 sqlite3_value **argv |
|
950 ){ |
|
951 char zResult[8]; |
|
952 const u8 *zIn; |
|
953 int i, j; |
|
954 static const unsigned char iCode[] = { |
|
955 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, |
|
956 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, |
|
957 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, |
|
958 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, |
|
959 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0, |
|
960 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, |
|
961 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0, |
|
962 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, |
|
963 }; |
|
964 assert( argc==1 ); |
|
965 zIn = (u8*)sqlite3_value_text(argv[0]); |
|
966 if( zIn==0 ) zIn = (u8*)""; |
|
967 for(i=0; zIn[i] && !isalpha(zIn[i]); i++){} |
|
968 if( zIn[i] ){ |
|
969 u8 prevcode = iCode[zIn[i]&0x7f]; |
|
970 zResult[0] = toupper(zIn[i]); |
|
971 for(j=1; j<4 && zIn[i]; i++){ |
|
972 int code = iCode[zIn[i]&0x7f]; |
|
973 if( code>0 ){ |
|
974 if( code!=prevcode ){ |
|
975 prevcode = code; |
|
976 zResult[j++] = code + '0'; |
|
977 } |
|
978 }else{ |
|
979 prevcode = 0; |
|
980 } |
|
981 } |
|
982 while( j<4 ){ |
|
983 zResult[j++] = '0'; |
|
984 } |
|
985 zResult[j] = 0; |
|
986 sqlite3_result_text(context, zResult, 4, SQLITE_TRANSIENT); |
|
987 }else{ |
|
988 sqlite3_result_text(context, "?000", 4, SQLITE_STATIC); |
|
989 } |
|
990 } |
|
991 #endif |
|
992 |
|
993 #ifndef SQLITE_OMIT_LOAD_EXTENSION |
|
994 /* |
|
995 ** A function that loads a shared-library extension then returns NULL. |
|
996 */ |
|
997 static void loadExt(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
998 const char *zFile = (const char *)sqlite3_value_text(argv[0]); |
|
999 const char *zProc; |
|
1000 sqlite3 *db = sqlite3_context_db_handle(context); |
|
1001 char *zErrMsg = 0; |
|
1002 |
|
1003 if( argc==2 ){ |
|
1004 zProc = (const char *)sqlite3_value_text(argv[1]); |
|
1005 }else{ |
|
1006 zProc = 0; |
|
1007 } |
|
1008 if( zFile && sqlite3_load_extension(db, zFile, zProc, &zErrMsg) ){ |
|
1009 sqlite3_result_error(context, zErrMsg, -1); |
|
1010 sqlite3_free(zErrMsg); |
|
1011 } |
|
1012 } |
|
1013 #endif |
|
1014 |
|
1015 |
|
1016 /* |
|
1017 ** An instance of the following structure holds the context of a |
|
1018 ** sum() or avg() aggregate computation. |
|
1019 */ |
|
1020 typedef struct SumCtx SumCtx; |
|
1021 struct SumCtx { |
|
1022 double rSum; /* Floating point sum */ |
|
1023 i64 iSum; /* Integer sum */ |
|
1024 i64 cnt; /* Number of elements summed */ |
|
1025 u8 overflow; /* True if integer overflow seen */ |
|
1026 u8 approx; /* True if non-integer value was input to the sum */ |
|
1027 }; |
|
1028 |
|
1029 /* |
|
1030 ** Routines used to compute the sum, average, and total. |
|
1031 ** |
|
1032 ** The SUM() function follows the (broken) SQL standard which means |
|
1033 ** that it returns NULL if it sums over no inputs. TOTAL returns |
|
1034 ** 0.0 in that case. In addition, TOTAL always returns a float where |
|
1035 ** SUM might return an integer if it never encounters a floating point |
|
1036 ** value. TOTAL never fails, but SUM might through an exception if |
|
1037 ** it overflows an integer. |
|
1038 */ |
|
1039 static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
1040 SumCtx *p; |
|
1041 int type; |
|
1042 assert( argc==1 ); |
|
1043 p = sqlite3_aggregate_context(context, sizeof(*p)); |
|
1044 type = sqlite3_value_numeric_type(argv[0]); |
|
1045 if( p && type!=SQLITE_NULL ){ |
|
1046 p->cnt++; |
|
1047 if( type==SQLITE_INTEGER ){ |
|
1048 i64 v = sqlite3_value_int64(argv[0]); |
|
1049 p->rSum += v; |
|
1050 if( (p->approx|p->overflow)==0 ){ |
|
1051 i64 iNewSum = p->iSum + v; |
|
1052 int s1 = p->iSum >> (sizeof(i64)*8-1); |
|
1053 int s2 = v >> (sizeof(i64)*8-1); |
|
1054 int s3 = iNewSum >> (sizeof(i64)*8-1); |
|
1055 p->overflow = (s1&s2&~s3) | (~s1&~s2&s3); |
|
1056 p->iSum = iNewSum; |
|
1057 } |
|
1058 }else{ |
|
1059 p->rSum += sqlite3_value_double(argv[0]); |
|
1060 p->approx = 1; |
|
1061 } |
|
1062 } |
|
1063 } |
|
1064 static void sumFinalize(sqlite3_context *context){ |
|
1065 SumCtx *p; |
|
1066 p = sqlite3_aggregate_context(context, 0); |
|
1067 if( p && p->cnt>0 ){ |
|
1068 if( p->overflow ){ |
|
1069 sqlite3_result_error(context,"integer overflow",-1); |
|
1070 }else if( p->approx ){ |
|
1071 sqlite3_result_double(context, p->rSum); |
|
1072 }else{ |
|
1073 sqlite3_result_int64(context, p->iSum); |
|
1074 } |
|
1075 } |
|
1076 } |
|
1077 static void avgFinalize(sqlite3_context *context){ |
|
1078 SumCtx *p; |
|
1079 p = sqlite3_aggregate_context(context, 0); |
|
1080 if( p && p->cnt>0 ){ |
|
1081 sqlite3_result_double(context, p->rSum/(double)p->cnt); |
|
1082 } |
|
1083 } |
|
1084 static void totalFinalize(sqlite3_context *context){ |
|
1085 SumCtx *p; |
|
1086 p = sqlite3_aggregate_context(context, 0); |
|
1087 sqlite3_result_double(context, p ? p->rSum : 0.0); |
|
1088 } |
|
1089 |
|
1090 /* |
|
1091 ** The following structure keeps track of state information for the |
|
1092 ** count() aggregate function. |
|
1093 */ |
|
1094 typedef struct CountCtx CountCtx; |
|
1095 struct CountCtx { |
|
1096 i64 n; |
|
1097 }; |
|
1098 |
|
1099 /* |
|
1100 ** Routines to implement the count() aggregate function. |
|
1101 */ |
|
1102 static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
1103 CountCtx *p; |
|
1104 p = sqlite3_aggregate_context(context, sizeof(*p)); |
|
1105 if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){ |
|
1106 p->n++; |
|
1107 } |
|
1108 } |
|
1109 static void countFinalize(sqlite3_context *context){ |
|
1110 CountCtx *p; |
|
1111 p = sqlite3_aggregate_context(context, 0); |
|
1112 sqlite3_result_int64(context, p ? p->n : 0); |
|
1113 } |
|
1114 |
|
1115 /* |
|
1116 ** Routines to implement min() and max() aggregate functions. |
|
1117 */ |
|
1118 static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv){ |
|
1119 Mem *pArg = (Mem *)argv[0]; |
|
1120 Mem *pBest; |
|
1121 |
|
1122 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return; |
|
1123 pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest)); |
|
1124 if( !pBest ) return; |
|
1125 |
|
1126 if( pBest->flags ){ |
|
1127 int max; |
|
1128 int cmp; |
|
1129 CollSeq *pColl = sqlite3GetFuncCollSeq(context); |
|
1130 /* This step function is used for both the min() and max() aggregates, |
|
1131 ** the only difference between the two being that the sense of the |
|
1132 ** comparison is inverted. For the max() aggregate, the |
|
1133 ** sqlite3_user_data() function returns (void *)-1. For min() it |
|
1134 ** returns (void *)db, where db is the sqlite3* database pointer. |
|
1135 ** Therefore the next statement sets variable 'max' to 1 for the max() |
|
1136 ** aggregate, or 0 for min(). |
|
1137 */ |
|
1138 max = sqlite3_user_data(context)!=0; |
|
1139 cmp = sqlite3MemCompare(pBest, pArg, pColl); |
|
1140 if( (max && cmp<0) || (!max && cmp>0) ){ |
|
1141 sqlite3VdbeMemCopy(pBest, pArg); |
|
1142 } |
|
1143 }else{ |
|
1144 sqlite3VdbeMemCopy(pBest, pArg); |
|
1145 } |
|
1146 } |
|
1147 static void minMaxFinalize(sqlite3_context *context){ |
|
1148 sqlite3_value *pRes; |
|
1149 pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0); |
|
1150 if( pRes ){ |
|
1151 if( pRes->flags ){ |
|
1152 sqlite3_result_value(context, pRes); |
|
1153 } |
|
1154 sqlite3VdbeMemRelease(pRes); |
|
1155 } |
|
1156 } |
|
1157 |
|
1158 /* |
|
1159 ** group_concat(EXPR, ?SEPARATOR?) |
|
1160 */ |
|
1161 static void groupConcatStep( |
|
1162 sqlite3_context *context, |
|
1163 int argc, |
|
1164 sqlite3_value **argv |
|
1165 ){ |
|
1166 const char *zVal; |
|
1167 StrAccum *pAccum; |
|
1168 const char *zSep; |
|
1169 int nVal, nSep, i; |
|
1170 if( argc==0 || sqlite3_value_type(argv[0])==SQLITE_NULL ) return; |
|
1171 pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum)); |
|
1172 |
|
1173 if( pAccum ){ |
|
1174 sqlite3 *db = sqlite3_context_db_handle(context); |
|
1175 pAccum->useMalloc = 1; |
|
1176 pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH]; |
|
1177 if( pAccum->nChar ){ |
|
1178 if( argc>1 ){ |
|
1179 zSep = (char*)sqlite3_value_text(argv[argc-1]); |
|
1180 nSep = sqlite3_value_bytes(argv[argc-1]); |
|
1181 }else{ |
|
1182 zSep = ","; |
|
1183 nSep = 1; |
|
1184 } |
|
1185 sqlite3StrAccumAppend(pAccum, zSep, nSep); |
|
1186 } |
|
1187 i = 0; |
|
1188 do{ |
|
1189 zVal = (char*)sqlite3_value_text(argv[i]); |
|
1190 nVal = sqlite3_value_bytes(argv[i]); |
|
1191 sqlite3StrAccumAppend(pAccum, zVal, nVal); |
|
1192 i++; |
|
1193 }while( i<argc-1 ); |
|
1194 } |
|
1195 } |
|
1196 static void groupConcatFinalize(sqlite3_context *context){ |
|
1197 StrAccum *pAccum; |
|
1198 pAccum = sqlite3_aggregate_context(context, 0); |
|
1199 if( pAccum ){ |
|
1200 if( pAccum->tooBig ){ |
|
1201 sqlite3_result_error_toobig(context); |
|
1202 }else if( pAccum->mallocFailed ){ |
|
1203 sqlite3_result_error_nomem(context); |
|
1204 }else{ |
|
1205 sqlite3_result_text(context, sqlite3StrAccumFinish(pAccum), -1, |
|
1206 sqlite3_free); |
|
1207 } |
|
1208 } |
|
1209 } |
|
1210 |
|
1211 /* |
|
1212 ** This function registered all of the above C functions as SQL |
|
1213 ** functions. This should be the only routine in this file with |
|
1214 ** external linkage. |
|
1215 */ |
|
1216 void sqlite3RegisterBuiltinFunctions(sqlite3 *db){ |
|
1217 static const struct { |
|
1218 char *zName; |
|
1219 signed char nArg; |
|
1220 u8 argType; /* 1: 0, 2: 1, 3: 2,... N: N-1. */ |
|
1221 u8 eTextRep; /* 1: UTF-16. 0: UTF-8 */ |
|
1222 u8 needCollSeq; |
|
1223 void (*xFunc)(sqlite3_context*,int,sqlite3_value **); |
|
1224 } aFuncs[] = { |
|
1225 { "min", -1, 0, SQLITE_UTF8, 1, minmaxFunc }, |
|
1226 { "min", 0, 0, SQLITE_UTF8, 1, 0 }, |
|
1227 { "max", -1, 1, SQLITE_UTF8, 1, minmaxFunc }, |
|
1228 { "max", 0, 1, SQLITE_UTF8, 1, 0 }, |
|
1229 { "typeof", 1, 0, SQLITE_UTF8, 0, typeofFunc }, |
|
1230 { "length", 1, 0, SQLITE_UTF8, 0, lengthFunc }, |
|
1231 { "substr", 2, 0, SQLITE_UTF8, 0, substrFunc }, |
|
1232 { "substr", 3, 0, SQLITE_UTF8, 0, substrFunc }, |
|
1233 { "abs", 1, 0, SQLITE_UTF8, 0, absFunc }, |
|
1234 { "round", 1, 0, SQLITE_UTF8, 0, roundFunc }, |
|
1235 { "round", 2, 0, SQLITE_UTF8, 0, roundFunc }, |
|
1236 { "upper", 1, 0, SQLITE_UTF8, 0, upperFunc }, |
|
1237 { "lower", 1, 0, SQLITE_UTF8, 0, lowerFunc }, |
|
1238 { "coalesce", -1, 0, SQLITE_UTF8, 0, ifnullFunc }, |
|
1239 { "coalesce", 0, 0, SQLITE_UTF8, 0, 0 }, |
|
1240 { "coalesce", 1, 0, SQLITE_UTF8, 0, 0 }, |
|
1241 { "hex", 1, 0, SQLITE_UTF8, 0, hexFunc }, |
|
1242 { "ifnull", 2, 0, SQLITE_UTF8, 1, ifnullFunc }, |
|
1243 { "random", -1, 0, SQLITE_UTF8, 0, randomFunc }, |
|
1244 { "randomblob", 1, 0, SQLITE_UTF8, 0, randomBlob }, |
|
1245 { "nullif", 2, 0, SQLITE_UTF8, 1, nullifFunc }, |
|
1246 { "sqlite_version", 0, 0, SQLITE_UTF8, 0, versionFunc}, |
|
1247 { "quote", 1, 0, SQLITE_UTF8, 0, quoteFunc }, |
|
1248 { "last_insert_rowid", 0, 0, SQLITE_UTF8, 0, last_insert_rowid }, |
|
1249 { "changes", 0, 0, SQLITE_UTF8, 0, changes }, |
|
1250 { "total_changes", 0, 0, SQLITE_UTF8, 0, total_changes }, |
|
1251 { "replace", 3, 0, SQLITE_UTF8, 0, replaceFunc }, |
|
1252 { "ltrim", 1, 1, SQLITE_UTF8, 0, trimFunc }, |
|
1253 { "ltrim", 2, 1, SQLITE_UTF8, 0, trimFunc }, |
|
1254 { "rtrim", 1, 2, SQLITE_UTF8, 0, trimFunc }, |
|
1255 { "rtrim", 2, 2, SQLITE_UTF8, 0, trimFunc }, |
|
1256 { "trim", 1, 3, SQLITE_UTF8, 0, trimFunc }, |
|
1257 { "trim", 2, 3, SQLITE_UTF8, 0, trimFunc }, |
|
1258 { "zeroblob", 1, 0, SQLITE_UTF8, 0, zeroblobFunc }, |
|
1259 #ifdef SQLITE_SOUNDEX |
|
1260 { "soundex", 1, 0, SQLITE_UTF8, 0, soundexFunc}, |
|
1261 #endif |
|
1262 #ifndef SQLITE_OMIT_LOAD_EXTENSION |
|
1263 { "load_extension", 1, 0, SQLITE_UTF8, 0, loadExt }, |
|
1264 { "load_extension", 2, 0, SQLITE_UTF8, 0, loadExt }, |
|
1265 #endif |
|
1266 }; |
|
1267 static const struct { |
|
1268 char *zName; |
|
1269 signed char nArg; |
|
1270 u8 argType; |
|
1271 u8 needCollSeq; |
|
1272 void (*xStep)(sqlite3_context*,int,sqlite3_value**); |
|
1273 void (*xFinalize)(sqlite3_context*); |
|
1274 } aAggs[] = { |
|
1275 { "min", 1, 0, 1, minmaxStep, minMaxFinalize }, |
|
1276 { "max", 1, 1, 1, minmaxStep, minMaxFinalize }, |
|
1277 { "sum", 1, 0, 0, sumStep, sumFinalize }, |
|
1278 { "total", 1, 0, 0, sumStep, totalFinalize }, |
|
1279 { "avg", 1, 0, 0, sumStep, avgFinalize }, |
|
1280 { "count", 0, 0, 0, countStep, countFinalize }, |
|
1281 { "count", 1, 0, 0, countStep, countFinalize }, |
|
1282 { "group_concat", -1, 0, 0, groupConcatStep, groupConcatFinalize }, |
|
1283 }; |
|
1284 int i; |
|
1285 |
|
1286 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ |
|
1287 void *pArg; |
|
1288 u8 argType = aFuncs[i].argType; |
|
1289 pArg = SQLITE_INT_TO_PTR(argType); |
|
1290 sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg, |
|
1291 aFuncs[i].eTextRep, pArg, aFuncs[i].xFunc, 0, 0); |
|
1292 if( aFuncs[i].needCollSeq ){ |
|
1293 FuncDef *pFunc = sqlite3FindFunction(db, aFuncs[i].zName, |
|
1294 strlen(aFuncs[i].zName), aFuncs[i].nArg, aFuncs[i].eTextRep, 0); |
|
1295 if( pFunc && aFuncs[i].needCollSeq ){ |
|
1296 pFunc->needCollSeq = 1; |
|
1297 } |
|
1298 } |
|
1299 } |
|
1300 #ifndef SQLITE_OMIT_ALTERTABLE |
|
1301 sqlite3AlterFunctions(db); |
|
1302 #endif |
|
1303 #ifndef SQLITE_OMIT_PARSER |
|
1304 sqlite3AttachFunctions(db); |
|
1305 #endif |
|
1306 for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){ |
|
1307 void *pArg = SQLITE_INT_TO_PTR(aAggs[i].argType); |
|
1308 sqlite3CreateFunc(db, aAggs[i].zName, aAggs[i].nArg, SQLITE_UTF8, |
|
1309 pArg, 0, aAggs[i].xStep, aAggs[i].xFinalize); |
|
1310 if( aAggs[i].needCollSeq ){ |
|
1311 FuncDef *pFunc = sqlite3FindFunction( db, aAggs[i].zName, |
|
1312 strlen(aAggs[i].zName), aAggs[i].nArg, SQLITE_UTF8, 0); |
|
1313 if( pFunc && aAggs[i].needCollSeq ){ |
|
1314 pFunc->needCollSeq = 1; |
|
1315 } |
|
1316 } |
|
1317 } |
|
1318 sqlite3RegisterDateTimeFunctions(db); |
|
1319 if( !db->mallocFailed ){ |
|
1320 int rc = sqlite3_overload_function(db, "MATCH", 2); |
|
1321 assert( rc==SQLITE_NOMEM || rc==SQLITE_OK ); |
|
1322 if( rc==SQLITE_NOMEM ){ |
|
1323 db->mallocFailed = 1; |
|
1324 } |
|
1325 } |
|
1326 #ifdef SQLITE_SSE |
|
1327 (void)sqlite3SseFunctions(db); |
|
1328 #endif |
|
1329 #ifdef SQLITE_CASE_SENSITIVE_LIKE |
|
1330 sqlite3RegisterLikeFunctions(db, 1); |
|
1331 #else |
|
1332 sqlite3RegisterLikeFunctions(db, 0); |
|
1333 #endif |
|
1334 } |
|
1335 |
|
1336 /* |
|
1337 ** Set the LIKEOPT flag on the 2-argument function with the given name. |
|
1338 */ |
|
1339 static void setLikeOptFlag(sqlite3 *db, const char *zName, int flagVal){ |
|
1340 FuncDef *pDef; |
|
1341 pDef = sqlite3FindFunction(db, zName, strlen(zName), 2, SQLITE_UTF8, 0); |
|
1342 if( pDef ){ |
|
1343 pDef->flags = flagVal; |
|
1344 } |
|
1345 } |
|
1346 |
|
1347 /* |
|
1348 ** Register the built-in LIKE and GLOB functions. The caseSensitive |
|
1349 ** parameter determines whether or not the LIKE operator is case |
|
1350 ** sensitive. GLOB is always case sensitive. |
|
1351 */ |
|
1352 void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){ |
|
1353 struct compareInfo *pInfo; |
|
1354 if( caseSensitive ){ |
|
1355 pInfo = (struct compareInfo*)&likeInfoAlt; |
|
1356 }else{ |
|
1357 pInfo = (struct compareInfo*)&likeInfoNorm; |
|
1358 } |
|
1359 sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0); |
|
1360 sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0); |
|
1361 sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8, |
|
1362 (struct compareInfo*)&globInfo, likeFunc, 0,0); |
|
1363 setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE); |
|
1364 setLikeOptFlag(db, "like", |
|
1365 caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE); |
|
1366 } |
|
1367 |
|
1368 /* |
|
1369 ** pExpr points to an expression which implements a function. If |
|
1370 ** it is appropriate to apply the LIKE optimization to that function |
|
1371 ** then set aWc[0] through aWc[2] to the wildcard characters and |
|
1372 ** return TRUE. If the function is not a LIKE-style function then |
|
1373 ** return FALSE. |
|
1374 */ |
|
1375 int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){ |
|
1376 FuncDef *pDef; |
|
1377 if( pExpr->op!=TK_FUNCTION || !pExpr->pList ){ |
|
1378 return 0; |
|
1379 } |
|
1380 if( pExpr->pList->nExpr!=2 ){ |
|
1381 return 0; |
|
1382 } |
|
1383 pDef = sqlite3FindFunction(db, (char*)pExpr->token.z, pExpr->token.n, 2, |
|
1384 SQLITE_UTF8, 0); |
|
1385 if( pDef==0 || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){ |
|
1386 return 0; |
|
1387 } |
|
1388 |
|
1389 /* The memcpy() statement assumes that the wildcard characters are |
|
1390 ** the first three statements in the compareInfo structure. The |
|
1391 ** asserts() that follow verify that assumption |
|
1392 */ |
|
1393 memcpy(aWc, pDef->pUserData, 3); |
|
1394 assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll ); |
|
1395 assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne ); |
|
1396 assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet ); |
|
1397 *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0; |
|
1398 return 1; |
|
1399 } |