/ Check-in [80889306]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add the built-in affinity() SQL function. This turns out to be not as useful as originally envisioned, so abandon it on a branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | affinity-func
Files: files | file ages | folders
SHA1: 80889306bf57b886a248b880bc90197b8975a666
User & Date: drh 2015-06-02 16:09:58
Context
2015-06-02
16:09
Add the built-in affinity() SQL function. This turns out to be not as useful as originally envisioned, so abandon it on a branch. Leaf check-in: 80889306 user: drh tags: affinity-func
15:32
Rename SQLITE_AFF_NONE to SQLITE_AFF_BLOB. Closed-Leaf check-in: bce3f041 user: drh tags: blob-affinity-rename
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

2869
2870
2871
2872
2873
2874
2875
















2876
2877
2878
2879
2880
2881
2882
      ** of the first argument.
      */
      if( pDef->funcFlags & SQLITE_FUNC_UNLIKELY ){
        assert( nFarg>=1 );
        sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
        break;
      }

















      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
          testcase( i==31 );
          constMask |= MASKBIT32(i);
        }
        if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){







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







2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
      ** of the first argument.
      */
      if( pDef->funcFlags & SQLITE_FUNC_UNLIKELY ){
        assert( nFarg>=1 );
        sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
        break;
      }

      /* The AFFINITY() function returns the type affinity of its argument.
      ** The type affinity is a compile-time value, so we might as well
      ** code it directly.
      */
      if( pDef->funcFlags & SQLITE_FUNC_AFFINITY ){
        int aff;
        static const char *const azAffName[] = {
           "", "BLOB", "TEXT", "NUMERIC", "INTEGER", "REAL"
        };
        assert( nFarg==1 );
        aff = sqlite3ExprAffinity(pFarg->a[0].pExpr);
        if( aff ) aff -= SQLITE_AFF_BLOB - 1;
        sqlite3VdbeAddOp4(v, OP_String8, 0, target, 0, azAffName[aff], 0);
        break;
      }

      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
          testcase( i==31 );
          constMask |= MASKBIT32(i);
        }
        if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){

Changes to src/func.c.

1713
1714
1715
1716
1717
1718
1719

1720
1721
1722
1723
1724
1725
1726
    AGGREGATE2(min,              1, 0, 1, minmaxStep,      minMaxFinalize,
                                          SQLITE_FUNC_MINMAX ),
    FUNCTION(max,               -1, 1, 1, minmaxFunc       ),
    FUNCTION(max,                0, 1, 1, 0                ),
    AGGREGATE2(max,              1, 1, 1, minmaxStep,      minMaxFinalize,
                                          SQLITE_FUNC_MINMAX ),
    FUNCTION2(typeof,            1, 0, 0, typeofFunc,  SQLITE_FUNC_TYPEOF),

    FUNCTION2(length,            1, 0, 0, lengthFunc,  SQLITE_FUNC_LENGTH),
    FUNCTION(instr,              2, 0, 0, instrFunc        ),
    FUNCTION(substr,             2, 0, 0, substrFunc       ),
    FUNCTION(substr,             3, 0, 0, substrFunc       ),
    FUNCTION(printf,            -1, 0, 0, printfFunc       ),
    FUNCTION(unicode,            1, 0, 0, unicodeFunc      ),
    FUNCTION(char,              -1, 0, 0, charFunc         ),







>







1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
    AGGREGATE2(min,              1, 0, 1, minmaxStep,      minMaxFinalize,
                                          SQLITE_FUNC_MINMAX ),
    FUNCTION(max,               -1, 1, 1, minmaxFunc       ),
    FUNCTION(max,                0, 1, 1, 0                ),
    AGGREGATE2(max,              1, 1, 1, minmaxStep,      minMaxFinalize,
                                          SQLITE_FUNC_MINMAX ),
    FUNCTION2(typeof,            1, 0, 0, typeofFunc,  SQLITE_FUNC_TYPEOF),
    FUNCTION2(affinity,          1, 0, 0, noopFunc,    SQLITE_FUNC_AFFINITY),
    FUNCTION2(length,            1, 0, 0, lengthFunc,  SQLITE_FUNC_LENGTH),
    FUNCTION(instr,              2, 0, 0, instrFunc        ),
    FUNCTION(substr,             2, 0, 0, substrFunc       ),
    FUNCTION(substr,             3, 0, 0, substrFunc       ),
    FUNCTION(printf,            -1, 0, 0, printfFunc       ),
    FUNCTION(unicode,            1, 0, 0, unicodeFunc      ),
    FUNCTION(char,              -1, 0, 0, charFunc         ),

Changes to src/sqliteInt.h.

1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362

1363
1364
1365
1366
1367
1368
1369
};

