|
public ParseAlterTableAddNodeTableColumnStatement ( string queryStr, List |
||
| queryStr | string | The CREATE TABLE statement creating a ndoe table |
| nodeTableColumns | List |
A list of columns of the node table |
| errors | IList |
Parsing errors |
| return | ||
public WSqlFragment ParseAlterTableAddNodeTableColumnStatement(
string queryStr,
out List<WNodeTableColumn> nodeTableColumns,
out IList<ParseError> errors)
{
// Gets token stream
var tsqlParser = new TSql110Parser(true);
var sr = new StringReader(queryStr);
var tokens = new List<TSqlParserToken>(tsqlParser.GetTokenStream(sr, out errors));
if (errors.Count > 0)
{
nodeTableColumns = null;
return null;
}
// Retrieves node table columns
var currentToken = 0;
var farestError = 0;
nodeTableColumns = new List<WNodeTableColumn>();
while (currentToken < tokens.Count)
{
WNodeTableColumn column = null;
if (ParseNodeTableColumn(tokens, ref currentToken, ref column, ref farestError))
nodeTableColumns.Add(column);
else
currentToken++;
}
// Replaces column annotations with whitespace
foreach (var t in nodeTableColumns)
{
tokens[t.FirstTokenIndex].TokenType = TSqlTokenType.WhiteSpace;
tokens[t.FirstTokenIndex].Text = "";
}
// Parses the remaining statement using the T-SQL parser
//IList<ParseError> errors;
var parser = new WSqlParser();
var fragment = parser.Parse(tokens, out errors) as WSqlScript;
if (errors.Count > 0)
return null;
var deltaColumnDefList = new List<WColumnDefinition>();
var stmt = fragment.Batches[0].Statements[0] as WAlterTableAddTableElementStatement;
if (stmt == null || stmt.Definition == null || stmt.Definition.ColumnDefinitions == null)
{
return null;
}
else if (stmt.Definition.ColumnDefinitions.Count != nodeTableColumns.Count)
{
var error = tokens[stmt.FirstTokenIndex];
errors.Add(new ParseError(0, error.Offset, error.Line, error.Column,
"Metadata should be specified for each column when altering a node table"));
}
var graphColIndex = 0;
var rawColumnDef = stmt.Definition.ColumnDefinitions;
for (var i = 0; i < rawColumnDef.Count && graphColIndex < nodeTableColumns.Count; ++i, ++graphColIndex)
{
var nextGraphColumn = nodeTableColumns[graphColIndex];
// Skips columns without annotations
while (i < rawColumnDef.Count && rawColumnDef[i].LastTokenIndex < nextGraphColumn.FirstTokenIndex)
{
++i;
}
switch (nextGraphColumn.ColumnRole)
{
case WNodeTableColumnRole.Edge:
// For an adjacency-list column, its data type is always varbinary(max)
var def = rawColumnDef[i];
def.DataType = new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier { Value = "varbinary" }),
Parameters = new List<Literal> { new MaxLiteral { Value = "max" } }
};
def.Constraints.Add(new WNullableConstraintDefinition { Nullable = false });
def.DefaultConstraint = new WDefaultConstraintDefinition
{
Expression = new WValueExpression
{
Value = "0x"
}
};
// For each adjacency-list column, adds a "delta" column to
// facilitate deleting edges.
deltaColumnDefList.Add(new WColumnDefinition
{
ColumnIdentifier = new Identifier { Value = def.ColumnIdentifier.Value + "DeleteCol" },
ComputedColumnExpression = def.ComputedColumnExpression,
Constraints = def.Constraints,
DataType = def.DataType,
DefaultConstraint = def.DefaultConstraint,
});
// For each adjacency-list column, adds an integer column to record the list's outgoing degree
deltaColumnDefList.Add(new WColumnDefinition
{
ColumnIdentifier = new Identifier {Value = def.ColumnIdentifier.Value + "OutDegree"},
Constraints = def.Constraints,
DataType = new WParameterizedDataTypeReference
{
Name = new WSchemaObjectName(new Identifier {Value = "int"}),
},
DefaultConstraint = new WDefaultConstraintDefinition
{
Expression = new WValueExpression
{
Value = "0"
}
}
});
break;
case WNodeTableColumnRole.Property:
break;
default:
var error = tokens[nextGraphColumn.FirstTokenIndex];
errors.Add(new ParseError(0, error.Offset, error.Line, error.Column,
"Only edge or property can be added to the node table"));
break;
}
}
foreach (var definition in deltaColumnDefList)
stmt.Definition.ColumnDefinitions.Add(definition);
return fragment;
}
// 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);
}
}
