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