DROP TABLE [dbo].[PrecisionRecall]
GO

CREATE TABLE [dbo].[PrecisionRecall] (
[algorithm] char(20) NOT NULL,
[sim] float NOT NULL,
[epsilon] float NOT NULL,
[S] int NOT NULL,
[common] int NOT NULL,
[correct] int NOT NULL,
[retrieved] int NOT NULL,
PRIMARY KEY (algorithm,S,sim,epsilon)
)
GO

CREATE INDEX [similarities] ON [dbo].[ResultR1R2] ([S], [sim]  desc )
WITH FILLFACTOR = 100
GO
CREATE INDEX [similarities] ON [dbo].[ResultsR1sR2] ([S], [sim]  desc )
WITH FILLFACTOR = 100
GO
CREATE INDEX [similarities] ON [dbo].[ResultR1sR2] ([S], [sim]  desc )
WITH FILLFACTOR = 100
GO
CREATE INDEX [similarities] ON [dbo].[ResultR2sR1] ([S], [sim]  desc )
WITH FILLFACTOR = 100
GO
CREATE INDEX [similarities] ON [dbo].[WHIRL] ([sim]  desc )
WITH FILLFACTOR = 100
GO




-- 1
DECLARE @SAMPLE int
DECLARE @ALG char(20)
DECLARE @I int
DECLARE @epsilon float
DECLARE @UPPERLIMIT int -- The upper limit in the sample size will be 2^@UPPERLIMIT
SET @I=0
SET @UPPERLIMIT=8
WHILE @I <= @UPPERLIMIT
	BEGIN
		SET @SAMPLE=POWER(2, @I)
		DECLARE @SIM float
		SET @SIM=0.1
		WHILE @SIM < 1.0
		BEGIN
			SET @epsilon=0.0
			WHILE @epsilon < 0.3
			BEGIN		
				BEGIN TRANSACTION
				INSERT INTO PrecisionRecall(algorithm, sim, S, epsilon, common, correct, retrieved)
					SELECT "ResultR1R2",  @SIM AS sim, @SAMPLE AS S, @epsilon AS epsilon, D.common, D.correct, D.retrieved
					FROM (
						SELECT     A.num AS common, B.num AS correct, C.num AS retrieved
						FROM
						(SELECT     COUNT(*) AS num
						 FROM          Baseline INNER JOIN ResultR1R2 ON Baseline.tid1 = ResultR1R2.tid1 AND Baseline.tid2 = ResultR1R2.tid2
						 WHERE      (ResultR1R2.sim >= (1-@epsilon)*@SIM AND ResultR1R2.S=@SAMPLE) AND (Baseline.sim >= @SIM)) AS A,
						(SELECT     BaselineNumbers.matches AS num
						 FROM          BaselineNumbers
						 WHERE      (BaselineNumbers.sim = @SIM)) AS B,
						(SELECT     COUNT(*) AS num
						 FROM          ResultR1R2
						 WHERE      (ResultR1R2.sim >= (1-@epsilon)*@SIM AND ResultR1R2.S=@SAMPLE)) AS C
					) AS D
				COMMIT TRANSACTION
				CHECKPOINT
				SET @epsilon = @epsilon + 0.05
			END				
			SET @SIM = @SIM + 0.1
	   	END
		SET @I = @I+1
    	END
GO

-- 3
DECLARE @SAMPLE int
DECLARE @ALG char(20)
DECLARE @I int
DECLARE @epsilon float
DECLARE @UPPERLIMIT int -- The upper limit in the sample size will be 2^@UPPERLIMIT
SET @I=0
SET @UPPERLIMIT=8
WHILE @I <= @UPPERLIMIT
	BEGIN
		SET @SAMPLE=POWER(2, @I)
		DECLARE @SIM float
		SET @SIM=0.1
		WHILE @SIM < 1.0
		BEGIN
			SET @epsilon=0.0
			WHILE @epsilon < 0.3
			BEGIN	
				BEGIN TRANSACTION			
				INSERT INTO PrecisionRecall(algorithm, sim, S, epsilon, common, correct, retrieved)
					SELECT "ResultR1sR2",  @SIM AS sim, @SAMPLE AS S, @epsilon AS epsilon, D.common, D.correct, D.retrieved
					FROM (
						SELECT     A.num AS common, B.num AS correct, C.num AS retrieved
						FROM
						(SELECT     COUNT(*) AS num
						FROM          Baseline INNER JOIN ResultR1sR2 ON Baseline.tid1 = ResultR1sR2.tid1 AND Baseline.tid2 = ResultR1sR2.tid2
						WHERE      (ResultR1sR2.sim >= (1-@epsilon)*@SIM AND ResultR1sR2.S=@SAMPLE) AND (Baseline.sim >= @SIM)) AS A,
						(SELECT     BaselineNumbers.matches AS num
						FROM          BaselineNumbers
						WHERE      (BaselineNumbers.sim = @SIM)) AS B,
						(SELECT     COUNT(*) AS num
						FROM          ResultR1sR2
						WHERE      (ResultR1sR2.sim >= (1-@epsilon)*@SIM AND ResultR1sR2.S=@SAMPLE)) AS C
					) AS D
				COMMIT TRANSACTION
				CHECKPOINT
				SET @epsilon = @epsilon + 0.05
			END						
			SET @SIM = @SIM + 0.1
	   	END
		SET @I = @I+1
    	END
