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 sql_util.cc
30 */
31
32 #include "config.h"
33
34 #include <ctype.h>
35 #include <stdio.h>
36 #include <string.h>
37
38 #include <regex>
39 #include <algorithm>
40 #include <vector>
41
42 #include "auto_mem.hh"
43 #include "sql_util.hh"
44 #include "base/injector.hh"
45 #include "base/string_util.hh"
46 #include "base/lnav_log.hh"
47 #include "base/time_util.hh"
48 #include "pcrepp/pcrepp.hh"
49 #include "readline_curses.hh"
50 #include "bound_tags.hh"
51 #include "sqlite-extension-func.hh"
52
53 using namespace std;
54
55 /**
56 * Copied from -- http://www.sqlite.org/lang_keywords.html
57 */
58 const char *sql_keywords[] = {
59 "ABORT",
60 "ACTION",
61 "ADD",
62 "AFTER",
63 "ALL",
64 "ALTER",
65 "ALWAYS",
66 "ANALYZE",
67 "AND",
68 "AS",
69 "ASC",
70 "ATTACH",
71 "AUTOINCREMENT",
72 "BEFORE",
73 "BEGIN",
74 "BETWEEN",
75 "BY",
76 "CASCADE",
77 "CASE",
78 "CAST",
79 "CHECK",
80 "COLLATE",
81 "COLUMN",
82 "COMMIT",
83 "CONFLICT",
84 "CONSTRAINT",
85 "CREATE",
86 "CROSS",
87 "CURRENT",
88 "CURRENT_DATE",
89 "CURRENT_TIME",
90 "CURRENT_TIMESTAMP",
91 "DATABASE",
92 "DEFAULT",
93 "DEFERRABLE",
94 "DEFERRED",
95 "DELETE",
96 "DESC",
97 "DETACH",
98 "DISTINCT",
99 "DO",
100 "DROP",
101 "EACH",
102 "ELSE",
103 "END",
104 "ESCAPE",
105 "EXCEPT",
106 "EXCLUDE",
107 "EXCLUSIVE",
108 "EXISTS",
109 "EXPLAIN",
110 "FAIL",
111 "FILTER",
112 "FIRST",
113 "FOLLOWING",
114 "FOR",
115 "FOREIGN",
116 "FROM",
117 "FULL",
118 "GENERATED",
119 "GLOB",
120 "GROUP",
121 "GROUPS",
122 "HAVING",
123 "IF",
124 "IGNORE",
125 "IMMEDIATE",
126 "IN",
127 "INDEX",
128 "INDEXED",
129 "INITIALLY",
130 "INNER",
131 "INSERT",
132 "INSTEAD",
133 "INTERSECT",
134 "INTO",
135 "IS",
136 "ISNULL",
137 "JOIN",
138 "KEY",
139 "LAST",
140 "LEFT",
141 "LIKE",
142 "LIMIT",
143 "MATCH",
144 "NATURAL",
145 "NO",
146 "NOT",
147 "NOTHING",
148 "NOTNULL",
149 "NULL",
150 "NULLS",
151 "OF",
152 "OFFSET",
153 "ON",
154 "OR",
155 "ORDER",
156 "OTHERS",
157 "OUTER",
158 "OVER",
159 "PARTITION",
160 "PLAN",
161 "PRAGMA",
162 "PRECEDING",
163 "PRIMARY",
164 "QUERY",
165 "RAISE",
166 "RANGE",
167 "RECURSIVE",
168 "REFERENCES",
169 "REGEXP",
170 "REINDEX",
171 "RELEASE",
172 "RENAME",
173 "REPLACE",
174 "RESTRICT",
175 "RIGHT",
176 "ROLLBACK",
177 "ROW",
178 "ROWS",
179 "SAVEPOINT",
180 "SELECT",
181 "SET",
182 "TABLE",
183 "TEMP",
184 "TEMPORARY",
185 "THEN",
186 "TIES",
187 "TO",
188 "TRANSACTION",
189 "TRIGGER",
190 "UNBOUNDED",
191 "UNION",
192 "UNIQUE",
193 "UPDATE",
194 "USING",
195 "VACUUM",
196 "VALUES",
197 "VIEW",
198 "VIRTUAL",
199 "WHEN",
200 "WHERE",
201 "WINDOW",
202 "WITH",
203 "WITHOUT",
204 };
205
206 const char *sql_function_names[] = {
207 /* http://www.sqlite.org/lang_aggfunc.html */
208 "avg(",
209 "count(",
210 "group_concat(",
211 "max(",
212 "min(",
213 "sum(",
214 "total(",
215
216 /* http://www.sqlite.org/lang_corefunc.html */
217 "abs(",
218 "changes()",
219 "char(",
220 "coalesce(",
221 "glob(",
222 "ifnull(",
223 "instr(",
224 "hex(",
225 "last_insert_rowid()",
226 "length(",
227 "like(",
228 "load_extension(",
229 "lower(",
230 "ltrim(",
231 "nullif(",
232 "printf(",
233 "quote(",
234 "random()",
235 "randomblob(",
236 "replace(",
237 "round(",
238 "rtrim(",
239 "soundex(",
240 "sqlite_compileoption_get(",
241 "sqlite_compileoption_used(",
242 "sqlite_source_id()",
243 "sqlite_version()",
244 "substr(",
245 "total_changes()",
246 "trim(",
247 "typeof(",
248 "unicode(",
249 "upper(",
250 "zeroblob(",
251
252 /* http://www.sqlite.org/lang_datefunc.html */
253 "date(",
254 "time(",
255 "datetime(",
256 "julianday(",
257 "strftime(",
258
259 nullptr
260 };
261
262 multimap<std::string, help_text *> sqlite_function_help;
263
handle_db_list(void * ptr,int ncols,char ** colvalues,char ** colnames)264 static int handle_db_list(void *ptr,
265 int ncols,
266 char **colvalues,
267 char **colnames)
268 {
269 struct sqlite_metadata_callbacks *smc;
270
271 smc = (struct sqlite_metadata_callbacks *)ptr;
272
273 smc->smc_db_list[colvalues[1]] = std::vector<std::string>();
274 if (!smc->smc_database_list) {
275 return 0;
276 }
277
278 return smc->smc_database_list(ptr, ncols, colvalues, colnames);
279 }
280
281 struct table_list_data {
282 struct sqlite_metadata_callbacks *tld_callbacks;
283 db_table_map_t::iterator * tld_iter;
284 };
285
handle_table_list(void * ptr,int ncols,char ** colvalues,char ** colnames)286 static int handle_table_list(void *ptr,
287 int ncols,
288 char **colvalues,
289 char **colnames)
290 {
291 struct table_list_data *tld = (struct table_list_data *)ptr;
292
293 (*tld->tld_iter)->second.emplace_back(colvalues[0]);
294 if (!tld->tld_callbacks->smc_table_list) {
295 return 0;
296 }
297
298 return tld->tld_callbacks->smc_table_list(tld->tld_callbacks,
299 ncols,
300 colvalues,
301 colnames);
302 }
303
walk_sqlite_metadata(sqlite3 * db,struct sqlite_metadata_callbacks & smc)304 int walk_sqlite_metadata(sqlite3 *db, struct sqlite_metadata_callbacks &smc)
305 {
306 auto_mem<char, sqlite3_free> errmsg;
307 int retval;
308
309 if (smc.smc_collation_list) {
310 retval = sqlite3_exec(db,
311 "pragma collation_list",
312 smc.smc_collation_list,
313 &smc,
314 errmsg.out());
315 if (retval != SQLITE_OK) {
316 log_error("could not get collation list -- %s", errmsg.in());
317 return retval;
318 }
319 }
320
321 retval = sqlite3_exec(db,
322 "pragma database_list",
323 handle_db_list,
324 &smc,
325 errmsg.out());
326 if (retval != SQLITE_OK) {
327 log_error("could not get DB list -- %s", errmsg.in());
328 return retval;
329 }
330
331 for (auto iter = smc.smc_db_list.begin();
332 iter != smc.smc_db_list.end();
333 ++iter) {
334 struct table_list_data tld = { &smc, &iter };
335 auto_mem<char, sqlite3_free> query;
336
337 query = sqlite3_mprintf("SELECT name,sql FROM %Q.sqlite_master "
338 "WHERE type in ('table', 'view')",
339 iter->first.c_str());
340
341 retval = sqlite3_exec(db,
342 query,
343 handle_table_list,
344 &tld,
345 errmsg.out());
346 if (retval != SQLITE_OK) {
347 log_error("could not get table list -- %s", errmsg.in());
348 return retval;
349 }
350
351 for (auto table_iter = iter->second.begin();
352 table_iter != iter->second.end();
353 ++table_iter) {
354 auto_mem<char, sqlite3_free> table_query;
355 std::string &table_name = *table_iter;
356
357 table_query = sqlite3_mprintf(
358 "pragma %Q.table_xinfo(%Q)",
359 iter->first.c_str(),
360 table_name.c_str());
361 if (table_query == nullptr) {
362 return SQLITE_NOMEM;
363 }
364
365 if (smc.smc_table_info) {
366 retval = sqlite3_exec(db,
367 table_query,
368 smc.smc_table_info,
369 &smc,
370 errmsg.out());
371 if (retval != SQLITE_OK) {
372 log_error("could not get table info -- %s", errmsg.in());
373 return retval;
374 }
375 }
376
377 table_query = sqlite3_mprintf(
378 "pragma %Q.foreign_key_list(%Q)",
379 iter->first.c_str(),
380 table_name.c_str());
381 if (table_query == nullptr) {
382 return SQLITE_NOMEM;
383 }
384
385 if (smc.smc_foreign_key_list) {
386 retval = sqlite3_exec(db,
387 table_query,
388 smc.smc_foreign_key_list,
389 &smc,
390 errmsg.out());
391 if (retval != SQLITE_OK) {
392 log_error("could not get foreign key list -- %s",
393 errmsg.in());
394 return retval;
395 }
396 }
397 }
398 }
399
400 return retval;
401 }
402
schema_collation_list(void * ptr,int ncols,char ** colvalues,char ** colnames)403 static int schema_collation_list(void *ptr,
404 int ncols,
405 char **colvalues,
406 char **colnames)
407 {
408 return 0;
409 }
410
schema_db_list(void * ptr,int ncols,char ** colvalues,char ** colnames)411 static int schema_db_list(void *ptr,
412 int ncols,
413 char **colvalues,
414 char **colnames)
415 {
416 struct sqlite_metadata_callbacks *smc = (sqlite_metadata_callbacks *)ptr;
417 string &schema_out = *((string *)smc->smc_userdata);
418 auto_mem<char, sqlite3_free> attach_sql;
419
420 attach_sql = sqlite3_mprintf("ATTACH DATABASE %Q AS %Q;\n",
421 colvalues[2], colvalues[1]);
422
423 schema_out += attach_sql;
424
425 return 0;
426 }
427
schema_table_list(void * ptr,int ncols,char ** colvalues,char ** colnames)428 static int schema_table_list(void *ptr,
429 int ncols,
430 char **colvalues,
431 char **colnames)
432 {
433 struct sqlite_metadata_callbacks *smc = (sqlite_metadata_callbacks *)ptr;
434 string &schema_out = *((string *)smc->smc_userdata);
435 auto_mem<char, sqlite3_free> create_sql;
436
437 create_sql = sqlite3_mprintf("%s;\n", colvalues[1]);
438
439 schema_out += create_sql;
440
441 return 0;
442 }
443
schema_table_info(void * ptr,int ncols,char ** colvalues,char ** colnames)444 static int schema_table_info(void *ptr,
445 int ncols,
446 char **colvalues,
447 char **colnames)
448 {
449 return 0;
450 }
451
schema_foreign_key_list(void * ptr,int ncols,char ** colvalues,char ** colnames)452 static int schema_foreign_key_list(void *ptr,
453 int ncols,
454 char **colvalues,
455 char **colnames)
456 {
457 return 0;
458 }
459
dump_sqlite_schema(sqlite3 * db,std::string & schema_out)460 void dump_sqlite_schema(sqlite3 *db, std::string &schema_out)
461 {
462 struct sqlite_metadata_callbacks schema_sql_meta_callbacks = {
463 schema_collation_list,
464 schema_db_list,
465 schema_table_list,
466 schema_table_info,
467 schema_foreign_key_list,
468 &schema_out,
469 {}
470 };
471
472 walk_sqlite_metadata(db, schema_sql_meta_callbacks);
473 }
474
attach_sqlite_db(sqlite3 * db,const std::string & filename)475 void attach_sqlite_db(sqlite3 *db, const std::string &filename)
476 {
477 static const std::regex db_name_converter("[^\\w]");
478
479 auto_mem<sqlite3_stmt> stmt(sqlite3_finalize);
480
481 if (sqlite3_prepare_v2(db,
482 "ATTACH DATABASE ? as ?",
483 -1,
484 stmt.out(),
485 NULL) != SQLITE_OK) {
486 log_error("could not prepare DB attach statement -- %s",
487 sqlite3_errmsg(db));
488 return;
489 }
490
491 if (sqlite3_bind_text(stmt.in(), 1,
492 filename.c_str(), filename.length(),
493 SQLITE_TRANSIENT) != SQLITE_OK) {
494 log_error("could not bind DB attach statement -- %s",
495 sqlite3_errmsg(db));
496 return;
497 }
498
499 size_t base_start = filename.find_last_of("/\\");
500 string db_name;
501
502 if (base_start == string::npos) {
503 db_name = filename;
504 }
505 else {
506 db_name = filename.substr(base_start + 1);
507 }
508
509 db_name = std::regex_replace(db_name, db_name_converter, "_");
510
511 if (sqlite3_bind_text(stmt.in(), 2,
512 db_name.c_str(), db_name.length(),
513 SQLITE_TRANSIENT) != SQLITE_OK) {
514 log_error("could not bind DB attach statement -- %s",
515 sqlite3_errmsg(db));
516 return;
517 }
518
519 if (sqlite3_step(stmt.in()) != SQLITE_DONE) {
520 log_error("could not execute DB attach statement -- %s",
521 sqlite3_errmsg(db));
522 return;
523 }
524 }
525
sql_strftime(char * buffer,size_t buffer_size,lnav::time64_t tim,int millis,char sep)526 ssize_t sql_strftime(char *buffer, size_t buffer_size, lnav::time64_t tim, int millis,
527 char sep)
528 {
529 struct tm gmtm;
530 int year, month, index = 0;
531
532 secs2tm(tim, &gmtm);
533 year = gmtm.tm_year + 1900;
534 month = gmtm.tm_mon + 1;
535 buffer[index++] = '0' + ((year / 1000) % 10);
536 buffer[index++] = '0' + ((year / 100) % 10);
537 buffer[index++] = '0' + ((year / 10) % 10);
538 buffer[index++] = '0' + ((year / 1) % 10);
539 buffer[index++] = '-';
540 buffer[index++] = '0' + ((month / 10) % 10);
541 buffer[index++] = '0' + ((month / 1) % 10);
542 buffer[index++] = '-';
543 buffer[index++] = '0' + ((gmtm.tm_mday / 10) % 10);
544 buffer[index++] = '0' + ((gmtm.tm_mday / 1) % 10);
545 buffer[index++] = sep;
546 buffer[index++] = '0' + ((gmtm.tm_hour / 10) % 10);
547 buffer[index++] = '0' + ((gmtm.tm_hour / 1) % 10);
548 buffer[index++] = ':';
549 buffer[index++] = '0' + ((gmtm.tm_min / 10) % 10);
550 buffer[index++] = '0' + ((gmtm.tm_min / 1) % 10);
551 buffer[index++] = ':';
552 buffer[index++] = '0' + ((gmtm.tm_sec / 10) % 10);
553 buffer[index++] = '0' + ((gmtm.tm_sec / 1) % 10);
554 buffer[index++] = '.';
555 buffer[index++] = '0' + ((millis / 100) % 10);
556 buffer[index++] = '0' + ((millis / 10) % 10);
557 buffer[index++] = '0' + ((millis / 1) % 10);
558 buffer[index] = '\0';
559
560 return index;
561 }
562
sqlite_logger(void * dummy,int code,const char * msg)563 static void sqlite_logger(void *dummy, int code, const char *msg)
564 {
565 lnav_log_level_t level;
566
567 switch (code) {
568 case SQLITE_OK:
569 level = lnav_log_level_t::DEBUG;
570 break;
571 #ifdef SQLITE_NOTICE
572 case SQLITE_NOTICE:
573 level = lnav_log_level_t::INFO;
574 break;
575 #endif
576 #ifdef SQLITE_WARNING
577 case SQLITE_WARNING:
578 level = lnav_log_level_t::WARNING;
579 break;
580 #endif
581 default:
582 level = lnav_log_level_t::ERROR;
583 break;
584 }
585
586 log_msg(level, __FILE__, __LINE__, "%s", msg);
587 }
588
sql_install_logger(void)589 void sql_install_logger(void)
590 {
591 #ifdef SQLITE_CONFIG_LOG
592 sqlite3_config(SQLITE_CONFIG_LOG, sqlite_logger, NULL);
593 #endif
594 }
595
sql_ident_needs_quote(const char * ident)596 bool sql_ident_needs_quote(const char *ident)
597 {
598 for (int lpc = 0; ident[lpc]; lpc++) {
599 if (!isalnum(ident[lpc]) && ident[lpc] != '_') {
600 return true;
601 }
602 }
603
604 return false;
605 }
606
sql_quote_ident(const char * ident)607 char *sql_quote_ident(const char *ident)
608 {
609 bool needs_quote = false;
610 size_t quote_count = 0, alloc_size;
611 char *retval;
612
613 for (int lpc = 0; ident[lpc]; lpc++) {
614 if ((lpc == 0 && isdigit(ident[lpc])) ||
615 (!isalnum(ident[lpc]) && ident[lpc] != '_')) {
616 needs_quote = true;
617 }
618 if (ident[lpc] == '"') {
619 quote_count += 1;
620 }
621 }
622
623 alloc_size = strlen(ident) + quote_count * 2 + (needs_quote ? 2: 0) + 1;
624 if ((retval = (char *)sqlite3_malloc(alloc_size)) == NULL) {
625 retval = NULL;
626 }
627 else {
628 char *curr = retval;
629
630 if (needs_quote) {
631 curr[0] = '"';
632 curr += 1;
633 }
634 for (size_t lpc = 0; ident[lpc] != '\0'; lpc++) {
635 switch (ident[lpc]) {
636 case '"':
637 curr[0] = '"';
638 curr += 1;
639 default:
640 curr[0] = ident[lpc];
641 break;
642 }
643 curr += 1;
644 }
645 if (needs_quote) {
646 curr[0] = '"';
647 curr += 1;
648 }
649
650 *curr = '\0';
651 }
652
653 return retval;
654 }
655
sql_safe_ident(const string_fragment & ident)656 string sql_safe_ident(const string_fragment &ident)
657 {
658 string retval = to_string(ident);
659
660 for (size_t lpc = 0; lpc < retval.size(); lpc++) {
661 char ch = retval[lpc];
662
663 if (isalnum(ch) || ch == '_') {
664 retval[lpc] = tolower(ch);
665 } else {
666 retval[lpc] = '_';
667 }
668 }
669
670 return retval;
671 }
672
sql_compile_script(sqlite3 * db,const char * src_name,const char * script_orig,std::vector<sqlite3_stmt * > & stmts,std::vector<std::string> & errors)673 void sql_compile_script(sqlite3 *db,
674 const char *src_name,
675 const char *script_orig,
676 std::vector<sqlite3_stmt *> &stmts,
677 std::vector<std::string> &errors) {
678 const char *script = script_orig;
679
680 while (script != NULL && script[0]) {
681 auto_mem<sqlite3_stmt> stmt(sqlite3_finalize);
682 int line_number = 1;
683 const char *tail;
684 int retcode;
685
686 while (isspace(*script) && script[0]) {
687 script += 1;
688 }
689 for (const char *ch = script_orig; ch < script && ch[0]; ch++) {
690 if (*ch == '\n') {
691 line_number += 1;
692 }
693 }
694
695 retcode = sqlite3_prepare_v2(db,
696 script,
697 -1,
698 stmt.out(),
699 &tail);
700 log_debug("retcode %d %p %p", retcode, script, tail);
701 if (retcode != SQLITE_OK) {
702 const char *errmsg = sqlite3_errmsg(db);
703 auto_mem<char> full_msg;
704
705 if (asprintf(full_msg.out(), "error:%s:%d:%s", src_name,
706 line_number, errmsg) == -1) {
707 log_error("unable to allocate error message");
708 break;
709 }
710 errors.emplace_back(full_msg.in());
711 break;
712 } else if (script == tail) {
713 break;
714 } else if (stmt == NULL) {
715
716 } else {
717 stmts.push_back(stmt.release());
718 }
719
720 script = tail;
721 }
722 }
723
sql_execute_script(sqlite3 * db,const std::vector<sqlite3_stmt * > & stmts,std::vector<std::string> & errors)724 void sql_execute_script(sqlite3 *db,
725 const std::vector<sqlite3_stmt *> &stmts,
726 std::vector<std::string> &errors)
727 {
728 map<string, string> lvars;
729
730 for (sqlite3_stmt *stmt : stmts) {
731 bool done = false;
732 int param_count;
733
734 sqlite3_clear_bindings(stmt);
735
736 param_count = sqlite3_bind_parameter_count(stmt);
737 for (int lpc = 0; lpc < param_count; lpc++) {
738 const char *name;
739
740 name = sqlite3_bind_parameter_name(stmt, lpc + 1);
741 if (name[0] == '$') {
742 map<string, string>::iterator iter;
743 const char *env_value;
744
745 if ((iter = lvars.find(&name[1])) != lvars.end()) {
746 sqlite3_bind_text(stmt, lpc + 1,
747 iter->second.c_str(), -1,
748 SQLITE_TRANSIENT);
749 } else if ((env_value = getenv(&name[1])) != nullptr) {
750 sqlite3_bind_text(stmt, lpc + 1,
751 env_value, -1,
752 SQLITE_TRANSIENT);
753 } else {
754 sqlite3_bind_null(stmt, lpc + 1);
755 }
756 } else {
757 sqlite3_bind_null(stmt, lpc + 1);
758 }
759 }
760 while (!done) {
761 int retcode = sqlite3_step(stmt);
762 switch (retcode) {
763 case SQLITE_OK:
764 case SQLITE_DONE:
765 done = true;
766 break;
767
768 case SQLITE_ROW: {
769 int ncols = sqlite3_column_count(stmt);
770
771 for (int lpc = 0; lpc < ncols; lpc++) {
772 const char *name = sqlite3_column_name(stmt, lpc);
773 const char *value = (const char *)
774 sqlite3_column_text(stmt, lpc);
775
776 lvars[name] = value;
777 }
778 break;
779 }
780
781 default: {
782 const char *errmsg;
783
784 errmsg = sqlite3_errmsg(db);
785 errors.emplace_back(errmsg);
786 break;
787 }
788 }
789 }
790
791 sqlite3_reset(stmt);
792 }
793 }
794
sql_execute_script(sqlite3 * db,const char * src_name,const char * script,std::vector<std::string> & errors)795 void sql_execute_script(sqlite3 *db,
796 const char *src_name,
797 const char *script,
798 std::vector<std::string> &errors)
799 {
800 vector<sqlite3_stmt *> stmts;
801
802 sql_compile_script(db, src_name, script, stmts, errors);
803 if (errors.empty()) {
804 sql_execute_script(db, stmts, errors);
805 }
806
807 for (sqlite3_stmt *stmt : stmts) {
808 sqlite3_finalize(stmt);
809 }
810 }
811
812 static struct {
813 int sqlite_type;
814 const char *collator;
815 const char *sample;
816 } TYPE_TEST_VALUE[] = {
817 { SQLITE3_TEXT, "", "foobar" },
818 { SQLITE_INTEGER, "", "123" },
819 { SQLITE_FLOAT, "", "123.0" },
820 { SQLITE_TEXT, "ipaddress", "127.0.0.1" },
821 };
822
guess_type_from_pcre(const string & pattern,std::string & collator)823 int guess_type_from_pcre(const string &pattern, std::string &collator)
824 {
825 try {
826 pcrepp re(pattern);
827 vector<int> matches;
828 int retval = SQLITE3_TEXT;
829 int index = 0;
830
831 collator.clear();
832 for (const auto& test_value : TYPE_TEST_VALUE) {
833 pcre_context_static<30> pc;
834 pcre_input pi(test_value.sample);
835
836 if (re.match(pc, pi, PCRE_ANCHORED) &&
837 pc[0]->c_begin == 0 && pc[0]->length() == (int) pi.pi_length) {
838 matches.push_back(index);
839 }
840
841 index += 1;
842 }
843
844 if (matches.size() == 1) {
845 retval = TYPE_TEST_VALUE[matches.front()].sqlite_type;
846 collator = TYPE_TEST_VALUE[matches.front()].collator;
847 }
848
849 return retval;
850 } catch (pcrepp::error &e) {
851 return SQLITE3_TEXT;
852 }
853 }
854
855 /* XXX figure out how to do this with the template */
sqlite_close_wrapper(void * mem)856 void sqlite_close_wrapper(void *mem)
857 {
858 sqlite3_close((sqlite3 *)mem);
859 }
860
sqlite_authorizer(void * pUserData,int action_code,const char * detail1,const char * detail2,const char * detail3,const char * detail4)861 int sqlite_authorizer(void *pUserData, int action_code, const char *detail1,
862 const char *detail2, const char *detail3,
863 const char *detail4)
864 {
865 if (action_code == SQLITE_ATTACH)
866 {
867 return SQLITE_DENY;
868 }
869 return SQLITE_OK;
870 }
871
sql_keyword_re(void)872 string sql_keyword_re(void)
873 {
874 string retval = "(?:";
875 bool first = true;
876
877 for (const char *kw : sql_keywords) {
878 if (!first) {
879 retval.append("|");
880 } else {
881 first = false;
882 }
883 retval.append("\\b");
884 retval.append(kw);
885 retval.append("\\b");
886 }
887 retval += ")";
888
889 return retval;
890 }
891
892 string_attr_type SQL_COMMAND_ATTR("sql_command");
893 string_attr_type SQL_KEYWORD_ATTR("sql_keyword");
894 string_attr_type SQL_IDENTIFIER_ATTR("sql_ident");
895 string_attr_type SQL_FUNCTION_ATTR("sql_func");
896 string_attr_type SQL_STRING_ATTR("sql_string");
897 string_attr_type SQL_OPERATOR_ATTR("sql_oper");
898 string_attr_type SQL_PAREN_ATTR("sql_paren");
899 string_attr_type SQL_COMMA_ATTR("sql_comma");
900 string_attr_type SQL_GARBAGE_ATTR("sql_garbage");
901
annotate_sql_statement(attr_line_t & al)902 void annotate_sql_statement(attr_line_t &al)
903 {
904 static string keyword_re_str = R"(\A)" + sql_keyword_re();
905
906 static struct {
907 pcrepp re;
908 string_attr_type_t type;
909 } PATTERNS[] = {
910 { pcrepp{R"(^(\.\w+))"}, &SQL_COMMAND_ATTR },
911 { pcrepp{R"(\A,)"}, &SQL_COMMA_ATTR },
912 { pcrepp{R"(\A\(|\A\))"}, &SQL_PAREN_ATTR },
913 { pcrepp{keyword_re_str, PCRE_CASELESS}, &SQL_KEYWORD_ATTR },
914 { pcrepp{R"(\A'[^']*('(?:'[^']*')*|$))"}, &SQL_STRING_ATTR },
915 { pcrepp{R"(\A(\$?\b[a-z_]\w*)|\"([^\"]+)\"|\[([^\]]+)])", PCRE_CASELESS}, &SQL_IDENTIFIER_ATTR },
916 { pcrepp{R"(\A(\*|<|>|=|!|\-|\+|\|\|))"}, &SQL_OPERATOR_ATTR },
917 { pcrepp{R"(\A.)"}, &SQL_GARBAGE_ATTR },
918 };
919
920 static pcrepp ws_pattern(R"(\A\s+)");
921
922 pcre_context_static<30> pc;
923 pcre_input pi(al.get_string());
924 auto &line = al.get_string();
925 auto &sa = al.get_attrs();
926
927 while (pi.pi_next_offset < line.length()) {
928 if (ws_pattern.match(pc, pi, PCRE_ANCHORED)) {
929 continue;
930 }
931 for (const auto &pat : PATTERNS) {
932 if (pat.re.match(pc, pi, PCRE_ANCHORED)) {
933 pcre_context::capture_t *cap = pc.all();
934 struct line_range lr(cap->c_begin, cap->c_end);
935
936 sa.emplace_back(lr, pat.type);
937 break;
938 }
939 }
940 }
941
942 string_attrs_t::const_iterator iter;
943 int start = 0;
944
945 while ((iter = find_string_attr(sa, &SQL_IDENTIFIER_ATTR, start)) != sa.end()) {
946 string_attrs_t::const_iterator piter;
947 bool found_open = false;
948 ssize_t lpc;
949
950 for (lpc = iter->sa_range.lr_end; lpc < (int)line.length(); lpc++) {
951 if (line[lpc] == '(') {
952 found_open = true;
953 break;
954 } else if (!isspace(line[lpc])) {
955 break;
956 }
957 }
958
959 if (found_open) {
960 ssize_t pstart = lpc + 1;
961 int depth = 1;
962
963 while (depth > 0 &&
964 (piter = find_string_attr(sa, &SQL_PAREN_ATTR, pstart)) != sa.end()) {
965 if (line[piter->sa_range.lr_start] == '(') {
966 depth += 1;
967 } else {
968 depth -= 1;
969 }
970 pstart = piter->sa_range.lr_end;
971 }
972
973 line_range func_range{iter->sa_range.lr_start};
974 if (piter == sa.end()) {
975 func_range.lr_end = line.length();
976 } else {
977 func_range.lr_end = piter->sa_range.lr_end - 1;
978 }
979 sa.emplace_back(func_range, &SQL_FUNCTION_ATTR);
980 }
981
982 start = iter->sa_range.lr_end;
983 }
984
985 remove_string_attr(sa, &SQL_PAREN_ATTR);
986 stable_sort(sa.begin(), sa.end());
987 }
988
find_sql_help_for_line(const attr_line_t & al,size_t x)989 vector<const help_text *> find_sql_help_for_line(const attr_line_t &al, size_t x)
990 {
991 vector<const help_text *> retval;
992 const auto& sa = al.get_attrs();
993 string name;
994
995 x = al.nearest_text(x);
996
997 {
998 auto sa_opt = get_string_attr(al.get_attrs(), &SQL_COMMAND_ATTR);
999
1000 if (sa_opt) {
1001 auto sql_cmd_map = injector::get<
1002 readline_context::command_map_t*, sql_cmd_map_tag>();
1003 auto cmd_name = al.get_substring((*sa_opt)->sa_range);
1004 auto cmd_iter = sql_cmd_map->find(cmd_name);
1005
1006 if (cmd_iter != sql_cmd_map->end()) {
1007 return {&cmd_iter->second->c_help};
1008 }
1009 }
1010 }
1011
1012 vector<string> kw;
1013 auto iter = rfind_string_attr_if(sa, x, [&al, &name, &kw, x](auto sa) {
1014
1015 if (sa.sa_type != &SQL_FUNCTION_ATTR &&
1016 sa.sa_type != &SQL_KEYWORD_ATTR) {
1017 return false;
1018 }
1019
1020 const string &str = al.get_string();
1021 const line_range &lr = sa.sa_range;
1022 int lpc;
1023
1024 if (sa.sa_type == &SQL_FUNCTION_ATTR) {
1025 if (!sa.sa_range.contains(x)) {
1026 return false;
1027 }
1028 }
1029
1030 for (lpc = lr.lr_start; lpc < lr.lr_end; lpc++) {
1031 if (!isalnum(str[lpc]) && str[lpc] != '_') {
1032 break;
1033 }
1034 }
1035
1036 string tmp_name = str.substr(lr.lr_start, lpc - lr.lr_start);
1037 if (sa.sa_type == &SQL_KEYWORD_ATTR) {
1038 tmp_name = toupper(tmp_name);
1039 }
1040 bool retval = sqlite_function_help.count(tmp_name) > 0;
1041
1042 if (retval) {
1043 kw.push_back(tmp_name);
1044 name = tmp_name;
1045 }
1046 return retval;
1047 });
1048
1049 if (iter != sa.end()) {
1050 auto func_pair = sqlite_function_help.equal_range(name);
1051 size_t help_count = distance(func_pair.first, func_pair.second);
1052
1053 if (help_count > 1 && name != func_pair.first->second->ht_name) {
1054 while (func_pair.first != func_pair.second) {
1055 if (find(kw.begin(), kw.end(),
1056 func_pair.first->second->ht_name) == kw.end()) {
1057 ++func_pair.first;
1058 } else {
1059 func_pair.second = next(func_pair.first);
1060 break;
1061 }
1062 }
1063 }
1064 for (auto func_iter = func_pair.first;
1065 func_iter != func_pair.second;
1066 ++func_iter) {
1067 retval.emplace_back(func_iter->second);
1068 }
1069 }
1070
1071 return retval;
1072 }
1073