// public void DropAssemblyAndMetaUDFV110(SqlTransaction externalTransaction = null)
// {
// SqlTransaction tx;
// tx = externalTransaction ?? Conn.BeginTransaction();
// try
// {
// using (var command = Conn.CreateCommand())
// {
// command.Transaction = tx;
// //Drop assembly and UDF
// const string dropAssembly = @"
// DROP {0} {1}";
// command.CommandText = string.Join("\n", Version110MetaUdf.Select(x => string.Format(dropAssembly, x.Item1, x.Item2)));
// command.ExecuteNonQuery();
// }
// }
// catch (Exception e)
// {
// if (externalTransaction == null)
// tx.Rollback();
// throw new Exception(e.Message);
// }
// }
/// <summary>
/// Add properties or edges to 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 AddNodeTableColumn(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of ALTER TABLE ADD command
var parser = new GraphViewParser();
List<WNodeTableColumn> columns;
IList<ParseError> errors;
var script = parser.ParseAlterTableAddNodeTableColumnStatement(sqlStr, out columns, 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 ADD PROPERTY/EDGE statement.");
var statement = script.Batches[0].Statements[0] as WAlterTableAddTableElementStatement;
var tableSchema = statement.SchemaObjectName.SchemaIdentifier != null
? statement.SchemaObjectName.SchemaIdentifier.Value
: "dbo";
var tableName = statement.SchemaObjectName.BaseIdentifier.Value;
SqlTransaction tx;
tx = externalTransaction ?? Conn.BeginTransaction();
try
{
Int64 tableId;
using (var command = new SqlCommand(null, Conn))
{
// Get altered table Id
command.Transaction = tx;
command.CommandText = String.Format(@"
SELECT [TableId], [TableSchema], [TableName]
FROM [{0}]
WHERE [TableRole] = {1} AND [TableSchema] = '{2}' AND [TableName] = '{3}'",
MetadataTables[0], (int)WNodeTableRole.NodeTable, tableSchema, tableName);
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
throw new GraphViewException("Table " + tableSchema + "." + tableName + " doesn't exist.");
tableId = Convert.ToInt64(reader["TableId"], CultureInfo.CurrentCulture);
}
// Avoid naming conflicts
foreach (var column in columns)
{
command.CommandText = String.Format(@"
SELECT [ColumnId]
FROM [{0}]
WHERE [TableId] = {1} AND [ColumnName] = '{2}'",
MetadataTables[1], tableId, column.ColumnName.Value);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
throw new GraphViewException(
string.Format("Table {0} already has a column named \"{1}\".",
tableSchema + "." + tableName, column.ColumnName.Value));
}
}
// Alter table add column
command.CommandText = statement.ToString();
command.ExecuteNonQuery();
}
var edgeColumnNameToColumnId = new Dictionary<string, long>(StringComparer.OrdinalIgnoreCase);
var hasReversedEdge = new Dictionary<string, bool>(StringComparer.OrdinalIgnoreCase);
// Drop assmebly if needed
if (columns.OfType<WGraphTableEdgeColumn>().Any())
{
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
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();
}
}
}
}
// _NodeTableColumnCollection update
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [TableId], [ColumnName], [ColumnRole], [Reference], [HasReversedEdge], [EdgeUdfPrefix])
OUTPUT [Inserted].[ColumnId]
VALUES (@tableSchema, @tableName, @tableid, @columnName, @columnRole, @ref, @hasRevEdge, @udfPrefix)", MetadataTables[1]);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@tableid", tableId);
command.Parameters.Add("@columnName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@columnRole", SqlDbType.Int);
command.Parameters.Add("@ref", SqlDbType.NVarChar, 128);
command.Parameters.Add("@hasRevEdge", SqlDbType.Bit);
command.Parameters.Add("@udfPrefix", SqlDbType.NVarChar, 512);
foreach (var column in columns)
{
command.Parameters["@columnName"].Value = column.ColumnName.Value;
command.Parameters["@columnRole"].Value = (int) column.ColumnRole;
command.Parameters["@hasRevEdge"].Value = column.ColumnRole == WNodeTableColumnRole.Edge ? 1 : 0;
var edgeColumn = column as WGraphTableEdgeColumn;
if (edgeColumn != null)
{
command.Parameters["@ref"].Value =
(edgeColumn.TableReference as WNamedTableReference).ExposedName.Value;
command.Parameters["@udfPrefix"].Value =
tableSchema + "_" + tableName + "_" + column.ColumnName.Value;
}
else
command.Parameters["@ref"].Value = command.Parameters["@udfPrefix"].Value = SqlChars.Null;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
if (column.ColumnRole == WNodeTableColumnRole.Edge)
{
edgeColumnNameToColumnId[column.ColumnName.Value] = Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture);
hasReversedEdge[column.ColumnName.Value] = true;
}
}
}
// _EdgeAverageDegree update
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AverageDegree])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @AverageDegree)", MetadataTables[3]);
command.Parameters.Add("@AverageDegree", SqlDbType.Int);
command.Parameters["@AverageDegree"].Value = 5;
command.Parameters.Add("@columnid", SqlDbType.Int);
foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
{
command.Parameters["@columnid"].Value = edgeColumnNameToColumnId[column.ColumnName.Value];
command.Parameters["@columnName"].Value = column.ColumnName.Value;
command.ExecuteNonQuery();
}
}
// _EdgeAttributeCollection update
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
// Set Max(attributeEdgeId) + 1 as createOrder
command.CommandText = String.Format(@"
SELECT MAX([AttributeEdgeId]) AS maxAttrEdgeId
FROM [{0}]
WHERE [TableSchema] = '{1}' AND [TableName] = '{2}'",
MetadataTables[2], tableSchema, tableName);
var createOrder = 1;
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var startOrder = reader["maxAttrEdgeId"].ToString();
createOrder = String.IsNullOrEmpty(startOrder) ? 1 : Convert.ToInt32(startOrder, CultureInfo.CurrentCulture) + 1;
}
}
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AttributeName], [AttributeType], [AttributeEdgeId])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @attrName, @attrType, @attrId)", MetadataTables[2]);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.Add("@columnName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrType", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrId", SqlDbType.Int);
command.Parameters.Add("@columnid", SqlDbType.Int);
foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
{
command.Parameters["@columnName"].Value = column.ColumnName.Value;
foreach (var attr in column.Attributes)
{
command.Parameters["@attrName"].Value = attr.Item1.Value;
command.Parameters["@attrType"].Value = attr.Item2.ToString();
command.Parameters["@attrId"].Value = (createOrder++).ToString();
command.Parameters["@columnid"].Value = edgeColumnNameToColumnId[column.ColumnName.Value];
command.ExecuteNonQuery();
}
}
}
//var edgeDict =
// columns.OfType<WGraphTableEdgeColumn>()
// .Select(
// col =>
// new Tuple<string, int, List<Tuple<string, string>>>(col.ColumnName.Value,
// (int) edgeColumnNameToColumnId[col.ColumnName.Value],
// col.Attributes.Select(
// x =>
// new Tuple<string, string>(x.Item1.Value,
// x.Item2.ToString().ToLower(CultureInfo.CurrentCulture)))
// .ToList())).ToList();
// Edge UDF Register
if (columns.OfType<WGraphTableEdgeColumn>().Any())
{
UpgradeNodeTableFunction(tableName, tx);
//var assemblyName = tableSchema + '_' + tableName;
//GraphViewDefinedFunctionRegister register = new NodeTableRegister(assemblyName, tableName, edgeDict, userId);
//register.Register(Conn, tx);
}
// Edge sampling table created
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
{
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
SELECT * INTO [{0}_{1}_{2}_Sampling] FROM (
SELECT ([GlobalNodeID]+0) as [Src], [Edge].*
FROM [{0}].[{1}] WITH (NOLOCK)
CROSS APPLY {0}_{1}_{2}_Decoder([{2}],[{2}DeleteCol],0) AS Edge
WHERE 1=0) as EdgeSample",
tableSchema, tableName, column.ColumnName.Value);
command.ExecuteNonQuery();
}
}
// <revEdgeName, edgeUdfPrefix, <srcTableSchema, srcTableName, srcTableId>, <attrName, attrType>>
var revEdgeMetaDataList =
new List<Tuple<string, string, Tuple<string, string, long>, List<Tuple<string, string>>>>();
// <refTableSchema, refTableName>
var refTableTuple = new Tuple<string, string>(tableSchema, tableName);
foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
{
Tuple<string, string, long> srcTableTuple;
var edgeName = column.ColumnName.Value;
var edgeUdfPrefix = tableSchema + "_" + tableName + "_" + edgeName;
var revEdgeName = tableName + "_" + edgeName + "Reversed";
var srcTableName = (column.TableReference as WNamedTableReference).ExposedName.Value;
var attrList =
column.Attributes.Select(
attr => new Tuple<string, string>(attr.Item1.Value, attr.Item2.ToString())).ToList();
if (srcTableName.Equals(tableName, StringComparison.OrdinalIgnoreCase))
{
srcTableTuple = new Tuple<string, string, long>(tableSchema, tableName, tableId);
revEdgeMetaDataList.Add(new Tuple<string, string, Tuple<string, string, long>, List<Tuple<string, string>>>(
revEdgeName, edgeUdfPrefix, srcTableTuple, attrList));
}
else
{
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = String.Format(@"
SELECT [TableId], [TableSchema], [TableName]
FROM [{0}]
WHERE [TableRole] = {1} AND [TableName] = '{2}'",
MetadataTables[0], (int)WNodeTableRole.NodeTable, srcTableName);
using (var reader = command.ExecuteReader())
{
if (!reader.Read()) continue;
srcTableTuple = new Tuple<string, string, long>(
reader["TableSchema"].ToString(),
srcTableName,
Convert.ToInt64(reader["TableId"].ToString(), CultureInfo.CurrentCulture));
revEdgeMetaDataList.Add(new Tuple<string, string, Tuple<string, string, long>, List<Tuple<string, string>>>(
revEdgeName, edgeUdfPrefix, srcTableTuple, attrList));
}
}
}
}
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
foreach (var revEdgeMetaData in revEdgeMetaDataList)
{
var edgeName = revEdgeMetaData.Item1;
var edgeUdfPrefix = revEdgeMetaData.Item2;
var srcTableSchema = revEdgeMetaData.Item3.Item1;
var srcTableName = revEdgeMetaData.Item3.Item2;
var srcTableId = revEdgeMetaData.Item3.Item3;
var attrList = revEdgeMetaData.Item4;
command.Parameters.Clear();
// Alter table add reversed edge column
command.CommandText = String.Format(@"
ALTER TABLE [{0}].[{1}]
ADD [{2}] VARBINARY(MAX) NOT NULL DEFAULT 0x,
[{3}] VARBINARY(MAX) NOT NULL DEFAULT 0x,
[{4}] INT NOT NULL DEFAULT 0
",
srcTableSchema, srcTableName,
edgeName,
edgeName + "DeleteCol",
edgeName + "OutDegree");
command.ExecuteNonQuery();
// _NodeTableColumnCollection update
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [TableId], [ColumnName], [ColumnRole], [Reference], [IsReversedEdge], [EdgeUdfPrefix])
OUTPUT [Inserted].[ColumnId]
VALUES (@tableSchema, @tableName, @tableid, @columnName, @columnRole, @ref, @isRevEdge, @udfPrefix)", MetadataTables[1]);
command.Parameters.AddWithValue("@tableSchema", srcTableSchema);
command.Parameters.AddWithValue("@tableName", srcTableName);
command.Parameters.AddWithValue("@tableid", srcTableId);
command.Parameters.AddWithValue("@columnName", edgeName);
command.Parameters.AddWithValue("@columnRole", (int)WNodeTableColumnRole.Edge);
command.Parameters.AddWithValue("@ref", tableName);
command.Parameters.AddWithValue("@refTableSchema", tableSchema);
command.Parameters.AddWithValue("@isRevEdge", 1);
command.Parameters.AddWithValue("@udfPrefix", edgeUdfPrefix);
long columnId = 0;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
return false;
else
columnId = Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture);
}
// _EdgeAverageDegree update
command.Parameters.Add("@AverageDegree", SqlDbType.Int);
command.Parameters["@AverageDegree"].Value = 5;
command.Parameters.Add("@columnid", SqlDbType.Int);
command.Parameters["@columnid"].Value = columnId;
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AverageDegree])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @AverageDegree)", MetadataTables[3]);
command.ExecuteNonQuery();
// Create reversed edge sampling table
command.CommandText = String.Format(@"
SELECT * INTO [{0}_{1}_{2}_Sampling] FROM (
SELECT ([GlobalNodeID]+0) as [Src], [Edge].*
FROM [{0}].[{1}] WITH (NOLOCK)
CROSS APPLY {3}_Decoder([{2}],[{2}DeleteCol],0) AS Edge
WHERE 1=0) as EdgeSample
",
srcTableSchema, srcTableName, edgeName, edgeUdfPrefix);
command.ExecuteNonQuery();
// Set Max(attributeEdgeId) + 1 as createOrder
command.CommandText = String.Format(@"
SELECT MAX([AttributeEdgeId]) AS maxAttrEdgeId
FROM [{0}]
WHERE [TableSchema] = '{1}' AND [TableName] = '{2}'",
MetadataTables[2], srcTableSchema, srcTableName);
var createOrder = 1;
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var startOrder = reader["maxAttrEdgeId"].ToString();
createOrder = String.IsNullOrEmpty(startOrder) ? 1 : Convert.ToInt32(startOrder, CultureInfo.CurrentCulture) + 1;
}
}
// _EdgeAttributeCollection update
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AttributeName], [AttributeType], [AttributeEdgeId])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @attrName, @attrType, @attrId)", MetadataTables[2]);
command.Parameters.Add("@attrName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrType", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrId", SqlDbType.Int);
foreach (var attr in attrList)
{
command.Parameters["@attrName"].Value = attr.Item1;
command.Parameters["@attrType"].Value = attr.Item2;
command.Parameters["@attrId"].Value = (createOrder++).ToString();
command.ExecuteNonQuery();
}
}
}
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);
}
}