GO

-- 5
DECLARE @SAMPLE int
DECLARE @ALG char(20)
DECLARE @I int
DECLARE @epsilon float
DECLARE @UPPERLIMIT int -- The upper limit in the sample size will be 2^@UPPERLIMIT
SET @I=0
SET @UPPERLIMIT=8
WHILE @I <= @UPPERLIMIT
	BEGIN
		SET @SAMPLE=POWER(2, @I)
		DECLARE @SIM float
		SET @SIM=0.1
		WHILE @SIM < 1.0
		BEGIN
			SET @epsilon=0.0
			WHILE @epsilon < 0.3
			BEGIN	
				BEGIN TRANSACTION			
				INSERT INTO PrecisionRecall(algorithm, sim, S, epsilon, common, correct, retrieved)
					SELECT "ResultR2sR1",  @SIM AS sim, @SAMPLE AS S, @epsilon AS epsilon, D.common, D.correct, D.retrieved
					FROM (
						SELECT     A.num AS common, B.num AS correct, C.num AS retrieved
						FROM
						(SELECT     COUNT(*) AS num
						FROM          Baseline INNER JOIN ResultR2sR1 ON Baseline.tid1 = ResultR2sR1.tid1 AND Baseline.tid2 = ResultR2sR1.tid2
						WHERE      (ResultR2sR1.sim >= (1-@epsilon)*@SIM AND ResultR2sR1.S=@SAMPLE) AND (Baseline.sim >= @SIM)) AS A,
						(SELECT     BaselineNumbers.matches AS num
						FROM          BaselineNumbers
						WHERE      (BaselineNumbers.sim = @SIM)) AS B,
						(SELECT     COUNT(*) AS num
						FROM          ResultR2sR1
						WHERE      (ResultR2sR1.sim >= (1-@epsilon)*@SIM AND ResultR2sR1.S=@SAMPLE)) AS C
					) AS D
				COMMIT TRANSACTION
				CHECKPOINT
				SET @epsilon = @epsilon + 0.05
			END						
			SET @SIM = @SIM + 0.1
	   	END
		SET @I = @I+1
    	END
GO

-- 7
DECLARE @SAMPLE int
DECLARE @ALG char(20)
DECLARE @I int
DECLARE @epsilon float
DECLARE @UPPERLIMIT int -- The upper limit in the sample size will be 2^@UPPERLIMIT
SET @I=0
SET @UPPERLIMIT=8
WHILE @I <= @UPPERLIMIT
	BEGIN
		SET @SAMPLE=POWER(2, @I)
		DECLARE @SIM float
		SET @SIM=0.1
		WHILE @SIM < 1.0
		BEGIN
			SET @epsilon=0.0
			WHILE @epsilon < 0.3
			BEGIN		
				BEGIN TRANSACTION			
				INSERT INTO PrecisionRecall(algorithm, sim, S, epsilon, common, correct, retrieved)
					SELECT "ResultsR1sR2",  @SIM AS sim, @SAMPLE AS S, @epsilon AS epsilon, D.common, D.correct, D.retrieved
					FROM (
						SELECT     A.num AS common, B.num AS correct, C.num AS retrieved
						FROM
						(SELECT     COUNT(*) AS num
						FROM          Baseline INNER JOIN ResultsR1sR2 ON Baseline.tid1 = ResultsR1sR2.tid1 AND Baseline.tid2 = ResultsR1sR2.tid2
						WHERE      (ResultsR1sR2.sim >= (1-@epsilon)*@SIM AND ResultsR1sR2.S=@SAMPLE) AND (Baseline.sim >= @SIM)) AS A,
						(SELECT     BaselineNumbers.matches AS num
						FROM          BaselineNumbers
						WHERE      (BaselineNumbers.sim = @SIM)) AS B,
						(SELECT     COUNT(*) AS num
						FROM          ResultsR1sR2
						WHERE      (ResultsR1sR2.sim >= (1-@epsilon)*@SIM AND ResultsR1sR2.S=@SAMPLE)) AS C
					) AS D
				COMMIT TRANSACTION
				CHECKPOINT
				SET @epsilon = @epsilon + 0.05
			END						
			SET @SIM = @SIM + 0.1
	   	END
		SET @I = @I+1
    	END
GO