flepper / flepper

Flepper is a library to aid in database interaction. :frog:

Branch	status(windows)	status(unix)	coverage
Master
Development

A SQL query builder that is flexible and fun to use!

Flepper is a library to facilitate some database interactions that we need to perform in our applications. The initial idea of Flepper is to provide a query builder for writing SQL queries fluently, improving the readability of your code.

Installing / Getting started

A quick introduction of the minimal setup you need to get a hello world up & running.

Package Manager

Install-Package Flepper.QueryBuilder

.NET CLI

dotnet add package Flepper.QueryBuilder

After executing one of the commands above you will have the Flepper installed and ready to be used.

More about

Access Flepper Page to learn more.

Developing

Here's a brief intro about what a developer must do in order to start developing the project further:

git clone https://github.com/Flepper/flepper
cd flepper/
dotnet restore
dotnet build

Features

Functionality implemented

• Query Builder

Database support

Currently supported databases

• MSSQL 2008 or greater

Contributing

Hey, are you in the mood to contribute to Flepper? Then take a look at our file where it explains how to contribute and we love receiving feedback and pull requests.

Licensing

"The code in this project is licensed under MIT license."

flepper's Issues

Conflicting code

Expected Behavior

internal abstract class BaseQueryBuilder : IQueryCommand
{
        protected SqlColumn[] columns;

or change name Columns

Actual Behavior

internal abstract class BaseQueryBuilder : IQueryCommand
{
        protected SqlColumn[] Columns;

Steps to Reproduce the Problem

Severity Code Description Project File Line Suppression State
Warning CS0108 'InsertCommand.Columns(params SqlColumn[])' hides inherited member 'BaseQueryBuilder.Columns'. Use the new keyword if hiding was intended. Flepper.QueryBuilder C:\Temp\Flepper\netdragoon\flepper\Flepper.QueryBuilder\Commands\InsertCommand.cs 15 Active

Continuous Integration

Create a continuous integration process to run unit tests automatically and ensure code integration.

Replace Insert by Insert().Into()

as per consistency (Select().From("tableName")) the same should happen for Insert

Insert().Into("tableName").values("...")

Bug table alias and sql functions

Expected Behavior

SELECT MIN([ALIAS].[COLUMN]) AS ALIASCOLUMN FROM [TABLE] ALIAS

Actual Behavior

SELECT [ALIAS].MIN([COLUMN]) AS ALIASCOLUMN FROM [TABLE] ALIAS

Steps to Reproduce the Problem

1. use alias table with sql function

Integrate with Jquery Builder UI

Any chance we get this to work with https://querybuilder.js.org/

Create a builder to do a update operation

Create a builder to delete operation

SELECT JOIN With Multi level condition

Expected Behavior

Should can create SELECT with JOIN Statement and multiple WHERE level.

Actual Behavior

When create a SELECT with JOIN statement is just possible apply one WHERE statement level because AND and OR statement don't have overload to declare table alias.

Example

FlepperQueryBuilder
    .Select("Name","Age","PostalCode")
    .From("User").As("usr")
    .LeftJoin("Address").As("adr")
    .On("usr","AddressId").EqualTo("adr","Id")
    .Where("usr","Age").GreatherThan(18)
    .And("adr","State").EqualTo("SP")
    .Or("adr","State").EqualTo("SC")
    .Build();

No Order By and No Alias With As Method

No OrderBy (OrderByDescending) and no alias with As Method !

Expected Behavior

FlepperQueryBuilder
                .Select()
                .From("User").As("u")
                .OrderBy("Id","u");

Actual Behavior

Problem: without OrderBy with method As and OrderBy without alias:

FlepperQueryBuilder
                .Select()
                .From("User").As("u") // problem: without OrderBy com method "As"
                .OrderBy("Id") // No OrderBy And No Alias # bug

Cannot pass an array of column names to Columns method

Expected Behavior

It should be possible to pass an array of column names to Columns method while inserting a row.

Actual Behavior

Passing an array is not possible:

Argument 1: cannot convert from 'string[]' to 'Flepper.QueryBuilder.SqlColumn'

and SqlColumn constructor is internal so it's not possible to create an array of that too.

Steps to Reproduce the Problem

Create an array of column names and pass it to the Columns method:

var columnNames = new[] {"Column1", "Column2", "Column3"};

Connection.Insert().Into("Table1").Columns(columnNames).Values("Value1", "Value2", "Value3").Execute();

SQL Command Enhancement

Expected Behavior

Make improvements in Select Command to add some methods such as:

• OrderBy
• GroupBy
• Count
• Distinct
• Max
• Min

Actual Behavior

Functionality not implemented

It is not possible to perform multiple joins in a query

Expected Behavior

Must allow more than one join in the same query.

Actual Behavior

Feature not supported

No LIKE ???

Expected Behavior

FlepperQueryBuilder.Select().From("table").Where("field").Contains("value");
FlepperQueryBuilder.Select().From("table").Where("field").StartsWith("value");
FlepperQueryBuilder.Select().From("table").Where("field").EndsWith("value");

Actual Behavior

Does not exist !!!!

Steps to Reproduce the Problem

1. Like Filter is not present and does not generate the expected SQL

Create Column Names Using Generics

What about an extension method to create the column names to be used on Select Command using Generics?

I was wondering if could be somethng like this!

public static ISelectCommand Select<T>() where T : class
{
       // code
}

With some reflection we can get all properties of DTO and build the column names!

Column Alias

Expected Behavior

Create an extension that is possible define an alias to a column.

Example:
Select().Column("UserName","usr").Column("UserEmail","email").From("User");

Actual Behavior

Feature not implemented.

Query Interceptors

Expected Behavior

It would be interesting if we could have some way of intercepting the execution of Query in the same style we have with interceptors in the Entity Framework and Nhibernate.

Actual Behavior

Functionality not implemented

update link in the readme.md

Soft Delete

Expected Behavior

It would be interesting if it had an API to perform Soft Delete from the records instead of a real delete.

For example:

FlepperQueryBuilder.SoftDelete()
                .From("user")
                .Where("Id",1);

To know which field is used to mark the record as deleted, we can use a configuration class so that we do not have to inform all that we have to use this feature.

Actual Behavior

Functionality not implemented

[Bug] When using alias in table doest not apply alias in selected columns

When using alias in table doest not apply alias in selected columns

Expected Behavior

FlepperQueryBuilder.Select("Name")
                .From("Table1").As("t1")
                .Build();

Result

SELECT t1.Name FROM Table1 t1

Actual Behavior

FlepperQueryBuilder.Select("Name")
                .From("Table1").As("t1")
                .Build();

Result

SELECT Name FROM Table1 t1

Typed column specification

Expected Behavior

When using a select with a specific type, it would be interesting to have the possibility to pass an expression where it would be possible to inform which fields of that object I want to be added to the query.

For example:

FlepperQueryBuilder
                .Select<UserDto>(x => new { x.Id, x.Name, x.Birthday })
                .From("user")
                .Build()

Result must be:

SELECT [Id],[Name],[Birthday] FROM [user]

Actual Behavior

Functionality not implemented

Specifications

• 1.0.0:

Support Hacktoberfest event adding hacktoberfest tag.

Expected Behavior

Hi folks, I think that you know about hacktoberfest, I would be very happy to see this project supporting this event add a hacktoberfest label, so when I search by #hacktoberfest I can see this repo.

Actual Behavior

When I search by #hacktoberfest language:C# I don't see this repository in list of results.

Steps to Reproduce the Problem

1. Open github;
2. Search by #hacktoberfest language:C# in global search input
   

Work with string null values

When creating a sql query when a column has a null value, we must write this: column IS NULL instead column = NULL

Create a builder to do a select operation.

Add Function WithScopeIdentity()

Example:

var builder = FlepperQueryBuilder
                .Insert()
                .Into("Table1")
                .Columns("Column1")
                .Values("Value1")
                .WithScopeIdentity()
                .BuildWithParameters();

Output:

INSERT INTO [Table1] ([Column1] ) VALUES (@p0);SELECT scope_identity();

To be used in the Dapper for execution of the insert and as output the "id" inserted for fields configured auto increment in SqlServer

Example com Dapper e SqlServer

IDbConnection dbConnection = new SqlConnection("");

var id = dbConnection.Insert()
                    .Into("credit")
                    .Columns("description")
                    .Values(Guid.NewGuid().ToString())
                    .WithScopeIdentity()
                    .QueryFirst<int>();

Multiple GroupBy Clause With OrderBy

Cant use multiple GroupBy

eg: .GroupBy("Name").ThenBy("Age")

After using GroupBy clause cant use OrderBy clause

eg: .GroupBy("Name").ThenBy("Age")
.OrderBy("Name")

Error inserting or updating when using null values

Expected Behavior

When inserting fields with null values you must create the query using NULL

Actual Behavior

Throws an exception when entering null values

Improve Code Coverage

Expected Behavior

Improve Code Coverage

Actual Behavior

Code coverage is too low

Cascate Soft Delete

Expected Behavior

Since we have the option to perform Soft Delete as requested in issue: #36
It would be very interesting if we had an option to configure whether to do the soft delete in cascade.

Actual Behavior

Functionality not implemented

Nuget package can not be found

Expected Behavior

A description of the expected behavior

Actual Behavior

Description of current behavior
When i search for "Flepper" package on nuget, nothing is found

Steps to Reproduce the Problem

Improve documentation

Expected Behavior

More documentation about

• select statements
• sql functions joins
• comparison operators
• dapper extansions.

Actual Behavior

There are just a get started with simple statements.

Create CONTRIBUTING.md

You need to add information on how to make contributions to the project.

Not implemented SubQuery Exists and NotExists?

Expected Behavior

SELECT * FROM [User] AS u 
       WHERE EXISTS(SELECT * FROM [Phone] AS p WHERE [p].[UserId] = [u].[Id] )

---

SELECT * FROM [User] AS u 
       WHERE NOT EXISTS(SELECT * FROM [Phone] AS p WHERE [p].[UserId] = [u].[Id] )

Actual Behavior

Not implemented

Select with Top

Expected Behavior

to be possible create a query with Top:

Select().Top(2).Fom("table")

Actual Behavior

feature not implemented

And with alias

Expected Behavior

Where("alias","column).Equal("alias,"column").And("alias","column")

Actual Behavior

this feature does not exist.

Support for other SQL-ANSI dialects

Expected Behavior

Today there is only support for SQL Server 2008 and higher, in order to have a wider scope it would be interesting to have support for other SQL-ANSI dialects

Actual Behavior

Functionality not implemented

Create a builder to insert operation

Add XML Comments

Add XML comments to enhance the experience of those who are consuming the Flepper api.

Function in where clause

Cant use functions in where clause

eg: .Where("year(DATE)").EqualsTo(2021)

Run query with dapper

Expected Behavior

After performing the query builder it would be interesting to have the option to execute this query using Dapper.

For example:

FlepperQueryBuilder.Select<UserDto>(x => x.Id, x.Name, x.Birthday)
                .From("user")
                .QueryFirstOrDefault();

Actual Behavior

Functionality not implemented

technical debit of the sort statement

Expected Behavior

• use just one Command.ToString() in the Sort class.
• remove the comments in the OrderByCommandTests class.
• remove code duplication. Reuse de code below where it is needed.

