/*
    This script relates to the following SQL Server Central Forum topic:
    Processing strings ( https://www.sqlservercentral.com/forums/topic/processing-strings )

    This script provides several tests for the [ParseCSV] SQLCLR Stored Procedure.

    A T-SQL installation script (no external DLL) containing only two Stored Procedures is located at:
    https://pastebin.com/aqsWiX1e

    The source code for the [ParseCSV] and [GarbageCollect] SQLCLR Stored Procedures is located at:
    https://pastebin.com/BY8F994R

    Date: 2016-04-11
    Version: 1.0.0

    For more functions like this, please visit: https://SQLsharp.com

    Stairway to SQLCLR series: https://www.sqlservercentral.com/stairways/stairway-to-sqlclr

    Copyright (c) 2016 Sql Quantum Leap. All rights reserved.
    https://SqlQuantumLeap.com
*/

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
SET NOCOUNT ON;
GO


USE [CSVParser];
GO

------------------------  BEGIN TEST #1 ------------------------------------
-- Simple functional test to make sure that various scenarios are properly handled:
-- 1) text-qualfied fields
-- 2) not all fields need to be text-qualified
-- 3) embedded text-qualifier
-- 4) embedded field delimiter
-- 5) embedded row delimiter

DECLARE @CSV NVARCHAR(MAX) = N'12,12,1231231,fgd,231
g,h,j,"w
ggg",y
"sdf,hhh",32,"dfg"",z""ghd",55,,5564
45
5,6,77
a,';

EXEC dbo.ParseCSV ',', @CSV;

------------------------  END TEST #1 ------------------------------------


------------------------  BEGIN TEST #2 ------------------------------------
USE [CSVParser];

-- FIRST, create the destinaton table.
-- Please note that all fields are NVARCHAR(MAX) because all result set
-- fields coming back from [ParseCSV] are NVARCHAR(MAX).

-- DROP TABLE [dbo].[Destination];
IF (OBJECT_ID(N'dbo.Destination') IS NULL)
BEGIN
	PRINT 'Creating table: Destination...';
	CREATE TABLE [dbo].[Destination](
		[object_id] NVARCHAR(MAX) NOT NULL,
		[definition] NVARCHAR(MAX) NULL,
		[uses_ansi_nulls] NVARCHAR(MAX) NULL,
		[uses_quoted_identifier] NVARCHAR(MAX) NULL,
		[is_schema_bound] NVARCHAR(MAX) NULL,
		[uses_database_collation] NVARCHAR(MAX) NULL,
		[is_recompiled] NVARCHAR(MAX) NULL,
		[null_on_null_input] NVARCHAR(MAX) NULL,
		[execute_as_principal_id] NVARCHAR(MAX) NULL,
		[uses_native_compilation] NVARCHAR(MAX) NULL,
		[name] NVARCHAR(MAX) NOT NULL,
		[principal_id] NVARCHAR(MAX) NULL,
		[schema_id] NVARCHAR(MAX) NOT NULL,
		[parent_object_id] NVARCHAR(MAX) NOT NULL,
		[type] NVARCHAR(MAX) NOT NULL,
		[type_desc] NVARCHAR(MAX) NULL,
		[create_date] NVARCHAR(MAX) NOT NULL,
		[modify_date] NVARCHAR(MAX) NOT NULL,
		[is_ms_shipped] NVARCHAR(MAX) NULL,
		[is_published] NVARCHAR(MAX) NULL,
		[is_schema_published] NVARCHAR(MAX) NULL,
		[extra] VARCHAR(MAX) NULL
	) ON [UserData] TEXTIMAGE_ON [UserData];
END;



