/ Check-in [23fa7c57]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add support for the ".excel" command (and ".once -e" and ".once -x") in the CLI.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | excel-shell-cmd
Files: files | file ages | folders
SHA3-256:23fa7c57c2b204d1ddcc2a939b5271628cf26689ad4ede6976038113095a9801
User & Date: drh 2018-01-10 21:41:55
Context
2018-01-10
21:50
Fix a potential SQLITE_MISUSE in the .excel command when no database is open. check-in: 9b95ff1a user: drh tags: excel-shell-cmd
21:41
Add support for the ".excel" command (and ".once -e" and ".once -x") in the CLI. check-in: 23fa7c57 user: drh tags: excel-shell-cmd
19:50
Fix a harmless compiler warning in zipfile.c check-in: 60c694c1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

  1008   1008   struct ShellState {
  1009   1009     sqlite3 *db;           /* The database */
  1010   1010     u8 autoExplain;        /* Automatically turn on .explain mode */
  1011   1011     u8 autoEQP;            /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */
  1012   1012     u8 statsOn;            /* True to display memory stats before each finalize */
  1013   1013     u8 scanstatsOn;        /* True to display scan stats before each finalize */
  1014   1014     u8 openMode;           /* SHELL_OPEN_NORMAL, _APPENDVFS, or _ZIPFILE */
         1015  +  u8 doXdgOpen;          /* Invoke start/open/xdg-open in output_reset() */
  1015   1016     int outCount;          /* Revert to stdout when reaching zero */
  1016   1017     int cnt;               /* Number of records displayed so far */
  1017   1018     FILE *out;             /* Write results here */
  1018   1019     FILE *traceOut;        /* Output for sqlite3_trace() */
  1019   1020     int nErr;              /* Number of errors seen */
  1020   1021     int mode;              /* An output mode setting */
  1021   1022     int cMode;             /* temporary output mode for the current query */
  1022   1023     int normalMode;        /* Output mode before ".explain on" */
  1023   1024     int writableSchema;    /* True if PRAGMA writable_schema=ON */
  1024   1025     int showHeader;        /* True to show column names in List or Column mode */
  1025   1026     int nCheck;            /* Number of ".check" commands run */
  1026   1027     unsigned shellFlgs;    /* Various flags */
  1027   1028     char *zDestTable;      /* Name of destination table when MODE_Insert */
         1029  +  char *zTempFile;       /* Temporary file that might need deleting */
  1028   1030     char zTestcase[30];    /* Name of current test case */
  1029   1031     char colSeparator[20]; /* Column separator character for several modes */
  1030   1032     char rowSeparator[20]; /* Row separator character for MODE_Ascii */
  1031   1033     int colWidth[100];     /* Requested width of each column when in column mode*/
  1032   1034     int actualWidth[100];  /* Actual width of each column */
  1033   1035     char nullValue[20];    /* The text to print when a NULL comes back from
  1034   1036                            ** the database */
................................................................................
  3060   3062     ".databases             List names and files of attached databases\n"
  3061   3063     ".dbinfo ?DB?           Show status information about the database\n"
  3062   3064     ".dump ?TABLE? ...      Dump the database in an SQL text format\n"
  3063   3065     "                         If TABLE specified, only dump tables matching\n"
  3064   3066     "                         LIKE pattern TABLE.\n"
  3065   3067     ".echo on|off           Turn command echo on or off\n"
  3066   3068     ".eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN\n"
         3069  +  ".excel                 Display the output of next command in a spreadsheet\n"
  3067   3070     ".exit                  Exit this program\n"
  3068   3071     ".expert                EXPERIMENTAL. Suggest indexes for specified queries\n"
  3069   3072   /* Because explain mode comes on automatically now, the ".explain" mode
  3070   3073   ** is removed from the help screen.  It is still supported for legacy, however */
  3071   3074   /*".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n"*/
  3072   3075     ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n"
  3073   3076     ".headers on|off        Turn display of headers on or off\n"
................................................................................
  3922   3925       sqlite3_exec(newDb, "COMMIT;", 0, 0, 0);
  3923   3926       sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
  3924   3927     }
  3925   3928     sqlite3_close(newDb);
  3926   3929   }
  3927   3930   
  3928   3931   /*
  3929         -** Change the output file back to stdout
         3932  +** Change the output file back to stdout.
         3933  +**
         3934  +** If the p->doXdgOpen flag is set, that means the output was being
         3935  +** redirected to a temporary file named by p->zTempFile.  In that case,
         3936  +** launch start/open/xdg-open on that temporary file.
  3930   3937   */
  3931   3938   static void output_reset(ShellState *p){
  3932   3939     if( p->outfile[0]=='|' ){
  3933   3940   #ifndef SQLITE_OMIT_POPEN
  3934   3941       pclose(p->out);
  3935   3942   #endif
  3936   3943     }else{
  3937   3944       output_file_close(p->out);
         3945  +    if( p->doXdgOpen ){
         3946  +      const char *zXdgOpenCmd =
         3947  +#if defined(_WIN32)
         3948  +      "start";
         3949  +#elif defined(__APPLE__)
         3950  +      "open";
         3951  +#else
         3952  +      "xdg-open";
         3953  +#endif
         3954  +      char *zCmd;
         3955  +      zCmd = sqlite3_mprintf("%s %s", zXdgOpenCmd, p->zTempFile);
         3956  +      system(zCmd);
         3957  +      sqlite3_free(zCmd);
         3958  +      p->mode = p->doXdgOpen - 1;
         3959  +      p->doXdgOpen = 0;
         3960  +    }
  3938   3961     }
  3939   3962     p->outfile[0] = 0;
  3940   3963     p->out = stdout;
  3941   3964   }
  3942   3965   
  3943   3966   /*
  3944   3967   ** Run an SQL command and return the single integer result.
................................................................................
  4188   4211     rc = _wunlink(z);
  4189   4212     sqlite3_free(z);
  4190   4213   #else
  4191   4214     rc = unlink(zFilename);
  4192   4215   #endif
  4193   4216     return rc;
  4194   4217   }
         4218  +
         4219  +/*
         4220  +** Try to delete the temporary file (if there is one) and free the
         4221  +** memory used to hold the name of the temp file.
         4222  +*/
         4223  +static void clearTempFile(ShellState *p){
         4224  +  if( p->zTempFile==0 ) return;
         4225  +  if( shellDeleteFile(p->zTempFile) ) return;
         4226  +  sqlite3_free(p->zTempFile);
         4227  +  p->zTempFile = 0;
         4228  +}
         4229  +
         4230  +/*
         4231  +** Create a new temp file name with the given suffix.
         4232  +*/
         4233  +static void newTempFile(ShellState *p, const char *zSuffix){
         4234  +  clearTempFile(p);
         4235  +  sqlite3_free(p->zTempFile);
         4236  +  p->zTempFile = 0;
         4237  +  sqlite3_file_control(p->db, 0, SQLITE_FCNTL_TEMPFILENAME, &p->zTempFile);
         4238  +  if( p->zTempFile==0 ){
         4239  +    sqlite3_uint64 r;
         4240  +    sqlite3_randomness(sizeof(r), &r);
         4241  +    p->zTempFile = sqlite3_mprintf("temp%llx.%s", r, zSuffix);
         4242  +  }else{
         4243  +    p->zTempFile = sqlite3_mprintf("%z.%s", p->zTempFile, zSuffix);
         4244  +  }
         4245  +  if( p->zTempFile==0 ){
         4246  +    raw_printf(stderr, "out of memory\n");
         4247  +    exit(1);
         4248  +  }
         4249  +}
  4195   4250   
  4196   4251   
  4197   4252   /*
  4198   4253   ** The implementation of SQL scalar function fkey_collate_clause(), used
  4199   4254   ** by the ".lint fkey-indexes" command. This scalar function is always
  4200   4255   ** called with four arguments - the parent table name, the parent column name,
  4201   4256   ** the child table name and the child column name.
................................................................................
  5201   5256     }
  5202   5257   
  5203   5258     /* Process the input line.
  5204   5259     */
  5205   5260     if( nArg==0 ) return 0; /* no tokens, no error */
  5206   5261     n = strlen30(azArg[0]);
  5207   5262     c = azArg[0][0];
         5263  +  clearTempFile(p);
  5208   5264   
  5209   5265   #ifndef SQLITE_OMIT_AUTHORIZATION
  5210   5266     if( c=='a' && strncmp(azArg[0], "auth", n)==0 ){
  5211   5267       if( nArg!=2 ){
  5212   5268         raw_printf(stderr, "Usage: .auth ON|OFF\n");
  5213   5269         rc = 1;
  5214   5270         goto meta_command_exit;
................................................................................
  6105   6161       if( p->db==0 ){
  6106   6162         /* As a fall-back open a TEMP database */
  6107   6163         p->zDbFilename = 0;
  6108   6164         open_db(p, 0);
  6109   6165       }
  6110   6166     }else
  6111   6167   
  6112         -  if( c=='o'
  6113         -   && (strncmp(azArg[0], "output", n)==0 || strncmp(azArg[0], "once", n)==0)
         6168  +  if( (c=='o'
         6169  +        && (strncmp(azArg[0], "output", n)==0||strncmp(azArg[0], "once", n)==0))
         6170  +   || (c=='e' && n==5 && strcmp(azArg[0],"excel")==0)
  6114   6171     ){
  6115   6172       const char *zFile = nArg>=2 ? azArg[1] : "stdout";
         6173  +    if( azArg[0][0]=='e' ){
         6174  +      /* Transform the ".excel" command into ".once -x" */
         6175  +      nArg = 2;
         6176  +      azArg[0] = "once";
         6177  +      zFile = azArg[1] = "-x";
         6178  +      n = 4;
         6179  +    }
  6116   6180       if( nArg>2 ){
  6117         -      utf8_printf(stderr, "Usage: .%s FILE\n", azArg[0]);
         6181  +      utf8_printf(stderr, "Usage: .%s [-e|-x|FILE]\n", azArg[0]);
  6118   6182         rc = 1;
  6119   6183         goto meta_command_exit;
  6120   6184       }
  6121   6185       if( n>1 && strncmp(azArg[0], "once", n)==0 ){
  6122   6186         if( nArg<2 ){
  6123         -        raw_printf(stderr, "Usage: .once FILE\n");
         6187  +        raw_printf(stderr, "Usage: .once (-e|-x|FILE)\n");
  6124   6188           rc = 1;
  6125   6189           goto meta_command_exit;
  6126   6190         }
  6127   6191         p->outCount = 2;
  6128   6192       }else{
  6129   6193         p->outCount = 0;
  6130   6194       }
  6131   6195       output_reset(p);
         6196  +    if( zFile[0]=='-' && zFile[1]=='-' ) zFile++;
         6197  +    if( strcmp(zFile, "-e")==0 || strcmp(zFile, "-x")==0 ){
         6198  +      p->doXdgOpen = p->mode + 1;
         6199  +      if( zFile[1]=='x' ){
         6200  +        newTempFile(p, "csv");
         6201  +        p->mode = MODE_Csv;
         6202  +        sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Comma);
         6203  +        sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_CrLf);
         6204  +      }else{
         6205  +        newTempFile(p, "txt");
         6206  +      }
         6207  +      zFile = p->zTempFile;
         6208  +    }
  6132   6209       if( zFile[0]=='|' ){
  6133   6210   #ifdef SQLITE_OMIT_POPEN
  6134   6211         raw_printf(stderr, "Error: pipes are not supported in this OS\n");
  6135   6212         rc = 1;
  6136   6213         p->out = stdout;
  6137   6214   #else
  6138   6215         p->out = popen(zFile + 1, "w");
................................................................................
  7554   7631       if( nSql && line_contains_semicolon(&zSql[nSqlPrior], nSql-nSqlPrior)
  7555   7632                   && sqlite3_complete(zSql) ){
  7556   7633         errCnt += runOneSqlLine(p, zSql, in, startline);
  7557   7634         nSql = 0;
  7558   7635         if( p->outCount ){
  7559   7636           output_reset(p);
  7560   7637           p->outCount = 0;
         7638  +      }else{
         7639  +        clearTempFile(p);
  7561   7640         }
  7562   7641       }else if( nSql && _all_whitespace(zSql) ){
  7563   7642         if( ShellHasFlag(p, SHFLG_Echo) ) printf("%s\n", zSql);
  7564   7643         nSql = 0;
  7565   7644       }
  7566   7645     }
  7567   7646     if( nSql && !_all_whitespace(zSql) ){
................................................................................
  8176   8255     set_table_name(&data, 0);
  8177   8256     if( data.db ){
  8178   8257       session_close_all(&data);
  8179   8258       sqlite3_close(data.db);
  8180   8259     }
  8181   8260     sqlite3_free(data.zFreeOnClose);
  8182   8261     find_home_dir(1);
         8262  +  clearTempFile(&data);
  8183   8263   #if !SQLITE_SHELL_IS_UTF8
  8184   8264     for(i=0; i<argc; i++) sqlite3_free(argv[i]);
  8185   8265     sqlite3_free(argv);
  8186   8266   #endif
  8187   8267     return rc;
  8188   8268   }