/ Check-in [658f4225]
Login

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

Overview
Comment:Test cases for sqlite3_normalize().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | normalize
Files: files | file ages | folders
SHA3-256: 658f42257d56a3562dfa8e55023c6e497f55c565cd751d942de3a9e0cb2fa708
User & Date: drh 2018-01-08 20:04:57
Context
2018-01-24
15:07
Add the normalize.c extension. check-in: 16ebe558 user: drh tags: trunk
2018-01-08
20:04
Test cases for sqlite3_normalize(). Closed-Leaf check-in: 658f4225 user: drh tags: normalize
19:29
Special handling of the NULL keyword. Sometimes it is a literal, and sometimes it is a keyword. check-in: db5d138e user: drh tags: normalize
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to Makefile.in.

436
437
438
439
440
441
442

443
444
445
446
447
448
449
  $(TOP)/ext/misc/fuzzer.c \
  $(TOP)/ext/fts5/fts5_tcl.c \
  $(TOP)/ext/fts5/fts5_test_mi.c \
  $(TOP)/ext/fts5/fts5_test_tok.c \
  $(TOP)/ext/misc/ieee754.c \
  $(TOP)/ext/misc/mmapwarm.c \
  $(TOP)/ext/misc/nextchar.c \

  $(TOP)/ext/misc/percentile.c \
  $(TOP)/ext/misc/regexp.c \
  $(TOP)/ext/misc/remember.c \
  $(TOP)/ext/misc/series.c \
  $(TOP)/ext/misc/spellfix.c \
  $(TOP)/ext/misc/totype.c \
  $(TOP)/ext/misc/unionvtab.c \







>







436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
  $(TOP)/ext/misc/fuzzer.c \
  $(TOP)/ext/fts5/fts5_tcl.c \
  $(TOP)/ext/fts5/fts5_test_mi.c \
  $(TOP)/ext/fts5/fts5_test_tok.c \
  $(TOP)/ext/misc/ieee754.c \
  $(TOP)/ext/misc/mmapwarm.c \
  $(TOP)/ext/misc/nextchar.c \
  $(TOP)/ext/misc/normalize.c \
  $(TOP)/ext/misc/percentile.c \
  $(TOP)/ext/misc/regexp.c \
  $(TOP)/ext/misc/remember.c \
  $(TOP)/ext/misc/series.c \
  $(TOP)/ext/misc/spellfix.c \
  $(TOP)/ext/misc/totype.c \
  $(TOP)/ext/misc/unionvtab.c \

Changes to Makefile.msc.

1496
1497
1498
1499
1500
1501
1502

1503
1504
1505
1506
1507
1508
1509
  $(TOP)\ext\misc\fuzzer.c \
  $(TOP)\ext\fts5\fts5_tcl.c \
  $(TOP)\ext\fts5\fts5_test_mi.c \
  $(TOP)\ext\fts5\fts5_test_tok.c \
  $(TOP)\ext\misc\ieee754.c \
  $(TOP)\ext\misc\mmapwarm.c \
  $(TOP)\ext\misc\nextchar.c \

  $(TOP)\ext\misc\percentile.c \
  $(TOP)\ext\misc\regexp.c \
  $(TOP)\ext\misc\remember.c \
  $(TOP)\ext\misc\series.c \
  $(TOP)\ext\misc\spellfix.c \
  $(TOP)\ext\misc\totype.c \
  $(TOP)\ext\misc\unionvtab.c \







>







1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
  $(TOP)\ext\misc\fuzzer.c \
  $(TOP)\ext\fts5\fts5_tcl.c \
  $(TOP)\ext\fts5\fts5_test_mi.c \
  $(TOP)\ext\fts5\fts5_test_tok.c \
  $(TOP)\ext\misc\ieee754.c \
  $(TOP)\ext\misc\mmapwarm.c \
  $(TOP)\ext\misc\nextchar.c \
  $(TOP)\ext\misc\normalize.c \
  $(TOP)\ext\misc\percentile.c \
  $(TOP)\ext\misc\regexp.c \
  $(TOP)\ext\misc\remember.c \
  $(TOP)\ext\misc\series.c \
  $(TOP)\ext\misc\spellfix.c \
  $(TOP)\ext\misc\totype.c \
  $(TOP)\ext\misc\unionvtab.c \

Changes to ext/misc/normalize.c.

