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