/// <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);
}
}
