Skip to content

Commit cb92b13

Browse files
vbilopavclaude
vbilopav
and
claude
committed
Add @define_param virtual parameters, multi-line block comment test
New @define_param annotation creates virtual HTTP parameters that are not bound to PostgreSQL. Used for custom parameter placeholders (e.g., table_format = {format}) and claim mapping without SQL references. - IsVirtual flag on NpgsqlRestParameter (init-only) - SqlFileParser.ExtractVirtualParams parses @define_param name [type] - SqlFileSource adds virtual params to Routine with IsVirtual = true - NpgsqlRestEndpoint skips command.Parameters.Add for virtual params - 5 virtual param tests (claim mapping, typed, multiple, custom placeholder) - Multi-line block comment endpoint test (/* ... */ with HTTP, @param, @authorize) - Changelog updated with virtual parameters section and examples Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
1 parent 45d547e commit cb92b13

9 files changed

Lines changed: 257 additions & 11 deletions

File tree

‎NpgsqlRest/Defaults/CommentParsers/AnnotationReference.cs‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -397,6 +397,14 @@ static JsonArray ToJsonArray(string[] values)
397397
["description"] = "Rename a result key in multi-command SQL file responses. N is the 1-based command index. Example: '@result1 validate' renames the first result from 'result1' to 'validate'. SQL file source only."
398398
});
399399

400+
annotations.Add((JsonNode)new JsonObject
401+
{
402+
["name"] = "define_param",
403+
["aliases"] = new JsonArray("define_param"),
404+
["syntax"] = "define_param <name> [type]",
405+
["description"] = "Define a virtual parameter that exists for HTTP matching and claim mapping but is NOT bound to the PostgreSQL command. Useful for SQL file endpoints where you need parameters for comment placeholders or claim mapping without referencing them in SQL. Default type is text. SQL file source only."
406+
});
407+
400408
return annotations;
401409
}
402410
}

‎NpgsqlRest/NpgsqlRestEndpoint.cs‎

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -299,7 +299,7 @@ currentEndpoint.SseEventNoticeLevel is not null &&
299299
}
300300
}
301301
}
302-
command.Parameters.Add(parameter);
302+
if (!parameter.IsVirtual) command.Parameters.Add(parameter);
303303
hasNulls = true;
304304
if (formatter.IsFormattable is false)
305305
{
@@ -420,7 +420,7 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
420420
}
421421
}
422422
}
423-
command.Parameters.Add(parameter);
423+
if (!parameter.IsVirtual) command.Parameters.Add(parameter);
424424

425425
if (hasNulls is false && parameter.Value == DBNull.Value)
426426
{
@@ -502,7 +502,7 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
502502
}
503503
}
504504
}
505-
command.Parameters.Add(parameter);
505+
if (!parameter.IsVirtual) command.Parameters.Add(parameter);
506506
if (hasNulls is false && parameter.Value == DBNull.Value)
507507
{
508508
hasNulls = true;
@@ -598,7 +598,7 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
598598
}
599599
}
600600
}
601-
command.Parameters.Add(parameter);
601+
if (!parameter.IsVirtual) command.Parameters.Add(parameter);
602602

603603
if (hasNulls is false && parameter.Value == DBNull.Value)
604604
{
@@ -691,7 +691,7 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
691691
}
692692
}
693693
}
694-
command.Parameters.Add(parameter);
694+
if (!parameter.IsVirtual) command.Parameters.Add(parameter);
695695

696696
if (hasNulls is false && parameter.Value == DBNull.Value)
697697
{
@@ -955,7 +955,7 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
955955
}
956956
}
957957
}
958-
command.Parameters.Add(parameter);
958+
if (!parameter.IsVirtual) command.Parameters.Add(parameter);
959959
hasNulls = true;
960960
if (formatter.IsFormattable is false)
961961
{
@@ -1079,7 +1079,7 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
10791079
}
10801080
}
10811081
}
1082-
command.Parameters.Add(parameter);
1082+
if (!parameter.IsVirtual) command.Parameters.Add(parameter);
10831083

10841084
if (hasNulls is false && parameter.Value == DBNull.Value)
10851085
{
@@ -1172,7 +1172,7 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
11721172
}
11731173
}
11741174
}
1175-
command.Parameters.Add(parameter);
1175+
if (!parameter.IsVirtual) command.Parameters.Add(parameter);
11761176

