SQLite

Check-in [4e1db8e9]
Login

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

Overview
Comment:Improvements to columnar output in the CLI. Columns automatically expand to contain the largest row.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4e1db8e9a9ee370a398f13fd8546a520111b8cfb84460389535b5bc5bd9f4f82
User & Date: drh 2020-05-29 16:15:58
Context
2020-05-29
19:03
Fix a memory leak in the CLI when an unknown or unrecognized argument is given to the ".dump" command. (check-in: 71bfbbcc user: drh tags: trunk)
16:15
Improvements to columnar output in the CLI. Columns automatically expand to contain the largest row. (check-in: 4e1db8e9 user: drh tags: trunk)
14:38
Space to hold the ".width" of columns in the CLI is now obtained from malloc() and hence is not limited in the number of columns supported. (check-in: 445ed5da user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/shell.c.in.
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
static void print_row_separator(
  ShellState *p,
  int nArg,
  const char *zSep
){
  int i;
  for(i=0; i<nArg; i++){
    int w;
    if( i<ArraySize(p->actualWidth) ){
      w = p->actualWidth[i];
      if( w<0 ) w = -w;
    }else{
       w = 10;
    }
    fputs(zSep, p->out);
    print_dashes(p->out, w+2);
  }
  fputs(zSep, p->out);
  fputs("\n", p->out);
}

/*
** This is the callback routine that the shell







<
<
<
<
<
<
<

|







1941
1942
1943
1944
1945
1946
1947







1948
1949
1950
1951
1952
1953
1954
1955
1956
static void print_row_separator(
  ShellState *p,
  int nArg,
  const char *zSep
){
  int i;
  for(i=0; i<nArg; i++){







    fputs(zSep, p->out);
    print_dashes(p->out, p->actualWidth[i]+2);
  }
  fputs(zSep, p->out);
  fputs("\n", p->out);
}

/*
** This is the callback routine that the shell
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
      if( p->cnt++>0 ) utf8_printf(p->out, "%s", p->rowSeparator);
      for(i=0; i<nArg; i++){
        utf8_printf(p->out,"%*s = %s%s", w, azCol[i],
                azArg[i] ? azArg[i] : p->nullValue, p->rowSeparator);
      }
      break;
    }
    case MODE_Table:
    case MODE_Markdown:
    case MODE_Explain:
    case MODE_Column: {
      int showHdr;
      char *rowSep;
      char *colSep;
      char *rowStart;
      if( p->cMode==MODE_Column ){
        showHdr = p->showHeader;
        rowSep = p->rowSeparator;
        colSep = "  ";
        rowStart = "";
      }else if( p->cMode==MODE_Explain ){
        showHdr = 1;
        rowSep = SEP_Row;
        colSep = "  ";
        rowStart = "";
      }else{
        showHdr = 1;
        rowSep = " |\n";
        colSep = " | ";
        rowStart = "| ";
      }

      if( p->cnt++==0 ){
        /* Compute column widths in p->actualWidth[] when the first row
        ** is seen.  The width of each column is either the defined
        ** width in p->colWidth[], or if p->colWidth[i]==0, then the
        ** width is the larger of the width of the first row and the
        ** column name. */
        static const int aExplainWidths[] = {4, 13, 4, 4, 4, 13, 2, 13};
        const int *colWidth;
        int nWidth;
        if( p->cMode==MODE_Explain ){
          colWidth = aExplainWidths;
          nWidth = ArraySize(aExplainWidths);
        }else{
          colWidth = p->colWidth;
          nWidth = p->nWidth;
        }
        for(i=0; i<nArg; i++){
          int w, n;
          if( i<nWidth ){
            w = colWidth[i];
          }else{
            w = 0;
          }
          if( w==0 ){
            w = strlenChar(azCol[i] ? azCol[i] : "");
            if( w<10 ) w = 10;
            n = strlenChar(azArg && azArg[i] ? azArg[i] : p->nullValue);
            if( w<n ) w = n;
          }
          if( i<ArraySize(p->actualWidth) ){
            p->actualWidth[i] = w;
          }
        }
        if( p->cMode==MODE_Table ){
          print_row_separator(p, nArg, "+");
        }
        if( showHdr ){
          fputs(rowStart, p->out);
          for(i=0; i<nArg; i++){
            int w;
            if( i<ArraySize(p->actualWidth) ){
               w = p->actualWidth[i];
               if( w<0 ) w = -w;
            }else{
               w = 10;
            }
            utf8_width_print(p->out, w, azCol[i]);
            fputs(i==nArg-1 ? rowSep : colSep, p->out);
          }
          for(i=0; i<nArg; i++){
            int w;
            if( i<ArraySize(p->actualWidth) ){
               w = p->actualWidth[i];
               if( w<0 ) w = -w;
            }else{
               w = 10;
            }
            if( p->cMode==MODE_Table || p->cMode==MODE_Markdown ){
              char *zX = p->cMode==MODE_Markdown ? "|" : "+";
              fputs(zX, p->out);
              print_dashes(p->out, w+2);
              if( i==nArg-1 ){
                fputs(zX, p->out);
                fputs("\n", p->out);
              }
            }else{
              print_dashes(p->out, w);
              fputs(i==nArg-1 ? rowSep : colSep, p->out);
            }
          }
        }
      }
      if( azArg==0 ) break;
      fputs(rowStart, p->out);
      for(i=0; i<nArg; i++){
        int w;
        if( i<ArraySize(p->actualWidth) ){
           w = p->actualWidth[i];
        }else{
           w = 10;
        }
        if( p->cMode==MODE_Explain ){
          if( azArg[i] && strlenChar(azArg[i])>w ){
            w = strlenChar(azArg[i]);
          }
          if( i==1 && p->aiIndent && p->pStmt ){
            if( p->iIndent<p->nIndent ){
              utf8_printf(p->out, "%*.s", p->aiIndent[p->iIndent], "");
            }
            p->iIndent++;
          }
        }
        utf8_width_print(p->out, w, azArg[i] ? azArg[i] : p->nullValue);
        utf8_printf(p->out, "%s", i==nArg-1 ? rowSep : colSep);
      }
      break;
    }
    case MODE_Semi: {   /* .schema and .fullschema output */
      printSchemaLine(p->out, azArg[0], ";\n");
      break;
    }