/*
** Possible values for FuncDef.flags.  Note that the _LENGTH and _TYPEOF
** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG.  There
** are assert() statements in the code to verify this.
*/
#define SQLITE_FUNC_ENCMASK  0x003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */
#define SQLITE_FUNC_LIKE     0x004 /* Candidate for the LIKE optimization */
#define SQLITE_FUNC_CASE     0x008 /* Case-sensitive LIKE-type function */
#define SQLITE_FUNC_EPHEM    0x010 /* Ephemeral.  Delete with VDBE */
#define SQLITE_FUNC_NEEDCOLL 0x020 /* sqlite3GetFuncCollSeq() might be called */
#define SQLITE_FUNC_LENGTH   0x040 /* Built-in length() function */
#define SQLITE_FUNC_TYPEOF   0x080 /* Built-in typeof() function */
#define SQLITE_FUNC_COUNT    0x100 /* Built-in count(*) aggregate */
#define SQLITE_FUNC_COALESCE 0x200 /* Built-in coalesce() or ifnull() */
#define SQLITE_FUNC_UNLIKELY 0x400 /* Built-in unlikely() function */
#define SQLITE_FUNC_CONSTANT 0x800 /* Constant inputs give a constant output */
#define SQLITE_FUNC_MINMAX  0x1000 /* True for min() and max() aggregates */


/*
** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
** used to create the initializers for the FuncDef structures.
**
**   FUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Used to create a scalar function definition of a function zName 







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







1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
};

/*
** Possible values for FuncDef.flags.  Note that the _LENGTH and _TYPEOF
** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG.  There
** are assert() statements in the code to verify this.
*/
#define SQLITE_FUNC_ENCMASK  0x0003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */
#define SQLITE_FUNC_LIKE     0x0004 /* Candidate for the LIKE optimization */
#define SQLITE_FUNC_CASE     0x0008 /* Case-sensitive LIKE-type function */
#define SQLITE_FUNC_EPHEM    0x0010 /* Ephemeral.  Delete with VDBE */
#define SQLITE_FUNC_NEEDCOLL 0x0020 /* sqlite3GetFuncCollSeq might be called */
#define SQLITE_FUNC_LENGTH   0x0040 /* Built-in length() function */
#define SQLITE_FUNC_TYPEOF   0x0080 /* Built-in typeof() function */
#define SQLITE_FUNC_COUNT    0x0100 /* Built-in count(*) aggregate */
#define SQLITE_FUNC_COALESCE 0x0200 /* Built-in coalesce() or ifnull() */
#define SQLITE_FUNC_UNLIKELY 0x0400 /* Built-in unlikely() function */
#define SQLITE_FUNC_CONSTANT 0x0800 /* Constant inputs give a constant output */
#define SQLITE_FUNC_MINMAX   0x1000 /* True for min() and max() aggregates */
#define SQLITE_FUNC_AFFINITY 0x2000 /* Built-in affinity() function */

