/// <summary>
/// Table(Name,AsName) 构造函数
/// </summary>
/// <param name="_name">表名</param>
/// <param name="_asName">表别名</param>
/// <param name="_relation">两表的关系</param>
/// <param name="_conditions">两表关系的条件</param>
public Table(string _name, string _asName, TableRelation _relation, IList <Where> _conditions)
{
this.Name = _name;
this.AsName = _asName;
this.Relation = _relation;
this.Conditions = _conditions;
}
private static bool IsApplicable(TableRelation relation, TableSymbol leftTable, TableSymbol rightTable)
{
var isParentAndChild = (relation.ParentTable == leftTable && relation.ChildTable == rightTable);
var isChildAndParent = (relation.ParentTable == rightTable && relation.ChildTable == leftTable);
return(isParentAndChild || isChildAndParent);
}
public RelationCollection WithoutRelation(TableRelation relation)
{
var relationships = this.relations.ToList();
relationships.Remove(relation);
return(new RelationCollection(relationships));
}
/// <summary>
/// 生成一个带别名的子查询
/// </summary>
/// <param name="aliasName"></param>
/// <returns></returns>
public TableRelation <TSub> SubQuery <TSub>(string aliasName)
where TSub : Entity
{
TableRelation <TSub> tr = new TableRelation <TSub>(null, aliasName);
tr.section.Query = section.SubQuery <TSub>(aliasName);
return(tr);
}
/// <summary>
/// 生成一个子查询
/// </summary>
/// <returns></returns>
public TableRelation <TSub> SubQuery <TSub>()
where TSub : Entity
{
TableRelation <TSub> tr = new TableRelation <TSub>(null, null);
tr.GetFromSection().Query = section.SubQuery <TSub>();
return(tr);
}
/// <summary>
/// Adds a relation to the configuration database.
/// </summary>
protected void AddRelation(IBaseTable parentTable, IBaseTable childTable, string childColumn)
{
TableRelation relation = new TableRelation(parentTable, childTable, childColumn);
childTable.AddIndex(childColumn);
childTable.DependsOn.Add(relation);
parentTable.Dependent.Add(relation);
}
public DataRelation initializeRelation(TableRelation name, DataColumn parent, DataColumn child)
{
DataRelation r = new DataRelation(name.ToString(), parent, child);
relations.Add(name, r);
set.Relations.Add(r);
return(r);
}
public TableRelationViewModel(TableRelation relation)
{
this.FieldA = relation.FieldA;
this.FieldB = relation.FieldB;
this.Relation = relation.Relation;
this.TableA = relation.TableA;
this.TableB = relation.TableB;
this.Id = relation.Id;
this.Name = relation.Name;
}
public RelationCollection WithoutRelation(TableRelation relation)
{
var relationships = this.relations.ToList();
if (!relationships.Remove(relation))
{
throw new InvalidOperationException("Could not remove relation: " + relation);
}
return(new RelationCollection(relationships));
}
public void Contains_WhenNotAdded_EdgeDoesNotExists()
{
// Arrange
var graphRoute = this.CreateGraphRoute();
TableRelation item = FacetsGraph.EdgeContaniner.GetEdge("C", "F");
// Act
var result = graphRoute.Contains(item);
// Assert
Assert.False(result);
}
public void Visit(TableRelation component)
{
sql.Append(" " + TranslateJoinType(component.JoinType) + " ");
component.ForeignTable.Accept(this);
sql.Append(" ON ");
component.LocalForeignKeys.First().Accept(this);
foreach (var key in component.LocalForeignKeys.Skip(1))
{
sql.Append(" AND ");
key.Accept(this);
}
}
public void Contains_WhenAdded_EdgeExists()
{
// Arrange
var graphRoute = this.CreateGraphRoute();
TableRelation item = FacetsGraph.EdgeContaniner.GetEdge("A", "B");
// Act
var result = graphRoute.Contains(item);
// Assert
Assert.True(result);
}
public FakeGraphGenerator Add(string x, string y, int w)
{
var edge = new TableRelation()
{
TableRelationId = Edges.Count + 1,
SourceTable = NodeMap[x],
TargetTable = NodeMap[y],
Weight = w,
SourceColumName = $"{x.ToLower()}{y.ToLower()}_key",
TargetColumnName = $"{x.ToLower()}{y.ToLower()}_key"
};
Edges.Add(edge);
return(this);
}
private static CompletionItem GetCompletionItem(TableRelation tableRelation, TableInstanceSymbol leftInstance, TableInstanceSymbol rightInstance)
{
var leftColumns = leftInstance.Table == tableRelation.ParentTable
? tableRelation.ParentColumns
: tableRelation.ChildColumns;
var rightColumns = leftInstance.Table == tableRelation.ParentTable
? tableRelation.ChildColumns
: tableRelation.ParentColumns;
var condition = CreateCondition(leftInstance, leftColumns, rightInstance, rightColumns);
var displayText = condition;
var insertionText = condition;
var description = condition;
return(new CompletionItem(displayText, insertionText, description, Glyph.Relation));
}
public void Compile_WithSingleEdge_ReturnSingleJoin()
{
// Arrange
TableRelation edge = new TableRelation()
{
TableRelationId = -2151,
SourceTableId = 46,
TargetTableId = 113,
Weight = 5,
SourceColumName = "location_id",
TargetColumnName = "location_id",
SourceTable = new Table()
{
TableId = 46, TableOrUdfName = "countries"
},
TargetTable = new Table()
{
TableId = 113, TableOrUdfName = "tbl_site_locations"
}
};
FacetTable facetTable = new FacetTable {
FacetTableId = 1,
FacetId = 1,
SequenceId = 1,
TableId = edge.TargetTableId,
Table = edge.TargetTable,
UdfCallArguments = null,
Alias = ""
};
// Act
var edgeSqlCompiler = new JoinSqlCompiler();
var result = edgeSqlCompiler.Compile(edge, facetTable, false);
// Assert
var expected = "left join tbl_site_locations on tbl_site_locations.\"location_id\" = countries.\"location_id\"";
Assert.Equal(expected, result.ToLower().Trim());
}
public void Compile_WithSingleEdgeWithWithoutAliasAndNoUdf_ReturnSingleJoinWithNoAlias()
{
// Arrange
TableRelation edge = new TableRelation()
{
TableRelationId = -2151,
Weight = 5,
SourceColumName = "a",
TargetColumnName = "a",
SourceTable = new Table()
{
TableId = 1, TableOrUdfName = "A"
},
TargetTable = new Table()
{
TableId = 2, TableOrUdfName = "B"
}
};
FacetTable facetTable = new FacetTable {
FacetTableId = 1,
FacetId = 1,
SequenceId = 1,
TableId = edge.TargetTableId,
Table = edge.TargetTable,
UdfCallArguments = null,
Alias = ""
};
// Act
var edgeSqlCompiler = new JoinSqlCompiler();
var result = edgeSqlCompiler.Compile(edge, facetTable, false);
// Assert
var expected = @"\s*left\s+join\s+b\s+on\s+b\.""a""\s+=\s+a\.""a""\s*";
Assert.Matches(expected, result.ToLower());
}
public void AcceptRelation(TableRefBinding parentTableRef, TableRefBinding childTableRef, TableRelation relation)
{
}
public void AcceptRelation(TableRefBinding parentBinding, TableRefBinding childBinding, TableRelation relation)
{
ColumnBindingCollection parentColumns;
if (parentBinding.TableBinding == relation.ParentTable)
parentColumns = relation.ParentColumns;
else
parentColumns = relation.ChildColumns;
ColumnBindingCollection childColumns;
if (childBinding.TableBinding == relation.ChildTable)
childColumns = relation.ChildColumns;
else
childColumns = relation.ParentColumns;
// Item in member list:
// ~~~~~~~~~~~~~~~~~~~~
// parentRef(col1, col2) ---> childRef(col1, col2)
StringBuilder sb = new StringBuilder();
sb.Append(parentBinding.Name);
sb.Append(" (");
for (int i = 0; i < relation.ColumnCount; i++)
{
if (i > 0)
sb.Append(", ");
sb.Append(parentColumns[i].Name);
}
sb.Append(") ---> ");
sb.Append(childBinding.Name);
sb.Append(" (");
for (int i = 0; i < relation.ColumnCount; i++)
{
if (i > 0)
sb.Append(", ");
sb.Append(childColumns[i].Name);
}
sb.Append(")");
string name = sb.ToString();
sb.Length = 0;
// Description:
// ~~~~~~~~~~~~
// Relation <b>parentTable parentRef</b> ---> <b>ChildTable childRef</b> <br/>
// ON (parentRef.Col1 = childRef.Col1 AND parentRef.Col2 = childRef.Col2)
sb.Append("Relation <br/>");
sb.Append("<b>");
sb.Append(MaskIdentifier(parentBinding.TableBinding.Name));
sb.Append(" AS ");
sb.Append(MaskIdentifier(parentBinding.Name));
sb.Append("</b>");
sb.Append(" ---> ");
sb.Append("<b>");
sb.Append(MaskIdentifier(childBinding.TableBinding.Name));
sb.Append(" AS ");
sb.Append(MaskIdentifier(childBinding.Name));
sb.Append("</b><br/>ON (");
for (int i = 0; i < relation.ColumnCount; i++)
{
if (i > 0)
sb.Append(" AND ");
sb.Append(MaskIdentifier(parentBinding.Name));
sb.Append(".");
sb.Append(MaskIdentifier(parentColumns[i].Name));
sb.Append(" = ");
sb.Append(MaskIdentifier(childBinding.Name));
sb.Append(".");
sb.Append(MaskIdentifier(childColumns[i].Name));
}
sb.Append(")");
string description = sb.ToString();
sb.Length = 0;
// PreText:
// ~~~~~~~~
// parentAlias.Col1 = childAlias.Col1 AND parentAlias.Col2 = childAlias.Col2
for (int i = 0; i < relation.ColumnCount; i++)
{
if (i > 0)
sb.Append(" AND ");
sb.Append(MaskIdentifier(parentBinding.Name));
sb.Append(".");
sb.Append(MaskIdentifier(parentColumns[i].Name));
sb.Append(" = ");
sb.Append(MaskIdentifier(childBinding.Name));
sb.Append(".");
sb.Append(MaskIdentifier(childColumns[i].Name));
}
string preText = sb.ToString();
sb.Length = 0;
IntelliPromptMemberListItem item = new IntelliPromptMemberListItem(name, RELATION_IMG_INDEX, description, preText, String.Empty);
_members.Add(item);
}
public DataRow[] findChildren(DataRow row, TableRelation relationName)
{
return(row.GetChildRows(getRelation(relationName)));
}
protected override List<Table> GetTables()
{
var tmp = new List<Table>();
using (Command)
{
Command.CommandText = GetCmd();
try
{
Command.Connection.Open();
using (var reader = Command.ExecuteReader())
{
var data = new DataSet();
data.Load(reader, LoadOption.OverwriteChanges, "");
if (data.Tables.Count > 0)
{
var tables =
data.Tables[0].Rows.OfType<DataRow>().GroupBy(t => t["TABLE_NAME"]).ToDictionary(
t => t.Key as string, t => t.ToList());
foreach (var table in tables)
{
try
{
var tbl = new Table();
tbl.Name = table.Key; // row["TABLE_NAME"] as string;
tbl.Columns = new List<Column>();
foreach (var row in table.Value)
{
tbl.Owner = row["TABLE_SCHEMA"] as string;
var type = row["TABLE_TYPE"] as string;
tbl.Type = type != null &&
type.Equals("VIEW", StringComparison.CurrentCultureIgnoreCase)
? TableType.View
: TableType.Default;
var col = new Column();
col.Name = row["COLUMN_NAME"] as string;
col.DBType = row["DATA_TYPE"] as string;
col.DefaultValue = row["COLUMN_NAME"] as string;
var index = (row["ORDINAL_POSITION"] as string) ?? "0";
col.Index = int.Parse(index);
var length = (row["MAXIMUM_LENGTH"] as string) ?? "-1";
col.MaxLength = int.Parse(length);
col.Nullable = ((row["IS_NULLABLE"] as string) ?? string.Empty).Equals("YES",
StringComparison
.
OrdinalIgnoreCase);
col.Owner = row["TABLE_SCHEMA"] as string;
col.PrimaryKey = ((row["PRIMARY_KEY"] as string) ?? string.Empty).Equals("YES",
StringComparison
.
OrdinalIgnoreCase);
var foreignTable = row["FOREIGN_TALBE"] as string;
var foreignColumn = row["FOREIGN_COLUMN"] as string;
//gen table parent -- foreign key
if (!string.IsNullOrEmpty(foreignColumn) && !string.IsNullOrEmpty(foreignTable))
{
var parent = new TableRelation { Column = col.Name, ColumnRelated = foreignColumn, TableRelated = foreignTable };
tbl.Parents.Add(parent);
}
tbl.Columns.Add(col);
}
tmp.Add(tbl);
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw;
}
}
}
data.Dispose();
reader.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw;
}
finally
{
if (Command.Connection.State != ConnectionState.Closed)
Command.Connection.Close();
}
}
return tmp;
}
public void AcceptRelation(TableRefBinding parentBinding, TableRefBinding childBinding, TableRelation relation)
{
ColumnBindingCollection parentColumns;
if (parentBinding.TableBinding == relation.ParentTable)
{
parentColumns = relation.ParentColumns;
}
else
{
parentColumns = relation.ChildColumns;
}
ColumnBindingCollection childColumns;
if (childBinding.TableBinding == relation.ChildTable)
{
childColumns = relation.ChildColumns;
}
else
{
childColumns = relation.ParentColumns;
}
// Item in member list:
// ~~~~~~~~~~~~~~~~~~~~
// parentRef(col1, col2) ---> childRef(col1, col2)
StringBuilder sb = new StringBuilder();
sb.Append(parentBinding.Name);
sb.Append(" (");
for (int i = 0; i < relation.ColumnCount; i++)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append(parentColumns[i].Name);
}
sb.Append(") ---> ");
sb.Append(childBinding.Name);
sb.Append(" (");
for (int i = 0; i < relation.ColumnCount; i++)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append(childColumns[i].Name);
}
sb.Append(")");
string name = sb.ToString();
sb.Length = 0;
// Description:
// ~~~~~~~~~~~~
// Relation <b>parentTable parentRef</b> ---> <b>ChildTable childRef</b> <br/>
// ON (parentRef.Col1 = childRef.Col1 AND parentRef.Col2 = childRef.Col2)
sb.Append("Relation <br/>");
sb.Append("<b>");
sb.Append(MaskIdentifier(parentBinding.TableBinding.Name));
sb.Append(" AS ");
sb.Append(MaskIdentifier(parentBinding.Name));
sb.Append("</b>");
sb.Append(" ---> ");
sb.Append("<b>");
sb.Append(MaskIdentifier(childBinding.TableBinding.Name));
sb.Append(" AS ");
sb.Append(MaskIdentifier(childBinding.Name));
sb.Append("</b><br/>ON (");
for (int i = 0; i < relation.ColumnCount; i++)
{
if (i > 0)
{
sb.Append(" AND ");
}
sb.Append(MaskIdentifier(parentBinding.Name));
sb.Append(".");
sb.Append(MaskIdentifier(parentColumns[i].Name));
sb.Append(" = ");
sb.Append(MaskIdentifier(childBinding.Name));
sb.Append(".");
sb.Append(MaskIdentifier(childColumns[i].Name));
}
sb.Append(")");
string description = sb.ToString();
sb.Length = 0;
// PreText:
// ~~~~~~~~
// parentAlias.Col1 = childAlias.Col1 AND parentAlias.Col2 = childAlias.Col2
for (int i = 0; i < relation.ColumnCount; i++)
{
if (i > 0)
{
sb.Append(" AND ");
}
sb.Append(MaskIdentifier(parentBinding.Name));
sb.Append(".");
sb.Append(MaskIdentifier(parentColumns[i].Name));
sb.Append(" = ");
sb.Append(MaskIdentifier(childBinding.Name));
sb.Append(".");
sb.Append(MaskIdentifier(childColumns[i].Name));
}
string preText = sb.ToString();
sb.Length = 0;
IntelliPromptMemberListItem item = new IntelliPromptMemberListItem(name, RELATION_IMG_INDEX, description, preText, String.Empty);
_members.Add(item);
}
private FacetTable GetFacetTableByNameOrAlias(FacetsConfig2 facetsConfig, TableRelation edge) => facetsConfig.GetFacetTable(edge.TargetName) ?? FacetsGraph.GetAliasedFacetTable(edge.TargetName);
public DataRow[] findChildren(TableName tableName, object key, TableRelation relationName)
{
return(findChildren(find(tableName, key), relationName));
}
public DataRelation getRelation(TableRelation name)
{
return(relations[name]);
}
public static IEnumerable <Invocation> ToInvocations(this TableRelation tableRelation) => tableRelation.Select(l => l.ToInvocation());
protected override List<Table> GetTables()
{
var tmp = new List<Table>();
using (Command)
{
Command.CommandText = GetCmd();
try
{
if (Command.Connection != null)
{
Command.Connection.Open();
}
else
{
throw new Exception("Connection can not be null.");
}
using (var reader = Command.ExecuteReader())
{
try
{
while (reader.Read())
{
var tableName = reader["TABLE_NAME"] as string;
if (string.IsNullOrEmpty(tableName))
{
continue;
}
var table = tmp.FirstOrDefault(t => t.Name.Equals(tableName, StringComparison.CurrentCultureIgnoreCase))
??
new Table { Name = tableName };
var col = new Column();
col.Name = reader["COLUMN_NAME"] as string;
col.DBType = reader["DATA_TYPE"] as string;
col.DefaultValue = reader["COLUMN_NAME"] as string;
var index = (reader["ORDINAL_POSITION"] as string) ?? "0";
col.Index = int.Parse(index);
var length = (reader["MAXIMUM_LENGTH"] as string) ?? "-1";
col.MaxLength = int.Parse(length);
col.Nullable = ((reader["IS_NULLABLE"] as string) ?? string.Empty).Equals("YES", StringComparison.OrdinalIgnoreCase);
//col.Owner = row["TABLE_SCHEMA"] as string;
col.PrimaryKey = ((reader["PRIMARY_KEY"] as string) ?? string.Empty).Equals("YES", StringComparison.OrdinalIgnoreCase);
var foreignTable = reader["FOREIGN_TALBE"] as string;
var foreignColumn = reader["FOREIGN_COLUMN"] as string;
//gen table parent -- foreign key
if (!string.IsNullOrEmpty(foreignColumn) && !string.IsNullOrEmpty(foreignTable))
{
var parent = new TableRelation { Column = col.Name, ColumnRelated = foreignColumn, TableRelated = foreignTable };
table.Parents.Add(parent);
}
table.Columns.Add(col);
if (!tmp.Any(t => t.Name.Equals(tableName, StringComparison.CurrentCultureIgnoreCase)))
{
tmp.Add(table);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw;
}
finally
{
if (reader != null)
reader.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw;
}
finally
{
if (Command.Connection.State != ConnectionState.Closed)
Command.Connection.Close();
}
}
return tmp;
}
/// <summary>
/// This function waas previously used to determmine type of join. If HasUserPick then INNER else LEFT.
/// The logic behind this is not clear since putting a where-constraint on target enforces INNER join.
/// This is also the reason why criterias were grouped by table and stored in a dictionary.
/// </summary>
/// <param name="edge"></param>
/// <param name="tableCriterias"></param>
/// <returns></returns>
public static bool HasUserPicks(TableRelation edge, Dictionary <string, string> tableCriterias)
{
// FIXME: SHould SourceName really be considered here...?
return /* tableCriterias.ContainsKey(edge.SourceName) || */ (tableCriterias.ContainsKey(edge.TargetName));
}
public void Visit(TableRelation component)
{
throw new NotImplementedException();
}
public void AcceptRelation(TableRefBinding parentTableRef, TableRefBinding childTableRef, TableRelation relation)
{
}
