/******************************************************
 *
 * Name:         step02-make-db-4-prime-numbers.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     01-17-2018
 *     Purpose:  Calculate & store prime numbers.
 * 
 ******************************************************/

--
-- Create the database (instead of ps code)
--

-- Delete existing database
DROP DATABASE IF EXISTS [db4primes]
GO


-- Create new database
CREATE DATABASE [db4primes]
(
MAXSIZE = 100MB,
EDITION = 'basic',
SERVICE_OBJECTIVE = 'basic'
)
GO   


--
-- Create ACTIVE schema
--

-- Delete existing schema.
DROP SCHEMA IF EXISTS [active]
GO
 
-- Add new schema.
CREATE SCHEMA [active] AUTHORIZATION [active]
GO


--
-- Create table to hold primes
--

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

-- Add new table
CREATE TABLE [active].[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_primes_value] PRIMARY KEY CLUSTERED ([my_value] ASC)
) 
GO


--
-- Create a procedure to determine if number is prime
--

-- Delete existing procedure
DROP PROCEDURE IF EXISTS [active].[usp_is_prime]
GO

-- Create the stored procedure from scratch
CREATE PROCEDURE [active].[usp_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 [active].[usp_store_primes]
GO

-- Create the stored procedure from scratch
CREATE PROCEDURE [active].[usp_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 = [active].[usp_is_prime] @VAR_CNT1;
        
        -- FOUND A PRIME
        IF (@VAR_RET1 = 1)
          INSERT INTO [active].[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 procedure to store primes from x to y.
--

-- Delete existing table
DROP TABLE IF EXISTS [active].[tbl_control_cards]
GO

-- Add new table
CREATE TABLE [active].[tbl_control_cards] 
(
  [my_start] [bigint] NOT NULL,
  [my_end] [bigint] NOT NULL,
  [my_primes] INT NOT NULL,
  [my_time] [datetime] NOT NULL CONSTRAINT [df_tbl_control_card] DEFAULT (GETDATE())
) 
GO

-- Start at 1
INSERT INTO [active].[tbl_control_cards] VALUES (1, 250000, 0, default);


/*    
    Show database objects
*/

SELECT name, type, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0;


/*    
    Show database objects
*/

exec sp_spaceused 'active.tbl_primes';