An alternative would be to use ABAP SQL! What about an SQL statement which can play chess?
Let’s give it a go!
Note: the demo code here was written about 5 years ago and does not use the newest ABAP SQL features. Note also that most tables mentioned here are actually available as demo content in modern SAP NetWeaver systems, see the package SABAP_DEMOS_SQL_CHESS.
Note also: I am a curious programmer, but no chess expert. There might be subtle non-rule conforming errors in the coding below. Please find them and post it in the comments section below.
Because we want to write SQL, everything must be on the database. In the first example we use global temporary tables for some auxiliary data. In modern ABAP SQL you could probably use SERIES_GENERATE_INTEGER and avoid additional tables completely. In the final “monster” statement of this blog post at the end of the article we will use joins with T000 to get a similar result.
The first table simply holds all different kinds of possible positions, like (1,1), (1,2), …, (8,8). We go for the mathematical coordinate like notation here instead of the chess notation (which would be A1, B2). It has two columns X_POS and Y_POS of type integer and is filled like this:
insert demo_chess_pos_t from table @(
value #( for i = 1 then i + 1 while i <= 8
for j = 1 then j + 1 while j <= 8
( x_pos = i y_pos = j )
) ).The second table contains all different kind of chessman, like rook, king, etc. The best way is to create an ABAP enumeration type and fill the database table like this:
insert demo_chess_cm_t from table @(
value #( ( chessman = cl_demo_sql_chess=>c_chessman_king )
( chessman = cl_demo_sql_chess=>c_chessman_queen )
( chessman = cl_demo_sql_chess=>c_chessman_rook )
( chessman = cl_demo_sql_chess=>c_chessman_knight )
( chessman = cl_demo_sql_chess=>c_chessman_bishop )
( chessman = cl_demo_sql_chess=>c_chessman_pawn )
) ).
The last table simply contains all different kinds of colours, i.e. black and white.
insert demo_chess_col_t from table @( value #(
( color = cl_demo_sql_chess=>c_color_white )
( color = cl_demo_sql_chess=>c_color_black )
) ).Next we want a table which contains all possible chess moves if only 1 chessman is on the chessboard. This table DEMO_CHESS_MOVES contains the following columns:
| COLOR (KEY) | Enumeration value | Color (white, black) |
| CHESSMAN (KEY) | Enumeration vlaue | Chessman (king, rook, …) |
| FROM_X (KEY) | Integer | Starting position, x value |
| FROM_Y (KEY) | Integer | Starting position, y value |
| TO_X (KEY) | Integer | Target position, x value |
| TO_Y (KEY) | Integer | Target position, y value |
| SPECIAL_FLAG | Boolean | Normal move or special move |
| COLOR_DEP | Enumeration value | Color of dependent chessman (see below) |
| CHESSMAN_DEP | Enumeration value | Dependent chessman (see below) |
| FROM_X_DEP | Integer | Starting position, dependent move (see below), x value |
| FROM_Y_DEP | Integer | Starting position, dependent move (see below), y value |
| TO_X_DEP | Integer | Target position dependent move, x value (see below) |
| TO_Y_DEP | Integer | Target position dependent move, y value (see below) |
| TRANSFORM_PAWN_FLAG | Boolean | Is the pawn transformed to, e.g. a queen? |
The “SPECIAL_FLAG” contains a space if the move is not special, else it contains ‘E’, ‘P’ or ‘C’. This is the special meaning of the flags:
| E | Move can only be done if the field is empy (for pawns) |
| C | Castling (in this case the “DEP” fields contain the corresponding move of the king/rook) |
| P | Pawn mode. Means that the move can only be done if the target field is occupied by an enemy chessman OR if en-passant can be made. |
Now, it is time to fill the table with all possible chess moves (assuming only one chessman is on the board). We will lift this restriction below, so stay tuned!
This can be done via a simple INSERT statement (which we will call the “baby monster” in the sequel).
insert demo_chess_moves from (
select
from demo_chess_col_t as color
cross join demo_chess_cm_t as chessman
cross join demo_chess_pos_t as source
inner join demo_chess_pos_t as target on
case chessman
when @c_chessman_king then
case when
" normal king movement
( abs( source~x_pos - target~x_pos ) <= 1 and
abs( source~y_pos - target~y_pos ) <= 1 and not
( source~x_pos = target~x_pos and
source~y_pos = target~y_pos ) ) or
" castling
( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7 and target~y_pos = 1 ) or
( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and target~y_pos = 1 ) or
( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and target~y_pos = 1 ) or
( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and target~y_pos = 1 )
then 1 else 0 end
when @c_chessman_queen then
case when ( abs( target~y_pos - source~y_pos )
= abs( target~x_pos - source~x_pos ) or
target~x_pos = source~x_pos or
target~y_pos = source~y_pos ) and not
( source~x_pos = target~x_pos and
source~y_pos = target~y_pos )
then 1 else 0 end
when @c_chessman_rook then
case when ( target~x_pos = source~x_pos or
target~y_pos = source~y_pos ) and not
( source~x_pos = target~x_pos and
source~y_pos = target~y_pos )
then 1 else 0 end
when @c_chessman_knight then
case when ( abs( target~x_pos - source~x_pos ) = 1 and
abs( target~y_pos - source~y_pos ) = 2 ) or
( abs( target~x_pos - source~x_pos ) = 2 and
abs( target~y_pos - source~y_pos ) = 1 )
then 1 else 2 end
when @c_chessman_bishop then
case when abs( target~y_pos - source~y_pos ) =
abs( target~x_pos - source~x_pos ) and not
( source~x_pos = target~x_pos and
source~y_pos = target~y_pos )
then 1 else 0 end
when @c_chessman_pawn then
case when
" white pawn move 1 field
( color = @c_color_white and
target~y_pos = source~y_pos + 1 and
abs( target~x_pos - source~x_pos ) <= 1 and
source~y_pos > 1
) or
" white pawn move 2 fields
( color = @c_color_white and
target~y_pos = 4 and
source~y_pos = 2 and
target~x_pos = source~x_pos ) or
" black pawn move 1 field
( color = @c_color_black and
target~y_pos = source~y_pos - 1 and
abs( target~x_pos - source~x_pos ) <= 1 and
source~y_pos < 8 ) or
" black pawn move 2 fields
( color = @c_color_black and
target~y_pos = 5 and
source~y_pos = 7 and
target~x_pos = source~x_pos )
then 1 else 0 end
end = 1
fields color, chessman, source~x_pos, source~y_pos, target~x_pos, target~y_pos,
coalesce(
case chessman
when @c_chessman_king then
" castling
case when
( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7 and target~y_pos = 1 ) or
( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and target~y_pos = 1 ) or
( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and target~y_pos = 1 ) or
( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and target~y_pos = 1 )
then @c_special_flag_castling
end
when @c_chessman_pawn then
" special rule for pawns => they may only move diagonal
case when abs( target~x_pos - source~x_pos ) = 1
then @c_special_flag_pawn
else @c_special_flag_empty
end
end
, @c_special_flag_none ),
coalesce(
case chessman
when @c_chessman_king then
case when abs( target~x_pos - source~x_pos ) > 1
then color
end
when @c_chessman_pawn then
" en passant => color
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then @c_color_black
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then @c_color_white
end
end
, @c_color_invalid ),
coalesce(
case chessman
when @c_chessman_king then
case when abs( target~x_pos - source~x_pos ) > 1
then @c_chessman_rook
end
when @c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then @c_chessman_pawn
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then @c_chessman_pawn
end
end
, @c_chessman_invalid ),
coalesce(
case chessman
when @c_chessman_king then
case when ( color = @c_color_white and source~x_pos = 5 and
source~y_pos = 1 and
target~x_pos = 7 and target~y_pos = 1 )
then cast( 8 as int1 )
when ( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7
and target~y_pos = 1 )
then cast( 8 as int1 )
when ( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3
and target~y_pos = 1 )
then cast( 1 as int1 )
end
when @c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then target~x_pos
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then target~x_pos
end
end
, @c_position_invalid ),
coalesce(
case chessman
when @c_chessman_king then
case when
( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 8 as int1 )
when ( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 8 as int1 )
end
when @c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then cast( 7 as int1 )
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then cast( 2 as int1 )
end
end
, @c_position_invalid ),
coalesce(
case chessman
when @c_chessman_king then
case when ( color = @c_color_white and source~x_pos = 5
" castling
and source~y_pos = 1 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 6 as int1 )
when ( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 4 as int1 )
when ( color = @c_color_black and source~x_pos = 5
" castling
and source~y_pos = 8 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 6 as int1 )
when ( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 4 as int1 )
end
when @c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
" en passant/castling => x_to_dep
color = @c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then target~x_pos
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then target~x_pos
end
end
, @c_position_invalid ),
coalesce(
case chessman
when @c_chessman_king then
case when ( color = @c_color_white and source~x_pos = 5
" castling
and source~y_pos = 1 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @c_color_black and source~x_pos = 5
" castling
and source~y_pos = 8 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 8 as int1 )
when ( color = @c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 8 as int1 )
end
when @c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
" en passant/castling => y_to_dep
color = @c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then cast( 5 as int1 )
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then cast( 4 as int1 )
end
end
, @c_position_invalid ),
case chessman
when @c_chessman_pawn then
case when ( target~y_pos = 1 and color = @c_color_black ) or
( target~y_pos = 8 and color = @c_color_white )
then @abap_true
else @abap_false
end
else @abap_false
end
).This table is a huge step forward. We now have the possibility to generate all chess moves if only one chessman is on the board.
In a normal game, we typically have many chessman on the board. To generate all possible positions in a real chess game we need some other database tables which contains an actual chess game position.
We thus create two further tables, DEMO_CHESS_GAME and DEMO_CHESS_POS. The first is the header table which contains some metadata about a chess game. The other is the table with the
actual positions.
Here is the table DEMO_CHESS_GAME:
| Field name | Datatype | Usage |
| CLIENT (KEY) | CLIENT | Client |
| GAMEUUID (KEY) | CHAR(32) | UUID of the game |
| MOVECNT | INTEGER | Number of half-turn |
| COLOR | Enumeration type | Color of player which should move next |
| CASTLING_WHITE_LONG | Boolean | Did white already did a long castling? |
| CASTLING_WHITE_SHORT | Boolean | Did white already did a short castling? |
| CASTLING_BLACK_LONG | Boolean | Did black already did a long castling? |
| CASTLING_BLACK_SHORT | Boolean | Did black already did a short castling? |
We now need the database table DEMO_CHESS_POS with the actual game position:
| Field name | Datatype | Usage |
| CLIENT (KEY) | CLIENT | Client |
| GAMEUUID (KEY) | CHAR(32) | UUID of the game |
| MOVECNT (KEY) | Integer | Number of half-turn |
| CHESSMAN (KEY) | Enumeration value | Kind of chessman (rook, king, …) |
| COLOR (KEY) | Enumeration value | Color of chessman |
| X (KEY) | Integer | X-position (i.e. x coordinate) |
| Y (KEY) | Integer | Y-position (i.e. y coordinate) |
| BEFORE_X | Integer | Previous X-position |
| BEFORE_Y | Integer | Previous Y-position |
Now, given the above two tables filled with an actual game position you can write a turn-generator which, given an actual game position in DEMO_CHESS_GAME and DEMO_CHESS_POS, can generate all valid possible moves in just a single SQL statement. Alternatively we can check whether a given move is correct.
There are two possible ways to do that:
- By writing a cascading hierarchy of CDS views. This has been done and the corresponding view is named DEMO_CHESS_V_ALLOWED in package SABAP_DEMOS_SQL_CHESS. This view hierarchy heavily uses the DEMO_CHESS_MOVES table which we filled above using the gigantic INSERT statement. Please also look at the corresponding class CL_DEMO_SQL_CHESS, which contains the complete SQL game engine using these views.
- Write a single ABAP SQL WITH statement which combines the coding from the above INSERT statement with additional logic to check chess moves if more than one chessman is on the board. This WITH statement is just a single ABAP statement and does not use the table DEMO_CHESS_MOVES anymore, as it contains this table as a common table expression (CTE) in a (small) subpart.
As replicating a CDS view hierarchy inside a blog post is rather difficult, let’s go for the second option in this blog post. We will also go for the option to check whether a given move is correct. This is not less difficult as the generation of all possible chess moves which are valid in a given chess possition.
We thus make the following assumptions:
- You have the complete game positions filled into the table DEMO_CHESS_GAME and DEMO_CHESS_MOVE
- You have variables UUID, MOVECNT, COLOR, CHESSMAN, FROM_X, FROM_Y, TO_X, TO_Y which contain a game UUID, move number and the chessman you want to move from (FROM_X, FROM_Y) to (TO_X, TO_Y)
We want to achieve the following query:
- An empty result, if the move is not valid
- The complete content of the table DEMO_CHESS_POS for the next move, if the move has been valid
So you could now INSERT the result (if non empty) into DEMO_CHESS_POS, update the MOVCNT in the table DEMO_CHESS_GAME and continue for the next move.
The solution is this stunning 1100 lines of shear beauty (the “monster”). Note we will use subsequent JOINs with T000 to circumvent the usage of GTTs and SERIES_GENERATE_INTEGER.
with
+dummy as ( select 1 as one from t000 where mandt = @sy-mandt ),
+numbers( num ) as (
select 1 from +dummy union all
select 2 from +dummy union all
select 3 from +dummy union all
select 4 from +dummy union all
select 5 from +dummy union all
select 6 from +dummy union all
select 7 from +dummy union all
select 8 from +dummy ),
+coordinates( x_pos, y_pos ) as ( select t1~num, t2~num from +numbers as t1
cross join +numbers as t2 ),
+chessmen( chessman ) as (
select @cl_demo_sql_chess=>c_chessman_king from +dummy union all
select @cl_demo_sql_chess=>c_chessman_queen from +dummy union all
select @cl_demo_sql_chess=>c_chessman_rook from +dummy union all
select @cl_demo_sql_chess=>c_chessman_knight from +dummy union all
select @cl_demo_sql_chess=>c_chessman_bishop from +dummy union all
select @cl_demo_sql_chess=>c_chessman_pawn from +dummy ),
+colors( color ) as
( select @cl_demo_sql_chess=>c_color_white from +dummy union all
select @cl_demo_sql_chess=>c_color_black from +dummy ),
+moves( color, chessman, from_x, from_y, to_x, to_y, special_flag, color_dep,
chessman_dep, from_x_dep, from_y_dep, to_x_dep, to_y_dep,
transform_pawn_flag ) as
( select from +colors as colors
cross join +chessmen as chessman
cross join +coordinates as source
inner join +coordinates as target on
case chessman
when @cl_demo_sql_chess=>c_chessman_king then
case when ( abs( source~x_pos - target~x_pos ) <= 1 and
abs( source~y_pos - target~y_pos ) <= 1 and not
( source~x_pos = target~x_pos and
source~y_pos = target~y_pos ) ) or
( color = @cl_demo_sql_chess=>c_color_white and
source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7 and
target~y_pos = 1 ) or
( color = @cl_demo_sql_chess=>c_color_white and
source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and
target~y_pos = 1 ) or
( color = @cl_demo_sql_chess=>c_color_black and
source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and
target~y_pos = 1 ) or
( color = @cl_demo_sql_chess=>c_color_black and
source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and
target~y_pos = 1 )
then 1 else 0 end
when @cl_demo_sql_chess=>c_chessman_queen then
case when ( abs( target~y_pos - source~y_pos ) =
abs( target~x_pos - source~x_pos ) or
target~x_pos = source~x_pos or
target~y_pos = source~y_pos ) and not
( source~x_pos = target~x_pos and
source~y_pos = target~y_pos )
then 1 else 0 end
when @cl_demo_sql_chess=>c_chessman_rook then
case when ( target~x_pos = source~x_pos or
target~y_pos = source~y_pos ) and not
( source~x_pos = target~x_pos and
source~y_pos = target~y_pos )
then 1 else 0 end
when @cl_demo_sql_chess=>c_chessman_knight then
case when ( abs( target~x_pos - source~x_pos ) = 1 and
abs( target~y_pos - source~y_pos ) = 2 ) or
( abs( target~x_pos - source~x_pos ) = 2 and
abs( target~y_pos - source~y_pos ) = 1 )
then 1 else 2 end
when @cl_demo_sql_chess=>c_chessman_bishop then
case when abs( target~y_pos - source~y_pos )
= abs( target~x_pos - source~x_pos ) and not
( source~x_pos = target~x_pos and
source~y_pos = target~y_pos )
then 1 else 0 end
when @cl_demo_sql_chess=>c_chessman_pawn then
case when ( color = @cl_demo_sql_chess=>c_color_white and
target~y_pos = source~y_pos + 1 and
abs( target~x_pos - source~x_pos ) <= 1 and
source~y_pos > 1
) or
( color = @cl_demo_sql_chess=>c_color_white and
target~y_pos = 4 and
source~y_pos = 2 and
target~x_pos = source~x_pos ) or
( color = @cl_demo_sql_chess=>c_color_black and
target~y_pos = source~y_pos - 1 and
abs( target~x_pos - source~x_pos ) <= 1 and
source~y_pos < 8 ) or
( color = @cl_demo_sql_chess=>c_color_black and
target~y_pos = 5 and
source~y_pos = 7 and
target~x_pos = source~x_pos )
then 1 else 0 end
end = 1
fields color, chessman, source~x_pos, source~y_pos, target~x_pos,
target~y_pos,
coalesce(
case chessman
when @cl_demo_sql_chess=>c_chessman_king then case when ( color =
@cl_demo_sql_chess=>c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7 and target~y_pos = 1 ) or
( color = @cl_demo_sql_chess=>c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and target~y_pos = 1 ) or
( color = @cl_demo_sql_chess=>c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and target~y_pos = 1 ) or
( color = @cl_demo_sql_chess=>c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and target~y_pos = 1 )
then @cl_demo_sql_chess=>c_special_flag_castling
end
when @cl_demo_sql_chess=>c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1
then @cl_demo_sql_chess=>c_special_flag_pawn
else @cl_demo_sql_chess=>c_special_flag_empty
end
end
, @cl_demo_sql_chess=>c_special_flag_none ),
coalesce(
case chessman
when @cl_demo_sql_chess=>c_chessman_king
then case when abs( target~x_pos - source~x_pos ) > 1
then color
end
when @cl_demo_sql_chess=>c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then @cl_demo_sql_chess=>c_color_black
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then @cl_demo_sql_chess=>c_color_white
end
end
, @cl_demo_sql_chess=>c_color_invalid ),
coalesce(
case chessman
when @cl_demo_sql_chess=>c_chessman_king then
case when abs( target~x_pos - source~x_pos ) > 1
then @cl_demo_sql_chess=>c_chessman_rook
end
when @cl_demo_sql_chess=>c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then @cl_demo_sql_chess=>c_chessman_pawn
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then @cl_demo_sql_chess=>c_chessman_pawn
end
end
, @cl_demo_sql_chess=>c_chessman_invalid ),
coalesce(
case chessman
when @cl_demo_sql_chess=>c_chessman_king then
case when ( color = @cl_demo_sql_chess=>c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7 and target~y_pos = 1 )
then cast( 8 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_white and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and target~y_pos = 1 )
then cast( 8 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_black and source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and target~y_pos = 1 )
then cast( 1 as int1 )
end
when @cl_demo_sql_chess=>c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then target~x_pos
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then target~x_pos
end
end
, @cl_demo_sql_chess=>c_position_invalid ),
coalesce(
case chessman
when @cl_demo_sql_chess=>c_chessman_king then
case when ( color = @cl_demo_sql_chess=>c_color_white
and source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7
and target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_white and
source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_black and
source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 8 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_black and
source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 8 as int1 )
end
when @cl_demo_sql_chess=>c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then cast( 7 as int1 )
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then cast( 2 as int1 )
end
end
, @cl_demo_sql_chess=>c_position_invalid ),
coalesce(
case chessman
when @cl_demo_sql_chess=>c_chessman_king then
case when ( color = @cl_demo_sql_chess=>c_color_white and
source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 6 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_white and
source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 4 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_black and
source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 6 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_black and
source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 4 as int1 )
end
when @cl_demo_sql_chess=>c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then target~x_pos
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then target~x_pos
end
end
, @cl_demo_sql_chess=>c_position_invalid ),
coalesce(
case chessman
when @cl_demo_sql_chess=>c_chessman_king then
case when ( color = @cl_demo_sql_chess=>c_color_white and
source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_white and
source~x_pos = 5
and source~y_pos = 1 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 1 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_black and
source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 7 and
target~y_pos = 1 )
then cast( 8 as int1 )
when ( color = @cl_demo_sql_chess=>c_color_black and
source~x_pos = 5
and source~y_pos = 8 and target~x_pos = 3 and
target~y_pos = 1 )
then cast( 8 as int1 )
end
when @cl_demo_sql_chess=>c_chessman_pawn then
case when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_white and
source~y_pos = 5 and
target~y_pos = 6 )
then cast( 5 as int1 )
when abs( target~x_pos - source~x_pos ) = 1 and (
color = @cl_demo_sql_chess=>c_color_black and
source~y_pos = 4 and
target~y_pos = 3 )
then cast( 4 as int1 )
end
end
, @cl_demo_sql_chess=>c_position_invalid ),
case chessman
when @cl_demo_sql_chess=>c_chessman_pawn then
case when ( target~y_pos = 1 and
color = @cl_demo_sql_chess=>c_color_black ) or
( target~y_pos = 8 and
color = @cl_demo_sql_chess=>c_color_white )
then @abap_true
else @abap_false
end
else @abap_false
end
),
+positions as ( select from demo_chess_pos
fields gameuuid, movecnt, chessman,
color, x, y, before_x, before_y,
case when before_x = x and before_y = y then @abap_false
when before_x < 1 or before_x > 8 then @abap_false
when before_y < 1 or before_y > 8 then @abap_false
else @abap_true end as moved_flag ),
+stage1 as ( select from ( demo_chess_game as game
inner join +positions as source on game~gameuuid = source~gameuuid )
inner join +moves as moves on source~color = moves~color and
source~chessman = moves~chessman and
source~x = moves~from_x and
source~y = moves~from_y
left outer join +positions as target_field
on target_field~gameuuid = source~gameuuid and
target_field~movecnt = source~movecnt and
target_field~x = moves~to_x and
target_field~y = moves~to_y
left outer join +positions as last_move
on last_move~gameuuid = source~gameuuid and
last_move~movecnt = source~movecnt and
last_move~moved_flag = @abap_true
fields source~gameuuid, source~movecnt, source~color, source~chessman,
source~x as from_x,
source~y as from_y, moves~to_x, moves~to_y,
moves~special_flag, moves~color_dep,
moves~chessman_dep, moves~from_x_dep, moves~from_y_dep,
moves~to_x_dep, moves~to_y_dep,
moves~transform_pawn_flag,
case when game~castling_black_long = @abap_true then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_black and
source~chessman = @cl_demo_sql_chess=>c_chessman_king then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_black and
source~chessman = @cl_demo_sql_chess=>c_chessman_rook and
source~x = 1 and
source~y = 8 then @abap_true
end as castling_black_long,
case when game~castling_black_short = @abap_true then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_black and
source~chessman = @cl_demo_sql_chess=>c_chessman_king then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_black and
source~chessman = @cl_demo_sql_chess=>c_chessman_rook and
source~x = 8 and
source~y = 8 then @abap_true end as castling_black_short,
case when game~castling_white_long = @abap_true then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_white and
source~chessman = @cl_demo_sql_chess=>c_chessman_king then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_white and
source~chessman = @cl_demo_sql_chess=>c_chessman_rook and
source~x = 1 and source~y = 1 then @abap_true end as castling_white_long,
case when game~castling_white_short = @abap_true then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_white and
source~chessman = @cl_demo_sql_chess=>c_chessman_king then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_white and
source~chessman = @cl_demo_sql_chess=>c_chessman_rook and
source~x = 8 and
source~y = 1 then @abap_true end as castling_white_short,
last_move~chessman as last_chessman,
last_move~color as last_color,
last_move~before_x as last_from_x,
last_move~before_y as last_from_y,
last_move~x as last_to_x,
last_move~y as last_to_y
where source~color = game~color and
( target_field~color is null or
target_field~color <> source~color ) and
( moves~special_flag = @abap_false or
moves~special_flag = 'P' or
moves~special_flag = 'E' ) and
(
moves~special_flag <> 'P' or
target_field~color is not null or
(
last_move~chessman = moves~chessman_dep and
last_move~color = moves~color_dep and
last_move~before_x = moves~from_x_dep and
last_move~before_y = moves~from_y_dep and
last_move~x = moves~to_x_dep and
last_move~y = moves~to_y_dep
)
)
and
(
moves~special_flag <> 'E' or
target_field~color is null
)
),
+stage2 as ( select from +stage1 as moves
inner join +positions as position
on position~gameuuid = moves~gameuuid and
position~movecnt = moves~movecnt
fields moves~gameuuid, moves~movecnt, moves~color, moves~chessman,
moves~from_x, moves~from_y, moves~to_x, moves~to_y,
position~color as other_color, position~x as other_x,
position~y as other_y, moves~special_flag,
moves~color_dep, moves~chessman_dep, moves~from_x_dep,
moves~from_y_dep, moves~to_x_dep, moves~to_y_dep,
moves~transform_pawn_flag,
( moves~to_x - moves~from_x ) * ( position~y - moves~from_y ) as first_slope,
( position~x - moves~from_x ) * ( moves~to_y - moves~from_y ) as second_slope,
case when moves~from_x < position~x then -1
when moves~from_x = position~x then 0
else 1
end as cmp_1x,
case when moves~to_x < position~x then -1
when moves~to_x = position~x then 0
else 1
end as cmp_2x,
case when moves~from_y < position~y then -1
when moves~from_y = position~y then 0
else 1
end as cmp_1y,
case when moves~to_y < position~y then -1
when moves~to_y = position~y then 0
else 1 end as cmp_2y
where not ( position~chessman = moves~chessman and
position~color = moves~color and
position~x = moves~from_x and
position~y = moves~from_y )
),
+stage3 as ( select from +stage2 as source
fields source~gameuuid, source~movecnt, source~color,
source~chessman, source~from_x,
source~from_y, source~to_x, source~to_y,
source~other_color, source~other_x,
source~other_y, source~special_flag,
source~color_dep, source~chessman_dep,
source~from_x_dep, source~from_y_dep,
source~to_x_dep, source~to_y_dep,
source~transform_pawn_flag,
source~first_slope, source~second_slope,
abs( source~cmp_1x + source~cmp_2x ) as first_distance,
abs( source~cmp_1y + source~cmp_2y ) as second_distance ),
+stage4 as ( select from +stage3 as source
fields source~gameuuid, source~movecnt, source~color, source~chessman,
source~from_x, source~from_y, source~to_x, source~to_y
where source~chessman <> @cl_demo_sql_chess=>c_chessman_knight and
source~first_slope = source~second_slope and
source~first_distance <= 1 and
source~second_distance <= 1 and not
(
source~color <> source~other_color and
source~to_x = source~other_x and
source~to_y = source~other_y
)
),
+stage5 as ( select from +stage1 as allowed left
outer join +stage4 as forbidden
on allowed~gameuuid = forbidden~gameuuid and
allowed~movecnt = forbidden~movecnt and
allowed~color = forbidden~color and
allowed~chessman = forbidden~chessman and
allowed~from_x = forbidden~from_x and
allowed~from_y = forbidden~from_y and
allowed~to_x = forbidden~to_x and
allowed~to_y = forbidden~to_y
fields allowed~*
where forbidden~gameuuid is null
),
+cstl_bl as ( select from demo_chess_game as game
left outer join +positions as a8
on game~gameuuid = a8~gameuuid and
game~movecnt = a8~movecnt and
a8~x = 1 and
a8~y = 8
left outer join +positions as b8
on game~gameuuid = b8~gameuuid and
game~movecnt = b8~movecnt and
b8~x = 2 and
b8~y = 8
left outer join +positions as c8
on game~gameuuid = c8~gameuuid and
game~movecnt = c8~movecnt and
c8~x = 3 and
c8~y = 8
left outer join +positions as d8
on game~gameuuid = d8~gameuuid and
game~movecnt = d8~movecnt and
d8~x = 4 and
d8~y = 8
left outer join +positions as e8
on game~gameuuid = e8~gameuuid and
game~movecnt = e8~movecnt and
e8~x = 5 and
e8~y = 8
fields game~gameuuid, game~movecnt,
@cl_demo_sql_chess=>c_color_black as color,
@cl_demo_sql_chess=>c_chessman_king as chessman,
5 as from_x, 8 as from_y,
3 as to_x, 8 as to_y, 'C' as special_flag,
@cl_demo_sql_chess=>c_color_black as color_dep,
@cl_demo_sql_chess=>c_chessman_rook as chessman_dep,
1 as from_x_dep,
8 as from_y_dep,
4 as to_x_dep, 8 as to_y_dep,
@abap_false as transform_pawn_flag,
@abap_true as castling_black_long,
@abap_true as castling_black_short,
game~castling_white_long, game~castling_white_short
where game~castling_black_long = @abap_false and
e8~color = @cl_demo_sql_chess=>c_color_black and
e8~chessman = @cl_demo_sql_chess=>c_chessman_king and
b8~color is null and
c8~color is null and
d8~color is null and
a8~color = @cl_demo_sql_chess=>c_color_black and
a8~chessman = @cl_demo_sql_chess=>c_chessman_rook
),
+cstl_bs as ( select from demo_chess_game as game
left outer join +positions as e8
on game~gameuuid = e8~gameuuid and
game~movecnt = e8~movecnt and
e8~x = 5 and
e8~y = 8
left outer join +positions as f8
on game~gameuuid = f8~gameuuid and
game~movecnt = f8~movecnt and
f8~x = 6 and
f8~y = 8
left outer join +positions as g8
on game~gameuuid = g8~gameuuid and
game~movecnt = g8~movecnt and
g8~x = 7 and
g8~y = 8
left outer join +positions as h8
on game~gameuuid = h8~gameuuid and
game~movecnt = h8~movecnt and
h8~x = 8 and
h8~y = 8
fields game~gameuuid, game~movecnt,
@cl_demo_sql_chess=>c_color_black as color,
@cl_demo_sql_chess=>c_chessman_king as chessman,
5 as from_x, 8 as from_y,
7 as to_x, 8 as to_y, 'C' as special_flag,
@cl_demo_sql_chess=>c_color_black as color_dep,
@cl_demo_sql_chess=>c_chessman_rook as chessman_dep,
8 as from_x_dep,
8 as from_y_dep,
6 as to_x_dep, 8 as to_y_dep,
@abap_false as transform_pawn_flag,
@abap_true as castling_black_long,
@abap_true as castling_black_short,
game~castling_white_long, game~castling_white_short
where game~castling_black_short = @abap_false and
e8~color = @cl_demo_sql_chess=>c_color_black and
e8~chessman = @cl_demo_sql_chess=>c_chessman_king and
f8~color is null and
g8~color is null and
h8~color = @cl_demo_sql_chess=>c_color_black and
h8~chessman = @cl_demo_sql_chess=>c_chessman_rook
),
+cstl_wl as ( select from demo_chess_game as game
left outer join +positions as a1
on game~gameuuid = a1~gameuuid and
game~movecnt = a1~movecnt and
a1~x = 1 and
a1~y = 1
left outer join +positions as b1
on game~gameuuid = b1~gameuuid and
game~movecnt = b1~movecnt and
b1~x = 2 and
b1~y = 1
left outer join +positions as c1
on game~gameuuid = c1~gameuuid and
game~movecnt = c1~movecnt and
c1~x = 3 and
c1~y = 1
left outer join +positions as d1
on game~gameuuid = d1~gameuuid and
game~movecnt = d1~movecnt and
d1~x = 4 and
d1~y = 1
left outer join +positions as e1
on game~gameuuid = e1~gameuuid and
game~movecnt = e1~movecnt and
e1~x = 5 and
e1~y = 1
fields game~gameuuid, game~movecnt,
@cl_demo_sql_chess=>c_color_white as color,
@cl_demo_sql_chess=>c_chessman_king as chessman,
5 as from_x, 1 as from_y,
3 as to_x, 1 as to_y, 'C' as special_flag,
@cl_demo_sql_chess=>c_color_white as color_dep,
@cl_demo_sql_chess=>c_chessman_rook as chessman_dep,
1 as from_x_dep, 1 as from_y_dep, 4 as to_x_dep, 1 as to_y_dep,
@abap_false as transform_pawn_flag, game~castling_black_long,
game~castling_black_short,
@abap_true as castling_white_long,
@abap_true as castling_white_short
where game~castling_white_long = @abap_false and
e1~color = @cl_demo_sql_chess=>c_color_white and
e1~chessman = @cl_demo_sql_chess=>c_chessman_king and
b1~color is null and
c1~color is null and
d1~color is null and
a1~color = @cl_demo_sql_chess=>c_color_white and
a1~chessman = @cl_demo_sql_chess=>c_chessman_rook
),
+cstl_ws as ( select from demo_chess_game as game
left outer join demo_chess_v_pos_cds as e1
on game~gameuuid = e1~gameuuid and
game~movecnt = e1~movecnt and
e1~x = 5 and
e1~y = 1
left outer join demo_chess_v_pos_cds as f1
on game~gameuuid = f1~gameuuid and
game~movecnt = f1~movecnt and
f1~x = 6 and
f1~y = 1
left outer join demo_chess_v_pos_cds as g1
on game~gameuuid = g1~gameuuid and
game~movecnt = g1~movecnt and
g1~x = 7 and
g1~y = 1
left outer join demo_chess_v_pos_cds as h1
on game~gameuuid = h1~gameuuid and
game~movecnt = h1~movecnt and
h1~x = 8 and
h1~y = 1
fields game~gameuuid, game~movecnt, @cl_demo_sql_chess=>c_color_white as color,
@cl_demo_sql_chess=>c_chessman_king as chessman, 5 as from_x, 1 as from_y,
7 as to_x, 1 as to_y, 'C' as special_flag,
@cl_demo_sql_chess=>c_color_white as color_dep,
@cl_demo_sql_chess=>c_chessman_rook as chessman_dep,
8 as from_x_dep, 1 as from_y_dep, 6 as to_x_dep, 1 as to_y_dep,
@abap_false as transform_pawn_flag, game~castling_black_long,
game~castling_black_short,
@abap_true as castling_white_long,
@abap_true as castling_white_short
where game~castling_white_short = @abap_false and
e1~color = @cl_demo_sql_chess=>c_color_white and
e1~chessman = @cl_demo_sql_chess=>c_chessman_king and
f1~color is null and
g1~color is null and
h1~color = @cl_demo_sql_chess=>c_color_white and
h1~chessman = @cl_demo_sql_chess=>c_chessman_rook
),
+stage6 as ( select from +stage5 as allowed
fields allowed~gameuuid, allowed~movecnt, allowed~color, allowed~chessman,
allowed~from_x, allowed~from_y, allowed~to_x, allowed~to_y,
allowed~special_flag,
case when allowed~color_dep <> '-'
then allowed~color_dep end as color_dep,
case when chessman_dep <> '-'
then chessman_dep end as chessman_dep,
allowed~from_x_dep, allowed~from_y_dep, allowed~to_x_dep,
allowed~to_y_dep,
allowed~transform_pawn_flag, allowed~castling_black_long,
allowed~castling_black_short,
allowed~castling_white_long, allowed~castling_white_short,
@abap_false as is_castling_black_long,
@abap_false as is_castling_black_short,
@abap_false as is_castling_white_long,
@abap_false as is_castling_white_short
union select from +cstl_bl fields
gameuuid, movecnt, color, chessman,
from_x, from_y, to_x, to_y, special_flag,
color_dep, chessman_dep, from_x_dep, from_y_dep,
to_x_dep, to_y_dep, transform_pawn_flag, castling_black_long,
castling_black_short, castling_white_long,
castling_white_short,
@abap_true as is_castling_black_long,
@abap_false as is_castling_black_short,
@abap_false as is_castling_white_long,
@abap_false as is_castling_white_short
union select from +cstl_bs fields
gameuuid, movecnt, color, chessman,
from_x, from_y, to_x, to_y, special_flag,
color_dep, chessman_dep, from_x_dep, from_y_dep,
to_x_dep, to_y_dep, transform_pawn_flag, castling_black_long,
castling_black_short, castling_white_long,
castling_white_short,
@abap_false as is_castling_black_long,
@abap_true as is_castling_black_short,
@abap_false as is_castling_white_long,
@abap_false as is_castling_white_short
union select from +cstl_wl fields
gameuuid, movecnt, color, chessman,
from_x, from_y, to_x, to_y, special_flag,
color_dep, chessman_dep, from_x_dep, from_y_dep,
to_x_dep, to_y_dep, transform_pawn_flag, castling_black_long,
castling_black_short, castling_white_long,
castling_white_short,
@abap_false as is_castling_black_long,
@abap_false as is_castling_black_short,
@abap_true as is_castling_white_long,
@abap_false as is_castling_white_short
union select from +cstl_ws fields
gameuuid, movecnt, color, chessman,
from_x, from_y, to_x, to_y, special_flag,
color_dep, chessman_dep, from_x_dep, from_y_dep,
to_x_dep, to_y_dep, transform_pawn_flag, castling_black_long,
castling_black_short, castling_white_long,
castling_white_short,
@abap_false as is_castling_black_long,
@abap_false as is_castling_black_short,
@abap_false as is_castling_white_long,
@abap_true as is_castling_white_short
),
+stage7 as ( select from +stage6 as moves
inner join +positions as pos
on moves~gameuuid = pos~gameuuid and
moves~movecnt = pos~movecnt
fields moves~gameuuid, moves~movecnt, moves~color as move_color,
moves~chessman as move_chessman,
moves~from_x as move_from_x, moves~from_y as move_from_y,
moves~to_x as move_to_x,
moves~to_y as move_to_y,
case when moves~transform_pawn_flag = @abap_true and
moves~chessman = pos~chessman and
moves~color = pos~color and
moves~from_x = pos~x and
moves~from_y = pos~y
then 'Q'
else pos~chessman end as chessman,
pos~color,
case when moves~chessman = pos~chessman and
moves~color = pos~color and
moves~from_x = pos~x and
moves~from_y = pos~y
then moves~to_x
when moves~special_flag = 'C' and
moves~chessman_dep = pos~chessman and
moves~color_dep = pos~color and
moves~from_x_dep = pos~x and
moves~from_y_dep = pos~y
then moves~to_x_dep
else pos~x end as x,
case when moves~chessman = pos~chessman and
moves~color = pos~color and
moves~from_x = pos~x and
moves~from_y = pos~y
then moves~to_y
when moves~special_flag = 'C' and
moves~chessman_dep = pos~chessman and
moves~color_dep = pos~color and
moves~from_x_dep = pos~x and
moves~from_y_dep = pos~y
then moves~to_y_dep
else pos~y end as y,
pos~x as before_x,
pos~y as before_y,
case when moves~chessman = pos~chessman and
moves~color = pos~color and
moves~from_x = pos~x and
moves~from_y = pos~y
then @abap_true
when moves~special_flag = 'C' and
moves~chessman_dep = pos~chessman and
moves~color_dep = pos~color and
moves~from_x_dep = pos~x and
moves~from_y_dep = pos~y
then @abap_true
else @abap_false end as moved_flag,
moves~castling_black_long,
moves~castling_black_short,
moves~castling_white_long,
moves~castling_white_short,
moves~is_castling_black_long,
moves~is_castling_black_short,
moves~is_castling_white_long,
moves~is_castling_white_short
where ( not
( pos~x = moves~to_x and pos~y = moves~to_y ) ) and ( not
( moves~special_flag = 'P' and
moves~color_dep = pos~color and
moves~chessman_dep = pos~chessman and
moves~to_x_dep = pos~x and
moves~to_y_dep = pos~y ) )
),
+stage8 as ( select from demo_chess_game as game inner join +stage7 as source
on game~gameuuid = source~gameuuid
inner join +stage7 as kings_pos
on kings_pos~gameuuid = source~gameuuid and
kings_pos~movecnt = source~movecnt and
kings_pos~move_color = source~move_color and
kings_pos~move_chessman = source~move_chessman and
kings_pos~move_from_x = source~move_from_x and
kings_pos~move_from_y = source~move_from_y and
kings_pos~move_to_x = source~move_to_x and
kings_pos~move_to_y = source~move_to_y and
kings_pos~color = source~move_color and
kings_pos~chessman = @cl_demo_sql_chess=>c_chessman_king
inner join +moves as moves on
source~color = moves~color and
source~chessman = moves~chessman and
source~x = moves~from_x and
source~y = moves~from_y
left outer join +stage7 as target_field on
target_field~gameuuid = source~gameuuid and
target_field~movecnt = source~movecnt and
target_field~move_color = source~move_color and
target_field~move_chessman = source~move_chessman and
target_field~move_from_x = source~move_from_x and
target_field~move_from_y = source~move_from_y and
target_field~move_to_x = source~move_to_x and
target_field~move_to_y = source~move_to_y and
target_field~x = moves~to_x and
target_field~y = moves~to_y
left outer join +stage7 as last_move
on last_move~gameuuid = source~gameuuid and
last_move~movecnt = source~movecnt and
last_move~move_color = source~move_color and
last_move~move_chessman = source~move_chessman and
last_move~move_from_x = source~move_from_x and
last_move~move_from_y = source~move_from_y and
last_move~move_to_x = source~move_to_x and
last_move~move_to_y = source~move_to_y and
last_move~moved_flag = @abap_true
fields source~gameuuid, source~movecnt, source~move_color,
source~move_chessman, source~move_from_x,
source~move_from_y, source~move_to_x, source~move_to_y,
source~color, source~chessman,
source~x as from_x, source~y as from_y, moves~to_x,
moves~to_y, moves~special_flag,
moves~color_dep, moves~chessman_dep, moves~from_x_dep,
moves~from_y_dep, moves~to_x_dep,
moves~to_y_dep, moves~transform_pawn_flag,
case when source~castling_black_long = @abap_true then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_black and
source~chessman = @cl_demo_sql_chess=>c_chessman_king
then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_black and
source~chessman = @cl_demo_sql_chess=>c_chessman_rook and
source~x = 1 and
source~y = 8
then @abap_true
else @abap_false end as castling_black_long,
case when source~castling_black_short = @abap_true then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_black and
source~chessman = @cl_demo_sql_chess=>c_chessman_king then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_black and
source~chessman = @cl_demo_sql_chess=>c_chessman_rook and
source~x = 8 and
source~y = 8 then @abap_true
else @abap_false
end as castling_black_short,
case when source~castling_white_long = @abap_true then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_white and
source~chessman = @cl_demo_sql_chess=>c_chessman_king then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_white and
source~chessman = @cl_demo_sql_chess=>c_chessman_rook and
source~x = 1 and
source~y = 1 then @abap_true
else @abap_false end as castling_white_long,
case when source~castling_white_short = @abap_true then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_white and
source~chessman = @cl_demo_sql_chess=>c_chessman_king then @abap_true
when source~color = @cl_demo_sql_chess=>c_color_white and
source~chessman = @cl_demo_sql_chess=>c_chessman_rook and
source~x = 8 and
source~y = 1 then @abap_true
else @abap_false end as castling_white_short,
last_move~chessman as last_chessman,
last_move~color as last_color,
last_move~before_x as last_from_x,
last_move~before_y as last_from_y,
last_move~x as last_to_x,
last_move~y as last_to_y
where
( ( source~color = @cl_demo_sql_chess=>c_color_black and
game~color = @cl_demo_sql_chess=>c_color_white ) or
( source~color = @cl_demo_sql_chess=>c_color_white and
game~color = @cl_demo_sql_chess=>c_color_black ) ) and
( target_field~color is null or target_field~color <> source~color ) and
( moves~special_flag = @abap_false or
moves~special_flag = 'P' or
moves~special_flag = 'E' ) and
(
moves~special_flag <> 'P' or
target_field~color is not null or
(
last_move~chessman = moves~chessman_dep and
last_move~color = moves~color_dep and
last_move~before_x = moves~from_x_dep and
last_move~before_y = moves~from_y_dep and
last_move~x = moves~to_x_dep and
last_move~y = moves~to_y_dep
)
)
and
(
moves~special_flag <> 'E' or
target_field~color is null
) and
( ( moves~to_x = kings_pos~x and
moves~to_y = kings_pos~y ) or
( source~is_castling_black_long = @abap_true and
moves~to_y = 8 and
( moves~to_x = 4 or moves~to_x = 5 )
) or
( source~is_castling_white_long = @abap_true and
moves~to_y = 1 and
( moves~to_x = 4 or moves~to_x = 5 )
) or
( source~is_castling_black_short = @abap_true and
moves~to_y = 8 and
( moves~to_x = 5 or moves~to_x = 6 )
) or
( source~is_castling_white_short = @abap_true and
moves~to_y = 1 and
( moves~to_x = 5 or moves~to_x = 6 )
)
)
),
+stage9 as ( select from +stage8 as moves
inner join +stage7 as position
on position~gameuuid = moves~gameuuid and
position~movecnt = moves~movecnt and
position~move_color = moves~move_color and
position~move_chessman = moves~move_chessman and
position~move_from_x = moves~move_from_x and
position~move_from_y = moves~move_from_y and
position~move_to_x = moves~move_to_x and
position~move_to_y = moves~move_to_y
fields moves~gameuuid, moves~movecnt, moves~move_color,
moves~move_chessman,
moves~move_from_x,
moves~move_from_y, moves~move_to_x, moves~move_to_y,
moves~color, moves~chessman,
moves~from_x, moves~from_y, moves~to_x, moves~to_y,
position~color as other_color,
position~x as other_x, position~y as other_y,
moves~special_flag, moves~color_dep,
moves~chessman_dep, moves~from_x_dep, moves~from_y_dep,
moves~to_x_dep, moves~to_y_dep,
moves~transform_pawn_flag,
( moves~to_x - moves~from_x ) * ( position~y - moves~from_y )
as first_slope,
( position~x - moves~from_x ) * ( moves~to_y - moves~from_y )
as second_slope,
case when moves~from_x < position~x then -1
when moves~from_x = position~x then 0
else 1
end as cmp_1x,
case when moves~to_x < position~x then -1
when moves~to_x = position~x then 0
else 1
end as cmp_2x,
case when moves~from_y < position~y then -1
when moves~from_y = position~y then 0
else 1
end as cmp_1y,
case when moves~to_y < position~y then -1
when moves~to_y = position~y then 0
else 1
end as cmp_2y
where not ( position~chessman = moves~chessman and
position~color = moves~color and
position~x = moves~from_x and
position~y = moves~from_y
)
),
+stage10 as ( select from +stage9 as source
fields gameuuid, movecnt, move_color, move_chessman,
move_from_x, move_from_y, move_to_x,
move_to_y, color, chessman, from_x, from_y, to_x, to_y,
other_color, other_x,
other_y, special_flag, color_dep, chessman_dep,
from_x_dep, from_y_dep, to_x_dep,
to_y_dep, transform_pawn_flag, first_slope, second_slope,
abs( cmp_1x + cmp_2x ) as first_distance,
abs( cmp_1y + cmp_2y ) as second_distance
),
+stage11 as ( select from +stage10 as source
fields gameuuid, movecnt, move_color, move_chessman,
move_from_x, move_from_y, move_to_x,
move_to_y, color, chessman, from_x, from_y, to_x, to_y
where chessman <> @cl_demo_sql_chess=>c_chessman_knight and
first_slope = source~second_slope and
first_distance <= 1 and
second_distance <= 1 and not
(
color <> source~other_color and
to_x = source~other_x and
to_y = source~other_y
)
),
+stage12 as ( select from +stage8 as allowed
left outer join +stage11 as forbidden
on allowed~gameuuid = forbidden~gameuuid and
allowed~movecnt = forbidden~movecnt and
allowed~move_color = forbidden~move_color and
allowed~move_chessman = forbidden~move_chessman and
allowed~move_from_x = forbidden~move_from_x and
allowed~move_from_y = forbidden~move_from_y and
allowed~move_to_x = forbidden~move_to_x and
allowed~move_to_y = forbidden~move_to_y and
allowed~color = forbidden~color and
allowed~chessman = forbidden~chessman and
allowed~from_x = forbidden~from_x and
allowed~from_y = forbidden~from_y and
allowed~to_x = forbidden~to_x and
allowed~to_y = forbidden~to_y
fields allowed~*
where forbidden~gameuuid is null
),
+stage13 as ( select from +stage6 as allowed
left outer join +stage12 as forbidden
on allowed~gameuuid = forbidden~gameuuid and
allowed~movecnt = forbidden~movecnt and
allowed~color = forbidden~move_color and
allowed~chessman = forbidden~move_chessman and
allowed~from_x = forbidden~move_from_x and
allowed~from_y = forbidden~move_from_y and
allowed~to_x = forbidden~move_to_x and
allowed~to_y = forbidden~move_to_y
fields allowed~*
where forbidden~gameuuid is null
),
+next as ( select from +stage13 as moves
inner join +positions as pos
on moves~gameuuid = pos~gameuuid and
moves~movecnt = pos~movecnt
fields moves~gameuuid,
moves~movecnt,
moves~color as move_color,
moves~chessman as move_chessman,
moves~from_x as move_from_x,
moves~from_y as move_from_y,
moves~to_x as move_to_x,
moves~to_y as move_to_y,
case when moves~transform_pawn_flag = 'X' and
moves~chessman = pos~chessman and
moves~color = pos~color and
moves~from_x = pos~x and
moves~from_y = pos~y
then @cl_demo_sql_chess=>c_chessman_queen
else pos~chessman end as chessman,
pos~color,
case when moves~chessman = pos~chessman and
moves~color = pos~color and
moves~from_x = pos~x and
moves~from_y = pos~y
then moves~to_x
when moves~special_flag = 'C' and
moves~chessman_dep = pos~chessman and
moves~color_dep = pos~color and
moves~from_x_dep = pos~x and
moves~from_y_dep = pos~y
then moves~to_x_dep
else pos~x end as x,
case when moves~chessman = pos~chessman and
moves~color = pos~color and
moves~from_x = pos~x and
moves~from_y = pos~y
then moves~to_y
when moves~special_flag = 'C' and
moves~chessman_dep = pos~chessman and
moves~color_dep = pos~color and
moves~from_x_dep = pos~x and
moves~from_y_dep = pos~y
then moves~to_y_dep
else pos~y end as y,
pos~x as before_x,
pos~y as before_y,
case when moves~chessman = pos~chessman and
moves~color = pos~color and
moves~from_x = pos~x and
moves~from_y = pos~y
then 'X'
when moves~special_flag = 'C' and
moves~chessman_dep = pos~chessman and
moves~color_dep = pos~color and
moves~from_x_dep = pos~x and
moves~from_y_dep = pos~y
then 'X'
else ' ' end as moved_flag,
moves~castling_black_long,
moves~castling_black_short,
moves~castling_white_long,
moves~castling_white_short,
moves~is_castling_black_long,
moves~is_castling_black_short,
moves~is_castling_white_long,
moves~is_castling_white_short
where ( not " capture chessman:
( pos~x = moves~to_x and
pos~y = moves~to_y ) ) and ( not
" en passant:
( moves~special_flag = 'P' and
moves~color_dep = pos~color and
moves~chessman_dep = pos~chessman and
moves~to_x_dep = pos~x and
moves~to_y_dep = pos~y ) )
)
select from +next
fields gameuuid, movecnt + 1 as movecnt, chessman, color,
x, y, before_x, before_y, castling_black_long,
castling_black_short, castling_white_long,
castling_white_short
where gameuuid = @uuid and
movecnt = @movecnt and
move_color = @color and
move_chessman = @chessman and
move_from_x = @from_x and
move_from_y = @from_y and
move_to_x = @to_x and
move_to_y = @to_y
into table @data(move_table).Please see the class CL_DEMO_SQL_CHESS where this is exercised with the CDS view version.
As one of the authors of the ABAP SQL compiler I was actually very happy to see our compiler correctly compile this “monster” statement.
So how is this large WITH statement used? Here is a simple example using a complicated chess position:
data positions type table of demo_chess_pos with empty key.
data(uuid) = cl_system_uuid=>create_uuid_c32_static( ).
positions = value #(
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_rook x = 1 y = 1 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_bishop x = 3 y = 1 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_queen x = 4 y = 1 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_rook x = 6 y = 1 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_king x = 7 y = 1 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 1 y = 2 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 2 y = 2 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 3 y = 2 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 6 y = 2 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 7 y = 2 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 8 y = 2 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 4 y = 4 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_knight x = 5 y = 5 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white
chessman = cl_demo_sql_chess=>c_chessman_knight x = 2 y = 5 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_rook x = 1 y = 8 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_queen x = 4 y = 8 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_king x = 5 y = 8 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_bishop x = 6 y = 8 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_rook x = 8 y = 8 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 1 y = 7 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 2 y = 7 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 6 y = 7 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 7 y = 7 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 8 y = 7 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_knight x = 3 y = 6 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_knight x = 6 y = 6 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 4 y = 5 )
( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_black
chessman = cl_demo_sql_chess=>c_chessman_pawn x = 5 y = 4 )
).
insert into demo_chess_game values @(
value #( gameuuid = uuid movecnt = 0 color = cl_demo_sql_chess=>c_color_white )
).
insert demo_chess_pos from table @positions.
data(movecnt) = 0.
data(chessman) = cl_demo_sql_chess=>c_chessman_knight.
data(color) = cl_demo_sql_chess=>c_color_white.
data(from_x) = 5.
data(from_y) = 5.
data(to_x) = 3.
data(to_y) = 6.
<---- WITH STATEMENT COMES HERENote: it is very easy to write a similar WITH statement which generates all possible moves instead.
What is very surprising: on a SAP HANA database this WITH statement runs quite fast. In my system it is subsecond.
