Esempio n. 1
0
        /// <summary>
        /// Execute Next Script
        /// </summary>
        /// <returns>DateTime and comments from next script</returns>
        public string ExecuteNextScript()
        {
            try {
                DatabaseScript nextScript = NextScript();

                // execute next script
                string returnMessage = string.Empty;
                if (nextScript != null)
                {
                    returnMessage += DateTime.UtcNow + " " + "Script Executed" + " " + "( " + Execute(nextScript, haltOnException: true) + " )" + "\r\n";

                    if (IsVersionTableGood())   // make sure table exists
                    {
                        returnMessage += DateTime.UtcNow + " " + DefaultVersion(nextScript.DatabaseVersion) + "\r\n";
                    }
                }
                else
                {
                    returnMessage = DateTime.UtcNow + " " + "Database is up to date\r\n";
                }

                return(returnMessage);
            } catch (Exception ex) {
                throw new Exception("Failed to upgrade database to next script", ex);
            }
        }
Esempio n. 2
0
        /// <summary>
        /// Execute a script
        /// </summary>
        /// <param name="databaseScript">Database Script to execute</param>
        /// <param name="haltOnException">Halt if exception is caught</param>
        /// <returns>Comments from scripts</returns>
        public string Execute(
            DatabaseScript databaseScript,
            bool haltOnException = false
            )
        {
            if (databaseScript.Name.Equals("CreationCheckmark"))
            {
                return(string.Empty);   // do not execute this script
            }

            try {
                if (!Execute(
                        databaseScript.DatabaseScriptBatch.Script,
                        databaseScript.DatabaseScriptBatch.BreakUpToBatches
                        ))
                {
                    return("Failed");
                }
            } catch (System.Exception ex) {
                if (haltOnException)
                {
                    throw new Exception("Failed to execute script; " + databaseScript.Name, ex);
                }

                if (ex.HResult == -2147467261)
                {
                    return("Exception; No database script to execute");
                }
                else
                {
                    return
                        ("Script Name: " + databaseScript.Name + "\r\n" + "\r\n"
                         + "Exception: " + ExtractException(ex));
                }
            }

            return(databaseScript.Description);
        }
Esempio n. 3
0
        /// <summary>
        /// Place holder for new script
        /// </summary>
        /// <param name="majorNumber">Major number for version</param>
        /// <param name="minorNumber">Minor number for version</param>
        /// <returns>Database Version script</returns>
        private DatabaseVersion InitBlank(
            int majorNumber,
            int minorNumber
            )
        {
            DatabaseVersion databaseVersion = new DatabaseVersion {
                MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = 0, DateTime = new DateTime(2018, 03, 10)
            };

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion     = databaseVersion.SequenceIncrease,
                ScriptNumber        = scriptNumber++,
                Name                = "",
                Description         = "",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
                        ",
                    BreakUpToBatches = true
                }
            });

            return(databaseVersion);
        }
Esempio n. 4
0
        void InitVersioning(
            int majorNumber,
            int minorNumber
            )
        {
            int sequence = 0;

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = defaultVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 01, 21)
                },
                ScriptNumber = this.scriptNumber++,
                Name         = "Versioning",
                Description  = "Database Versioning",
                #region script ( default_version )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
                            if object_id (N'default_version') is not null
                                drop table default_version
                            go
                            create table default_version (
	                            default_version_id uniqueidentifier not null primary key clustered (default_version_id asc),
	                            fun_version varchar(20) not null,
	                            number varchar(20) not null,
	                            major_number int not null,
	                            minor_number int not null,
	                            sequence_number int not null,
	                            default_user_id uniqueidentifier null ,
	                            date_time datetime not null 
                                )
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2016, 01, 01)
                },
                ScriptNumber = this.scriptNumber++,
                Name         = "Default",
                Description  = "Part of Default sql2x Tables",
                #region script ( default_user, default_state_ref )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
                            if object_id (N'default_user') is not null
                                drop table default_user 
                            go
                            if object_id (N'default_state_ref') is not null
                                drop table default_state_ref 
                            go
                            create table default_state_ref (
                                default_state_rcd  varchar ( 20 )  not null ,
                                default_state_name  nvarchar ( 240 )  not null ,
                                default_user_id  uniqueidentifier  null ,
                                date_time  datetime  not null ,
                                constraint  xpkdefault_state_ref primary key   clustered ( default_state_rcd  asc )
                            )
                            go
                            -- default_state_ref
                            insert into default_state_ref( default_state_rcd, default_state_name, date_time)
                                values ( 'C' , 'Created' , getutcdate () );

                            insert into default_state_ref ( default_state_rcd , default_state_name , date_time )
                                values ( 'U' , 'Updated' , getutcdate () );

                            insert into default_state_ref ( default_state_rcd , default_state_name , date_time )
                                values ( 'I' , 'Invalidated' , getutcdate () );
                            go
                            create table default_user (
                                default_user_id  uniqueidentifier  not null ,
                                default_user_code  varchar ( 20 )  not null ,
                                default_user_name  nvarchar ( 240 )  not null ,
                                email  nvarchar ( 240 )  null ,
                                password  nvarchar ( 240 )  null ,
                                default_state_rcd  varchar ( 20 )  not null ,
                                created_by_default_user_id  uniqueidentifier  null ,
                                date_time  datetime  not null ,
                                last_activity_date_time  datetime  null ,
                                constraint  xpkdefault_user primary key   clustered ( default_user_id  asc ) ,
                                constraint  default_user_to_created_by_default_user foreign key ( created_by_default_user_id ) references default_user ( default_user_id ) ,
                                constraint  default_user_to_default_state foreign key ( default_state_rcd ) references default_state_ref ( default_state_rcd )
                            )
                            go

                            -- default_user
                            insert into default_user (default_user_id, default_user_code, default_user_name, default_state_rcd, created_by_default_user_id, date_time) 
	                            values ('{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', 'SUX', 'System user X', 'C', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
                            go
                            if object_id ( N'dbo.ref' ) is not null begin
                                drop function dbo.ref
                            end
                            go
                            create function dbo.ref (@ref_code nvarchar(240)) 
                            returns nvarchar(240) as begin
                                return right ( @ref_code , len ( @ref_code ) - charindex ( '.' , @ref_code ) )
                            end
                            go
                            if object_id (N'get_defaults') is not null begin
	                            drop procedure get_defaults
                            end
                            go
                            create procedure get_defaults (@user_id uniqueidentifier out) as begin
	                            -- base
	                            set @user_id = '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}'
                            end
                            go
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });
        }
