USE RomanianGouverment
GO
--
---CREATING VIEWS AND INSERT INTO VIEW TABLE
CREATE OR ALTER VIEW CitiesView
AS
    SELECT DISTINCT city
    FROM Addresses
GO
CREATE OR ALTER VIEW CitiesWithSchoolView
AS
    SELECT Addr.city, Sch.name
    FROM Addresses Addr INNER JOIN Schools Sch on Addr.addid = Sch.addid
GO
CREATE OR ALTER VIEW BiggestGradeBySchoolView
AS
    SELECT Sch.schid, MAX(St.gdp) AS Grade
    FROM Schools Sch INNER JOIN Studies St on Sch.schid = St.schid
    GROUP BY Sch.schid
GO


INSERT Views
VALUES
    ('CitiesView'),
    ('CitiesWithSchoolView'),
    ('BiggestGradeBySchoolView')
GO


-----INSERT INTO TABLES TABLE

INSERT INTO Tables
VALUES
    ('Addresses'),
    ('Schools'),
    ('Studies')
GO

-----INSERT INTO TESTS TABLE

INSERT INTO Tests
VALUES
    ('insertTest'),
    ('deleteTest'),
    ('selectViewTest')
GO

----INSERT INTO TestTables

INSERT INTO TestTables
    (TestID,TableID,NoOfRows,Position)
VALUES
    (1, 1, 1000, 1),
    (1, 2, 1000, 2),
    (1, 3, 1000, 3),
    (2, 1, 1000, 3),
    (2, 2, 1000, 2),
    (2, 3, 1000, 1)
GO

-----INSERT INTO TestViews

INSERT INTO TestViews
    (TestID,ViewID)
VALUES
    (3, 1),
    (3, 2),
    (3, 3)
GO

----CREATE INSERT PROCEDURES

CREATE OR ALTER PROC testAddresses
AS
-----DECLARING THE TIME VARIABLES
DECLARE @start datetime
DECLARE @viewStart datetime
DECLARE @end datetime
SET @start = SYSDATETIME()
------INSERTING INTO THE TestRuns
DECLARE @id int
INSERT TestRuns
VALUES
    ('AddressTest', @start, @start)
SET @id= SCOPE_IDENTITY()
-----STARTING TO INSERT
DECLARE @i INT
DECLARE @limit INT
SELECT TOP 1
    @limit = NoOfRows
FROM TestTables
WHERE TableID = 1
SET @i = 0
WHILE @i < @limit
 BEGIN
    INSERT INTO Addresses
        (conty,city,street,nr)
    VALUES
        ('TEST', 'TEST', 'TEST', @i)
    SET @i=@i+1
END
----STARTING TO DELETE
DELETE FROM Addresses WHERE conty LIKE 'TEST'

---INSERTING INTO TestRunTables
SET @viewStart = SYSDATETIME()
INSERT TestRunTables
    (TestRunID,TableID,StartAt,EndAt)
VALUES
    (@id, 1, @start, @viewStart)
-----STARTING VIEW AND INSERTING INTO TestRunViews
SELECT *
FROM CitiesView
SET @end=SYSDATETIME()
INSERT INTO TestRunViews
    (TestRunID,ViewID,StartAt,EndAt)
VALUES
    (@id, 1, @viewStart, @end)
--UPDATING THE EndAt in the TestRuns
UPDATE TestRuns
SET EndAt = @end
WHERE TestRunID = @id

GO

CREATE OR ALTER PROC testSchools
AS
-----DECLARING THE TIME VARIABLES
DECLARE @start datetime
DECLARE @viewStart datetime
DECLARE @end datetime
SET @start = SYSDATETIME()
------INSERTING INTO THE TestRuns
DECLARE @id int
INSERT TestRuns
VALUES
    ('SchoolTest', @start, @start)
SET @id = SCOPE_IDENTITY()
-----STARTING TO INSERT
DECLARE @i INT
SET @i = 0
------FOREIGN KEY INIT
DECLARE @fk INT
SELECT TOP 1
    @fk = addid
