/ Check-in [540014ef]
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:Ensure that CREATE VIEW, TRIGGER or INDEX statements can be edited by ALTER TABLE RENAME COLUMN even if they use collation-sequences or user-defined-functions that are not available.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | edit-trigger-wrapper
Files: files | file ages | folders
SHA3-256: 540014efd6a048373313c6cd9413de10d5d7114daf537cf5999ccf3c5c3f9358
User & Date: dan 2018-08-17 17:18:16
Context
2018-08-17
18:08
Allow an ALTER TABLE RENAME COLUMN to proceed even if the schema contains a virtual table for which the module is unavailable. check-in: 7b72b236 user: dan tags: edit-trigger-wrapper
17:18
Ensure that CREATE VIEW, TRIGGER or INDEX statements can be edited by ALTER TABLE RENAME COLUMN even if they use collation-sequences or user-defined-functions that are not available. check-in: 540014ef user: dan tags: edit-trigger-wrapper
2018-08-16
19:49
Further progress on updating trigger programs as part of ALTER TABLE RENAME COLUMN. check-in: 3f47222b user: dan tags: edit-trigger-wrapper
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

   756    756             ** sqlite_version() that might change over time cannot be used
   757    757             ** in an index. */
   758    758             notValid(pParse, pNC, "non-deterministic functions",
   759    759                      NC_IdxExpr|NC_PartIdx);
   760    760           }
   761    761         }
   762    762   
          763  +      if( 0==IN_RENAME_COLUMN ){
   763    764   #ifndef SQLITE_OMIT_WINDOWFUNC
   764         -      assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
          765  +        assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
   765    766             || (pDef->xValue==0 && pDef->xInverse==0)
   766    767             || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize)
   767         -      );
   768         -      if( pDef && pDef->xValue==0 && pExpr->pWin ){
   769         -        sqlite3ErrorMsg(pParse, 
   770         -            "%.*s() may not be used as a window function", nId, zId
   771    768           );
   772         -        pNC->nErr++;
   773         -      }else if( 
   774         -            (is_agg && (pNC->ncFlags & NC_AllowAgg)==0)
   775         -         || (is_agg && (pDef->funcFlags & SQLITE_FUNC_WINDOW) && !pExpr->pWin)
   776         -         || (is_agg && pExpr->pWin && (pNC->ncFlags & NC_AllowWin)==0)
   777         -      ){
   778         -        const char *zType;
   779         -        if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pExpr->pWin ){
   780         -          zType = "window";
   781         -        }else{
   782         -          zType = "aggregate";
          769  +        if( pDef && pDef->xValue==0 && pExpr->pWin ){
          770  +          sqlite3ErrorMsg(pParse, 
          771  +              "%.*s() may not be used as a window function", nId, zId
          772  +          );
          773  +          pNC->nErr++;
          774  +        }else if( 
          775  +              (is_agg && (pNC->ncFlags & NC_AllowAgg)==0)
          776  +           || (is_agg && (pDef->funcFlags & SQLITE_FUNC_WINDOW) && !pExpr->pWin)
          777  +           || (is_agg && pExpr->pWin && (pNC->ncFlags & NC_AllowWin)==0)
          778  +        ){
          779  +          const char *zType;
          780  +          if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pExpr->pWin ){
          781  +            zType = "window";
          782  +          }else{
          783  +            zType = "aggregate";
          784  +          }
          785  +          sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
          786  +          pNC->nErr++;
          787  +          is_agg = 0;
   783    788           }
   784         -        sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()", zType, nId,zId);
   785         -        pNC->nErr++;
   786         -        is_agg = 0;
   787         -      }
   788    789   #else
   789         -      if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){
   790         -        sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId);
   791         -        pNC->nErr++;
   792         -        is_agg = 0;
   793         -      }
          790  +        if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){
          791  +          sqlite3ErrorMsg(pParse,"misuse of aggregate function %.*s()",nId,zId);
          792  +          pNC->nErr++;
          793  +          is_agg = 0;
          794  +        }
   794    795   #endif
   795         -      else if( no_such_func && pParse->db->init.busy==0
          796  +        else if( no_such_func && pParse->db->init.busy==0 && !IN_RENAME_COLUMN
   796    797   #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
   797         -                && pParse->explain==0
          798  +                  && pParse->explain==0
   798    799   #endif
   799         -      ){
   800         -        sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId);
   801         -        pNC->nErr++;
   802         -      }else if( wrong_num_args ){
   803         -        sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()",
   804         -             nId, zId);
   805         -        pNC->nErr++;
   806         -      }
   807         -      if( is_agg ){
          800  +        ){
          801  +          sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId);
          802  +          pNC->nErr++;
          803  +        }else if( wrong_num_args ){
          804  +          sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()",
          805  +               nId, zId);
          806  +          pNC->nErr++;
          807  +        }
          808  +        if( is_agg ){
   808    809   #ifndef SQLITE_OMIT_WINDOWFUNC
   809         -        pNC->ncFlags &= ~(pExpr->pWin ? NC_AllowWin : NC_AllowAgg);
          810  +          pNC->ncFlags &= ~(pExpr->pWin ? NC_AllowWin : NC_AllowAgg);
   810    811   #else
   811         -        pNC->ncFlags &= ~NC_AllowAgg;
          812  +          pNC->ncFlags &= ~NC_AllowAgg;
   812    813   #endif
          814  +        }
   813    815         }
   814    816         sqlite3WalkExprList(pWalker, pList);
   815    817         if( is_agg ){
   816    818   #ifndef SQLITE_OMIT_WINDOWFUNC
   817    819           if( pExpr->pWin ){
   818    820             Select *pSel = pNC->pWinSelect;
   819    821             sqlite3WalkExprList(pWalker, pExpr->pWin->pPartition);

Changes to test/altercol.test.

   329    329     ALTER TABLE b1 RENAME a TO aaa;
   330    330     SELECT sql FROM sqlite_master WHERE name = 'zzz'
   331    331   } {{CREATE VIEW zzz AS SELECT george, ringo FROM b1}}
   332    332   
   333    333   #-------------------------------------------------------------------------
   334    334   # More triggers.
   335    335   #
          336  +proc do_rename_column_test {tn old new lSchema} {
          337  +  reset_db
          338  +  set lSorted [list]
          339  +  foreach sql $lSchema { 
          340  +    execsql $sql 
          341  +    lappend lSorted [string trim $sql]
          342  +  }
          343  +  set lSorted [lsort $lSorted]
          344  +
          345  +  do_execsql_test $tn.1 {
          346  +    SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1
          347  +  } $lSorted
          348  +
          349  +  do_execsql_test $tn.2 "ALTER TABLE t1 RENAME $old TO $new"
          350  +
          351  +  do_execsql_test $tn.3 {
          352  +    SELECT sql FROM sqlite_master ORDER BY 1
          353  +  } [string map [list $old $new] $lSorted]
          354  +}
          355  +
   336    356   foreach {tn old new lSchema} {
   337    357     1 _x_ _xxx_ {
   338    358       { CREATE TABLE t1(a, b, _x_) }
   339    359       { CREATE TRIGGER AFTER INSERT ON t1 BEGIN
   340    360           SELECT _x_ FROM t1;
   341    361         END }
   342    362     }
................................................................................
   361    381       { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
   362    382       { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
   363    383           INSERT INTO t1 VALUES(new.a, new.b, new._x_)
   364    384             ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1;
   365    385         END }
   366    386     }
   367    387   } {
   368         -  reset_db
   369         -  set lSorted [list]
   370         -  foreach sql $lSchema { 
   371         -    execsql $sql 
   372         -    lappend lSorted [string trim $sql]
          388  +  do_rename_column_test 9.$tn $old $new $lSchema
          389  +}
          390  +
          391  +#-------------------------------------------------------------------------
          392  +# Test that views can be edited even if there are missing collation 
          393  +# sequences or user defined functions.
          394  +#
          395  +reset_db
          396  +
          397  +foreach {tn old new lSchema} {
          398  +  1 _x_ _xxx_ {
          399  +    { CREATE TABLE t1(a, b, _x_) }
          400  +    { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE _x_='abc' COLLATE xyz }
   373    401     }
   374         -  set lSorted [lsort $lSorted]
   375    402   
   376         -  do_execsql_test 9.$tn.1 {
   377         -    SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1
   378         -  } $lSorted
          403  +  2 _x_ _xxx_ {
          404  +    { CREATE TABLE t1(a, b, _x_) }
          405  +    { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE scalar(_x_) }
          406  +  }
   379    407   
   380         -if { $tn==3 } breakpoint
   381         -  do_execsql_test 9.$tn.2 "ALTER TABLE t1 RENAME $old TO $new"
   382         -
   383         -  do_execsql_test 9.$tn.1 {
   384         -    SELECT sql FROM sqlite_master ORDER BY 1
   385         -  } [string map [list $old $new] $lSorted]
          408  +  3 _x_ _xxx_ {
          409  +    { CREATE TABLE t1(a, b, _x_) }
          410  +    { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE _x_ = unicode(1, 2, 3) }
          411  +  }
          412  +} {
          413  +  do_rename_column_test 10.$tn $old $new $lSchema
   386    414   }
   387    415   
   388         -reset_db
   389    416   
   390    417   finish_test