 Command.AppendFormat(Command.ToString().Contains("ORDER BY")
                ? ",[{0}]"
                : "ORDER BY [{0}]", column);

and

  Command.AppendFormat(Command.ToString().Contains("ORDER BY")
               ? ",{0}.[{1}]"
                : "ORDER BY {0}.[{1}]", tableAlias, column);

SQL Injection Prevention

It is necessary to implement prevention against SQL Injection, because today this is a security failure in the writing of queries.

Create paging methods

Expected Behavior

It would be nice if there were paging methods

Actual Behavior

Functionality not implemented

Problem Field with Nullable Type?

Expected Behavior

Table:

In the code:

     dbConnection
                    .Insert()
                    .Into("Owe")
                    .Columns("Created", "Name", "Active")
                    .Values(null, Guid.NewGuid().ToString(), true)
                    .Execute();

Actual Behavior

Can not write NULL value to fields that can receive this value type?

Steps to Reproduce the Problem

IDbConnection dbConnection = new SqlConnection("....")
dbConnection
            .Insert()
            .Into("Owe")
             .Columns("Created", "Name", "Active")
             .Values(null, Guid.NewGuid().ToString(), true)
             .Execute();

Exception

CloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action2 paramReader) at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable1 commandTimeout, Nullable1 commandType) at Flepper.QueryBuilder.DapperExtensions.FlepperDapperQuery.Execute(IDbTransaction transaction, Nullable1 commandTimeout, Nullable1 commandType) at Flepper.QueryBuilder.DapperExtensions.QueryBuilderExtensions.Execute(IQueryCommand queryCommand, IDbTransaction transaction, Nullable1 commandTimeout, Nullable`1 commandType)
at ConsoleApp33.Program.Main(String[] args) in C:\Users\Developer\Documents\Visual Studio 2017\Projects\ConsoleApp33\ConsoleApp33\Program.cs:line 32
ClientConnectionId:64eb31fb-9124-45b1-b139-7ae23c22072c
Error Number:257,State:3,Class:16

The exception happens because of the NULL pass, which should be a problem, but then what value do I have to pass to symbolize DateTime? or any type (Nullable<>) Maybe a constant for later testing??

Line of problem:

https://github.com/Flepper/flepper/blob/development/Flepper.QueryBuilder/Utils/ParameterObjectBuilder.cs#L46

Code line of problem

CreateProperty(typeBuilder, parameter.Key.Replace("@", ""), 
            parameter.Value is null 
                 ? typeof(object)
                 : parameter.Value.GetType());

Specific problem

typeof(object)