Esempio n. 5
0
        void InitIpsum(
            int majorNumber,
            int minorNumber
            )
        {
            int sequence = 0;

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = this.scriptNumber++,
                Name         = "IpsumCatalog",
                Description  = "Ipsum Catalog",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
set nocount on
go
if object_id('dbo.getNewID') is not null
	drop view dbo.getNewID
go
create view dbo.getNewID as select newid() as new_id
go
if object_id('dbo.random_color') is not null
	drop function dbo.random_color
go
create function dbo.random_color()
returns nvarchar(240) as begin
	declare @color varchar(240)
	set @color = (
		SELECT top 1 color
		FROM (
			select 'MediumPurple' as color 
			union select 'MediumSeaGreen' union select 'MediumSlateBlue' union select 'MediumSpringGreen' union select 'MediumTurquoise' union select 'MediumVioletRed' union select 'MidnightBlue' union select 'MediumOrchid' union select 'MintCream' union select 'Moccasin' union select 'NavajoWhite' union select 'Navy' union select 'OldLace' union select 'Olive' union select 'OliveDrab' union select 'Orange' union select 'MistyRose' union select 'OrangeRed' union select 'MediumBlue' union select 'Maroon' union select 'LightBlue' union select 'LightCoral' union select 'LightGoldenrodYellow' union select 'LightGreen' union select 'LightGray' union select 'LightPink' union select 'LightSalmon' union select 'MediumAquamarine' union select 'LightSeaGreen' union select 'LightSlateGray' union select 'LightSteelBlue' union select 'LightYellow' union select 'Lime' union select 'LimeGreen' union select 'Linen' union select 'Magenta' union select 'LightSkyBlue' union select 'LemonChiffon' union select 'Orchid' union select 'PaleGreen' union select 'SlateBlue' union select 'SlateGray' union select 'Snow' union select 'SpringGreen' union select 'SteelBlue' union select 'Tan' union select 'Teal' union select 'SkyBlue' union select 'Thistle' union select 'Turquoise' union select 'Violet' union select 'Wheat' union select 'White' union select 'WhiteSmoke' union select 'Yellow' union select 'YellowGreen' union select 'Tomato' union select 'PaleGoldenrod' union select 'Silver' union select 'SeaShell' union select 'PaleTurquoise' union select 'PaleVioletRed' union select 'PapayaWhip' union select 'PeachPuff' union select 'Peru' union select 'Pink' union select 'Plum' union select 'Sienna' union select 'PowderBlue' union select 'Red' union select 'RosyBrown' union select 'RoyalBlue' union select 'SaddleBrown' union select 'Salmon' union select 'SandyBrown' union select 'SeaGreen' union select 'Purple' union select 'LawnGreen' union select 'LightCyan' union select 'Lavender' union select 'DarkKhaki' union select 'DarkGreen' union select 'DarkGray' union select 'DarkGoldenrod' union select 'DarkCyan' union select 'DarkBlue' union select 'Cyan' union select 'Crimson' union select 'Cornsilk' union select 'LavenderBlush' union select 'Coral' union select 'Chocolate' union select 'Chartreuse' union select 'DarkMagenta' union select 'CadetBlue' union select 'Brown' union select 'BlueViolet' union select 'Blue' union select 'BlanchedAlmond' union select 'Black' union select 'Bisque' union select 'Beige' union select 'Azure' union select 'Aquamarine' union select 'Aqua' union select 'AntiqueWhite' union select 'AliceBlue' union select 'Transparent' union select 'BurlyWood' union select 'DarkOliveGreen' union select 'CornflowerBlue' union select 'DarkOrchid' union select 'Khaki' union select 'Ivory' union select 'DarkOrange' union select 'Indigo' union select 'IndianRed' union select 'HotPink' union select 'Honeydew' union select 'GreenYellow' union select 'Green' union select 'Gray' union select 'Goldenrod' union select 'GhostWhite' union select 'Gainsboro' union select 'Fuchsia' union select 'Gold' union select 'FloralWhite' union select 'DarkRed' union select 'DarkSalmon' union select 'DarkSeaGreen' union select 'ForestGreen' union select 'DarkSlateGray' union select 'DarkTurquoise' union select 'DarkSlateBlue' union select 'DeepPink' union select 'DeepSkyBlue' union select 'DimGray' union select 'DodgerBlue' union select 'Firebrick' union select 'DarkViolet'
			) as color
		ORDER BY (select * from getNewID)
		)

		return @color
end
go
if object_id('dbo.random_period') is not null
	drop function dbo.random_period
go
create function dbo.random_period()
returns nvarchar(240) as begin
	declare @period varchar(240)
	set @period = (
		SELECT top 1 period
		FROM (
			select 'Winter' as period 
			union select 'Summer' union select 'Spring' union select 'Fall'
			) as period
		ORDER BY (select * from getNewID)
		)

		return @period
end
go
if object_id('dbo.random_latin') is not null
	drop function dbo.random_latin
go
create function dbo.random_latin()
returns nvarchar(2400) as begin
	-- not so random latin
	return 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.'
end
go
if object_id('product_info') is not null
    delete product_info
if object_id('product_category_mapping') is not null
    delete product_category_mapping
if object_id('cart_product') is not null
    delete cart_product
if object_id('financial_order_line') is not null
    delete financial_order_line
if object_id('product_attribute') is not null
    delete product_attribute
if object_id('product_identifier') is not null
    delete product_identifier
if object_id('product_change_set') is not null
    delete product_change_set
if object_id('product_expose') is not null
    delete product_expose
if object_id('product_expose_plan') is not null
    delete product_expose_plan
if object_id('product_documentation') is not null
    delete product_documentation
if object_id('product_image') is not null
    delete product_image
if object_id('product') is not null
    delete product
go
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = this.scriptNumber++,
                Name         = "IpsumLast",
                Description  = "IpsumStop",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
set quoted_identifier on
go
set nocount on
go
if object_id('dbo.random_latin') is not null
	drop function dbo.random_latin
go
create function dbo.random_latin()
returns nvarchar(2400) as begin
	-- not so random latin
	return 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.'
end
go
if object_id('dbo.ipsum') is not null
	drop function dbo.ipsum
go
create function dbo.ipsum(@rand float)
returns nvarchar(2400) as begin
	declare 
		@lorem nvarchar(max)
		,@pos int
		,@pos2 int
	
	select
	--    @lorem = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.'
		@lorem = 'Lorem ipsum dolor sit amet consectetur adipisicing elit sed do eiusmod tempor incididunt ut labore et dolore magna aliqua Ut enim ad minim veniam quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur Excepteur sint occaecat cupidatat non proident sunt in culpa qui officia deserunt mollit anim id est laborum'

	while 1=1 begin
		set @pos = cast(@rand * Len(@lorem) as int)
		set @pos = charindex(' ', @lorem, @pos)
		set @pos2 = charindex(' ', @lorem, @pos + 20)
		if @pos2 + 20 < len(@lorem) and @pos2 != 0 break
		set @rand = @rand * 0.1
	end

	return lower(substring(@lorem, @pos, @pos2 - @pos))
end
go
if object_id('dbo.product_insert') is not null
	drop procedure dbo.product_insert
go
create procedure product_insert(
	@product_category_parent_id_3 uniqueidentifier, 
	@chapter_text nvarchar(240), 
	@chapter_name_last nvarchar(240)) as begin

	-- products
	declare @product_count int = 0
	while @product_count < 10 begin
		declare @new_product_id uniqueidentifier set @new_product_id = newid();
		insert into product (product_id, product_name, state_rcd, user_id, date_time)
				values (@new_product_id, 
									' ( ' + @chapter_text + ' )'										+ ' ' + @chapter_name_last
									+ ' ' + cast(abs(checksum(newid())) % 14 + 1 as varchar)  + ' mm'	+ ' ' + dbo.random_color()
									+ ' ' + dbo.random_period(),
						'A', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate() );
		insert into product_category_mapping(	product_category_mapping_id,    product_category_id,            product_id,         user_id,                                    date_time				) values (
												newid(),                        @product_category_parent_id_3,  @new_product_id,	'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',   getutcdate()			)

		-- attributes
		insert into product_attribute (product_attribute_id, product_id, product_attribute_rcd, value, product_attribute_unit_rcd, user_id, date_time) 
			values (newid(), @new_product_id, dbo.ref('product_attribute_ref.Color'), 
			dbo.random_color(),	dbo.ref('product_attribute_unit_ref.Co'),														-- random color
			'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

		insert into product_attribute (product_attribute_id, product_id, product_attribute_rcd, value, product_attribute_unit_rcd, user_id, date_time) 
			values (newid(), @new_product_id, dbo.ref('product_attribute_ref.H'), 
			cast(abs(checksum(newid())) % 14 + 1 as varchar), dbo.ref('product_attribute_unit_ref.MM'),							-- random positive number and 'mm'
			'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

		insert into product_attribute (product_attribute_id, product_id, product_attribute_rcd, value, product_attribute_unit_rcd, user_id, date_time) 
			values (newid(), @new_product_id, dbo.ref('product_attribute_ref.L'), 
			cast(abs(checksum(newid())) % 14 + 1 as varchar), dbo.ref('product_attribute_unit_ref.MM'),							-- random positive number and 'mm'
			'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

		insert into product_attribute (product_attribute_id, product_id, product_attribute_rcd, value, product_attribute_unit_rcd, user_id, date_time) 
			values (newid(), @new_product_id, dbo.ref('product_attribute_ref.RKM'), 
			cast(abs(checksum(newid())) % 14 + 1 as varchar), dbo.ref('product_attribute_unit_rcd.KM'),                         -- random positive number
			'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

		insert into product_identifier (product_identifier_id, product_id, product_identifier_rcd, identifier, user_id, date_time)
			values (newid(), @new_product_id, dbo.ref('product_identifier_ref.GTIN13'), 
			left(replace(CAST(CAST(CRYPT_GEN_RANDOM(8) AS bigint) as varchar), '-', ''), 13),	-- random 13 char long positive number
			'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

		insert into product_identifier (product_identifier_id, product_id, product_identifier_rcd, identifier, user_id, date_time)
			values (newid(), @new_product_id, dbo.ref('product_identifier_ref.GTIN14'), 
			left(replace(CAST(CAST(CRYPT_GEN_RANDOM(8) AS bigint) as varchar), '-', ''), 14),	-- random 14 char long positive number
			'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

		insert into product_info (product_info_id, product_id, product_info_rcd, product_info_value, user_id, date_time)
			values (newid(), @new_product_id, dbo.ref('product_info_ref.PI'), 
			left(replace(CAST(CAST(CRYPT_GEN_RANDOM(8) AS bigint) as varchar), '-', ''), 14),	-- random 14 char long positive number
			'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

		insert into product_documentation (product_documentation_id, product_id, product_documentation_type_rcd, documentation, user_id, date_time)
			values (newid(), @new_product_id, dbo.ref('product_documentation_type_ref.HD'), 
			dbo.random_latin(),																	-- not so random latin
			'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

		set @product_count = @product_count + 1
	end
end
go
delete product_category_documentation
go
delete from product_category_mapping
go
delete from product_category_image
go
delete from product_category
go
--delete from product
go
declare @level_1 int = 01
while @level_1 < 15 begin
	declare @chapter_text char(6) = '000000'

	-- level 1
	set @chapter_text = right('00' + cast(@level_1 as varchar(2)), 2) + '0000'
	declare @product_category_parent_id uniqueidentifier = newid()

	insert into product_category (product_category_id, product_category_parent_id, product_category_code, product_category_name,	product_category_position, state_rcd, user_id, date_time ) 
	values ( @product_category_parent_id, @product_category_parent_id,						@chapter_text, @chapter_text + ' - ' + dbo.ipsum(rand())		, 1, 'C', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()	)

	-- level 2
	declare @level_2 int = 1
	while @level_2 < 3 begin
		set @chapter_text = right('00' + cast(@level_1 as varchar(2)), 2) + right('00' + cast(@level_2 as varchar(2)), 2) + '00'
		declare @product_category_parent_id_2 uniqueidentifier = newid()

		insert into product_category (product_category_id, product_category_parent_id, product_category_code, product_category_name,	product_category_position, state_rcd, user_id, date_time ) 
		values ( @product_category_parent_id_2, @product_category_parent_id,						@chapter_text, @chapter_text + ' - ' + dbo.ipsum(rand())		, 1, 'C', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()	)

		-- level 3
		declare @level_3 int = 1
		while @level_3 < 6 begin
			set @chapter_text = right('00' + cast(@level_1 as varchar(2)), 2) + right('00' + cast(@level_2 as varchar(2)), 2) + right('00' + cast(@level_3 as varchar(2)), 2)
			declare @product_category_parent_id_3 uniqueidentifier = newid()

			declare @chapter_name_last nvarchar(240) = dbo.ipsum(rand())

			insert into product_category (product_category_id, product_category_parent_id, product_category_code, product_category_name,	product_category_position, state_rcd, user_id, date_time ) 
			values ( @product_category_parent_id_3, @product_category_parent_id_2,						@chapter_text, @chapter_text + ' - ' + @chapter_name_last		, 1, 'C', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()	)

			exec product_insert @product_category_parent_id_3, @chapter_text, @chapter_name_last

			set @level_3 = @level_3 + 1
		end
		set @level_2 = @level_2 + 1
	end
	set @level_1 = @level_1 + 1
end
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });
        }
Esempio n. 6
0
        /// <summary>
        /// Delete all objects in the database
        /// </summary>
        /// <param name="majorNumber">Major Version number of this script</param>
        /// <param name="minorNumber">Minor Version number of this script</param>
        public void InitDatabaseClean(
            int majorNumber,
            int minorNumber
            )
        {
            int sequence = 0;

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22)
                },
                ScriptNumber        = this.scriptNumber++,
                Name                = "DatabaseCleanScript1",
                Description         = "Clear database script ( Drop all non-system stored procs )",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script =
                        @"/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END",
                    BreakUpToBatches = false
                }
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22)
                },
                ScriptNumber        = this.scriptNumber++,
                Name                = "DatabaseCleanScript2",
                Description         = "Clear database script ( Drop all views )",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script =
                        @"/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
",
                    BreakUpToBatches = false
                }
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22)
                },
                ScriptNumber        = this.scriptNumber++,
                Name                = "DatabaseCleanScript3",
                Description         = "Clear database script ( Drop all functions )",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script =
                        @"/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
