/// <summary>
/// Returns the translated T-SQL script. For testing only.
/// </summary>
/// <returns>The translated T-SQL script</returns>
internal string GetTsqlQuery()
{
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
// Translation and Check CheckInvisibleColumn
using (SqlTransaction tx = Connection.BeginTransaction())
{
var visitor = new TranslateMatchClauseVisitor(tx);
visitor.Invoke(script);
return script.ToString();
}
}
/// <summary>
/// Drops procedure and related metadata.
/// </summary>
/// <param name="sqlStr"> Name of procedure to be dropped.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the drop procedure will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool DropProcedure(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of DROP TABLE command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WDropProcedureStatement;
if (statement == null)
return false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
using (var command = new SqlCommand(null, Conn, tran))
{
// delete metadata
foreach (var delObject in statement.Objects)
{
var proSchema = delObject.SchemaIdentifier == null ? "dbo" : delObject.SchemaIdentifier.Value;
var procName = delObject.BaseIdentifier.Value;
command.Parameters.Clear();
command.CommandText = string.Format(
@"DELETE FROM {0} WHERE ProcSchema = @procSchema AND ProcName = @procName",
MetadataTables[4]);
command.Parameters.AddWithValue("@procSchema", proSchema);
command.Parameters.AddWithValue("@procName", procName);
command.ExecuteNonQuery();
}
// drop procedure
command.CommandText = sqlStr;
command.ExecuteNonQuery();
if (externalTransaction == null)
{
tran.Commit();
}
return true;
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when dropping the procedure.", e);
}
}
/// <summary>
/// Create procedure and related metadata.
/// </summary>
/// <param name="sqlStr"> A create procedure statement with metadata.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the create procedure will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool CreateProcedure(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of CREATE Procedure command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
// Translation
var modVisitor = new TranslateDataModificationVisitor(Conn);
modVisitor.Invoke(script);
var matchVisitor = new TranslateMatchClauseVisitor(Conn);
matchVisitor.Invoke(script);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WCreateProcedureStatement;
if (statement == null)
return false;
var procName = statement.ProcedureReference.Name;
if (procName.SchemaIdentifier == null)
procName.Identifiers.Insert(0, new Identifier {Value = "dbo"});
bool exists = false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
using (var cmd = Conn.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandText = script.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = string.Format(
@"SELECT ProcID FROM {0} WHERE ProcName = @procName AND ProcSchema = @procSchema",
MetadataTables[4]);
cmd.Parameters.AddWithValue("@procName", procName.BaseIdentifier.Value);
cmd.Parameters.AddWithValue("@procSchema", procName.SchemaIdentifier.Value);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
exists = true;
}
if (!exists)
{
cmd.CommandText = string.Format(@"
INSERT INTO [{0}]([ProcSchema], [ProcName]) VALUES (@schema, @name)", MetadataTables[4]);
cmd.Parameters.AddWithValue("@schema", procName.SchemaIdentifier.Value);
cmd.Parameters.AddWithValue("@name", procName.BaseIdentifier.Value);
cmd.ExecuteNonQuery();
}
if (externalTransaction == null)
{
tran.Commit();
}
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when creating the procedure.", e);
}
return true;
}
/// <summary>
/// Drops node table and related metadata.
/// </summary>
/// <param name="sqlStr"> Name of table to be dropped.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the drop node table will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool DropNodeTable(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of DROP TABLE command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WDropTableStatement;
if (statement == null)
return false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
// delete metadata
using (var command = new SqlCommand(null, Conn, tran))
{
foreach (var obj in statement.Objects)
{
var tableName = obj.BaseIdentifier.Value;
var tableSchema = obj.SchemaIdentifier != null
? obj.SchemaIdentifier.Value
: "dbo";
var edgeColumns = GetGraphEdgeColumns(tableSchema, tableName, tran);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
foreach (var table in MetadataTables)
{
if (table == MetadataTables[4] || table == MetadataTables[5] || table == MetadataTables[6] ||
table == MetadataTables[7])
continue;
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
DELETE FROM [{0}]
WHERE [TableName] = @tableName AND [TableSchema] = @tableSchema", table);
command.ExecuteNonQuery();
}
foreach (var edgeColumn in edgeColumns)
{
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
DROP TABLE [{0}_{1}_{2}_Sampling]",
tableSchema, tableName, edgeColumn.Item1);
command.ExecuteNonQuery();
}
var assemblyName = tableSchema + '_' + tableName;
foreach (var edgeColumn in edgeColumns)
{
if (edgeColumn.Item2)
{
command.CommandText = string.Format(
@"DROP FUNCTION [{0}_{1}_Decoder];
DROP FUNCTION [{0}_{1}_Recycle];
DROP FUNCTION [{0}_{1}_PathDecoder];
DROP FUNCTION [{0}_{1}_bfs];
DROP AGGREGATE [{0}_{1}_Encoder];",
assemblyName,
edgeColumn.Item1);
}
else
{
command.CommandText = string.Format(
@"DROP FUNCTION [{0}_{1}_Decoder];
DROP FUNCTION [{0}_{1}_Recycle];
DROP AGGREGATE [{0}_{1}_Encoder];",
assemblyName,
edgeColumn.Item1);
}
command.ExecuteNonQuery();
}
if (edgeColumns.Count == 0)
continue;
command.CommandText = @"DROP ASSEMBLY [" + assemblyName + "_Assembly]";
command.ExecuteNonQuery();
}
// drop node table
command.CommandText = sqlStr;
command.ExecuteNonQuery();
if (externalTransaction == null)
{
tran.Commit();
}
return true;
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when dropping the node table.", e);
}
}
/// <summary>
/// Creates node table and inserts related metadata.
/// </summary>
/// <param name="sqlStr">A CREATE TABLE statement with metadata.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the create node table will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool CreateNodeTable(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of CREATE TABLE command
var parser = new GraphViewParser();
List<WNodeTableColumn> columns;
IList<ParseError> errors;
var script = parser.ParseCreateNodeTableStatement(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 CREATE TABLE statement.");
}
var statement = script.Batches[0].Statements[0] as WCreateTableStatement;
var tableSchema = statement.SchemaObjectName.SchemaIdentifier != null
? statement.SchemaObjectName.SchemaIdentifier.Value
: "dbo";
var tableName = statement.SchemaObjectName.BaseIdentifier.Value;
SqlTransaction tx;
if (externalTransaction == null)
{
tx = Conn.BeginTransaction();
}
else
{
tx = externalTransaction;
}
// Persists the node table's meta-data
try
{
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = string.Format(@"
INSERT INTO [{0}] ([TableSchema], [TableName])
OUTPUT [Inserted].[TableId]
VALUES (@tableSchema, @tableName)", MetadataTables[0]);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
// get generated TableId
WValueExpression tableIdentitySeed;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
var tableId = Convert.ToInt64(reader["TableId"], CultureInfo.CurrentCulture) << 48;
tableIdentitySeed = new WValueExpression(tableId.ToString(CultureInfo.InvariantCulture), false);
}
// create graph table
var wColumnDefinition = statement.Definition.ColumnDefinitions
.FirstOrDefault(x => x.ColumnIdentifier.Value == "GlobalNodeId");
if (wColumnDefinition != null)
wColumnDefinition.IdentityOptions.IdentitySeed = tableIdentitySeed;
command.CommandText = statement.ToString();
command.ExecuteNonQuery();
}
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
// insert graph column
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnRole], [Reference])
VALUES (@tableSchema, @tableName, @columnName, @columnRole, @ref)", MetadataTables[1]);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.Add("@columnName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@columnRole", SqlDbType.Int);
command.Parameters.Add("@ref", SqlDbType.NVarChar, 128);
//command.Parameters["@columnName"].Value = "NodeId";
//command.Parameters["@columnRole"].Value = (int) WGraphTableColumnRole.NodeId;
//command.Parameters["@ref"].Value = SqlChars.Null;
//command.ExecuteNonQuery();
foreach (var column in columns)
{
command.Parameters["@columnName"].Value = column.ColumnName.Value;
command.Parameters["@columnRole"].Value = (int) column.ColumnRole;
var edgeColumn = column as WGraphTableEdgeColumn;
if (edgeColumn != null)
{
command.Parameters["@ref"].Value =
(edgeColumn.TableReference as WNamedTableReference).ExposedName.Value;
}
else
{
command.Parameters["@ref"].Value = SqlChars.Null;
}
command.ExecuteNonQuery();
}
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [AverageDegree])
VALUES (@tableSchema, @tableName, @columnName, @AverageDegree)", MetadataTables[3]);
command.Parameters.Add("@AverageDegree", SqlDbType.Int);
command.Parameters["@AverageDegree"].Value = 5;
foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
{
command.Parameters["@columnName"].Value = column.ColumnName.Value;
command.ExecuteNonQuery();
}
}
// insert graph edge attributes
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [AttributeName], [AttributeType], [AttributeEdgeId])
VALUES (@tableSchema, @tableName, @columnName, @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);
var createOrder = 1;
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.ExecuteNonQuery();
}
}
}
// create column edge sampling table
// using (var command = new SqlCommand(null, Conn))
// {
// command.Transaction = tx;
// foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
// {
// command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
// CREATE TABLE [{0}_{1}_{2}_Sampling] (
// [src] [bigint],
// [dst] [bigint]
// )", tableSchema, tableName, column.ColumnName.Value);
// command.ExecuteNonQuery();
// }
// }
// process edge's Decoder function
//var edgeDict = columns.OfType<WGraphTableEdgeColumn>()
// .ToDictionary(col => col.ColumnName.Value,
// col =>
// col.Attributes.Select(
// x =>
// new Tuple<string, string>(x.Item1.Value,
// x.Item2.ToString().ToLower(CultureInfo.CurrentCulture)))
// .ToList());
var edgeDict =
columns.OfType<WGraphTableEdgeColumn>()
.Select(
col =>
new Tuple<string, bool, List<Tuple<string, string>>>(col.ColumnName.Value,
String.Equals(tableName,
(col.TableReference as WNamedTableReference).ExposedName.Value,
StringComparison.CurrentCultureIgnoreCase),
col.Attributes.Select(
x =>
new Tuple<string, string>(x.Item1.Value,
x.Item2.ToString().ToLower(CultureInfo.CurrentCulture)))
.ToList())).ToList();
if (edgeDict.Count > 0)
{
var assemblyName = tableSchema + '_' + tableName;
GraphViewDefinedFunctionGenerator.NodeTableRegister(assemblyName, tableName, edgeDict, Conn, tx);
}
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]) AS Edge
WHERE 1=0) as EdgeSample",
tableSchema, tableName, column.ColumnName.Value);
command.ExecuteNonQuery();
}
}
if (externalTransaction == null)
{
tx.Commit();
}
return true;
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tx.Rollback();
}
throw new SqlExecutionException("An error occurred when creating the node table.", e);
}
}
/// <summary>
/// Creates Edge View using CREATE EDGE VIEW statement
/// </summary>
/// <param name="query"></param>
public void CreateEdgeView(string query)
{
IList<ParseError> errors;
var parser = new GraphViewParser();
var script = parser.ParseCreateNodeEdgeViewStatement(query, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null || script.Batches.Count == 0)
{
throw new SyntaxErrorException("Invalid CREATE VIEW statement.");
}
var statement = script.Batches[0].Statements[0] as WCreateViewStatement;
if (statement == null)
throw new SyntaxErrorException("Not a CREATE VIEW statement");
var edgeViewObjectName = statement.SchemaObjectName;
string schema = edgeViewObjectName.DatabaseIdentifier == null
? "dbo"
: edgeViewObjectName.DatabaseIdentifier.Value;
if (edgeViewObjectName.SchemaIdentifier == null)
throw new SyntaxErrorException(
"Source node type should be specified. Format: <Node name>.<Edgeview Name>");
string nodeName = edgeViewObjectName.SchemaIdentifier.Value;
string edgeViewName = edgeViewObjectName.BaseIdentifier.Value;
var visitor = new EdgeViewSelectStatementVisitor();
List<Tuple<string, string>> edges;
List<string> edgeAttribute;
List<Tuple<string, List<Tuple<string, string, string>>>> attributeMapping;
visitor.Invoke(schema, statement.SelectStatement, out edges, out edgeAttribute, out attributeMapping);
CreateEdgeView(schema, nodeName, edgeViewName, edges, edgeAttribute, null, attributeMapping);
statement.SchemaObjectName =
new WSchemaObjectName(new Identifier
{
Value = string.Format("{0}_{1}_{2}_Sampling", schema, nodeName, edgeViewName)
});
string a = statement.ToString();
//ExecuteNonQuery(statement.ToString());
}
public void CreateNodeView(string query)
{
IList<ParseError> errors;
var parser = new GraphViewParser();
var script = parser.ParseCreateNodeEdgeViewStatement(query, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null || script.Batches.Count == 0)
{
throw new SyntaxErrorException("Invalid CREATE VIEW statement.");
}
var statement = script.Batches[0].Statements[0] as WCreateViewStatement;
if (statement == null)
throw new SyntaxErrorException("Not a CREATE VIEW statement");
var nodeViewObjectName = statement.SchemaObjectName;
string schema = nodeViewObjectName.SchemaIdentifier == null
? "dbo"
: nodeViewObjectName.SchemaIdentifier.Value;
string nodeViewName = nodeViewObjectName.BaseIdentifier.Value;
var visitor = new NodeViewSelectStatementVisitor();
List<string> tableObjList;
List<Tuple<string, List<Tuple<string, string>>>> propertymapping;
visitor.Invoke(schema, statement.SelectStatement, out tableObjList, out propertymapping);
CreateNodeView(schema, nodeViewName, tableObjList, propertymapping);
}
public int ExecuteNonQuery()
{
try
{
if (CommandType == CommandType.StoredProcedure)
{
if (Tx != null)
{
Command.Transaction = Tx;
}
Command.CommandText = CommandText;
return Command.ExecuteNonQuery();
}
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
bool externalTransaction = true;
if (Tx == null)
{
externalTransaction = false;
Tx = Connection.BeginTransaction();
}
// Translation
var modVisitor = new TranslateDataModificationVisitor(Tx);
modVisitor.Invoke(script);
var matchVisitor = new TranslateMatchClauseVisitor(Tx);
matchVisitor.Invoke(script);
Command.CommandText = script.ToString();
Command.Transaction = Tx;
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
#endif
int res = Command.ExecuteNonQuery();
if (!externalTransaction)
{
Tx.Commit();
Tx.Dispose();
Tx = null;
}
return res;
}
catch (SqlException e)
{
throw new SqlExecutionException("An error occurred when executing the query", e);
}
}
public SqlDataReader ExecuteReader()
{
try
{
if (CommandType == CommandType.StoredProcedure)
{
if (Tx != null)
{
Command.Transaction = Tx;
}
Command.CommandText = CommandText;
return Command.ExecuteReader();
}
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (Tx == null)
{
using(SqlConnection translationConnection = new SqlConnection(Connection.Conn.ConnectionString))
{
translationConnection.Open();
using (SqlTransaction translationTx = translationConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
{
var visitor = new TranslateMatchClauseVisitor(translationTx);
visitor.Invoke(script);
// Executes translated SQL
Command.CommandText = script.ToString();
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
//throw new GraphViewException("No Execution");
#endif
var reader = Command.ExecuteReader();
translationTx.Commit();
return reader;
}
}
}
else
{
var visitor = new TranslateMatchClauseVisitor(Tx);
visitor.Invoke(script);
// Executes translated SQL
Command.CommandText = script.ToString();
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
//throw new GraphViewException("No Execution");
#endif
var reader = Command.ExecuteReader();
return reader;
}
}
catch (SqlException e)
{
throw new SqlExecutionException("An error occurred when executing the query", e);
}
}
/// <summary>
/// Creates a node table in the graph database.
/// </summary>
/// <param name="sqlStr">A CREATE TABLE 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 CreateNodeTable(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of CREATE TABLE command
var parser = new GraphViewParser();
List<WNodeTableColumn> columns;
IList<ParseError> errors;
var script = parser.ParseCreateNodeTableStatement(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 CREATE TABLE statement.");
}
var statement = script.Batches[0].Statements[0] as WCreateTableStatement;
var tableSchema = statement.SchemaObjectName.SchemaIdentifier != null
? statement.SchemaObjectName.SchemaIdentifier.Value
: "dbo";
var tableName = statement.SchemaObjectName.BaseIdentifier.Value;
SqlTransaction tx;
if (externalTransaction == null)
{
tx = Conn.BeginTransaction();
}
else
{
tx = externalTransaction;
}
// Persists the node table's meta-data
try
{
Int64 tableId;
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = string.Format(@"
INSERT INTO [{0}] ([TableSchema], [TableName])
OUTPUT [Inserted].[TableId]
VALUES (@tableSchema, @tableName)", MetadataTables[0]);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
// get generated TableId
WValueExpression tableIdentitySeed;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
tableId = Convert.ToInt64(reader["TableId"], CultureInfo.CurrentCulture);
var tableIdSeek = tableId << 48;
tableIdentitySeed = new WValueExpression(tableIdSeek.ToString(CultureInfo.InvariantCulture), false);
}
// create graph table
var wColumnDefinition = statement.Definition.ColumnDefinitions
.FirstOrDefault(x => x.ColumnIdentifier.Value == "GlobalNodeId");
if (wColumnDefinition != null)
wColumnDefinition.IdentityOptions.IdentitySeed = tableIdentitySeed;
command.CommandText = statement.ToString();
command.ExecuteNonQuery();
}
var edgeColumnNameToColumnId = new Dictionary<string, long>(StringComparer.OrdinalIgnoreCase);
var hasReversedEdge = new Dictionary<string, bool>(StringComparer.OrdinalIgnoreCase);
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
// insert graph column
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;
}
}
}
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();
}
}
// insert graph edge attributes
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
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);
var createOrder = 1;
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();
}
}
}
// create column edge sampling table
// using (var command = new SqlCommand(null, Conn))
// {
// command.Transaction = tx;
// foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
// {
// command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
// CREATE TABLE [{0}_{1}_{2}_Sampling] (
// [src] [bigint],
// [dst] [bigint]
// )", tableSchema, tableName, column.ColumnName.Value);
// command.ExecuteNonQuery();
// }
// }
// process edge's Decoder function
//var edgeDict = columns.OfType<WGraphTableEdgeColumn>()
// .ToDictionary(col => col.ColumnName.Value,
// col =>
// col.Attributes.Select(
// x =>
// new Tuple<string, string>(x.Item1.Value,
// x.Item2.ToString().ToLower(CultureInfo.CurrentCulture)))
// .ToList());
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();
var userIdColumn = columns.Where(e => e.ColumnRole == WNodeTableColumnRole.NodeId).ToList();
string userId = (userIdColumn.Count == 0) ? "" : userIdColumn[0].ColumnName.Value;
if (edgeDict.Count > 0)
{
var assemblyName = tableSchema + '_' + tableName;
GraphViewDefinedFunctionRegister register = new NodeTableRegister(assemblyName, tableName, edgeDict, userId);
register.Register(Conn, tx);
}
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();
}
}
//updateGlobalNodeView(tableSchema, tx);
// check whether adding reversed edges into the current node table column collection are required
var reversedEdgeCommandText = String.Empty;
var reversedEdgeSamplingCommandText = String.Empty;
var reversedMetaCommandTextList = new List<string>();
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = String.Format(@"
SELECT [ColumnId], [TableSchema], [TableName], [ColumnName], [ColumnRole], [Reference], [HasReversedEdge], [EdgeUdfPrefix]
FROM [{0}]
WHERE [ColumnRole] = 1 AND [Reference] = '{1}'",
MetadataTables[1], tableName);
var originalColumnIdList = new List<long>();
using (var reader = command.ExecuteReader())
{
var index = 0;
while (reader.Read())
{
if (!bool.Parse(reader["HasReversedEdge"].ToString())) continue;
var sourceTableSchema = reader["TableSchema"].ToString();
var sourceTableName = reader["TableName"].ToString();
var sourceEdgeName = reader["ColumnName"].ToString();
var reversedEdgeName = sourceTableName + "_" + sourceEdgeName + "Reversed";
var parameters = new string[]
{
"@tableSchema" + index,
"@tableName" + index,
"@tableid" + index,
"@columnName" + index,
"@columnRole" + index,
"@ref" + index,
"@refTableSchema" + index,
"@isRevEdge" + index,
"@udfPrefix" + index
};
// add reversed edge varbinary columns
reversedEdgeCommandText += 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;
",
tableSchema, tableName,
reversedEdgeName,
reversedEdgeName + "DeleteCol",
reversedEdgeName + "OutDegree");
// create reversed edge sampling table
reversedEdgeSamplingCommandText += String.Format(@"
SELECT * INTO [{0}_{1}_{2}_Sampling] FROM (
SELECT ([GlobalNodeID]+0) as [Src], [Edge].*
FROM [{0}].[{1}] WITH (NOLOCK)
CROSS APPLY {3}_{4}_{5}_Decoder([{2}],[{2}DeleteCol],0) AS Edge
WHERE 1=0) as EdgeSample
",
tableSchema, tableName, reversedEdgeName,
sourceTableSchema, sourceTableName, sourceEdgeName);
// update meta info about reversed edges
reversedMetaCommandTextList.Add(string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [TableId], [ColumnName], [ColumnRole], [Reference], [RefTableSchema], [IsReversedEdge], [EdgeUdfPrefix])
OUTPUT [Inserted].[ColumnId]
VALUES ({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})
",
MetadataTables[1],
parameters[0], parameters[1], parameters[2],
parameters[3], parameters[4], parameters[5],
parameters[6], parameters[7], parameters[8]));
command.Parameters.AddWithValue(parameters[0], tableSchema);
command.Parameters.AddWithValue(parameters[1], tableName);
command.Parameters.AddWithValue(parameters[2], tableId);
command.Parameters.AddWithValue(parameters[3], reversedEdgeName);
command.Parameters.AddWithValue(parameters[4], Convert.ToInt32(reader["ColumnRole"].ToString(), CultureInfo.CurrentCulture));
command.Parameters.AddWithValue(parameters[5], sourceTableName);
command.Parameters.AddWithValue(parameters[6], sourceTableSchema);
command.Parameters.AddWithValue(parameters[7], 1);
command.Parameters.AddWithValue(parameters[8], reader["EdgeUdfPrefix"].ToString());
originalColumnIdList.Add(Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture));
++index;
}
}
if (String.IsNullOrEmpty(reversedEdgeCommandText) == false)
{
command.CommandText = reversedEdgeCommandText;
command.ExecuteNonQuery();
command.CommandText = reversedEdgeSamplingCommandText;
command.ExecuteNonQuery();
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;
}
}
// NodeTableColumnCollection update
for (var i = 0; i < reversedMetaCommandTextList.Count; ++i)
{
command.CommandText = reversedMetaCommandTextList[i];
if (command.Parameters.Contains("@tableSchema"))
{
command.Parameters.RemoveAt("@tableSchema");
command.Parameters.RemoveAt("@tableName");
command.Parameters.RemoveAt("@columnName");
command.Parameters.RemoveAt("@columnid");
command.Parameters.RemoveAt("@AverageDegree");
if (command.Parameters.Contains("@attrName"))
{
command.Parameters.RemoveAt("@attrName");
command.Parameters.RemoveAt("@attrType");
command.Parameters.RemoveAt("@attrId");
}
}
long reversedColumnId = 0;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
if (Convert.ToInt32(command.Parameters["@columnRole"+i].Value, CultureInfo.CurrentCulture) == 1)
{
reversedColumnId = Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture);
}
}
// EdgeAverageDegreeCollection update
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@columnName", command.Parameters["@columnName" + i].Value.ToString());
command.Parameters.Add("@columnid", SqlDbType.Int);
command.Parameters["@columnid"].Value = reversedColumnId;
command.Parameters.Add("@AverageDegree", SqlDbType.Int);
command.Parameters["@AverageDegree"].Value = 5;
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AverageDegree])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @AverageDegree)", MetadataTables[3]);
command.ExecuteNonQuery();
// get all original attributes
var attributes = new List<Tuple<string, string>>();
command.CommandText = String.Format(@"
SELECT [AttributeName], [AttributeType]
FROM [{0}]
WHERE [ColumnId] = {1}",
MetadataTables[2], originalColumnIdList[i]);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
attributes.Add(new Tuple<string, string>(
reader["AttributeName"].ToString(), reader["AttributeType"].ToString()));
}
}
if (attributes.Count == 0) continue;
// EdgeAttributeCollection update
command.Parameters.Add("@attrName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrType", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrId", SqlDbType.Int);
foreach (var attr in attributes)
{
command.Parameters["@attrName"].Value = attr.Item1;
command.Parameters["@attrType"].Value = attr.Item2;
command.Parameters["@attrId"].Value = (createOrder++).ToString();
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.ExecuteNonQuery();
}
}
}
}
// check whether adding reverse edges to pointed node tables are required
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
foreach (var column in columns.Where(x=>hasReversedEdge.ContainsKey(x.ColumnName.Value)))
{
command.Parameters.Clear();
reversedEdgeCommandText = reversedEdgeSamplingCommandText = String.Empty;
var edgeColumn = column as WGraphTableEdgeColumn;
if (edgeColumn == null) continue;
var refTableName =
(edgeColumn.TableReference as WNamedTableReference).ExposedName.Value;
string refTableSchema = String.Empty, reversedEdgeName = String.Empty;
Int64 refTableId = 0;
// skip the edge which connects to the node table itself
if (refTableName.Equals(tableName)) continue;
command.CommandText = String.Format(@"
SELECT [TableId], [TableSchema], [TableName]
FROM [{0}]
WHERE [TableRole] = 0 AND [TableName] = '{1}'",
MetadataTables[0], refTableName);
using (var reader = command.ExecuteReader())
{
// if the target node table exists
if (reader.Read())
{
refTableSchema = reader["TableSchema"].ToString();
reversedEdgeName = tableName + "_" + column.ColumnName.Value + "Reversed";
// add reversed edge varbinary column
reversedEdgeCommandText += 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
",
refTableSchema, refTableName,
reversedEdgeName,
reversedEdgeName + "DeleteCol",
reversedEdgeName + "OutDegree");
// create reversed edge sampling table
reversedEdgeSamplingCommandText += String.Format(@"
SELECT * INTO [{0}_{1}_{2}_Sampling] FROM (
SELECT ([GlobalNodeID]+0) as [Src], [Edge].*
FROM [{0}].[{1}] WITH (NOLOCK)
CROSS APPLY {3}_{4}_{5}_Decoder([{2}],[{2}DeleteCol],0) AS Edge
WHERE 1=0) as EdgeSample
",
refTableSchema, refTableName, reversedEdgeName,
tableSchema, tableName, column.ColumnName.Value);
refTableId = Convert.ToInt64(reader["TableId"], CultureInfo.CurrentCulture);
}
}
// update meta info about reversed edge
if (String.IsNullOrEmpty(reversedEdgeCommandText) == false)
{
command.CommandText = reversedEdgeCommandText;
command.ExecuteNonQuery();
command.CommandText = reversedEdgeSamplingCommandText;
command.ExecuteNonQuery();
// NodeTableColumnCollection update
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [TableId], [ColumnName], [ColumnRole], [Reference], [RefTableSchema], [IsReversedEdge], [EdgeUdfPrefix])
OUTPUT [Inserted].[ColumnId]
VALUES (@tableSchema, @tableName, @tableid, @columnName, @columnRole, @ref, @refTableSchema, @isRevEdge, @udfPrefix)", MetadataTables[1]);
command.Parameters.AddWithValue("@tableSchema", refTableSchema);
command.Parameters.AddWithValue("@tableName", refTableName);
command.Parameters.AddWithValue("@tableid", refTableId);
command.Parameters.AddWithValue("@columnName", reversedEdgeName);
command.Parameters.AddWithValue("@columnRole", (int)column.ColumnRole);
command.Parameters.AddWithValue("@ref", tableName);
command.Parameters.AddWithValue("@refTableSchema", tableSchema);
command.Parameters.AddWithValue("@isRevEdge", 1);
command.Parameters.AddWithValue("@udfPrefix", tableSchema + "_" + tableName + "_" + column.ColumnName.Value);
long reversedColumnId = 0;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
if (column.ColumnRole == WNodeTableColumnRole.Edge)
{
reversedColumnId = Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture);
}
}
// EdgeAverageDegreeCollection update
command.Parameters.Add("@columnid", SqlDbType.Int);
command.Parameters["@columnid"].Value = reversedColumnId;
command.Parameters.Add("@AverageDegree", SqlDbType.Int);
command.Parameters["@AverageDegree"].Value = 5;
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AverageDegree])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @AverageDegree)", MetadataTables[3]);
command.ExecuteNonQuery();
command.CommandText = String.Format(@"
SELECT MAX([AttributeEdgeId]) AS maxAttrEdgeId
FROM [{0}]
WHERE [TableSchema] = '{1}' AND [TableName] = '{2}'",
MetadataTables[2], refTableSchema, refTableName);
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.Parameters.Add("@attrName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrType", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrId", SqlDbType.Int);
foreach (var attr in edgeColumn.Attributes)
{
command.Parameters["@attrName"].Value = attr.Item1.Value;
command.Parameters["@attrType"].Value = attr.Item2.ToString();
command.Parameters["@attrId"].Value = (createOrder++).ToString();
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.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 creating the node table.\n" + e.Message, e);
}
}
// 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);
}
}
/// <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);
}
}
/// <summary>
/// Drops node table and related metadata.
/// </summary>
/// <param name="sqlStr"> Name of table to be dropped.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the drop node table will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool DropNodeTable(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of DROP TABLE command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WDropTableStatement;
if (statement == null)
return false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
// delete metadata
using (var command = new SqlCommand(null, Conn, tran))
{
var schemaSet = new HashSet<string>();
foreach (var obj in statement.Objects)
{
var tableName = obj.BaseIdentifier.Value;
var tableSchema = obj.SchemaIdentifier != null
? obj.SchemaIdentifier.Value
: "dbo";
if (!schemaSet.Contains(tableSchema.ToLower()))
{
schemaSet.Add(tableSchema.ToLower());
}
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
var edgeColumns = GetGraphEdgeColumns(tableSchema, tableName, tran);
if (edgeColumns.Count > 0)
{
var assemblyName = tableSchema + '_' + tableName;
foreach (var edgeColumn in edgeColumns)
{
// isEdgeView
if (edgeColumn.Item3)
DropEdgeView(tableSchema, tableName,
edgeColumn.Item1 /*edgeView name*/, edgeColumn.Item6 /*isRevEdgeView*/, tran);
else
{
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
DROP TABLE [{0}_{1}_{2}_Sampling]",
tableSchema, tableName, edgeColumn.Item1);
command.ExecuteNonQuery();
// skip reversed edges since they have no UDF
if (!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();
}
}
foreach (var table in MetadataTables)
{
if (table == MetadataTables[4] || table == MetadataTables[5] || table == MetadataTables[6] ||
table == MetadataTables[7])
continue;
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
DELETE FROM [{0}]
WHERE [TableName] = @tableName AND [TableSchema] = @tableSchema", table);
command.ExecuteNonQuery();
}
}
// drop node table
command.CommandText = sqlStr;
command.ExecuteNonQuery();
foreach (var it in schemaSet)
{
UpdateGlobalNodeView(it, tran);
}
if (externalTransaction == null)
{
tran.Commit();
}
return true;
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when dropping the node table.", e);
}
}