SQLite

Check-in [540014efd6]
Login

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
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.131
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: 7b72b2360a 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: 540014efd6 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: 3f47222b6e user: dan tags: edit-trigger-wrapper)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/resolve.c.
756
757
758
759
760
761
762

763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812

813
814
815
816
817
818
819
          ** 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);







>

|


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

|
|
|
|
|

|

|

|
|
|
|
|
|
|
|
|

|

|

>







756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
          ** 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.
329
330
331
332
333
334
335




















336
337
338
339
340
341
342
  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 }
  }







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







329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
  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 }
  }
361
362
363
364
365
366
367







368
369
370
371
372



373
374
375
376


377
378
379
380

381

382

383
384
385
386
387
388
389
390
    { 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







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

<

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


<


381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397


398
399
400
401

402

403
404
405

406
407
408
409
410
411
412
413


414
415

416
417
    { 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