000001  hash-threshold 8
000002  
000003  statement ok
000004  CREATE TABLE t1( x INTEGER, y VARCHAR(8) )
000005  
000006  statement ok
000007  INSERT INTO t1 VALUES(1,'true')
000008  
000009  statement ok
000010  INSERT INTO t1 VALUES(0,'false')
000011  
000012  statement ok
000013  INSERT INTO t1 VALUES(NULL,'NULL')
000014  
000015  statement ok
000016  CREATE INDEX t1i1 ON t1(x)
000017  
000018  
000019  # TBD-EVIDENCE-OF: R-13439-14752 The CREATE VIEW command assigns a name to a
000020  # pre-packaged SELECT statement.
000021  
000022  statement ok
000023  CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0
000024  
000025  # already exists
000026  statement error
000027  CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0
000028  
000029  query I rowsort label-0
000030  SELECT x FROM t1 WHERE x>0
000031  ----
000032  1
000033  
000034  query I rowsort label-0
000035  SELECT x FROM view1
000036  ----
000037  1
000038  
000039  # TBD-EVIDENCE-OF: R-63075-15970 Once the view is created, it can be used in
000040  # the FROM clause of another SELECT in place of a table name.
000041  
000042  # PARTIAL-EVIDENCE-OF: R-48816-31606 If the "TEMP" or "TEMPORARY" keyword occurs
000043  # in between "CREATE" and "VIEW" then the view that is created is only
000044  # visible to the process that opened the database and is automatically
000045  # deleted when the database is closed.
000046  
000047  onlyif sqlite
000048  statement ok
000049  CREATE TEMP VIEW view2 AS SELECT x FROM t1 WHERE x>0
000050  
000051  onlyif sqlite
000052  statement ok
000053  CREATE TEMPORARY VIEW view3 AS SELECT x FROM t1 WHERE x>0
000054  
000055  # TBD-EVIDENCE-OF: R-49139-10687 If a <database-name> is specified,
000056  # then the view is created in the named database.
000057  
000058  # TBD-EVIDENCE-OF: R-48195-55336 It is an error to specify both a
000059  # <database-name> and the TEMP keyword on a VIEW, unless the
000060  # <database-name> is "temp".
000061  
000062  # TBD-EVIDENCE-OF: R-49817-02954 If no database name is specified, and the
000063  # TEMP keyword is not present, the VIEW is created in the main database.
000064  
000065  # EVIDENCE-OF: R-16775-34716 You cannot DELETE, INSERT, or UPDATE a
000066  # view.
000067  
000068  skipif mssql  # this is allowed
000069  statement error
000070  DELETE FROM view1 WHERE x>0
000071  
000072  onlyif mssql  # this is allowed
000073  statement ok
000074  DELETE FROM view1 WHERE x>0
000075  
000076  statement error
000077  INSERT INTO view1 VALUES(2,'unknown')
000078  
000079  skipif mssql  # this is allowed
000080  statement error
000081  UPDATE view1 SET x=2
000082  
000083  onlyif mssql  # this is allowed
000084  statement ok
000085  UPDATE view1 SET x=2
000086  
000087  # PARTIAL-EVIDENCE-OF: R-05363-17893 Views are read-only in SQLite.
000088  
000089  onlyif sqlite
000090  statement error
000091  DELETE FROM view1 WHERE x>0
000092  
000093  onlyif sqlite
000094  statement error
000095  INSERT INTO view1 VALUES(2,'unknown')
000096  
000097  onlyif sqlite
000098  statement error
000099  INSERT OR REPLACE INTO view1 VALUES(2,'unknown')
000100  
000101  onlyif sqlite
000102  statement error
000103  UPDATE view1 SET x=2
000104  
000105  # TBD-EVIDENCE-OF: R-16876-26469 However, in many cases you can use an
000106  # INSTEAD OF trigger on the view to accomplish the same thing.
000107  
000108  # EVIDENCE-OF: R-10484-47921 Views are removed with the DROP VIEW
000109  # command.
000110  
000111  statement ok
000112  DROP VIEW view1
000113  
000114  onlyif sqlite
000115  statement ok
000116  DROP VIEW view2
000117  
000118  onlyif sqlite
000119  statement ok
000120  DROP VIEW view3
000121  
000122  # already dropped
000123  statement error
000124  DROP VIEW view1
000125  
000126  # never existed
000127  statement error
000128  DROP VIEW viewX