From Addresses
DECLARE @limit INT
SELECT TOP 1
    @limit = NoOfRows
FROM TestTables
WHERE TableID = 2
------
WHILE @i < @limit
 BEGIN
    INSERT INTO Schools
        (name,est,rank,level,addid)
    VALUES
        ('TEST', GETDATE(), @i, 'TEST', @fk)
    SET @i=@i+1
END
----STARTING TO DELETE
DELETE FROM Schools WHERE name LIKE 'TEST'

---INSERTING INTO TestRunTables
SET @viewStart = SYSDATETIME()
INSERT TestRunTables
    (TestRunID,TableID,StartAt,EndAt)
VALUES
    (@id, 2, @start, @viewStart)
-----STARTING VIEW AND INSERTING INTO TestRunViews
SELECT *
FROM CitiesWithSchoolView
SET @end=SYSDATETIME()
INSERT INTO TestRunViews
    (TestRunID,ViewID,StartAt,EndAt)
VALUES
    (@id, 2, @viewStart, @end)
--UPDATING THE EndAt in the TestRuns
UPDATE TestRuns
SET EndAt = @end
WHERE TestRunID = @id

GO
-------------------------------------
CREATE OR ALTER PROC testStudies
AS
DECLARE @id INT
------CREATING THIS TO HAVE THE POSIBILITY TO MAKE THE INSERTION
DECLARE @i INT
SET @i = 0
------FOREIGN KEY INIT
DECLARE @fk INT
SELECT TOP 1
    @fk = addid
FROM Addresses
WHILE @i < 1000
 BEGIN
    INSERT INTO Schools
        (name,est,rank,level,addid)
    VALUES
        ('TEST', GETDATE(), @i, 'TEST', @fk)
    SET @i = @i+1
END
SELECT TOP 1
    @fk = schid
FROM Schools
ORDER BY schid DESC
DECLARE @fk1 INT
SELECT TOP 1
    @fk1 = pid
FROM Politicians
-----DECLARING THE TIME VARIABLES
DECLARE @start datetime
DECLARE @viewStart datetime
DECLARE @end datetime
SET @start = SYSDATETIME()
------INSERTING INTO THE TestRuns
INSERT TestRuns
VALUES
    ('Studies Test', @start, @start)
SET @id = SCOPE_IDENTITY()
-----STARTING TO INSERT
SET @i = 0
DECLARE @limit INT
SELECT TOP 1
    @limit = NoOfRows
FROM TestTables
WHERE TableID = 3
WHILE @i < @limit
 BEGIN
    INSERT INTO Studies
        (schid,pid,profile,gdp,yog)
    VALUES
        (@fk, @fk1, 'TEST', 1, 1990)
    SET @fk = @fk - 1
    SET @i=@i+1
END
----STARTING TO DELETE
DELETE FROM Studies WHERE profile LIKE 'TEST'

---INSERTING INTO TestRunTables
SET @viewStart = SYSDATETIME()
INSERT TestRunTables
    (TestRunID,TableID,StartAt,EndAt)
VALUES
    (@id, 3, @start, @viewStart)
-----STARTING VIEW AND INSERTING INTO TestRunViews
SELECT *
FROM CitiesView
SET @end=SYSDATETIME()
INSERT INTO TestRunViews
    (TestRunID,ViewID,StartAt,EndAt)
VALUES
    (@id, 3, @viewStart, @end)
--UPDATING THE EndAt in the TestRuns
UPDATE TestRuns
SET EndAt = @end
WHERE TestRunID = @id
DELETE FROM Schools WHERE name LIKE 'TEST'

GO
------EXECUTING TESTS
EXEC testAddresses
EXEC testSchools
EXEC testStudies
----SEEING THE RESULTS
SELECT *
FROM TestRuns
SELECT *
FROM TestRunTables
SELECT *
FROM TestRunViews
SELECT *
FROM Addresses
DELETE FROM TestRuns
DELETE FROM TestRunTables
DELETE FROM TestRunViews