-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathset.sql
More file actions
183 lines (180 loc) · 5.41 KB
/
set.sql
File metadata and controls
183 lines (180 loc) · 5.41 KB
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
@connect_as_demo
clear screen
set serveroutput on size unlimited format wrapped
-- tab = gameofset_predicate macro
prompt gameofset_predicate
create or replace function gameofset_predicate
( col_in in dbms_tf.columns_t )
return varchar2 sql_macro( scalar ) is
lsql varchar2( 32767 );
begin
lsql := '
( ( c1.' || col_in( 1 ) || ' = c2.' || col_in( 1 ) || '
and c2.' || col_in( 1 ) || ' = c3.' || col_in( 1 ) || '
)
or ( c1.' || col_in( 1 ) || ' <> c2.' || col_in( 1 ) || '
and c2.' || col_in( 1 ) || ' <> c3.' || col_in( 1 ) || '
and c3.' || col_in( 1 ) || ' <> c1.' || col_in( 1 ) || '
)
)
';
dbms_output.put_line( lsql );
return lsql;
end;
/
/*
-- tab = puzzle+solution [failes]
prompt puzzle+solution [failes]
with symbols as
( select 'oval' symbol from dual
union all select 'squiggle' from dual
union all select 'diamond' from dual
)
, colors as
( select 'green' color from dual
union all select 'purple' from dual
union all select 'red' from dual
)
, shadings as
( select 'filled' shading from dual
union all select 'striped' from dual
union all select 'open' from dual
)
, numbers as
( select 1 quantity from dual
union all select 2 from dual
union all select 3 from dual
)
, deck as
(select symbol, color, shading, quantity
from symbols
cross join colors
cross join shadings
cross join numbers
)
, puzzle as
(select rownum as cardno, symbol, color
, shading , quantity
from (select *
from deck
order by dbms_random.value
)
where rownum <= 12
)
, solution as
(select c1.symbol as symbol1
, c1.color as color1
, c1.shading as shading1
, c1.quantity as quantity1
, c2.symbol as symbol2
, c2.color as color2
, c2.shading as shading2
, c2.quantity as quantity2
, c3.symbol as symbol3
, c3.color as color3
, c3.shading as shading3
, c3.quantity as quantity3
from puzzle c1
join puzzle c2
on c1.cardno < c2.cardno
join puzzle c3
on c2.cardno < c3.cardno
where 1=1
and gameofset_predicate(columns(symbol)) = 1
and gameofset_predicate(columns(color)) = 1
and gameofset_predicate(columns(shading)) = 1
and gameofset_predicate(columns(quantity)) = 1
)
select 'puzzle' as what
, symbol, color
, shading, quantity
, null as symbol, null as color
, null as shading, null as quantity
, null as symbol, null as color
, null as shading, null as quantity
from puzzle
union all
select 'solution' as what
, symbol1, color1
, shading1, quantity1
, symbol2, color2
, shading2, quantity2
, symbol3, color3
, shading3, quantity3
from solution
/
-- */
@connect_as_demo
clear screen
set serveroutput on size unlimited format wrapped
-- tab = puzzle+solution
prompt puzzle+solution
with symbols as
( select 'oval' symbol from dual
union all select 'squiggle' from dual
union all select 'diamond' from dual
)
, colors as
( select 'green' color from dual
union all select 'purple' from dual
union all select 'red' from dual
)
, shadings as
( select 'filled' shading from dual
union all select 'striped' from dual
union all select 'open' from dual
)
, numbers as
( select 1 quantity from dual
union all select 2 from dual
union all select 3 from dual
)
, deck as
(select symbol, color, shading, quantity
from symbols
cross join colors
cross join shadings
cross join numbers
)
, puzzle as
(select rownum as cardno, symbol, color
, shading , quantity
from (select *
from deck
order by dbms_random.value
)
where rownum <= 12
)
select 'puzzle' as what
, symbol, color
, shading, quantity
, null as symbol, null as color
, null as shading, null as quantity
, null as symbol, null as color
, null as shading, null as quantity
from puzzle
union all
select 'solution' as what
, c1.symbol as symbol1
, c1.color as color1
, c1.shading as shading1
, c1.quantity as quantity1
, c2.symbol as symbol2
, c2.color as color2
, c2.shading as shading2
, c2.quantity as quantity2
, c3.symbol as symbol3
, c3.color as color3
, c3.shading as shading3
, c3.quantity as quantity3
from puzzle c1
join puzzle c2
on c1.cardno < c2.cardno
join puzzle c3
on c2.cardno < c3.cardno
where 1=1
and gameofset_predicate(columns(symbol)) = 1
and gameofset_predicate(columns(color)) = 1
and gameofset_predicate(columns(shading)) = 1
and gameofset_predicate(columns(quantity)) = 1
/