MASTERMIND SUR SQL SERVER

---

Commentaire sur cette source (1)
Ajouter un commentaire et/ou une note

Jeu du Mastermind sur SQL server 2000 -2005.
J'ai trouvé ces sources sur Internet.
Je me permet de les poster ici car j'ai trouvé celles-ci originales ...


But du jeu

Le Jeu de Master Mind se joue à deux.
Chaque Joueur écrit un nombre « secret » à 4 chiffres.
Le premier joueur qui trouve le code de l’autre a gagner.

Comment Jouer ?
Voici un exemple :
Disons que le code du joueur 2 est 0105
Et le premier essai du joueur 1 est 2005.
Le joueur 2 prévient que joueur 1 a trouvé deux chiffres bien placés et 1 pas bien placé.
Après c’est au joueur 2 d’essayer de trouvé le code du joueur 1 et ainsi de suite.
Aprés avoir créé les tables ,procedures et fonctions et que la table
masterMindNumbers table créées , le jeu peut commencer.

Le jeu commence en exécutant:
==========================
exec startMasterMindGame
==========================
Game id will be displayed, e.g.
=================================
Game ID
-----------
1
=================================
Human player guess always first executing:
==========================================
exec MasterMindGameTurn <game id>, <code>
==========================================
Example:
==========================================
exec MasterMindGameTurn 1, '1111'
==========================================
The result of human player guess is displayed and also computer player guess.
Example:
==========================================
In place correct digits
-------- --------------
0 0
guess
-----
6003
==========================================
Now, thehuman player should inform the computer player about correct digits and digits in place. At the same time human player tries next code. Let's say human player secret code was 1108. Then the next move would be:
==========================================
exec MasterMindGameTurn 1, '2345', 1, 1
==========================================
First time masterMindGameTurn is executed there wasn't previous computer player guess so last two parameters were left null.
The output was this time:
=========================================
In place correct digits
-------- --------------
2 2
guess
-----
8253
=========================================
I'll post next moves and outputs without comments:
=========================================
exec MasterMindGameTurn 1, '2311', 0, 1

In place correct digits
-------- --------------
2 2
guess
-----
5709
exec MasterMindGameTurn 1, '2367', 1, 1
In place correct digits
-------- --------------
2 2
guess
-----
6129
exec MasterMindGameTurn 1, '6378', 1, 1
In place correct digits
-------- --------------
1 1
guess
-----
6788
exec MasterMindGameTurn 1, '2309', 1, 1
In place correct digits
-------- --------------
3 3
guess
-----
1108
exec MasterMindGameTurn 1, '2300', 4, 4
*** You lost! Better luck next time! *** computer player secret number
-----------------------------
2300
=============================================

Instructions de paramétrage

S’assurer que résultat en texte soit parameter dans l’analyseur de requête avant de commencer le jeu.

