sqllogictest

hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# TBD-EVIDENCE-OF: R-13439-14752 The CREATE VIEW command assigns a name to a
# pre-packaged SELECT statement.

statement ok
CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0

# already exists
statement error
CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0

# TBD-EVIDENCE-OF: R-63075-15970 Once the view is created, it can be used in
# the FROM clause of another SELECT in place of a table name.

# PARTIAL-EVIDENCE-OF: R-48816-31606 If the "TEMP" or "TEMPORARY" keyword occurs
# in between "CREATE" and "VIEW" then the view that is created is only
# visible to the process that opened the database and is automatically
# deleted when the database is closed.

onlyif sqlite
statement ok
CREATE TEMP VIEW view2 AS SELECT x FROM t1 WHERE x>0

onlyif sqlite
statement ok
CREATE TEMPORARY VIEW view3 AS SELECT x FROM t1 WHERE x>0

# TBD-EVIDENCE-OF: R-49139-10687 If a <database-name> is specified,
# then the view is created in the named database.

# TBD-EVIDENCE-OF: R-48195-55336 It is an error to specify both a
# <database-name> and the TEMP keyword on a VIEW, unless the
# <database-name> is "temp".

# TBD-EVIDENCE-OF: R-49817-02954 If no database name is specified, and the
# TEMP keyword is not present, the VIEW is created in the main database.

# EVIDENCE-OF: R-16775-34716 You cannot DELETE, INSERT, or UPDATE a
# view.

skipif mssql  # this is allowed
statement error
DELETE FROM view1 WHERE x>0

onlyif mssql  # this is allowed
statement ok
DELETE FROM view1 WHERE x>0

statement error
INSERT INTO view1 VALUES(2,'unknown')

skipif mssql  # this is allowed
statement error
UPDATE view1 SET x=2

onlyif mssql  # this is allowed
statement ok
UPDATE view1 SET x=2

# PARTIAL-EVIDENCE-OF: R-05363-17893 Views are read-only in SQLite.

onlyif sqlite
statement error
DELETE FROM view1 WHERE x>0

onlyif sqlite
statement error
INSERT INTO view1 VALUES(2,'unknown')

onlyif sqlite
statement error
INSERT OR REPLACE INTO view1 VALUES(2,'unknown')

onlyif sqlite
statement error
UPDATE view1 SET x=2

# TBD-EVIDENCE-OF: R-16876-26469 However, in many cases you can use an
# INSTEAD OF trigger on the view to accomplish the same thing.

# EVIDENCE-OF: R-10484-47921 Views are removed with the DROP VIEW
# command.

statement ok
DROP VIEW view1

onlyif sqlite
statement ok
DROP VIEW view2

onlyif sqlite
statement ok
DROP VIEW view3

# already dropped
statement error
DROP VIEW view1

# never existed
statement error
DROP VIEW viewX

hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# EVIDENCE-OF: R-42037-15614 The DROP INDEX statement removes an index
# added with the CREATE INDEX statement.

statement ok
DROP INDEX t1i1;

# this should error, as already dropped
statement error
DROP INDEX t1i1;

# this should error, as never existed
statement error
DROP INDEX tXiX;

hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# EVIDENCE-OF: R-01463-03846 The DROP TABLE statement removes a table
# added with the CREATE TABLE statement.

statement ok
DROP TABLE t1

# already dropped
statement error
DROP TABLE t1

# never existed
statement error
DROP TABLE tX

# TBD-EVIDENCE-OF: R-40197-14811 The dropped table is completely removed
# from the database schema and the disk file.

# EVIDENCE-OF: R-33950-57093 All indices and triggers associated with
# the table are also deleted.

# this should error, as was dropped with table
statement error
DROP INDEX t1i1;

# PARTIAL-EVIDENCE-OF: R-57089-01510 The optional IF EXISTS clause suppresses
# the error that would normally result if the table does not exist.

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

skipif mssql
DROP TABLE IF EXISTS t1

skipif mssql
DROP TABLE IF EXISTS t1

# TBD-EVIDENCE-OF: R-55943-26846 If foreign key constraints are enabled, a
# DROP TABLE command performs an implicit DELETE FROM <tbl>
# command before removing the table from the database schema.

# TBD-EVIDENCE-OF: R-02002-57504 Any triggers attached to the table are
# dropped from the database schema before the implicit DELETE FROM
# <tbl> is executed, so this cannot cause any triggers to fire.

