/// <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;
}
