public void Cloning_With_StaticTable(Connection conn) { //Arrange var config = Utils.MakeDefaultSettings(conn); var tablesConfig = config.GetDefaultSchema(); tablesConfig.AddRange(new List <TableModifier> { new TableModifier { Name = "Artist", IsStatic = true } }); var executionPlanBuilder = new ExecutionPlanBuilder(config); var source = new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Album", Columns = new ColumnsWithValue { { "AlbumId", 1 } } }; var clonedData = new List <RowIdentifier>(); executionPlanBuilder.StatusChanged += (s, e) => { if (e.Status == Status.Cloning) { clonedData.Add(e.SourceRow); } }; //Act var query = executionPlanBuilder.Append(source, false).Compile(); query.Commiting += (s, e) => e.Cancel = true; query.Execute(); //Assert var expectedData = new List <RowIdentifier> { new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Album", Columns = new ColumnsWithValue { { "AlbumId", 1 } } } }; Assert.True(Utils.ScrambledEquals(clonedData, expectedData, RowIdentifierComparer.OrdinalIgnoreCase)); }
public BasicClonerTests() { _cache = FakeBasicDatabase.CreateDatabaseSchema(); _queryHelper = FakeBasicDatabase.CreateData(); _queryDispatcher = FakeBasicDatabase.CreateServer(_queryHelper); _executionPlanBuilder = new ExecutionPlanBuilder(null, _queryDispatcher, (IQueryDispatcher d, Settings s, ref MetadataContainer m) => m = _cache, null); }
private void cbDestinationEnvir_SelectionChanged(object sender, SelectionChangedEventArgs e) { //try //{ if (_maps == null || cbDestinationEnvir.SelectedValue == null) { return; } var map = _maps.FirstOrDefault(m => m.From == cbSourceEnvir.SelectedValue.ToString() && m.To == cbDestinationEnvir.SelectedValue.ToString() && m.UsableBehaviours.Split(',').Contains(cbDatabaseConfig.SelectedValue.ToString())); if (map != null) { Properties.Settings.Default.DestinationEnvir = cbDestinationEnvir.SelectedValue.ToString(); Properties.Settings.Default.Save(); var configId = (Int16)cbDatabaseConfig.SelectedValue; var selectedSettings = new Settings { Project = _proj, MapId = map.Id, BehaviourId = configId }; _executionPlanBuilder = new ExecutionPlanBuilder(selectedSettings); _executionPlanBuilder.StatusChanged += ClonerWorkerStatusChanged_event; Servers = _executionPlanBuilder.MetadataContainer.Metadatas.Keys.ToArray().ToList(); cbServer.ItemsSource = Servers; //Tente de charger la préférence utilisateur if (Servers.Contains(Properties.Settings.Default.ServerSource)) { cbServer.SelectedItem = Properties.Settings.Default.ServerSource; } else { if (Servers.Count == 1) { cbServer.SelectedIndex = 0; } } } //} //catch (Exception ex) //{ // MessageBox.Show("Erreur lors de l'initialisation de la cache.\r\n" + ex.ToString(), "Erreur", MessageBoxButton.OK, MessageBoxImage.Error); // throw; //} }
/// <summary> /// DataCloner Hello World program /// </summary> static void Main(string[] args) { //Minimal configuration var project = new Project() { ConnectionStrings = new List <Connection> { new Connection { Id = "UNI", ProviderName = "System.Data.SqlClient", ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;" } } }; //Creating an execution plan for reuse later var builder = new ExecutionPlanBuilder(project, null); builder.StatusChanged += Builder_StatusChanged; Console.WriteLine("Retreiving rows to the execution plan"); NbRowsFetch = 0; var startTime = DateTime.Now.Ticks; builder.Append(new RowIdentifier { ServerId = "UNI", Database = "Chinook", Schema = "dbo", Table = "Customer", Columns = new ColumnsWithValue { { "CustomerId", 1 } } }); var endTime = DateTime.Now.Ticks; //Creating a mew clone of the data inside the database var query = builder.Compile(); query.Commiting += Query_Commiting; query.Execute(); //Results var msElapsed = new TimeSpan(endTime - startTime).TotalMilliseconds; Console.WriteLine($"Rows fetched : {NbRowsFetch}"); Console.WriteLine($"Completed in : {msElapsed} ms"); Console.WriteLine($"Row per second : {NbRowsFetch / (msElapsed / 1000)}"); Console.ReadKey(); }
private void OnExecuteQuery(string Guid, int ID, object CustomIn, object CustomOut, ref bool CancelDefault) { ThreadHelper.ThrowIfNotOnUIThread(); try { if (ActiveDocument == null) { return; } if (!IsDataverse()) { return; } // We are running a query against the Dataverse TDS endpoint, so check if there are any DML statements in the query // Get the SQL editor object var scriptFactory = new ScriptFactoryWrapper(ServiceCache.ScriptFactory); var sqlScriptEditorControl = scriptFactory.GetCurrentlyActiveFrameDocView(ServiceCache.VSMonitorSelection, false, out _); var textSpan = sqlScriptEditorControl.GetSelectedTextSpan(); var sql = textSpan.Text; // Quick check first so we don't spend a long time connecting to CDS just to find there's a simple SELECT query if (sql.IndexOf("INSERT", StringComparison.OrdinalIgnoreCase) == -1 && sql.IndexOf("UPDATE", StringComparison.OrdinalIgnoreCase) == -1 && sql.IndexOf("DELETE", StringComparison.OrdinalIgnoreCase) == -1) { return; } // Allow user to bypass SQL 4 CDS logic in case of problematic queries if (sql.IndexOf("Bypass SQL 4 CDS", StringComparison.OrdinalIgnoreCase) != -1 || sql.IndexOf("Bypass SQL4CDS", StringComparison.OrdinalIgnoreCase) != -1) { return; } // Store the options being used for these queries so we can cancel them later var options = new QueryExecutionOptions(sqlScriptEditorControl, Package.Settings); var metadata = GetMetadataCache(); var org = ConnectCDS(); var dataSource = new DataSource { Name = "local", Metadata = metadata, TableSizeCache = new TableSizeCache(org, metadata), Connection = org }; // We've possibly got a DML statement, so parse the query properly to get the details var converter = new ExecutionPlanBuilder(new[] { dataSource }, options) { TDSEndpointAvailable = true, QuotedIdentifiers = sqlScriptEditorControl.QuotedIdentifiers }; IRootExecutionPlanNode[] queries; try { queries = converter.Build(sql); } catch (Exception ex) { CancelDefault = true; ShowError(sqlScriptEditorControl, textSpan, ex); return; } var dmlQueries = queries.OfType <IDmlQueryExecutionPlanNode>().ToArray(); var hasSelect = queries.Length > dmlQueries.Length; var hasDml = dmlQueries.Length > 0; if (hasSelect && hasDml) { // Can't mix SELECT and DML queries as we can't show results in the grid and SSMS can't execute the DML queries CancelDefault = true; ShowError(sqlScriptEditorControl, textSpan, new ApplicationException("Cannot mix SELECT queries with DML queries. Execute SELECT statements in a separate batch to INSERT/UPDATE/DELETE")); return; } if (hasSelect) { return; } // We need to execute the DML statements directly CancelDefault = true; // Show the queries starting to run sqlScriptEditorControl.StandardPrepareBeforeExecute(); sqlScriptEditorControl.OnExecutionStarted(sqlScriptEditorControl, EventArgs.Empty); sqlScriptEditorControl.ToggleResultsControl(true); sqlScriptEditorControl.Results.StartExecution(); _options[ActiveDocument] = options; var doc = ActiveDocument; // Run the queries in a background thread var task = new System.Threading.Tasks.Task(async() => { var resultFlag = 0; foreach (var query in dmlQueries) { if (options.Cancelled) { break; } try { _ai.TrackEvent("Execute", new Dictionary <string, string> { ["QueryType"] = query.GetType().Name, ["Source"] = "SSMS" }); var msg = query.Execute(new Dictionary <string, DataSource>(StringComparer.OrdinalIgnoreCase) { [dataSource.Name] = dataSource }, options, null, null); sqlScriptEditorControl.Results.AddStringToMessages(msg + "\r\n\r\n"); resultFlag |= 1; // Success } catch (Exception ex) { var error = ex; if (ex is PartialSuccessException partial) { error = partial.InnerException; if (partial.Result is string msg) { sqlScriptEditorControl.Results.AddStringToMessages(msg + "\r\n\r\n"); resultFlag |= 1; // Success } } _ai.TrackException(error, new Dictionary <string, string> { ["Sql"] = sql, ["Source"] = "SSMS" }); AddException(sqlScriptEditorControl, textSpan, error); resultFlag |= 2; // Failure } } if (options.Cancelled) { resultFlag = 4; // Cancel } await Package.JoinableTaskFactory.SwitchToMainThreadAsync(); sqlScriptEditorControl.Results.OnSqlExecutionCompletedInt(resultFlag); _options.Remove(doc); }); options.Task = task; task.Start(); } catch (Exception ex) { VsShellUtilities.LogError("SQL 4 CDS", ex.ToString()); } }
public void CloningDerivatives_With_GlobalAccessDenied(Connection conn) { //Arrange var project = Utils.MakeDefaultProject(conn); project.ExtractionTemplates[0].Tables.Add(new Table { Name = "Album", DerativeTableGlobal = new DerivativeTableGlobal { GlobalAccess = DerivativeTableAccess.Denied } }); var executionPlanBuilder = new ExecutionPlanBuilder(project, Utils.MakeDefaultContext()); var source = new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Artist", Columns = new ColumnsWithValue { { "ArtistId", 1 } } }; var clonedData = new List <RowIdentifier>(); executionPlanBuilder.StatusChanged += (s, e) => { if (e.Status == Status.Cloning) { clonedData.Add(e.SourceRow); } }; //Act var query = executionPlanBuilder.Append(source, true).Compile(); query.Commiting += (s, e) => e.Cancel = true; query.Execute(); //Assert var expectedData = new List <RowIdentifier> { new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Artist", Columns = new ColumnsWithValue { { "ArtistId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Album", Columns = new ColumnsWithValue { { "AlbumId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Album", Columns = new ColumnsWithValue { { "AlbumId", 4 } } } }; Assert.True(Utils.ScrambledEquals(clonedData, expectedData, RowIdentifierComparer.OrdinalIgnoreCase)); }
public void Cloning_With_DataBuilder(Connection conn) { //Arrange var project = Utils.MakeDefaultProject(conn); project.ExtractionTemplates[0].Tables.AddRange(new List <Table> { new Table { Name = "Employee", DataBuilders = new List <DataBuilder> { new DataBuilder { Name = "FirstName", BuilderName = "StringDataBuilder" }, new DataBuilder { Name = "LastName", BuilderName = "StringDataBuilder" }, new DataBuilder { Name = "ReportsTo", BuilderName = "AutoIncrementDataBuilder" } } } }); var executionPlanBuilder = new ExecutionPlanBuilder(project, Utils.MakeDefaultContext()); var source = new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Employee", Columns = new ColumnsWithValue { { "EmployeeId", 1 } } }; //Act var query = executionPlanBuilder.Append(source, false).Compile(); IDbCommand command = null; query.Commiting += (s, e) => { e.Cancel = true; command = e.Command; }; query.Execute(); //Assert var paramFirstName = command?.Parameters["@FirstName0"] as IDataParameter; Assert.Matches("(.+){20}", paramFirstName.Value.ToString()); var paramLastName = command?.Parameters["@LastName0"] as IDataParameter; Assert.Matches("(.+){20}", paramLastName.Value.ToString()); var paramReportsTo = command?.Parameters["@ReportsTo0"] as IDataParameter; Assert.True(paramReportsTo.Value.IsNumericType()); }
public void Cloning_With_ForeignKeyRemove(Connection conn) { //Arrange var project = Utils.MakeDefaultProject(conn); project.ExtractionTemplates[0].Tables.AddRange(new List <Table> { new Table { Name = "Album", ForeignKeys = new ForeignKeys { ForeignKeyRemove = new ForeignKeyRemove { Columns = new List <ForeignKeyRemoveColumn> { new ForeignKeyRemoveColumn { Name = "ArtistId" } } } } } }); var executionPlanBuilder = new ExecutionPlanBuilder(project, Utils.MakeDefaultContext()); var source = new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Album", Columns = new ColumnsWithValue { { "AlbumId", 1 } } }; var clonedData = new List <RowIdentifier>(); executionPlanBuilder.StatusChanged += (s, e) => { if (e.Status == Status.Cloning) { clonedData.Add(e.SourceRow); } }; //Act var query = executionPlanBuilder.Append(source, false).Compile(); query.Commiting += (s, e) => e.Cancel = true; query.Execute(); //Assert var expectedData = new List <RowIdentifier> { new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Album", Columns = new ColumnsWithValue { { "AlbumId", 1 } } } }; Assert.True(Utils.ScrambledEquals(clonedData, expectedData, RowIdentifierComparer.OrdinalIgnoreCase)); }
public void Cloning_Should_NotCloneDerivativeOfDependancy(Connection conn) { //Arrange var project = Utils.MakeDefaultProject(conn); var executionPlanBuilder = new ExecutionPlanBuilder(project, Utils.MakeDefaultContext()); var source = new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "PlaylistTrack", Columns = new ColumnsWithValue { { "PlaylistId", 1 }, { "TrackId", 1 } } }; var clonedData = new List <RowIdentifier>(); executionPlanBuilder.StatusChanged += (s, e) => { if (e.Status == Status.Cloning) { clonedData.Add(e.SourceRow); } }; //Act var query = executionPlanBuilder.Append(source, false).Compile(); query.Execute(); query.Commiting += (s, e) => e.Cancel = true; //Assert var expectedData = new List <RowIdentifier> { new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "PlaylistTrack", Columns = new ColumnsWithValue { { "PlaylistId", 1 }, { "TrackId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Playlist", Columns = new ColumnsWithValue { { "PlaylistId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Track", Columns = new ColumnsWithValue { { "TrackId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Album", Columns = new ColumnsWithValue { { "AlbumId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Artist", Columns = new ColumnsWithValue { { "ArtistId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Genre", Columns = new ColumnsWithValue { { "GenreId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "MediaType", Columns = new ColumnsWithValue { { "MediaTypeId", 1 } } } }; Assert.True(Utils.ScrambledEquals(clonedData, expectedData, RowIdentifierComparer.OrdinalIgnoreCase)); }
public void CloningDependencies_With_DefaultConfig(Connection conn) { //Arrange var executionPlanBuilder = new ExecutionPlanBuilder(Utils.MakeDefaultProject(conn), Utils.MakeDefaultContext()); var source = new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Customer", Columns = new ColumnsWithValue { { "CustomerId", 1 } } }; var clonedData = new List <RowIdentifier>(); executionPlanBuilder.StatusChanged += (s, e) => { if (e.Status == Status.Cloning) { clonedData.Add(e.SourceRow); } }; //Act var query = executionPlanBuilder.Append(source, false).Compile(); query.Commiting += (s, e) => e.Cancel = true; query.Execute(); //Assert var expectedData = new List <RowIdentifier> { new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Customer", Columns = new ColumnsWithValue { { "CustomerId", 1 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Employee", Columns = new ColumnsWithValue { { "EmployeeId", 3 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Employee", Columns = new ColumnsWithValue { { "EmployeeId", 2 } } }, new RowIdentifier { ServerId = conn.Id, Database = Utils.TestDatabase(conn), Schema = Utils.TestSchema(conn), Table = "Employee", Columns = new ColumnsWithValue { { "EmployeeId", 1 } } } }; Assert.True(Utils.ScrambledEquals(clonedData, expectedData, RowIdentifierComparer.OrdinalIgnoreCase)); }
/// <summary> /// This function is the callback used to execute the command when the menu item is clicked. /// See the constructor to see how the menu item is associated with this function using /// OleMenuCommandService service and MenuCommand class. /// </summary> /// <param name="sender">Event sender.</param> /// <param name="e">Event args.</param> private void Execute(object sender, EventArgs e) { ThreadHelper.ThrowIfNotOnUIThread(); try { var sql = GetQuery(); var scriptFactory = new ScriptFactoryWrapper(ServiceCache.ScriptFactory); var sqlScriptEditorControl = scriptFactory.GetCurrentlyActiveFrameDocView(ServiceCache.VSMonitorSelection, false, out _); var options = new QueryExecutionOptions(sqlScriptEditorControl, Package.Settings); var metadata = GetMetadataCache(); var org = ConnectCDS(); var converter = new ExecutionPlanBuilder(metadata, new TableSizeCache(org, metadata), options) { TDSEndpointAvailable = false, QuotedIdentifiers = sqlScriptEditorControl.QuotedIdentifiers }; try { var queries = converter.Build(sql); foreach (var query in queries) { _ai.TrackEvent("Convert", new Dictionary <string, string> { ["QueryType"] = query.GetType().Name, ["Source"] = "SSMS" }); var window = Dte.ItemOperations.NewFile("General\\XML File"); var editPoint = ActiveDocument.EndPoint.CreateEditPoint(); editPoint.Insert("<!--\r\nCreated from query:\r\n\r\n"); editPoint.Insert(query.Sql); var nodes = GetAllNodes(query).ToList(); if (nodes.Any(node => !(node is IFetchXmlExecutionPlanNode))) { editPoint.Insert("\r\n‼ WARNING ‼\r\n"); editPoint.Insert("This query requires additional processing. This FetchXML gives the required data, but needs additional processing to format it in the same way as returned by the TDS Endpoint or SQL 4 CDS.\r\n\r\n"); editPoint.Insert("Learn more at https://markcarrington.dev/sql-4-cds/additional-processing/\r\n\r\n"); } editPoint.Insert("\r\n\r\n-->"); foreach (var fetchXml in nodes.OfType <IFetchXmlExecutionPlanNode>()) { editPoint.Insert("\r\n\r\n"); editPoint.Insert(fetchXml.FetchXmlString); } } } catch (NotSupportedQueryFragmentException ex) { VsShellUtilities.LogError("SQL 4 CDS", ex.ToString()); VsShellUtilities.ShowMessageBox(Package, "The query could not be converted to FetchXML: " + ex.Message, "Query Not Supported", OLEMSGICON.OLEMSGICON_CRITICAL, OLEMSGBUTTON.OLEMSGBUTTON_OK, OLEMSGDEFBUTTON.OLEMSGDEFBUTTON_FIRST); } catch (QueryParseException ex) { VsShellUtilities.LogError("SQL 4 CDS", ex.ToString()); VsShellUtilities.ShowMessageBox(Package, "The query could not be parsed: " + ex.Message, "Query Parsing Error", OLEMSGICON.OLEMSGICON_CRITICAL, OLEMSGBUTTON.OLEMSGBUTTON_OK, OLEMSGDEFBUTTON.OLEMSGDEFBUTTON_FIRST); } } catch (Exception ex) { VsShellUtilities.LogError("SQL 4 CDS", ex.ToString()); } }
public void Should_NotFail_When_Settuping(Connection conn) { var epb = new ExecutionPlanBuilder(Utils.MakeDefaultSettings(conn)); }