SQLite

      db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
    ]
  }
  set res
}


# EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name
#
do_createtable_tests 0.1.1 -repair {
  drop_all_tables
} {
  1   "CREATE TABLE t1(c1 one)"                        {}
  2   "CREATE TABLE t1(c1 one two)"                    {}
  3   "CREATE TABLE t1(c1 one two three)"              {}
  4   "CREATE TABLE t1(c1 one two three four)"         {}
  5   "CREATE TABLE t1(c1 one two three four(14))"     {}
  6   "CREATE TABLE t1(c1 one two three four(14, 22))" {}
  7   "CREATE TABLE t1(c1 var(+14, -22.3))"            {}
  8   "CREATE TABLE t1(c1 var(1.0e10))"                {}
}
do_createtable_tests 0.1.2 -error {
  near "%s": syntax error
} {
  1   "CREATE TABLE t1(c1 one(number))"                {number}
}


# EVIDENCE-OF: R-18762-12428 -- syntax diagram column-constraint
#
#   Note: Not shown in the syntax diagram is the "NULL" constraint. This
#         is the opposite of "NOT NULL" - it implies that the column may
#         take a NULL value. This is the default anyway, so this type of
#         constraint is rarely used.
#
do_createtable_tests 0.2.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(x PRIMARY KEY) }
} {
  1.1   "CREATE TABLE t1(c1 text PRIMARY KEY)"                         {}
  1.2   "CREATE TABLE t1(c1 text PRIMARY KEY ASC)"                     {}

  8.2   {
    CREATE TABLE t1(c1 
      REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY 
    );
  } {}
}

# EVIDENCE-OF: R-17905-31923 -- syntax diagram table-constraint
#
do_createtable_tests 0.3.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(x PRIMARY KEY) }
} {
  1.1   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))"                         {}
  1.2   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))"                     {}
  1.3   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)"  {}

  2.1   "CREATE TABLE t1(c1, c2, UNIQUE(c1))"                              {}
  2.2   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))"                          {}
  2.3   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)"       {}

  3.1   "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))"                     {}

  4.1   "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)"           {}
}

# EVIDENCE-OF: R-18765-31171 -- syntax diagram column-def
#
do_createtable_tests 0.4.1 -repair {
  drop_all_tables 
} {
  1     {CREATE TABLE t1(
           col1,
           col2 TEXT,
           col3 INTEGER UNIQUE,
           col4 VARCHAR(10, 10) PRIMARY KEY,
           "name with spaces" REFERENCES t1
         );
        } {}
}

# EVIDENCE-OF: R-59573-11075 -- syntax diagram create-table-stmt
#
do_createtable_tests 0.5.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(a, b, c) }
} {
  1     "CREATE TABLE t1(a, b, c)"                                    {}
  2     "CREATE TEMP TABLE t1(a, b, c)"                               {}

  12    "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)"       {}

  13    "CREATE TABLE t1 AS SELECT * FROM t2"                         {}
  14    "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2"              {}
  15    "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2"  {}
}

# EVIDENCE-OF: R-32138-02228 -- syntax diagram foreign-key-clause
#
#   1:         Explicit parent-key columns.
#   2:         Implicit child-key columns.
#
#   1:         MATCH FULL
#   2:         MATCH PARTIAL
#   3:         MATCH SIMPLE

}

do_execsql_test e_delete-0.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
} {}

# EVIDENCE-OF: R-24177-52883 -- syntax diagram delete-stmt
#
# EVIDENCE-OF: R-12802-60464 -- syntax diagram qualified-table-name
#
do_delete_tests e_delete-0.1 {
  1  "DELETE FROM t1"                              {}
  2  "DELETE FROM t1 INDEXED BY i1"                {}
  3  "DELETE FROM t1 NOT INDEXED"                  {}
  4  "DELETE FROM main.t1"                         {}
  5  "DELETE FROM main.t1 INDEXED BY i1"           {}

}

# EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax
# of the DELETE statement is extended by the addition of optional ORDER
# BY and LIMIT clauses:
#
# EVIDENCE-OF: R-45897-01670 -- syntax diagram delete-stmt-limited
#
do_delete_tests e_delete-3.1 {
  1   "DELETE FROM t1 LIMIT 5"                                    {}
  2   "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2"                       {}
  3   "DELETE FROM t1 LIMIT 2+2, 16/4"                            {}
  4   "DELETE FROM t1 ORDER BY x LIMIT 5"                         {}
  5   "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2"            {}

    CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END;
    CREATE TRIGGER aux.tr2 AFTER  $event ON t3 BEGIN SELECT r('aux.tr2') ; END;
    CREATE TRIGGER aux.tr3 AFTER  $event ON t3 BEGIN SELECT r('aux.tr3') ; END;
  "
}