126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
...
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
/* An array to map all upper-case characters into their corresponding
** lower-case character. 
**
** SQLite only considers US-ASCII (or EBCDIC) characters.  We do not
** handle case conversions for the UTF character set since the tables
** involved are nearly as big or bigger than SQLite itself.
*/
const unsigned char sqlite3UpperToLower[] = {
      0,  1,  2,  3,  4,  5,  6,  7,  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, 33, 34, 35,
     36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53,
     54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 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, 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,
................................................................................
** array. tolower() is used more often than toupper() by SQLite.
**
** Bit 0x40 is set if the character is non-alphanumeric and can be used in an 
** SQLite identifier.  Identifiers are alphanumerics, "_", "$", and any
** non-ASCII UTF character. Hence the test for whether or not a character is
** part of an identifier is 0x46.
*/
const unsigned char sqlite3CtypeMap[256] = {
  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,  /* 00..07    ........ */
  0x00, 0x01, 0x01, 0x01, 0x01, 0x01, 0x00, 0x00,  /* 08..0f    ........ */
  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,  /* 10..17    ........ */
  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,  /* 18..1f    ........ */
  0x01, 0x00, 0x80, 0x00, 0x40, 0x00, 0x00, 0x80,  /* 20..27     !"#$%&' */
  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,  /* 28..2f    ()*+,-./ */
  0x0c, 0x0c, 0x0c, 0x0c, 0x0c, 0x0c, 0x0c, 0x0c,  /* 30..37    01234567 */







|







 







|







126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
...
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
/* An array to map all upper-case characters into their corresponding
** lower-case character. 
**
** SQLite only considers US-ASCII (or EBCDIC) characters.  We do not
** handle case conversions for the UTF character set since the tables
** involved are nearly as big or bigger than SQLite itself.
*/
static const unsigned char sqlite3UpperToLower[] = {
      0,  1,  2,  3,  4,  5,  6,  7,  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, 33, 34, 35,
     36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53,
     54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 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, 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,
................................................................................
** array. tolower() is used more often than toupper() by SQLite.
**
** Bit 0x40 is set if the character is non-alphanumeric and can be used in an 
** SQLite identifier.  Identifiers are alphanumerics, "_", "$", and any
** non-ASCII UTF character. Hence the test for whether or not a character is
** part of an identifier is 0x46.
*/
static const unsigned char sqlite3CtypeMap[256] = {
  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,  /* 00..07    ........ */
  0x00, 0x01, 0x01, 0x01, 0x01, 0x01, 0x00, 0x00,  /* 08..0f    ........ */
  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,  /* 10..17    ........ */
  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,  /* 18..1f    ........ */
  0x01, 0x00, 0x80, 0x00, 0x40, 0x00, 0x00, 0x80,  /* 20..27     !"#$%&' */
  0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,  /* 28..2f    ()*+,-./ */
  0x0c, 0x0c, 0x0c, 0x0c, 0x0c, 0x0c, 0x0c, 0x0c,  /* 30..37    01234567 */

Changes to main.mk.

357
358
359
360
361
362
363

364
365
366
367
368
369
370
  $(TOP)/ext/misc/csv.c \
  $(TOP)/ext/misc/eval.c \
  $(TOP)/ext/misc/fileio.c \
  $(TOP)/ext/misc/fuzzer.c \
  $(TOP)/ext/misc/ieee754.c \
  $(TOP)/ext/misc/mmapwarm.c \
  $(TOP)/ext/misc/nextchar.c \

  $(TOP)/ext/misc/percentile.c \
  $(TOP)/ext/misc/regexp.c \
  $(TOP)/ext/misc/remember.c \
  $(TOP)/ext/misc/series.c \
  $(TOP)/ext/misc/spellfix.c \
  $(TOP)/ext/misc/totype.c \
  $(TOP)/ext/misc/unionvtab.c \







>







357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
  $(TOP)/ext/misc/csv.c \
  $(TOP)/ext/misc/eval.c \
  $(TOP)/ext/misc/fileio.c \
  $(TOP)/ext/misc/fuzzer.c \
  $(TOP)/ext/misc/ieee754.c \
  $(TOP)/ext/misc/mmapwarm.c \
  $(TOP)/ext/misc/nextchar.c \
  $(TOP)/ext/misc/normalize.c \
  $(TOP)/ext/misc/percentile.c \
  $(TOP)/ext/misc/regexp.c \
  $(TOP)/ext/misc/remember.c \
  $(TOP)/ext/misc/series.c \
  $(TOP)/ext/misc/spellfix.c \
  $(TOP)/ext/misc/totype.c \
  $(TOP)/ext/misc/unionvtab.c \

Changes to src/test1.c.

4554
4555
4556
4557
4558
4559
4560





























4561
4562
4563
4564
4565
4566
4567
....
7543
7544
7545
7546
7547
7548
7549

7550
7551
7552
7553
7554
7555
7556
  }

  zBuf = (char*)Tcl_GetByteArrayFromObj(objv[1], 0);
  Tcl_SetObjResult(interp, Tcl_NewIntObj(sqlite3_complete16(zBuf)));
#endif /* SQLITE_OMIT_COMPLETE && SQLITE_OMIT_UTF16 */
  return TCL_OK;
}






























/*
** Usage: sqlite3_step STMT
**
** Advance the statement to the next row.
*/
static int SQLITE_TCLAPI test_step(
................................................................................
     { "sqlite3_extended_errcode",      test_ex_errcode    ,0 },
     { "sqlite3_errmsg",                test_errmsg        ,0 },
     { "sqlite3_errmsg16",              test_errmsg16      ,0 },
     { "sqlite3_open",                  test_open          ,0 },
     { "sqlite3_open16",                test_open16        ,0 },
     { "sqlite3_open_v2",               test_open_v2       ,0 },
     { "sqlite3_complete16",            test_complete16    ,0 },


     { "sqlite3_prepare",               test_prepare       ,0 },
     { "sqlite3_prepare16",             test_prepare16     ,0 },
     { "sqlite3_prepare_v2",            test_prepare_v2    ,0 },
     { "sqlite3_prepare_tkt3134",       test_prepare_tkt3134, 0},
     { "sqlite3_prepare16_v2",          test_prepare16_v2  ,0 },
     { "sqlite3_finalize",              test_finalize      ,0 },







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







 







>







4554
4555
4556
4557
4558
4559
4560
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
....
7572
7573
7574
7575
7576
7577
7578
7579
7580
7581
7582
7583
7584
7585
7586
  }

  zBuf = (char*)Tcl_GetByteArrayFromObj(objv[1], 0);
  Tcl_SetObjResult(interp, Tcl_NewIntObj(sqlite3_complete16(zBuf)));
#endif /* SQLITE_OMIT_COMPLETE && SQLITE_OMIT_UTF16 */
  return TCL_OK;
}

/*
** Usage: sqlite3_normalize SQL
**
** Return the normalized value for an SQL statement.
*/
static int SQLITE_TCLAPI test_normalize(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  char *zSql;
  char *zNorm;
  extern char *sqlite3_normalize(const char*);

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "SQL");
    return TCL_ERROR;
  }

  zSql = (char*)Tcl_GetString(objv[1]);
  zNorm = sqlite3_normalize(zSql);
  if( zNorm ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(zNorm, -1));
    sqlite3_free(zNorm);
  }
  return TCL_OK;
}

