-- Copyright 2022 Colorado Mountain College
--
--Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), 
--to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, 
--and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
--WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.



-- Canvas Database load script
--
-- The stored procedure loads the tables from the downloaded and unpacked files. Since the files 
-- have the same column order as the tables (both are based on the JSON schema) we can leverage this to 
-- dynamically create the insert statement. 
--
-- The stored procedure can be called from a scheduled task or a SQL Server integration services task after the data has been downloaded from Canvas.
--
--
--

-- Original Post:  https://community.canvaslms.com/t5/Data-and-Analytics-Group/MS-SQL-Canvas-Data-Database-Generation-and-Load-Scripts/m-p/522970#M1439

-- Mods:
-- 2023-04-05: Modified from the original for Microsoft Azure environment.  SQL Server Managed Instance.


USE CanvasAnalytics2
go
IF OBJECT_ID(N'dbo.LoadCanvasAnalytics') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.LoadCanvasAnalytics
    IF OBJECT_ID(N'dbo.LoadCanvasAnalytics') IS NOT NULL
        PRINT N'<<< FAILED DROPPING PROCEDURE dbo.LoadCanvasAnalytics >>>'
    ELSE
        PRINT N'<<< DROPPED PROCEDURE dbo.LoadCanvasAnalytics >>>'
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
CREATE PROC dbo.LoadCanvasAnalytics
AS
BEGIN

	-- Fetch the files to be processed
	-- We are reading in the file names from the provided path. These should be the unpacked files.
	--************************IMPORTANT: change the path to the correct location *****************************

/* 
	Mod: 4/5/2023 modified from original script for Azure.  xp_DirTree does not available in Azure SQL Server.  Replaced w/list of files 
*/

	/* DECLARE @files TABLE ( FileName varchar(100), Depth int, FileType int  )
	INSERT INTO @files EXEC Master.dbo.xp_DirTree '\\cs-powerbi1\f$\imports\CanvasData\UnpackedFiles', 1, 1

	*/
DECLARE @files TABLE ( FileName varchar(100), Depth int, FileType int  )