# EVIDENCE-OF: R-52650-16855 -- syntax diagram drop-trigger-stmt
#
do_droptrigger_tests 1.1 -repair {
  droptrigger_reopen_db
} -tclquery {
  list_all_triggers 
} {
  1   "DROP TRIGGER main.tr1"            

  set res
}

proc do_dropview_tests {nm args} {
  uplevel do_select_tests $nm $args
}

# EVIDENCE-OF: R-21739-51207 -- syntax diagram drop-view-stmt
#
# All paths in the syntax diagram for DROP VIEW are tested by tests 1.*.
#
do_dropview_tests 1 -repair {
  dropview_reopen_db
} -tclquery {
  list_all_views

       [sqlite3_column_type $stmt 3] 
} {NULL NULL NULL NULL}
do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK

#-------------------------------------------------------------------------
# "Test" the syntax diagrams in lang_expr.html.
#
# EVIDENCE-OF: R-62067-43884 -- syntax diagram signed-number
#
do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
do_execsql_test e_expr-12.1.4 { 
  SELECT 1.4, +1.4, -1.4 
} {1.4 1.4 -1.4}
do_execsql_test e_expr-12.1.5 { 
  SELECT 1.5e+5, +1.5e+5, -1.5e+5 
} {150000.0 150000.0 -150000.0}
do_execsql_test e_expr-12.1.6 { 
  SELECT 0.0001, +0.0001, -0.0001 
} {0.0001 0.0001 -0.0001}

# EVIDENCE-OF: R-21258-25489 -- syntax diagram literal-value
#
set sqlite_current_time 1
do_execsql_test e_expr-12.2.1 {SELECT 123}               {123}
do_execsql_test e_expr-12.2.2 {SELECT 123.4e05}          {12340000.0}
do_execsql_test e_expr-12.2.3 {SELECT 'abcde'}           {abcde}
do_execsql_test e_expr-12.2.4 {SELECT X'414243'}         {ABC}
do_execsql_test e_expr-12.2.5 {SELECT NULL}              {{}}
do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME}      {00:00:01}
do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE}      {1970-01-01}
do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
set sqlite_current_time 0

# EVIDENCE-OF: R-57598-59332 -- syntax diagram expr
#
forcedelete test.db2
execsql {
  ATTACH 'test.db2' AS dbname;
  CREATE TABLE dbname.tblname(cname);
}

    incr x
    do_test e_expr-12.3.$tn.$x { 
      set rc [catch { execsql "SELECT $e FROM tblname" } msg]
    } {0}
  }
}

# EVIDENCE-OF: R-49462-56079 -- syntax diagram raise-function
#
foreach {tn raiseexpr} {
  1 "RAISE(IGNORE)"
  2 "RAISE(ROLLBACK, 'error message')"
  3 "RAISE(ABORT, 'error message')"
  4 "RAISE(FAIL, 'error message')"
} {

  CREATE TABLE a4(c UNIQUE, d);
} {}

proc do_insert_tests {args} {
  uplevel do_select_tests $args
}

# EVIDENCE-OF: R-41448-54465 -- syntax diagram insert-stmt
#
do_insert_tests e_insert-0 {
     1  "INSERT             INTO a1 DEFAULT VALUES"                   {}
     2  "INSERT             INTO main.a1 DEFAULT VALUES"              {}
     3  "INSERT OR ROLLBACK INTO main.a1 DEFAULT VALUES"              {}
     4  "INSERT OR ROLLBACK INTO a1 DEFAULT VALUES"                   {}
     5  "INSERT OR ABORT    INTO main.a1 DEFAULT VALUES"              {}

do_execsql_test e_reindex-0.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
  CREATE INDEX i2 ON t1(b, a);
} {}

# EVIDENCE-OF: R-57021-15304 -- syntax diagram reindex-stmt
#
do_reindex_tests e_reindex-0.1 {
  1   "REINDEX"           {}
  2   "REINDEX nocase"    {}
  3   "REINDEX binary"    {}
  4   "REINDEX t1"        {}
  5   "REINDEX main.t1"   {}
  4   "REINDEX i1"        {}
  5   "REINDEX main.i1"   {}
}

# EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and
# recreate indices from scratch.
#
#    Test this by corrupting some database indexes, running REINDEX, and
#    observing that the corruption is gone.

  }
}

