/ Check-in [e84d3afe]
Login

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

Overview
Comment:Document the SHOW_DATATYPES pragma and add tests for it to the test suite. Make sure datatypes are show even for aliased columns. Tickets #220 and #221. (CVS 822)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:e84d3afe7b9153d003fdcca98221f446c004ffa2
User & Date: drh 2003-01-11 14:19:52
Context
2003-01-11
14:25
Fix two compiler warnings from OS-X. (CVS 823) check-in: 4c22da76 user: drh tags: trunk
14:19
Document the SHOW_DATATYPES pragma and add tests for it to the test suite. Make sure datatypes are show even for aliased columns. Tickets #220 and #221. (CVS 822) check-in: e84d3afe user: drh tags: trunk
13:30
Remove the ColumnCount opcode, which had become a no-op. (CVS 821) check-in: 86deb12d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702













703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
...
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
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.118 2003/01/11 13:30:58 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i;
  if( pParse->colNamesSet || v==0 || sqlite_malloc_failed ) return;
  pParse->colNamesSet = 1;
#if 0
  if( pParse->db->flags & SQLITE_ReportTypes ){
    sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr*2, 0);
  }else{
    sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr, 0);
  }
#endif
  for(i=0; i<pEList->nExpr; i++){
    Expr *p;
    char *zType = 0;
    int showFullNames;













    if( pEList->a[i].zName ){
      char *zName = pEList->a[i].zName;
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
      continue;
    }
    p = pEList->a[i].pExpr;
    if( p==0 ) continue;
    showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
    if( p->op==TK_COLUMN && pTabList ){
      Table *pTab = pTabList->a[p->iTable - base].pTab;
      char *zCol;
      int iCol = p->iColumn;
      if( iCol<0 ) iCol = pTab->iPKey;
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
................................................................................
    }else{
      char zName[30];
      assert( p->op!=TK_COLUMN || pTabList==0 );
      sprintf(zName, "column%d", i+1);
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
    }
    if( pParse->db->flags & SQLITE_ReportTypes ){
      if( zType==0 ){
        if( sqliteExprType(p)==SQLITE_SO_TEXT ){
          zType = "TEXT";
        }else{
          zType = "NUMERIC";
        }
      }
      sqliteVdbeAddOp(v, OP_ColumnName, i + pEList->nExpr, 0);
      sqliteVdbeChangeP3(v, -1, zType, P3_STATIC);
    }
  }
}

/*
** Name of the connection operator, used for error messages.
*/
static const char *selectOpName(int id){







|







 







<
<
<
<
<
<
<




>
>
>
>
>
>
>
>
>
>
>
>
>






<
<







 







<
<
<
<
<
<
<
<
<
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
685
686
687
688
689
690
691







692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714


715
716
717
718
719
720
721
...
751
752
753
754
755
756
757











758
759
760
761
762
763
764
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.119 2003/01/11 14:19:52 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i;
  if( pParse->colNamesSet || v==0 || sqlite_malloc_failed ) return;
  pParse->colNamesSet = 1;







  for(i=0; i<pEList->nExpr; i++){
    Expr *p;
    char *zType = 0;
    int showFullNames;
    p = pEList->a[i].pExpr;
    if( p==0 ) continue;
    if( pParse->db->flags & SQLITE_ReportTypes ){
      if( zType==0 ){
        if( sqliteExprType(p)==SQLITE_SO_TEXT ){
          zType = "TEXT";
        }else{
          zType = "NUMERIC";
        }
      }
      sqliteVdbeAddOp(v, OP_ColumnName, i + pEList->nExpr, 0);
      sqliteVdbeChangeP3(v, -1, zType, P3_STATIC);
    }
    if( pEList->a[i].zName ){
      char *zName = pEList->a[i].zName;
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
      continue;
    }


    showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
    if( p->op==TK_COLUMN && pTabList ){
      Table *pTab = pTabList->a[p->iTable - base].pTab;
      char *zCol;
      int iCol = p->iColumn;
      if( iCol<0 ) iCol = pTab->iPKey;
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
................................................................................
    }else{
      char zName[30];
      assert( p->op!=TK_COLUMN || pTabList==0 );
      sprintf(zName, "column%d", i+1);
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
    }











  }
}

