SQLite: Check-in [540014ef]

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 Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

          ** sqlite_version() that might change over time cannot be used
          ** in an index. */
          notValid(pParse, pNC, "non-deterministic functions",
                   NC_IdxExpr|NC_PartIdx);
        }
      }


#ifndef SQLITE_OMIT_WINDOWFUNC
      assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
          || (pDef->xValue==0 && pDef->xInverse==0)
          || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize)
      );
      if( pDef && pDef->xValue==0 && pExpr->pWin ){
        sqlite3ErrorMsg(pParse, 
            "%.*s() may not be used as a window function", nId, zId
        );
        pNC->nErr++;
      }else if( 
            (is_agg && (pNC->ncFlags & NC_AllowAgg)==0)
         || (is_agg && (pDef->funcFlags & SQLITE_FUNC_WINDOW) && !pExpr->pWin)
         || (is_agg && pExpr->pWin && (pNC->ncFlags & NC_AllowWin)==0)
      ){
        const char *zType;
        if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pExpr->pWin ){
          zType = "window";
        }else{
          zType = "aggregate";
        }
        sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()", zType, nId,zId);
        pNC->nErr++;
        is_agg = 0;
      }
#else
      if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){
        sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId);
        pNC->nErr++;
        is_agg = 0;
      }
#endif
      else if( no_such_func && pParse->db->init.busy==0
#ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
                && pParse->explain==0
#endif
      ){
        sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId);
        pNC->nErr++;
      }else if( wrong_num_args ){
        sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()",
             nId, zId);
        pNC->nErr++;
      }
      if( is_agg ){
#ifndef SQLITE_OMIT_WINDOWFUNC
        pNC->ncFlags &= ~(pExpr->pWin ? NC_AllowWin : NC_AllowAgg);
#else
        pNC->ncFlags &= ~NC_AllowAgg;
#endif

      }
      sqlite3WalkExprList(pWalker, pList);
      if( is_agg ){
#ifndef SQLITE_OMIT_WINDOWFUNC
        if( pExpr->pWin ){
          Select *pSel = pNC->pWinSelect;
          sqlite3WalkExprList(pWalker, pExpr->pWin->pPartition);








>

|


|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|

|

|

|
|
|
|
|
|
|
|
|

|

|

>

          ** sqlite_version() that might change over time cannot be used
          ** in an index. */
          notValid(pParse, pNC, "non-deterministic functions",
                   NC_IdxExpr|NC_PartIdx);
        }
      }

      if( 0==IN_RENAME_COLUMN ){
#ifndef SQLITE_OMIT_WINDOWFUNC
        assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX)
          || (pDef->xValue==0 && pDef->xInverse==0)
          || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize)
        );
        if( pDef && pDef->xValue==0 && pExpr->pWin ){
          sqlite3ErrorMsg(pParse, 
              "%.*s() may not be used as a window function", nId, zId
          );
          pNC->nErr++;
        }else if( 
              (is_agg && (pNC->ncFlags & NC_AllowAgg)==0)
           || (is_agg && (pDef->funcFlags & SQLITE_FUNC_WINDOW) && !pExpr->pWin)
           || (is_agg && pExpr->pWin && (pNC->ncFlags & NC_AllowWin)==0)
        ){
          const char *zType;
          if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pExpr->pWin ){
            zType = "window";
          }else{
            zType = "aggregate";
          }
          sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
          pNC->nErr++;
          is_agg = 0;
        }
#else
        if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){
          sqlite3ErrorMsg(pParse,"misuse of aggregate function %.*s()",nId,zId);
          pNC->nErr++;
          is_agg = 0;
        }
#endif
        else if( no_such_func && pParse->db->init.busy==0 && !IN_RENAME_COLUMN
#ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
                  && pParse->explain==0
#endif
        ){
          sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId);
          pNC->nErr++;
        }else if( wrong_num_args ){
          sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()",
               nId, zId);
          pNC->nErr++;
        }
        if( is_agg ){
#ifndef SQLITE_OMIT_WINDOWFUNC
          pNC->ncFlags &= ~(pExpr->pWin ? NC_AllowWin : NC_AllowAgg);
#else
          pNC->ncFlags &= ~NC_AllowAgg;
#endif
        }
      }
      sqlite3WalkExprList(pWalker, pList);
      if( is_agg ){
#ifndef SQLITE_OMIT_WINDOWFUNC
        if( pExpr->pWin ){
          Select *pSel = pNC->pWinSelect;
          sqlite3WalkExprList(pWalker, pExpr->pWin->pPartition);

Changes to test/altercol.test.

  ALTER TABLE b1 RENAME a TO aaa;
  SELECT sql FROM sqlite_master WHERE name = 'zzz'
} {{CREATE VIEW zzz AS SELECT george, ringo FROM b1}}

#-------------------------------------------------------------------------
# More triggers.
#




















foreach {tn old new lSchema} {
  1 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE TRIGGER AFTER INSERT ON t1 BEGIN
        SELECT _x_ FROM t1;
      END }
  }
................................................................................
    { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
    { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
        INSERT INTO t1 VALUES(new.a, new.b, new._x_)
          ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1;
      END }
  }
} {







  reset_db
  set lSorted [list]
  foreach sql $lSchema { 
    execsql $sql 
    lappend lSorted [string trim $sql]
  }
  set lSorted [lsort $lSorted]





  do_execsql_test 9.$tn.1 {
    SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1
  } $lSorted

if { $tn==3 } breakpoint
  do_execsql_test 9.$tn.2 "ALTER TABLE t1 RENAME $old TO $new"




  do_execsql_test 9.$tn.1 {
    SELECT sql FROM sqlite_master ORDER BY 1
  } [string map [list $old $new] $lSorted]
}




reset_db





finish_test








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







 







>
>
>
>
>
>
>
|
<
<
<
<
|
<
>
>
>
>
|
<
<
<

<
<
>
>
>
|
<
<
<
|
>
>
>
|
<
>
>
|
>
>

  ALTER TABLE b1 RENAME a TO aaa;
  SELECT sql FROM sqlite_master WHERE name = 'zzz'
} {{CREATE VIEW zzz AS SELECT george, ringo FROM b1}}

#-------------------------------------------------------------------------
# More triggers.
#
proc do_rename_column_test {tn old new lSchema} {
  reset_db
  set lSorted [list]
  foreach sql $lSchema { 
    execsql $sql 
    lappend lSorted [string trim $sql]
  }
  set lSorted [lsort $lSorted]

  do_execsql_test $tn.1 {
    SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1
  } $lSorted

  do_execsql_test $tn.2 "ALTER TABLE t1 RENAME $old TO $new"

  do_execsql_test $tn.3 {
    SELECT sql FROM sqlite_master ORDER BY 1
  } [string map [list $old $new] $lSorted]
}

foreach {tn old new lSchema} {
  1 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE TRIGGER AFTER INSERT ON t1 BEGIN
        SELECT _x_ FROM t1;
      END }
  }
................................................................................
    { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
    { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
        INSERT INTO t1 VALUES(new.a, new.b, new._x_)
          ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1;
      END }
  }
} {
  do_rename_column_test 9.$tn $old $new $lSchema
}

#-------------------------------------------------------------------------
# Test that views can be edited even if there are missing collation 
# sequences or user defined functions.
#
reset_db






foreach {tn old new lSchema} {
  1 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE _x_='abc' COLLATE xyz }
  }






  2 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE scalar(_x_) }
  }




  3 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE _x_ = unicode(1, 2, 3) }
  }

} {
  do_rename_column_test 10.$tn $old $new $lSchema
}


finish_test