lib/sql/src/statement/execute.zig
daab053ee43316e1809a84551d573ddd1e5bf3d2
1 const std = @import("std");
2 const alloc_phase = @import("alloc_phase");
3 const sql = @import("../root.zig");
4 const access_mod = @import("access.zig");
5 const ast_mod = @import("ast.zig");
6 const cursor_mod = @import("cursor.zig");
7 const insert_mod = @import("insert/root.zig");
8 const parse_mod = @import("parse.zig");
9 const predicate_mod = @import("predicate.zig");
10 const result_mod = @import("result.zig");
11 const branch_mod = sql.branch;
12 const catalog_mod = sql.catalog;
13 const file = sql.file;
14 const page = sql.page;
15 const plan = sql.plan;
16 const relation_mod = sql.relation;
17 const row = sql.row;
18 const session_mod = sql.session;
19 const trace = sql.trace;
20 const tree = sql.tree;
21 const version = sql.version;
22 const wal = sql.wal;
23
24 const Allocator = std.mem.Allocator;
25
26 pub const UpdateExecution = struct {
27 assignment_fields: []usize,
28
29 fn deinit(self: *UpdateExecution, allocator: Allocator) void {
30 allocator.free(self.assignment_fields);
31 self.* = undefined;
32 }
33 };
34
35 pub const CreateIndexExecution = struct {
36 fields: []usize,
37 columns: []row.Column,
38
39 fn deinit(self: *CreateIndexExecution, allocator: Allocator) void {
40 allocator.free(self.columns);
41 allocator.free(self.fields);
42 self.* = undefined;
43 }
44 };
45
46 pub const ProjectedColumn = union(enum) {
47 rowid,
48 field: usize,
49 };
50
51 pub const OrderField = struct {
52 source: ProjectedColumn,
53 collation: row.Collation,
54 descending: bool,
55 };
56
57 pub fn orderIsRowidAscending(order_fields: []const OrderField) bool {
58 if (order_fields.len == 0) return false;
59 const leading = order_fields[0];
60 return leading.source == .rowid and !leading.descending;
61 }
62
63 pub const SelectExecution = struct {
64 fields: []ProjectedColumn,
65 order_fields: []OrderField,
66 access: access_mod.SelectAccess,
67
68 fn deinit(self: *SelectExecution, allocator: Allocator) void {
69 allocator.free(self.order_fields);
70 allocator.free(self.fields);
71 self.* = undefined;
72 }
73 };
74
75 pub const Window = struct {
76 skip: usize = 0,
77 remaining: ?usize = null,
78
79 pub fn full(self: *const Window) bool {
80 if (self.remaining) |remaining| return remaining == 0;
81 return false;
82 }
83
84 pub fn admit(self: *Window) bool {
85 if (self.skip > 0) {
86 self.skip -= 1;
87 return false;
88 }
89 if (self.remaining) |*remaining| {
90 if (remaining.* == 0) return false;
91 remaining.* -= 1;
92 }
93 return true;
94 }
95 };
96
97 pub const Execution = union(enum) {
98 insert: insert_mod.Execution,
99 select: SelectExecution,
100 update: UpdateExecution,
101 delete,
102 create_table,
103 create_index: CreateIndexExecution,
104 drop_table,
105 analyze,
106
107 fn deinit(self: *Execution, allocator: Allocator) void {
108 switch (self.*) {
109 .insert => |*insert| insert.deinit(allocator),
110 .select => |*select| select.deinit(allocator),
111 .update => |*update| update.deinit(allocator),
112 .delete => {},
113 .create_table => {},
114 .create_index => |*create_index| create_index.deinit(allocator),
115 .drop_table => {},
116 .analyze => {},
117 }
118 self.* = undefined;
119 }
120 };
121
122 pub const ExecuteOptions = struct {
123 durability: file.CommitDurability = .synced,
124 validate_indexes: bool = false,
125 session: ?*session_mod.DatabaseSession = null,
126 write: ?*session_mod.DatabaseWrite = null,
127
128 fn commit(self: ExecuteOptions) file.CommitOptions {
129 return .{
130 .durability = self.durability,
131 .validate_indexes = self.validate_indexes,
132 };
133 }
134
135 fn databaseSession(self: ExecuteOptions) ast_mod.Error!*session_mod.DatabaseSession {
136 if (self.write) |write| return write.session;
137 return self.session orelse error.WriteSessionRequired;
138 }
139 };
140
141 pub const Prepared = struct {
142 allocator: Allocator,
143 catalog: catalog_mod.Catalog,
144 source: []u8,
145 statement: ast_mod.Statement,
146 parameters: []ast_mod.Parameter,
147 bindings: []result_mod.Binding,
148 relation: ?plan.PreparedRelation,
149 execution: Execution,
150 validation: plan.Validation = .content,
151
152 pub fn deinit(self: *Prepared) void {
153 for (self.bindings) |*binding| binding.deinit(self.allocator);
154 self.allocator.free(self.bindings);
155 self.allocator.free(self.parameters);
156 self.execution.deinit(self.allocator);
157 if (self.relation) |*relation| relation.deinit();
158 self.statement.deinit(self.allocator);
159 self.allocator.free(self.source);
160 self.* = undefined;
161 }
162
163 pub fn execute(self: *Prepared, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!result_mod.Result {
164 return self.executeResolved(result_allocator, options) catch |err| switch (err) {
165 error.RelationNotFound => error.TableNotFound,
166 else => err,
167 };
168 }
169
170 fn executeResolved(self: *Prepared, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!result_mod.Result {
171 return self.executeOnce(result_allocator, options) catch |err| switch (err) {
172 error.PlanChanged => {
173 try self.reprepare();
174 return try self.executeOnce(result_allocator, options);
175 },
176 else => err,
177 };
178 }
179
180 /// Opens a cursor over the rows the select returns in `target`, which the
181 /// cursor fills in place. `target` is undefined after an error.
182 pub fn openCursor(
183 self: *Prepared,
184 target: *cursor_mod.Cursor,
185 result_allocator: Allocator,
186 ) ast_mod.Error!void {
187 self.openCursorResolved(target, result_allocator) catch |err| return switch (err) {
188 error.RelationNotFound => error.TableNotFound,
189 else => err,
190 };
191 }
192
193 fn openCursorResolved(
194 self: *Prepared,
195 target: *cursor_mod.Cursor,
196 result_allocator: Allocator,
197 ) ast_mod.Error!void {
198 self.openCursorOnce(target, result_allocator) catch |err| switch (err) {
199 error.PlanChanged => {
200 try self.reprepare();
201 try self.openCursorOnce(target, result_allocator);
202 },
203 else => return err,
204 };
205 }
206
207 pub fn parameterCount(self: *const Prepared) usize {
208 return self.parameters.len;
209 }
210
211 pub fn cacheKey(self: *const Prepared) plan.PlanKey {
212 return self.relation.?.cacheKey();
213 }
214
215 pub fn currentCacheKey(self: *Prepared) ast_mod.Error!plan.PlanKey {
216 const relation = try self.relationPtr();
217 return try relation.currentCacheKey();
218 }
219
220 pub fn parameterName(self: *const Prepared, index: usize) ast_mod.Error!?[]const u8 {
221 if (index == 0 or index > self.parameters.len) return error.ParameterIndexOutOfBounds;
222 return self.parameters[index - 1].name;
223 }
224
225 pub fn parameterIndex(self: *const Prepared, name: []const u8) ?usize {
226 for (self.parameters, 0..) |parameter, offset| {
227 if (parameter.name) |parameter_name| {
228 if (std.mem.eql(u8, parameter_name, name)) return offset + 1;
229 }
230 }
231 return null;
232 }
233
234 pub fn bind(self: *Prepared, index: usize, bound_value: row.Value) ast_mod.Error!void {
235 if (index == 0 or index > self.bindings.len) return error.ParameterIndexOutOfBounds;
236 try self.bindings[index - 1].set(self.allocator, bound_value);
237 }
238
239 pub fn bindName(self: *Prepared, name: []const u8, bound_value: row.Value) ast_mod.Error!void {
240 const index = self.parameterIndex(name) orelse return error.ParameterNotFound;
241 try self.bind(index, bound_value);
242 }
243
244 pub fn clearBinding(self: *Prepared, index: usize) ast_mod.Error!void {
245 if (index == 0 or index > self.bindings.len) return error.ParameterIndexOutOfBounds;
246 self.bindings[index - 1].clear();
247 }
248
249 pub fn clearBindings(self: *Prepared) void {
250 for (self.bindings) |*binding| binding.clear();
251 }
252
253 fn executeOnce(self: *Prepared, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!result_mod.Result {
254 const phase = trace.scope("statement.execute");
255 defer phase.end();
256
257 return switch (self.statement) {
258 .insert => |insert| try self.executeInsert(insert, result_allocator, options),
259 .select => |select| .{
260 .rows = try self.executeSelect(select, result_allocator),
261 },
262 .update => |update| try self.executeUpdate(update, result_allocator, options),
263 .delete => |delete| try self.executeDelete(delete, result_allocator, options),
264 .create_table => |create_table| .{
265 .catalog = try self.executeCreateTable(create_table, result_allocator, options),
266 },
267 .create_index => |create_index| .{
268 .catalog = try self.executeCreateIndex(create_index, result_allocator, options),
269 },
270 .drop_table => |drop_table| .{
271 .catalog = try self.executeDropTable(drop_table, result_allocator, options),
272 },
273 .analyze => |analyze| .{
274 .catalog = try self.executeAnalyze(analyze, result_allocator, options),
275 },
276 };
277 }
278
279 fn executeInsert(self: *Prepared, insert: ast_mod.Insert, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!result_mod.Result {
280 const insert_execution = switch (self.execution) {
281 .insert => |*execution| execution,
282 else => unreachable,
283 };
284 const relation = try self.relationPtr();
285 const rowid_slot = insert_execution.rowidSlot();
286 std.debug.assert(rowid_slot < insert.values.len);
287 const rowid = try self.rowidValue(insert.values[rowid_slot]);
288 const values = insert_execution.resolve(
289 insert,
290 relation.handle.definitions,
291 self,
292 );
293 return try applyEdits(relation, result_allocator, options, &.{.{ .put = .{
294 .rowid = rowid,
295 .values = values,
296 } }});
297 }
298
299 fn executeUpdate(self: *Prepared, update: ast_mod.Update, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!result_mod.Result {
300 const update_execution = switch (self.execution) {
301 .update => |execution| execution,
302 else => unreachable,
303 };
304 const relation = try self.relationPtr();
305 const assignments = try result_allocator.alloc(relation_mod.Edit.Assignment, update.assignments.len);
306 defer result_allocator.free(assignments);
307 for (assignments, update.assignments, update_execution.assignment_fields) |*target, assignment, field| {
308 target.* = .{
309 .column = field,
310 .value = self.expressionValue(assignment.value),
311 };
312 }
313 if (predicate_mod.rowidEqualityOnly(update.predicates)) |expression| {
314 return try applyEdits(relation, result_allocator, options, &.{.{ .update = .{
315 .rowid = try self.rowidValue(expression),
316 .assignments = assignments,
317 } }});
318 }
319 const matched = try self.matchedRowids(relation, update.predicates, result_allocator, options);
320 defer result_allocator.free(matched);
321 const edits = try result_allocator.alloc(PendingEdit, matched.len);
322 defer result_allocator.free(edits);
323 for (edits, matched) |*edit, rowid| {
324 edit.* = .{ .update = .{
325 .rowid = rowid,
326 .assignments = assignments,
327 } };
328 }
329 return try applyEdits(relation, result_allocator, options, edits);
330 }
331
332 fn executeDelete(self: *Prepared, delete: ast_mod.Delete, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!result_mod.Result {
333 const relation = try self.relationPtr();
334 if (predicate_mod.rowidEqualityOnly(delete.predicates)) |expression| {
335 return try applyEdits(relation, result_allocator, options, &.{.{
336 .delete = try self.rowidValue(expression),
337 }});
338 }
339 const matched = try self.matchedRowids(relation, delete.predicates, result_allocator, options);
340 defer result_allocator.free(matched);
341 const edits = try result_allocator.alloc(PendingEdit, matched.len);
342 defer result_allocator.free(edits);
343 for (edits, matched) |*edit, rowid| edit.* = .{ .delete = rowid };
344 return try applyEdits(relation, result_allocator, options, edits);
345 }
346
347 fn matchedRowids(self: *Prepared, relation: *plan.PreparedRelation, predicates: []const ast_mod.Predicate, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error![]i64 {
348 const phase = trace.scope("statement.matched_rowids");
349 defer phase.end();
350
351 var reader = try relation.execute();
352 defer reader.deinit();
353 var predicate_stack: [relation_mod.max_index_fields + 1]ast_mod.Predicate = undefined;
354 var value_stack: [relation_mod.max_index_fields + 1]row.Value = undefined;
355 const runtime = try predicate_mod.runtimePredicates(self, predicates, result_allocator, &predicate_stack, &value_stack);
356 defer runtime.deinit(result_allocator);
357 var overlay = try StagedOverlay.init(
358 result_allocator,
359 options,
360 relation,
361 reader.borrow(),
362 );
363 defer overlay.deinit();
364 var matched: std.ArrayList(i64) = .empty;
365 errdefer matched.deinit(result_allocator);
366 if (predicate_mod.firstRowidPredicate(predicates, .eq)) |offset| {
367 const rowid = try predicate_mod.rowidFromValue(runtime.values[offset]);
368 if (overlay.visible(rowid)) |staged| {
369 if (staged) |bytes| {
370 if (try predicate_mod.rowBytesMatchPredicates(runtime.predicates, runtime.values, &relation.handle, rowid, bytes)) {
371 try matched.append(result_allocator, rowid);
372 }
373 }
374 } else if (try reader.get(result_allocator, rowid)) |bytes| {
375 defer result_allocator.free(bytes);
376 if (try predicate_mod.rowBytesMatchPredicates(runtime.predicates, runtime.values, &relation.handle, rowid, bytes)) {
377 try matched.append(result_allocator, rowid);
378 }
379 }
380 return try matched.toOwnedSlice(result_allocator);
381 }
382 var start: ?i64 = null;
383 var end: ?i64 = null;
384 if (predicate_mod.firstRowidRangePredicate(predicates)) |offset| {
385 const rowid = try predicate_mod.rowidFromValue(runtime.values[offset]);
386 start = predicate_mod.rowidStart(predicates[offset].operator, rowid);
387 end = predicate_mod.rowidEnd(predicates[offset].operator, rowid);
388 }
389 var scan: sql.TableScan = undefined;
390 try reader.scan(&scan, result_allocator, start, end);
391 defer scan.deinit();
392 while (try scan.next()) |entry| {
393 if (overlay.shadows(entry.rowid)) continue;
394 if (!try predicate_mod.rowBytesMatchPredicates(runtime.predicates, runtime.values, &relation.handle, entry.rowid, entry.bytes)) continue;
395 try matched.append(result_allocator, entry.rowid);
396 }
397 var overlay_rows = overlay.rows.iterator();
398 while (overlay_rows.next()) |staged| {
399 const bytes = staged.value_ptr.* orelse continue;
400 if (!try predicate_mod.rowBytesMatchPredicates(runtime.predicates, runtime.values, &relation.handle, staged.key_ptr.*, bytes)) continue;
401 try matched.append(result_allocator, staged.key_ptr.*);
402 }
403 std.mem.sort(i64, matched.items, {}, std.sort.asc(i64));
404 return try matched.toOwnedSlice(result_allocator);
405 }
406
407 fn executeCreateTable(self: *Prepared, create_table: ast_mod.CreateTable, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!session_mod.CatalogFlush {
408 const database_session = try options.databaseSession();
409 return try database_session.createRelation(result_allocator, &self.catalog, create_table.relationDefinition(), options.commit());
410 }
411
412 fn executeCreateIndex(self: *Prepared, create_index: ast_mod.CreateIndex, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!session_mod.CatalogFlush {
413 const database_session = try options.databaseSession();
414 const create_index_execution = switch (self.execution) {
415 .create_index => |execution| execution,
416 else => unreachable,
417 };
418 return try database_session.createIndex(result_allocator, &self.catalog, create_index.table, .{
419 .name = create_index.name,
420 .fields = create_index_execution.fields,
421 .columns = create_index_execution.columns,
422 }, options.commit());
423 }
424
425 fn executeDropTable(self: *Prepared, drop_table: ast_mod.DropTable, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!session_mod.CatalogFlush {
426 const database_session = try options.databaseSession();
427 return try database_session.dropRelation(result_allocator, &self.catalog, drop_table.table, options.commit());
428 }
429
430 fn executeAnalyze(self: *Prepared, analyze: ast_mod.Analyze, result_allocator: Allocator, options: ExecuteOptions) ast_mod.Error!session_mod.CatalogFlush {
431 const database_session = try options.databaseSession();
432 return try database_session.analyzeRelation(result_allocator, &self.catalog, analyze.table, options.commit());
433 }
434
435 fn executeSelect(self: *Prepared, select: ast_mod.Select, result_allocator: Allocator) ast_mod.Error!result_mod.Rows {
436 const select_execution = switch (self.execution) {
437 .select => |execution| execution,
438 else => unreachable,
439 };
440 if (select_execution.order_fields.len != 0 and !orderIsRowidAscending(select_execution.order_fields)) {
441 return try self.executeSortedSelect(select, result_allocator);
442 }
443 const relation = try self.relationPtr();
444 var reader = try relation.execute();
445 defer reader.deinit();
446 var predicate_stack: [relation_mod.max_index_fields + 1]ast_mod.Predicate = undefined;
447 var value_stack: [relation_mod.max_index_fields + 1]row.Value = undefined;
448 const predicates = try predicate_mod.runtimePredicates(self, select.predicates, result_allocator, &predicate_stack, &value_stack);
449 defer predicates.deinit(result_allocator);
450 const access = try access_mod.runtimeSelectAccess(select, relation, select_execution.fields, predicates.values);
451 if (select_execution.order_fields.len != 0 and
452 (access == .index or access == .covering))
453 {
454 return try self.executeSortedSelect(select, result_allocator);
455 }
456 var window = try self.selectWindow(select);
457 switch (access) {
458 .rowid => |rowid_access| if (rowid_access.operator == .eq) {
459 const rowid = try predicate_mod.rowidFromValue(predicates.values[rowid_access.predicate_index]);
460 const bytes = (try reader.get(result_allocator, rowid)) orelse return .{
461 .allocator = result_allocator,
462 .storage = .{ .single = null },
463 };
464 errdefer result_allocator.free(bytes);
465 if (!try predicate_mod.rowBytesMatchPredicates(predicates.predicates, predicates.values, &relation.handle, rowid, bytes)) {
466 result_allocator.free(bytes);
467 return .{
468 .allocator = result_allocator,
469 .storage = .{ .single = null },
470 };
471 }
472 if (!window.admit()) {
473 result_allocator.free(bytes);
474 return .{
475 .allocator = result_allocator,
476 .storage = .{ .single = null },
477 };
478 }
479 if (select_execution.fields.len == 0) {
480 return .{
481 .allocator = result_allocator,
482 .storage = .{ .single = bytes },
483 };
484 }
485 const projected = try result_mod.selectedRow(result_allocator, select_execution.fields, rowid, bytes);
486 result_allocator.free(bytes);
487 return .{
488 .allocator = result_allocator,
489 .storage = .{ .single = projected },
490 };
491 },
492 else => {},
493 }
494 var selected: std.ArrayList([]u8) = .empty;
495 errdefer result_mod.freeSelectedRows(result_allocator, selected.items);
496 switch (access) {
497 .rowid => |rowid_access| {
498 const rowid = try predicate_mod.rowidFromValue(predicates.values[rowid_access.predicate_index]);
499 var scan: sql.TableScan = undefined;
500 try reader.scan(
501 &scan,
502 result_allocator,
503 predicate_mod.rowidStart(rowid_access.operator, rowid),
504 predicate_mod.rowidEnd(rowid_access.operator, rowid),
505 );
506 defer scan.deinit();
507 while (try scan.next()) |entry| {
508 if (window.full()) break;
509 if (!predicate_mod.rowidMatches(entry.rowid, rowid_access.operator, rowid)) continue;
510 if (!try predicate_mod.rowBytesMatchPredicates(predicates.predicates, predicates.values, &relation.handle, entry.rowid, entry.bytes)) continue;
511 if (!window.admit()) continue;
512 try result_mod.appendSelectedRow(result_allocator, &selected, select_execution.fields, entry.rowid, entry.bytes);
513 }
514 },
515 .index => |indexed| {
516 var bound_storage: [relation_mod.max_index_fields]row.Value = undefined;
517 const bound_values = predicate_mod.indexedPrefixValues(indexed, predicates.values, &bound_storage);
518 const covered = access_mod.indexCoversPredicates(&relation.handle, indexed.index_slot, select_execution.fields, predicates.predicates);
519 var index_scan: sql.IndexScan = undefined;
520 switch (indexed.operator) {
521 .eq => try reader.lookup(
522 &index_scan,
523 result_allocator,
524 indexed.index_slot,
525 bound_values,
526 ),
527 .lt, .lte, .gt, .gte => try reader.indexRange(
528 &index_scan,
529 result_allocator,
530 indexed.index_slot,
531 predicate_mod.indexedRangeStart(indexed, bound_values),
532 predicate_mod.indexedRangeEnd(indexed, bound_values),
533 ),
534 }
535 defer index_scan.deinit();
536 while (try index_scan.next()) |entry| {
537 if (window.full()) break;
538 if (covered) {
539 var index_values: [catalog_mod.max_columns]row.Value = undefined;
540 var index_scratch: [page.size]u8 = undefined;
541 const values = try predicate_mod.coveredIndexValues(
542 predicates.predicates,
543 predicates.values,
544 &relation.handle,
545 indexed.index_slot,
546 entry,
547 &index_values,
548 &index_scratch,
549 ) orelse continue;
550 if (!window.admit()) continue;
551 try result_mod.appendSelectedIndexRow(
552 result_allocator,
553 &selected,
554 select_execution.fields,
555 &relation.handle,
556 indexed.index_slot,
557 values,
558 entry.rowid,
559 );
560 continue;
561 }
562 const bytes = (try reader.get(result_allocator, entry.rowid)) orelse continue;
563 errdefer result_allocator.free(bytes);
564 if (!try predicate_mod.rowBytesMatchPredicates(predicates.predicates, predicates.values, &relation.handle, entry.rowid, bytes)) {
565 result_allocator.free(bytes);
566 continue;
567 }
568 if (!window.admit()) {
569 result_allocator.free(bytes);
570 continue;
571 }
572 try result_mod.appendSelectedRow(result_allocator, &selected, select_execution.fields, entry.rowid, bytes);
573 result_allocator.free(bytes);
574 }
575 },
576 .covering => unreachable,
577 .scan => {
578 var scan: sql.TableScan = undefined;
579 try reader.scan(&scan, result_allocator, null, null);
580 defer scan.deinit();
581 while (try scan.next()) |entry| {
582 if (window.full()) break;
583 if (!try predicate_mod.rowBytesMatchPredicates(predicates.predicates, predicates.values, &relation.handle, entry.rowid, entry.bytes)) continue;
584 if (!window.admit()) continue;
585 try result_mod.appendSelectedRow(result_allocator, &selected, select_execution.fields, entry.rowid, entry.bytes);
586 }
587 },
588 }
589 return .{
590 .allocator = result_allocator,
591 .storage = .{ .many = try selected.toOwnedSlice(result_allocator) },
592 };
593 }
594
595 fn executeSortedSelect(self: *Prepared, select: ast_mod.Select, result_allocator: Allocator) ast_mod.Error!result_mod.Rows {
596 const phase = trace.scope("statement.execute_sorted_select");
597 defer phase.end();
598
599 const relation = try self.relationPtr();
600 var reader = try relation.execute();
601 defer reader.deinit();
602 return .{
603 .allocator = result_allocator,
604 .storage = .{ .many = try self.sortedSelectedRows(select, result_allocator, &reader) },
605 };
606 }
607
608 fn sortedSelectedRows(
609 self: *Prepared,
610 select: ast_mod.Select,
611 result_allocator: Allocator,
612 reader: *const plan.RelationRead,
613 ) ast_mod.Error![][]u8 {
614 const select_execution = switch (self.execution) {
615 .select => |execution| execution,
616 else => unreachable,
617 };
618 const collected = try self.collectSortedRows(select, result_allocator, reader);
619 errdefer result_mod.freeSortedRows(result_allocator, collected);
620 std.mem.sort(result_mod.SortedRow, collected, result_mod.SortedRowContext{
621 .order_fields = select_execution.order_fields,
622 }, result_mod.sortedRowLessThan);
623 const window = try self.selectWindow(select);
624 const start = @min(window.skip, collected.len);
625 const end = if (window.remaining) |remaining| @min(start +| remaining, collected.len) else collected.len;
626 std.debug.assert(start <= end);
627 std.debug.assert(end <= collected.len);
628 const selected = try result_allocator.alloc([]u8, end - start);
629 for (selected, collected[start..end]) |*out, sorted_row| out.* = sorted_row.projected;
630 for (collected[0..start]) |sorted_row| result_allocator.free(sorted_row.projected);
631 for (collected[end..]) |sorted_row| result_allocator.free(sorted_row.projected);
632 for (collected) |sorted_row| result_mod.freeSortedKeys(result_allocator, sorted_row.keys);
633 result_allocator.free(collected);
634 return selected;
635 }
636
637 fn collectSortedRows(
638 self: *Prepared,
639 select: ast_mod.Select,
640 result_allocator: Allocator,
641 reader: *const plan.RelationRead,
642 ) ast_mod.Error![]result_mod.SortedRow {
643 const select_execution = switch (self.execution) {
644 .select => |execution| execution,
645 else => unreachable,
646 };
647 const relation = try self.relationPtr();
648 var predicate_stack: [relation_mod.max_index_fields + 1]ast_mod.Predicate = undefined;
649 var value_stack: [relation_mod.max_index_fields + 1]row.Value = undefined;
650 const predicates = try predicate_mod.runtimePredicates(self, select.predicates, result_allocator, &predicate_stack, &value_stack);
651 defer predicates.deinit(result_allocator);
652 const access = try access_mod.runtimeSelectAccess(select, relation, select_execution.fields, predicates.values);
653 var collected: std.ArrayList(result_mod.SortedRow) = .empty;
654 errdefer {
655 result_mod.freeSortedRowContents(result_allocator, collected.items);
656 collected.deinit(result_allocator);
657 }
658 switch (access) {
659 .rowid => |rowid_access| {
660 const rowid = try predicate_mod.rowidFromValue(predicates.values[rowid_access.predicate_index]);
661 if (rowid_access.operator == .eq) {
662 const bytes = (try reader.get(result_allocator, rowid)) orelse return try collected.toOwnedSlice(result_allocator);
663 defer result_allocator.free(bytes);
664 if (!try predicate_mod.rowBytesMatchPredicates(predicates.predicates, predicates.values, &relation.handle, rowid, bytes)) {
665 return try collected.toOwnedSlice(result_allocator);
666 }
667 try result_mod.appendSortedRow(result_allocator, &collected, select_execution.fields, select_execution.order_fields, rowid, bytes);
668 return try collected.toOwnedSlice(result_allocator);
669 }
670 var scan: sql.TableScan = undefined;
671 try reader.scan(
672 &scan,
673 result_allocator,
674 predicate_mod.rowidStart(rowid_access.operator, rowid),
675 predicate_mod.rowidEnd(rowid_access.operator, rowid),
676 );
677 defer scan.deinit();
678 while (try scan.next()) |entry| {
679 if (!predicate_mod.rowidMatches(entry.rowid, rowid_access.operator, rowid)) continue;
680 if (!try predicate_mod.rowBytesMatchPredicates(predicates.predicates, predicates.values, &relation.handle, entry.rowid, entry.bytes)) continue;
681 try result_mod.appendSortedRow(result_allocator, &collected, select_execution.fields, select_execution.order_fields, entry.rowid, entry.bytes);
682 }
683 },
684 .index => |indexed| {
685 var bound_storage: [relation_mod.max_index_fields]row.Value = undefined;
686 const bound_values = predicate_mod.indexedPrefixValues(indexed, predicates.values, &bound_storage);
687 var index_scan: sql.IndexScan = undefined;
688 switch (indexed.operator) {
689 .eq => try reader.lookup(
690 &index_scan,
691 result_allocator,
692 indexed.index_slot,
693 bound_values,
694 ),
695 .lt, .lte, .gt, .gte => try reader.indexRange(
696 &index_scan,
697 result_allocator,
698 indexed.index_slot,
699 predicate_mod.indexedRangeStart(indexed, bound_values),
700 predicate_mod.indexedRangeEnd(indexed, bound_values),
701 ),
702 }
703 defer index_scan.deinit();
704 const covered = access_mod.indexCoversPredicates(
705 &relation.handle,
706 indexed.index_slot,
707 select_execution.fields,
708 predicates.predicates,
709 ) and access_mod.indexCoversOrder(
710 &relation.handle,
711 indexed.index_slot,
712 select.order,
713 );
714 try collectIndexRows(
715 result_allocator,
716 &collected,
717 reader,
718 &index_scan,
719 select_execution,
720 &relation.handle,
721 predicates,
722 indexed.index_slot,
723 covered,
724 );
725 },
726 .covering => |index_slot| {
727 var index_scan: sql.IndexScan = undefined;
728 try reader.indexScan(&index_scan, result_allocator, index_slot, null, null);
729 defer index_scan.deinit();
730 try collectIndexRows(
731 result_allocator,
732 &collected,
733 reader,
734 &index_scan,
735 select_execution,
736 &relation.handle,
737 predicates,
738 index_slot,
739 true,
740 );
741 },
742 .scan => {
743 var scan: sql.TableScan = undefined;
744 try reader.scan(&scan, result_allocator, null, null);
745 defer scan.deinit();
746 while (try scan.next()) |entry| {
747 if (!try predicate_mod.rowBytesMatchPredicates(predicates.predicates, predicates.values, &relation.handle, entry.rowid, entry.bytes)) continue;
748 try result_mod.appendSortedRow(result_allocator, &collected, select_execution.fields, select_execution.order_fields, entry.rowid, entry.bytes);
749 }
750 },
751 }
752 return try collected.toOwnedSlice(result_allocator);
753 }
754
755 /// Appends a sorted row for each entry an open index scan yields. A covered scan builds the
756 /// row from the decoded key, and an uncovered scan reads the table row.
757 fn collectIndexRows(
758 result_allocator: Allocator,
759 collected: *std.ArrayList(result_mod.SortedRow),
760 reader: *const plan.RelationRead,
761 index_scan: *sql.IndexScan,
762 select_execution: SelectExecution,
763 handle: *const catalog_mod.RelationHandle,
764 predicates: predicate_mod.RuntimePredicates,
765 index_slot: usize,
766 covered: bool,
767 ) ast_mod.Error!void {
768 while (try index_scan.next()) |entry| {
769 if (covered) {
770 var index_values: [catalog_mod.max_columns]row.Value = undefined;
771 var index_scratch: [page.size]u8 = undefined;
772 const values = try predicate_mod.coveredIndexValues(
773 predicates.predicates,
774 predicates.values,
775 handle,
776 index_slot,
777 entry,
778 &index_values,
779 &index_scratch,
780 ) orelse continue;
781 try result_mod.appendSortedIndexRow(
782 result_allocator,
783 collected,
784 select_execution.fields,
785 select_execution.order_fields,
786 handle,
787 index_slot,
788 values,
789 entry.rowid,
790 );
791 continue;
792 }
793 const bytes = (try reader.get(result_allocator, entry.rowid)) orelse continue;
794 defer result_allocator.free(bytes);
795 if (!try predicate_mod.rowBytesMatchPredicates(
796 predicates.predicates,
797 predicates.values,
798 handle,
799 entry.rowid,
800 bytes,
801 )) continue;
802 try result_mod.appendSortedRow(
803 result_allocator,
804 collected,
805 select_execution.fields,
806 select_execution.order_fields,
807 entry.rowid,
808 bytes,
809 );
810 }
811 }
812
813 fn openCursorOnce(
814 self: *Prepared,
815 target: *cursor_mod.Cursor,
816 result_allocator: Allocator,
817 ) ast_mod.Error!void {
818 const phase = trace.scope("statement.cursor.open");
819 defer phase.end();
820
821 const select = switch (self.statement) {
822 .select => |select_statement| select_statement,
823 else => return error.UnsupportedStatement,
824 };
825 const select_execution = switch (self.execution) {
826 .select => |execution| execution,
827 else => unreachable,
828 };
829 const relation = try self.relationPtr();
830 var reader = try relation.execute();
831 var reader_live = true;
832 errdefer if (reader_live) reader.deinit();
833 var owned_predicates = try predicate_mod.ownedPredicates(result_allocator, self, select.predicates);
834 var owned_predicates_live = true;
835 errdefer if (owned_predicates_live) owned_predicates.deinit(result_allocator);
836 const access = try access_mod.runtimeSelectAccess(select, relation, select_execution.fields, owned_predicates.values);
837 const sort_needed = select_execution.order_fields.len != 0 and
838 (!orderIsRowidAscending(select_execution.order_fields) or
839 access == .index or access == .covering);
840 target.* = .{
841 .allocator = result_allocator,
842 .relation = relation,
843 .reader = null,
844 .fields = select_execution.fields,
845 .access = access,
846 .predicates = owned_predicates.predicates,
847 .predicate_values = owned_predicates.values,
848 .state = .empty,
849 .window = if (sort_needed) .{} else try self.selectWindow(select),
850 };
851 owned_predicates_live = false;
852 errdefer target.deinit();
853 if (sort_needed) {
854 const rows = try self.sortedSelectedRows(select, result_allocator, &reader);
855 target.state = .{ .sorted = .{ .rows = rows } };
856 reader.deinit();
857 reader_live = false;
858 return;
859 }
860 target.reader = reader;
861 reader_live = false;
862 switch (access) {
863 .rowid => |rowid_access| {
864 const rowid = try predicate_mod.rowidFromValue(
865 target.predicate_values[rowid_access.predicate_index],
866 );
867 if (rowid_access.operator == .eq) {
868 const bytes = (try reader.get(result_allocator, rowid)) orelse {
869 target.state = .empty;
870 return;
871 };
872 errdefer result_allocator.free(bytes);
873 if (!try predicate_mod.rowBytesMatchPredicates(
874 target.predicates,
875 target.predicate_values,
876 &relation.handle,
877 rowid,
878 bytes,
879 )) {
880 result_allocator.free(bytes);
881 target.state = .empty;
882 return;
883 }
884 if (select_execution.fields.len == 0) {
885 target.state = .{ .single = .{ .bytes = bytes } };
886 return;
887 }
888 const projected = try result_mod.selectedRow(result_allocator, select_execution.fields, rowid, bytes);
889 result_allocator.free(bytes);
890 target.state = .{ .single = .{ .bytes = projected } };
891 return;
892 }
893 target.state = .{ .rowid = undefined };
894 errdefer target.state = .empty;
895 try reader.scan(
896 &target.state.rowid,
897 result_allocator,
898 predicate_mod.rowidStart(rowid_access.operator, rowid),
899 predicate_mod.rowidEnd(rowid_access.operator, rowid),
900 );
901 },
902 .index => |indexed| {
903 var bound_storage: [relation_mod.max_index_fields]row.Value = undefined;
904 const bound_values = predicate_mod.indexedPrefixValues(
905 indexed,
906 target.predicate_values,
907 &bound_storage,
908 );
909 const covered = access_mod.indexCoversPredicates(
910 &relation.handle,
911 indexed.index_slot,
912 select_execution.fields,
913 target.predicates,
914 );
915 target.state = .{ .index = .{ .scan = undefined, .covered = covered } };
916 errdefer target.state = .empty;
917 const index_scan = &target.state.index.scan;
918 switch (indexed.operator) {
919 .eq => try reader.lookup(
920 index_scan,
921 result_allocator,
922 indexed.index_slot,
923 bound_values,
924 ),
925 .lt, .lte, .gt, .gte => try reader.indexRange(
926 index_scan,
927 result_allocator,
928 indexed.index_slot,
929 predicate_mod.indexedRangeStart(indexed, bound_values),
930 predicate_mod.indexedRangeEnd(indexed, bound_values),
931 ),
932 }
933 },
934 .covering => unreachable,
935 .scan => {
936 target.state = .{ .scan = undefined };
937 errdefer target.state = .empty;
938 try reader.scan(&target.state.scan, result_allocator, null, null);
939 },
940 }
941 }
942
943 fn selectWindow(self: *const Prepared, select: ast_mod.Select) ast_mod.Error!Window {
944 var window = Window{};
945 if (select.limit) |expression| window.remaining = try self.windowCount(expression);
946 if (select.offset) |expression| window.skip = try self.windowCount(expression);
947 return window;
948 }
949
950 fn windowCount(self: *const Prepared, expression: ast_mod.Expression) ast_mod.Error!usize {
951 return switch (self.expressionValue(expression)) {
952 .integer => |integer| if (integer < 0) error.InvalidLimit else @intCast(integer),
953 else => error.InvalidLimit,
954 };
955 }
956
957 fn rowidValue(self: *const Prepared, expression: ast_mod.Expression) ast_mod.Error!i64 {
958 return try predicate_mod.rowidFromValue(self.expressionValue(expression));
959 }
960
961 pub fn expressionValue(self: *const Prepared, expression: ast_mod.Expression) row.Value {
962 return switch (expression) {
963 .literal => |literal| literal,
964 .parameter => |index| self.bindings[index].value(),
965 };
966 }
967
968 fn reprepare(self: *Prepared) ast_mod.Error!void {
969 const phase = trace.scope("statement.reprepare");
970 defer phase.end();
971
972 if (self.relation == null) return;
973 var relation = try prepareStatementRelation(&self.catalog, self.allocator, self.statement, ast_mod.parameterShape(self.parameters), self.validation) orelse return;
974 errdefer relation.deinit();
975 var execution = try prepareExecution(self.allocator, self.statement, &relation);
976 errdefer execution.deinit(self.allocator);
977 self.execution.deinit(self.allocator);
978 self.relation.?.deinit();
979 self.relation = relation;
980 self.execution = execution;
981 }
982
983 fn relationPtr(self: *Prepared) ast_mod.Error!*plan.PreparedRelation {
984 if (self.relation) |*relation| return relation;
985 return error.UnsupportedStatement;
986 }
987 };
988
989 pub const PendingEdit = union(enum) {
990 put: struct {
991 rowid: i64,
992 values: []const row.Value,
993 },
994 update: struct {
995 rowid: i64,
996 assignments: []const relation_mod.Edit.Assignment,
997 },
998 delete: i64,
999
1000 fn stage(self: PendingEdit, write: *session_mod.DatabaseWrite, name: []const u8) ast_mod.Error!void {
1001 switch (self) {
1002 .put => |put_edit| try write.stagePut(name, put_edit.rowid, put_edit.values),
1003 .update => |update_edit| try write.stageUpdate(name, update_edit.rowid, update_edit.assignments),
1004 .delete => |rowid| try write.stageDelete(name, rowid),
1005 }
1006 }
1007
1008 fn append(self: PendingEdit, relation_session: *session_mod.RelationSession) ast_mod.Error!void {
1009 switch (self) {
1010 .put => |put_edit| try relation_session.put(put_edit.rowid, put_edit.values),
1011 .update => |update_edit| try relation_session.update(update_edit.rowid, update_edit.assignments),
1012 .delete => |rowid| try relation_session.delete(rowid),
1013 }
1014 }
1015
1016 fn addDemand(
1017 self: PendingEdit,
1018 demand: *session_mod.DatabaseWrite.Demand,
1019 ) ast_mod.Error!void {
1020 demand.edits = try checkedAdd(demand.edits, 1);
1021 switch (self) {
1022 .put => |put_edit| {
1023 demand.payload_bytes = try checkedAdd(
1024 demand.payload_bytes,
1025 try row.encodedSize(put_edit.values),
1026 );
1027 },
1028 .update => |update_edit| {
1029 demand.assignments = try checkedAdd(
1030 demand.assignments,
1031 update_edit.assignments.len,
1032 );
1033 for (update_edit.assignments) |assignment| {
1034 const bytes = switch (assignment.value) {
1035 .nil, .integer => 0,
1036 .text => |text| text.len,
1037 .blob => |blob| blob.len,
1038 };
1039 demand.payload_bytes = try checkedAdd(demand.payload_bytes, bytes);
1040 }
1041 },
1042 .delete => {},
1043 }
1044 }
1045 };
1046
1047 pub fn applyEdits(relation: *plan.PreparedRelation, result_allocator: Allocator, options: ExecuteOptions, edits: []const PendingEdit) ast_mod.Error!result_mod.Result {
1048 const demand = try pendingEditDemand(edits);
1049 if (options.write) |write| {
1050 try write.ensure(demand);
1051 if (write.stagedRelationRoot(relation.name)) |staged_root| {
1052 if (relation.matchesRoot(staged_root)) {
1053 for (edits) |edit| try edit.stage(write, relation.name);
1054 return .{ .staged = write.pendingRelations() };
1055 }
1056 }
1057 var relation_session = try relation.writeSession();
1058 var relation_session_live = true;
1059 errdefer if (relation_session_live) relation_session.deinit();
1060 try write.stageRelation(&relation_session);
1061 relation_session_live = false;
1062 for (edits) |edit| try edit.stage(write, relation.name);
1063 return .{ .staged = write.pendingRelations() };
1064 }
1065
1066 var relation_session = try relation.writeSession();
1067 var relation_session_live = true;
1068 errdefer if (relation_session_live) relation_session.deinit();
1069 for (edits) |edit| try edit.append(&relation_session);
1070 const database_session = try options.databaseSession();
1071 const limits = session_mod.DatabaseWrite.Limits{
1072 .relations = 1,
1073 .edits = demand.edits,
1074 .payload_bytes = demand.payload_bytes,
1075 .assignments = demand.assignments,
1076 };
1077 var workspace = try session_mod.DatabaseWrite.Workspace.allocate(
1078 result_allocator,
1079 limits,
1080 );
1081 defer workspace.deallocate(result_allocator);
1082 var write = try database_session.beginWrite(
1083 &workspace,
1084 result_allocator,
1085 limits,
1086 options.commit(),
1087 );
1088 defer write.deinit();
1089 try write.stageRelation(&relation_session);
1090 relation_session_live = false;
1091 var database_flush = try write.flush();
1092 defer database_flush.deinit();
1093 const flush = database_flush.onlyRelation();
1094 try relation.refresh();
1095 return .{ .mutation = flush };
1096 }
1097
1098 fn pendingEditDemand(
1099 edits: []const PendingEdit,
1100 ) ast_mod.Error!session_mod.DatabaseWrite.Demand {
1101 var demand = session_mod.DatabaseWrite.Demand{};
1102 for (edits) |edit| try edit.addDemand(&demand);
1103 return demand;
1104 }
1105
1106 fn checkedAdd(left: usize, right: usize) error{CapacityOverflow}!usize {
1107 return std.math.add(usize, left, right) catch error.CapacityOverflow;
1108 }
1109
1110 pub const StagedOverlay = struct {
1111 allocator: Allocator,
1112 rows: std.AutoArrayHashMapUnmanaged(i64, ?[]u8) = .empty,
1113
1114 fn init(allocator: Allocator, options: ExecuteOptions, relation: *const plan.PreparedRelation, reader: plan.RelationExecution) ast_mod.Error!StagedOverlay {
1115 var overlay = StagedOverlay{ .allocator = allocator };
1116 errdefer overlay.deinit();
1117 const write = options.write orelse return overlay;
1118 const staged_root = write.stagedRelationRoot(relation.name) orelse return overlay;
1119 if (!relation.matchesRoot(staged_root)) return overlay;
1120 var edits = write.stagedRelationEdits(relation.name) orelse return overlay;
1121 while (edits.next()) |edit| try overlay.apply(reader, edit);
1122 return overlay;
1123 }
1124
1125 fn deinit(self: *StagedOverlay) void {
1126 for (self.rows.values()) |staged| {
1127 if (staged) |bytes| self.allocator.free(bytes);
1128 }
1129 self.rows.deinit(self.allocator);
1130 self.* = undefined;
1131 }
1132
1133 fn shadows(self: *const StagedOverlay, rowid: i64) bool {
1134 return self.rows.contains(rowid);
1135 }
1136
1137 fn visible(self: *const StagedOverlay, rowid: i64) ??[]const u8 {
1138 const staged = self.rows.get(rowid) orelse return null;
1139 if (staged) |bytes| return @as(?[]const u8, bytes);
1140 return @as(?[]const u8, null);
1141 }
1142
1143 fn apply(self: *StagedOverlay, reader: plan.RelationExecution, edit: relation_mod.Edit) ast_mod.Error!void {
1144 switch (edit) {
1145 .put => |put_edit| {
1146 const owned = try self.allocator.dupe(u8, put_edit.bytes);
1147 errdefer self.allocator.free(owned);
1148 try self.replace(put_edit.rowid, owned);
1149 },
1150 .update => |update_edit| {
1151 if (self.rows.getPtr(update_edit.rowid)) |slot| {
1152 const current = slot.* orelse return error.KeyNotFound;
1153 const merged = try relation_mod.applyUpdate(self.allocator, current, update_edit.assignments);
1154 self.allocator.free(current);
1155 slot.* = merged;
1156 return;
1157 }
1158 const base = (try reader.get(self.allocator, update_edit.rowid)) orelse return error.KeyNotFound;
1159 defer self.allocator.free(base);
1160 const merged = try relation_mod.applyUpdate(self.allocator, base, update_edit.assignments);
1161 errdefer self.allocator.free(merged);
1162 try self.rows.putNoClobber(self.allocator, update_edit.rowid, merged);
1163 },
1164 .delete => |rowid| {
1165 if (self.rows.getPtr(rowid)) |slot| {
1166 const current = slot.* orelse return error.KeyNotFound;
1167 self.allocator.free(current);
1168 slot.* = null;
1169 return;
1170 }
1171 const base = (try reader.get(self.allocator, rowid)) orelse return error.KeyNotFound;
1172 self.allocator.free(base);
1173 try self.rows.putNoClobber(self.allocator, rowid, null);
1174 },
1175 }
1176 }
1177
1178 fn replace(self: *StagedOverlay, rowid: i64, bytes: ?[]u8) ast_mod.Error!void {
1179 if (self.rows.getPtr(rowid)) |slot| {
1180 if (slot.*) |current| self.allocator.free(current);
1181 slot.* = bytes;
1182 return;
1183 }
1184 try self.rows.putNoClobber(self.allocator, rowid, bytes);
1185 }
1186 };
1187
1188 pub fn prepare(catalog: *const catalog_mod.Catalog, allocator: Allocator, source: []const u8) ast_mod.Error!Prepared {
1189 return try prepareWithValidation(catalog, allocator, source, .content);
1190 }
1191
1192 pub fn prepareReadOnly(catalog: *const catalog_mod.Catalog, allocator: Allocator, source: []const u8) ast_mod.Error!Prepared {
1193 return try prepareWithValidation(catalog, allocator, source, .shape);
1194 }
1195
1196 pub fn prepareWithValidation(catalog: *const catalog_mod.Catalog, allocator: Allocator, source: []const u8, validation: plan.Validation) ast_mod.Error!Prepared {
1197 const phase = trace.scope("statement.prepare");
1198 defer phase.end();
1199
1200 const owned_source = try allocator.dupe(u8, source);
1201 errdefer allocator.free(owned_source);
1202
1203 var parsed = try parse_mod.parseWithParameters(allocator, owned_source);
1204 errdefer parsed.deinit(allocator);
1205
1206 if (validation == .shape) switch (parsed.statement) {
1207 .select => {},
1208 else => return error.UnsupportedStatement,
1209 };
1210 var relation = try prepareStatementRelation(catalog, allocator, parsed.statement, ast_mod.parameterShape(parsed.parameters), validation);
1211 errdefer if (relation) |*prepared_relation| prepared_relation.deinit();
1212 var execution = try prepareExecution(allocator, parsed.statement, if (relation) |*prepared_relation| prepared_relation else null);
1213 errdefer execution.deinit(allocator);
1214 const bindings = try allocator.alloc(result_mod.Binding, parsed.parameters.len);
1215 errdefer allocator.free(bindings);
1216 @memset(bindings, result_mod.Binding.unset);
1217
1218 return .{
1219 .allocator = allocator,
1220 .catalog = catalog.*,
1221 .source = owned_source,
1222 .statement = parsed.statement,
1223 .parameters = parsed.parameters,
1224 .bindings = bindings,
1225 .relation = relation,
1226 .execution = execution,
1227 .validation = validation,
1228 };
1229 }
1230
1231 pub fn prepareStatementRelation(catalog: *const catalog_mod.Catalog, allocator: Allocator, statement: ast_mod.Statement, parameters: version.Hash, validation: plan.Validation) ast_mod.Error!?plan.PreparedRelation {
1232 return switch (statement) {
1233 .insert, .select, .update, .delete, .create_index => prepareRelation(catalog, allocator, statement.tableName(), parameters, validation) catch |err| switch (err) {
1234 error.RelationNotFound => error.TableNotFound,
1235 else => err,
1236 },
1237 .create_table, .drop_table, .analyze => null,
1238 };
1239 }
1240
1241 pub fn prepareRelation(catalog: *const catalog_mod.Catalog, allocator: Allocator, name: []const u8, parameters: version.Hash, validation: plan.Validation) ast_mod.Error!plan.PreparedRelation {
1242 return switch (validation) {
1243 .content => try plan.prepareRelation(catalog, allocator, name, parameters),
1244 .shape => try plan.prepareRelationShape(catalog, allocator, name, parameters),
1245 };
1246 }
1247
1248 pub fn prepareExecution(allocator: Allocator, statement: ast_mod.Statement, relation: ?*const plan.PreparedRelation) ast_mod.Error!Execution {
1249 return switch (statement) {
1250 .insert => |insert| .{ .insert = try insert_mod.prepare(allocator, insert, (relation orelse return error.UnsupportedStatement).handle.definitions) },
1251 .select => |select| .{ .select = try prepareSelectExecution(allocator, select, relation orelse return error.UnsupportedStatement) },
1252 .update => |update| .{ .update = try prepareUpdateExecution(allocator, update, (relation orelse return error.UnsupportedStatement).handle.definitions) },
1253 .delete => |delete| delete: {
1254 try predicate_mod.validatePredicates(delete.predicates, (relation orelse return error.UnsupportedStatement).handle.definitions);
1255 break :delete .delete;
1256 },
1257 .create_table => .create_table,
1258 .create_index => |create_index| .{ .create_index = try prepareCreateIndexExecution(allocator, create_index, (relation orelse return error.UnsupportedStatement).handle.definitions) },
1259 .drop_table => .drop_table,
1260 .analyze => .analyze,
1261 };
1262 }
1263
1264 pub fn prepareCreateIndexExecution(allocator: Allocator, create_index: ast_mod.CreateIndex, definitions: []const catalog_mod.ColumnDefinition) ast_mod.Error!CreateIndexExecution {
1265 const fields = try allocator.alloc(usize, create_index.columns.len);
1266 errdefer allocator.free(fields);
1267 const columns = try allocator.alloc(row.Column, create_index.columns.len);
1268 errdefer allocator.free(columns);
1269 for (create_index.columns, fields, columns) |name, *field, *column| {
1270 const index = access_mod.columnIndex(definitions, name) orelse return error.ColumnNotFound;
1271 field.* = index;
1272 column.* = definitions[index].column;
1273 }
1274 return .{
1275 .fields = fields,
1276 .columns = columns,
1277 };
1278 }
1279
1280 pub fn prepareUpdateExecution(allocator: Allocator, update: ast_mod.Update, definitions: []const catalog_mod.ColumnDefinition) ast_mod.Error!UpdateExecution {
1281 if (update.assignments.len == 0) return error.ExpectedColumn;
1282 try predicate_mod.validatePredicates(update.predicates, definitions);
1283 const assignment_fields = try allocator.alloc(usize, update.assignments.len);
1284 errdefer allocator.free(assignment_fields);
1285 for (update.assignments, assignment_fields) |assignment, *field| {
1286 if (std.ascii.eqlIgnoreCase(assignment.column, "rowid")) return error.RowIdAssignment;
1287 field.* = access_mod.columnIndex(definitions, assignment.column) orelse return error.ColumnNotFound;
1288 }
1289 for (assignment_fields, 0..) |field, offset| {
1290 for (assignment_fields[0..offset]) |seen| {
1291 if (seen == field) return error.DuplicateColumn;
1292 }
1293 }
1294 return .{ .assignment_fields = assignment_fields };
1295 }
1296
1297 pub fn prepareSelectExecution(allocator: Allocator, select: ast_mod.Select, relation: *const plan.PreparedRelation) ast_mod.Error!SelectExecution {
1298 const fields = try prepareProjection(allocator, select.projection, &relation.handle);
1299 errdefer allocator.free(fields);
1300 const order_fields = try prepareOrderFields(allocator, select.order, &relation.handle);
1301 errdefer allocator.free(order_fields);
1302 return .{
1303 .fields = fields,
1304 .order_fields = order_fields,
1305 .access = try prepareSelectAccess(select, relation, fields, null),
1306 };
1307 }
1308
1309 pub fn prepareOrderFields(allocator: Allocator, order: []const ast_mod.OrderKey, handle: *const catalog_mod.RelationHandle) ast_mod.Error![]OrderField {
1310 const order_fields = try allocator.alloc(OrderField, order.len);
1311 errdefer allocator.free(order_fields);
1312 for (order, order_fields) |order_key, *target| {
1313 target.* = switch (order_key.column) {
1314 .rowid => .{
1315 .source = .rowid,
1316 .collation = .binary,
1317 .descending = order_key.descending,
1318 },
1319 .field => |name| order_field: {
1320 const field = access_mod.columnIndex(handle.definitions, name) orelse return error.ColumnNotFound;
1321 break :order_field .{
1322 .source = .{ .field = field },
1323 .collation = handle.definitions[field].column.collation,
1324 .descending = order_key.descending,
1325 };
1326 },
1327 };
1328 }
1329 return order_fields;
1330 }
1331
1332 pub fn prepareProjection(allocator: Allocator, projection: ast_mod.Projection, handle: *const catalog_mod.RelationHandle) ast_mod.Error![]ProjectedColumn {
1333 return switch (projection) {
1334 .all => try allocator.alloc(ProjectedColumn, 0),
1335 .columns => |columns| fields: {
1336 const projected = try allocator.alloc(ProjectedColumn, columns.len);
1337 errdefer allocator.free(projected);
1338 for (columns, 0..) |column, offset| {
1339 projected[offset] = if (std.ascii.eqlIgnoreCase(column, "rowid"))
1340 .rowid
1341 else
1342 .{ .field = access_mod.columnIndex(handle.definitions, column) orelse return error.ColumnNotFound };
1343 }
1344 break :fields projected;
1345 },
1346 };
1347 }
1348
1349 pub fn prepareSelectAccess(select: ast_mod.Select, relation: *const plan.PreparedRelation, fields: []const ProjectedColumn, values: ?[]const row.Value) ast_mod.Error!access_mod.SelectAccess {
1350 const handle = &relation.handle;
1351 try predicate_mod.validatePredicates(select.predicates, handle.definitions);
1352 var rowid_candidate: ?predicate_mod.RowidPredicate = null;
1353 var predicate_offset: usize = 0;
1354 while (predicate_offset < predicate_mod.selectPredicateCount(select)) : (predicate_offset += 1) {
1355 const predicate = predicate_mod.selectPredicateAt(select, predicate_offset);
1356 switch (predicate.column) {
1357 .rowid => {
1358 const candidate = predicate_mod.RowidPredicate{
1359 .predicate_index = predicate_offset,
1360 .operator = predicate.operator,
1361 };
1362 if (predicate.operator == .eq) return .{ .rowid = candidate };
1363 if (rowid_candidate == null) rowid_candidate = candidate;
1364 },
1365 .field => {},
1366 }
1367 }
1368 if (access_mod.predicateIndex(select, relation, fields, values)) |choice| return .{ .index = choice };
1369 if (rowid_candidate) |choice| return .{ .rowid = choice };
1370 if (access_mod.coveringIndex(select, relation, fields)) |slot| return .{ .covering = slot };
1371 return .{ .scan = try predicate_mod.scannedPredicate(select, handle, relation.relationStats()) };
1372 }
1373
1374 const testing_write_limits = session_mod.DatabaseWrite.Limits{
1375 .relations = 2,
1376 .edits = 16,
1377 .payload_bytes = 4096,
1378 .assignments = 16,
1379 };
1380
1381 test "prepared statements order selected rows with windows" {
1382 var tmp = std.testing.tmpDir(.{});
1383 defer tmp.cleanup();
1384
1385 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
1386 .paths = .{ .database = "statement-order.db", .wal = "statement-order.wal" },
1387 .header = testingHeader(),
1388 .read_lease_limit = 1,
1389 });
1390 defer database.deinit();
1391 try database.reserve(.{ .wal_frames = 420 });
1392
1393 var catalog = try catalog_mod.Catalog.open(&database, .{});
1394 _ = try catalog.createRelation(std.testing.allocator, .{
1395 .name = "items",
1396 .columns = &.{ .{ .name = "name", .column = .{ .collation = .nocase } }, .{ .name = "score" } },
1397 }, .{ .durability = .buffered });
1398 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
1399 defer database_session.deinit();
1400
1401 const rows_spec = [_]struct { rowid: i64, name: []const u8, score: i64 }{
1402 .{ .rowid = 1, .name = "Dot", .score = 2 },
1403 .{ .rowid = 2, .name = "ada", .score = 7 },
1404 .{ .rowid = 3, .name = "Bea", .score = 7 },
1405 .{ .rowid = 4, .name = "cal", .score = 1 },
1406 };
1407 for (rows_spec) |spec| {
1408 var buffer: [120]u8 = undefined;
1409 const source = try std.fmt.bufPrint(&buffer, "INSERT INTO items (rowid, name, score) VALUES ({d}, '{s}', {d})", .{ spec.rowid, spec.name, spec.score });
1410 var insert = try prepare(&catalog, std.testing.allocator, source);
1411 defer insert.deinit();
1412 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1413 defer inserted.deinit(std.testing.allocator);
1414 }
1415
1416 var by_score = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items ORDER BY score DESC, name ASC");
1417 defer by_score.deinit();
1418 var by_score_rows = try by_score.execute(std.testing.allocator, .{ .durability = .buffered });
1419 defer by_score_rows.deinit(std.testing.allocator);
1420 try std.testing.expectEqual(@as(usize, 4), by_score_rows.rowCount());
1421 const expected_score_order = [_][]const u8{ "ada", "Bea", "Dot", "cal" };
1422 for (expected_score_order) |expected| {
1423 const view = try row.View.init(by_score_rows.nextRow().?);
1424 try std.testing.expectEqualStrings(expected, (try view.column(0)).text);
1425 }
1426
1427 var by_name = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items ORDER BY name");
1428 defer by_name.deinit();
1429 var by_name_rows = try by_name.execute(std.testing.allocator, .{ .durability = .buffered });
1430 defer by_name_rows.deinit(std.testing.allocator);
1431 const expected_name_order = [_][]const u8{ "ada", "Bea", "cal", "Dot" };
1432 for (expected_name_order) |expected| {
1433 const view = try row.View.init(by_name_rows.nextRow().?);
1434 try std.testing.expectEqualStrings(expected, (try view.column(0)).text);
1435 }
1436
1437 var by_rowid = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items ORDER BY rowid DESC LIMIT 2 OFFSET 1");
1438 defer by_rowid.deinit();
1439 var by_rowid_rows = try by_rowid.execute(std.testing.allocator, .{ .durability = .buffered });
1440 defer by_rowid_rows.deinit(std.testing.allocator);
1441 try std.testing.expectEqual(@as(usize, 2), by_rowid_rows.rowCount());
1442 const rowid_first = try row.View.init(by_rowid_rows.nextRow().?);
1443 try std.testing.expectEqual(@as(i64, 3), (try rowid_first.column(0)).integer);
1444 const rowid_second = try row.View.init(by_rowid_rows.nextRow().?);
1445 try std.testing.expectEqual(@as(i64, 2), (try rowid_second.column(0)).integer);
1446
1447 var filtered = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE score >= 2 ORDER BY name DESC");
1448 defer filtered.deinit();
1449 var filtered_rows = try filtered.execute(std.testing.allocator, .{ .durability = .buffered });
1450 defer filtered_rows.deinit(std.testing.allocator);
1451 try std.testing.expectEqual(@as(usize, 3), filtered_rows.rowCount());
1452 const filtered_first = try row.View.init(filtered_rows.nextRow().?);
1453 try std.testing.expectEqualStrings("Dot", (try filtered_first.column(0)).text);
1454
1455 var cursor_statement = try prepare(&catalog, std.testing.allocator, "SELECT name, score FROM items ORDER BY score LIMIT 2");
1456 defer cursor_statement.deinit();
1457 var cursor: cursor_mod.Cursor = undefined;
1458 try cursor_statement.openCursor(&cursor, std.testing.allocator);
1459 defer cursor.deinit();
1460 var materialization_read = try database.beginRead();
1461 materialization_read.deinit();
1462 const cursor_first = try row.View.init((try cursor.next()).?);
1463 try std.testing.expectEqualStrings("cal", (try cursor_first.column(0)).text);
1464 const cursor_second = try row.View.init((try cursor.next()).?);
1465 try std.testing.expectEqualStrings("Dot", (try cursor_second.column(0)).text);
1466 try std.testing.expect(try cursor.next() == null);
1467
1468 try std.testing.expectError(error.ColumnNotFound, prepare(&catalog, std.testing.allocator, "SELECT name FROM items ORDER BY missing"));
1469 }
1470
1471 test "sorted cursor allocation failure leaves cleanup empty and retryable" {
1472 var tmp = std.testing.tmpDir(.{});
1473 defer tmp.cleanup();
1474
1475 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
1476 .paths = .{ .database = "sorted-cursor-oom.db", .wal = "sorted-cursor-oom.wal" },
1477 .header = testingHeader(),
1478 });
1479 defer database.deinit();
1480 try database.reserve(.{ .wal_frames = 420 });
1481
1482 var catalog = try catalog_mod.Catalog.open(&database, .{});
1483 _ = try catalog.createRelation(std.testing.allocator, .{
1484 .name = "items",
1485 .columns = &.{ .{ .name = "name" }, .{ .name = "score" } },
1486 }, .{ .durability = .buffered });
1487 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
1488 defer database_session.deinit();
1489
1490 const rows = [_]struct { rowid: i64, name: []const u8, score: i64 }{
1491 .{ .rowid = 1, .name = "one", .score = 2 },
1492 .{ .rowid = 2, .name = "two", .score = 1 },
1493 };
1494 for (rows) |value| {
1495 var source_buffer: [128]u8 = undefined;
1496 const source = try std.fmt.bufPrint(
1497 &source_buffer,
1498 "INSERT INTO items (rowid, name, score) VALUES ({d}, '{s}', {d})",
1499 .{ value.rowid, value.name, value.score },
1500 );
1501 var insert = try prepare(&catalog, std.testing.allocator, source);
1502 defer insert.deinit();
1503 var inserted = try insert.execute(std.testing.allocator, .{
1504 .durability = .buffered,
1505 .session = &database_session,
1506 });
1507 defer inserted.deinit(std.testing.allocator);
1508 }
1509
1510 var statement = try prepare(
1511 &catalog,
1512 std.testing.allocator,
1513 "SELECT name FROM items ORDER BY score",
1514 );
1515 defer statement.deinit();
1516 var calibration = std.testing.FailingAllocator.init(std.testing.allocator, .{
1517 .fail_index = std.math.maxInt(usize),
1518 });
1519 var calibrated_cursor: cursor_mod.Cursor = undefined;
1520 try statement.openCursor(&calibrated_cursor, calibration.allocator());
1521 calibrated_cursor.deinit();
1522 try std.testing.expect(calibration.alloc_index > 0);
1523
1524 var failing = std.testing.FailingAllocator.init(std.testing.allocator, .{
1525 .fail_index = calibration.alloc_index - 1,
1526 });
1527 var failed_cursor: cursor_mod.Cursor = undefined;
1528 try std.testing.expectError(
1529 error.OutOfMemory,
1530 statement.openCursor(&failed_cursor, failing.allocator()),
1531 );
1532 try std.testing.expect(failing.has_induced_failure);
1533 failing.fail_index = std.math.maxInt(usize);
1534 var retry: cursor_mod.Cursor = undefined;
1535 try statement.openCursor(&retry, failing.allocator());
1536 defer retry.deinit();
1537 const first = try row.View.init((try retry.next()).?);
1538 try std.testing.expectEqualStrings("two", (try first.column(0)).text);
1539 const second = try row.View.init((try retry.next()).?);
1540 try std.testing.expectEqualStrings("one", (try second.column(0)).text);
1541 try std.testing.expect(try retry.next() == null);
1542 }
1543
1544 test "prepared statements stream rowid-ascending orders through the scan window" {
1545 var tmp = std.testing.tmpDir(.{});
1546 defer tmp.cleanup();
1547
1548 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
1549 .paths = .{ .database = "statement-elide.db", .wal = "statement-elide.wal" },
1550 .header = testingHeader(),
1551 });
1552 defer database.deinit();
1553 try database.reserve(.{ .wal_frames = 420 });
1554
1555 var catalog = try catalog_mod.Catalog.open(&database, .{});
1556 _ = try catalog.createRelation(std.testing.allocator, .{
1557 .name = "items",
1558 .columns = &.{ .{ .name = "name", .column = .{ .collation = .nocase } }, .{ .name = "score" } },
1559 }, .{ .durability = .buffered });
1560 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
1561 defer database_session.deinit();
1562
1563 const rows_spec = [_]struct { rowid: i64, name: []const u8, score: i64 }{
1564 .{ .rowid = 4, .name = "cal", .score = 1 },
1565 .{ .rowid = 1, .name = "Dot", .score = 2 },
1566 .{ .rowid = 3, .name = "Bea", .score = 7 },
1567 .{ .rowid = 2, .name = "ada", .score = 7 },
1568 };
1569 for (rows_spec) |spec| {
1570 var buffer: [120]u8 = undefined;
1571 const source = try std.fmt.bufPrint(&buffer, "INSERT INTO items (rowid, name, score) VALUES ({d}, '{s}', {d})", .{ spec.rowid, spec.name, spec.score });
1572 var insert = try prepare(&catalog, std.testing.allocator, source);
1573 defer insert.deinit();
1574 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1575 defer inserted.deinit(std.testing.allocator);
1576 }
1577
1578 try std.testing.expect(orderIsRowidAscending(&.{
1579 .{ .source = .rowid, .collation = .binary, .descending = false },
1580 }));
1581 try std.testing.expect(!orderIsRowidAscending(&.{
1582 .{ .source = .rowid, .collation = .binary, .descending = true },
1583 }));
1584 try std.testing.expect(!orderIsRowidAscending(&.{}));
1585
1586 var windowed = try prepare(&catalog, std.testing.allocator, "SELECT rowid, name FROM items WHERE score >= 2 ORDER BY rowid LIMIT 2 OFFSET 1");
1587 defer windowed.deinit();
1588 var windowed_rows = try windowed.execute(std.testing.allocator, .{ .durability = .buffered });
1589 defer windowed_rows.deinit(std.testing.allocator);
1590 try std.testing.expectEqual(@as(usize, 2), windowed_rows.rowCount());
1591 const windowed_first = try row.View.init(windowed_rows.nextRow().?);
1592 try std.testing.expectEqual(@as(i64, 2), (try windowed_first.column(0)).integer);
1593 try std.testing.expectEqualStrings("ada", (try windowed_first.column(1)).text);
1594 const windowed_second = try row.View.init(windowed_rows.nextRow().?);
1595 try std.testing.expectEqual(@as(i64, 3), (try windowed_second.column(0)).integer);
1596 try std.testing.expectEqualStrings("Bea", (try windowed_second.column(1)).text);
1597
1598 var trailing = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items ORDER BY rowid, name DESC");
1599 defer trailing.deinit();
1600 var trailing_rows = try trailing.execute(std.testing.allocator, .{ .durability = .buffered });
1601 defer trailing_rows.deinit(std.testing.allocator);
1602 try std.testing.expectEqual(@as(usize, 4), trailing_rows.rowCount());
1603 var expected_rowid: i64 = 1;
1604 while (trailing_rows.nextRow()) |bytes| : (expected_rowid += 1) {
1605 const view = try row.View.init(bytes);
1606 try std.testing.expectEqual(expected_rowid, (try view.column(0)).integer);
1607 }
1608
1609 var streamed = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items WHERE score >= 2 ORDER BY rowid LIMIT 2 OFFSET 1");
1610 defer streamed.deinit();
1611 var cursor: cursor_mod.Cursor = undefined;
1612 try streamed.openCursor(&cursor, std.testing.allocator);
1613 defer cursor.deinit();
1614 try std.testing.expect(cursor.state != .sorted);
1615 const cursor_first = try row.View.init((try cursor.next()).?);
1616 try std.testing.expectEqual(@as(i64, 2), (try cursor_first.column(0)).integer);
1617 const cursor_second = try row.View.init((try cursor.next()).?);
1618 try std.testing.expectEqual(@as(i64, 3), (try cursor_second.column(0)).integer);
1619 try std.testing.expectEqual(@as(?[]const u8, null), try cursor.next());
1620 }
1621
1622 test "prepared statements select bare tables with limit and offset windows" {
1623 var tmp = std.testing.tmpDir(.{});
1624 defer tmp.cleanup();
1625
1626 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
1627 .paths = .{ .database = "statement-window.db", .wal = "statement-window.wal" },
1628 .header = testingHeader(),
1629 });
1630 defer database.deinit();
1631 try database.reserve(.{ .wal_frames = 420 });
1632
1633 var catalog = try catalog_mod.Catalog.open(&database, .{});
1634 const indexes = [_]catalog_mod.IndexDefinition{.{
1635 .name = "items_score",
1636 .fields = &.{1},
1637 }};
1638 _ = try catalog.createRelation(std.testing.allocator, .{
1639 .name = "items",
1640 .columns = &.{ .{ .name = "name" }, .{ .name = "score" } },
1641 .indexes = &indexes,
1642 }, .{ .durability = .buffered });
1643 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
1644 defer database_session.deinit();
1645
1646 const names = [_][]const u8{ "ada", "bea", "cal", "dot", "eve" };
1647 for (names, 0..) |name, offset| {
1648 var buffer: [96]u8 = undefined;
1649 const source = try std.fmt.bufPrint(&buffer, "INSERT INTO items (rowid, name, score) VALUES ({d}, '{s}', {d})", .{ offset + 1, name, offset + 1 });
1650 var insert = try prepare(&catalog, std.testing.allocator, source);
1651 defer insert.deinit();
1652 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1653 defer inserted.deinit(std.testing.allocator);
1654 }
1655
1656 var all = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items");
1657 defer all.deinit();
1658 var all_rows = try all.execute(std.testing.allocator, .{ .durability = .buffered });
1659 defer all_rows.deinit(std.testing.allocator);
1660 try std.testing.expectEqual(@as(usize, 5), all_rows.rowCount());
1661 const first_view = try row.View.init(all_rows.nextRow().?);
1662 try std.testing.expectEqualStrings("ada", (try first_view.column(0)).text);
1663
1664 var paged = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items LIMIT 2 OFFSET 1");
1665 defer paged.deinit();
1666 var paged_rows = try paged.execute(std.testing.allocator, .{ .durability = .buffered });
1667 defer paged_rows.deinit(std.testing.allocator);
1668 try std.testing.expectEqual(@as(usize, 2), paged_rows.rowCount());
1669 const paged_first = try row.View.init(paged_rows.nextRow().?);
1670 try std.testing.expectEqualStrings("bea", (try paged_first.column(0)).text);
1671 const paged_second = try row.View.init(paged_rows.nextRow().?);
1672 try std.testing.expectEqualStrings("cal", (try paged_second.column(0)).text);
1673
1674 var bound = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items LIMIT ?1 OFFSET ?2");
1675 defer bound.deinit();
1676 try bound.bind(1, .{ .integer = 1 });
1677 try bound.bind(2, .{ .integer = 4 });
1678 var bound_rows = try bound.execute(std.testing.allocator, .{ .durability = .buffered });
1679 defer bound_rows.deinit(std.testing.allocator);
1680 try std.testing.expectEqual(@as(usize, 1), bound_rows.rowCount());
1681 const bound_view = try row.View.init(bound_rows.nextRow().?);
1682 try std.testing.expectEqualStrings("eve", (try bound_view.column(0)).text);
1683
1684 var indexed = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE score >= 2 LIMIT 2");
1685 defer indexed.deinit();
1686 var indexed_rows = try indexed.execute(std.testing.allocator, .{ .durability = .buffered });
1687 defer indexed_rows.deinit(std.testing.allocator);
1688 try std.testing.expectEqual(@as(usize, 2), indexed_rows.rowCount());
1689
1690 var single_skip = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE rowid = 3 LIMIT 1 OFFSET 1");
1691 defer single_skip.deinit();
1692 var single_skip_rows = try single_skip.execute(std.testing.allocator, .{ .durability = .buffered });
1693 defer single_skip_rows.deinit(std.testing.allocator);
1694 try std.testing.expect(single_skip_rows.nextRow() == null);
1695
1696 var empty = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items LIMIT 0");
1697 defer empty.deinit();
1698 var empty_rows = try empty.execute(std.testing.allocator, .{ .durability = .buffered });
1699 defer empty_rows.deinit(std.testing.allocator);
1700 try std.testing.expectEqual(@as(usize, 0), empty_rows.rowCount());
1701
1702 var negative = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items LIMIT -1");
1703 defer negative.deinit();
1704 try std.testing.expectError(error.InvalidLimit, negative.execute(std.testing.allocator, .{ .durability = .buffered }));
1705
1706 var unbound = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items LIMIT ?1");
1707 defer unbound.deinit();
1708 try std.testing.expectError(error.InvalidLimit, unbound.execute(std.testing.allocator, .{ .durability = .buffered }));
1709
1710 var cursor_statement = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items LIMIT 2 OFFSET 2");
1711 defer cursor_statement.deinit();
1712 var cursor: cursor_mod.Cursor = undefined;
1713 try cursor_statement.openCursor(&cursor, std.testing.allocator);
1714 defer cursor.deinit();
1715 const cursor_first = try row.View.init((try cursor.next()).?);
1716 try std.testing.expectEqualStrings("cal", (try cursor_first.column(0)).text);
1717 const cursor_second = try row.View.init((try cursor.next()).?);
1718 try std.testing.expectEqualStrings("dot", (try cursor_second.column(0)).text);
1719 try std.testing.expect(try cursor.next() == null);
1720 }
1721
1722 test "prepared statements update assigned columns by rowid" {
1723 var tmp = std.testing.tmpDir(.{});
1724 defer tmp.cleanup();
1725
1726 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
1727 .paths = .{ .database = "statement-update.db", .wal = "statement-update.wal" },
1728 .header = testingHeader(),
1729 });
1730 defer database.deinit();
1731 try database.reserve(.{ .wal_frames = 420 });
1732
1733 var catalog = try catalog_mod.Catalog.open(&database, .{});
1734 const columns = [_]catalog_mod.ColumnDefinition{
1735 .{ .name = "name" },
1736 .{ .name = "score" },
1737 };
1738 const indexes = [_]catalog_mod.IndexDefinition{.{
1739 .name = "items_score",
1740 .fields = &.{1},
1741 }};
1742 _ = try catalog.createRelation(std.testing.allocator, .{
1743 .name = "items",
1744 .columns = &columns,
1745 .indexes = &indexes,
1746 }, .{ .durability = .buffered });
1747 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
1748 defer database_session.deinit();
1749
1750 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (5, 'ada', 7)");
1751 defer insert.deinit();
1752 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1753 defer inserted.deinit(std.testing.allocator);
1754
1755 var update = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = ?1 WHERE rowid = 5");
1756 defer update.deinit();
1757 try update.bind(1, .{ .integer = 12 });
1758 try std.testing.expectError(error.WriteSessionRequired, update.execute(std.testing.allocator, .{ .durability = .buffered }));
1759 var updated = try update.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1760 defer updated.deinit(std.testing.allocator);
1761 try std.testing.expect(version.same(updated.mutation.database, database_session.checkout.working.working));
1762
1763 var select = try prepare(&catalog, std.testing.allocator, "SELECT name, score FROM items WHERE rowid = 5");
1764 defer select.deinit();
1765 var selected = try select.execute(std.testing.allocator, .{ .durability = .buffered });
1766 defer selected.deinit(std.testing.allocator);
1767 const bytes = selected.nextRow().?;
1768 const view = try row.View.init(bytes);
1769 try std.testing.expectEqualStrings("ada", (try view.column(0)).text);
1770 try std.testing.expectEqual(@as(i64, 12), (try view.column(1)).integer);
1771
1772 var indexed = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items WHERE score = 12");
1773 defer indexed.deinit();
1774 var indexed_result = try indexed.execute(std.testing.allocator, .{ .durability = .buffered });
1775 defer indexed_result.deinit(std.testing.allocator);
1776 const indexed_view = try row.View.init(indexed_result.nextRow().?);
1777 try std.testing.expectEqual(@as(i64, 5), (try indexed_view.column(0)).integer);
1778
1779 var stale = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items WHERE score = 7");
1780 defer stale.deinit();
1781 var stale_result = try stale.execute(std.testing.allocator, .{ .durability = .buffered });
1782 defer stale_result.deinit(std.testing.allocator);
1783 try std.testing.expect(stale_result.nextRow() == null);
1784
1785 var missing = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 1 WHERE rowid = 99");
1786 defer missing.deinit();
1787 try std.testing.expectError(error.KeyNotFound, missing.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session }));
1788
1789 try std.testing.expectError(error.ColumnNotFound, prepare(&catalog, std.testing.allocator, "UPDATE items SET missing = 1 WHERE rowid = 5"));
1790 try std.testing.expectError(error.DuplicateColumn, prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 1, score = 2 WHERE rowid = 5"));
1791 try std.testing.expectError(error.RowIdAssignment, prepare(&catalog, std.testing.allocator, "UPDATE items SET rowid = 9 WHERE rowid = 5"));
1792 try std.testing.expectError(error.ColumnNotFound, prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 1 WHERE missing = 2"));
1793 try std.testing.expectError(error.ColumnNotFound, prepare(&catalog, std.testing.allocator, "DELETE FROM items WHERE missing = 2"));
1794 }
1795
1796 test "prepared statements update rows matching predicates" {
1797 var tmp = std.testing.tmpDir(.{});
1798 defer tmp.cleanup();
1799
1800 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
1801 .paths = .{ .database = "statement-update-predicates.db", .wal = "statement-update-predicates.wal" },
1802 .header = testingHeader(),
1803 });
1804 defer database.deinit();
1805 try database.reserve(.{ .wal_frames = 720 });
1806
1807 var catalog = try catalog_mod.Catalog.open(&database, .{});
1808 const indexes = [_]catalog_mod.IndexDefinition{.{
1809 .name = "items_score",
1810 .fields = &.{1},
1811 }};
1812 _ = try catalog.createRelation(std.testing.allocator, .{
1813 .name = "items",
1814 .columns = &.{ .{ .name = "name" }, .{ .name = "score" }, .{ .name = "note" } },
1815 .indexes = &indexes,
1816 }, .{ .durability = .buffered });
1817 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
1818 defer database_session.deinit();
1819
1820 const rows_spec = [_]struct { rowid: i64, name: []const u8, score: i64, note: []const u8 }{
1821 .{ .rowid = 1, .name = "ada", .score = 7, .note = "hot" },
1822 .{ .rowid = 2, .name = "bea", .score = 7, .note = "cold" },
1823 .{ .rowid = 3, .name = "cy", .score = 3, .note = "hot" },
1824 .{ .rowid = 4, .name = "dot", .score = 1, .note = "cold" },
1825 };
1826 for (rows_spec) |spec| {
1827 var buffer: [160]u8 = undefined;
1828 const source = try std.fmt.bufPrint(&buffer, "INSERT INTO items (rowid, name, score, note) VALUES ({d}, '{s}', {d}, '{s}')", .{ spec.rowid, spec.name, spec.score, spec.note });
1829 var insert = try prepare(&catalog, std.testing.allocator, source);
1830 defer insert.deinit();
1831 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1832 defer inserted.deinit(std.testing.allocator);
1833 }
1834
1835 var update = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 100 WHERE note = 'hot'");
1836 defer update.deinit();
1837 var updated = try update.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1838 defer updated.deinit(std.testing.allocator);
1839 try std.testing.expect(version.same(updated.mutation.database, database_session.checkout.working.working));
1840
1841 var by_score = try prepare(&catalog, std.testing.allocator, "SELECT rowid, name FROM items WHERE score = 100");
1842 defer by_score.deinit();
1843 var by_score_rows = try by_score.execute(std.testing.allocator, .{ .durability = .buffered });
1844 defer by_score_rows.deinit(std.testing.allocator);
1845 try std.testing.expectEqual(@as(usize, 2), by_score_rows.rowCount());
1846 const first = try row.View.init(by_score_rows.nextRow().?);
1847 try std.testing.expectEqual(@as(i64, 1), (try first.column(0)).integer);
1848 try std.testing.expectEqualStrings("ada", (try first.column(1)).text);
1849 const second = try row.View.init(by_score_rows.nextRow().?);
1850 try std.testing.expectEqual(@as(i64, 3), (try second.column(0)).integer);
1851 try std.testing.expectEqualStrings("cy", (try second.column(1)).text);
1852
1853 var ranged = try prepare(&catalog, std.testing.allocator, "UPDATE items SET name = 'late' WHERE rowid > 2 AND note = 'cold'");
1854 defer ranged.deinit();
1855 var ranged_result = try ranged.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1856 defer ranged_result.deinit(std.testing.allocator);
1857 var renamed = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE rowid = 4");
1858 defer renamed.deinit();
1859 var renamed_rows = try renamed.execute(std.testing.allocator, .{ .durability = .buffered });
1860 defer renamed_rows.deinit(std.testing.allocator);
1861 const renamed_view = try row.View.init(renamed_rows.nextRow().?);
1862 try std.testing.expectEqualStrings("late", (try renamed_view.column(0)).text);
1863
1864 var bound = try prepare(&catalog, std.testing.allocator, "UPDATE items SET note = 'tied' WHERE score = ?1");
1865 defer bound.deinit();
1866 try bound.bind(1, .{ .integer = 100 });
1867 var bound_result = try bound.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1868 defer bound_result.deinit(std.testing.allocator);
1869 var tied = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items WHERE note = 'tied'");
1870 defer tied.deinit();
1871 var tied_rows = try tied.execute(std.testing.allocator, .{ .durability = .buffered });
1872 defer tied_rows.deinit(std.testing.allocator);
1873 try std.testing.expectEqual(@as(usize, 2), tied_rows.rowCount());
1874
1875 var unmatched = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 1 WHERE note = 'missing'");
1876 defer unmatched.deinit();
1877 var unmatched_result = try unmatched.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1878 defer unmatched_result.deinit(std.testing.allocator);
1879 try std.testing.expect(version.same(unmatched_result.mutation.database, database_session.checkout.working.working));
1880
1881 var missing_row = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 1 WHERE rowid = 99 AND score >= 0");
1882 defer missing_row.deinit();
1883 var missing_row_result = try missing_row.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1884 defer missing_row_result.deinit(std.testing.allocator);
1885
1886 var all = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items");
1887 defer all.deinit();
1888 var all_rows = try all.execute(std.testing.allocator, .{ .durability = .buffered });
1889 defer all_rows.deinit(std.testing.allocator);
1890 try std.testing.expectEqual(@as(usize, 4), all_rows.rowCount());
1891 }
1892
1893 test "prepared statements delete rows matching predicates" {
1894 var tmp = std.testing.tmpDir(.{});
1895 defer tmp.cleanup();
1896
1897 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
1898 .paths = .{ .database = "statement-delete-predicates.db", .wal = "statement-delete-predicates.wal" },
1899 .header = testingHeader(),
1900 });
1901 defer database.deinit();
1902 try database.reserve(.{ .wal_frames = 620 });
1903
1904 var catalog = try catalog_mod.Catalog.open(&database, .{});
1905 const indexes = [_]catalog_mod.IndexDefinition{.{
1906 .name = "items_score",
1907 .fields = &.{1},
1908 }};
1909 _ = try catalog.createRelation(std.testing.allocator, .{
1910 .name = "items",
1911 .columns = &.{ .{ .name = "name" }, .{ .name = "score" } },
1912 .indexes = &indexes,
1913 }, .{ .durability = .buffered });
1914 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
1915 defer database_session.deinit();
1916
1917 var rowid: i64 = 1;
1918 while (rowid <= 5) : (rowid += 1) {
1919 var buffer: [120]u8 = undefined;
1920 const source = try std.fmt.bufPrint(&buffer, "INSERT INTO items (rowid, name, score) VALUES ({d}, 'row-{d}', {d})", .{ rowid, rowid, rowid * 2 });
1921 var insert = try prepare(&catalog, std.testing.allocator, source);
1922 defer insert.deinit();
1923 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1924 defer inserted.deinit(std.testing.allocator);
1925 }
1926
1927 var unmatched = try prepare(&catalog, std.testing.allocator, "DELETE FROM items WHERE score > 100");
1928 defer unmatched.deinit();
1929 var unmatched_result = try unmatched.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1930 defer unmatched_result.deinit(std.testing.allocator);
1931 try std.testing.expect(version.same(unmatched_result.mutation.database, database_session.checkout.working.working));
1932
1933 var delete = try prepare(&catalog, std.testing.allocator, "DELETE FROM items WHERE score >= 6");
1934 defer delete.deinit();
1935 var deleted = try delete.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
1936 defer deleted.deinit(std.testing.allocator);
1937 try std.testing.expect(version.same(deleted.mutation.database, database_session.checkout.working.working));
1938
1939 var remaining = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items");
1940 defer remaining.deinit();
1941 var remaining_rows = try remaining.execute(std.testing.allocator, .{ .durability = .buffered });
1942 defer remaining_rows.deinit(std.testing.allocator);
1943 try std.testing.expectEqual(@as(usize, 2), remaining_rows.rowCount());
1944 const first = try row.View.init(remaining_rows.nextRow().?);
1945 try std.testing.expectEqual(@as(i64, 1), (try first.column(0)).integer);
1946 const second = try row.View.init(remaining_rows.nextRow().?);
1947 try std.testing.expectEqual(@as(i64, 2), (try second.column(0)).integer);
1948
1949 var indexed = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM items WHERE score = 6");
1950 defer indexed.deinit();
1951 var indexed_rows = try indexed.execute(std.testing.allocator, .{ .durability = .buffered });
1952 defer indexed_rows.deinit(std.testing.allocator);
1953 try std.testing.expectEqual(@as(usize, 0), indexed_rows.rowCount());
1954
1955 var missing_row = try prepare(&catalog, std.testing.allocator, "DELETE FROM items WHERE rowid = 99");
1956 defer missing_row.deinit();
1957 try std.testing.expectError(error.KeyNotFound, missing_row.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session }));
1958 }
1959
1960 test "prepared statements stage updates with read-your-writes" {
1961 var tmp = std.testing.tmpDir(.{});
1962 defer tmp.cleanup();
1963
1964 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
1965 .paths = .{ .database = "statement-update-staged.db", .wal = "statement-update-staged.wal" },
1966 .header = testingHeader(),
1967 });
1968 defer database.deinit();
1969 try database.reserve(.{ .wal_frames = 420 });
1970
1971 var catalog = try catalog_mod.Catalog.open(&database, .{});
1972 _ = try catalog.createRelation(std.testing.allocator, .{
1973 .name = "items",
1974 .columns = &.{ .{ .name = "name" }, .{ .name = "score" } },
1975 }, .{ .durability = .buffered });
1976 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
1977 defer database_session.deinit();
1978
1979 var workspace = try session_mod.DatabaseWrite.Workspace.allocate(
1980 std.testing.allocator,
1981 testing_write_limits,
1982 );
1983 defer workspace.deallocate(std.testing.allocator);
1984 var write = try database_session.beginWrite(
1985 &workspace,
1986 std.testing.allocator,
1987 testing_write_limits,
1988 .{ .durability = .buffered },
1989 );
1990 defer write.deinit();
1991
1992 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (3, 'grace', 1)");
1993 defer insert.deinit();
1994 var staged_insert = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
1995 defer staged_insert.deinit(std.testing.allocator);
1996 try std.testing.expectEqual(@as(usize, 1), staged_insert.staged);
1997
1998 var update = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 2 WHERE rowid = 3");
1999 defer update.deinit();
2000 var staged_update = try update.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2001 defer staged_update.deinit(std.testing.allocator);
2002 try std.testing.expectEqual(@as(usize, 1), staged_update.staged);
2003
2004 var flush = try write.flush();
2005 defer flush.deinit();
2006
2007 var select = try prepare(&catalog, std.testing.allocator, "SELECT name, score FROM items WHERE rowid = 3");
2008 defer select.deinit();
2009 var selected = try select.execute(std.testing.allocator, .{ .durability = .buffered });
2010 defer selected.deinit(std.testing.allocator);
2011 const view = try row.View.init(selected.nextRow().?);
2012 try std.testing.expectEqualStrings("grace", (try view.column(0)).text);
2013 try std.testing.expectEqual(@as(i64, 2), (try view.column(1)).integer);
2014 }
2015
2016 test "prepared statements stage predicate updates with read-your-writes" {
2017 var tmp = std.testing.tmpDir(.{});
2018 defer tmp.cleanup();
2019
2020 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2021 .paths = .{ .database = "statement-update-staged-predicates.db", .wal = "statement-update-staged-predicates.wal" },
2022 .header = testingHeader(),
2023 });
2024 defer database.deinit();
2025 try database.reserve(.{ .wal_frames = 720 });
2026
2027 var catalog = try catalog_mod.Catalog.open(&database, .{});
2028 _ = try catalog.createRelation(std.testing.allocator, .{
2029 .name = "items",
2030 .columns = &.{ .{ .name = "name" }, .{ .name = "score" } },
2031 }, .{ .durability = .buffered });
2032 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2033 defer database_session.deinit();
2034
2035 var seed_first = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (1, 'base-one', 5)");
2036 defer seed_first.deinit();
2037 var seeded_first = try seed_first.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2038 defer seeded_first.deinit(std.testing.allocator);
2039 var seed_second = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (2, 'base-two', 8)");
2040 defer seed_second.deinit();
2041 var seeded_second = try seed_second.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2042 defer seeded_second.deinit(std.testing.allocator);
2043
2044 var workspace = try session_mod.DatabaseWrite.Workspace.allocate(
2045 std.testing.allocator,
2046 testing_write_limits,
2047 );
2048 defer workspace.deallocate(std.testing.allocator);
2049 var write = try database_session.beginWrite(
2050 &workspace,
2051 std.testing.allocator,
2052 testing_write_limits,
2053 .{ .durability = .buffered },
2054 );
2055 defer write.deinit();
2056
2057 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (3, 'staged', 7)");
2058 defer insert.deinit();
2059 var staged_insert = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2060 defer staged_insert.deinit(std.testing.allocator);
2061
2062 var raise = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 9 WHERE rowid = 1");
2063 defer raise.deinit();
2064 var staged_raise = try raise.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2065 defer staged_raise.deinit(std.testing.allocator);
2066
2067 var drop_row = try prepare(&catalog, std.testing.allocator, "DELETE FROM items WHERE rowid = 2");
2068 defer drop_row.deinit();
2069 var staged_drop = try drop_row.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2070 defer staged_drop.deinit(std.testing.allocator);
2071
2072 var update = try prepare(&catalog, std.testing.allocator, "UPDATE items SET name = 'seen' WHERE score >= 7");
2073 defer update.deinit();
2074 var staged_update = try update.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2075 defer staged_update.deinit(std.testing.allocator);
2076 try std.testing.expectEqual(@as(usize, 1), staged_update.staged);
2077
2078 var flush = try write.flush();
2079 defer flush.deinit();
2080
2081 var handle = try catalog.openRelation(std.testing.allocator, "items");
2082 defer handle.deinit();
2083 const raised = (try handle.relation.get(std.testing.allocator, 1)).?;
2084 defer std.testing.allocator.free(raised);
2085 const raised_view = try row.View.init(raised);
2086 try std.testing.expectEqualStrings("seen", (try raised_view.column(0)).text);
2087 try std.testing.expectEqual(@as(i64, 9), (try raised_view.column(1)).integer);
2088 const removed = try handle.relation.get(std.testing.allocator, 2);
2089 if (removed) |bytes| std.testing.allocator.free(bytes);
2090 try std.testing.expect(removed == null);
2091 const inserted = (try handle.relation.get(std.testing.allocator, 3)).?;
2092 defer std.testing.allocator.free(inserted);
2093 const inserted_view = try row.View.init(inserted);
2094 try std.testing.expectEqualStrings("seen", (try inserted_view.column(0)).text);
2095 try std.testing.expectEqual(@as(i64, 7), (try inserted_view.column(1)).integer);
2096 }
2097
2098 test "prepared statements stage predicate deletes with read-your-writes" {
2099 var tmp = std.testing.tmpDir(.{});
2100 defer tmp.cleanup();
2101
2102 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2103 .paths = .{ .database = "statement-delete-staged-predicates.db", .wal = "statement-delete-staged-predicates.wal" },
2104 .header = testingHeader(),
2105 });
2106 defer database.deinit();
2107 try database.reserve(.{ .wal_frames = 720 });
2108
2109 var catalog = try catalog_mod.Catalog.open(&database, .{});
2110 _ = try catalog.createRelation(std.testing.allocator, .{
2111 .name = "items",
2112 .columns = &.{ .{ .name = "name" }, .{ .name = "score" } },
2113 }, .{ .durability = .buffered });
2114 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2115 defer database_session.deinit();
2116
2117 var rowid: i64 = 1;
2118 while (rowid <= 3) : (rowid += 1) {
2119 var buffer: [120]u8 = undefined;
2120 const source = try std.fmt.bufPrint(&buffer, "INSERT INTO items (rowid, name, score) VALUES ({d}, 'base-{d}', {d})", .{ rowid, rowid, rowid * 4 });
2121 var insert = try prepare(&catalog, std.testing.allocator, source);
2122 defer insert.deinit();
2123 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2124 defer inserted.deinit(std.testing.allocator);
2125 }
2126
2127 var workspace = try session_mod.DatabaseWrite.Workspace.allocate(
2128 std.testing.allocator,
2129 testing_write_limits,
2130 );
2131 defer workspace.deallocate(std.testing.allocator);
2132 var write = try database_session.beginWrite(
2133 &workspace,
2134 std.testing.allocator,
2135 testing_write_limits,
2136 .{ .durability = .buffered },
2137 );
2138 defer write.deinit();
2139
2140 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (4, 'staged', 4)");
2141 defer insert.deinit();
2142 var staged_insert = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2143 defer staged_insert.deinit(std.testing.allocator);
2144
2145 var unmatched = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 1 WHERE score > 100");
2146 defer unmatched.deinit();
2147 var staged_unmatched = try unmatched.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2148 defer staged_unmatched.deinit(std.testing.allocator);
2149 try std.testing.expectEqual(@as(usize, 1), staged_unmatched.staged);
2150
2151 var delete = try prepare(&catalog, std.testing.allocator, "DELETE FROM items WHERE score <= 4");
2152 defer delete.deinit();
2153 var staged_delete = try delete.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2154 defer staged_delete.deinit(std.testing.allocator);
2155 try std.testing.expectEqual(@as(usize, 1), staged_delete.staged);
2156
2157 var flush = try write.flush();
2158 defer flush.deinit();
2159
2160 var handle = try catalog.openRelation(std.testing.allocator, "items");
2161 defer handle.deinit();
2162 const first = try handle.relation.get(std.testing.allocator, 1);
2163 if (first) |bytes| std.testing.allocator.free(bytes);
2164 try std.testing.expect(first == null);
2165 const staged_row = try handle.relation.get(std.testing.allocator, 4);
2166 if (staged_row) |bytes| std.testing.allocator.free(bytes);
2167 try std.testing.expect(staged_row == null);
2168 const second = (try handle.relation.get(std.testing.allocator, 2)).?;
2169 defer std.testing.allocator.free(second);
2170 const second_view = try row.View.init(second);
2171 try std.testing.expectEqualStrings("base-2", (try second_view.column(0)).text);
2172 const third = try handle.relation.get(std.testing.allocator, 3);
2173 try std.testing.expect(third != null);
2174 std.testing.allocator.free(third.?);
2175 }
2176
2177 test "prepared statements stage repeated executes against one staged base" {
2178 var tmp = std.testing.tmpDir(.{});
2179 defer tmp.cleanup();
2180
2181 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2182 .paths = .{ .database = "statement-staged-repeat.db", .wal = "statement-staged-repeat.wal" },
2183 .header = testingHeader(),
2184 });
2185 defer database.deinit();
2186 try database.reserve(.{ .wal_frames = 512 });
2187
2188 var catalog = try catalog_mod.Catalog.open(&database, .{});
2189 _ = try catalog.createRelation(std.testing.allocator, .{
2190 .name = "items",
2191 .columns = &.{ .{ .name = "name" }, .{ .name = "score" } },
2192 }, .{ .durability = .buffered });
2193
2194 var seed = try session_mod.RelationSession.open(std.testing.allocator, &catalog, "items");
2195 var seed_live = true;
2196 errdefer if (seed_live) seed.deinit();
2197 try seed.put(1, &.{ .{ .text = "base-one" }, .{ .integer = 1 } });
2198 try seed.put(2, &.{ .{ .text = "base-two" }, .{ .integer = 2 } });
2199
2200 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2201 defer database_session.deinit();
2202 var workspace = try session_mod.DatabaseWrite.Workspace.allocate(
2203 std.testing.allocator,
2204 testing_write_limits,
2205 );
2206 defer workspace.deallocate(std.testing.allocator);
2207 var seed_write = try database_session.beginWrite(
2208 &workspace,
2209 std.testing.allocator,
2210 testing_write_limits,
2211 .{ .durability = .buffered },
2212 );
2213 defer seed_write.deinit();
2214 try seed_write.stageRelation(&seed);
2215 seed_live = false;
2216 var seed_flush = try seed_write.flush();
2217 defer seed_flush.deinit();
2218
2219 var write = try database_session.beginWrite(
2220 &workspace,
2221 std.testing.allocator,
2222 testing_write_limits,
2223 .{ .durability = .buffered },
2224 );
2225 defer write.deinit();
2226
2227 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (?1, ?2, ?3)");
2228 defer insert.deinit();
2229 var rowid: i64 = 3;
2230 while (rowid <= 6) : (rowid += 1) {
2231 try insert.bind(1, .{ .integer = rowid });
2232 try insert.bind(2, .{ .text = "staged" });
2233 try insert.bind(3, .{ .integer = rowid * 10 });
2234 var staged = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2235 defer staged.deinit(std.testing.allocator);
2236 try std.testing.expectEqual(@as(usize, 1), staged.staged);
2237 }
2238
2239 var update = try prepare(&catalog, std.testing.allocator, "UPDATE items SET score = 44 WHERE rowid = 4");
2240 defer update.deinit();
2241 var staged_update = try update.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2242 defer staged_update.deinit(std.testing.allocator);
2243 try std.testing.expectEqual(@as(usize, 1), staged_update.staged);
2244
2245 var delete = try prepare(&catalog, std.testing.allocator, "DELETE FROM items WHERE rowid = 1");
2246 defer delete.deinit();
2247 var staged_delete = try delete.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2248 defer staged_delete.deinit(std.testing.allocator);
2249 try std.testing.expectEqual(@as(usize, 1), staged_delete.staged);
2250
2251 var flush = try write.flush();
2252 defer flush.deinit();
2253
2254 var handle = try catalog.openRelation(std.testing.allocator, "items");
2255 defer handle.deinit();
2256 const removed = try handle.relation.get(std.testing.allocator, 1);
2257 if (removed) |bytes| std.testing.allocator.free(bytes);
2258 try std.testing.expect(removed == null);
2259 const updated = (try handle.relation.get(std.testing.allocator, 4)).?;
2260 defer std.testing.allocator.free(updated);
2261 const updated_view = try row.View.init(updated);
2262 try std.testing.expectEqualStrings("staged", (try updated_view.column(0)).text);
2263 try std.testing.expectEqual(@as(i64, 44), (try updated_view.column(1)).integer);
2264 const last = (try handle.relation.get(std.testing.allocator, 6)).?;
2265 defer std.testing.allocator.free(last);
2266 const last_view = try row.View.init(last);
2267 try std.testing.expectEqual(@as(i64, 60), (try last_view.column(1)).integer);
2268 }
2269
2270 test "prepared statements stage bounded writes without steady allocation" {
2271 comptime {
2272 @stardustClaim(
2273 @import("alloc_phase").capacity.witness(@import("../session/staging/root.zig").Storage, "sql_database_write_statement_semantics"),
2274 null,
2275 null,
2276 null,
2277 null,
2278 null,
2279 null,
2280 );
2281 }
2282
2283 var tmp = std.testing.tmpDir(.{});
2284 defer tmp.cleanup();
2285
2286 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2287 .paths = .{ .database = "statement-staged-bounds.db", .wal = "statement-staged-bounds.wal" },
2288 .header = testingHeader(),
2289 });
2290 defer database.deinit();
2291 try database.reserve(.{ .wal_frames = 512 });
2292
2293 var catalog = try catalog_mod.Catalog.open(&database, .{});
2294 _ = try catalog.createRelation(std.testing.allocator, .{
2295 .name = "items",
2296 .columns = &.{.{ .name = "name" }},
2297 }, .{ .durability = .buffered });
2298 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2299 defer database_session.deinit();
2300
2301 var insert = try prepare(
2302 &catalog,
2303 std.testing.allocator,
2304 "INSERT INTO items (rowid, name) VALUES (1, 'bounded')",
2305 );
2306 defer insert.deinit();
2307 const encoded_bytes = try row.encodedSize(&.{.{ .text = "bounded" }});
2308
2309 var phase_allocator = try alloc_phase.SealedPhaseAllocator.init(std.testing.allocator);
2310 var maybe_write: ?session_mod.DatabaseWrite = null;
2311 const limits = session_mod.DatabaseWrite.Limits{
2312 .relations = 1,
2313 .edits = 2,
2314 .payload_bytes = encoded_bytes * 2,
2315 .assignments = 0,
2316 };
2317 const phase_memory = phase_allocator.initializationAllocator();
2318 var workspace = try session_mod.DatabaseWrite.Workspace.allocate(
2319 phase_memory,
2320 limits,
2321 );
2322 var workspace_live = true;
2323 errdefer {
2324 if (phase_allocator.phase() == .initialization) phase_allocator.abortInitialization();
2325 if (phase_allocator.phase() == .steady) phase_allocator.beginTeardown();
2326 if (maybe_write) |*write| write.deinit();
2327 if (workspace_live) {
2328 workspace.deallocate(phase_allocator.teardownAllocator());
2329 }
2330 phase_allocator.deinit();
2331 }
2332 maybe_write = try database_session.beginWrite(
2333 &workspace,
2334 phase_memory,
2335 limits,
2336 .{ .durability = .buffered },
2337 );
2338 phase_allocator.seal();
2339 const write = &maybe_write.?;
2340
2341 var first = try insert.execute(phase_memory, .{ .durability = .buffered, .write = write });
2342 try std.testing.expectEqual(@as(usize, 1), first.staged);
2343 first.deinit(phase_memory);
2344 var second = try insert.execute(phase_memory, .{ .durability = .buffered, .write = write });
2345 try std.testing.expectEqual(@as(usize, 1), second.staged);
2346 second.deinit(phase_memory);
2347 try std.testing.expectError(
2348 error.TooManyStagedEdits,
2349 insert.execute(phase_memory, .{ .durability = .buffered, .write = write }),
2350 );
2351 try std.testing.expectEqual(alloc_phase.PhaseViolations{}, phase_allocator.violations());
2352
2353 phase_allocator.beginTeardown();
2354 write.deinit();
2355 maybe_write = null;
2356 workspace.deallocate(phase_allocator.teardownAllocator());
2357 workspace_live = false;
2358 try std.testing.expectEqual(alloc_phase.PhaseViolations{}, phase_allocator.violations());
2359 phase_allocator.deinit();
2360 }
2361
2362 test "prepared statements surface staged base drift at flush" {
2363 var tmp = std.testing.tmpDir(.{});
2364 defer tmp.cleanup();
2365
2366 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2367 .paths = .{ .database = "statement-staged-drift.db", .wal = "statement-staged-drift.wal" },
2368 .header = testingHeader(),
2369 });
2370 defer database.deinit();
2371 try database.reserve(.{ .wal_frames = 512 });
2372
2373 var catalog = try catalog_mod.Catalog.open(&database, .{});
2374 _ = try catalog.createRelation(std.testing.allocator, .{
2375 .name = "items",
2376 .columns = &.{.{ .name = "name" }},
2377 }, .{ .durability = .buffered });
2378 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2379 defer database_session.deinit();
2380
2381 var workspace = try session_mod.DatabaseWrite.Workspace.allocate(
2382 std.testing.allocator,
2383 testing_write_limits,
2384 );
2385 defer workspace.deallocate(std.testing.allocator);
2386 var write = try database_session.beginWrite(
2387 &workspace,
2388 std.testing.allocator,
2389 testing_write_limits,
2390 .{ .durability = .buffered },
2391 );
2392 defer write.deinit();
2393
2394 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name) VALUES (?1, ?2)");
2395 defer insert.deinit();
2396 try insert.bind(1, .{ .integer = 1 });
2397 try insert.bind(2, .{ .text = "first" });
2398 var first = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2399 defer first.deinit(std.testing.allocator);
2400 try std.testing.expectEqual(@as(usize, 1), first.staged);
2401
2402 var live = try catalog.openRelation(std.testing.allocator, "items");
2403 defer live.deinit();
2404 _ = try live.relation.put(std.testing.allocator, 99, &.{.{ .text = "external" }}, .{ .durability = .buffered });
2405
2406 try insert.bind(1, .{ .integer = 2 });
2407 try insert.bind(2, .{ .text = "second" });
2408 var second = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write });
2409 defer second.deinit(std.testing.allocator);
2410 try std.testing.expectEqual(@as(usize, 1), second.staged);
2411
2412 var fresh = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name) VALUES (3, 'fresh')");
2413 defer fresh.deinit();
2414 try std.testing.expectError(error.StagedRelationRootMismatch, fresh.execute(std.testing.allocator, .{ .durability = .buffered, .write = &write }));
2415
2416 try std.testing.expectError(error.StagedRelationRootMismatch, write.flush());
2417
2418 const missing = try live.relation.get(std.testing.allocator, 1);
2419 if (missing) |bytes| std.testing.allocator.free(bytes);
2420 try std.testing.expect(missing == null);
2421 const external = (try live.relation.get(std.testing.allocator, 99)).?;
2422 defer std.testing.allocator.free(external);
2423 const external_view = try row.View.init(external);
2424 try std.testing.expectEqualStrings("external", (try external_view.column(0)).text);
2425 }
2426
2427 test "prepared statements route catalog writes through database sessions" {
2428 var tmp = std.testing.tmpDir(.{});
2429 defer tmp.cleanup();
2430
2431 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2432 .paths = .{ .database = "statement-ddl.db", .wal = "statement-ddl.wal" },
2433 .header = testingHeader(),
2434 });
2435 defer database.deinit();
2436 try database.reserve(.{ .wal_frames = 520 });
2437
2438 var catalog = try catalog_mod.Catalog.open(&database, .{});
2439 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2440 defer database_session.deinit();
2441
2442 var create = try prepare(&catalog, std.testing.allocator, "CREATE TABLE items (name DEFAULT 'unknown' COLLATE nocase, score, INDEX items_score (score))");
2443 defer create.deinit();
2444 try std.testing.expectError(error.WriteSessionRequired, create.execute(std.testing.allocator, .{ .durability = .buffered }));
2445 var created = try create.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2446 defer created.deinit(std.testing.allocator);
2447 const create_flush = switch (created) {
2448 .catalog => |flush| flush,
2449 else => return error.UnsupportedStatement,
2450 };
2451 try std.testing.expectEqual(@as(u64, 1), create_flush.commit.schema.version);
2452 try std.testing.expect(version.same(create_flush.database, database_session.checkout.working.working));
2453 try std.testing.expect(database_session.checkout.working.dirty());
2454
2455 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (1, 'Ada', 7)");
2456 defer insert.deinit();
2457 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2458 defer inserted.deinit(std.testing.allocator);
2459
2460 var indexed = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE score = 7");
2461 defer indexed.deinit();
2462 const indexed_execution = switch (indexed.execution) {
2463 .select => |execution| execution,
2464 else => return error.UnsupportedStatement,
2465 };
2466 switch (indexed_execution.access) {
2467 .index => |access| try std.testing.expectEqual(@as(usize, 0), access.index_slot),
2468 else => return error.UnsupportedStatement,
2469 }
2470
2471 var analyze = try prepare(&catalog, std.testing.allocator, "ANALYZE items");
2472 defer analyze.deinit();
2473 try std.testing.expectError(error.WriteSessionRequired, analyze.execute(std.testing.allocator, .{ .durability = .buffered }));
2474 var analyzed = try analyze.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2475 defer analyzed.deinit(std.testing.allocator);
2476 const analyze_flush = switch (analyzed) {
2477 .catalog => |flush| flush,
2478 else => return error.UnsupportedStatement,
2479 };
2480 try std.testing.expectEqual(create_flush.commit.schema, analyze_flush.commit.schema);
2481 try std.testing.expect(version.same(analyze_flush.database, database_session.checkout.working.working));
2482 try std.testing.expect(!version.same(inserted.mutation.database, analyze_flush.database));
2483
2484 var selected = try indexed.execute(std.testing.allocator, .{ .durability = .buffered });
2485 defer selected.deinit(std.testing.allocator);
2486 const view = try row.View.init(selected.nextRow().?);
2487 try std.testing.expectEqualStrings("Ada", (try view.column(0)).text);
2488 try std.testing.expectEqual(@as(i64, 1), (try view.column(1)).integer);
2489 }
2490
2491 test "prepared statements create indexes over existing rows" {
2492 var tmp = std.testing.tmpDir(.{});
2493 defer tmp.cleanup();
2494
2495 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2496 .paths = .{ .database = "statement-create-index.db", .wal = "statement-create-index.wal" },
2497 .header = testingHeader(),
2498 });
2499 defer database.deinit();
2500 try database.reserve(.{ .wal_frames = 520 });
2501
2502 var catalog = try catalog_mod.Catalog.open(&database, .{});
2503 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2504 defer database_session.deinit();
2505
2506 var create = try prepare(&catalog, std.testing.allocator, "CREATE TABLE items (region, sequence, name)");
2507 defer create.deinit();
2508 var created = try create.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2509 defer created.deinit(std.testing.allocator);
2510
2511 var first = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, region, sequence, name) VALUES (1, 1, 4, 'before')");
2512 defer first.deinit();
2513 var first_insert = try first.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2514 defer first_insert.deinit(std.testing.allocator);
2515
2516 var second = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, region, sequence, name) VALUES (2, 1, 9, 'after')");
2517 defer second.deinit();
2518 var second_insert = try second.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2519 defer second_insert.deinit(std.testing.allocator);
2520
2521 var third = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, region, sequence, name) VALUES (3, 2, 1, 'other')");
2522 defer third.deinit();
2523 var third_insert = try third.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2524 defer third_insert.deinit(std.testing.allocator);
2525
2526 var create_index = try prepare(&catalog, std.testing.allocator, "CREATE INDEX items_region_sequence ON items (region, sequence)");
2527 defer create_index.deinit();
2528 try std.testing.expectError(error.WriteSessionRequired, create_index.execute(std.testing.allocator, .{ .durability = .buffered }));
2529 var indexed = try create_index.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2530 defer indexed.deinit(std.testing.allocator);
2531
2532 var selected = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE region = ?1 AND sequence >= ?2 LIMIT 2");
2533 defer selected.deinit();
2534 const selected_execution = switch (selected.execution) {
2535 .select => |execution| execution,
2536 else => return error.UnsupportedStatement,
2537 };
2538 switch (selected_execution.access) {
2539 .index => |access| {
2540 try std.testing.expectEqual(@as(usize, 0), access.index_slot);
2541 try std.testing.expectEqual(@as(usize, 2), access.prefix_count);
2542 try std.testing.expectEqual(@as(usize, 1), access.equality_count);
2543 },
2544 else => return error.UnsupportedStatement,
2545 }
2546 try selected.bind(1, .{ .integer = 1 });
2547 try selected.bind(2, .{ .integer = 5 });
2548 var selected_rows = try selected.execute(std.testing.allocator, .{ .durability = .buffered });
2549 defer selected_rows.deinit(std.testing.allocator);
2550 try std.testing.expectEqual(@as(usize, 1), selected_rows.rowCount());
2551 const view = try row.View.init(selected_rows.nextRow().?);
2552 try std.testing.expectEqualStrings("after", (try view.column(0)).text);
2553 try std.testing.expectEqual(@as(i64, 2), (try view.column(1)).integer);
2554 try std.testing.expect(selected_rows.nextRow() == null);
2555 }
2556
2557 test "prepared read only statements use composite event window indexes" {
2558 var tmp = std.testing.tmpDir(.{});
2559 defer tmp.cleanup();
2560
2561 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2562 .paths = .{ .database = "statement-read-index.db", .wal = "statement-read-index.wal" },
2563 .header = testingHeader(),
2564 });
2565 defer database.deinit();
2566 try database.reserve(.{ .wal_frames = 320 });
2567
2568 var catalog = try catalog_mod.Catalog.open(&database, .{});
2569 _ = try catalog.createRelation(std.testing.allocator, .{
2570 .name = "events",
2571 .columns = &.{
2572 .{ .name = "branch" },
2573 .{ .name = "sequence" },
2574 .{ .name = "kind" },
2575 .{ .name = "actor" },
2576 .{ .name = "text" },
2577 .{ .name = "payload" },
2578 .{ .name = "created_ns" },
2579 },
2580 .indexes = &.{.{
2581 .name = "events_branch_sequence",
2582 .fields = &.{ 0, 1 },
2583 }},
2584 }, .{ .durability = .buffered });
2585
2586 var statement = try prepareReadOnly(&catalog, std.testing.allocator, "SELECT branch, sequence, kind, actor, text, payload, created_ns FROM events WHERE branch = ?1 AND sequence >= ?2 LIMIT ?3");
2587 defer statement.deinit();
2588 const execution = switch (statement.execution) {
2589 .select => |select| select,
2590 else => return error.UnsupportedStatement,
2591 };
2592 switch (execution.access) {
2593 .index => |access| {
2594 try std.testing.expectEqual(@as(usize, 0), access.index_slot);
2595 try std.testing.expectEqual(@as(usize, 2), access.prefix_count);
2596 try std.testing.expectEqual(@as(usize, 1), access.equality_count);
2597 },
2598 else => return error.UnsupportedStatement,
2599 }
2600 }
2601
2602 test "prepared statements drop tables and report missing tables" {
2603 var tmp = std.testing.tmpDir(.{});
2604 defer tmp.cleanup();
2605
2606 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2607 .paths = .{ .database = "statement-drop.db", .wal = "statement-drop.wal" },
2608 .header = testingHeader(),
2609 });
2610 defer database.deinit();
2611 try database.reserve(.{ .wal_frames = 620 });
2612
2613 var catalog = try catalog_mod.Catalog.open(&database, .{});
2614 _ = try catalog.createRelation(std.testing.allocator, .{
2615 .name = "items",
2616 .columns = &.{.{ .name = "name" }},
2617 }, .{ .durability = .buffered });
2618 _ = try catalog.createRelation(std.testing.allocator, .{
2619 .name = "users",
2620 .columns = &.{.{ .name = "name" }},
2621 }, .{ .durability = .buffered });
2622 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2623 defer database_session.deinit();
2624
2625 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name) VALUES (1, 'kept')");
2626 defer insert.deinit();
2627 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2628 defer inserted.deinit(std.testing.allocator);
2629
2630 var stale = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE rowid = 1");
2631 defer stale.deinit();
2632
2633 var drop = try prepare(&catalog, std.testing.allocator, "DROP TABLE items");
2634 defer drop.deinit();
2635 try std.testing.expectError(error.WriteSessionRequired, drop.execute(std.testing.allocator, .{ .durability = .buffered }));
2636 var dropped = try drop.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2637 defer dropped.deinit(std.testing.allocator);
2638 const drop_flush = switch (dropped) {
2639 .catalog => |flush| flush,
2640 else => return error.UnsupportedStatement,
2641 };
2642 try std.testing.expect(version.same(drop_flush.database, database_session.checkout.working.working));
2643 try std.testing.expect(testWorkingEntry(&database_session, "items") == null);
2644 try std.testing.expect(testWorkingEntry(&database_session, "users") != null);
2645 try std.testing.expectError(error.RelationNotFound, catalog.openRelation(std.testing.allocator, "items"));
2646
2647 try std.testing.expectError(error.TableNotFound, prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE rowid = 1"));
2648 try std.testing.expectError(error.TableNotFound, prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name) VALUES (2, 'gone')"));
2649 try std.testing.expectError(error.TableNotFound, stale.execute(std.testing.allocator, .{ .durability = .buffered }));
2650 try std.testing.expectError(error.TableNotFound, drop.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session }));
2651
2652 var missing = try prepare(&catalog, std.testing.allocator, "DROP TABLE absent");
2653 defer missing.deinit();
2654 try std.testing.expectError(error.TableNotFound, missing.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session }));
2655
2656 var survivor = try prepare(&catalog, std.testing.allocator, "SELECT name FROM users");
2657 defer survivor.deinit();
2658 var survivor_rows = try survivor.execute(std.testing.allocator, .{ .durability = .buffered });
2659 defer survivor_rows.deinit(std.testing.allocator);
2660 try std.testing.expectEqual(@as(usize, 0), survivor_rows.rowCount());
2661 }
2662
2663 test "prepared statements insert select and delete rowid rows" {
2664 comptime {
2665 @stardustClaim(
2666 @import("alloc_phase").capacity.witness(@import("./insert/root.zig").Execution, "sql_language_semantics_transitive_risk"),
2667 null,
2668 null,
2669 null,
2670 null,
2671 null,
2672 null,
2673 );
2674 }
2675 comptime {
2676 @stardustClaim(
2677 @import("alloc_phase").capacity.witness(@import("./insert/root.zig").Execution, "sql_language_semantics_foreign_risk"),
2678 null,
2679 null,
2680 null,
2681 null,
2682 null,
2683 null,
2684 );
2685 }
2686
2687 var tmp = std.testing.tmpDir(.{});
2688 defer tmp.cleanup();
2689
2690 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2691 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
2692 .header = testingHeader(),
2693 });
2694 defer database.deinit();
2695 try database.reserve(.{ .wal_frames = 320 });
2696
2697 var catalog = try catalog_mod.Catalog.open(&database, .{});
2698 _ = try catalog.createRelation(std.testing.allocator, .{
2699 .name = "items",
2700 }, .{ .durability = .buffered });
2701 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2702 defer database_session.deinit();
2703
2704 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items VALUES (8, 42, 'forty-two')");
2705 defer insert.deinit();
2706 try std.testing.expectError(error.WriteSessionRequired, insert.execute(std.testing.allocator, .{ .durability = .buffered }));
2707 var insert_result = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2708 defer insert_result.deinit(std.testing.allocator);
2709 try std.testing.expectEqual(@as(usize, 2), insert_result.mutation.commit.pages);
2710 try std.testing.expect(version.same(insert_result.mutation.database, database_session.checkout.working.working));
2711 try std.testing.expect(database_session.checkout.working.dirty());
2712
2713 var select = try prepare(&catalog, std.testing.allocator, "SELECT * FROM items WHERE rowid = 8");
2714 defer select.deinit();
2715 var select_result = try select.execute(std.testing.allocator, .{ .durability = .buffered });
2716 defer select_result.deinit(std.testing.allocator);
2717 const bytes = select_result.nextRow().?;
2718 const view = try row.View.init(bytes);
2719 try std.testing.expectEqual(@as(i64, 42), (try view.column(0)).integer);
2720 try std.testing.expectEqualStrings("forty-two", (try view.column(1)).text);
2721 try std.testing.expect(select_result.nextRow() == null);
2722
2723 var delete_statement = try prepare(&catalog, std.testing.allocator, "DELETE FROM items WHERE rowid = 8");
2724 defer delete_statement.deinit();
2725 var delete_result = try delete_statement.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2726 defer delete_result.deinit(std.testing.allocator);
2727 try std.testing.expectEqual(@as(usize, 2), delete_result.mutation.commit.pages);
2728 try std.testing.expect(version.same(delete_result.mutation.database, database_session.checkout.working.working));
2729
2730 var missing = try select.execute(std.testing.allocator, .{ .durability = .buffered });
2731 defer missing.deinit(std.testing.allocator);
2732 try std.testing.expect(missing.nextRow() == null);
2733 }
2734
2735 test "prepared statements resolve catalog columns for insert and select projection" {
2736 comptime {
2737 @stardustClaim(
2738 @import("alloc_phase").capacity.witness(@import("./insert/root.zig").Execution, "sql_explicit_semantics"),
2739 null,
2740 null,
2741 null,
2742 null,
2743 null,
2744 null,
2745 );
2746 }
2747
2748 var tmp = std.testing.tmpDir(.{});
2749 defer tmp.cleanup();
2750
2751 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2752 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
2753 .header = testingHeader(),
2754 });
2755 defer database.deinit();
2756 try database.reserve(.{ .wal_frames = 420 });
2757
2758 var catalog = try catalog_mod.Catalog.open(&database, .{});
2759 const columns = [_]catalog_mod.ColumnDefinition{
2760 .{ .name = "name" },
2761 .{ .name = "score" },
2762 .{ .name = "note", .default = .{ .text = "fresh" } },
2763 };
2764 _ = try catalog.createRelation(std.testing.allocator, .{
2765 .name = "items",
2766 .columns = &columns,
2767 }, .{ .durability = .buffered });
2768 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2769 defer database_session.deinit();
2770
2771 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, score, name) VALUES (12, 99, 'Ada')");
2772 defer insert.deinit();
2773 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2774 defer inserted.deinit(std.testing.allocator);
2775
2776 var select = try prepare(&catalog, std.testing.allocator, "SELECT name, score, note, rowid FROM items WHERE rowid = 12");
2777 defer select.deinit();
2778 var selected = try select.execute(std.testing.allocator, .{ .durability = .buffered });
2779 defer selected.deinit(std.testing.allocator);
2780
2781 const bytes = selected.nextRow().?;
2782 const view = try row.View.init(bytes);
2783 try std.testing.expectEqualStrings("Ada", (try view.column(0)).text);
2784 try std.testing.expectEqual(@as(i64, 99), (try view.column(1)).integer);
2785 try std.testing.expectEqualStrings("fresh", (try view.column(2)).text);
2786 try std.testing.expectEqual(@as(i64, 12), (try view.column(3)).integer);
2787
2788 _ = try catalog.createRelation(std.testing.allocator, .{
2789 .name = "users",
2790 .columns = &.{.{ .name = "name" }},
2791 }, .{ .durability = .buffered });
2792 var selected_after_schema = try select.execute(std.testing.allocator, .{ .durability = .buffered });
2793 defer selected_after_schema.deinit(std.testing.allocator);
2794 const after_view = try row.View.init(selected_after_schema.nextRow().?);
2795 try std.testing.expectEqualStrings("Ada", (try after_view.column(0)).text);
2796 try std.testing.expectEqual(@as(i64, 99), (try after_view.column(1)).integer);
2797 try std.testing.expectEqualStrings("fresh", (try after_view.column(2)).text);
2798 try std.testing.expectEqual(@as(i64, 12), (try after_view.column(3)).integer);
2799
2800 try std.testing.expectError(error.ColumnNotFound, prepare(&catalog, std.testing.allocator, "SELECT missing FROM items WHERE rowid = 12"));
2801 try std.testing.expectError(error.ExpectedRowId, prepare(&catalog, std.testing.allocator, "INSERT INTO items (name) VALUES ('Ada')"));
2802 try std.testing.expectError(error.ColumnCountMismatch, prepare(&catalog, std.testing.allocator, "INSERT INTO items VALUES (12, 'Ada')"));
2803 }
2804
2805 test "prepared index cursor keeps one relation read across table mutation" {
2806 var tmp = std.testing.tmpDir(.{});
2807 defer tmp.cleanup();
2808 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2809 .paths = .{ .database = "cursor.db", .wal = "cursor.wal" },
2810 .header = testingHeader(),
2811 });
2812 defer database.deinit();
2813 try database.reserve(.{ .wal_frames = 320 });
2814
2815 var catalog = try catalog_mod.Catalog.open(&database, .{});
2816 _ = try catalog.createRelation(std.testing.allocator, .{
2817 .name = "items",
2818 .columns = &.{ .{ .name = "name" }, .{ .name = "score" } },
2819 .indexes = &.{.{ .name = "items_score", .fields = &.{1} }},
2820 }, .{ .durability = .buffered });
2821 var handle = try catalog.openRelation(std.testing.allocator, "items");
2822 defer handle.deinit();
2823 _ = try handle.relation.put(
2824 std.testing.allocator,
2825 2,
2826 &.{ .{ .text = "Ada" }, .{ .integer = 7 } },
2827 .{ .durability = .buffered },
2828 );
2829 _ = try handle.relation.put(
2830 std.testing.allocator,
2831 5,
2832 &.{ .{ .text = "Bea" }, .{ .integer = 7 } },
2833 .{ .durability = .buffered },
2834 );
2835
2836 var select = try prepare(
2837 &catalog,
2838 std.testing.allocator,
2839 "SELECT name, rowid FROM items WHERE score = 7",
2840 );
2841 defer select.deinit();
2842 var cursor: cursor_mod.Cursor = undefined;
2843 try select.openCursor(&cursor, std.testing.allocator);
2844 defer cursor.deinit();
2845 _ = try handle.relation.update(
2846 std.testing.allocator,
2847 2,
2848 &.{.{ .column = 0, .value = .{ .text = "Zed" } }},
2849 .{ .durability = .buffered },
2850 );
2851
2852 const first = try row.View.init((try cursor.next()).?);
2853 try std.testing.expectEqualStrings("Ada", (try first.column(0)).text);
2854 try std.testing.expectEqual(@as(i64, 2), (try first.column(1)).integer);
2855 const second = try row.View.init((try cursor.next()).?);
2856 try std.testing.expectEqualStrings("Bea", (try second.column(0)).text);
2857 try std.testing.expectEqual(@as(i64, 5), (try second.column(1)).integer);
2858 try std.testing.expect(try cursor.next() == null);
2859 }
2860
2861 test "prepared statements choose indexed and scanned predicates" {
2862 var tmp = std.testing.tmpDir(.{});
2863 defer tmp.cleanup();
2864
2865 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
2866 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
2867 .header = recoveredHeader(),
2868 });
2869 defer database.deinit();
2870 try database.reserve(.{ .wal_frames = 620 });
2871
2872 var catalog = try catalog_mod.Catalog.open(&database, .{});
2873 const columns = [_]catalog_mod.ColumnDefinition{
2874 .{ .name = "name" },
2875 .{ .name = "score" },
2876 .{ .name = "note" },
2877 };
2878 const indexes = [_]catalog_mod.IndexDefinition{.{
2879 .name = "items_score",
2880 .fields = &.{1},
2881 }};
2882 _ = try catalog.createRelation(std.testing.allocator, .{
2883 .name = "items",
2884 .columns = &columns,
2885 .indexes = &indexes,
2886 }, .{ .durability = .buffered });
2887 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
2888 defer database_session.deinit();
2889
2890 var first = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score, note) VALUES (2, 'Ada', 7, 'hot')");
2891 defer first.deinit();
2892 var first_insert = try first.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2893 defer first_insert.deinit(std.testing.allocator);
2894
2895 var second = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score, note) VALUES (5, 'Bea', 7, 'hot')");
2896 defer second.deinit();
2897 var second_insert = try second.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2898 defer second_insert.deinit(std.testing.allocator);
2899
2900 var third = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score, note) VALUES (9, 'Cy', 3, 'cold')");
2901 defer third.deinit();
2902 var third_insert = try third.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
2903 defer third_insert.deinit(std.testing.allocator);
2904
2905 var indexed = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE score = ?1");
2906 defer indexed.deinit();
2907 const indexed_execution = switch (indexed.execution) {
2908 .select => |execution| execution,
2909 else => return error.UnsupportedStatement,
2910 };
2911 switch (indexed_execution.access) {
2912 .index => |index_access| {
2913 try std.testing.expectEqual(@as(usize, 1), index_access.field);
2914 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
2915 try std.testing.expectEqual(ast_mod.PredicateOperator.eq, index_access.operator);
2916 },
2917 else => return error.UnsupportedStatement,
2918 }
2919 try indexed.bind(1, .{ .integer = 7 });
2920 var indexed_rows = try indexed.execute(std.testing.allocator, .{ .durability = .buffered });
2921 defer indexed_rows.deinit(std.testing.allocator);
2922 try std.testing.expectEqual(@as(usize, 2), indexed_rows.rowCount());
2923 const indexed_first = try row.View.init(indexed_rows.nextRow().?);
2924 try std.testing.expectEqualStrings("Ada", (try indexed_first.column(0)).text);
2925 try std.testing.expectEqual(@as(i64, 2), (try indexed_first.column(1)).integer);
2926 const indexed_second = try row.View.init(indexed_rows.nextRow().?);
2927 try std.testing.expectEqualStrings("Bea", (try indexed_second.column(0)).text);
2928 try std.testing.expectEqual(@as(i64, 5), (try indexed_second.column(1)).integer);
2929 try std.testing.expect(indexed_rows.nextRow() == null);
2930
2931 var rowid_range = try prepare(&catalog, std.testing.allocator, "SELECT rowid, name FROM items WHERE rowid > 2");
2932 defer rowid_range.deinit();
2933 const rowid_range_execution = switch (rowid_range.execution) {
2934 .select => |execution| execution,
2935 else => return error.UnsupportedStatement,
2936 };
2937 switch (rowid_range_execution.access) {
2938 .rowid => |rowid_access| try std.testing.expectEqual(ast_mod.PredicateOperator.gt, rowid_access.operator),
2939 else => return error.UnsupportedStatement,
2940 }
2941 var rowid_rows = try rowid_range.execute(std.testing.allocator, .{ .durability = .buffered });
2942 defer rowid_rows.deinit(std.testing.allocator);
2943 try std.testing.expectEqual(@as(usize, 2), rowid_rows.rowCount());
2944 const rowid_first = try row.View.init(rowid_rows.nextRow().?);
2945 try std.testing.expectEqual(@as(i64, 5), (try rowid_first.column(0)).integer);
2946 try std.testing.expectEqualStrings("Bea", (try rowid_first.column(1)).text);
2947 const rowid_second = try row.View.init(rowid_rows.nextRow().?);
2948 try std.testing.expectEqual(@as(i64, 9), (try rowid_second.column(0)).integer);
2949 try std.testing.expectEqualStrings("Cy", (try rowid_second.column(1)).text);
2950 try std.testing.expect(rowid_rows.nextRow() == null);
2951 var rowid_cursor: cursor_mod.Cursor = undefined;
2952 try rowid_range.openCursor(&rowid_cursor, std.testing.allocator);
2953 defer rowid_cursor.deinit();
2954 const rowid_cursor_first = try row.View.init((try rowid_cursor.next()).?);
2955 try std.testing.expectEqual(@as(i64, 5), (try rowid_cursor_first.column(0)).integer);
2956 try std.testing.expectEqualStrings("Bea", (try rowid_cursor_first.column(1)).text);
2957 const rowid_cursor_second = try row.View.init((try rowid_cursor.next()).?);
2958 try std.testing.expectEqual(@as(i64, 9), (try rowid_cursor_second.column(0)).integer);
2959 try std.testing.expectEqualStrings("Cy", (try rowid_cursor_second.column(1)).text);
2960 try std.testing.expect(try rowid_cursor.next() == null);
2961
2962 var indexed_range = try prepare(&catalog, std.testing.allocator, "SELECT name, score, rowid FROM items WHERE score >= ?1");
2963 defer indexed_range.deinit();
2964 const indexed_range_execution = switch (indexed_range.execution) {
2965 .select => |execution| execution,
2966 else => return error.UnsupportedStatement,
2967 };
2968 switch (indexed_range_execution.access) {
2969 .index => |index_access| {
2970 try std.testing.expectEqual(@as(usize, 1), index_access.field);
2971 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
2972 try std.testing.expectEqual(ast_mod.PredicateOperator.gte, index_access.operator);
2973 },
2974 else => return error.UnsupportedStatement,
2975 }
2976 try indexed_range.bind(1, .{ .integer = 7 });
2977 var indexed_range_rows = try indexed_range.execute(std.testing.allocator, .{ .durability = .buffered });
2978 defer indexed_range_rows.deinit(std.testing.allocator);
2979 try std.testing.expectEqual(@as(usize, 2), indexed_range_rows.rowCount());
2980 const indexed_range_first = try row.View.init(indexed_range_rows.nextRow().?);
2981 try std.testing.expectEqualStrings("Ada", (try indexed_range_first.column(0)).text);
2982 try std.testing.expectEqual(@as(i64, 7), (try indexed_range_first.column(1)).integer);
2983 try std.testing.expectEqual(@as(i64, 2), (try indexed_range_first.column(2)).integer);
2984 const indexed_range_second = try row.View.init(indexed_range_rows.nextRow().?);
2985 try std.testing.expectEqualStrings("Bea", (try indexed_range_second.column(0)).text);
2986 try std.testing.expectEqual(@as(i64, 7), (try indexed_range_second.column(1)).integer);
2987 try std.testing.expectEqual(@as(i64, 5), (try indexed_range_second.column(2)).integer);
2988 try std.testing.expect(indexed_range_rows.nextRow() == null);
2989 var indexed_range_cursor: cursor_mod.Cursor = undefined;
2990 try indexed_range.openCursor(&indexed_range_cursor, std.testing.allocator);
2991 defer indexed_range_cursor.deinit();
2992 const indexed_range_cursor_first = try row.View.init((try indexed_range_cursor.next()).?);
2993 try std.testing.expectEqualStrings("Ada", (try indexed_range_cursor_first.column(0)).text);
2994 try std.testing.expectEqual(@as(i64, 7), (try indexed_range_cursor_first.column(1)).integer);
2995 try std.testing.expectEqual(@as(i64, 2), (try indexed_range_cursor_first.column(2)).integer);
2996 const indexed_range_cursor_second = try row.View.init((try indexed_range_cursor.next()).?);
2997 try std.testing.expectEqualStrings("Bea", (try indexed_range_cursor_second.column(0)).text);
2998 try std.testing.expectEqual(@as(i64, 7), (try indexed_range_cursor_second.column(1)).integer);
2999 try std.testing.expectEqual(@as(i64, 5), (try indexed_range_cursor_second.column(2)).integer);
3000 try std.testing.expect(try indexed_range_cursor.next() == null);
3001
3002 var covered_range = try prepare(&catalog, std.testing.allocator, "SELECT score, rowid FROM items WHERE score >= ?1");
3003 defer covered_range.deinit();
3004 const covered_range_execution = switch (covered_range.execution) {
3005 .select => |execution| execution,
3006 else => return error.UnsupportedStatement,
3007 };
3008 switch (covered_range_execution.access) {
3009 .index => |index_access| {
3010 try std.testing.expectEqual(@as(usize, 1), index_access.field);
3011 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
3012 try std.testing.expect(access_mod.indexCoversProjection(&covered_range.relation.?.handle, index_access.index_slot, covered_range_execution.fields));
3013 },
3014 else => return error.UnsupportedStatement,
3015 }
3016 try covered_range.bind(1, .{ .integer = 7 });
3017 var covered_range_rows = try covered_range.execute(std.testing.allocator, .{ .durability = .buffered });
3018 defer covered_range_rows.deinit(std.testing.allocator);
3019 try std.testing.expectEqual(@as(usize, 2), covered_range_rows.rowCount());
3020 const covered_range_first = try row.View.init(covered_range_rows.nextRow().?);
3021 try std.testing.expectEqual(@as(i64, 7), (try covered_range_first.column(0)).integer);
3022 try std.testing.expectEqual(@as(i64, 2), (try covered_range_first.column(1)).integer);
3023 const covered_range_second = try row.View.init(covered_range_rows.nextRow().?);
3024 try std.testing.expectEqual(@as(i64, 7), (try covered_range_second.column(0)).integer);
3025 try std.testing.expectEqual(@as(i64, 5), (try covered_range_second.column(1)).integer);
3026 try std.testing.expect(covered_range_rows.nextRow() == null);
3027 var covered_range_cursor: cursor_mod.Cursor = undefined;
3028 try covered_range.openCursor(&covered_range_cursor, std.testing.allocator);
3029 defer covered_range_cursor.deinit();
3030 const covered_range_cursor_first = try row.View.init((try covered_range_cursor.next()).?);
3031 try std.testing.expectEqual(@as(i64, 7), (try covered_range_cursor_first.column(0)).integer);
3032 try std.testing.expectEqual(@as(i64, 2), (try covered_range_cursor_first.column(1)).integer);
3033 const covered_range_cursor_second = try row.View.init((try covered_range_cursor.next()).?);
3034 try std.testing.expectEqual(@as(i64, 7), (try covered_range_cursor_second.column(0)).integer);
3035 try std.testing.expectEqual(@as(i64, 5), (try covered_range_cursor_second.column(1)).integer);
3036 try std.testing.expect(try covered_range_cursor.next() == null);
3037
3038 var indexed_lt = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE score < 7");
3039 defer indexed_lt.deinit();
3040 var indexed_lt_rows = try indexed_lt.execute(std.testing.allocator, .{ .durability = .buffered });
3041 defer indexed_lt_rows.deinit(std.testing.allocator);
3042 try std.testing.expectEqual(@as(usize, 1), indexed_lt_rows.rowCount());
3043 const indexed_lt_first = try row.View.init(indexed_lt_rows.nextRow().?);
3044 try std.testing.expectEqualStrings("Cy", (try indexed_lt_first.column(0)).text);
3045 try std.testing.expectEqual(@as(i64, 9), (try indexed_lt_first.column(1)).integer);
3046 try std.testing.expect(indexed_lt_rows.nextRow() == null);
3047
3048 var scanned = try prepare(&catalog, std.testing.allocator, "SELECT rowid, name FROM items WHERE note = 'hot'");
3049 defer scanned.deinit();
3050 const scanned_execution = switch (scanned.execution) {
3051 .select => |execution| execution,
3052 else => return error.UnsupportedStatement,
3053 };
3054 switch (scanned_execution.access) {
3055 .scan => |scan_access| {
3056 try std.testing.expectEqual(@as(usize, 2), scan_access.?.field);
3057 try std.testing.expectEqual(ast_mod.PredicateOperator.eq, scan_access.?.operator);
3058 },
3059 else => return error.UnsupportedStatement,
3060 }
3061 var scanned_rows = try scanned.execute(std.testing.allocator, .{ .durability = .buffered });
3062 defer scanned_rows.deinit(std.testing.allocator);
3063 try std.testing.expectEqual(@as(usize, 2), scanned_rows.rowCount());
3064 const scanned_first = try row.View.init(scanned_rows.nextRow().?);
3065 try std.testing.expectEqual(@as(i64, 2), (try scanned_first.column(0)).integer);
3066 try std.testing.expectEqualStrings("Ada", (try scanned_first.column(1)).text);
3067 const scanned_second = try row.View.init(scanned_rows.nextRow().?);
3068 try std.testing.expectEqual(@as(i64, 5), (try scanned_second.column(0)).integer);
3069 try std.testing.expectEqualStrings("Bea", (try scanned_second.column(1)).text);
3070 try std.testing.expect(scanned_rows.nextRow() == null);
3071
3072 var scanned_range = try prepare(&catalog, std.testing.allocator, "SELECT rowid, name FROM items WHERE note > 'cold'");
3073 defer scanned_range.deinit();
3074 const scanned_range_execution = switch (scanned_range.execution) {
3075 .select => |execution| execution,
3076 else => return error.UnsupportedStatement,
3077 };
3078 switch (scanned_range_execution.access) {
3079 .scan => |scan_access| {
3080 try std.testing.expectEqual(@as(usize, 2), scan_access.?.field);
3081 try std.testing.expectEqual(ast_mod.PredicateOperator.gt, scan_access.?.operator);
3082 },
3083 else => return error.UnsupportedStatement,
3084 }
3085 var scanned_range_rows = try scanned_range.execute(std.testing.allocator, .{ .durability = .buffered });
3086 defer scanned_range_rows.deinit(std.testing.allocator);
3087 try std.testing.expectEqual(@as(usize, 2), scanned_range_rows.rowCount());
3088 const scanned_range_first = try row.View.init(scanned_range_rows.nextRow().?);
3089 try std.testing.expectEqual(@as(i64, 2), (try scanned_range_first.column(0)).integer);
3090 try std.testing.expectEqualStrings("Ada", (try scanned_range_first.column(1)).text);
3091 const scanned_range_second = try row.View.init(scanned_range_rows.nextRow().?);
3092 try std.testing.expectEqual(@as(i64, 5), (try scanned_range_second.column(0)).integer);
3093 try std.testing.expectEqualStrings("Bea", (try scanned_range_second.column(1)).text);
3094 try std.testing.expect(scanned_range_rows.nextRow() == null);
3095 }
3096
3097 test "prepared statements use and predicates with composite index prefixes" {
3098 var tmp = std.testing.tmpDir(.{});
3099 defer tmp.cleanup();
3100
3101 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3102 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3103 .header = recoveredHeader(),
3104 });
3105 defer database.deinit();
3106 try database.reserve(.{ .wal_frames = 720 });
3107
3108 var catalog = try catalog_mod.Catalog.open(&database, .{});
3109 const columns = [_]catalog_mod.ColumnDefinition{
3110 .{ .name = "region" },
3111 .{ .name = "payload" },
3112 .{ .name = "name" },
3113 };
3114 const indexes = [_]catalog_mod.IndexDefinition{
3115 .{
3116 .name = "items_region",
3117 .fields = &.{0},
3118 },
3119 .{
3120 .name = "items_region_payload",
3121 .fields = &.{ 0, 1 },
3122 },
3123 };
3124 _ = try catalog.createRelation(std.testing.allocator, .{
3125 .name = "items",
3126 .columns = &columns,
3127 .indexes = &indexes,
3128 }, .{ .durability = .buffered });
3129
3130 var handle = try catalog.openRelation(std.testing.allocator, "items");
3131 defer handle.deinit();
3132 _ = try handle.relation.put(std.testing.allocator, 1, &.{ .{ .integer = 1 }, .{ .integer = 5 }, .{ .text = "alpha" } }, .{ .durability = .buffered });
3133 _ = try handle.relation.put(std.testing.allocator, 2, &.{ .{ .integer = 1 }, .{ .integer = 7 }, .{ .text = "beta" } }, .{ .durability = .buffered });
3134 _ = try handle.relation.put(std.testing.allocator, 3, &.{ .{ .integer = 1 }, .{ .integer = 8 }, .{ .text = "gamma" } }, .{ .durability = .buffered });
3135 _ = try handle.relation.put(std.testing.allocator, 4, &.{ .{ .integer = 2 }, .{ .integer = 7 }, .{ .text = "delta" } }, .{ .durability = .buffered });
3136
3137 var residual = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE region = ?1 AND name = 'gamma'");
3138 defer residual.deinit();
3139 const residual_execution = switch (residual.execution) {
3140 .select => |execution| execution,
3141 else => return error.UnsupportedStatement,
3142 };
3143 switch (residual_execution.access) {
3144 .index => |index_access| {
3145 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
3146 try std.testing.expectEqual(@as(usize, 1), index_access.prefix_count);
3147 },
3148 else => return error.UnsupportedStatement,
3149 }
3150 try residual.bind(1, .{ .integer = 1 });
3151 var residual_rows = try residual.execute(std.testing.allocator, .{ .durability = .buffered });
3152 defer residual_rows.deinit(std.testing.allocator);
3153 try std.testing.expectEqual(@as(usize, 1), residual_rows.rowCount());
3154 const residual_view = try row.View.init(residual_rows.nextRow().?);
3155 try std.testing.expectEqualStrings("gamma", (try residual_view.column(0)).text);
3156 try std.testing.expectEqual(@as(i64, 3), (try residual_view.column(1)).integer);
3157 try std.testing.expect(residual_rows.nextRow() == null);
3158
3159 var composite = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE region = ?1 AND payload = ?2");
3160 defer composite.deinit();
3161 const composite_execution = switch (composite.execution) {
3162 .select => |execution| execution,
3163 else => return error.UnsupportedStatement,
3164 };
3165 switch (composite_execution.access) {
3166 .index => |index_access| {
3167 try std.testing.expectEqual(@as(usize, 1), index_access.index_slot);
3168 try std.testing.expectEqual(@as(usize, 2), index_access.prefix_count);
3169 try std.testing.expectEqual(@as(usize, 2), index_access.equality_count);
3170 try std.testing.expectEqual(@as(usize, 0), index_access.prefix_predicates[0]);
3171 try std.testing.expectEqual(@as(usize, 1), index_access.prefix_predicates[1]);
3172 },
3173 else => return error.UnsupportedStatement,
3174 }
3175 try composite.bind(1, .{ .integer = 1 });
3176 try composite.bind(2, .{ .integer = 7 });
3177 var composite_rows = try composite.execute(std.testing.allocator, .{ .durability = .buffered });
3178 defer composite_rows.deinit(std.testing.allocator);
3179 try std.testing.expectEqual(@as(usize, 1), composite_rows.rowCount());
3180 const composite_view = try row.View.init(composite_rows.nextRow().?);
3181 try std.testing.expectEqualStrings("beta", (try composite_view.column(0)).text);
3182 try std.testing.expectEqual(@as(i64, 2), (try composite_view.column(1)).integer);
3183 try std.testing.expect(composite_rows.nextRow() == null);
3184
3185 var composite_cursor: cursor_mod.Cursor = undefined;
3186 try composite.openCursor(&composite_cursor, std.testing.allocator);
3187 defer composite_cursor.deinit();
3188 const cursor_view = try row.View.init((try composite_cursor.next()).?);
3189 try std.testing.expectEqualStrings("beta", (try cursor_view.column(0)).text);
3190 try std.testing.expectEqual(@as(i64, 2), (try cursor_view.column(1)).integer);
3191 try std.testing.expect(try composite_cursor.next() == null);
3192
3193 var range = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE region = ?1 AND payload >= ?2");
3194 defer range.deinit();
3195 const range_execution = switch (range.execution) {
3196 .select => |execution| execution,
3197 else => return error.UnsupportedStatement,
3198 };
3199 switch (range_execution.access) {
3200 .index => |index_access| {
3201 try std.testing.expectEqual(@as(usize, 1), index_access.index_slot);
3202 try std.testing.expectEqual(ast_mod.PredicateOperator.gte, index_access.operator);
3203 try std.testing.expectEqual(@as(usize, 2), index_access.prefix_count);
3204 try std.testing.expectEqual(@as(usize, 1), index_access.equality_count);
3205 try std.testing.expectEqual(@as(usize, 0), index_access.prefix_predicates[0]);
3206 try std.testing.expectEqual(@as(usize, 1), index_access.prefix_predicates[1]);
3207 const bound_values = [_]row.Value{ .{ .integer = 1 }, .{ .integer = 7 } };
3208 const start = predicate_mod.indexedRangeStart(index_access, &bound_values).?;
3209 try std.testing.expectEqual(@as(usize, 2), start.values.len);
3210 try std.testing.expect(start.inclusive);
3211 try std.testing.expectEqual(@as(i64, 1), start.values[0].integer);
3212 try std.testing.expectEqual(@as(i64, 7), start.values[1].integer);
3213 const end = predicate_mod.indexedRangeEnd(index_access, &bound_values).?;
3214 try std.testing.expectEqual(@as(usize, 1), end.values.len);
3215 try std.testing.expect(end.inclusive);
3216 try std.testing.expectEqual(@as(i64, 1), end.values[0].integer);
3217 },
3218 else => return error.UnsupportedStatement,
3219 }
3220 try range.bind(1, .{ .integer = 1 });
3221 try range.bind(2, .{ .integer = 7 });
3222 var range_rows = try range.execute(std.testing.allocator, .{ .durability = .buffered });
3223 defer range_rows.deinit(std.testing.allocator);
3224 try std.testing.expectEqual(@as(usize, 2), range_rows.rowCount());
3225 const range_first = try row.View.init(range_rows.nextRow().?);
3226 try std.testing.expectEqualStrings("beta", (try range_first.column(0)).text);
3227 try std.testing.expectEqual(@as(i64, 2), (try range_first.column(1)).integer);
3228 const range_second = try row.View.init(range_rows.nextRow().?);
3229 try std.testing.expectEqualStrings("gamma", (try range_second.column(0)).text);
3230 try std.testing.expectEqual(@as(i64, 3), (try range_second.column(1)).integer);
3231 try std.testing.expect(range_rows.nextRow() == null);
3232
3233 var range_cursor: cursor_mod.Cursor = undefined;
3234 try range.openCursor(&range_cursor, std.testing.allocator);
3235 defer range_cursor.deinit();
3236 const range_cursor_first = try row.View.init((try range_cursor.next()).?);
3237 try std.testing.expectEqualStrings("beta", (try range_cursor_first.column(0)).text);
3238 try std.testing.expectEqual(@as(i64, 2), (try range_cursor_first.column(1)).integer);
3239 const range_cursor_second = try row.View.init((try range_cursor.next()).?);
3240 try std.testing.expectEqualStrings("gamma", (try range_cursor_second.column(0)).text);
3241 try std.testing.expectEqual(@as(i64, 3), (try range_cursor_second.column(1)).integer);
3242 try std.testing.expect(try range_cursor.next() == null);
3243 }
3244
3245 test "prepared statements use composite index after checkpoint reopen" {
3246 var tmp = std.testing.tmpDir(.{});
3247 defer tmp.cleanup();
3248
3249 {
3250 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3251 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3252 .header = recoveredHeader(),
3253 });
3254 defer database.deinit();
3255 try database.reserve(.{ .wal_frames = 720 });
3256
3257 var catalog = try catalog_mod.Catalog.open(&database, .{});
3258 const columns = [_]catalog_mod.ColumnDefinition{
3259 .{ .name = "region" },
3260 .{ .name = "payload" },
3261 .{ .name = "name" },
3262 };
3263 const indexes = [_]catalog_mod.IndexDefinition{.{
3264 .name = "items_region_payload",
3265 .fields = &.{ 0, 1 },
3266 }};
3267 _ = try catalog.createRelation(std.testing.allocator, .{
3268 .name = "items",
3269 .columns = &columns,
3270 .indexes = &indexes,
3271 }, .{ .durability = .buffered });
3272
3273 var handle = try catalog.openRelation(std.testing.allocator, "items");
3274 defer handle.deinit();
3275 _ = try handle.relation.put(std.testing.allocator, 1, &.{ .{ .integer = 1 }, .{ .integer = 5 }, .{ .text = "alpha" } }, .{ .durability = .buffered });
3276 _ = try handle.relation.put(std.testing.allocator, 2, &.{ .{ .integer = 1 }, .{ .integer = 7 }, .{ .text = "beta" } }, .{ .durability = .buffered });
3277 _ = try handle.relation.put(std.testing.allocator, 3, &.{ .{ .integer = 1 }, .{ .integer = 8 }, .{ .text = "gamma" } }, .{ .durability = .buffered });
3278 try database.syncWal();
3279 _ = try database.checkpoint(.{ .restart_header = recoveredHeader() });
3280 }
3281
3282 var reopened = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3283 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3284 .header = recoveredHeader(),
3285 });
3286 defer reopened.deinit();
3287 var catalog = try catalog_mod.Catalog.open(&reopened, .{});
3288 var statement = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE region = ?1 AND payload >= ?2");
3289 defer statement.deinit();
3290 try statement.bind(1, .{ .integer = 1 });
3291 try statement.bind(2, .{ .integer = 7 });
3292 var rows = try statement.execute(std.testing.allocator, .{ .durability = .buffered });
3293 defer rows.deinit(std.testing.allocator);
3294 try std.testing.expectEqual(@as(usize, 2), rows.rowCount());
3295 const first = try row.View.init(rows.nextRow().?);
3296 try std.testing.expectEqualStrings("beta", (try first.column(0)).text);
3297 try std.testing.expectEqual(@as(i64, 2), (try first.column(1)).integer);
3298 const second = try row.View.init(rows.nextRow().?);
3299 try std.testing.expectEqualStrings("gamma", (try second.column(0)).text);
3300 try std.testing.expectEqual(@as(i64, 3), (try second.column(1)).integer);
3301 }
3302
3303 test "prepared statements use large composite index after checkpoint reopen" {
3304 var tmp = std.testing.tmpDir(.{});
3305 defer tmp.cleanup();
3306 const row_count = 4096;
3307
3308 {
3309 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3310 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3311 .header = recoveredHeader(),
3312 .max_wal_bytes = 256 * 1024 * 1024,
3313 });
3314 defer database.deinit();
3315 try database.reserve(.{ .wal_frames = row_count * 8 });
3316
3317 var catalog = try catalog_mod.Catalog.open(&database, .{});
3318 const columns = [_]catalog_mod.ColumnDefinition{
3319 .{ .name = "region" },
3320 .{ .name = "sequence" },
3321 .{ .name = "name" },
3322 };
3323 const indexes = [_]catalog_mod.IndexDefinition{.{
3324 .name = "items_region_sequence",
3325 .fields = &.{ 0, 1 },
3326 }};
3327 _ = try catalog.createRelation(std.testing.allocator, .{
3328 .name = "items",
3329 .columns = &columns,
3330 .indexes = &indexes,
3331 }, .{ .durability = .buffered });
3332
3333 var handle = try catalog.openRelation(std.testing.allocator, "items");
3334 defer handle.deinit();
3335 var index: usize = 0;
3336 while (index < row_count) : (index += 1) {
3337 const name = try std.fmt.allocPrint(std.testing.allocator, "item-{d}", .{index});
3338 defer std.testing.allocator.free(name);
3339 _ = try handle.relation.put(std.testing.allocator, @intCast(index + 1), &.{
3340 .{ .integer = 0 },
3341 .{ .integer = @intCast(index) },
3342 .{ .text = name },
3343 }, .{ .durability = .buffered });
3344 }
3345 try database.syncWal();
3346 _ = try database.checkpoint(.{ .restart_header = recoveredHeader() });
3347 }
3348
3349 var reopened = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3350 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3351 .header = recoveredHeader(),
3352 });
3353 defer reopened.deinit();
3354 var catalog = try catalog_mod.Catalog.open(&reopened, .{});
3355 var statement = try prepare(&catalog, std.testing.allocator, "SELECT name, rowid FROM items WHERE region = ?1 AND sequence >= ?2 LIMIT ?3");
3356 defer statement.deinit();
3357 try statement.bind(1, .{ .integer = 0 });
3358 try statement.bind(2, .{ .integer = row_count - 3 });
3359 try statement.bind(3, .{ .integer = 3 });
3360 var rows = try statement.execute(std.testing.allocator, .{ .durability = .buffered });
3361 defer rows.deinit(std.testing.allocator);
3362 try std.testing.expectEqual(@as(usize, 3), rows.rowCount());
3363 const first = try row.View.init(rows.nextRow().?);
3364 try std.testing.expectEqualStrings("item-4093", (try first.column(0)).text);
3365 try std.testing.expectEqual(@as(i64, 4094), (try first.column(1)).integer);
3366 const second = try row.View.init(rows.nextRow().?);
3367 try std.testing.expectEqualStrings("item-4094", (try second.column(0)).text);
3368 try std.testing.expectEqual(@as(i64, 4095), (try second.column(1)).integer);
3369 const third = try row.View.init(rows.nextRow().?);
3370 try std.testing.expectEqualStrings("item-4095", (try third.column(0)).text);
3371 try std.testing.expectEqual(@as(i64, 4096), (try third.column(1)).integer);
3372 }
3373
3374 test "prepared statements use analyzed stats to prefer covered index" {
3375 var tmp = std.testing.tmpDir(.{});
3376 defer tmp.cleanup();
3377
3378 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3379 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3380 .header = testingHeader(),
3381 });
3382 defer database.deinit();
3383 try database.reserve(.{ .wal_frames = 980 });
3384
3385 var catalog = try catalog_mod.Catalog.open(&database, .{});
3386 const columns = [_]catalog_mod.ColumnDefinition{
3387 .{ .name = "name" },
3388 .{ .name = "score" },
3389 .{ .name = "note" },
3390 };
3391 const indexes = [_]catalog_mod.IndexDefinition{
3392 .{
3393 .name = "items_score",
3394 .fields = &.{1},
3395 },
3396 .{
3397 .name = "items_score_name",
3398 .fields = &.{ 1, 0 },
3399 },
3400 };
3401 _ = try catalog.createRelation(std.testing.allocator, .{
3402 .name = "items",
3403 .columns = &columns,
3404 .indexes = &indexes,
3405 }, .{ .durability = .buffered });
3406
3407 var handle = try catalog.openRelation(std.testing.allocator, "items");
3408 defer handle.deinit();
3409 var index: usize = 0;
3410 while (index < 40) : (index += 1) {
3411 var name_buffer: [24]u8 = undefined;
3412 const name = try std.fmt.bufPrint(&name_buffer, "name-{d:0>3}", .{index});
3413 const score = @as(i64, @intCast(index % 5));
3414 _ = try handle.relation.put(
3415 std.testing.allocator,
3416 @intCast(index + 1),
3417 &.{
3418 .{ .text = name },
3419 .{ .integer = score },
3420 .{ .text = "kept" },
3421 },
3422 .{ .durability = .buffered },
3423 );
3424 }
3425
3426 var before = try prepare(&catalog, std.testing.allocator, "SELECT name, score, rowid FROM items WHERE score = ?1");
3427 defer before.deinit();
3428 const before_execution = switch (before.execution) {
3429 .select => |execution| execution,
3430 else => return error.UnsupportedStatement,
3431 };
3432 switch (before_execution.access) {
3433 .index => |index_access| {
3434 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
3435 try std.testing.expect(!index_access.cost.from_stats);
3436 },
3437 else => return error.UnsupportedStatement,
3438 }
3439
3440 _ = try catalog.analyzeRelation(std.testing.allocator, "items", .{ .durability = .buffered });
3441
3442 var after = try prepare(&catalog, std.testing.allocator, "SELECT name, score, rowid FROM items WHERE score = ?1");
3443 defer after.deinit();
3444 const after_execution = switch (after.execution) {
3445 .select => |execution| execution,
3446 else => return error.UnsupportedStatement,
3447 };
3448 switch (after_execution.access) {
3449 .index => |index_access| {
3450 try std.testing.expectEqual(@as(usize, 1), index_access.index_slot);
3451 try std.testing.expect(index_access.cost.from_stats);
3452 try std.testing.expect(index_access.cost.index_scan < index_access.cost.table_scan);
3453 try std.testing.expect(access_mod.indexCoversProjection(&after.relation.?.handle, index_access.index_slot, after_execution.fields));
3454 },
3455 else => return error.UnsupportedStatement,
3456 }
3457
3458 try after.bind(1, .{ .integer = 3 });
3459 var rows = try after.execute(std.testing.allocator, .{ .durability = .buffered });
3460 defer rows.deinit(std.testing.allocator);
3461 try std.testing.expectEqual(@as(usize, 8), rows.rowCount());
3462 while (rows.nextRow()) |bytes| {
3463 const view = try row.View.init(bytes);
3464 try std.testing.expectEqual(@as(i64, 3), (try view.column(1)).integer);
3465 }
3466 }
3467
3468 test "prepared statements use analyzed distribution to avoid unselective literal index" {
3469 var tmp = std.testing.tmpDir(.{});
3470 defer tmp.cleanup();
3471
3472 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3473 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3474 .header = testingHeader(),
3475 });
3476 defer database.deinit();
3477 try database.reserve(.{ .wal_frames = 920 });
3478
3479 var catalog = try catalog_mod.Catalog.open(&database, .{});
3480 const columns = [_]catalog_mod.ColumnDefinition{
3481 .{ .name = "score" },
3482 .{ .name = "note" },
3483 };
3484 const indexes = [_]catalog_mod.IndexDefinition{.{
3485 .name = "items_score",
3486 .fields = &.{0},
3487 }};
3488 _ = try catalog.createRelation(std.testing.allocator, .{
3489 .name = "items",
3490 .columns = &columns,
3491 .indexes = &indexes,
3492 }, .{ .durability = .buffered });
3493
3494 var handle = try catalog.openRelation(std.testing.allocator, "items");
3495 defer handle.deinit();
3496 var index: usize = 0;
3497 while (index < 64) : (index += 1) {
3498 var note_buffer: [24]u8 = undefined;
3499 const note = try std.fmt.bufPrint(¬e_buffer, "note-{d:0>3}", .{index});
3500 const score: i64 = if (index < 60) 0 else @intCast(index - 59);
3501 _ = try handle.relation.put(
3502 std.testing.allocator,
3503 @intCast(index + 1),
3504 &.{
3505 .{ .integer = score },
3506 .{ .text = note },
3507 },
3508 .{ .durability = .buffered },
3509 );
3510 }
3511
3512 var before = try prepare(&catalog, std.testing.allocator, "SELECT note FROM items WHERE score = 0");
3513 defer before.deinit();
3514 const before_execution = switch (before.execution) {
3515 .select => |execution| execution,
3516 else => return error.UnsupportedStatement,
3517 };
3518 switch (before_execution.access) {
3519 .index => |index_access| {
3520 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
3521 try std.testing.expect(!index_access.cost.from_stats);
3522 },
3523 else => return error.UnsupportedStatement,
3524 }
3525
3526 _ = try catalog.analyzeRelation(std.testing.allocator, "items", .{ .durability = .buffered });
3527
3528 var common = try prepare(&catalog, std.testing.allocator, "SELECT note FROM items WHERE score = 0");
3529 defer common.deinit();
3530 const common_execution = switch (common.execution) {
3531 .select => |execution| execution,
3532 else => return error.UnsupportedStatement,
3533 };
3534 switch (common_execution.access) {
3535 .scan => |scan_access| {
3536 try std.testing.expect(scan_access.?.cost.from_stats);
3537 try std.testing.expect(!scan_access.?.cost.from_distribution);
3538 },
3539 else => return error.UnsupportedStatement,
3540 }
3541 var common_rows = try common.execute(std.testing.allocator, .{ .durability = .buffered });
3542 defer common_rows.deinit(std.testing.allocator);
3543 try std.testing.expectEqual(@as(usize, 60), common_rows.rowCount());
3544
3545 var rare = try prepare(&catalog, std.testing.allocator, "SELECT note FROM items WHERE score = 4");
3546 defer rare.deinit();
3547 const rare_execution = switch (rare.execution) {
3548 .select => |execution| execution,
3549 else => return error.UnsupportedStatement,
3550 };
3551 switch (rare_execution.access) {
3552 .index => |index_access| {
3553 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
3554 try std.testing.expect(index_access.cost.from_distribution);
3555 try std.testing.expect(index_access.cost.estimated_rows <= 1);
3556 },
3557 else => return error.UnsupportedStatement,
3558 }
3559 var rare_rows = try rare.execute(std.testing.allocator, .{ .durability = .buffered });
3560 defer rare_rows.deinit(std.testing.allocator);
3561 try std.testing.expectEqual(@as(usize, 1), rare_rows.rowCount());
3562 }
3563
3564 test "prepared statements use bound distribution for runtime access" {
3565 var tmp = std.testing.tmpDir(.{});
3566 defer tmp.cleanup();
3567
3568 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3569 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3570 .header = testingHeader(),
3571 });
3572 defer database.deinit();
3573 try database.reserve(.{ .wal_frames = 920 });
3574
3575 var catalog = try catalog_mod.Catalog.open(&database, .{});
3576 const columns = [_]catalog_mod.ColumnDefinition{
3577 .{ .name = "score" },
3578 .{ .name = "note" },
3579 };
3580 const indexes = [_]catalog_mod.IndexDefinition{.{
3581 .name = "items_score",
3582 .fields = &.{0},
3583 }};
3584 _ = try catalog.createRelation(std.testing.allocator, .{
3585 .name = "items",
3586 .columns = &columns,
3587 .indexes = &indexes,
3588 }, .{ .durability = .buffered });
3589
3590 var handle = try catalog.openRelation(std.testing.allocator, "items");
3591 defer handle.deinit();
3592 var index: usize = 0;
3593 while (index < 64) : (index += 1) {
3594 var note_buffer: [24]u8 = undefined;
3595 const note = try std.fmt.bufPrint(¬e_buffer, "note-{d:0>3}", .{index});
3596 const score: i64 = if (index < 60) 0 else @intCast(index - 59);
3597 _ = try handle.relation.put(
3598 std.testing.allocator,
3599 @intCast(index + 1),
3600 &.{
3601 .{ .integer = score },
3602 .{ .text = note },
3603 },
3604 .{ .durability = .buffered },
3605 );
3606 }
3607 _ = try catalog.analyzeRelation(std.testing.allocator, "items", .{ .durability = .buffered });
3608
3609 var statement = try prepare(&catalog, std.testing.allocator, "SELECT note FROM items WHERE score = ?1");
3610 defer statement.deinit();
3611 const select = switch (statement.statement) {
3612 .select => |select_statement| select_statement,
3613 else => return error.UnsupportedStatement,
3614 };
3615 const select_execution = switch (statement.execution) {
3616 .select => |execution| execution,
3617 else => return error.UnsupportedStatement,
3618 };
3619 switch (select_execution.access) {
3620 .index => |index_access| {
3621 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
3622 try std.testing.expect(index_access.cost.from_distribution);
3623 },
3624 else => return error.UnsupportedStatement,
3625 }
3626
3627 const statement_relation = &statement.relation.?;
3628 const common_values = [_]row.Value{.{ .integer = 0 }};
3629 const common_access = try access_mod.runtimeSelectAccess(select, statement_relation, select_execution.fields, &common_values);
3630 switch (common_access) {
3631 .scan => |scan_access| try std.testing.expect(scan_access.?.cost.from_stats),
3632 else => return error.UnsupportedStatement,
3633 }
3634 const rare_values = [_]row.Value{.{ .integer = 4 }};
3635 const rare_access = try access_mod.runtimeSelectAccess(select, statement_relation, select_execution.fields, &rare_values);
3636 switch (rare_access) {
3637 .index => |index_access| {
3638 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
3639 try std.testing.expect(index_access.cost.from_distribution);
3640 try std.testing.expect(index_access.cost.estimated_rows <= 1);
3641 },
3642 else => return error.UnsupportedStatement,
3643 }
3644
3645 try statement.bind(1, .{ .integer = 0 });
3646 var common_cursor: cursor_mod.Cursor = undefined;
3647 try statement.openCursor(&common_cursor, std.testing.allocator);
3648 defer common_cursor.deinit();
3649 switch (common_cursor.access) {
3650 .scan => {},
3651 else => return error.UnsupportedStatement,
3652 }
3653 var common_count: usize = 0;
3654 while (try common_cursor.next()) |_| common_count += 1;
3655 try std.testing.expectEqual(@as(usize, 60), common_count);
3656
3657 try statement.bind(1, .{ .integer = 4 });
3658 var rare_cursor: cursor_mod.Cursor = undefined;
3659 try statement.openCursor(&rare_cursor, std.testing.allocator);
3660 defer rare_cursor.deinit();
3661 switch (rare_cursor.access) {
3662 .index => |index_access| try std.testing.expect(index_access.cost.from_distribution),
3663 else => return error.UnsupportedStatement,
3664 }
3665 var rare_count: usize = 0;
3666 while (try rare_cursor.next()) |_| rare_count += 1;
3667 try std.testing.expectEqual(@as(usize, 1), rare_count);
3668 }
3669
3670 test "prepared statements use composite prefix distribution for runtime range access" {
3671 var tmp = std.testing.tmpDir(.{});
3672 defer tmp.cleanup();
3673
3674 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3675 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3676 .header = testingHeader(),
3677 });
3678 defer database.deinit();
3679 try database.reserve(.{ .wal_frames = 820 });
3680
3681 var catalog = try catalog_mod.Catalog.open(&database, .{});
3682 const columns = [_]catalog_mod.ColumnDefinition{
3683 .{ .name = "region" },
3684 .{ .name = "payload" },
3685 .{ .name = "note" },
3686 };
3687 const indexes = [_]catalog_mod.IndexDefinition{.{
3688 .name = "items_region_payload",
3689 .fields = &.{ 0, 1 },
3690 }};
3691 _ = try catalog.createRelation(std.testing.allocator, .{
3692 .name = "items",
3693 .columns = &columns,
3694 .indexes = &indexes,
3695 }, .{ .durability = .buffered });
3696
3697 var handle = try catalog.openRelation(std.testing.allocator, "items");
3698 defer handle.deinit();
3699 var next_rowid: i64 = 1;
3700 var region: usize = 0;
3701 while (region < 3) : (region += 1) {
3702 var payload: usize = 0;
3703 while (payload < 8) : (payload += 1) {
3704 var note_buffer: [24]u8 = undefined;
3705 const note = try std.fmt.bufPrint(¬e_buffer, "r{d}-p{d}", .{ region, payload });
3706 _ = try handle.relation.put(
3707 std.testing.allocator,
3708 next_rowid,
3709 &.{
3710 .{ .integer = @intCast(region) },
3711 .{ .integer = @intCast(payload) },
3712 .{ .text = note },
3713 },
3714 .{ .durability = .buffered },
3715 );
3716 next_rowid += 1;
3717 }
3718 }
3719 _ = try catalog.analyzeRelation(std.testing.allocator, "items", .{ .durability = .buffered });
3720
3721 var statement = try prepare(&catalog, std.testing.allocator, "SELECT payload, rowid FROM items WHERE region = ?1 AND payload >= ?2");
3722 defer statement.deinit();
3723 const select = switch (statement.statement) {
3724 .select => |select_statement| select_statement,
3725 else => return error.UnsupportedStatement,
3726 };
3727 const select_execution = switch (statement.execution) {
3728 .select => |execution| execution,
3729 else => return error.UnsupportedStatement,
3730 };
3731 switch (select_execution.access) {
3732 .index => |index_access| {
3733 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
3734 try std.testing.expectEqual(ast_mod.PredicateOperator.gte, index_access.operator);
3735 try std.testing.expectEqual(@as(usize, 2), index_access.prefix_count);
3736 try std.testing.expectEqual(@as(usize, 1), index_access.equality_count);
3737 try std.testing.expect(index_access.cost.from_distribution);
3738 try std.testing.expectEqual(@as(usize, 4), index_access.cost.estimated_rows);
3739 },
3740 else => return error.UnsupportedStatement,
3741 }
3742
3743 const statement_relation = &statement.relation.?;
3744 const runtime_values = [_]row.Value{ .{ .integer = 1 }, .{ .integer = 5 } };
3745 const runtime_access = try access_mod.runtimeSelectAccess(select, statement_relation, select_execution.fields, &runtime_values);
3746 switch (runtime_access) {
3747 .index => |index_access| {
3748 try std.testing.expect(index_access.cost.from_distribution);
3749 try std.testing.expectEqual(@as(usize, 3), index_access.cost.estimated_rows);
3750 },
3751 else => return error.UnsupportedStatement,
3752 }
3753
3754 try statement.bind(1, .{ .integer = 1 });
3755 try statement.bind(2, .{ .integer = 5 });
3756 var rows = try statement.execute(std.testing.allocator, .{ .durability = .buffered });
3757 defer rows.deinit(std.testing.allocator);
3758 try std.testing.expectEqual(@as(usize, 3), rows.rowCount());
3759 var expected_payload: i64 = 5;
3760 while (rows.nextRow()) |bytes| {
3761 const view = try row.View.init(bytes);
3762 try std.testing.expectEqual(expected_payload, (try view.column(0)).integer);
3763 expected_payload += 1;
3764 }
3765 try std.testing.expectEqual(@as(i64, 8), expected_payload);
3766 }
3767
3768 test "prepared statements require matching predicate index collation" {
3769 var tmp = std.testing.tmpDir(.{});
3770 defer tmp.cleanup();
3771
3772 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3773 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3774 .header = testingHeader(),
3775 });
3776 defer database.deinit();
3777 try database.reserve(.{ .wal_frames = 620 });
3778
3779 var catalog = try catalog_mod.Catalog.open(&database, .{});
3780 const columns = [_]catalog_mod.ColumnDefinition{.{
3781 .name = "name",
3782 .column = .{ .collation = .nocase },
3783 }};
3784 const binary_indexes = [_]catalog_mod.IndexDefinition{.{
3785 .name = "binary_name",
3786 .fields = &.{0},
3787 }};
3788 _ = try catalog.createRelation(std.testing.allocator, .{
3789 .name = "binary_items",
3790 .columns = &columns,
3791 .indexes = &binary_indexes,
3792 }, .{ .durability = .buffered });
3793
3794 const nocase_index_columns = [_]row.Column{.{ .collation = .nocase }};
3795 const nocase_indexes = [_]catalog_mod.IndexDefinition{.{
3796 .name = "nocase_name",
3797 .fields = &.{0},
3798 .columns = &nocase_index_columns,
3799 }};
3800 _ = try catalog.createRelation(std.testing.allocator, .{
3801 .name = "nocase_items",
3802 .columns = &columns,
3803 .indexes = &nocase_indexes,
3804 }, .{ .durability = .buffered });
3805
3806 var binary = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM binary_items WHERE name = 'Ada'");
3807 defer binary.deinit();
3808 const binary_execution = switch (binary.execution) {
3809 .select => |execution| execution,
3810 else => return error.UnsupportedStatement,
3811 };
3812 switch (binary_execution.access) {
3813 .scan => |scan_access| try std.testing.expectEqual(@as(usize, 0), scan_access.?.field),
3814 else => return error.UnsupportedStatement,
3815 }
3816
3817 var nocase = try prepare(&catalog, std.testing.allocator, "SELECT rowid FROM nocase_items WHERE name = 'Ada'");
3818 defer nocase.deinit();
3819 const nocase_execution = switch (nocase.execution) {
3820 .select => |execution| execution,
3821 else => return error.UnsupportedStatement,
3822 };
3823 switch (nocase_execution.access) {
3824 .index => |index_access| try std.testing.expectEqual(@as(usize, 0), index_access.index_slot),
3825 else => return error.UnsupportedStatement,
3826 }
3827 }
3828
3829 test "prepared statements bind parameters by index and name" {
3830 var tmp = std.testing.tmpDir(.{});
3831 defer tmp.cleanup();
3832
3833 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3834 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3835 .header = testingHeader(),
3836 });
3837 defer database.deinit();
3838 try database.reserve(.{ .wal_frames = 520 });
3839
3840 var catalog = try catalog_mod.Catalog.open(&database, .{});
3841 const columns = [_]catalog_mod.ColumnDefinition{
3842 .{ .name = "name" },
3843 .{ .name = "score" },
3844 .{ .name = "note", .default = .{ .text = "fresh" } },
3845 };
3846 _ = try catalog.createRelation(std.testing.allocator, .{
3847 .name = "items",
3848 .columns = &columns,
3849 }, .{ .durability = .buffered });
3850 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
3851 defer database_session.deinit();
3852
3853 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, score) VALUES (?1, :name, ?)");
3854 defer insert.deinit();
3855 try std.testing.expectEqual(@as(usize, 3), insert.parameterCount());
3856 try std.testing.expectEqualStrings("?1", (try insert.parameterName(1)).?);
3857 try std.testing.expectEqualStrings(":name", (try insert.parameterName(2)).?);
3858 try std.testing.expect((try insert.parameterName(3)) == null);
3859 try std.testing.expectEqual(@as(usize, 2), insert.parameterIndex(":name").?);
3860
3861 var name_buffer = [_]u8{ 'A', 'd', 'a' };
3862 try insert.bind(1, .{ .integer = 14 });
3863 try insert.bindName(":name", .{ .text = name_buffer[0..] });
3864 name_buffer[0] = 'X';
3865 try insert.bind(3, .{ .integer = 88 });
3866 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
3867 defer inserted.deinit(std.testing.allocator);
3868
3869 var select = try prepare(&catalog, std.testing.allocator, "SELECT name, score, note, rowid FROM items WHERE rowid = ?1");
3870 defer select.deinit();
3871 try select.bind(1, .{ .integer = 14 });
3872 var selected = try select.execute(std.testing.allocator, .{ .durability = .buffered });
3873 defer selected.deinit(std.testing.allocator);
3874 const view = try row.View.init(selected.nextRow().?);
3875 try std.testing.expectEqualStrings("Ada", (try view.column(0)).text);
3876 try std.testing.expectEqual(@as(i64, 88), (try view.column(1)).integer);
3877 try std.testing.expectEqualStrings("fresh", (try view.column(2)).text);
3878 try std.testing.expectEqual(@as(i64, 14), (try view.column(3)).integer);
3879
3880 var repeat = try prepare(&catalog, std.testing.allocator, "INSERT INTO items (rowid, name, note) VALUES (:id, :same, :same)");
3881 defer repeat.deinit();
3882 try std.testing.expectEqual(@as(usize, 2), repeat.parameterCount());
3883 try repeat.bindName(":id", .{ .integer = 15 });
3884 try repeat.bindName(":same", .{ .text = "shared" });
3885 var repeated = try repeat.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
3886 defer repeated.deinit(std.testing.allocator);
3887
3888 try select.bind(1, .{ .integer = 15 });
3889 selected.deinit(std.testing.allocator);
3890 selected = try select.execute(std.testing.allocator, .{ .durability = .buffered });
3891 const repeat_view = try row.View.init(selected.nextRow().?);
3892 try std.testing.expectEqualStrings("shared", (try repeat_view.column(0)).text);
3893 try std.testing.expectEqual(row.Value.nil, try repeat_view.column(1));
3894 try std.testing.expectEqualStrings("shared", (try repeat_view.column(2)).text);
3895 try std.testing.expectEqual(@as(i64, 15), (try repeat_view.column(3)).integer);
3896
3897 try std.testing.expectError(error.ParameterIndexOutOfBounds, insert.bind(0, .nil));
3898 try std.testing.expectError(error.ParameterNotFound, insert.bindName(":missing", .nil));
3899
3900 var unbound = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE rowid = ?");
3901 defer unbound.deinit();
3902 try std.testing.expectError(error.ExpectedRowId, unbound.execute(std.testing.allocator, .{ .durability = .buffered }));
3903 }
3904
3905 test "prepared statement cache key includes parameter shape" {
3906 var tmp = std.testing.tmpDir(.{});
3907 defer tmp.cleanup();
3908
3909 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3910 .paths = .{ .database = "statement-cache-key.db", .wal = "statement-cache-key.wal" },
3911 .header = testingHeader(),
3912 });
3913 defer database.deinit();
3914 try database.reserve(.{ .wal_frames = 320 });
3915
3916 var catalog = try catalog_mod.Catalog.open(&database, .{});
3917 _ = try catalog.createRelation(std.testing.allocator, .{
3918 .name = "items",
3919 .columns = &.{.{ .name = "name" }},
3920 }, .{ .durability = .buffered });
3921
3922 var anonymous = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE rowid = ?");
3923 defer anonymous.deinit();
3924 var numbered = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE rowid = ?1");
3925 defer numbered.deinit();
3926 var named = try prepare(&catalog, std.testing.allocator, "SELECT name FROM items WHERE rowid = :id");
3927 defer named.deinit();
3928
3929 const anonymous_key = anonymous.cacheKey();
3930 const numbered_key = numbered.cacheKey();
3931 const named_key = named.cacheKey();
3932 try std.testing.expect(version.same(anonymous_key.relation, numbered_key.relation));
3933 try std.testing.expect(version.same(anonymous_key.schema, numbered_key.schema));
3934 try std.testing.expect(version.same(anonymous_key.stats, numbered_key.stats));
3935 try std.testing.expect(!version.same(anonymous_key.parameters, numbered_key.parameters));
3936 try std.testing.expect(!version.same(numbered_key.parameters, named_key.parameters));
3937 try std.testing.expect(!plan.PlanKey.same(anonymous_key, numbered_key));
3938 try std.testing.expect(!plan.PlanKey.same(numbered_key, named_key));
3939
3940 try numbered.bind(1, .{ .integer = 7 });
3941 try std.testing.expect(version.same(numbered_key.parameters, numbered.cacheKey().parameters));
3942 }
3943
3944 test "prepared statement ignores unrelated catalog schema changes" {
3945 var tmp = std.testing.tmpDir(.{});
3946 defer tmp.cleanup();
3947
3948 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3949 .paths = .{ .database = "statement.db", .wal = "statement.wal" },
3950 .header = recoveredHeader(),
3951 });
3952 defer database.deinit();
3953 try database.reserve(.{ .wal_frames = 420 });
3954
3955 var catalog = try catalog_mod.Catalog.open(&database, .{});
3956 _ = try catalog.createRelation(std.testing.allocator, .{
3957 .name = "items",
3958 }, .{ .durability = .buffered });
3959 var database_session = try testingDatabaseSession(std.testing.allocator, &catalog);
3960 defer database_session.deinit();
3961
3962 var insert = try prepare(&catalog, std.testing.allocator, "INSERT INTO items VALUES (5, 77)");
3963 defer insert.deinit();
3964 var inserted = try insert.execute(std.testing.allocator, .{ .durability = .buffered, .session = &database_session });
3965 defer inserted.deinit(std.testing.allocator);
3966
3967 var select = try prepare(&catalog, std.testing.allocator, "SELECT * FROM items WHERE rowid = 5");
3968 defer select.deinit();
3969 try std.testing.expectEqual(@as(u64, 1), select.relation.?.schema.version);
3970 const before_key = select.cacheKey();
3971
3972 _ = try catalog.createRelation(std.testing.allocator, .{
3973 .name = "users",
3974 }, .{ .durability = .buffered });
3975 try select.relation.?.validate();
3976 try std.testing.expect(plan.PlanKey.same(before_key, try select.currentCacheKey()));
3977
3978 var selected = try select.execute(std.testing.allocator, .{ .durability = .buffered });
3979 defer selected.deinit(std.testing.allocator);
3980 try std.testing.expectEqual(@as(u64, 1), select.relation.?.schema.version);
3981 const after_key = select.cacheKey();
3982 try std.testing.expect(plan.PlanKey.same(before_key, after_key));
3983 try std.testing.expect(version.same(before_key.parameters, after_key.parameters));
3984 const bytes = selected.nextRow().?;
3985 const view = try row.View.init(bytes);
3986 try std.testing.expectEqual(@as(i64, 77), (try view.column(0)).integer);
3987 }
3988
3989 test "prepared statement reprepares after stats root changes" {
3990 var tmp = std.testing.tmpDir(.{});
3991 defer tmp.cleanup();
3992
3993 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
3994 .paths = .{ .database = "statement-stats.db", .wal = "statement-stats.wal" },
3995 .header = recoveredHeader(),
3996 });
3997 defer database.deinit();
3998 try database.reserve(.{ .wal_frames = 920 });
3999
4000 var catalog = try catalog_mod.Catalog.open(&database, .{});
4001 const columns = [_]catalog_mod.ColumnDefinition{
4002 .{ .name = "name" },
4003 .{ .name = "score" },
4004 .{ .name = "note" },
4005 };
4006 const indexes = [_]catalog_mod.IndexDefinition{
4007 .{
4008 .name = "items_score",
4009 .fields = &.{1},
4010 },
4011 .{
4012 .name = "items_score_name",
4013 .fields = &.{ 1, 0 },
4014 },
4015 };
4016 _ = try catalog.createRelation(std.testing.allocator, .{
4017 .name = "items",
4018 .columns = &columns,
4019 .indexes = &indexes,
4020 }, .{ .durability = .buffered });
4021
4022 var handle = try catalog.openRelation(std.testing.allocator, "items");
4023 defer handle.deinit();
4024 var index: usize = 0;
4025 while (index < 40) : (index += 1) {
4026 var name_buffer: [24]u8 = undefined;
4027 const name = try std.fmt.bufPrint(&name_buffer, "name-{d:0>3}", .{index});
4028 _ = try handle.relation.put(
4029 std.testing.allocator,
4030 @intCast(index + 1),
4031 &.{
4032 .{ .text = name },
4033 .{ .integer = @intCast(index % 5) },
4034 .{ .text = "kept" },
4035 },
4036 .{ .durability = .buffered },
4037 );
4038 }
4039
4040 var statement = try prepare(&catalog, std.testing.allocator, "SELECT name, score, rowid FROM items WHERE score = ?1");
4041 defer statement.deinit();
4042 const before_key = statement.cacheKey();
4043 const before_execution = switch (statement.execution) {
4044 .select => |execution| execution,
4045 else => return error.UnsupportedStatement,
4046 };
4047 switch (before_execution.access) {
4048 .index => |index_access| {
4049 try std.testing.expectEqual(@as(usize, 0), index_access.index_slot);
4050 try std.testing.expect(!index_access.cost.from_stats);
4051 },
4052 else => return error.UnsupportedStatement,
4053 }
4054
4055 _ = try catalog.analyzeRelation(std.testing.allocator, "items", .{ .durability = .buffered });
4056 try std.testing.expectError(error.PlanChanged, statement.relation.?.validate());
4057
4058 try statement.bind(1, .{ .integer = 3 });
4059 var rows = try statement.execute(std.testing.allocator, .{ .durability = .buffered });
4060 defer rows.deinit(std.testing.allocator);
4061 const after_key = statement.cacheKey();
4062 try std.testing.expect(!plan.PlanKey.same(before_key, after_key));
4063 try std.testing.expect(!version.same(before_key.stats, after_key.stats));
4064 const after_execution = switch (statement.execution) {
4065 .select => |execution| execution,
4066 else => return error.UnsupportedStatement,
4067 };
4068 switch (after_execution.access) {
4069 .index => |index_access| {
4070 try std.testing.expectEqual(@as(usize, 1), index_access.index_slot);
4071 try std.testing.expect(index_access.cost.from_stats);
4072 },
4073 else => return error.UnsupportedStatement,
4074 }
4075 try std.testing.expectEqual(@as(usize, 8), rows.rowCount());
4076 }
4077
4078 pub fn testingHeader() wal.Header {
4079 return .{
4080 .sequence = 1301,
4081 .salt = .{ .first = 0x2222_eeee, .second = 0x4444_ffff },
4082 };
4083 }
4084
4085 pub fn testWorkingEntry(database_session: *const session_mod.DatabaseSession, name: []const u8) ?version.Hash {
4086 for (database_session.workingRoot().entries) |entry| {
4087 if (std.mem.eql(u8, entry.name, name)) return entry.hash;
4088 }
4089 return null;
4090 }
4091
4092 pub fn testingDatabaseSession(allocator: Allocator, catalog: *const catalog_mod.Catalog) ast_mod.Error!session_mod.DatabaseSession {
4093 var root = try version.databaseRootMaintained(
4094 allocator,
4095 catalog,
4096 version.ConflictRoot.empty().hash,
4097 );
4098 const commit = version.Commit.init(root.hash, &.{});
4099 const root_hash = root.hash;
4100 return session_mod.DatabaseSession.initWithRoot(allocator, branch_mod.checkout(.{
4101 .name = "main",
4102 .target = commit.hash,
4103 }, root_hash), &root);
4104 }
4105
4106 pub fn recoveredHeader() wal.Header {
4107 return .{
4108 .sequence = 1302,
4109 .salt = .{ .first = 0x6666_dddd, .second = 0x8888_cccc },
4110 };
4111 }
4112
4113 test "covering planner requires order, binary covered columns and a lower cost" {
4114 var tmp = std.testing.tmpDir(.{});
4115 defer tmp.cleanup();
4116 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
4117 .paths = .{ .database = "cover-plan.db", .wal = "cover-plan.wal" },
4118 .header = testingHeader(),
4119 });
4120 defer database.deinit();
4121 try database.reserve(.{ .wal_frames = 320 });
4122 var catalog = try catalog_mod.Catalog.open(&database, .{});
4123 _ = try catalog.createRelation(std.testing.allocator, .{
4124 .name = "items",
4125 .columns = &.{
4126 .{ .name = "a" },
4127 .{ .name = "b" },
4128 .{ .name = "body" },
4129 .{ .name = "extra" },
4130 },
4131 .indexes = &.{
4132 .{ .name = "wide", .fields = &.{ 0, 1, 2 } },
4133 .{ .name = "narrow", .fields = &.{ 0, 1 } },
4134 },
4135 }, .{ .durability = .buffered });
4136 var covered = try prepare(
4137 &catalog,
4138 std.testing.allocator,
4139 "SELECT rowid, a, b FROM items ORDER BY rowid",
4140 );
4141 defer covered.deinit();
4142 const covered_access = covered.execution.select.access;
4143 try std.testing.expect(covered_access == .covering);
4144 try std.testing.expectEqual(@as(usize, 1), covered_access.covering);
4145
4146 const scanned_sources = [_][]const u8{
4147 "SELECT rowid, a, b FROM items",
4148 "SELECT rowid, a, b, extra FROM items ORDER BY rowid",
4149 "SELECT rowid, a, b FROM items ORDER BY extra",
4150 "SELECT rowid, a, b FROM items WHERE extra > 0 ORDER BY rowid",
4151 };
4152 for (scanned_sources) |source| {
4153 var statement = try prepare(&catalog, std.testing.allocator, source);
4154 defer statement.deinit();
4155 try std.testing.expect(statement.execution.select.access == .scan);
4156 }
4157
4158 _ = try catalog.createRelation(std.testing.allocator, .{
4159 .name = "nocase_items",
4160 .columns = &.{
4161 .{ .name = "a", .column = .{ .collation = .nocase } },
4162 .{ .name = "b" },
4163 },
4164 .indexes = &.{.{ .name = "nocase_index", .fields = &.{ 0, 1 } }},
4165 }, .{ .durability = .buffered });
4166 var nocase = try prepare(
4167 &catalog,
4168 std.testing.allocator,
4169 "SELECT rowid, a, b FROM nocase_items ORDER BY rowid",
4170 );
4171 defer nocase.deinit();
4172 try std.testing.expect(nocase.execution.select.access == .scan);
4173
4174 _ = try catalog.createRelation(std.testing.allocator, .{
4175 .name = "small_items",
4176 .columns = &.{ .{ .name = "a" }, .{ .name = "b" }, .{ .name = "c" } },
4177 .indexes = &.{.{ .name = "small_ab", .fields = &.{ 0, 1 } }},
4178 }, .{ .durability = .buffered });
4179 var small_handle = try catalog.openRelation(std.testing.allocator, "small_items");
4180 defer small_handle.deinit();
4181 for (0..18) |offset| {
4182 const values = [_]row.Value{
4183 .{ .integer = @intCast(offset) },
4184 .{ .integer = @intCast(offset % 3) },
4185 .{ .integer = 1 },
4186 };
4187 _ = try small_handle.relation.put(
4188 std.testing.allocator,
4189 @intCast(offset + 1),
4190 &values,
4191 .{ .durability = .buffered },
4192 );
4193 }
4194 _ = try catalog.analyzeRelation(
4195 std.testing.allocator,
4196 "small_items",
4197 .{ .durability = .buffered },
4198 );
4199 var small = try prepare(
4200 &catalog,
4201 std.testing.allocator,
4202 "SELECT rowid, a, b FROM small_items ORDER BY rowid",
4203 );
4204 defer small.deinit();
4205 try std.testing.expect(small.execution.select.access == .scan);
4206 }
4207
4208 test "covering index and table scan return the same overflow-backed cards" {
4209 var tmp = std.testing.tmpDir(.{});
4210 defer tmp.cleanup();
4211 var database = try file.Database.openForTesting(std.testing.allocator, tmp.dir, .{
4212 .paths = .{ .database = "cover-rows.db", .wal = "cover-rows.wal" },
4213 .header = testingHeader(),
4214 });
4215 defer database.deinit();
4216 try database.reserve(.{ .wal_frames = 900 });
4217 var catalog = try catalog_mod.Catalog.open(&database, .{});
4218 const columns = [_]catalog_mod.ColumnDefinition{
4219 .{ .name = "a" },
4220 .{ .name = "b" },
4221 .{ .name = "body" },
4222 };
4223 _ = try catalog.createRelation(std.testing.allocator, .{
4224 .name = "covered_cards",
4225 .columns = &columns,
4226 .indexes = &.{.{ .name = "cards_ab", .fields = &.{ 0, 1 } }},
4227 }, .{ .durability = .buffered });
4228 _ = try catalog.createRelation(std.testing.allocator, .{
4229 .name = "plain_cards",
4230 .columns = &columns,
4231 }, .{ .durability = .buffered });
4232 var covered_handle = try catalog.openRelation(std.testing.allocator, "covered_cards");
4233 defer covered_handle.deinit();
4234 var plain_handle = try catalog.openRelation(std.testing.allocator, "plain_cards");
4235 defer plain_handle.deinit();
4236 var body: [2048]u8 = undefined;
4237 @memset(&body, 'x');
4238 for (0..18) |offset| {
4239 const rowid: i64 = @intCast(offset + 1);
4240 const values = [_]row.Value{
4241 .{ .integer = @intCast(offset % 5) },
4242 .{ .integer = @intCast((offset * 3) % 7) },
4243 .{ .text = &body },
4244 };
4245 _ = try covered_handle.relation.put(
4246 std.testing.allocator,
4247 rowid,
4248 &values,
4249 .{ .durability = .buffered },
4250 );
4251 _ = try plain_handle.relation.put(
4252 std.testing.allocator,
4253 rowid,
4254 &values,
4255 .{ .durability = .buffered },
4256 );
4257 }
4258 _ = try catalog.analyzeRelation(
4259 std.testing.allocator,
4260 "covered_cards",
4261 .{ .durability = .buffered },
4262 );
4263 const Access = std.meta.Tag(access_mod.SelectAccess);
4264 const queries = [_]struct { access: Access, covered: []const u8, plain: []const u8 }{
4265 .{
4266 .access = .covering,
4267 .covered = "SELECT rowid, a, b FROM covered_cards ORDER BY rowid",
4268 .plain = "SELECT rowid, a, b FROM plain_cards ORDER BY rowid",
4269 },
4270 .{
4271 .access = .covering,
4272 .covered = "SELECT rowid, a, b FROM covered_cards ORDER BY rowid LIMIT 6 OFFSET 4",
4273 .plain = "SELECT rowid, a, b FROM plain_cards ORDER BY rowid LIMIT 6 OFFSET 4",
4274 },
4275 .{
4276 .access = .covering,
4277 .covered = "SELECT rowid, a, b FROM covered_cards ORDER BY b DESC, rowid",
4278 .plain = "SELECT rowid, a, b FROM plain_cards ORDER BY b DESC, rowid",
4279 },
4280 .{
4281 .access = .covering,
4282 .covered = "SELECT b, rowid FROM covered_cards ORDER BY a",
4283 .plain = "SELECT b, rowid FROM plain_cards ORDER BY a",
4284 },
4285 .{
4286 .access = .covering,
4287 .covered = "SELECT rowid, a, b FROM covered_cards WHERE b >= 2 " ++
4288 "ORDER BY b DESC, rowid LIMIT 5 OFFSET 2",
4289 .plain = "SELECT rowid, a, b FROM plain_cards WHERE b >= 2 " ++
4290 "ORDER BY b DESC, rowid LIMIT 5 OFFSET 2",
4291 },
4292 .{
4293 .access = .index,
4294 .covered = "SELECT rowid, b FROM covered_cards WHERE a = 3 ORDER BY b DESC",
4295 .plain = "SELECT rowid, b FROM plain_cards WHERE a = 3 ORDER BY b DESC",
4296 },
4297 .{
4298 .access = .index,
4299 .covered = "SELECT a, b FROM covered_cards WHERE a >= 2 AND b < 5 " ++
4300 "ORDER BY rowid LIMIT 4 OFFSET 1",
4301 .plain = "SELECT a, b FROM plain_cards WHERE a >= 2 AND b < 5 " ++
4302 "ORDER BY rowid LIMIT 4 OFFSET 1",
4303 },
4304 };
4305 for (queries) |query| {
4306 var covered_statement = try prepare(&catalog, std.testing.allocator, query.covered);
4307 defer covered_statement.deinit();
4308 var plain_statement = try prepare(&catalog, std.testing.allocator, query.plain);
4309 defer plain_statement.deinit();
4310 const stats = covered_statement.relation.?.relationStats().?;
4311 try std.testing.expectEqual(@as(usize, 18), stats.table.overflow_records);
4312 try std.testing.expectEqual(@as(usize, 1), stats.indexes.len);
4313 try std.testing.expectEqual(@as(usize, 0), stats.indexes[0].summary.overflow_pages);
4314 const covered_access: Access = covered_statement.execution.select.access;
4315 try std.testing.expectEqual(query.access, covered_access);
4316 try std.testing.expect(plain_statement.execution.select.access == .scan);
4317 var covered_rows = try covered_statement.execute(
4318 std.testing.allocator,
4319 .{ .durability = .buffered },
4320 );
4321 defer covered_rows.deinit(std.testing.allocator);
4322 var plain_rows = try plain_statement.execute(
4323 std.testing.allocator,
4324 .{ .durability = .buffered },
4325 );
4326 defer plain_rows.deinit(std.testing.allocator);
4327 try std.testing.expect(plain_rows.rowCount() > 0);
4328 try std.testing.expectEqual(plain_rows.rowCount(), covered_rows.rowCount());
4329 while (plain_rows.nextRow()) |expected| {
4330 try std.testing.expectEqualSlices(u8, expected, covered_rows.nextRow().?);
4331 }
4332 try std.testing.expect(covered_rows.nextRow() == null);
4333
4334 var cursor_expected = try plain_statement.execute(
4335 std.testing.allocator,
4336 .{ .durability = .buffered },
4337 );
4338 defer cursor_expected.deinit(std.testing.allocator);
4339 var covered_cursor: cursor_mod.Cursor = undefined;
4340 try covered_statement.openCursor(&covered_cursor, std.testing.allocator);
4341 defer covered_cursor.deinit();
4342 while (cursor_expected.nextRow()) |expected| {
4343 try std.testing.expectEqualSlices(u8, expected, (try covered_cursor.next()).?);
4344 }
4345 try std.testing.expect(try covered_cursor.next() == null);
4346 }
4347 }