11771177
if (hasNulls is false && parameter.Value == DBNull.Value)
11781178
{

‎NpgsqlRest/NpgsqlRestParameter.cs‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,11 @@ public class NpgsqlRestParameter : NpgsqlParameter
1515
/// Never changed by annotations. Used by RoutineSourceParameterFormatter for SQL generation.
1616
/// </summary>
1717
public string OriginalName { get; init; }
18+
/// <summary>
19+
/// Virtual parameters exist for HTTP matching and claim mapping but are NOT bound
20+
/// to the PostgreSQL command. Created by @define_param annotation on SQL file endpoints.
21+
/// </summary>
22+
public bool IsVirtual { get; init; }
1823
public TypeDescriptor TypeDescriptor { get; init; }
1924

2025
public ParamType ParamType { get; set; } = default!;

‎NpgsqlRestTests/SqlFileSourceTests/QueryEndpointTests/CommentPositionEndpointTests.cs‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,19 @@ public static void CommentPositionEndpointTests()
2626
File.WriteAllText(Path.Combine(Dir, "comment_inline.sql"), """
2727
select $1 as result; -- @param $1 my_val
2828
""");
29+
30+
// Full multi-line block comment with multiple annotations
31+
File.WriteAllText(Path.Combine(Dir, "comment_multiline_block.sql"), """
32+
/*
33+
HTTP GET
34+
@param $1 user_id
35+
@param $2 active_flag
36+
@authorize
37+
*/
38+
select id, name, active
39+
from sql_describe_test
40+
where id = $1 and active = $2;
41+
""");
2942
}
3043
}
3144

@@ -71,4 +84,26 @@ public async Task InlineComment_ParamAnnotationWorks()
7184
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
7285
content.Should().Be("[{\"result\":\"hello\"}]");
7386
}
87+
88+
[Fact]
89+
public async Task MultiLineBlockComment_AllAnnotationsWork()
90+
{
91+
// Multi-line block comment with HTTP, @param, @authorize — all should be parsed
92+
using var client = test.CreateClient();
93+
// Must login because @authorize is set
94+
await client.GetAsync("/login");
95+
using var response = await client.GetAsync("/api/comment-multiline-block?user_id=1&active_flag=true");
96+
var content = await response.Content.ReadAsStringAsync();
97+
98+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
99+
content.Should().Be("[{\"id\":1,\"name\":\"test1\",\"active\":true}]");
100+
}
101+
102+
[Fact]
103+
public async Task MultiLineBlockComment_Unauthorized_WithoutLogin()
104+
{
105+
using var client = test.CreateClient();
106+
using var response = await client.GetAsync("/api/comment-multiline-block?user_id=1&active_flag=true");
107+
response.StatusCode.Should().Be(HttpStatusCode.Unauthorized);
108+
}
74109
}

‎NpgsqlRestTests/SqlFileSourceTests/QueryEndpointTests/VirtualParamCustomTests.cs‎

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
using NpgsqlRestTests.Setup;
2+
3+
namespace NpgsqlRestTests.SqlFileSourceTests;
4+
5+
public static partial class SqlFiles
6+
{
7+
public static void VirtualParamCustomTests()
8+
{
9+
// Virtual param used as custom parameter placeholder
10+
// format is passed via HTTP query string, substituted into table_format annotation
11+
File.WriteAllText(Path.Combine(Dir, "virtual_format.sql"), """
12+
-- @define_param format text
13+
-- table_format = {format}
14+
-- @param $1 department_id
15+
select id, name from sql_describe_test where id = $1;
16+
""");
17+
}
18+
}
19+
20+
[Collection("SqlFileSourceFixture")]
21+
public class VirtualParamCustomTests(SqlFileSourceTestFixture test)
22+
{
23+
[Fact]
24+
public async Task VirtualParam_AsCustomPlaceholder_PassedViaQueryString()
25+
{
26+
// format=json passed in query string, feeds into table_format = {format}
27+
// Without a registered table_format handler for "json", it falls through to default JSON rendering
28+
using var response = await test.Client.GetAsync("/api/virtual-format?department_id=1&format=json");
29+
response.StatusCode.Should().Be(HttpStatusCode.OK,
30+
$"Response: {await response.Content.ReadAsStringAsync()}");
31+
var content = await response.Content.ReadAsStringAsync();
32+
content.Should().Contain("test1");
33+
}
34+
35+
[Fact]
36+
public async Task VirtualParam_WithDifferentFormatValue()
37+
{
38+
// format=excel passed — virtual param value is available for custom parameter substitution
39+
using var response = await test.Client.GetAsync("/api/virtual-format?department_id=1&format=excel");
40+
response.StatusCode.Should().Be(HttpStatusCode.OK,
41+
$"Response: {await response.Content.ReadAsStringAsync()}");
42+
var content = await response.Content.ReadAsStringAsync();
43+
content.Should().Contain("test1");
44+
}
45+
}

