SQLite

Check-in [7b72b2360a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Allow an ALTER TABLE RENAME COLUMN to proceed even if the schema contains a virtual table for which the module is unavailable.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | edit-trigger-wrapper
Files: files | file ages | folders
SHA3-256: 7b72b2360a70eb1f788b3c4d745967dfedb2c80af6b2e146ba940a3741fd51a1
User & Date: dan 2018-08-17 18:08:28.458
Context
2018-08-18
17:35
Improvements to error handling in ALTER TABLE RENAME COLUMN. (Closed-Leaf check-in: 7fa1faeaff user: dan tags: edit-trigger-wrapper)
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)
Changes
Unified Diff Show Whitespace Changes Patch
Changes to src/alter.c.
︙︙
841
842
843
844
845
846
847
848

849
850
851
852
853
854
855

  zNew = sqlite3NameFromToken(db, pNew);
  if( !zNew ) goto exit_rename_column;
  assert( pNew->n>0 );
  bQuote = sqlite3Isquote(pNew->z[0]);
  sqlite3NestedParse(pParse, 
      "UPDATE \"%w\".%s SET "
      "sql = sqlite_rename_column(sql, %Q, %Q, %d, %Q, %d) "
      "WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)",

      zDb, MASTER_NAME, 
      zDb, pTab->zName, iCol, zNew, bQuote,
      pTab->zName
  );

  /* Drop and reload the database schema. */
  if( pParse->pVdbe ){








|
>








841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856

  zNew = sqlite3NameFromToken(db, pNew);
  if( !zNew ) goto exit_rename_column;
  assert( pNew->n>0 );
  bQuote = sqlite3Isquote(pNew->z[0]);
  sqlite3NestedParse(pParse, 
      "UPDATE \"%w\".%s SET "
      "sql = sqlite_rename_column(sql, %Q, %Q, %d, %Q, %d) "
      "WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)"
      " AND sql NOT LIKE 'create virtual%%'",
      zDb, MASTER_NAME, 
      zDb, pTab->zName, iCol, zNew, bQuote,
      pTab->zName
  );

  /* Drop and reload the database schema. */
  if( pParse->pVdbe ){
︙︙
Changes to test/altercol.test.
︙︙
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

  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 }








|
>
>
>
>
>
>
>







|



>
>
>
>
>
|

|



>








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
363
364
365
366
367
368
369
370
371
372
373
374

  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} {

  for {set i 0} {$i < 2} {incr i} {
    # DROP all tables and views in database.
    set sql "SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1"
    foreach nm [db eval $sql] { db eval "DROP TABLE $nm" }
    set sql "SELECT name FROM sqlite_master WHERE type='view' ORDER BY 1"
    foreach nm [db eval $sql] { db eval "DROP VIEW $nm" }

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

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

    if {0 && $i==1} {
      db close
      sqlite3 db test.db
    }

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

    do_execsql_test $tn.$i.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 }
︙︙
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

# 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








|











>
>
>
>
>

>






406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436

# sequences or user defined functions.
#
reset_db

foreach {tn old new lSchema} {
  1 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE VIEW s1 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) }
  }

  4 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE VIRTUAL TABLE e1 USING echo(t1) }
  }
} {
  register_echo_module db
  do_rename_column_test 10.$tn $old $new $lSchema
}


finish_test