create table masterMindNumbers(
number char(4) not null
)
go
insert into masterMindNumbers
select a.digit+b.digit+c.digit+d.digit from (
select '0' as digit union all
select '1' union all
select '2' union all
select '3' union all
select '4' union all
select '5' union all
select '6' union all
select '7' union all
select '8' union all
select '9'
) as a
cross join (
select '0' as digit union all
select '1' union all
select '2' union all
select '3' union all
select '4' union all
select '5' union all
select '6' union all
select '7' union all
select '8' union all
select '9'
) as b
cross join (
select '0' as digit union all
select '1' union all
select '2' union all
select '3' union all
select '4' union all
select '5' union all
select '6' union all
select '7' union all
select '8' union all
select '9'
) as c
cross join (
select '0' as digit union all
select '1' union all
select '2' union all
select '3' union all
select '4' union all
select '5' union all
select '6' union all
select '7' union all
select '8' union all
select '9'
) as d
go
create index idx_masterMindNumbers on masterMindNumbers(number)
go
--drop table masterMindGame
go
create table masterMindGame(
gameID int identity(1,1) primary key clustered,
SecretNumber char(4) not null,
status tinyInt default(0) --0 in progress, 1 - human player won, 2 - human player cheated, 3 - human player lost
)
go
create table possibleAnswers(
gameID int not null,
number char(4) not null,
constraint pk_possibleAnswers primary key clustered(gameID, number)
)
go
create procedure startMasterMindGame as begin
set noCount on
declare @gameID int
insert into masterMindGame(secretNumber) 
select number
from masterMindNumbers
where cast(number as int) = dateDiff(ms, dateAdd(day, dateDiff(day, 0, getDate()), 0), getDate())%10000
select @gameID = scope_identity()
insert into possibleAnswers(GameID, number)
select @gameID, number
from masterMindNumbers
select @gameID as "Game ID"
end
go
CREATE FUNCTION dbo.rightDigits(@guess char(4), @secretNumber char(4))
RETURNS tinyInt
AS
BEGIN
declare @rightDigits tinyint
declare @i tinyInt
set @rightDigits = 0
set @i = 1
while @i <= 4 begin
if @secretNumber like '%'+substring(@guess, @i, 1) + '%' begin
set @rightDigits = @rightDigits + 1
set @secretNumber = substring(@secretNumber, 1, charindex(substring(@guess, @i, 1), @secretNumber)-1) 
+ 'x' 
+ substring(@secretNumber, charindex(substring(@guess, @i, 1), @secretNumber)+1, 4) 
end --if
set @i = @i+1
end
RETURN @rightDigits
END
GO
CREATE FUNCTION dbo.DigitsInPlace(@guess char(4), @secretNumber char(4))
RETURNS tinyInt
AS
BEGIN
declare @InPlace tinyint
declare @i tinyInt
set @InPlace = 0
set @i = 1
while @i <= 4 begin
if substring(@guess, @i, 1) = substring(@secretNumber, @i, 1) set @InPlace = @InPlace + 1
set @i = @i+1
end
RETURN @inPlace
END
GO
create table computerPlayerGuess(
computerPlayerGuessID int identity(1,1) primary key clustered,
gameID int not null,
guess char(4) not null
)
go
create procedure MasterMindGameTurn 
@gameID int, 
@guess char(4), --human player guess
@answerDigitsInPlace tinyint = null, --previous computer player guess digits in place
@answerRightDigits tinyint = null --previous computer player guess correct digits
as begin
set noCount on
declare @secretNumber char(4)
declare @i tinyInt
declare @computerGuess char(4)
declare @possibleAnswersCount smallint
declare @digitsInPlace tinyint
declare @rightDigits tinyint
declare @status tinyint
select @status = status from masterMindGame where gameId = @gameID
if @status <> 0 begin
if @status = 1 print '*** Human player already won this game. ***'
if @status = 2 print '*** Human player cheated, computer player won the game. ***'
if @status = 3 print '*** Computer player already won this game ***'
return
end
if @answerDigitsInPlace = 4 begin
print '*** You lost! Better luck next time! ***'
update masterMindGame set status = 3 where gameID = @gameID
select secretNumber as "computer player secret number" from masterMindGame where gameID = @gameID
return
end
select @secretNumber = secretNumber from masterMindGame where gameID = @gameID
select @digitsInPlace = dbo.digitsInPlace(@guess, @secretNumber), 
@rightDigits = dbo.rightDigits(@guess, @secretNumber) 
if @digitsInPlace = 4 begin
print '*** Congratulations! You won! ***'
update masterMindGame set status = 1 where gameID = @gameID
return
end
select @digitsInPlace as "In place", @rightDigits as "correct digits"
set @computerGuess = (select top 1 guess from computerPlayerGuess where gameID = @gameID order by computerPlayerGuessID desc)
delete 
from possibleAnswers
where gameID = @gameID and
(dbo.DigitsInPlace(@computerGuess, number) <> @answerDigitsInPlace or
dbo.rightDigits(@computerGuess, number) <> @answerRightDigits)
select @possibleAnswersCount = count(*) from possibleAnswers where gameID = @gameID --how many possible answers for computer player left
insert into computerPlayerGuess(gameID, guess)
select top 1 @gameID, number
from possibleAnswers
where gameID = @gameID
order by abs(cast(number as int) - dateDiff(ms, dateAdd(day, dateDiff(day, 0, getDate()), 0), getDate())%10000)
if @possibleAnswersCount = 0 begin
print '*** You cheated! Don''t even try that! You lost! ***'
update masterMindGame set status = 2 where gameID = @gameID
select secretNumber as "computer player secret number" from masterMindGame where gameID = @gameID
return
end
if @possibleAnswersCount = 1 begin
print '*** You lost! Better luck next time! ***'
update masterMindGame set status = 3 where gameID = @gameID
select secretNumber as "computer player secret number" from masterMindGame where gameID = @gameID
return
end
-- select @possibleAnswersCount as 'Computer choices for this turn'
select top 1 guess from computerPlayerGuess where gameID = @gameID order by computerPlayerGuessID desc
end
go
/*************** Game example **************************************
exec startMasterMindGame
--game id = 1
--Human player guess, there was not previous guess by computer player, human player secret number = 0911
exec MasterMindGameTurn 1, '1111'
--0 digits in place, 0 correct digits, it means there is no digit 1 in computer secret number
--computer guess is 9183 
--new human player guess is 2345, there were 0 digits in place, 2 digits were correct for previous comuter player guess
exec MasterMindGameTurn 1, '2345', 0, 2 
--in place = 0, correct = 1, computer guess = 6718
--human player guess = 6711, computer guess 6718 has 1 digit in place and 1 correct compared to human player secret number 0911
exec MasterMindGameTurn 1, '6711', 1, 1
--results: in place = 0, corect = 1, computer tries 5038
exec MasterMindGameTurn 1, '8171', 0, 1
--in place = 1, correct = 1, guess = 6329
exec MasterMindGameTurn 1, '9011', 0, 1
--in place = 0, correct = 1, guess = 4915
exec MasterMindGameTurn 1, '2975', 2, 2
--in place = 0, correct = 1, guess = 0911
exec masterMindGameTurn 1, '6328', 0, 0
--in place = 0, correct = 2
--Human player cheated
--Computer secret number was 8560
*/

