Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | In the CLI, avoid unnecessary identifier quoting in the ".dump" output. Also add new ".dump" test cases. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
de65f907610a59e64cbf2214789c11f7 |
User & Date: | drh 2017-03-08 12:25:18.771 |
Context
2017-03-08
| ||
13:50 | Add the shathree.c extension for implementing SHA3() and SHA3_QUERY() SQL functions. (check-in: f7ca9193dd user: drh tags: trunk) | |
12:25 | In the CLI, avoid unnecessary identifier quoting in the ".dump" output. Also add new ".dump" test cases. (check-in: de65f90761 user: drh tags: trunk) | |
11:44 | Add the --preserve-rowids option to the ".dump" command in the CLI. (check-in: c60aee2471 user: drh tags: trunk) | |
Changes
Changes to src/shell.c.
︙ | ︙ | |||
551 552 553 554 555 556 557 558 559 560 561 562 563 564 | free(zPrior); zResult = shell_readline(zPrompt); if( zResult && *zResult ) shell_add_history(zResult); #endif } return zResult; } #if defined(SQLITE_ENABLE_SESSION) /* ** State information for a single open session */ typedef struct OpenSession OpenSession; struct OpenSession { | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 | free(zPrior); zResult = shell_readline(zPrompt); if( zResult && *zResult ) shell_add_history(zResult); #endif } return zResult; } /* ** A variable length string to which one can append text. */ typedef struct ShellText ShellText; struct ShellText { char *z; int n; int nAlloc; }; /* ** Initialize and destroy a ShellText object */ static void initText(ShellText *p){ memset(p, 0, sizeof(*p)); } static void freeText(ShellText *p){ free(p->z); initText(p); } /* zIn is either a pointer to a NULL-terminated string in memory obtained ** from malloc(), or a NULL pointer. The string pointed to by zAppend is ** added to zIn, and the result returned in memory obtained from malloc(). ** zIn, if it was not NULL, is freed. ** ** If the third argument, quote, is not '\0', then it is used as a ** quote character for zAppend. */ static void appendText(ShellText *p, char const *zAppend, char quote){ int len; int i; int nAppend = strlen30(zAppend); len = nAppend+p->n+1; if( quote ){ len += 2; for(i=0; i<nAppend; i++){ if( zAppend[i]==quote ) len++; } } if( p->n+len>=p->nAlloc ){ p->nAlloc = p->nAlloc*2 + len + 20; p->z = realloc(p->z, p->nAlloc); if( p->z==0 ){ memset(p, 0, sizeof(*p)); return; } } if( quote ){ char *zCsr = p->z+p->n; *zCsr++ = quote; for(i=0; i<nAppend; i++){ *zCsr++ = zAppend[i]; if( zAppend[i]==quote ) *zCsr++ = quote; } *zCsr++ = quote; p->n = (int)(zCsr - p->z); *zCsr = '\0'; }else{ memcpy(p->z+p->n, zAppend, nAppend); p->n += nAppend; p->z[p->n] = '\0'; } } /* ** Attempt to determine if identifier zName needs to be quoted, either ** because it contains non-alphanumeric characters, or because it is an ** SQLite keyword. Be conservative in this estimate: When in doubt assume ** that quoting is required. ** ** Return '"' if quoting is required. Return 0 if no quoting is required. */ static char quoteChar(const char *zName){ /* All SQLite keywords, in alphabetical order */ static const char *azKeywords[] = { "ABORT", "ACTION", "ADD", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC", "ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY", "CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT", "CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT", "DEFERRABLE", "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT", "DROP", "EACH", "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE", "EXISTS", "EXPLAIN", "FAIL", "FOR", "FOREIGN", "FROM", "FULL", "GLOB", "GROUP", "HAVING", "IF", "IGNORE", "IMMEDIATE", "IN", "INDEX", "INDEXED", "INITIALLY", "INNER", "INSERT", "INSTEAD", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY", "LEFT", "LIKE", "LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL", "NULL", "OF", "OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA", "PRIMARY", "QUERY", "RAISE", "RECURSIVE", "REFERENCES", "REGEXP", "REINDEX", "RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT", "ROLLBACK", "ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP", "TEMPORARY", "THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE", "UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN", "WHERE", "WITH", "WITHOUT", }; int i, lwr, upr, mid, c; if( !isalpha((unsigned char)zName[0]) && zName[0]!='_' ) return '"'; for(i=0; zName[i]; i++){ if( !isalnum((unsigned char)zName[i]) && zName[i]!='_' ) return '"'; } lwr = 0; upr = sizeof(azKeywords)/sizeof(azKeywords[0]) - 1; while( lwr<=upr ){ mid = (lwr+upr)/2; c = sqlite3_stricmp(azKeywords[mid], zName); if( c==0 ) return '"'; if( c<0 ){ lwr = mid+1; }else{ upr = mid-1; } } return 0; } #if defined(SQLITE_ENABLE_SESSION) /* ** State information for a single open session */ typedef struct OpenSession OpenSession; struct OpenSession { |
︙ | ︙ | |||
1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 | ** This is the callback routine that the SQLite library ** invokes for each row of a query result. */ static int callback(void *pArg, int nArg, char **azArg, char **azCol){ /* since we don't have type info, call the shell_callback with a NULL value */ return shell_callback(pArg, nArg, azArg, azCol, NULL); } /* ** Set the destination table field of the ShellState structure to ** the name of the table given. Escape any quote characters in the ** table name. */ static void set_table_name(ShellState *p, const char *zName){ int i, n; | > | < < < | | < < | | | | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 | ** This is the callback routine that the SQLite library ** invokes for each row of a query result. */ static int callback(void *pArg, int nArg, char **azArg, char **azCol){ /* since we don't have type info, call the shell_callback with a NULL value */ return shell_callback(pArg, nArg, azArg, azCol, NULL); } /* ** Set the destination table field of the ShellState structure to ** the name of the table given. Escape any quote characters in the ** table name. */ static void set_table_name(ShellState *p, const char *zName){ int i, n; int cQuote; char *z; if( p->zDestTable ){ free(p->zDestTable); p->zDestTable = 0; } if( zName==0 ) return; cQuote = quoteChar(zName); n = strlen30(zName); if( cQuote ) n += 2; z = p->zDestTable = malloc( n+1 ); if( z==0 ){ raw_printf(stderr,"Error: out of memory\n"); exit(1); } n = 0; if( cQuote ) z[n++] = cQuote; for(i=0; zName[i]; i++){ z[n++] = zName[i]; if( zName[i]==cQuote ) z[n++] = cQuote; } if( cQuote ) z[n++] = cQuote; z[n] = 0; } /* ** Execute a query statement that will generate SQL output. Print ** the result columns, comma-separated, on a line and then add a ** semicolon terminator to the end of that line. |
︙ | ︙ | |||
2127 2128 2129 2130 2131 2132 2133 | break; } } } return azCol; } | < < < | 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 | break; } } } return azCol; } /* ** This is a different callback routine used for dumping the database. ** Each row received by this callback consists of a table name, ** the table type ("index" or "table") and SQL to create the table. ** This routine should print text sufficient to recreate the table. */ static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){ |
︙ | ︙ | |||
2173 2174 2175 2176 2177 2178 2179 | sqlite3_free(zIns); return 0; }else{ printSchemaLine(p->out, zSql, ";\n"); } if( strcmp(zType, "table")==0 ){ | | | | | | | | | | | | 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 | sqlite3_free(zIns); return 0; }else{ printSchemaLine(p->out, zSql, ";\n"); } if( strcmp(zType, "table")==0 ){ ShellText sSelect; ShellText sTable; char **azCol; int i; char *savedDestTable; int savedMode; azCol = tableColumnList(p, zTable); if( azCol==0 ){ p->nErr++; return 0; } /* Always quote the table name, even if it appears to be pure ascii, ** in case it is a keyword. Ex: INSERT INTO "table" ... */ initText(&sTable); appendText(&sTable, zTable, quoteChar(zTable)); /* If preserving the rowid, add a column list after the table name. ** In other words: "INSERT INTO tab(rowid,a,b,c,...) VALUES(...)" ** instead of the usual "INSERT INTO tab VALUES(...)". */ if( azCol[0] ){ appendText(&sTable, "(", 0); appendText(&sTable, azCol[0], 0); for(i=1; azCol[i]; i++){ appendText(&sTable, ",", 0); appendText(&sTable, azCol[i], quoteChar(azCol[i])); } appendText(&sTable, ")", 0); } /* Build an appropriate SELECT statement */ initText(&sSelect); appendText(&sSelect, "SELECT ", 0); if( azCol[0] ){ appendText(&sSelect, azCol[0], 0); appendText(&sSelect, ",", 0); } for(i=1; azCol[i]; i++){ appendText(&sSelect, azCol[i], quoteChar(azCol[i])); if( azCol[i+1] ){ appendText(&sSelect, ",", 0); } } freeColumnList(azCol); appendText(&sSelect, " FROM ", 0); appendText(&sSelect, zTable, quoteChar(zTable)); savedDestTable = p->zDestTable; savedMode = p->mode; p->zDestTable = sTable.z; p->mode = p->cMode = MODE_Insert; rc = shell_exec(p->db, sSelect.z, shell_callback, p, 0); p->zDestTable = savedDestTable; p->mode = savedMode; freeText(&sTable); freeText(&sSelect); if( rc ) p->nErr++; } return 0; } /* ** Run zQuery. Use dump_callback() as the callback routine so that |
︙ | ︙ |
Changes to test/shell1.test.
︙ | ︙ | |||
736 737 738 739 740 741 742 | INSERT INTO t3 VALUES(1,null), (2,''), (3,1), (4,2.25), (5,'hello'), (6,x'807f'); } catchcmd test.db {.dump} } {0 {PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t1(x); | | | | | | | | | | | | | > > | | | | | | | | | | | | > > > | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 | INSERT INTO t3 VALUES(1,null), (2,''), (3,1), (4,2.25), (5,'hello'), (6,x'807f'); } catchcmd test.db {.dump} } {0 {PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t1(x); INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES(''); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2.25); INSERT INTO t1 VALUES('hello'); INSERT INTO t1 VALUES(X'807f'); CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(1,NULL); INSERT INTO t3 VALUES(2,''); INSERT INTO t3 VALUES(3,1); INSERT INTO t3 VALUES(4,2.25); INSERT INTO t3 VALUES(5,'hello'); INSERT INTO t3 VALUES(6,X'807f'); COMMIT;}} # The --preserve-rowids option to .dump # do_test shell1-4.1.1 { catchcmd test.db {.dump --preserve-rowids} } {0 {PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t1(x); INSERT INTO t1(rowid,x) VALUES(1,NULL); INSERT INTO t1(rowid,x) VALUES(2,''); INSERT INTO t1(rowid,x) VALUES(3,1); INSERT INTO t1(rowid,x) VALUES(4,2.25); INSERT INTO t1(rowid,x) VALUES(5,'hello'); INSERT INTO t1(rowid,x) VALUES(6,X'807f'); CREATE TABLE t3(x,y); INSERT INTO t3(rowid,x,y) VALUES(1,1,NULL); INSERT INTO t3(rowid,x,y) VALUES(2,2,''); INSERT INTO t3(rowid,x,y) VALUES(3,3,1); INSERT INTO t3(rowid,x,y) VALUES(4,4,2.25); INSERT INTO t3(rowid,x,y) VALUES(5,5,'hello'); INSERT INTO t3(rowid,x,y) VALUES(6,6,X'807f'); COMMIT;}} # If the table contains an INTEGER PRIMARY KEY, do not record a separate # rowid column in the output. # do_test shell1-4.1.2 { db close forcedelete test2.db sqlite3 db test2.db db eval { CREATE TABLE t1(x INTEGER PRIMARY KEY, y); INSERT INTO t1 VALUES(1,null), (2,''), (3,1), (4,2.25), (5,'hello'), (6,x'807f'); } catchcmd test2.db {.dump --preserve-rowids} } {0 {PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t1(x INTEGER PRIMARY KEY, y); INSERT INTO t1 VALUES(1,NULL); INSERT INTO t1 VALUES(2,''); INSERT INTO t1 VALUES(3,1); INSERT INTO t1 VALUES(4,2.25); INSERT INTO t1 VALUES(5,'hello'); INSERT INTO t1 VALUES(6,X'807f'); COMMIT;}} # Verify that the table named [table] is correctly quoted and that # an INTEGER PRIMARY KEY DESC is not an alias for the rowid. # do_test shell1-4.1.3 { db close forcedelete test2.db sqlite3 db test2.db db eval { CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y); INSERT INTO [table] VALUES(1,null), (12,''), (23,1), (34,2.25), (45,'hello'), (56,x'807f'); } catchcmd test2.db {.dump --preserve-rowids} } {0 {PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE [table](x INTEGER PRIMARY KEY DESC, y); INSERT INTO "table"(rowid,x,y) VALUES(1,1,NULL); INSERT INTO "table"(rowid,x,y) VALUES(2,12,''); INSERT INTO "table"(rowid,x,y) VALUES(3,23,1); INSERT INTO "table"(rowid,x,y) VALUES(4,34,2.25); INSERT INTO "table"(rowid,x,y) VALUES(5,45,'hello'); INSERT INTO "table"(rowid,x,y) VALUES(6,56,X'807f'); COMMIT;}} # Do not record rowids for a WITHOUT ROWID table. Also check correct quoting # of table names that contain odd characters. # do_test shell1-4.1.4 { db close forcedelete test2.db sqlite3 db test2.db db eval { CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID; INSERT INTO [ta<>ble] VALUES(1,null), (12,''), (23,1), (34,2.25), (45,'hello'), (56,x'807f'); } catchcmd test2.db {.dump --preserve-rowids} } {0 {PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE [ta<>ble](x INTEGER PRIMARY KEY, y) WITHOUT ROWID; INSERT INTO "ta<>ble" VALUES(1,NULL); INSERT INTO "ta<>ble" VALUES(12,''); INSERT INTO "ta<>ble" VALUES(23,1); INSERT INTO "ta<>ble" VALUES(34,2.25); INSERT INTO "ta<>ble" VALUES(45,'hello'); INSERT INTO "ta<>ble" VALUES(56,X'807f'); COMMIT;}} # Do not record rowids if the rowid is inaccessible # do_test shell1-4.1.5 { db close forcedelete test2.db sqlite3 db test2.db db eval { CREATE TABLE t1(_ROWID_,rowid,oid); INSERT INTO t1 VALUES(1,null,'alpha'), (12,'',99), (23,1,x'b0b1b2'); } catchcmd test2.db {.dump --preserve-rowids} } {0 {PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t1(_ROWID_,rowid,oid); INSERT INTO t1 VALUES(1,NULL,'alpha'); INSERT INTO t1 VALUES(12,'',99); INSERT INTO t1 VALUES(23,1,X'b0b1b2'); COMMIT;}} # Test the output of ".mode insert" # do_test shell1-4.2.1 { catchcmd test.db ".mode insert t1\nselect * from t1;" } {0 {INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES(''); |
︙ | ︙ |