Skip to content

Commit 72a653e

Browse files
vbilopavclaude
vbilopav
and
claude
committed
Add per-command positional @single and @Result annotations for multi-command SQL files
Positional annotations apply to the next statement below them. @single makes a command return a JSON object instead of array. @Result names a command's result key without using numbered @resultN syntax. TsClient generates correct non-array types for per-command @single. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
1 parent 198bfda commit 72a653e

10 files changed

Lines changed: 591 additions & 18 deletions

File tree

‎NpgsqlRest/NpgsqlRestEndpoint.cs‎

Lines changed: 39 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1964,12 +1964,15 @@ await LoginHandler.HandleAsync(
19641964
}
19651965
else
19661966
{
1967-
// Query command — read result set as JSON array
1967+
// Query command — read result set as JSON array (or object if @single)
19681968
// AllResultTypesAreUnknown forces all values to string — same as single-command path
19691969
mcCmd.AllResultTypesAreUnknown = true;
19701970
await using var mcReader = await mcCmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken);
19711971

1972-
writer.Advance(Encoding.UTF8.GetBytes("[".AsSpan(), writer.GetSpan(1)));
1972+
if (!currentCmd.IsSingle)
1973+
{
1974+
writer.Advance(Encoding.UTF8.GetBytes("[".AsSpan(), writer.GetSpan(1)));
1975+
}
19731976
bool mcFirstRow = true;
19741977
var mcRowBuilder = StringBuilderPool.Rent(512);
19751978
var mcBufferRows = endpoint.BufferRows ?? Options.BufferRows;
@@ -2178,6 +2181,11 @@ mcDescriptor.CompositeFieldNames is not null &&
21782181
} // end non-composite mc else
21792182
}
21802183

2184+
if (currentCmd.IsSingle)
2185+
{
2186+
break;
2187+
}
2188+
21812189
// Buffer rows flush
21822190
if (mcBufferRows > 0 && mcRowCount % mcBufferRows == 0)
21832191
{
@@ -2186,14 +2194,23 @@ mcDescriptor.CompositeFieldNames is not null &&
21862194
}
21872195
}
21882196

2197+
if (currentCmd.IsSingle && mcRowCount == 0)
2198+
{
2199+
// Empty result with @single — write null
2200+
writer.Advance(Encoding.UTF8.GetBytes(Consts.Null.AsSpan(), writer.GetSpan(Encoding.UTF8.GetMaxByteCount(Consts.Null.Length))));
2201+
}
2202+
21892203
if (mcRowBuilder.Length > 0)
21902204
{
21912205
WriteStringBuilderToWriter(mcRowBuilder, writer);
21922206
}
21932207
StringBuilderPool.Return(mcRowBuilder);
21942208
if (mcCompositeBuffer is not null) StringBuilderPool.Return(mcCompositeBuffer);
21952209

2196-
writer.Advance(Encoding.UTF8.GetBytes("]".AsSpan(), writer.GetSpan(1)));
2210+
if (!currentCmd.IsSingle)
2211+
{
2212+
writer.Advance(Encoding.UTF8.GetBytes("]".AsSpan(), writer.GetSpan(1)));
2213+
}
21972214
}
21982215
}
21992216

@@ -2543,7 +2560,16 @@ Options.AuthenticationOptions.DefaultDataProtector is not null &&
25432560
currentColumnCount = cmdInfo.ColumnCount;
25442561
}
25452562

2546-
if (routine.ReturnsSet && endpoint.Raw is false && binary is false && endpoint.ReturnSingleRecord is false)
2563+
// Per-command single record: for multi-command, use per-command IsSingle only;
2564+
// for single-command, use endpoint-level ReturnSingleRecord
2565+
var isSingleRecord = multiCmd is not null
2566+
? (multiCmdIndex < multiCmd.Length && multiCmd[multiCmdIndex].IsSingle)
2567+
: endpoint.ReturnSingleRecord;
2568+
2569+
2570+
2571+
2572+
if (routine.ReturnsSet && endpoint.Raw is false && binary is false && isSingleRecord is false)
25472573
{
25482574
writer.Advance(Encoding.UTF8.GetBytes(Consts.OpenBracket.ToString().AsSpan(), writer.GetSpan(Encoding.UTF8.GetMaxByteCount(1))));
25492575
if (shouldCache)
@@ -2873,7 +2899,7 @@ descriptor.CompositeFieldNames is not null &&
28732899
cacheBuffer = null; // Release memory
28742900
}
28752901