• SCHEMA DE PROCEDURE PERMETTANT D'EFFACER LES DOUBLONS SUR PLUSIEURS CHAMPS D'UNE TABLE SQL SERVER
• INSERTION CODE POSTAL + CEDEX FRANCE
• RECALCUL DES STATISTIQUES POUR AMÉLIORER LES PERFOMANCE D'UNE BASE SQLSERVER
• LABYRINTHE SUR SQL SERVER
• RECHERCHE D'UNE CHAINE DANS LES DONNÉES D'UNE TABLE OU DE TOUTE UNE BASE DE DONNÉE MSSQL

• INTERPRETEUR BRAINFUCK
• CRÉATION D'UNE BASE DE DONNÉE AVEC FICHIER DE DONNÉES, JOURNAL, TABLES ET CONSTRAINTES
• MYSQL : CRÉATION D'UN GUID
• SQL SERVER 2005 : OBTENIR LA LISTE DES BASES DE CONTENU D'UNE FERME SHAREPOINT 2007
• LABYRINTHE SUR SQL SERVER

• LABYRINTHE SUR SQL SERVER
• SQL SERVER - PROCÉDURE STOCKÉE FAISANT UN BACKUP DE TOUTES LES BASES ET JOURNAUX DE TRANSACTION AUTOMATIQUEMENT
• SQL SERVER - PROCÉDURE STOCKÉE METTANT À JOUR TOUTES LES STATISTIQUES DE TOUTES LES BASES DU SERVEUR
• SQL SERVER - REQUETTE POUR OBTENIR LES INFORMATIONS D'UNE PROCÉDURE STOCKÉE DANS UNE BASE DE DONNÉES
• SQL SERVER - OBTENIR LA LISTE DES PROCÉDURES STOCKÉES D'UNE BASE DE DONNÉES SQL SERVER