public ParseAlterTableDropNodeTableColumnStatement ( string queryStr, IList |
||
queryStr | string | The CREATE TABLE statement creating a ndoe table |
errors | IList |
Parsing errors |
return |
/// <summary> /// Drop properties or edges of a node table in the graph database. /// </summary> /// <param name="sqlStr">A ALTER TABLE ADD statement with annotations.</param> /// <param name="externalTransaction">An existing SqlTransaction instance under which the create node table will occur.</param> /// <returns>True, if the statement is successfully executed.</returns> public bool DropNodeTableColumn(string sqlStr, SqlTransaction externalTransaction = null) { var parser = new GraphViewParser(); IList<ParseError> errors; var script = parser.ParseAlterTableDropNodeTableColumnStatement(sqlStr, out errors) as WSqlScript; if (errors.Count > 0) throw new SyntaxErrorException(errors); if (script == null || script.Batches.Count == 0) throw new SyntaxErrorException("Invalid ALTER TABLE DROP PROPERTY/EDGE statement."); var statement = script.Batches[0].Statements[0] as WAlterTableDropTableElementStatement; SqlTransaction tx; tx = externalTransaction ?? Conn.BeginTransaction(); try { // <columnName, <columnId, role, hasRevEdge, isRevEdge, <refTableSchema, refTableName>>> var columnDict = new Dictionary<string, Tuple<long, WNodeTableColumnRole, bool, bool, Tuple<string, string>>>( StringComparer.OrdinalIgnoreCase); // <tableName, columnDict> var tableColDict = new Dictionary <string, Dictionary<string, Tuple<long, WNodeTableColumnRole, bool, bool, Tuple<string, string>>>>( StringComparer.OrdinalIgnoreCase); // <tableName, <tableId, tableSchema, userId>> var tableInfoDict = new Dictionary<string, Tuple<long, string, string>>(StringComparer.OrdinalIgnoreCase); string tableSchema = null, tableName = null; using (var command = new SqlCommand(null, Conn)) { long tableId = -1; string userId = null; command.Transaction = tx; command.CommandText = string.Format(@" select nt.TableId, nt.TableSchema, nt.TableName, ntc.ColumnName, ntc.ColumnId, ntc.ColumnRole, ntc.HasReversedEdge, ntc.IsReversedEdge, ntc.RefTableSchema, ntc.Reference from {0} as nt join {1} as ntc on ntc.TableId = nt.TableId where ntc.ColumnRole = @role0 or ntc.ColumnRole = @role1 or ntc.ColumnRole = @role2 order by ntc.TableId", MetadataTables[0], MetadataTables[1]); command.Parameters.AddWithValue("@role0", WNodeTableColumnRole.Property); command.Parameters.AddWithValue("@role1", WNodeTableColumnRole.Edge); command.Parameters.AddWithValue("@role2", WNodeTableColumnRole.NodeId); // Metadata retrieval using (var reader = command.ExecuteReader()) { while (reader.Read()) { var curTableId = (long)reader["TableId"]; if (tableId == -1) { tableId = curTableId; tableSchema = reader["TableSchema"].ToString(); tableName = reader["TableName"].ToString(); } else if (curTableId != tableId) { tableColDict[tableName] = columnDict; tableInfoDict[tableName] = new Tuple<long, string, string>(tableId, tableSchema, userId); tableSchema = reader["TableSchema"].ToString(); tableName = reader["TableName"].ToString(); userId = null; columnDict = new Dictionary <string, Tuple<long, WNodeTableColumnRole, bool, bool, Tuple<string, string>>>(); tableId = curTableId; } var role = (WNodeTableColumnRole)reader["ColumnRole"]; if (role == WNodeTableColumnRole.NodeId) userId = reader["ColumnName"].ToString(); else if (role == WNodeTableColumnRole.Property || role == WNodeTableColumnRole.Edge) { var columnName = reader["ColumnName"].ToString(); var colId = (long)reader["ColumnId"]; var hasRevEdge = bool.Parse(reader["HasReversedEdge"].ToString()); var isRevEdge = bool.Parse(reader["IsReversedEdge"].ToString()); var refTableSchema = role == WNodeTableColumnRole.Property ? "" : "dbo"; var refTableName = reader.IsDBNull(9) ? "" : reader["Reference"].ToString(); var colInfo = new Tuple<long, WNodeTableColumnRole, bool, bool, Tuple<string, string>>(colId, role, hasRevEdge, isRevEdge, new Tuple<string, string>(refTableSchema, refTableName)); columnDict[columnName] = colInfo; } } tableColDict[tableName] = columnDict; tableInfoDict[tableName] = new Tuple<long, string, string>(tableId, tableSchema, userId); } tableSchema = statement.SchemaObjectName.SchemaIdentifier != null ? statement.SchemaObjectName.SchemaIdentifier.Value : "dbo"; tableName = statement.SchemaObjectName.BaseIdentifier.Value; if (!tableInfoDict.ContainsKey(tableName)) throw new GraphViewException("Table " + tableSchema + "." + tableName + " doesn't exist."); // Get GlobalNodeView Table Id long globalNodeViewTableId; command.CommandText = string.Format( @"SELECT TableId FROM _NodeTableCollection as nt WHERE nt.TableRole = {0} and nt.TableName = 'GlobalNodeView'", (int)WNodeTableRole.NodeView); using (var reader = command.ExecuteReader()) { globalNodeViewTableId = reader.Read() ? (long)reader["TableId"] : -1; } // <NodeViewColumnId, <NodeViewName, ColumnName>> var viewInfoDict = new Dictionary<long, Tuple<string, string>>(); // <ColumnId, NodeViewColumnId> var viewColDict = new Dictionary<long, HashSet<long>>(); command.CommandText = string.Format( @"SELECT nt.TableName, ntc.ColumnName, nvc.NodeViewColumnId, nvc.ColumnId FROM _NodeTableCollection as nt JOIN _NodeTableColumnCollection as ntc ON nt.TableId = ntc.TableId JOIN _NodeViewColumnCollection as nvc ON ntc.ColumnId = nvc.NodeViewColumnId WHERE nt.TableId != {0}", globalNodeViewTableId); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var nodeViewName = reader["TableName"].ToString(); var columnName = reader["ColumnName"].ToString(); var nodeViewColumnId = (long)reader["NodeViewColumnId"]; var columnId = (long)reader["ColumnId"]; if (!viewColDict.ContainsKey(columnId)) viewColDict[columnId] = new HashSet<long>(); viewColDict[columnId].Add(nodeViewColumnId); if (!viewInfoDict.ContainsKey(nodeViewColumnId)) viewInfoDict.Add(nodeViewColumnId, new Tuple<string, string>(nodeViewName, columnName)); } } const string dropColumnScript = @" SET @tablename = '{0}' SET @colname = '{1}' SELECT @constraint_name = name FROM sys.default_constraints WHERE parent_object_id = object_id(@tablename) AND parent_column_id = ( select column_id from sys.columns where object_id = object_id(@tablename) and name = @colname ) SET @sql = N'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constraint_name EXEC sp_executesql @sql SET @sql = N'alter table ' + @tablename + ' DROP COLUMN ' + @colname EXEC sp_executesql @sql "; var dropColumnStr = ""; // <tableName, columnDict> var alteredTableColumn = new Dictionary <string, Dictionary<string, Tuple<long, WNodeTableColumnRole, bool, bool, Tuple<string, string>>>>( StringComparer.OrdinalIgnoreCase); alteredTableColumn.Add(tableName, new Dictionary<string, Tuple<long, WNodeTableColumnRole, bool, bool, Tuple<string, string>>>()); columnDict = tableColDict[tableName]; foreach (var element in statement.AlterTableDropTableElements) { var columnName = element.Name.Value; if (!columnDict.ContainsKey(columnName)) throw new GraphViewException( string.Format("Table {0} doesn't have a column named \"{1}\".", tableSchema + "." + tableName, columnName)); var columnInfo = columnDict[columnName]; var colId = columnInfo.Item1; HashSet<long> refViewIds; if (viewColDict.TryGetValue(colId, out refViewIds)) { var msg = ""; var first = true; foreach (var refViewId in refViewIds) { if (first) first = false; else msg += ", "; var viewInfo = viewInfoDict[refViewId]; msg += viewInfo.Item1 + "." + viewInfo.Item2; } throw new GraphViewException( string.Format("\"{0}\" is referenced by Nodeview/Edgeview {1}.", columnName, msg)); } dropColumnStr += string.Format(dropColumnScript, tableName, columnName); var alteredColumn = alteredTableColumn[tableName]; alteredColumn.Add(columnName, columnInfo); // role == edge if (columnInfo.Item2 == WNodeTableColumnRole.Edge) { // Drop DeleteCol and OutDegree dropColumnStr += string.Format(dropColumnScript, tableName, columnName + "DeleteCol"); dropColumnStr += string.Format(dropColumnScript, tableName, columnName + "OutDegree"); // hasReversedEdge if (columnInfo.Item3) { var refTableSchema = columnInfo.Item5.Item1; var refTableName = columnInfo.Item5.Item2; var revEdgeName = tableName + "_" + columnName + "Reversed"; // refTable still not created if (!tableColDict.ContainsKey(refTableName)) continue; // Drop revEdge, DeleteCol and OutDegree dropColumnStr += string.Format(dropColumnScript, refTableName, revEdgeName); dropColumnStr += string.Format(dropColumnScript, refTableName, revEdgeName + "DeleteCol"); dropColumnStr += string.Format(dropColumnScript, refTableName, revEdgeName + "OutDegree"); if (!alteredTableColumn.ContainsKey(refTableName)) alteredTableColumn[refTableName] = new Dictionary <string, Tuple<long, WNodeTableColumnRole, bool, bool, Tuple<string, string>>>(); var revEdgeInfo = tableColDict[refTableName][revEdgeName]; alteredTableColumn[refTableName].Add(revEdgeName, revEdgeInfo); } } } // Alter Table Drop Column const string dropColumnDeclare = @" DECLARE @tablename nvarchar(128), @colname nvarchar(128) DECLARE @constraint_name sysname, @sql nvarchar(max)"; command.CommandText = dropColumnDeclare + dropColumnStr; command.ExecuteNonQuery(); // Drop Assembly if needed if (alteredTableColumn[tableName].Any(col => col.Value.Item2 == WNodeTableColumnRole.Edge)) { var edgeColumns = GetGraphEdgeColumns(tableSchema, tableName, tx); if (edgeColumns.Count > 0) { var assemblyName = tableSchema + '_' + tableName; foreach (var edgeColumn in edgeColumns) { // !isEdgeView && !isReversedEdge // skip edgeView since they needn't to be reconstructed // skip reversed edges since they have no UDF if (!edgeColumn.Item3 && !edgeColumn.Item6) { foreach (var it in _currentTableUdf) { command.CommandText = string.Format( @"DROP {2} [{0}_{1}_{3}];", assemblyName, edgeColumn.Item1, it.Item1, it.Item2); command.ExecuteNonQuery(); } } } // skip tables which have only reversed edge columns if (edgeColumns.Count(x => !x.Item6) > 0) { command.CommandText = @"DROP ASSEMBLY [" + assemblyName + "_Assembly]"; command.ExecuteNonQuery(); } } } var deletePropertyColumnIds = ""; var deleteEdgeColumnIds = ""; var dropSamplingTableNames = ""; var pFirst = true; var eFirst = true; var sFirst = true; foreach (var tableDict in alteredTableColumn) { foreach (var column in tableDict.Value) { var columnInfo = column.Value; if (columnInfo.Item2 == WNodeTableColumnRole.Property) { if (pFirst) pFirst = false; else deletePropertyColumnIds += ", "; deletePropertyColumnIds += columnInfo.Item1; } else if (columnInfo.Item2 == WNodeTableColumnRole.Edge) { if (eFirst) eFirst = false; else deleteEdgeColumnIds += ", "; deleteEdgeColumnIds += columnInfo.Item1; var samplingTableName = tableDict.Key; var samplingTableSchema = tableInfoDict[samplingTableName].Item2; var dropTableName = samplingTableSchema + "_" + samplingTableName + "_" + column.Key + "_Sampling"; if (sFirst) sFirst = false; else dropSamplingTableNames += ", "; dropSamplingTableNames += "[" + dropTableName + "]"; } } } var deleteColumnIds = deletePropertyColumnIds + (string.IsNullOrEmpty(deletePropertyColumnIds) ? deleteEdgeColumnIds : ", " + deleteEdgeColumnIds); const string metaTableDeleteStr = @" DELETE FROM [{0}] WHERE [ColumnId] in ({1}) "; // _NodeTableColumnCollection update command.CommandText = string.Format(metaTableDeleteStr, MetadataTables[1], deleteColumnIds); command.ExecuteNonQuery(); if (!string.IsNullOrEmpty(deleteEdgeColumnIds)) { // _EdgeAverageDegree update command.CommandText = string.Format(metaTableDeleteStr, MetadataTables[3], deleteColumnIds); command.ExecuteNonQuery(); // _EdgeAttributeCollection update command.CommandText = string.Format(metaTableDeleteStr, MetadataTables[2], deleteEdgeColumnIds); command.ExecuteNonQuery(); // Drop sampling tables command.CommandText = string.Format(@"DROP TABLE {0}", dropSamplingTableNames); command.ExecuteNonQuery(); // Edge UDF Register UpgradeNodeTableFunction(tableName, tx); } } UpdateGlobalNodeView(tableSchema, tx); if (externalTransaction == null) { tx.Commit(); } return true; } catch (SqlException e) { if (externalTransaction == null) { tx.Rollback(); } throw new SqlExecutionException("An error occurred when altering the node table.\n" + e.Message, e); } }