public string DefaultVersion(
DatabaseVersion databaseVersion
)
{
string sql =
@"
insert into default_version (default_version_id, fun_version, number, major_number, minor_number, sequence_number, default_user_id, date_time)
values (
newid(),
'initVersion',
'" + databaseVersion.MajorNumber + "." + databaseVersion.MinorNumber + "." + databaseVersion.SequenceNumber + @"',
" + databaseVersion.MajorNumber + @",
" + databaseVersion.MinorNumber + @",
" + databaseVersion.SequenceNumber + @",
'{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate())
";
Execute(
sql: sql
);
return("Script Executed" + " " + "( setting version '" + databaseVersion.MajorNumber + "." + databaseVersion.MinorNumber + "." + databaseVersion.SequenceNumber + "' )");
}
/// <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
});
}
/// <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>
/// Execute all scripts between to versions
/// </summary>
/// <param name="databaseVersionEqualToOrGreaterThan">DatabaseVersion object with version to start from</param>
/// <param name="databaseVersionEqualToOrLessThan">DatabaseVersion object with version to go to</param>
/// <returns>SQL Server comments from executing the scripts</returns>
public string ExecuteScriptsBetween(
DatabaseVersion databaseVersionEqualToOrGreaterThan,
DatabaseVersion databaseVersionEqualToOrLessThan,
bool breakOnCreationCheckmark
)
{
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())
{
return("Database is not good; " + databaseName + " on connection; " + SanitizePassword(connectionString));
}
// get version
DatabaseVersion databaseVersion = new DatabaseVersion();
string returnMessage = string.Empty;
if (!IsVersionTableGood())
{
returnMessage += "Version table is not good on connection; " + SanitizePassword(connectionString) + "\r\n";
}
else
{
try {
databaseVersion = VersionTableLastEntry();
} catch (Exception ex) {
if (ex != null)
{
}
}
}
if (maximumVersion == databaseVersion)
{
returnMessage += "Code version and database version is the same" + "\r\n";
}
if (maximumVersion > databaseVersion)
{
returnMessage += "Code version is greater than the database version" + "\r\n";
// https://stackoverflow.com/questions/26196/filtering-collections-in-c-sharp
// https://stackoverflow.com/questions/1414893/linq-to-sql-value-between-two-double-values
List <DatabaseScript> scriptsFiltered =
scripts
.Where(s =>
s.DatabaseVersion >= databaseVersionEqualToOrGreaterThan &&
s.DatabaseVersion <= databaseVersionEqualToOrLessThan)
.ToList();
// start message with time, script info
// exception message with time, script info, error info
// end message with time, script info
// no overall run page..
List <DatabaseScript> scriptOrdered =
scriptsFiltered
.OrderBy(o => o.DatabaseVersion.MajorNumber)
.ThenBy(o2 => o2.DatabaseVersion.MinorNumber)
.ThenBy(o3 => o3.DatabaseVersion.SequenceNumber)
.ToList();
foreach (DatabaseScript script in scriptOrdered)
{
if (script.Name.Equals("CreationCheckmark") && breakOnCreationCheckmark)
{
break;
}
if (script.Name.Equals("CreationCheckmark"))
{
continue; // do not execute this script
}
returnMessage += DateTime.UtcNow;
//returnMessage += "Script Executed" + " " + "( " + script.Name + " )" + "\r\n";
returnMessage += "Script Executed" + " " + "( " + Execute(script) + " )" + "\r\n";
try {
if (IsVersionTableGood()) // make sure table exists
{
returnMessage += DefaultVersion(script.DatabaseVersion) + "\r\n";
}
} catch (Exception ex) {
if (ex != null)
{
}
}
}
return(returnMessage);
}
else
{
return("Code version is less than the database version");
}
} catch (Exception ex) {
throw new Exception("Failed to upgrade database to latest version", 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);
}
}