/******************************************************
 *
 * Name:         make-prime-number-database.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     02-27-2018
 *     Purpose:  Calculate & store prime numbers.
 * 
 ******************************************************/


/*  
    Create a database to hold the prime numbers
*/

-- Delete existing database
/*
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'devdb4math')
DROP DATABASE [devdb4math]
GO
*/

-- Create new database
/*
CREATE DATABASE [devdb4math]
(
MAXSIZE = 2GB,
EDITION = 'STANDARD',
SERVICE_OBJECTIVE = 'S0'
)
GO
*/   
 
 
/*  
    Create a table to hold the prime numbers
*/

-- Delete existing table
DROP TABLE IF EXISTS [dbo].[tbl_primes]
GO

-- Add new table
CREATE TABLE [dbo].[tbl_primes] 
(
  [my_value] [bigint] NOT NULL,
  [my_division] [bigint] NOT NULL CONSTRAINT [chk_tbl_primes] CHECK ([my_division] > 0),
  [my_time] [datetime] NOT NULL CONSTRAINT [df_tbl_primes] DEFAULT (GETDATE())
  CONSTRAINT [pk_tbl_primes] PRIMARY KEY CLUSTERED ([my_value] ASC)
) 
GO


/*  
    Create a procedure to determine if number is prime
*/

-- Delete existing procedure
DROP PROCEDURE IF EXISTS [dbo].[sp_is_prime]
GO

-- Create the stored procedure from scratch
CREATE PROCEDURE [dbo].[sp_is_prime]
    @var_num2 BIGINT
AS
BEGIN
    -- NO DISPLAY
    SET NOCOUNT ON
 
    -- LOCAL VARIABLES
    DECLARE @var_cnt2 BIGINT;
    DECLARE @var_max2 BIGINT;

    -- NOT A PRIME NUMBER
    IF (@var_num2 = 1)
        RETURN 0;            

    -- A PRIME NUMBER
    IF (@var_num2 = 2)
        RETURN 1;            

    -- SET UP COUNTERS    
    SELECT @var_cnt2 = 2;
    SELECT @var_max2 = SQRT(@var_num2) + 1;

    -- TRIAL DIVISION 2 TO SQRT(X)
    WHILE (@var_cnt2 <= @var_max2)
    BEGIN
        -- NOT A PRIME NUMBER
        IF (@var_num2 % @var_cnt2) = 0
            RETURN 0;            

        -- INCREMENT COUNTER
        SELECT @var_cnt2 = @var_cnt2 + 1;
        
    END;

    -- A PRIME NUMBER
    RETURN 1;
    
END
GO


/*    
    Create a procedure to store primes from x to y.
*/

-- Delete existing procedure
DROP PROCEDURE IF EXISTS [dbo].[sp_store_primes]
GO

-- Create the stored procedure from scratch
CREATE PROCEDURE sp_store_primes
    @var_alpha BIGINT,
    @var_omega BIGINT
AS
BEGIN
    -- NO DISPLAY
    SET NOCOUNT ON
 
    -- DECLARE VARIABLES
    DECLARE @var_cnt1 BIGINT;
    DECLARE @var_ret1 INT;
    
    -- SET VARIABLES
    SELECT @var_ret1 = 0;
    SELECT @var_cnt1 = @var_alpha;

    -- CHECK EACH NUMBER FOR PRIMENESS
    WHILE (@var_cnt1 <= @var_omega)
    BEGIN
        -- ARE WE PRIME?
        EXEC @var_ret1 = DBO.sp_is_prime @var_cnt1;
        
        -- FOUND A PRIME
        IF (@var_ret1 = 1)
          INSERT INTO [dbo].[tbl_primes] (my_value, my_division) 
          VALUES (@var_cnt1, SQRT(@var_cnt1));
    
        -- INCREMENT COUNTER
        SELECT @var_cnt1 = @var_cnt1 + 1        
    END;
    
END
GO


/*  
    Create a table to hold the job control card
*/

-- Delete existing table
DROP TABLE IF EXISTS [dbo].[tbl_control_card]
GO

-- Add new table
CREATE TABLE [dbo].[tbl_control_card] 
(
  [my_value] [bigint] NOT NULL
) 
GO

-- Start at 1
INSERT INTO [dbo].[tbl_control_card] VALUES (1);



/*    
    Create a procedure to find primes in next 5K of candidates
*/

-- Delete existing procedure
DROP PROCEDURE IF EXISTS [dbo].[sp_run_batch]
GO

-- Create the stored procedure to calculate a batch of records
CREATE PROCEDURE sp_run_batch
AS
BEGIN

    -- Calculate prime numbers
    DECLARE @var_low_value [BIGINT];
    DECLARE @var_high_value [BIGINT];

	-- Try this code
	BEGIN TRY

		-- Low & High water marks
		SELECT @var_low_value = [MY_VALUE] FROM [dbo].[tbl_control_card];
		SELECT @var_high_value = @var_low_value + 5000 - 1;

		-- Start the process
		BEGIN TRANSACTION

		-- Run the calculation
		EXEC dbo.sp_store_primes @var_low_value, @var_high_value;

		-- Update the control card
		UPDATE [dbo].[tbl_control_card]
		SET [my_value] = @var_high_value + 1;

		-- End the process
		COMMIT TRANSACTION;

		-- Return good code
		RETURN (0);

	END TRY

	-- Report any errors
	BEGIN CATCH

	    -- Show error information
	    SELECT
            ERROR_NUMBER() AS ErrorNumber
           ,ERROR_SEVERITY() AS ErrorSeverity
           ,ERROR_STATE() AS ErrorState
           ,ERROR_PROCEDURE() AS ErrorProcedure
           ,ERROR_LINE() AS ErrorLine
           ,ERROR_MESSAGE() AS ErrorMessage;

		-- Roll back trans
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;

		-- Return bad code
		RETURN(1);

	END CATCH

END
GO


/*    
    Show database objects
*/

SELECT *
FROM sys.objects
WHERE is_ms_shipped = 0;



/*    
    Show database objects
*/

exec sp_spaceused 'tbl_primes';



/*    
    Development user
*/


-- Service Account
CREATE USER [svcacct4dev] WITH PASSWORD = 'MS#tIpS$2018dev';

-- Give rights
EXEC sp_addrolemember 'db_owner', 'svcacct4dev'  



/*    
    Production user
*/


-- Service Account
CREATE USER [svcacct4prd] WITH PASSWORD = 'MS#tIpS$2018prd';

-- Give rights
EXEC sp_addrolemember 'db_owner', 'svcacct4prd'  



-- Who is executing
sp_who2