#-------------------------------------------------------------------------
# The following tests check that all paths on the syntax diagrams on
# the lang_select.html page may be taken.
#
# EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint
#
do_join_test e_select-0.1.1 {
  SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a)
} {3}
do_join_test e_select-0.1.2 {
  SELECT count(*) FROM t1 %JOIN% t2 USING (a)
} {3}
do_join_test e_select-0.1.3 {
  SELECT count(*) FROM t1 %JOIN% t2
} {9}
do_catchsql_test e_select-0.1.4 {
  SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a)
} {1 {cannot have both ON and USING clauses in the same join}}
do_catchsql_test e_select-0.1.5 {
  SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a)
} {1 {near "ON": syntax error}}

# EVIDENCE-OF: R-44854-11739 -- syntax diagram select-core
#
#   0: SELECT ...
#   1: SELECT DISTINCT ...
#   2: SELECT ALL ...
#
#   0: No FROM clause
#   1: Has FROM clause

    1 a 1 c
  }
  2112.2  "SELECT ALL count(*), max(a) FROM t1 
           WHERE 0 GROUP BY b HAVING count(*)=2" { }
}


# EVIDENCE-OF: R-23316-20169 -- syntax diagram result-column
#
do_select_tests e_select-0.3 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT t1.* FROM t1" {a one b two c three}
  3  "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}
  4  "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}
  5  "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}
}

# EVIDENCE-OF: R-41233-21397 -- syntax diagram join-source
#
# EVIDENCE-OF: R-45040-11121 -- syntax diagram join-op
#
do_select_tests e_select-0.4 {
  1  "SELECT t1.rowid FROM t1" {1 2 3}
  2  "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}
  3  "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}

  4  "SELECT t1.rowid FROM t1" {1 2 3}

  12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}
  13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}
  14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3}
  15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}
  16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}
}

# EVIDENCE-OF: R-56911-63533 -- syntax diagram compound-operator
#
do_select_tests e_select-0.5 {
  1  "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}
  2  "SELECT rowid FROM t1 UNION     SELECT rowid+2 FROM t4" {1 2 3 4}
  3  "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}
  4  "SELECT rowid FROM t1 EXCEPT    SELECT rowid+2 FROM t4" {1 2}
}

# EVIDENCE-OF: R-60388-27458 -- syntax diagram ordering-term
#
do_select_tests e_select-0.6 {
  1  "SELECT b||a FROM t1 ORDER BY b||a"                  {onea threec twob}
  2  "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}
  3  "SELECT b||a FROM t1 ORDER BY (b||a) ASC"            {onea threec twob}
  4  "SELECT b||a FROM t1 ORDER BY (b||a) DESC"           {twob threec onea}
}

# EVIDENCE-OF: R-36494-33519 -- syntax diagram select-stmt
#
do_select_tests e_select-0.7 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT * FROM t1 ORDER BY b" {a one c three b two}
  3  "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}

  4  "SELECT * FROM t1 LIMIT 10" {a one b two c three}

  CREATE TABLE aux.t5(a, b);
} {}

proc do_update_tests {args} {
  uplevel do_select_tests $args
}

# EVIDENCE-OF: R-05685-44205 -- syntax diagram update-stmt
#
do_update_tests e_update-0 {
  1    "UPDATE t1 SET a=10" {}
  2    "UPDATE t1 SET a=10, b=5" {}
  3    "UPDATE t1 SET a=10 WHERE b=5" {}
  4    "UPDATE t1 SET b=5,a=10 WHERE 1" {}
  5    "UPDATE main.t1 SET a=10" {}

}

# EVIDENCE-OF: R-59581-44104 If SQLite is built with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax
# of the UPDATE statement is extended with optional ORDER BY and LIMIT
# clauses
#
# EVIDENCE-OF: R-08948-01887 -- syntax diagram update-stmt-limited
#
do_update_tests e_update-3.0 {
  1   "UPDATE t1 SET a=b LIMIT 5"                                    {}
  2   "UPDATE t1 SET a=b LIMIT 5-1 OFFSET 2+2"                       {}
  3   "UPDATE t1 SET a=b LIMIT 2+2, 16/4"                            {}
  4   "UPDATE t1 SET a=b ORDER BY a LIMIT 5"                         {}
  5   "UPDATE t1 SET a=b ORDER BY a LIMIT 5-1 OFFSET 2+2"            {}

    set prevpageno $pageno
  }
  execsql { DROP TABLE temp.stat }
  set nFrag
}


# EVIDENCE-OF: R-63707-33375 -- syntax diagram vacuum-stmt
#
do_execsql_test e_vacuum-0.1 { VACUUM } {}

# EVIDENCE-OF: R-51469-36013 Unless SQLite is running in
# "auto_vacuum=FULL" mode, when a large amount of data is deleted from
# the database file it leaves behind empty space, or "free" database
# pages.