Repository Public
1// SP Notes Core2// File: Tests/SPNotesAPITests/ParityDatabaseFixtureTests.swift3// Description: The database models decode the real answers of the scratch backend (group `databases`): databases,4// properties, views, rows with computed cells, query, templates, CSV, errors, and the entity feed rows.5//6// Author: Simon-Pierre Boucher7// Contact: contact@spboucher.ai8import Foundation9import XCTest10@testable import SPNotesAPI1112final class ParityDatabaseFixtureTests: XCTestCase {13 private let group = "databases"1415 private func full() throws -> DatabaseInfo { try NextFixture.decode(DatabaseInfo.self, group, "db-get") }1617 private func property(_ name: String, file: StaticString = #filePath, line: UInt = #line) throws -> DatabaseProperty {18 try XCTUnwrap(full().properties?.first { $0.name == name }, "property \(name)", file: file, line: line)19 }2021 func testDatabaseObjectListAndPatch() throws {22 let born = try NextFixture.decode(DatabaseInfo.self, group, "db-get-new")23 XCTAssertEqual(born.name, "Fixture · Biens")24 XCTAssertEqual(born.properties?.map(\.type), [DatabasePropertyType.title], "a new database has its title property")25 XCTAssertEqual(born.titleProperty?.name, "Name")26 XCTAssertEqual(born.views?.map(\.type), [DatabaseViewType.table])27 XCTAssertEqual(born.defaultView?.isDefault, true)28 XCTAssertNotEqual(born.id, born.pageId, "the database id is not the host page id")29 XCTAssertNil(born.rev, "the object of the route has no rev (the feed row has)")3031 let patched = try NextFixture.decode(DatabaseInfo.self, group, "db-patch")32 XCTAssertEqual(patched.name, "Fixture · Biens immobiliers")33 XCTAssertEqual(patched.description, "Parc locatif")34 XCTAssertEqual(patched.icon, "🏠")35 XCTAssertEqual(patched.isTemplate, false)3637 let whole = try full()38 XCTAssertEqual(whole.id, born.id)39 XCTAssertGreaterThan(whole.properties?.count ?? 0, 10)40 XCTAssertTrue(whole.properties?.allSatisfy { $0.databaseId == whole.id } ?? false)41 XCTAssertEqual(Set((whole.properties ?? []).map(\.type)).subtracting(DatabasePropertyType.all), [], "only known property types")4243 let list = try NextFixture.decode([DatabaseInfo].self, group, "db-list")44 let item = try XCTUnwrap(list.first { $0.id == whole.id })45 XCTAssertEqual(item.rowCount, 2, "live rows only")46 XCTAssertNotNil(item.updatedAt)47 XCTAssertNil(item.properties, "the list carries no properties")4849 let trashed = try NextFixture.decode(DatabaseInfo.self, group, "db-get-trashed")50 XCTAssertEqual(trashed.name, "Fixture · Lectures", "a database whose host page is in the trash is still readable")51 }5253 func testDuplicateAndTemplates() throws {54 let copy = try NextFixture.decode(DatabaseInfo.self, group, "db-duplicate")55 XCTAssertEqual(copy.name, "Fixture · Biens (copie)")56 XCTAssertNotEqual(copy.id, try full().id)57 XCTAssertEqual(copy.properties?.map(\.name), try full().properties?.map(\.name), "same columns, new ids")58 XCTAssertTrue(Set(copy.properties?.map(\.id) ?? []).isDisjoint(with: try full().properties?.map(\.id) ?? []))59 let template = try NextFixture.decode(DatabaseInfo.self, group, "db-duplicate-template")60 XCTAssertEqual(template.isTemplate, true)61 XCTAssertEqual(try NextFixture.load(group, "db-duplicate").entry.status, 201)6263 let templates = try NextFixture.decode([DatabaseTemplate].self, group, "templates")64 XCTAssertEqual(templates.map(\.id), ["research-papers", "tasks", "reading-list"])65 XCTAssertEqual(templates[2].properties?.first, "Title")66 XCTAssertNotNil(templates[0].description)67 let applied = try NextFixture.decode(DatabaseTemplateApplied.self, group, "template-apply")68 XCTAssertEqual(applied.applied, "reading-list")69 XCTAssertEqual(applied.properties, 9)70 let after = try NextFixture.decode(DatabaseInfo.self, group, "template-db")71 XCTAssertEqual(after.titleProperty?.name, "Title", "the template renames the title property")72 XCTAssertEqual(after.icon, "📖")73 XCTAssertTrue(after.properties?.contains { $0.type == DatabasePropertyType.ai } ?? false)74 let unknown = try NextFixture.error(group, "template-unknown")75 XCTAssertEqual(unknown.status, 400)76 XCTAssertNil(unknown.code)77 }7879 func testProperties() throws {80 let created = try NextFixture.decode(DatabaseProperty.self, group, "prop-create")81 XCTAssertEqual(created.type, DatabasePropertyType.select)82 XCTAssertEqual(created.width, 160)83 XCTAssertEqual(created.options.map(\.name), ["Loué", "Vacant"])84 XCTAssertEqual(created.options.first?.color, "green")85 XCTAssertNotNil(created.rev)86 XCTAssertFalse(created.isComputed)8788 // The same client id again: 201, the stored property, no x-existing header.89 let again = try NextFixture.load(group, "prop-create-again")90 XCTAssertEqual(again.entry.status, 201)91 XCTAssertNil(again.entry.headers["x-existing"])92 XCTAssertEqual(try APIJSON.decoder().decode(DatabaseProperty.self, from: again.data), created, "a retry never overwrites")9394 let relation = try NextFixture.decode(DatabaseProperty.self, group, "prop-create-relation")95 XCTAssertEqual(relation.typedConfig.twoWay, true)96 XCTAssertNotNil(relation.typedConfig.targetDatabaseId)97 // The mirror property of a two-way relation lives in the target database and points back with syncedPropertyId.98 let mirror = try XCTUnwrap(try NextFixture.decode(SyncPage.self, group, "feed").entities?[SyncKind.databaseProperty]?99 .compactMap { try? $0.decode(DatabaseProperty.self) }.first { $0.typedConfig.syncedPropertyId == relation.id })100 XCTAssertEqual(mirror.type, DatabasePropertyType.relation)101 XCTAssertEqual(mirror.databaseId, relation.typedConfig.targetDatabaseId)102 XCTAssertEqual(mirror.typedConfig.targetDatabaseId, relation.databaseId)103104 let rollup = try NextFixture.decode(DatabaseProperty.self, group, "prop-create-rollup")105 XCTAssertEqual(rollup.typedConfig.aggregate, DatabaseAggregate.sum)106 XCTAssertEqual(rollup.typedConfig.relationPropertyId, relation.id)107 XCTAssertTrue(rollup.isComputed)108 let formula = try NextFixture.decode(DatabaseProperty.self, group, "prop-create-formula")109 XCTAssertEqual(formula.typedConfig.expression, #"prop("Prix") * 2"#)110 XCTAssertTrue(formula.isReadOnly)111 XCTAssertEqual(try NextFixture.decode(DatabaseProperty.self, group, "prop-create-number").typedConfig.format, "currency")112113 let patched = try NextFixture.decode(DatabaseProperty.self, group, "prop-patch")114 XCTAssertEqual(patched.name, "Remarque")115 XCTAssertEqual(patched.width, 320)116 XCTAssertGreaterThan(patched.rev ?? 0, 0)117 XCTAssertEqual(try NextFixture.decode(DatabaseProperty.self, group, "prop-patch-type").type, DatabasePropertyType.url)118 XCTAssertTrue(try NextFixture.decode(OKResult.self, group, "prop-reorder").ok)119 XCTAssertTrue(try NextFixture.decode(OKResult.self, group, "prop-delete").ok)120 XCTAssertEqual(try property("Prix").position, 1, "reordered first")121 }122123 func testPropertyErrors() throws {124 let taken = try NextFixture.error(group, "prop-id-taken")125 XCTAssertTrue(taken.isIDTaken)126 for name in ["prop-title-refused", "prop-validation", "prop-patch-no-id", "prop-delete-title"] {127 let error = try NextFixture.error(group, name)128 XCTAssertEqual(error.status, 400, name)129 XCTAssertNil(error.code, "\(name): 2.x database errors carry no code")130 XCTAssertTrue(error.isBadRequest)131 }132 let missing = try NextFixture.error(group, "prop-delete-not-found")133 XCTAssertTrue(missing.isNotFound)134 XCTAssertNil(missing.code)135 XCTAssertFalse(missing.isRouteMissing, "a JSON 404 is an answer of the route")136137 XCTAssertTrue(try NextFixture.error(group, "db-not-found").isNotFound)138 XCTAssertEqual(try NextFixture.error(group, "db-bad-id").status, 400)139 // There is no DELETE on a database: 405 with an empty body.140 let noDelete = try NextFixture.error(group, "db-delete-not-allowed")141 XCTAssertEqual(noDelete.status, 405)142 XCTAssertTrue(noDelete.isRouteMissing)143 }144145 func testViews() throws {146 let created = try NextFixture.decode(DatabaseView.self, group, "view-create")147 XCTAssertEqual(created.type, DatabaseViewType.board)148 XCTAssertEqual(created.isDefault, false)149 let config = created.typedConfig150 XCTAssertEqual(config.groupBy, try property("État").id)151 XCTAssertEqual(config.sorts, [DatabaseSort(property: try property("Prix").id, descending: true)])152 XCTAssertEqual(config.hidden?.count, 1)153 XCTAssertEqual(config.filter, .all([.rule(try property("Prix").id, DatabaseFilterOp.gt, 100_000)]))154155 let again = try NextFixture.load(group, "view-create-again")156 XCTAssertEqual(again.entry.status, 201)157 XCTAssertEqual(try APIJSON.decoder().decode(DatabaseView.self, from: again.data), created)158159 let patched = try NextFixture.decode(DatabaseView.self, group, "view-patch")160 XCTAssertEqual(patched.name, "Tableau par état")161 XCTAssertEqual(patched.isDefault, true)162 XCTAssertEqual(patched.position, 3)163 XCTAssertEqual(patched.config, created.config, "a patch without config keeps it")164165 let list = try NextFixture.decode([DatabaseView].self, group, "view-list")166 XCTAssertEqual(list.count, 3)167 XCTAssertEqual(list.filter { $0.isDefault == true }.map(\.id), [patched.id], "one default view")168 XCTAssertEqual(list.first { $0.type == DatabaseViewType.calendar }?.typedConfig.dateProperty, try property("Achat").id)169170 XCTAssertTrue(try NextFixture.decode(OKResult.self, group, "view-delete").ok)171 XCTAssertTrue(try NextFixture.decode(OKResult.self, group, "view-delete-not-found").ok, "deleting an unknown view answers 200")172 XCTAssertEqual(try NextFixture.error(group, "view-delete-last").status, 400)173 XCTAssertEqual(try NextFixture.error(group, "view-validation").status, 400)174 }175176 func testRowsAndComputedCells() throws {177 let row = try NextFixture.decode(DatabaseRow.self, group, "row-create")178 XCTAssertEqual(row.title, "255, rue Laurel")179 XCTAssertEqual(row.icon, "🏠")180 XCTAssertNil(row.databaseId, "the row routes do not repeat the database id")181 XCTAssertNil(row.rev)182 XCTAssertNotNil(row.createdAt)183184 XCTAssertEqual(row.cell(try property("Name").id)?.string, "255, rue Laurel", "the title is also a cell")185 XCTAssertEqual(row.cell(try property("État").id), .text("optaaaa1"), "a select holds the option id")186 XCTAssertEqual(row.cell(try property("Prix").id)?.double, 425_000)187 XCTAssertEqual(row.cell(try property("Achat").id)?.string, "2026-03-15")188 XCTAssertEqual(row.cell(try property("Inspecté").id)?.boolean, true)189 XCTAssertEqual(row.cell(try property("Étiquettes").id)?.strings, ["tagaaaa1", "tagaaaa2"])190 // Computed by the server: relation (hydrated), rollup, formula, formula error, created time.191 let cities = try XCTUnwrap(row.cell(try property("Ville").id)?.references)192 XCTAssertEqual(Set(cities.map(\.title)), ["Gatineau", "Québec"])193 XCTAssertTrue(cities.allSatisfy { !$0.rowId.isEmpty && !$0.pageId.isEmpty })194 XCTAssertEqual(row.cell(try property("Population ville").id), .number(840_000), "rollup: sum over the relation")195 XCTAssertEqual(row.cell(try property("Prix double").id), .number(850_000), "formula")196 XCTAssertEqual(row.cell(try property("Cassée").id)?.errorMessage, "Unexpected end of formula")197 XCTAssertNotNil(row.cell(try property("Créé").id)?.string.flatMap(APIDate.parse))198 XCTAssertNil(row.cell("no-such-property"))199 XCTAssertEqual(row.cells.count, 13)200201 let again = try NextFixture.load(group, "row-create-again")202 XCTAssertEqual(again.entry.status, 201)203 XCTAssertNil(again.entry.headers["x-existing"])204 XCTAssertEqual(try APIJSON.decoder().decode(DatabaseRow.self, from: again.data).title, row.title, "a retry with the same id never overwrites")205206 let bulk = try NextFixture.decode([DatabaseRow].self, group, "row-create-bulk")207 XCTAssertEqual(bulk.map(\.title), ["99, rue de Magog", "252, montée Dalton", "Terrain"])208 XCTAssertEqual(bulk.map(\.position), [2, 3, 4])209210 let patched = try NextFixture.decode(DatabaseRow.self, group, "row-patch")211 XCTAssertEqual(patched.title, "255, rue Laurel (révisé)", "the title property renames the page")212 XCTAssertEqual(patched.cell(try property("Prix").id)?.double, 430_000)213 XCTAssertNil(patched.cell(try property("Inspecté").id), "null removes a value")214 XCTAssertEqual(patched.cell(try property("Prix double").id)?.double, 860_000, "formulas follow")215 let many = try NextFixture.decode([DatabaseRow].self, group, "row-patch-bulk")216 XCTAssertEqual(many.count, 2)217 XCTAssertEqual(many[1].cell(try property("Prix").id)?.double, 565_000, "values may be keyed by property name")218 XCTAssertTrue(try NextFixture.decode(OKResult.self, group, "row-reorder").ok)219220 let refs = try NextFixture.decode([DatabaseRelationRef].self, group, "row-refs")221 XCTAssertEqual(refs.count, 4)222 XCTAssertEqual(refs.first?.title, "252, montée Dalton", "in the new order")223 XCTAssertEqual(try NextFixture.decode([DatabaseRelationRef].self, group, "row-refs-search").map(\.icon), ["🏠"])224 let page = try NextFixture.decode(DatabaseQueryResult.self, group, "row-list-full")225 XCTAssertEqual(page.total, 4)226 XCTAssertEqual(page.limit, 2)227 XCTAssertEqual(page.offset, 1)228 XCTAssertEqual(page.rows.count, 2)229 XCTAssertTrue(page.hasMore)230 }231232 func testRowErrorsDeleteAndRestore() throws {233 let deleted = try NextFixture.error(group, "row-deleted")234 XCTAssertTrue(deleted.isRowDeleted)235 XCTAssertEqual(deleted.code, APIError.NextCode.rowDeleted)236 XCTAssertTrue(try NextFixture.error(group, "row-id-taken").isIDTaken)237 XCTAssertTrue(try NextFixture.error(group, "row-page-id-taken").isIDTaken)238 let strict = try NextFixture.error(group, "row-validation")239 XCTAssertEqual(strict.status, 400)240 XCTAssertTrue(strict.message.contains("Unrecognized key"), "a row body takes no unknown key")241 XCTAssertTrue(try NextFixture.error(group, "row-patch-not-found").isNotFound)242 XCTAssertEqual(try NextFixture.error(group, "row-patch-no-id").status, 400)243 XCTAssertEqual(try NextFixture.error(group, "row-delete-bad-id").code, APIError.NextCode.badRequest)244245 XCTAssertEqual(try NextFixture.decode(DatabaseRowsDeleted.self, group, "row-delete").deleted, 1)246 XCTAssertEqual(try NextFixture.decode(DatabaseRowsDeleted.self, group, "row-delete-again").deleted, 1, "already deleted rows are counted again")247 XCTAssertEqual(try NextFixture.decode(DatabaseRowsRestored.self, group, "row-restore").restored, 1)248 XCTAssertEqual(try NextFixture.decode(DatabaseRowsRestored.self, group, "row-restore-again").restored, 0)249 XCTAssertEqual(try NextFixture.decode(DatabaseRowsDeleted.self, group, "row-delete-many").deleted, 2)250 }251252 func testQueryAndCSV() throws {253 let price = try property("Prix").id254 let all = try NextFixture.decode(DatabaseQueryResult.self, group, "query-all")255 XCTAssertEqual(all.total, 4)256 XCTAssertEqual(all.limit, 200, "server default")257 XCTAssertFalse(all.hasMore)258259 let filtered = try NextFixture.decode(DatabaseQueryResult.self, group, "query-filter-sort")260 XCTAssertEqual(filtered.rows.compactMap { $0.cell(price)?.double }, [565_000, 447_000], "price ≥ 430 000 and (Loué or no status), descending, 2 per page")261 XCTAssertEqual(filtered.total, 3)262 XCTAssertTrue(filtered.hasMore)263264 let view = try NextFixture.decode(DatabaseQueryResult.self, group, "query-view")265 XCTAssertEqual(view.rows.compactMap { $0.cell(price)?.double }, [565_000, 447_000, 430_000], "the filter and sort of the view: > 100 000, descending")266 XCTAssertEqual(try NextFixture.decode(DatabaseQueryResult.self, group, "query-get-view").rows.map(\.id), view.rows.map(\.id))267 XCTAssertEqual(try NextFixture.decode(DatabaseQueryResult.self, group, "query-search").rows.map(\.title), ["252, montée Dalton"])268 XCTAssertEqual(try NextFixture.error(group, "query-validation").status, 400)269270 // The mirror side of a two-way relation is computed too.271 let cities = try NextFixture.decode(DatabaseQueryResult.self, group, "query-two-way")272 let gatineau = try XCTUnwrap(cities.rows.first { $0.title == "Gatineau" })273 XCTAssertEqual(gatineau.cells.values.compactMap(\.references).first?.map(\.title), ["255, rue Laurel (révisé)"])274275 let csv = try NextFixture.load(group, "query-csv")276 XCTAssertEqual(csv.entry.headers["content-type"], "text/csv; charset=utf-8")277 let text = String(decoding: csv.data, as: UTF8.self)278 XCTAssertTrue(text.hasPrefix("\u{FEFF}Name,Prix,État"), "UTF-8 with BOM, one column per property")279 XCTAssertTrue(text.contains("#ERR Unexpected end of formula"))280 let viewCSV = try NextFixture.load(group, "db-csv-view")281 XCTAssertFalse(String(decoding: viewCSV.data, as: UTF8.self).contains("Remarque"), "hidden columns of the view are left out")282 XCTAssertNotNil(viewCSV.entry.headers["content-disposition"])283284 let imported = try NextFixture.decode(DatabaseCSVImportResult.self, group, "csv-import")285 XCTAssertEqual(imported.created, 2)286 XCTAssertEqual(imported.properties, ["Quartier"])287 XCTAssertEqual(try NextFixture.error(group, "csv-import-no-file").status, 400)288 }289290 func testFilterRoundTrip() throws {291 let filter = DatabaseFilter.all([.rule("p1", DatabaseFilterOp.gte, 430_000), .any([.rule("p2", DatabaseFilterOp.eq, "opt"), .rule(property: "p2", op: DatabaseFilterOp.empty, value: nil)])])292 let data = try APIJSON.encoder().encode(filter)293 XCTAssertEqual(String(decoding: data, as: UTF8.self), #"{"and":[{"op":"gte","property":"p1","value":430000},{"or":[{"op":"eq","property":"p2","value":"opt"},{"op":"empty","property":"p2"}]}]}"#)294 XCTAssertEqual(try APIJSON.decoder().decode(DatabaseFilter.self, from: data), filter)295 }296297 /// The same types decode the rows of the entity feed (API-NEXT §3.1).298 func testEntityFeedRows() throws {299 let feed = try NextFixture.decode(SyncPage.self, group, "feed")300 let entities = try XCTUnwrap(feed.entities)301 XCTAssertEqual(Set(entities.keys), [SyncKind.database, SyncKind.databaseProperty, SyncKind.databaseView, SyncKind.databaseRow, SyncKind.reference])302303 let databases = try XCTUnwrap(entities[SyncKind.database]).map { try $0.decode(DatabaseInfo.self) }304 XCTAssertEqual(databases.count, 5)305 let main = try XCTUnwrap(databases.first { $0.name == "Fixture · Biens immobiliers" })306 XCTAssertEqual(main.id, try full().id)307 XCTAssertNotNil(main.rev)308 XCTAssertNotNil(main.createdAt)309 XCTAssertNil(main.deletedAt)310 XCTAssertNil(main.properties, "properties and views are kinds of their own in the feed")311 XCTAssertEqual(databases.filter { $0.isTemplate == true }.count, 1)312313 let properties = try XCTUnwrap(entities[SyncKind.databaseProperty]).map { try $0.decode(DatabaseProperty.self) }314 let fed = properties.filter { $0.databaseId == main.id }315 XCTAssertTrue(Set(fed.map(\.id)).isSuperset(of: try full().properties?.map(\.id) ?? []))316 XCTAssertEqual(Set(fed.map(\.name)).subtracting(try full().properties?.map(\.name) ?? []), ["Quartier"], "plus the column created by the CSV import")317 XCTAssertTrue(properties.allSatisfy { $0.rev != nil && $0.position != nil })318 XCTAssertEqual(properties.first { $0.id == (try? property("État").id) }?.options.count, 2)319320 let views = try XCTUnwrap(entities[SyncKind.databaseView]).map { try $0.decode(DatabaseView.self) }321 XCTAssertEqual(views.filter { $0.databaseId == main.id }.count, 2)322 XCTAssertNotNil(views.first { $0.type == DatabaseViewType.board }?.typedConfig.groupBy)323324 let rows = try XCTUnwrap(entities[SyncKind.databaseRow]).map { try $0.decode(DatabaseRow.self) }325 let mine = rows.filter { $0.databaseId == main.id }326 XCTAssertEqual(mine.count, 6)327 XCTAssertEqual(mine.filter { $0.deletedAt != nil }.count, 2, "deleted rows stay in the feed with deletedAt")328 XCTAssertTrue(mine.allSatisfy { $0.rev != nil && $0.title == nil }, "feed rows carry no title field")329 let laurel = try XCTUnwrap(mine.first { $0.cell((try? property("Name").id) ?? "")?.string == "255, rue Laurel (révisé)" })330 XCTAssertEqual(laurel.cell(try property("Prix").id)?.double, 430_000)331 XCTAssertNil(laurel.cell(try property("Ville").id), "stored values only: no relation…")332 XCTAssertNil(laurel.cell(try property("Prix double").id), "…and no formula in the feed")333334 // Observed: trashing the host page does not set deletedAt on the database row; a purge sends tombstones.335 let trashed = try NextFixture.decode(SyncPage.self, group, "feed-database-trashed")336 let lectures = try XCTUnwrap(trashed.entities?[SyncKind.database]?.first { (try? $0.decode(DatabaseInfo.self).name) == "Fixture · Lectures" })337 XCTAssertNil(lectures.deletedAt)338 let purged = try NextFixture.decode(SyncPage.self, group, "feed-database-purged")339 let kinds = Set(purged.tombstones.map(\.kind))340 XCTAssertTrue(kinds.isSuperset(of: ["database", "database_property", "database_view", "database_row"]), "a purged database leaves tombstones of every kind: \(kinds)")341 }342}343