1 /**
2 * Copyright (c) 2013, Timothy Stack
3 *
4 * All rights reserved.
5 *
6 * Redistribution and use in source and binary forms, with or without
7 * modification, are permitted provided that the following conditions are met:
8 *
9 * * Redistributions of source code must retain the above copyright notice, this
10 * list of conditions and the following disclaimer.
11 * * Redistributions in binary form must reproduce the above copyright notice,
12 * this list of conditions and the following disclaimer in the documentation
13 * and/or other materials provided with the distribution.
14 * * Neither the name of Timothy Stack nor the names of its contributors
15 * may be used to endorse or promote products derived from this software
16 * without specific prior written permission.
17 *
18 * THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ''AS IS'' AND ANY
19 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
20 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
21 * DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY
22 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
23 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
24 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
25 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
26 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
27 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
28 *
29 * @file sqlite-extension-func.c
30 */
31
32 #include "config.h"
33
34 #include <assert.h>
35
36 #include "base/string_util.hh"
37 #include "base/lnav_log.hh"
38 #include "auto_mem.hh"
39 #include "sql_util.hh"
40
41 #include "sqlite-extension-func.hh"
42
43 using namespace std;
44
45 extern "C" {
46 struct sqlite3_api_routines;
47
48 int sqlite3_series_init(
49 sqlite3 *db,
50 char **pzErrMsg,
51 const sqlite3_api_routines *pApi
52 );
53 }
54
55 sqlite_registration_func_t sqlite_registration_funcs[] = {
56 common_extension_functions,
57 state_extension_functions,
58 string_extension_functions,
59 network_extension_functions,
60 fs_extension_functions,
61 json_extension_functions,
62 time_extension_functions,
63
64 nullptr
65 };
66
register_sqlite_funcs(sqlite3 * db,sqlite_registration_func_t * reg_funcs)67 int register_sqlite_funcs(sqlite3 *db, sqlite_registration_func_t *reg_funcs)
68 {
69 int lpc;
70
71 assert(db != nullptr);
72 assert(reg_funcs != nullptr);
73
74 {
75 auto_mem<char> errmsg(sqlite3_free);
76
77 sqlite3_series_init(db, errmsg.out(), nullptr);
78 }
79
80 for (lpc = 0; reg_funcs[lpc]; lpc++) {
81 struct FuncDef *basic_funcs = nullptr;
82 struct FuncDefAgg *agg_funcs = nullptr;
83 int i;
84
85 reg_funcs[lpc](&basic_funcs, &agg_funcs);
86
87 for (i = 0; basic_funcs && basic_funcs[i].zName; i++) {
88 struct FuncDef &fd = basic_funcs[i];
89
90 //sqlite3CreateFunc
91 /* LMH no error checking */
92 sqlite3_create_function(db,
93 basic_funcs[i].zName,
94 basic_funcs[i].nArg,
95 basic_funcs[i].eTextRep,
96 (void *) &fd,
97 basic_funcs[i].xFunc,
98 nullptr,
99 nullptr);
100
101 if (fd.fd_help.ht_context != help_context_t::HC_NONE) {
102 help_text &ht = fd.fd_help;
103
104 sqlite_function_help.insert(make_pair(ht.ht_name, &ht));
105 ht.index_tags();
106 }
107 }
108
109 for (i = 0; agg_funcs && agg_funcs[i].zName; i++) {
110 struct FuncDefAgg &fda = agg_funcs[i];
111
112 //sqlite3CreateFunc
113 sqlite3_create_function(db,
114 agg_funcs[i].zName,
115 agg_funcs[i].nArg,
116 SQLITE_UTF8,
117 (void *) &agg_funcs[i],
118 nullptr,
119 agg_funcs[i].xStep,
120 agg_funcs[i].xFinalize);
121
122 if (fda.fda_help.ht_context != help_context_t::HC_NONE) {
123 help_text &ht = fda.fda_help;
124
125 sqlite_function_help.insert(make_pair(ht.ht_name, &ht));
126 ht.index_tags();
127 }
128 }
129 }
130
131 static help_text builtin_funcs[] = {
132 help_text("abs",
133 "Return the absolute value of the argument")
134 .sql_function()
135 .with_parameter({"x", "The number to convert"})
136 .with_tags({"math"})
137 .with_example({
138 "To get the absolute value of -1",
139 "SELECT abs(-1)"
140 }),
141
142 help_text("changes",
143 "The number of database rows that were changed, inserted, or deleted by the most recent statement.")
144 .sql_function(),
145
146 help_text("char",
147 "Returns a string composed of characters having the given unicode code point values")
148 .sql_function()
149 .with_parameter(help_text("X", "The unicode code point values")
150 .zero_or_more())
151 .with_tags({"string"})
152 .with_example({
153 "To get a string with the code points 0x48 and 0x49",
154 "SELECT char(0x48, 0x49)"
155 }),
156
157 help_text("coalesce",
158 "Returns a copy of its first non-NULL argument, or NULL if all arguments are NULL")
159 .sql_function()
160 .with_parameter({"X", "A value to check for NULL-ness"})
161 .with_parameter(help_text("Y", "A value to check for NULL-ness")
162 .one_or_more())
163 .with_example({
164 "To get the first non-null value from three parameters",
165 "SELECT coalesce(null, 0, null)"
166 }),
167
168 help_text("glob",
169 "Match a string against Unix glob pattern")
170 .sql_function()
171 .with_parameter({"pattern", "The glob pattern"})
172 .with_parameter({"str", "The string to match"})
173 .with_example({
174 "To test if the string 'abc' matches the glob 'a*'",
175 "SELECT glob('a*', 'abc')"
176 }),
177
178 help_text("hex",
179 "Returns a string which is the upper-case hexadecimal rendering of the content of its argument.")
180 .sql_function()
181 .with_parameter({"X", "The blob to convert to hexadecimal"})
182 .with_example({
183 "To get the hexadecimal rendering of the string 'abc'",
184 "SELECT hex('abc')"
185 }),
186
187 help_text("ifnull",
188 "Returns a copy of its first non-NULL argument, or NULL if both arguments are NULL")
189 .sql_function()
190 .with_parameter({"X", "A value to check for NULL-ness"})
191 .with_parameter({"Y", "A value to check for NULL-ness"})
192 .with_example({
193 "To get the first non-null value between null and zero",
194 "SELECT ifnull(null, 0)"
195 }),
196
197 help_text("instr",
198 "Finds the first occurrence of the needle within the haystack and returns the number of prior characters plus 1, or 0 if the needle was not found")
199 .sql_function()
200 .with_parameter({"haystack", "The string to search within"})
201 .with_parameter({"needle", "The string to look for in the haystack"})
202 .with_tags({"string"})
203 .with_example({
204 "To test get the position of 'b' in the string 'abc'",
205 "SELECT instr('abc', 'b')"
206 }),
207
208 help_text("last_insert_rowid",
209 "Returns the ROWID of the last row insert from the database connection which invoked the function")
210 .sql_function(),
211
212 help_text("length",
213 "Returns the number of characters (not bytes) in the given string prior to the first NUL character")
214 .sql_function()
215 .with_parameter({"str", "The string to determine the length of"})
216 .with_tags({"string"})
217 .with_example({
218 "To get the length of the string 'abc'",
219 "SELECT length('abc')"
220 }),
221
222 help_text("like",
223 "Match a string against a pattern")
224 .sql_function()
225 .with_parameter({"pattern",
226 "The pattern to match. "
227 "A percent symbol (%) will match zero or more characters "
228 "and an underscore (_) will match a single character."})
229 .with_parameter({"str", "The string to match"})
230 .with_parameter(help_text("escape",
231 "The escape character that can be used to prefix a literal percent or underscore in the pattern.")
232 .optional())
233 .with_example({
234 "To test if the string 'aabcc' contains the letter 'b'",
235 "SELECT like('%b%', 'aabcc')"
236 })
237 .with_example({
238 "To test if the string 'aab%' ends with 'b%'",
239 "SELECT like('%b:%', 'aab%', ':')"
240 }),
241
242 help_text("likelihood",
243 "Provides a hint to the query planner that the first argument is a boolean that is true with the given probability")
244 .sql_function()
245 .with_parameter({"value", "The boolean value to return"})
246 .with_parameter({"probability", "A floating point constant between 0.0 and 1.0"}),
247
248 help_text("likely",
249 "Short-hand for likelihood(X,0.9375)")
250 .sql_function()
251 .with_parameter({"value", "The boolean value to return"}),
252
253 help_text("load_extension",
254 "Loads SQLite extensions out of the given shared library file using the given entry point.")
255 .sql_function()
256 .with_parameter({"path", "The path to the shared library containing the extension."})
257 .with_parameter(help_text("entry-point", "")
258 .optional()),
259
260 help_text("lower",
261 "Returns a copy of the given string with all ASCII characters converted to lower case.")
262 .sql_function()
263 .with_parameter({"str", "The string to convert."})
264 .with_tags({"string"})
265 .with_example({
266 "To lowercase the string 'AbC'",
267 "SELECT lower('AbC')"
268 }),
269
270 help_text("ltrim",
271 "Returns a string formed by removing any and all characters that appear in the second argument from the left side of the first.")
272 .sql_function()
273 .with_parameter({"str", "The string to trim characters from the left side"})
274 .with_parameter(help_text("chars", "The characters to trim. Defaults to spaces.")
275 .optional())
276 .with_tags({"string"})
277 .with_example({
278 "To trim the leading whitespace from the string ' abc'",
279 "SELECT ltrim(' abc')"})
280 .with_example({
281 "To trim the characters 'a' or 'b' from the left side of the string 'aaaabbbc'",
282 "SELECT ltrim('aaaabbbc', 'ab')"
283 }),
284
285 help_text("max",
286 "Returns the argument with the maximum value, or return NULL if any argument is NULL.")
287 .sql_function()
288 .with_parameter(help_text("X", "The numbers to find the maximum of. "
289 "If only one argument is given, this function operates as an aggregate.")
290 .one_or_more())
291 .with_tags({"math"})
292 .with_example({
293 "To get the largest value from the parameters",
294 "SELECT max(2, 1, 3)"})
295 .with_example({
296 "To get the largest value from an aggregate",
297 "SELECT max(status) FROM http_status_codes"
298 }),
299
300 help_text("min",
301 "Returns the argument with the minimum value, or return NULL if any argument is NULL.")
302 .sql_function()
303 .with_parameter(help_text("X", "The numbers to find the minimum of. "
304 "If only one argument is given, this function operates as an aggregate.")
305 .one_or_more())
306 .with_tags({"math"})
307 .with_example({
308 "To get the smallest value from the parameters",
309 "SELECT min(2, 1, 3)"})
310 .with_example({
311 "To get the smallest value from an aggregate",
312 "SELECT min(status) FROM http_status_codes"
313 }),
314
315 help_text("nullif",
316 "Returns its first argument if the arguments are different and NULL if the arguments are the same.")
317 .sql_function()
318 .with_parameter({"X", "The first argument to compare."})
319 .with_parameter({"Y", "The argument to compare against the first."})
320 .with_example({
321 "To test if 1 is different from 1",
322 "SELECT nullif(1, 1)"})
323 .with_example({
324 "To test if 1 is different from 2",
325 "SELECT nullif(1, 2)"
326 }),
327
328 help_text("printf",
329 "Returns a string with this functions arguments substituted into the given format. "
330 "Substitution points are specified using percent (%) options, much like the standard C printf() function.")
331 .sql_function()
332 .with_parameter({"format", "The format of the string to return."})
333 .with_parameter(help_text("X", "The argument to substitute at a given position in the format."))
334 .with_tags({"string"})
335 .with_example({
336 "To substitute 'World' into the string 'Hello, %s!'",
337 "SELECT printf('Hello, %s!', 'World')"})
338 .with_example({
339 "To right-align 'small' in the string 'align:' with a column width of 10",
340 "SELECT printf('align: % 10s', 'small')"})
341 .with_example({
342 "To format 11 with a width of five characters and leading zeroes",
343 "SELECT printf('value: %05d', 11)"
344 }),
345
346 help_text("quote",
347 "Returns the text of an SQL literal which is the value of its argument suitable for inclusion into an SQL statement.")
348 .sql_function()
349 .with_parameter({"X", "The string to quote."})
350 .with_example({
351 "To quote the string 'abc'",
352 "SELECT quote('abc')"})
353 .with_example({
354 "To quote the string 'abc'123'",
355 "SELECT quote('abc''123')"
356 }),
357
358 help_text("random",
359 "Returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.")
360 .sql_function(),
361
362 help_text("randomblob",
363 "Return an N-byte blob containing pseudo-random bytes.")
364 .sql_function()
365 .with_parameter({"N", "The size of the blob in bytes."}),
366
367 help_text("replace",
368 "Returns a string formed by substituting the replacement string for every occurrence of the old string in the given string.")
369 .sql_function()
370 .with_parameter({"str", "The string to perform substitutions on."})
371 .with_parameter({"old", "The string to be replaced."})
372 .with_parameter({"replacement", "The string to replace any occurrences of the old string with."})
373 .with_tags({"string"})
374 .with_example({
375 "To replace the string 'x' with 'z' in 'abc'",
376 "SELECT replace('abc', 'x', 'z')"})
377 .with_example({
378 "To replace the string 'a' with 'z' in 'abc'",
379 "SELECT replace('abc', 'a', 'z')"
380 }),
381
382 help_text("round",
383 "Returns a floating-point value rounded to the given number of digits to the right of the decimal point.")
384 .sql_function()
385 .with_parameter({"num", "The value to round."})
386 .with_parameter(help_text("digits", "The number of digits to the right of the decimal to round to.")
387 .optional())
388 .with_tags({"math"})
389 .with_example({
390 "To round the number 123.456 to an integer",
391 "SELECT round(123.456)"})
392 .with_example({
393 "To round the number 123.456 to a precision of 1",
394 "SELECT round(123.456, 1)"})
395 .with_example({
396 "To round the number 123.456 to a precision of 5",
397 "SELECT round(123.456, 5)"
398 }),
399
400 help_text("rtrim",
401 "Returns a string formed by removing any and all characters that appear in the second argument from the right side of the first.")
402 .sql_function()
403 .with_parameter({"str", "The string to trim characters from the right side"})
404 .with_parameter(help_text("chars", "The characters to trim. Defaults to spaces.")
405 .optional())
406 .with_tags({"string"})
407 .with_example({
408 "To trim the whitespace from the end of the string 'abc '",
409 "SELECT rtrim('abc ')"
410 })
411 .with_example({
412 "To trim the characters 'b' and 'c' from the string 'abbbbcccc'",
413 "SELECT rtrim('abbbbcccc', 'bc')"
414 }),
415
416 help_text("sqlite_compileoption_get",
417 "Returns the N-th compile-time option used to build SQLite or NULL if N is out of range.")
418 .sql_function()
419 .with_parameter({"N", "The option number to get"}),
420
421 help_text("sqlite_compileoption_used",
422 "Returns true (1) or false (0) depending on whether or not that compile-time option was used during the build.")
423 .sql_function()
424 .with_parameter({"option", "The name of the compile-time option."})
425 .with_example({
426 "To check if the SQLite library was compiled with ENABLE_FTS3",
427 "SELECT sqlite_compileoption_used('ENABLE_FTS3')"
428 }),
429
430 help_text("sqlite_source_id",
431 "Returns a string that identifies the specific version of the source code that was used to build the SQLite library.")
432 .sql_function(),
433
434 help_text("sqlite_version",
435 "Returns the version string for the SQLite library that is running.")
436 .sql_function(),
437
438 help_text("substr",
439 "Returns a substring of input string X that begins with the Y-th character and which is Z characters long.")
440 .sql_function()
441 .with_parameter({"str", "The string to extract a substring from."})
442 .with_parameter({"start", "The index within 'str' that is the start of the substring. "
443 "Indexes begin at 1. "
444 "A negative value means that the substring is found by counting from the right rather than the left. "})
445 .with_parameter(help_text("size", "The size of the substring. "
446 "If not given, then all characters through the end of the string are returned. "
447 "If the value is negative, then the characters before the start are returned.")
448 .optional())
449 .with_tags({"string"})
450 .with_example({
451 "To get the substring starting at the second character until the end of the string 'abc'",
452 "SELECT substr('abc', 2)"
453 })
454 .with_example({
455 "To get the substring of size one starting at the second character of the string 'abc'",
456 "SELECT substr('abc', 2, 1)"
457 })
458 .with_example({
459 "To get the substring starting at the last character until the end of the string 'abc'",
460 "SELECT substr('abc', -1)"
461 })
462 .with_example({
463 "To get the substring starting at the last character and going backwards one step of the string 'abc'",
464 "SELECT substr('abc', -1, -1)"
465 }),
466
467 help_text("total_changes",
468 "Returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened.")
469 .sql_function(),
470
471 help_text("trim",
472 "Returns a string formed by removing any and all characters that appear in the second argument from the left and right sides of the first.")
473 .sql_function()
474 .with_parameter({"str", "The string to trim characters from the left and right sides."})
475 .with_parameter(help_text("chars", "The characters to trim. Defaults to spaces.")
476 .optional())
477 .with_tags({"string"})
478 .with_example({
479 "To trim whitespace from the start and end of the string ' abc '",
480 "SELECT trim(' abc ')"
481 })
482 .with_example({
483 "To trim the characters '-' and '+' from the string '-+abc+-'",
484 "SELECT trim('-+abc+-', '-+')"
485 }),
486
487 help_text("typeof",
488 "Returns a string that indicates the datatype of the expression X: \"null\", \"integer\", \"real\", \"text\", or \"blob\".")
489 .sql_function()
490 .with_parameter({"X", "The expression to check."})
491 .with_example({
492 "To get the type of the number 1",
493 "SELECT typeof(1)"
494 })
495 .with_example({
496 "To get the type of the string 'abc'",
497 "SELECT typeof('abc')"
498 }),
499
500 help_text("unicode",
501 "Returns the numeric unicode code point corresponding to the first character of the string X.")
502 .sql_function()
503 .with_parameter({"X", "The string to examine."})
504 .with_tags({"string"})
505 .with_example({
506 "To get the unicode code point for the first character of 'abc'",
507 "SELECT unicode('abc')"
508 }),
509
510 help_text("unlikely",
511 "Short-hand for likelihood(X, 0.0625)")
512 .sql_function()
513 .with_parameter({"value", "The boolean value to return"}),
514
515 help_text("upper",
516 "Returns a copy of the given string with all ASCII characters converted to upper case.")
517 .sql_function()
518 .with_parameter({"str", "The string to convert."})
519 .with_tags({"string"})
520 .with_example({
521 "To uppercase the string 'aBc'",
522 "SELECT upper('aBc')"
523 }),
524
525 help_text("zeroblob",
526 "Returns a BLOB consisting of N bytes of 0x00.")
527 .sql_function()
528 .with_parameter({"N", "The size of the BLOB."}),
529
530 help_text("date",
531 "Returns the date in this format: YYYY-MM-DD.")
532 .sql_function()
533 .with_parameter({"timestring", "The string to convert to a date."})
534 .with_parameter(help_text("modifier", "A transformation that is applied to the value to the left.")
535 .zero_or_more())
536 .with_tags({"datetime"})
537 .with_example({
538 "To get the date portion of the timestamp '2017-01-02T03:04:05'",
539 "SELECT date('2017-01-02T03:04:05')"
540 })
541 .with_example({
542 "To get the date portion of the timestamp '2017-01-02T03:04:05' plus one day",
543 "SELECT date('2017-01-02T03:04:05', '+1 day')"
544 })
545 .with_example({
546 "To get the date portion of the epoch timestamp 1491341842",
547 "SELECT date(1491341842, 'unixepoch')"
548 }),
549
550 help_text("time",
551 "Returns the time in this format: HH:MM:SS.")
552 .sql_function()
553 .with_parameter({"timestring", "The string to convert to a time."})
554 .with_parameter(help_text("modifier", "A transformation that is applied to the value to the left.")
555 .zero_or_more())
556 .with_tags({"datetime"})
557 .with_example({
558 "To get the time portion of the timestamp '2017-01-02T03:04:05'",
559 "SELECT time('2017-01-02T03:04:05')"
560 })
561 .with_example({
562 "To get the time portion of the timestamp '2017-01-02T03:04:05' plus one minute",
563 "SELECT time('2017-01-02T03:04:05', '+1 minute')"
564 })
565 .with_example({
566 "To get the time portion of the epoch timestamp 1491341842",
567 "SELECT time(1491341842, 'unixepoch')"
568 }),
569
570 help_text("datetime",
571 "Returns the date and time in this format: YYYY-MM-DD HH:MM:SS.")
572 .sql_function()
573 .with_parameter({"timestring", "The string to convert to a date with time."})
574 .with_parameter(help_text("modifier", "A transformation that is applied to the value to the left.")
575 .zero_or_more())
576 .with_tags({"datetime"})
577 .with_example({
578 "To get the date and time portion of the timestamp '2017-01-02T03:04:05'",
579 "SELECT datetime('2017-01-02T03:04:05')"
580 })
581 .with_example({
582 "To get the date and time portion of the timestamp '2017-01-02T03:04:05' plus one minute",
583 "SELECT datetime('2017-01-02T03:04:05', '+1 minute')"
584 })
585 .with_example({
586 "To get the date and time portion of the epoch timestamp 1491341842",
587 "SELECT datetime(1491341842, 'unixepoch')"
588 }),
589
590 help_text("julianday",
591 "Returns the number of days since noon in Greenwich on November 24, 4714 B.C.")
592 .sql_function()
593 .with_parameter({"timestring", "The string to convert to a date with time."})
594 .with_parameter(help_text("modifier", "A transformation that is applied to the value to the left.")
595 .zero_or_more())
596 .with_tags({"datetime"})
597 .with_example({
598 "To get the julian day from the timestamp '2017-01-02T03:04:05'",
599 "SELECT julianday('2017-01-02T03:04:05')"
600 })
601 .with_example({
602 "To get the julian day from the timestamp '2017-01-02T03:04:05' plus one minute",
603 "SELECT julianday('2017-01-02T03:04:05', '+1 minute')"
604 })
605 .with_example({
606 "To get the julian day from the timestamp 1491341842",
607 "SELECT julianday(1491341842, 'unixepoch')"
608 }),
609
610 help_text("strftime",
611 "Returns the date formatted according to the format string specified as the first argument.")
612 .sql_function()
613 .with_parameter({"format", "A format string with substitutions similar to those found in the strftime() standard C library."})
614 .with_parameter({"timestring", "The string to convert to a date with time."})
615 .with_parameter(help_text("modifier", "A transformation that is applied to the value to the left.")
616 .zero_or_more())
617 .with_tags({"datetime"})
618 .with_example({
619 "To get the year from the timestamp '2017-01-02T03:04:05'",
620 "SELECT strftime('%Y', '2017-01-02T03:04:05')"
621 })
622 .with_example({
623 "To create a string with the time from the timestamp '2017-01-02T03:04:05' plus one minute",
624 "SELECT strftime('The time is: %H:%M:%S', '2017-01-02T03:04:05', '+1 minute')"
625 })
626 .with_example({
627 "To create a string with the Julian day from the epoch timestamp 1491341842",
628 "SELECT strftime('Julian day: %J', 1491341842, 'unixepoch')"
629 }),
630
631 help_text("avg",
632 "Returns the average value of all non-NULL numbers within a group.")
633 .sql_function()
634 .with_parameter({"X", "The value to compute the average of."})
635 .with_tags({"math"})
636 .with_example({
637 "To get the average of the column 'ex_duration' from the table 'lnav_example_log'",
638 "SELECT avg(ex_duration) FROM lnav_example_log"
639 })
640 .with_example({
641 "To get the average of the column 'ex_duration' from the table 'lnav_example_log' when grouped by 'ex_procname'",
642 "SELECT ex_procname, avg(ex_duration) FROM lnav_example_log GROUP BY ex_procname"
643 }),
644
645 help_text("count",
646 "If the argument is '*', the total number of rows in the group is returned. "
647 "Otherwise, the number of times the argument is non-NULL.")
648 .sql_function()
649 .with_parameter({"X", "The value to count."})
650 .with_example({
651 "To get the count of the non-NULL rows of 'lnav_example_log'",
652 "SELECT count(*) FROM lnav_example_log"
653 })
654 .with_example({
655 "To get the count of the non-NULL values of 'log_part' from 'lnav_example_log'",
656 "SELECT count(log_part) FROM lnav_example_log"
657 }),
658
659 help_text("group_concat",
660 "Returns a string which is the concatenation of all non-NULL values of X separated by a comma or the given separator.")
661 .sql_function()
662 .with_parameter({"X", "The value to concatenate."})
663 .with_parameter(help_text("sep", "The separator to place between the values.")
664 .optional())
665 .with_tags({"string"})
666 .with_example({
667 "To concatenate the values of the column 'ex_procname' from the table 'lnav_example_log'",
668 "SELECT group_concat(ex_procname) FROM lnav_example_log"
669 })
670 .with_example({
671 "To join the values of the column 'ex_procname' using the string ', '",
672 "SELECT group_concat(ex_procname, ', ') FROM lnav_example_log"
673 })
674 .with_example({
675 "To concatenate the distinct values of the column 'ex_procname' from the table 'lnav_example_log'",
676 "SELECT group_concat(DISTINCT ex_procname) FROM lnav_example_log"
677 }),
678
679 help_text("sum",
680 "Returns the sum of the values in the group as an integer.")
681 .sql_function()
682 .with_parameter({"X", "The values to add."})
683 .with_tags({"math"})
684 .with_example({
685 "To sum all of the values in the column 'ex_duration' from the table 'lnav_example_log'",
686 "SELECT sum(ex_duration) FROM lnav_example_log"
687 }),
688
689 help_text("total",
690 "Returns the sum of the values in the group as a floating-point.")
691 .sql_function()
692 .with_parameter({"X", "The values to add."})
693 .with_tags({"math"})
694 .with_example({
695 "To total all of the values in the column 'ex_duration' from the table 'lnav_example_log'",
696 "SELECT total(ex_duration) FROM lnav_example_log"
697 }),
698
699 help_text("generate_series",
700 "A table-valued-function that returns the whole numbers "
701 "between a lower and upper bound, inclusive")
702 .sql_table_valued_function()
703 .with_parameter({"start", "The starting point of the series"})
704 .with_parameter({"stop", "The stopping point of the series"})
705 .with_parameter(help_text("step", "The increment between each value")
706 .optional())
707 .with_result({"value", "The number in the series"})
708 .with_example({
709 "To generate the numbers in the range [10, 14]",
710 "SELECT value FROM generate_series(10, 14)"
711 })
712 .with_example({
713 "To generate every other number in the range [10, 14]",
714 "SELECT value FROM generate_series(10, 14, 2)"
715 })
716 .with_example({
717 "To count down from five to 1",
718 "SELECT value FROM generate_series(1, 5, -1)"
719 })
720 };
721
722 for (auto &ht : builtin_funcs) {
723 sqlite_function_help.insert(make_pair(ht.ht_name, &ht));
724 ht.index_tags();
725 }
726
727 static help_text builtin_win_funcs[] = {
728 help_text("row_number", "Returns the number of the row within the current partition, starting from 1.")
729 .sql_function()
730 .with_tags({"window"})
731 .with_example({
732 "To number messages from a process",
733 "SELECT row_number() OVER (PARTITION BY ex_procname ORDER BY log_line) AS msg_num, ex_procname, log_body FROM lnav_example_log"
734 }),
735
736 help_text("rank", "Returns the row_number() of the first peer in each group with gaps")
737 .sql_function()
738 .with_tags({"window"}),
739
740 help_text("dense_rank", "Returns the row_number() of the first peer in each group without gaps")
741 .sql_function()
742 .with_tags({"window"}),
743
744 help_text("percent_rank", "Returns (rank - 1) / (partition-rows - 1)")
745 .sql_function()
746 .with_tags({"window"}),
747
748 help_text("cume_dist", "Returns the cumulative distribution")
749 .sql_function()
750 .with_tags({"window"}),
751
752 help_text("ntile", "Returns the number of the group that the current row is a part of")
753 .sql_function()
754 .with_parameter({"groups", "The number of groups"})
755 .with_tags({"window"}),
756
757 help_text("lag", "Returns the result of evaluating the expression against the previous row in the partition.")
758 .sql_function()
759 .with_parameter({"expr", "The expression to execute over the previous row"})
760 .with_parameter(help_text("offset", "The offset from the current row in the partition")
761 .optional())
762 .with_parameter(help_text("default", "The default value if the previous row does not exist instead of NULL")
763 .optional())
764 .with_tags({"window"}),
765
766 help_text("lead", "Returns the result of evaluating the expression against the next row in the partition.")
767 .sql_function()
768 .with_parameter({"expr", "The expression to execute over the next row"})
769 .with_parameter(help_text("offset", "The offset from the current row in the partition")
770 .optional())
771 .with_parameter(help_text("default", "The default value if the next row does not exist instead of NULL")
772 .optional())
773 .with_tags({"window"}),
774
775 help_text("first_value", "Returns the result of evaluating the expression against the first row in the window frame.")
776 .sql_function()
777 .with_parameter({"expr", "The expression to execute over the first row"})
778 .with_tags({"window"}),
779
780 help_text("last_value", "Returns the result of evaluating the expression against the last row in the window frame.")
781 .sql_function()
782 .with_parameter({"expr", "The expression to execute over the last row"})
783 .with_tags({"window"}),
784
785 help_text("nth_value", "Returns the result of evaluating the expression against the nth row in the window frame.")
786 .sql_function()
787 .with_parameter({"expr", "The expression to execute over the nth row"})
788 .with_parameter({"N", "The row number"})
789 .with_tags({"window"}),
790 };
791
792 for (auto &ht : builtin_win_funcs) {
793 sqlite_function_help.insert(make_pair(ht.ht_name, &ht));
794 ht.index_tags();
795 }
796
797 static help_text idents[] = {
798 help_text("ATTACH",
799 "Attach a database file to the current connection.")
800 .sql_keyword()
801 .with_parameter(help_text("filename", "The path to the database file.")
802 .with_flag_name("DATABASE"))
803 .with_parameter(help_text("schema-name", "The prefix for tables in this database.")
804 .with_flag_name("AS"))
805 .with_example({
806 "To attach the database file '/tmp/customers.db' with the name customers",
807 "ATTACH DATABASE '/tmp/customers.db' AS customers"
808 }),
809
810 help_text("DETACH",
811 "Detach a database from the current connection.")
812 .sql_keyword()
813 .with_parameter(help_text("schema-name", "The prefix for tables in this database.")
814 .with_flag_name("DATABASE"))
815 .with_example({
816 "To detach the database named 'customers'",
817 "DETACH DATABASE customers"
818 }),
819
820 help_text("CREATE", "Assign a name to a SELECT statement")
821 .sql_keyword()
822 .with_parameter(help_text("TEMP")
823 .optional())
824 .with_parameter(help_text("")
825 .with_flag_name("VIEW"))
826 .with_parameter(help_text("IF NOT EXISTS", "Do not create the view if it already exists")
827 .optional())
828 .with_parameter(help_text("schema-name.", "The database to create the view in")
829 .optional())
830 .with_parameter(help_text("view-name", "The name of the view"))
831 .with_parameter(help_text("select-stmt", "The SELECT statement the view represents")
832 .with_flag_name("AS")),
833
834 help_text("CREATE", "Create a table")
835 .sql_keyword()
836 .with_parameter(help_text("TEMP").optional())
837 .with_parameter(help_text("")
838 .with_flag_name("TABLE"))
839 .with_parameter(help_text("IF NOT EXISTS")
840 .optional())
841 .with_parameter(help_text("schema-name.")
842 .optional())
843 .with_parameter(help_text("table-name"))
844 .with_parameter(help_text("select-stmt")
845 .with_flag_name("AS")),
846
847 help_text("DELETE", "Delete rows from a table")
848 .sql_keyword()
849 .with_parameter(help_text("table-name", "The name of the table")
850 .with_flag_name("FROM"))
851 .with_parameter(help_text("cond", "The conditions used to delete the rows.")
852 .with_flag_name("WHERE")
853 .optional()),
854
855 help_text("DROP", "Drop an index")
856 .sql_keyword()
857 .with_parameter(help_text("")
858 .with_flag_name("INDEX"))
859 .with_parameter(help_text("IF EXISTS")
860 .optional())
861 .with_parameter(help_text("schema-name.")
862 .optional())
863 .with_parameter(help_text("index-name")),
864
865 help_text("DROP", "Drop a table")
866 .sql_keyword()
867 .with_parameter(help_text("")
868 .with_flag_name("TABLE"))
869 .with_parameter(help_text("IF EXISTS")
870 .optional())
871 .with_parameter(help_text("schema-name.")
872 .optional())
873 .with_parameter(help_text("table-name")),
874
875 help_text("DROP", "Drop a view")
876 .sql_keyword()
877 .with_parameter(help_text("")
878 .with_flag_name("VIEW"))
879 .with_parameter(help_text("IF EXISTS")
880 .optional())
881 .with_parameter(help_text("schema-name.")
882 .optional())
883 .with_parameter(help_text("view-name")),
884
885 help_text("DROP", "Drop a trigger")
886 .sql_keyword()
887 .with_parameter(help_text("")
888 .with_flag_name("TRIGGER"))
889 .with_parameter(help_text("IF EXISTS")
890 .optional())
891 .with_parameter(help_text("schema-name.")
892 .optional())
893 .with_parameter(help_text("trigger-name")),
894
895 help_text("INSERT", "Insert rows into a table")
896 .sql_keyword()
897 .with_parameter(help_text("")
898 .with_flag_name("INTO"))
899 .with_parameter(help_text("schema-name.")
900 .optional())
901 .with_parameter(help_text("table-name"))
902 .with_parameter(help_text("column-name")
903 .with_grouping("(", ")")
904 .zero_or_more())
905 .with_parameter(help_text("expr")
906 .with_flag_name("VALUES")
907 .with_grouping("(", ")")
908 .one_or_more())
909 .with_example({
910 "To insert the pair containing 'MSG' and 'HELLO, WORLD!' into the 'environ' table",
911 "INSERT INTO environ VALUES ('MSG', 'HELLO, WORLD!')"
912 }),
913
914 help_text("SELECT",
915 "Query the database and return zero or more rows of data.")
916 .sql_keyword()
917 .with_parameter(help_text("result-column", "")
918 .one_or_more())
919 .with_parameter(help_text("table", "The table(s) to query for data")
920 .with_flag_name("FROM")
921 .zero_or_more())
922 .with_parameter(help_text("cond", "The conditions used to select the rows to return.")
923 .with_flag_name("WHERE")
924 .optional())
925 .with_parameter(help_text("grouping-expr", "The expression to use when grouping rows.")
926 .with_flag_name("GROUP BY")
927 .zero_or_more())
928 .with_parameter(help_text("ordering-term", "The values to use when ordering the result set.")
929 .with_flag_name("ORDER BY")
930 .zero_or_more())
931 .with_parameter(help_text("limit-expr", "The maximum number of rows to return")
932 .with_flag_name("LIMIT")
933 .zero_or_more())
934 .with_example({
935 "To select all of the columns from the table 'syslog_log'",
936 "SELECT * FROM syslog_log"
937 }),
938
939 help_text("WITH",
940 "Create a temporary view that exists only for the duration of a SQL statement.")
941 .sql_keyword()
942 .with_parameter(help_text("")
943 .with_flag_name("RECURSIVE")
944 .optional())
945 .with_parameter({"cte-table-name", "The name for the temporary table."})
946 .with_parameter(help_text("select-stmt", "The SELECT statement used to populate the temporary table.")
947 .with_flag_name("AS")),
948
949 help_text("UPDATE",
950 "Modify a subset of values in zero or more rows of the given table")
951 .sql_keyword()
952 .with_parameter(help_text("table", "The table to update"))
953 .with_parameter(help_text("")
954 .with_flag_name("SET"))
955 .with_parameter(help_text("column-name", "The columns in the table to update.")
956 .with_parameter(help_text("expr", "The values to place into the column.")
957 .with_flag_name("="))
958 .one_or_more())
959 .with_parameter(help_text("cond", "The condition used to determine whether a row should be updated.")
960 .with_flag_name("WHERE")
961 .optional())
962 .with_example({
963 "To mark the syslog message at line 40",
964 "UPDATE syslog_log SET log_mark = 1 WHERE log_line = 40"
965 }),
966
967 help_text("CASE",
968 "Evaluate a series of expressions in order until one evaluates to true and then return it's result. "
969 "Similar to an IF-THEN-ELSE construct in other languages.")
970 .sql_keyword()
971 .with_parameter(help_text("base-expr", "The base expression that is used for comparison in the branches")
972 .optional())
973 .with_parameter(help_text("cmp-expr", "The expression to test if this branch should be taken")
974 .with_flag_name("WHEN")
975 .one_or_more()
976 .with_parameter(help_text("then-expr", "The result for this branch.")
977 .with_flag_name("THEN")))
978 .with_parameter(help_text("else-expr", "The result of this CASE if no branches matched.")
979 .with_flag_name("ELSE")
980 .optional())
981 .with_parameter(help_text("")
982 .with_flag_name("END"))
983 .with_example({
984 "To evaluate the number one and return the string 'one'",
985 "SELECT CASE 1 WHEN 0 THEN 'zero' WHEN 1 THEN 'one' END"
986 }),
987
988 help_text("CAST",
989 "Convert the value of the given expression to a different storage class specified by type-name.")
990 .sql_function()
991 .with_parameter({"expr", "The value to convert."})
992 .with_parameter(help_text("type-name", "The name of the type to convert to.")
993 .with_flag_name("AS"))
994 .with_example({
995 "To cast the value 1.23 as an integer",
996 "SELECT CAST(1.23 AS INTEGER)"
997 }),
998
999 help_text("expr",
1000 "Match an expression against a glob pattern.")
1001 .sql_infix()
1002 .with_parameter(help_text("NOT")
1003 .optional())
1004 .with_parameter(help_text("pattern", "The glob pattern to match against.")
1005 .with_flag_name("GLOB"))
1006 .with_example({
1007 "To check if a value matches the pattern '*.log'",
1008 "SELECT 'foobar.log' GLOB '*.log'"
1009 }),
1010
1011 help_text("expr",
1012 "Match an expression against a text pattern.")
1013 .sql_infix()
1014 .with_parameter(help_text("NOT")
1015 .optional())
1016 .with_parameter(help_text("pattern", "The pattern to match against.")
1017 .with_flag_name("LIKE"))
1018 .with_example({
1019 "To check if a value matches the pattern 'Hello, %!'",
1020 "SELECT 'Hello, World!' LIKE 'Hello, %!'"
1021 }),
1022
1023 help_text("expr",
1024 "Match an expression against a regular expression.")
1025 .sql_infix()
1026 .with_parameter(help_text("NOT")
1027 .optional())
1028 .with_parameter(help_text("pattern", "The regular expression to match against.")
1029 .with_flag_name("REGEXP"))
1030 .with_example({
1031 "To check if a value matches the pattern 'file-\\d+'",
1032 "SELECT 'file-23' REGEXP 'file-\\d+'"
1033 }),
1034
1035 help_text("expr",
1036 "Assign a collating sequence to the expression.")
1037 .sql_infix()
1038 .with_parameter(help_text("collation-name", "The name of the collator.")
1039 .with_flag_name("COLLATE"))
1040 .with_example({
1041 "To change the collation method for string comparisons",
1042 "SELECT ('a2' < 'a10'), ('a2' < 'a10' COLLATE naturalnocase)"
1043 }),
1044
1045 help_text("expr",
1046 "Test if an expression is between two values.")
1047 .sql_infix()
1048 .with_parameter(help_text("NOT")
1049 .optional())
1050 .with_parameter(help_text("low", "The low point")
1051 .with_flag_name("BETWEEN"))
1052 .with_parameter(help_text("hi", "The high point")
1053 .with_flag_name("AND"))
1054 .with_example({
1055 "To check if 3 is between 5 and 10",
1056 "SELECT 3 BETWEEN 5 AND 10"
1057 })
1058 .with_example({
1059 "To check if 10 is between 5 and 10",
1060 "SELECT 10 BETWEEN 5 AND 10"
1061 }),
1062
1063 help_text("OVER", "Executes the preceding function over a window")
1064 .sql_keyword()
1065 .with_parameter({"window-name", "The name of the window definition"}),
1066
1067 help_text("OVER", "Executes the preceding function over a window")
1068 .sql_function()
1069 .with_parameter(help_text{"base-window-name", "The name of the window definition"}
1070 .optional())
1071 .with_parameter(help_text{"expr", "The values to use for partitioning"}
1072 .with_flag_name("PARTITION BY")
1073 .zero_or_more())
1074 .with_parameter(help_text{"expr", "The values used to order the rows in the window"}
1075 .with_flag_name("ORDER BY")
1076 .zero_or_more())
1077 .with_parameter(help_text{"frame-spec", "Determines which output rows are read by an aggregate window function"}
1078 .optional()),
1079 };
1080
1081 for (auto &ht : idents) {
1082 sqlite_function_help.insert(make_pair(toupper(ht.ht_name), &ht));
1083 for (const auto ¶m : ht.ht_parameters) {
1084 if (!param.ht_flag_name) {
1085 continue;
1086 }
1087 sqlite_function_help.insert(make_pair(toupper(param.ht_flag_name), &ht));
1088 }
1089 }
1090
1091 return 0;
1092 }
1093