INSERT INTO @files
values 
('account_dim.txt',1,1),
('assignment_dim.txt',1,1),
('assignment_fact.txt',1,1),
('assignment_group_dim.txt',1,1),
('assignment_group_fact.txt',1,1),
('assignment_group_rule_dim.txt',1,1),
('assignment_group_score_dim.txt',1,1),
('assignment_group_score_fact.txt',1,1),
('assignment_override_dim.txt',1,1),
('assignment_override_fact.txt',1,1),
('assignment_override_user_dim.txt',1,1),
('assignment_override_user_fact.txt',1,1),
('assignment_override_user_rollup_fact.txt',1,1),
('assignment_rule_dim.txt',1,1),
('communication_channel_dim.txt',1,1),
('communication_channel_fact.txt',1,1),
('conference_dim.txt',1,1),
('conference_fact.txt',1,1),
('conference_participant_dim.txt',1,1),
('conference_participant_fact.txt',1,1),
('conversation_dim.txt',1,1),
('conversation_message_dim.txt',1,1),
('conversation_message_participant_fact.txt',1,1),
('course_dim.txt',1,1),
('course_score_dim.txt',1,1),
('course_score_fact.txt',1,1),
('course_section_dim.txt',1,1),
('course_ui_canvas_navigation_dim.txt',1,1),
('course_ui_navigation_item_dim.txt',1,1),
('course_ui_navigation_item_fact.txt',1,1),
('discussion_entry_dim.txt',1,1),
('discussion_entry_fact.txt',1,1),
('discussion_topic_dim.txt',1,1),
('discussion_topic_fact.txt',1,1),
('enrollment_dim.txt',1,1),
('enrollment_fact.txt',1,1),
('enrollment_rollup_dim.txt',1,1),
('enrollment_term_dim.txt',1,1),
('external_tool_activation_dim.txt',1,1),
('external_tool_activation_fact.txt',1,1),
('file_dim.txt',1,1),
('file_fact.txt',1,1),
('group_dim.txt',1,1),
('group_fact.txt',1,1),
('group_membership_dim.txt',1,1),
('group_membership_fact.txt',1,1),
('learning_outcome_dim.txt',1,1),
('learning_outcome_fact.txt',1,1),
('learning_outcome_group_association_fact.txt',1,1),
('learning_outcome_group_dim.txt',1,1),
('learning_outcome_group_fact.txt',1,1),
('learning_outcome_question_result_dim.txt',1,1),
('learning_outcome_question_result_fact.txt',1,1),
('learning_outcome_result_dim.txt',1,1),
('learning_outcome_result_fact.txt',1,1),
('learning_outcome_rubric_criterion_dim.txt',1,1),
('learning_outcome_rubric_criterion_fact.txt',1,1),
('module_completion_requirement_dim.txt',1,1),
('module_completion_requirement_fact.txt',1,1),
('module_dim.txt',1,1),
('module_fact.txt',1,1),
('module_item_dim.txt',1,1),
('module_item_fact.txt',1,1),
('module_prerequisite_dim.txt',1,1),
('module_prerequisite_fact.txt',1,1),
('module_progression_completion_requirement_dim.txt',1,1),
('module_progression_completion_requirement_fact.txt',1,1),
('module_progression_dim.txt',1,1),
('module_progression_fact.txt',1,1),
('pseudonym_dim.txt',1,1),
('pseudonym_fact.txt',1,1),
('quiz_dim.txt',1,1),
('quiz_fact.txt',1,1),
('quiz_question_answer_dim.txt',1,1),
('quiz_question_answer_fact.txt',1,1),
('quiz_question_dim.txt',1,1),
('quiz_question_fact.txt',1,1),
('quiz_question_group_dim.txt',1,1),
('quiz_question_group_fact.txt',1,1),
('quiz_submission_dim.txt',1,1),
('quiz_submission_fact.txt',1,1),
('quiz_submission_historical_dim.txt',1,1),
('quiz_submission_historical_fact.txt',1,1),
('requests.txt',1,1),
('role_dim.txt',1,1),
('submission_comment_dim.txt',1,1),
('submission_comment_fact.txt',1,1),
('submission_dim.txt',1,1),
('submission_fact.txt',1,1),
('submission_file_fact.txt',1,1),
('user_dim.txt',1,1),
('wiki_dim.txt',1,1),
('wiki_fact.txt',1,1),
('wiki_page_dim.txt',1,1),
('wiki_page_fact.txt',1,1)



	--Step through the files that we just fetched
	DECLARE @fileName AS varchar(100)

	DECLARE files_cursor CURSOR
		FOR SELECT FileName FROM @files
	OPEN files_cursor
	FETCH NEXT FROM files_cursor
	INTO @fileName

	WHILE @@FETCH_STATUS = 0
	BEGIN

		-- The pathname is used in the bulk insert statement
		--************************IMPORTANT: change the path to the correct location *****************************
		/* Mod:  4/5/2023 replace origin with Azure Blob storage location */
		DECLARE @pathName AS varchar(500)
		SET @pathName = 'https://xxxxxxxx.blob.core.windows.net/xxxxxx?svXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
		--from which we can also derive the table in the database since they have the same name as the file
		DECLARE @tableName AS varchar(100)
		SET @tableName = SUBSTRING(@fileName, 1, CHARINDEX('.', @fileName) - 1)
		
		--debug: SELECT @tableName

		--fetch the number of columns that we need to load
		DECLARE @columnCount AS int

		SELECT @columnCount = COUNT(1)
		FROM sys.columns a
		JOIN sys.tables b
			ON a.object_id = b.object_id
		WHERE b.name = @tableName

		--select @columnCount

		-- create the column defenition that we will load the file data into. Note that the file data should have the same ordinal position as the table data 
		-- so when we load the data we do not need to worry about column names, only that there are an equal number of columns
		DECLARE @columnNames AS varchar(max)
		SET @columnNames = ''

		DECLARE @columnCounter as int
		SET @columnCounter = 1

		WHILE @columnCounter <= @columnCount
		BEGIN
				/* Mod:  4/5/2023: do not add "," when at last item */
				IF @columnCounter < @columnCount
				BEGIN
					SET @columnNames = @columnNames + 'C' + CAST(@columnCounter AS varchar(3)) + ' nvarchar(max), '
				END
				ELSE
				BEGIN
					SET @columnNames = @columnNames + 'C' + CAST(@columnCounter AS varchar(3)) + ' nvarchar(max) '
				END
				SET @columnCounter = @columnCounter + 1
		END

		--debug: select @columnNames

		--create the create table syntax for the stage table
		DECLARE @execCreateTableStatement AS nvarchar(max)
		SET @execCreateTableStatement = 'CREATE TABLE [dbo].[DATA_LOAD_STAGE] ( ' + @columnNames + ')'

		--debug: PRINT @execCreateTableStatement

		--Create our stage table to load data into. We recreate the table for each table load.
		IF EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME LIKE 'DATA_LOAD_STAGE')
		BEGIN
			DROP TABLE [dbo].[DATA_LOAD_STAGE] 
		END 

		--debug: PRINT 'File Name: ' + @fileName
		--debug: PRINT 'Create Table Statement: ' + @execCreateTableStatement
		EXECUTE sp_executesql @execCreateTableStatement


		--load the data into the stage table
		DECLARE @execLoadStageTableStatement AS nvarchar(max)
		/* Mod: 4/5/2023 - replace with Azure Blob storage reference */
		--SET @execLoadStageTableStatement = 'BULK INSERT [dbo].[DATA_LOAD_STAGE] FROM ''' + @pathName + @fileName + ''' WITH (FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''0x0A'', FIRSTROW = 2)'
		SET @execLoadStageTableStatement = 'BULK INSERT [dbo].[DATA_LOAD_STAGE] FROM '''  +@fileName + ''' WITH ( DATA_SOURCE =''RAJStorage'', FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''0x0A'', FIRSTROW = 2)'
		
		debug: PRINT 'Load Stage Table Statememnt: ' + @execLoadStageTableStatement
		EXECUTE sp_executesql @execLoadStageTableStatement

		--debug: select * from [dbo].[DATA_LOAD_STAGE]

		-- build the column statement for loading from the stage table to the final target table
		SET @columnCounter = 1
		DECLARE @columnString AS varchar(max)
		SET @columnString = ''
		DECLARE @dataType AS varchar(50)
		DECLARE @dataLength AS varchar(50)

		WHILE @columnCounter <= @columnCount
		BEGIN

			SET @columnNames = 'C' + CAST(@columnCounter AS varchar(3)) 
			SET @dataType = (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND ORDINAL_POSITION = @columnCounter)
			SET @dataLength = (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND ORDINAL_POSITION = @columnCounter)
	 
			SET @columnString = @columnString + ' ''' + @columnNames + ''' = '   
							+ 'CAST( (CASE WHEN ' + @columnNames + ' = ''\N'' THEN NULL ELSE ' + @columnNames + ' END) AS '										
							+ CAST(@dataType AS varchar(50))
							+ (CASE WHEN @dataType = 'varchar' THEN (CASE WHEN @dataLength = '-1' THEN '(max)' ELSE '('+ @dataLength +')' END ) ELSE '' END)
							+ ' ) ,'			
 
			SET @columnCounter = @columnCounter + 1
		END

		--remove the trailing comma on the columns statement
		SET @columnString = SUBSTRING( @columnString, 1, LEN(@columnString) - 1)

		--debug: PRINT 'Column String: ' +  @columnString

		--build the insert string		
		DECLARE @execTruncatStatement AS nvarchar(100)
		SET @execTruncatStatement = 'TRUNCATE TABLE ' + @tableName
		
		DECLARE @execInsertIntoStatemenet AS nvarchar(max)
		SET @execInsertIntoStatemenet = 'INSERT INTO [dbo].[' + @tableName + '] SELECT ' + @columnString + ' FROM [dbo].[DATA_LOAD_STAGE]'

		--debug: PRINT 'Truncate Statement: ' + @execTruncatStatement
		--debug: 
		PRINT 'Execute Insert Statement: ' + @execInsertIntoStatemenet
		EXECUTE sp_executesql @execTruncatStatement
		EXECUTE sp_executesql @execInsertIntoStatemenet

		FETCH NEXT FROM files_cursor
		INTO @fileName
	END

	CLOSE files_cursor
	DEALLOCATE files_cursor

END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID(N'dbo.LoadCanvasAnalytics') IS NOT NULL
    PRINT N'<<< CREATED PROCEDURE dbo.LoadCanvasAnalytics >>>'
ELSE
    PRINT N'<<< FAILED CREATING PROCEDURE dbo.LoadCanvasAnalytics >>>'
go