# TBD-EVIDENCE-OF: R-33044-60878 an implicit DELETE FROM <tbl> does
# cause any configured foreign key actions to take place.

# TBD-EVIDENCE-OF: R-17649-15849 If the implicit DELETE FROM <tbl>
# executed as part of a DROP TABLE command violates any immediate
# foreign key constraints, an error is returned and the table is not
# dropped.

# TBD-EVIDENCE-OF: R-01608-60102 If the implicit DELETE FROM <tbl>
# causes any deferred foreign key constraints to be violated, and the
# violations still exist when the transaction is committed, an error is
# returned at the time of commit.

hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)

# There is no REINDEX in the SQL92 standard.
# So far, this only runs well on SQLite and Postgres.
# I believe MySQL uses the form REPAIR TABLE [tbl_name].

# skip this entire file if ms sql server
onlyif mssql
halt

# skip this entire file if oracle
onlyif oracle
halt

# skip this entire file if oracle
onlyif mysql
halt

# EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and
# recreate indices from scratch.

statement ok
REINDEX t1i1

statement error
REINDEX tXiX

# TBD-EVIDENCE-OF: R-38396-20088 In the first form, all indices in all
# attached databases that use the named collation sequence are
# recreated.

# TBD-EVIDENCE-OF: R-46980-03026 In the second form, if
# [database-name.]table/index-name identifies a table, then all indices
# associated with the table are rebuilt.

# TBD-EVIDENCE-OF: R-50401-40957 If an index is identified, then only this
# specific index is deleted and recreated.

# TBD-EVIDENCE-OF: R-59524-35239 If no database-name is specified and there
# exists both a table or index and a collation sequence of the specified
# name, then indices associated with the collation sequence only are
# reconstructed.

hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# EVIDENCE-OF: R-51331-37626 The UPDATE statement is used to change the
# value of columns in selected rows of a table.

statement ok
UPDATE t1 SET x=2 WHERE x>0

# EVIDENCE-OF: R-39990-21878 Each assignment in an UPDATE specifies a
# column name to the left of the equals sign and an arbitrary expression
# to the right.

statement ok
UPDATE t1 SET y='unknown' WHERE x>0

statement error
UPDATE t1 SET z='foo'

statement error
UPDATE t1 SET z='foo' WHERE x>0

# EVIDENCE-OF: R-51230-45980 The expressions may use the values of other
# columns.

statement ok
UPDATE t1 SET y=x WHERE x>0

# EVIDENCE-OF: R-34438-08394 All expressions are evaluated before any
# assignments are made.

statement ok
UPDATE t1 SET y=y+2 WHERE x>0

# EVIDENCE-OF: R-55426-32478 A WHERE clause can be used to restrict
# which rows are updated.

statement ok
UPDATE t1 SET x=1 WHERE x>0

statement ok
UPDATE t1 SET y='true' WHERE x>0

# TBD-EVIDENCE-OF: R-48588-18160 The optional conflict-clause allows the
# specification of an alternative constraint conflict resolution
# algorithm to use during this one UPDATE command.

# TBD-EVIDENCE-OF: R-55493-24001 There are additional syntax restrictions on
# UPDATE statements that occur within the body of a CREATE TRIGGER
# statement.

# TBD-EVIDENCE-OF: R-07956-29754 The table-name of the UPDATE must be
# unqualified.

# TBD-EVIDENCE-OF: R-09690-36749 In other words, the database-name. prefix
# on the table name of the UPDATE is not allowed within triggers.

# TBD-EVIDENCE-OF: R-08610-60711 The table to be updated must be in the same
# database as the table to which the trigger is attached.

# TBD-EVIDENCE-OF: R-19619-42762 The INDEXED BY and NOT INDEXED clauses are
# not allowed on UPDATE statements within triggers.

# TBD-EVIDENCE-OF: R-14001-24988 The LIMIT clause for UPDATE is unsupported
# within triggers.

# TBD-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

# TBD-EVIDENCE-OF: R-47158-42005 The optional LIMIT clause can be used to
# limit the number of rows modified, and thereby limit the size of the
# transaction.

# TBD-EVIDENCE-OF: R-27955-06688 The ORDER BY clause on an UPDATE statement
# is used only to determine which rows fall within the LIMIT.

# TBD-EVIDENCE-OF: R-45033-12484 The order in which rows are modified is
# arbitrary and is not determined by the ORDER BY clause.