‎NpgsqlRestTests/SqlFileSourceTests/QueryEndpointTests/VirtualParamTests.cs‎

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
using NpgsqlRestTests.Setup;
2+
3+
namespace NpgsqlRestTests.SqlFileSourceTests;
4+
5+
public static partial class SqlFiles
6+
{
7+
public static void VirtualParamTests()
8+
{
9+
// Virtual param used for claim mapping — not in SQL, auto-filled from claims
10+
File.WriteAllText(Path.Combine(Dir, "virtual_claim.sql"), """
11+
-- @authorize
12+
-- @user_parameters
13+
-- @define_param _user_id
14+
select 'ok' as result;
15+
""");
16+
17+
// Virtual param with type specified
18+
File.WriteAllText(Path.Combine(Dir, "virtual_typed.sql"), """
19+
-- @define_param extra_info text
20+
-- @param $1 id
21+
select id, name from sql_describe_test where id = $1;
22+
""");
23+
24+
// Multiple virtual params
25+
File.WriteAllText(Path.Combine(Dir, "virtual_multiple.sql"), """
26+
-- @define_param tracking_id text
27+
-- @define_param source text
28+
-- @param $1 id
29+
select id, name from sql_describe_test where id = $1;
30+
""");
31+
32+
}
33+
}
34+
35+
[Collection("SqlFileSourceFixture")]
36+
public class VirtualParamTests(SqlFileSourceTestFixture test)
37+
{
38+
[Fact]
39+
public async Task VirtualClaimParam_EndpointWorks_WithoutPassingParam()
40+
{
41+
// _user_id is virtual — not in SQL, but claim mapping fills it
42+
using var client = test.CreateClient();
43+
await client.GetAsync("/login");
44+
using var response = await client.GetAsync("/api/virtual-claim");
45+
response.StatusCode.Should().Be(HttpStatusCode.OK,
46+
$"Virtual claim param should not block endpoint. Response: {await response.Content.ReadAsStringAsync()}");
47+
}
48+
49+
[Fact]
50+
public async Task VirtualTypedParam_EndpointWorks_WithRealParam()
51+
{
52+
// Real $1 param works, virtual extra_info is ignored in SQL
53+
using var response = await test.Client.GetAsync("/api/virtual-typed?id=1&extra_info=test");
54+
response.StatusCode.Should().Be(HttpStatusCode.OK);
55+
var content = await response.Content.ReadAsStringAsync();
56+
content.Should().Contain("test1");
57+
}
58+
59+
[Fact]
60+
public async Task VirtualMultipleParams_EndpointWorks()
61+
{
62+
using var response = await test.Client.GetAsync("/api/virtual-multiple?id=1&tracking_id=abc&source=web");
63+
response.StatusCode.Should().Be(HttpStatusCode.OK);
64+
var content = await response.Content.ReadAsStringAsync();
65+
content.Should().Contain("test1");
66+
}
67+
68+
}

‎changelog.md‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -125,6 +125,33 @@ Now: `GET /api/my-query?user_name=hello&age=42`
125125
- Same `$N` with conflicting types → startup error with clear message (override with `@param $1 name type`)
126126
- `$N` used in only some statements → type from the statement(s) that reference it
127127

