CuU6rruv

1# Tic-Tac-Toe
2
3Wie sieht das Spielfeld aus?
4Wie können wir das Spielfeld in SQL darstellen?
5
6Wie lauten die Regeln?
7=> IF und andere Kontrollstrukturen
8
9Welche Spielzüge sind möglich?
10=> Koordinaten in Tabelle erfassen
11
12Welche Siegbedingungen gibt es?
13
14Wie wird das Spiel gesteuert?
15=> Spieler ruft immer move(x,y); auf
16
17
18
19Spieler gibt seinen Zug an mit => CALL move(x,y);
20Spiel prüft ob Eingabedaten im richtigen bereich liegen: ist x 1-3 und y A-C
21Spiel prüft ob benötigte Tabellen im RAM schon vorhanden sind und legt diese wenn nötig an.
22Spiel prüft ob in den Tabellen Daten drin sind. wenn nicht werden sie befüllt.
23Spiel prüft ob der Spielzug gültig ist, resp. noch in der Liste mit möglichen Spielzügen vorhanden ist.
24Spielzug wird aus Liste mit allen möglichen Spielzügen entfernt
25Spielbrett wird aktualisiert
26Siegbedingung für Spieler wird geprüft => SIEG?
27Spiel wählt zufällig einen möglichen Spielzug aus
28Spielzug wird aus Liste mit allen möglichen Spielzügen entfernt
29Spielbrett wird aktualisiert
30Siegbedingung für Spiel wird geprüft => SIEG?
31Sind alle Züge gespielt und immer noch kein Sieger? => Unentschieden!
32Spielbrett wird angezeigt
33
34--------------------------------
35
36DROP PROCEDURE IF EXISTS `move`;
37DELIMITER //
38CREATE PROCEDURE move(x INT UNSIGNED, y CHAR(1))
39BEGIN
40  IF x NOT IN (1,2,3) THEN
41    SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'x sollte 1,2 oder 3 sein';
42  END IF;
43  IF y NOT IN ('A','B','C') THEN
44    SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'y sollte A,B oder C sein';
45  END IF;
46  CREATE TABLE IF NOT EXISTS tictactoe(`_` CHAR(1), A CHAR(1), B CHAR(1), C CHAR(1))ENGINE=MEMORY;
47  CREATE TABLE IF NOT EXISTS moves (a char(1), b char(1))ENGINE=MEMORY;
48  IF (SELECT COUNT(*) FROM moves) < 1
49  THEN
50    INSERT INTO moves VALUES ('1','A'),('1','B'),('1','C'),('2','A'),('2','B'),('2','C'),('3','A'),('3','B'),('3','C');
51  END IF;
52  IF (SELECT COUNT(*) FROM tictactoe) < 1
53  THEN
54    INSERT INTO tictactoe VALUES ('1','','',''),('2','','',''),('3','','','');
55  END IF;
56
57  SET @s = CONCAT("SELECT COUNT(*) INTO @p FROM moves WHERE a = ",x ," AND b = '",y ,"'");
58  PREPARE stmt FROM @s;
59  EXECUTE stmt;
60  IF @p != 1
61  THEN
62    SELECT * FROM tictactoe ORDER BY `_`;
63    SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'ILLEGAL MOVE!!! TRY AGAIN!!!';
64  END IF;
65  SET @s = CONCAT("DELETE FROM moves WHERE a = '", x, "' AND b = '", y, "'");
66  PREPARE stmt FROM @s;
67  EXECUTE stmt;
68  SET @s = CONCAT("UPDATE tictactoe SET ", y, " = ", "'X'", " WHERE _ = '", x, "'");
69  PREPARE stmt FROM @s;
70  EXECUTE stmt;
71  IF (SELECT COUNT(*) FROM tictactoe WHERE _ = 1 AND A = 'X' AND B = 'X' AND C = 'X')
72  OR (SELECT COUNT(*) FROM tictactoe WHERE _ = 2 AND A = 'X' AND B = 'X' AND C = 'X')
73  OR (SELECT COUNT(*) FROM tictactoe WHERE _ = 3 AND A = 'X' AND B = 'X' AND C = 'X')
74  OR (SELECT anzahl FROM (SELECT COUNT(*) AS anzahl FROM tictactoe WHERE A = 'X') a WHERE anzahl = 3)
75  OR (SELECT anzahl FROM (SELECT COUNT(*) AS anzahl FROM tictactoe WHERE B = 'X') a WHERE anzahl = 3)
76  OR (SELECT anzahl FROM (SELECT COUNT(*) AS anzahl FROM tictactoe WHERE C = 'X') a WHERE anzahl = 3)
77  OR ((SELECT CHAR_LENGTH(CONCAT(A,B,C)) FROM (SELECT A FROM tictactoe WHERE _ = 1 AND A = 'X') a, (SELECT B FROM tictactoe WHERE _ = 2 AND B = 'X') b, (SELECT C FROM tictactoe WHERE _ = 3 AND C = 'X') c) = 3)
78  OR ((SELECT CHAR_LENGTH(CONCAT(A,B,C)) FROM (SELECT A FROM tictactoe WHERE _ = 3 AND A = 'X') a, (SELECT B FROM tictactoe WHERE _ = 2 AND B = 'X') b, (SELECT C FROM tictactoe WHERE _ = 1 AND C = 'X') c) = 3)
79  THEN
80    SELECT * FROM tictactoe ORDER BY `_`;
81    DROP TABLE IF EXISTS tictactoe;
82    DROP TABLE IF EXISTS moves;
83    SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'YOU WIN!!!';
84  END IF;
85  SET @s = CONCAT("SELECT a, b INTO @i, @j FROM moves ORDER BY RAND() LIMIT 1");
86  PREPARE stmt FROM @s;
87  EXECUTE stmt;
88  SET @s = CONCAT("DELETE FROM moves WHERE a = '", @i, "' AND b = '", @j, "'");
89  PREPARE stmt FROM @s;
90  EXECUTE stmt;
91  SET @s = CONCAT("UPDATE tictactoe SET ", @j, " = ", "'O'", " WHERE _ = '", @i, "'");
92  PREPARE stmt FROM @s;
93  EXECUTE stmt;
94  IF (SELECT COUNT(*) FROM tictactoe WHERE _ = 1 AND A = 'O' AND B = 'O' AND C = 'O')
95  OR (SELECT COUNT(*) FROM tictactoe WHERE _ = 2 AND A = 'O' AND B = 'O' AND C = 'O')
96  OR (SELECT COUNT(*) FROM tictactoe WHERE _ = 3 AND A = 'O' AND B = 'O' AND C = 'O')
97  OR (SELECT anzahl FROM (SELECT COUNT(*) AS anzahl FROM tictactoe WHERE A = 'O') a WHERE anzahl = 3)
98  OR (SELECT anzahl FROM (SELECT COUNT(*) AS anzahl FROM tictactoe WHERE B = 'O') a WHERE anzahl = 3)
99  OR (SELECT anzahl FROM (SELECT COUNT(*) AS anzahl FROM tictactoe WHERE C = 'O') a WHERE anzahl = 3)
100  OR ((SELECT CHAR_LENGTH(CONCAT(A,B,C)) FROM (SELECT A FROM tictactoe WHERE _ = 1 AND A = 'O') a, (SELECT B FROM tictactoe WHERE _ = 2 AND B = 'O') b, (SELECT C FROM tictactoe WHERE _ = 3 AND C = 'O') c) = 3)
101  OR ((SELECT CHAR_LENGTH(CONCAT(A,B,C)) FROM (SELECT A FROM tictactoe WHERE _ = 3 AND A = 'O') a, (SELECT B FROM tictactoe WHERE _ = 2 AND B = 'O') b, (SELECT C FROM tictactoe WHERE _ = 1 AND C = 'O') c) = 3)
102  THEN
103    SELECT * FROM tictactoe ORDER BY `_`;
104    DROP TABLE IF EXISTS tictactoe;
105    DROP TABLE IF EXISTS moves;
106    SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'GAME OVER!!! I WIN!!!';
107  END IF;
108  IF (SELECT COUNT(*) FROM moves) < 1
109  THEN
110    SELECT * FROM tictactoe ORDER BY `_`;
111    DROP TABLE IF EXISTS tictactoe;
112    DROP TABLE IF EXISTS moves;
113    SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = "GAME OVER!!! IT's A TIE!!!";
114  END IF;  
115  SELECT * FROM tictactoe ORDER BY `_`;
116END
117//
118DELIMITER ;