private IQueryImporter GetQueryImporter(string tableName, bool remote)
{
var d = new DestinationTableParameters();
d.Table = new Jhu.Graywulf.Schema.Table()
{
Dataset = new Jhu.Graywulf.Schema.SqlServer.SqlServerDataset("", Test.Constants.TestConnectionString),
SchemaName = "dbo",
TableName = tableName
};
d.Operation = DestinationTableOperation.Create;
var s = new SourceQueryParameters();
s.Dataset = new Jhu.Graywulf.Schema.SqlServer.SqlServerDataset("TEST", Test.Constants.TestConnectionString);
s.Query = "SELECT 1 AS one, 2 AS two, 3 AS three";
IQueryImporter q = null;
if (remote)
{
q = RemoteServiceHelper.CreateObject<IQueryImporter>(Test.Constants.Localhost);
}
else
{
q = new QueryImporter();
}
q.Source = s;
q.Destination = d;
return q;
}
public QueryImporter(SourceQueryParameters source, DestinationTableParameters destination)
: base(destination)
{
InitializeMembers();
this.source = source;
}
public DataFileExporter(SourceQueryParameters source, DataFileBase destination)
{
InitializeMembers();
this.source = source;
this.destination = destination;
}
private IDataFileExporter GetDataFileExporter(string name, bool remote)
{
var source = new SourceQueryParameters();
source.Dataset = new Jhu.Graywulf.Schema.SqlServer.SqlServerDataset("TEST", Test.Constants.TestConnectionString);
source.Query = "SELECT * FROM SampleData";
var uri = new Uri(String.Format(@"file://{0}/{1}/{2}.txt", Test.Constants.RemoteHost1, Test.Constants.GWCode, name));
var destination = new CsvFile(uri, DataFileMode.Write);
IDataFileExporter dfe = null;
if (remote)
{
dfe = RemoteServiceHelper.CreateObject<IDataFileExporter>(Test.Constants.Localhost);
}
else
{
dfe = new DataFileExporter();
}
dfe.Source = source;
dfe.Destination = destination;
return dfe;
}
/// <summary>
/// Creates or truncates destination table in the output database (usually MYDB)
/// </summary>
/// <remarks>
/// This has to be in the QueryPartition class because the Query class does not
/// have information about the database server the partition is executing on and
/// the temporary tables are required to generate the destination table schema.
/// </remarks>
public void PrepareDestinationTable(Context context, IScheduler scheduler)
{
switch (query.ExecutionMode)
{
case ExecutionMode.SingleServer:
// Output is already written to target table
break;
case Jobs.Query.ExecutionMode.Graywulf:
{
InitializeQueryObject(context, scheduler);
lock (query.syncRoot)
{
// Only initialize target table if it's still uninitialzed
if (!query.IsDestinationTableInitialized)
{
var source = new SourceQueryParameters()
{
Query = GetDestinationTableSchemaSourceQuery(),
Dataset = GetDestinationTableSchemaSourceDataset(),
Timeout = Query.QueryTimeout
};
// TODO: this is screwed up here
// drop table in every partition... call it only once
if ((query.Destination.Operation & DestinationTableOperation.Drop) != 0)
{
DropTableOrView(query.Destination.Table);
}
if ((query.Destination.Operation & DestinationTableOperation.Create) != 0)
{
CreateTableForBulkCopy(source, query.Destination, false);
}
else if ((query.Destination.Operation & DestinationTableOperation.Clear) != 0)
{
// TODO: This might need some revision here
// what if schema differs?
TruncateTable(query.Destination.Table);
}
else if ((query.Destination.Operation & DestinationTableOperation.Append) != 0)
{
// TODO: This might need some revision here
// what if schema differs?
throw new NotImplementedException();
}
else
{
throw new NotImplementedException();
}
}
query.IsDestinationTableInitialized = true;
}
}
break;
default:
throw new NotImplementedException();
}
}
private void InitializeMembers()
{
this.source = null;
}
public void ExecuteQuery()
{
DestinationTableParameters destination;
SourceQueryParameters source = new SourceQueryParameters()
{
Query = GetOutputSelectQuery(),
Timeout = Query.QueryTimeout,
};
switch (Query.ExecutionMode)
{
case ExecutionMode.SingleServer:
// In single-server mode results are directly written into destination table
source.Dataset = Query.Destination.Table.Dataset;
destination = Query.Destination;
break;
case ExecutionMode.Graywulf:
// In graywulf mode results are written into a temporary table first
var temptable = GetOutputTable();
TemporaryTables.TryAdd(temptable.TableName, temptable);
DropTableOrView(temptable); // TODO: not needed
source.Dataset = AssignedServerInstance.GetDataset();
destination = new DestinationTableParameters()
{
Operation = DestinationTableOperation.Append, // TODO: change to drop
Table = temptable
};
break;
default:
throw new NotImplementedException();
}
ExecuteSelectInto(source, destination);
}
public SourceQueryParameters PrepareCopyRemoteTable(TableReference table)
{
// Load schema
var sm = this.GetSchemaManager(false);
var ds = sm.Datasets[table.DatasetName];
// Graywulf dataset is to be converted to prevent registry access
if (ds is GraywulfDataset)
{
ds = new SqlServerDataset(ds);
}
var source = new SourceQueryParameters();
source.Dataset = ds;
// Find the query specification this table belongs to
var qs = ((TableSource)table.Node).QuerySpecification;
// Run the normalizer
var cnr = new SearchConditionNormalizer();
cnr.NormalizeQuerySpecification(qs);
var cg = SqlCodeGeneratorFactory.CreateCodeGenerator(ds);
source.Query = cg.GenerateMostRestrictiveTableQuery(table, true, 0);
return source;
}
public void CopyRemoteTable(TableReference table, SourceQueryParameters source)
{
// Temp table name
var temptable = GetTemporaryTable(table.EscapedUniqueName);
TemporaryTables.TryAdd(table.UniqueName, temptable);
var destination = new DestinationTableParameters()
{
Table = temptable,
Operation = DestinationTableOperation.Drop | DestinationTableOperation.Create,
};
var bcp = CreateQueryImporter(source, destination, false);
bcp.Source = source;
bcp.Destination = destination;
bcp.CreateDestinationTable();
var guid = Guid.NewGuid();
RegisterCancelable(guid, bcp);
bcp.Execute();
UnregisterCancelable(guid);
}
/// <summary>
/// Copies resultset from the output temporary table to the destination database (MYDB)
/// </summary>
public void CopyResultset()
{
switch (Query.ExecutionMode)
{
case ExecutionMode.SingleServer:
// Do nothing as execute writes results directly into destination table
break;
case ExecutionMode.Graywulf:
{
var sql = "SELECT tablealias.* FROM [{0}].[{1}].[{2}] AS tablealias";
var temptable = GetOutputTable();
sql = String.Format(sql, temptable.DatabaseName, temptable.SchemaName, temptable.TableName);
var source = new SourceQueryParameters()
{
Dataset = temptable.Dataset,
Query = sql,
Timeout = Query.QueryTimeout,
};
// Change destination to Append, output table has already been created,
// partitions only append to it
var destination = new DestinationTableParameters(Query.Destination);
destination.Operation = DestinationTableOperation.Append;
ExecuteBulkCopy(source, destination, false, Query.QueryTimeout);
}
break;
default:
throw new NotImplementedException();
}
}
protected IQueryImporter CreateQueryImporter(SourceQueryParameters source, DestinationTableParameters destination, bool local)
{
var desthost = GetHostnameFromSqlConnectionString(destination.Table.Dataset.ConnectionString);
IQueryImporter qi;
if (local)
{
qi = new QueryImporter();
}
else
{
qi = RemoteServiceHelper.CreateObject<IQueryImporter>(desthost);
}
qi.Source = source;
qi.Destination = destination;
return qi;
}
private void OnAsyncExecute(Guid workflowInstanceGuid, string activityInstanceId, QueryPartitionBase querypartition, TableReference remotetable, SourceQueryParameters source)
{
RegisterCancelable(workflowInstanceGuid, activityInstanceId, querypartition);
querypartition.CopyRemoteTable(remotetable, source);
UnregisterCancelable(workflowInstanceGuid, activityInstanceId, querypartition);
}
protected void CreateTableForBulkCopy(SourceQueryParameters source, DestinationTableParameters destination, bool local)
{
#if !SKIPQUERIES
var bcp = CreateQueryImporter(source, destination, local);
bcp.CreateDestinationTable();
#endif
}
private void CopyMembers(SourceQueryParameters old)
{
this.dataset = old.dataset;
this.query = old.query;
this.timeout = old.timeout;
}
public SourceQueryParameters(SourceQueryParameters old)
{
CopyMembers(old);
}
protected void ExecuteBulkCopy(SourceQueryParameters source, DestinationTableParameters destination, bool local, int timeout)
{
var bcp = CreateQueryImporter(source, destination, local);
bcp.Destination.BulkInsertTimeout = timeout;
var guid = Guid.NewGuid();
RegisterCancelable(guid, bcp);
#if !SKIPQUERIES
bcp.Execute();
#endif
UnregisterCancelable(guid);
}
protected void ExecuteSelectInto(SourceQueryParameters source, DestinationTableParameters destination)
{
string sql = String.Format(
"SELECT __tablealias.* INTO [{0}].[{1}].[{2}] FROM ({3}) AS __tablealias",
!String.IsNullOrWhiteSpace(destination.Table.DatabaseName) ? destination.Table.DatabaseName : destination.Table.Dataset.DatabaseName,
destination.Table.SchemaName,
destination.Table.TableName,
source.Query);
ExecuteLongCommandNonQuery(sql, source.Dataset.ConnectionString, source.Timeout);
}
public IDataFileExporter GetInitializedExporter()
{
// Determine server name from connection string
// This is required, because bulk copy can go into databases that are only known
// by their connection string
// Get server name from data source name (requires trimming the sql server instance name)
string host;
var csb = new SqlConnectionStringBuilder(source.Dataset.ConnectionString);
int i = csb.DataSource.IndexOf('\\');
if (i > -1)
{
host = csb.DataSource.Substring(i);
}
else
{
host = csb.DataSource;
}
// Create bulk operation
var sq = new SourceQueryParameters();
sq.Dataset = source.Dataset;
//sq.ProviderInvariantName = Jhu.Graywulf.Schema.Constants.SqlServerProviderName;
//sq.ConnectionString = connectionString;
sq.Query = String.Format("SELECT t.* FROM [{0}].[{1}] AS t", source.SchemaName, source.ObjectName);
var dfe = RemoteServiceHelper.CreateObject<IDataFileExporter>(host);
dfe.Source = sq;
dfe.Destination = destination;
return dfe;
}
private void InitializeMembers()
{
this.source = null;
this.destination = null;
}
