CHECK-Constraint-Compiler/execute_function.sql at main · glatiuden/CHECK-Constraint-Compiler · GitHub

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
85
86
87
88
89
90
91
92
93
94
95
96
-- VALID: Trivial Example - any customer aged below 21 downloading 'Domainer' will trigger the function
-- EXPECTED RESULT: true
Select add_constraint(
    'r21',
    '
    SELECT c.customerid
    FROM customers c NATURAL JOIN downloads d
    WHERE d.name = ''Domainer'' AND c.age < 21
    ',
    'EXISTS',
    'INSERT OR UPDATE',
    'DEFERRABLE INITIALLY DEFERRED',
    'customers, downloads',
    'An underaged customer cannot download Domainer'
);


-- INVALID: invalid SQL query as condition
-- EXPECTED RESULT: Invalid condition entered, make sure it is a valid SQL query!
Select add_constraint(
    'r21',
    '
    SELECT c.customerid
    FROM customers c NATURAL JOIN downloads d
    WHERE d.name = ''Domainer'' AND c.age < ABC
    ',
    'EXISTS',
    'INSERT OR UPDATE',
    'DEFERRABLE INITIALLY DEFERRED',
    'customers, downloads',
    'An underaged customer cannot download Domainer'
);

-- INVALID: EXIST sytax, only [EXISTS / NOT EXISTS] (case insensitive)
-- EXPECTED RESULT: "HAS" is not a valid argument, either input "EXISTS" or "NOT EXISTS" (case insensitive)
Select add_constraint(
    'r21',
    '
    SELECT c.customerid
    FROM customers c NATURAL JOIN downloads d
    WHERE d.name = ''Domainer'' AND c.age < 21
    ',
    'HAS',
    'INSERT OR UPDATE',
    'DEFERRABLE INITIALLY DEFERRED',
    'customers, downloads',
    'An underaged customer cannot download Domainer'
);

-- INVALID: DEFER syntax, only [DEFERRABLE | NOT DEFERRABLE] [INITIALLY IMMEDIATE | INITIALLY DEFERRED] (case insensitive)
-- EXPECTED RESULT: syntax error at or near "DEFERRABLE"
Select add_constraint(
    'r21',
    '
    SELECT c.customerid
    FROM customers c NATURAL JOIN downloads d
    WHERE d.name = ''Domainer'' AND c.age < 21
    ',
    'EXISTS',
    'INSERT OR UPDATE',
    'DEFERRABLE INITIALLY DEFERRABLE',
    'customers, downloads',
    'An underaged customer cannot download Domainer'
);

-- INVALID: No table entered by user
-- EXPECTED RESULT: No table input detected, please input at least one existing table for the trigger to be applied to!
Select add_constraint(
    'r21',
    '
    SELECT c.customerid
    FROM customers c NATURAL JOIN downloads d
    WHERE d.name = ''Domainer'' AND c.age < 21
    ',
    'EXISTS',
    'INSERT OR UPDATE',
    'DEFERRABLE INITIALLY DEFERRED',
    '',
    'An underaged customer cannot download Domainer'
);

-- INVALID: Table entered by user does not exists
-- EXPECTED RESULT: Table name "downloadsssss" does not exists!
Select add_constraint(
    'r21',
    '
    SELECT c.customerid
    FROM customers c NATURAL JOIN downloads d
    WHERE d.name = ''Domainer'' AND c.age < 21
    ',
    'EXISTS',
    'INSERT OR UPDATE',
    'DEFERRABLE INITIALLY DEFERRED',
    'customers, downloadsssss',
    'An underaged customer cannot download Domainer'
);