/*
** Name of the connection operator, used for error messages.
*/
static const char *selectOpName(int id){

Changes to src/test1.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
501
502
503
504
505
506
507






















































508
509
510
511
512
513
514
...
523
524
525
526
527
528
529

530
531
532
533
534
535
536
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.13 2002/08/31 18:53:08 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

/*
................................................................................
  rc = sqlite_create_function(db, argv[2], -1, testFunc, 0);
  if( rc!=0 ){
    Tcl_AppendResult(interp, sqlite_error_string(rc), 0);
    return TCL_ERROR;
  }
  return TCL_OK;
}























































/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite_search_count;
  static struct {
................................................................................
     { "sqlite_exec_printf",             (Tcl_CmdProc*)test_exec_printf      },
     { "sqlite_get_table_printf",        (Tcl_CmdProc*)test_get_table_printf },
     { "sqlite_close",                   (Tcl_CmdProc*)sqlite_test_close     },
     { "sqlite_create_function",         (Tcl_CmdProc*)test_create_function  },
     { "sqlite_create_aggregate",        (Tcl_CmdProc*)test_create_aggregate },
     { "sqlite_register_test_function",  (Tcl_CmdProc*)test_register_func    },
     { "sqlite_abort",                   (Tcl_CmdProc*)sqlite_abort          },

#ifdef MEMORY_DEBUG
     { "sqlite_malloc_fail",             (Tcl_CmdProc*)sqlite_malloc_fail    },
     { "sqlite_malloc_stat",             (Tcl_CmdProc*)sqlite_malloc_stat    },
#endif
  };
  int i;








|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
...
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.14 2003/01/11 14:19:52 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

/*
................................................................................
  rc = sqlite_create_function(db, argv[2], -1, testFunc, 0);
  if( rc!=0 ){
    Tcl_AppendResult(interp, sqlite_error_string(rc), 0);
    return TCL_ERROR;
  }
  return TCL_OK;
}

/*
** This SQLite callback records the datatype of all columns.
**
** The pArg argument is really a pointer to a TCL interpreter.  The
** column names are inserted as the result of this interpreter.
**
** This routine returns non-zero which causes the query to abort.
*/
static int rememberDataTypes(void *pArg, int nCol, char **argv, char **colv){
  int i;
  Tcl_Interp *interp = (Tcl_Interp*)pArg;
  Tcl_Obj *pList, *pElem;
  if( colv[nCol+1]==0 ){
    return 1;
  }
  pList = Tcl_NewObj();
  for(i=0; i<nCol; i++){
    pElem = Tcl_NewStringObj(colv[i+nCol] ? colv[i+nCol] : "NULL", -1);
    Tcl_ListObjAppendElement(interp, pList, pElem);
  }
  Tcl_SetObjResult(interp, pList);
  return 1;
}

/*
** Invoke an SQL statement but ignore all the data in the result.  Instead,
** return a list that consists of the datatypes of the various columns.
**
** This only works if "PRAGMA show_datatypes=on" has been executed against
** the database connection.
*/
static int sqlite_datatypes(
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  char **argv            /* Text of each argument */
){
  sqlite *db;
  int rc;
  if( argc!=3 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0], 
       " DB SQL", 0);
    return TCL_ERROR;
  }
  db = (sqlite*)strtol(argv[1], 0, 0);
  rc = sqlite_exec(db, argv[2], rememberDataTypes, interp, 0);
  if( rc!=0 && rc!=SQLITE_ABORT ){
    Tcl_AppendResult(interp, sqlite_error_string(rc), 0);
    return TCL_ERROR;
  }
  return TCL_OK;
}


/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite_search_count;
  static struct {
................................................................................
     { "sqlite_exec_printf",             (Tcl_CmdProc*)test_exec_printf      },
     { "sqlite_get_table_printf",        (Tcl_CmdProc*)test_get_table_printf },
     { "sqlite_close",                   (Tcl_CmdProc*)sqlite_test_close     },
     { "sqlite_create_function",         (Tcl_CmdProc*)test_create_function  },
     { "sqlite_create_aggregate",        (Tcl_CmdProc*)test_create_aggregate },
     { "sqlite_register_test_function",  (Tcl_CmdProc*)test_register_func    },
     { "sqlite_abort",                   (Tcl_CmdProc*)sqlite_abort          },
     { "sqlite_datatypes",               (Tcl_CmdProc*)sqlite_datatypes      },
#ifdef MEMORY_DEBUG
     { "sqlite_malloc_fail",             (Tcl_CmdProc*)sqlite_malloc_fail    },
     { "sqlite_malloc_stat",             (Tcl_CmdProc*)sqlite_malloc_stat    },
#endif
  };
  int i;

Changes to test/pager.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: pager.test,v 1.11 2002/02/02 18:49:21 drh Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl

