private WSqlStatement ParseUpdateStatement(UpdateSpecification upSpec) { if (upSpec == null) return null; var wupSpec = new WUpdateSpecification { Target = ParseTableReference(upSpec.Target), FirstTokenIndex = upSpec.FirstTokenIndex, LastTokenIndex = upSpec.LastTokenIndex }; //TopRowFilter if (upSpec.TopRowFilter != null) { wupSpec.TopRowFilter = new WTopRowFilter { Percent = upSpec.TopRowFilter.Percent, WithTies = upSpec.TopRowFilter.WithTies, Expression = ParseScalarExpression(upSpec.TopRowFilter.Expression), FirstTokenIndex = upSpec.TopRowFilter.FirstTokenIndex, LastTokenIndex = upSpec.TopRowFilter.LastTokenIndex }; } //From Clause if (upSpec.FromClause != null && upSpec.FromClause.TableReferences != null) { wupSpec.FromClause = new WFromClause { FirstTokenIndex = upSpec.FromClause.FirstTokenIndex, LastTokenIndex = upSpec.FromClause.LastTokenIndex, TableReferences = new List<WTableReference>(upSpec.FromClause.TableReferences.Count) }; foreach (var pref in upSpec.FromClause.TableReferences.Select(ParseTableReference).Where(pref => pref != null)) { wupSpec.FromClause.TableReferences.Add(pref); } } //Where Clause if (upSpec.WhereClause != null && upSpec.WhereClause.SearchCondition != null) { wupSpec.WhereClause = new WWhereClause { FirstTokenIndex = upSpec.WhereClause.FirstTokenIndex, LastTokenIndex = upSpec.WhereClause.LastTokenIndex, SearchCondition = ParseBooleanExpression(upSpec.WhereClause.SearchCondition) }; } //Set Clauses IList<WSetClause> wsetClauses = new List<WSetClause>(upSpec.SetClauses.Count); foreach (var setClause in upSpec.SetClauses) { WSetClause wsetClause; switch (setClause.GetType().Name) { case "AssignmentSetClause": { var asSetClause = setClause as AssignmentSetClause; wsetClause = ParseAssignmentSetClause(asSetClause); break; } case "FunctionCallSetClause": { var fcSetClause = setClause as FunctionCallSetClause; var mtFunction = fcSetClause.MutatorFunction; wsetClause = new WFunctionCallSetClause { MutatorFuction = ParseScalarExpression(mtFunction) as WFunctionCall }; break; } default: continue; } wsetClauses.Add(wsetClause); } wupSpec.SetClauses = wsetClauses; return wupSpec; }
private void TranslateEdgeDelete(WDeleteEdgeSpecification node, List<WSqlStatement> res) { const string deleteEdgeTableName = "GraphView_DeleteEdgeInternalTable"; string deleteEdgeTempTable = "DeleteTmp_" + Path.GetRandomFileName().Replace(".", "").Substring(0, 8); // Wraps the select statement in a common table expression WTableReference sinkTable, sourceTable; var deleteCommonTableExpr = ConstructDeleteEdgeSelect(node.SelectDeleteExpr, node.EdgeColumn.Alias, deleteEdgeTempTable, out sinkTable, out sourceTable); var sourceNameTable = sourceTable as WNamedTableReference; if (sourceNameTable == null) throw new GraphViewException("Source table of DELETE EDGE statement should be a named table reference."); var sinkNameTable = sinkTable as WNamedTableReference; if (sinkNameTable == null) throw new GraphViewException("Sink table of DELETE EDGE statement should be a named table reference."); // 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 = GraphViewConnection.GraphViewUdfAssemblyName + "EdgeIdEncoder"}, Parameters = new List<WScalarExpression> { new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier(new Identifier {Value = "edgeid"}) }, }, }, 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 = deleteEdgeTempTable }) } } }, 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 = "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 = new WMultiPartIdentifier(new Identifier { Value = node.EdgeColumn.MultiPartIdentifier.Identifiers.Last().Value + "DeleteCol" }) }, FunctionName = new Identifier {Value = "WRITE"}, Parameters = new WScalarExpression[] { new WColumnReferenceExpression { MultiPartIdentifier = new WMultiPartIdentifier( new Identifier {Value = deleteEdgeTableName}, 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.Subtract } }, 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.Subtract } } }; 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 = deleteEdgeTableName }, new Identifier { Value = "source" } ) }, ComparisonType = BooleanComparisonType.Equals } }; if (compare == 0) { res.Add(new WDeclareTableVariable { VariableName = new Identifier { Value = "@" + deleteEdgeTempTable}, 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>() } }); var tableVar = new WSpecialNamedTableReference { TableObjectName = new WSchemaObjectName(new Identifier { Value = "@" + deleteEdgeTempTable }) }; res.Add(deleteCommonTableExpr); 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 = deleteEdgeTableName + "_1"}, new Identifier {Value = "source"} }), }, new WColumnReferenceExpression { MultiPartIdentifier = new WSchemaObjectName(new Identifier[] { new Identifier {Value = deleteEdgeTableName + "_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 = deleteEdgeTableName + "_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 = deleteEdgeTableName + "_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 = deleteEdgeTableName + "_1"}, QueryExpr = edgeSelectQuery }, SecondTableRef = new WQueryDerivedTable { Alias = new Identifier {Value = deleteEdgeTableName + "_2"}, QueryExpr = reversedEdgeSelectQuery }, JoinCondition = new WBooleanComparisonExpression() { ComparisonType = BooleanComparisonType.Equals, FirstExpr = new WColumnReferenceExpression { MultiPartIdentifier = new WSchemaObjectName(new Identifier[] { new Identifier {Value = deleteEdgeTableName + "_1"}, new Identifier {Value = "source"} }), }, SecondExpr = new WColumnReferenceExpression { MultiPartIdentifier = new WSchemaObjectName(new Identifier[] { new Identifier {Value = deleteEdgeTableName + "_2"}, new Identifier {Value = "source"} }), } } }, } } } } }); res.Add(new WUpdateSpecification { Target = new WNamedTableReference { TableObjectName = sourceNameTable.TableObjectName }, SetClauses = setClauses, FromClause = new WFromClause { TableReferences = new WTableReference[] { new WQueryDerivedTable { Alias = new Identifier {Value = deleteEdgeTableName}, 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}", deleteEdgeTempTable) }); } else { // Declare table variables res.Add(new WDeclareTableVariable { VariableName = new Identifier { Value = "@" + deleteEdgeTempTable + "_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 = "@" + deleteEdgeTempTable + "_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 = "@" + deleteEdgeTempTable + "_1" }) }; var reversedTableVar = new WSpecialNamedTableReference { TableObjectName = new WSchemaObjectName(new Identifier { Value = "@" + deleteEdgeTempTable + "_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 = new WNamedTableReference { TableObjectName = sourceNameTable.TableObjectName }, SetClauses = new List<WSetClause> {setClauses[0], setClauses[2]}, FromClause = new WFromClause { TableReferences = new WTableReference[] { new WQueryDerivedTable { QueryExpr = new WSelectQueryBlock { SelectElements = new List<WSelectElement>() { new WSelectStarExpression() }, FromClause = new WFromClause { TableReferences = new List<WTableReference> {tableVar} } }, Alias = new Identifier {Value = deleteEdgeTableName}, }, } }, 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 = deleteEdgeTableName}, 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(deleteCommonTableExpr); res.Add(insertStat[0]); res.Add(updateStat[0]); res.Add(new WSqlUnknownStatement { Statement = String.Format("DELETE FROM @{0}_1", deleteEdgeTempTable) }); // With expression res.Add(deleteCommonTableExpr); res.Add(insertStat[1]); res.Add(updateStat[1]); res.Add(new WSqlUnknownStatement { Statement = String.Format("DELETE FROM @{0}_2", deleteEdgeTempTable) }); } else { // With expression res.Add(deleteCommonTableExpr); res.Add(insertStat[1]); res.Add(updateStat[1]); res.Add(new WSqlUnknownStatement { Statement = String.Format("DELETE FROM @{0}_2", deleteEdgeTempTable) }); // With expression res.Add(deleteCommonTableExpr); res.Add(insertStat[0]); res.Add(updateStat[0]); res.Add(new WSqlUnknownStatement { Statement = String.Format("DELETE FROM @{0}_1", deleteEdgeTempTable) }); } } }
public virtual void Visit(WUpdateSpecification node) { node.AcceptChildren(this); }