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();
        }
예제 #2
0
        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();
        }
예제 #3
0
        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);
            }
        }