Using Source Generators for parsing sql at compile time

[ElderJames]

As you know, this is to improve performance

[SelectQuery( @"
select a.column_1 as col1, a.column_2 as col2
from table_a as a
left join table_b as b on a.id = b.table_a_id
where b.table_a_id is null
")]
public partial SomeSql { 

}

FromClause from = SomeSql.FromClause!;
string sql = SomeSql .ToCommand().CommandText;

[mk3008]

Thanks for the suggestion. I've never used Source Generator, so I'll leave a note.

https://learn.microsoft.com/en-us/dotnet/csharp/roslyn-sdk/source-generators-overview

[mk3008]

Since the development stumbled, I would like to temporarily stop the development work for this issue.

The story will be long, but I will write the background.

Even if you use SourceGenerators, you still need code that somehow instantiates the SelectQuery class.

For example, there is a way to pass the query string to the constructor of the SelectQuery class, but this way the SQL parsing is performed at runtime, so I don't think using SourceGenerators will do any compile time parsing.

// slow because it is parsed at runtime
var sq = new SelectQuery("select a. column_1 as c1 from table_a as a");
return sq;

Therefore, we need to think of a way to instantiate it quickly.

I think the fastest way is to write C# code that parses the SQL statement and instantiates SelectQuery from scratch.

// fastest because no parsing
// select a.column_1 as c1 from table_a as a
var sq = new SelectQuery();
var (from, a) = sq.From("table_a").As("a");
sq.Selecr(a, "column_1").As("c1");
return sq;

However, that would mean creating another parsing class, which would require considerable effort. This proposal cannot be adopted.

Then I came up with deserialization. I thought it would be faster to build a SelectQuery class from JSON than to parse the SQL statement and build it. Even if it is slow, it can be used as a deep copy, so I expected that it would not be wasted. (#166, #168)

As a result, the deserialized instance is more than 10 times faster than the SQL parse, which is exactly what we expected.

After that, you can say that the analysis process can be completed at compile time by analyzing SQL with SourceGenerators, converting it to byte[], and automatically generating the following code.

// serialize at compile time and deserialize at runtime
// select a.column_1 as c1 from table_a as a
var json = new byte[] { 146, 0, 154, 144, 145, 146, 146, 6, 147, 192, 161, 97, 168, 99, 111, 108, 117, 109, 110, 95, 49, 162, 99, 49, 146, 147, 146, 1, 148, 194, 192, 167, 116, 97, 98, 108, 101, 95, 97, 192, 161, 97, 192, 192, 192, 192, 192, 192, 192, 192, 128 };
SelectQuery sq = Serializer.Deserialize(json);
return sq;

I can see the goal. But I think I'll end here for now.

This is because SourceGenerators only supports net standard 2.0 and cannot refer to Carbunql developed with .NET6.

It may be possible if Carbunql supports net standard 2.0. I'm just not very interested in that task.

If SourceGenerators can reference .NET6 libraries, I would resume development.

Any advice is welcome.

[mk3008]

Although it is not directly related to this case, I created a project using SourceGenerator.

https://github.com/mk3008/PropertyBind

Now that I have acquired a certain amount of know-how, there is a possibility that I will restart this project. However, it is a low priority.

[mk3008]

A comment on a related issue:

Currently we are generating type-safe query builders and are considering generating raw SQL at compile time.

See the following issue if you are interested:

[Experiment] TypeSafe Query Builder #400 https://github.com/mk3008/Carbunql/milestone/3