SQLite

Check-in [162cf42e33]
Login

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

Overview
Comment:Update the typeof() operator to respect manifest types. (CVS 1450)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 162cf42e33691f4c9ec3a25abcaa2bdcdca2b5e0
User & Date: danielk1977 2004-05-24 12:55:55.000
Context
2004-05-24
23:48
Aggregate functions also use sqlite_value* instead of const char * for arguments. (CVS 1451) (check-in: 5c28ed5e9b user: danielk1977 tags: trunk)
12:55
Update the typeof() operator to respect manifest types. (CVS 1450) (check-in: 162cf42e33 user: danielk1977 tags: trunk)
12:39
Non-aggregate SQL functions use sqlite_value* instead of const char * for argument values. (CVS 1449) (check-in: 1e47d7384d user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/func.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.50 2004/05/24 12:39:02 danielk1977 Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "os.h"







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.51 2004/05/24 12:55:55 danielk1977 Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "os.h"
55
56
57
58
59
60
61

62







63
64
65
66
67
68
69
70
  sqlite3_set_result_string(context, zBest, -1);
}

/*
** Return the type of the argument.
*/
static void typeofFunc(sqlite_func *context, int argc, sqlite3_value **argv){

  assert( argc==2 );







  sqlite3_set_result_string(context, sqlite3_value_data(argv[1]), -1);
}

/*
** Implementation of the length() function
*/
static void lengthFunc(sqlite_func *context, int argc, sqlite3_value **argv){
  const char *z;







>

>
>
>
>
>
>
>
|







55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
  sqlite3_set_result_string(context, zBest, -1);
}

/*
** Return the type of the argument.
*/
static void typeofFunc(sqlite_func *context, int argc, sqlite3_value **argv){
  const char *z = 0;
  assert( argc==2 );
  switch( sqlite3_value_type(argv[0]) ){
    case SQLITE3_NULL: z = "null" ; break;
    case SQLITE3_INTEGER: z = "integer" ; break;
    case SQLITE3_TEXT: z = "text" ; break;
    case SQLITE3_FLOAT: z = "real" ; break;
    case SQLITE3_BLOB: z = "blob" ; break;
  }
  sqlite3_set_result_string(context, z, -1);
}