-- SECOND, generate the test CSV file. The following query should produce about
-- 80 MB of result data (there is slight variation due to the last column being
-- a random number of repetitions per row of a GUID, and not all systems have
-- the same number of entries in the two tables used in the following query.
--
-- On my install of SQL Server 2012, SP2 the following query generated 185,288
-- rows. I adjust the "207" value in the REPLACE on the top SELECT line to get
-- the output to be almost exactly 80 MB. Without the REPLACE function, the same
-- number of rows produced a file that was 674 MB. I would suggest not going over
-- 100 MB for the file since the test uses the INSERT...EXEC construct which
-- stores the results in memory until the EXEC call finishes, before it starts
-- inserting those results into the table.
--
-- To generate the file:
-- 1) Run the query using Results to Grid (the default setting unless you changed it).
-- 2) Right-click in the grid and select "Save Results As...".
-- 3) "Save as type" should be set to "CSV (Comma delimited) (*.csv)"
-- 4) Choose a path and enter in a name (path should be easily accessible; "C:\" is
--    usually restricted; I create a "C:\TEMP" folder).
-- 5) Click the "Save" button.
-- 6) In File Explorer, go to the path you just saved the file in, right-click on the
--    file, and select "Properties" (bottom option).
-- 7) Make a note of the "Size" value, not the "Size on disk" value.

SELECT sasm.[object_id], '"' + REPLACE(LEFT(sasm.[definition], 207), N'"', N'""') + '"' AS [definition],
       sasm.[uses_ansi_nulls], sasm.[uses_quoted_identifier], sasm.[is_schema_bound],
       sasm.[uses_database_collation], sasm.[is_recompiled], sasm.[null_on_null_input],
       sasm.[execute_as_principal_id], CONVERT(BIT, 0) AS [uses_native_compilation],
       sao.[name], sao.[principal_id], sao.[schema_id], sao.[parent_object_id], sao.[type], sao.[type_desc],
       sao.[create_date], sao.[modify_date], sao.[is_ms_shipped], sao.[is_published], sao.[is_schema_published],
       '"' + REPLICATE(NEWID(), (CRYPT_GEN_RANDOM(1) % 5) + 1) + '"' AS [extra]
FROM   msdb.[sys].[all_sql_modules] sasm
INNER JOIN  msdb.[sys].[all_objects] sao
        ON  sao.[object_id] = sasm.[object_id]
CROSS JOIN  msdb.sys.views;
-- 185,288 rows / 80.0 MB (83,936,807 bytes)



-- THIRD, run the SQLCLR stored proc once with simple input to
-- ensure that the App Domain has been created and that the Assembly
-- has been loaded. This initialization sometimes takes a second or
-- two and we don't want that time throwing off the test results.
EXEC dbo.ParseCSV @Delimiter = N',', @InputString = N'1,"2a,2b,2c",3';



-- FOURTH, make sure that the @FilePath parameter has the correct path
-- and file name for the CSV file that you created in the second step.

-- TRUNCATE TABLE dbo.Destination;
INSERT INTO dbo.Destination ([object_id], [definition], uses_ansi_nulls, uses_quoted_identifier,
                             is_schema_bound, uses_database_collation, is_recompiled, null_on_null_input,
                             execute_as_principal_id, uses_native_compilation, name, principal_id,
                             [schema_id], parent_object_id, [type], type_desc, create_date, modify_date,
                             is_ms_shipped, is_published, is_schema_published, extra)
  EXEC dbo.ParseCSV
          @Delimiter = N',',
          @InputString = NULL, -- default value not allowed so specify empty or NULL
          @FilePath = N'C:\TEMP\TestImportData.csv'; -- replace this value with your path + filename



-- FIFTH, check to make sure that all of the data got loaded. NULL values will import as the word
-- NULL instead of being an actual NULL, but this is just a simple test and that inaccuracy does
-- not affect the timing.
SELECT * FROM dbo.Destination;



-- SIXTH, run again, just to not rely on a single timing. Before we can re-run, however, we
-- should clear out the Destination table:
CHECKPOINT;
TRUNCATE TABLE dbo.Destination;
CHECKPOINT;
-- now go back and repeat the THIRD through FIFTH steps



-- EXEC dbo.GarbageCollect;
-- DBCC DROPCLEANBUFFERS
-- DBCC FREESYSTEMCACHE('ALL')
-- CHECKPOINT;

------------------------  END TEST #2 ------------------------------------