From 6c92a7711e105f00e34fea74680f9792304f68d4 Mon Sep 17 00:00:00 2001 From: drh Date: Tue, 2 Jun 2015 16:09:58 +0000 Subject: [PATCH] Add the built-in affinity() SQL function. This turns out to be not as useful as originally envisioned, so abandon it on a branch. FossilOrigin-Name: 80889306bf57b886a248b880bc90197b8975a666 --- manifest | 23 ++++++------ manifest.uuid | 2 +- src/expr.c | 16 +++++++++ src/func.c | 1 + src/sqliteInt.h | 25 ++++++------- test/affinity1.test | 88 +++++++++++++++++++++++++++++++++++++++++++++ 6 files changed, 131 insertions(+), 24 deletions(-) create mode 100644 test/affinity1.test diff --git a/manifest b/manifest index 0403efeca2..b9a2fc8c19 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Rename\sSQLITE_AFF_NONE\sto\sSQLITE_AFF_BLOB. -D 2015-06-02T15:32:08.579 +C Add\sthe\sbuilt-in\saffinity()\sSQL\sfunction.\s\sThis\sturns\sout\sto\sbe\snot\sas\suseful\nas\soriginally\senvisioned,\sso\sabandon\sit\son\sa\sbranch. +D 2015-06-02T16:09:58.099 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 994bab32a3a69e0c35bd148b65cde49879772964 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -202,10 +202,10 @@ F src/ctime.c 5a0b735dc95604766f5dac73973658eef782ee8b F src/date.c e4d50b3283696836ec1036b695ead9a19e37a5ac F src/dbstat.c f402e77e25089c6003d0c60b3233b9b3947d599a F src/delete.c 37964e6c1d73ff49cbea9ff690c9605fb15f600e -F src/expr.c 52f5c1c2c16bf47234dc276d9f72b5ea85ae14af +F src/expr.c 5abe19af1c41dd8b334318611c6614cc21155b88 F src/fault.c 160a0c015b6c2629d3899ed2daf63d75754a32bb F src/fkey.c c9b63a217d86582c22121699a47f22f524608869 -F src/func.c 5b8b8e77a0fb644eaf8947d413804622e32692b6 +F src/func.c c215efa308b831f7ea147c7a6a03080c870caf8a F src/global.c 4f77cadbc5427d00139ba43d0f3979804cbb700e F src/hash.c 4263fbc955f26c2e8cdc0cf214bc42435aa4e4f5 F src/hash.h c8f3c31722cf3277d03713909761e152a5b81094 @@ -255,7 +255,7 @@ F src/shell.c 07dda7cd692911d2f22269953418d049f2e2c0ee F src/sqlite.h.in d165beeceb6b40af60f352a4d4e37e02d9af7df0 F src/sqlite3.rc 992c9f5fb8285ae285d6be28240a7e8d3a7f2bad F src/sqlite3ext.h 2ebeb634e751a61a6f0eebfa0f4669f46a42f6cd -F src/sqliteInt.h bcf51f6ec3ad67dbdf1acf78fcb94884af93c183 +F src/sqliteInt.h 98c49cbd565f08f2e516399734e497340ca9549c F src/sqliteLimit.h 216557999cb45f2e3578ed53ebefe228d779cb46 F src/status.c f266ad8a2892d659b74f0f50cb6a88b6e7c12179 F src/table.c 51b46b2a62d1b3a959633d593b89bab5e2c9155e @@ -329,6 +329,7 @@ F src/walker.c c253b95b4ee44b21c406e2a1052636c31ea27804 F src/where.c dcdfee81d35ae9261a4c5bda6289ed5fa6d7e1ae F src/whereInt.h a6f5a762bc1b4b1c76e1cea79976b437ac35a435 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 +F test/affinity1.test 3f68d94550f47bac467f916844d939c94882b557 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/aggnested.test b35b4cd69fc913f90d39a575e171e1116c3a4bb7 F test/alias.test 4529fbc152f190268a15f9384a5651bbbabc9d87 @@ -1281,10 +1282,10 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 4e621af1345a001360938de76e3b0a14deb5e991 -R ee13a8dd8e0014c6c36d8309e414bd72 -T *branch * blob-affinity-rename -T *sym-blob-affinity-rename * -T -sym-trunk * +P bce3f04186cd2d69414a5a98b5b77dc8f060554a +R 98be638f0f819b9a8a15b45777fdc534 +T *branch * affinity-func +T *sym-affinity-func * +T -sym-blob-affinity-rename * U drh -Z 3dc5f9f8176f1194f0510f7321cda212 +Z 13640b2e248aab3cf67f2fff3528da6a diff --git a/manifest.uuid b/manifest.uuid index 90256eb79a..c434fc94fb 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -bce3f04186cd2d69414a5a98b5b77dc8f060554a \ No newline at end of file +80889306bf57b886a248b880bc90197b8975a666 \ No newline at end of file diff --git a/src/expr.c b/src/expr.c index fa5715c04f..8fbce90a98 100644 --- a/src/expr.c +++ b/src/expr.c @@ -2874,6 +2874,22 @@ int sqlite3ExprCodeTarget(Parse *pParse, Expr *pExpr, int 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; ia[i].pExpr) ){ testcase( i==31 ); diff --git a/src/func.c b/src/func.c index 62abf13d4d..79e38577d0 100644 --- a/src/func.c +++ b/src/func.c @@ -1717,6 +1717,7 @@ void sqlite3RegisterGlobalFunctions(void){ 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 ), diff --git a/src/sqliteInt.h b/src/sqliteInt.h index a31f363708..725096ab09 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -1348,18 +1348,19 @@ struct FuncDestructor { ** 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 */ +#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 diff --git a/test/affinity1.test b/test/affinity1.test new file mode 100644 index 0000000000..0e91718f2c --- /dev/null +++ b/test/affinity1.test @@ -0,0 +1,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 -- 2.39.5