/// <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);
        }
Esempio n. 2
0
        /// <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)}");
        }
Esempio n. 3
0
        /// <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");
        }
Esempio n. 4
0
        /// <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}");
        }
Esempio n. 9
0
        /// <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});");
        }
Esempio n. 10
0
        /// <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);
        }
Esempio n. 11
0
        /// <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");
        }
Esempio n. 12
0
        /// <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");
        }
Esempio n. 13
0
        /// <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");
        }
Esempio n. 14
0
        /// <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)}");
        }
Esempio n. 15
0
        /// <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);
        }
Esempio n. 16
0
        /// <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}");
        }
Esempio n. 17
0
        /// <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";
        }