SQLite

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !vtab {
  finish_test
  return
}

# Capture the output of a pragma in a TEMP table.
#
proc capture_pragma {db tabname sql} {
  $db eval "DROP TABLE IF EXISTS temp.$tabname"
  set once 1
  $db eval $sql x {
    if {$once} {
      set once 0
      set ins "INSERT INTO $tabname VALUES"
      set crtab "CREATE TEMP TABLE $tabname "
      set sep "("
      foreach col $x(*) {
        append ins ${sep}\$x($col)
        append crtab ${sep}\"$col\"
        set sep ,
      }
      append ins )
      append crtab )
      $db eval $crtab
    }
    $db eval $ins
  }
}


load_static_extension db wholenumber;
do_test index7-1.1 {
  # Able to parse and manage partial indices
  execsql {
    CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;
    CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
    CREATE INDEX t1b ON t1(b) WHERE b>10;
    CREATE VIRTUAL TABLE nums USING wholenumber;
    INSERT INTO t1(a,b,c)
       SELECT CASE WHEN value%3!=0 THEN value END, value, value
         FROM nums WHERE value<=20;
    SELECT count(a), count(b) FROM t1;
    PRAGMA integrity_check;
  }
} {14 20 ok}

# (The "partial" column of the PRAGMA index_list output is...)
# EVIDENCE-OF: R-34457-09668 "1" if the index is a partial index and "0"
# if not.
#
do_test index7-1.1a {
  capture_pragma db out {PRAGMA index_list(t1)}
  db eval {SELECT "name", "partial", '|' FROM out ORDER BY "name"}
} {sqlite_autoindex_t1_1 0 | t1a 1 | t1b 1 |}

# Make sure the count(*) optimization works correctly with
# partial indices.  Ticket [a5c8ed66cae16243be6] 2013-10-03.
#
do_execsql_test index7-1.1.1 {
  SELECT count(*) FROM t1;
} {20}

    CREATE INDEX t3i1 ON t3(a,b);
  }
  capture_pragma db out {
    pragma index_info(t3i1);
  }
  db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
} {0 0 a 1 1 b}

# EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown
# by the index_info pragma, but they are listed by the index_xinfo
# pragma.
#
do_test pragma-6.5.1b {
  capture_pragma db out {PRAGMA index_xinfo(t3i1)}
  db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
} {0 0 a 1 1 b 2 -1 {}}


# EVIDENCE-OF: R-62725-03366 PRAGMA database.index_info(index-name);
# This pragma returns one row for each key column in the named index.
#
# (The first column of output from PRAGMA index_info is...)
# EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0
# means left-most.)
#
# (The second column of output from PRAGMA index_info is...)
# EVIDENCE-OF: R-65019-08383 The rank of the column within the table
# being indexed.
#
# (The third column of output from PRAGMA index_info is...)
# EVIDENCE-OF: R-09773-34266 The name of the column being indexed.
#
do_execsql_test pragma-6.5.1c {
  CREATE INDEX t3i2 ON t3(b,a);
  PRAGMA index_info='t3i2';
  DROP INDEX t3i2;
} {0 1 b 1 0 a}

do_test pragma-6.5.2 {
  execsql {
    pragma index_info(t3i1_bogus);
  }
} {}

ifcapable tempdb {

  {3 four REAL 0 X'abcdef' 0} \
  {4 five {} 0 CURRENT_TIME 0} \
]
} ;# ifcapable schema_pragmas
# Miscellaneous tests
#
ifcapable schema_pragmas {
# EVIDENCE-OF: R-63500-32024 PRAGMA database.index_list(table-name);
# This pragma returns one row for each index associated with the given
# table.
do_test pragma-7.1.1 {
  # Make sure a pragma knows to read the schema if it needs to
  db close
  sqlite3 db test.db
  capture_pragma db out "PRAGMA index_list(t3)"
  db eval {SELECT name, "origin" FROM out ORDER BY name DESC}
} {t3i1 c sqlite_autoindex_t3_1 u}

sqlite3 db test.db
sqlite3 db2 test.db
do_test 23.1 {
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
    CREATE INDEX i1 ON t1(b,c);
    CREATE INDEX i2 ON t1(c,d);
    CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC);
    CREATE TABLE t2(x INTEGER REFERENCES t1);
  }
  db2 eval {SELECT name FROM sqlite_master}
} {t1 i1 i2 t2}
} {t1 i1 i2 i2x t2}
do_test 23.2a {
  db eval {
    DROP INDEX i2;
    CREATE INDEX i2 ON t1(c,d,b);
  }
  capture_pragma db2 out {PRAGMA index_info(i2)}
  db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno}
} {2 c | 3 d | 1 b |}

# EVIDENCE-OF: R-44874-46325 PRAGMA database.index_xinfo(index-name);
# This pragma returns information about every column in an index.
#
# EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma
# returns information about every column in the index, not just the key
# columns.
#
do_test 23.2b {
breakpoint;
  capture_pragma db2 out {PRAGMA index_xinfo(i2)}
  db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
} {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |}

# (The first column of output from PRAGMA index_xinfo is...)
# EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0
# means left-most. Key columns come before auxiliary columns.)
#
# (The second column of output from PRAGMA index_xinfo is...)
# EVIDENCE-OF: R-40889-06838 The rank of the column within the table
# being indexed, or -1 if the index-column is the rowid of the table
# being indexed.
#
# (The third column of output from PRAGMA index_xinfo is...)
# EVIDENCE-OF: R-22751-28901 The name of the column being indexed, or
# NULL if the index-column is the rowid of the table being indexed.
#
# (The fourth column of output from PRAGMA index_xinfo is...)
# EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse
# (DESC) order by the index and 0 otherwise.
#
# (The fifth column of output from PRAGMA index_xinfo is...)
# EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to
# compare values in the index-column.
#
# (The sixth column of output from PRAGMA index_xinfo is...)
# EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0
# if the index-column is an auxiliary column.
#
do_test 23.2c {
  db2 eval {PRAGMA index_xinfo(i2)}
} {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0}
do_test 23.2d {
  db2 eval {PRAGMA index_xinfo(i2x)}
} {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0}

# EVIDENCE-OF: R-63500-32024 PRAGMA database.index_list(table-name);
# This pragma returns one row for each index associated with the given
# table.
#
# (The first column of output from PRAGMA index_list is...)
# EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index
# for internal tracking purposes.
#
# (The second column of output from PRAGMA index_list is...)
# EVIDENCE-OF: R-35496-03635 The name of the index.
#
# (The third column of output from PRAGMA index_list is...)
# EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not.
#
# (The fourth column of output from PRAGMA index_list is...)
# EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE
# INDEX statement, "u" if the index was created by a UNIQUE constraint,
# or "pk" if the index was created by a PRIMARY KEY constraint.
#
do_test 23.3 {
  db eval {
    CREATE INDEX i3 ON t1(d,b,c);
  }
  capture_pragma db2 out {PRAGMA index_list(t1)}
  db2 eval {SELECT name, "unique", origin FROM out ORDER BY seq}
} {i3 0 c i2 0 c i1 0 c}
  db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq}
} {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |}
do_test 23.4 {
  db eval {
    ALTER TABLE t1 ADD COLUMN e;
  }
  db2 eval {
    PRAGMA table_info(t1);
  }