Game-Set-Match/[set]sqlmacro4.sql at main · patch72/Game-Set-Match · 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
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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
-- tab = gameofset4_predicate macro
create or replace function gameofset4_predicate
( col_in in dbms_tf.columns_t )
return varchar2 sql_macro( scalar ) is
  lsql varchar2(32767);
begin
  lsql := '
( case
    when (    (     c1.' || col_in( 1 ) || '  = c2.' || col_in( 1 ) || '
                and c2.' || col_in( 1 ) || '  = c3.' || col_in( 1 ) || '
                and c3.' || col_in( 1 ) || '  = c4.' || col_in( 1 ) || '
              )
           or (     c1.' || col_in( 1 ) || ' <> c2.' || col_in( 1 ) || '
                and c1.' || col_in( 1 ) || ' <> c3.' || col_in( 1 ) || '
                and c1.' || col_in( 1 ) || ' <> c4.' || col_in( 1 ) || '
                and c2.' || col_in( 1 ) || ' <> c3.' || col_in( 1 ) || '
                and c2.' || col_in( 1 ) || ' <> c4.' || col_in( 1 ) || '
                and c3.' || col_in( 1 ) || ' <> c4.' || col_in( 1 ) || '
              )
         ) then 1
    else 0
  end
)';
  dbms_output.put_line( lsql );
  return lsql;

end;
/

-- tab = puzzle+solution [failes]
with symbols as
  (          select 'oval'     symbol   from dual
   union all select 'squiggle'          from dual
   union all select 'diamond'           from dual
   union all select 'square'            from dual -- fourth symbol
  )
,    colors   as
 (           select 'green'    color    from dual
   union all select 'purple'            from dual
   union all select 'red'               from dual
   union all select 'yellow'            from dual -- fourth color
  )
,    shadings as
 (           select 'filled'   shading  from dual
   union all select 'striped'           from dual
   union all select 'open'              from dual
   union all select 'dotted'            from dual -- fourth shading
  )
,    numbers  as
 (           select 1          quantity from dual
   union all select 2                   from dual
   union all select 3                   from dual
   union all select 4                   from dual -- fourth quantity
  )
,    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
          )
   -- increase this number to increase the number of sets present
   where  rownum <= 20
  )
,    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
        , c4.symbol   as symbol4
        , c4.color    as color4
        , c4.shading  as shading4
        , c4.quantity as quantity4
   from   puzzle c1
   join   puzzle c2
     on   c1.cardno < c2.cardno
   join   puzzle c3
     on   c2.cardno < c3.cardno
   join   puzzle c4
     on   c3.cardno < c4.cardno
   where  1=1
   and    gameofset4_predicate( columns( symbol   ) ) = 1
   and    gameofset4_predicate( columns( color    ) ) = 1
   and    gameofset4_predicate( columns( shading  ) ) = 1
   and    gameofset4_predicate( columns( quantity ) ) = 1
  )
select 'puzzle'
     , puzzle.symbol  as symbol1,  puzzle.color    as color1
     , puzzle.shading as shading1, puzzle.quantity as quantity1
     , null           as symbol2,  null            as color2
     , null           as shading2, null            as quantity2
     , null           as symbol3,  null            as color3
     , null           as shading3, null            as quantity3
     , null           as symbol4,  null            as color4
     , null           as shading4, null            as quantity4
from   puzzle
union  all
select 'solution'     as what
     ,                   symbol1,                     color1
     ,                   shading1,                    quantity1
     ,                   symbol2,                     color2
     ,                   shading2,                    quantity2
     ,                   symbol3,                     color3
     ,                   shading3,                    quantity3
     ,                   symbol4,                     color4
     ,                   shading4,                    quantity4
from   solution
/
-- tab = puzzle+solution
with symbols as
  (          select 'oval'     symbol   from dual
   union all select 'squiggle'          from dual
   union all select 'diamond'           from dual
   union all select 'square'            from dual -- fourth symbol
  )
,    colors   as
 (           select 'green'    color    from dual
   union all select 'purple'            from dual
   union all select 'red'               from dual
   union all select 'yellow'            from dual -- fourth color
  )
,    shadings as
 (           select 'filled'   shading  from dual
   union all select 'striped'           from dual
   union all select 'open'              from dual
   union all select 'dotted'            from dual -- fourth shading
  )
,    numbers  as
 (           select 1          quantity from dual
   union all select 2                   from dual
   union all select 3                   from dual
   union all select 4                   from dual -- fourth quantity
  )
,    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
          )
   -- increase this number to increase the number of sets present
   where  rownum <= 20
  )
select 'puzzle'   as what
     , symbol     as symbol1 ,  color    as color1
     , shading    as shading1, quantity as quantity1
     , null       as symbol2 ,  null as color2
     , null       as shading2, null as quantity2
     , null       as symbol3 ,  null as color3
     , null       as shading3, null as quantity3
     , null       as symbol4 ,  null as color4
     , null       as shading4, null as quantity4
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
     , c4.symbol  as symbol4 , c4.color    as color4
     , c4.shading as shading4, c4.quantity as quantity4
from   puzzle c1
join   puzzle c2
  on   c1.cardno < c2.cardno
join   puzzle c3
  on   c2.cardno < c3.cardno
join   puzzle c4
  on   c3.cardno < c4.cardno
where  1=1
and    gameofset4_predicate( columns( symbol   ) ) = 1
and    gameofset4_predicate( columns( color    ) ) = 1
and    gameofset4_predicate( columns( shading  ) ) = 1
and    gameofset4_predicate( columns( quantity ) ) = 1
/