/*
** Implementation of the length() function
*/
static void lengthFunc(sqlite_func *context, int argc, sqlite3_value **argv){
  const char *z;
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.248 2004/05/24 07:04:26 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.249 2004/05/24 12:55:55 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
/*
** Each SQL function is defined by an instance of the following
** structure.  A pointer to this structure is stored in the sqlite.aFunc
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {
  void (*xFunc)(sqlite_func*,int,const char**);  /* Regular function */
  void (*xStep)(sqlite_func*,int,const char**);  /* Aggregate function step */
  void (*xFinalize)(sqlite_func*);           /* Aggregate function finializer */
  signed char nArg;         /* Number of arguments.  -1 means unlimited */
  signed char dataType;     /* Arg that determines datatype.  -1=NUMERIC, */
                            /* -2=TEXT. -3=SQLITE_ARGS */
  u8 includeTypes;          /* Add datatypes to args of xFunc and xStep */
  void *pUserData;          /* User data parameter */







|







460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
/*
** Each SQL function is defined by an instance of the following
** structure.  A pointer to this structure is stored in the sqlite.aFunc
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {
  void (*xFunc)(sqlite_func*,int,sqlite3_value**);  /* Regular function */
  void (*xStep)(sqlite_func*,int,const char**);  /* Aggregate function step */
  void (*xFinalize)(sqlite_func*);           /* Aggregate function finializer */
  signed char nArg;         /* Number of arguments.  -1 means unlimited */
  signed char dataType;     /* Arg that determines datatype.  -1=NUMERIC, */
                            /* -2=TEXT. -3=SQLITE_ARGS */
  u8 includeTypes;          /* Add datatypes to args of xFunc and xStep */
  void *pUserData;          /* User data parameter */
Changes to test/bind.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2003 September 6
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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 testing the sqlite_bind API.
#
# $Id: bind.test,v 1.6 2004/05/21 10:08:55 danielk1977 Exp $
#

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

do_test bind-1.1 {
  db close













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2003 September 6
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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 testing the sqlite_bind API.
#
# $Id: bind.test,v 1.7 2004/05/24 12:55:55 danielk1977 Exp $
#

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

do_test bind-1.1 {
  db close
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
  sqlite3_bind_int32 $VM 2 -2000000000
  sqlite3_bind_int32 $VM 3 2000000000
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 123 456 789 2 123 -2000000000 2000000000}
do_test bind-2.4 {
  execsql {SELECT classof(a), classof(b), classof(c) FROM t1}
} {INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER}
do_test bind-2.5 {
  execsql {
    DELETE FROM t1;
  }
} {}

# 64 bit Integers
do_test bind-3.1 {
  sqlite3_bind_int64 $VM 1 32
  sqlite3_bind_int64 $VM 2 -2000000000000
  sqlite3_bind_int64 $VM 3 2000000000000
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 32 -2000000000000 2000000000000}
do_test bind-3.2 {
  execsql {SELECT classof(a), classof(b), classof(c) FROM t1}
} {INTEGER INTEGER INTEGER}
do_test bind-3.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# Doubles
do_test bind-4.1 {
  sqlite3_bind_double $VM 1 1234.1234
  sqlite3_bind_double $VM 2 0.00001
  sqlite3_bind_double $VM 3 123456789
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 1234.1234 1e-05 123456789}
do_test bind-4.2 {
  execsql {SELECT classof(a), classof(b), classof(c) FROM t1}
} {REAL REAL REAL}
do_test bind-4.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# NULL
do_test bind-5.1 {
  sqlite3_bind_null $VM 1
  sqlite3_bind_null $VM 2
  sqlite3_bind_null $VM 3 
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 {} {} {}}
do_test bind-5.2 {
  execsql {SELECT classof(a), classof(b), classof(c) FROM t1}
} {NULL NULL NULL}
do_test bind-5.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# UTF-8 text
do_test bind-6.1 {
  sqlite3_bind_text $VM 1 hellothere 5
  sqlite3_bind_text $VM 2 "." 2
  sqlite3_bind_text $VM 3 world -1
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 hello . world}
do_test bind-6.2 {
  execsql {SELECT classof(a), classof(b), classof(c) FROM t1}
} {TEXT TEXT TEXT}
do_test bind-6.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# UTF-16 text
do_test bind-7.1 {
  sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
  sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
  sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 hello {} world}
do_test bind-7.2 {
  execsql {SELECT classof(a), classof(b), classof(c) FROM t1}
} {TEXT TEXT TEXT}
do_test bind-7.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# Test that the 'out of range' error works.







|
|
















|
|
















|
|
















|
|
















|
|
















|
|







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
  sqlite3_bind_int32 $VM 2 -2000000000
  sqlite3_bind_int32 $VM 3 2000000000
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 123 456 789 2 123 -2000000000 2000000000}
do_test bind-2.4 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {integer integer integer integer integer integer}
do_test bind-2.5 {
  execsql {
    DELETE FROM t1;
  }
} {}

# 64 bit Integers
do_test bind-3.1 {
  sqlite3_bind_int64 $VM 1 32
  sqlite3_bind_int64 $VM 2 -2000000000000
  sqlite3_bind_int64 $VM 3 2000000000000
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 32 -2000000000000 2000000000000}
do_test bind-3.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {integer integer integer}
do_test bind-3.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# Doubles
do_test bind-4.1 {
  sqlite3_bind_double $VM 1 1234.1234
  sqlite3_bind_double $VM 2 0.00001
  sqlite3_bind_double $VM 3 123456789
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 1234.1234 1e-05 123456789}
do_test bind-4.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {real real real}
do_test bind-4.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# NULL
do_test bind-5.1 {
  sqlite3_bind_null $VM 1
  sqlite3_bind_null $VM 2
  sqlite3_bind_null $VM 3 
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 {} {} {}}
do_test bind-5.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {null null null}
do_test bind-5.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# UTF-8 text
do_test bind-6.1 {
  sqlite3_bind_text $VM 1 hellothere 5
  sqlite3_bind_text $VM 2 "." 2
  sqlite3_bind_text $VM 3 world -1
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 hello . world}
do_test bind-6.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {text text text}
do_test bind-6.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# UTF-16 text
do_test bind-7.1 {
  sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
  sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
  sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 hello {} world}
do_test bind-7.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {text text text}
do_test bind-7.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# Test that the 'out of range' error works.
Changes to test/table.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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 file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.23 2004/05/19 21:09:32 drh Exp $

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

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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 file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.24 2004/05/24 12:55:55 danielk1977 Exp $

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

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {
469
470
471
472
473
474
475
476

477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
    CREATE TABLE t6(a,b,c,
      FOREIGN KEY (x,b) REFERENCES t4(x,y)
    );
  }
} {1 {unknown column "x" in foreign key definition}}


# Test for the "typeof" function.

