protected static bool GetCondition(DmlfCommandBase cmd, ChangeSetItem item, ChangeSetCondition cond, DatabaseInfo db)
{
var source = new DmlfSource
{
Alias = "basetbl",
LinkedInfo = item.LinkedInfo,
TableOrView = item.TargetTable,
};
var colref = cmd.SingleFrom.GetColumnRef(source, item.TargetTable, StructuredIdentifier.Parse(cond.Column), db, DmlfJoinType.Inner);
if (colref == null) return false;
//var table = db.FindTable(item.TargetTable);
//if (table == null) return false;
//var column = table.FindColumn(cond.Column);
//if (column == null) return false;
var colexpr = new DmlfColumnRefExpression
{
Column = colref
};
var column = colref.FindSourceColumn(db);
cmd.AddAndCondition(FilterParser.FilterParser.ParseFilterExpression(column != null ? column.CommonType : new DbTypeString(), colexpr, cond.Expression));
return true;
}
protected static bool GetCondition(DmlfCommandBase cmd, ChangeSetItem item, ChangeSetCondition cond, DatabaseInfo db)
{
var source = new DmlfSource
{
Alias = "basetbl",
LinkedInfo = item.LinkedInfo,
TableOrView = item.TargetTable,
};
var colref = cmd.SingleFrom.GetColumnRef(source, item.TargetTable, StructuredIdentifier.Parse(cond.Column), db, DmlfJoinType.Inner);
if (colref == null)
{
return(false);
}
//var table = db.FindTable(item.TargetTable);
//if (table == null) return false;
//var column = table.FindColumn(cond.Column);
//if (column == null) return false;
var colexpr = new DmlfColumnRefExpression
{
Column = colref
};
var column = colref.FindSourceColumn(db);
var condItem = FilterParserTool.ParseFilterExpression(column != null ? column.CommonType : new DbTypeString(), colexpr, cond.UsedExpression);
if (condItem == null)
{
throw new Exception("DBSH-00000 Error creating condition");
}
cmd.AddAndCondition(condItem);
return(true);
}
private DmlfSource DoAddOrFindRelation(DmlfSource baseSource, NameWithSchema baseTable, StructuredIdentifier relationJoined, StructuredIdentifier relationToJoin, DatabaseInfo db, DmlfJoinType joinType)
{
if (relationToJoin.IsEmpty) return baseSource;
string relName = relationToJoin.First;
string alias = String.Format("_REF{0}_{1}", relationJoined.NameItems.Select(x => "_" + x).CreateDelimitedText(""), relName);
var source = FindSourceByAlias(alias);
if (source == null)
{
var baseTableInfo = db.FindTable(baseTable);
var fk = baseTableInfo.ForeignKeys.FirstOrDefault(x => System.String.Compare(x.ConstraintName, relName, StringComparison.OrdinalIgnoreCase) == 0);
if (fk == null)
{
var column = baseTableInfo.FindColumn(relName);
if (column != null) fk = column.GetForeignKeys().FirstOrDefault(x => x.Columns.Count == 1);
}
if (fk == null) return null;
source = new DmlfSource
{
TableOrView = fk.RefTableFullName,
Alias = alias,
};
var relation = new DmlfRelation
{
Reference = source,
JoinType = joinType,
};
for (int i = 0; i < fk.Columns.Count; i++)
{
relation.Conditions.Add(new DmlfEqualCondition
{
LeftExpr = new DmlfColumnRefExpression
{
Column = new DmlfColumnRef
{
ColumnName = fk.Columns[0].RefColumnName,
Source = baseSource,
}
},
RightExpr = new DmlfColumnRefExpression
{
Column = new DmlfColumnRef
{
ColumnName = fk.RefColumns[0].RefColumnName,
Source = source,
}
},
});
Relations.Add(relation);
}
}
if (relationToJoin.IsEmpty) return source;
return DoAddOrFindRelation(source, source.TableOrView, relationJoined/relationToJoin.First, relationToJoin.WithoutFirst, db, joinType);
}
public DmlfExpression CreateTargetExpression(DmlfSource targetSource)
{
return new DmlfColumnRefExpression
{
Column = new DmlfColumnRef
{
Source = targetSource,
ColumnName = Name,
}
};
}
public static DmlfResultField BuildFromColumn(string colname, DmlfSource src)
{
return new DmlfResultField
{
Expr = new DmlfColumnRefExpression
{
Column = new DmlfColumnRef
{
ColumnName = colname,
Source = src,
}
},
};
}
public bool IsMultiTable()
{
DmlfSource lastsrc = null;
foreach (var col in this)
{
if (col.Source != null)
{
if (lastsrc != null && col.Source != lastsrc)
{
return(true);
}
lastsrc = col.Source;
}
}
return(false);
}
public void InitializeQuerySource(ITabularDataSource dataSource, IShellContext context, string sourceTableVariable, string sourceQueryVariable)
{
if (!_dbsh.ForceExternalSource)
{
// try to create non-external source
var tableOrView = dataSource as DbShell.Core.Utility.TableOrView;
if (!String.IsNullOrEmpty(sourceTableVariable))
{
QuerySource = new DmlfSource
{
Alias = SqlAlias,
TableOrView = new NameWithSchema($"###({sourceTableVariable})###"),
};
TableName = new NameWithSchema(sourceTableVariable);
return;
}
if (!String.IsNullOrEmpty(sourceQueryVariable))
{
QuerySource = new DmlfSource
{
Alias = SqlAlias,
SubQueryString = $"###({sourceQueryVariable})###",
};
return;
}
if (tableOrView != null)
{
bool canUseTable = true;
LinkedDatabaseInfo linked = null;
var ctxConn = new NormalizedDatabaseConnectionInfo(new DatabaseConnectionInfoHolder { ProviderString = context.GetDefaultConnection() });
var tableConn = tableOrView.GetNormalizedConnectionInfo(context);
if (ctxConn != tableConn)
{
if (ctxConn.ServerConnectionString == tableConn.ServerConnectionString)
{
linked = tableConn.GetLinkedInfo();
}
else
{
canUseTable = false;
}
}
if (canUseTable)
{
TableName = tableOrView.GetFullName(context);
QuerySource = new DmlfSource
{
Alias = SqlAlias,
TableOrView = TableName,
LinkedInfo = linked,
};
return;
}
}
var query = dataSource as DbShell.Core.Query;
if (query != null && query.GetProviderString(context) == context.GetDefaultConnection())
{
string sql = context.Replace(query.Text);
QuerySource = new DmlfSource
{
Alias = SqlAlias,
SubQueryString = sql,
};
return;
}
}
IsExternal = true;
_externalDataName = new NameWithSchema(null, $"##{SqlAlias}_{new Random().Next(10000, 100000)}");
QuerySource = new DmlfSource
{
Alias = SqlAlias,
TableOrView = _externalDataName,
};
_dataSync.AddExternalSource(this);
}
public void MaterializeIfNeeded()
{
if (IsExternal) return;
if (!_dbsh.Materialize) return;
_materializedName = new NameWithSchema("#" + SqlAlias.Replace(".", "_"));
_materializeSelect = new DmlfSelect();
_materializeSelect.SingleFrom.Source = QuerySource;
_materializeSelect.SelectIntoTable = _materializedName;
_materializeSelect.SelectAll = true;
QuerySource = new DmlfSource
{
Alias = SqlAlias,
TableOrView = _materializedName,
};
}
public DmlfSource GetRefSource(DmlfFromItem from, SourceJoinSqlModel joinModel)
{
var res = from.FindSourceWithAlias(SqlAlias);
if (res != null) return res;
res = new DmlfSource
{
Alias = SqlAlias,
TableOrView = TargetTableSqlName,
LinkedInfo = TargetLinkedInfo,
};
var rel = new DmlfRelation
{
JoinType = DmlfJoinType.Left,
Reference = res,
};
foreach (var keycol in TargetColumns.Where(x => x.IsKey || x.IsRestriction))
{
rel.Conditions.Add(new DmlfEqualCondition
{
LeftExpr = keycol.CreateSourceExpression(joinModel, false),
RightExpr = keycol.CreateTargetExpression(res),
CollateSpec = keycol.UseCollate(joinModel) ? "DATABASE_DEFAULT" : null,
});
}
from.Relations.Add(rel);
return res;
}
public DmlfColumnRef GetColumnRef(DmlfSource baseSource, NameWithSchema baseTable, StructuredIdentifier columnId, DatabaseInfo db, DmlfJoinType joinType)
{
var relationId = columnId.WithoutLast;
string column = columnId.Last;
var source = AddOrFindRelation(baseSource, baseTable, relationId, db, joinType);
if (source == null) return null;
return new DmlfColumnRef
{
ColumnName = column,
Source = source,
};
}
public DmlfSource AddOrFindRelation(DmlfSource baseSource, NameWithSchema baseTable, StructuredIdentifier relationId, DatabaseInfo db, DmlfJoinType joinType)
{
return DoAddOrFindRelation(baseSource, baseTable, new StructuredIdentifier(), relationId, db, joinType);
}
public void ReplaceSimpleSource(DmlfSource source, string newAlias)
{
source = source.GetSimpleSourceCopy();
ForEachChild(x =>
{
var src = x as DmlfSource;
if (src != null && src == source)
{
src.Alias = newAlias;
}
});
}
