test_run = require('test_run').new()
---
...
engine = test_run:get_cfg('engine')
---
...
_ = box.space._session_settings:update('sql_default_engine', {{'=', 2, engine}})
---
...
-- Verify that constraints on 'view' option are working.
-- box.cfg()
-- Create space and view.
box.execute("CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a, b));");
---
- row_count: 1
...
box.execute("CREATE VIEW v1 AS SELECT a+b FROM t1;");
---
- row_count: 1
...
-- View can't have any indexes.
box.execute("CREATE INDEX i1 on v1(a);");
---
- null
- 'Can''t create or modify index ''I1'' in space ''V1'': views can not be indexed'
...
v1 = box.space.V1;
---
...
v1:create_index('primary', {parts = {1, 'string'}})
---
- error: 'Can''t modify space ''V1'': can not add index on a view'
...
v1:create_index('secondary', {parts = {1, 'string'}})
---
- error: 'Can''t modify space ''V1'': can not add index on a view'
...
-- View option can't be changed.
v1 = box.space._space.index[2]:select('V1')[1]:totable();
---
...
v1[6]['view'] = false;
---
...
box.space._space:replace(v1);
---
- error: 'Can''t modify space ''V1'': can not convert a space to a view and vice versa'
...
t1 = box.space._space.index[2]:select('T1')[1]:totable();
---
...
t1[6]['view'] = true;
---
...
t1[6]['sql'] = 'SELECT * FROM t1;'
---
...
box.space._space:replace(t1);
---
- error: 'Can''t modify space ''T1'': can not convert a space to a view and vice versa'
...
-- View can't exist without SQL statement.
v1[6] = {};
---
...
v1[6]['view'] = true;
---
...
box.space._space:replace(v1);
---
- error: Space declared as a view must have SQL statement
...
-- Views can't be created via space_create().
box.schema.create_space('view', {view = true})
---
- error: Illegal parameters, unexpected option 'view'
...
-- Space referenced by a view can't be renamed.
box.execute("ALTER TABLE t1 RENAME TO new_name;")
---
- null
- 'Can''t modify space ''T1'': can not rename space which is referenced by view'
...
-- View can be created via straight insertion into _space.
sp = box.schema.create_space('test');
---
...
raw_sp = box.space._space:get(sp.id):totable();
---
...
sp:drop();
---
...
raw_sp[6].sql = 'CREATE VIEW v as SELECT * FROM t1;';
---
...
raw_sp[6].view = true;
---
...
sp = box.space._space:replace(raw_sp);
---
...
box.space._space:select(sp['id'])[1]['name']
---
- test
...
-- Can't create view with incorrect SELECT statement.
box.space.test:drop();
---
...
-- This case must fail since parser converts it to expr AST.
raw_sp[6].sql = 'SELECT 1;';
---
...
sp = box.space._space:replace(raw_sp);
---
- error: 'Failed to execute SQL statement: SELECT 1;'
...
-- Can't drop space via Lua if at least one view refers to it.
box.execute('CREATE TABLE t2(id INT PRIMARY KEY);');
---
- row_count: 1
...
box.execute('CREATE VIEW v2 AS SELECT * FROM t2;');
---
- row_count: 1
...
box.space.T2:drop();
---
- error: 'Can''t drop space ''T2'': other views depend on this space'
...
box.execute('DROP VIEW v2;');
---
- row_count: 1
...
box.execute('DROP TABLE t2;');
---
- row_count: 1
...
-- Check that alter transfers reference counter.
box.execute("CREATE TABLE t2(id INTEGER PRIMARY KEY);");
---
- row_count: 1
...
box.execute("CREATE VIEW v2 AS SELECT * FROM t2;");
---
- row_count: 1
...
box.execute("DROP TABLE t2;");
---
- null
- 'Can''t drop space ''T2'': other views depend on this space'
...
sp = box.space._space:get{box.space.T2.id};
---
...
sp = box.space._space:replace(sp);
---
...
box.execute("DROP TABLE t2;");
---
- null
- 'Can''t drop space ''T2'': other views depend on this space'
...
box.execute("DROP VIEW v2;");
---
- row_count: 1
...
box.execute("DROP TABLE t2;");
---
- row_count: 1
...
-- gh-3849: failed to create VIEW in form of AS VALUES (const);
--
box.execute("CREATE VIEW cv AS VALUES(1);")
---
- row_count: 1
...
box.execute("CREATE VIEW cv1 AS VALUES('k', 1);")
---
- row_count: 1
...
box.execute("CREATE VIEW cv2 AS VALUES((VALUES((SELECT 1))));")
---
- row_count: 1
...
box.execute("CREATE VIEW cv3 AS VALUES(1+2, 1+2);")
---
- row_count: 1
...
box.execute("DROP VIEW cv;")
---
- row_count: 1
...
box.execute("DROP VIEW cv1;")
---
- row_count: 1
...
box.execute("DROP VIEW cv2;")
---
- row_count: 1
...
box.execute("DROP VIEW cv3;")
---
- row_count: 1
...
-- gh-3815: AS VALUES syntax didn't incerement VIEW reference
-- counter. Moreover, tables within sub-select were not accounted
-- as well.
--
box.execute("CREATE TABLE b (s1 INT PRIMARY KEY);")
---
- row_count: 1
...
box.execute("CREATE VIEW bv (wombat) AS VALUES ((SELECT 'k' FROM b));")
---
- row_count: 1
...
box.execute("DROP TABLE b;")
---
- null
- 'Can''t drop space ''B'': other views depend on this space'
...
box.execute("DROP VIEW bv;")
---
- row_count: 1
...
box.execute("DROP TABLE b;")
---
- row_count: 1
...
box.execute("CREATE TABLE b (s1 INT PRIMARY KEY);")
---
- row_count: 1
...
box.execute("CREATE TABLE c (s1 INT PRIMARY KEY);")
---
- row_count: 1
...
box.execute("CREATE VIEW bcv AS SELECT * FROM b WHERE s1 IN (SELECT * FROM c);")
---
- row_count: 1
...
box.execute("DROP TABLE c;")
---
- null
- 'Can''t drop space ''C'': other views depend on this space'
...
box.execute("DROP VIEW bcv;")
---
- row_count: 1
...
box.execute("DROP TABLE c;")
---
- row_count: 1
...
box.execute("CREATE TABLE c (s1 INT PRIMARY KEY);")
---
- row_count: 1
...
box.execute("CREATE VIEW bcv(x, y) AS VALUES((SELECT 'k' FROM b), (VALUES((SELECT 1 FROM b WHERE s1 IN (VALUES((SELECT 1 + c.s1 FROM c)))))))")
---
- row_count: 1
...
box.execute("DROP TABLE c;")
---
- null
- 'Can''t drop space ''C'': other views depend on this space'
...
box.space.BCV:drop()
---
...
box.execute("DROP TABLE c;")
---
- row_count: 1
...
box.execute("DROP TABLE b;")
---
- row_count: 1
...
-- gh-3814: make sure that recovery of view processed without
-- unexpected errors.
--
box.snapshot()
---
- ok
...
box.execute("CREATE TABLE t2 (id INT PRIMARY KEY);")
---
- row_count: 1
...
box.execute("CREATE VIEW v2 AS SELECT * FROM t2;")
---
- row_count: 1
...
test_run:cmd('restart server default')
box.execute("DROP TABLE t2;")
---
- null
- 'Can''t drop space ''T2'': other views depend on this space'
...
box.execute("SELECT * FROM v2;")
---
- metadata:
  - name: ID
    type: integer
  rows: []
