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 + "' )");
}
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);
}
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
});
}
public string ExecuteScriptsBetween(
DatabaseVersion databaseVersionEqualToOrGreaterThan,
DatabaseVersion databaseVersionEqualToOrLessThan,
bool breakOnCreationCheckmark
)
{
try {
// find current state
if (!IsConnectionGood())
{
return("Connection not good; " + SanitizePassword(this.connectionString));
}
if (!IsDatabaseGood())
{
return("Database is not good; " + this.databaseName + " on connection; " + SanitizePassword(this.connectionString));
}
// get version
DatabaseVersion databaseVersion = new DatabaseVersion();
string returnMessage = string.Empty;
if (!IsVersionTableGood())
{
returnMessage += "Version table is not good on connection; " + SanitizePassword(this.connectionString) + "\r\n";
}
else
{
databaseVersion = VersionTableLastEntry();
}
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";
if (IsVersionTableGood()) // make sure table exists
{
returnMessage += DefaultVersion(script.DatabaseVersion) + "\r\n";
}
}
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);
}
}
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);
}
}
