internal override SqlSource VisitJoin(SqlJoin join)
{
if (join.JoinType == SqlJoinType.CrossApply)
{
// Visit the left side as usual.
join.Left = this.VisitSource(join.Left);
// Visit the condition as usual.
join.Condition = this.VisitExpression(join.Condition);
// Visit the right, with the expressionSink set.
SelectScope s = expressionSink;
expressionSink = new SelectScope();
expressionSink.LeftProduction = SqlGatherProducedAliases.Gather(join.Left);
join.Right = this.VisitSource(join.Right);
// Were liftable expressions found?
SqlSource newSource = join;
foreach (List <SqlColumn> cols in expressionSink.Lifted)
{
newSource = PushSourceDown(newSource, cols);
}
expressionSink = s;
return(newSource);
}
return(base.VisitJoin(join));
}
static internal List <SqlColumn> GatherColumns(SqlSource source)
{
List <SqlColumn> columns = new List <SqlColumn>();
new ProducedColumnsGatherer(columns).Visit(source);
return(columns);
}
//INIT PIPELINE ACTIVITIES
/**
* not used currently
*/
public Activity create_Activity_Init_1()
{
Console.WriteLine("Creating " + DualLoadConfig.ACTIVITY_INIT_1);
Activity activity = new Activity();
List <ActivityInput> activityInputs = new List <ActivityInput>();
ActivityInput activityInput = new ActivityInput();
activityInput.Name = DualLoadConfig.DATASET_ETL_Control;
activityInputs.Add(activityInput);
SqlSource source = new SqlSource();
source.SqlReaderQuery = DualLoadConfig.QUERY_INIT_1;
List <ActivityOutput> activityOutputs = new List <ActivityOutput>();
ActivityOutput activityOutput = new ActivityOutput();
activityOutput.Name = DualLoadConfig.DATASET_LOAD_1_SQLDUMMY;
activityOutputs.Add(activityOutput);
SqlSink sink = new SqlSink();
CopyActivity copyActivity = new CopyActivity();
copyActivity.Source = source;
copyActivity.Sink = sink;
activity.Name = DualLoadConfig.ACTIVITY_INIT_1;
activity.Inputs = activityInputs;
activity.Outputs = activityOutputs;
activity.TypeProperties = copyActivity;
return(activity);
}
private SqlJoin GetLeftOuterWithUnreferencedSingletonOnLeft(SqlSource source)
{
SqlAlias alias = source as SqlAlias;
if (alias != null)
{
SqlSelect select = alias.Node as SqlSelect;
if (select != null &&
select.Where == null &&
select.Top == null &&
select.GroupBy.Count == 0 &&
select.OrderBy.Count == 0)
{
return(this.GetLeftOuterWithUnreferencedSingletonOnLeft(select.From));
}
}
SqlJoin join = source as SqlJoin;
if (join == null || join.JoinType != SqlJoinType.LeftOuter)
{
return(null);
}
if (!this.IsSingletonSelect(join.Left))
{
return(null);
}
HashSet <SqlAlias> p = SqlGatherProducedAliases.Gather(join.Left);
HashSet <SqlAlias> c = SqlGatherConsumedAliases.Gather(join.Right);
if (p.Overlaps(c))
{
return(null);
}
return(join);
}
internal static bool CanLift(SqlSource source, HashSet <SqlAlias> aliasesForLifting, HashSet <SqlExpression> liftedExpressions)
{
SelectionLifter v = new SelectionLifter(false, aliasesForLifting, liftedExpressions);
v.VisitSource(source);
return(v.CanLiftAll);
}
internal static List <List <SqlColumn> > Lift(SqlSource source, HashSet <SqlAlias> aliasesForLifting, HashSet <SqlExpression> liftedExpressions)
{
SelectionLifter v = new SelectionLifter(true, aliasesForLifting, liftedExpressions);
v.VisitSource(source);
return(v.Lifted);
}
private SqlSource PushSourceDown(SqlSource sqlSource, List <SqlColumn> cols)
{
SqlSelect ns = new SqlSelect(new SqlNop(cols[0].ClrType, cols[0].SqlType, sqlSource.SourceExpression), sqlSource, sqlSource.SourceExpression);
ns.Row.Columns.AddRange(cols);
return(new SqlAlias(ns));
}
/// <summary>
/// Cria uma nova consulta SQL, a partir da origem especificada.
/// </summary>
/// <param name="fields">Os campos</param>
/// <param name="source">A origem</param>
/// <param name="condition">A condição</param>
/// <param name="order">A ordem</param>
public SqlQuery(string[] fields, SqlSource source, SqlCondition condition, SqlOrder order)
{
if (fields != null)
this.fields.AddRange(fields);
this.source = source;
this.condition = condition;
this.order = order;
}
internal override SqlSource VisitJoin(SqlJoin join)
{
SqlSource left = this.VisitSource(@join.Left);
SqlSource right = this.VisitSource(@join.Right);
SqlExpression cond = (SqlExpression)this.Visit(@join.Condition);
return(new SqlJoin(@join.JoinType, left, right, cond, @join.SourceExpression));
}
internal static bool CanLift(SqlSource source, HashSet <SqlAlias> aliasesForLifting, HashSet <SqlExpression> liftedExpressions)
{
Diagnostics.Debug.Assert(source != null);
Diagnostics.Debug.Assert(aliasesForLifting != null);
PredicateLifter v = new PredicateLifter(false, aliasesForLifting, liftedExpressions);
v.VisitSource(source);
return(v.CanLiftAll);
}
private SqlUnion GetUnion(SqlSource source) {
SqlAlias alias = source as SqlAlias;
if (alias != null) {
SqlUnion union = alias.Node as SqlUnion;
if (union != null)
return union;
}
return null;
}
internal static SqlExpression Lift(SqlSource source, HashSet <SqlAlias> aliasesForLifting)
{
Diagnostics.Debug.Assert(source != null);
Diagnostics.Debug.Assert(aliasesForLifting != null);
PredicateLifter v = new PredicateLifter(true, aliasesForLifting, null);
v.VisitSource(source);
return(v.Lifted);
}
private bool HasTrivialSource(SqlSource node)
{
SqlAlias alias = node as SqlAlias;
if (alias == null)
{
return(false);
}
return(alias.Node is SqlSelect);
}
/// <summary>
/// Cria uma nova consulta SQL, a partir da origem especificada.
/// </summary>
/// <param name="fields">Os campos</param>
/// <param name="source">A origem</param>
/// <param name="condition">A condição</param>
/// <param name="order">A ordem</param>
public SqlQuery(string[] fields, SqlSource source, SqlCondition condition, SqlOrder order)
{
if (fields != null)
{
this.fields.AddRange(fields);
}
this.source = source;
this.condition = condition;
this.order = order;
}
private bool HasTrivialSource(SqlSource node)
{
SqlJoin join = node as SqlJoin;
if (@join != null)
{
return(this.HasTrivialSource(@join.Left) &&
this.HasTrivialSource(@join.Right));
}
return(node is SqlAlias);
}
public void TestPipeCreate()
{
Client client = new Client(jwt, subscriptionId);
Pipe p = new Pipe("id-for-my-new-pipe");
var source = new SqlSource();
source.SetTable("customer");
source.SetSystem("id-for-my-new-system");
source.SetType("sql");
p.WithSource(source);
string json = client.CreatePipe(p);
}
internal override SqlSelect VisitSelect(SqlSelect select)
{
SqlSource from = this.VisitSource(@select.From);
List <SqlExpression> gex = null;
if (@select.GroupBy.Count > 0)
{
gex = new List <SqlExpression>(@select.GroupBy.Count);
foreach (SqlExpression sqlExpr in @select.GroupBy)
{
gex.Add((SqlExpression)this.Visit(sqlExpr));
}
}
SqlExpression having = (SqlExpression)this.Visit(@select.Having);
List <SqlOrderExpression> lex = null;
if (@select.OrderBy.Count > 0)
{
lex = new List <SqlOrderExpression>(@select.OrderBy.Count);
foreach (SqlOrderExpression sox in @select.OrderBy)
{
SqlOrderExpression nsox = new SqlOrderExpression(sox.OrderType, (SqlExpression)this.Visit(sox.Expression));
lex.Add(nsox);
}
}
SqlExpression top = (SqlExpression)this.Visit(@select.Top);
SqlExpression where = (SqlExpression)this.Visit(@select.Where);
SqlRow row = (SqlRow)this.Visit(@select.Row);
SqlExpression selection = this.VisitExpression(@select.Selection);
SqlSelect n = new SqlSelect(selection, @from, @select.SourceExpression);
if (gex != null)
{
n.GroupBy.AddRange(gex);
}
n.Having = having;
if (lex != null)
{
n.OrderBy.AddRange(lex);
}
n.OrderingType = @select.OrderingType;
n.Row = row;
n.Top = top;
n.IsDistinct = @select.IsDistinct;
n.IsPercent = @select.IsPercent;
n.Where = @where;
n.DoNotOutput = @select.DoNotOutput;
return(n);
}
private SqlUnion GetUnion(SqlSource source)
{
SqlAlias alias = source as SqlAlias;
if (alias != null)
{
SqlUnion union = alias.Node as SqlUnion;
if (union != null)
{
return(union);
}
}
return(null);
}
internal override SqlSource VisitSource(SqlSource node)
{
node = (SqlSource)this.Visit(node);
SqlAlias alias = node as SqlAlias;
if (alias != null)
{
SqlSelect sel = alias.Node as SqlSelect;
if (sel != null && this.IsTrivialSelect(sel))
{
_removedMap[alias] = alias;
node = sel.From;
}
}
return(node);
}
public void TestDataflowDbToDb()
{
using (TestDb)
{
string destSchema = "test";
string destTable = "Staging3";
string destObject = $"[{destSchema}].[{destTable}]";
new DropAndCreateTableTask(TestDb.SqlConnection).Execute(destSchema, destTable, new List <TableColumn>()
{
Ziel_F0, Ziel_F1, Ziel_F2, Ziel_F3
});
SqlSource <Datensatz_DbToDb> DBSource = new SqlSource <Datensatz_DbToDb>(TestDb.getNewSqlConnection()
, "SELECT 0 as F1"
+ " UNION ALL SELECT 4 as F1"
+ " UNION ALL SELECT -3 as F1"
+ " UNION ALL SELECT -2 as F1"
);
DBSource.DataMappingMethod = ReaderAdapter_DbToDb.Read;
SqlDestination <Datensatz_DbToDb> destination = new SqlDestination <Datensatz_DbToDb>();
destination.ObjectName = destObject;
destination.FieldCount = 4;
destination.ObjectMappingMethod = WriterAdapter_DbToDb.Fill;
destination.SqlConnection = TestDb.SqlConnection;
Graph g = new Graph();
g.GetVertex(0, DBSource);
g.GetVertex(1, new RowTransformation <Datensatz_DbToDb>(RowTransformationDB));
g.GetVertex(2, new RowTransformation <Datensatz_DbToDb>(RowTransformationDB2));
g.GetVertex(3, destination);
g.AddEdge(0, 1); // connect 0 to 1
g.AddEdge(1, 2); // connect 1 to 2
g.AddEdge(2, 3); // connect 2 to 3
DataFlowTask <Datensatz_DbToDb> .Execute("Test dataflow task", 10000, 1, g);
//TestHelper.VisualizeGraph(g);
Assert.AreEqual(4, new ExecuteSQLTask(TestDb.SqlConnection).ExecuteScalar(string.Format("select count(*) from {0}", destObject)));
}
}
public void SelectSourceTest()
{
SqlSource source = new SqlSource()
.Connection(DbContext.CreateConnection())
.FromQuery("SELECT * FROM cad_NCM LIMIT 100");
foreach (var item in source.Rows)
{
string line = "";
foreach (var field in source.GetFieldNames())
{
line += item[field] + "".PadRight(15);
}
WriteLine(line);
}
}
private void CreateDataSources()
{
_dataSources.Clear();
if (checkBoxSqlServer.Checked)
{
var sqlSource = new SqlSource(textBoxSqlServer.Text);
if (!sqlSource.TestConnection())
{
MessageBox.Show("Could not connect to database. Please check the connection string.", "Error");
}
else
{
_dataSources.Add(sqlSource);
}
}
if (checkBoxCsvFile.Checked)
{
var csvSource = new CsvSource(textBoxCsvFile.Text);
if (!csvSource.TestConnection())
{
MessageBox.Show("Could not find the CSV file. Please check the path to the file.", "Error");
}
else
{
_dataSources.Add(csvSource);
}
}
if (checkBoxXmlFile.Checked)
{
var xmlSource = new XmlSource(textBoxXmlFile.Text);
if (!xmlSource.TestConnection())
{
MessageBox.Show("Could not find the XML file. Please check the path to the file.", "Error");
}
else
{
_dataSources.Add(xmlSource);
}
}
}
private bool IsSingletonSelect(SqlSource source)
{
SqlAlias alias = source as SqlAlias;
if (alias == null)
{
return(false);
}
SqlSelect select = alias.Node as SqlSelect;
if (select == null)
{
return(false);
}
if (select.From != null)
{
return(false);
}
return(true);
}
private void GetSelectionsBeforeJoin(SqlSource source, List <List <SqlColumn> > selections)
{
SqlJoin join = source as SqlJoin;
if (join != null)
{
return;
}
SqlAlias alias = source as SqlAlias;
if (alias != null)
{
SqlSelect select = alias.Node as SqlSelect;
if (select != null)
{
this.GetSelectionsBeforeJoin(select.From, selections);
selections.Add(select.Row.Columns);
}
}
}
protected virtual void VisitJoinSource(SqlSource src) // с этим нужно что-то делать в случае join-деревьев...
{
switch (src.NodeType)
{
case SqlExpressionType.Raw:
case SqlExpressionType.Select:
case SqlExpressionType.TableValuedFunction:
case SqlExpressionType.Union:
_depth++;
_builder.Append("(");
Visit(src);
_builder.Append(")");
_depth--;
break;
default:
Visit(src);
break;
}
}
private bool HasEmptySource(SqlSource node)
{
SqlAlias alias = node as SqlAlias;
if (alias == null)
{
return(false);
}
SqlSelect sel = alias.Node as SqlSelect;
if (sel == null)
{
return(false);
}
return(sel.Row.Columns.Count == 0 &&
sel.From == null &&
sel.Where == null &&
sel.GroupBy.Count == 0 &&
sel.Having == null &&
sel.OrderBy.Count == 0);
}
internal SqlJoin MakeJoin(SqlJoinType joinType, SqlSource location, SqlAlias alias, SqlExpression condition, Expression source)
{
// if the new item is on the right side of some outer join then fixup the projection to reflect that it can possibly be null
if (joinType == SqlJoinType.LeftOuter)
{
SqlSelect sel = alias.Node as SqlSelect;
if (sel != null && sel.Selection != null && sel.Selection.NodeType != SqlNodeType.OptionalValue)
{
// replace selection w/ optional + outer-joined-value
sel.Selection = new SqlOptionalValue(
new SqlColumn(
"test",
this.Unary(SqlNodeType.OuterJoinedValue,
this.Value(typeof(int?), this.typeProvider.From(typeof(int)), 1, false, source))
),
sel.Selection
);
}
}
return(new SqlJoin(joinType, location, alias, condition, source));
}
public void NCMDestinationTest()
{
SqlSource source = new SqlSource()
.FromQuery("SELECT GUID, EGUID, NCM, Descricao FROM cad_NCM LIMIT 100");
ObjectDestination <NCMJustForTest> destination = new ObjectDestination <NCMJustForTest>()
.ProcessWithAction((s, r) =>
{
s.Save();
WriteLine(s.ToString());
});
Transform transform = new Transform(source, destination)
.Map("GUID", "GUID")
.Map("EGUID", "EGUID")
.Map("NCM", "NCM", (e) =>
{
return(e);
})
.Map("Descricao", "Descricao")
.Execute();
}
internal override SqlSource VisitSource(SqlSource source) {
source = base.VisitSource(source);
SqlJoin join = source as SqlJoin;
if (join != null) {
if (join.JoinType == SqlJoinType.OuterApply) {
// Reduce outer-apply into left-outer-join
HashSet<SqlAlias> leftProducedAliases = SqlGatherProducedAliases.Gather(join.Left);
HashSet<SqlExpression> liftedExpressions = new HashSet<SqlExpression>();
if (SqlPredicateLifter.CanLift(join.Right, leftProducedAliases, liftedExpressions) &&
SqlSelectionLifter.CanLift(join.Right, leftProducedAliases, liftedExpressions) &&
!SqlAliasDependencyChecker.IsDependent(join.Right, leftProducedAliases, liftedExpressions) ) {
SqlExpression liftedPredicate = SqlPredicateLifter.Lift(join.Right, leftProducedAliases);
List<List<SqlColumn>> liftedSelections = SqlSelectionLifter.Lift(join.Right, leftProducedAliases, liftedExpressions);
join.JoinType = SqlJoinType.LeftOuter;
join.Condition = liftedPredicate;
if (liftedSelections != null) {
foreach(List<SqlColumn> selection in liftedSelections) {
source = this.PushSourceDown(source, selection);
}
}
}
else {
this.AnnotateSqlIncompatibility(join, SqlProvider.ProviderMode.Sql2000);
}
}
else if (join.JoinType == SqlJoinType.CrossApply) {
// reduce cross apply with special nested left-outer-join's into a single left-outer-join
//
// SELECT x.*, y.*
// FROM X
// CROSS APPLY (
// SELECT y.*
// FROM (
// SELECT ?
// )
// LEFT OUTER JOIN (
// SELECT y.* FROM Y
// ) AS y
//
// ==>
//
// SELECT x.*, y.*
// FROM X
// LEFT OUTER JOIN (
// SELECT y.* FROM Y
// )
SqlJoin leftOuter = this.GetLeftOuterWithUnreferencedSingletonOnLeft(join.Right);
if (leftOuter != null) {
HashSet<SqlAlias> leftProducedAliases = SqlGatherProducedAliases.Gather(join.Left);
HashSet<SqlExpression> liftedExpressions = new HashSet<SqlExpression>();
if (SqlPredicateLifter.CanLift(leftOuter.Right, leftProducedAliases, liftedExpressions) &&
SqlSelectionLifter.CanLift(leftOuter.Right, leftProducedAliases, liftedExpressions) &&
!SqlAliasDependencyChecker.IsDependent(leftOuter.Right, leftProducedAliases, liftedExpressions)
) {
SqlExpression liftedPredicate = SqlPredicateLifter.Lift(leftOuter.Right, leftProducedAliases);
List<List<SqlColumn>> liftedSelections = SqlSelectionLifter.Lift(leftOuter.Right, leftProducedAliases, liftedExpressions);
// add intermediate selections
this.GetSelectionsBeforeJoin(join.Right, liftedSelections);
// push down all selections
foreach(List<SqlColumn> selection in liftedSelections.Where(s => s.Count > 0)) {
source = this.PushSourceDown(source, selection);
}
join.JoinType = SqlJoinType.LeftOuter;
join.Condition = this.factory.AndAccumulate(leftOuter.Condition, liftedPredicate);
join.Right = leftOuter.Right;
}
else {
this.AnnotateSqlIncompatibility(join, SqlProvider.ProviderMode.Sql2000);
}
}
}
// re-balance join tree of left-outer-joins to expose LOJ w/ leftside unreferenced
while (join.JoinType == SqlJoinType.LeftOuter) {
// look for buried left-outer-joined-with-unreferenced singleton
SqlJoin leftLeftOuter = this.GetLeftOuterWithUnreferencedSingletonOnLeft(join.Left);
if (leftLeftOuter == null)
break;
List<List<SqlColumn>> liftedSelections = new List<List<SqlColumn>>();
// add intermediate selections
this.GetSelectionsBeforeJoin(join.Left, liftedSelections);
// push down all selections
foreach(List<SqlColumn> selection in liftedSelections) {
source = this.PushSourceDown(source, selection);
}
// bubble this one up on-top of this 'join'.
SqlSource jRight = join.Right;
SqlExpression jCondition = join.Condition;
join.Left = leftLeftOuter.Left;
join.Right = leftLeftOuter;
join.Condition = leftLeftOuter.Condition;
leftLeftOuter.Left = leftLeftOuter.Right;
leftLeftOuter.Right = jRight;
leftLeftOuter.Condition = jCondition;
}
}
return source;
}
private bool HasTrivialSource(SqlSource node) {
SqlAlias alias = node as SqlAlias;
if (alias == null) return false;
return alias.Node is SqlSelect;
}
internal virtual SqlSource VisitSource(SqlSource source) {
return (SqlSource) this.Visit(source);
}
private bool IsSingletonSelect(SqlSource source) {
SqlAlias alias = source as SqlAlias;
if (alias == null)
return false;
SqlSelect select = alias.Node as SqlSelect;
if (select == null)
return false;
if (select.From != null)
return false;
return true;
}
internal virtual SqlSource VisitSource(SqlSource source) {
return (SqlSource) this.Visit(source);
}
internal static bool CanLift(SqlSource source, HashSet<SqlAlias> aliasesForLifting, HashSet<SqlExpression> liftedExpressions) {
Visitor v = new Visitor(false, aliasesForLifting, liftedExpressions);
v.VisitSource(source);
return v.canLiftAll;
}
internal override SqlSource VisitSource(SqlSource source) {
return source;
}
internal static bool CanLift(SqlSource source, HashSet<SqlAlias> aliasesForLifting, HashSet<SqlExpression> liftedExpressions) {
System.Diagnostics.Debug.Assert(source != null);
System.Diagnostics.Debug.Assert(aliasesForLifting != null);
Visitor v = new Visitor(false, aliasesForLifting, liftedExpressions);
v.VisitSource(source);
return v.canLiftAll;
}
private SqlSelect GetSourceSelect(SqlSource source) {
SqlAlias alias = source as SqlAlias;
if (alias == null) {
return null;
}
return alias.Node as SqlSelect;
}
// insert new join closest to the aliases it depends on
private bool IsOuterDependent(bool isOuterDependent, SqlSource location, HashSet<SqlAlias> consumed, out HashSet<SqlAlias> produced)
{
if (location.NodeType == SqlNodeType.Join)
{
// walk down join tree looking for best location for join
SqlJoin join = (SqlJoin)location;
if (this.IsOuterDependent(isOuterDependent, join.Left, consumed, out produced))
return true;
HashSet<SqlAlias> rightProduced;
bool rightIsOuterDependent = join.JoinType == SqlJoinType.LeftOuter || join.JoinType == SqlJoinType.OuterApply;
if (this.IsOuterDependent(rightIsOuterDependent, join.Right, consumed, out rightProduced))
return true;
produced.UnionWith(rightProduced);
}
else
{
SqlAlias a = location as SqlAlias;
if (a != null)
{
SqlSelect s = a.Node as SqlSelect;
if (s != null && !isOuterDependent && s.From != null)
{
if (this.IsOuterDependent(false, s.From, consumed, out produced))
return true;
}
}
produced = SqlGatherProducedAliases.Gather(location);
}
// look to see if this subtree fully satisfies join condition
if (consumed.IsSubsetOf(produced))
{
return isOuterDependent;
}
return false;
}
private bool HasEmptySource(SqlSource node) {
SqlAlias alias = node as SqlAlias;
if (alias == null) return false;
SqlSelect sel = alias.Node as SqlSelect;
if (sel == null) return false;
return sel.Row.Columns.Count == 0 &&
sel.From == null &&
sel.Where == null &&
sel.GroupBy.Count == 0 &&
sel.Having == null &&
sel.OrderBy.Count == 0;
}
private bool HasTrivialSource(SqlSource node) {
SqlJoin join = node as SqlJoin;
if (join != null) {
return this.HasTrivialSource(join.Left) &&
this.HasTrivialSource(join.Right);
}
return node is SqlAlias;
}
internal override SqlSource VisitSource(SqlSource node) {
node = (SqlSource)this.Visit(node);
SqlAlias alias = node as SqlAlias;
if (alias != null) {
SqlSelect sel = alias.Node as SqlSelect;
if (sel != null && this.IsTrivialSelect(sel)) {
this.removedMap[alias] = alias;
node = sel.From;
}
}
return node;
}
private SqlJoin GetLeftOuterWithUnreferencedSingletonOnLeft(SqlSource source) {
SqlAlias alias = source as SqlAlias;
if (alias != null) {
SqlSelect select = alias.Node as SqlSelect;
if (select != null &&
select.Where == null &&
select.Top == null &&
select.GroupBy.Count == 0 &&
select.OrderBy.Count == 0) {
return this.GetLeftOuterWithUnreferencedSingletonOnLeft(select.From);
}
}
SqlJoin join = source as SqlJoin;
if (join == null || join.JoinType != SqlJoinType.LeftOuter)
return null;
if (!this.IsSingletonSelect(join.Left))
return null;
HashSet<SqlAlias> p = SqlGatherProducedAliases.Gather(join.Left);
HashSet<SqlAlias> c = SqlGatherConsumedAliases.Gather(join.Right);
if (p.Overlaps(c)) {
return null;
}
return join;
}
private void GetSelectionsBeforeJoin(SqlSource source, List<List<SqlColumn>> selections) {
SqlJoin join = source as SqlJoin;
if (join != null)
return;
SqlAlias alias = source as SqlAlias;
if (alias != null) {
SqlSelect select = alias.Node as SqlSelect;
if (select != null) {
this.GetSelectionsBeforeJoin(select.From, selections);
selections.Add(select.Row.Columns);
}
}
}
// insert new join in an appropriate location within an existing join tree
private bool IsOuterDependent(SqlSource location, SqlAlias alias, SqlExpression where)
{
HashSet<SqlAlias> consumed = SqlGatherConsumedAliases.Gather(where);
consumed.ExceptWith(SqlGatherProducedAliases.Gather(alias));
HashSet<SqlAlias> produced;
if (this.IsOuterDependent(false, location, consumed, out produced))
return true;
return false;
}
protected virtual void VisitJoinSource(SqlSource src) // с этим нужно что-то делать в случае join-деревьев...
{
switch (src.NodeType)
{
case SqlExpressionType.Raw:
case SqlExpressionType.Select:
case SqlExpressionType.TableValuedFunction:
case SqlExpressionType.Union:
_depth++;
_builder.Append("(");
Visit(src);
_builder.Append(")");
_depth--;
break;
default:
Visit(src);
break;
}
}
static void Main(string[] args)
{
string resourceGroupName = "PyWeiResourceGroup";
string dataFactoryName = "PyWeiDataFactory";
TokenCloudCredentials aadTokenCredentials = new TokenCloudCredentials(
ConfigurationManager.AppSettings["SubscriptionId"],
GetAuthorizationHeader().Result);
Uri resourceManagerUri = new Uri(ConfigurationManager.AppSettings["ResourceManagerEndpoint"]);
DataFactoryManagementClient client = new DataFactoryManagementClient(aadTokenCredentials, resourceManagerUri);
//client.DataFactories.
var pip = client.Pipelines.Get(resourceGroupName, dataFactoryName, "CopyActivity1");
//Console.Write(pip.Pipeline.Name);
//DateTime PipelineActivePeriodStartTime = new DateTime(2017, 5, 11, 0, 0, 0, 0, DateTimeKind.Utc);
//DateTime PipelineActivePeriodEndTime = new DateTime(2017, 5, 12, 0, 0, 0, 0, DateTimeKind.Utc);
SqlSource s = new SqlSource();
s.SqlReaderQuery = "#3 the new query on 9.29 select column1 from atable";
client.Pipelines.CreateOrUpdate(resourceGroupName, dataFactoryName,
new PipelineCreateOrUpdateParameters()
{
Pipeline = new Pipeline()
{
Name = pip.Pipeline.Name,
Properties = new PipelineProperties()
{
//Description = "Description",
// Initial value for pipeline's active period. With this, you won't need to set slice status
//Start = PipelineActivePeriodStartTime,
//End = PipelineActivePeriodEndTime,
Activities = new List <Activity>()
{
new Activity()
{
Name = "PatientAct",
Inputs = new List <ActivityInput>()
{
new ActivityInput()
{
Name = "PatientPayerODS"
}
},
Outputs = new List <ActivityOutput>()
{
new ActivityOutput()
{
Name = "PatientPayerODS"
}
},
TypeProperties = new CopyActivity()
{
Source = s,
Sink = new SqlSink()
{
WriteBatchSize = 0,
WriteBatchTimeout = TimeSpan.FromMinutes(0)
}
}
}
}
}
}
});
Console.WriteLine("Success!");
Console.Read();
}
internal static List<List<SqlColumn>> Lift(SqlSource source, HashSet<SqlAlias> aliasesForLifting, HashSet<SqlExpression> liftedExpressions) {
Visitor v = new Visitor(true, aliasesForLifting, liftedExpressions);
v.VisitSource(source);
return v.lifted;
}
public void TestDataflow_Massendaten()
{
using (TestDb)
{
int SkalaGrenze = 10000;
for (int i = SkalaGrenze * -1; i < SkalaGrenze; i = i + 50)
{
MetrischeSkala.Add(new IntervalPointMetric(i, i));
}
int Anzahl_je_Faktor = 10000;
int Anzahl_Faktoren = 10;
string TempObjectNameName = "test.tmp";
new DropTableTask(TestDb.getNewSqlConnection()).Execute(TempObjectNameName);
string QuellSchemaName = "test";
string QuellTabelle = "source";
string QuellObjekt = $"[{QuellSchemaName}].[{QuellTabelle}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(QuellSchemaName, QuellTabelle, new List <TableColumn>()
{
new TableColumn("Key", SqlDbType.Int, false, true, true),
new TableColumn("F1", SqlDbType.Int, true),
new TableColumn("F2", SqlDbType.Int, true),
new TableColumn("F3", SqlDbType.Int, true),
new TableColumn("F4", SqlDbType.Int, true),
new TableColumn("F5", SqlDbType.Int, true),
new TableColumn("F6", SqlDbType.Int, true),
new TableColumn("F7", SqlDbType.Int, true),
new TableColumn("F8", SqlDbType.Int, true),
new TableColumn("F9", SqlDbType.Int, true),
new TableColumn("F10", SqlDbType.Int, true),
});
string sql_generate_Massendaten = @"
select top 0 F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 into " + TempObjectNameName + @" from " + QuellObjekt + @" -- tmp-Tabelle erstellen
declare @grenze as int = " + SkalaGrenze + @"
declare @i as int = 0
while (@i < " + Anzahl_je_Faktor + @")
begin
insert into test.tmp
select @i % @grenze, @i % @grenze + 1, @i % @grenze + 2, (@i % @grenze) * -1, (@i % @grenze) * -1 -1, @i % @grenze, @i % @grenze -1, @i % @grenze +2, @i% @grenze+3, @i % @grenze+4
set @i = @i + 1
end
declare @j as int = 0
while (@j < " + Anzahl_Faktoren + @")
begin
insert into " + QuellObjekt + @"
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 from test.tmp
set @j = @j + 1
end
"
;
Debug.WriteLine("Generiere Massendaten ... ");
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(sql_generate_Massendaten);
string ZielSchemaName = "test";
string ZielTabelle = "destination";
string ZielObjekt = $"[{ZielSchemaName}].[{ZielTabelle}]";
new DropAndCreateTableTask(TestDb.getNewSqlConnection()).Execute(ZielSchemaName, ZielTabelle, new List <TableColumn>()
{
new TableColumn("Key", SqlDbType.Int, false, true, true),
new TableColumn("F1", SqlDbType.Int, true), new TableColumn("F1_calc", SqlDbType.Int, true),
new TableColumn("F2", SqlDbType.Int, true), new TableColumn("F2_calc", SqlDbType.Int, true),
new TableColumn("F3", SqlDbType.Int, true), new TableColumn("F3_calc", SqlDbType.Int, true),
new TableColumn("F4", SqlDbType.Int, true), new TableColumn("F4_calc", SqlDbType.Int, true),
new TableColumn("F5", SqlDbType.Int, true), new TableColumn("F5_calc", SqlDbType.Int, true),
new TableColumn("F6", SqlDbType.Int, true), new TableColumn("F6_calc", SqlDbType.Int, true),
new TableColumn("F7", SqlDbType.Int, true), new TableColumn("F7_calc", SqlDbType.Int, true),
new TableColumn("F8", SqlDbType.Int, true), new TableColumn("F8_calc", SqlDbType.Int, true),
new TableColumn("F9", SqlDbType.Int, true), new TableColumn("F9_calc", SqlDbType.Int, true),
new TableColumn("F10", SqlDbType.Int, true), new TableColumn("F10_calc", SqlDbType.Int, true),
});
System.Data.SqlClient.SqlConnectionStringBuilder builder_CurrentDbConnection
= new System.Data.SqlClient.SqlConnectionStringBuilder(TestDb.getNewSqlConnection().ConnectionString);
string Current_InitialCatalog = builder_CurrentDbConnection.InitialCatalog;
string Current_DataSource = builder_CurrentDbConnection.DataSource;
SqlSource <Datensatz> DBSource = new SqlSource <Datensatz>(TestDb.getNewSqlConnection()
, string.Format("select [Key],F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 from {0}", QuellObjekt)
);
DBSource.DataMappingMethod = ReaderAdapter.Read;
SqlDestination <Datensatz> Ziel_Schreibe = new SqlDestination <Datensatz>();
Ziel_Schreibe.ObjectName = ZielObjekt;
Ziel_Schreibe.FieldCount = FieldCount;
Ziel_Schreibe.ObjectMappingMethod = WriterAdapter.Fill;
Ziel_Schreibe.SqlConnection = TestDb.SqlConnection;
Graph g = new Graph();
g.GetVertex(0, DBSource);
g.GetVertex(1, new RowTransformation <Datensatz>(RowTransformationDB));
g.GetVertex(2, Ziel_Schreibe);
g.AddEdge(0, 1); // connect 0 to 1
g.AddEdge(1, 2); // connect 1 to 2
//TestHelper.VisualizeGraph(g);
int MaxDegreeOfParallelism = 1;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
Stopwatch s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
MaxDegreeOfParallelism = 5;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
MaxDegreeOfParallelism = 10;
new ExecuteSQLTask(TestDb.getNewSqlConnection()).ExecuteNonQuery(string.Format("truncate table {0}", ZielObjekt));
Debug.WriteLine("Start Laufzeittest MaxDegreeOfParallelism {0} ... ", MaxDegreeOfParallelism);
s = Stopwatch.StartNew();
DBSource.SqlConnection = TestDb.getNewSqlConnection();
DataFlowTask <Datensatz> .Execute("Test dataflow task", 10000, MaxDegreeOfParallelism, g);
Debug.WriteLine("Laufzeit in ms: {0}", s.ElapsedMilliseconds);
Assert.AreEqual(Anzahl_je_Faktor * Anzahl_Faktoren, new ExecuteSQLTask(TestDb.SqlConnection).ExecuteScalar(string.Format("select count(*) from {0}", QuellObjekt)));
}
}
static internal List<SqlColumn> GatherColumns(SqlSource source) {
List<SqlColumn> columns = new List<SqlColumn>();
new Visitor(columns).Visit(source);
return columns;
}
internal override SqlSource VisitSource(SqlSource source) {
return base.VisitSource(source);
}
/// <summary>
/// Cria uma nova consulta SQL, a partir da origem especificada.
/// </summary>
/// <param name="source">A origem</param>
/// <param name="condition">A condição</param>
/// <param name="orderBy">A ordem</param>
public SqlQuery(SqlSource source, SqlCondition condition, SqlOrder orderBy)
: this(null, source, condition, orderBy)
{
}
private SqlSource PushSourceDown(SqlSource sqlSource, List<SqlColumn> cols) {
SqlSelect ns = new SqlSelect(new SqlNop(cols[0].ClrType, cols[0].SqlType, sqlSource.SourceExpression), sqlSource, sqlSource.SourceExpression);
ns.Row.Columns.AddRange(cols);
return new SqlAlias(ns);
}
/// <summary>
/// Cria uma nova consulta SQL, a partir da origem especificada.
/// </summary>
/// <param name="source">A origem</param>
/// <param name="condition">A condição</param>
public SqlQuery(SqlSource source, SqlCondition condition)
: this(null, source, condition, null)
{
}
public static SqlExpression Parse(Expression expression, SqlSource source)
{
return(Parse(expression, new List <SqlSource> {
source
}));
}
/// <summary>
/// Cria uma nova consulta SQL, a partir da origem especificada.
/// </summary>
/// <param name="source">A origem</param>
public SqlQuery(SqlSource source)
: this(null, source, null, null)
{
}
internal SqlJoin MakeJoin(SqlJoinType joinType, SqlSource location, SqlAlias alias, SqlExpression condition, Expression source) {
// if the new item is on the right side of some outer join then fixup the projection to reflect that it can possibly be null
if (joinType == SqlJoinType.LeftOuter) {
SqlSelect sel = alias.Node as SqlSelect;
if (sel != null && sel.Selection != null && sel.Selection.NodeType != SqlNodeType.OptionalValue) {
// replace selection w/ optional + outer-joined-value
sel.Selection = new SqlOptionalValue(
new SqlColumn(
"test",
this.Unary(SqlNodeType.OuterJoinedValue,
this.Value(typeof(int?), this.typeProvider.From(typeof(int)), 1, false, source))
),
sel.Selection
);
}
}
return new SqlJoin(joinType, location, alias, condition, source);
}
internal static SqlExpression Lift(SqlSource source, HashSet<SqlAlias> aliasesForLifting) {
System.Diagnostics.Debug.Assert(source != null);
System.Diagnostics.Debug.Assert(aliasesForLifting != null);
Visitor v = new Visitor(true, aliasesForLifting, null);
v.VisitSource(source);
return v.lifted;
}
internal void VisitJoinSource(SqlSource src) {
if (src.NodeType == SqlNodeType.Join) {
this.depth++;
sb.Append("(");
this.Visit(src);
sb.Append(")");
this.depth--;
} else {
this.Visit(src);
}
}
internal void BuildEqivalenceMap(SqlSource scope) {
this.map = new Dictionary<SqlColumn, SqlColumn>();
this.Visit(scope);
}