",
                    BreakUpToBatches = false
                }
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22)
                },
                ScriptNumber        = this.scriptNumber++,
                Name                = "DatabaseCleanScript4",
                Description         = "Clear database script ( Drop all Foreign Key constraints )",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script =
                        @"/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
",
                    BreakUpToBatches = false
                }
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22)
                },
                ScriptNumber        = this.scriptNumber++,
                Name                = "DatabaseCleanScript5",
                Description         = "Clear database script ( Drop all Primary Key constraints )",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script =
                        @"/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
",
                    BreakUpToBatches = false
                }
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22)
                },
                ScriptNumber        = this.scriptNumber++,
                Name                = "DatabaseCleanScript6",
                Description         = "Clear database script ( Drop all tables )",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script =
                        @"/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
",
                    BreakUpToBatches = false
                }
            });
        }
Esempio n. 7
0
        public void InitJSONClient(
            int majorNumber,
            int minorNumber
            )
        {
            int sequence = 0;

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22)
                },
                ScriptNumber        = scriptNumber++,
                Name                = "ClientSchema",
                Description         = "Client Schema",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
                            /*
                              SQL2X Generated code based on a SQL Server Schema
                              SQL2X Version: 0.d
                              http://sql2core.azurewebsites.net/
                              Generated Date: 21.01.2018 18:54:14
                              Template: sql2x.GenerateSchema.Generate
                            */
                            if object_id (N'client_link') is not null
                                drop table client_link 
                            go
                            if object_id (N'client_identifier') is not null
                                drop table client_identifier 
                            go
                            if object_id (N'client_event') is not null
                                drop table client_event 
                            go
                            if object_id (N'client_document') is not null
                                drop table client_document 
                            go
                            if object_id (N'client_contact_method') is not null
                                drop table client_contact_method 
                            go
                            if object_id (N'client_link_type_ref') is not null
                                drop table client_link_type_ref 
                            go
                            if object_id (N'client_identifier_type_ref') is not null
                                drop table client_identifier_type_ref 
                            go
                            if object_id (N'client_event_type_ref') is not null
                                drop table client_event_type_ref 
                            go
                            if object_id (N'client_document_type_ref') is not null
                                drop table client_document_type_ref 
                            go
                            if object_id (N'client_contact_method_ref') is not null
                                drop table client_contact_method_ref 
                            go
                            if object_id (N'client') is not null
                                drop table client 
                            go
                            if object_id (N'client_address') is not null
                                drop table client_address 
                            go
                            if object_id (N'client_type_ref') is not null
                                drop table client_type_ref 
                            go
                            if object_id (N'client_nationality_ref') is not null
                                drop table client_nationality_ref 
                            go
                            if object_id (N'client_gender_ref') is not null
                                drop table client_gender_ref 
                            go
                            if object_id (N'client_title_ref') is not null
                                drop table client_title_ref 
                            go
                            if object_id (N'client_address_type_ref') is not null
                                drop table client_address_type_ref 
                            go
                            set nocount on
                            go
                            create table client_type_ref (
                                client_type_rcd varchar(20) not null,
                                client_type_name nvarchar(240) not null,
                                client_type_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_type_ref
                                    primary key clustered (client_type_rcd asc)
                            )
                            go
                            insert into client_type_ref(client_type_rcd, client_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('PRA', 'Private', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_type_ref(client_type_rcd, client_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('BUA', 'Business', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_nationality_ref (
                                client_nationality_rcd varchar(20) not null,
                                client_nationality_name nvarchar(240) not null,
                                client_nationality_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_nationality_ref
                                    primary key clustered (client_nationality_rcd asc)
                            )
                            go
                            insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time)
                             values ('AFA', 'Africa', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time)
                             values ('AMA', 'Americas', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time)
                             values ('ASA', 'Asia', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time)
                             values ('EUA', 'Europe', 4, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time)
                             values ('OCA', 'Oceania', 5, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_gender_ref (
                                client_gender_rcd varchar(20) not null,
                                client_gender_name nvarchar(240) not null,
                                client_gender_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_gender_ref
                                    primary key clustered (client_gender_rcd asc)
                            )
                            go
                            insert into client_gender_ref(client_gender_rcd, client_gender_name, sort_order, active_flag, [user_id], date_time)
                             values ('MAA', 'Male', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_gender_ref(client_gender_rcd, client_gender_name, sort_order, active_flag, [user_id], date_time)
                             values ('FEA', 'Female', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_gender_ref(client_gender_rcd, client_gender_name, sort_order, active_flag, [user_id], date_time)
                             values ('UNA', 'Unknown', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_title_ref (
                                client_title_rcd varchar(20) not null,
                                client_title_name nvarchar(240) not null,
                                client_title_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_title_ref
                                    primary key clustered (client_title_rcd asc)
                            )
                            go
                            insert into client_title_ref(client_title_rcd, client_title_name, sort_order, active_flag, [user_id], date_time)
                             values ('MR', 'Mr', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_title_ref(client_title_rcd, client_title_name, sort_order, active_flag, [user_id], date_time)
                             values ('MRS', 'Mrs', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_title_ref(client_title_rcd, client_title_name, sort_order, active_flag, [user_id], date_time)
                             values ('CHA', 'Child', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_title_ref(client_title_rcd, client_title_name, sort_order, active_flag, [user_id], date_time)
                             values ('INA', 'Infant', 4, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_link_type_ref (
                                client_link_type_rcd varchar(20) not null,
                                client_link_type_name nvarchar(240) not null,
                                client_link_type_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_link_type_ref
                                    primary key clustered (client_link_type_rcd asc)
                            )
                            go
                            insert into client_link_type_ref(client_link_type_rcd, client_link_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('WPE', 'Web Page', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_identifier_type_ref (
                                client_identifier_type_rcd varchar(20) not null,
                                client_identifier_type_name nvarchar(240) not null,
                                client_identifier_type_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_identifier_type_ref
                                    primary key clustered (client_identifier_type_rcd asc)
                            )
                            go
                            insert into client_identifier_type_ref(client_identifier_type_rcd, client_identifier_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('PNR', 'Passport Number', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_identifier_type_ref(client_identifier_type_rcd, client_identifier_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('ENR', 'Employee Number', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_address_type_ref (
                                client_address_type_rcd varchar(20) not null,
                                client_address_type_name nvarchar(240) not null,
                                client_address_type_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_address_type_ref
                                    primary key clustered (client_address_type_rcd asc)
                            )
                            go
                            insert into client_address_type_ref(client_address_type_rcd, client_address_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('PRA', 'Private', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_address_type_ref(client_address_type_rcd, client_address_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('OFA', 'Office', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_address_type_ref(client_address_type_rcd, client_address_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('TEA', 'Temporary', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_event_type_ref (
                                client_event_type_rcd varchar(20) not null,
                                client_event_type_name nvarchar(240) not null,
                                client_event_type_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_event_type_ref
                                    primary key clustered (client_event_type_rcd asc)
                            )
                            go
                            insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('CRA', 'Created', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('INA', 'Inactivated', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('SIM', 'Sent Introduction Mail', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('IMC', 'Introduction Mail Confirmed', 4, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('PMS', 'Package Mail Sent', 5, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('PSV', 'Package Site Viewed', 6, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('POD', 'Package Ordered', 7, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_document_type_ref (
                                client_document_type_rcd varchar(20) not null,
                                client_document_type_name nvarchar(240) not null,
                                client_document_type_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_document_type_ref
                                    primary key clustered (client_document_type_rcd asc)
                            )
                            go
                            insert into client_document_type_ref(client_document_type_rcd, client_document_type_name, sort_order, active_flag, [user_id], date_time)
                             values ('PFP', 'Passport First Page', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_contact_method_ref (
                                client_contact_method_rcd varchar(20) not null,
                                client_contact_method_name nvarchar(240) not null,
                                client_contact_method_description nvarchar(240) null,
                                active_flag bit not null,
                                sort_order integer not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_contact_method_ref
                                    primary key clustered (client_contact_method_rcd asc)
                            )
                            go
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('EMA', 'EMail', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('TEA', 'Telephone', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('LPE', 'Local Phone', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('IPE', 'International Phone', 4, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('HPE', 'Home Phone', 5, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('BPE', 'Business Phone', 6, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('FAX', 'Fax', 7, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('MOA', 'Mobile', 8, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('SME', 'SMS Mobile', 9, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time)
                             values ('MME', 'MMS Mobile', 10, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate())
                            go
                            create table client_address (
                                client_address_id uniqueidentifier not null,
                                client_id uniqueidentifier null,
                                client_address_type_rcd varchar(20) not null,
                                address_line_one_name nvarchar(240) not null,
                                address_line_two_name nvarchar(240) not null,
                                address_line_three_name nvarchar(240) not null,
                                city_name nvarchar(240) not null,
                                street_name nvarchar(240) not null,
                                state_name nvarchar(240) not null,
                                district_name nvarchar(240) not null,
                                province_name nvarchar(240) not null,
                                zip_code nvarchar(240) not null,
                                po_box nvarchar(240) not null,
                                [comment] nvarchar(240) not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_address
                                    primary key clustered (client_address_id asc),
                                constraint client_address_to_client_address_type_ref 
                                    foreign key (client_address_type_rcd)
                                    references client_address_type_ref(client_address_type_rcd)
                            )
                            go
                            create table client (
                                client_id uniqueidentifier not null,
                                client_type_rcd varchar(20) not null,
                                client_nationality_rcd varchar(20) null,
                                client_gender_rcd varchar(20) null,
                                client_title_rcd varchar(20) null,
                                client_address_id uniqueidentifier null,
                                first_name nvarchar(240) not null,
                                middle_name nvarchar(240) null,
                                last_name nvarchar(240) not null,
                                [image] varbinary(max) null,
                                image_blob_filename nvarchar(240) null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client
                                    primary key clustered (client_id asc),
                                constraint client_to_client_type_ref 
                                    foreign key (client_type_rcd)
                                    references client_type_ref(client_type_rcd),
                                constraint client_to_client_nationality_ref 
                                    foreign key (client_nationality_rcd)
                                    references client_nationality_ref(client_nationality_rcd),
                                constraint client_to_client_gender_ref 
                                    foreign key (client_gender_rcd)
                                    references client_gender_ref(client_gender_rcd),
                                constraint client_to_client_title_ref 
                                    foreign key (client_title_rcd)
                                    references client_title_ref(client_title_rcd),
                                constraint client_to_client_address 
                                    foreign key (client_address_id)
                                    references client_address(client_address_id)
                            )
                            go
                            create table client_link (
                                client_link_id uniqueidentifier not null,
                                client_id uniqueidentifier not null,
                                client_link_type_rcd varchar(20) not null,
                                link_name nvarchar(240) null,
                                link nvarchar(240) not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_link
                                    primary key clustered (client_link_id asc),
                                constraint client_link_to_client 
                                    foreign key (client_id)
                                    references client(client_id),
                                constraint client_link_to_client_link_type_ref 
                                    foreign key (client_link_type_rcd)
                                    references client_link_type_ref(client_link_type_rcd)
                            )
                            go
                            create table client_identifier (
                                client_identifier_id uniqueidentifier not null,
                                client_id uniqueidentifier not null,
                                client_identifier_type_rcd varchar(20) not null,
                                client_identifier_code varchar(20) not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_identifier
                                    primary key clustered (client_identifier_id asc),
                                constraint client_identifier_to_client 
                                    foreign key (client_id)
                                    references client(client_id),
                                constraint client_identifier_to_client_identifier_type_ref 
                                    foreign key (client_identifier_type_rcd)
                                    references client_identifier_type_ref(client_identifier_type_rcd)
                            )
                            go
                            create table client_event (
                                client_event_id uniqueidentifier not null,
                                client_id uniqueidentifier not null,
                                client_event_type_rcd varchar(20) not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_event
                                    primary key clustered (client_event_id asc),
                                constraint client_event_to_client 
                                    foreign key (client_id)
                                    references client(client_id),
                                constraint client_event_to_client_event_type_ref 
                                    foreign key (client_event_type_rcd)
                                    references client_event_type_ref(client_event_type_rcd)
                            )
                            go
                            create table client_document (
                                client_document_id uniqueidentifier not null,
                                client_id uniqueidentifier not null,
                                client_document_type_rcd varchar(20) not null,
                                document_name nvarchar(240) not null,
                                document_date_time datetime not null,
                                expiry_date_time datetime not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_document
                                    primary key clustered (client_document_id asc),
                                constraint client_document_to_client 
                                    foreign key (client_id)
                                    references client(client_id),
                                constraint client_document_to_client_document_type_ref 
                                    foreign key (client_document_type_rcd)
                                    references client_document_type_ref(client_document_type_rcd)
                            )
                            go
                            create table client_contact_method (
                                client_contact_method_id uniqueidentifier not null,
                                client_id uniqueidentifier not null,
                                client_contact_method_rcd varchar(20) not null,
                                contact_method_way nvarchar(240) not null,
                                [comment] text not null,
                                [user_id] uniqueidentifier not null,
                                date_time datetime not null,
                                constraint xpk_client_contact_method
                                    primary key clustered (client_contact_method_id asc),
                                constraint client_contact_method_to_client 
                                    foreign key (client_id)
                                    references client(client_id),
                                constraint client_contact_method_to_client_contact_method_ref 
                                    foreign key (client_contact_method_rcd)
                                    references client_contact_method_ref(client_contact_method_rcd)
                            )
                            ",
                    BreakUpToBatches = false
                }
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22)
                },
                ScriptNumber        = scriptNumber++,
                Name                = "ClientTestData",
                Description         = "Client Test Data",
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
                            -- default client uses default user 
                            declare @client_id uniqueidentifier = N'ffffffff-5555-5555-5555-ffffffffffff'
                            insert into client ( client_id, first_name, middle_name, last_name, client_nationality_rcd, client_gender_rcd, client_title_rcd, client_type_rcd, user_id, date_time)
			                            values (@client_id,	'Test', '', 'Data', dbo.ref('client_nationality_ref.OCA'), dbo.ref('client_gender_ref.MAA'), dbo.ref('client_title_ref.MR'), dbo.ref('client_type_ref.BUA'), '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
                            insert into client_contact_method ( client_contact_method_id, client_id, client_contact_method_rcd, contact_method_way, comment, user_id, date_time)
			                            values ( newid(), @client_id, dbo.ref('client_contact_method_ref.EMA'), '*****@*****.**', '', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
                            insert into client_contact_method ( client_contact_method_id, client_id, client_contact_method_rcd, contact_method_way, comment, user_id, date_time)
			                            values ( newid(), @client_id, dbo.ref('client_contact_method_ref.MOA'), '+1 987 654321', '', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
                            insert into client_link ( client_link_id, client_id, client_link_type_rcd, link, user_id, date_time)
			                            values ( newid(), @client_id, dbo.ref('client_link_type_ref.WPE'), 'www.google.com', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
                            insert into client_event ( client_event_id, client_id, client_event_type_rcd, user_id, date_time)
			                            values ( newid(), @client_id, dbo.ref('client_event_type_ref.CRA'), '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
                            go
                            declare @client_address_id uniqueidentifier = newid()
                            insert into client_address ( client_address_id, client_id, client_address_type_rcd,     address_line_one_name ,    address_line_two_name ,    address_line_three_name ,    city_name ,    street_name ,    state_name ,    district_name ,    province_name ,    zip_code ,    po_box ,    [comment] , user_id, date_time)
                               values (@client_address_id, @client_id, dbo.ref('client_address_type_ref.PRA'), 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', N'ffffffff-5555-5555-5555-ffffffffffff', getdate())
                            ",
                    BreakUpToBatches = false
                }
            });
        }
Esempio n. 8
0
        /// <summary>
        /// Init Database Manager
        /// </summary>
        /// <param name="databaseName">Name of Database to delete</param>
        /// <param name="toRemote">Use remote or local database</param>
        public DatabaseManager(
            string databaseName,
            bool toRemote
            )
        {
            if (string.IsNullOrEmpty(databaseName))
            {
                throw new ArgumentException("Missing Database Name", "databaseName");
            }

            try {
                this.databaseName = databaseName;

                // remote or local
                this.toRemote = toRemote;
                if (this.toRemote)
                {
                    connectionString = Conn.ConnectionStringRemote;
                }
                else
                {
                    connectionString = Conn.ConnectionStringLocal;

                    // check if database exist
                    if (!DatabaseExist(
                            connectionStringLocal: connectionString.Replace("Initial Catalog=NorSolutionPim;", ""),
                            databaseName: this.databaseName
                            ))
                    {
                        DatabaseCreate(
                            connectionStringLocal: connectionString.Replace("Initial Catalog=NorSolutionPim;", ""),
                            databaseName: this.databaseName
                            );

                        Console.WriteLine($"Created database {this.databaseName}");
                    }
                }

                int minorNumber = 0;
                int sequence    = 0;
                InitDatabaseClean(majorNumber: 0, minorNumber: minorNumber++);
                InitVersioning(majorNumber: 0, minorNumber: minorNumber++);
                InitJSONClient(majorNumber: 0, minorNumber: minorNumber++);
                InitSystemReferenceErrorAndIssue(majorNumber: 0, minorNumber: minorNumber++);
                InitDefaultUserActivity(majorNumber: 0, minorNumber: minorNumber++, sequence: out sequence);
                InitDefaultSystemSetting(majorNumber: 0, minorNumber: minorNumber++, sequence: ref sequence);
                InitDefaultChangeLog(majorNumber: 0, minorNumber: minorNumber++, sequence: ref sequence);
                InitDefaultRule(majorNumber: 0, minorNumber: minorNumber++, sequence: ref sequence);
                InitDefaultPerformanceIssue(majorNumber: 0, minorNumber: minorNumber++, sequence: ref sequence);
                InitFinancial(majorNumber: 0, minorNumber: minorNumber++);
                InitProduct(majorNumber: 0, minorNumber: minorNumber++);
                InitOAuth(majorNumber: 0, minorNumber: minorNumber++);
                InitIpsum(majorNumber: 0, minorNumber: minorNumber++);

                // creation checkmark, do not process scripts after this until creation is created
                scripts.Add(lastScript = new DatabaseScript {
                    DatabaseVersion = new DatabaseVersion {
                        MajorNumber = 0, MinorNumber = minorNumber++, SequenceNumber = sequence++, DateTime = new DateTime(2019, 02, 05)
                    },
                    ScriptNumber = scriptNumber++,
                    Name         = "CreationCheckmark"
                });

                // intentionally left blank to serve as a template and to get the last version number
                maximumVersion = InitBlank(majorNumber: 0, minorNumber: minorNumber++);
            } catch (System.Exception ex) {
                throw new System.Exception(
                          "Failed to Initialize database manager",
                          ex
                          );
            }
        }
Esempio n. 9
0
        /// <summary>
        /// Get Next Script from current version in database
        /// </summary>
        /// <returns>Next DatabaseScript Object</returns>
        public DatabaseScript NextScript()
        {
            try {
                // find current state
                string whyNot = string.Empty;
                if (!IsConnectionGood(out whyNot))
                {
                    throw new Exception($"Connection not good; {SanitizePassword(connectionString)}\r\n    Because: {whyNot}");
                }

                if (!IsDatabaseGood())
                {
                    throw new Exception("Database is not good; " + databaseName + " on connection; " + SanitizePassword(connectionString));
                }

                // get version
                DatabaseVersion databaseVersion = new DatabaseVersion();

                string returnMessage = string.Empty;
                if (!IsVersionTableGood())
                {
                    throw new Exception("Version table is not good on connection; " + SanitizePassword(connectionString));
                }

                databaseVersion = VersionTableLastEntry();

                // is code version and database version the same?
                if (maximumVersion == databaseVersion)
                {
                    return(null);
                }

                if (maximumVersion < databaseVersion)
                {
                    throw new Exception("Maxmimum version is less than the database version" + "\r\n");
                }

                // filter out scripts to execute
                List <DatabaseScript> scriptsFiltered =
                    scripts
                    .Where(s =>
                           s.DatabaseVersion > databaseVersion &&
                           s.DatabaseVersion <= maximumVersion)
                    .ToList();

                if (scriptsFiltered.Count == 0)
                {
                    return(null);
                }

                // find next script
                List <DatabaseScript> scriptOrdered =
                    scriptsFiltered
                    .OrderBy(o => o.DatabaseVersion.MajorNumber)
                    .ThenBy(o2 => o2.DatabaseVersion.MinorNumber)
                    .ThenBy(o3 => o3.DatabaseVersion.SequenceNumber)
                    .ToList();

                DatabaseScript nextScript = scriptOrdered.FirstOrDefault(); // https://codereview.stackexchange.com/questions/55340/update-first-and-last-item-in-listt

                return(nextScript);
            } catch (Exception ex) {
                throw new Exception("Failed to get next script", ex);
            }
        }
Esempio n. 10
0
        void InitOAuth(
            int majorNumber,
            int minorNumber
            )
        {
            int sequence = 0;

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = this.scriptNumber++,
                Name         = "OAuthDropTables",
                Description  = "OAuth Drop Tables",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
if object_id (N'AspNetUserClaims') is not null
    drop table AspNetUserClaims
if object_id (N'AspNetUserLogins') is not null
    drop table AspNetUserLogins
if object_id (N'AspNetUserRoles') is not null
    drop table AspNetUserRoles
if object_id (N'AspNetUsers') is not null
    drop table AspNetUsers
if object_id (N'AspNetRoles') is not null
    drop table AspNetRoles
if object_id (N'__MigrationHistory') is not null
    drop table __MigrationHistory
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = this.scriptNumber++,
                Name         = "OAuthCreateTables",
                Description  = "OAuth Create Tables",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
CREATE TABLE [dbo].[__MigrationHistory] (
    [MigrationId]    NVARCHAR (150)  NOT NULL,
    [ContextKey]     NVARCHAR (300)  NOT NULL,
    [Model]          VARBINARY (MAX) NOT NULL,
    [ProductVersion] NVARCHAR (32)   NOT NULL,
    CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED ([MigrationId] ASC, [ContextKey] ASC)
);
go
CREATE TABLE [dbo].[AspNetRoles] (
    [Id]   NVARCHAR (128) NOT NULL,
    [Name] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC)
);
go
CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex]
    ON [dbo].[AspNetRoles]([Name] ASC);
go
CREATE TABLE [dbo].[AspNetUserClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [UserId]     NVARCHAR (128) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC)
);
go
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserClaims]([UserId] ASC);
go
CREATE TABLE [dbo].[AspNetUserLogins] (
    [LoginProvider] NVARCHAR (128) NOT NULL,
    [ProviderKey]   NVARCHAR (128) NOT NULL,
    [UserId]        NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC, [UserId] ASC)
);
go
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserLogins]([UserId] ASC);
go
CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] NVARCHAR (128) NOT NULL,
    [RoleId] NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC)
);
go
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserRoles]([UserId] ASC);
go
CREATE NONCLUSTERED INDEX [IX_RoleId]
    ON [dbo].[AspNetUserRoles]([RoleId] ASC);
go
CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                   NVARCHAR (128) NOT NULL,
    [Email]                NVARCHAR (256) NULL,
    [EmailConfirmed]       BIT            NOT NULL,
    [PasswordHash]         NVARCHAR (MAX) NULL,
    [SecurityStamp]        NVARCHAR (MAX) NULL,
    [PhoneNumber]          NVARCHAR (MAX) NULL,
    [PhoneNumberConfirmed] BIT            NOT NULL,
    [TwoFactorEnabled]     BIT            NOT NULL,
    [LockoutEndDateUtc]    DATETIME       NULL,
    [LockoutEnabled]       BIT            NOT NULL,
    [AccessFailedCount]    INT            NOT NULL,
    [UserName]             NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);
go
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
    ON [dbo].[AspNetUsers]([UserName] ASC);
go
ALTER TABLE [dbo].[AspNetUserClaims] WITH NOCHECK
    ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;
go
ALTER TABLE [dbo].[AspNetUserLogins] WITH NOCHECK
    ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;
go
ALTER TABLE [dbo].[AspNetUserRoles] WITH NOCHECK
    ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE;
go
ALTER TABLE [dbo].[AspNetUserRoles] WITH NOCHECK
    ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = this.scriptNumber++,
                Name         = "OAuthTestData",
                Description  = "OAuth Test Data",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
set quoted_identifier on
go
set nocount on
go
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });
        }
Esempio n. 11
0
        private void InitFinancial(
            int majorNumber,
            int minorNumber
            )
        {
            int sequence = 0;

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = scriptNumber++,
                Name         = "DefaultFinancialColumnTypes",
                Description  = "Default Financial Column Types",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
set quoted_identifier on
go
if type_id (N'name') is null
	exec sp_addtype 'name'
	    , 'nvarchar(240)', 'not null'
go
if type_id (N'code') is null
	exec sp_addtype 'code'
	    , 'nvarchar(240)', 'not null'
go
if type_id (N'comment') is null
	exec sp_addtype 'comment'
		, 'nvarchar(255)', 'not null'
go
if type_id (N'currency') is null
	exec sp_addtype 'currency'
	    , 'decimal(15,4)', 'not null'
go
if type_id (N'description') is null
	exec sp_addtype 'description'
	    , 'varchar(255)', 'not null'
go
if type_id (N'flag') is null
	exec sp_addtype 'flag'
	    , 'bit', 'null'
go
if type_id (N'id') is null
	exec sp_addtype 'id'
	    , 'uniqueidentifier', 'not null'
go
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = scriptNumber++,
                Name         = "DefaultFinancialDropTables",
                Description  = "Default Financial Drop Tables",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
set quoted_identifier on
go
if object_id (N'financial_order') is not null
	drop table financial_order
if object_id (N'financial_currency') is not null
	drop table financial_currency
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = scriptNumber++,
                Name         = "DefaultFinancialCreateTables",
                Description  = "Default Financial Create Tables",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
set quoted_identifier on
go
create table financial_currency
(
    financial_currency_id id,
    financial_currency_type_rcd code,
    financial_currency_against_financial_currency_type_rcd code  not null,
    user_id            id,
    date_time          datetime  not null,
    valid_from_date_time datetime  not null,
    valid_until_date_time datetime  null,
    amount             currency,
    equals_amount      currency,
    decimal_count      integer  not null,
    financial_currency_type_code name,
    financial_currency_type_name name,
    constraint xpkfinancial_currency primary key  clustered(financial_currency_id asc)
)
go



create nonclustered index xif1financial_currency on financial_currency
(
    financial_currency_type_rcd  asc
)
go



create nonclustered index xif2financial_currency on financial_currency
(
    financial_currency_against_financial_currency_type_rcd  asc
)
go


create table financial_order
(
    financial_order_id id,
    user_id            id,
    date_time          datetime  not null,
    comment            name  null,
    location_address_id id  null,
    financial_currency_id id,
    financial_order_source_rcd code,
    client_id          id,
    constraint xpkfinancial_order primary key  clustered(financial_order_id asc)
)
go



create nonclustered index xif7financial_order on financial_order
(
    location_address_id  asc
)
go



create nonclustered index xif8financial_order on financial_order
(
    financial_currency_id  asc
)
go



create nonclustered index xif9financial_order on financial_order
(
    financial_order_source_rcd  asc
)
go



create nonclustered index xif10financial_order on financial_order
(
    client_id           asc
)
go
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });

            scripts.Add(lastScript = new DatabaseScript {
                DatabaseVersion = new DatabaseVersion {
                    MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07)
                },
                ScriptNumber = scriptNumber++,
                Name         = "FinancialReferenceData",
                Description  = "Financial Reference Data",
                #region script ( *** )
                DatabaseScriptBatch = new DatabaseScriptBatch {
                    Script           = @"
set quoted_identifier on
go
set nocount on
go

-- financial currency
-- SEK
declare @financial_currency_swe_id uniqueidentifier set @financial_currency_swe_id = newid()
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (@financial_currency_swe_id, dbo.ref('financial_currency_type_ref.SEK'), dbo.ref('financial_currency_type_ref.SEK'), 'Swedish Krona',  
			1, dbo.ref('financial_currency_type_ref.SEK'), 1, getutcdate(), 0, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

-- EUR
declare @financial_currency_eur_id uniqueidentifier set @financial_currency_eur_id = newid()
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (@financial_currency_eur_id, dbo.ref('financial_currency_type_ref.EUR'), dbo.ref('financial_currency_type_ref.EUR'), 'Euro',  
			1, dbo.ref('financial_currency_type_ref.EUR'), 1, getutcdate(), 4, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
-- against USD
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (newid(), dbo.ref('financial_currency_type_ref.EUR'), dbo.ref('financial_currency_type_ref.EUR'), 'Euro', 
			1, dbo.ref('financial_currency_type_ref.USD'), 7, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

-- THB
declare @financial_currency_thb_id uniqueidentifier set @financial_currency_thb_id = newid()
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (@financial_currency_thb_id, dbo.ref('financial_currency_type_ref.THB'), dbo.ref('financial_currency_type_ref.THB'), 'Thai Bath',  
			1, dbo.ref('financial_currency_type_ref.THB'), 1, getutcdate(), 4, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
-- against USD
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (newid(), dbo.ref('financial_currency_type_ref.EUR'), dbo.ref('financial_currency_type_ref.EUR'), 'Euro', 
			1, dbo.ref('financial_currency_type_ref.USD'), 40, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

-- USD
declare @financial_currency_usd_id uniqueidentifier set @financial_currency_usd_id = newid()
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (@financial_currency_usd_id, dbo.ref('financial_currency_type_ref.USD'), dbo.ref('financial_currency_type_ref.USD'), 'United States Dollar',  
			1, dbo.ref('financial_currency_type_ref.USD'), 1, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
-- against NOK
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (newid(), dbo.ref('financial_currency_type_ref.USD'), dbo.ref('financial_currency_type_ref.USD'), 'United States Dollar', 
			1, dbo.ref('financial_currency_type_ref.NOK'), 7, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());

-- NOK
declare @financial_currency_nok_id uniqueidentifier set @financial_currency_nok_id = newid()
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (@financial_currency_nok_id, dbo.ref('financial_currency_type_ref.NOK'), dbo.ref('financial_currency_type_ref.NOK'), 'Norwegian Krone', 
			1, dbo.ref('financial_currency_type_ref.NOK'), 1, getutcdate(), 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
-- against USD
insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time )
	values (newid(), dbo.ref('financial_currency_type_ref.NOK'), dbo.ref('financial_currency_type_ref.NOK'), 'Norwegian Krone', 
			7, dbo.ref('financial_currency_type_ref.USD'), 1, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
go
                            ",
                    BreakUpToBatches = true
                }
                #endregion
            });
        }