SQLite4
Hex Artifact Content
Not logged in

Artifact bb30f5e46f6b23f7d1fcf375387e3de88f5032a7:


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 34 20  2}} {.  sqlite4 
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 34 20 64  atch { sqlite4 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 34  uses.  # sqlite4
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 34 5f 53 43 48 45 4d 41 20 65  SQLITE4_SCHEMA e
1ca0: 72 72 6f 72 2e 20 54 68 65 6e 2c 20 77 68 65 6e  rror. Then, when
1cb0: 20 74 72 79 69 6e 67 20 74 6f 20 72 65 63 6f 6d   trying to recom
1cc0: 70 69 6c 65 20 74 68 65 20 73 74 61 74 65 6d 65  pile the stateme
1cd0: 6e 74 2c 20 74 68 65 0a 20 20 23 20 22 75 6e 73  nt, the.  # "uns
1ce0: 75 70 70 6f 72 74 65 64 20 66 69 6c 65 20 66 6f  upported file fo
1cf0: 72 6d 61 74 22 20 65 72 72 6f 72 20 69 73 20 65  rmat" error is e
1d00: 6e 63 6f 75 6e 74 65 72 65 64 2e 20 53 6f 20 74  ncountered. So t
1d10: 68 65 20 65 72 72 6f 72 20 63 6f 64 65 0a 20 20  he error code.  
1d20: 23 20 72 65 74 75 72 6e 65 64 20 69 73 20 53 51  # returned is SQ
1d30: 4c 49 54 45 34 5f 53 43 48 45 4d 41 2c 20 6e 6f  LITE4_SCHEMA, no
1d40: 74 20 53 51 4c 49 54 45 34 5f 45 52 52 4f 52 20  t SQLITE4_ERROR 
1d50: 61 73 20 72 65 71 75 69 72 65 64 20 62 79 20 74  as required by t
1d60: 68 65 20 66 6f 6c 6c 6f 77 69 6e 67 0a 20 20 23  he following.  #
1d70: 20 74 65 73 74 20 63 61 73 65 2e 0a 20 20 23 0a   test case..  #.
1d80: 20 20 23 20 57 68 65 6e 20 74 68 65 20 71 75 65    # When the que
1d90: 72 79 20 69 73 20 61 74 74 65 6d 70 74 65 64 20  ry is attempted 
1da0: 61 20 73 65 63 6f 6e 64 20 74 69 6d 65 2c 20 74  a second time, t
1db0: 68 65 20 73 61 6d 65 20 65 72 72 6f 72 20 6d 65  he same error me
1dc0: 73 73 61 67 65 20 69 73 0a 20 20 23 20 72 65 74  ssage is.  # ret
1dd0: 75 72 6e 65 64 20 62 75 74 20 74 68 65 20 65 72  urned but the er
1de0: 72 6f 72 20 63 6f 64 65 20 69 73 20 53 51 4c 49  ror code is SQLI
1df0: 54 45 34 5f 45 52 52 4f 52 2c 20 62 65 63 61 75  TE4_ERROR, becau
1e00: 73 65 20 74 68 65 20 75 6e 73 75 70 70 6f 72 74  se the unsupport
1e10: 65 64 0a 20 20 23 20 66 69 6c 65 20 66 6f 72 6d  ed.  # file form
1e20: 61 74 20 77 61 73 20 64 65 74 65 63 74 65 64 20  at was detected 
1e30: 64 75 72 69 6e 67 20 61 20 63 61 6c 6c 20 74 6f  during a call to
1e40: 20 73 71 6c 69 74 65 34 5f 70 72 65 70 61 72 65   sqlite4_prepare
1e50: 28 29 2c 20 6e 6f 74 0a 20 20 23 20 73 71 6c 69  (), not.  # sqli
1e60: 74 65 34 5f 73 74 65 70 28 29 2e 0a 20 20 23 0a  te4_step()..  #.
1e70: 20 20 63 61 74 63 68 73 71 6c 20 7b 20 53 45 4c    catchsql { SEL
1e80: 45 43 54 20 2a 20 46 52 4f 4d 20 73 71 6c 69 74  ECT * FROM sqlit
1e90: 65 5f 6d 61 73 74 65 72 3b 20 7d 0a 20 20 63 61  e_master; }.  ca
1ea0: 74 63 68 73 71 6c 20 7b 20 53 45 4c 45 43 54 20  tchsql { SELECT 
1eb0: 2a 20 46 52 4f 4d 20 73 71 6c 69 74 65 5f 6d 61  * FROM sqlite_ma
1ec0: 73 74 65 72 3b 20 7d 0a 7d 20 7b 31 20 7b 75 6e  ster; }.} {1 {un
1ed0: 73 75 70 70 6f 72 74 65 64 20 66 69 6c 65 20 66  supported file f
1ee0: 6f 72 6d 61 74 7d 7d 0a 64 6f 5f 74 65 73 74 20  ormat}}.do_test 
1ef0: 61 6c 74 65 72 32 2d 34 2e 33 20 7b 0a 20 20 73  alter2-4.3 {.  s
1f00: 71 6c 69 74 65 34 5f 65 72 72 63 6f 64 65 20 64  qlite4_errcode d
1f10: 62 0a 7d 20 7b 53 51 4c 49 54 45 34 5f 45 52 52  b.} {SQLITE4_ERR
1f20: 4f 52 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65  OR}.do_test alte
1f30: 72 32 2d 34 2e 34 20 7b 0a 20 20 73 65 74 20 3a  r2-4.4 {.  set :
1f40: 3a 44 42 20 5b 73 71 6c 69 74 65 34 5f 63 6f 6e  :DB [sqlite4_con
1f50: 6e 65 63 74 69 6f 6e 5f 70 6f 69 6e 74 65 72 20  nection_pointer 
1f60: 64 62 5d 0a 20 20 63 61 74 63 68 73 71 6c 20 7b  db].  catchsql {
1f70: 0a 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52  .    SELECT * FR
1f80: 4f 4d 20 73 71 6c 69 74 65 5f 6d 61 73 74 65 72  OM sqlite_master
1f90: 3b 0a 20 20 7d 0a 7d 20 7b 31 20 7b 75 6e 73 75  ;.  }.} {1 {unsu
1fa0: 70 70 6f 72 74 65 64 20 66 69 6c 65 20 66 6f 72  pported file for
1fb0: 6d 61 74 7d 7d 0a 64 6f 5f 74 65 73 74 20 61 6c  mat}}.do_test al
1fc0: 74 65 72 32 2d 34 2e 35 20 7b 0a 20 20 73 71 6c  ter2-4.5 {.  sql
1fd0: 69 74 65 34 5f 65 72 72 63 6f 64 65 20 64 62 0a  ite4_errcode db.
1fe0: 7d 20 7b 53 51 4c 49 54 45 34 5f 45 52 52 4f 52  } {SQLITE4_ERROR
1ff0: 7d 0a 0a 23 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 2d 2d 2d 2d 2d 2d 0a 23 20 43 68 65 63  ---------.# Chec
2040: 6b 20 74 68 61 74 20 65 78 65 63 75 74 69 6e 67  k that executing
2050: 20 56 41 43 55 55 4d 20 6f 6e 20 61 20 66 69 6c   VACUUM on a fil
2060: 65 20 77 69 74 68 20 66 69 6c 65 2d 66 6f 72 6d  e with file-form
2070: 61 74 20 76 65 72 73 69 6f 6e 20 32 0a 23 20 72  at version 2.# r
2080: 65 73 65 74 73 20 74 68 65 20 66 69 6c 65 20 66  esets the file f
2090: 6f 72 6d 61 74 20 74 6f 20 31 2e 0a 23 0a 73 65  ormat to 1..#.se
20a0: 74 20 64 65 66 61 75 6c 74 5f 66 69 6c 65 5f 66  t default_file_f
20b0: 6f 72 6d 61 74 20 5b 65 78 70 72 20 24 53 51 4c  ormat [expr $SQL
20c0: 49 54 45 34 5f 44 45 46 41 55 4c 54 5f 46 49 4c  ITE4_DEFAULT_FIL
20d0: 45 5f 46 4f 52 4d 41 54 3d 3d 34 20 3f 20 34 20  E_FORMAT==4 ? 4 
20e0: 3a 20 31 5d 0a 69 66 63 61 70 61 62 6c 65 20 76  : 1].ifcapable v
20f0: 61 63 75 75 6d 20 7b 0a 20 20 64 6f 5f 74 65 73  acuum {.  do_tes
2100: 74 20 61 6c 74 65 72 32 2d 35 2e 31 20 7b 0a 20  t alter2-5.1 {. 
2110: 20 20 20 73 65 74 5f 66 69 6c 65 5f 66 6f 72 6d     set_file_form
2120: 61 74 20 32 0a 20 20 20 20 64 62 20 63 6c 6f 73  at 2.    db clos
2130: 65 0a 20 20 20 20 73 71 6c 69 74 65 34 20 64 62  e.    sqlite4 db
2140: 20 74 65 73 74 2e 64 62 0a 20 20 20 20 65 78 65   test.db.    exe
2150: 63 73 71 6c 20 7b 53 45 4c 45 43 54 20 31 20 46  csql {SELECT 1 F
2160: 52 4f 4d 20 73 71 6c 69 74 65 5f 6d 61 73 74 65  ROM sqlite_maste
2170: 72 20 4c 49 4d 49 54 20 31 3b 7d 0a 20 20 20 20  r LIMIT 1;}.    
2180: 67 65 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74 0a  get_file_format.
2190: 20 20 7d 20 7b 32 7d 0a 20 20 64 6f 5f 74 65 73    } {2}.  do_tes
21a0: 74 20 61 6c 74 65 72 32 2d 35 2e 32 20 7b 0a 20  t alter2-5.2 {. 
21b0: 20 20 20 65 78 65 63 73 71 6c 20 7b 20 56 41 43     execsql { VAC
21c0: 55 55 4d 20 7d 0a 20 20 7d 20 7b 7d 0a 20 20 64  UUM }.  } {}.  d
21d0: 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 35 2e  o_test alter2-5.
21e0: 33 20 7b 0a 20 20 20 20 67 65 74 5f 66 69 6c 65  3 {.    get_file
21f0: 5f 66 6f 72 6d 61 74 0a 20 20 7d 20 24 64 65 66  _format.  } $def
2200: 61 75 6c 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74  ault_file_format
2210: 0a 7d 0a 20 0a 23 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 2d 2d 2d 2d 2d 2d 2d 0a 23 20 54 65  -----------.# Te
2260: 73 74 20 74 68 61 74 20 77 68 65 6e 20 61 20 64  st that when a d
2270: 61 74 61 62 61 73 65 20 77 69 74 68 20 66 69 6c  atabase with fil
2280: 65 2d 66 6f 72 6d 61 74 20 32 20 69 73 20 6f 70  e-format 2 is op
2290: 65 6e 65 64 2c 20 6e 65 77 20 0a 23 20 64 61 74  ened, new .# dat
22a0: 61 62 61 73 65 73 20 61 72 65 20 73 74 69 6c 6c  abases are still
22b0: 20 63 72 65 61 74 65 64 20 77 69 74 68 20 66 69   created with fi
22c0: 6c 65 2d 66 6f 72 6d 61 74 20 31 2e 0a 23 0a 64  le-format 1..#.d
22d0: 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 36 2e  o_test alter2-6.
22e0: 31 20 7b 0a 20 20 64 62 20 63 6c 6f 73 65 0a 20  1 {.  db close. 
22f0: 20 73 65 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74   set_file_format
2300: 20 32 0a 20 20 73 71 6c 69 74 65 34 20 64 62 20   2.  sqlite4 db 
2310: 74 65 73 74 2e 64 62 0a 20 20 67 65 74 5f 66 69  test.db.  get_fi
2320: 6c 65 5f 66 6f 72 6d 61 74 0a 7d 20 7b 32 7d 0a  le_format.} {2}.
2330: 69 66 63 61 70 61 62 6c 65 20 61 74 74 61 63 68  ifcapable attach
2340: 20 7b 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74   {.  do_test alt
2350: 65 72 32 2d 36 2e 32 20 7b 0a 20 20 20 20 66 6f  er2-6.2 {.    fo
2360: 72 63 65 64 65 6c 65 74 65 20 74 65 73 74 32 2e  rcedelete test2.
2370: 64 62 2d 6a 6f 75 72 6e 61 6c 0a 20 20 20 20 66  db-journal.    f
2380: 6f 72 63 65 64 65 6c 65 74 65 20 74 65 73 74 32  orcedelete test2
2390: 2e 64 62 0a 20 20 20 20 65 78 65 63 73 71 6c 20  .db.    execsql 
23a0: 7b 0a 20 20 20 20 20 20 41 54 54 41 43 48 20 27  {.      ATTACH '
23b0: 74 65 73 74 32 2e 64 62 27 20 41 53 20 61 75 78  test2.db' AS aux
23c0: 3b 0a 20 20 20 20 20 20 43 52 45 41 54 45 20 54  ;.      CREATE T
23d0: 41 42 4c 45 20 61 75 78 2e 74 31 28 61 2c 20 62  ABLE aux.t1(a, b
23e0: 29 3b 0a 20 20 20 20 7d 0a 20 20 20 20 67 65 74  );.    }.    get
23f0: 5f 66 69 6c 65 5f 66 6f 72 6d 61 74 20 74 65 73  _file_format tes
2400: 74 32 2e 64 62 0a 20 20 7d 20 24 64 65 66 61 75  t2.db.  } $defau
2410: 6c 74 5f 66 69 6c 65 5f 66 6f 72 6d 61 74 0a 7d  lt_file_format.}
2420: 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d  .do_test alter2-
2430: 36 2e 33 20 7b 0a 20 20 65 78 65 63 73 71 6c 20  6.3 {.  execsql 
2440: 7b 0a 20 20 20 20 43 52 45 41 54 45 20 54 41 42  {.    CREATE TAB
2450: 4c 45 20 74 31 28 61 2c 20 62 29 3b 0a 20 20 7d  LE t1(a, b);.  }
2460: 0a 20 20 67 65 74 5f 66 69 6c 65 5f 66 6f 72 6d  .  get_file_form
2470: 61 74 20 0a 7d 20 7b 32 7d 0a 0a 23 2d 2d 2d 2d  at .} {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 2d 2d 2d 2d 2d 2d  ----------------
24c0: 2d 0a 23 20 54 65 73 74 20 74 68 61 74 20 74 79  -.# Test that ty
24d0: 70 65 73 20 61 6e 64 20 76 61 6c 75 65 73 20 66  pes and values f
24e0: 6f 72 20 63 6f 6c 75 6d 6e 73 20 61 64 64 65 64  or columns added
24f0: 20 77 69 74 68 20 64 65 66 61 75 6c 74 20 76 61   with default va
2500: 6c 75 65 73 20 0a 23 20 6f 74 68 65 72 20 74 68  lues .# other th
2510: 61 6e 20 4e 55 4c 4c 20 77 6f 72 6b 20 77 69 74  an NULL work wit
2520: 68 20 53 45 4c 45 43 54 20 73 74 61 74 65 6d 65  h SELECT stateme
2530: 6e 74 73 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 61  nts..#.do_test a
2540: 6c 74 65 72 32 2d 37 2e 31 20 7b 0a 20 20 65 78  lter2-7.1 {.  ex
2550: 65 63 73 71 6c 20 7b 0a 20 20 20 20 44 52 4f 50  ecsql {.    DROP
2560: 20 54 41 42 4c 45 20 74 31 3b 0a 20 20 20 20 43   TABLE t1;.    C
2570: 52 45 41 54 45 20 54 41 42 4c 45 20 74 31 28 61  REATE TABLE t1(a
2580: 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e  );.    INSERT IN
2590: 54 4f 20 74 31 20 56 41 4c 55 45 53 28 31 29 3b  TO t1 VALUES(1);
25a0: 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f  .    INSERT INTO
25b0: 20 74 31 20 56 41 4c 55 45 53 28 32 29 3b 0a 20   t1 VALUES(2);. 
25c0: 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74     INSERT INTO t
25d0: 31 20 56 41 4c 55 45 53 28 33 29 3b 0a 20 20 20  1 VALUES(3);.   
25e0: 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 31 20   INSERT INTO t1 
25f0: 56 41 4c 55 45 53 28 34 29 3b 0a 20 20 20 20 53  VALUES(4);.    S
2600: 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74 31 3b  ELECT * FROM t1;
2610: 0a 20 20 7d 0a 7d 20 7b 31 20 32 20 33 20 34 7d  .  }.} {1 2 3 4}
2620: 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d  .do_test alter2-
2630: 37 2e 32 20 7b 0a 20 20 73 65 74 20 73 71 6c 20  7.2 {.  set sql 
2640: 7b 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 31  {CREATE TABLE t1
2650: 28 61 2c 20 62 20 44 45 46 41 55 4c 54 20 27 31  (a, b DEFAULT '1
2660: 32 33 27 2c 20 63 20 49 4e 54 45 47 45 52 20 44  23', c INTEGER D
2670: 45 46 41 55 4c 54 20 27 31 32 33 27 29 7d 0a 20  EFAULT '123')}. 
2680: 20 61 6c 74 65 72 5f 74 61 62 6c 65 20 74 31 20   alter_table t1 
2690: 24 73 71 6c 20 33 0a 20 20 65 78 65 63 73 71 6c  $sql 3.  execsql
26a0: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a 20   {.    SELECT * 
26b0: 46 52 4f 4d 20 74 31 20 4c 49 4d 49 54 20 31 3b  FROM t1 LIMIT 1;
26c0: 0a 20 20 7d 0a 7d 20 7b 31 20 31 32 33 20 31 32  .  }.} {1 123 12
26d0: 33 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65 72  3}.do_test alter
26e0: 32 2d 37 2e 33 20 7b 0a 20 20 65 78 65 63 73 71  2-7.3 {.  execsq
26f0: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 61  l {.    SELECT a
2700: 2c 20 74 79 70 65 6f 66 28 61 29 2c 20 62 2c 20  , typeof(a), b, 
2710: 74 79 70 65 6f 66 28 62 29 2c 20 63 2c 20 74 79  typeof(b), c, ty
2720: 70 65 6f 66 28 63 29 20 46 52 4f 4d 20 74 31 20  peof(c) FROM t1 
2730: 4c 49 4d 49 54 20 31 3b 0a 20 20 7d 0a 7d 20 7b  LIMIT 1;.  }.} {
2740: 31 20 69 6e 74 65 67 65 72 20 31 32 33 20 74 65  1 integer 123 te
2750: 78 74 20 31 32 33 20 69 6e 74 65 67 65 72 7d 0a  xt 123 integer}.
2760: 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 37  do_test alter2-7
2770: 2e 34 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b  .4 {.  execsql {
2780: 0a 20 20 20 20 53 45 4c 45 43 54 20 61 2c 20 74  .    SELECT a, t
2790: 79 70 65 6f 66 28 61 29 2c 20 62 2c 20 74 79 70  ypeof(a), b, typ
27a0: 65 6f 66 28 62 29 2c 20 63 2c 20 74 79 70 65 6f  eof(b), c, typeo
27b0: 66 28 63 29 20 46 52 4f 4d 20 74 31 20 4c 49 4d  f(c) FROM t1 LIM
27c0: 49 54 20 31 3b 0a 20 20 7d 0a 7d 20 7b 31 20 69  IT 1;.  }.} {1 i
27d0: 6e 74 65 67 65 72 20 31 32 33 20 74 65 78 74 20  nteger 123 text 
27e0: 31 32 33 20 69 6e 74 65 67 65 72 7d 0a 64 6f 5f  123 integer}.do_
27f0: 74 65 73 74 20 61 6c 74 65 72 32 2d 37 2e 35 20  test alter2-7.5 
2800: 7b 0a 20 20 73 65 74 20 73 71 6c 20 7b 43 52 45  {.  set sql {CRE
2810: 41 54 45 20 54 41 42 4c 45 20 74 31 28 61 2c 20  ATE TABLE t1(a, 
2820: 62 20 44 45 46 41 55 4c 54 20 2d 31 32 33 2e 30  b DEFAULT -123.0
2830: 2c 20 63 20 56 41 52 43 48 41 52 28 31 30 29 20  , c VARCHAR(10) 
2840: 64 65 66 61 75 6c 74 20 35 29 7d 0a 20 20 61 6c  default 5)}.  al
2850: 74 65 72 5f 74 61 62 6c 65 20 74 31 20 24 73 71  ter_table t1 $sq
2860: 6c 20 33 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a  l 3.  execsql {.
2870: 20 20 20 20 53 45 4c 45 43 54 20 61 2c 20 74 79      SELECT a, ty
2880: 70 65 6f 66 28 61 29 2c 20 62 2c 20 74 79 70 65  peof(a), b, type
2890: 6f 66 28 62 29 2c 20 63 2c 20 74 79 70 65 6f 66  of(b), c, typeof
28a0: 28 63 29 20 46 52 4f 4d 20 74 31 20 4c 49 4d 49  (c) FROM t1 LIMI
28b0: 54 20 31 3b 0a 20 20 7d 0a 7d 20 7b 31 20 69 6e  T 1;.  }.} {1 in
28c0: 74 65 67 65 72 20 2d 31 32 33 20 69 6e 74 65 67  teger -123 integ
28d0: 65 72 20 35 20 74 65 78 74 7d 0a 0a 23 2d 2d 2d  er 5 text}..#---
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 2d 2d 2d  ----------------
2920: 2d 2d 2d 2d 0a 23 20 54 65 73 74 20 74 68 61 74  ----.# Test that
2930: 20 55 50 44 41 54 45 20 74 72 69 67 67 65 72 20   UPDATE trigger 
2940: 74 61 62 6c 65 73 20 77 6f 72 6b 20 77 69 74 68  tables work with
2950: 20 64 65 66 61 75 6c 74 20 76 61 6c 75 65 73 2c   default values,
2960: 20 61 6e 64 20 74 68 61 74 20 77 68 65 6e 0a 23   and that when.#
2970: 20 61 20 72 6f 77 20 69 73 20 75 70 64 61 74 65   a row is update
2980: 64 20 74 68 65 20 64 65 66 61 75 6c 74 20 76 61  d the default va
2990: 6c 75 65 73 20 61 72 65 20 63 6f 72 72 65 63 74  lues are correct
29a0: 6c 79 20 74 72 61 6e 73 66 65 72 65 64 20 74 6f  ly transfered to
29b0: 20 74 68 65 20 0a 23 20 6e 65 77 20 72 6f 77 2e   the .# new row.
29c0: 0a 23 20 0a 69 66 63 61 70 61 62 6c 65 20 74 72  .# .ifcapable tr
29d0: 69 67 67 65 72 20 7b 0a 64 62 20 66 75 6e 63 74  igger {.db funct
29e0: 69 6f 6e 20 73 65 74 5f 76 61 6c 20 7b 73 65 74  ion set_val {set
29f0: 20 3a 3a 76 61 6c 7d 0a 20 20 64 6f 5f 74 65 73   ::val}.  do_tes
2a00: 74 20 61 6c 74 65 72 32 2d 38 2e 31 20 7b 0a 20  t alter2-8.1 {. 
2a10: 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20     execsql {.   
2a20: 20 20 20 43 52 45 41 54 45 20 54 52 49 47 47 45     CREATE TRIGGE
2a30: 52 20 74 72 69 67 31 20 42 45 46 4f 52 45 20 55  R trig1 BEFORE U
2a40: 50 44 41 54 45 20 4f 4e 20 74 31 20 42 45 47 49  PDATE ON t1 BEGI
2a50: 4e 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 73  N.      SELECT s
2a60: 65 74 5f 76 61 6c 28 0a 20 20 20 20 20 20 20 20  et_val(.        
2a70: 20 20 6f 6c 64 2e 62 7c 7c 27 20 27 7c 7c 74 79    old.b||' '||ty
2a80: 70 65 6f 66 28 6f 6c 64 2e 62 29 7c 7c 27 20 27  peof(old.b)||' '
2a90: 7c 7c 6f 6c 64 2e 63 7c 7c 27 20 27 7c 7c 74 79  ||old.c||' '||ty
2aa0: 70 65 6f 66 28 6f 6c 64 2e 63 29 7c 7c 27 20 27  peof(old.c)||' '
2ab0: 7c 7c 0a 20 20 20 20 20 20 20 20 20 20 6e 65 77  ||.          new
2ac0: 2e 62 7c 7c 27 20 27 7c 7c 74 79 70 65 6f 66 28  .b||' '||typeof(
2ad0: 6e 65 77 2e 62 29 7c 7c 27 20 27 7c 7c 6e 65 77  new.b)||' '||new
2ae0: 2e 63 7c 7c 27 20 27 7c 7c 74 79 70 65 6f 66 28  .c||' '||typeof(
2af0: 6e 65 77 2e 63 29 20 0a 20 20 20 20 20 20 29 3b  new.c) .      );
2b00: 0a 20 20 20 20 20 20 45 4e 44 3b 0a 20 20 20 20  .      END;.    
2b10: 7d 0a 20 20 20 20 6c 69 73 74 0a 20 20 7d 20 7b  }.    list.  } {
2b20: 7d 0a 7d 0a 64 6f 5f 74 65 73 74 20 61 6c 74 65  }.}.do_test alte
2b30: 72 32 2d 38 2e 32 20 7b 0a 20 20 65 78 65 63 73  r2-8.2 {.  execs
2b40: 71 6c 20 7b 0a 20 20 20 20 55 50 44 41 54 45 20  ql {.    UPDATE 
2b50: 74 31 20 53 45 54 20 63 20 3d 20 31 30 20 57 48  t1 SET c = 10 WH
2b60: 45 52 45 20 61 20 3d 20 31 3b 0a 20 20 20 20 53  ERE a = 1;.    S
2b70: 45 4c 45 43 54 20 61 2c 20 74 79 70 65 6f 66 28  ELECT a, typeof(
2b80: 61 29 2c 20 62 2c 20 74 79 70 65 6f 66 28 62 29  a), b, typeof(b)
2b90: 2c 20 63 2c 20 74 79 70 65 6f 66 28 63 29 20 46  , c, typeof(c) F
2ba0: 52 4f 4d 20 74 31 20 4c 49 4d 49 54 20 31 3b 0a  ROM t1 LIMIT 1;.
2bb0: 20 20 7d 0a 7d 20 7b 31 20 69 6e 74 65 67 65 72    }.} {1 integer
2bc0: 20 2d 31 32 33 20 69 6e 74 65 67 65 72 20 31 30   -123 integer 10
2bd0: 20 74 65 78 74 7d 0a 69 66 63 61 70 61 62 6c 65   text}.ifcapable
2be0: 20 74 72 69 67 67 65 72 20 7b 0a 20 20 64 6f 5f   trigger {.  do_
2bf0: 74 65 73 74 20 61 6c 74 65 72 32 2d 38 2e 33 20  test alter2-8.3 
2c00: 7b 0a 20 20 20 20 73 65 74 20 3a 3a 76 61 6c 0a  {.    set ::val.
2c10: 20 20 7d 20 7b 2d 31 32 33 20 69 6e 74 65 67 65    } {-123 intege
2c20: 72 20 35 20 74 65 78 74 20 2d 31 32 33 20 69 6e  r 5 text -123 in
2c30: 74 65 67 65 72 20 31 30 20 74 65 78 74 7d 0a 7d  teger 10 text}.}
2c40: 0a 0a 23 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 2d 2d 2d 2d 2d 2d 2d 0a 23 20 54 65 73  ----------.# Tes
2c90: 74 20 74 68 61 74 20 44 45 4c 45 54 45 20 74 72  t that DELETE tr
2ca0: 69 67 67 65 72 20 74 61 62 6c 65 73 20 77 6f 72  igger tables wor
2cb0: 6b 20 77 69 74 68 20 64 65 66 61 75 6c 74 20 76  k with default v
2cc0: 61 6c 75 65 73 2c 20 61 6e 64 20 74 68 61 74 20  alues, and that 
2cd0: 77 68 65 6e 0a 23 20 61 20 72 6f 77 20 69 73 20  when.# a row is 
2ce0: 75 70 64 61 74 65 64 20 74 68 65 20 64 65 66 61  updated the defa
2cf0: 75 6c 74 20 76 61 6c 75 65 73 20 61 72 65 20 63  ult values are c
2d00: 6f 72 72 65 63 74 6c 79 20 74 72 61 6e 73 66 65  orrectly transfe
2d10: 72 65 64 20 74 6f 20 74 68 65 20 0a 23 20 6e 65  red to the .# ne
2d20: 77 20 72 6f 77 2e 0a 23 20 0a 69 66 63 61 70 61  w row..# .ifcapa
2d30: 62 6c 65 20 74 72 69 67 67 65 72 20 7b 0a 20 20  ble trigger {.  
2d40: 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 39  do_test alter2-9
2d50: 2e 31 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c  .1 {.    execsql
2d60: 20 7b 0a 20 20 20 20 20 20 43 52 45 41 54 45 20   {.      CREATE 
2d70: 54 52 49 47 47 45 52 20 74 72 69 67 32 20 42 45  TRIGGER trig2 BE
2d80: 46 4f 52 45 20 44 45 4c 45 54 45 20 4f 4e 20 74  FORE DELETE ON t
2d90: 31 20 42 45 47 49 4e 0a 20 20 20 20 20 20 53 45  1 BEGIN.      SE
2da0: 4c 45 43 54 20 73 65 74 5f 76 61 6c 28 0a 20 20  LECT set_val(.  
2db0: 20 20 20 20 20 20 20 20 6f 6c 64 2e 62 7c 7c 27          old.b||'
2dc0: 20 27 7c 7c 74 79 70 65 6f 66 28 6f 6c 64 2e 62   '||typeof(old.b
2dd0: 29 7c 7c 27 20 27 7c 7c 6f 6c 64 2e 63 7c 7c 27  )||' '||old.c||'
2de0: 20 27 7c 7c 74 79 70 65 6f 66 28 6f 6c 64 2e 63   '||typeof(old.c
2df0: 29 0a 20 20 20 20 20 20 29 3b 0a 20 20 20 20 20  ).      );.     
2e00: 20 45 4e 44 3b 0a 20 20 20 20 7d 0a 20 20 20 20   END;.    }.    
2e10: 6c 69 73 74 0a 20 20 7d 20 7b 7d 0a 20 20 64 6f  list.  } {}.  do
2e20: 5f 74 65 73 74 20 61 6c 74 65 72 32 2d 39 2e 32  _test alter2-9.2
2e30: 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b   {.    execsql {
2e40: 0a 20 20 20 20 20 20 44 45 4c 45 54 45 20 46 52  .      DELETE FR
2e50: 4f 4d 20 74 31 20 57 48 45 52 45 20 61 20 3d 20  OM t1 WHERE a = 
2e60: 32 3b 0a 20 20 20 20 7d 0a 20 20 20 20 73 65 74  2;.    }.    set
2e70: 20 3a 3a 76 61 6c 0a 20 20 7d 20 7b 2d 31 32 33   ::val.  } {-123
2e80: 20 69 6e 74 65 67 65 72 20 35 20 74 65 78 74 7d   integer 5 text}
2e90: 0a 7d 0a 0a 23 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 2d 2d 2d 2d 2d 2d 2d 0a 23 20 54  ------------.# T
2ee0: 65 73 74 20 63 72 65 61 74 69 6e 67 20 61 6e 20  est creating an 
2ef0: 69 6e 64 65 78 20 6f 6e 20 61 20 63 6f 6c 75 6d  index on a colum
2f00: 6e 20 61 64 64 65 64 20 77 69 74 68 20 61 20 64  n added with a d
2f10: 65 66 61 75 6c 74 20 76 61 6c 75 65 2e 20 0a 23  efault value. .#
2f20: 0a 69 66 63 61 70 61 62 6c 65 20 62 6c 6f 62 6c  .ifcapable blobl
2f30: 69 74 20 7b 0a 20 20 64 6f 5f 74 65 73 74 20 61  it {.  do_test a
2f40: 6c 74 65 72 32 2d 31 30 2e 31 20 7b 0a 20 20 20  lter2-10.1 {.   
2f50: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20   execsql {.     
2f60: 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 32   CREATE TABLE t2
2f70: 28 61 29 3b 0a 20 20 20 20 20 20 49 4e 53 45 52  (a);.      INSER
2f80: 54 20 49 4e 54 4f 20 74 32 20 56 41 4c 55 45 53  T INTO t2 VALUES
2f90: 28 27 61 27 29 3b 0a 20 20 20 20 20 20 49 4e 53  ('a');.      INS
2fa0: 45 52 54 20 49 4e 54 4f 20 74 32 20 56 41 4c 55  ERT INTO t2 VALU
2fb0: 45 53 28 27 62 27 29 3b 0a 20 20 20 20 20 20 49  ES('b');.      I
2fc0: 4e 53 45 52 54 20 49 4e 54 4f 20 74 32 20 56 41  NSERT INTO t2 VA
2fd0: 4c 55 45 53 28 27 63 27 29 3b 0a 20 20 20 20 20  LUES('c');.     
2fe0: 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 32 20   INSERT INTO t2 
2ff0: 56 41 4c 55 45 53 28 27 64 27 29 3b 0a 20 20 20  VALUES('d');.   
3000: 20 7d 0a 20 20 20 20 61 6c 74 65 72 5f 74 61 62   }.    alter_tab
3010: 6c 65 20 74 32 20 7b 43 52 45 41 54 45 20 54 41  le t2 {CREATE TA
3020: 42 4c 45 20 74 32 28 61 2c 20 62 20 44 45 46 41  BLE t2(a, b DEFA
3030: 55 4c 54 20 58 27 41 42 43 44 27 2c 20 63 20 44  ULT X'ABCD', c D
3040: 45 46 41 55 4c 54 20 4e 55 4c 4c 29 3b 7d 20 33  EFAULT NULL);} 3
3050: 0a 20 20 20 20 63 61 74 63 68 73 71 6c 20 7b 0a  .    catchsql {.
3060: 20 20 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46        SELECT * F
3070: 52 4f 4d 20 73 71 6c 69 74 65 5f 6d 61 73 74 65  ROM sqlite_maste
3080: 72 3b 0a 20 20 20 20 7d 0a 20 20 20 20 65 78 65  r;.    }.    exe
3090: 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 53 45 4c  csql {.      SEL
30a0: 45 43 54 20 71 75 6f 74 65 28 61 29 2c 20 71 75  ECT quote(a), qu
30b0: 6f 74 65 28 62 29 2c 20 71 75 6f 74 65 28 63 29  ote(b), quote(c)
30c0: 20 46 52 4f 4d 20 74 32 20 4c 49 4d 49 54 20 31   FROM t2 LIMIT 1
30d0: 3b 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 27 61 27  ;.    }.  } {'a'
30e0: 20 58 27 41 42 43 44 27 20 4e 55 4c 4c 7d 0a 20   X'ABCD' NULL}. 
30f0: 20 64 6f 5f 74 65 73 74 20 61 6c 74 65 72 32 2d   do_test alter2-
3100: 31 30 2e 32 20 7b 0a 20 20 20 20 65 78 65 63 73  10.2 {.    execs
3110: 71 6c 20 7b 0a 20 20 20 20 20 20 43 52 45 41 54  ql {.      CREAT
3120: 45 20 49 4e 44 45 58 20 69 31 20 4f 4e 20 74 32  E INDEX i1 ON t2
3130: 28 62 29 3b 0a 20 20 20 20 20 20 53 45 4c 45 43  (b);.      SELEC
3140: 54 20 61 20 46 52 4f 4d 20 74 32 20 57 48 45 52  T a FROM t2 WHER
3150: 45 20 62 20 3d 20 58 27 41 42 43 44 27 3b 0a 20  E b = X'ABCD';. 
3160: 20 20 20 7d 0a 20 20 7d 20 7b 61 20 62 20 63 20     }.  } {a b c 
3170: 64 7d 0a 20 20 64 6f 5f 74 65 73 74 20 61 6c 74  d}.  do_test alt
3180: 65 72 32 2d 31 30 2e 33 20 7b 0a 20 20 20 20 65  er2-10.3 {.    e
3190: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 44  xecsql {.      D
31a0: 45 4c 45 54 45 20 46 52 4f 4d 20 74 32 20 57 48  ELETE FROM t2 WH
31b0: 45 52 45 20 61 20 3d 20 27 63 27 3b 0a 20 20 20  ERE a = 'c';.   
31c0: 20 20 20 53 45 4c 45 43 54 20 61 20 46 52 4f 4d     SELECT a FROM
31d0: 20 74 32 20 57 48 45 52 45 20 62 20 3d 20 58 27   t2 WHERE b = X'
31e0: 41 42 43 44 27 3b 0a 20 20 20 20 7d 0a 20 20 7d  ABCD';.    }.  }
31f0: 20 7b 61 20 62 20 64 7d 0a 20 20 64 6f 5f 74 65   {a b d}.  do_te
3200: 73 74 20 61 6c 74 65 72 32 2d 31 30 2e 34 20 7b  st alter2-10.4 {
3210: 0a 20 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20  .    execsql {. 
3220: 20 20 20 20 20 53 45 4c 45 43 54 20 63 6f 75 6e       SELECT coun
3230: 74 28 62 29 20 46 52 4f 4d 20 74 32 20 57 48 45  t(b) FROM t2 WHE
3240: 52 45 20 62 20 3d 20 58 27 41 42 43 44 27 3b 0a  RE b = X'ABCD';.
3250: 20 20 20 20 7d 0a 20 20 7d 20 7b 33 7d 0a 7d 0a      }.  } {3}.}.
3260: 0a 66 69 6e 69 73 68 5f 74 65 73 74 0a           .finish_test.