/ Hex Artifact Content
Login

Artifact 7ea05c7d92ac99349a802ef7ada17294dd647060:


0000: 23 20 32 30 30 35 20 46 65 62 72 75 61 72 79 20  # 2005 February 
0010: 31 38 0a 23 0a 23 20 54 68 65 20 61 75 74 68 6f  18.#.# The autho
0020: 72 20 64 69 73 63 6c 61 69 6d 73 20 63 6f 70 79  r disclaims copy
0030: 72 69 67 68 74 20 74 6f 20 74 68 69 73 20 73 6f  right to this so
0040: 75 72 63 65 20 63 6f 64 65 2e 20 20 49 6e 20 70  urce code.  In p
0050: 6c 61 63 65 20 6f 66 0a 23 20 61 20 6c 65 67 61  lace of.# a lega
0060: 6c 20 6e 6f 74 69 63 65 2c 20 68 65 72 65 20 69  l notice, here i
0070: 73 20 61 20 62 6c 65 73 73 69 6e 67 3a 0a 23 0a  s a blessing:.#.
0080: 23 20 20 20 20 4d 61 79 20 79 6f 75 20 64 6f 20  #    May you do 
0090: 67 6f 6f 64 20 61 6e 64 20 6e 6f 74 20 65 76 69  good and not evi
00a0: 6c 2e 0a 23 20 20 20 20 4d 61 79 20 79 6f 75 20  l..#    May you 
00b0: 66 69 6e 64 20 66 6f 72 67 69 76 65 6e 65 73 73  find forgiveness
00c0: 20 66 6f 72 20 79 6f 75 72 73 65 6c 66 20 61 6e   for yourself an
00d0: 64 20 66 6f 72 67 69 76 65 20 6f 74 68 65 72 73  d forgive others
00e0: 2e 0a 23 20 20 20 20 4d 61 79 20 79 6f 75 20 73  ..#    May you s
00f0: 68 61 72 65 20 66 72 65 65 6c 79 2c 20 6e 65 76  hare freely, nev
0100: 65 72 20 74 61 6b 69 6e 67 20 6d 6f 72 65 20 74  er taking more t
0110: 68 61 6e 20 79 6f 75 20 67 69 76 65 2e 0a 23 0a  han you give..#.
0120: 23 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  #***************
0130: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0140: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0150: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0160: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 0a 23 20 54 68 69  **********.# Thi
0170: 73 20 66 69 6c 65 20 69 6d 70 6c 65 6d 65 6e 74  s file implement
0180: 73 20 72 65 67 72 65 73 73 69 6f 6e 20 74 65 73  s regression tes
0190: 74 73 20 66 6f 72 20 53 51 4c 69 74 65 20 6c 69  ts for SQLite li
01a0: 62 72 61 72 79 2e 20 20 54 68 65 0a 23 20 66 6f  brary.  The.# fo
01b0: 63 75 73 20 6f 66 20 74 68 69 73 20 73 63 72 69  cus of this scri
01c0: 70 74 20 69 73 20 74 65 73 74 69 6e 67 20 74 68  pt is testing th
01d0: 61 74 20 53 51 4c 69 74 65 20 63 61 6e 20 68 61  at SQLite can ha
01e0: 6e 64 6c 65 20 61 20 73 75 62 74 6c 65 20 0a 23  ndle a subtle .#
01f0: 20 66 69 6c 65 20 66 6f 72 6d 61 74 20 63 68 61   file format cha
0200: 6e 67 65 20 74 68 61 74 20 6d 61 79 20 62 65 20  nge that may be 
0210: 75 73 65 64 20 69 6e 20 74 68 65 20 66 75 74 75  used in the futu
0220: 72 65 20 74 6f 20 69 6d 70 6c 65 6d 65 6e 74 0a  re to implement.
0230: 23 20 22 41 4c 54 45 52 20 54 41 42 4c 45 20 2e  # "ALTER TABLE .
0240: 2e 2e 20 41 44 44 20 43 4f 4c 55 4d 4e 22 2e 0a  .. ADD COLUMN"..
0250: 23 0a 23 20 24 49 64 3a 20 61 6c 74 65 72 32 2e  #.# $Id: alter2.
0260: 74 65 73 74 2c 76 20 31 2e 31 34 20 32 30 30 39  test,v 1.14 2009
0270: 2f 30 34 2f 30 37 20 31 34 3a 31 34 3a 32 32 20  /04/07 14:14:22 
0280: 64 61 6e 69 65 6c 6b 31 39 37 37 20 45 78 70 20  danielk1977 Exp 
0290: 24 0a 23 0a 0a 73 65 74 20 74 65 73 74 64 69 72  $.#..set testdir
02a0: 20 5b 66 69 6c 65 20 64 69 72 6e 61 6d 65 20 24   [file dirname $
02b0: 61 72 67 76 30 5d 0a 73 6f 75 72 63 65 20 24 74  argv0].source $t
02c0: 65 73 74 64 69 72 2f 74 65 73 74 65 72 2e 74 63  estdir/tester.tc
02d0: 6c 0a 0a 23 20 57 65 20 68 61 76 65 20 74 6f 20  l..# We have to 
02e0: 68 61 76 65 20 70 72 61 67 6d 61 73 20 69 6e 20  have pragmas in 
02f0: 6f 72 64 65 72 20 74 6f 20 64 6f 20 74 68 69 73  order to do this
0300: 20 74 65 73 74 0a 69 66 63 61 70 61 62 6c 65 20   test.ifcapable 
0310: 7b 21 70 72 61 67 6d 61 7d 20 72 65 74 75 72 6e  {!pragma} return
0320: 0a 0a 23 20 44 6f 20 6e 6f 74 20 75 73 65 20 61  ..# Do not use a
0330: 20 63 6f 64 65 63 20 66 6f 72 20 74 65 73 74 73   codec for tests
0340: 20 69 6e 20 74 68 69 73 20 66 69 6c 65 2c 20 61   in this file, a
0350: 73 20 74 68 65 20 64 61 74 61 62 61 73 65 20 66  s the database f
0360: 69 6c 65 20 69 73 0a 23 20 6d 61 6e 69 70 75 6c  ile is.# manipul
0370: 61 74 65 64 20 64 69 72 65 63 74 6c 79 20 75 73  ated directly us
0380: 69 6e 67 20 74 63 6c 20 73 63 72 69 70 74 73 2e  ing tcl scripts.
0390: 20 53 65 65 20 70 72 6f 63 20 5b 73 65 74 5f 66   See proc [set_f
03a0: 69 6c 65 5f 66 6f 72 6d 61 74 5d 2e 0a 23 0a 64  ile_format]..#.d
03b0: 6f 5f 6e 6f 74 5f 75 73 65 5f 63 6f 64 65 63 0a  o_not_use_codec.
03c0: 0a 23 20 54 68 65 20 66 69 6c 65 20 66 6f 72 6d  .# The file form
03d0: 61 74 20 63 68 61 6e 67 65 20 61 66 66 65 63 74  at change affect
03e0: 73 20 74 68 65 20 77 61 79 20 72 6f 77 2d 72 65  s the way row-re
03f0: 63 6f 72 64 73 20 73 74 6f 72 65 64 20 69 6e 20  cords stored in 
0400: 74 61 62 6c 65 73 20 28 62 75 74 20 0a 23 20 6e  tables (but .# n
0410: 6f 74 20 69 6e 64 69 63 65 73 29 20 61 72 65 20  ot indices) are 
0420: 69 6e 74 65 72 70 72 65 74 65 64 2e 20 42 65 66  interpreted. Bef
0430: 6f 72 65 20 76 65 72 73 69 6f 6e 20 33 2e 31 2e  ore version 3.1.
0440: 33 2c 20 61 20 72 6f 77 2d 72 65 63 6f 72 64 20  3, a row-record 
0450: 66 6f 72 20 61 20 0a 23 20 74 61 62 6c 65 20 77  for a .# table w
0460: 69 74 68 20 4e 20 63 6f 6c 75 6d 6e 73 20 77 61  ith N columns wa
0470: 73 20 67 75 61 72 61 6e 74 65 65 64 20 74 6f 20  s guaranteed to 
0480: 63 6f 6e 74 61 69 6e 20 65 78 61 63 74 6c 79 20  contain exactly 
0490: 4e 20 66 69 65 6c 64 73 2e 20 41 73 0a 23 20 6f  N fields. As.# o
04a0: 66 20 76 65 72 73 69 6f 6e 20 33 2e 31 2e 33 2c  f version 3.1.3,
04b0: 20 74 68 65 20 72 65 63 6f 72 64 20 6d 61 79 20   the record may 
04c0: 63 6f 6e 74 61 69 6e 20 75 70 20 74 6f 20 4e 20  contain up to N 
04d0: 66 69 65 6c 64 73 2e 20 49 6e 20 74 68 69 73 20  fields. In this 
04e0: 63 61 73 65 0a 23 20 74 68 65 20 4d 20 66 69 65  case.# the M fie
04f0: 6c 64 73 20 74 68 61 74 20 61 72 65 20 70 72 65  lds that are pre
0500: 73 65 6e 74 20 61 72 65 20 74 68 65 20 76 61 6c  sent are the val
0510: 75 65 73 20 66 6f 72 20 74 68 65 20 6c 65 66 74  ues for the left
0520: 2d 6d 6f 73 74 20 4d 20 0a 23 20 63 6f 6c 75 6d  -most M .# colum
0530: 6e 73 2e 20 54 68 65 20 28 4e 2d 4d 29 20 72 69  ns. The (N-M) ri
0540: 67 68 74 6d 6f 73 74 20 63 6f 6c 75 6d 6e 73 20  ghtmost columns 
0550: 63 6f 6e 74 61 69 6e 20 4e 55 4c 4c 2e 0a 23 0a  contain NULL..#.
0560: 23 20 49 66 20 61 6e 79 20 72 65 63 6f 72 64 73  # If any records
0570: 20 69 6e 20 74 68 65 20 64 61 74 61 62 61 73 65   in the database
0580: 20 63 6f 6e 74 61 69 6e 20 6c 65 73 73 20 66 69   contain less fi
0590: 65 6c 64 73 20 74 68 61 6e 20 74 68 65 69 72 20  elds than their 
05a0: 74 61 62 6c 65 0a 23 20 68 61 73 20 63 6f 6c 75  table.# has colu
05b0: 6d 6e 73 2c 20 74 68 65 6e 20 74 68 65 20 66 69  mns, then the fi
05c0: 6c 65 2d 66 6f 72 6d 61 74 20 6d 65 74 61 20 76  le-format meta v
05d0: 61 6c 75 65 20 73 68 6f 75 6c 64 20 62 65 20 73  alue should be s
05e0: 65 74 20 74 6f 20 28 61 74 20 6c 65 61 73 74 29  et to (at least)
05f0: 20 32 2e 20 0a 23 0a 0a 23 20 54 68 69 73 20 70   2. .#..# This p
0600: 72 6f 63 65 64 75 72 65 20 73 65 74 73 20 74 68  rocedure sets th
0610: 65 20 76 61 6c 75 65 20 6f 66 20 74 68 65 20 66  e value of the f
0620: 69 6c 65 2d 66 6f 72 6d 61 74 20 69 6e 20 66 69  ile-format in fi
0630: 6c 65 20 27 74 65 73 74 2e 64 62 27 0a 23 20 74  le 'test.db'.# t
0640: 6f 20 24 6e 65 77 76 61 6c 2e 20 41 6c 73 6f 2c  o $newval. Also,
0650: 20 74 68 65 20 73 63 68 65 6d 61 20 63 6f 6f 6b   the schema cook
0660: 69 65 20 69 73 20 69 6e 63 72 65 6d 65 6e 74 65  ie is incremente
0670: 64 2e 0a 23 20 0a 70 72 6f 63 20 73 65 74 5f 66  d..# .proc set_f
0680: 69 6c 65 5f 66 6f 72 6d 61 74 20 7b 6e 65 77 76  ile_format {newv
0690: 61 6c 7d 20 7b 0a 20 20 68 65 78 69 6f 5f 77 72  al} {.  hexio_wr
06a0: 69 74 65 20 74 65 73 74 2e 64 62 20 34 34 20 5b  ite test.db 44 [
06b0: 68 65 78 69 6f 5f 72 65 6e 64 65 72 5f 69 6e 74  hexio_render_int
06c0: 33 32 20 24 6e 65 77 76 61 6c 5d 0a 20 20 73 65  32 $newval].  se
06d0: 74 20 73 63 68 65 6d 61 63 6f 6f 6b 69 65 20 5b  t schemacookie [
06e0: 68 65 78 69 6f 5f 67 65 74 5f 69 6e 74 20 5b 68  hexio_get_int [h
06f0: 65 78 69 6f 5f 72 65 61 64 20 74 65 73 74 2e 64  exio_read test.d
0700: 62 20 34 30 20 34 5d 5d 0a 20 20 69 6e 63 72 20  b 40 4]].  incr 
0710: 73 63 68 65 6d 61 63 6f 6f 6b 69 65 0a 20 20 68  schemacookie.  h
0720: 65 78 69 6f 5f 77 72 69 74 65 20 74 65 73 74 2e  exio_write test.
0730: 64 62 20 34 30 20 5b 68 65 78 69 6f 5f 72 65 6e  db 40 [hexio_ren
0740: 64 65 72 5f 69 6e 74 33 32 20 24 73 63 68 65 6d  der_int32 $schem
0750: 61 63 6f 6f 6b 69 65 5d 0a 20 20 72 65 74 75 72  acookie].  retur
0760: 6e 20 7b 7d 0a 7d 0a 0a 23 20 54 68 69 73 20 70  n {}.}..# This p
0770: 72 6f 63 65 64 75 72 65 20 72 65 74 75 72 6e 73  rocedure returns
0780: 20 74 68 65 20 76 61 6c 75 65 20 6f 66 20 74 68   the value of th
0790: 65 20 66 69 6c 65 2d 66 6f 72 6d 61 74 20 69 6e  e file-format in
07a0: 20 66 69 6c 65 20 27 74 65 73 74 2e 64 62 27 2e   file 'test.db'.
07b0: 0a 23 20 0a 70 72 6f 63 20 67 65 74 5f 66 69 6c  .# .proc get_fil
07c0: 65 5f 66 6f 72 6d 61 74 20 7b 7b 66 6e 61 6d 65  e_format {{fname
07d0: 20 74 65 73 74 2e 64 62 7d 7d 20 7b 0a 20 20 72   test.db}} {.  r
07e0: 65 74 75 72 6e 20 5b 68 65 78 69 6f 5f 67 65 74  eturn [hexio_get
07f0: 5f 69 6e 74 20 5b 68 65 78 69 6f 5f 72 65 61 64  _int [hexio_read
0800: 20 24 66 6e 61 6d 65 20 34 34 20 34 5d 5d 0a 7d   $fname 44 4]].}
0810: 0a 0a 23 20 54 68 69 73 20 70 72 6f 63 65 64 75  ..# This procedu
0820: 72 65 20 73 65 74 73 20 74 68 65 20 53 51 4c 20  re sets the SQL 
0830: 73 74 61 74 65 6d 65 6e 74 20 73 74 6f 72 65 64  statement stored
0840: 20 66 6f 72 20 74 61 62 6c 65 20 24 74 62 6c 20   for table $tbl 
0850: 69 6e 20 74 68 65 0a 23 20 73 71 6c 69 74 65 5f  in the.# sqlite_
0860: 6d 61 73 74 65 72 20 74 61 62 6c 65 20 6f 66 20  master table of 
0870: 66 69 6c 65 20 27 74 65 73 74 2e 64 62 27 20 74  file 'test.db' t
0880: 6f 20 24 73 71 6c 2e 20 41 6c 73 6f 20 73 65 74  o $sql. Also set
0890: 20 74 68 65 20 66 69 6c 65 20 66 6f 72 6d 61 74   the file format
08a0: 0a 23 20 74 6f 20 74 68 65 20 73 75 70 70 6c 69  .# to the suppli
08b0: 65 64 20 76 61 6c 75 65 2e 20 54 68 69 73 20 69  ed value. This i
08c0: 73 20 32 20 69 66 20 74 68 65 20 61 64 64 65 64  s 2 if the added
08d0: 20 63 6f 6c 75 6d 6e 20 68 61 73 20 61 20 64 65   column has a de
08e0: 66 61 75 6c 74 20 74 68 61 74 20 69 73 0a 23 20  fault that is.# 
08f0: 4e 55 4c 4c 2c 20 6f 72 20 33 20 6f 74 68 65 72  NULL, or 3 other
0900: 77 69 73 65 2e 20 0a 23 0a 70 72 6f 63 20 61 6c  wise. .#.proc al
0910: 74 65 72 5f 74 61 62 6c 65 20 7b 74 62 6c 20 73  ter_table {tbl s
0920: 71 6c 20 7b 66 69 6c 65 5f 66 6f 72 6d 61 74 20  ql {file_format 
0930: 32 7d 7d 20 7b 0a 20 20 73 71 6c 69 74 65 33 20  2}} {.  sqlite3 
0940: 64 62 61 74 20 74 65 73 74 2e 64 62 0a 20 20 73  dbat test.db.  s
0950: 65 74 20 73 20 5b 73 74 72 69 6e 67 20 6d 61 70  et s [string map
0960: 20 7b 27 20 27 27 7d 20 24 73 71 6c 5d 0a 20 20   {' ''} $sql].  
0970: 73 65 74 20 74 20 5b 73 74 72 69 6e 67 20 6d 61  set t [string ma
0980: 70 20 7b 27 20 27 27 7d 20 24 74 62 6c 5d 0a 20  p {' ''} $tbl]. 
0990: 20 64 62 61 74 20 65 76 61 6c 20 5b 73 75 62 73   dbat eval [subs
09a0: 74 20 7b 0a 20 20 20 20 50 52 41 47 4d 41 20 77  t {.    PRAGMA w
09b0: 72 69 74 61 62 6c 65 5f 73 63 68 65 6d 61 20 3d  ritable_schema =
09c0: 20 31 3b 0a 20 20 20 20 55 50 44 41 54 45 20 73   1;.    UPDATE s
09d0: 71 6c 69 74 65 5f 6d 61 73 74 65 72 20 53 45 54  qlite_master SET
09e0: 20 73 71 6c 20 3d 20 27 24 73 27 20 57 48 45 52   sql = '$s' WHER
09f0: 45 20 6e 61 6d 65 20 3d 20 27 24 74 27 20 41 4e  E name = '$t' AN
0a00: 44 20 74 79 70 65 20 3d 20 27 74 61 62 6c 65 27  D type = 'table'
0a10: 3b 0a 20 20 20 20 50 52 41 47 4d 41 20 77 72 69  ;.    PRAGMA wri
0a20: 74 61 62 6c 65 5f 73 63 68 65 6d 61 20 3d 20 30  table_schema = 0
0a30: 3b 0a 20 20 7d 5d 0a 20 20 64 62 61 74 20 63 6c  ;.  }].  dbat cl
0a40: 6f 73 65 0a 20 20 73 65 74 5f 66 69 6c 65 5f 66  ose.  set_file_f
0a50: 6f 72 6d 61 74 20 32 0a 7d 0a 0a 23 20 43 72 65  ormat 2.}..# Cre
0a60: 61 74 65 20 62 6f 67 75 73 20 61 70 70 6c 69 63  ate bogus applic
0a70: 61 74 69 6f 6e 2d 64 65 66 69 6e 65 64 20 66 75  ation-defined fu
0a80: 6e 63 74 69 6f 6e 73 20 66 6f 72 20 66 75 6e 63  nctions for func
0a90: 74 69 6f 6e 73 20 75 73 65 64 20 0a 23 20 69 6e  tions used .# in
0aa0: 74 65 72 6e 61 6c 6c 79 20 62 79 20 41 4c 54 45  ternally by ALTE
0ab0: 52 20 54 41 42 4c 45 2c 20 74 6f 20 65 6e 73 75  R TABLE, to ensu
0ac0: 72 65 20 74 68 61 74 20 41 4c 54 45 52 20 54 41  re that ALTER TA
0ad0: 42 4c 45 20 66 61 6c 6c 73 20 62 61 63 6b 0a 23  BLE falls back.#
0ae0: 20 74 6f 20 74 68 65 20 62 75 69 6c 74 2d 69 6e   to the built-in
0af0: 20 66 75 6e 63 74 69 6f 6e 73 2e 0a 23 0a 70 72   functions..#.pr
0b00: 6f 63 20 66 61 69 6c 69 6e 67 5f 61 70 70 5f 66  oc failing_app_f
0b10: 75 6e 63 20 7b 61 72 67 73 7d 20 7b 65 72 72 6f  unc {args} {erro
0b20: 72 20 22 62 61 64 20 66 75 6e 63 74 69 6f 6e 22  r "bad function"
0b30: 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32  }.do_test alter2
0b40: 2d 31 2e 30 20 7b 0a 20 20 64 62 20 66 75 6e 63  -1.0 {.  db func
0b50: 20 73 75 62 73 74 72 20 66 61 69 6c 69 6e 67 5f   substr failing_
0b60: 61 70 70 5f 66 75 6e 63 0a 20 20 64 62 20 66 75  app_func.  db fu
0b70: 6e 63 20 6c 69 6b 65 20 66 61 69 6c 69 6e 67 5f  nc like failing_
0b80: 61 70 70 5f 66 75 6e 63 0a 20 20 64 62 20 66 75  app_func.  db fu
0b90: 6e 63 20 73 71 6c 69 74 65 5f 72 65 6e 61 6d 65  nc sqlite_rename
0ba0: 5f 74 61 62 6c 65 20 66 61 69 6c 69 6e 67 5f 61  _table failing_a
0bb0: 70 70 5f 66 75 6e 63 0a 20 20 64 62 20 66 75 6e  pp_func.  db fun
0bc0: 63 20 73 71 6c 69 74 65 5f 72 65 6e 61 6d 65 5f  c sqlite_rename_
0bd0: 74 72 69 67 67 65 72 20 66 61 69 6c 69 6e 67 5f  trigger failing_
0be0: 61 70 70 5f 66 75 6e 63 0a 20 20 64 62 20 66 75  app_func.  db fu
0bf0: 6e 63 20 73 71 6c 69 74 65 5f 72 65 6e 61 6d 65  nc sqlite_rename
0c00: 5f 70 61 72 65 6e 74 20 66 61 69 6c 69 6e 67 5f  _parent failing_
0c10: 61 70 70 5f 66 75 6e 63 0a 20 20 63 61 74 63 68  app_func.  catch
0c20: 73 71 6c 20 7b 53 45 4c 45 43 54 20 73 75 62 73  sql {SELECT subs
0c30: 74 72 28 27 61 62 63 64 65 66 67 27 2c 31 2c 33  tr('abcdefg',1,3
0c40: 29 7d 0a 7d 20 7b 31 20 7b 62 61 64 20 66 75 6e  )}.} {1 {bad fun
0c50: 63 74 69 6f 6e 7d 7d 0a 0a 0a 23 2d 2d 2d 2d 2d  ction}}...#-----
0c60: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
0c70: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
0c80: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
0c90: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
0ca0: 2d 2d 0a 23 20 53 6f 6d 65 20 62 61 73 69 63 20  --.# Some basic 
0cb0: 74 65 73 74 73 20 74 6f 20 6d 61 6b 65 20 73 75  tests to make su
0cc0: 72 65 20 73 68 6f 72 74 20 72 6f 77 73 20 61 72  re short rows ar
0cd0: 65 20 68 61 6e 64 6c 65 64 2e 0a 23 0a 64 6f 5f  e handled..#.do_
0ce0: 74 65 73 74 20 61 6c 74 65 72 32 2d 31 2e 31 20  test alter2-1.1 
0cf0: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
0d00: 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 61    CREATE TABLE a
0d10: 62 63 28 61 2c 20 62 29 3b 0a 20 20 20 20 49 4e  bc(a, b);.    IN
0d20: 53 45 52 54 20 49 4e 54 4f 20 61 62 63 20 56 41  SERT INTO abc VA
0d30: 4c 55 45 53 28 31 2c 20 32 29 3b 0a 20 20 20 20  LUES(1, 2);.    
0d40: 49 4e 53 45 52 54 20 49 4e 54 4f 20 61 62 63 20  INSERT INTO abc 
0d50: 56 41 4c 55 45 53 28 33 2c 20 34 29 3b 0a 20 20  VALUES(3, 4);.  
0d60: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 61 62    INSERT INTO ab
0d70: 63 20 56 41 4c 55 45 53 28 35 2c 20 36 29 3b 0a  c VALUES(5, 6);.
0d80: 20 20 7d 0a 7d 20 7b 7d 0a 64 6f 5f 74 65 73 74    }.} {}.do_test
0d90: 20 61 6c 74 65 72 32 2d 31 2e 32 20 7b 0a 20 20   alter2-1.2 {.  
0da0: 23 20 41 4c 54 45 52 20 54 41 42 4c 45 20 61 62  # ALTER TABLE ab
0db0: 63 20 41 44 44 20 43 4f 4c 55 4d 4e 20 63 3b 0a  c ADD COLUMN c;.
0dc0: 20 20 61 6c 74 65 72 5f 74 61 62 6c 65 20 61 62    alter_table ab
0dd0: 63 20 7b 43 52 45 41 54 45 20 54 41 42 4c 45 20  c {CREATE TABLE 
0de0: 61 62 63 28 61 2c 20 62 2c 20 63 29 3b 7d 0a 7d  abc(a, b, c);}.}
0df0: 20 7b 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65   {}.do_test alte
0e00: 72 32 2d 31 2e 33 20 7b 0a 20 20 65 78 65 63 73  r2-1.3 {.  execs
0e10: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
0e20: 2a 20 46 52 4f 4d 20 61 62 63 3b 0a 20 20 7d 0a  * FROM abc;.  }.
0e30: 7d 20 7b 31 20 32 20 7b 7d 20 33 20 34 20 7b 7d  } {1 2 {} 3 4 {}
0e40: 20 35 20 36 20 7b 7d 7d 0a 64 6f 5f 74 65 73 74   5 6 {}}.do_test
0e50: 20 61 6c 74 65 72 32 2d 31 2e 34 20 7b 0a 20 20   alter2-1.4 {.  
0e60: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 55 50  execsql {.    UP
0e70: 44 41 54 45 20 61 62 63 20 53 45 54 20 63 20 3d  DATE abc SET c =
0e80: 20 31 30 20 57 48 45 52 45 20 61 20 3d 20 31 3b   10 WHERE a = 1;
0e90: 0a 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52  .    SELECT * FR
0ea0: 4f 4d 20 61 62 63 3b 0a 20 20 7d 0a 7d 20 7b 31  OM abc;.  }.} {1
0eb0: 20 32 20 31 30 20 33 20 34 20 7b 7d 20 35 20 36   2 10 3 4 {} 5 6
0ec0: 20 7b 7d 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74   {}}.do_test alt
0ed0: 65 72 32 2d 31 2e 35 20 7b 0a 20 20 65 78 65 63  er2-1.5 {.  exec
0ee0: 73 71 6c 20 7b 0a 20 20 20 20 43 52 45 41 54 45  sql {.    CREATE
0ef0: 20 49 4e 44 45 58 20 61 62 63 5f 69 20 4f 4e 20   INDEX abc_i ON 
0f00: 61 62 63 28 63 29 3b 0a 20 20 7d 0a 7d 20 7b 7d  abc(c);.  }.} {}
0f10: 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d  .do_test alter2-
0f20: 31 2e 36 20 7b 0a 20 20 65 78 65 63 73 71 6c 20  1.6 {.  execsql 
0f30: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 63 20 46  {.    SELECT c F
0f40: 52 4f 4d 20 61 62 63 20 4f 52 44 45 52 20 42 59  ROM abc ORDER BY
0f50: 20 63 3b 0a 20 20 7d 0a 7d 20 7b 7b 7d 20 7b 7d   c;.  }.} {{} {}
0f60: 20 31 30 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74   10}.do_test alt
0f70: 65 72 32 2d 31 2e 37 20 7b 0a 20 20 65 78 65 63  er2-1.7 {.  exec
0f80: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
0f90: 20 2a 20 46 52 4f 4d 20 61 62 63 20 57 48 45 52   * FROM abc WHER
0fa0: 45 20 63 20 3d 20 31 30 3b 0a 20 20 7d 0a 7d 20  E c = 10;.  }.} 
0fb0: 7b 31 20 32 20 31 30 7d 0a 64 6f 5f 74 65 73 74  {1 2 10}.do_test
0fc0: 20 61 6c 74 65 72 32 2d 31 2e 38 20 7b 0a 20 20   alter2-1.8 {.  
0fd0: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45  execsql {.    SE
0fe0: 4c 45 43 54 20 73 75 6d 28 61 29 2c 20 63 20 46  LECT sum(a), c F
0ff0: 52 4f 4d 20 61 62 63 20 47 52 4f 55 50 20 42 59  ROM abc GROUP BY
1000: 20 63 3b 0a 20 20 7d 0a 7d 20 7b 38 20 7b 7d 20   c;.  }.} {8 {} 
1010: 31 20 31 30 7d 0a 64 6f 5f 74 65 73 74 20 61 6c  1 10}.do_test al
1020: 74 65 72 32 2d 31 2e 39 20 7b 0a 20 20 23 20 41  ter2-1.9 {.  # A
1030: 4c 54 45 52 20 54 41 42 4c 45 20 61 62 63 20 41  LTER TABLE abc A
1040: 44 44 20 43 4f 4c 55 4d 4e 20 64 3b 0a 20 20 61  DD COLUMN d;.  a
1050: 6c 74 65 72 5f 74 61 62 6c 65 20 61 62 63 20 7b  lter_table abc {
1060: 43 52 45 41 54 45 20 54 41 42 4c 45 20 61 62 63  CREATE TABLE abc
1070: 28 61 2c 20 62 2c 20 63 2c 20 64 29 3b 7d 0a 20  (a, b, c, d);}. 
1080: 20 69 66 20 7b 5b 70 65 72 6d 75 74 61 74 69 6f   if {[permutatio
1090: 6e 5d 20 3d 3d 20 22 70 72 65 70 61 72 65 22 7d  n] == "prepare"}
10a0: 20 7b 20 64 62 20 63 61 63 68 65 20 66 6c 75 73   { db cache flus
10b0: 68 20 7d 0a 20 20 65 78 65 63 73 71 6c 20 7b 20  h }.  execsql { 
10c0: 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 61 62  SELECT * FROM ab
10d0: 63 3b 20 7d 0a 20 20 65 78 65 63 73 71 6c 20 7b  c; }.  execsql {
10e0: 0a 20 20 20 20 55 50 44 41 54 45 20 61 62 63 20  .    UPDATE abc 
10f0: 53 45 54 20 64 20 3d 20 31 31 20 57 48 45 52 45  SET d = 11 WHERE
1100: 20 63 20 49 53 20 4e 55 4c 4c 20 41 4e 44 20 61   c IS NULL AND a
1110: 3c 34 3b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a  <4;.    SELECT *
1120: 20 46 52 4f 4d 20 61 62 63 3b 0a 20 20 7d 0a 7d   FROM abc;.  }.}
1130: 20 7b 31 20 32 20 31 30 20 7b 7d 20 33 20 34 20   {1 2 10 {} 3 4 
1140: 7b 7d 20 31 31 20 35 20 36 20 7b 7d 20 7b 7d 7d  {} 11 5 6 {} {}}
1150: 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d  .do_test alter2-
1160: 31 2e 31 30 20 7b 0a 20 20 65 78 65 63 73 71 6c  1.10 {.  execsql
1170: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 74 79   {.    SELECT ty
1180: 70 65 6f 66 28 64 29 20 46 52 4f 4d 20 61 62 63  peof(d) FROM abc
1190: 3b 0a 20 20 7d 0a 7d 20 7b 6e 75 6c 6c 20 69 6e  ;.  }.} {null in
11a0: 74 65 67 65 72 20 6e 75 6c 6c 7d 0a 64 6f 5f 74  teger null}.do_t
11b0: 65 73 74 20 61 6c 74 65 72 32 2d 31 2e 39 39 20  est alter2-1.99 
11c0: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
11d0: 20 20 44 52 4f 50 20 54 41 42 4c 45 20 61 62 63    DROP TABLE abc
11e0: 3b 0a 20 20 7d 0a 7d 20 7b 7d 0a 0a 23 2d 2d 2d  ;.  }.} {}..#---
11f0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1200: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1210: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1220: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1230: 2d 2d 2d 2d 0a 23 20 54 65 73 74 20 74 68 61 74  ----.# Test that
1240: 20 76 69 65 77 73 20 77 6f 72 6b 20 77 68 65 6e   views work when
1250: 20 74 68 65 20 75 6e 64 65 72 6c 79 69 6e 67 20   the underlying 
1260: 74 61 62 6c 65 20 73 74 72 75 63 74 75 72 65 20  table structure 
1270: 69 73 20 63 68 61 6e 67 65 64 2e 0a 23 0a 69 66  is changed..#.if
1280: 63 61 70 61 62 6c 65 20 76 69 65 77 20 7b 0a 20  capable view {. 
1290: 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d   do_test alter2-
12a0: 32 2e 31 20 7b 0a 20 20 20 20 65 78 65 63 73 71  2.1 {.    execsq
12b0: 6c 20 7b 0a 20 20 20 20 20 20 43 52 45 41 54 45  l {.      CREATE
12c0: 20 54 41 42 4c 45 20 61 62 63 32 28 61 2c 20 62   TABLE abc2(a, b
12d0: 2c 20 63 29 3b 0a 20 20 20 20 20 20 49 4e 53 45  , c);.      INSE
12e0: 52 54 20 49 4e 54 4f 20 61 62 63 32 20 56 41 4c  RT INTO abc2 VAL
12f0: 55 45 53 28 31 2c 20 32 2c 20 31 30 29 3b 0a 20  UES(1, 2, 10);. 
1300: 20 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f       INSERT INTO
1310: 20 61 62 63 32 20 56 41 4c 55 45 53 28 33 2c 20   abc2 VALUES(3, 
1320: 34 2c 20 4e 55 4c 4c 29 3b 0a 20 20 20 20 20 20  4, NULL);.      
1330: 49 4e 53 45 52 54 20 49 4e 54 4f 20 61 62 63 32  INSERT INTO abc2
1340: 20 56 41 4c 55 45 53 28 35 2c 20 36 2c 20 4e 55   VALUES(5, 6, NU
1350: 4c 4c 29 3b 0a 20 20 20 20 20 20 43 52 45 41 54  LL);.      CREAT
1360: 45 20 56 49 45 57 20 61 62 63 32 5f 76 20 41 53  E VIEW abc2_v AS
1370: 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 61   SELECT * FROM a
1380: 62 63 32 3b 0a 20 20 20 20 20 20 53 45 4c 45 43  bc2;.      SELEC
1390: 54 20 2a 20 46 52 4f 4d 20 61 62 63 32 5f 76 3b  T * FROM abc2_v;
13a0: 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 31 20 32 20  .    }.  } {1 2 
13b0: 31 30 20 33 20 34 20 7b 7d 20 35 20 36 20 7b 7d  10 3 4 {} 5 6 {}
13c0: 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65  }.  do_test alte
13d0: 72 32 2d 32 2e 32 20 7b 0a 20 20 20 20 23 20 41  r2-2.2 {.    # A
13e0: 4c 54 45 52 20 54 41 42 4c 45 20 61 62 63 20 41  LTER TABLE abc A
13f0: 44 44 20 43 4f 4c 55 4d 4e 20 64 3b 0a 20 20 20  DD COLUMN d;.   
1400: 20 61 6c 74 65 72 5f 74 61 62 6c 65 20 61 62 63   alter_table abc
1410: 32 20 7b 43 52 45 41 54 45 20 54 41 42 4c 45 20  2 {CREATE TABLE 
1420: 61 62 63 32 28 61 2c 20 62 2c 20 63 2c 20 64 29  abc2(a, b, c, d)
1430: 3b 7d 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b  ;}.    execsql {
1440: 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 2a 20  .      SELECT * 
1450: 46 52 4f 4d 20 61 62 63 32 5f 76 3b 0a 20 20 20  FROM abc2_v;.   
1460: 20 7d 0a 20 20 7d 20 7b 31 20 32 20 31 30 20 7b   }.  } {1 2 10 {
1470: 7d 20 33 20 34 20 7b 7d 20 7b 7d 20 35 20 36 20  } 3 4 {} {} 5 6 
1480: 7b 7d 20 7b 7d 7d 0a 20 20 64 6f 5f 74 65 73 74  {} {}}.  do_test
1490: 20 61 6c 74 65 72 32 2d 32 2e 33 20 7b 0a 20 20   alter2-2.3 {.  
14a0: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
14b0: 20 20 44 52 4f 50 20 54 41 42 4c 45 20 61 62 63    DROP TABLE abc
14c0: 32 3b 0a 20 20 20 20 20 20 44 52 4f 50 20 56 49  2;.      DROP VI
14d0: 45 57 20 61 62 63 32 5f 76 3b 0a 20 20 20 20 7d  EW abc2_v;.    }
14e0: 0a 20 20 7d 20 7b 7d 0a 7d 0a 0a 23 2d 2d 2d 2d  .  } {}.}..#----
14f0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1500: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1510: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1520: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1530: 2d 2d 2d 0a 23 20 54 65 73 74 20 74 68 61 74 20  ---.# Test that 
1540: 74 72 69 67 67 65 72 73 20 77 6f 72 6b 20 77 68  triggers work wh
1550: 65 6e 20 61 20 73 68 6f 72 74 20 72 6f 77 20 69  en a short row i
1560: 73 20 63 6f 70 69 65 64 20 74 6f 20 74 68 65 20  s copied to the 
1570: 6f 6c 64 2e 2a 0a 23 20 74 72 69 67 67 65 72 20  old.*.# trigger 
1580: 70 73 65 75 64 6f 2d 74 61 62 6c 65 2e 0a 23 0a  pseudo-table..#.
1590: 69 66 63 61 70 61 62 6c 65 20 74 72 69 67 67 65  ifcapable trigge
15a0: 72 20 7b 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c  r {.  do_test al
15b0: 74 65 72 32 2d 33 2e 31 20 7b 0a 20 20 20 20 65  ter2-3.1 {.    e
15c0: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 43  xecsql {.      C
15d0: 52 45 41 54 45 20 54 41 42 4c 45 20 61 62 63 33  REATE TABLE abc3
15e0: 28 61 2c 20 62 29 3b 0a 20 20 20 20 20 20 43 52  (a, b);.      CR
15f0: 45 41 54 45 20 54 41 42 4c 45 20 62 6c 6f 67 28  EATE TABLE blog(
1600: 6f 2c 20 6e 29 3b 0a 20 20 20 20 20 20 43 52 45  o, n);.      CRE
1610: 41 54 45 20 54 52 49 47 47 45 52 20 61 62 63 33  ATE TRIGGER abc3
1620: 5f 74 20 41 46 54 45 52 20 55 50 44 41 54 45 20  _t AFTER UPDATE 
1630: 4f 46 20 62 20 4f 4e 20 61 62 63 33 20 42 45 47  OF b ON abc3 BEG
1640: 49 4e 0a 20 20 20 20 20 20 20 20 49 4e 53 45 52  IN.        INSER
1650: 54 20 49 4e 54 4f 20 62 6c 6f 67 20 56 41 4c 55  T INTO blog VALU
1660: 45 53 28 6f 6c 64 2e 62 2c 20 6e 65 77 2e 62 29  ES(old.b, new.b)
1670: 3b 0a 20 20 20 20 20 20 45 4e 44 3b 0a 20 20 20  ;.      END;.   
1680: 20 7d 0a 20 20 7d 20 7b 7d 0a 20 20 64 6f 5f 74   }.  } {}.  do_t
1690: 65 73 74 20 61 6c 74 65 72 32 2d 33 2e 32 20 7b  est alter2-3.2 {
16a0: 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20  .    execsql {. 
16b0: 20 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f       INSERT INTO
16c0: 20 61 62 63 33 20 56 41 4c 55 45 53 28 31 2c 20   abc3 VALUES(1, 
16d0: 34 29 3b 0a 20 20 20 20 20 20 55 50 44 41 54 45  4);.      UPDATE
16e0: 20 61 62 63 33 20 53 45 54 20 62 20 3d 20 32 20   abc3 SET b = 2 
16f0: 57 48 45 52 45 20 62 20 3d 20 34 3b 0a 20 20 20  WHERE b = 4;.   
1700: 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d     SELECT * FROM
1710: 20 62 6c 6f 67 3b 0a 20 20 20 20 7d 0a 20 20 7d   blog;.    }.  }
1720: 20 7b 34 20 32 7d 0a 20 20 64 6f 5f 74 65 73 74   {4 2}.  do_test
1730: 20 61 6c 74 65 72 32 2d 33 2e 33 20 7b 0a 20 20   alter2-3.3 {.  
1740: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
1750: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 61 62    INSERT INTO ab
1760: 63 33 20 56 41 4c 55 45 53 28 33 2c 20 34 29 3b  c3 VALUES(3, 4);
1770: 0a 20 20 20 20 20 20 49 4e 53 45 52 54 20 49 4e  .      INSERT IN
1780: 54 4f 20 61 62 63 33 20 56 41 4c 55 45 53 28 35  TO abc3 VALUES(5
1790: 2c 20 36 29 3b 0a 20 20 20 20 7d 0a 20 20 20 20  , 6);.    }.    
17a0: 61 6c 74 65 72 5f 74 61 62 6c 65 20 61 62 63 33  alter_table abc3
17b0: 20 7b 43 52 45 41 54 45 20 54 41 42 4c 45 20 61   {CREATE TABLE a
17c0: 62 63 33 28 61 2c 20 62 2c 20 63 29 3b 7d 0a 20  bc3(a, b, c);}. 
17d0: 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20     execsql {.   
17e0: 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d     SELECT * FROM
17f0: 20 61 62 63 33 3b 0a 20 20 20 20 7d 0a 20 20 7d   abc3;.    }.  }
1800: 20 7b 31 20 32 20 7b 7d 20 33 20 34 20 7b 7d 20   {1 2 {} 3 4 {} 
1810: 35 20 36 20 7b 7d 7d 0a 20 20 64 6f 5f 74 65 73  5 6 {}}.  do_tes
1820: 74 20 61 6c 74 65 72 32 2d 33 2e 34 20 7b 0a 20  t alter2-3.4 {. 
1830: 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20     execsql {.   
1840: 20 20 20 55 50 44 41 54 45 20 61 62 63 33 20 53     UPDATE abc3 S
1850: 45 54 20 62 20 3d 20 62 2a 32 20 57 48 45 52 45  ET b = b*2 WHERE
1860: 20 61 3c 34 3b 0a 20 20 20 20 20 20 53 45 4c 45   a<4;.      SELE
1870: 43 54 20 2a 20 46 52 4f 4d 20 61 62 63 33 3b 0a  CT * FROM abc3;.
1880: 20 20 20 20 7d 0a 20 20 7d 20 7b 31 20 34 20 7b      }.  } {1 4 {
1890: 7d 20 33 20 38 20 7b 7d 20 35 20 36 20 7b 7d 7d  } 3 8 {} 5 6 {}}
18a0: 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  .  do_test alter
18b0: 32 2d 33 2e 35 20 7b 0a 20 20 20 20 65 78 65 63  2-3.5 {.    exec
18c0: 73 71 6c 20 7b 0a 20 20 20 20 20 20 53 45 4c 45  sql {.      SELE
18d0: 43 54 20 2a 20 46 52 4f 4d 20 62 6c 6f 67 3b 0a  CT * FROM blog;.
18e0: 20 20 20 20 7d 0a 20 20 7d 20 7b 34 20 32 20 32      }.  } {4 2 2
18f0: 20 34 20 34 20 38 7d 0a 0a 20 20 64 6f 5f 74 65   4 4 8}..  do_te
1900: 73 74 20 61 6c 74 65 72 32 2d 33 2e 36 20 7b 0a  st alter2-3.6 {.
1910: 20 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20      execsql {.  
1920: 20 20 20 20 43 52 45 41 54 45 20 54 41 42 4c 45      CREATE TABLE
1930: 20 63 6c 6f 67 28 6f 2c 20 6e 29 3b 0a 20 20 20   clog(o, n);.   
1940: 20 20 20 43 52 45 41 54 45 20 54 52 49 47 47 45     CREATE TRIGGE
1950: 52 20 61 62 63 33 5f 74 32 20 41 46 54 45 52 20  R abc3_t2 AFTER 
1960: 55 50 44 41 54 45 20 4f 46 20 63 20 4f 4e 20 61  UPDATE OF c ON a
1970: 62 63 33 20 42 45 47 49 4e 0a 20 20 20 20 20 20  bc3 BEGIN.      
1980: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 63 6c    INSERT INTO cl
1990: 6f 67 20 56 41 4c 55 45 53 28 6f 6c 64 2e 63 2c  og VALUES(old.c,
19a0: 20 6e 65 77 2e 63 29 3b 0a 20 20 20 20 20 20 45   new.c);.      E
19b0: 4e 44 3b 0a 20 20 20 20 20 20 55 50 44 41 54 45  ND;.      UPDATE
19c0: 20 61 62 63 33 20 53 45 54 20 63 20 3d 20 61 2a   abc3 SET c = a*
19d0: 32 3b 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20  2;.      SELECT 
19e0: 2a 20 46 52 4f 4d 20 63 6c 6f 67 3b 0a 20 20 20  * FROM clog;.   
19f0: 20 7d 0a 20 20 7d 20 7b 7b 7d 20 32 20 7b 7d 20   }.  } {{} 2 {} 
1a00: 36 20 7b 7d 20 31 30 7d 0a 7d 20 65 6c 73 65 20  6 {} 10}.} else 
1a10: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 20 43 52  {.  execsql { CR
1a20: 45 41 54 45 20 54 41 42 4c 45 20 61 62 63 33 28  EATE TABLE abc3(
1a30: 61 2c 20 62 29 3b 20 7d 0a 7d 0a 0a 23 2d 2d 2d  a, b); }.}..#---
1a40: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1a50: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1a60: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1a70: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1a80: 2d 2d 0a 23 20 43 68 65 63 6b 20 74 68 61 74 20  --.# Check that 
1a90: 61 6e 20 65 72 72 6f 72 20 6f 63 63 75 72 73 20  an error occurs 
1aa0: 69 66 20 74 68 65 20 64 61 74 61 62 61 73 65 20  if the database 
1ab0: 69 73 20 75 70 67 72 61 64 65 64 20 74 6f 20 61  is upgraded to a
1ac0: 20 66 69 6c 65 0a 23 20 66 6f 72 6d 61 74 20 74   file.# format t
1ad0: 68 61 74 20 53 51 4c 69 74 65 20 64 6f 65 73 20  hat SQLite does 
1ae0: 6e 6f 74 20 73 75 70 70 6f 72 74 20 28 69 6e 20  not support (in 
1af0: 74 68 69 73 20 63 61 73 65 20 35 29 2e 20 4e 6f  this case 5). No
1b00: 74 65 3a 20 54 68 65 20 0a 23 20 66 69 6c 65 20  te: The .# file 
1b10: 66 6f 72 6d 61 74 20 69 73 20 63 68 65 63 6b 65  format is checke
1b20: 64 20 65 61 63 68 20 74 69 6d 65 20 74 68 65 20  d each time the 
1b30: 73 63 68 65 6d 61 20 69 73 20 72 65 61 64 2c 20  schema is read, 
1b40: 73 6f 20 63 68 61 6e 67 69 6e 67 20 74 68 65 0a  so changing the.
1b50: 23 20 66 69 6c 65 20 66 6f 72 6d 61 74 20 72 65  # file format re
1b60: 71 75 69 72 65 73 20 69 6e 63 72 65 6d 65 6e 74  quires increment
1b70: 69 6e 67 20 74 68 65 20 73 63 68 65 6d 61 20 63  ing the schema c
1b80: 6f 6f 6b 69 65 2e 0a 23 0a 64 6f 5f 74 65 73 74  ookie..#.do_test
1b90: 20 61 6c 74 65 72 32 2d 34 2e 31 20 7b 0a 20 20   alter2-4.1 {.  
1ba0: 64 62 20 63 6c 6f 73 65 0a 20 20 73 65 74 5f 66  db close.  set_f
1bb0: 69 6c 65 5f 66 6f 72 6d 61 74 20 35 0a 20 20 63  ile_format 5.  c
1bc0: 61 74 63 68 20 7b 20 73 71 6c 69 74 65 33 20 64  atch { sqlite3 d
1bd0: 62 20 74 65 73 74 2e 64 62 20 7d 0a 20 20 73 65  b test.db }.  se
1be0: 74 20 7b 7d 20 7b 7d 0a 7d 20 7b 7d 0a 64 6f 5f  t {} {}.} {}.do_
1bf0: 74 65 73 74 20 61 6c 74 65 72 32 2d 34 2e 32 20  test alter2-4.2 
1c00: 7b 0a 20 20 23 20 57 65 20 68 61 76 65 20 74 6f  {.  # We have to
1c10: 20 72 75 6e 20 74 77 6f 20 71 75 65 72 69 65 73   run two queries
1c20: 20 68 65 72 65 20 62 65 63 61 75 73 65 20 74 68   here because th
1c30: 65 20 54 63 6c 20 69 6e 74 65 72 66 61 63 65 20  e Tcl interface 
1c40: 75 73 65 73 0a 20 20 23 20 73 71 6c 69 74 65 33  uses.  # sqlite3
1c50: 5f 70 72 65 70 61 72 65 5f 76 32 28 29 2e 20 49  _prepare_v2(). I
1c60: 6e 20 74 68 69 73 20 63 61 73 65 2c 20 74 68 65  n this case, the
1c70: 20 66 69 72 73 74 20 71 75 65 72 79 20 65 6e 63   first query enc
1c80: 6f 75 6e 74 65 72 73 20 61 6e 20 0a 20 20 23 20  ounters an .  # 
1c90: 53 51 4c 49 54 45 5f 53 43 48 45 4d 41 20 65 72  SQLITE_SCHEMA er
1ca0: 72 6f 72 2e 20 54 68 65 6e 2c 20 77 68 65 6e 20  ror. Then, when 
1cb0: 74 72 79 69 6e 67 20 74 6f 20 72 65 63 6f 6d 70  trying to recomp
1cc0: 69 6c 65 20 74 68 65 20 73 74 61 74 65 6d 65 6e  ile the statemen
1cd0: 74 2c 20 74 68 65 0a 20 20 23 20 22 75 6e 73 75  t, the.  # "unsu
1ce0: 70 70 6f 72 74 65 64 20 66 69 6c 65 20 66 6f 72  pported file for
1cf0: 6d 61 74 22 20 65 72 72 6f 72 20 69 73 20 65 6e  mat" error is en
1d00: 63 6f 75 6e 74 65 72 65 64 2e 20 53 6f 20 74 68  countered. So th
1d10: 65 20 65 72 72 6f 72 20 63 6f 64 65 0a 20 20 23  e error code.  #
1d20: 20 72 65 74 75 72 6e 65 64 20 69 73 20 53 51 4c   returned is SQL
1d30: 49 54 45 5f 53 43 48 45 4d 41 2c 20 6e 6f 74 20  ITE_SCHEMA, not 
1d40: 53 51 4c 49 54 45 5f 45 52 52 4f 52 20 61 73 20  SQLITE_ERROR as 
1d50: 72 65 71 75 69 72 65 64 20 62 79 20 74 68 65 20  required by the 
1d60: 66 6f 6c 6c 6f 77 69 6e 67 0a 20 20 23 20 74 65  following.  # te
1d70: 73 74 20 63 61 73 65 2e 0a 20 20 23 0a 20 20 23  st case..  #.  #
1d80: 20 57 68 65 6e 20 74 68 65 20 71 75 65 72 79 20   When the query 
1d90: 69 73 20 61 74 74 65 6d 70 74 65 64 20 61 20 73  is attempted a s
1da0: 65 63 6f 6e 64 20 74 69 6d 65 2c 20 74 68 65 20  econd time, the 
1db0: 73 61 6d 65 20 65 72 72 6f 72 20 6d 65 73 73 61  same error messa
1dc0: 67 65 20 69 73 0a 20 20 23 20 72 65 74 75 72 6e  ge is.  # return
1dd0: 65 64 20 62 75 74 20 74 68 65 20 65 72 72 6f 72  ed but the error
1de0: 20 63 6f 64 65 20 69 73 20 53 51 4c 49 54 45 5f   code is SQLITE_
1df0: 45 52 52 4f 52 2c 20 62 65 63 61 75 73 65 20 74  ERROR, because t
1e00: 68 65 20 75 6e 73 75 70 70 6f 72 74 65 64 0a 20  he unsupported. 
1e10: 20 23 20 66 69 6c 65 20 66 6f 72 6d 61 74 20 77   # file format w
1e20: 61 73 20 64 65 74 65 63 74 65 64 20 64 75 72 69  as detected duri
1e30: 6e 67 20 61 20 63 61 6c 6c 20 74 6f 20 73 71 6c  ng a call to sql
1e40: 69 74 65 33 5f 70 72 65 70 61 72 65 28 29 2c 20  ite3_prepare(), 
1e50: 6e 6f 74 0a 20 20 23 20 73 71 6c 69 74 65 33 5f  not.  # sqlite3_
1e60: 73 74 65 70 28 29 2e 0a 20 20 23 0a 20 20 63 61  step()..  #.  ca
1e70: 74 63 68 73 71 6c 20 7b 20 53 45 4c 45 43 54 20  tchsql { SELECT 
1e80: 2a 20 46 52 4f 4d 20 73 71 6c 69 74 65 5f 6d 61  * FROM sqlite_ma
1e90: 73 74 65 72 3b 20 7d 0a 20 20 63 61 74 63 68 73  ster; }.  catchs
1ea0: 71 6c 20 7b 20 53 45 4c 45 43 54 20 2a 20 46 52  ql { SELECT * FR
1eb0: 4f 4d 20 73 71 6c 69 74 65 5f 6d 61 73 74 65 72  OM sqlite_master
1ec0: 3b 20 7d 0a 7d 20 7b 31 20 7b 75 6e 73 75 70 70  ; }.} {1 {unsupp
1ed0: 6f 72 74 65 64 20 66 69 6c 65 20 66 6f 72 6d 61  orted file forma
1ee0: 74 7d 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65  t}}.do_test alte
1ef0: 72 32 2d 34 2e 33 20 7b 0a 20 20 73 71 6c 69 74  r2-4.3 {.  sqlit
1f00: 65 33 5f 65 72 72 63 6f 64 65 20 64 62 0a 7d 20  e3_errcode db.} 
1f10: 7b 53 51 4c 49 54 45 5f 45 52 52 4f 52 7d 0a 64  {SQLITE_ERROR}.d
1f20: 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 34 2e  o_test alter2-4.
1f30: 34 20 7b 0a 20 20 73 65 74 20 3a 3a 44 42 20 5b  4 {.  set ::DB [
1f40: 73 71 6c 69 74 65 33 5f 63 6f 6e 6e 65 63 74 69  sqlite3_connecti
1f50: 6f 6e 5f 70 6f 69 6e 74 65 72 20 64 62 5d 0a 20  on_pointer db]. 
1f60: 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20   catchsql {.    
1f70: 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 73 71  SELECT * FROM sq
1f80: 6c 69 74 65 5f 6d 61 73 74 65 72 3b 0a 20 20 7d  lite_master;.  }
1f90: 0a 7d 20 7b 31 20 7b 75 6e 73 75 70 70 6f 72 74  .} {1 {unsupport
1fa0: 65 64 20 66 69 6c 65 20 66 6f 72 6d 61 74 7d 7d  ed file format}}
1fb0: 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d  .do_test alter2-
1fc0: 34 2e 35 20 7b 0a 20 20 73 71 6c 69 74 65 33 5f  4.5 {.  sqlite3_
1fd0: 65 72 72 63 6f 64 65 20 64 62 0a 7d 20 7b 53 51  errcode db.} {SQ
1fe0: 4c 49 54 45 5f 45 52 52 4f 52 7d 0a 0a 23 2d 2d  LITE_ERROR}..#--
1ff0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2000: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2010: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2020: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2030: 2d 2d 2d 0a 23 20 43 68 65 63 6b 20 74 68 61 74  ---.# Check that
2040: 20 65 78 65 63 75 74 69 6e 67 20 56 41 43 55 55   executing VACUU
2050: 4d 20 6f 6e 20 61 20 66 69 6c 65 20 77 69 74 68  M on a file with
2060: 20 66 69 6c 65 2d 66 6f 72 6d 61 74 20 76 65 72   file-format ver
2070: 73 69 6f 6e 20 32 0a 23 20 72 65 73 65 74 73 20  sion 2.# resets 
2080: 74 68 65 20 66 69 6c 65 20 66 6f 72 6d 61 74 20  the file format 
2090: 74 6f 20 31 2e 0a 23 0a 73 65 74 20 64 65 66 61  to 1..#.set defa
20a0: 75 6c 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74 20  ult_file_format 
20b0: 5b 65 78 70 72 20 24 53 51 4c 49 54 45 5f 44 45  [expr $SQLITE_DE
20c0: 46 41 55 4c 54 5f 46 49 4c 45 5f 46 4f 52 4d 41  FAULT_FILE_FORMA
20d0: 54 3d 3d 34 20 3f 20 34 20 3a 20 31 5d 0a 69 66  T==4 ? 4 : 1].if
20e0: 63 61 70 61 62 6c 65 20 76 61 63 75 75 6d 20 7b  capable vacuum {
20f0: 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  .  do_test alter
2100: 32 2d 35 2e 31 20 7b 0a 20 20 20 20 73 65 74 5f  2-5.1 {.    set_
2110: 66 69 6c 65 5f 66 6f 72 6d 61 74 20 32 0a 20 20  file_format 2.  
2120: 20 20 64 62 20 63 6c 6f 73 65 0a 20 20 20 20 73    db close.    s
2130: 71 6c 69 74 65 33 20 64 62 20 74 65 73 74 2e 64  qlite3 db test.d
2140: 62 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b 53  b.    execsql {S
2150: 45 4c 45 43 54 20 31 20 46 52 4f 4d 20 73 71 6c  ELECT 1 FROM sql
2160: 69 74 65 5f 6d 61 73 74 65 72 20 4c 49 4d 49 54  ite_master LIMIT
2170: 20 31 3b 7d 0a 20 20 20 20 67 65 74 5f 66 69 6c   1;}.    get_fil
2180: 65 5f 66 6f 72 6d 61 74 0a 20 20 7d 20 7b 32 7d  e_format.  } {2}
2190: 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  .  do_test alter
21a0: 32 2d 35 2e 32 20 7b 0a 20 20 20 20 65 78 65 63  2-5.2 {.    exec
21b0: 73 71 6c 20 7b 20 56 41 43 55 55 4d 20 7d 0a 20  sql { VACUUM }. 
21c0: 20 7d 20 7b 7d 0a 20 20 64 6f 5f 74 65 73 74 20   } {}.  do_test 
21d0: 61 6c 74 65 72 32 2d 35 2e 33 20 7b 0a 20 20 20  alter2-5.3 {.   
21e0: 20 67 65 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74   get_file_format
21f0: 0a 20 20 7d 20 24 64 65 66 61 75 6c 74 5f 66 69  .  } $default_fi
2200: 6c 65 5f 66 6f 72 6d 61 74 0a 7d 0a 20 0a 23 2d  le_format.}. .#-
2210: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2220: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2230: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2240: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2250: 2d 2d 2d 2d 0a 23 20 54 65 73 74 20 74 68 61 74  ----.# Test that
2260: 20 77 68 65 6e 20 61 20 64 61 74 61 62 61 73 65   when a database
2270: 20 77 69 74 68 20 66 69 6c 65 2d 66 6f 72 6d 61   with file-forma
2280: 74 20 32 20 69 73 20 6f 70 65 6e 65 64 2c 20 6e  t 2 is opened, n
2290: 65 77 20 0a 23 20 64 61 74 61 62 61 73 65 73 20  ew .# databases 
22a0: 61 72 65 20 73 74 69 6c 6c 20 63 72 65 61 74 65  are still create
22b0: 64 20 77 69 74 68 20 66 69 6c 65 2d 66 6f 72 6d  d with file-form
22c0: 61 74 20 31 2e 0a 23 0a 64 6f 5f 74 65 73 74 20  at 1..#.do_test 
22d0: 61 6c 74 65 72 32 2d 36 2e 31 20 7b 0a 20 20 64  alter2-6.1 {.  d
22e0: 62 20 63 6c 6f 73 65 0a 20 20 73 65 74 5f 66 69  b close.  set_fi
22f0: 6c 65 5f 66 6f 72 6d 61 74 20 32 0a 20 20 73 71  le_format 2.  sq
2300: 6c 69 74 65 33 20 64 62 20 74 65 73 74 2e 64 62  lite3 db test.db
2310: 0a 20 20 67 65 74 5f 66 69 6c 65 5f 66 6f 72 6d  .  get_file_form
2320: 61 74 0a 7d 20 7b 32 7d 0a 69 66 63 61 70 61 62  at.} {2}.ifcapab
2330: 6c 65 20 61 74 74 61 63 68 20 7b 0a 20 20 64 6f  le attach {.  do
2340: 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 36 2e 32  _test alter2-6.2
2350: 20 7b 0a 20 20 20 20 66 6f 72 63 65 64 65 6c 65   {.    forcedele
2360: 74 65 20 74 65 73 74 32 2e 64 62 2d 6a 6f 75 72  te test2.db-jour
2370: 6e 61 6c 0a 20 20 20 20 66 6f 72 63 65 64 65 6c  nal.    forcedel
2380: 65 74 65 20 74 65 73 74 32 2e 64 62 0a 20 20 20  ete test2.db.   
2390: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20   execsql {.     
23a0: 20 41 54 54 41 43 48 20 27 74 65 73 74 32 2e 64   ATTACH 'test2.d
23b0: 62 27 20 41 53 20 61 75 78 3b 0a 20 20 20 20 20  b' AS aux;.     
23c0: 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 61 75   CREATE TABLE au
23d0: 78 2e 74 31 28 61 2c 20 62 29 3b 0a 20 20 20 20  x.t1(a, b);.    
23e0: 7d 0a 20 20 20 20 67 65 74 5f 66 69 6c 65 5f 66  }.    get_file_f
23f0: 6f 72 6d 61 74 20 74 65 73 74 32 2e 64 62 0a 20  ormat test2.db. 
2400: 20 7d 20 24 64 65 66 61 75 6c 74 5f 66 69 6c 65   } $default_file
2410: 5f 66 6f 72 6d 61 74 0a 7d 0a 64 6f 5f 74 65 73  _format.}.do_tes
2420: 74 20 61 6c 74 65 72 32 2d 36 2e 33 20 7b 0a 20  t alter2-6.3 {. 
2430: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 43   execsql {.    C
2440: 52 45 41 54 45 20 54 41 42 4c 45 20 74 31 28 61  REATE TABLE t1(a
2450: 2c 20 62 29 3b 0a 20 20 7d 0a 20 20 67 65 74 5f  , b);.  }.  get_
2460: 66 69 6c 65 5f 66 6f 72 6d 61 74 20 0a 7d 20 7b  file_format .} {
2470: 32 7d 0a 0a 23 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  2}..#-----------
2480: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2490: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
24a0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
24b0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 0a 23 20 54 65 73  ----------.# Tes
24c0: 74 20 74 68 61 74 20 74 79 70 65 73 20 61 6e 64  t that types and
24d0: 20 76 61 6c 75 65 73 20 66 6f 72 20 63 6f 6c 75   values for colu
24e0: 6d 6e 73 20 61 64 64 65 64 20 77 69 74 68 20 64  mns added with d
24f0: 65 66 61 75 6c 74 20 76 61 6c 75 65 73 20 0a 23  efault values .#
2500: 20 6f 74 68 65 72 20 74 68 61 6e 20 4e 55 4c 4c   other than NULL
2510: 20 77 6f 72 6b 20 77 69 74 68 20 53 45 4c 45 43   work with SELEC
2520: 54 20 73 74 61 74 65 6d 65 6e 74 73 2e 0a 23 0a  T statements..#.
2530: 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 37  do_test alter2-7
2540: 2e 31 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b  .1 {.  execsql {
2550: 0a 20 20 20 20 44 52 4f 50 20 54 41 42 4c 45 20  .    DROP TABLE 
2560: 74 31 3b 0a 20 20 20 20 43 52 45 41 54 45 20 54  t1;.    CREATE T
2570: 41 42 4c 45 20 74 31 28 61 29 3b 0a 20 20 20 20  ABLE t1(a);.    
2580: 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 31 20 56  INSERT INTO t1 V
2590: 41 4c 55 45 53 28 31 29 3b 0a 20 20 20 20 49 4e  ALUES(1);.    IN
25a0: 53 45 52 54 20 49 4e 54 4f 20 74 31 20 56 41 4c  SERT INTO t1 VAL
25b0: 55 45 53 28 32 29 3b 0a 20 20 20 20 49 4e 53 45  UES(2);.    INSE
25c0: 52 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45  RT INTO t1 VALUE
25d0: 53 28 33 29 3b 0a 20 20 20 20 49 4e 53 45 52 54  S(3);.    INSERT
25e0: 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53 28   INTO t1 VALUES(
25f0: 34 29 3b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a  4);.    SELECT *
2600: 20 46 52 4f 4d 20 74 31 3b 0a 20 20 7d 0a 7d 20   FROM t1;.  }.} 
2610: 7b 31 20 32 20 33 20 34 7d 0a 64 6f 5f 74 65 73  {1 2 3 4}.do_tes
2620: 74 20 61 6c 74 65 72 32 2d 37 2e 32 20 7b 0a 20  t alter2-7.2 {. 
2630: 20 73 65 74 20 73 71 6c 20 7b 43 52 45 41 54 45   set sql {CREATE
2640: 20 54 41 42 4c 45 20 74 31 28 61 2c 20 62 20 44   TABLE t1(a, b D
2650: 45 46 41 55 4c 54 20 27 31 32 33 27 2c 20 63 20  EFAULT '123', c 
2660: 49 4e 54 45 47 45 52 20 44 45 46 41 55 4c 54 20  INTEGER DEFAULT 
2670: 27 31 32 33 27 29 7d 0a 20 20 61 6c 74 65 72 5f  '123')}.  alter_
2680: 74 61 62 6c 65 20 74 31 20 24 73 71 6c 20 33 0a  table t1 $sql 3.
2690: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
26a0: 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74 31  SELECT * FROM t1
26b0: 20 4c 49 4d 49 54 20 31 3b 0a 20 20 7d 0a 7d 20   LIMIT 1;.  }.} 
26c0: 7b 31 20 31 32 33 20 31 32 33 7d 0a 64 6f 5f 74  {1 123 123}.do_t
26d0: 65 73 74 20 61 6c 74 65 72 32 2d 37 2e 33 20 7b  est alter2-7.3 {
26e0: 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  .  execsql {.   
26f0: 20 53 45 4c 45 43 54 20 61 2c 20 74 79 70 65 6f   SELECT a, typeo
2700: 66 28 61 29 2c 20 62 2c 20 74 79 70 65 6f 66 28  f(a), b, typeof(
2710: 62 29 2c 20 63 2c 20 74 79 70 65 6f 66 28 63 29  b), c, typeof(c)
2720: 20 46 52 4f 4d 20 74 31 20 4c 49 4d 49 54 20 31   FROM t1 LIMIT 1
2730: 3b 0a 20 20 7d 0a 7d 20 7b 31 20 69 6e 74 65 67  ;.  }.} {1 integ
2740: 65 72 20 31 32 33 20 74 65 78 74 20 31 32 33 20  er 123 text 123 
2750: 69 6e 74 65 67 65 72 7d 0a 64 6f 5f 74 65 73 74  integer}.do_test
2760: 20 61 6c 74 65 72 32 2d 37 2e 34 20 7b 0a 20 20   alter2-7.4 {.  
2770: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45  execsql {.    SE
2780: 4c 45 43 54 20 61 2c 20 74 79 70 65 6f 66 28 61  LECT a, typeof(a
2790: 29 2c 20 62 2c 20 74 79 70 65 6f 66 28 62 29 2c  ), b, typeof(b),
27a0: 20 63 2c 20 74 79 70 65 6f 66 28 63 29 20 46 52   c, typeof(c) FR
27b0: 4f 4d 20 74 31 20 4c 49 4d 49 54 20 31 3b 0a 20  OM t1 LIMIT 1;. 
27c0: 20 7d 0a 7d 20 7b 31 20 69 6e 74 65 67 65 72 20   }.} {1 integer 
27d0: 31 32 33 20 74 65 78 74 20 31 32 33 20 69 6e 74  123 text 123 int
27e0: 65 67 65 72 7d 0a 64 6f 5f 74 65 73 74 20 61 6c  eger}.do_test al
27f0: 74 65 72 32 2d 37 2e 35 20 7b 0a 20 20 73 65 74  ter2-7.5 {.  set
2800: 20 73 71 6c 20 7b 43 52 45 41 54 45 20 54 41 42   sql {CREATE TAB
2810: 4c 45 20 74 31 28 61 2c 20 62 20 44 45 46 41 55  LE t1(a, b DEFAU
2820: 4c 54 20 2d 31 32 33 2e 30 2c 20 63 20 56 41 52  LT -123.0, c VAR
2830: 43 48 41 52 28 31 30 29 20 64 65 66 61 75 6c 74  CHAR(10) default
2840: 20 35 29 7d 0a 20 20 61 6c 74 65 72 5f 74 61 62   5)}.  alter_tab
2850: 6c 65 20 74 31 20 24 73 71 6c 20 33 0a 20 20 65  le t1 $sql 3.  e
2860: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  xecsql {.    SEL
2870: 45 43 54 20 61 2c 20 74 79 70 65 6f 66 28 61 29  ECT a, typeof(a)
2880: 2c 20 62 2c 20 74 79 70 65 6f 66 28 62 29 2c 20  , b, typeof(b), 
2890: 63 2c 20 74 79 70 65 6f 66 28 63 29 20 46 52 4f  c, typeof(c) FRO
28a0: 4d 20 74 31 20 4c 49 4d 49 54 20 31 3b 0a 20 20  M t1 LIMIT 1;.  
28b0: 7d 0a 7d 20 7b 31 20 69 6e 74 65 67 65 72 20 2d  }.} {1 integer -
28c0: 31 32 33 20 69 6e 74 65 67 65 72 20 35 20 74 65  123 integer 5 te
28d0: 78 74 7d 0a 0a 23 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  xt}..#----------
28e0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
28f0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2900: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2910: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 0a 23 20  -------------.# 
2920: 54 65 73 74 20 74 68 61 74 20 55 50 44 41 54 45  Test that UPDATE
2930: 20 74 72 69 67 67 65 72 20 74 61 62 6c 65 73 20   trigger tables 
2940: 77 6f 72 6b 20 77 69 74 68 20 64 65 66 61 75 6c  work with defaul
2950: 74 20 76 61 6c 75 65 73 2c 20 61 6e 64 20 74 68  t values, and th
2960: 61 74 20 77 68 65 6e 0a 23 20 61 20 72 6f 77 20  at when.# a row 
2970: 69 73 20 75 70 64 61 74 65 64 20 74 68 65 20 64  is updated the d
2980: 65 66 61 75 6c 74 20 76 61 6c 75 65 73 20 61 72  efault values ar
2990: 65 20 63 6f 72 72 65 63 74 6c 79 20 74 72 61 6e  e correctly tran
29a0: 73 66 65 72 65 64 20 74 6f 20 74 68 65 20 0a 23  sfered to the .#
29b0: 20 6e 65 77 20 72 6f 77 2e 0a 23 20 0a 69 66 63   new row..# .ifc
29c0: 61 70 61 62 6c 65 20 74 72 69 67 67 65 72 20 7b  apable trigger {
29d0: 0a 64 62 20 66 75 6e 63 74 69 6f 6e 20 73 65 74  .db function set
29e0: 5f 76 61 6c 20 7b 73 65 74 20 3a 3a 76 61 6c 7d  _val {set ::val}
29f0: 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  .  do_test alter
2a00: 32 2d 38 2e 31 20 7b 0a 20 20 20 20 65 78 65 63  2-8.1 {.    exec
2a10: 73 71 6c 20 7b 0a 20 20 20 20 20 20 43 52 45 41  sql {.      CREA
2a20: 54 45 20 54 52 49 47 47 45 52 20 74 72 69 67 31  TE TRIGGER trig1
2a30: 20 42 45 46 4f 52 45 20 55 50 44 41 54 45 20 4f   BEFORE UPDATE O
2a40: 4e 20 74 31 20 42 45 47 49 4e 0a 20 20 20 20 20  N t1 BEGIN.     
2a50: 20 53 45 4c 45 43 54 20 73 65 74 5f 76 61 6c 28   SELECT set_val(
2a60: 0a 20 20 20 20 20 20 20 20 20 20 6f 6c 64 2e 62  .          old.b
2a70: 7c 7c 27 20 27 7c 7c 74 79 70 65 6f 66 28 6f 6c  ||' '||typeof(ol
2a80: 64 2e 62 29 7c 7c 27 20 27 7c 7c 6f 6c 64 2e 63  d.b)||' '||old.c
2a90: 7c 7c 27 20 27 7c 7c 74 79 70 65 6f 66 28 6f 6c  ||' '||typeof(ol
2aa0: 64 2e 63 29 7c 7c 27 20 27 7c 7c 0a 20 20 20 20  d.c)||' '||.    
2ab0: 20 20 20 20 20 20 6e 65 77 2e 62 7c 7c 27 20 27        new.b||' '
2ac0: 7c 7c 74 79 70 65 6f 66 28 6e 65 77 2e 62 29 7c  ||typeof(new.b)|
2ad0: 7c 27 20 27 7c 7c 6e 65 77 2e 63 7c 7c 27 20 27  |' '||new.c||' '
2ae0: 7c 7c 74 79 70 65 6f 66 28 6e 65 77 2e 63 29 20  ||typeof(new.c) 
2af0: 0a 20 20 20 20 20 20 29 3b 0a 20 20 20 20 20 20  .      );.      
2b00: 45 4e 44 3b 0a 20 20 20 20 7d 0a 20 20 20 20 6c  END;.    }.    l
2b10: 69 73 74 0a 20 20 7d 20 7b 7d 0a 7d 0a 64 6f 5f  ist.  } {}.}.do_
2b20: 74 65 73 74 20 61 6c 74 65 72 32 2d 38 2e 32 20  test alter2-8.2 
2b30: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
2b40: 20 20 55 50 44 41 54 45 20 74 31 20 53 45 54 20    UPDATE t1 SET 
2b50: 63 20 3d 20 31 30 20 57 48 45 52 45 20 61 20 3d  c = 10 WHERE a =
2b60: 20 31 3b 0a 20 20 20 20 53 45 4c 45 43 54 20 61   1;.    SELECT a
2b70: 2c 20 74 79 70 65 6f 66 28 61 29 2c 20 62 2c 20  , typeof(a), b, 
2b80: 74 79 70 65 6f 66 28 62 29 2c 20 63 2c 20 74 79  typeof(b), c, ty
2b90: 70 65 6f 66 28 63 29 20 46 52 4f 4d 20 74 31 20  peof(c) FROM t1 
2ba0: 4c 49 4d 49 54 20 31 3b 0a 20 20 7d 0a 7d 20 7b  LIMIT 1;.  }.} {
2bb0: 31 20 69 6e 74 65 67 65 72 20 2d 31 32 33 20 69  1 integer -123 i
2bc0: 6e 74 65 67 65 72 20 31 30 20 74 65 78 74 7d 0a  nteger 10 text}.
2bd0: 69 66 63 61 70 61 62 6c 65 20 74 72 69 67 67 65  ifcapable trigge
2be0: 72 20 7b 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c  r {.  do_test al
2bf0: 74 65 72 32 2d 38 2e 33 20 7b 0a 20 20 20 20 73  ter2-8.3 {.    s
2c00: 65 74 20 3a 3a 76 61 6c 0a 20 20 7d 20 7b 2d 31  et ::val.  } {-1
2c10: 32 33 20 69 6e 74 65 67 65 72 20 35 20 74 65 78  23 integer 5 tex
2c20: 74 20 2d 31 32 33 20 69 6e 74 65 67 65 72 20 31  t -123 integer 1
2c30: 30 20 74 65 78 74 7d 0a 7d 0a 0a 23 2d 2d 2d 2d  0 text}.}..#----
2c40: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2c50: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2c60: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2c70: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2c80: 2d 2d 2d 0a 23 20 54 65 73 74 20 74 68 61 74 20  ---.# Test that 
2c90: 44 45 4c 45 54 45 20 74 72 69 67 67 65 72 20 74  DELETE trigger t
2ca0: 61 62 6c 65 73 20 77 6f 72 6b 20 77 69 74 68 20  ables work with 
2cb0: 64 65 66 61 75 6c 74 20 76 61 6c 75 65 73 2c 20  default values, 
2cc0: 61 6e 64 20 74 68 61 74 20 77 68 65 6e 0a 23 20  and that when.# 
2cd0: 61 20 72 6f 77 20 69 73 20 75 70 64 61 74 65 64  a row is updated
2ce0: 20 74 68 65 20 64 65 66 61 75 6c 74 20 76 61 6c   the default val
2cf0: 75 65 73 20 61 72 65 20 63 6f 72 72 65 63 74 6c  ues are correctl
2d00: 79 20 74 72 61 6e 73 66 65 72 65 64 20 74 6f 20  y transfered to 
2d10: 74 68 65 20 0a 23 20 6e 65 77 20 72 6f 77 2e 0a  the .# new row..
2d20: 23 20 0a 69 66 63 61 70 61 62 6c 65 20 74 72 69  # .ifcapable tri
2d30: 67 67 65 72 20 7b 0a 20 20 64 6f 5f 74 65 73 74  gger {.  do_test
2d40: 20 61 6c 74 65 72 32 2d 39 2e 31 20 7b 0a 20 20   alter2-9.1 {.  
2d50: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
2d60: 20 20 43 52 45 41 54 45 20 54 52 49 47 47 45 52    CREATE TRIGGER
2d70: 20 74 72 69 67 32 20 42 45 46 4f 52 45 20 44 45   trig2 BEFORE DE
2d80: 4c 45 54 45 20 4f 4e 20 74 31 20 42 45 47 49 4e  LETE ON t1 BEGIN
2d90: 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 73 65  .      SELECT se
2da0: 74 5f 76 61 6c 28 0a 20 20 20 20 20 20 20 20 20  t_val(.         
2db0: 20 6f 6c 64 2e 62 7c 7c 27 20 27 7c 7c 74 79 70   old.b||' '||typ
2dc0: 65 6f 66 28 6f 6c 64 2e 62 29 7c 7c 27 20 27 7c  eof(old.b)||' '|
2dd0: 7c 6f 6c 64 2e 63 7c 7c 27 20 27 7c 7c 74 79 70  |old.c||' '||typ
2de0: 65 6f 66 28 6f 6c 64 2e 63 29 0a 20 20 20 20 20  eof(old.c).     
2df0: 20 29 3b 0a 20 20 20 20 20 20 45 4e 44 3b 0a 20   );.      END;. 
2e00: 20 20 20 7d 0a 20 20 20 20 6c 69 73 74 0a 20 20     }.    list.  
2e10: 7d 20 7b 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61  } {}.  do_test a
2e20: 6c 74 65 72 32 2d 39 2e 32 20 7b 0a 20 20 20 20  lter2-9.2 {.    
2e30: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20  execsql {.      
2e40: 44 45 4c 45 54 45 20 46 52 4f 4d 20 74 31 20 57  DELETE FROM t1 W
2e50: 48 45 52 45 20 61 20 3d 20 32 3b 0a 20 20 20 20  HERE a = 2;.    
2e60: 7d 0a 20 20 20 20 73 65 74 20 3a 3a 76 61 6c 0a  }.    set ::val.
2e70: 20 20 7d 20 7b 2d 31 32 33 20 69 6e 74 65 67 65    } {-123 intege
2e80: 72 20 35 20 74 65 78 74 7d 0a 7d 0a 0a 23 2d 2d  r 5 text}.}..#--
2e90: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2ea0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2eb0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2ec0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2ed0: 2d 2d 2d 2d 2d 0a 23 20 54 65 73 74 20 63 72 65  -----.# Test cre
2ee0: 61 74 69 6e 67 20 61 6e 20 69 6e 64 65 78 20 6f  ating an index o
2ef0: 6e 20 61 20 63 6f 6c 75 6d 6e 20 61 64 64 65 64  n a column added
2f00: 20 77 69 74 68 20 61 20 64 65 66 61 75 6c 74 20   with a default 
2f10: 76 61 6c 75 65 2e 20 0a 23 0a 69 66 63 61 70 61  value. .#.ifcapa
2f20: 62 6c 65 20 62 6c 6f 62 6c 69 74 20 7b 0a 20 20  ble bloblit {.  
2f30: 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 31  do_test alter2-1
2f40: 30 2e 31 20 7b 0a 20 20 20 20 65 78 65 63 73 71  0.1 {.    execsq
2f50: 6c 20 7b 0a 20 20 20 20 20 20 43 52 45 41 54 45  l {.      CREATE
2f60: 20 54 41 42 4c 45 20 74 32 28 61 29 3b 0a 20 20   TABLE t2(a);.  
2f70: 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20      INSERT INTO 
2f80: 74 32 20 56 41 4c 55 45 53 28 27 61 27 29 3b 0a  t2 VALUES('a');.
2f90: 20 20 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54        INSERT INT
2fa0: 4f 20 74 32 20 56 41 4c 55 45 53 28 27 62 27 29  O t2 VALUES('b')
2fb0: 3b 0a 20 20 20 20 20 20 49 4e 53 45 52 54 20 49  ;.      INSERT I
2fc0: 4e 54 4f 20 74 32 20 56 41 4c 55 45 53 28 27 63  NTO t2 VALUES('c
2fd0: 27 29 3b 0a 20 20 20 20 20 20 49 4e 53 45 52 54  ');.      INSERT
2fe0: 20 49 4e 54 4f 20 74 32 20 56 41 4c 55 45 53 28   INTO t2 VALUES(
2ff0: 27 64 27 29 3b 0a 20 20 20 20 7d 0a 20 20 20 20  'd');.    }.    
3000: 61 6c 74 65 72 5f 74 61 62 6c 65 20 74 32 20 7b  alter_table t2 {
3010: 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 32 28  CREATE TABLE t2(
3020: 61 2c 20 62 20 44 45 46 41 55 4c 54 20 58 27 41  a, b DEFAULT X'A
3030: 42 43 44 27 2c 20 63 20 44 45 46 41 55 4c 54 20  BCD', c DEFAULT 
3040: 4e 55 4c 4c 29 3b 7d 20 33 0a 20 20 20 20 63 61  NULL);} 3.    ca
3050: 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 20 20 53  tchsql {.      S
3060: 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 73 71 6c  ELECT * FROM sql
3070: 69 74 65 5f 6d 61 73 74 65 72 3b 0a 20 20 20 20  ite_master;.    
3080: 7d 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b 0a  }.    execsql {.
3090: 20 20 20 20 20 20 53 45 4c 45 43 54 20 71 75 6f        SELECT quo
30a0: 74 65 28 61 29 2c 20 71 75 6f 74 65 28 62 29 2c  te(a), quote(b),
30b0: 20 71 75 6f 74 65 28 63 29 20 46 52 4f 4d 20 74   quote(c) FROM t
30c0: 32 20 4c 49 4d 49 54 20 31 3b 0a 20 20 20 20 7d  2 LIMIT 1;.    }
30d0: 0a 20 20 7d 20 7b 27 61 27 20 58 27 41 42 43 44  .  } {'a' X'ABCD
30e0: 27 20 4e 55 4c 4c 7d 0a 20 20 64 6f 5f 74 65 73  ' NULL}.  do_tes
30f0: 74 20 61 6c 74 65 72 32 2d 31 30 2e 32 20 7b 0a  t alter2-10.2 {.
3100: 20 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20      execsql {.  
3110: 20 20 20 20 43 52 45 41 54 45 20 49 4e 44 45 58      CREATE INDEX
3120: 20 69 31 20 4f 4e 20 74 32 28 62 29 3b 0a 20 20   i1 ON t2(b);.  
3130: 20 20 20 20 53 45 4c 45 43 54 20 61 20 46 52 4f      SELECT a FRO
3140: 4d 20 74 32 20 57 48 45 52 45 20 62 20 3d 20 58  M t2 WHERE b = X
3150: 27 41 42 43 44 27 3b 0a 20 20 20 20 7d 0a 20 20  'ABCD';.    }.  
3160: 7d 20 7b 61 20 62 20 63 20 64 7d 0a 20 20 64 6f  } {a b c d}.  do
3170: 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 31 30 2e  _test alter2-10.
3180: 33 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c 20  3 {.    execsql 
3190: 7b 0a 20 20 20 20 20 20 44 45 4c 45 54 45 20 46  {.      DELETE F
31a0: 52 4f 4d 20 74 32 20 57 48 45 52 45 20 61 20 3d  ROM t2 WHERE a =
31b0: 20 27 63 27 3b 0a 20 20 20 20 20 20 53 45 4c 45   'c';.      SELE
31c0: 43 54 20 61 20 46 52 4f 4d 20 74 32 20 57 48 45  CT a FROM t2 WHE
31d0: 52 45 20 62 20 3d 20 58 27 41 42 43 44 27 3b 0a  RE b = X'ABCD';.
31e0: 20 20 20 20 7d 0a 20 20 7d 20 7b 61 20 62 20 64      }.  } {a b d
31f0: 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65  }.  do_test alte
3200: 72 32 2d 31 30 2e 34 20 7b 0a 20 20 20 20 65 78  r2-10.4 {.    ex
3210: 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 53 45  ecsql {.      SE
3220: 4c 45 43 54 20 63 6f 75 6e 74 28 62 29 20 46 52  LECT count(b) FR
3230: 4f 4d 20 74 32 20 57 48 45 52 45 20 62 20 3d 20  OM t2 WHERE b = 
3240: 58 27 41 42 43 44 27 3b 0a 20 20 20 20 7d 0a 20  X'ABCD';.    }. 
3250: 20 7d 20 7b 33 7d 0a 7d 0a 0a 66 69 6e 69 73 68   } {3}.}..finish
3260: 5f 74 65 73 74 0a                                _test.