public void Save(DatabaseVersion inst)
{
using (FileStream fs = new FileStream("some.xml", FileMode.Create))
{
new XmlSerializer(typeof(DatabaseVersion)).Serialize(fs, inst);
}
}
public Database()
{
this.crmSoftware = AppGlobal.getCRMSoftwareValue(UConfig.getAppConfigValue("CRMSoftware", false));
this.databaseVersion = AppGlobal.getDatabaseVersion(UConfig.getAppConfigValue("DatabaseVersion", false));
this.connectionString = UConfig.getAppConfigValue("SQLConnectionString", bool.Parse(UConfig.getAppConfigValue("EncryptedCredentials", false)));
this.connection = new SqlConnection(connectionString);
}
public void Migrate(IEnumerable<IStep> migrationSteps, DatabaseVersion currentVersion, long? targetVersion, IScriptRunner scriptRunner, string schema, string tablespace)
{
if (_databaseMetadataProvider.SchemaExists(schema))
{
TerminateDatabase(migrationSteps.Select(m => m.BackwardScript), scriptRunner, schema);
}
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
// A normal heap that'll be round robin allocated among the data files.
// As first 8 pages are stored in the IAM page header, and thus in the same
// data file, we'll create 100 to hit multiple data files
string query = "CREATE TABLE RoundRobinHeap (A int identity, B char(6000));";
for (int i = 0; i < 100; i++)
query += "INSERT INTO RoundRobinHeap DEFAULT VALUES;";
RunQuery(query, conn);
// Test the same with a clustered table
query = "CREATE TABLE RoundRobinClustered (A int identity, B char(6000));";
for (int i = 0; i < 100; i++)
query += "INSERT INTO RoundRobinClustered DEFAULT VALUES;";
RunQuery(query, conn);
// Create a new filegroup, add a new data file and create a new heap on this FG
RunQuery("ALTER DATABASE [" + conn.Database + "] ADD FILEGROUP [SecondaryFilegroup]", conn);
RunQuery("ALTER DATABASE [" + conn.Database + "] ADD FILE ( NAME = N'SecondaryFGFile', FILENAME = N'" + Path.Combine(DataFileRootPath, conn.Database + "_SecondFG.ndf") + "' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SecondaryFilegroup]", conn);
query = "CREATE TABLE FGSpecificHeap (A int identity, B char(6000));";
for (int i = 0; i < 100; i++)
query += "INSERT INTO FGSpecificHeap DEFAULT VALUES;";
RunQuery(query, conn);
// Test the same with a clustered table
query = "CREATE TABLE FGSpecificClustered (A int identity, B char(6000));";
for (int i = 0; i < 100; i++)
query += "INSERT INTO FGSpecificClustered DEFAULT VALUES;";
RunQuery(query, conn);
}
public void BigintTests(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("BigintTests").ToList();
Assert.AreEqual(1, rows[0].Field<long?>("A"));
Assert.AreEqual(-125, rows[1].Field<long?>("A"));
Assert.AreEqual(-129, rows[2].Field<long?>("A"));
Assert.AreEqual(-130, rows[3].Field<long?>("A"));
Assert.AreEqual(125, rows[4].Field<long?>("A"));
Assert.AreEqual(130, rows[5].Field<long?>("A"));
Assert.AreEqual(-32768, rows[6].Field<long?>("A"));
Assert.AreEqual(32767, rows[7].Field<long?>("A"));
Assert.AreEqual(null, rows[8].Field<long?>("A"));
Assert.AreEqual(0, rows[9].Field<long?>("A"));
Assert.AreEqual(32768, rows[10].Field<long?>("A"));
Assert.AreEqual(8388607, rows[11].Field<long?>("A"));
Assert.AreEqual(2147483647, rows[12].Field<long?>("A"));
Assert.AreEqual(-8388608, rows[13].Field<long?>("A"));
Assert.AreEqual(-8388609, rows[14].Field<long?>("A"));
Assert.AreEqual(-2147483648, rows[15].Field<long?>("A"));
Assert.AreEqual(9223372036854775807, rows[16].Field<long?>("A"));
Assert.AreEqual(36028797018963967, rows[17].Field<long?>("A"));
Assert.AreEqual(140737488355327, rows[18].Field<long?>("A"));
Assert.AreEqual(549755813887, rows[19].Field<long?>("A"));
Assert.AreEqual(2147483648, rows[20].Field<long?>("A"));
Assert.AreEqual(-9223372036854775808, rows[21].Field<long?>("A"));
Assert.AreEqual(-36028797018963967, rows[22].Field<long?>("A"));
Assert.AreEqual(-140737488355327, rows[23].Field<long?>("A"));
Assert.AreEqual(-549755813887, rows[24].Field<long?>("A"));
Assert.AreEqual(-2147483648, rows[25].Field<long?>("A"));
});
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
RunQuery(@" CREATE TABLE HeapForwardedRecord (A int, B varchar(5000))
INSERT INTO HeapForwardedRecord VALUES (25, REPLICATE('A', 4000))
INSERT INTO HeapForwardedRecord VALUES (28, REPLICATE('B', 4000))
UPDATE HeapForwardedRecord SET B = REPLICATE('A', 5000) WHERE A = 25", conn);
}
public AConnectorBase()
{
this.hubSpotAPIKey = UConfig.getAppConfigValue("HubSpotAPIKey", bool.Parse(UConfig.getAppConfigValue("EncryptedCredentials", false)));
this.connectionString = UConfig.getAppConfigValue("SQLConnectionString", bool.Parse(UConfig.getAppConfigValue("EncryptedCredentials", false)));
//***** MHM Contacts – Replaced *****//
//this.hubSpotAPIUrl = UConfig.getAppConfigValue("HubSpotURL", false) + hubSpotAPIKey;
if (UConfig.getAppConfigValue("HubSpotContact", false) == "true")
this.hubSpotAPIUrl = UConfig.getAppConfigValue("HubSpotContactURL", false) + hubSpotAPIKey;
else
this.hubSpotAPIUrl = UConfig.getAppConfigValue("HubSpotURL", false) + hubSpotAPIKey;
//***** MHM Contacts ****************//
this.hubSpotAPIVersion = UConfig.getAppConfigValue("HubSpotAPIVersion", false).ToLower();
this.databaseVersion = AppGlobal.getDatabaseVersion(UConfig.getAppConfigValue("DatabaseVersion", false));
this.sqlConnection = new SqlConnection(connectionString);
this.debugLogPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location) + "\\OLD_Log.txt";
this.dataMaps = new Hashtable();
this.pushPullDateTimeFormat = AppGlobal.getPushPullDateTimeFormat();
this.timeZone = AppGlobal.getCurrentTimeZone();
this.hubSpotMaps = new Hashtable();
this.endpoint = new HubSpotEndpoint(hubSpotAPIVersion, hubSpotAPIKey);
this.hubSpotTimePivot = UConfig.getAppConfigValue("HubSpotTimePivot", false);
this.sqlBatchSize = UConfig.getAppConfigValue("SQLBatchSize", false);
int batchSize; // need to make sure that sqlBatchSize is a valid integer
if (Int32.TryParse(sqlBatchSize, out batchSize) == false)
{
sqlBatchSize = "0";
}
loadDataMaps();
}
public void Sysowners(DatabaseVersion version)
{
RunDatabaseTest(version, db => {
var row = db.BaseTables.sysowners.First();
TestHelper.GetAllPublicProperties(row);
});
}
public SchemaChange(string pathToSchemaChangeFolder, DatabaseVersion version, DatabaseVersion previousVersion, SchemaManagerGlobalOptions options)
{
PathToSchemaChangeFolder = pathToSchemaChangeFolder;
Version = version;
PreviousVersion = previousVersion;
Options = options;
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
RunQuery(@"
CREATE TABLE UniquifierTest (A int) WITH (DATA_COMPRESSION = ROW)
CREATE CLUSTERED INDEX CX_A ON UniquifierTest (A ASC)
INSERT INTO UniquifierTest VALUES (1), (1), (1)
", conn);
}
public void Dmv(DatabaseVersion version)
{
RunDatabaseTest(version, db => {
var procedures = db.Dmvs.Procedures;
Assert.AreEqual(1, procedures.Count());
Assert.AreEqual("TestA", procedures.First().Name);
});
}
public void Text65(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("TextTest65").ToList();
Assert.AreEqual("".PadLeft(65, 'A'), rows[0].Field<string>("A"));
});
}
public void NVarcharMax33(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("NVarcharMaxTest33").ToList();
Assert.AreEqual("".PadLeft(33, '\u040A'), rows[0].Field<string>("A"));
});
}
public void Image65(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("ImageTest65").ToList();
Assert.AreEqual(Encoding.UTF7.GetBytes("".PadLeft(65, 'A')), rows[0].Field<byte[]>("A"));
});
}
public void VarcharMax8040(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("VarcharMaxTest8040").ToList();
Assert.AreEqual("".PadLeft(8040, 'A'), rows[0].Field<string>("A"));
});
}
public void FGSpecificClusteredAllocation(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("FGSpecificClustered").ToList();
Assert.AreEqual(100, rows.Count);
});
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
RunQuery(@"
CREATE TABLE VarcharOverflow (A varchar(8000), B varchar(8000)) WITH (DATA_COMPRESSION = ROW)
INSERT INTO VarcharOverflow VALUES (REPLICATE('A', 5000), REPLICATE('B', 5000))
CREATE TABLE VarcharBlobInlineRoot (A varchar(MAX)) WITH (DATA_COMPRESSION = ROW)
INSERT INTO VarcharBlobInlineRoot VALUES (REPLICATE(CAST('A' AS varchar(MAX)), 25000))
", conn);
}
public void RoundRobinHeapAllocation(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("RoundRobinHeap").ToList();
Assert.AreEqual(100, rows.Count);
});
}
public void NText4020(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("NTextTest4020").ToList();
Assert.AreEqual("".PadLeft(4020, '\u040A'), rows[0].Field<string>("A"));
});
}
public void VarcharBlobInlineRoot(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("VarcharBlobInlineRoot").ToList();
Assert.AreEqual("".PadLeft(25000, 'A'), rows[0].Field<string>("A"));
});
}
public override DatabaseSchema LoadDatabaseSchema(string databaseName, DatabaseVersion version)
{
DatabaseSchema databaseSchema = null;
XmlSerializer serializer = new XmlSerializer(typeof(DatabaseSchema));
using (var fileStream = new System.IO.FileStream(RepositoryLocation, FileMode.Open))
{
databaseSchema = serializer.Deserialize(fileStream) as DatabaseSchema;
}
return databaseSchema;
}
public void VarBinary8040(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("VarBinaryTest8040").ToList();
Assert.AreEqual(Encoding.UTF7.GetBytes("".PadLeft(8040, 'A')), rows[0].Field<byte[]>("A"));
});
}
protected void RunDatabaseTest(DatabaseVersion version, Action<Database> test)
{
Debug.WriteLine("Test database: " + databaseBaseName);
// Setup database and store file paths, if we haven't done so already
ensureDatabaseIsSetup(version);
// Run actual test
using (var db = new Database(databaseFiles[version]))
test(db);
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
RunQuery(@"
CREATE TABLE TestA (A int, PRIMARY KEY CLUSTERED (A));
CREATE TABLE TestB (B int, FOREIGN KEY (B) REFERENCES TestA(A));
", conn);
RunQuery(@"
CREATE PROCEDURE TestC AS SELECT 1 AS A;
", conn);
}
public void ScanAllNullSparse(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("ScanAllNullSparse").ToList();
Assert.AreEqual(null, rows[0].Field<int?>("A"));
Assert.AreEqual(null, rows[0].Field<int?>("B"));
});
}
public void BinaryTests(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("BinaryTest").ToList();
Assert.AreEqual(null, rows[0].Field<byte[]>("A"));
Assert.AreEqual(TestHelper.GetBytesFromByteString("25FF2500000000000000"), rows[1].Field<byte[]>("A"));
Assert.AreEqual(TestHelper.GetBytesFromByteString("01020304050607080910"), rows[2].Field<byte[]>("A"));
});
}
public void RowOverflowPointer(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("RowOverflowPointer").ToList();
Assert.AreEqual(1, rows.Count);
Assert.AreEqual("".PadLeft(5000, 'a'), rows[0]["A"]);
Assert.AreEqual("".PadLeft(5000, 'b'), rows[0]["B"]);
});
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
RunQuery(@"
CREATE TABLE MoneyTest (A money)
INSERT INTO MoneyTest VALUES (123.456789)
INSERT INTO MoneyTest VALUES (-123.456789)
INSERT INTO MoneyTest VALUES (123456789.0123)
INSERT INTO MoneyTest VALUES (-123456789.0123)
INSERT INTO MoneyTest VALUES (-922337203685477.5808)
INSERT INTO MoneyTest VALUES (922337203685477.5807)
", conn);
}
public void MultipleShortDataColumns(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("MultipleShortDataColumns").ToList();
Assert.AreEqual(1, rows[0].Field<byte>("A"));
Assert.AreEqual(2, rows[0].Field<byte>("B"));
Assert.AreEqual(3, rows[0].Field<byte>("C"));
});
}
private void SetDatabaseRevisionTo(DatabaseVersion version)
{
using (var command = _context.CreateCommand())
{
command.CommandText = string.Format("exec sp_updateextendedproperty @name='DatabaseVersion', @value='{0}.{1}.{2}.{3}'",
version.MajorVersion, version.MinorVersion, version.PatchVersion, version.ScriptVersion);
command.ExecuteNonQuery();
_revision = new DatabaseVersion(version.MajorVersion, version.MinorVersion, version.PatchVersion, version.ScriptVersion);
}
}
public void SmallMoneyTests(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("SmallMoneyTests").ToList();
Assert.AreEqual(123.4568m, rows[0].Field <decimal>("A"));
Assert.AreEqual(-123.4568m, rows[1].Field <decimal>("A"));
Assert.AreEqual(123456.0123m, rows[2].Field <decimal>("A"));
Assert.AreEqual(-123456.0123m, rows[3].Field <decimal>("A"));
Assert.AreEqual(-214748.3648m, rows[4].Field <decimal>("A"));
Assert.AreEqual(214748.3647m, rows[5].Field <decimal>("A"));
});
}
public void UniquifierTest(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("UniquifierTest").ToList();
Assert.AreEqual(1, rows[0].Field <int?>("A"));
Assert.AreEqual(0, rows[0].Field <int?>(DataColumn.Uniquifier));
Assert.AreEqual(1, rows[1].Field <int?>("A"));
Assert.AreEqual(1, rows[1].Field <int?>(DataColumn.Uniquifier));
Assert.AreEqual(1, rows[2].Field <int?>("A"));
Assert.AreEqual(2, rows[2].Field <int?>(DataColumn.Uniquifier));
});
}
public void SmallDatetimeTest(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("SmallDatetimeTest").ToList();
Assert.AreEqual(new DateTime(2012, 08, 07, 12, 23, 00), rows[0].Field <DateTime>("A"));
Assert.AreEqual(new DateTime(2011, 02, 23, 01, 02, 00), rows[0].Field <DateTime>("B"));
Assert.AreEqual(new DateTime(1900, 01, 01, 00, 00, 00), rows[0].Field <DateTime>("C"));
Assert.AreEqual(new DateTime(1900, 01, 01, 00, 01, 00), rows[0].Field <DateTime>("D"));
Assert.AreEqual(new DateTime(2079, 06, 06, 23, 59, 00), rows[0].Field <DateTime>("E"));
Assert.AreEqual(new DateTime(2079, 06, 06, 23, 58, 00), rows[0].Field <DateTime>("F"));
});
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
RunQuery(@"
CREATE TABLE TestA (A int, PRIMARY KEY CLUSTERED (A));
CREATE TABLE TestB (B int, FOREIGN KEY (B) REFERENCES TestA(A));
" , conn);
RunQuery(@"
CREATE PROCEDURE TestC AS SELECT 1 AS A;
" , conn);
RunQuery(@"
CREATE VIEW TestD AS SELECT 1 AS A;
" , conn);
}
public void MoneyTests(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("MoneyTests").ToList();
Assert.AreEqual(123.4568m, rows[0].Field <decimal>("A"));
Assert.AreEqual(-123.4568m, rows[1].Field <decimal>("A"));
Assert.AreEqual(123456789.0123m, rows[2].Field <decimal>("A"));
Assert.AreEqual(-123456789.0123m, rows[3].Field <decimal>("A"));
Assert.AreEqual(-922337203685477.5808m, rows[4].Field <decimal>("A"));
Assert.AreEqual(922337203685477.5807m, rows[5].Field <decimal>("A"));
});
}
public void ScanNonclusteredIndexOnNonUniqueClusteredTable(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new IndexScanner(db);
var result = scanner.ScanIndex("NonUniqueClusteredTable", "IDX_Num1").ToList();
Assert.AreEqual(112, result[0]["Num1"]);
Assert.AreEqual(0, result[0][DataColumn.Uniquifier]);
Assert.AreEqual(382, result[1]["Num1"]);
Assert.AreEqual(0, result[1][DataColumn.Uniquifier]);
Assert.AreEqual(382, result[2]["Num1"]);
Assert.AreEqual(1, result[2][DataColumn.Uniquifier]);
});
}
protected override void Given()
{
base.Given();
var schemaChange = GetMockFor <ISchemaChange>();
var version = new DatabaseVersion(1, 0, 0, 0);
schemaChange.SetupGet(s => s.Version).Returns(version);
schemaChange.Setup(s => s.NeedsToBeAppliedTo(It.IsAny <IDatabase>()))
.Returns((IDatabase d) => version > d.Revision);
GetMockFor <IProvideSchemaChanges>()
.Setup(p => p.GetAllChanges())
.Returns(new[] { schemaChange.Object });
}
/// <summary>
/// Creates an instance of the <see cref="VersionNodePair"/> class.
/// </summary>
/// <param name="version">The database version.</param>
/// <param name="node">The <see cref="DependencyNode"/> corresponding to the database version.</param>
public VersionNodePair(DatabaseVersion version, DependencyNode node)
{
if (version == null)
{
throw new ArgumentNullException("version");
}
if (node == null)
{
throw new ArgumentNullException("node");
}
_version = version;
_node = node;
}
public void CharTests(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("CharTests").ToList();
Assert.AreEqual(null, rows[0].Field <string>("A"));
Assert.AreEqual("a", rows[1].Field <string>("A"));
Assert.AreEqual("1234567890", rows[2].Field <string>("A"));
Assert.AreEqual("123", rows[3].Field <string>("A"));
Assert.AreEqual("", rows[4].Field <string>("A"));
Assert.AreEqual("", rows[5].Field <string>("A"));
});
}
public void TinyintTests(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("TinyintTest").ToList();
Assert.AreEqual(1, rows[0].Field <byte?>("A"));
Assert.AreEqual(127, rows[1].Field <byte?>("A"));
Assert.AreEqual(128, rows[2].Field <byte?>("A"));
Assert.AreEqual(255, rows[3].Field <byte?>("A"));
Assert.AreEqual(null, rows[4].Field <byte?>("A"));
Assert.AreEqual(0, rows[5].Field <byte?>("A"));
});
}
public override bool Equals(object obj)
{
//Check for null and compare run-time types.
if (obj == null || GetType() != obj.GetType())
{
return(false);
}
DatabaseVersion a = (DatabaseVersion)obj;
return(a.Build.Equals(Build) &&
a.Minor.Equals(Minor) &&
a.Major.Equals(Major) &&
a.Revision.Equals(Revision));
}
public async Task SelectMigrationsAsync_RepeatablesRerunWhenDependenciesUpdated()
{
migrationProvider.GetMigrationHistoryAsync()
.Returns(new[]
{
new FakeDatabaseMigrationRecord()
{
ModuleName = "view",
Checksum = "xyz",
TimeApplied = Clock.Current.Now
}
});
migrationRegistry.Migrations.Returns(new List <IDatabaseMigration>()
{
new FakeDatabaseMigration()
{
ModuleName = "data",
Version = DatabaseVersion.Parse("1")
},
new FakeDatabaseMigration()
{
ModuleName = "view",
IsRepeatable = true,
Checksum = "xyz",
Dependencies = new []
{
new DatabaseMigrationSpecifier("data", null),
}
}
});
var migrations = await sut.SelectMigrationsAsync(
new[]
{
new DatabaseMigrationSpecifier("data", null),
new DatabaseMigrationSpecifier("view", null)
},
new string[0]);
migrations.Should().HaveCount(2);
migrations.First().Specifier.Should().Be(new DatabaseMigrationSpecifier("data", null));
migrations.First().Migrations.Should().Equal(
migrationRegistry.Migrations.ElementAt(0));
migrations.ElementAt(1).Specifier.Should().Be(new DatabaseMigrationSpecifier("view", null));
migrations.ElementAt(1).Migrations.Should().Equal(
migrationRegistry.Migrations.ElementAt(1));
}
public IEnumerable <ISchemaChange> GetAllChanges()
{
var changes = _provider.GetAllChanges();
if (!changes.Any())
{
return(new ISchemaChange[0]);
}
var maxVersion = changes.Max(c => c.Version);
var baseVersion = new DatabaseVersion(maxVersion.MajorVersion, maxVersion.MinorVersion, maxVersion.PatchVersion, 0);
return(from c in changes
select c.Version >= baseVersion ? new ChangeDecorator(c) : c);
}
public DatabaseVersion Get()
{
DatabaseVersion obj = null;
try
{
obj = _context.db.GetAll <DatabaseVersion>()
.SingleOrDefault();
}
catch (Exception ex)
{
_log.Error("Error:", ex);
}
return(obj);
}
/// <summary>
/// Updates the database to the given main version by executing diffs or undo diffs.
/// </summary>
/// <param name="version">The target version.</param>
/// <param name="afterFileExecution">Action which is called after a file was executed with a List of all SQLFiles
/// (<see cref="IEnumerable{SQLFile}"/>) and the currently executed one.</param>
/// <exception cref="TeamworkConnectionException">Is thrown when an error occurred while executing the SQL Statements.</exception>
public void UpdateToVersion(DatabaseVersion version, Action <IEnumerable <SQLFile>, SQLFile> afterFileExecution = null)
{
var files = new List <SQLFile>(this.GetToBeAppliedSQLFiles(version));
Log.Info(string.Format("Upgrade database from version {0} to version {1} with {2} sql files", this.CurrentVersion, version, files.Count()));
// execute diffs
for (var fileIndex = 0; fileIndex < files.Count(); fileIndex++)
{
var file = files.ElementAt(fileIndex);
file.ExecuteInTransaction();
this.UpdateVersion();
afterFileExecution?.Invoke(files, file);
}
}
/// <summary>
/// Adds a database version record to the database, preventing this script from running again in the future.
/// </summary>
/// <param name="fileName">The file name, WITHOUT namespacing.</param>
private void AddDatabaseVersionRecord(string fileName)
{
var versionInfo = GetVersionInformation(fileName);
var version = new DatabaseVersion
{
DateApplied = DateTime.UtcNow,
ScriptName = fileName,
VersionDate = versionInfo.Item1,
VersionNumber = versionInfo.Item2
};
using (var connection = new SqlConnection(_swlorConnectionString))
{
connection.Insert(version);
}
}
public void ScanNonUniqueClusteredTable(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("NonUniqueClusteredTable").ToList();
Assert.AreEqual(112, rows[0].Field <int>("Num1"));
Assert.AreEqual("Doe", rows[0].Field <string>("Name"));
Assert.AreEqual(0, rows[0].Field <int>(DataColumn.Uniquifier));
Assert.AreEqual(112, rows[1].Field <int>("Num1"));
Assert.AreEqual("Doe", rows[1].Field <string>("Name"));
Assert.AreEqual(1, rows[1].Field <int>(DataColumn.Uniquifier));
});
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
// A garbage bitmap may occur if it's added to an existing column that did not already have a null bitmap
RunQuery(@" CREATE TABLE Garbage (A int sparse)
INSERT INTO Garbage VALUES (5)
ALTER TABLE Garbage ADD B int NULL
ALTER TABLE Garbage ADD C int NULL
ALTER TABLE Garbage ADD D int NULL" , conn);
// Second test of garbage bitmaps
RunQuery(@" CREATE TABLE Garbage2 (A int sparse)
INSERT INTO Garbage2 VALUES (5)
ALTER TABLE Garbage2 ADD B int NULL
UPDATE Garbage2 SET B = 2
ALTER TABLE Garbage2 ADD C varchar(10)" , conn);
}
private void SaveNewSchemaVersion(int newSchemaVersion)
{
using (var con = DbConnection())
{
var dbVersion = new DatabaseVersion();
dbVersion.DatabaseVersionId = newSchemaVersion;
dbVersion.UpgradedOn = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
var insertSql = @"INSERT INTO DatabaseVersion (DatabaseVersionId, UpgradedOn) VALUES (@DatabaseVersionId, @UpgradedOn);";
// Add a column to store GridSquare level
con.Open();
con.Query(insertSql, dbVersion);
con.Close();
}
}
public async Task SelectMigrationsAsync_DependencyTags()
{
migrationRegistry.Migrations.Returns(new List <IDatabaseMigration>()
{
new FakeDatabaseMigration()
{
ModuleName = "appModule1",
Version = DatabaseVersion.Parse("1.0.0"),
Tags = new []
{
new [] { "DEV" }
},
Dependencies = new []
{
new DatabaseMigrationSpecifier("baseModule1", DatabaseVersion.Parse("1.0.0"))
}
},
new FakeDatabaseMigration()
{
ModuleName = "baseModule1",
Version = DatabaseVersion.Parse("1.0.0"),
Tags = new []
{
new [] { "PROD" }
}
},
new FakeDatabaseMigration()
{
ModuleName = "baseModule1",
Version = DatabaseVersion.Parse("1.0.0"),
Tags = new []
{
new [] { "DEV" }
}
}
});
var migrations = await sut.SelectMigrationsAsync(
new[] { new DatabaseMigrationSpecifier("appModule1", null) },
new[] { "DEV" });
migrations.Should().HaveCount(1);
migrations.First().Specifier.Should().Be(new DatabaseMigrationSpecifier("appModule1", null));
migrations.First().Migrations.Should().Equal(
migrationRegistry.Migrations.ElementAt(2),
migrationRegistry.Migrations.ElementAt(0));
}
private void DowngradeDatabase(IEnumerable <IScript> migrationScripts, DatabaseVersion currentVersion, long?targetVersionNumber, IScriptRunner scriptRunner, string schema)
{
var applicableScripts = migrationScripts.Where(s => currentVersion.IsNull() || s.Version <= currentVersion.Version)
.Where(s => !targetVersionNumber.HasValue || s.Version > targetVersionNumber).ToArray();
if (applicableScripts.IsNullOrEmpty())
{
Output.Warn("No migration scripts need to be run. Nothing will be done.");
}
else
{
Output.Info("The following scripts will be applied to the database:");
foreach (var script in applicableScripts)
{
Output.Info(script.Name, 1);
}
Output.EmptyLine();
Prompt.ForAnyKey("Press any key to start the 'down' migration. Ctrl-C to abort.");
foreach (var script in applicableScripts)
{
Output.Info("Executing script: {0}".FormatWith(script.Path));
try
{
scriptRunner.Execute(script);
}
catch (SqlPlusException)
{
if (!_force)
{
throw;
}
}
Output.Info("Script '{0}' completed.".FormatWith(script.Name));
Output.Text("Removing script contents for script '{0}' from applied scripts table".FormatWith(script.Name));
_appliedScriptsRepository.RemoveAppliedScript(script.AsDatabaseVersion(), schema);
Output.Text("Removing version {0} for script '{1}' from version table".FormatWith(script.Version, script.Name));
_versionRespository.RemoveVersion(script.AsDatabaseVersion(), schema);
}
}
}
public void Validate_MixingRepeatableAndNonRepeatable()
{
sut.AddMigration(new FakeDatabaseMigration()
{
ModuleName = "appModule1",
IsRepeatable = true
});
sut.AddMigration(new FakeDatabaseMigration()
{
ModuleName = "appModule1",
Version = DatabaseVersion.Parse("1.0.0")
});
sut.Invoking(x => x.ValidateMigrations())
.Should().Throw <DatabaseMigrationException>();
}
public IEnumerable <MenuItem> GetMenuItems()
{
List <MenuItem> menuItems = new List <MenuItem>();
var newsletterRoles = new List <string>()
{
"NewsletterAdmins", "NewsletterEditors", "CmsAdmins"
};
SectionMenuItem sectionMenuItem = new SectionMenuItem("Newsletter", "/global/newsletter");
sectionMenuItem.IsAvailable = x => newsletterRoles.Any(y => x.HttpContext.User.IsInRole(y));
menuItems.Add(sectionMenuItem);
// During installation, we'll show another menu
int version = DatabaseVersion.GetInstalledDatabaseVersion();
if (version == DatabaseVersion.NotInstalled)
{
// Link to database installer page
UrlMenuItem urlMenuItem = new UrlMenuItem("Newsletter Installation", "/global/newsletter/install",
NewsLetterConfiguration.GetModuleBaseDir() + "/plugin/admin/newsletterinstall.aspx");
urlMenuItem.IsAvailable = ((RequestContext request) => true);
urlMenuItem.SortIndex = 100;
menuItems.Add(urlMenuItem);
}
else
{
UrlMenuItem urlMenuItem = new UrlMenuItem("Newsletters", "/global/newsletter/newsletters", NewsLetterConfiguration.GetModuleBaseDir("/plugin/newsletters.aspx"));
urlMenuItem.IsAvailable = ((RequestContext request) => true);
urlMenuItem.SortIndex = 100;
menuItems.Add(urlMenuItem);
urlMenuItem = new UrlMenuItem("Lists", "/global/newsletter/lists", NewsLetterConfiguration.GetModuleBaseDir("/plugin/lists.aspx"));
urlMenuItem.IsAvailable = ((RequestContext request) => true);
urlMenuItem.SortIndex = 200;
menuItems.Add(urlMenuItem);
//TODO: Add this menu item, when admin settings are ready to be moved from appsettings to dds
//UrlMenuItem urlAdminMenuItem = new UrlMenuItem("Admin", "/global/newsletter/admin",
// NewsLetterConfiguration.GetModuleBaseDir() + "/plugin/Admin/NewsLetterAdmin.aspx");
//urlAdminMenuItem.IsAvailable = ((RequestContext request) => true);
//urlAdminMenuItem.SortIndex = 200;
}
return(menuItems.ToArray());
}
public async Task SelectMigrationsAsync_TopModuleDependencies()
{
migrationRegistry.Migrations.Returns(new List <IDatabaseMigration>()
{
new FakeDatabaseMigration()
{
ModuleName = "appModule1",
Version = DatabaseVersion.Parse("1.0.0"),
Dependencies = new [] { new DatabaseMigrationSpecifier("baseModule1", DatabaseVersion.Parse("1.0.0")) }
},
new FakeDatabaseMigration()
{
ModuleName = "appModule2",
Version = DatabaseVersion.Parse("1.0.0"),
Dependencies = new [] { new DatabaseMigrationSpecifier("baseModule1", DatabaseVersion.Parse("1.0.0")) }
},
new FakeDatabaseMigration()
{
ModuleName = "baseModule1",
Version = DatabaseVersion.Parse("1.0.0")
}
});
var migrations = await sut.SelectMigrationsAsync(
new[]
{
new DatabaseMigrationSpecifier("appModule1", null),
new DatabaseMigrationSpecifier("appModule2", null)
},
new string[0]);
migrations.Should().HaveCount(2);
migrations.Should().Contain(x => x.Specifier.Equals(new DatabaseMigrationSpecifier("appModule1", null)));
migrations.Should().Contain(x => x.Specifier.Equals(new DatabaseMigrationSpecifier("appModule2", null)));
var a1 = migrations.First(x => x.Specifier.ModuleName == "appModule1");
a1.Migrations.Should().Equal(
migrationRegistry.Migrations.ElementAt(2),
migrationRegistry.Migrations.ElementAt(0));
var a2 = migrations.First(x => x.Specifier.ModuleName == "appModule2");
a2.Migrations.Should().Equal(
migrationRegistry.Migrations.ElementAt(1));
}
/// <summary>
/// Creates a SQL statement which lets you add a row in the executed file table of the teamwork schema.
/// </summary>
/// <param name="version">Version of the SQL file.</param>
/// <param name="fileType">The type of the file (diff, dump...).</param>
/// <param name="message">An optional message which is added into the database table.</param>
/// <returns>The SQL as string which can be executed on the database.</returns>
public static string AddExecutedFileSql(DatabaseVersion version, FileType fileType, string message = "")
{
// ignores the whole statement which add the file to the ExecutedFiles if it is an undo diff
var ignore = string.Empty;
if (fileType == FileType.UndoDiff)
{
ignore = Comment;
}
return(addExecutedFile
.Replace("[Schema]", PostgreSQLTeamworkSchemaName)
.Replace("[Version]", version.Full)
.Replace("[Time]", DateTime.Now.ToString("dd.MM.yyyy hh:mm"))
.Replace("[FileType]", fileType.ToString())
.Replace("[Message]", message)
.Replace("[Ignore]", ignore));
}
public DatabaseVersion Upgrade(string oldDbFile)
{
using (var scope = new TransactionScope())
{
Log.WriteInfo("DatabaseUpdater20To30. OldFile={0}", oldDbFile);
if (File.Exists(oldDbFile))
{
Log.WriteInfo("File exists. Start converting db from 2.0.0.0 to 3.0.0.0");
importProfiles(oldDbFile);
}
Log.WriteInfo("Set database version to 3.0.0.0");
DatabaseVersion version = new DatabaseVersion("3.0.0.0");
version.Create();
scope.VoteCommit();
Log.WriteInfo("DatabaseUpdater20To30. Everything is ok");
return(version);
}
}
public void ScanNonSparseInts(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("ScanNonSparseInts").ToList();
Assert.AreEqual(123, rows[0].Field <int?>("A"));
Assert.AreEqual(null, rows[0].Field <int?>("B"));
Assert.AreEqual(null, rows[0].Field <int?>("C"));
Assert.AreEqual(127, rows[0].Field <int?>("D"));
Assert.AreEqual(null, rows[1].Field <int?>("A"));
Assert.AreEqual(null, rows[1].Field <int?>("B"));
Assert.AreEqual(123982, rows[1].Field <int?>("C"));
Assert.AreEqual(null, rows[1].Field <int?>("D"));
});
}
public void BitTests(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("BitTests").ToList();
Assert.AreEqual(null, rows[0].Field <bool?>("A"));
Assert.AreEqual(true, rows[1].Field <bool?>("A"));
Assert.AreEqual(true, rows[2].Field <bool?>("A"));
Assert.AreEqual(false, rows[3].Field <bool?>("A"));
Assert.AreEqual(true, rows[4].Field <bool?>("A"));
Assert.AreEqual(false, rows[5].Field <bool?>("A"));
Assert.AreEqual(false, rows[6].Field <bool?>("A"));
Assert.AreEqual(null, rows[7].Field <bool?>("A"));
Assert.AreEqual(true, rows[8].Field <bool?>("A"));
});
}
public void VardecimalTest(DatabaseVersion version)
{
RunDatabaseTest(version, db =>
{
var scanner = new DataScanner(db);
var rows = scanner.ScanTable("VardecimalTest").ToList();
Assert.AreEqual(12.3m, rows[0].Field <decimal>("A"));
Assert.AreEqual(0m, rows[0].Field <decimal>("B"));
Assert.AreEqual(1m, rows[0].Field <decimal>("C"));
Assert.AreEqual(12345m, rows[0].Field <decimal>("D"));
Assert.AreEqual(39201.230m, rows[0].Field <decimal>("E"));
Assert.AreEqual(-4892384.382090m, rows[0].Field <decimal>("F"));
Assert.AreEqual(1328783742987.29m, rows[0].Field <decimal>("G"));
Assert.AreEqual(2940382040198493029.235m, rows[0].Field <decimal>("H"));
Assert.AreEqual(-1m, rows[0].Field <decimal>("I"));
});
}
protected override void RunSetupQueries(SqlConnection conn, DatabaseVersion version)
{
// Create unique clustered table
RunQuery(@" CREATE TABLE UniqueClusteredTable
(
Num1 int NOT NULL,
Name nvarchar(30)
)
CREATE UNIQUE CLUSTERED INDEX CX_Num1_Name ON UniqueClusteredTable (Num1, Name)
INSERT INTO UniqueClusteredTable (Num1, Name) VALUES (382, 'John')
INSERT INTO UniqueClusteredTable (Num1, Name) VALUES (112, 'Doe')" , conn);
// Create non-unique clustered table with uniquifier
RunQuery(@" CREATE TABLE NonUniqueClusteredTable
(
Num1 int NOT NULL,
Name nvarchar(30)
)
CREATE CLUSTERED INDEX CX_Num1_Name ON NonUniqueClusteredTable (Num1, Name)
INSERT INTO NonUniqueClusteredTable (Num1, Name) VALUES (112, 'Doe')
INSERT INTO NonUniqueClusteredTable (Num1, Name) VALUES (112, 'Doe')" , conn);
// Create table with data, drop a few columns
RunQuery(@" CREATE TABLE IgnoreDroppedColumnData
(
A int,
B int,
C varchar(30) NOT NULL,
D int
)
INSERT INTO
IgnoreDroppedColumnData (A, B, C, D)
VALUES
(1, 2, 'A', 3)
ALTER TABLE IgnoreDroppedColumnData ALTER COLUMN A int NOT NULL
ALTER TABLE IgnoreDroppedColumnData ALTER COLUMN B int NOT NULL
ALTER TABLE IgnoreDroppedColumnData ALTER COLUMN D int NOT NULL
UPDATE IgnoreDroppedColumnData SET B = 27" , conn);
}