<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
<
<
<
|
|
<
<
<
|
<
<
<
<
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
<
<
|
<
<
<
<
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<



<

<
<
|
<
<
<
<
|
|
|
|
|
|
|
|
|
<

|







1978
1979
1980
1981
1982
1983
1984


1985




























1986



1987
1988



1989







1990














1991


1992




1993
1994






















1995
1996
1997

1998


1999




2000
2001
2002
2003
2004
2005
2006
2007
2008

2009
2010
2011
2012
2013
2014
2015
2016
2017
      if( p->cnt++>0 ) utf8_printf(p->out, "%s", p->rowSeparator);
      for(i=0; i<nArg; i++){
        utf8_printf(p->out,"%*s = %s%s", w, azCol[i],
                azArg[i] ? azArg[i] : p->nullValue, p->rowSeparator);
      }
      break;
    }


    case MODE_Explain: {




























      static const int aExplainWidth[] = {4, 13, 4, 4, 4, 13, 2, 13};



      if( nArg>ArraySize(aExplainWidth) ){
        nArg = ArraySize(aExplainWidth);



      }







      if( p->cnt++==0 ){














        for(i=0; i<nArg; i++){


          int w = aExplainWidth[i];




          utf8_width_print(p->out, w, azCol[i]);
          fputs(i==nArg-1 ? "\n" : "  ", p->out);






















        }
      }
      if( azArg==0 ) break;

      for(i=0; i<nArg; i++){


        int w = aExplainWidth[i];




        if( azArg[i] && strlenChar(azArg[i])>w ){
          w = strlenChar(azArg[i]);
        }
        if( i==1 && p->aiIndent && p->pStmt ){
          if( p->iIndent<p->nIndent ){
            utf8_printf(p->out, "%*.s", p->aiIndent[p->iIndent], "");
          }
          p->iIndent++;
        }

        utf8_width_print(p->out, w, azArg[i] ? azArg[i] : p->nullValue);
        fputs(i==nArg-1 ? "\n" : "  ", p->out);
      }
      break;
    }
    case MODE_Semi: {   /* .schema and .fullschema output */
      printSchemaLine(p->out, azArg[0], ";\n");
      break;
    }
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058









3059







3060










3061

























































3062
3063
3064
3065
3066
3067
3068
3069
3070








3071
3072
3073
3074
3075
3076
3077
      sqlite3_bind_null(pStmt, i);
    }
    sqlite3_reset(pQ);
  }
  sqlite3_finalize(pQ);
}

#if 0
/*
** Run a prepared statement and output the result in one of the
** table-oriented formats, either MODE_Markdown or MODE_Table.
**
** This is different from ordinary exec_prepared_stmt() in that
** it has to run the entire query and gather the results into memory
** first, in order to determine column widths, before providing
** any output.
*/
static void exec_prepared_stmt_tablemode(
  ShellState *pArg,                                /* Pointer to ShellState */
  sqlite3_stmt *pStmt                              /* Statment to run */
){

















}










#endif


























































/*
** Run a prepared statement
*/
static void exec_prepared_stmt(
  ShellState *pArg,                                /* Pointer to ShellState */
  sqlite3_stmt *pStmt                              /* Statment to run */
){
  int rc;









  /* perform the first step.  this will tell us if we
  ** have a result set or not and how wide it is.
  */
  rc = sqlite3_step(pStmt);
  /* if we have a result set... */
  if( SQLITE_ROW == rc ){







<


|






|
|
|

>
>
>
>
>
>
>
>
>

>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









>
>
>
>
>
>
>
>







2938
2939
2940
2941
2942
2943
2944

2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
      sqlite3_bind_null(pStmt, i);
    }
    sqlite3_reset(pQ);
  }
  sqlite3_finalize(pQ);
}


