/// <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);
}
}
/// <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);
}
/// <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);
}
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
});
}
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
});
}
/// <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
}
});
}
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
}
});
}
/// <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
);
}
}
/// <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);
}
}
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
});
}
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
});
}