/// <summary>
/// Composes a source query for a remote table
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public SourceTableQuery PrepareCopyRemoteTable(TableReference table)
{
// -- Load schema
var sm = this.GetSchemaManager(false);
var ds = sm.Datasets[table.DatasetName];
// Graywulf dataset has to be converted to prevent registry access
if (ds is GraywulfDataset)
{
ds = new SqlServerDataset(ds);
}
// --- Generate most restrictive query
// Find the query specification this table belongs to
var qs = ((TableSource)table.Node).QuerySpecification;
// Run the normalizer to convert where clause to a normal form
var cnr = new SearchConditionNormalizer();
cnr.NormalizeQuerySpecification(qs);
var cg = SqlCodeGeneratorFactory.CreateCodeGenerator(ds);
var sql = cg.GenerateMostRestrictiveTableQuery(table, true, 0);
return(new SourceTableQuery()
{
Dataset = ds,
Query = 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());
}
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 override string GenerateMostRestrictiveTableQuery(TableReference table, bool includePrimaryKey, int top)
{
// Normalize search conditions and extract where clause
var cn = new SearchConditionNormalizer();
cn.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
var where = cn.GenerateWhereClauseSpecificToTable(table);
// Build table specific query
var sql = new StringWriter();
sql.Write("SELECT ");
// Now write the referenced columns
int q = 0;
foreach (var cr in table.ColumnReferences.Where(c => c.IsReferenced))
{
if (q != 0)
{
sql.Write(", ");
}
if (cr.DataType.IsInteger)
{
// Here a cast to a type that is accepted by SQL Server has to be made
sql.Write("CAST({0} AS SIGNED) AS {1}", GetResolvedColumnName(cr), QuoteIdentifier(cr.ColumnName));
}
else
{
sql.Write(GetResolvedColumnName(cr));
}
q++;
}
// From cluse
sql.Write(" FROM {0} ", GetResolvedTableName(table));
if (table.Alias != null)
{
sql.Write("AS {0} ", QuoteIdentifier(table.Alias));
}
if (where != null)
{
Execute(sql, where);
}
if (top > 0)
{
sql.Write(" LIMIT {0} ", top);
}
return(sql.ToString());
}
public override string GenerateMostRestrictiveTableQuery(TableReference table, bool includePrimaryKey, int top)
{
// Normalize search conditions and extract where clause
var cn = new SearchConditionNormalizer();
cn.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
var where = cn.GenerateWhereClauseSpecificToTable(table);
// Build table specific query
var sql = new StringWriter();
sql.Write("SELECT ");
// Now write the referenced columns
int q = 0;
foreach (var cr in table.ColumnReferences.Where(c => c.IsReferenced))
{
if (q != 0)
{
sql.Write(", ");
}
if (cr.DataType.IsInteger)
{
// Here a cast to a type that is accepted by SQL Server has to be made
sql.Write("CAST(`{0}` AS SIGNED) AS `{0}`", cr.ColumnName);
}
else
{
sql.Write("`{0}`", cr.ColumnName);
}
q++;
}
// From cluse
sql.Write(" FROM `{0}`", table.DatabaseObjectName);
if (table.Alias != null)
{
sql.Write(" `{0}`", table.Alias);
}
sql.Write(" ");
if (where != null)
{
Execute(sql, where);
}
if (top > 0)
{
sql.Write(" LIMIT {0} ", top);
}
return sql.ToString();
}
public override string GenerateMostRestrictiveTableQuery(TableReference table, bool includePrimaryKey, int top)
{
// Normalize search conditions and extract where clause
var cn = new SearchConditionNormalizer();
cn.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
var where = cn.GenerateWhereClauseSpecificToTable(table);
// Build table specific query
var sql = new StringWriter();
sql.Write("SELECT ");
// Now write the referenced columns
int q = 0;
foreach (var cr in table.ColumnReferences.Where(c => c.IsReferenced))
{
if (q != 0)
{
sql.Write(", ");
}
sql.Write("{0}", QuoteIdentifier(cr.ColumnName));
q++;
}
// From cluse
sql.Write(" FROM {0}", QuoteIdentifier(table.DatabaseObjectName));
if (table.Alias != null)
{
sql.Write(" {0}", QuoteIdentifier(table.Alias));
}
sql.Write(" ");
if (where != null)
{
Execute(sql, where);
}
if (top > 0)
{
sql.Write(" LIMIT {0} ", top);
}
return(sql.ToString());
}
public override string GenerateMostRestrictiveTableQuery(TableReference table, bool includePrimaryKey, int top)
{
// Normalize search conditions and extract where clause
var cn = new SearchConditionNormalizer();
cn.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
var where = cn.GenerateWhereClauseSpecificToTable(table);
// Build table specific query
var sql = new StringWriter();
sql.Write("SELECT ");
// Now write the referenced columns
int q = 0;
foreach (var cr in table.ColumnReferences.Where(c => c.IsReferenced))
{
if (q != 0)
{
sql.Write(", ");
}
sql.Write("{0}", QuoteIdentifier(cr.ColumnName));
q++;
}
// From cluse
sql.Write(" FROM {0}", QuoteIdentifier(table.DatabaseObjectName));
if (table.Alias != null)
{
sql.Write(" {0}", QuoteIdentifier(table.Alias));
}
sql.Write(" ");
if (where != null)
{
Execute(sql, where);
}
if (top > 0)
{
sql.Write(" LIMIT {0} ", top);
}
return sql.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 override string GenerateMostRestrictiveTableQuery(TableReference table, bool includePrimaryKey, int top)
{
// Normalize search conditions and extract where clause
var cn = new SearchConditionNormalizer();
cn.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
var where = cn.GenerateWhereClauseSpecificToTable(table);
// Build table specific query
var sql = new StringWriter();
sql.Write("SELECT ");
if (top > 0)
{
sql.Write("TOP {0} ", top);
}
// Now write the referenced columns
var referencedcolumns = new HashSet<string>(Jhu.Graywulf.Schema.SqlServer.SqlServerSchemaManager.Comparer);
int q = 0;
if (includePrimaryKey)
{
var t = table.DatabaseObject as Jhu.Graywulf.Schema.Table;
foreach (var cr in t.PrimaryKey.Columns.Values)
{
var columnname = String.Format(
"{0}.{1}",
QuoteIdentifier(table.Alias),
QuoteIdentifier(cr.ColumnName));
if (!referencedcolumns.Contains(columnname))
{
if (q != 0)
{
sql.Write(", ");
}
sql.Write(columnname);
q++;
referencedcolumns.Add(columnname);
}
}
}
foreach (var cr in table.ColumnReferences.Where(c => c.IsReferenced))
{
var columnname = GetResolvedColumnName(cr); // TODO: verify
if (!referencedcolumns.Contains(columnname))
{
if (q != 0)
{
sql.Write(", ");
}
sql.Write(columnname);
q++;
referencedcolumns.Add(columnname);
}
}
// From cluse
sql.Write(" FROM {0} ", GetResolvedTableName(table));
if (!String.IsNullOrWhiteSpace(table.Alias))
{
sql.Write("AS {0} ", QuoteIdentifier(table.Alias));
}
if (where != null)
{
Execute(sql, where);
}
return sql.ToString();
}
public override string GenerateMostRestrictiveTableQuery(TableReference table, bool includePrimaryKey, int top)
{
// Normalize search conditions and extract where clause
var cn = new SearchConditionNormalizer();
cn.NormalizeQuerySpecification(((TableSource)table.Node).QuerySpecification);
var where = cn.GenerateWhereClauseSpecificToTable(table);
// Build table specific query
var sql = new StringWriter();
sql.Write("SELECT ");
if (top > 0)
{
sql.Write("TOP {0} ", top);
}
// Now write the referenced columns
var referencedcolumns = new HashSet <string>(Jhu.Graywulf.Schema.SqlServer.SqlServerSchemaManager.Comparer);
int q = 0;
if (includePrimaryKey)
{
var t = table.DatabaseObject as Jhu.Graywulf.Schema.Table;
foreach (var cr in t.PrimaryKey.Columns.Values)
{
var columnname = String.Format(
"{0}.{1}",
QuoteIdentifier(table.Alias),
QuoteIdentifier(cr.ColumnName));
if (!referencedcolumns.Contains(columnname))
{
if (q != 0)
{
sql.Write(", ");
}
sql.Write(columnname);
q++;
referencedcolumns.Add(columnname);
}
}
}
foreach (var cr in table.ColumnReferences.Where(c => c.IsReferenced))
{
var columnname = GetResolvedColumnName(cr); // TODO: verify
if (!referencedcolumns.Contains(columnname))
{
if (q != 0)
{
sql.Write(", ");
}
sql.Write(columnname);
q++;
referencedcolumns.Add(columnname);
}
}
// From cluse
sql.Write(" FROM {0} ", GetResolvedTableName(table));
if (!String.IsNullOrWhiteSpace(table.Alias))
{
sql.Write("AS {0} ", QuoteIdentifier(table.Alias));
}
if (where != null)
{
Execute(sql, where);
}
return(sql.ToString());
}