//Modified paging code from https://github.com/markrendle/Simple.Data/blob/master/Simple.Data.SqlServer/SqlQueryPager.cs
protected override string ApplyPaging(string sql)
{
if (!Rows.HasValue)
{
return(sql);
}
if (!Skip.HasValue)
{
Skip = 0;
}
//SEE broken comment in: ToSelectStatement()
if (Skip == 0)
{
return(sql); //short-circuit for only Take queries
}
var builder = new StringBuilder("WITH __Data AS (SELECT ");
var match = ColumnExtract.Match(sql);
var columns = match.Groups[1].Value.Trim();
var fromEtc = match.Groups[2].Value.Trim();
builder.Append(columns);
var orderBy = ExtractOrderBy(columns, ref fromEtc);
builder.AppendFormat(", ROW_NUMBER() OVER({0}) AS [_#_]", orderBy);
builder.AppendLine();
builder.Append(fromEtc);
builder.AppendLine(")");
builder.AppendFormat("SELECT {0} FROM __Data WHERE [_#_] BETWEEN {1} AND {2}", DequalifyColumns(columns),
Skip.Value + 1, Skip.Value + Rows.Value);
return(builder.ToString());
}
/// This method gets called once for each cmdlet in the pipeline when the pipeline starts executing
protected override void BeginProcessing()
{
skipPattern = new Regex(SkipLinePattern, RegexOptions.Compiled);
if (!Fields.Contains("Timestamp"))
{
throw new ArgumentException("The specified field map does not contain field 'Timestamp' which is required");
}
this.lineSplitter = new Regex(SplitBy, RegexOptions.Compiled);
if (!Fields.EndsWith(";"))
{
Fields = $"{Fields};";
}
foreach (Match field in Regex.Matches(Fields, "(?<index>.*?):(?<column>.*?);"))
{
fieldMap.Add(field.Groups["column"].Value, Int32.Parse(field.Groups["index"].Value));
}
if (!String.IsNullOrEmpty(SubColumns))
{
if (!SubColumns.EndsWith(";;"))
{
SubColumns = $"{SubColumns};;";
}
var subcol = new Regex("(?<column>.*?):(?<subcolumns>.*?);;");
if (!subcol.IsMatch(SubColumns))
{
throw new ArgumentException("Subcolumn definition is incorrect, refer to help for proper format");
}
foreach (Match col in subcol.Matches(SubColumns))
{
if (!fieldMap.ContainsKey(col.Groups["column"].Value))
{
throw new ArgumentException($"Error - {col.Groups["column"].Value} can't be used in subcolumns without it being in fieldsmap");
}
var subcolumn = new SubColumnsConf(col.Groups["column"].Value, col.Groups["subcolumns"].Value);
foreach (var c in subcolumn.SubColumns)
{
if (fieldMap.ContainsKey(c) && fieldMap[c] < 0)
{
throw new ArgumentException($"Error - SubColumn {c} is defined more than once!");
}
else if (!fieldMap.ContainsKey(c))
{
fieldMap.Add(c, -1);
}
}
subColumnCollection.Add(subcolumn);
}
}
if (!AggregatedColumns.EndsWith(";"))
{
AggregatedColumns = $"{AggregatedColumns};";
}
foreach (Match clmn in Regex.Matches(AggregatedColumns, "(?<column>.*?):(?<aggregate>.*?);"))
{
if (!fieldMap.ContainsKey(clmn.Groups["column"].Value))
{
throw new ArgumentException($"Error - {clmn.Groups["column"].Value} can't be used in aggregate without it being in fieldsmap");
}
if (!aggregateMap.ContainsKey(clmn.Groups["column"].Value))
{
aggregateMap.Add(clmn.Groups["column"].Value, null);
}
if (aggregateMap[clmn.Groups["column"].Value] == null)
{
aggregateMap[clmn.Groups["column"].Value] = new List <AggregatedColumn>();
}
aggregateMap[clmn.Groups["column"].Value].Add(new AggregatedColumn(clmn.Groups["column"].Value, clmn.Groups["aggregate"].Value));
}
if (!String.IsNullOrEmpty(FilterBy))
{
if (!FilterBy.EndsWith(";"))
{
FilterBy = $"{FilterBy};";
}
foreach (Match clmn in Regex.Matches(FilterBy, "(?<column>.*?):(?<filter>.*?);"))
{
if (!fieldMap.ContainsKey(clmn.Groups["column"].Value))
{
throw new ArgumentException($"Error - {clmn.Groups["column"].Value} can't be used in filter without it being in fieldsmap");
}
columnFilters.Add(new ColumnFilterConfiguration(
clmn.Groups["column"].Value,
clmn.Groups["filter"].Value,
fieldMap[clmn.Groups["column"].Value]));
}
}
var timeparts = lineSplitter.Split(InputTimeFormat);
if (timeparts.Count() > 1)
{
if (!fieldMap.ContainsKey("Date"))
{
throw new ArgumentException("The specified field map does not contain field 'Date' which is required when the -inputTimeFormat spread across two columns");
}
var min = Math.Min(fieldMap["Timestamp"], fieldMap["Date"]);
InputTimeFormat = timeparts[fieldMap["Timestamp"] - min];
inputDateFormat = timeparts[fieldMap["Date"] - min];
}
if (!String.IsNullOrEmpty(CalculatedColumns))
{
if (!CalculatedColumns.EndsWith(";"))
{
CalculatedColumns = $"{CalculatedColumns};";
}
foreach (Match clmn in Regex.Matches(CalculatedColumns, "(?<column>.*?)=(?<formula>.*?);"))
{
calculatedColumns.Add(new CalculatedColumn(
clmn.Groups["column"].Value,
clmn.Groups["formula"].Value));
}
}
if (!String.IsNullOrEmpty(GroupedColumns))
{
if (!GroupedColumns.EndsWith(";"))
{
GroupedColumns = $"{GroupedColumns};";
}
foreach (Match clmn in Regex.Matches(GroupedColumns, "(?<column>.*?);"))
{
if (!fieldMap.ContainsKey(clmn.Groups["column"].Value))
{
throw new ArgumentException($"Error - {clmn.Groups["column"].Value} can't be used in GroupBy without it being in fieldsmap");
}
groupByColumns.Add(clmn.Groups["column"].Value);
}
}
if (!String.IsNullOrEmpty(ColumnExtract))
{
if (!ColumnExtract.EndsWith(";;"))
{
ColumnExtract = $"{ColumnExtract};;";
}
foreach (Match clmn in Regex.Matches(ColumnExtract, "(?<column>.*?):(?<filter>.*?);;"))
{
if (!fieldMap.ContainsKey(clmn.Groups["column"].Value))
{
throw new ArgumentException($"Error - {clmn.Groups["column"].Value} can't be used in ColumnExtract without it being in fieldsmap");
}
columnExtracts.Add(new ColumnFilterConfiguration(
clmn.Groups["column"].Value,
clmn.Groups["filter"].Value,
fieldMap[clmn.Groups["column"].Value]));
}
}
}
