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