2876-
if (endpoint.ReturnSingleRecord)
2902+
if (isSingleRecord)
28772903
{
28782904
break;
28792905
}
@@ -2891,9 +2917,14 @@ descriptor.CompositeFieldNames is not null &&
28912917
}
28922918
} // end while
28932919

2894-
if (endpoint.ReturnSingleRecord && rowCount == 0)
2920+
if (isSingleRecord && rowCount == 0)
28952921
{
2896-
if (endpoint.TextResponseNullHandling == TextResponseNullHandling.NullLiteral)
2922+
if (multiCmd is not null)
2923+
{
2924+
// Multi-command: always write null for empty @single results
2925+
writer.Advance(Encoding.UTF8.GetBytes(Consts.Null.AsSpan(), writer.GetSpan(Encoding.UTF8.GetMaxByteCount(Consts.Null.Length))));
2926+
}
2927+
else if (endpoint.TextResponseNullHandling == TextResponseNullHandling.NullLiteral)
28972928
{
28982929
writer.Advance(Encoding.UTF8.GetBytes(Consts.Null.AsSpan(), writer.GetSpan(Encoding.UTF8.GetMaxByteCount(Consts.Null.Length))));
28992930
await writer.FlushAsync(cancellationToken);
@@ -2920,7 +2951,7 @@ descriptor.CompositeFieldNames is not null &&
29202951
WriteStringBuilderToWriter(rowBuilder, writer);
29212952
await writer.FlushAsync(cancellationToken);
29222953
}
2923-
if (routine.ReturnsSet && endpoint.Raw is false && endpoint.ReturnSingleRecord is false)
2954+
if (routine.ReturnsSet && endpoint.Raw is false && isSingleRecord is false)
29242955
{
29252956
writer.Advance(Encoding.UTF8.GetBytes(Consts.CloseBracket.ToString().AsSpan(), writer.GetSpan(Encoding.UTF8.GetMaxByteCount(1))));
29262957
if (shouldCache)

‎NpgsqlRest/Routine.cs‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -217,4 +217,10 @@ public class MultiCommandInfo
217217
/// When true, this command returns a flat array of values instead of objects.
218218
/// </summary>
219219
public bool ReturnsUnnamedSet { get; init; }
220+
221+
/// <summary>
222+
/// When true, only the first row of this command's result set is returned as a JSON object
223+
/// instead of a JSON array. Configured via positional @single annotation.
224+
/// </summary>
225+
public bool IsSingle { get; init; }
220226
}

‎NpgsqlRestTests/SqlFileSourceTests/MultiCommandTests/MultiCommandSingleTests.cs‎

Lines changed: 234 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,234 @@
1+
using NpgsqlRestTests.Setup;
2+
3+
namespace NpgsqlRestTests.SqlFileSourceTests;
4+
5+
public static partial class SqlFiles
6+
{
7+
public static void MultiCommandSingleTests()
8+
{
9+
// Per-command @single on first command only
10+
File.WriteAllText(Path.Combine(Dir, "multi_single_first.sql"), """
11+
-- HTTP GET
12+
-- @param $1 id
13+
-- @single
14+
select id, name from sql_describe_test where id = $1;
15+
select id, name from sql_describe_test where id = $1;
16+
""");
17+
18+
// Per-command @single on second command only
19+
File.WriteAllText(Path.Combine(Dir, "multi_single_second.sql"), """
20+
-- HTTP GET
21+
-- @param $1 id
22+
select id, name from sql_describe_test where id = $1;
23+
-- @single
24+
select id, name from sql_describe_test where id = $1;
25+
""");
26+
27+
// Per-command @single on both commands
28+
File.WriteAllText(Path.Combine(Dir, "multi_single_both.sql"), """
29+
-- HTTP GET
30+
-- @param $1 id
31+
-- @single
32+
select id, name from sql_describe_test where id = $1;
33+
-- @single
34+
select id, name from sql_describe_test where id = $1;
35+
""");
36+
37+
// Per-command @single with a void command in between
38+
File.WriteAllText(Path.Combine(Dir, "multi_single_with_void.sql"), """
39+
-- HTTP POST
40+
-- @param $1 id
41+
-- @single
42+
select id, name from sql_describe_test where id = $1;
43+
update sql_describe_test set active = true where id = $1;
44+
select count(*) as total from sql_describe_test;
45+
""");
46+
47+
// Multi-command without @single for comparison
48+
File.WriteAllText(Path.Combine(Dir, "multi_no_single.sql"), """
49+
-- HTTP GET
50+
-- @param $1 id
51+
select id, name from sql_describe_test where id = $1;
52+
select id, name from sql_describe_test where id = $1;
53+
""");
54+
55+
// Per-command @single with multi-row query — only first row returned
56+
File.WriteAllText(Path.Combine(Dir, "multi_single_multi_row.sql"), """
57+
-- HTTP GET
58+
select id, name from sql_describe_test order by id;
59+
-- @single
60+
select id, name from sql_describe_test order by id;
61+
""");
62+
63+
// Positional @result combined with @single
64+
File.WriteAllText(Path.Combine(Dir, "multi_single_named.sql"), """
65+
-- HTTP GET
66+
-- @param $1 id
67+
-- @single
68+
-- @result user
69+
select id, name from sql_describe_test where id = $1;
70+
-- @result items
71+
select id, name from sql_describe_test order by id;
72+
""");
73+
74+
// Positional @result without @single
75+
File.WriteAllText(Path.Combine(Dir, "multi_positional_result.sql"), """
76+
-- HTTP GET
77+
-- @param $1 id
78+
-- @result lookup
79+
select id, name from sql_describe_test where id = $1;
80+
-- @result all_items
81+
select id, name from sql_describe_test order by id;
82+
""");
83+
84+
// Positional @result with "is" syntax
85+
File.WriteAllText(Path.Combine(Dir, "multi_positional_result_is.sql"), """
86+
-- HTTP GET
87+
-- @param $1 id
88+
-- @result is details
89+
select id, name from sql_describe_test where id = $1;
90+
select count(*) as total from sql_describe_test;
91+
""");
92+
93+
// Mixed: @resultN (numbered) and positional @result coexist
94+
File.WriteAllText(Path.Combine(Dir, "multi_mixed_result.sql"), """
95+
-- HTTP GET
96+
-- @param $1 id
97+
-- @result2 count
98+
select id, name from sql_describe_test where id = $1;
99+
-- @result first
100+
select id, name from sql_describe_test order by id limit 1;
101+
""");
102+
}
103+
}
104+
105+
[Collection("SqlFileSourceFixture")]
106+
public class MultiCommandSingleTests(SqlFileSourceTestFixture test)
107+
{
108+
[Fact]
109+
public async Task MultiSingleFirst_FirstIsObjectSecondIsArray()
110+
{
111+
using var response = await test.Client.GetAsync("/api/multi-single-first?id=1");
112+
var content = await response.Content.ReadAsStringAsync();
113+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
114+
content.Should().Be("{\"result1\":{\"id\":1,\"name\":\"test1\"},\"result2\":[{\"id\":1,\"name\":\"test1\"}]}");
115+
}
116+
117+
[Fact]
118+
public async Task MultiSingleSecond_FirstIsArraySecondIsObject()
119+
{
120+
using var response = await test.Client.GetAsync("/api/multi-single-second?id=1");
121+
var content = await response.Content.ReadAsStringAsync();
122+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
123+
content.Should().Be("{\"result1\":[{\"id\":1,\"name\":\"test1\"}],\"result2\":{\"id\":1,\"name\":\"test1\"}}");
124+
}
125+
126+
[Fact]
127+
public async Task MultiSingleBoth_BothAreObjects()
128+
{
129+
using var response = await test.Client.GetAsync("/api/multi-single-both?id=1");
130+
var content = await response.Content.ReadAsStringAsync();
131+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
132+
content.Should().Be("{\"result1\":{\"id\":1,\"name\":\"test1\"},\"result2\":{\"id\":1,\"name\":\"test1\"}}");
133+
}
134+
135+
[Fact]
136+
public async Task MultiSingleWithVoid_SingleObjectThenVoidThenArray()
137+
{
138+
using var response = await test.Client.PostAsync("/api/multi-single-with-void",
139+
new StringContent("{\"id\":1}", System.Text.Encoding.UTF8, "application/json"));
140+
var content = await response.Content.ReadAsStringAsync();
141+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
142+
143+
using var doc = JsonDocument.Parse(content);
144+
// result1: single object
145+
doc.RootElement.GetProperty("result1").ValueKind.Should().Be(JsonValueKind.Object);
146+
doc.RootElement.GetProperty("result1").GetProperty("id").GetInt32().Should().Be(1);
147+
// result2: rows affected (number)
148+
doc.RootElement.GetProperty("result2").ValueKind.Should().Be(JsonValueKind.Number);
149+
// result3: array
150+
doc.RootElement.GetProperty("result3").ValueKind.Should().Be(JsonValueKind.Array);
151+
}
152+
153+
[Fact]
154+
public async Task MultiNoSingle_BothAreArrays()
155+
{
156+
using var response = await test.Client.GetAsync("/api/multi-no-single?id=1");
157+
var content = await response.Content.ReadAsStringAsync();
158+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
159+
content.Should().Be("{\"result1\":[{\"id\":1,\"name\":\"test1\"}],\"result2\":[{\"id\":1,\"name\":\"test1\"}]}");
160+
}
161+
162+
[Fact]
163+
public async Task MultiSingleMultiRow_SingleReturnsOnlyFirstRow()
164+
{
165+
using var response = await test.Client.GetAsync("/api/multi-single-multi-row");
166+
var content = await response.Content.ReadAsStringAsync();
167+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
168+
169+
using var doc = JsonDocument.Parse(content);
170+
// result1: array (no @single)
171+
doc.RootElement.GetProperty("result1").ValueKind.Should().Be(JsonValueKind.Array);
172+
doc.RootElement.GetProperty("result1").GetArrayLength().Should().BeGreaterThan(1);
173+
// result2: single object (first row only)
174+
doc.RootElement.GetProperty("result2").ValueKind.Should().Be(JsonValueKind.Object);
175+
doc.RootElement.GetProperty("result2").GetProperty("id").GetInt32().Should().Be(1);
176+
}
177+
178+
[Fact]
179+
public async Task MultiSingleNamed_PositionalResultWithSingle()
180+
{
181+
using var response = await test.Client.GetAsync("/api/multi-single-named?id=1");
182+
var content = await response.Content.ReadAsStringAsync();
183+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
184+
185+
using var doc = JsonDocument.Parse(content);
186+
// "user" key with single object
187+
doc.RootElement.GetProperty("user").ValueKind.Should().Be(JsonValueKind.Object);
188+
doc.RootElement.GetProperty("user").GetProperty("id").GetInt32().Should().Be(1);
189+
// "items" key with array
190+
doc.RootElement.GetProperty("items").ValueKind.Should().Be(JsonValueKind.Array);
191+
}
192+
193+
[Fact]
194+
public async Task MultiPositionalResult_CustomNames()
195+
{
196+
using var response = await test.Client.GetAsync("/api/multi-positional-result?id=1");
197+
var content = await response.Content.ReadAsStringAsync();
198+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
199+
200+
using var doc = JsonDocument.Parse(content);
201+
doc.RootElement.TryGetProperty("lookup", out _).Should().BeTrue();
202+
doc.RootElement.TryGetProperty("all_items", out _).Should().BeTrue();
203+
// Should NOT have result1/result2
204+
doc.RootElement.TryGetProperty("result1", out _).Should().BeFalse();
205+
doc.RootElement.TryGetProperty("result2", out _).Should().BeFalse();
206+
}
207+
208+
[Fact]
209+
public async Task MultiPositionalResultIs_IsSyntax()
210+
{
211+
using var response = await test.Client.GetAsync("/api/multi-positional-result-is?id=1");
212+
var content = await response.Content.ReadAsStringAsync();
213+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
214+
215+
using var doc = JsonDocument.Parse(content);
216+
doc.RootElement.TryGetProperty("details", out _).Should().BeTrue();
217+
// Second result uses default name (no positional annotation)
218+
doc.RootElement.TryGetProperty("result2", out _).Should().BeTrue();
219+
}
220+
221+
[Fact]
222+
public async Task MultiMixedResult_NumberedAndPositionalCoexist()
223+
{
224+
using var response = await test.Client.GetAsync("/api/multi-mixed-result?id=1");
225+
var content = await response.Content.ReadAsStringAsync();
226+
response.StatusCode.Should().Be(HttpStatusCode.OK, $"Response: {content}");
227+
228+
using var doc = JsonDocument.Parse(content);
229+
// First result: no annotation → default "result1"
230+
doc.RootElement.TryGetProperty("result1", out _).Should().BeTrue();
231+
// Second result: positional @result wins over @result2 (numbered)
232+
doc.RootElement.TryGetProperty("first", out _).Should().BeTrue();
233+
}
234+
}

0 commit comments

Comments
 (0)