/*
** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
** used to create the initializers for the FuncDef structures.
**
**   FUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Used to create a scalar function definition of a function zName 

Added test/affinity1.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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
# 2015-06-02
#
# 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 type affinity and the affinity() built-in
# function.
#

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

do_execsql_test 100 {
  CREATE TABLE t1(
    c01 INT,
    c02 INTEGER,
    c03 TINYINT,
    c04 SMALLINT,
    c05 MEDIUMINT, 
    c06 BIGINT,
    c07 UNSIGNED BIG INT,
    c08 INT2,
    c09 INT4,
    c10 CHARACTER(20),
    c11 VARCHAR(255),
    c12 VARYING CHARACTER(255),
    c13 NCHAR(55),
    c14 NATIVE CHARACTER(70),
    c15 NVARCHAR(100),
    c16 TEXT,
    c17 CLOB,
    c20 BLOB,
    c21,
    c30 REAL,
    c31 DOUBLE,
    c32 DOUBLE PRECISION,
    c33 FLOAT,
    c40 NUMERIC,
    c41 DECIMAL(10,5),
    c42 BOOLEAN,
    c43 DATE,
    c44 DATETIME,
    c50 FLOATING POINT,
    c60 STRING
  );
  INSERT INTO t1 DEFAULT VALUES;
} {}
do_execsql_test 201 {
  SELECT affinity(c01), affinity(c01+1), affinity(+c01) FROM t1;
} {INTEGER {} {}}
do_execsql_test 202 {SELECT affinity(c02) FROM t1} {INTEGER}
do_execsql_test 203 {SELECT affinity(c03) FROM t1} {INTEGER}
do_execsql_test 204 {SELECT affinity(c04) FROM t1} {INTEGER}
do_execsql_test 205 {SELECT affinity(c05) FROM t1} {INTEGER}
do_execsql_test 206 {SELECT affinity(c06) FROM t1} {INTEGER}
do_execsql_test 207 {SELECT affinity(c07) FROM t1} {INTEGER}
do_execsql_test 208 {SELECT affinity(c08) FROM t1} {INTEGER}
do_execsql_test 209 {SELECT affinity(c09) FROM t1} {INTEGER}
do_execsql_test 210 {SELECT affinity(c10) FROM t1} {TEXT}
do_execsql_test 211 {SELECT affinity(c11) FROM t1} {TEXT}
do_execsql_test 212 {SELECT affinity(c12) FROM t1} {TEXT}
do_execsql_test 213 {SELECT affinity(c13) FROM t1} {TEXT}
do_execsql_test 214 {SELECT affinity(c14) FROM t1} {TEXT}
do_execsql_test 215 {SELECT affinity(c15) FROM t1} {TEXT}
do_execsql_test 216 {SELECT affinity(c16) FROM t1} {TEXT}
do_execsql_test 220 {SELECT affinity(c20) FROM t1} {BLOB}
do_execsql_test 221 {SELECT affinity(c21) FROM t1} {BLOB}
do_execsql_test 230 {SELECT affinity(c30) FROM t1} {REAL}
do_execsql_test 231 {SELECT affinity(c31) FROM t1} {REAL}
do_execsql_test 232 {SELECT affinity(c32) FROM t1} {REAL}
do_execsql_test 233 {SELECT affinity(c33) FROM t1} {REAL}
do_execsql_test 240 {SELECT affinity(c40) FROM t1} {NUMERIC}
do_execsql_test 241 {SELECT affinity(c41) FROM t1} {NUMERIC}
do_execsql_test 242 {SELECT affinity(c42) FROM t1} {NUMERIC}
do_execsql_test 243 {SELECT affinity(c43) FROM t1} {NUMERIC}
do_execsql_test 244 {SELECT affinity(c44) FROM t1} {NUMERIC}
do_execsql_test 250 {SELECT affinity(c50) FROM t1} {INTEGER}
do_execsql_test 260 {SELECT affinity(c60) FROM t1} {NUMERIC}


finish_test