#
do_test table-11.1 {
  execsql {
    CREATE TABLE t7(
       a integer primary key,
       b number(5,10),
       c character varying (8),
       d VARCHAR(9),
       e clob,
       f BLOB,
       g Text,
       h
    );
    INSERT INTO t7(a) VALUES(1);
    SELECT typeof(a), typeof(b), typeof(c), typeof(d),
           typeof(e), typeof(f), typeof(g), typeof(h)
    FROM t7 LIMIT 1;
  }
} {numeric numeric text text text numeric text numeric}
do_test table-11.2 {
  execsql {
    SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
    FROM t7 LIMIT 1;
  }
} {numeric text numeric text}

finish_test







|
>


















|





|


469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
    CREATE TABLE t6(a,b,c,
      FOREIGN KEY (x,b) REFERENCES t4(x,y)
    );
  }
} {1 {unknown column "x" in foreign key definition}}


# Test for the "typeof" function. More tests for the
# typeof() function are found in bind.test and types.test.
#
do_test table-11.1 {
  execsql {
    CREATE TABLE t7(
       a integer primary key,
       b number(5,10),
       c character varying (8),
       d VARCHAR(9),
       e clob,
       f BLOB,
       g Text,
       h
    );
    INSERT INTO t7(a) VALUES(1);
    SELECT typeof(a), typeof(b), typeof(c), typeof(d),
           typeof(e), typeof(f), typeof(g), typeof(h)
    FROM t7 LIMIT 1;
  }
} {integer null null null null null null null} 
do_test table-11.2 {
  execsql {
    SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
    FROM t7 LIMIT 1;
  }
} {null null null null}

finish_test
Changes to test/types.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.5 2004/05/20 12:41:20 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.6 2004/05/24 12:55:55 danielk1977 Exp $

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

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
# Each element of the following list represents one test case.
#
# The first value of each sub-list is an SQL literal. The following
# four value are the storage classes that would be used if the
# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
# or NONE, respectively.
set values {
  { 5.0    INTEGER REAL    TEXT REAL    }
  { 5      INTEGER INTEGER TEXT INTEGER }
  { '5.0'  INTEGER REAL    TEXT TEXT    }
  { '-5.0' INTEGER REAL    TEXT TEXT    }
  { '-5.0' INTEGER REAL    TEXT TEXT    }
  { '5'    INTEGER INTEGER TEXT TEXT    }
  { 'abc'  TEXT    TEXT    TEXT TEXT    }
  { NULL   NULL    NULL    NULL NULL    }
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# INSERT INTO <table> VALUE(<values>);
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "DELETE FROM t1;"
  execsql "INSERT INTO t1 VALUES($lit, $lit, $lit, $lit);"
  do_test types-1.1.$tnum {
    execsql {
      SELECT classof(i), classof(n), classof(t), classof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# INSERT INTO t1 SELECT ....
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "DELETE FROM t1;"
  execsql "INSERT INTO t1 SELECT $lit, $lit, $lit, $lit;"
  do_test types-1.2.$tnum {
    execsql {
      SELECT classof(i), classof(n), classof(t), classof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# UPDATE <table> SET <column> = <value>;
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "UPDATE t1 SET i = $lit, n = $lit, t = $lit, o = $lit;"
  do_test types-1.3.$tnum {
    execsql {
      SELECT classof(i), classof(n), classof(t), classof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

execsql {
  DROP TABLE t1;







|
|
|
|
|
|
|
|















|


















|

















|







48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
# Each element of the following list represents one test case.
#
# The first value of each sub-list is an SQL literal. The following
# four value are the storage classes that would be used if the
# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
# or NONE, respectively.
set values {
  { 5.0    integer real    text real    }
  { 5      integer integer text integer }
  { '5.0'  integer real    text text    }
  { '-5.0' integer real    text text    }
  { '-5.0' integer real    text text    }
  { '5'    integer integer text text    }
  { 'abc'  text    text    text text    }
  { NULL   null    null    null null    }
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# INSERT INTO <table> VALUE(<values>);
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "DELETE FROM t1;"
  execsql "INSERT INTO t1 VALUES($lit, $lit, $lit, $lit);"
  do_test types-1.1.$tnum {
    execsql {
      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# INSERT INTO t1 SELECT ....
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "DELETE FROM t1;"
  execsql "INSERT INTO t1 SELECT $lit, $lit, $lit, $lit;"
  do_test types-1.2.$tnum {
    execsql {
      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# UPDATE <table> SET <column> = <value>;
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "UPDATE t1 SET i = $lit, n = $lit, t = $lit, o = $lit;"
  do_test types-1.3.$tnum {
    execsql {
      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

execsql {
  DROP TABLE t1;