63 lines
1.9 KiB
Transact-SQL
63 lines
1.9 KiB
Transact-SQL
USE "Gwent - The Witcher Card Game"
|
|
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE populateFactions (@rows INT) AS
|
|
WHILE @rows > 0
|
|
BEGIN
|
|
INSERT INTO Factions(name, totalMatches, totalWins, totalLosses, totalCards, totalDraws)
|
|
VALUES ('test', -1, -1, -1, -1, -1)
|
|
SET @rows = @rows - 1
|
|
END
|
|
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE populatePlayers (@rows INT) AS
|
|
WHILE @rows > 0
|
|
BEGIN
|
|
INSERT INTO Players(name, totalMatches, totalWins, totalLosses, totalDraws, reward_points, scraps, ores, meteorite_powder, rank)
|
|
VALUES ('test', -1, -1, -1, -1, -1, -1, -1, -1, -1)
|
|
SET @rows = @rows - 1
|
|
END
|
|
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE populateMatches (@rows INT) AS
|
|
DECLARE @player1 INT
|
|
SET @player1 = (SELECT TOP 1 hmy FROM Players)
|
|
DECLARE @player2 INT
|
|
SET @player2 = (SELECT TOP 1 hmy FROM Players WHERE hmy <> @player1)
|
|
DECLARE @faction INT
|
|
SET @faction = (SELECT TOP 1 hmy FROM Factions)
|
|
WHILE @rows > 0
|
|
BEGIN
|
|
INSERT INTO Matches(hPlayer1, hPlayer2, hPlayer1Faction, hPlayer2Faction, result)
|
|
VALUES (@player1, @player2, @faction, @faction, 1)
|
|
SET @rows = @rows - 1
|
|
END
|
|
|
|
GO
|
|
|
|
CREATE OR ALTER VIEW getMatches AS
|
|
SELECT p1.name as player1, p2.name as player2, f1.name as player1Faction, f2.name as player2Faction
|
|
FROM Matches m
|
|
JOIN Players p1 on m.hPlayer1 = p1.hmy
|
|
JOIN Players p2 on m.hPlayer2 = p2.hmy
|
|
JOIN Factions f1 on m.hPlayer1Faction = f1.hmy
|
|
JOIN Factions f2 on m.hPlayer2Faction = f2.hmy
|
|
|
|
GO
|
|
|
|
EXEC addTests 'test3'
|
|
EXEC addToTables 'Factions'
|
|
EXEC addToTables 'Players'
|
|
EXEC addToTables 'Matches'
|
|
EXEC addToViews 'getMatches'
|
|
EXEC addTestTables 'test3', 'Factions', 1000, 3
|
|
EXEC addTestTables 'test3', 'Players', 1000, 2
|
|
EXEC addTestTables 'test3', 'Matches', 1000, 1
|
|
EXEC addTestViews 'test3', 'getMatches'
|
|
|
|
GO
|
|
|
|
EXEC runTest 'test3' |