if {[info commands pager_open]!=""} {

................................................................................
# Try to write a few pages.
#
do_test pager-2.1 {
  set v [catch {
    set ::p1 [pager_open ptf1.db 10]
  } msg]
} {0}
do_test pager-2.2 {
  set v [catch {
    set ::g1 [page_get $::p1 0]
  } msg]
  lappend v $msg
} {1 SQLITE_ERROR}
do_test pager-2.3.1 {
  set ::gx [page_lookup $::p1 1]
} {}
do_test pager-2.3.2 {
  pager_stats $::p1
} {ref 0 page 0 max 10 size -1 state 0 err 0 hit 0 miss 0 ovfl 0}
do_test pager-2.3.3 {







|







 







|
|
|
|
|
|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: pager.test,v 1.12 2003/01/11 14:19:52 drh Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl

if {[info commands pager_open]!=""} {

................................................................................
# Try to write a few pages.
#
do_test pager-2.1 {
  set v [catch {
    set ::p1 [pager_open ptf1.db 10]
  } msg]
} {0}
#do_test pager-2.2 {
#  set v [catch {
#    set ::g1 [page_get $::p1 0]
#  } msg]
#  lappend v $msg
#} {1 SQLITE_ERROR}
do_test pager-2.3.1 {
  set ::gx [page_lookup $::p1 1]
} {}
do_test pager-2.3.2 {
  pager_stats $::p1
} {ref 0 page 0 max 10 size -1 state 0 err 0 hit 0 miss 0 ovfl 0}
do_test pager-2.3.3 {

Changes to test/pragma.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
...
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119







































120
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.1 2002/03/06 22:01:37 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
db close
file delete test.db
sqlite db test.db

do_test pragma-1.1 {
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
................................................................................
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}
do_test pragma-1.10 {
  db close
  sqlite db test.db
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}








































finish_test







|









|







 







|








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
...
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.2 2003/01/11 14:19:52 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
db close
file delete test.db
set DB [sqlite db test.db]

do_test pragma-1.1 {
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
................................................................................
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}
do_test pragma-1.10 {
  db close
  set ::DB [sqlite db test.db]
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}

do_test pragma-1.11 {
  execsql {
    PRAGMA show_datatypes=on;
    PRAGMA empty_result_callbacks=off;
  }
  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
} {}
do_test pragma-1.12 {
  execsql {
    PRAGMA empty_result_callbacks=on;
  }
  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
} {NUMERIC NUMERIC NUMERIC NUMERIC NUMERIC}
do_test pragma-1.13 {
  execsql {
    CREATE TABLE t1(
       a INTEGER,
       b TEXT,
       c WHATEVER,
       d CLOB,
       e BLOB,
       f VARCHAR(123),
       g nVaRcHaR(432)
    );
  }
  sqlite_datatypes $::DB {SELECT * FROM t1}
} {NUMERIC TEXT NUMERIC TEXT TEXT TEXT TEXT}
do_test pragma-1.14 {
  sqlite_datatypes $::DB {
     SELECT 1, 'hello', NULL
  }
} {NUMERIC TEXT TEXT}
do_test pragma-1.15 {
  sqlite_datatypes $::DB {
     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
  }
} {NUMERIC TEXT TEXT}


finish_test

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
....
1112
1113
1114
1115
1116
1117
1118





























1119
1120
1121
1122
1123
1124
1125
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.46 2002/09/12 14:08:32 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................

<li><p><b>PRAGMA integrity_check;</b></p>
    <p>The command does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, and malformed records.
    If any problems are found, then a single string is returned which is
    a description of all problems.  If everything is in order, "ok" is
    returned.</p>






























<li><p><b>PRAGMA synchronous;
       <br>PRAGMA synchronous = ON;
       <br>PRAGMA synchronous = OFF;</b></p>
    <p>Query or change the setting of the "synchronous" flag in
    the database for the duration of the current database connect.
    The synchronous flag reverts to its default value when the database



|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
....
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.47 2003/01/11 14:19:52 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................

<li><p><b>PRAGMA integrity_check;</b></p>
    <p>The command does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, and malformed records.
    If any problems are found, then a single string is returned which is
    a description of all problems.  If everything is in order, "ok" is
    returned.</p>

<li><p><b>PRAGMA show_datatypes = ON;<br>PRAGMA show_datatypes = OFF;</b></p>
    <p>When turned on, the SHOW_DATATYPES pragma causes extra entries containing
    the names of <a href="datatypes.html">datatypes</a> of columns to be
    appended to the 4th ("columnNames") argument to <b>sqlite_exec()</b>
    callbacks.  When
    turned off, the 4th argument to callbacks contains only the column names.
    SQLite <a href="datatypes.html">datatypes</a> are always either "TEXT"
    or "NUMERIC".
    The following chart illustrates the difference for the query
    "SELECT 'xyzzy', 5, NULL AS empty ":</p>

    <blockquote><table border=0>
    <tr><th>show_datatypes=OFF</th><th width=30></th>
        <th>show_datatypes=ON</th></tr>
    <tr><td valign="top">
       azCol[0] = "xyzzy";<br>
       azCol[1] = "5";<br>
       azCol[2] = "empty";<br>
       azCol[3] = 0;
    </td><td></td><td valign="top">
       azCol[0] = "xyzzy";<br>
       azCol[1] = "5";<br>
       azCol[2] = "empty";<br>
       azCol[3] = "TEXT";<br>
       azCol[4] = "NUMERIC";<br>
       azCol[5] = "TEXT";<br>
       azCol[6] = 0;
    </td></table></blockquote></li>

<li><p><b>PRAGMA synchronous;
       <br>PRAGMA synchronous = ON;
       <br>PRAGMA synchronous = OFF;</b></p>
    <p>Query or change the setting of the "synchronous" flag in
    the database for the duration of the current database connect.
    The synchronous flag reverts to its default value when the database