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