private bool CheckNoDupTreeDefaults(CruiseDatastore v2db)
{
var counts = v2db.QueryScalar <int>("SELECT count(1) FROM TreeDefaultValue GROUP BY Species, LiveDead, PrimaryProduct;");
var hasDupTreeDefaults = counts.Any(x => x > 1);
return(hasDupTreeDefaults is false);
}
IEnumerable <string> DiffTables(CruiseDatastore left, CruiseDatastore right)
{
var leftTables = left.GetTableNames();
var rightTables = right.GetTableNames();
return(leftTables.Except(rightTables));
}
public void update_from_2_5_to_2_7()
{
var fileName = "v2_5_0.cruise";;
var path = InitializeTestFile(fileName);
Output.WriteLine(path);
using (var db = new CruiseDatastore(path))
{
var updater = new Updater_V2();
updater.Update(db);
db.CurrentTransaction.Should().BeNull();
// insert multiple trees with the same guid to make sure that the tree guid uniqe constraint is removed
//for (var i = 0; i < 2; i++)
//{
// db.Insert(new V2.Models.Tree()
// {
// Tree_GUID = "something",
// CuttingUnit_CN = 1,
// Stratum_CN = 1,
// SampleGroup_CN = 1,
// TreeDefaultValue_CN = 1,
// });
//}
db.DatabaseVersion.Should().StartWith("2.7.");
}
}
public void Update_FROM_05_30_2013()
{
var filePath = Path.Combine(TestTempPath, "TestUpdate.cruise");
try
{
using (var setup = new SQLiteDatastore(filePath))
{
setup.Execute(CruiseDAL.Tests.SQL.CRUISECREATE_05_30_2013);
}
using (var database = new CruiseDatastore(filePath))
{
var dataStore = new CruiseDatastore(filePath);
var updater = new Updater_V2();
updater.Invoking(x => x.Update(dataStore)).Should().Throw <IncompatibleSchemaException>();
database.CurrentTransaction.Should().BeNull();
}
}
finally
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
}
public void MigrateFromV2ToV3(string v2Path, CruiseDatastore_V3 v3db, string deviceID = null)
{
using (var v2Cruise = new CruiseDatastore(v2Path, false, null, new Updater_V2()))
{
MigrateFromV2ToV3(v2Cruise, v3db, deviceID: deviceID);
}
}
public void Tree_Test(string fileName)
{
var(orgFile, crz3File, origAgain) = SetUpTestFile(fileName);
using (var dbv2 = new CruiseDatastore(orgFile))
using (var dbv2Arain = new CruiseDatastore(origAgain))
{
var treev2Again = dbv2Arain.From <V2.Models.Tree>()
.Query().ToArray();
treev2Again.Should().NotBeEmpty();
var treev2 = dbv2.From <V2.Models.Tree>()
.Query().ToArray();
treev2.Should().NotBeEmpty();
treev2Again.Should().HaveSameCount(treev2);
treev2Again.Should().BeEquivalentTo(treev2,
config => config
.Excluding(y => y.TreeDefaultValue_CN)
.Excluding(y => y.TreeFactor)
.Excluding(y => y.PointFactor)
.Excluding(y => y.ExpansionFactor)
.Excluding(y => y.Tree_GUID));
}
}
public void FixVersion_2_5_0(string fileName)
{
var targetPath = InitializeTestFile(fileName);
using (var db = new CruiseDatastore(targetPath))
{
//var trees = db.From<TreeDO>().Query().ToArray();
db.From <TreeDOold>().Invoking(x => x.Query().ToArray()).Should().NotThrow();
var errorTrees = db.QueryGeneric("SELECT * FROM Tree WHERE typeof(Tree_GUID) = 'text' AND Tree_GUID NOT LIKE '________-____-____-____-____________';")
.ToArray();
errorTrees.Should().NotBeEmpty();
var updater = new Updater_V2();
updater.Update(db);
//db.DatabaseVersion.Should().Be("2.5.1.1");
var errorTreesAgain = db.QueryGeneric("SELECT * FROM Tree WHERE typeof(Tree_GUID) = 'text' AND Tree_GUID NOT LIKE '________-____-____-____-____________';")
.ToArray();
errorTreesAgain.Should().BeEmpty();
db.From <TreeDOold>().Invoking(x => x.Query()).Should().NotThrow();
}
}
public void Update_FROM_2015_01_05()
{
var filePath = Path.Combine(TestTempPath, "TestUpdate.cruise");
try
{
using (var setup = new SQLiteDatastore(filePath))
{
setup.Execute(CruiseDAL.Tests.SQL.CRUISECREATE_2015_01_05);
}
using (var datastore = new CruiseDatastore(filePath))
{
var updater = new Updater_V2();
updater.Invoking(x => x.Update(datastore))
.Should().NotThrow();
var semVerActual = new Version(datastore.DatabaseVersion);
var semVerExpected = new Version("2.7.0");
semVerActual.Major.Should().Be(semVerExpected.Major);
semVerActual.Minor.Should().Be(semVerExpected.Minor);
ValidateUpdate(datastore);
}
}
finally
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
}
private void ValidateUpdate(CruiseDatastore database)
{
using (var fresh = new DAL())
{
var tDiff = DiffTables(fresh, database);
tDiff.Should().BeEmpty();
var diff = DiffTableInfo(fresh, database);
diff.Except(new[] { ("TreeDefaultValue", "Chargeable") })
void ValidateCRUD(CruiseDatastore ds, Type type)
{
var entDesc = new EntityDescription(type);
var commandBuilder = new CommandBuilder();
var selectBuilder = commandBuilder.BuildSelect(entDesc.Source, entDesc.Fields);
var selectCommand = selectBuilder.ToString();
var selectResult = ds.QueryGeneric(selectCommand);
}
void ValidateCRUD(CruiseDatastore ds)
{
var dataobjects = typeof(TreeDO).Assembly.GetTypes()
.Where(x => x.Namespace == "CruiseDAL.DataObjects");
foreach (var doType in dataobjects)
{
ValidateCRUD(ds, doType);
}
}
IEnumerable <string> DiffTableInfo(CruiseDatastore left, CruiseDatastore right, string table)
{
var leftValues = left.QueryGeneric(
$@"SELECT '{table}.' || name FROM pragma_table_info('{table}') where Name != 'CreatedBy' AND Name != 'CreatedDate'; ").ToArray();
var rightValues = right.QueryGeneric(
$@"SELECT '{table}.' || name FROM pragma_table_info('{table}'); ").ToArray();
return(leftValues.Select(x => x.Values.First().ToString())
.Except(rightValues.Select(x => x.Values.First().ToString())).ToArray());
}
public void UpdateFrom340_To_342()
{
var filePath = InitializeTestFile("UpdateFrom340_To_342.crz3");
using var ds = new CruiseDatastore(filePath);
var updater = new Updater_V3();
updater.Update(ds);
ds.Invoking(x => x.Execute("DELETE FROM Plot_Stratum;")).Should().NotThrow <FMSC.ORM.SQLException>();
}
public static List <T> ReadCruiseMethods <T>(CruiseDatastore db, bool reconMethodsOnly) where T : CruiseMethodsDO, new()
{
if (reconMethodsOnly)
{
return(db.From <T>()
.Where("Code = 'FIX' OR Code = 'PNT'")
.Read().ToList());
}
else
{
return(db.From <T>().Read().ToList());
}
}
public bool EnsureCanMigrate(CruiseDatastore v2db, out string errorMsg)
{
errorMsg = null;
if (CheckNoDupTreeDefaults(v2db))
{
return(true);
}
else
{
errorMsg = "Cruise Has Multiple Tree Defaults With The Same Species, LiveDead and PrimaryProduct";
return(false);
}
}
IEnumerable <IEnumerable <string> > DiffTableInfo(CruiseDatastore left, CruiseDatastore right)
{
var tables = left.GetTableNames();
foreach (var t in tables)
{
var diff = DiffTableInfo(left, right, t);
if (diff.Any())
{
yield return(diff);
}
}
}
public void AttachDB_DetachDB_inmemory()
{
using (var maindb = new CruiseDatastore())
using (var db2 = new CruiseDatastore())
{
maindb.AttachDB(db2, "db2");
maindb.Invoking(x => x.Execute("SELECT count(*) FROM db2.sqlite_master"))
.Should().NotThrow();
maindb.DetachDB("db2");
maindb.Invoking(x => x.Execute("SELECT count(*) FROM db2.sqlite_master"))
.Should().Throw <SQLException>();
}
}
protected void ValidateUpdate(CruiseDatastore database)
{
VerifyTablesCanDelete(database);
ValidateCRUD(database);
using (var fresh = new DAL())
{
var tDiff = DiffTables(fresh, database);
tDiff.Should().BeEmpty();
var diff = DiffTableInfo(fresh, database);
diff.Should().BeEmpty();
}
database.CheckTableExists("SamplerState").Should().BeTrue();
}
public void Update(string fileName)
{
var filePath = InitializeTestFile(fileName);
using (var ds = new CruiseDatastore(filePath))
{
var orgDbVersion = ds.DatabaseVersion;
orgDbVersion.Should().Be(Path.GetFileNameWithoutExtension(fileName));
var unitCount = ds.GetRowCount("CuttingUnit", "");
var updater = new Updater_V3();
updater.Update(ds);
var unitCountAfter = ds.GetRowCount("CuttingUnit", "");
unitCountAfter.Should().Be(unitCount);
var verAfter = ds.DatabaseVersion;
verAfter.Should().Be(CruiseDatastoreBuilder_V3.DATABASE_VERSION.ToString());
var tableDefs = CruiseDatastoreBuilder_V3.TABLE_DEFINITIONS;
foreach (var t in tableDefs)
{
var ti = ds.GetTableInfo(t.TableName);
ti.Should().NotBeNullOrEmpty(t.TableName);
}
var sale = ds.From <Sale>().Query().Single();
sale.Should().NotBeNull();
var cruise = ds.From <Cruise>().Query().Single();
cruise.SaleNumber.Should().Be(sale.SaleNumber);
var logs = ds.From <Log>().Query().ToArray();
if (logs.Any())
{
logs.Should().OnlyContain(x => string.IsNullOrEmpty(x.CruiseID) == false);
}
// do integrity check
var ic_results = ds.QueryScalar <string>("PRAGMA integrity_check;");
ic_results.Should().HaveCount(1);
ic_results.Single().Should().Be("ok");
}
}
public void MigrateFromV2ToV3(CruiseDatastore v2db, CruiseDatastore_V3 v3db, string deviceID = null)
{
var oldDbAlias = "v2";
v3db.AttachDB(v2db, oldDbAlias);
try
{
var connection = v3db.OpenConnection();
MigrateFromV2ToV3(connection, oldDbAlias, deviceID: deviceID, exceptionProcessor: v3db.ExceptionProcessor, migrators: Migrators);
}
finally
{
v3db.DetachDB(oldDbAlias);
v3db.ReleaseConnection();
}
}
public static string[] ReadCruiseMethodStr(CruiseDatastore db, bool reconMethodsOnly)
{
var format = "Select group_concat(Code,',') FROM CruiseMethods {0};";
string command = string.Format(format,
(reconMethodsOnly) ? "WHERE Code = 'FIX' OR Code = 'PNT'" : string.Empty);
string result = db.ExecuteScalar(command) as string ?? string.Empty;
if (string.IsNullOrEmpty(result))
{
return(new string[0]);
}
else
{
return(result.Split(','));
}
}
protected void VerifyTablesCanDelete(CruiseDatastore datastore)
{
var tableNames = datastore.ExecuteScalar <string>("SELECT group_concat(Name) FROM sqlite_master WHERE Type = 'table';").Split(',');
foreach (var table in tableNames)
{
try
{
datastore.Execute($"DELETE FROM {table};");
}
catch (Exception e)
{
Output.WriteLine(e.Message);
Output.WriteLine(e.InnerException.Message);
}
//datastore.Invoking(x => x.Execute($"DELETE FROM {table};")).Should().NotThrow();
}
}
public void Reports_Test(string fileName)
{
var(orgFile, crz3File, origAgain) = SetUpTestFile(fileName);
using (var dbv2 = new CruiseDatastore(orgFile))
using (var dbv2Again = new CruiseDatastore(origAgain))
{
var reportsV2Again = dbv2Again.From <V2.Models.Reports>()
.Query().ToArray();
reportsV2Again.Should().NotBeEmpty();
var reportsV2 = dbv2.From <V2.Models.Reports>()
.Query().ToArray();
reportsV2.Should().NotBeEmpty();
reportsV2Again.Should().HaveSameCount(reportsV2);
reportsV2Again.Should().BeEquivalentTo(reportsV2);
}
}
public void CuttingUnits_Test(string fileName)
{
var(orgFile, crz3File, origAgain) = SetUpTestFile(fileName);
using (var dbv2 = new CruiseDatastore(orgFile))
using (var dbv2Again = new CruiseDatastore(origAgain))
{
var cuttingUnitsV2Again = dbv2Again.From <V2.Models.CuttingUnit>()
.Query();
var cuttingUnitsv2 = dbv2.From <V2.Models.CuttingUnit>()
.Query();
cuttingUnitsV2Again.Should().BeEquivalentTo(cuttingUnitsv2,
config => config
.Using <string>(x => x.Subject.Should().Be(x.Expectation?.Trim())).WhenTypeIs <string>()// ignore whitespace when type is string
.Excluding(y => y.TallyHistory));
}
}
public void SampleGroup_Test(string fileName)
{
var(orgFile, crz3File, origAgain) = SetUpTestFile(fileName);
using (var dbv2 = new CruiseDatastore(orgFile))
using (var dbv2Again = new CruiseDatastore(origAgain))
{
var sgv2Again = dbv2Again.From <V2.Models.SampleGroup>()
.Query();
var sgv2 = dbv2.From <V2.Models.SampleGroup>()
.Query();
sgv2Again.Should().BeEquivalentTo(sgv2, x =>
x.Excluding(y => y.SampleSelectorState)
.Excluding(y => y.SampleSelectorType)
.Excluding(y => y.TallyMethod));
}
}
void ValidateMigration(CruiseDatastore v3db, CruiseDatastore v2db)
{
var units = v3db.From <V3.Models.CuttingUnit>().Query();
var strata = v3db.From <V3.Models.Stratum>().Query().ToArray();
var samplegroups = v3db.From <V3.Models.SampleGroup>().Query();
var plots = v3db.From <V3.Models.Plot>().Query();
var trees = v3db.From <V3.Models.Tree>().Query();
var units2 = v2db.From <V2.Models.CuttingUnit>().Query();
var strata2 = v2db.From <V2.Models.Stratum>().Query().ToArray();
var samplegroups2 = v2db.From <V2.Models.SampleGroup>().Query();
var plots2 = v2db.From <V2.Models.Plot>().Query();
var trees2 = v2db.From <V2.Models.Tree>().Query();
units.Should().HaveSameCount(units2);
strata.Should().HaveSameCount(strata2);
samplegroups.Should().HaveSameCount(samplegroups2);
plots.Should().HaveSameCount(plots2);
trees.Should().HaveSameCount(trees2);
}
public void Stratum_Test(string fileName)
{
var(orgFile, crz3File, origAgain) = SetUpTestFile(fileName);
using (var dbv2 = new CruiseDatastore(orgFile))
using (var dbv2Again = new CruiseDatastore(origAgain))
{
var stratav2Again = dbv2Again.From <V2.Models.Stratum>()
.Query();
var stratav2 = dbv2.From <V2.Models.Stratum>()
.Query();
stratav2Again.Should().BeEquivalentTo(stratav2,
config => config
.Excluding(x => x.VolumeFactor)
.Excluding(x => x.Month)
.Excluding(x => x.Year));
}
}
public void Sync(string cruiseID, CruiseDatastore source, CruiseDatastore destination, CruiseSyncOptions options)
{
var sourceConn = source.OpenConnection();
try
{
var destConn = destination.OpenConnection();
try
{
Sync(cruiseID, sourceConn, destConn, options);
}
finally
{
destination.ReleaseConnection();
}
}
finally
{
source.ReleaseConnection();
}
}
// test that all positive counts are the same after round trip conversion
// we only test positive counts because additional 0 count records can be
// created in the conversion process
public void RountTrip_CountTree_Test_only_positiveTreeCounts(string fileName)
{
var(orgFile, crz3File, origAgain) = SetUpTestFile(fileName);
using (var dbv2 = new CruiseDatastore(orgFile))
using (var dbv2Again = new CruiseDatastore(origAgain))
{
var countTreeV2Again = dbv2Again.From <V2.Models.CountTree>()
.Where("TreeCount > 0")
.Query();
countTreeV2Again.Should().NotBeEmpty();
var countTreeV2 = dbv2.From <V2.Models.CountTree>()
.Where("TreeCount > 0")
.GroupBy("CuttingUnit_CN", "SampleGroup_CN", "ifnull(TreeDefaultValue_CN, '')")
.Query();
countTreeV2.Should().NotBeEmpty();
countTreeV2Again.Should().HaveSameCount(countTreeV2);
countTreeV2Again.Should().BeEquivalentTo(countTreeV2,
x => x.Excluding(y => y.Tally_CN)
.Excluding(y => y.CountTree_CN));
}
}
private static bool HasBlankCountOrMeasure(CruiseDatastore dal)
{
return(dal.GetRowCount(TREE._NAME, "WHERE ifnull(CountOrMeasure, '') = ''") > 0);
}
