95 lines
2.8 KiB
Transact-SQL
95 lines
2.8 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 populateCardSet (@rows INT) AS
|
|
WHILE @rows > 0
|
|
BEGIN
|
|
INSERT INTO CardSet(name, image, releaseDate)
|
|
VALUES ('test', 'test', '2020-01-01')
|
|
SET @rows = @rows - 1
|
|
END
|
|
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE populateCards (@rows INT) AS
|
|
DECLARE @faction INT
|
|
SET @faction = (SELECT TOP 1 hmy FROM Factions)
|
|
DECLARE @cardset INT
|
|
SET @cardset = (SELECT TOP 1 hmy FROM CardSet)
|
|
WHILE @rows > 0
|
|
BEGIN
|
|
INSERT INTO Cards(name, faction, description, action, image, cost, type, power, color, cardset)
|
|
VALUES ('test', @faction, 'test', 'test', 'test', -1, 'unit', -1, 'gold', @cardset)
|
|
SET @rows = @rows - 1
|
|
END
|
|
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE populateCardsOwnership (@rows INT) AS
|
|
DECLARE @card INT
|
|
DECLARE @player INT
|
|
WHILE @rows > 0
|
|
BEGIN
|
|
SET @card = (SELECT hmy FROM Cards ORDER BY hmy OFFSET @rows ROWS FETCH NEXT 1 ROWS ONLY)
|
|
SET @player = (SELECT hmy FROM Players ORDER BY hmy OFFSET @rows ROWS FETCH NEXT 1 ROWS ONLY)
|
|
INSERT INTO CardsOwnership(hCard, hPlayer, nrOfCards, nrOfPremiumCards)
|
|
VALUES (@card, @player, 1, 1)
|
|
SET @rows = @rows - 1
|
|
END
|
|
|
|
GO
|
|
|
|
CREATE OR ALTER VIEW getNumberOfCardsOfEachTypeOwnByEachPlayer AS
|
|
SELECT p.name as Player, cs.Name as CardSet, Count(c.hmy) as NumberOfCards
|
|
FROM CardsOwnership co JOIN Cards c on co.hCard = c.hmy JOIN Players p on co.hPlayer = p.hmy JOIN CardSet cs on c.cardset = cs.hmy
|
|
GROUP BY p.name, cs.Name
|
|
|
|
|
|
GO
|
|
EXEC addTests 'test2'
|
|
|
|
EXEC addToTables 'Factions'
|
|
EXEC addToTables 'Players'
|
|
EXEC addToTables 'Matches'
|
|
EXEC addToTables 'CardSet'
|
|
EXEC addToTables 'Cards'
|
|
EXEC addToTables 'CardsOwnership'
|
|
|
|
EXEC addToViews 'getNumberOfCardsOfEachTypeOwnByEachPlayer'
|
|
|
|
EXEC addTestTables 'test2', 'Factions', 100, 6
|
|
EXEC addTestTables 'test2', 'Players', 1000, 5
|
|
EXEC addTestTables 'test2', 'CardSet', 10, 4
|
|
EXEC addTestTables 'test2', 'Matches', 10, 3
|
|
EXEC addTestTables 'test2', 'Cards', 1000, 2
|
|
EXEC addTestTables 'test2', 'CardsOwnership', 500, 1
|
|
|
|
EXEC addTestViews 'test2', 'getNumberOfCardsOfEachTypeOwnByEachPlayer'
|
|
|
|
GO
|
|
|
|
EXEC runTest 'test2'
|
|
|
|
|