/// <summary> /// gets all FKs that refer back to their own table and filters those suitalbe for NavTree navigaion, otherwise adds an error message /// </summary> /// <returns>For each such FK Pair<suitable(true/false), description/reason></returns> public Dictionary <string, KeyValuePair <bool, string> > CheckHierarchies() { List <FK> selfRefFK = stats.SelfRefFKs(); Dictionary <string, List <string> > PKs = stats.PKs; var tableSRFKs = from fk in selfRefFK group fk by fk.myTable into groups select new { Key = groups.Key, Value = groups }; Dictionary <string, KeyValuePair <bool, string> > res = new Dictionary <string, KeyValuePair <bool, string> >(); foreach (var x in tableSRFKs) { if (PKs[x.Key].Count != 1) { res[x.Key] = new KeyValuePair <bool, string>(false, "This table does not have a single-column PK."); } else if (x.Value.Count() > 1) { res[x.Key] = new KeyValuePair <bool, string>(false, "Multiple self-referential columns?"); } else if (x.Value.First().refColumn != PKs[x.Key][0]) { res[x.Key] = new KeyValuePair <bool, string>(false, "The self-referential column must refer to the PK."); } else { FK goodSRFK = x.Value.First(); res[x.Key] = new KeyValuePair <bool, string>(true, goodSRFK.myColumn + " refers to " + goodSRFK.refColumn + " this table can be navigated through via a trre"); } } return(res); }
/// <summary> /// Ignores mappings, displays first 4 columns in a classic NavTable /// or the first display column in a NavTree if a self-referential FK is present. /// navtable includes edit and delete action buttons, second control - insert button /// </summary> /// <param name="tableName">string</param> /// <returns>Panel</returns> public Panel proposeSummaryPanel(string tableName) { DataColumnCollection cols = stats.ColumnTypes[tableName]; List <FK> FKs = stats.FKs[tableName]; // a table with more than one self-referential FK is improbable List <FK> selfRefs = new List <FK>(from FK in FKs where FK.myTable == FK.refTable select FK as FK); List <string> PKCols = stats.PKs[tableName]; FK selfRefFK = null; // strict hierarchy structure validation - not nice => no Tree if (hierarchies.Contains(tableName)) { selfRefFK = selfRefs.First(); } List <string> displayColOrder = stats.ColumnsToDisplay[tableName]; Control control; DataTable controlTab = new DataTable(); List <Control> Controls = new List <Control>(); if (selfRefFK != null) // this will be a NavTree { Panel res = new Panel(tableName, 0, PanelTypes.NavTree, new List <Panel>(), new List <IField>(), new List <Control>(), PKCols); res.displayAccessRights = 1; control = new TreeControl(0, new HierarchyNavTable(), PKCols[0], selfRefFK.myColumn, displayColOrder[0], new List <UserAction> { UserAction.View }); Controls.Add(control); control = new Control(0, null, PKCols, UserAction.Insert); Controls.Add(control); res.AddControls(Controls); return(res); } else { // a simple NavTable Panel res = new Panel(tableName, 0, PanelTypes.NavTable, new List <Panel>(), new List <IField>(), new List <Control>(), PKCols); res.displayAccessRights = 1; List <UserAction> actions = new List <UserAction>(new UserAction[] { UserAction.View, UserAction.Delete }); List <string> displayColumns = displayColOrder.GetRange(0, Math.Min(displayColOrder.Count, 4)); List <FK> neededFKs = (from FK fk in FKs where displayColumns.Contains(fk.myColumn) select fk).ToList(); control = new NavTableControl(0, null, PKCols, neededFKs, actions); control.displayColumns = displayColumns; Controls.Add(control); control = new Control(0, null, PKCols, UserAction.Insert); Controls.Add(control); res.AddControls(Controls); return(res); } }
private SortedDictionary <int, string> FetchFKOptions(FK fk) { DataTable tbl = driver.fetchAll("SELECT ", dbe.Cols(new string[] { fk.refColumn, fk.displayColumn }), " FROM ", dbe.Table(fk.refTable)); SortedDictionary <int, string> res = new SortedDictionary <int, string>(); foreach (DataRow r in tbl.Rows) { if (r[0] == DBNull.Value || r[0].ToString() == "") { continue; } res.Add(Int32.Parse(r[0].ToString()), r[1].ToString()); // awful... obj to int through int - find a better way } return(res); }
// use only through FullProject load, if there is no panel in session or project version has changed #region Deserialization /// <summary> /// loads the whole project from database (in 3 queries) /// </summary> public void FullProjectLoad() { Panels = new Dictionary <int, Panel>(); driver.BeginTransaction(); FK control_panel = new FK("controls", "id_panel", "panels", "id_panel", null); FK field_panel = new FK("fields", "id_panel", "panels", "id_panel", null); DataTable panels = driver.fetchAll("SELECT * FROM ", dbe.Table("panels"), "WHERE id_project =", CE.project.Id); DataTable controls = driver.fetchAll("SELECT controls.* FROM ", dbe.Table("controls"), dbe.Join(control_panel), "WHERE id_project =", CE.project.Id); DataTable fields = driver.fetchAll("SELECT fields.* FROM ", dbe.Table("fields"), dbe.Join(field_panel), "WHERE id_project =", CE.project.Id); driver.CommitTransaction(); panels.TableName = "panels"; controls.TableName = "controls"; fields.TableName = "fields"; DataSet ds = new DataSet(); /* * panels.PrimaryKey = new DataColumn[] { panels.Columns["id_panel"] }; * controls.PrimaryKey = new DataColumn[] { panels.Columns["id_control"] }; * fields.PrimaryKey = new DataColumn[] { panels.Columns["id_field"] }; */ if (panels.DataSet is DataSet) { panels.DataSet.Tables.Clear(); } if (controls.DataSet is DataSet) { controls.DataSet.Tables.Clear(); } if (fields.DataSet is DataSet) { fields.DataSet.Tables.Clear(); } ds.Tables.Add(panels); ds.Tables.Add(controls); ds.Tables.Add(fields); ds.Relations.Add(new DataRelation(CC.SYSDRIVER_FK_CONTROL_PANEL, ds.Tables["panels"].Columns["id_panel"], ds.Tables["controls"].Columns["id_panel"], true)); ds.Relations.Add(new DataRelation(CC.SYSDRIVER_FK_FIELD_PANEL, ds.Tables["panels"].Columns["id_panel"], ds.Tables["fields"].Columns["id_panel"], true)); ds.Relations.Add(new DataRelation(CC.SYSDRIVER_FK_PANEL_PARENT, ds.Tables["panels"].Columns["id_panel"], ds.Tables["panels"].Columns["id_parent"], true)); DataSet2Architecture(ds); }
/// <summary> /// finds the hierarchical relation within table only if valid for use in a NavTree /// </summary> /// <returns></returns> public List <FK> SelfRefFKs() { List <FK> res = new List <FK>(); DataTable stats = driver.fetchAll("SELECT * FROM KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = \"" + webDb + "\" AND TABLE_NAME = REFERENCED_TABLE_NAME AND REFERENCED_COLUMN_NAME IS NOT NULL"); foreach (DataRow r in stats.Rows) { FK fk = (new FK(r["TABLE_NAME"] as string, r["COLUMN_NAME"] as string, r["REFERENCED_TABLE_NAME"] as string, r["REFERENCED_COLUMN_NAME"] as string, r["REFERENCED_COLUMN_NAME"] as string)); // the following checks will be done in the Architect and errors will be displayed to the user //if(res.Any(x => x.myTable == fk.myTable)) continue; // there can be only one sef-ref FK per table //if(PKs[fk.myTable].Count != 1) continue; // must have a single-column primary key... //if (fk.refColumn != PKs[fk.myTable][0]) continue; // ...to which the FK refers res.Add(fk); } return(res); }
/// <summary> /// fills "data" tables of NavTableControls /// </summary> /// <param name="ntc"></param> /// <param name="schemaOnly"></param> private void AssignDataForNavTable(NavTableControl ntc, bool schemaOnly) { List <string> toSelect = ntc.displayColumns.Union(ntc.PKColNames).ToList(); List <IDbCol> specSelect = new List <IDbCol>(); List <IDbCol> prefixedKeys = new List <IDbCol>(); List <string> neededTables = new List <string>(); // different FKs to the same table - JOIN colision prevention by giving each table an alias Dictionary <string, int> countingForeignTableUse = new Dictionary <string, int>(); foreach (string col in toSelect) { FK correspondingFK = ntc.FKs.Where(x => x.myColumn == col).FirstOrDefault(); // can be at most one if (correspondingFK is FK && ((FK)correspondingFK).refTable != ntc.panel.tableName) // dont join on itself { neededTables.Add(correspondingFK.refTable); if (!countingForeignTableUse.ContainsKey(correspondingFK.refTable)) // don`t need an alias { specSelect.Add(dbe.Col(correspondingFK.refTable, correspondingFK.displayColumn, col)); countingForeignTableUse[correspondingFK.refTable] = 1; } else { // use SALT + the count so that it is (hopefully) unique countingForeignTableUse[correspondingFK.refTable]++; specSelect.Add(dbe.Col(correspondingFK.refTable + Common.Constants.SALT + countingForeignTableUse[correspondingFK.refTable], correspondingFK.displayColumn, col)); } // we must provide the real column value (its a part of the PK) in another column if (ntc.PKColNames.Contains(col) && correspondingFK is FK) { prefixedKeys.Add(dbe.Col(ntc.panel.tableName, col, CC.TABLE_COLUMN_REAL_VALUE_PREFIX + col)); } } else { // no FK, just use the original column name specSelect.Add(dbe.Col(ntc.panel.tableName, col, col)); } } specSelect.AddRange(prefixedKeys); FetchMethod fetchAccordingly; // so that we can use the same fetch-code for both Architect and Admin mode if (schemaOnly) { fetchAccordingly = driver.fetchSchema; } else { fetchAccordingly = driver.fetchAll; } if (countingForeignTableUse.Values.Any(x => x > 1)) { // FK table aliases // not 100% solution - tables with suffix "1"... List <IDbJoin> joins = new List <IDbJoin>(); ntc.FKs.Reverse(); // the counter counts down when creating joins in reverse order foreach (FK fk in ntc.FKs) { string alias = fk.refTable + (countingForeignTableUse[fk.refTable] > 1 ? Common.Constants.SALT + (countingForeignTableUse[fk.refTable]--).ToString() : ""); joins.Add(dbe.Join(fk, alias)); } ntc.data = fetchAccordingly("SELECT ", dbe.Cols(specSelect), " FROM ", dbe.Table(ntc.panel.tableName), dbe.Joins(joins)); } else { ntc.data = fetchAccordingly("SELECT ", dbe.Cols(specSelect), " FROM ", dbe.Table(ntc.panel.tableName), dbe.Joins(ntc.FKs.Where(x => x.refTable != x.myTable).ToList <FK>())); } }
public FKField(string columnName, string caption, FK FK) : base(columnName, caption) { this.FK = FK; }
public virtual void ToUControl(UControl container, WC.GridViewCommandEventHandler handler, WC.GridViewPageEventHandler pagingHandler, WC.GridViewSortEventHandler sortingHandler, string navigateUrl = null) { // take care of all the dependant controls as well WC.GridView grid = new WC.GridView(); string[] DataKeyNames = PKColNames.ToArray(); if (CE.GlobalState == GlobalState.Administer) { // one of our datakeys may have been a FK and its value is now the representative field of the // foreign table, not our key, but in such cases the key will be stored in a prefixed column. for (int i = 0; i < DataKeyNames.Length; i++) { FK fk = FKs.Where(x => x.myColumn == DataKeyNames[i]).FirstOrDefault(); if (fk is FK) { DataKeyNames[i] = CC.TABLE_COLUMN_REAL_VALUE_PREFIX + DataKeyNames[i]; } } } grid.DataKeyNames = DataKeyNames; grid.AutoGenerateColumns = false; WC.TemplateField tf = new WC.TemplateField(); tf.HeaderTemplate = new SummaryGridCommandColumn(WC.ListItemType.Header); tf.FooterTemplate = new SummaryGridCommandColumn(WC.ListItemType.Footer); tf.ItemTemplate = new SummaryGridCommandColumn(WC.ListItemType.Item, actions); grid.Columns.Add(tf); foreach (string col in displayColumns) { WC.BoundField bf = new WC.BoundField(); bf.DataField = col; bf.HeaderText = col + " [-]"; bf.SortExpression = col; grid.Columns.Add(bf); } // must contain the whole PK even if it is not displayed - for the navigator // DataKeyNames are the real ones - including "_" prefixing foreach (string col in DataKeyNames) { if (displayColumns.Contains(col)) { continue; } WC.BoundField bf = new WC.BoundField(); bf.DataField = col; bf.Visible = false; grid.Columns.Add(bf); bf.HeaderText = col; } grid.AllowSorting = true; //PBPR container.Controls.Add(grid); grid.PagerStyle.CssClass = "navTablePaging"; grid.CssClass = "navTable"; grid.AllowPaging = true; grid.PageSize = 25; grid.PageIndexChanging += pagingHandler; grid.Sorting += sortingHandler; grid.DataSource = data.DefaultView; grid.DataBind(); //PBPR grid.RowCommand += handler; grid.ID = "Control" + controlId; }
public InnerJoin(FK fk, string alias) { this.fk = fk; this.alias = alias; }
public InnerJoin(FK fk) { this.fk = fk; }