iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article

💎

Generating Dynamic SQL with Carbunql

2023/03/06に公開

Introduction

This article discusses methods for processing static SQL statements to generate dynamic SQL based on arguments, and introduces a SQL processing library called "Carbunql".

Cases where dynamic SQL is required

Common cases include:

Search screens or report screens with many optional fields
Batch processing such as ETL

The definition of dynamic SQL here is as follows:

A base SQL exists
Search conditions or sort conditions need to be modified based on arguments

Common solutions

There are various ways to generate dynamic SQL, each with its own pros and cons.

All-Parameter Description Method (Static SQL)

First, let's introduce a method to cover this with static SQL.

var sql = @"select * from table where 
case when :param is null then true else val = :param end";

You can test all parameter patterns on a SQL client.

Problem

Because SQL search conditions become complex, the appropriate execution plan might not be adopted.
The solution to this problem is dynamic SQL that does not include unnecessary search conditions.

String Concatenation Method

Since SQL is a string, it can be handled by string manipulation.

var sql = @"select a.a_id, a.val from table as a
where 1 = 1";
if (param != null) sql += "and a.val = :param";

A very simple and easy implementation.

Problem

When conditions become complex, code readability suffers.
Also, because it assumes "appending search conditions to the end," it's troublesome if there is processing after the WHERE clause (such as an ORDER clause).

String Replacement Method

A method to insert search conditions at any location by replacing unique placeholders.

var sql = @"select a.a_id, a.val from table as a
where 1 = 1 /*dynamic_condition*/
order by id";
var cnd = string.Empty;
if (parameter != null) cnd = "and a.val = :param";
if (!string.IsNullOrEmpty(cnd)) sql = sql.Replace("/*dynamic_condition*/", cnd);

Solves the "can only append to the end" issue of the string concatenation method.
Since it uses SQL comments, the SQL remains valid on its own.

Problem

Almost none.

If anything, "the process of generating the search condition string lacks versatility."
If there are many dynamic SQL generation processes, specialized processing becomes necessary.

Bonus: 2WaySQL

A method that defines unique syntax in SQL comments and generates SQL through a dedicated SQL parser.

By replacing SQL comments, the SQL remains valid on its own, maintaining maintainability.
Branching expressions can also be written.
It's not seen much lately.

Problem

Requires a dedicated parser, and there is no "de facto standard" library.
Currently, major SQL clients support parameter placeholders, making it difficult for this to become popular again.

SQL Parsing Method

A method to objectify and process SQL through a dedicated parser.

using Carbunql.Building;
using Carbunql.Values;

//sql parse
var sq = new SelectQuery(@"select a.a_id, a.val from table as a");

//get FROM table
var a = sq.FromClause!.Root;

//add WHERE clause
sq.Where(a, "val").Equal(":prm");

This is the main focus of this article. It will be explained in detail in the next section.

What is Carbunql?

A SELECT query processing library for C#. It can objectify SQL, process it, and write it back as SQL. Because it objectifies the query, it allows for advanced manipulation.

Features

Does not use a DBMS. No DB connection string required.
Can be used with only a SELECT query string.

It aims to be able to parse any SELECT query, and it supports parsing CTEs, subqueries, and VALUES clauses.

If you have examples that cannot be parsed, please provide the details. I will consider improvements. (Please note that I may not be able to meet all requests for low-usage or DBMS-specific features.)

Limitations

Only SELECT queries can be parsed
Comments written in SQL are removed
No SQL execution functionality

Queries other than SELECT (INSERT, UPDATE, etc.) are outside the scope of parsing.

Since comments are removed, it is not suitable for processing SELECT queries with hint clauses.

Carbunql itself does not have a SQL execution function, so please convert it to a SQL string for use, or use the library for Dapper, Carbunql.Dapper. Using Carbunql.Dapper is recommended because it allows parameter information to be passed along.

Installation

It can be installed from NuGet.

PM> Install-Package Carbunql

Note that .NET 6 or higher is required.

Usage

I will explain how to use it using C# sample code.
Please refer to here for the full sample code.

Objectifying SQL

Instantiate the SelectQuery class by specifying a SQL string in the constructor.

using Carbunql;
using Carbunql.Values;

//sql parse
var sq = new SelectQuery(@"select a.a_id, a.val from table as a");

This allows you to manipulate the SELECT, FROM, and WHERE clauses as objects.

If no base SQL exists and you want to generate SQL from scratch, omit the argument.

//blank query
var sq = new SelectQuery();

The key point is that the SelectQuery class can be used in both cases.

Adding conditions to the WHERE clause

You can add conditions using the Where method of the SelectQuery class (the first argument is the table, and the second is the column name).
The AND logical operator is applied by default.

//sq is SelectQuery class

//get FROM table
var a = sq.FromClause!.Root;