...
box.space.V2:drop()
---
...
box.space.T2:drop()
---
...
-- Cleanup
box.execute("DROP VIEW v1;");
---
- row_count: 1
...
box.execute("DROP TABLE t1;");
---
- row_count: 1
...
--
-- gh-4740: make sure INSTEAD OF DELETE and INSTEAD OF UPDATE
-- triggers work for each row of view.
--
box.cfg{}
---
...
box.execute('CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT, a INT);')
---
- row_count: 1
...
box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT, a INT);')
---
- row_count: 1
...
box.execute('CREATE VIEW v AS SELECT a FROM t;')
---
- row_count: 1
...
box.execute('CREATE TRIGGER r1 INSTEAD OF DELETE ON v FOR EACH ROW BEGIN INSERT INTO t1 VALUES (NULL, 1); END;')
---
- row_count: 1
...
box.execute('CREATE TRIGGER r2 INSTEAD OF UPDATE ON v FOR EACH ROW BEGIN INSERT INTO t1 VALUES (NULL, 2); END;')
---
- row_count: 1
...
box.execute('INSERT INTO t VALUES (NULL, 1), (NULL, 1), (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 3);')
---
- autoincrement_ids:
  - 1
  - 2
  - 3
  - 4
  - 5
  - 6
  row_count: 6
...
box.execute('DELETE FROM v;')
---
- row_count: 0
...
box.execute('UPDATE v SET a = 10;')
---
- row_count: 0
...
box.execute('SELECT * FROM t1;')
---
- metadata:
  - name: I
    type: integer
  - name: A
    type: integer
  rows:
  - [1, 1]
  - [2, 1]
  - [3, 1]
  - [4, 1]
  - [5, 1]
  - [6, 1]
  - [7, 2]
  - [8, 2]
  - [9, 2]
  - [10, 2]
  - [11, 2]
  - [12, 2]
...
box.execute('DROP VIEW v;')
---
- row_count: 1
...
box.execute('DROP TABLE t;')
---
- row_count: 1
...
box.execute('DROP TABLE t1;')
---
- row_count: 1
...