public static string GetCode(Node node, bool resolvedNames)
{
var sw = new StringWriter();
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = resolvedNames;
cg.Execute(sw, node);
return sw.ToString();
}
public static string GetCode(Node node, bool resolvedNames)
{
var sw = new StringWriter();
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = resolvedNames;
cg.Execute(sw, node);
return(sw.ToString());
}
public string GenerateTableStatisticsQuery(TableReference table)
{
if (table.Statistics == null)
{
throw new InvalidOperationException();
}
// Build table specific where clause
var cnr = new SearchConditionNormalizer();
cnr.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
var wh = cnr.GenerateWhereClauseSpecificToTable(table);
var where = new StringWriter();
if (wh != null)
{
var cg = new SqlServerCodeGenerator();
cg.Execute(where, wh);
}
;
//*** TODO: move into resource
string sql = String.Format(@"
IF OBJECT_ID('tempdb..##keys_{4}') IS NOT NULL
DROP TABLE ##keys_{4}
SELECT CAST({2} AS float) AS __key
INTO ##keys_{4}
FROM {0} {1}
{3};
DECLARE @count bigint = @@ROWCOUNT;
DECLARE @step bigint = @count / @bincount;
IF (@step = 0) SET @step = NULL;
WITH q AS
(
SELECT __key, ROW_NUMBER() OVER (ORDER BY __key) __rn
FROM ##keys_{4}
)
SELECT __key, __rn
FROM q
WHERE __rn % @step = 1 OR __rn = @count;
DROP TABLE ##keys_{4};
",
GetResolvedTableName(table),
table.Alias == null ? "" : String.Format(" AS {0} ", QuoteIdentifier(table.Alias)),
QuoteIdentifier(table.Statistics.KeyColumn),
where.ToString(),
Guid.NewGuid().ToString().Replace('-', '_'));
return(sql);
}
public void GenerateCreateDestinationTableQueryTest()
{
DataTable schema;
using (var cn = new SqlConnection(Jhu.Graywulf.Test.AppSettings.IOTestConnectionString))
{
cn.Open();
var sql = "SELECT * FROM SampleData";
using (var cmd = new SqlCommand(sql, cn))
{
using (var dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
{
schema = dr.GetSchemaTable();
}
}
}
var dest = new Table()
{
SchemaName = "dbo",
TableName = "destination"
};
var cg = new SqlServerCodeGenerator();
var res = cg.GenerateCreateDestinationTableQuery(schema, dest);
Assert.AreEqual(@"CREATE TABLE [dbo].[destination] ([float] real NULL,
[double] float NULL,
[decimal] money NULL,
[nvarchar(50)] nvarchar(50) NULL,
[bigint] bigint NULL,
[int] int NOT NULL,
[tinyint] tinyint NULL,
[smallint] smallint NULL,
[bit] bit NULL,
[ntext] nvarchar(max) NULL,
[char] char(1) NULL,
[datetime] datetime NULL,
[guid] uniqueidentifier NULL)", res);
}
private string[] GenerateMostRestrictiveTableQueryTestHelper(string sql, bool includePrimaryKey, int top)
{
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = true;
var ss = CreateSelect(sql);
var res = new List<string>();
foreach (var qs in ss.EnumerateQuerySpecifications())
{
// TODO: use qs.SourceTableReferences
foreach (var tr in qs.EnumerateSourceTableReferences(true))
{
res.Add(cg.GenerateMostRestrictiveTableQuery(tr, includePrimaryKey, top));
}
}
return res.ToArray();
}
private string GenerateCode(string query, bool resolveAliases, bool resolveNames, bool substituteStars)
{
var ss = CreateSelect(query);
var w = new StringWriter();
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = resolveNames;
cg.Execute(w, ss);
return w.ToString();
}
/// <summary>
/// Creates the destination table
/// </summary>
/// <param name="schemaTable"></param>
protected void CreateDestinationTable(DataTable schemaTable)
{
// Generate create table SQL
var cg = new SqlServerCodeGenerator();
var sql = cg.GenerateCreateDestinationTableQuery(schemaTable, destination.Table);
// Execute CREATE TABLE query on destination
using (var cn = new SqlConnection(destination.Table.Dataset.ConnectionString))
{
cn.Open();
using (var cmd = new SqlCommand(sql, cn))
{
cmd.ExecuteNonQuery();
}
}
}
protected override string GetExecuteQueryText()
{
// strip off order by
var orderby = SelectStatement.FindDescendant<OrderByClause>();
if (orderby != null)
{
SelectStatement.Stack.Remove(orderby);
}
// strip off partition on
foreach (var qs in SelectStatement.EnumerateQuerySpecifications())
{
// strip off select into
var into = qs.FindDescendant<IntoClause>();
if (into != null)
{
qs.Stack.Remove(into);
}
foreach (var ts in qs.EnumerateDescendantsRecursive<SimpleTableSource>())
{
var pc = ts.FindDescendant<TablePartitionClause>();
if (pc != null)
{
pc.Parent.Stack.Remove(pc);
}
}
}
// Generate code
var sw = new StringWriter();
var cg = new SqlServerCodeGenerator()
{
ResolveNames = true
};
cg.Execute(sw, SelectStatement);
return sw.ToString();
}
private void AppendPartitioningConditions(QuerySpecification qs, SimpleTableSource ts)
{
if (!double.IsInfinity(PartitioningKeyFrom) || !double.IsInfinity(PartitioningKeyTo))
{
var cg = new SqlServerCodeGenerator();
string format;
if (double.IsInfinity(PartitioningKeyFrom) && double.IsInfinity(PartitioningKeyTo))
{
format = "{1} <= {0} AND {0} < {2}";
}
else if (double.IsInfinity(PartitioningKeyFrom))
{
format = "{0} < {2}";
}
else
{
format = "{1} <= {0}";
}
string sql = String.Format(format,
cg.GetResolvedColumnName(ts.PartitioningColumnReference),
PartitioningKeyFrom.ToString(System.Globalization.CultureInfo.InvariantCulture),
PartitioningKeyTo.ToString(System.Globalization.CultureInfo.InvariantCulture));
var parser = new Jhu.Graywulf.SqlParser.SqlParser();
var sc = (SearchCondition)parser.Execute(new SearchCondition(), sql);
var where = qs.FindDescendant<WhereClause>();
if (where == null)
{
where = WhereClause.Create(sc);
var ws = Whitespace.Create();
var wsn = qs.Stack.AddAfter(qs.Stack.Find(qs.FindDescendant<FromClause>()), ws);
qs.Stack.AddAfter(wsn, where);
}
else
{
where.AppendCondition(sc, "AND");
}
}
// --- remove partition clause
ts.Stack.Remove(ts.FindDescendant<TablePartitionClause>());
}
public void PrepareComputeTableStatistics(Context context, TableReference tr, out string connectionString, out string sql)
{
// Assign a database server to the query
// TODO: maybe make this function generic
// TODO: check this part to use appropriate server and database
var sm = GetSchemaManager(false);
var ds = sm.Datasets[tr.DatasetName];
if (ds is GraywulfDataset && !((GraywulfDataset)ds).IsSpecificInstanceRequired)
{
var gds = (GraywulfDataset)ds;
var dd = new DatabaseDefinition(context);
dd.Guid = gds.DatabaseDefinition.Guid;
dd.Load();
// Get a server from the scheduler
var si = new ServerInstance(Context);
si.Guid = Scheduler.GetNextServerInstance(new Guid[] { dd.Guid }, StatDatabaseVersionName, null);
si.Load();
connectionString = si.GetConnectionString().ConnectionString;
SubstituteDatabaseName(tr, si.Guid, StatDatabaseVersionName);
tr.DatabaseObject = null;
}
else
{
// Run it on the specific database
connectionString = ds.ConnectionString;
}
// Generate statistics query
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = true;
sql = cg.GenerateTableStatisticsQuery(tr);
}
public virtual string GetDestinationTableSchemaSourceQuery()
{
// strip off order by
OrderByClause orderby = SelectStatement.FindDescendant<OrderByClause>();
if (orderby != null)
{
SelectStatement.Stack.Remove(orderby);
}
// strip off partition on
foreach (QuerySpecification qs in SelectStatement.EnumerateQuerySpecifications())
{
// strip off select into
IntoClause into = qs.FindDescendant<IntoClause>();
if (into != null)
{
qs.Stack.Remove(into);
}
foreach (var ts in qs.EnumerateDescendantsRecursive<SimpleTableSource>())
{
var pc = ts.FindDescendant<TablePartitionClause>();
if (pc != null)
{
pc.Parent.Stack.Remove(pc);
}
}
}
var cg = new Jhu.Graywulf.SqlParser.SqlCodeGen.SqlServerCodeGenerator();
cg.ResolveNames = true;
var sw = new StringWriter();
cg.Execute(sw, SelectStatement);
return sw.ToString();
}
public string GenerateTableStatisticsQuery(TableReference table)
{
if (table.Statistics == null)
{
throw new InvalidOperationException();
}
// Build table specific where clause
var cnr = new SearchConditionNormalizer();
cnr.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
var wh = cnr.GenerateWhereClauseSpecificToTable(table);
var where = new StringWriter();
if (wh != null)
{
var cg = new SqlServerCodeGenerator();
cg.Execute(where, wh);
};
//*** TODO: move into resource
string sql = String.Format(@"
IF OBJECT_ID('tempdb..##keys_{4}') IS NOT NULL
DROP TABLE ##keys_{4}
SELECT CAST({2} AS float) AS __key
INTO ##keys_{4}
FROM {0} {1}
{3};
DECLARE @count bigint = @@ROWCOUNT;
DECLARE @step bigint = @count / @bincount;
IF (@step = 0) SET @step = NULL;
WITH q AS
(
SELECT __key, ROW_NUMBER() OVER (ORDER BY __key) __rn
FROM ##keys_{4}
)
SELECT __key, __rn
FROM q
WHERE __rn % @step = 1 OR __rn = @count;
DROP TABLE ##keys_{4};
",
GetResolvedTableName(table),
table.Alias == null ? "" : String.Format(" AS {0} ", QuoteIdentifier(table.Alias)),
QuoteIdentifier(table.Statistics.KeyColumn),
where.ToString(),
Guid.NewGuid().ToString().Replace('-', '_'));
return sql;
}
private string[] GetWhereClauses(string query)
{
SearchConditionNormalizer cn = new SearchConditionNormalizer();
var select = CreateSelect(query);
var res = new List<string>();
foreach (var qs in select.EnumerateQuerySpecifications())
{
cn.NormalizeQuerySpecification(qs);
// TODO use qs.SourceTableReferences ???
foreach (var tr in qs.EnumerateSourceTableReferences(true))
{
WhereClause where = cn.GenerateWhereClauseSpecificToTable(tr);
if (where != null)
{
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = true;
var sw = new StringWriter();
cg.Execute(sw, where);
res.Add(sw.ToString());
}
else
{
res.Add("");
}
}
}
return res.ToArray();
}
private void RenderResults(JobInstance ji)
{
var q = (QueryBase)ji.Parameters["Query"].Value;
var codegen = new SqlServerCodeGenerator();
string sql = codegen.GenerateSelectStarQuery(q.Destination.GetTable(), 100);
using (var cn = new SqlConnection())
{
cn.ConnectionString = MyDBDataset.ConnectionString;
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
using (var dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
RenderTable(dr);
}
}
}
}
/// <summary>
/// Executest bulk copy to ingest data from the DataReader
/// </summary>
/// <param name="dr"></param>
protected void ExecuteBulkCopy(IDataReader dr, Table destination)
{
// TODO: it can only import the first resultset from dr
var cg = new SqlServerCodeGenerator();
isBulkCopyCanceled = false;
bulkCopyFinishedEvent = new AutoResetEvent(false);
// Initialize bulk copy
var sbc = new System.Data.SqlClient.SqlBulkCopy(destination.Dataset.ConnectionString);
sbc.DestinationTableName = cg.GetResolvedTableName(destination);
sbc.BatchSize = batchSize;
sbc.BulkCopyTimeout = timeout;
// Initialize events
sbc.NotifyAfter = batchSize;
sbc.SqlRowsCopied += delegate(object sender, SqlRowsCopiedEventArgs e)
{
//RowsAffected = e.RowsCopied; // TODO: delete if not used
e.Abort = isBulkCopyCanceled;
};
try
{
sbc.WriteToServer(dr);
}
finally
{
bulkCopyFinishedEvent.Set();
}
}
protected override string GetOutputSelectQuery()
{
// Generate code
var sw = new StringWriter();
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = true;
cg.Execute(sw, SelectStatement);
return sw.ToString();
}