Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1a77894

Browse files
committed
Add rule tests to regression tests...
1 parent338c54c commit1a77894

File tree

3 files changed

+402
-0
lines changed

3 files changed

+402
-0
lines changed

‎src/test/regress/sql/run_ruletest.sql

Lines changed: 261 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,261 @@
1+
--
2+
-- Tests on a view that is select * of a table
3+
-- and has insert/update/delete instead rules to
4+
-- behave close like the real table.
5+
--
6+
7+
--
8+
-- We need test date later
9+
--
10+
insert into rtest_t2values (1,21);
11+
insert into rtest_t2values (2,22);
12+
insert into rtest_t2values (3,23);
13+
14+
insert into rtest_t3values (1,31);
15+
insert into rtest_t3values (2,32);
16+
insert into rtest_t3values (3,33);
17+
insert into rtest_t3values (4,34);
18+
insert into rtest_t3values (5,35);
19+
20+
-- insert values
21+
insert into rtest_v1values (1,11);
22+
insert into rtest_v1values (2,12);
23+
select*from rtest_v1;
24+
25+
-- delete with constant expression
26+
deletefrom rtest_v1where a=1;
27+
select*from rtest_v1;
28+
insert into rtest_v1values (1,11);
29+
deletefrom rtest_v1where b=12;
30+
select*from rtest_v1;
31+
insert into rtest_v1values (2,12);
32+
insert into rtest_v1values (2,13);
33+
select*from rtest_v1;
34+
** Remember thedelete ruleon rtest_v1: It says
35+
** DO INSTEADDELETEFROM rtest_t1WHERE a=current.a
36+
** So thistime both rows with a=2 must get deleted
37+
\p
38+
\r
39+
deletefrom rtest_v1where b=12;
40+
select*from rtest_v1;
41+
deletefrom rtest_v1;
42+
43+
-- insert select
44+
insert into rtest_v1select*from rtest_t2;
45+
select*from rtest_v1;
46+
deletefrom rtest_v1;
47+
48+
-- same with swapped targetlist
49+
insert into rtest_v1 (b, a)select b, afrom rtest_t2;
50+
select*from rtest_v1;
51+
52+
-- now with only one target attribute
53+
insert into rtest_v1 (a)select afrom rtest_t3;
54+
select*from rtest_v1;
55+
select*from rtest_v1where b isnull;
56+
57+
-- let attribute a differ (must be done on rtest_t1 - see above)
58+
update rtest_t1set a= a+10where b isnull;
59+
deletefrom rtest_v1where b isnull;
60+
select*from rtest_v1;
61+
62+
-- now updates with constant expression
63+
update rtest_v1set b=42where a=2;
64+
select*from rtest_v1;
65+
update rtest_v1set b=99where b=42;
66+
select*from rtest_v1;
67+
update rtest_v1set b=88where b<50;
68+
select*from rtest_v1;
69+
deletefrom rtest_v1;
70+
insert into rtest_v1selectrtest_t2.a,rtest_t3.bwherertest_t2.a=rtest_t3.a;
71+
select*from rtest_v1;
72+
73+
-- updates in a mergejoin
74+
update rtest_v1set b=rtest_t2.bwhere a=rtest_t2.a;
75+
select*from rtest_v1;
76+
insert into rtest_v1select*from rtest_t3;
77+
select*from rtest_v1;
78+
update rtest_t1set a= a+10where b>30;
79+
select*from rtest_v1;
80+
update rtest_v1set a=rtest_t3.a+20where b=rtest_t3.b;
81+
select*from rtest_v1;
82+
83+
--
84+
-- Test for constraint updates/deletes
85+
--
86+
insert into rtest_systemvalues ('orion','Linux Jan Wieck');
87+
insert into rtest_systemvalues ('notjw','WinNT Jan Wieck (notebook)');
88+
insert into rtest_systemvalues ('neptun','Fileserver');
89+
90+
insert into rtest_interfacevalues ('orion','eth0');
91+
insert into rtest_interfacevalues ('orion','eth1');
92+
insert into rtest_interfacevalues ('notjw','eth0');
93+
insert into rtest_interfacevalues ('neptun','eth0');
94+
95+
insert into rtest_personvalues ('jw','Jan Wieck');
96+
insert into rtest_personvalues ('bm','Bruce Momjian');
97+
98+
insert into rtest_adminvalues ('jw','orion');
99+
insert into rtest_adminvalues ('jw','notjw');
100+
insert into rtest_adminvalues ('bm','neptun');
101+
102+
update rtest_systemset sysname='pluto'where sysname='neptun';
103+
104+
select*from rtest_interface;
105+
select*from rtest_admin;
106+
107+
update rtest_personset pname='jwieck'where pdesc='Jan Wieck';
108+
109+
select*from rtest_admin;
110+
111+
deletefrom rtest_systemwhere sysname='orion';
112+
113+
select*from rtest_interface;
114+
select*from rtest_admin;
115+
116+
--
117+
-- Rule qualification test
118+
--
119+
insert into rtest_empvalues ('wiech','5000.00');
120+
insert into rtest_empvalues ('gates','80000.00');
121+
update rtest_empset ename='wiecx'where ename='wiech';
122+
update rtest_empset ename='wieck', salary='6000.00'where ename='wiecx';
123+
update rtest_empset salary='7000.00'where ename='wieck';
124+
deletefrom rtest_empwhere ename='gates';
125+
126+
select*from rtest_emplog;
127+
insert into rtest_empmassvalues ('meyer','4000.00');
128+
insert into rtest_empmassvalues ('maier','5000.00');
129+
insert into rtest_empmassvalues ('mayr','6000.00');
130+
insert into rtest_empselect*from rtest_empmass;
131+
select*from rtest_emplog;
132+
update rtest_empmassset salary= salary+'1000.00';
133+
update rtest_empset salary=rtest_empmass.salarywhere ename=rtest_empmass.ename;
134+
select*from rtest_emplog;
135+
deletefrom rtest_empwhere ename=rtest_empmass.ename;
136+
select*from rtest_emplog;
137+
138+
--
139+
-- Multiple cascaded qualified instead rule test
140+
--
141+
insert into rtest_t4values (1,'Record should go to rtest_t4');
142+
insert into rtest_t4values (2,'Record should go to rtest_t4');
143+
insert into rtest_t4values (10,'Record should go to rtest_t5');
144+
insert into rtest_t4values (15,'Record should go to rtest_t5');
145+
insert into rtest_t4values (19,'Record should go to rtest_t5 and t7');
146+
insert into rtest_t4values (20,'Record should go to rtest_t4 and t6');
147+
insert into rtest_t4values (26,'Record should go to rtest_t4 and t8');
148+
insert into rtest_t4values (28,'Record should go to rtest_t4 and t8');
149+
insert into rtest_t4values (30,'Record should go to rtest_t4');
150+
insert into rtest_t4values (40,'Record should go to rtest_t4');
151+
152+
select*from rtest_t4;
153+
select*from rtest_t5;
154+
select*from rtest_t6;
155+
select*from rtest_t7;
156+
select*from rtest_t8;
157+
158+
deletefrom rtest_t4;
159+
deletefrom rtest_t5;
160+
deletefrom rtest_t6;
161+
deletefrom rtest_t7;
162+
deletefrom rtest_t8;
163+
164+
insert into rtest_t9values (1,'Record should go to rtest_t4');
165+
insert into rtest_t9values (2,'Record should go to rtest_t4');
166+
insert into rtest_t9values (10,'Record should go to rtest_t5');
167+
insert into rtest_t9values (15,'Record should go to rtest_t5');
168+
insert into rtest_t9values (19,'Record should go to rtest_t5 and t7');
169+
insert into rtest_t9values (20,'Record should go to rtest_t4 and t6');
170+
insert into rtest_t9values (26,'Record should go to rtest_t4 and t8');
171+
insert into rtest_t9values (28,'Record should go to rtest_t4 and t8');
172+
insert into rtest_t9values (30,'Record should go to rtest_t4');
173+
insert into rtest_t9values (40,'Record should go to rtest_t4');
174+
175+
insert into rtest_t4select*from rtest_t9where a<20;
176+
177+
select*from rtest_t4;
178+
select*from rtest_t5;
179+
select*from rtest_t6;
180+
select*from rtest_t7;
181+
select*from rtest_t8;
182+
183+
insert into rtest_t4select*from rtest_t9where b ~'and t8';
184+
185+
select*from rtest_t4;
186+
select*from rtest_t5;
187+
select*from rtest_t6;
188+
select*from rtest_t7;
189+
select*from rtest_t8;
190+
191+
insert into rtest_t4select a+1, bfrom rtest_t9where ain (20,30,40);
192+
193+
select*from rtest_t4;
194+
select*from rtest_t5;
195+
select*from rtest_t6;
196+
select*from rtest_t7;
197+
select*from rtest_t8;
198+
199+
--
200+
-- Check that the ordering of rules fired is correct
201+
--
202+
insert into rtest_order1values (1);
203+
select*from rtest_order2;
204+
205+
--
206+
-- Check if instead nothing w/without qualification works
207+
--
208+
insert into rtest_nothn1values (1,'want this');
209+
insert into rtest_nothn1values (2,'want this');
210+
insert into rtest_nothn1values (10,'don''t want this');
211+
insert into rtest_nothn1values (19,'don''t want this');
212+
insert into rtest_nothn1values (20,'want this');
213+
insert into rtest_nothn1values (29,'want this');
214+
insert into rtest_nothn1values (30,'don''t want this');
215+
insert into rtest_nothn1values (39,'don''t want this');
216+
insert into rtest_nothn1values (40,'want this');
217+
insert into rtest_nothn1values (50,'want this');
218+
insert into rtest_nothn1values (60,'want this');
219+
220+
select*from rtest_nothn1;
221+
222+
insert into rtest_nothn2values (10,'too small');
223+
insert into rtest_nothn2values (50,'too small');
224+
insert into rtest_nothn2values (100,'OK');
225+
insert into rtest_nothn2values (200,'OK');
226+
227+
select*from rtest_nothn2;
228+
select*from rtest_nothn3;
229+
230+
deletefrom rtest_nothn1;
231+
deletefrom rtest_nothn2;
232+
deletefrom rtest_nothn3;
233+
234+
insert into rtest_nothn4values (1,'want this');
235+
insert into rtest_nothn4values (2,'want this');
236+
insert into rtest_nothn4values (10,'don''t want this');
237+
insert into rtest_nothn4values (19,'don''t want this');
238+
insert into rtest_nothn4values (20,'want this');
239+
insert into rtest_nothn4values (29,'want this');
240+
insert into rtest_nothn4values (30,'don''t want this');
241+
insert into rtest_nothn4values (39,'don''t want this');
242+
insert into rtest_nothn4values (40,'want this');
243+
insert into rtest_nothn4values (50,'want this');
244+
insert into rtest_nothn4values (60,'want this');
245+
246+
insert into rtest_nothn1select*from rtest_nothn4;
247+
248+
select*from rtest_nothn1;
249+
250+
deletefrom rtest_nothn4;
251+
252+
insert into rtest_nothn4values (10,'too small');
253+
insert into rtest_nothn4values (50,'too small');
254+
insert into rtest_nothn4values (100,'OK');
255+
insert into rtest_nothn4values (200,'OK');
256+
257+
insert into rtest_nothn2select*from rtest_nothn4;
258+
259+
select*from rtest_nothn2;
260+
select*from rtest_nothn3;
261+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp