/// <summary> /// Filters out metadata that is not present in the result set, and matches metadata ordering to resultset. /// </summary> public static EditColumnMetadata[] FilterColumnMetadata(EditColumnMetadata[] metaColumns, DbColumnWrapper[] resultColumns) { if (metaColumns.Length == 0) { return(metaColumns); } bool escapeColName = FromSqlScript.IsIdentifierBracketed(metaColumns[0].EscapedName); Dictionary <string, int> columnNameOrdinalMap = new Dictionary <string, int>(capacity: resultColumns.Length); for (int i = 0; i < resultColumns.Length; i++) { DbColumnWrapper column = resultColumns[i]; string columnName = column.ColumnName; if (escapeColName && !FromSqlScript.IsIdentifierBracketed(columnName)) { columnName = ToSqlScript.FormatIdentifier(columnName); } columnNameOrdinalMap.Add(columnName, column.ColumnOrdinal ?? i); } HashSet <string> resultColumnNames = columnNameOrdinalMap.Keys.ToHashSet(); metaColumns = Array.FindAll(metaColumns, column => resultColumnNames.Contains(column.EscapedName)); foreach (EditColumnMetadata metaCol in metaColumns) { metaCol.Ordinal = columnNameOrdinalMap[metaCol.EscapedName]; } Array.Sort(metaColumns, (x, y) => (Comparer <int> .Default).Compare(x.Ordinal, y.Ordinal)); return(metaColumns); }
public void DecodeMultipartIdentifierTest(string input, string[] output) { // If: I decode the input string[] decoded = FromSqlScript.DecodeMultipartIdentifier(input); // Then: The output should match what was expected Assert.Equal(output, decoded); }
/// <summary> /// Generates a edit-ready metadata object using SMO /// </summary> /// <param name="connection">Connection to use for getting metadata</param> /// <param name="objectName">Name of the object to return metadata for</param> /// <param name="objectType">Type of the object to return metadata for</param> /// <returns>Metadata about the object requested</returns> public TableMetadata GetObjectMetadata(DbConnection connection, string schemaName, string objectName, string objectType) { // Get a connection to the database for SMO purposes SqlConnection sqlConn = connection as SqlConnection; if (sqlConn == null) { // It's not actually a SqlConnection, so let's try a reliable SQL connection ReliableSqlConnection reliableConn = connection as ReliableSqlConnection; if (reliableConn == null) { // If we don't have connection we can use with SMO, just give up on using SMO return(null); } // We have a reliable connection, use the underlying connection sqlConn = reliableConn.GetUnderlyingConnection(); } // Connect with SMO and get the metadata for the table ServerConnection serverConnection; if (sqlConn.AccessToken == null) { serverConnection = new ServerConnection(sqlConn); } else { serverConnection = new ServerConnection(sqlConn, new AzureAccessToken(sqlConn.AccessToken)); } Server server = new Server(serverConnection); Database database = server.Databases[sqlConn.Database]; TableViewTableTypeBase smoResult; switch (objectType.ToLowerInvariant()) { case "table": Table table = string.IsNullOrEmpty(schemaName) ? new Table(database, objectName) : new Table(database, objectName, schemaName); table.Refresh(); smoResult = table; break; case "view": View view = string.IsNullOrEmpty(schemaName) ? new View(database, objectName) : new View(database, objectName, schemaName); view.Refresh(); smoResult = view; break; default: throw new ArgumentOutOfRangeException(nameof(objectType), SR.EditDataUnsupportedObjectType(objectType)); } if (smoResult == null) { throw new ArgumentOutOfRangeException(nameof(objectName), SR.EditDataObjectMetadataNotFound); } // Generate the edit column metadata List <ColumnMetadata> editColumns = new List <ColumnMetadata>(); for (int i = 0; i < smoResult.Columns.Count; i++) { Column smoColumn = smoResult.Columns[i]; // The default value may be escaped string defaultValue = smoColumn.DefaultConstraint == null ? null : FromSqlScript.UnwrapLiteral(smoColumn.DefaultConstraint.Text); ColumnMetadata column = new ColumnMetadata { DefaultValue = defaultValue, EscapedName = ToSqlScript.FormatIdentifier(smoColumn.Name), Ordinal = i }; editColumns.Add(column); } // Only tables can be memory-optimized Table smoTable = smoResult as Table; bool isMemoryOptimized = smoTable != null && smoTable.IsMemoryOptimized; // Escape the parts of the name string[] objectNameParts = { smoResult.Schema, smoResult.Name }; string escapedMultipartName = ToSqlScript.FormatMultipartIdentifier(objectNameParts); return(new TableMetadata { Columns = editColumns.ToArray(), EscapedMultipartName = escapedMultipartName, IsMemoryOptimized = isMemoryOptimized, }); }
/// <summary> /// Generates a edit-ready metadata object using SMO /// </summary> /// <param name="connection">Connection to use for getting metadata</param> /// <param name="objectNamedParts">Split and unwrapped name parts</param> /// <param name="objectType">Type of the object to return metadata for</param> /// <returns>Metadata about the object requested</returns> public EditTableMetadata GetObjectMetadata(DbConnection connection, string[] objectNamedParts, string objectType) { Validate.IsNotNull(nameof(objectNamedParts), objectNamedParts); if (objectNamedParts.Length <= 0) { throw new ArgumentNullException(nameof(objectNamedParts), SR.EditDataMetadataObjectNameRequired); } if (objectNamedParts.Length > 2) { throw new InvalidOperationException(SR.EditDataMetadataTooManyIdentifiers); } // Get a connection to the database for SMO purposes SqlConnection sqlConn = connection as SqlConnection; if (sqlConn == null) { // It's not actually a SqlConnection, so let's try a reliable SQL connection ReliableSqlConnection reliableConn = connection as ReliableSqlConnection; if (reliableConn == null) { // If we don't have connection we can use with SMO, just give up on using SMO return(null); } // We have a reliable connection, use the underlying connection sqlConn = reliableConn.GetUnderlyingConnection(); } // Connect with SMO and get the metadata for the table ServerConnection serverConnection; if (sqlConn.AccessToken == null) { serverConnection = new ServerConnection(sqlConn); } else { serverConnection = new ServerConnection(sqlConn, new AzureAccessToken(sqlConn.AccessToken)); } Server server = new Server(serverConnection); Database db = new Database(server, sqlConn.Database); TableViewTableTypeBase smoResult; switch (objectType.ToLowerInvariant()) { case "table": smoResult = objectNamedParts.Length == 1 ? new Table(db, objectNamedParts[0]) // No schema provided : new Table(db, objectNamedParts[1], objectNamedParts[0]); // Schema provided break; case "view": smoResult = objectNamedParts.Length == 1 ? new View(db, objectNamedParts[0]) // No schema provided : new View(db, objectNamedParts[1], objectNamedParts[0]); // Schema provided break; default: throw new ArgumentOutOfRangeException(nameof(objectType), SR.EditDataUnsupportedObjectType(objectType)); } // A bug in SMO makes it necessary to call refresh to attain certain properties (such as IsMemoryOptimized) smoResult.Refresh(); if (smoResult.State != SqlSmoState.Existing) { throw new ArgumentOutOfRangeException(nameof(objectNamedParts), SR.EditDataObjectNotFound); } // Generate the edit column metadata List <EditColumnMetadata> editColumns = new List <EditColumnMetadata>(); for (int i = 0; i < smoResult.Columns.Count; i++) { Column smoColumn = smoResult.Columns[i]; string defaultValue = null; try { // The default value may be escaped defaultValue = smoColumn.DefaultConstraint == null ? null : FromSqlScript.UnwrapLiteral(smoColumn.DefaultConstraint.Text); } catch (PropertyCannotBeRetrievedException) { // This exception will be thrown when the user doesn't have view definition privilege, // we can ignore it and use null as the default value; } EditColumnMetadata column = new EditColumnMetadata { DefaultValue = defaultValue, EscapedName = ToSqlScript.FormatIdentifier(smoColumn.Name), Ordinal = i, IsHierarchyId = smoColumn.DataType.SqlDataType == SqlDataType.HierarchyId, }; editColumns.Add(column); } // Only tables can be memory-optimized Table smoTable = smoResult as Table; bool isMemoryOptimized = false; // TODO: Remove IsSupported check once SMO fixes broken IsMemoryOptimized scenario (TFS #10871823) if (smoTable != null) { isMemoryOptimized = smoTable.IsSupportedProperty("IsMemoryOptimized") && smoTable.IsMemoryOptimized; } // Escape the parts of the name string[] objectNameParts = { smoResult.Schema, smoResult.Name }; string escapedMultipartName = ToSqlScript.FormatMultipartIdentifier(objectNameParts); return(new EditTableMetadata { Columns = editColumns.ToArray(), EscapedMultipartName = escapedMultipartName, IsMemoryOptimized = isMemoryOptimized }); }
public static string[] GetEditTargetName(EditInitializeParams initParams) { return(initParams.SchemaName != null ? new [] { initParams.SchemaName, initParams.ObjectName } : FromSqlScript.DecodeMultipartIdentifier(initParams.ObjectName)); }
public void UnescapeTest(string input, string output) { Assert.Equal(output, FromSqlScript.UnwrapLiteral(input)); }
public void DecodeMultipartIdentifierFailTest(string input) { // If: I decode an invalid input // Then: It should throw an exception Assert.Throws <FormatException>(() => FromSqlScript.DecodeMultipartIdentifier(input)); }
public void BracketedIdentifierTest(string input, bool output) { Assert.Equal(output, FromSqlScript.IsIdentifierBracketed(input)); }