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;
}
int minorNumber = 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 int 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);
//InitLocation(majorNumber: 0, minorNumber: minorNumber++);
//InitOAuth(majorNumber: 0, minorNumber: minorNumber++);
InitPort(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 = this.scriptNumber++,
Name = "CreationCheckmark"
});
//InitReferenceLanguageSwitch(majorNumber: 0, minorNumber: minorNumber++);
// 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
);
}
}
public string ExecuteNextScript()
{
try {
DatabaseScript nextScript = NextScript();
// execute next script
string returnMessage = string.Empty;
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";
}
return(returnMessage);
} catch (Exception ex) {
throw new Exception("Failed to upgrade database to next script", ex);
}
}
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 = this.scriptNumber++,
Name = "",
Description = "",
DatabaseScriptBatch = new DatabaseScriptBatch {
Script = @"
",
BreakUpToBatches = true
}
});
return(databaseVersion);
}
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);
}
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 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
});
}
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://norsolutionsql2xcore.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'), 'emailLog', '', '{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
}
});
}
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 DatabaseScript NextScript()
{
try {
// find current state
if (!IsConnectionGood())
{
throw new Exception("Connection not good; " + SanitizePassword(this.connectionString));
}
if (!IsDatabaseGood())
{
throw new Exception("Database is not good; " + this.databaseName + " on connection; " + SanitizePassword(this.connectionString));
}
// get version
DatabaseVersion databaseVersion = new DatabaseVersion();
string returnMessage = string.Empty;
if (!IsVersionTableGood())
{
throw new Exception("Version table is not good on connection; " + SanitizePassword(this.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 InitReferenceLanguageSwitch(
int majorNumber,
int minorNumber
)
{
int sequence = 0;
scripts.Add(lastScript = new DatabaseScript {
DatabaseVersion = new DatabaseVersion {
MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2019, 02, 04)
},
ScriptNumber = this.scriptNumber++,
Name = "LanguageSwitchtoNorwegian",
Description = "Language Switch to Norwegian",
#region script ( *** )
DatabaseScriptBatch = new DatabaseScriptBatch {
Script = @"
update address_type_ref set address_type_name = 'Office' where address_type_rcd = 'Office'
update address_type_ref set address_type_name = 'Primære' where address_type_rcd = 'Primary'
update address_type_ref set address_type_name = 'Privat' where address_type_rcd = 'Private'
update aircraft_body_ref set aircraft_body_name = 'Smal kropp' where aircraft_body_rcd = 'NB'
update aircraft_body_ref set aircraft_body_name = 'Bred Body' where aircraft_body_rcd = 'WB'
update aircraft_compartment_type_ref set aircraft_compartment_type_name = 'Hoved Last' where aircraft_compartment_type_rcd = 'MCD'
update aircraft_compartment_type_ref set aircraft_compartment_type_name = 'Passasjer sone A' where aircraft_compartment_type_rcd = 'PZA'
update aircraft_compartment_type_ref set aircraft_compartment_type_name = 'Passasjer sone B' where aircraft_compartment_type_rcd = 'PZB'
update aircraft_compartment_type_ref set aircraft_compartment_type_name = 'Passasjer sone C' where aircraft_compartment_type_rcd = 'PZC'
update aircraft_configuration_ref set aircraft_configuration_name = 'Frakte fly' where aircraft_configuration_rcd = 'CA'
update aircraft_configuration_ref set aircraft_configuration_name = 'Kombi fly' where aircraft_configuration_rcd = 'COA'
update aircraft_configuration_ref set aircraft_configuration_name = 'Passasjerfly' where aircraft_configuration_rcd = 'PA'
update aircraft_document_type_ref set aircraft_document_type_name = 'Farbare sertifikat' where aircraft_document_type_rcd = 'AC'
update aircraft_identifier_type_ref set aircraft_identifier_type_name = 'Institutt for transport sertifiserings kode' where aircraft_identifier_type_rcd = 'DOTCC'
update aircraft_identifier_type_ref set aircraft_identifier_type_name = 'Avdeling for transport enhetskode' where aircraft_identifier_type_rcd = 'DOTEC'
update aircraft_identifier_type_ref set aircraft_identifier_type_name = 'Federal Airport Association kode' where aircraft_identifier_type_rcd = 'FAA'
update aircraft_identifier_type_ref set aircraft_identifier_type_name = 'Internasjonale flyselskaps transport tilknytnings kode' where aircraft_identifier_type_rcd = 'IATA'
update aircraft_identifier_type_ref set aircraft_identifier_type_name = 'International Civil Aviation Organization kode' where aircraft_identifier_type_rcd = 'ICAO'
update aircraft_identifier_type_ref set aircraft_identifier_type_name = 'Serienummer for produsent' where aircraft_identifier_type_rcd = 'MSN'
update aircraft_identifier_type_ref set aircraft_identifier_type_name = 'Registrering koden' where aircraft_identifier_type_rcd = 'RG'
update aircraft_type_ref set aircraft_type_name = 'Airbus A300-600' where aircraft_type_rcd = 'Airbus A300-600'
update aircraft_type_ref set aircraft_type_name = 'Airbus A310' where aircraft_type_rcd = 'Airbus A310'
update aircraft_type_ref set aircraft_type_name = 'Airbus A318' where aircraft_type_rcd = 'Airbus A318'
update aircraft_type_ref set aircraft_type_name = 'Airbus A319' where aircraft_type_rcd = 'Airbus A319'
update aircraft_type_ref set aircraft_type_name = 'Airbus A320' where aircraft_type_rcd = 'Airbus A320'
update aircraft_type_ref set aircraft_type_name = 'Airbus A321' where aircraft_type_rcd = 'Airbus A321'
update aircraft_type_ref set aircraft_type_name = 'Airbus A330-200' where aircraft_type_rcd = 'Airbus A330-200'
update aircraft_type_ref set aircraft_type_name = 'Airbus A330-300' where aircraft_type_rcd = 'Airbus A330-300'
update aircraft_type_ref set aircraft_type_name = 'Airbus A340-200' where aircraft_type_rcd = 'Airbus A340-200'
update aircraft_type_ref set aircraft_type_name = 'Airbus A340-300' where aircraft_type_rcd = 'Airbus A340-300'
update aircraft_type_ref set aircraft_type_name = 'Airbus A340-500' where aircraft_type_rcd = 'Airbus A340-500'
update aircraft_type_ref set aircraft_type_name = 'Airbus A340-600' where aircraft_type_rcd = 'Airbus A340-600'
update aircraft_type_ref set aircraft_type_name = 'Airbus A350-800' where aircraft_type_rcd = 'Airbus A350-800'
update aircraft_type_ref set aircraft_type_name = 'Airbus A350-900' where aircraft_type_rcd = 'Airbus A350-900'
update aircraft_type_ref set aircraft_type_name = 'Airbus A350-900r' where aircraft_type_rcd = 'Airbus A350-900R'
update aircraft_type_ref set aircraft_type_name = 'Airbus A380' where aircraft_type_rcd = 'Airbus A380'
update airline_identifier_type_ref set airline_identifier_type_name = 'Flyselskapet koden kort' where airline_identifier_type_rcd = 'AC2'
update airline_identifier_type_ref set airline_identifier_type_name = 'Flyselskapets kode Long' where airline_identifier_type_rcd = 'AC3'
update airline_identifier_type_ref set airline_identifier_type_name = 'Institutt for transport sertifiserings kode' where airline_identifier_type_rcd = 'DOTCC'
update airline_identifier_type_ref set airline_identifier_type_name = 'Avdeling for transport enhetskode' where airline_identifier_type_rcd = 'DOTEC'
update airline_identifier_type_ref set airline_identifier_type_name = 'Internasjonale flyselskaps transport tilknytnings kode' where airline_identifier_type_rcd = 'IATA'
update airline_identifier_type_ref set airline_identifier_type_name = 'Den internasjonale organisasjonen for sivil luftfart' where airline_identifier_type_rcd = 'ICAO'
update airline_type_ref set airline_type_name = 'Planlagt transportør' where airline_type_rcd = 'SC'
update airport_identifier_type_ref set airport_identifier_type_name = 'Flyplasskode tre' where airport_identifier_type_rcd = 'AC3'
update airport_identifier_type_ref set airport_identifier_type_name = 'Flyplasskode fire' where airport_identifier_type_rcd = 'AC4'
update airport_identifier_type_ref set airport_identifier_type_name = 'Federal Airport Association kode' where airport_identifier_type_rcd = 'FAA'
update airport_identifier_type_ref set airport_identifier_type_name = 'Internasjonale flyselskaps transport tilknytnings kode' where airport_identifier_type_rcd = 'IATA'
update airport_identifier_type_ref set airport_identifier_type_name = 'Den internasjonale organisasjonen for sivil luftfart' where airport_identifier_type_rcd = 'ICAO'
update airport_type_ref set airport_type_name = 'Sivile flyplass' where airport_type_rcd = 'CA'
update booking_document_type_ref set booking_document_type_name = 'Eksterne system rådata' where booking_document_type_rcd = 'ESRD'
update booking_document_type_ref set booking_document_type_name = 'Teletype' where booking_document_type_rcd = 'TTY'
update booking_event_type_ref set booking_event_type_name = 'Booking bekreftet' where booking_event_type_rcd = 'BC'
update booking_event_type_ref set booking_event_type_name = 'Booking epost sendt' where booking_event_type_rcd = 'BES'
update booking_event_type_ref set booking_event_type_name = 'Bestilling på vent' where booking_event_type_rcd = 'BH'
update booking_event_type_ref set booking_event_type_name = 'Bestilling re-booket' where booking_event_type_rcd = 'BR'
update booking_event_type_ref set booking_event_type_name = 'Bestilling Split' where booking_event_type_rcd = 'BS'
update booking_event_type_ref set booking_event_type_name = 'Bestilling avbrutt' where booking_event_type_rcd = 'CA'
update booking_event_type_ref set booking_event_type_name = 'Bestilling opprettet' where booking_event_type_rcd = 'CRE'
update booking_event_type_ref set booking_event_type_name = 'Bestilling oppdatert' where booking_event_type_rcd = 'UPD'
update booking_identifier_type_ref set booking_identifier_type_name = 'Bestillingsnummer' where booking_identifier_type_rcd = 'BN'
update booking_identifier_type_ref set booking_identifier_type_name = 'Registrer Locator' where booking_identifier_type_rcd = 'RL'
update booking_identifier_type_ref set booking_identifier_type_name = 'Sender Locator' where booking_identifier_type_rcd = 'SL'
update booking_identifier_type_ref set booking_identifier_type_name = 'Turoperatør Locator' where booking_identifier_type_rcd = 'TOL'
update booking_passenger_event_type_ref set booking_passenger_event_type_name = 'Bordet' where booking_passenger_event_type_rcd = 'BOA'
update booking_passenger_event_type_ref set booking_passenger_event_type_name = 'Checkin' where booking_passenger_event_type_rcd = 'CIFS'
update booking_passenger_event_type_ref set booking_passenger_event_type_name = 'Checkout' where booking_passenger_event_type_rcd = 'COFS'
update booking_passenger_event_type_ref set booking_passenger_event_type_name = 'Avlastede' where booking_passenger_event_type_rcd = 'OL'
update booking_passenger_event_type_ref set booking_passenger_event_type_name = 'Ticketed' where booking_passenger_event_type_rcd = 'TI'
update booking_passenger_event_type_ref set booking_passenger_event_type_name = 'Un-ticketed' where booking_passenger_event_type_rcd = 'UTI'
update booking_source_ref set booking_source_name = 'Byrå' where booking_source_rcd = 'AG'
update booking_source_ref set booking_source_name = 'Internt system' where booking_source_rcd = 'IS'
update booking_source_ref set booking_source_name = 'Turoperatør' where booking_source_rcd = 'TO'
update booking_source_ref set booking_source_name = 'Teletype' where booking_source_rcd = 'TTY'
update client_event_type_ref set client_event_type_name = 'Innledning Mail bekreftet' where client_event_type_rcd = 'IMC'
update client_event_type_ref set client_event_type_name = 'Pakkepost sendt' where client_event_type_rcd = 'PMS'
update client_event_type_ref set client_event_type_name = 'Pakke bestilt' where client_event_type_rcd = 'PO'
update client_event_type_ref set client_event_type_name = 'Pakke nettsted vist' where client_event_type_rcd = 'PSV'
update client_event_type_ref set client_event_type_name = 'Sendt Introduksjons post' where client_event_type_rcd = 'SIM'
update client_type_ref set client_type_name = 'Business' where client_type_rcd = 'B'
update client_type_ref set client_type_name = 'Privat' where client_type_rcd = 'P'
update contact_method_ref set contact_method_name = 'Telefon, arbeid' where contact_method_rcd = 'Business'
update contact_method_ref set contact_method_name = 'Email' where contact_method_rcd = 'Email'
update contact_method_ref set contact_method_name = 'Faks' where contact_method_rcd = 'Fax'
update contact_method_ref set contact_method_name = 'Privat telefon' where contact_method_rcd = 'Home'
update contact_method_ref set contact_method_name = 'Mobile' where contact_method_rcd = 'Mobile'
update contact_method_ref set contact_method_name = 'Mobile MMS' where contact_method_rcd = 'MobileMMS'
update contact_method_ref set contact_method_name = 'Mobil SMS' where contact_method_rcd = 'MobileSMS'
update contact_method_ref set contact_method_name = 'Telefon (internasjonal)' where contact_method_rcd = 'PhoneI'
update contact_method_ref set contact_method_name = 'Telefon (lokal)' where contact_method_rcd = 'PhoneL'
update date_time_type_ref set date_time_type_name = 'Faktisk ankomst' where date_time_type_rcd = 'AA'
update date_time_type_ref set date_time_type_name = 'Aktivert' where date_time_type_rcd = 'AC'
update date_time_type_ref set date_time_type_name = 'Faktisk avgang' where date_time_type_rcd = 'AD'
update date_time_type_ref set date_time_type_name = 'Adl' where date_time_type_rcd = 'ADL'
update date_time_type_ref set date_time_type_name = 'Avansert passasjer informasjons system' where date_time_type_rcd = 'APIS'
update date_time_type_ref set date_time_type_name = 'Bestilling stengt' where date_time_type_rcd = 'BC'
update date_time_type_ref set date_time_type_name = 'Bestilling åpen' where date_time_type_rcd = 'BO'
update date_time_type_ref set date_time_type_name = 'Boarding stengt' where date_time_type_rcd = 'BOAC'
update date_time_type_ref set date_time_type_name = 'Boarding åpen' where date_time_type_rcd = 'BOAO'
update date_time_type_ref set date_time_type_name = 'Avbrutt' where date_time_type_rcd = 'CA'
update date_time_type_ref set date_time_type_name = 'Innsjekking stengt' where date_time_type_rcd = 'CC'
update date_time_type_ref set date_time_type_name = 'Sjekk inn åpen' where date_time_type_rcd = 'CO'
update date_time_type_ref set date_time_type_name = 'Sjekk inn påminnelse' where date_time_type_rcd = 'CR'
update date_time_type_ref set date_time_type_name = 'Opprettet' where date_time_type_rcd = 'CRE'
update date_time_type_ref set date_time_type_name = 'Sendt' where date_time_type_rcd = 'DP'
update date_time_type_ref set date_time_type_name = 'Estimert ankomst' where date_time_type_rcd = 'EA'
update date_time_type_ref set date_time_type_name = 'Estimert avgang' where date_time_type_rcd = 'ED'
update date_time_type_ref set date_time_type_name = 'Fly fløyet' where date_time_type_rcd = 'FF'
update date_time_type_ref set date_time_type_name = 'Inaktivert' where date_time_type_rcd = 'IA'
update date_time_type_ref set date_time_type_name = 'Låst' where date_time_type_rcd = 'LOC'
update date_time_type_ref set date_time_type_name = 'Laster inn' where date_time_type_rcd = 'LOI'
update date_time_type_ref set date_time_type_name = 'Lasting av' where date_time_type_rcd = 'LOO'
update date_time_type_ref set date_time_type_name = 'Planlagt ankomst' where date_time_type_rcd = 'PA'
update date_time_type_ref set date_time_type_name = 'Planlagt avgang' where date_time_type_rcd = 'PD'
update date_time_type_ref set date_time_type_name = 'Pfs' where date_time_type_rcd = 'PFS'
update date_time_type_ref set date_time_type_name = 'Passasjer navneliste' where date_time_type_rcd = 'PNL'
update date_time_type_ref set date_time_type_name = 'Prl' where date_time_type_rcd = 'PRL'
update date_time_type_ref set date_time_type_name = 'Tilbakestille' where date_time_type_rcd = 'RE'
update date_time_type_ref set date_time_type_name = 'Touchdown' where date_time_type_rcd = 'TD'
update date_time_type_ref set date_time_type_name = 'Takeoff' where date_time_type_rcd = 'TO'
update default_change_log_type_ref set default_change_log_type_name = 'Endret Feature' where default_change_log_type_rcd = 'Change'
update default_change_log_type_ref set default_change_log_type_name = 'Problemet' where default_change_log_type_rcd = 'Issue'
update default_change_log_type_ref set default_change_log_type_name = 'Ny funksjon' where default_change_log_type_rcd = 'New'
update default_change_log_type_ref set default_change_log_type_name = 'Fjernet Feature' where default_change_log_type_rcd = 'Removed'
update default_error_layer_ref set default_error_layer_name = 'Forretningslogikk-lag' where default_error_layer_rcd = 'BLL'
update default_error_layer_ref set default_error_layer_name = 'Business Layer-motor' where default_error_layer_rcd = 'BLLEngine'
update default_error_layer_ref set default_error_layer_name = 'Business Logic Layer rest' where default_error_layer_rcd = 'BLLRest'
update default_error_layer_ref set default_error_layer_name = 'Business Logic Layer såpe' where default_error_layer_rcd = 'BLLSoap'
update default_error_layer_ref set default_error_layer_name = 'Klient motor' where default_error_layer_rcd = 'CEngine'
update default_error_layer_ref set default_error_layer_name = 'Database' where default_error_layer_rcd = 'DB'
update default_error_layer_ref set default_error_layer_name = 'Data tilgangs lag' where default_error_layer_rcd = 'DLL'
update default_error_layer_ref set default_error_layer_name = 'Dokumentasjon' where default_error_layer_rcd = 'DOC'
update default_error_layer_ref set default_error_layer_name = 'Kontroller for ASP-modell visning' where default_error_layer_rcd = 'MVC'
update default_error_layer_ref set default_error_layer_name = 'Klient-proxy' where default_error_layer_rcd = 'Proxy'
update default_error_layer_ref set default_error_layer_name = 'Sql2Wcf' where default_error_layer_rcd = 'S2W'
update default_error_layer_ref set default_error_layer_name = 'Automatisert testing' where default_error_layer_rcd = 'Test'
update default_error_layer_ref set default_error_layer_name = 'Test front' where default_error_layer_rcd = 'TestF'
update default_error_layer_ref set default_error_layer_name = 'Windows-WinForms' where default_error_layer_rcd = 'WinForms'
update default_error_type_ref set default_error_type_name = 'Motoren' where default_error_type_rcd = 'Engine'
update default_error_type_ref set default_error_type_name = 'Lydløs/fanget' where default_error_type_rcd = 'Silent'
update default_error_type_ref set default_error_type_name = 'Automatisert test' where default_error_type_rcd = 'Test'
update default_error_type_ref set default_error_type_name = 'Bruker startet' where default_error_type_rcd = 'User'
update default_issue_status_ref set default_issue_status_name = 'Ikke et problem' where default_issue_status_rcd = 'NotAnIssue'
update default_issue_status_ref set default_issue_status_name = 'Ikke reproduserbar' where default_issue_status_rcd = 'NotReproducible'
update default_issue_status_ref set default_issue_status_name = 'Løst' where default_issue_status_rcd = 'Resolved'
update default_issue_status_ref set default_issue_status_name = 'Skal løses' where default_issue_status_rcd = 'ToBeResolved'
update default_issue_type_ref set default_issue_type_name = 'Bug' where default_issue_type_rcd = 'Bug'
update default_issue_type_ref set default_issue_type_name = 'Endre forespørsel' where default_issue_type_rcd = 'Change'
update default_issue_type_ref set default_issue_type_name = 'Feil oppførsel' where default_issue_type_rcd = 'Incorrect'
update default_issue_type_ref set default_issue_type_name = 'Feature forespørsel' where default_issue_type_rcd = 'Request'
update default_rule_type_ref set default_rule_type_name = 'Skjul lag adresse' where default_rule_type_rcd = 'Hide'
update default_state_ref set default_state_name = 'Opprettet' where default_state_rcd = 'C'
update default_state_ref set default_state_name = 'Ugyldig' where default_state_rcd = 'I'
update default_state_ref set default_state_name = 'Oppdatert' where default_state_rcd = 'U'
update default_system_setting_ref set default_system_setting_name = 'Booking costcentre' where default_system_setting_rcd = 'BCC'
update default_system_setting_ref set default_system_setting_name = 'Booking valuta' where default_system_setting_rcd = 'BCU'
update default_system_setting_ref set default_system_setting_name = 'Mørkt fargevalg' where default_system_setting_rcd = 'dcs'
update default_system_setting_ref set default_system_setting_name = 'e-postadresse' where default_system_setting_rcd = 'EURL'
update default_test_run_result_ref set default_test_run_result_name = 'ok' where default_test_run_result_rcd = 'OK'
update default_test_run_result_ref set default_test_run_result_name = 'Unntak' where default_test_run_result_rcd = 'EX'
update default_test_run_result_ref set default_test_run_result_name = 'Web-unntak' where default_test_run_result_rcd = 'WEBEX'
update default_user_activity_type_ref set default_user_activity_type_name = 'Business Logic-metode startet' where default_user_activity_type_rcd = 'blmi'
update default_user_activity_type_ref set default_user_activity_type_name = 'Skjema åpnet' where default_user_activity_type_rcd = 'fo'
update default_user_activity_type_ref set default_user_activity_type_name = 'Pålogging' where default_user_activity_type_rcd = 'li'
update default_user_activity_type_ref set default_user_activity_type_name = 'Logout' where default_user_activity_type_rcd = 'lo'
update default_user_activity_type_ref set default_user_activity_type_name = 'Planlagt hendelse' where default_user_activity_type_rcd = 'se'
update default_user_activity_type_ref set default_user_activity_type_name = 'Webside åpnet' where default_user_activity_type_rcd = 'wpo'
update ferry_type_ref set ferry_type_name = 'Lastebil' where ferry_type_rcd = 'LO'
update ferry_type_ref set ferry_type_name = 'Bil ferge' where ferry_type_rcd = 'SF'
update financial_adjustment_type_ref set financial_adjustment_type_name = 'Regel for avrunding' where financial_adjustment_type_rcd = 'RR'
update financial_bank_account_number_type_ref set financial_bank_account_number_type_name = 'Internasjonal bank konto' where financial_bank_account_number_type_rcd = 'IBAN'
update financial_bank_account_number_type_ref set financial_bank_account_number_type_name = 'Samfunn over hele verden finansiell telekommunikasjon' where financial_bank_account_number_type_rcd = 'SWIFT'
update financial_booking_transaction_type_ref set financial_booking_transaction_type_name = 'Lagt' where financial_booking_transaction_type_rcd = 'ADD'
update financial_booking_transaction_type_ref set financial_booking_transaction_type_name = 'Fjernet' where financial_booking_transaction_type_rcd = 'REMOVE'
update financial_card_type_ref set financial_card_type_name = 'Amerikansk Ekspress' where financial_card_type_rcd = 'AmEx'
update financial_card_type_ref set financial_card_type_name = 'Master-kort' where financial_card_type_rcd = 'Master'
update financial_card_type_ref set financial_card_type_name = 'Visa' where financial_card_type_rcd = 'Visa'
update financial_coupon_type_ref set financial_coupon_type_name = 'Lounge kupongen' where financial_coupon_type_rcd = 'LC'
update financial_coupon_type_ref set financial_coupon_type_name = 'Måltid kupongen' where financial_coupon_type_rcd = 'MC'
update financial_currency_type_ref set financial_currency_type_name = 'Euro' where financial_currency_type_rcd = 'EUR'
update financial_currency_type_ref set financial_currency_type_name = 'Norwegian Krone' where financial_currency_type_rcd = 'NOK'
update financial_currency_type_ref set financial_currency_type_name = 'Svenske kroner' where financial_currency_type_rcd = 'SEK'
update financial_currency_type_ref set financial_currency_type_name = 'Thailandsk bad' where financial_currency_type_rcd = 'THB'
update financial_currency_type_ref set financial_currency_type_name = 'Amerikanske dollar' where financial_currency_type_rcd = 'USD'
update financial_fare_type_ref set financial_fare_type_name = 'Standard ikke' where financial_fare_type_rcd = 'STD'
update financial_fee_type_ref set financial_fee_type_name = 'Agency Fee' where financial_fee_type_rcd = 'Agency'
update financial_fee_type_ref set financial_fee_type_name = 'Booking Fee' where financial_fee_type_rcd = 'Booking'
update financial_fee_type_ref set financial_fee_type_name = 'Betaling Fee' where financial_fee_type_rcd = 'Payment'
update financial_fee_type_ref set financial_fee_type_name = 'Ekstra service avgift' where financial_fee_type_rcd = 'SSR'
update financial_fee_type_ref set financial_fee_type_name = 'Billett avgift' where financial_fee_type_rcd = 'Ticket'
update financial_payment_type_ref set financial_payment_type_name = 'Regnskap' where financial_payment_type_rcd = 'ACC'
update financial_payment_type_ref set financial_payment_type_name = 'Bank' where financial_payment_type_rcd = 'BANK'
update financial_payment_type_ref set financial_payment_type_name = 'Kort' where financial_payment_type_rcd = 'CARD'
update financial_payment_type_ref set financial_payment_type_name = 'Kontanter' where financial_payment_type_rcd = 'CASH'
update financial_payment_type_ref set financial_payment_type_name = 'Kupong' where financial_payment_type_rcd = 'COUPON'
update financial_payment_type_ref set financial_payment_type_name = 'Standard betaling' where financial_payment_type_rcd = 'STD'
update financial_payment_type_ref set financial_payment_type_name = 'Bilaget' where financial_payment_type_rcd = 'VOU'
update financial_tax_type_ref set financial_tax_type_name = 'Agency skatt' where financial_tax_type_rcd = 'Agency'
update financial_tax_type_ref set financial_tax_type_name = 'Booking skatt' where financial_tax_type_rcd = 'Booking'
update financial_tax_type_ref set financial_tax_type_name = 'Betaling skatt' where financial_tax_type_rcd = 'Payment'
update financial_tax_type_ref set financial_tax_type_name = 'Spesiell tjenesteavgift' where financial_tax_type_rcd = 'SSR'
update financial_tax_type_ref set financial_tax_type_name = 'Billett avgift' where financial_tax_type_rcd = 'Ticket'
update financial_voucher_type_ref set financial_voucher_type_name = 'Bilag for avbrudd' where financial_voucher_type_rcd = 'DV'
update financial_voucher_type_ref set financial_voucher_type_name = 'Flykupong' where financial_voucher_type_rcd = 'FV'
update flight_identifier_type_ref set flight_identifier_type_name = 'Flynummer to' where flight_identifier_type_rcd = 'FN2'
update flight_identifier_type_ref set flight_identifier_type_name = 'Flynummer tre' where flight_identifier_type_rcd = 'FN3'
update flight_identifier_type_ref set flight_identifier_type_name = 'Matriculation' where flight_identifier_type_rcd = 'MATRI'
update flight_schedule_segment_rule_ref set flight_schedule_segment_rule_name = 'Ikke på fredag' where flight_schedule_segment_rule_rcd = 'NoFriday'
update flight_schedule_segment_rule_ref set flight_schedule_segment_rule_name = 'Ikke på mandag' where flight_schedule_segment_rule_rcd = 'NoMonday'
update flight_schedule_segment_rule_ref set flight_schedule_segment_rule_name = 'Ikke på lørdag' where flight_schedule_segment_rule_rcd = 'NoSaturday'
update flight_schedule_segment_rule_ref set flight_schedule_segment_rule_name = 'Ikke på søndag' where flight_schedule_segment_rule_rcd = 'NoSunday'
update flight_schedule_segment_rule_ref set flight_schedule_segment_rule_name = 'Ikke på torsdag' where flight_schedule_segment_rule_rcd = 'NoThursday'
update flight_schedule_segment_rule_ref set flight_schedule_segment_rule_name = 'Ikke på tirsdag' where flight_schedule_segment_rule_rcd = 'NoTuesday'
update flight_schedule_segment_rule_ref set flight_schedule_segment_rule_name = 'Ikke på onsdag' where flight_schedule_segment_rule_rcd = 'NoWednesday'
update gender_ref set gender_name = 'Kvinnelige' where gender_rcd = 'F'
update gender_ref set gender_name = 'Mannlige' where gender_rcd = 'M'
update language_ref set language_name = 'Engelsk' where language_rcd = 'en-US'
update language_ref set language_name = 'Norsk Bokmål' where language_rcd = 'nb-NO'
update language_ref set language_name = '???????' where language_rcd = 'th-TH'
update link_type_ref set link_type_name = 'Sosiale medier' where link_type_rcd = 'SM'
update link_type_ref set link_type_name = 'Webområde' where link_type_rcd = 'WS'
update nationality_ref set nationality_name = 'Jupiter' where nationality_rcd = 'JU'
update nationality_ref set nationality_name = 'Mars' where nationality_rcd = 'MA'
update passenger_type_ref set passenger_type_name = 'Voksen' where passenger_type_rcd = 'AD'
update passenger_type_ref set passenger_type_name = 'Barn' where passenger_type_rcd = 'CHD'
update passenger_type_ref set passenger_type_name = 'Spedbarn' where passenger_type_rcd = 'INF'
update reporting_roundup_type_ref set reporting_roundup_type_name = 'Bestillinger per dag' where reporting_roundup_type_rcd = 'Bookings'
update reporting_roundup_type_ref set reporting_roundup_type_name = 'Bestilling passasjer' where reporting_roundup_type_rcd = 'BP'
update service_image_type_ref set service_image_type_name = 'Internt Stock-bilde' where service_image_type_rcd = 'ISP'
update service_image_type_ref set service_image_type_name = 'Variabel størrelse for produktbilde' where service_image_type_rcd = 'PI'
update service_request_status_ref set service_request_status_name = 'SSR har blitt bekreftet' where service_request_status_rcd = 'HK'
update service_request_status_ref set service_request_status_name = 'SSR har blitt forespurt' where service_request_status_rcd = 'NN'
update service_request_status_ref set service_request_status_name = 'Andre' where service_request_status_rcd = 'OTHER'
update service_request_status_ref set service_request_status_name = 'SSR venter på bekreftelse' where service_request_status_rcd = 'PN'
update service_request_status_ref set service_request_status_name = 'Fjernet' where service_request_status_rcd = 'REM'
update service_request_status_ref set service_request_status_name = 'Transportøren kan ikke fullføre forespørselen' where service_request_status_rcd = 'UN'
update service_special_service_request_group_ref set service_special_service_request_group_name = 'Ingen' where service_special_service_request_group_rcd = 'None'
update service_special_service_request_operation_rule_ref set service_special_service_request_operation_rule_name = 'Manuell' where service_special_service_request_operation_rule_rcd = 'MAN'
update service_special_service_request_operation_rule_ref set service_special_service_request_operation_rule_name = 'Programmatisk' where service_special_service_request_operation_rule_rcd = 'PRO'
update service_special_service_request_requirement_ref set service_special_service_request_requirement_name = 'Ingen' where service_special_service_request_requirement_rcd = 'N'
update service_special_service_request_requirement_ref set service_special_service_request_requirement_name = 'Valgfritt' where service_special_service_request_requirement_rcd = 'O'
update service_special_service_request_requirement_ref set service_special_service_request_requirement_name = 'Nødvendig' where service_special_service_request_requirement_rcd = 'R'
update service_type_ref set service_type_name = 'Leiebil' where service_type_rcd = 'CR'
update service_type_ref set service_type_name = 'Ferge Booking' where service_type_rcd = 'FE'
update service_type_ref set service_type_name = 'Hotell Booking' where service_type_rcd = 'HB'
update service_type_ref set service_type_name = 'Spesielle tjenester' where service_type_rcd = 'SS'
update title_ref set title_name = 'Mr.' where title_rcd = 'MR'
update title_ref set title_name = 'Ms.' where title_rcd = 'MS'
",
BreakUpToBatches = true
}
#endregion
});
}
void InitLocation(
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 = "LocationDropTables",
Description = "Location Drop Tables",
#region script ( *** )
DatabaseScriptBatch = new DatabaseScriptBatch {
Script = @"
set quoted_identifier on
go
if object_id (N'location_address_type_ref') is not null
drop table location_address_type_ref
if object_id (N'location_country') is not null
drop table location_country
if object_id (N'location_address') is not null
drop table location_address
if object_id (N'location_city') is not null
drop table location_city
",
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 = "LocationCreateTables",
Description = "Location Create Tables",
#region script ( *** )
DatabaseScriptBatch = new DatabaseScriptBatch {
Script = @"
set quoted_identifier on
go
create table location_address_type_ref
(
location_address_type_rcd nvarchar(240) not null,
location_address_type_name nvarchar(240) not null,
user_id uniqueidentifier not null,
date_time datetime not null,
constraint xpklocation_address_type_ref primary key clustered(location_address_type_rcd asc)
)
go
create table location_country
(
location_country_id uniqueidentifier not null,
location_country_code nvarchar(240) not null,
location_country_name nvarchar(240) not null,
user_id uniqueidentifier not null,
date_time datetime not null,
constraint xpklocation_country primary key clustered(location_country_id asc)
)
go
create table location_address
(
location_address_id uniqueidentifier not null,
location_address_type_rcd nvarchar(240) null,
location_country_id uniqueidentifier not null,
address_one nvarchar(240) null,
address_two nvarchar(240) null,
address_three nvarchar(240) null,
city nvarchar(240) null,
street nvarchar(240) null,
state nvarchar(240) null,
district nvarchar(240) null,
province nvarchar(240) null,
zip_code nvarchar(240) null,
po_box nvarchar(240) null,
comment nvarchar(240) null,
user_id uniqueidentifier not null,
date_time datetime not null,
constraint xpklocation_address primary key clustered(location_address_id asc),
constraint address_to_address_type_ref foreign key(location_address_type_rcd) references location_address_type_ref(location_address_type_rcd),
constraint location_address_to_location_country foreign key(location_country_id) references location_country(location_country_id)
)
go
create table location_city
(
location_city_id uniqueidentifier not null,
location_country_id uniqueidentifier not null,
location_city_code nvarchar(240) not null,
location_city_name nvarchar(240) not null,
user_id uniqueidentifier not null,
date_time datetime not null,
constraint xpklocation_city primary key clustered(location_city_id asc),
constraint city_to_country foreign key(location_country_id) references location_country(location_country_id)
)
go
create index xif1location_city on location_city
(
location_country_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 = this.scriptNumber++,
Name = "LocationReferenceAndTestData",
Description = "Location Reference And Test Data",
#region script ( *** )
DatabaseScriptBatch = new DatabaseScriptBatch {
Script = @"
set quoted_identifier on
go
insert into default_system_reference_table(default_system_reference_table_id, default_system_reference_table_name, default_system_reference_display_name, default_user_id, date_time)
values (newid(), 'location_address_type_ref' ,'Location Address Type' ,'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
-- country
insert into location_country (location_country_id, location_country_code, location_country_name, user_id, date_time )
values (newid(), 'NO', 'Norway', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getdate());
insert into location_country (location_country_id, location_country_code, location_country_name, user_id, date_time )
values (newid(), 'SE', 'Sweden', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getdate());
-- address type
insert into location_address_type_ref (location_address_type_rcd, location_address_type_name, user_id, date_time )
values ('Primary', 'Primary', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
insert into location_address_type_ref (location_address_type_rcd, location_address_type_name, user_id, date_time )
values ('Office', 'Office', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
insert into location_address_type_ref (location_address_type_rcd, location_address_type_name, user_id, date_time )
values ('Private', 'Private', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate());
",
BreakUpToBatches = true
}
#endregion
});
}