/// <summary> /// Default implementation of a Get Query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <param name="cache">true if this query should be cached</param> /// <returns>A sql statement that selects a single item</returns> public virtual string GetQuery(TableInfo tableInfo, string sql, bool cache = false) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!q.IsSelect) { return(GetQueries.Acquire( tableInfo.ClassType.TypeHandle, () => cache && string.IsNullOrEmpty(q.Sql), () => { var wc = string.IsNullOrWhiteSpace(q.Sql) ? $"where {EscapeWhereList(tableInfo.KeyColumns)}" : q.Sql; if (string.IsNullOrEmpty(q.FromClause)) { return $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from { EscapeTableName(tableInfo)} {wc}"; } else { return $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {wc}"; } } )); } return(q.Sql); }
/// <summary> /// Constructs a paged sql statement /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="page">the page to request</param> /// <param name="pageSize">the size of the page to request</param> /// <param name="sql">a sql statement or partial statement</param> /// <param name="parameters">the dynamic parameters for the query</param> /// <returns>A paginated sql statement</returns> public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql, DynamicParameters parameters) { var q = new SqlParser(GetListQuery(tableInfo, sql)); var pageSkip = (page - 1) * pageSize; var pageTake = pageSkip + pageSize; var sqlOrderBy = "order by (select null)"; var sqlOrderByRemoved = q.Sql; if (string.IsNullOrEmpty(q.OrderByClause)) { if (tableInfo.KeyColumns.Any()) { sqlOrderBy = $"order by {EscapeColumn(tableInfo.KeyColumns.First().PropertyName)}"; } } else { sqlOrderBy = q.OrderByClause; sqlOrderByRemoved = sqlOrderByRemoved.Replace(q.OrderByClause, ""); } var columnsOnly = $"page_inner.* FROM ({sqlOrderByRemoved}) page_inner"; parameters.Add(PageSizeParamName, pageTake, DbType.Int64); parameters.Add(PageSkipParamName, pageSkip, DbType.Int64); return ($"select * from (select row_number() over ({sqlOrderBy}) page_rn, {columnsOnly}) page_outer where page_rn > {EscapeParameter(PageSkipParamName)} and page_rn <= {EscapeParameter(PageSizeParamName)}"); }
/// <summary> /// Default implementation of a delete query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement that deletes</returns> public virtual string DeleteQuery(TableInfo tableInfo, string sql) { var q = new SqlParser(sql ?? string.Empty); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } //Remove any order by statements if (!string.IsNullOrEmpty(q.OrderByClause)) { q.Sql = q.Sql.Replace(q.OrderByClause, string.Empty); q.OrderByClause = string.Empty; } //Partial statement passed in if (!q.IsDelete) { return(string.IsNullOrEmpty(q.FromClause) ? $"delete from {EscapeTableName(tableInfo)} {q.Sql}" : $"delete {q.Sql}"); } return($"delete from ({q.Sql}) calc_inner"); }
/// <summary> /// Default implementation of a count query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement that selects the count of matching records</returns> public virtual string CountQuery(TableInfo tableInfo, string sql) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!string.IsNullOrEmpty(q.OrderByClause)) { q.Sql = q.Sql.Replace(q.OrderByClause, ""); } if (!q.IsSelect) { if (string.IsNullOrEmpty(q.FromClause)) { return($"select count(*) from {EscapeTableName(tableInfo)} {q.Sql}"); } return($"select count(*) {q.Sql}"); } return($"select count(*) from ({q.Sql}) count_inner"); }
/// <summary> /// Default implementation of an Exists query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement that selects true if a record matches</returns> public override string ExistsQuery(TableInfo tableInfo, string sql) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!q.IsSelect) { var wc = string.IsNullOrWhiteSpace(q.Sql) ? $"where {EscapeWhereList(tableInfo.KeyColumns)}" : q.Sql; if (string.IsNullOrEmpty(q.FromClause)) { return($"select exists (select * from { EscapeTableName(tableInfo)} {wc})"); } else { return($"select exists (select * {wc})"); } } return($"select exists ({q.Sql})"); }
/// <summary> /// Default implementation of a delete query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement that deletes</returns> public virtual string DeleteQuery(TableInfo tableInfo, string sql) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!string.IsNullOrEmpty(q.OrderByClause)) { q.Sql = q.Sql.Replace(q.OrderByClause, ""); } if (!q.IsDelete) { if (string.IsNullOrEmpty(q.FromClause)) { return($"delete from { EscapeTableName(tableInfo)} {q.Sql}"); } else { return($"delete {q.Sql}"); } } return($"delete from ({q.Sql}) calc_inner"); }
/// <summary> /// Default implementation of a a paged sql statement /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="page">the page to request</param> /// <param name="pageSize">the size of the page to request</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A paginated sql statement</returns> public virtual string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql) { var q = new SqlParser(GetListQuery(tableInfo, sql)); var pageSkip = (page - 1) * pageSize; var sqlOrderBy = string.Empty; if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any()) { sqlOrderBy = $"order by {EscapeColumnn(tableInfo.KeyColumns.First().ColumnName)}"; } return($"{q.Sql} {sqlOrderBy} limit {pageSize} offset {pageSkip}"); }
/// <summary> /// Default implementation of a a paged sql statement /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="page">the page to request</param> /// <param name="pageSize">the size of the page to request</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A paginated sql statement</returns> public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql) { var q = new SqlParser(GetListQuery(tableInfo, sql)); var pageSkip = (page - 1) * pageSize; var sqlOrderBy = string.Empty; if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any()) { sqlOrderBy = $"order by {EscapeColumnn(tableInfo.KeyColumns.First().ColumnName)}"; } return($"select first {pageSize} skip {pageSkip} * from ({q.Sql}) page_inner {sqlOrderBy}"); }
/// <summary> /// Default implementation of an Exists query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement that selects true if a record matches</returns> public override string ExistsQuery(TableInfo tableInfo, string sql) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!q.IsSelect) { return(string.IsNullOrEmpty(q.FromClause) ? $"select first 1 1 from {EscapeTableName(tableInfo)} where exists (select 1 from {EscapeTableName(tableInfo)} {q.Sql});" : $"select first 1 1 from {EscapeTableName(tableInfo)} where exists (select 1 {q.Sql});"); } return($"select first 1 1 from {EscapeTableName(tableInfo)} where exists ({q.Sql});"); }
/// <summary> /// Default implementation of a Get List query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement</returns> public virtual string GetListQuery(TableInfo tableInfo, string sql) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!q.IsSelect) { return(string.IsNullOrEmpty(q.FromClause) ? $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from {EscapeTableName(tableInfo)} {q.Sql}" : $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {q.Sql}"); } return(q.Sql); }
/// <summary> /// Default implementation of an Exists query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement that selects true if a record matches</returns> public virtual string ExistsQuery(TableInfo tableInfo, string sql) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!q.IsSelect) { return(string.IsNullOrEmpty(q.FromClause) ? $"select 1 where exists (select 1 from {EscapeTableName(tableInfo)} {q.Sql})" : $"select 1 where exists (select 1 {q.Sql})"); } return($"select 1 from ({q.Sql}) calc_inner"); }
/// <inheritdoc /> public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql, DynamicParameters parameters) { var sqlParser = new SqlParser(GetListQuery(tableInfo, sql)); var pageSkip = (page - 1) * pageSize; var sqlOrderBy = string.Empty; if (string.IsNullOrEmpty(sqlParser.OrderByClause) && tableInfo.KeyColumns.Any()) { sqlOrderBy = $"order by {EscapeColumn(tableInfo.KeyColumns.First().PropertyName)}"; } parameters.Add(PageSizeParamName, pageSize, DbType.Int64); parameters.Add(PageSkipParamName, pageSkip, DbType.Int64); return ($"{sqlParser.Sql} {sqlOrderBy} offset {EscapeParameter(PageSkipParamName)} rows fetch next {EscapeParameter(PageSizeParamName)} rows only"); }
/// <summary> /// Oracle-specific implementation of an Exists query. /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement that selects true if a record matches</returns> public override string ExistsQuery(TableInfo tableInfo, string sql) { var sqlParser = new SqlParser(sql ?? ""); if (sqlParser.Sql.StartsWith(";", StringComparison.Ordinal)) { return(sqlParser.Sql.Substring(1)); } if (!sqlParser.IsSelect) { return(string.IsNullOrEmpty(sqlParser.FromClause) ? $"select case when exists (select * from {EscapeTableName(tableInfo)} {sqlParser.Sql}) then 1 else 0 end as rec_exists from dual" : $"select case when exists (select * {sqlParser.Sql}) then 1 else 0 end as rec_exists from dual"); } return($"select case when exists ({sqlParser.Sql}) then 1 else 0 end as rec_exists from dual"); }
/// <summary> /// Default implementation of a a paged sql statement /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="page">the page to request</param> /// <param name="pageSize">the size of the page to request</param> /// <param name="sql">a sql statement or partial statement</param> /// <param name="parameters">the dynamic parameters for the query</param> /// <returns>A paginated sql statement</returns> /// <remarks> /// Base implementation does not modify <paramref name="parameters" />. /// </remarks> public virtual string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql, DynamicParameters parameters) { var q = new SqlParser(GetListQuery(tableInfo, sql)); var pageSkip = (page - 1) * pageSize; var sqlOrderBy = string.Empty; if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any()) { sqlOrderBy = $"order by {EscapeColumn(tableInfo.KeyColumns.First().ColumnName)}"; } parameters.Add(PageSizeParamName, pageSize, DbType.Int64); parameters.Add(PageSkipParamName, pageSkip, DbType.Int64); return ($"{q.Sql} {sqlOrderBy} limit {EscapeParameter(PageSizeParamName)} offset {EscapeParameter(PageSkipParamName)}"); }
/// <summary> /// Default implementation of a Get Query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <param name="cache">true if this query should be cached</param> /// <returns>A sql statement that selects a single item</returns> public virtual string GetQuery(TableInfo tableInfo, string sql, bool cache = false) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!q.IsSelect) { return(GetQueries.Acquire( tableInfo.ClassType.TypeHandle, () => cache && string.IsNullOrEmpty(q.Sql), () => string.IsNullOrEmpty(q.FromClause) ? $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from {EscapeTableName(tableInfo)} {q.Sql}" : $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {q.Sql}")); } return(q.Sql); }
/// <summary> /// Default implementation of a a paged sql statement /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="page">the page to request</param> /// <param name="pageSize">the size of the page to request</param> /// <param name="sql">a sql statement or partial statement</param> /// <param name="parameters">the dynamic parameters for the query</param> /// <returns>A paginated sql statement</returns> public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql, DynamicParameters parameters) { var q = new SqlParser(GetListQuery(tableInfo, sql)); var pageSkip = (page - 1) * pageSize; var sqlOrderBy = string.Empty; if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any()) { sqlOrderBy = $"order by {EscapeColumn(tableInfo.KeyColumns.First().PropertyName)}"; } parameters.Add(PageSizeParamName, pageSize, DbType.Int64); parameters.Add(PageSkipParamName, pageSkip, DbType.Int64); return ($"select first {EscapeParameter(PageSizeParamName)} skip {EscapeParameter(PageSkipParamName)} * from ({q.Sql}) page_inner {sqlOrderBy}"); }
/// <summary> /// Default implementation of a Get List query /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A sql statement</returns> public virtual string GetListQuery(TableInfo tableInfo, string sql) { var q = new SqlParser(sql ?? ""); if (q.Sql.StartsWith(";")) { return(q.Sql.Substring(1)); } if (!q.IsSelect) { var wc = string.IsNullOrWhiteSpace(q.Sql) ? $"where {EscapeWhereList(tableInfo.KeyColumns)}" : q.Sql; if (string.IsNullOrEmpty(q.FromClause)) { return($"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from { EscapeTableName(tableInfo)} {wc}"); } else { return($"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {wc}"); } } return(q.Sql); //var q = sql ?? ""; //if (q.StartsWith(";")) // return q.Substring(1); //if (!rxSelect.IsMatch(q)) //{ // var wc = string.IsNullOrWhiteSpace(q) ? $"where {EscapeWhereList(tableInfo.KeyColumns)}" : q; // if (!rxFrom.IsMatch(q)) // return $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} from { EscapeTableName(tableInfo)} {wc}"; // else // return $"select {EscapeColumnListWithAliases(tableInfo.SelectColumns, tableInfo.TableName)} {wc}"; //} //return sql; }
/// <summary> /// Constructs a paged sql statement /// </summary> /// <param name="tableInfo">table information about the entity</param> /// <param name="page">the page to request</param> /// <param name="pageSize">the size of the page to request</param> /// <param name="sql">a sql statement or partial statement</param> /// <returns>A paginated sql statement</returns> public override string GetPageListQuery(TableInfo tableInfo, long page, long pageSize, string sql) { var q = new SqlParser(GetListQuery(tableInfo, sql)); var pageSkip = (page - 1) * pageSize; var sqlOrderBy = string.Empty; if (string.IsNullOrEmpty(q.OrderByClause) && tableInfo.KeyColumns.Any()) { sqlOrderBy = $"order by {EscapeColumnn(tableInfo.KeyColumns.First().ColumnName)}"; } //return $"{q.Sql} {sqlOrderBy} limit {pageSize} offset {pageSkip}"; return($"{q.Sql} {sqlOrderBy} offset {pageSkip} rows fetch next {pageSize} rows only"); //var selectQuery = GetListQuery(tableInfo, sql); //var m = rxColumns.Match(selectQuery); //var g = m.Groups[1]; //var sqlSelectRemoved = selectQuery.Substring(g.Index); //var sqlOrderBy = string.Empty; //var pageSkip = (page - 1) * pageSize; //m = rxOrderBy.Match(selectQuery); //if (m.Success) //{ // g = m.Groups[0]; // sqlOrderBy = g.ToString(); //} //else if (tableInfo.KeyColumns.Any()) //{ // sqlOrderBy = $"order by {EscapeColumnn(tableInfo.KeyColumns.First().ColumnName)}"; //} //return $"select {rxOrderBy.Replace(sqlSelectRemoved, "", 1)} {sqlOrderBy} offset {pageSkip} rows fetch next {pageSize} rows only"; }