Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : SP Notes Core9// File : Sources/SPNotesAPI/NotesAPI+Databases.swift10// Description : The database routes: databases, properties, views, rows11// (client ids), query, templates, duplicate, CSV import and12// export. Every `databaseID` may be the database id or the13// id of its host page.14// Module : SPNotesAPI15// Repository : https://git.spboucher.ai/sp-notes-core16// Created : 2026-11-1917// Last Modified : 2026-11-1918//19// =============================================================================20// AUTHOR & PROFESSIONAL AFFILIATION21// =============================================================================22//23// Author : Simon-Pierre Boucher24// Position : Professor of Finance25// Institution : Université du Québec en Outaouais (UQO)26// Department : Département des sciences administratives27// Organization : SP Boucher28//29// Website : https://spboucher.ai30// Git : https://git.spboucher.ai31//32// =============================================================================33// OFFICIAL CONTACT DIRECTORY34// =============================================================================35//36// General : info@spboucher.ai37// Professional : contact@spboucher.ai38// Direct : simon@spboucher.ai39// Research : research@spboucher.ai40// Development : dev@spboucher.ai41// Support : support@spboucher.ai42// Security/Admin : admin@spboucher.ai43// Notifications : notifications@spboucher.ai44//45// Preferred Contact: support@spboucher.ai46//47// =============================================================================48// COPYRIGHT & INTELLECTUAL PROPERTY49// =============================================================================50//51// Copyright (c) 202652// Simon-Pierre Boucher53//54// All rights reserved where applicable.55//56// This file is governed by the applicable project license,57// copyright notices, institutional agreements and relevant law.58//59// Unless expressly authorized by the applicable license or60// rights holder, copying, redistribution, sublicensing,61// publication and commercial exploitation are not permitted.62//63// Existing third-party rights and notices remain unaffected.64//65// =============================================================================66// CONFIDENTIALITY & SECURITY67// =============================================================================68//69// Classification : Public70// Distribution : Public repository; use is governed by the project license71// Security Contact : admin@spboucher.ai72//73// Access to this source does not independently grant permission74// to redistribute or disclose confidential materials.75//76// Do not embed credentials, private keys, access tokens,77// certificates or confidential personal information.78//79// =============================================================================80// LICENSE & MAINTENANCE81// =============================================================================82//83// License : None specified (no license file in this repository)84// Maintainer : Simon-Pierre Boucher85// Technical Contact: dev@spboucher.ai86//87// =============================================================================88// END OF SP BOUCHER SOURCE HEADER89// =============================================================================9091import Foundation9293extension NotesAPI {94 private static func db(_ id: String, _ tail: String = "") -> String { "/api/databases/\(segment(id))\(tail)" }9596 // MARK: - Databases9798 /// `GET /api/databases` — every live database of the workspace, with its row count.99 public func databases() async throws -> [DatabaseInfo] {100 try await client.decode([DatabaseInfo].self, .get("/api/databases"))101 }102103 /// `GET /api/databases/<id>` — the database with its properties and views. A database is created with its host104 /// page (`createPage(PageDraft(id:, type: .database))`); its object (title property, default table view) is born105 /// the first time this route is called with the host page id. `404` (no code) when unknown, `400` when malformed.106 public func database(id: String) async throws -> DatabaseInfo {107 try await client.decode(DatabaseInfo.self, .get(Self.db(id)))108 }109110 /// `PATCH /api/databases/<id>` — name, description, icon, template flag. There is no `DELETE`: trash the host page.111 public func updateDatabase(id: String, _ changes: DatabaseChanges, idempotencyKey: String? = nil) async throws -> DatabaseInfo {112 try await client.decode(DatabaseInfo.self, .json(.patch, Self.db(id), body: changes, idempotencyKey: idempotencyKey))113 }114115 /// `POST /api/databases/<id>/duplicate` → 201, the new database (new host page, properties, views, rows on demand).116 public func duplicateDatabase(id: String, _ options: DatabaseDuplicateOptions = DatabaseDuplicateOptions(), idempotencyKey: String? = nil) async throws -> DatabaseInfo {117 try await client.decode(DatabaseInfo.self, .json(.post, Self.db(id, "/duplicate"), body: options, idempotencyKey: idempotencyKey))118 }119120 // MARK: - Templates121122 /// `GET /api/databases/templates` — the built-in templates.123 public func databaseTemplates() async throws -> [DatabaseTemplate] {124 try await client.decode([DatabaseTemplate].self, .get("/api/databases/templates"))125 }126127 private struct TemplateBody: Encodable { var template: String }128129 /// `POST /api/databases/<id>/apply-template` — adds the properties and views of a template to a database (create130 /// the database first, then apply). `400` (no code) for an unknown template.131 public func applyDatabaseTemplate(databaseID: String, template: String, idempotencyKey: String? = nil) async throws -> DatabaseTemplateApplied {132 try await client.decode(DatabaseTemplateApplied.self, .json(.post, Self.db(databaseID, "/apply-template"), body: TemplateBody(template: template), idempotencyKey: idempotencyKey))133 }134135 // MARK: - Properties136137 /// `POST /api/databases/<id>/properties` → 201. Idempotent on `draft.id` (201 again, the stored property untouched,138 /// no `x-existing` header); `409 id_taken` when the id belongs to another database.139 public func createDatabaseProperty(databaseID: String, _ draft: DatabasePropertyDraft, idempotencyKey: String? = nil) async throws -> DatabaseProperty {140 try await client.decode(DatabaseProperty.self, .json(.post, Self.db(databaseID, "/properties"), body: draft, idempotencyKey: idempotencyKey))141 }142143 private struct PropertyPatchBody: Encodable {144 var id: String145 var changes: DatabasePropertyChanges146 func encode(to encoder: Encoder) throws {147 try changes.encode(to: encoder)148 var c = encoder.container(keyedBy: DatabasePropertyChanges.CodingKeys.self)149 try c.encode(id, forKey: .id)150 }151 }152153 /// `PATCH /api/databases/<id>/properties` `{ id, … }`. `404` (no code) for an unknown property.154 public func updateDatabaseProperty(databaseID: String, propertyID: String, _ changes: DatabasePropertyChanges, idempotencyKey: String? = nil) async throws -> DatabaseProperty {155 try await client.decode(DatabaseProperty.self, .json(.patch, Self.db(databaseID, "/properties"), body: PropertyPatchBody(id: propertyID, changes: changes), idempotencyKey: idempotencyKey))156 }157158 private struct OrderBody: Encodable { var order: [String] }159160 /// `PATCH /api/databases/<id>/properties` `{ order }` — positions 1…n in the given order.161 @discardableResult162 public func reorderDatabaseProperties(databaseID: String, order: [String], idempotencyKey: String? = nil) async throws -> OKResult {163 try await client.decode(OKResult.self, .json(.patch, Self.db(databaseID, "/properties"), body: OrderBody(order: order), idempotencyKey: idempotencyKey))164 }165166 /// `DELETE /api/databases/<id>/properties?id=` — `404` unknown property, `400` for the title property.167 @discardableResult168 public func deleteDatabaseProperty(databaseID: String, propertyID: String, idempotencyKey: String? = nil) async throws -> OKResult {169 try await client.decode(OKResult.self, HTTPRequest(method: .delete, path: Self.db(databaseID, "/properties"), query: [URLQueryItem(name: "id", value: propertyID)], idempotencyKey: idempotencyKey))170 }171172 // MARK: - Views173174 /// `GET /api/databases/<id>/views`.175 public func databaseViews(databaseID: String) async throws -> [DatabaseView] {176 try await client.decode([DatabaseView].self, .get(Self.db(databaseID, "/views")))177 }178179 /// `POST /api/databases/<id>/views` → 201. Idempotent on `draft.id` (201 again, no `x-existing`).180 public func createDatabaseView(databaseID: String, _ draft: DatabaseViewDraft, idempotencyKey: String? = nil) async throws -> DatabaseView {181 try await client.decode(DatabaseView.self, .json(.post, Self.db(databaseID, "/views"), body: draft, idempotencyKey: idempotencyKey))182 }183184 private struct ViewPatchBody: Encodable {185 var id: String186 var name: String?187 var type: String?188 var config: RawJSON?189 var position: Double?190 var isDefault: Bool?191 }192193 /// `PATCH /api/databases/<id>/views` `{ id, … }`. `404` (no code) for an unknown view.194 public func updateDatabaseView(databaseID: String, viewID: String, _ changes: DatabaseViewChanges, idempotencyKey: String? = nil) async throws -> DatabaseView {195 let body = ViewPatchBody(id: viewID, name: changes.name, type: changes.type, config: changes.config, position: changes.position, isDefault: changes.isDefault)196 return try await client.decode(DatabaseView.self, .json(.patch, Self.db(databaseID, "/views"), body: body, idempotencyKey: idempotencyKey))197 }198199 /// `DELETE /api/databases/<id>/views?id=` — `400` for the last view. Observed: an unknown view id answers 200.200 @discardableResult201 public func deleteDatabaseView(databaseID: String, viewID: String, idempotencyKey: String? = nil) async throws -> OKResult {202 try await client.decode(OKResult.self, HTTPRequest(method: .delete, path: Self.db(databaseID, "/views"), query: [URLQueryItem(name: "id", value: viewID)], idempotencyKey: idempotencyKey))203 }204205 // MARK: - Rows206207 /// `GET /api/databases/<id>/rows[?q=&limit=]` — light references of the live rows (relation pickers), by title.208 /// - Parameter limit: ≤ 500 (server default 50).209 public func databaseRowRefs(databaseID: String, search: String? = nil, limit: Int? = nil) async throws -> [DatabaseRelationRef] {210 let query = Self.item("q", search) + Self.item("limit", limit.map(String.init))211 return try await client.decode([DatabaseRelationRef].self, .get(Self.db(databaseID, "/rows"), query: query))212 }213214 /// `GET /api/databases/<id>/rows?full=1[&q=&limit=&offset=]` — hydrated rows in stored order (no view applied).215 public func databaseRows(databaseID: String, search: String? = nil, limit: Int? = nil, offset: Int? = nil) async throws -> DatabaseQueryResult {216 let query = [URLQueryItem(name: "full", value: "1")] + Self.item("q", search) + Self.item("limit", limit.map(String.init)) + Self.item("offset", offset.map(String.init))217 return try await client.decode(DatabaseQueryResult.self, .get(Self.db(databaseID, "/rows"), query: query))218 }219220 /// `POST /api/databases/<id>/rows` → 201, the hydrated row. Idempotent on `draft.id` (201 again, the stored row221 /// untouched, no `x-existing`). `409 row_deleted` / `409 id_taken` (`APIError.isRowDeleted`, `.isIDTaken`).222 public func createDatabaseRow(databaseID: String, _ draft: DatabaseRowDraft, idempotencyKey: String? = nil) async throws -> DatabaseRow {223 try await client.decode(DatabaseRow.self, .json(.post, Self.db(databaseID, "/rows"), body: draft, idempotencyKey: idempotencyKey))224 }225226 private struct RowsBody: Encodable { var rows: [DatabaseRowDraft] }227228 /// `POST /api/databases/<id>/rows` `{ rows }` (≤ 500) → 201, the rows in the same order.229 public func createDatabaseRows(databaseID: String, _ drafts: [DatabaseRowDraft], idempotencyKey: String? = nil) async throws -> [DatabaseRow] {230 try await client.decode([DatabaseRow].self, .json(.post, Self.db(databaseID, "/rows"), body: RowsBody(rows: drafts), idempotencyKey: idempotencyKey))231 }232233 /// `PATCH /api/databases/<id>/rows` `{ id, values }` — merges `values` (keys = property ids or names; `null` /234 /// `""` / `[]` removes a value; the title property renames the page). `404` (no code) unknown or deleted row.235 public func updateDatabaseRow(databaseID: String, rowID: String, values: RawJSON, idempotencyKey: String? = nil) async throws -> DatabaseRow {236 try await client.decode(DatabaseRow.self, .json(.patch, Self.db(databaseID, "/rows"), body: DatabaseRowUpdate(id: rowID, values: values), idempotencyKey: idempotencyKey))237 }238239 private struct UpdatesBody: Encodable { var updates: [DatabaseRowUpdate] }240241 /// `PATCH /api/databases/<id>/rows` `{ updates }` (≤ 500) — the updated rows in the same order.242 public func updateDatabaseRows(databaseID: String, _ updates: [DatabaseRowUpdate], idempotencyKey: String? = nil) async throws -> [DatabaseRow] {243 try await client.decode([DatabaseRow].self, .json(.patch, Self.db(databaseID, "/rows"), body: UpdatesBody(updates: updates), idempotencyKey: idempotencyKey))244 }245246 /// `PATCH /api/databases/<id>/rows` `{ order }` (≤ 5000 ids) — positions 1…n in the given order.247 @discardableResult248 public func reorderDatabaseRows(databaseID: String, order: [String], idempotencyKey: String? = nil) async throws -> OKResult {249 try await client.decode(OKResult.self, .json(.patch, Self.db(databaseID, "/rows"), body: OrderBody(order: order), idempotencyKey: idempotencyKey))250 }251252 /// `DELETE /api/databases/<id>/rows?ids=a,b` — soft delete: the rows get `deletedAt`, their pages go to the trash.253 @discardableResult254 public func deleteDatabaseRows(databaseID: String, ids: [String], idempotencyKey: String? = nil) async throws -> DatabaseRowsDeleted {255 try await client.decode(DatabaseRowsDeleted.self, HTTPRequest(method: .delete, path: Self.db(databaseID, "/rows"), query: [URLQueryItem(name: "ids", value: ids.joined(separator: ","))], idempotencyKey: idempotencyKey))256 }257258 private struct IDsBody: Encodable { var ids: [String] }259260 /// `POST /api/databases/<id>/rows/restore` `{ ids }` (1…500) — undo of a deletion: rows and pages come back.261 @discardableResult262 public func restoreDatabaseRows(databaseID: String, ids: [String], idempotencyKey: String? = nil) async throws -> DatabaseRowsRestored {263 try await client.decode(DatabaseRowsRestored.self, .json(.post, Self.db(databaseID, "/rows/restore"), body: IDsBody(ids: ids), idempotencyKey: idempotencyKey))264 }265266 // MARK: - Query267268 /// `POST /api/databases/<id>/query` — filter, sort, search and page the rows (hydrated: relations, rollups and269 /// formulas computed). A read sent as POST: it needs the `Origin` header like a mutation.270 public func queryDatabase(id: String, _ query: DatabaseQuery = DatabaseQuery()) async throws -> DatabaseQueryResult {271 try await client.decode(DatabaseQueryResult.self, .json(.post, Self.db(id, "/query"), body: query))272 }273274 /// `GET /api/databases/<id>/query?format=csv[&viewId=&q=]` — the rows as CSV text (UTF-8 with BOM, ≤ 1000 rows,275 /// filter / sorts / hidden columns of the view, display values).276 public func databaseCSV(id: String, viewID: String? = nil, search: String? = nil) async throws -> String {277 let query = [URLQueryItem(name: "format", value: "csv")] + Self.item("viewId", viewID) + Self.item("q", search)278 return try await text(.get(Self.db(id, "/query"), query: query), accept: "text/csv").text279 }280281 /// `POST /api/databases/<id>/import` (multipart `file`, ≤ 20 MB) → 201 — appends the rows of a CSV file; unknown282 /// columns become `text` properties.283 public func importDatabaseCSV(databaseID: String, fileURL: URL, filename: String? = nil) async throws -> DatabaseCSVImportResult {284 let form = MultipartForm()285 let body = FileManager.default.temporaryDirectory.appendingPathComponent("spnotes-csv-\(UUID().uuidString).multipart")286 defer { try? FileManager.default.removeItem(at: body) }287 try form.write(fields: [], fileField: "file", filename: filename ?? fileURL.lastPathComponent, mimeType: "text/csv", source: fileURL, to: body)288 let response = try await client.upload(HTTPRequest(method: .post, path: Self.db(databaseID, "/import"), contentType: form.contentType), fromFile: body)289 return try response.decode(DatabaseCSVImportResult.self)290 }291}292