From 0e12e7238f48ffc2a5d35dae059c2f00c7250f3b Mon Sep 17 00:00:00 2001 From: yyamashita Date: Sat, 9 May 2026 14:36:28 +0900 Subject: Add /api/export and /api/import endpoints for DB backup and sync Co-Authored-By: Claude Sonnet 4.6 --- app/lib/db.server.ts | 119 ++++++++++++++++++++++++++++++++++++++++++++++ app/routes.ts | 2 + app/routes/api-export.tsx | 16 +++++++ app/routes/api-import.tsx | 30 ++++++++++++ 4 files changed, 167 insertions(+) create mode 100644 app/routes/api-export.tsx create mode 100644 app/routes/api-import.tsx diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts index bf63e70..e35bba6 100644 --- a/app/lib/db.server.ts +++ b/app/lib/db.server.ts @@ -408,3 +408,122 @@ export function updateArtist(id: string, input: UpdateArtistInput): void { ).run(crypto.randomUUID(), id, snapshot, input.message, input.ip_address); })(); } + +// ── Export / Import ─────────────────────────────────────────────────────────── + +interface BandArtistRaw { + band_id: string; + artist_id: string; + role: string | null; + order_index: number; +} + +export interface DbExport { + version: 1; + exported_at: string; + bands: Band[]; + band_links: BandLink[]; + artists: Artist[]; + artist_links: ArtistLink[]; + band_artists: BandArtistRaw[]; + band_revisions: BandRevision[]; + artist_revisions: ArtistRevision[]; +} + +export function exportDb(): DbExport { + const db = getDb(); + return { + version: 1, + exported_at: new Date().toISOString(), + bands: db.prepare("SELECT * FROM bands").all() as Band[], + band_links: db.prepare("SELECT * FROM band_links ORDER BY band_id, order_index").all() as BandLink[], + artists: db.prepare("SELECT * FROM artists").all() as Artist[], + artist_links: db.prepare("SELECT * FROM artist_links ORDER BY artist_id, order_index").all() as ArtistLink[], + band_artists: db.prepare("SELECT * FROM band_artists ORDER BY band_id, order_index").all() as BandArtistRaw[], + band_revisions: db.prepare("SELECT * FROM band_revisions ORDER BY created_at").all() as BandRevision[], + artist_revisions: db.prepare("SELECT * FROM artist_revisions ORDER BY created_at").all() as ArtistRevision[], + }; +} + +export interface ImportResult { + bands: number; + artists: number; + band_links: number; + artist_links: number; + band_artists: number; + band_revisions: number; + artist_revisions: number; +} + +export function importDb(data: DbExport): ImportResult { + if (data.version !== 1) throw new Error("Unsupported export version"); + const db = getDb(); + return db.transaction(() => { + db.prepare("DELETE FROM band_artists").run(); + db.prepare("DELETE FROM band_revisions").run(); + db.prepare("DELETE FROM artist_revisions").run(); + db.prepare("DELETE FROM band_links").run(); + db.prepare("DELETE FROM artist_links").run(); + db.prepare("DELETE FROM bands").run(); + db.prepare("DELETE FROM artists").run(); + + const insertBand = db.prepare( + "INSERT INTO bands (id, slug, name, area, description, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)" + ); + for (const b of data.bands) { + insertBand.run(b.id, b.slug, b.name, b.area, b.description, b.status, b.created_at); + } + + const insertArtist = db.prepare( + "INSERT INTO artists (id, slug, name, created_at) VALUES (?, ?, ?, ?)" + ); + for (const a of data.artists) { + insertArtist.run(a.id, a.slug, a.name, a.created_at); + } + + const insertBandLink = db.prepare( + "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)" + ); + for (const l of data.band_links) { + insertBandLink.run(l.id, l.band_id, l.label, l.url, l.order_index); + } + + const insertArtistLink = db.prepare( + "INSERT INTO artist_links (id, artist_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)" + ); + for (const l of data.artist_links) { + insertArtistLink.run(l.id, l.artist_id, l.label, l.url, l.order_index); + } + + const insertBandArtist = db.prepare( + "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)" + ); + for (const ba of data.band_artists) { + insertBandArtist.run(ba.band_id, ba.artist_id, ba.role, ba.order_index); + } + + const insertBandRev = db.prepare( + "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address, created_at) VALUES (?, ?, ?, ?, ?, ?)" + ); + for (const r of data.band_revisions) { + insertBandRev.run(r.id, r.band_id, r.snapshot, r.message, r.ip_address, r.created_at); + } + + const insertArtistRev = db.prepare( + "INSERT INTO artist_revisions (id, artist_id, snapshot, message, ip_address, created_at) VALUES (?, ?, ?, ?, ?, ?)" + ); + for (const r of data.artist_revisions) { + insertArtistRev.run(r.id, r.artist_id, r.snapshot, r.message, r.ip_address, r.created_at); + } + + return { + bands: data.bands.length, + artists: data.artists.length, + band_links: data.band_links.length, + artist_links: data.artist_links.length, + band_artists: data.band_artists.length, + band_revisions: data.band_revisions.length, + artist_revisions: data.artist_revisions.length, + }; + })() as ImportResult; +} diff --git a/app/routes.ts b/app/routes.ts index d02abf7..1d8d60b 100644 --- a/app/routes.ts +++ b/app/routes.ts @@ -4,6 +4,8 @@ export default [ index("routes/home.tsx"), route("/api/bands", "routes/api-bands.tsx"), route("/api/artists", "routes/api-artists.tsx"), + route("/api/export", "routes/api-export.tsx"), + route("/api/import", "routes/api-import.tsx"), route("/bands/new", "routes/band-new.tsx"), route("/bands/of/:uuid", "routes/band-by-uuid.tsx"), route("/bands/named/:slug", "routes/band-by-slug.tsx"), diff --git a/app/routes/api-export.tsx b/app/routes/api-export.tsx new file mode 100644 index 0000000..17437df --- /dev/null +++ b/app/routes/api-export.tsx @@ -0,0 +1,16 @@ +import { exportDb } from "~/lib/db.server"; + +export function loader() { + const data = exportDb(); + const date = new Date().toISOString().slice(0, 10); + return new Response(JSON.stringify(data, null, 2), { + headers: { + "Content-Type": "application/json", + "Content-Disposition": `attachment; filename="whois-export-${date}.json"`, + }, + }); +} + +export default function () { + return null; +} diff --git a/app/routes/api-import.tsx b/app/routes/api-import.tsx new file mode 100644 index 0000000..e28a7db --- /dev/null +++ b/app/routes/api-import.tsx @@ -0,0 +1,30 @@ +import type { ActionFunctionArgs } from "react-router"; +import { importDb, type DbExport } from "~/lib/db.server"; + +export async function action({ request }: ActionFunctionArgs) { + if (request.method !== "POST") { + return Response.json({ error: "Method not allowed" }, { status: 405 }); + } + + let data: DbExport; + try { + data = await request.json(); + } catch { + return Response.json({ error: "Invalid JSON body" }, { status: 400 }); + } + + if (!data || data.version !== 1) { + return Response.json({ error: "Invalid or unsupported export format (expected version 1)" }, { status: 400 }); + } + + try { + const result = importDb(data); + return Response.json({ ok: true, imported: result }); + } catch (e) { + return Response.json({ error: e instanceof Error ? e.message : "Import failed" }, { status: 500 }); + } +} + +export default function () { + return null; +} -- cgit v1.2.3