/*
** Run a prepared statement and output the result in one of the
** table-oriented formats: MODE_Column, MODE_Markdown, or MODE_Table.
**
** This is different from ordinary exec_prepared_stmt() in that
** it has to run the entire query and gather the results into memory
** first, in order to determine column widths, before providing
** any output.
*/
static void exec_prepared_stmt_columnar(
  ShellState *p,                        /* Pointer to ShellState */
  sqlite3_stmt *pStmt                   /* Statment to run */
){
  int nRow = 0;
  int nColumn = 0;
  char **azData = 0;
  char *zMsg = 0;
  const char *z;
  int rc;
  int i, j, nTotal, w, n;
  const char *colSep;
  const char *rowSep;

  rc = sqlite3_get_table(p->db, sqlite3_sql(pStmt),
                         &azData, &nRow, &nColumn, &zMsg);
  if( rc ){
    utf8_printf(p->out, "ERROR: %s\n", zMsg);
    sqlite3_free(zMsg);
    sqlite3_free_table(azData);
    return;
  }
  if( nColumn>p->nWidth ){
    p->colWidth = realloc(p->colWidth, nColumn*2*sizeof(int));
    if( p->colWidth==0 ) shell_out_of_memory();
    for(i=p->nWidth; i<nColumn; i++) p->colWidth[i] = 0;
    p->nWidth = nColumn;
    p->actualWidth = &p->colWidth[nColumn];
  }
  memset(p->actualWidth, 0, nColumn*sizeof(int));
  for(i=0; i<nColumn; i++){
    w = p->colWidth[i];
    if( w<0 ) w = -w;
    p->actualWidth[i] = w;
  }
  nTotal = nColumn*(nRow+1);
  for(i=0; i<nTotal; i++){
    z = azData[i];
    if( z==0 ) z = p->nullValue;
    n = strlenChar(z);
    j = i%nColumn;
    if( n>p->actualWidth[j] ) p->actualWidth[j] = n;
  }
  if( p->cMode==MODE_Column ){
    colSep = "  ";
    rowSep = "\n";
    if( p->showHeader ){
      for(i=0; i<nColumn; i++){
        w = p->actualWidth[i];
        if( p->colWidth[i]<0 ) w = -w;
        utf8_width_print(p->out, w, azData[i]);
        fputs(i==nColumn-1?"\n":"  ", p->out);
      }
      for(i=0; i<nColumn; i++){
        print_dashes(p->out, p->actualWidth[i]);
        fputs(i==nColumn-1?"\n":"  ", p->out);
      }
    }
  }else{
    colSep = " | ";
    rowSep = " |\n";
    if( p->cMode==MODE_Table ) print_row_separator(p, nColumn, "+");
    fputs("| ", p->out);
    for(i=0; i<nColumn; i++){
      w = p->actualWidth[i];
      n = strlenChar(azData[i]);
      utf8_printf(p->out, "%*s%s%*s", (w-n)/2, "", azData[i], (w-n+1)/2, "");
      fputs(i==nColumn-1?" |\n":" | ", p->out);
    }
    print_row_separator(p, nColumn, p->cMode==MODE_Table ? "+" : "|");
  }
  for(i=nColumn, j=0; i<nTotal; i++, j++){
    if( j==0 && p->cMode!=MODE_Column ) fputs("| ", p->out);
    z = azData[i];
    if( z==0 ) z = p->nullValue;
    w = p->actualWidth[j];
    if( p->colWidth[j]<0 ) w = -w;
    utf8_width_print(p->out, w, z);
    if( j==nColumn-1 ){
      fputs(rowSep, p->out);
      j = -1;
    }else{
      fputs(colSep, p->out);
    }
  }
  if( p->cMode==MODE_Table ){
    print_row_separator(p, nColumn, "+");
  }
  sqlite3_free_table(azData);
}

/*
** Run a prepared statement
*/
static void exec_prepared_stmt(
  ShellState *pArg,                                /* Pointer to ShellState */
  sqlite3_stmt *pStmt                              /* Statment to run */
){
  int rc;

  if( pArg->cMode==MODE_Column
   || pArg->cMode==MODE_Table
   || pArg->cMode==MODE_Markdown
  ){
    exec_prepared_stmt_columnar(pArg, pStmt);
    return;
  }

  /* perform the first step.  this will tell us if we
  ** have a result set or not and how wide it is.
  */
  rc = sqlite3_step(pStmt);
  /* if we have a result set... */
  if( SQLITE_ROW == rc ){