128+
#### Virtual Parameters
129+
130+
Use `@define_param` to create HTTP parameters that are NOT bound to the PostgreSQL command. These parameters exist for HTTP request matching, custom parameter placeholders, and claim mapping — without appearing in the SQL query.
131+
132+
**Use case: custom parameter placeholders** — pass HTTP parameters that control endpoint behavior (e.g., output format) without referencing them in SQL:
133+
134+
```sql
135+
-- sql/users_report.sql
136+
-- @define_param format text
137+
-- table_format = {format}
138+
-- @param $1 department_id
139+
SELECT id, name, email FROM users WHERE department_id = $1;
140+
```
141+
142+
`GET /api/users-report?department_id=5&format=html_table` — the `format` parameter feeds into the `table_format` custom parameter via `{format}` placeholder, selecting the output format (JSON, HTML table, etc.) without being part of the SQL query.
143+
144+
**Use case: claim mapping** — auto-fill from user claims without SQL reference:
145+
146+
```sql
147+
-- @authorize
148+
-- @user_parameters
149+
-- @define_param _user_id
150+
SELECT * FROM user_data;
151+
```
152+
153+
Default type is `text`; specify a type with `@define_param name type`.
154+
128155
#### Comments and Annotations
129156

130157
All comments in the SQL file are parsed as annotations, just like `COMMENT ON FUNCTION` in PostgreSQL:
@@ -147,6 +174,7 @@ All existing NpgsqlRest annotations work: `@authorize`, `@allow_anonymous`, `@ta
147174
| `@param $N is name` | Rename ("is" style) | `-- @param $1 is user_id` |
148175
| `@resultN name` | Rename multi-command result key | `-- @result1 validate` |
149176
| `@resultN is name` | Rename result key ("is" style) | `-- @result1 is validate` |
177+
| `@define_param name [type]` | Define virtual parameter (not bound to SQL) | `-- @define_param _user_id` |
150178

151179
**`CommentScope` setting** controls which comments are parsed:
152180
- `All` (default) — every comment in the file, regardless of position

‎plugins/NpgsqlRest.SqlFileSource/SqlFileParser.cs‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,12 @@ public class SqlFileParseResult
3434
/// </summary>
3535
public Dictionary<int, string> ResultNames { get; } = [];
3636

37+
/// <summary>
38+
/// Virtual parameters from @define_param annotations.
39+
/// Each entry is (name, type) where type may be null (defaults to text).
40+
/// </summary>
41+
public List<(string Name, string? Type)> VirtualParams { get; } = [];
42+
3743
/// <summary>
3844
/// Errors encountered during parsing.
3945
/// </summary>
@@ -333,6 +339,9 @@ public static SqlFileParseResult Parse(ReadOnlySpan<char> content, CommentScope
333339
ExtractResultNames(result);
334340
}
335341

342+
// Extract @define_param annotations
343+
ExtractVirtualParams(result);
344+
336345
return result;
337346
}
338347
finally
@@ -415,6 +424,38 @@ private static void ExtractResultNames(SqlFileParseResult result)
415424
}
416425
}
417426

427+
/// <summary>
428+
/// Extract @define_param annotations from comment text.
429+
/// Supports: @define_param name, @define_param name type
430+
/// </summary>
431+
private static void ExtractVirtualParams(SqlFileParseResult result)
432+
{
433+
if (string.IsNullOrEmpty(result.Comment)) return;
434+
435+
var lines = result.Comment.Split(['\n', '\r'], StringSplitOptions.RemoveEmptyEntries);
436+
437+
foreach (var line in lines)
438+
{
439+
var trimmed = line.Trim();
440+
var s = trimmed.StartsWith('@') ? trimmed[1..] : trimmed;
441+
442+
if (!s.StartsWith("define_param", StringComparison.OrdinalIgnoreCase))
443+
{
444+
continue;
445+
}
446+
447+
var parts = s.Split([' ', '\t'], StringSplitOptions.RemoveEmptyEntries);
448+
if (parts.Length < 2)
449+
{
450+
continue; // no name provided
451+
}
452+
453+
var name = parts[1];
454+
var type = parts.Length >= 3 ? parts[2] : null;
455+
result.VirtualParams.Add((name, type));
456+
}
457+
}
458+
418459
private static void DetectMutation(ReadOnlySpan<char> word, SqlFileParseResult result)
419460
{
420461
if (word.Length == 6 &&

0 commit comments

Comments
 (0)