public static bool TryCreateOrOpen(string filePath, out TemplateDatabase templateDatabase)
{
// check for an existing database before instantiating the databse as SQL wrapper instantiation creates the database file
bool populateDatabase = !File.Exists(filePath);
templateDatabase = new TemplateDatabase(filePath);
if (populateDatabase)
{
// initialize the database if it's newly created
templateDatabase.OnDatabaseCreated(null);
}
else
{
// if it's an existing database check if it needs updating to current structure and load data tables
if (templateDatabase.OnExistingDatabaseOpened(null) == false)
{
return false;
}
}
return true;
}
protected virtual bool OnExistingDatabaseOpened(TemplateDatabase other)
{
List<string> tables = this.Database.GetTableNames();
if (tables.Contains(Constant.DatabaseTable.Controls) == false)
{
return false;
}
this.GetControlsSortedByControlOrder();
return true;
}
public virtual void Verify(TemplateDatabase templateDatabase)
{
Assert.IsTrue(templateDatabase.Controls.RowCount == TestConstant.DefaultFileDataColumns.Count - 1);
int rowIndex = 0;
this.File.Verify(templateDatabase.Controls[rowIndex++]);
this.RelativePath.Verify(templateDatabase.Controls[rowIndex++]);
this.DateTime.Verify(templateDatabase.Controls[rowIndex++]);
this.UtcOffset.Verify(templateDatabase.Controls[rowIndex++]);
this.ImageQuality.Verify(templateDatabase.Controls[rowIndex++]);
this.DeleteFlag.Verify(templateDatabase.Controls[rowIndex++]);
}
protected virtual void OnDatabaseCreated(TemplateDatabase other)
{
// create the template table
List<ColumnDefinition> templateTableColumns = new List<ColumnDefinition>();
templateTableColumns.Add(new ColumnDefinition(Constant.DatabaseColumn.ID, Constant.Sql.CreationStringPrimaryKey));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.ControlOrder, Constant.Sql.Integer));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.SpreadsheetOrder, Constant.Sql.Integer));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.Type, Constant.Sql.Text));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.DefaultValue, Constant.Sql.Text));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.Label, Constant.Sql.Text));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.DataLabel, Constant.Sql.Text));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.Tooltip, Constant.Sql.Text));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.Width, Constant.Sql.Integer));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.Copyable, Constant.Sql.Text));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.Visible, Constant.Sql.Text));
templateTableColumns.Add(new ColumnDefinition(Constant.Control.List, Constant.Sql.Text));
this.Database.CreateTable(Constant.DatabaseTable.Controls, templateTableColumns);
// if an existing table was passed, clone its contents into this database
if (other != null)
{
this.SyncTemplateTableToDatabase(other.Controls);
return;
}
// no existing table to clone, so add standard controls to template table
List<List<ColumnTuple>> standardControls = new List<List<ColumnTuple>>();
long controlOrder = 0; // The control order, a one based count incremented for every new entry
long spreadsheetOrder = 0; // The spreadsheet order, a one based count incremented for every new entry
// file
List<ColumnTuple> file = new List<ColumnTuple>();
file.Add(new ColumnTuple(Constant.Control.ControlOrder, ++controlOrder));
file.Add(new ColumnTuple(Constant.Control.SpreadsheetOrder, ++spreadsheetOrder));
file.Add(new ColumnTuple(Constant.Control.Type, Constant.DatabaseColumn.File));
file.Add(new ColumnTuple(Constant.Control.DefaultValue, Constant.ControlDefault.Value));
file.Add(new ColumnTuple(Constant.Control.Label, Constant.DatabaseColumn.File));
file.Add(new ColumnTuple(Constant.Control.DataLabel, Constant.DatabaseColumn.File));
file.Add(new ColumnTuple(Constant.Control.Tooltip, Constant.ControlDefault.FileTooltip));
file.Add(new ColumnTuple(Constant.Control.Width, Constant.ControlDefault.FileWidth));
file.Add(new ColumnTuple(Constant.Control.Copyable, false));
file.Add(new ColumnTuple(Constant.Control.Visible, true));
file.Add(new ColumnTuple(Constant.Control.List, Constant.ControlDefault.Value));
standardControls.Add(file);
// relative path
standardControls.Add(this.GetRelativePathTuples(++controlOrder, ++spreadsheetOrder, true));
// datetime
standardControls.Add(this.GetDateTimeTuples(++controlOrder, ++spreadsheetOrder, true));
// utcOffset
standardControls.Add(this.GetUtcOffsetTuples(++controlOrder, ++spreadsheetOrder, false));
// image quality
List<ColumnTuple> imageQuality = new List<ColumnTuple>();
imageQuality.Add(new ColumnTuple(Constant.Control.ControlOrder, ++controlOrder));
imageQuality.Add(new ColumnTuple(Constant.Control.SpreadsheetOrder, ++spreadsheetOrder));
imageQuality.Add(new ColumnTuple(Constant.Control.Type, Constant.DatabaseColumn.ImageQuality));
imageQuality.Add(new ColumnTuple(Constant.Control.DefaultValue, Constant.ControlDefault.Value));
imageQuality.Add(new ColumnTuple(Constant.Control.Label, Constant.DatabaseColumn.ImageQuality));
imageQuality.Add(new ColumnTuple(Constant.Control.DataLabel, Constant.DatabaseColumn.ImageQuality));
imageQuality.Add(new ColumnTuple(Constant.Control.Tooltip, Constant.ControlDefault.ImageQualityTooltip));
imageQuality.Add(new ColumnTuple(Constant.Control.Width, Constant.ControlDefault.ImageQualityWidth));
imageQuality.Add(new ColumnTuple(Constant.Control.Copyable, false));
imageQuality.Add(new ColumnTuple(Constant.Control.Visible, true));
imageQuality.Add(new ColumnTuple(Constant.Control.List, Constant.ImageQuality.ListOfValues));
standardControls.Add(imageQuality);
// delete flag
standardControls.Add(this.GetDeleteFlagTuples(++controlOrder, ++spreadsheetOrder, true));
// insert standard controls into the template table
this.Database.Insert(Constant.DatabaseTable.Controls, standardControls);
// populate the in memory version of the template table
this.GetControlsSortedByControlOrder();
}
private void VerifyControls(TemplateDatabase database)
{
for (int row = 0; row < database.Controls.RowCount; ++row)
{
// sanity check control
ControlRow control = database.Controls[row];
this.VerifyControl(control);
// verify controls are sorted in control order and that control order is ones based
Assert.IsTrue(control.ControlOrder == row + 1);
}
}
private void VerifyTemplateDatabase(TemplateDatabase templateDatabase, string templateDatabaseBaseFileName)
{
// sanity checks
Assert.IsNotNull(templateDatabase);
Assert.IsNotNull(templateDatabase.FilePath);
Assert.IsNotNull(templateDatabase.Controls);
// FilePath checks
string templateDatabaseFileName = Path.GetFileName(templateDatabase.FilePath);
Assert.IsTrue(templateDatabaseFileName.StartsWith(Path.GetFileNameWithoutExtension(templateDatabaseBaseFileName)));
Assert.IsTrue(templateDatabaseFileName.EndsWith(Path.GetExtension(templateDatabaseBaseFileName)));
Assert.IsTrue(File.Exists(templateDatabase.FilePath));
// TemplateTable checks
DataTable templateDataTable = templateDatabase.Controls.ExtractDataTable();
Assert.IsTrue(templateDataTable.Columns.Count == TestConstant.ControlsColumns.Count);
List<long> ids = new List<long>();
List<long> controlOrders = new List<long>();
List<long> spreadsheetOrders = new List<long>();
foreach (ControlRow control in templateDatabase.Controls)
{
ids.Add(control.ID);
controlOrders.Add(control.ControlOrder);
spreadsheetOrders.Add(control.SpreadsheetOrder);
}
List<long> uniqueIDs = ids.Distinct().ToList();
Assert.IsTrue(ids.Count == uniqueIDs.Count, "Expected {0} unique control IDs but found {1}. {2} id(s) are duplicated.", ids.Count, uniqueIDs.Count, ids.Count - uniqueIDs.Count);
List<long> uniqueControlOrders = controlOrders.Distinct().ToList();
Assert.IsTrue(controlOrders.Count == uniqueControlOrders.Count, "Expected {0} unique control orders but found {1}. {2} order(s) are duplicated.", controlOrders.Count, uniqueControlOrders.Count, controlOrders.Count - uniqueControlOrders.Count);
List<long> uniqueSpreadsheetOrders = spreadsheetOrders.Distinct().ToList();
Assert.IsTrue(spreadsheetOrders.Count == uniqueSpreadsheetOrders.Count, "Expected {0} unique spreadsheet orders but found {1}. {2} order(s) are duplicated.", spreadsheetOrders.Count, uniqueSpreadsheetOrders.Count, spreadsheetOrders.Count - uniqueSpreadsheetOrders.Count);
}
public override void Verify(TemplateDatabase templateDatabase)
{
base.Verify(templateDatabase);
int rowIndex = Constant.Control.StandardTypes.Count;
this.Counter0.Verify(templateDatabase.Controls[rowIndex++]);
this.Choice0.Verify(templateDatabase.Controls[rowIndex++]);
this.Note0.Verify(templateDatabase.Controls[rowIndex++]);
this.Flag0.Verify(templateDatabase.Controls[rowIndex++]);
this.CounterWithCustomDataLabel.Verify(templateDatabase.Controls[rowIndex++]);
this.ChoiceWithCustomDataLabel.Verify(templateDatabase.Controls[rowIndex++]);
this.NoteWithCustomDataLabel.Verify(templateDatabase.Controls[rowIndex++]);
this.FlagWithCustomDataLabel.Verify(templateDatabase.Controls[rowIndex++]);
this.CounterNotVisible.Verify(templateDatabase.Controls[rowIndex++]);
this.ChoiceNotVisible.Verify(templateDatabase.Controls[rowIndex++]);
this.NoteNotVisible.Verify(templateDatabase.Controls[rowIndex++]);
this.FlagNotVisible.Verify(templateDatabase.Controls[rowIndex++]);
this.Counter3.Verify(templateDatabase.Controls[rowIndex++]);
this.Choice3.Verify(templateDatabase.Controls[rowIndex++]);
this.Note3.Verify(templateDatabase.Controls[rowIndex++]);
this.Flag3.Verify(templateDatabase.Controls[rowIndex++]);
}
/// <summary>
/// Creates a file database unique to the calling test.
/// </summary>
protected FileDatabase CreateFileDatabase(TemplateDatabase templateDatabase, string fileDatabaseBaseFileName)
{
string fileDatabaseFilePath = this.GetUniqueFilePathForTest(fileDatabaseBaseFileName);
if (File.Exists(fileDatabaseFilePath))
{
File.Delete(fileDatabaseFilePath);
}
FileDatabase fileDatabase;
Assert.IsTrue(FileDatabase.TryCreateOrOpen(fileDatabaseFilePath, templateDatabase, false, CustomSelectionOperator.And, out fileDatabase));
return fileDatabase;
}
protected override bool OnExistingDatabaseOpened(TemplateDatabase templateDatabase)
{
// perform TemplateTable initializations and migrations, then check for synchronization issues
if (base.OnExistingDatabaseOpened(templateDatabase) == false)
{
return false;
}
List<string> templateDataLabels = templateDatabase.GetDataLabelsExceptIDInSpreadsheetOrder();
List<string> dataLabels = this.GetDataLabelsExceptIDInSpreadsheetOrder();
List<string> dataLabelsInTemplateButNotFileDatabase = templateDataLabels.Except(dataLabels).ToList();
foreach (string dataLabel in dataLabelsInTemplateButNotFileDatabase)
{
this.ControlSynchronizationIssues.Add("- A field with the DataLabel '" + dataLabel + "' was found in the template, but nothing matches that in the file database." + Environment.NewLine);
}
List<string> dataLabelsInIFileButNotTemplateDatabase = dataLabels.Except(templateDataLabels).ToList();
foreach (string dataLabel in dataLabelsInIFileButNotTemplateDatabase)
{
this.ControlSynchronizationIssues.Add("- A field with the DataLabel '" + dataLabel + "' was found in the file database, but nothing matches that in the template." + Environment.NewLine);
}
if (this.ControlSynchronizationIssues.Count == 0)
{
foreach (string dataLabel in dataLabels)
{
ControlRow fileDatabaseControl = this.FindControl(dataLabel);
ControlRow templateControl = templateDatabase.FindControl(dataLabel);
if (fileDatabaseControl.Type != templateControl.Type)
{
this.ControlSynchronizationIssues.Add(String.Format("- The field with DataLabel '{0}' is of type '{1}' in the image data file but of type '{2}' in the template.{3}", dataLabel, fileDatabaseControl.Type, templateControl.Type, Environment.NewLine));
}
List<string> fileDatabaseChoices = fileDatabaseControl.GetChoices();
List<string> templateChoices = templateControl.GetChoices();
List<string> choiceValuesRemovedInTemplate = fileDatabaseChoices.Except(templateChoices).ToList();
foreach (string removedValue in choiceValuesRemovedInTemplate)
{
this.ControlSynchronizationIssues.Add(String.Format("- The choice with DataLabel '{0}' allows the value of '{1}' in the image data file but not in the template.{2}", dataLabel, removedValue, Environment.NewLine));
}
}
}
// if there are no synchronization difficulties synchronize the image database's TemplateTable with the template's TemplateTable
if (this.ControlSynchronizationIssues.Count == 0)
{
foreach (string dataLabel in dataLabels)
{
ControlRow fileDatabaseControl = this.FindControl(dataLabel);
ControlRow templateControl = templateDatabase.FindControl(dataLabel);
if (fileDatabaseControl.Synchronize(templateControl))
{
this.SyncControlToDatabase(fileDatabaseControl);
}
}
}
return this.ControlSynchronizationIssues.Count == 0;
}
/// <summary>
/// Make an empty Data Table based on the information in the Template Table.
/// Assumes that the database has already been opened and that the Template Table is loaded, where the DataLabel always has a valid value.
/// Then create both the ImageSet table and the Markers table
/// </summary>
protected override void OnDatabaseCreated(TemplateDatabase templateDatabase)
{
// copy the template's controls table
base.OnDatabaseCreated(templateDatabase);
// create FileData from the controls
List<ColumnDefinition> columnDefinitions = new List<ColumnDefinition>();
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.ID, Constant.Sql.CreationStringPrimaryKey)); // It begins with the ID integer primary key
foreach (ControlRow control in this.Controls)
{
columnDefinitions.Add(this.CreateFileDataColumnDefinition(control));
}
this.Database.CreateTable(Constant.DatabaseTable.FileData, columnDefinitions);
// index the DateTime column
this.Database.ExecuteNonQuery("CREATE INDEX 'FileDateTimeIndex' ON 'FileData' ('DateTime')");
// Create ImageSet table with a singleton row
columnDefinitions.Clear();
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.ID, Constant.Sql.CreationStringPrimaryKey)); // It begins with the ID integer primary key
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.FileSelection, Constant.Sql.Text));
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.InitialFolderName, Constant.Sql.Text));
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.Log, Constant.Sql.Text));
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.Options, Constant.Sql.Text));
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.MostRecentFileID, Constant.Sql.Integer));
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.TimeZone, Constant.Sql.Text));
this.Database.CreateTable(Constant.DatabaseTable.ImageSet, columnDefinitions);
List<ColumnTuple> columnsToUpdate = new List<ColumnTuple>();
columnsToUpdate.Add(new ColumnTuple(Constant.DatabaseColumn.FileSelection, FileSelection.All.ToString()));
columnsToUpdate.Add(new ColumnTuple(Constant.DatabaseColumn.InitialFolderName, Path.GetFileName(this.FolderPath)));
columnsToUpdate.Add(new ColumnTuple(Constant.DatabaseColumn.Log, Constant.Database.ImageSetDefaultLog));
columnsToUpdate.Add(new ColumnTuple(Constant.DatabaseColumn.MostRecentFileID, Constant.Database.DefaultFileID));
columnsToUpdate.Add(new ColumnTuple(Constant.DatabaseColumn.Options, ImageSetOptions.None.ToString()));
columnsToUpdate.Add(new ColumnTuple(Constant.DatabaseColumn.TimeZone, TimeZoneInfo.Local.Id));
List<List<ColumnTuple>> insertionStatements = new List<List<ColumnTuple>>();
insertionStatements.Add(columnsToUpdate);
this.Database.Insert(Constant.DatabaseTable.ImageSet, insertionStatements);
this.GetImageSet();
// create the Files table
// This is necessary as files can't be added unles Files.Columns is available. SelectFiles() has to be called after the ImageSetTable is created
// so that the selection can be persisted.
this.SelectFiles(FileSelection.All);
// Create the Markers table and initialize it from the controls
columnDefinitions.Clear();
columnDefinitions.Add(new ColumnDefinition(Constant.DatabaseColumn.ID, Constant.Sql.CreationStringPrimaryKey));
string type = String.Empty;
foreach (ControlRow control in this.Controls)
{
if (control.Type.Equals(Constant.Control.Counter))
{
columnDefinitions.Add(new ColumnDefinition(control.DataLabel, Constant.Sql.Text, String.Empty));
}
}
this.Database.CreateTable(Constant.DatabaseTable.Markers, columnDefinitions);
}
public static bool TryCreateOrOpen(string filePath, TemplateDatabase templateDatabase, bool orderFilesByDate, CustomSelectionOperator customSelectionTermCombiningOperator, out FileDatabase fileDatabase)
{
// check for an existing database before instantiating the databse as SQL wrapper instantiation creates the database file
bool populateDatabase = !File.Exists(filePath);
fileDatabase = new FileDatabase(filePath);
if (populateDatabase)
{
// initialize the database if it's newly created
fileDatabase.OnDatabaseCreated(templateDatabase);
}
else
{
// if it's an existing database check if it needs updating to current structure and load data tables
if (fileDatabase.OnExistingDatabaseOpened(templateDatabase) == false)
{
return false;
}
}
// ensure all tables have been loaded from the database
if (fileDatabase.ImageSet == null)
{
fileDatabase.GetImageSet();
}
if (fileDatabase.Markers == null)
{
fileDatabase.GetMarkers();
}
fileDatabase.CustomSelection = new CustomSelection(fileDatabase.Controls, customSelectionTermCombiningOperator);
fileDatabase.OrderFilesByDateTime = orderFilesByDate;
fileDatabase.PopulateDataLabelMaps();
return true;
}
