/ Check-in [923cfd53]
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:Improved interface to double-quoted string literal enabling/disabling.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | new-dbconfig-options
Files: files | file ages | folders
SHA3-256: 923cfd53fcff2fcb91530bf819d2ecb0eda3f6a27dae29c7460f9ce3a3ffce7b
User & Date: drh 2019-06-17 13:56:11
Context
2019-06-17
14:13
New sqlite3_db_config() options: SQLITE_DBCONFIG_LEGACY_ALTER_TABLE, SQLITE_DBCONFIG_DQS_DML, and SQLITE_DBCONFIG_DQS_DDL. check-in: a61db8ff user: drh tags: trunk
13:56
Improved interface to double-quoted string literal enabling/disabling. Closed-Leaf check-in: 923cfd53 user: drh tags: new-dbconfig-options
2019-06-14
21:25
Add SQLITE_DBCONFIG options LEGACY_ALTER_TABLE, NO_DQS_SCHEMA, and NO_DQS. check-in: 1fad2092 user: drh tags: new-dbconfig-options
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/main.c.

   842    842           { SQLITE_DBCONFIG_ENABLE_QPSG,           SQLITE_EnableQPSG     },
   843    843           { SQLITE_DBCONFIG_TRIGGER_EQP,           SQLITE_TriggerEQP     },
   844    844           { SQLITE_DBCONFIG_RESET_DATABASE,        SQLITE_ResetDatabase  },
   845    845           { SQLITE_DBCONFIG_DEFENSIVE,             SQLITE_Defensive      },
   846    846           { SQLITE_DBCONFIG_WRITABLE_SCHEMA,       SQLITE_WriteSchema|
   847    847                                                    SQLITE_NoSchemaError  },
   848    848           { SQLITE_DBCONFIG_LEGACY_ALTER_TABLE,    SQLITE_LegacyAlter    },
   849         -        { SQLITE_DBCONFIG_NO_DQS_SCHEMA,         SQLITE_NoDQSSchema    },
   850         -        { SQLITE_DBCONFIG_NO_DQS,                SQLITE_NoDQS          },
          849  +        { SQLITE_DBCONFIG_DQS_DDL,               SQLITE_DqsDDL         },
          850  +        { SQLITE_DBCONFIG_DQS_DML,               SQLITE_DqsDML         },
   851    851         };
   852    852         unsigned int i;
   853    853         rc = SQLITE_ERROR; /* IMP: R-42790-23372 */
   854    854         for(i=0; i<ArraySize(aFlagOp); i++){
   855    855           if( aFlagOp[i].op==op ){
   856    856             int onoff = va_arg(ap, int);
   857    857             int *pRes = va_arg(ap, int*);
................................................................................
  3070   3070     db->aLimit[SQLITE_LIMIT_WORKER_THREADS] = SQLITE_DEFAULT_WORKER_THREADS;
  3071   3071     db->autoCommit = 1;
  3072   3072     db->nextAutovac = -1;
  3073   3073     db->szMmap = sqlite3GlobalConfig.szMmap;
  3074   3074     db->nextPagesize = 0;
  3075   3075     db->nMaxSorterMmap = 0x7FFFFFFF;
  3076   3076     db->flags |= SQLITE_ShortColNames | SQLITE_EnableTrigger | SQLITE_CacheSpill
  3077         -                 | SQLITE_NoDQSSchema
         3077  +                 | SQLITE_DqsDML
  3078   3078   #if !defined(SQLITE_DEFAULT_AUTOMATIC_INDEX) || SQLITE_DEFAULT_AUTOMATIC_INDEX
  3079   3079                    | SQLITE_AutoIndex
  3080   3080   #endif
  3081   3081   #if SQLITE_DEFAULT_CKPTFULLFSYNC
  3082   3082                    | SQLITE_CkptFullFSync
  3083   3083   #endif
  3084   3084   #if SQLITE_DEFAULT_FILE_FORMAT<4

Changes to src/resolve.c.

   143    143     }
   144    144     zSpan += n+1;
   145    145     if( zCol && sqlite3StrICmp(zSpan, zCol)!=0 ){
   146    146       return 0;
   147    147     }
   148    148     return 1;
   149    149   }
          150  +
          151  +/*
          152  +** Return TRUE if the double-quoted string  mis-feature should be supported.
          153  +*/
          154  +static int areDoubleQuotedStringsEnabled(sqlite3 *db, NameContext *pTopNC){
          155  +  if( db->init.busy ) return 1;  /* Always support for legacy schemas */
          156  +  if( pTopNC->ncFlags & NC_IsDDL ){
          157  +    /* Currently parsing a DDL statement */
          158  +    if( sqlite3WritableSchema(db) && (db->flags & SQLITE_DqsDML)!=0 ){
          159  +      return 1;
          160  +    }
          161  +    return (db->flags & SQLITE_DqsDDL)!=0;
          162  +  }else{
          163  +    /* Currently parsing a DML statement */
          164  +    return (db->flags & SQLITE_DqsDML)!=0;
          165  +  }
          166  +}
   150    167   
   151    168   /*
   152    169   ** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
   153    170   ** that name in the set of source tables in pSrcList and make the pExpr 
   154    171   ** expression node refer back to that source column.  The following changes
   155    172   ** are made to pExpr:
   156    173   **
................................................................................
   472    489     ** pExpr.
   473    490     **
   474    491     ** Because no reference was made to outer contexts, the pNC->nRef
   475    492     ** fields are not changed in any context.
   476    493     */
   477    494     if( cnt==0 && zTab==0 ){
   478    495       assert( pExpr->op==TK_ID );
   479         -    if( ExprHasProperty(pExpr,EP_DblQuoted) 
   480         -     && 0==(pTopNC->ncFlags&NC_NoDblQStr)
   481         -     && 0==(db->flags & SQLITE_NoDQS)
          496  +    if( ExprHasProperty(pExpr,EP_DblQuoted)
          497  +     && areDoubleQuotedStringsEnabled(db, pTopNC)
   482    498       ){
   483    499         /* If a double-quoted identifier does not match any known column name,
   484    500         ** then treat it as a string.
   485    501         **
   486    502         ** This hack was added in the early days of SQLite in a misguided attempt
   487    503         ** to be compatible with MySQL 3.x, which used double-quotes for strings.
   488    504         ** I now sorely regret putting in this hack. The effect of this hack is
................................................................................
  1767   1783       sSrc.nSrc = 1;
  1768   1784       sSrc.a[0].zName = pTab->zName;
  1769   1785       sSrc.a[0].pTab = pTab;
  1770   1786       sSrc.a[0].iCursor = -1;
  1771   1787     }
  1772   1788     sNC.pParse = pParse;
  1773   1789     sNC.pSrcList = &sSrc;
  1774         -  sNC.ncFlags = type;
  1775         -  if( (pParse->db->flags & SQLITE_NoDQS)!=0
  1776         -   || (!pParse->db->init.busy
  1777         -        && !sqlite3WritableSchema(pParse->db)
  1778         -        && (pParse->db->flags & SQLITE_NoDQSSchema)!=0
  1779         -      )
  1780         -  ){
  1781         -    sNC.ncFlags |= NC_NoDblQStr;
  1782         -  }
         1790  +  sNC.ncFlags = type | NC_IsDDL;
  1783   1791     if( (rc = sqlite3ResolveExprNames(&sNC, pExpr))!=SQLITE_OK ) return rc;
  1784   1792     if( pList ) rc = sqlite3ResolveExprListNames(&sNC, pList);
  1785   1793     return rc;
  1786   1794   }

Changes to src/shell.c.in.

  7082   7082           { "no_ckpt_on_close",   SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE      },
  7083   7083           { "enable_qpsg",        SQLITE_DBCONFIG_ENABLE_QPSG           },
  7084   7084           { "trigger_eqp",        SQLITE_DBCONFIG_TRIGGER_EQP           },
  7085   7085           { "reset_database",     SQLITE_DBCONFIG_RESET_DATABASE        },
  7086   7086           { "defensive",          SQLITE_DBCONFIG_DEFENSIVE             },
  7087   7087           { "wriable_schema",     SQLITE_DBCONFIG_WRITABLE_SCHEMA       },
  7088   7088           { "legacy_alter_table", SQLITE_DBCONFIG_LEGACY_ALTER_TABLE    },
  7089         -        { "no_dqs_schema",      SQLITE_DBCONFIG_NO_DQS_SCHEMA         },
  7090         -        { "no_dqs",             SQLITE_DBCONFIG_NO_DQS                },
         7089  +        { "dqs_dml",            SQLITE_DBCONFIG_DQS_DML               },
         7090  +        { "dqs_ddl",            SQLITE_DBCONFIG_DQS_DDL               },
  7091   7091       };
  7092   7092       int ii, v;
  7093   7093       open_db(p, 0);
  7094   7094       for(ii=0; ii<ArraySize(aDbConfig); ii++){
  7095   7095         if( nArg>1 && strcmp(azArg[1], aDbConfig[ii].zName)!=0 ) continue;
  7096   7096         if( nArg>=3 ){
  7097   7097           sqlite3_db_config(p->db, aDbConfig[ii].op, booleanValue(azArg[2]), 0);

Changes to src/sqlite.h.in.

  2227   2227   ** the legacy behavior of the [ALTER TABLE RENAME] command such it
  2228   2228   ** behaves as it did prior to [version 3.24.0] (2018-06-04).  See the
  2229   2229   ** "Compatibility Notice" on the [ALTER TABLE RENAME documentation] for
  2230   2230   ** additional information. This feature can also be turned on and off
  2231   2231   ** using the [PRAGMA legacy_alter_table] statement.
  2232   2232   ** </dd>
  2233   2233   **
  2234         -** [[SQLITE_DBCONFIG_NO_DQS_SCHEMA]]
  2235         -** <dt>SQLITE_DBCONFIG_NO_DQS_SCHEMA</td>
  2236         -** <dd>The SQLITE_DBCONFIG_NO_DQS_SCHEMA option activates or deactivates
  2237         -** the legacy [double-quoted string literal] misfeature for DDL statements.
         2234  +** [[SQLITE_DBCONFIG_DQS_DML]]
         2235  +** <dt>SQLITE_DBCONFIG_DQS_DML</td>
         2236  +** <dd>The SQLITE_DBCONFIG_DQS_DML option activates or deactivates
         2237  +** the legacy [double-quoted string literal] misfeature for DML statement
         2238  +** only, that is DELETE, INSERT, SELECT, and UPDATE statements.
  2238   2239   ** </dd>
  2239   2240   **
  2240         -** [[SQLITE_DBCONFIG_NO_DQS]]
  2241         -** <dt>SQLITE_DBCONFIG_NO_DQS</td>
  2242         -** <dd>The SQLITE_DBCONFIG_NO_DQS option activates or deactivates
  2243         -** the legacy [double-quoted string literal] misfeature.  The
  2244         -** double-quoted string literal misfeature is on by default for
  2245         -** compatibility, though it might become off by default in some
  2246         -** future release of SQLite.
         2241  +** [[SQLITE_DBCONFIG_DQS_DDL]]
         2242  +** <dt>SQLITE_DBCONFIG_DQS_DDL</td>
         2243  +** <dd>The SQLITE_DBCONFIG_DQS option activates or deactivates
         2244  +** the legacy [double-quoted string literal] misfeature for DDL statements,
         2245  +** such as CREATE TABLE and CREATE INDEX.
  2247   2246   ** </dd>
  2248   2247   ** </dl>
  2249   2248   */
  2250   2249   #define SQLITE_DBCONFIG_MAINDBNAME            1000 /* const char* */
  2251   2250   #define SQLITE_DBCONFIG_LOOKASIDE             1001 /* void* int int */
  2252   2251   #define SQLITE_DBCONFIG_ENABLE_FKEY           1002 /* int int* */
  2253   2252   #define SQLITE_DBCONFIG_ENABLE_TRIGGER        1003 /* int int* */
................................................................................
  2256   2255   #define SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE      1006 /* int int* */
  2257   2256   #define SQLITE_DBCONFIG_ENABLE_QPSG           1007 /* int int* */
  2258   2257   #define SQLITE_DBCONFIG_TRIGGER_EQP           1008 /* int int* */
  2259   2258   #define SQLITE_DBCONFIG_RESET_DATABASE        1009 /* int int* */
  2260   2259   #define SQLITE_DBCONFIG_DEFENSIVE             1010 /* int int* */
  2261   2260   #define SQLITE_DBCONFIG_WRITABLE_SCHEMA       1011 /* int int* */
  2262   2261   #define SQLITE_DBCONFIG_LEGACY_ALTER_TABLE    1012 /* int int* */
  2263         -#define SQLITE_DBCONFIG_NO_DQS_SCHEMA         1013 /* int int* */
  2264         -#define SQLITE_DBCONFIG_NO_DQS                1014 /* int int* */
         2262  +#define SQLITE_DBCONFIG_DQS_DML               1013 /* int int* */
         2263  +#define SQLITE_DBCONFIG_DQS_DDL               1014 /* int int* */
  2265   2264   #define SQLITE_DBCONFIG_MAX                   1014 /* Largest DBCONFIG */
  2266   2265   
  2267   2266   /*
  2268   2267   ** CAPI3REF: Enable Or Disable Extended Result Codes
  2269   2268   ** METHOD: sqlite3
  2270   2269   **
  2271   2270   ** ^The sqlite3_extended_result_codes() routine enables or disables the

Changes to src/sqliteInt.h.

  1544   1544   #define SQLITE_Fts3Tokenizer  0x00400000  /* Enable fts3_tokenizer(2) */
  1545   1545   #define SQLITE_EnableQPSG     0x00800000  /* Query Planner Stability Guarantee*/
  1546   1546   #define SQLITE_TriggerEQP     0x01000000  /* Show trigger EXPLAIN QUERY PLAN */
  1547   1547   #define SQLITE_ResetDatabase  0x02000000  /* Reset the database */
  1548   1548   #define SQLITE_LegacyAlter    0x04000000  /* Legacy ALTER TABLE behaviour */
  1549   1549   #define SQLITE_NoSchemaError  0x08000000  /* Do not report schema parse errors*/
  1550   1550   #define SQLITE_Defensive      0x10000000  /* Input SQL is likely hostile */
  1551         -#define SQLITE_NoDQSSchema    0x20000000  /* No dbl-quote strings in CREATE */
  1552         -#define SQLITE_NoDQS          0x40000000  /* No double-quoted strings anywhere*/
         1551  +#define SQLITE_DqsDDL         0x20000000  /* dbl-quoted strings allowed in DDL*/
         1552  +#define SQLITE_DqsDML         0x40000000  /* dbl-quoted strings allowed in DML*/
  1553   1553   
  1554   1554   /* Flags used only if debugging */
  1555   1555   #define HI(X)  ((u64)(X)<<32)
  1556   1556   #ifdef SQLITE_DEBUG
  1557   1557   #define SQLITE_SqlTrace       HI(0x0001)  /* Debug print SQL as it executes */
  1558   1558   #define SQLITE_VdbeListing    HI(0x0002)  /* Debug listings of VDBE progs */
  1559   1559   #define SQLITE_VdbeTrace      HI(0x0004)  /* True to trace VDBE execution */
................................................................................
  2792   2792   #define NC_UEList    0x0080  /* True if uNC.pEList is used */
  2793   2793   #define NC_UAggInfo  0x0100  /* True if uNC.pAggInfo is used */
  2794   2794   #define NC_UUpsert   0x0200  /* True if uNC.pUpsert is used */
  2795   2795   #define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
  2796   2796   #define NC_Complex   0x2000  /* True if a function or subquery seen */
  2797   2797   #define NC_AllowWin  0x4000  /* Window functions are allowed here */
  2798   2798   #define NC_HasWin    0x8000  /* One or more window functions seen */
  2799         -#define NC_NoDblQStr 0x10000 /* Do not allow double-quoted string hack.
  2800         -                             ** Mnemonic: "NO DouBLe-Quoted STRings" */
         2799  +#define NC_IsDDL    0x10000  /* Resolving names in a CREATE statement */
  2801   2800   
  2802   2801   /*
  2803   2802   ** An instance of the following object describes a single ON CONFLICT
  2804   2803   ** clause in an upsert.
  2805   2804   **
  2806   2805   ** The pUpsertTarget field is only set if the ON CONFLICT clause includes
  2807   2806   ** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the

Changes to src/test1.c.

  7615   7615       { "NO_CKPT_ON_CLOSE",   SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE },
  7616   7616       { "QPSG",               SQLITE_DBCONFIG_ENABLE_QPSG },
  7617   7617       { "TRIGGER_EQP",        SQLITE_DBCONFIG_TRIGGER_EQP },
  7618   7618       { "RESET_DB",           SQLITE_DBCONFIG_RESET_DATABASE },
  7619   7619       { "DEFENSIVE",          SQLITE_DBCONFIG_DEFENSIVE },
  7620   7620       { "WRITABLE_SCHEMA",    SQLITE_DBCONFIG_WRITABLE_SCHEMA },
  7621   7621       { "LEGACY_ALTER_TABLE", SQLITE_DBCONFIG_LEGACY_ALTER_TABLE },
  7622         -    { "NO_DQS_SCHEMA",      SQLITE_DBCONFIG_NO_DQS_SCHEMA },
  7623         -    { "NO_DQS",             SQLITE_DBCONFIG_NO_DQS },
         7622  +    { "DQS_DML",            SQLITE_DBCONFIG_DQS_DML },
         7623  +    { "DQS_DDL",            SQLITE_DBCONFIG_DQS_DDL },
  7624   7624     };
  7625   7625     int i;
  7626   7626     int v;
  7627   7627     const char *zSetting;
  7628   7628     sqlite3 *db;
  7629   7629   
  7630   7630     if( objc!=4 ){