private void SetupLookupTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Rows.Add(new object[] { "Dave", "Is a maniac" });
var tbl = Database.CreateTable("SimpleLookup", dt, new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 50)) });
var lookupCata = Import(tbl);
ExtractionInformation fkEi = _catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(n => n.GetRuntimeName() == "Name");
ColumnInfo pk = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Name");
ColumnInfo descLine1 = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Description");
var cmd = new ExecuteCommandCreateLookup(CatalogueRepository, fkEi, descLine1, pk, null, true);
cmd.Execute();
}
public void TestLookupCommand(LookupTestCase testCase)
{
var db = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer);
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("SendingLocation");
dt.Columns.Add("DischargeLocation");
dt.Columns.Add("Country");
var maintbl = db.CreateTable("MainDataset", dt);
var mainCata = Import(maintbl);
DataTable dtLookup = new DataTable();
dtLookup.Columns.Add("LocationCode");
dtLookup.Columns.Add("Line1");
dtLookup.Columns.Add("Line2");
dtLookup.Columns.Add("Postcode");
dtLookup.Columns.Add("Country");
var lookuptbl = db.CreateTable("Lookup", dtLookup);
var lookupCata = Import(lookuptbl);
ExtractionInformation fkEi = mainCata.GetAllExtractionInformation(ExtractionCategory.Any).Single(n => n.GetRuntimeName() == "SendingLocation");
ColumnInfo fk = mainCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "SendingLocation");
ColumnInfo pk = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "LocationCode");
ColumnInfo descLine1 = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Line1");
ColumnInfo descLine2 = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Line2");
ExecuteCommandCreateLookup cmd = null;
var sqlBefore = GetSql(mainCata);
switch (testCase)
{
case LookupTestCase.SingleKeySingleDescriptionNoVirtualColumn:
cmd = new ExecuteCommandCreateLookup(CatalogueRepository, fkEi, descLine1, pk, null, false);
cmd.Execute();
//sql should not have changed because we didn't create an new ExtractionInformation virtual column
Assert.AreEqual(sqlBefore, GetSql(mainCata));
break;
case LookupTestCase.SingleKeySingleDescription:
cmd = new ExecuteCommandCreateLookup(CatalogueRepository, fkEi, descLine1, pk, null, true);
cmd.Execute();
//should have the lookup join and the virtual column _Desc
var sqlAfter = GetSql(mainCata);
Assert.IsTrue(sqlAfter.Contains("JOIN"));
Assert.IsTrue(sqlAfter.Contains("SendingLocation_Desc"));
break;
default:
throw new ArgumentOutOfRangeException("testCase");
}
foreach (var d in CatalogueRepository.GetAllObjects <Lookup>())
{
d.DeleteInDatabase();
}
foreach (var d in CatalogueRepository.GetAllObjects <LookupCompositeJoinInfo>())
{
d.DeleteInDatabase();
}
foreach (var d in CatalogueRepository.GetAllObjects <TableInfo>())
{
d.DeleteInDatabase();
}
foreach (var d in CatalogueRepository.GetAllObjects <Catalogue>())
{
d.DeleteInDatabase();
}
maintbl.Drop();
lookuptbl.Drop();
}
private void UpdateValidityAssesment(bool actuallyDoIt)
{
btnCreateLookup.Enabled = false;
ragSmiley1.Reset();
try
{
if (pk1.SelectedColumn == null)
{
throw new Exception("No Primary key column selected");
}
if (fk1.SelectedColumn == null)
{
throw new Exception("No Foreign key column selected");
}
var allExtractionInformations = olvExtractionInformations.Objects.Cast <ExtractionInformation>().ToArray();
var foreignKeyExtractionInformation = allExtractionInformations.SingleOrDefault(e => e.ColumnInfo != null && e.ColumnInfo.Equals(fk1.SelectedColumn));
if (foreignKeyExtractionInformation == null)
{
throw new Exception("Foreign key column(s) must come from the Catalogue ExtractionInformation columns");
}
if ((pk2.SelectedColumn == null) != (fk2.SelectedColumn == null))
{
throw new Exception("If you want to have secondary joins you must have them in pairs");
}
if ((pk3.SelectedColumn == null) != (fk3.SelectedColumn == null))
{
throw new Exception("If you want to have secondary joins you must have them in pairs");
}
var p1 = pk1.SelectedColumn;
var f1 = fk1.SelectedColumn;
var p2 = pk2.SelectedColumn;
var f2 = fk2.SelectedColumn;
var p3 = pk3.SelectedColumn;
var f3 = fk3.SelectedColumn;
var uniqueIDs = new[] { p1, p2, p3, f1, f2, f3 }.Where(o => o != null).Select(c => c.ID).ToArray();
if (uniqueIDs.Distinct().Count() != uniqueIDs.Count())
{
throw new Exception("Columns can only appear once in any given key box");
}
if (new[] { p1, p2, p3 }.Where(o => o != null).Select(c => c.TableInfo_ID).Distinct().Count() != 1)
{
throw new Exception("All primary key columns must come from the same Lookup table");
}
if (new[] { f1, f2, f3 }.Where(o => o != null).Select(c => c.TableInfo_ID).Distinct().Count() != 1)
{
throw new Exception("All foreign key columns must come from the same Lookup table");
}
var descs = olvSelectedDescriptionColumns.Objects.Cast <ColumnInfo>().ToArray();
if (!descs.Any())
{
throw new Exception("You must have at least one Description column from the Lookup table");
}
if (descs.Any(d => d.TableInfo_ID != p1.TableInfo_ID))
{
throw new Exception("All Description columns must come from the Lookup table");
}
if (actuallyDoIt)
{
bool alsoCreateExtractionInformation =
Activator.YesNo(
"Also create a virtual extractable column(s) in '" + _catalogue + "' called '<Column>_Desc'",
"Create Extractable Column?");
var keyPairs = new List <Tuple <ColumnInfo, ColumnInfo> >();
keyPairs.Add(Tuple.Create(f1, p1));
if (p2 != null)
{
keyPairs.Add(Tuple.Create(f2, p2));
}
if (p3 != null)
{
keyPairs.Add(Tuple.Create(f3, p3));
}
var cmd = new ExecuteCommandCreateLookup(Activator.RepositoryLocator.CatalogueRepository, foreignKeyExtractionInformation, descs,
keyPairs, tbCollation.Text, alsoCreateExtractionInformation);
cmd.Execute();
Activator.RefreshBus.Publish(this, new RefreshObjectEventArgs(_catalogue));
SetDatabaseObject(Activator, _catalogue);
MessageBox.Show("Lookup created successfully, fields will now be cleared");
pk1.Clear();
pk2.Clear();
pk3.Clear();
fk1.Clear();
fk2.Clear();
fk3.Clear();
olvSelectedDescriptionColumns.ClearObjects();
SetStage(LookupCreationStage.DragAPrimaryKey);
}
btnCreateLookup.Enabled = true;
}
catch (Exception e)
{
if (actuallyDoIt)
{
ExceptionViewer.Show(e);
}
ragSmiley1.Fatal(e);
}
}