public ParseAlterTableAddNodeTableColumnStatement ( string queryStr, List |
||
queryStr | string | The CREATE TABLE statement creating a ndoe table |
nodeTableColumns | List |
A list of columns of the node table |
errors | IList |
Parsing errors |
Résultat |
// 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); } }