//add WHERE clause
sq.Where(a, "val").Equal(":prm");

SELECT
    a.a_id,
    a.val
FROM
    table AS a
WHERE
    a.val = :prm

When writing multiple conditions, write them as follows:

//add WHERE clause
sq.Where(a, "val1").Equal(":prm1");
sq.Where(a, "val2").Equal(":prm2");

SELECT
    a.a_id,
    a.val1,
    a.val2
FROM
    table AS a
WHERE
    a.val1 = :prm1
    AND a.val2 = :prm2

Parameter values

The SelectQuery class can manage parameter values with the Parameters property.
*To use registered parameter values, please use Carbunql.Dapper.

//add WHERE clause
if (parameter != null)
{
    sq.Where("a.val = :prm");
    sq.Parameters.Add(":prm", parameter);
}

/*
    :prm = 1
*/
SELECT
    a.a_id,
    a.val
FROM
    table AS a
WHERE
    a.val = :prm

OR conditions

OR conditions can also be written.
Below is an example of grouping conditions (parentheses) and an OR condition.

//add WHERE clause
sq.Where(() =>
{
    sq.Parameters.Add(":prm1", prm1);
    sq.Parameters.Add(":prm2", prm2);

    //(a.val = :prm1)
    var v1 = new ColumnValue(a, "val").Equal(":prm1").ToGroup();
    //(a.val = :prm2)
    var v2 = new ColumnValue(a, "val").Equal(":prm2").ToGroup();

    //((a.val = :prm1) or (a.val = :prm2))
    return v1.Or(v2).ToGroup();
});

The ColumnValue class represents a column. Specify the table in the first argument and the column in the second.

/*
    :prm1 = 1
    :prm2 = 2
*/
SELECT
    a.a_id,
    a.val
FROM
    table AS a
WHERE
    ((a.val = :prm1) OR (a.val = :prm2))

Advanced processing

This section introduces processing methods that are difficult to handle with simple string manipulation.

Column filtering in the SELECT clause

You can retrieve the columns selected by the SQL using the SelectClause property. You can also remove them.

//params string[] columns = "a_id", "val3"

//sql parse
var sq = new SelectQuery(@"select a.a_id, a.val1, a.val2, a.val2 as val3 from table as a");

//get FROM table
var a = sq.FromClause!.Root;

//get columns not contained in the list
var lst = sq.SelectClause!.Where(x => !columns.Contains(x.Alias)).ToList();

//remove columns
foreach (var item in lst) sq.SelectClause!.Remove(item);

SELECT
    a.a_id,
    a.val2 AS val3
FROM
    table AS a

Filtering using outer joins

Let's add a join condition to the FROM clause and then add a condition to the WHERE clause.

//sql parse
var sq = new SelectQuery(@"select a.a_id, a.val from table as a");

//get FROM table
var a = sq.FromClause!.Root;

//add left-join, and get joined table
var b = sq.FromClause!.LeftJoin("data").As("b").On(a, "a_id");

//add WHERE clause
sq.Where(b, "a_id").IsNull();

SELECT
    a.a_id,
    a.val
FROM
    table AS a
    LEFT JOIN data AS b ON a.a_id = b.a_id
WHERE
    b.a_id IS null

Filtering for WITH clauses

Let's retrieve a query used in a WITH clause and update the WHERE clause within that query.

//sql parse
var sq = new SelectQuery(@"with
cte as (
select a_id, val from table
)
select
a.a_id,
a.val
from
datasource as a");

var cte = sq.WithClause!.Where(x => x.Alias == "cte").First();

// cte SelectQuery
var cteq = cte.GetSelectQuery();

//get FROM table
var a = cteq.FromClause!.Root;

//add WHERE clause
cteq.Where(a, "val").Equal(":prm");
cteq.Parameters.Add(":prm", 1);

/*
    :prm = 1
*/
WITH
    cte AS (
        SELECT
            a_id,
            val
        FROM
            table
        WHERE
            table.val = :prm
    )
SELECT
    a.a_id,
    a.val
FROM
    datasource AS a

Summary

Using Carbunql, advanced processing can be easily implemented.

In addition, although not mentioned in this article, you can also perform modifications such as adding columns or converting to MERGE queries.
There is a demo site, so please feel free to refer to it and try it out.

iTranslated by AI

Introduction

Cases where dynamic SQL is required

Common solutions

All-Parameter Description Method (Static SQL)

Problem

String Concatenation Method

Problem

String Replacement Method

Problem

Bonus: 2WaySQL

Problem

SQL Parsing Method

What is Carbunql?

Features

Limitations

Installation

Usage

Objectifying SQL

Adding conditions to the WHERE clause

Parameter values

OR conditions

Advanced processing

Column filtering in the SELECT clause

Filtering using outer joins

Filtering for WITH clauses

Summary

Discussion