/ Hex Artifact Content
Login

Artifact 75f731508f1bf27ba09a6075c66cd02216ba464b:


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 65 78 65 63 73 71 6c 20 7b 20 53 45 4c 45 43   execsql { SELEC
1090: 54 20 2a 20 46 52 4f 4d 20 61 62 63 3b 20 7d 0a  T * FROM abc; }.
10a0: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
10b0: 55 50 44 41 54 45 20 61 62 63 20 53 45 54 20 64  UPDATE abc SET d
10c0: 20 3d 20 31 31 20 57 48 45 52 45 20 63 20 49 53   = 11 WHERE c IS
10d0: 20 4e 55 4c 4c 20 41 4e 44 20 61 3c 34 3b 0a 20   NULL AND a<4;. 
10e0: 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d     SELECT * FROM
10f0: 20 61 62 63 3b 0a 20 20 7d 0a 7d 20 7b 31 20 32   abc;.  }.} {1 2
1100: 20 31 30 20 7b 7d 20 33 20 34 20 7b 7d 20 31 31   10 {} 3 4 {} 11
1110: 20 35 20 36 20 7b 7d 20 7b 7d 7d 0a 64 6f 5f 74   5 6 {} {}}.do_t
1120: 65 73 74 20 61 6c 74 65 72 32 2d 31 2e 31 30 20  est alter2-1.10 
1130: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
1140: 20 20 53 45 4c 45 43 54 20 74 79 70 65 6f 66 28    SELECT typeof(
1150: 64 29 20 46 52 4f 4d 20 61 62 63 3b 0a 20 20 7d  d) FROM abc;.  }
1160: 0a 7d 20 7b 6e 75 6c 6c 20 69 6e 74 65 67 65 72  .} {null integer
1170: 20 6e 75 6c 6c 7d 0a 64 6f 5f 74 65 73 74 20 61   null}.do_test a
1180: 6c 74 65 72 32 2d 31 2e 39 39 20 7b 0a 20 20 65  lter2-1.99 {.  e
1190: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 44 52 4f  xecsql {.    DRO
11a0: 50 20 54 41 42 4c 45 20 61 62 63 3b 0a 20 20 7d  P TABLE abc;.  }
11b0: 0a 7d 20 7b 7d 0a 0a 23 2d 2d 2d 2d 2d 2d 2d 2d  .} {}..#--------
11c0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
11d0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
11e0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
11f0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 0a  ---------------.
1200: 23 20 54 65 73 74 20 74 68 61 74 20 76 69 65 77  # Test that view
1210: 73 20 77 6f 72 6b 20 77 68 65 6e 20 74 68 65 20  s work when the 
1220: 75 6e 64 65 72 6c 79 69 6e 67 20 74 61 62 6c 65  underlying table
1230: 20 73 74 72 75 63 74 75 72 65 20 69 73 20 63 68   structure is ch
1240: 61 6e 67 65 64 2e 0a 23 0a 69 66 63 61 70 61 62  anged..#.ifcapab
1250: 6c 65 20 76 69 65 77 20 7b 0a 20 20 64 6f 5f 74  le view {.  do_t
1260: 65 73 74 20 61 6c 74 65 72 32 2d 32 2e 31 20 7b  est alter2-2.1 {
1270: 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20  .    execsql {. 
1280: 20 20 20 20 20 43 52 45 41 54 45 20 54 41 42 4c       CREATE TABL
1290: 45 20 61 62 63 32 28 61 2c 20 62 2c 20 63 29 3b  E abc2(a, b, c);
12a0: 0a 20 20 20 20 20 20 49 4e 53 45 52 54 20 49 4e  .      INSERT IN
12b0: 54 4f 20 61 62 63 32 20 56 41 4c 55 45 53 28 31  TO abc2 VALUES(1
12c0: 2c 20 32 2c 20 31 30 29 3b 0a 20 20 20 20 20 20  , 2, 10);.      
12d0: 49 4e 53 45 52 54 20 49 4e 54 4f 20 61 62 63 32  INSERT INTO abc2
12e0: 20 56 41 4c 55 45 53 28 33 2c 20 34 2c 20 4e 55   VALUES(3, 4, NU
12f0: 4c 4c 29 3b 0a 20 20 20 20 20 20 49 4e 53 45 52  LL);.      INSER
1300: 54 20 49 4e 54 4f 20 61 62 63 32 20 56 41 4c 55  T INTO abc2 VALU
1310: 45 53 28 35 2c 20 36 2c 20 4e 55 4c 4c 29 3b 0a  ES(5, 6, NULL);.
1320: 20 20 20 20 20 20 43 52 45 41 54 45 20 56 49 45        CREATE VIE
1330: 57 20 61 62 63 32 5f 76 20 41 53 20 53 45 4c 45  W abc2_v AS SELE
1340: 43 54 20 2a 20 46 52 4f 4d 20 61 62 63 32 3b 0a  CT * FROM abc2;.
1350: 20 20 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46        SELECT * F
1360: 52 4f 4d 20 61 62 63 32 5f 76 3b 0a 20 20 20 20  ROM abc2_v;.    
1370: 7d 0a 20 20 7d 20 7b 31 20 32 20 31 30 20 33 20  }.  } {1 2 10 3 
1380: 34 20 7b 7d 20 35 20 36 20 7b 7d 7d 0a 20 20 64  4 {} 5 6 {}}.  d
1390: 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 32 2e  o_test alter2-2.
13a0: 32 20 7b 0a 20 20 20 20 23 20 41 4c 54 45 52 20  2 {.    # ALTER 
13b0: 54 41 42 4c 45 20 61 62 63 20 41 44 44 20 43 4f  TABLE abc ADD CO
13c0: 4c 55 4d 4e 20 64 3b 0a 20 20 20 20 61 6c 74 65  LUMN d;.    alte
13d0: 72 5f 74 61 62 6c 65 20 61 62 63 32 20 7b 43 52  r_table abc2 {CR
13e0: 45 41 54 45 20 54 41 42 4c 45 20 61 62 63 32 28  EATE TABLE abc2(
13f0: 61 2c 20 62 2c 20 63 2c 20 64 29 3b 7d 0a 20 20  a, b, c, d);}.  
1400: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
1410: 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20    SELECT * FROM 
1420: 61 62 63 32 5f 76 3b 0a 20 20 20 20 7d 0a 20 20  abc2_v;.    }.  
1430: 7d 20 7b 31 20 32 20 31 30 20 7b 7d 20 33 20 34  } {1 2 10 {} 3 4
1440: 20 7b 7d 20 7b 7d 20 35 20 36 20 7b 7d 20 7b 7d   {} {} 5 6 {} {}
1450: 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65  }.  do_test alte
1460: 72 32 2d 32 2e 33 20 7b 0a 20 20 20 20 65 78 65  r2-2.3 {.    exe
1470: 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 44 52 4f  csql {.      DRO
1480: 50 20 54 41 42 4c 45 20 61 62 63 32 3b 0a 20 20  P TABLE abc2;.  
1490: 20 20 20 20 44 52 4f 50 20 56 49 45 57 20 61 62      DROP VIEW ab
14a0: 63 32 5f 76 3b 0a 20 20 20 20 7d 0a 20 20 7d 20  c2_v;.    }.  } 
14b0: 7b 7d 0a 7d 0a 0a 23 2d 2d 2d 2d 2d 2d 2d 2d 2d  {}.}..#---------
14c0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
14d0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
14e0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
14f0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 0a 23  --------------.#
1500: 20 54 65 73 74 20 74 68 61 74 20 74 72 69 67 67   Test that trigg
1510: 65 72 73 20 77 6f 72 6b 20 77 68 65 6e 20 61 20  ers work when a 
1520: 73 68 6f 72 74 20 72 6f 77 20 69 73 20 63 6f 70  short row is cop
1530: 69 65 64 20 74 6f 20 74 68 65 20 6f 6c 64 2e 2a  ied to the old.*
1540: 0a 23 20 74 72 69 67 67 65 72 20 70 73 65 75 64  .# trigger pseud
1550: 6f 2d 74 61 62 6c 65 2e 0a 23 0a 69 66 63 61 70  o-table..#.ifcap
1560: 61 62 6c 65 20 74 72 69 67 67 65 72 20 7b 0a 20  able trigger {. 
1570: 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d   do_test alter2-
1580: 33 2e 31 20 7b 0a 20 20 20 20 65 78 65 63 73 71  3.1 {.    execsq
1590: 6c 20 7b 0a 20 20 20 20 20 20 43 52 45 41 54 45  l {.      CREATE
15a0: 20 54 41 42 4c 45 20 61 62 63 33 28 61 2c 20 62   TABLE abc3(a, b
15b0: 29 3b 0a 20 20 20 20 20 20 43 52 45 41 54 45 20  );.      CREATE 
15c0: 54 41 42 4c 45 20 62 6c 6f 67 28 6f 2c 20 6e 29  TABLE blog(o, n)
15d0: 3b 0a 20 20 20 20 20 20 43 52 45 41 54 45 20 54  ;.      CREATE T
15e0: 52 49 47 47 45 52 20 61 62 63 33 5f 74 20 41 46  RIGGER abc3_t AF
15f0: 54 45 52 20 55 50 44 41 54 45 20 4f 46 20 62 20  TER UPDATE OF b 
1600: 4f 4e 20 61 62 63 33 20 42 45 47 49 4e 0a 20 20  ON abc3 BEGIN.  
1610: 20 20 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54        INSERT INT
1620: 4f 20 62 6c 6f 67 20 56 41 4c 55 45 53 28 6f 6c  O blog VALUES(ol
1630: 64 2e 62 2c 20 6e 65 77 2e 62 29 3b 0a 20 20 20  d.b, new.b);.   
1640: 20 20 20 45 4e 44 3b 0a 20 20 20 20 7d 0a 20 20     END;.    }.  
1650: 7d 20 7b 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61  } {}.  do_test a
1660: 6c 74 65 72 32 2d 33 2e 32 20 7b 0a 20 20 20 20  lter2-3.2 {.    
1670: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20  execsql {.      
1680: 49 4e 53 45 52 54 20 49 4e 54 4f 20 61 62 63 33  INSERT INTO abc3
1690: 20 56 41 4c 55 45 53 28 31 2c 20 34 29 3b 0a 20   VALUES(1, 4);. 
16a0: 20 20 20 20 20 55 50 44 41 54 45 20 61 62 63 33       UPDATE abc3
16b0: 20 53 45 54 20 62 20 3d 20 32 20 57 48 45 52 45   SET b = 2 WHERE
16c0: 20 62 20 3d 20 34 3b 0a 20 20 20 20 20 20 53 45   b = 4;.      SE
16d0: 4c 45 43 54 20 2a 20 46 52 4f 4d 20 62 6c 6f 67  LECT * FROM blog
16e0: 3b 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 34 20 32  ;.    }.  } {4 2
16f0: 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65  }.  do_test alte
1700: 72 32 2d 33 2e 33 20 7b 0a 20 20 20 20 65 78 65  r2-3.3 {.    exe
1710: 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 49 4e 53  csql {.      INS
1720: 45 52 54 20 49 4e 54 4f 20 61 62 63 33 20 56 41  ERT INTO abc3 VA
1730: 4c 55 45 53 28 33 2c 20 34 29 3b 0a 20 20 20 20  LUES(3, 4);.    
1740: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 61 62    INSERT INTO ab
1750: 63 33 20 56 41 4c 55 45 53 28 35 2c 20 36 29 3b  c3 VALUES(5, 6);
1760: 0a 20 20 20 20 7d 0a 20 20 20 20 61 6c 74 65 72  .    }.    alter
1770: 5f 74 61 62 6c 65 20 61 62 63 33 20 7b 43 52 45  _table abc3 {CRE
1780: 41 54 45 20 54 41 42 4c 45 20 61 62 63 33 28 61  ATE TABLE abc3(a
1790: 2c 20 62 2c 20 63 29 3b 7d 0a 20 20 20 20 65 78  , b, c);}.    ex
17a0: 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 53 45  ecsql {.      SE
17b0: 4c 45 43 54 20 2a 20 46 52 4f 4d 20 61 62 63 33  LECT * FROM abc3
17c0: 3b 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 31 20 32  ;.    }.  } {1 2
17d0: 20 7b 7d 20 33 20 34 20 7b 7d 20 35 20 36 20 7b   {} 3 4 {} 5 6 {
17e0: 7d 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74  }}.  do_test alt
17f0: 65 72 32 2d 33 2e 34 20 7b 0a 20 20 20 20 65 78  er2-3.4 {.    ex
1800: 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 55 50  ecsql {.      UP
1810: 44 41 54 45 20 61 62 63 33 20 53 45 54 20 62 20  DATE abc3 SET b 
1820: 3d 20 62 2a 32 20 57 48 45 52 45 20 61 3c 34 3b  = b*2 WHERE a<4;
1830: 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 2a 20  .      SELECT * 
1840: 46 52 4f 4d 20 61 62 63 33 3b 0a 20 20 20 20 7d  FROM abc3;.    }
1850: 0a 20 20 7d 20 7b 31 20 34 20 7b 7d 20 33 20 38  .  } {1 4 {} 3 8
1860: 20 7b 7d 20 35 20 36 20 7b 7d 7d 0a 20 20 64 6f   {} 5 6 {}}.  do
1870: 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 33 2e 35  _test alter2-3.5
1880: 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b   {.    execsql {
1890: 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 2a 20  .      SELECT * 
18a0: 46 52 4f 4d 20 62 6c 6f 67 3b 0a 20 20 20 20 7d  FROM blog;.    }
18b0: 0a 20 20 7d 20 7b 34 20 32 20 32 20 34 20 34 20  .  } {4 2 2 4 4 
18c0: 38 7d 0a 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c  8}..  do_test al
18d0: 74 65 72 32 2d 33 2e 36 20 7b 0a 20 20 20 20 65  ter2-3.6 {.    e
18e0: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 43  xecsql {.      C
18f0: 52 45 41 54 45 20 54 41 42 4c 45 20 63 6c 6f 67  REATE TABLE clog
1900: 28 6f 2c 20 6e 29 3b 0a 20 20 20 20 20 20 43 52  (o, n);.      CR
1910: 45 41 54 45 20 54 52 49 47 47 45 52 20 61 62 63  EATE TRIGGER abc
1920: 33 5f 74 32 20 41 46 54 45 52 20 55 50 44 41 54  3_t2 AFTER UPDAT
1930: 45 20 4f 46 20 63 20 4f 4e 20 61 62 63 33 20 42  E OF c ON abc3 B
1940: 45 47 49 4e 0a 20 20 20 20 20 20 20 20 49 4e 53  EGIN.        INS
1950: 45 52 54 20 49 4e 54 4f 20 63 6c 6f 67 20 56 41  ERT INTO clog VA
1960: 4c 55 45 53 28 6f 6c 64 2e 63 2c 20 6e 65 77 2e  LUES(old.c, new.
1970: 63 29 3b 0a 20 20 20 20 20 20 45 4e 44 3b 0a 20  c);.      END;. 
1980: 20 20 20 20 20 55 50 44 41 54 45 20 61 62 63 33       UPDATE abc3
1990: 20 53 45 54 20 63 20 3d 20 61 2a 32 3b 0a 20 20   SET c = a*2;.  
19a0: 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f      SELECT * FRO
19b0: 4d 20 63 6c 6f 67 3b 0a 20 20 20 20 7d 0a 20 20  M clog;.    }.  
19c0: 7d 20 7b 7b 7d 20 32 20 7b 7d 20 36 20 7b 7d 20  } {{} 2 {} 6 {} 
19d0: 31 30 7d 0a 7d 20 65 6c 73 65 20 7b 0a 20 20 65  10}.} else {.  e
19e0: 78 65 63 73 71 6c 20 7b 20 43 52 45 41 54 45 20  xecsql { CREATE 
19f0: 54 41 42 4c 45 20 61 62 63 33 28 61 2c 20 62 29  TABLE abc3(a, b)
1a00: 3b 20 7d 0a 7d 0a 0a 23 2d 2d 2d 2d 2d 2d 2d 2d  ; }.}..#--------
1a10: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1a20: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1a30: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1a40: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 0a 23 20  -------------.# 
1a50: 43 68 65 63 6b 20 74 68 61 74 20 61 6e 20 65 72  Check that an er
1a60: 72 6f 72 20 6f 63 63 75 72 73 20 69 66 20 74 68  ror occurs if th
1a70: 65 20 64 61 74 61 62 61 73 65 20 69 73 20 75 70  e database is up
1a80: 67 72 61 64 65 64 20 74 6f 20 61 20 66 69 6c 65  graded to a file
1a90: 0a 23 20 66 6f 72 6d 61 74 20 74 68 61 74 20 53  .# format that S
1aa0: 51 4c 69 74 65 20 64 6f 65 73 20 6e 6f 74 20 73  QLite does not s
1ab0: 75 70 70 6f 72 74 20 28 69 6e 20 74 68 69 73 20  upport (in this 
1ac0: 63 61 73 65 20 35 29 2e 20 4e 6f 74 65 3a 20 54  case 5). Note: T
1ad0: 68 65 20 0a 23 20 66 69 6c 65 20 66 6f 72 6d 61  he .# file forma
1ae0: 74 20 69 73 20 63 68 65 63 6b 65 64 20 65 61 63  t is checked eac
1af0: 68 20 74 69 6d 65 20 74 68 65 20 73 63 68 65 6d  h time the schem
1b00: 61 20 69 73 20 72 65 61 64 2c 20 73 6f 20 63 68  a is read, so ch
1b10: 61 6e 67 69 6e 67 20 74 68 65 0a 23 20 66 69 6c  anging the.# fil
1b20: 65 20 66 6f 72 6d 61 74 20 72 65 71 75 69 72 65  e format require
1b30: 73 20 69 6e 63 72 65 6d 65 6e 74 69 6e 67 20 74  s incrementing t
1b40: 68 65 20 73 63 68 65 6d 61 20 63 6f 6f 6b 69 65  he schema cookie
1b50: 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65  ..#.do_test alte
1b60: 72 32 2d 34 2e 31 20 7b 0a 20 20 64 62 20 63 6c  r2-4.1 {.  db cl
1b70: 6f 73 65 0a 20 20 73 65 74 5f 66 69 6c 65 5f 66  ose.  set_file_f
1b80: 6f 72 6d 61 74 20 35 0a 20 20 63 61 74 63 68 20  ormat 5.  catch 
1b90: 7b 20 73 71 6c 69 74 65 33 20 64 62 20 74 65 73  { sqlite3 db tes
1ba0: 74 2e 64 62 20 7d 0a 20 20 73 65 74 20 7b 7d 20  t.db }.  set {} 
1bb0: 7b 7d 0a 7d 20 7b 7d 0a 64 6f 5f 74 65 73 74 20  {}.} {}.do_test 
1bc0: 61 6c 74 65 72 32 2d 34 2e 32 20 7b 0a 20 20 23  alter2-4.2 {.  #
1bd0: 20 57 65 20 68 61 76 65 20 74 6f 20 72 75 6e 20   We have to run 
1be0: 74 77 6f 20 71 75 65 72 69 65 73 20 68 65 72 65  two queries here
1bf0: 20 62 65 63 61 75 73 65 20 74 68 65 20 54 63 6c   because the Tcl
1c00: 20 69 6e 74 65 72 66 61 63 65 20 75 73 65 73 0a   interface uses.
1c10: 20 20 23 20 73 71 6c 69 74 65 33 5f 70 72 65 70    # sqlite3_prep
1c20: 61 72 65 5f 76 32 28 29 2e 20 49 6e 20 74 68 69  are_v2(). In thi
1c30: 73 20 63 61 73 65 2c 20 74 68 65 20 66 69 72 73  s case, the firs
1c40: 74 20 71 75 65 72 79 20 65 6e 63 6f 75 6e 74 65  t query encounte
1c50: 72 73 20 61 6e 20 0a 20 20 23 20 53 51 4c 49 54  rs an .  # SQLIT
1c60: 45 5f 53 43 48 45 4d 41 20 65 72 72 6f 72 2e 20  E_SCHEMA error. 
1c70: 54 68 65 6e 2c 20 77 68 65 6e 20 74 72 79 69 6e  Then, when tryin
1c80: 67 20 74 6f 20 72 65 63 6f 6d 70 69 6c 65 20 74  g to recompile t
1c90: 68 65 20 73 74 61 74 65 6d 65 6e 74 2c 20 74 68  he statement, th
1ca0: 65 0a 20 20 23 20 22 75 6e 73 75 70 70 6f 72 74  e.  # "unsupport
1cb0: 65 64 20 66 69 6c 65 20 66 6f 72 6d 61 74 22 20  ed file format" 
1cc0: 65 72 72 6f 72 20 69 73 20 65 6e 63 6f 75 6e 74  error is encount
1cd0: 65 72 65 64 2e 20 53 6f 20 74 68 65 20 65 72 72  ered. So the err
1ce0: 6f 72 20 63 6f 64 65 0a 20 20 23 20 72 65 74 75  or code.  # retu
1cf0: 72 6e 65 64 20 69 73 20 53 51 4c 49 54 45 5f 53  rned is SQLITE_S
1d00: 43 48 45 4d 41 2c 20 6e 6f 74 20 53 51 4c 49 54  CHEMA, not SQLIT
1d10: 45 5f 45 52 52 4f 52 20 61 73 20 72 65 71 75 69  E_ERROR as requi
1d20: 72 65 64 20 62 79 20 74 68 65 20 66 6f 6c 6c 6f  red by the follo
1d30: 77 69 6e 67 0a 20 20 23 20 74 65 73 74 20 63 61  wing.  # test ca
1d40: 73 65 2e 0a 20 20 23 0a 20 20 23 20 57 68 65 6e  se..  #.  # When
1d50: 20 74 68 65 20 71 75 65 72 79 20 69 73 20 61 74   the query is at
1d60: 74 65 6d 70 74 65 64 20 61 20 73 65 63 6f 6e 64  tempted a second
1d70: 20 74 69 6d 65 2c 20 74 68 65 20 73 61 6d 65 20   time, the same 
1d80: 65 72 72 6f 72 20 6d 65 73 73 61 67 65 20 69 73  error message is
1d90: 0a 20 20 23 20 72 65 74 75 72 6e 65 64 20 62 75  .  # returned bu
1da0: 74 20 74 68 65 20 65 72 72 6f 72 20 63 6f 64 65  t the error code
1db0: 20 69 73 20 53 51 4c 49 54 45 5f 45 52 52 4f 52   is SQLITE_ERROR
1dc0: 2c 20 62 65 63 61 75 73 65 20 74 68 65 20 75 6e  , because the un
1dd0: 73 75 70 70 6f 72 74 65 64 0a 20 20 23 20 66 69  supported.  # fi
1de0: 6c 65 20 66 6f 72 6d 61 74 20 77 61 73 20 64 65  le format was de
1df0: 74 65 63 74 65 64 20 64 75 72 69 6e 67 20 61 20  tected during a 
1e00: 63 61 6c 6c 20 74 6f 20 73 71 6c 69 74 65 33 5f  call to sqlite3_
1e10: 70 72 65 70 61 72 65 28 29 2c 20 6e 6f 74 0a 20  prepare(), not. 
1e20: 20 23 20 73 71 6c 69 74 65 33 5f 73 74 65 70 28   # sqlite3_step(
1e30: 29 2e 0a 20 20 23 0a 20 20 63 61 74 63 68 73 71  )..  #.  catchsq
1e40: 6c 20 7b 20 53 45 4c 45 43 54 20 2a 20 46 52 4f  l { SELECT * FRO
1e50: 4d 20 73 71 6c 69 74 65 5f 6d 61 73 74 65 72 3b  M sqlite_master;
1e60: 20 7d 0a 20 20 63 61 74 63 68 73 71 6c 20 7b 20   }.  catchsql { 
1e70: 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 73 71  SELECT * FROM sq
1e80: 6c 69 74 65 5f 6d 61 73 74 65 72 3b 20 7d 0a 7d  lite_master; }.}
1e90: 20 7b 31 20 7b 75 6e 73 75 70 70 6f 72 74 65 64   {1 {unsupported
1ea0: 20 66 69 6c 65 20 66 6f 72 6d 61 74 7d 7d 0a 64   file format}}.d
1eb0: 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 34 2e  o_test alter2-4.
1ec0: 33 20 7b 0a 20 20 73 71 6c 69 74 65 33 5f 65 72  3 {.  sqlite3_er
1ed0: 72 63 6f 64 65 20 64 62 0a 7d 20 7b 53 51 4c 49  rcode db.} {SQLI
1ee0: 54 45 5f 45 52 52 4f 52 7d 0a 64 6f 5f 74 65 73  TE_ERROR}.do_tes
1ef0: 74 20 61 6c 74 65 72 32 2d 34 2e 34 20 7b 0a 20  t alter2-4.4 {. 
1f00: 20 73 65 74 20 3a 3a 44 42 20 5b 73 71 6c 69 74   set ::DB [sqlit
1f10: 65 33 5f 63 6f 6e 6e 65 63 74 69 6f 6e 5f 70 6f  e3_connection_po
1f20: 69 6e 74 65 72 20 64 62 5d 0a 20 20 63 61 74 63  inter db].  catc
1f30: 68 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  hsql {.    SELEC
1f40: 54 20 2a 20 46 52 4f 4d 20 73 71 6c 69 74 65 5f  T * FROM sqlite_
1f50: 6d 61 73 74 65 72 3b 0a 20 20 7d 0a 7d 20 7b 31  master;.  }.} {1
1f60: 20 7b 75 6e 73 75 70 70 6f 72 74 65 64 20 66 69   {unsupported fi
1f70: 6c 65 20 66 6f 72 6d 61 74 7d 7d 0a 64 6f 5f 74  le format}}.do_t
1f80: 65 73 74 20 61 6c 74 65 72 32 2d 34 2e 35 20 7b  est alter2-4.5 {
1f90: 0a 20 20 73 71 6c 69 74 65 33 5f 65 72 72 63 6f  .  sqlite3_errco
1fa0: 64 65 20 64 62 0a 7d 20 7b 53 51 4c 49 54 45 5f  de db.} {SQLITE_
1fb0: 45 52 52 4f 52 7d 0a 0a 23 2d 2d 2d 2d 2d 2d 2d  ERROR}..#-------
1fc0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1fd0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1fe0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
1ff0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 0a 23  --------------.#
2000: 20 43 68 65 63 6b 20 74 68 61 74 20 65 78 65 63   Check that exec
2010: 75 74 69 6e 67 20 56 41 43 55 55 4d 20 6f 6e 20  uting VACUUM on 
2020: 61 20 66 69 6c 65 20 77 69 74 68 20 66 69 6c 65  a file with file
2030: 2d 66 6f 72 6d 61 74 20 76 65 72 73 69 6f 6e 20  -format version 
2040: 32 0a 23 20 72 65 73 65 74 73 20 74 68 65 20 66  2.# resets the f
2050: 69 6c 65 20 66 6f 72 6d 61 74 20 74 6f 20 31 2e  ile format to 1.
2060: 0a 23 0a 73 65 74 20 64 65 66 61 75 6c 74 5f 66  .#.set default_f
2070: 69 6c 65 5f 66 6f 72 6d 61 74 20 5b 65 78 70 72  ile_format [expr
2080: 20 24 53 51 4c 49 54 45 5f 44 45 46 41 55 4c 54   $SQLITE_DEFAULT
2090: 5f 46 49 4c 45 5f 46 4f 52 4d 41 54 3d 3d 34 20  _FILE_FORMAT==4 
20a0: 3f 20 34 20 3a 20 31 5d 0a 69 66 63 61 70 61 62  ? 4 : 1].ifcapab
20b0: 6c 65 20 76 61 63 75 75 6d 20 7b 0a 20 20 64 6f  le vacuum {.  do
20c0: 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 35 2e 31  _test alter2-5.1
20d0: 20 7b 0a 20 20 20 20 73 65 74 5f 66 69 6c 65 5f   {.    set_file_
20e0: 66 6f 72 6d 61 74 20 32 0a 20 20 20 20 64 62 20  format 2.    db 
20f0: 63 6c 6f 73 65 0a 20 20 20 20 73 71 6c 69 74 65  close.    sqlite
2100: 33 20 64 62 20 74 65 73 74 2e 64 62 0a 20 20 20  3 db test.db.   
2110: 20 65 78 65 63 73 71 6c 20 7b 53 45 4c 45 43 54   execsql {SELECT
2120: 20 31 20 46 52 4f 4d 20 73 71 6c 69 74 65 5f 6d   1 FROM sqlite_m
2130: 61 73 74 65 72 20 4c 49 4d 49 54 20 31 3b 7d 0a  aster LIMIT 1;}.
2140: 20 20 20 20 67 65 74 5f 66 69 6c 65 5f 66 6f 72      get_file_for
2150: 6d 61 74 0a 20 20 7d 20 7b 32 7d 0a 20 20 64 6f  mat.  } {2}.  do
2160: 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 35 2e 32  _test alter2-5.2
2170: 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b   {.    execsql {
2180: 20 56 41 43 55 55 4d 20 7d 0a 20 20 7d 20 7b 7d   VACUUM }.  } {}
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 33 20 7b 0a 20 20 20 20 67 65 74 5f  2-5.3 {.    get_
21b0: 66 69 6c 65 5f 66 6f 72 6d 61 74 0a 20 20 7d 20  file_format.  } 
21c0: 24 64 65 66 61 75 6c 74 5f 66 69 6c 65 5f 66 6f  $default_file_fo
21d0: 72 6d 61 74 0a 7d 0a 20 0a 23 2d 2d 2d 2d 2d 2d  rmat.}. .#------
21e0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
21f0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2200: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2210: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 0a  ---------------.
2220: 23 20 54 65 73 74 20 74 68 61 74 20 77 68 65 6e  # Test that when
2230: 20 61 20 64 61 74 61 62 61 73 65 20 77 69 74 68   a database with
2240: 20 66 69 6c 65 2d 66 6f 72 6d 61 74 20 32 20 69   file-format 2 i
2250: 73 20 6f 70 65 6e 65 64 2c 20 6e 65 77 20 0a 23  s opened, new .#
2260: 20 64 61 74 61 62 61 73 65 73 20 61 72 65 20 73   databases are s
2270: 74 69 6c 6c 20 63 72 65 61 74 65 64 20 77 69 74  till created wit
2280: 68 20 66 69 6c 65 2d 66 6f 72 6d 61 74 20 31 2e  h file-format 1.
2290: 0a 23 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  .#.do_test alter
22a0: 32 2d 36 2e 31 20 7b 0a 20 20 64 62 20 63 6c 6f  2-6.1 {.  db clo
22b0: 73 65 0a 20 20 73 65 74 5f 66 69 6c 65 5f 66 6f  se.  set_file_fo
22c0: 72 6d 61 74 20 32 0a 20 20 73 71 6c 69 74 65 33  rmat 2.  sqlite3
22d0: 20 64 62 20 74 65 73 74 2e 64 62 0a 20 20 67 65   db test.db.  ge
22e0: 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74 0a 7d 20  t_file_format.} 
22f0: 7b 32 7d 0a 69 66 63 61 70 61 62 6c 65 20 61 74  {2}.ifcapable at
2300: 74 61 63 68 20 7b 0a 20 20 64 6f 5f 74 65 73 74  tach {.  do_test
2310: 20 61 6c 74 65 72 32 2d 36 2e 32 20 7b 0a 20 20   alter2-6.2 {.  
2320: 20 20 66 69 6c 65 20 64 65 6c 65 74 65 20 2d 66    file delete -f
2330: 6f 72 63 65 20 74 65 73 74 32 2e 64 62 2d 6a 6f  orce test2.db-jo
2340: 75 72 6e 61 6c 0a 20 20 20 20 66 69 6c 65 20 64  urnal.    file d
2350: 65 6c 65 74 65 20 2d 66 6f 72 63 65 20 74 65 73  elete -force tes
2360: 74 32 2e 64 62 0a 20 20 20 20 65 78 65 63 73 71  t2.db.    execsq
2370: 6c 20 7b 0a 20 20 20 20 20 20 41 54 54 41 43 48  l {.      ATTACH
2380: 20 27 74 65 73 74 32 2e 64 62 27 20 41 53 20 61   'test2.db' AS a
2390: 75 78 3b 0a 20 20 20 20 20 20 43 52 45 41 54 45  ux;.      CREATE
23a0: 20 54 41 42 4c 45 20 61 75 78 2e 74 31 28 61 2c   TABLE aux.t1(a,
23b0: 20 62 29 3b 0a 20 20 20 20 7d 0a 20 20 20 20 67   b);.    }.    g
23c0: 65 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74 20 74  et_file_format t
23d0: 65 73 74 32 2e 64 62 0a 20 20 7d 20 24 64 65 66  est2.db.  } $def
23e0: 61 75 6c 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74  ault_file_format
23f0: 0a 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  .}.do_test alter
2400: 32 2d 36 2e 33 20 7b 0a 20 20 65 78 65 63 73 71  2-6.3 {.  execsq
2410: 6c 20 7b 0a 20 20 20 20 43 52 45 41 54 45 20 54  l {.    CREATE T
2420: 41 42 4c 45 20 74 31 28 61 2c 20 62 29 3b 0a 20  ABLE t1(a, b);. 
2430: 20 7d 0a 20 20 67 65 74 5f 66 69 6c 65 5f 66 6f   }.  get_file_fo
2440: 72 6d 61 74 20 0a 7d 20 7b 32 7d 0a 0a 23 2d 2d  rmat .} {2}..#--
2450: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2460: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2470: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2480: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2490: 2d 2d 2d 0a 23 20 54 65 73 74 20 74 68 61 74 20  ---.# Test that 
24a0: 74 79 70 65 73 20 61 6e 64 20 76 61 6c 75 65 73  types and values
24b0: 20 66 6f 72 20 63 6f 6c 75 6d 6e 73 20 61 64 64   for columns add
24c0: 65 64 20 77 69 74 68 20 64 65 66 61 75 6c 74 20  ed with default 
24d0: 76 61 6c 75 65 73 20 0a 23 20 6f 74 68 65 72 20  values .# other 
24e0: 74 68 61 6e 20 4e 55 4c 4c 20 77 6f 72 6b 20 77  than NULL work w
24f0: 69 74 68 20 53 45 4c 45 43 54 20 73 74 61 74 65  ith SELECT state
2500: 6d 65 6e 74 73 2e 0a 23 0a 64 6f 5f 74 65 73 74  ments..#.do_test
2510: 20 61 6c 74 65 72 32 2d 37 2e 31 20 7b 0a 20 20   alter2-7.1 {.  
2520: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 44 52  execsql {.    DR
2530: 4f 50 20 54 41 42 4c 45 20 74 31 3b 0a 20 20 20  OP TABLE t1;.   
2540: 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 31   CREATE TABLE t1
2550: 28 61 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20  (a);.    INSERT 
2560: 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53 28 31  INTO t1 VALUES(1
2570: 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e  );.    INSERT IN
2580: 54 4f 20 74 31 20 56 41 4c 55 45 53 28 32 29 3b  TO t1 VALUES(2);
2590: 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f  .    INSERT INTO
25a0: 20 74 31 20 56 41 4c 55 45 53 28 33 29 3b 0a 20   t1 VALUES(3);. 
25b0: 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74     INSERT INTO t
25c0: 31 20 56 41 4c 55 45 53 28 34 29 3b 0a 20 20 20  1 VALUES(4);.   
25d0: 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74   SELECT * FROM t
25e0: 31 3b 0a 20 20 7d 0a 7d 20 7b 31 20 32 20 33 20  1;.  }.} {1 2 3 
25f0: 34 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  4}.do_test alter
2600: 32 2d 37 2e 32 20 7b 0a 20 20 73 65 74 20 73 71  2-7.2 {.  set sq
2610: 6c 20 7b 43 52 45 41 54 45 20 54 41 42 4c 45 20  l {CREATE TABLE 
2620: 74 31 28 61 2c 20 62 20 44 45 46 41 55 4c 54 20  t1(a, b DEFAULT 
2630: 27 31 32 33 27 2c 20 63 20 49 4e 54 45 47 45 52  '123', c INTEGER
2640: 20 44 45 46 41 55 4c 54 20 27 31 32 33 27 29 7d   DEFAULT '123')}
2650: 0a 20 20 61 6c 74 65 72 5f 74 61 62 6c 65 20 74  .  alter_table t
2660: 31 20 24 73 71 6c 20 33 0a 20 20 65 78 65 63 73  1 $sql 3.  execs
2670: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
2680: 2a 20 46 52 4f 4d 20 74 31 20 4c 49 4d 49 54 20  * FROM t1 LIMIT 
2690: 31 3b 0a 20 20 7d 0a 7d 20 7b 31 20 31 32 33 20  1;.  }.} {1 123 
26a0: 31 32 33 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74  123}.do_test alt
26b0: 65 72 32 2d 37 2e 33 20 7b 0a 20 20 65 78 65 63  er2-7.3 {.  exec
26c0: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
26d0: 20 61 2c 20 74 79 70 65 6f 66 28 61 29 2c 20 62   a, typeof(a), b
26e0: 2c 20 74 79 70 65 6f 66 28 62 29 2c 20 63 2c 20  , typeof(b), c, 
26f0: 74 79 70 65 6f 66 28 63 29 20 46 52 4f 4d 20 74  typeof(c) FROM t
2700: 31 20 4c 49 4d 49 54 20 31 3b 0a 20 20 7d 0a 7d  1 LIMIT 1;.  }.}
2710: 20 7b 31 20 69 6e 74 65 67 65 72 20 31 32 33 20   {1 integer 123 
2720: 74 65 78 74 20 31 32 33 20 69 6e 74 65 67 65 72  text 123 integer
2730: 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32  }.do_test alter2
2740: 2d 37 2e 34 20 7b 0a 20 20 65 78 65 63 73 71 6c  -7.4 {.  execsql
2750: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 61 2c   {.    SELECT a,
2760: 20 74 79 70 65 6f 66 28 61 29 2c 20 62 2c 20 74   typeof(a), b, t
2770: 79 70 65 6f 66 28 62 29 2c 20 63 2c 20 74 79 70  ypeof(b), c, typ
2780: 65 6f 66 28 63 29 20 46 52 4f 4d 20 74 31 20 4c  eof(c) FROM t1 L
2790: 49 4d 49 54 20 31 3b 0a 20 20 7d 0a 7d 20 7b 31  IMIT 1;.  }.} {1
27a0: 20 69 6e 74 65 67 65 72 20 31 32 33 20 74 65 78   integer 123 tex
27b0: 74 20 31 32 33 20 69 6e 74 65 67 65 72 7d 0a 64  t 123 integer}.d
27c0: 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 37 2e  o_test alter2-7.
27d0: 35 20 7b 0a 20 20 73 65 74 20 73 71 6c 20 7b 43  5 {.  set sql {C
27e0: 52 45 41 54 45 20 54 41 42 4c 45 20 74 31 28 61  REATE TABLE t1(a
27f0: 2c 20 62 20 44 45 46 41 55 4c 54 20 2d 31 32 33  , b DEFAULT -123
2800: 2e 30 2c 20 63 20 56 41 52 43 48 41 52 28 31 30  .0, c VARCHAR(10
2810: 29 20 64 65 66 61 75 6c 74 20 35 29 7d 0a 20 20  ) default 5)}.  
2820: 61 6c 74 65 72 5f 74 61 62 6c 65 20 74 31 20 24  alter_table t1 $
2830: 73 71 6c 20 33 0a 20 20 65 78 65 63 73 71 6c 20  sql 3.  execsql 
2840: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 61 2c 20  {.    SELECT a, 
2850: 74 79 70 65 6f 66 28 61 29 2c 20 62 2c 20 74 79  typeof(a), b, ty
2860: 70 65 6f 66 28 62 29 2c 20 63 2c 20 74 79 70 65  peof(b), c, type
2870: 6f 66 28 63 29 20 46 52 4f 4d 20 74 31 20 4c 49  of(c) FROM t1 LI
2880: 4d 49 54 20 31 3b 0a 20 20 7d 0a 7d 20 7b 31 20  MIT 1;.  }.} {1 
2890: 69 6e 74 65 67 65 72 20 2d 31 32 33 20 69 6e 74  integer -123 int
28a0: 65 67 65 72 20 35 20 74 65 78 74 7d 0a 0a 23 2d  eger 5 text}..#-
28b0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
28c0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
28d0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
28e0: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
28f0: 2d 2d 2d 2d 2d 2d 0a 23 20 54 65 73 74 20 74 68  ------.# Test th
2900: 61 74 20 55 50 44 41 54 45 20 74 72 69 67 67 65  at UPDATE trigge
2910: 72 20 74 61 62 6c 65 73 20 77 6f 72 6b 20 77 69  r tables work wi
2920: 74 68 20 64 65 66 61 75 6c 74 20 76 61 6c 75 65  th default value
2930: 73 2c 20 61 6e 64 20 74 68 61 74 20 77 68 65 6e  s, and that when
2940: 0a 23 20 61 20 72 6f 77 20 69 73 20 75 70 64 61  .# a row is upda
2950: 74 65 64 20 74 68 65 20 64 65 66 61 75 6c 74 20  ted the default 
2960: 76 61 6c 75 65 73 20 61 72 65 20 63 6f 72 72 65  values are corre
2970: 63 74 6c 79 20 74 72 61 6e 73 66 65 72 65 64 20  ctly transfered 
2980: 74 6f 20 74 68 65 20 0a 23 20 6e 65 77 20 72 6f  to the .# new ro
2990: 77 2e 0a 23 20 0a 69 66 63 61 70 61 62 6c 65 20  w..# .ifcapable 
29a0: 74 72 69 67 67 65 72 20 7b 0a 64 62 20 66 75 6e  trigger {.db fun
29b0: 63 74 69 6f 6e 20 73 65 74 5f 76 61 6c 20 7b 73  ction set_val {s
29c0: 65 74 20 3a 3a 76 61 6c 7d 0a 20 20 64 6f 5f 74  et ::val}.  do_t
29d0: 65 73 74 20 61 6c 74 65 72 32 2d 38 2e 31 20 7b  est alter2-8.1 {
29e0: 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20  .    execsql {. 
29f0: 20 20 20 20 20 43 52 45 41 54 45 20 54 52 49 47       CREATE TRIG
2a00: 47 45 52 20 74 72 69 67 31 20 42 45 46 4f 52 45  GER trig1 BEFORE
2a10: 20 55 50 44 41 54 45 20 4f 4e 20 74 31 20 42 45   UPDATE ON t1 BE
2a20: 47 49 4e 0a 20 20 20 20 20 20 53 45 4c 45 43 54  GIN.      SELECT
2a30: 20 73 65 74 5f 76 61 6c 28 0a 20 20 20 20 20 20   set_val(.      
2a40: 20 20 20 20 6f 6c 64 2e 62 7c 7c 27 20 27 7c 7c      old.b||' '||
2a50: 74 79 70 65 6f 66 28 6f 6c 64 2e 62 29 7c 7c 27  typeof(old.b)||'
2a60: 20 27 7c 7c 6f 6c 64 2e 63 7c 7c 27 20 27 7c 7c   '||old.c||' '||
2a70: 74 79 70 65 6f 66 28 6f 6c 64 2e 63 29 7c 7c 27  typeof(old.c)||'
2a80: 20 27 7c 7c 0a 20 20 20 20 20 20 20 20 20 20 6e   '||.          n
2a90: 65 77 2e 62 7c 7c 27 20 27 7c 7c 74 79 70 65 6f  ew.b||' '||typeo
2aa0: 66 28 6e 65 77 2e 62 29 7c 7c 27 20 27 7c 7c 6e  f(new.b)||' '||n
2ab0: 65 77 2e 63 7c 7c 27 20 27 7c 7c 74 79 70 65 6f  ew.c||' '||typeo
2ac0: 66 28 6e 65 77 2e 63 29 20 0a 20 20 20 20 20 20  f(new.c) .      
2ad0: 29 3b 0a 20 20 20 20 20 20 45 4e 44 3b 0a 20 20  );.      END;.  
2ae0: 20 20 7d 0a 20 20 20 20 6c 69 73 74 0a 20 20 7d    }.    list.  }
2af0: 20 7b 7d 0a 7d 0a 64 6f 5f 74 65 73 74 20 61 6c   {}.}.do_test al
2b00: 74 65 72 32 2d 38 2e 32 20 7b 0a 20 20 65 78 65  ter2-8.2 {.  exe
2b10: 63 73 71 6c 20 7b 0a 20 20 20 20 55 50 44 41 54  csql {.    UPDAT
2b20: 45 20 74 31 20 53 45 54 20 63 20 3d 20 31 30 20  E t1 SET c = 10 
2b30: 57 48 45 52 45 20 61 20 3d 20 31 3b 0a 20 20 20  WHERE a = 1;.   
2b40: 20 53 45 4c 45 43 54 20 61 2c 20 74 79 70 65 6f   SELECT a, typeo
2b50: 66 28 61 29 2c 20 62 2c 20 74 79 70 65 6f 66 28  f(a), b, typeof(
2b60: 62 29 2c 20 63 2c 20 74 79 70 65 6f 66 28 63 29  b), c, typeof(c)
2b70: 20 46 52 4f 4d 20 74 31 20 4c 49 4d 49 54 20 31   FROM t1 LIMIT 1
2b80: 3b 0a 20 20 7d 0a 7d 20 7b 31 20 69 6e 74 65 67  ;.  }.} {1 integ
2b90: 65 72 20 2d 31 32 33 20 69 6e 74 65 67 65 72 20  er -123 integer 
2ba0: 31 30 20 74 65 78 74 7d 0a 69 66 63 61 70 61 62  10 text}.ifcapab
2bb0: 6c 65 20 74 72 69 67 67 65 72 20 7b 0a 20 20 64  le trigger {.  d
2bc0: 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 38 2e  o_test alter2-8.
2bd0: 33 20 7b 0a 20 20 20 20 73 65 74 20 3a 3a 76 61  3 {.    set ::va
2be0: 6c 0a 20 20 7d 20 7b 2d 31 32 33 20 69 6e 74 65  l.  } {-123 inte
2bf0: 67 65 72 20 35 20 74 65 78 74 20 2d 31 32 33 20  ger 5 text -123 
2c00: 69 6e 74 65 67 65 72 20 31 30 20 74 65 78 74 7d  integer 10 text}
2c10: 0a 7d 0a 0a 23 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  .}..#-----------
2c20: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2c30: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
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 0a 23 20 54  ------------.# T
2c60: 65 73 74 20 74 68 61 74 20 44 45 4c 45 54 45 20  est that DELETE 
2c70: 74 72 69 67 67 65 72 20 74 61 62 6c 65 73 20 77  trigger tables w
2c80: 6f 72 6b 20 77 69 74 68 20 64 65 66 61 75 6c 74  ork with default
2c90: 20 76 61 6c 75 65 73 2c 20 61 6e 64 20 74 68 61   values, and tha
2ca0: 74 20 77 68 65 6e 0a 23 20 61 20 72 6f 77 20 69  t when.# a row i
2cb0: 73 20 75 70 64 61 74 65 64 20 74 68 65 20 64 65  s updated the de
2cc0: 66 61 75 6c 74 20 76 61 6c 75 65 73 20 61 72 65  fault values are
2cd0: 20 63 6f 72 72 65 63 74 6c 79 20 74 72 61 6e 73   correctly trans
2ce0: 66 65 72 65 64 20 74 6f 20 74 68 65 20 0a 23 20  fered to the .# 
2cf0: 6e 65 77 20 72 6f 77 2e 0a 23 20 0a 69 66 63 61  new row..# .ifca
2d00: 70 61 62 6c 65 20 74 72 69 67 67 65 72 20 7b 0a  pable trigger {.
2d10: 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32    do_test alter2
2d20: 2d 39 2e 31 20 7b 0a 20 20 20 20 65 78 65 63 73  -9.1 {.    execs
2d30: 71 6c 20 7b 0a 20 20 20 20 20 20 43 52 45 41 54  ql {.      CREAT
2d40: 45 20 54 52 49 47 47 45 52 20 74 72 69 67 32 20  E TRIGGER trig2 
2d50: 42 45 46 4f 52 45 20 44 45 4c 45 54 45 20 4f 4e  BEFORE DELETE ON
2d60: 20 74 31 20 42 45 47 49 4e 0a 20 20 20 20 20 20   t1 BEGIN.      
2d70: 53 45 4c 45 43 54 20 73 65 74 5f 76 61 6c 28 0a  SELECT set_val(.
2d80: 20 20 20 20 20 20 20 20 20 20 6f 6c 64 2e 62 7c            old.b|
2d90: 7c 27 20 27 7c 7c 74 79 70 65 6f 66 28 6f 6c 64  |' '||typeof(old
2da0: 2e 62 29 7c 7c 27 20 27 7c 7c 6f 6c 64 2e 63 7c  .b)||' '||old.c|
2db0: 7c 27 20 27 7c 7c 74 79 70 65 6f 66 28 6f 6c 64  |' '||typeof(old
2dc0: 2e 63 29 0a 20 20 20 20 20 20 29 3b 0a 20 20 20  .c).      );.   
2dd0: 20 20 20 45 4e 44 3b 0a 20 20 20 20 7d 0a 20 20     END;.    }.  
2de0: 20 20 6c 69 73 74 0a 20 20 7d 20 7b 7d 0a 20 20    list.  } {}.  
2df0: 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 39  do_test alter2-9
2e00: 2e 32 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c  .2 {.    execsql
2e10: 20 7b 0a 20 20 20 20 20 20 44 45 4c 45 54 45 20   {.      DELETE 
2e20: 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 61 20  FROM t1 WHERE a 
2e30: 3d 20 32 3b 0a 20 20 20 20 7d 0a 20 20 20 20 73  = 2;.    }.    s
2e40: 65 74 20 3a 3a 76 61 6c 0a 20 20 7d 20 7b 2d 31  et ::val.  } {-1
2e50: 32 33 20 69 6e 74 65 67 65 72 20 35 20 74 65 78  23 integer 5 tex
2e60: 74 7d 0a 7d 0a 0a 23 2d 2d 2d 2d 2d 2d 2d 2d 2d  t}.}..#---------
2e70: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
2e80: 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d  ----------------
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 0a 23  --------------.#
2eb0: 20 54 65 73 74 20 63 72 65 61 74 69 6e 67 20 61   Test creating a
2ec0: 6e 20 69 6e 64 65 78 20 6f 6e 20 61 20 63 6f 6c  n index on a col
2ed0: 75 6d 6e 20 61 64 64 65 64 20 77 69 74 68 20 61  umn added with a
2ee0: 20 64 65 66 61 75 6c 74 20 76 61 6c 75 65 2e 20   default value. 
2ef0: 0a 23 0a 69 66 63 61 70 61 62 6c 65 20 62 6c 6f  .#.ifcapable blo
2f00: 62 6c 69 74 20 7b 0a 20 20 64 6f 5f 74 65 73 74  blit {.  do_test
2f10: 20 61 6c 74 65 72 32 2d 31 30 2e 31 20 7b 0a 20   alter2-10.1 {. 
2f20: 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20     execsql {.   
2f30: 20 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20     CREATE TABLE 
2f40: 74 32 28 61 29 3b 0a 20 20 20 20 20 20 49 4e 53  t2(a);.      INS
2f50: 45 52 54 20 49 4e 54 4f 20 74 32 20 56 41 4c 55  ERT INTO t2 VALU
2f60: 45 53 28 27 61 27 29 3b 0a 20 20 20 20 20 20 49  ES('a');.      I
2f70: 4e 53 45 52 54 20 49 4e 54 4f 20 74 32 20 56 41  NSERT INTO t2 VA
2f80: 4c 55 45 53 28 27 62 27 29 3b 0a 20 20 20 20 20  LUES('b');.     
2f90: 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 32 20   INSERT INTO t2 
2fa0: 56 41 4c 55 45 53 28 27 63 27 29 3b 0a 20 20 20  VALUES('c');.   
2fb0: 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74     INSERT INTO t
2fc0: 32 20 56 41 4c 55 45 53 28 27 64 27 29 3b 0a 20  2 VALUES('d');. 
2fd0: 20 20 20 7d 0a 20 20 20 20 61 6c 74 65 72 5f 74     }.    alter_t
2fe0: 61 62 6c 65 20 74 32 20 7b 43 52 45 41 54 45 20  able t2 {CREATE 
2ff0: 54 41 42 4c 45 20 74 32 28 61 2c 20 62 20 44 45  TABLE t2(a, b DE
3000: 46 41 55 4c 54 20 58 27 41 42 43 44 27 2c 20 63  FAULT X'ABCD', c
3010: 20 44 45 46 41 55 4c 54 20 4e 55 4c 4c 29 3b 7d   DEFAULT NULL);}
3020: 20 33 0a 20 20 20 20 63 61 74 63 68 73 71 6c 20   3.    catchsql 
3030: 7b 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 2a  {.      SELECT *
3040: 20 46 52 4f 4d 20 73 71 6c 69 74 65 5f 6d 61 73   FROM sqlite_mas
3050: 74 65 72 3b 0a 20 20 20 20 7d 0a 20 20 20 20 65  ter;.    }.    e
3060: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 53  xecsql {.      S
3070: 45 4c 45 43 54 20 71 75 6f 74 65 28 61 29 2c 20  ELECT quote(a), 
3080: 71 75 6f 74 65 28 62 29 2c 20 71 75 6f 74 65 28  quote(b), quote(
3090: 63 29 20 46 52 4f 4d 20 74 32 20 4c 49 4d 49 54  c) FROM t2 LIMIT
30a0: 20 31 3b 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 27   1;.    }.  } {'
30b0: 61 27 20 58 27 41 42 43 44 27 20 4e 55 4c 4c 7d  a' X'ABCD' NULL}
30c0: 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  .  do_test alter
30d0: 32 2d 31 30 2e 32 20 7b 0a 20 20 20 20 65 78 65  2-10.2 {.    exe
30e0: 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 43 52 45  csql {.      CRE
30f0: 41 54 45 20 49 4e 44 45 58 20 69 31 20 4f 4e 20  ATE INDEX i1 ON 
3100: 74 32 28 62 29 3b 0a 20 20 20 20 20 20 53 45 4c  t2(b);.      SEL
3110: 45 43 54 20 61 20 46 52 4f 4d 20 74 32 20 57 48  ECT a FROM t2 WH
3120: 45 52 45 20 62 20 3d 20 58 27 41 42 43 44 27 3b  ERE b = X'ABCD';
3130: 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 61 20 62 20  .    }.  } {a b 
3140: 63 20 64 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61  c d}.  do_test a
3150: 6c 74 65 72 32 2d 31 30 2e 33 20 7b 0a 20 20 20  lter2-10.3 {.   
3160: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20   execsql {.     
3170: 20 44 45 4c 45 54 45 20 46 52 4f 4d 20 74 32 20   DELETE FROM t2 
3180: 57 48 45 52 45 20 61 20 3d 20 27 63 27 3b 0a 20  WHERE a = 'c';. 
3190: 20 20 20 20 20 53 45 4c 45 43 54 20 61 20 46 52       SELECT a FR
31a0: 4f 4d 20 74 32 20 57 48 45 52 45 20 62 20 3d 20  OM t2 WHERE b = 
31b0: 58 27 41 42 43 44 27 3b 0a 20 20 20 20 7d 0a 20  X'ABCD';.    }. 
31c0: 20 7d 20 7b 61 20 62 20 64 7d 0a 20 20 64 6f 5f   } {a b d}.  do_
31d0: 74 65 73 74 20 61 6c 74 65 72 32 2d 31 30 2e 34  test alter2-10.4
31e0: 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b   {.    execsql {
31f0: 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 63 6f  .      SELECT co
3200: 75 6e 74 28 62 29 20 46 52 4f 4d 20 74 32 20 57  unt(b) FROM t2 W
3210: 48 45 52 45 20 62 20 3d 20 58 27 41 42 43 44 27  HERE b = X'ABCD'
3220: 3b 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 33 7d 0a  ;.    }.  } {3}.
3230: 7d 0a 0a 66 69 6e 69 73 68 5f 74 65 73 74 0a     }..finish_test.