/*
** Usage: sqlite3_step STMT
**
** Advance the statement to the next row.
*/
static int SQLITE_TCLAPI test_step(
................................................................................
     { "sqlite3_extended_errcode",      test_ex_errcode    ,0 },
     { "sqlite3_errmsg",                test_errmsg        ,0 },
     { "sqlite3_errmsg16",              test_errmsg16      ,0 },
     { "sqlite3_open",                  test_open          ,0 },
     { "sqlite3_open16",                test_open16        ,0 },
     { "sqlite3_open_v2",               test_open_v2       ,0 },
     { "sqlite3_complete16",            test_complete16    ,0 },
     { "sqlite3_normalize",             test_normalize     ,0 },

     { "sqlite3_prepare",               test_prepare       ,0 },
     { "sqlite3_prepare16",             test_prepare16     ,0 },
     { "sqlite3_prepare_v2",            test_prepare_v2    ,0 },
     { "sqlite3_prepare_tkt3134",       test_prepare_tkt3134, 0},
     { "sqlite3_prepare16_v2",          test_prepare16_v2  ,0 },
     { "sqlite3_finalize",              test_finalize      ,0 },

Added test/normalize.test.

















































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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
# 2018-01-08
#
# 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.
#
#***********************************************************************
#
# Tests for the sqlite3_normalize() extension function.
#

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

foreach {tnum sql norm} {
  100
  {SELECT * FROM t1 WHERE a IN (1) AND b=51.42}
  {select*from t1 where a in(?,?,?)and b=?;}

  110
  {SELECT a, b+15, c FROM t1 WHERE d NOT IN (SELECT x FROM t2);}
  {select a,b+?,c from t1 where d not in(select x from t2);}

  120
  { SELECT NULL, b FROM t1 -- comment text
     WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */
                 SELECT a FROM t)
        OR e='hello';
  }
  {select?,b from t1 where d in(with t(a)as(values(?))select a from t)or e=?;}

  121
  {/*Initial comment*/
   -- another comment line
   SELECT NULL  /* comment */ , b FROM t1 -- comment text
     WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */
                 SELECT a FROM t)
        OR e='hello';
  }
  {select?,b from t1 where d in(with t(a)as(values(?))select a from t)or e=?;}

  130
  {/* Query containing parameters */
   SELECT x,$::abc(15),y,@abc,z,?99,w FROM t1 /* Trailing comment */}
  {select x,?,y,?,z,?,w from t1;}

  140
  {/* Long list on the RHS of IN */
   SELECT 15 IN (1,2,3,(SELECT * FROM t1),'xyz',x'abcd',22*(x+5),null);}
  {select?in(?,?,?);}

  150
  {SELECT x'abc'; -- illegal token}
  {}

  160
  {SELECT a,NULL,b FROM t1 WHERE c IS NOT NULL or D is null or e=5}
  {select a,?,b from t1 where c is not null or d is null or e=?;}

  170
  {/* IN list exactly 5 bytes long */
   SELECT * FROM t1 WHERE x IN (1,2,3);}
  {select*from t1 where x in(?,?,?);}
} {
  do_test $tnum [list sqlite3_normalize $sql] $norm
}

finish_test