File: gh2259-in-stmt-trans.test.lua

package info (click to toggle)
tarantool 2.6.0-1.4
  • links: PTS, VCS
  • area: main
  • in suites: sid, trixie
  • size: 85,412 kB
  • sloc: ansic: 513,775; cpp: 69,493; sh: 25,650; python: 19,190; perl: 14,973; makefile: 4,178; yacc: 1,329; sql: 1,074; pascal: 620; ruby: 190; awk: 18; lisp: 7
file content (84 lines) | stat: -rwxr-xr-x 3,063 bytes parent folder | download | duplicates (3)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(20)

box.execute("DROP TABLE IF EXISTS t1")
box.execute("DROP TABLE IF EXISTS t2")

box.execute("CREATE TABLE t1 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
box.execute("CREATE TABLE t2 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")

box.execute("INSERT INTO t2 VALUES (1,1);")
box.execute("INSERT INTO t1 VALUES (3,3);")

for _, prefix in pairs({"BEFORE", "AFTER"}) do
    box.execute('DROP TRIGGER IF EXISTS t1i')
    box.execute('CREATE TRIGGER t1i '..prefix..' INSERT ON t1 FOR EACH ROW \
                     BEGIN INSERT INTO t2 VALUES (1,1); END')

    test:do_catchsql_test(prefix..'_insert1',
                          'INSERT INTO t1 VALUES(1, 2)',
                          {1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})

    test:do_execsql_test(prefix..'_insert1_check1',
                         'SELECT *  FROM t1',
                         {3, 3})

    test:do_execsql_test(prefix..'_insert1_check2',
                         'SELECT *  FROM t2',
                         {1, 1})

    box.execute('DROP TRIGGER IF EXISTS t1u')
    box.execute('CREATE TRIGGER t1u '..prefix..' UPDATE ON t1 FOR EACH ROW \
                     BEGIN INSERT INTO t2 VALUES (1,1); END')

    test:do_catchsql_test(prefix..'_update1',
                          'UPDATE t1 SET s1=1',
                          {1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})

    test:do_execsql_test(prefix..'_update1_check1',
                         'SELECT *  FROM t1',
                         {3, 3})

    test:do_execsql_test(prefix..'_insert1_check2',
                         'SELECT *  FROM t2',
                         {1, 1})

    box.execute('DROP TRIGGER IF EXISTS t1ds')
    -- FOR EACH STATEMENT
    box.execute('CREATE TRIGGER t1ds '..prefix..' DELETE ON t1 FOR EACH ROW\
                       BEGIN INSERT INTO t2 VALUES (2,2); \
                             INSERT INTO t2 VALUES (2,2); END')

    test:do_catchsql_test(prefix..'delete1',
                          'DELETE FROM t1;',
                          {1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})

    -- Nothing should be inserted due to abort
    test:do_execsql_test('delete1_check1',
                         'SELECT * FROM t2',
                         {1, 1})

    -- Nothing should be deleted
    test:do_execsql_test('delete1_check2',
                         'SELECT * FROM t1',
                         {3, 3})

end

-- Check multi-insert
test:do_catchsql_test('insert2',
                      'INSERT INTO t1 VALUES (5, 6), (6, 7)',
                      {1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test('insert2_check',
                     'SELECT * FROM t1;',
                     {3, 3})

-- Cleanup
box.execute('DROP TRIGGER IF EXISTS t1i')
box.execute('DROP TRIGGER IF EXISTS t1u')
box.execute('DROP TRIGGER IF EXISTS t1ds')
box.execute('DROP TABLE t1')
box.execute('DROP TABLE t2')

test:finish_test()