/// <summary>
/// convert statements in annotation to according graph data modification statements
/// </summary>
/// <param name="script">SQL script</param>
/// <param name="annotations">stores information of data modification statements</param>
/// <returns>Converted script</returns>
private void ConvertToGraphModificationStatement(IList <WSqlStatement> statements)
{
for (var j = 0; j < statements.Count; ++j)
{
var stmt = statements[j];
if (_nodeCount >= _annotations.Count)
{
break;
}
if (stmt is WSelectQueryBlock && stmt.FirstTokenIndex == _annotations[_nodeCount].Position)
{
var newStmt = stmt as WSelectQueryBlock;
var annotation = _annotations[_nodeCount] as DeleteEdgeAnnotation;
if (annotation != null)
{
newStmt.MatchClause = new WMatchClause
{
Paths = new List <WMatchPath>
{
annotation.Path
}
}
}
;
statements[j] = new WDeleteEdgeSpecification(newStmt);
_nodeCount++;
}
if (!(stmt is WInsertSpecification) &&
!(stmt is WDeleteSpecification))
{
continue;
}
if (stmt.LastTokenIndex < _annotations[_nodeCount].Position)
{
continue;
}
if (_annotations[_nodeCount] is InsertNodeAnnotation)
{
statements[j] = new WInsertNodeSpecification(stmt as WInsertSpecification);
}
else if (_annotations[_nodeCount] is InsertEdgeAnnotation)
{
var annotation = _annotations[_nodeCount] as InsertEdgeAnnotation;
statements[j] = new WInsertEdgeSpecification(stmt as WInsertSpecification)
{
EdgeColumn = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(annotation.EdgeColumn)
},
};
}
else if (_annotations[_nodeCount] is DeleteNodeAnnotation)
{
statements[j] = new WDeleteNodeSpecification(stmt as WDeleteSpecification);
}
_nodeCount++;
}
}
}
public virtual void Visit(WInsertEdgeSpecification node)
{
node.AcceptChildren(this);
}
/// <summary>
/// Translates insert edge statement into update statement.
/// Uses common table expression to record the inserting adjacency-list,
/// then inserts result into a table variable with soucrc id as its primary to ensure the order,
/// finally update node tables.
/// </summary>
/// <param name="node">Insert edge specification</param>
/// <param name="res">Resulting statements</param>
private void TranslateEdgeInsert(WInsertEdgeSpecification node,List<WSqlStatement> res)
{
const string insertEdgeTableName = "GraphView_InsertEdgeInternalTable";
string insertEdgeTempTable = "InsertTmp_"+Path.GetRandomFileName().Replace(".", "").Substring(0, 8);
// Wraps the select statement in a common table expression
var edgeSelectQueryVisitor = new InsertEdgeSelectVisitor();
WTableReference sinkTable;
List<WScalarExpression> encodeParameters;
var insertCommonTableExpr = edgeSelectQueryVisitor.Invoke(node.SelectInsertSource.Select, insertEdgeTempTable, out sinkTable, out encodeParameters);
var sourceNameTable = node.Target as WNamedTableReference;
if (sourceNameTable == null)
throw new GraphViewException("Source table of INSERT EDGE statement should be a named table reference.");
var sinkNameTable = sinkTable as WNamedTableReference;
if (sinkNameTable == null)
throw new GraphViewException("Sink table of INSERT EDGE statement should be a named table reference.");
var tableSchema =
sourceNameTable.TableObjectName.SchemaIdentifier != null
? sourceNameTable.TableObjectName.SchemaIdentifier.Value
: "dbo";
var tableName = sourceNameTable.TableObjectName.BaseIdentifier.Value;
var edgeName = node.EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value;
// Select Into TempTable
//res.Add(insertCommonTableExpr);
// Get edge select query and reversed edge select query
WSelectQueryBlock edgeSelectQuery = new WSelectQueryBlock
{
SelectElements = new List<WSelectElement>
{
new WSelectScalarExpression
{
SelectExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "src"})
},
ColumnName = "source"
},
new WSelectScalarExpression
{
SelectExpr = new WFunctionCall
{
CallTarget = new WMultiPartIdentifierCallTarget
{
Identifiers = new WMultiPartIdentifier(new Identifier {Value = "dbo"})
},
FunctionName = new Identifier {Value = tableSchema + '_' + tableName +'_' + edgeName + '_' + "Encoder"},
Parameters = encodeParameters,
},
ColumnName = "binary1"
},
new WSelectScalarExpression
{
SelectExpr = new WFunctionCall
{
FunctionName = new Identifier{Value = "COUNT"},
Parameters = new List<WScalarExpression>
{
new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "sink"})
},
},
},
ColumnName = "sinkCnt"
}
},
FromClause = new WFromClause
{
TableReferences = new List<WTableReference>
{
new WSpecialNamedTableReference()
{
TableObjectName = new WSchemaObjectName(new Identifier{Value = insertEdgeTempTable })
}
}
},
GroupByClause = new WGroupByClause
{
GroupingSpecifications = new List<WGroupingSpecification>
{
new WExpressionGroupingSpec
{
Expression = new WValueExpression("src",false)
}
}
}
};
WSelectQueryBlock reversedEdgeSelectQuery = new WSelectQueryBlock
{
SelectElements = new List<WSelectElement>
{
new WSelectScalarExpression
{
SelectExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "sink"})
},
ColumnName = "source"
},
new WSelectScalarExpression
{
SelectExpr = new WFunctionCall
{
FunctionName =
new Identifier
{
//Value = GraphViewConnection.GraphViewUdfAssemblyName + "GlobalNodeIdEncoder"
Value = "COUNT"
},
Parameters = new List<WScalarExpression>
{
new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "src"})
},
}
},
ColumnName = "srcCnt"
},
},
FromClause = edgeSelectQuery.FromClause,
GroupByClause = new WGroupByClause
{
GroupingSpecifications = new List<WGroupingSpecification>
{
new WExpressionGroupingSpec
{
Expression = new WValueExpression("sink", false)
}
}
}
};
//Check src & sink
string sourceTableName = sourceNameTable.TableObjectName.ToString();
string sinkTableName = sinkNameTable.TableObjectName.ToString();
int compare = string.CompareOrdinal(sourceTableName, sinkTableName);
WSetClause[] setClauses = new WSetClause[]
{
new WFunctionCallSetClause()
{
MutatorFuction = new WFunctionCall
{
CallTarget = new WMultiPartIdentifierCallTarget()
{
Identifiers = node.EdgeColumn.MultiPartIdentifier
},
FunctionName = new Identifier {Value = "WRITE"},
Parameters = new WScalarExpression[]
{
new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(
new Identifier {Value = insertEdgeTableName},
new Identifier {Value = "binary1"})
},
new WValueExpression("null", false),
new WValueExpression("null", false)
}
}
},
new WAssignmentSetClause()
{
AssignmentKind = AssignmentKind.Equals,
Column = new WColumnReferenceExpression
{
MultiPartIdentifier =
new WMultiPartIdentifier(new Identifier
{
Value = "InDegree"
})
},
NewValue = new WBinaryExpression
{
FirstExpr = new WColumnReferenceExpression
{
MultiPartIdentifier =
new WMultiPartIdentifier(new Identifier
{
Value = "InDegree"
})
},
SecondExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "srcCnt"})
},
ExpressionType = BinaryExpressionType.Add
}
},
new WAssignmentSetClause()
{
AssignmentKind = AssignmentKind.Equals,
Column = new WColumnReferenceExpression
{
MultiPartIdentifier =
new WMultiPartIdentifier(new Identifier
{
Value = node.EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + "OutDegree"
})
},
NewValue = new WBinaryExpression
{
FirstExpr = new WColumnReferenceExpression
{
MultiPartIdentifier =
new WMultiPartIdentifier(new Identifier
{
Value = node.EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + "OutDegree"
})
},
SecondExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "sinkCnt"})
},
ExpressionType = BinaryExpressionType.Add
}
}
};
WWhereClause whereClause = new WWhereClause
{
SearchCondition = new WBooleanComparisonExpression
{
FirstExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "GlobalNodeId"})
},
SecondExpr = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(
new Identifier {Value = insertEdgeTableName},
new Identifier {Value = "source"}
)
},
ComparisonType = BooleanComparisonType.Equals
}
};
if (compare == 0)
{
res.Add(new WDeclareTableVariable
{
VariableName = new Identifier { Value = "@"+insertEdgeTempTable},
Definition = new WTableDefinition
{
ColumnDefinitions = new WColumnDefinition[]
{
new WColumnDefinition
{
ColumnIdentifier = new Identifier{Value = "source"},
DataType = new WParameterizedDataTypeReference{Name = new WSchemaObjectName(new Identifier{Value = "bigint"})},
Constraints = new List<WConstraintDefinition>
{
new WUniqueConstraintDefinition
{
Clustered = true,
IsPrimaryKey = true
}
}
},
new WColumnDefinition
{
ColumnIdentifier = new Identifier{Value = "binary1"},
DataType = new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier{Value = "varbinary"}),
Parameters = new List<Literal>{new MaxLiteral{Value = "max"}}
},
},
new WColumnDefinition
{
ColumnIdentifier = new Identifier{Value = "sinkCnt"},
DataType = new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier{Value = "int"}),
},
},
new WColumnDefinition
{
ColumnIdentifier = new Identifier{Value = "srcCnt"},
DataType = new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier{Value = "int"}),
},
},
},
Indexes = new List<WIndexDefinition>(),
TableConstraints = new List<WConstraintDefinition>()
}
});
res.Add(insertCommonTableExpr);
var tableVar = new WSpecialNamedTableReference
{
TableObjectName = new WSchemaObjectName(new Identifier {Value = "@" + insertEdgeTempTable})
};
res.Add(new WInsertSpecification
{
Target =tableVar,
InsertSource = new WSelectInsertSource
{
Select = new WSelectQueryBlock
{
SelectElements = new WSelectElement[]
{
new WSelectScalarExpression
{
SelectExpr = new WFunctionCall
{
FunctionName = new Identifier {Value = "ISNULL"},
Parameters = new WScalarExpression[]
{
new WColumnReferenceExpression
{
MultiPartIdentifier =
new WSchemaObjectName(new Identifier[]
{
new Identifier {Value = insertEdgeTableName + "_1"},
new Identifier {Value = "source"}
}),
},
new WColumnReferenceExpression
{
MultiPartIdentifier =
new WSchemaObjectName(new Identifier[]
{
new Identifier {Value = insertEdgeTableName + "_2"},
new Identifier {Value = "source"}
}),
},
}
},
ColumnName = "source"
},
new WSelectScalarExpression
{
SelectExpr = new WColumnReferenceExpression
{
MultiPartIdentifier =
new WSchemaObjectName(new Identifier[] {new Identifier {Value = "binary1"}}),
}
},
//new WSelectScalarExpression
//{
// SelectExpr = new WColumnReferenceExpression
// {
// MultiPartIdentifier = new WSchemaObjectName(new Identifier[]{new Identifier{Value = "binary2"}}),
// }
//},
new WSelectScalarExpression
{
SelectExpr = new WFunctionCall
{
FunctionName = new Identifier {Value = "ISNULL"},
Parameters = new WScalarExpression[]
{
new WColumnReferenceExpression
{
MultiPartIdentifier =
new WSchemaObjectName(new Identifier[]
{
new Identifier {Value = insertEdgeTableName + "_1"},
new Identifier {Value = "sinkCnt"}
}),
},
new WValueExpression
{
Value = "0"
},
}
},
ColumnName = "sinkCnt"
},
new WSelectScalarExpression
{
SelectExpr = new WFunctionCall
{
FunctionName = new Identifier {Value = "ISNULL"},
Parameters = new WScalarExpression[]
{
new WColumnReferenceExpression
{
MultiPartIdentifier =
new WSchemaObjectName(new Identifier[]
{
new Identifier {Value = insertEdgeTableName + "_2"},
new Identifier {Value = "srcCnt"}
}),
},
new WValueExpression
{
Value = "0"
},
}
},
ColumnName = "srcCnt"
},
},
FromClause = new WFromClause
{
TableReferences = new WTableReference[]
{
new WQualifiedJoin
{
QualifiedJoinType = QualifiedJoinType.FullOuter,
FirstTableRef = new WQueryDerivedTable
{
Alias = new Identifier {Value = insertEdgeTableName + "_1"},
QueryExpr = edgeSelectQuery
},
SecondTableRef = new WQueryDerivedTable
{
Alias = new Identifier {Value = insertEdgeTableName + "_2"},
QueryExpr = reversedEdgeSelectQuery
},
JoinCondition = new WBooleanComparisonExpression()
{
ComparisonType = BooleanComparisonType.Equals,
FirstExpr = new WColumnReferenceExpression
{
MultiPartIdentifier =
new WSchemaObjectName(new Identifier[]
{
new Identifier {Value = insertEdgeTableName + "_1"},
new Identifier {Value = "source"}
}),
},
SecondExpr = new WColumnReferenceExpression
{
MultiPartIdentifier =
new WSchemaObjectName(new Identifier[]
{
new Identifier {Value = insertEdgeTableName + "_2"},
new Identifier {Value = "source"}
}),
}
}
},
}
}
}
}
});
res.Add(new WUpdateSpecification
{
Target = node.Target,
SetClauses = setClauses,
FromClause = new WFromClause
{
TableReferences = new WTableReference[]
{
new WQueryDerivedTable
{
Alias = new Identifier{Value = insertEdgeTableName},
QueryExpr = new WSelectQueryBlock
{
SelectElements = new List<WSelectElement>()
{
new WSelectStarExpression()
},
FromClause = new WFromClause
{
TableReferences = new List<WTableReference>{tableVar}
}
}
},
}
},
WhereClause = whereClause,
TopRowFilter = node.TopRowFilter,
});
res.Add(new WSqlUnknownStatement
{
Statement = String.Format("DELETE FROM @{0}", insertEdgeTempTable)
});
}
else
{
// Declare table variables
res.Add(new WDeclareTableVariable
{
VariableName = new Identifier {Value = "@" + insertEdgeTempTable+"_1"},
Definition = new WTableDefinition
{
ColumnDefinitions = new WColumnDefinition[]
{
new WColumnDefinition
{
ColumnIdentifier = new Identifier {Value = "source"},
DataType =
new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier {Value = "bigint"})
},
Constraints = new List<WConstraintDefinition>
{
new WUniqueConstraintDefinition
{
Clustered = true,
IsPrimaryKey = true
}
}
},
new WColumnDefinition
{
ColumnIdentifier = new Identifier {Value = "binary1"},
DataType = new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier {Value = "varbinary"}),
Parameters = new List<Literal> {new MaxLiteral {Value = "max"}}
},
},
new WColumnDefinition
{
ColumnIdentifier = new Identifier {Value = "sinkCnt"},
DataType = new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier {Value = "int"}),
},
},
},
Indexes = new List<WIndexDefinition>(),
TableConstraints = new List<WConstraintDefinition>()
}
});
res.Add(new WDeclareTableVariable
{
VariableName = new Identifier {Value = "@" + insertEdgeTempTable+"_2"},
Definition = new WTableDefinition
{
ColumnDefinitions = new WColumnDefinition[]
{
new WColumnDefinition
{
ColumnIdentifier = new Identifier {Value = "source"},
DataType =
new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier {Value = "bigint"})
},
Constraints = new List<WConstraintDefinition>
{
new WUniqueConstraintDefinition
{
Clustered = true,
IsPrimaryKey = true
}
}
},
new WColumnDefinition
{
ColumnIdentifier = new Identifier {Value = "srcCnt"},
DataType = new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier {Value = "int"}),
},
},
},
Indexes = new List<WIndexDefinition>(),
TableConstraints = new List<WConstraintDefinition>()
}
});
var tableVar = new WSpecialNamedTableReference
{
TableObjectName = new WSchemaObjectName(new Identifier { Value = "@" + insertEdgeTempTable+"_1" })
};
var reversedTableVar = new WSpecialNamedTableReference
{
TableObjectName = new WSchemaObjectName(new Identifier { Value = "@" + insertEdgeTempTable + "_2" })
};
WInsertSpecification[] insertStat = new WInsertSpecification[]
{
new WInsertSpecification
{
Target = tableVar,
InsertSource = new WSelectInsertSource
{
Select = edgeSelectQuery
}
},
new WInsertSpecification
{
Target = reversedTableVar,
InsertSource = new WSelectInsertSource
{
Select = reversedEdgeSelectQuery
}
}
};
// Update statments for edge and reversed edge
WUpdateSpecification[] updateStat = new WUpdateSpecification[]
{
new WUpdateSpecification
{
Target = node.Target,
SetClauses = new List<WSetClause> {setClauses[0], setClauses[2]},
FromClause = new WFromClause
{
TableReferences = new WTableReference[]
{
new WQueryDerivedTable
{
Alias = new Identifier {Value = insertEdgeTableName},
QueryExpr = new WSelectQueryBlock
{
SelectElements = new List<WSelectElement>()
{
new WSelectStarExpression()
},
FromClause = new WFromClause
{
TableReferences = new List<WTableReference> {tableVar}
}
}
},
}
},
WhereClause = whereClause,
TopRowFilter = node.TopRowFilter
},
new WUpdateSpecification
{
Target = new WNamedTableReference
{
TableObjectName = sinkNameTable.TableObjectName
},
SetClauses = new List<WSetClause> {setClauses[1]},
FromClause = new WFromClause
{
TableReferences = new WTableReference[]
{
new WQueryDerivedTable
{
Alias = new Identifier {Value = insertEdgeTableName},
QueryExpr = new WSelectQueryBlock
{
SelectElements = new List<WSelectElement>()
{
new WSelectStarExpression()
},
FromClause = new WFromClause
{
TableReferences = new List<WTableReference> {reversedTableVar}
}
}
},
}
},
WhereClause = whereClause,
TopRowFilter = node.TopRowFilter
},
};
bool sourceFirst = compare<0;
if (sourceFirst)
{
// With expression
res.Add(insertCommonTableExpr);
res.Add(insertStat[0]);
res.Add(updateStat[0]);
res.Add(new WSqlUnknownStatement
{
Statement = String.Format("DELETE FROM @{0}_1", insertEdgeTempTable)
});
// With expression
res.Add(insertCommonTableExpr);
res.Add(insertStat[1]);
res.Add(updateStat[1]);
res.Add(new WSqlUnknownStatement
{
Statement = String.Format("DELETE FROM @{0}_2", insertEdgeTempTable)
});
}
else
{
// With expression
res.Add(insertCommonTableExpr);
res.Add(insertStat[1]);
res.Add(updateStat[1]);
res.Add(new WSqlUnknownStatement
{
Statement = String.Format("DELETE FROM @{0}_2", insertEdgeTempTable)
});
// With expression
res.Add(insertCommonTableExpr);
res.Add(insertStat[0]);
res.Add(updateStat[0]);
res.Add(new WSqlUnknownStatement
{
Statement = String.Format("DELETE FROM @{0}_1", insertEdgeTempTable)
});
}
}
//res.Add(new WSqlUnknownStatement { Statement = "DROP TABLE " + insertEdgeTempTable });
}
public virtual void Visit(WInsertEdgeSpecification node)
{
node.AcceptChildren(this);
}
/// <summary>
/// convert statements in annotation to according graph data modification statements
/// </summary>
/// <param name="script">SQL script</param>
/// <param name="annotations">stores information of data modification statements</param>
/// <returns>Converted script</returns>
private void ConvertToGraphModificationStatement(IList<WSqlStatement> statements)
{
for (var j = 0; j < statements.Count; ++j)
{
var stmt = statements[j];
if (_nodeCount >= _annotations.Count)
break;
if (stmt is WSelectStatement && stmt.FirstTokenIndex == _annotations[_nodeCount].Position)
{
var newStmt = (stmt as WSelectStatement).QueryExpr as WSelectQueryBlock;
var annotation = _annotations[_nodeCount] as DeleteEdgeAnnotation;
if (annotation != null)
newStmt.MatchClause = new WMatchClause
{
Paths = new List<WMatchPath>
{
annotation.Path
}
};
statements[j] = new WDeleteEdgeSpecification(newStmt);
_nodeCount++;
}
if (!(stmt is WInsertSpecification) &&
!(stmt is WDeleteSpecification))
continue;
if (stmt.LastTokenIndex < _annotations[_nodeCount].Position)
{
continue;
}
if (_annotations[_nodeCount] is InsertNodeAnnotation)
{
statements[j] = new WInsertNodeSpecification(stmt as WInsertSpecification);
}
else if (_annotations[_nodeCount] is InsertEdgeAnnotation)
{
var annotation = _annotations[_nodeCount] as InsertEdgeAnnotation;
statements[j] = new WInsertEdgeSpecification(stmt as WInsertSpecification)
{
EdgeColumn = new WColumnReferenceExpression
{
MultiPartIdentifier = new WMultiPartIdentifier(annotation.EdgeColumn)
},
};
}
else if (_annotations[_nodeCount] is DeleteNodeAnnotation)
{
statements[j] = new WDeleteNodeSpecification(stmt as WDeleteSpecification);
}
_nodeCount++;
}
}