ose-net / yesql.net Goto Github PK

LoadFromDirectories does not check if the directory does not exist:

var data = new YeSqlLoader().LoadFromDirectories("./sql/products");

Expected behavior

It should generate the following error:

Unhandled exception. YeSql.Net.YeSqlLoaderException: ./sql/products: error: No such directory exists.

Current behavior

Unhandled exception. System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\samples\LoaderSample\sql\products'.
   at System.IO.Enumeration.FileSystemEnumerator`1.CreateDirectoryHandle(String path, Boolean ignoreNotFound)
   at System.IO.Enumeration.FileSystemEnumerator`1.Init()
   at System.IO.Enumeration.FileSystemEnumerable`1..ctor(String directory, FindTransform transform, EnumerationOptions options, Boolean isNormalized)
   at System.IO.Enumeration.FileSystemEnumerableFactory.UserFiles(String directory, String expression, EnumerationOptions options)
   at System.IO.Directory.InternalEnumeratePaths(String path, String searchPattern, SearchTarget searchTarget, EnumerationOptions options)
   at System.IO.Directory.GetFiles(String path, String searchPattern, EnumerationOptions enumerationOptions)
   at YeSql.Net.YeSqlLoader.GetSqlFilesDetails(String directoryName)+MoveNext() in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Loader\YeSqlLoader.HelperMethods.cs:line 78
   at System.Linq.Enumerable.Any[TSource](IEnumerable`1 source)
   at YeSql.Net.EnumerableExtensions.IsEmpty[T](IEnumerable`1 source) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Extensions\EnumerableExtensions.cs:line 15
   at YeSql.Net.YeSqlLoader.LoadFromDirectory(String directoryName) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Loader\YeSqlLoader.cs:line 74
   at YeSql.Net.YeSqlLoader.LoadFromDirectories(String[] directories) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Loader\YeSqlLoader.cs:line 55
   at Program.<Main>$(String[] args) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\samples\LoaderSample\Program.cs:line 5

This validation will be useful for two reasons:

• Avoid displaying unnecessary information in the stack trace (like .NET assemblies).
• Show all errors associated with DirectoryNotFound to the client instead of doing it one at a time.

Hi.
I have a solution which Select the db engine depending on the environment... Is there a native configuration or db switch?

Thanks

Example code to reproduce the problem

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();

var result = new YeSqlLoader().LoadFromDirectories("./yesql");

var app = builder.Build();
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

The following exception is thrown when the application is executed:

System.AggregateException: 'One or more errors occurred. (./yesql: error: No such directory exists.)

Given the following project structure

├── SqlFiles/
│   ├── bin/
│   │   └── Debug/
│   │       └── netstandard2.0/
│   │           └── yesql/
│   │               └── Reports/
│   │                   ├── order.sql
│   │                   └── orderDetails.sql
│   ├── Reports/
│   │   ├── order.sql
│   │   └── orderDetails.sql
│   └── SqlFiles.csproj
└── WebApi/
    ├── bin/
    │   └── Debug/
    │       └── net8.0/
    │           └── yesql/
    │               ├── product.sql
    │               └── Reports/
    │                   ├── order.sql
    │                   └── orderDetails.sql
    ├── product.sql
    ├── WebApi.csproj
    └── Program.cs

For a strange reason, the current directory is the root directory (where the WebApi.csproj project file is located). Of course, in that directory there is no yesql folder, that's why it doesn't find it.

The current directory should be WebApi/bin/Debug/net8.0. This should be the expected behavior.

Instead, I change the code to:

var path = Path.Combine(AppContext.BaseDirectory, "yesql");
var result = new YeSqlLoader().LoadFromDirectories(path);

There if the error disappears, since AppContext.BaseDirectory returns WebApi/bin/Debug/net8.0.

Usage

var sqlStatements = new YeSqlLoader().Load();

This method will search for .sql files inside the yesql folder. It should be noted that this method searches from the current directory (where the application assemblies are located).

Related to issue #12

The actual code is:

private void CreateAndThrowException()
    {
        var exceptions = new List<Exception>();

        if (_validationResult.HasError())
            exceptions.Add(new YeSqlLoaderException(_validationResult.ErrorMessages));

        if (_parser.ValidationResult.HasError())
            exceptions.Add(new YeSqlParserException(_parser.ValidationResult.ErrorMessages));

        if (exceptions.Count > 0)
            throw new AggregateException(exceptions);
    }

This code throws an AggregateException regardless if there is only one exception. In this case, it is better to throw YeSqlParserException or YeSqlLoaderException when there is only one exception.

After publishing my app in Nativ Aot Mode (/p:UseAppHost=true /p:PublishAot=true), the yesql folder and the queries are copied in my linux docker container, but they don't seem to be loading correctly and I get YeSql.Net.TagNotFoundException : The given tag '{MY_TAG}' was not present in the collection.

It is a more descriptive name and follows the convention of the other methods such as LoadFromFiles and LoadFromDirectories.

See this comment for more information.

Related to issue #12

The following sample projects will be added:

• Example.Reports
• Example.SqlFiles
• Example.ConsoleApp
• Example.AspNetCore
• Example.AspNetCore.Tests
• Example.Parsing
• Example.NetFramework

A solution file called YeSql.Net.Examples.sln will be created.

Conditions:

• Each Load method must throw AggregateException.
• Load method without parameters should search for files in the sql/ folder.

The SQL file loader should not throw an exception in this case, since it is not an error, i.e. if a directory does not have any SQL files, then the expected behavior is to load nothing and return to the consumer an empty dictionary without SQL statements.

This also avoid tests from failing from PR #89.
See the jobs: https://github.com/ose-net/yesql.net/actions/runs/7479832695/job/20357878024#step:4:325

LoadFromDirectories does not check if the directory passed as argument has an extension:

var data = new YeSqlLoader().LoadFromDirectories("sample.sql");

Expected behavior

It should generate the following error:

Unhandled exception. YeSql.Net.YeSqlLoaderException: error: 'sample.sql' must not have an extension.

Current behavior

Unhandled exception. System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\samples\LoaderSample\sample.sql'.
   at System.IO.Enumeration.FileSystemEnumerator`1.CreateDirectoryHandle(String path, Boolean ignoreNotFound)
   at System.IO.Enumeration.FileSystemEnumerator`1.Init()
   at System.IO.Enumeration.FileSystemEnumerable`1..ctor(String directory, FindTransform transform, EnumerationOptions options, Boolean isNormalized)
   at System.IO.Enumeration.FileSystemEnumerableFactory.UserFiles(String directory, String expression, EnumerationOptions options)
   at System.IO.Directory.InternalEnumeratePaths(String path, String searchPattern, SearchTarget searchTarget, EnumerationOptions options)
   at System.IO.Directory.GetFiles(String path, String searchPattern, EnumerationOptions enumerationOptions)
   at YeSql.Net.YeSqlLoader.GetSqlFilesDetails(String directoryName)+MoveNext() in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Loader\YeSqlLoader.HelperMethods.cs:line 78
   at System.Linq.Enumerable.Any[TSource](IEnumerable`1 source)
   at YeSql.Net.EnumerableExtensions.IsEmpty[T](IEnumerable`1 source) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Extensions\EnumerableExtensions.cs:line 15
   at YeSql.Net.YeSqlLoader.LoadFromDirectory(String directoryName) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Loader\YeSqlLoader.cs:line 74
   at YeSql.Net.YeSqlLoader.LoadFromDirectories(String[] directories) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Loader\YeSqlLoader.cs:line 55
   at Program.<Main>$(String[] args) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\samples\LoaderSample\Program.cs:line 5

LoadFromDirectories and LoadFromFiles methods do not check if the length of params list is zero:

var data = new YeSqlLoader().LoadFromDirectories();

Expected behavior

It should generate the following error:

Unhandled exception. System.ArgumentException: The length of the params list is zero.

Current behavior

SELECT* FROM [user];
SELECT* FROM [role];
Unhandled exception. YeSql.Net.TagNotFoundException: The given tag 'GetProducts' was not present in the collection.
   at YeSql.Net.YeSqlDictionary.get_Item(String tagName) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\src\Collections\YeSqlDictionary.cs:line 27
   at Program.<Main>$(String[] args) in C:\Users\syslan\Documents\Visual Studio 2022\yesql.net\samples\LoaderSample\Program.cs:line 9

Given the following .sql file in sql/products:

-- file: products.sql
SELECT * FROM products;

When loading the .sql file it generates the following error:

product.sql:(line 1, col 1): error: 'SELECT* FROM products;' line is not associated with any tag.

Expected behavior:

sql/products/product.sql:(line 1, col 1): error: 'SELECT* FROM products;' line is not associated with any tag.

The following implementation may serve as a guide: https://github.com/MrDave1999/dotenv.core/blob/master/src/Helpers/FormattingMessage.cs#L35

Subtasks:

• Create the AreNotNull method as private.
  The following implementation may serve as a guide: https://github.com/MrDave1999/dotenv.core/blob/master/src/Extensions/EnumerableExtensions.cs#L15

Formatting example:

users.sql: error: No such file or directory.

error: No sql file found in the directory '/sql/files'.

Parsing error: Data source is empty or consists only in whitespace.
users.sql: error: Data source is empty or consists only in whitespace.

error: The given tag 'GetUserById' was not present in the collection.

Parsing error (line 2, col 5): The given tag 'GetUserById' is duplicated.
users.sql:(line 2, col 5): error: The given tag 'GetUserById' is duplicated.

Parsing error (line 2, col 5): The tag name is empty.
users.sql:(line 2, col 5): error: The tag name is empty.

Parsing error (line 2, col 5): 'GetUserById' tag has no sql statement associated with it.
users.sql:(line 2, col 5): error: 'GetUserById' tag has no sql statement associated with it.

Parsing error (line 1, col 1): 'SELECT* FROM users;' statement is not associated with any tag. 
users.sql:(line 1, col 1): error: 'SELECT* FROM users;' statement is not associated with any tag.

I have a question, is this project to support other database rather than store procedure in sql server ?
Comparing to store procedure, what is the advantage?

ISqlCollection is a more descriptive name. This name makes the intention clear: A collection that contains sql code.

In this module two classes will be created:

• YeSqlParserException.
• TagNotFoundException.

Conditions:

• Both classes must have a default and parameterized constructor.

Example:

public YeSqlParserException();
public YeSqlParserException(string message);

Example:

var source =
"""
-- name: GetProducts
SELECT * FROM products;
""";

var sqlStatements = new YeSqlParser().ParseAndThrow(source);

In this module an interface and class will be created:

• IYeSqlCollection.
• YeSqlDictionary.

Conditions:

• Create a read-only indexer.
• The indexer must throw TagNotFoundException.

Subtasks:

• Create the TagModel as struct.

In this component can test several things:

• Indexer
• TryGetStatement
• ToString
• Enumerator

The following implementation may serve as a guide: https://github.com/MrDave1999/dotenv.core/blob/master/src/EnvValidationResult.cs#L11

The current API is confusing, e.g:

var sqlStatements = new YeSqlLoader().Load("./sample.sql");

The client thinks it should load the SQL file but it will not, because that overload expects the name of a directory and not an sql file.
If the client does not read the documentation of the method, your application will not behave correctly.
At the end the client will have to change his code to:

var sqlStatements = new YeSqlLoader().Load(new [] { "./sample.sql" });

To obtain the expected behavior, since this overload if it expects a sql file.

For this reason, it was decided to replace the overloads with methods such as:

LoadFromFiles(params string files);
LoadFromDirectories(params string directories);

LoadFromFiles and LoadFromDirectories does not throw any error when any element has a null value, empty string, or white-space characters:

var data = new YeSqlLoader().LoadFromFiles(new[] { "sample.sql", null, "", "    " });

Expected behavior

It should generate the following error:

Unhandled exception. System.ArgumentException: 'files' collection cannot contain elements with a null value, an empty string or consists only of white-space characters.

Template:

'{0}' collection cannot contain elements with a null value, an empty string or consists only of white-space characters.

Conditions:

• Each field must be a constant.
• Each field must follow the PascalCase convention.
• The class must be public, because it will be used in the test project.

The main namespace will be called YeSql.Net.

+--- docs/
+--- samples/
       +--- YeSql.Net.Console/
       +--- YeSql.Net.AspNetCore/
+--- src/
+--- tests/

In this class can test several things:

• HasError
• ErrorMessages

See https://github.com/ose-net/yesql.net/tree/v1.0.1?tab=readme-ov-file#accessing-sql-statements
This introduces a change in behavior, so it would be a breaking change.

Each constant ends with the suffix Message, for example: DuplicateTagNameMessage. This is redundant because in the project the constant is accessed from the ExceptionMessages class (it is understood that it is a message).

CopySqlFilesToPublishDirectory is a nuget package that is not needed. It is useless. This package should be marked as deprecated.

The package called CopySqlFilesToOutputDirectory is also used to copy to the publish directory when using the dotnet publish command, i.e. with this package you can also copy the SQL files to the publish directory.

The SQL file loader should not throw an exception in this case, since it is not an error, i.e. if the consumer passes an empty collection as argument, then the expected behavior is to return an empty dictionary without SQL statements.

This also facilitates integration with the plugin-based architecture, since the host application can decide not to load any plugins, so the host application would be passing to the SQL file loader an empty collection as argument and the expected behavior is not to throw an exception, but to return to the consumer an empty dictionary without SQL statements.

This also avoid tests from failing from PR #89.
See the jobs: https://github.com/ose-net/yesql.net/actions/runs/7496291700/job/20408051876#step:4:223

The idea of this is that when implementing a task, MSBuild will copy the .sql files into the yesql folder of the output directory and in that directory it will search for the YeSqlLoader.Load method.

It generates a lot of errors:

error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\Customers\customer.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\order.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\sample.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\security.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\sql\sample.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\Customers\customer.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\order.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\sample.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\security.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\sql\sample.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\bin\Debug\yesql\ThirdParties\thirdParty.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\Customers\customer.sql" because it was not found.
C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(5198,5): error MSB3030: Could not copy the file "C:\Users\mrdav\OneDrive\Documentos\Visual Studio 2022\Projects\yesql.net\samples\Example.NetFramework\bin\Debug\yesql\bin\Debug\yesql\order.sql" because it was not found.

This is a very strange error, since msbuild should not look for any sql files from the bin folder, because they are excluded with the property called DefaultItemExcludes. However, I have verified that this property is not set (returns an empty string) when the project points to .NET Framework instead of dotnetcore.