SPB Git

Repository Public

NotesAPI+Databases.swift 16.9 KB 292 lines swift

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
Files main
Menu

Theme