INSERT INTO Tables(Name) VALUES ('Artisti'), ('Impresar'), ('Lansare')
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE VIEW Artisti_Views AS
SELECT Artisti.ImpresarID,Artisti.Nume
FROM Artisti
GO

CREATE VIEW Impresar_Views AS
SELECT I.Nume
FROM Impresar I
INNER JOIN Artisti A ON       I.ImpresarID = A.ImpresarID
GO

CREATE VIEW Lansare_Views AS
SELECT M.Nume, Count(*) as 'Numar melodii lansate'
FROM Melodii M
INNER JOIN Lansare L     ON L.MelodieID = M.MelodieID
INNER JOIN Artisti A ON        A.ArtistID = L.ArtistID
GROUP BY M.Nume
GO

INSERT INTO Views VALUES ('Artisti_Views'), ('Impresar_Views'), ('Lansare_Views')

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

INSERT INTO Tests(Name) VALUES
('Insert_x'), --1
('Delete_x'), --2
('Evaluate')  --3
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

INSERT INTO TestTables VALUES
(1,1,10000,1), (2,1,10000,2), (3,1,10000,3),
(1,2,10000,1), (2,2,10000,2), (3,2,10000,3)
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

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

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE PROCEDURE Insert_Artisti (@rows int) AS

BEGIN

	DECLARE @ID		 int
	DECLARE @name	 VARCHAR(50)
	DECLARE @index	 int
	DECLARE @lastID  int
	    SET @name = 'UNTITLED_NAME'
		SET @ID    = 10000
		SET @index = 1

	WHILE @index <= @rows
	BEGIN
		SET @ID = 10000 + @index

		SELECT TOP 1 @lastID = Artisti.ImpresarID
		FROM dbo.Artisti
		ORDER BY Artisti.ImpresarID DESC

		IF @lastID > 10000
			SET @ID = @lastID + 1

		INSERT INTO Artisti VALUES (@ID, @name, null)

		SET @index = @index + 1
	END
END
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE PROCEDURE Delete_Artisti (@rows int) AS

BEGIN

	DECLARE @ID		 int
	DECLARE @index	 int
	DECLARE @lastID  int
	    SET @ID    = 10000
		SET @index = @rows

	WHILE @index > 0
	BEGIN
		SET @ID = 10000 + @index

		SELECT TOP 1 @lastID = Artisti.ArtistID
		FROM dbo.Artisti
		ORDER BY Artisti.ArtistID DESC

		IF @lastID > @ID
			SET @ID = @lastID

		DELETE FROM Artisti WHERE Artisti.ArtistID = @ID

		SET @index = @index - 1
	END
END
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE PROCEDURE Insert_Impresar (@rows int) AS

BEGIN
	DECLARE @ID		 int
	DECLARE @name 	 VARCHAR(50)
	DECLARE @index	 int
	DECLARE @lastID  int
	    SET @name  = 'GHOST_NAME'
		SET @ID    = 10000
		SET @index = 1

	WHILE @index <= @rows
	BEGIN
		SET @ID = 10000 + @index

		SELECT TOP 1 @lastID = Impresar.ImpresarID
		FROM dbo.Impresar
		ORDER BY Impresar.ImpresarID DESC

		IF @lastID > 10000
			SET @ID = @lastID + 1

		INSERT INTO Impresar VALUES (@ID, @name)

		SET @index = @index + 1
	END
END
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE PROCEDURE Delete_Impresar (@rows int) AS

BEGIN

	DECLARE @ID		 int
	DECLARE @index	 int
	DECLARE @lastID  int
	    SET @ID    = 10000
		SET @index = @rows

	WHILE @index > 0
	BEGIN
		SET @ID = 10000 + @index

		SELECT TOP 1 @lastID = Impresar.ImpresarID
		FROM dbo.Impresar
		ORDER BY Impresar.ImpresarID DESC

		IF @lastID > @ID
			SET @ID = @lastID

		DELETE FROM Impresar WHERE Impresar.ImpresarID = @ID

		SET @index = @index - 1
	END
