School/Anul 2/Semestrul 1/Baze de date/Lab 4/procedures.sql

USE "Gwent - The Witcher Card Game"

GO

CREATE OR ALTER PROCEDURE addToTables (@tableName VARCHAR(255)) AS
    IF @tableName IN (SELECT Name FROM Tables)
        BEGIN
            PRINT 'Table already present in Tables'
            RETURN
        END
    IF @tableName NOT IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
        BEGIN
            PRINT 'Table doesn''t exists'
            RETURN
        END
    INSERT INTO Tables (Name) VALUES (@tableName)

GO

CREATE OR ALTER PROCEDURE addToViews (@viewName VARCHAR(255)) AS
    IF @viewName IN (SELECT Name FROM Views)
        BEGIN
            PRINT 'View already present in Views'
            RETURN
        END
    IF @viewName NOT IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS)
        BEGIN
            PRINT 'View doesn''t exists'
            RETURN
        END
    INSERT INTO Views (Name) VALUES (@viewName)

GO

CREATE OR ALTER PROCEDURE addTests (@testName VARCHAR(255)) AS
    IF @testName IN (SELECT Name FROM Tests)
        BEGIN
            PRINT 'Test already present in Tests'
            RETURN
        END
    INSERT INTO Tests (Name) VALUES (@testName)

GO

CREATE OR ALTER PROCEDURE addTestTables (@testName VARCHAR(255), @tableName VARCHAR(255), @rows INT, @position INT) AS
    IF @tableName NOT IN (SELECT Name FROM Tables)
        BEGIN
            PRINT 'Table not present in Tables'
            RETURN
        END
    IF @testName NOT IN (SELECT Name FROM Tests)
        BEGIN
            PRINT 'Test not present in Tests'
            RETURN
        END
    IF EXISTS(
        SELECT 1 FROM TestTables tt JOIN Tests t on tt.TestID = t.TestID
        WHERE t.Name = @testName and tt.Position = @position 
    )
    BEGIN
        PRINT 'Position already exists in TestTables for current test'
        RETURN
    END
    INSERT INTO TestTables (TableID, TestID, NoOfRows, Position) VALUES
    (
        (SELECT TableID from Tables WHERE Name = @tableName),
        (SELECT TestID from Tests WHERE Name = @testName),
        @rows,
        @position
    )

GO

CREATE OR ALTER PROCEDURE addTestViews (@testName VARCHAR(255), @viewName VARCHAR(255)) AS
    IF @viewName NOT IN (SELECT Name FROM Views)
        BEGIN
            PRINT 'View not present in Views'
            RETURN
        END
    IF @testName NOT IN (SELECT Name FROM Tests)
        BEGIN
            PRINT 'Test not present in Tests'
            RETURN
        END
    INSERT INTO TestViews (ViewID, TestID) VALUES
    (
        (SELECT ViewID from Views WHERE Name = @viewName),
        (SELECT TestID from Tests WHERE Name = @testName)
    )

GO

CREATE OR ALTER PROCEDURE runTest (@testName VARCHAR(255)) AS
    DECLARE @startTime DATETIME
    DECLARE @endTime DATETIME
    DECLARE @insertStartTime DATETIME
    DECLARE @insertEndTime DATETIME
    DECLARE @selectStartTime DATETIME
    DECLARE @selectEndTime DATETIME
    DECLARE @table VARCHAR(255)
    DECLARE @view VARCHAR(255)
    DECLARE @noOfTables INT
    DECLARE @noOfViews INT
    DECLARE @offset INT
    DECLARE @rows INT
    DECLARE @testRunID INT
    DECLARE @command VARCHAR(255)
    INSERT INTO TestRuns (Description) VALUES
    (
        'Test run for ' + @testName
    )
    SET @testRunID =(SELECT MAX(TestRunID) FROM TestRuns)

    SET @offset = 0
    SET @noOfTables = (SELECT COUNT(*) FROM TestTables tt JOIN Tests t on tt.TestID = t.TestID WHERE t.Name = @testName)
    SET @noOfViews = (SELECT COUNT(*) FROM TestViews tv JOIN Tests t on tv.TestID = t.TestID WHERE t.Name = @testName)
    IF @testName NOT IN (SELECT Name FROM Tests)
        BEGIN
            PRINT 'Test not present in Tests'
            RETURN
        END
    
    SET @startTime = GETDATE()
    WHILE @offset < @noOfTables
        BEGIN
            SET @table = (SELECT tab.Name FROM TestTables tt JOIN Tests t on tt.TestID = t.TestID JOIN Tables tab on tt.TableID = tab.TableID WHERE t.Name = @testName ORDER BY tt.Position OFFSET @offset ROWS FETCH NEXT 1 ROWS ONLY)
            EXEC ('delete from ' + @table)
            SET @offset = @offset + 1
        END
    SET @offset = 0

    WHILE @offset < @noOfTables
        BEGIN
            SET @table = (SELECT tab.Name FROM TestTables tt JOIN Tests t on tt.TestID = t.TestID JOIN Tables tab on tt.TableID = tab.TableID WHERE t.Name = @testName ORDER BY tt.Position DESC OFFSET @offset ROWS FETCH NEXT 1 ROWS ONLY)
            SET @rows = (SELECT NoOfRows FROM TestTables tt JOIN Tests t on tt.TestID = t.TestID JOIN Tables tab on tt.TableID = tab.TableID WHERE t.Name = @testName ORDER BY tt.Position DESC OFFSET @offset ROWS FETCH NEXT 1 ROWS ONLY)
            SET @insertStartTime = GETDATE()
            SET @command = 'populate' + @table
            EXEC @command @rows
            SET @insertEndTime = GETDATE()
            INSERT INTO TestRunTables (TestRunID, TableID, StartAt, EndAt) VALUES
            (
                @testRunID,
                (SELECT TableID FROM Tables WHERE Name = @table),
                @insertStartTime,
                @insertEndTime
            )
            SET @offset = @offset + 1
        END
    SET @offset = 0
    WHILE @offset < @noOfViews
        BEGIN
            SET @view = (SELECT v.Name FROM TestViews tv JOIN Tests t on tv.TestID = t.TestID JOIN Views v on tv.ViewID = v.ViewID WHERE t.Name = @testName ORDER BY tv.ViewID OFFSET @offset ROWS FETCH NEXT 1 ROWS ONLY)
            SET @selectStartTime = GETDATE()
            EXEC ('select * from ' + @view)
            SET @selectEndTime = GETDATE()
            INSERT INTO TestRunViews (TestRunID, ViewID, StartAt, EndAt) VALUES
            (
                @testRunID,
                (SELECT ViewID FROM Views WHERE Name = @view),
                @selectStartTime,
                @selectEndTime
            )
            SET @offset = @offset + 1
        END
    SET @endTime = GETDATE()
    UPDATE TestRuns SET StartAt = @startTime, EndAt = @endTime WHERE TestRunID = @testRunID

    
    SELECT * FROM TestRuns
    SELECT * FROM TestRunTables
    SELECT * FROM TestRunViews