END
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE PROCEDURE Insert_Lansare (@rows int) AS

BEGIN

	DECLARE @ArtistID	 int
	DECLARE @Nume    varchar(50)
	DECLARE @index	 int
	    SET @index = @rows

	    EXEC Insert_Artisti @rows

	DECLARE arCursor CURSOR SCROLL FOR

	SELECT ArtistID,Nume
	FROM Artisti

	OPEN arCursor
	FETCH LAST FROM arCursor
	INTO @ArtistID, @Nume

	WHILE @index > 0 AND @@FETCH_STATUS = 0
		BEGIN
		INSERT INTO Lansare VALUES (@ArtistID,1,'2006-06-06')

		FETCH PRIOR FROM arCursor
		INTO @ArtistID, @Nume

		SET @index = @index - 1
	END

	CLOSE arCursor DEALLOCATE arCursor
END
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE PROCEDURE Delete_Lansare(@rows int) AS

BEGIN

	DECLARE @index	 int
	DECLARE @ArtistID	 int
		SET @index = @rows

	WHILE @index > 0
	BEGIN
		SELECT TOP 1 @ArtistID = ArtistID
		FROM dbo.Artisti
		ORDER BY ArtistID DESC

		IF @ArtistID > 10000
		BEGIN
			DELETE FROM Lansare
			WHERE Lansare.MelodieID = 1 AND
				  Lansare.ArtistID = @ArtistID

			    EXEC Delete_Artisti 1
		END

		SET @index = @index - 1
	END
END
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE PROCEDURE Insert_x (@table VARCHAR(50), @x int) AS
BEGIN

	DECLARE @insert   VARCHAR(20)
		SET @insert = 'Insert_' + @table
	   EXEC @insert   @x

END
GO

CREATE PROCEDURE Delete_x (@table VARCHAR(50), @x int) AS
BEGIN

	DECLARE @delete   VARCHAR(20)
		SET @delete = 'Delete_' + @table
	   EXEC @delete   @x

END
GO

CREATE PROCEDURE Evaluate (@View VARCHAR(50)) AS
BEGIN
	IF @View = 'Artisti'
		SELECT * FROM Artisti_Views

	IF @View = 'Impresar'
		SELECT * FROM Impresar_Views

	IF @View = 'Lansare'
		SELECT * FROM Lansare_Views
END
GO

--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--~~--

CREATE PROCEDURE MainProgram (@table VARCHAR(20), @rows int) AS
BEGIN
	DECLARE @startTime	datetime
	DECLARE @endTime	datetime
	DECLARE @evalTime	datetime
	DECLARE @info		NVARCHAR(2000)
	DECLARE @testInsert VARCHAR(20)
	DECLARE @testDelete VARCHAR(20)
	DECLARE @testID		int
	DECLARE @index		int

	SET @startTime = GETDATE()

	EXEC Insert_x @table, @rows
	EXEC Delete_x @table, @rows

	SET @endTime = GETDATE()

	EXEC Evaluate @table

	SET @evalTime = GETDATE()


	SET @info = 'The tests were executed on table ' + @table + ' for ' + CONVERT(VARCHAR(5),@rows) + ' entries of data.'

	INSERT INTO TestRuns VALUES (@info, @startTime, @evalTime)

	SELECT TOP 1 @testID = T.TestRunID
	FROM dbo.TestRuns T
	ORDER BY T.TestRunID DESC

	SELECT TOP 1 @index = Tables.TableID
	FROM dbo.Tables
	WHERE Tables.Name = @table

	INSERT INTO TestRunTables VALUES (@testID, @index, @startTime,  @endTime)
	INSERT INTO TestRunViews  VALUES (@testID, @index,   @endTime, @evalTime)
END